<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.api.manage.dao.chargeManagement.SysAdvancePaymentDao">

    <select id="list" parameterType="com.api.model.chargeManagement.SearchAdvancePayment" resultType="com.api.vo.chargeManagement.VoAdvancePayment">
        select cbue.id,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,GROUP_CONCAT(ur.name) as resident_name,
        (case cbue.advance_payment_price is null when true then 0 else cbue.advance_payment_price end) as advance_payment_price
        from cpm_building_unit as cbu,cpm_building as cb,cpm_resident_estate as cre,user_resident as ur,cpm_building_unit_estate as cbue
        <where>
            cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and cre.building_unit_estate_id = cbue.id
            and cre.resident_id = ur.id
            <if test="roomNumber != null and roomNumber != ''">
                and cbue.room_number like concat('%',#{roomNumber},'%')
            </if>
            <if test="unitNo != null">
                and cbu.no = #{unitNo}
            </if>
            <if test="buildingNo != null">
                and cb.no = #{buildingNo}
            </if>
            <if test="residentName != null and residentName != ''">
                and ur.name like concat('%',#{residentName},'%')
            </if>
        </where>
        GROUP BY cbue.id
    </select>
    <select id="findNearDateByEstateId" parameterType="java.lang.Integer" resultType="com.api.model.alipay.SysAdvancePaymentOrder">
        select * from sys_advance_payment_order
        where estate_id = #{id}
        order by create_date desc limit 1
    </select>
    <select id="findDetailById" parameterType="com.api.model.chargeManagement.SearchAdvancePaymentDetail" resultType="com.api.vo.chargeManagement.VoAdvancePaymentDetail">
        select T.* from (
            select sapo.id,sapo.create_date,sapo.pay_price,'充值-预缴' as remake,sapo.pay_type
            from sys_advance_payment_order as sapo
            where estate_id = #{estateId} and status = 2
            union all
            select sdpo.id,sdpo.create_date,sdpo.pay_price,CONCAT('支付-',sctd.name) as remake,4 as pay_type
            from sys_daily_payment_order as sdpo,sys_daily_payment_order_list as sdpol,sys_daily_payment as sdp,sys_charges_template_detail as sctd
            where sdpol.daily_payment_id = sdp.id and sdpol.daily_payment_order_id = sdpo.id and sdp.charges_template_detail_id = sctd.id and sdp.building_unit_estate_id = #{estateId}
            and sdpo.status = 2 and sdpo.pay_type = 5
            union all
            select saprr.id,saprr.create_date,saprr.refund_price,'退款' as remake,3 as pay_type
            from sys_advance_payment_refund_record as saprr
            where saprr.estate_id = #{estateId}
        ) as T
        <where>
            <if test="years != null">
                YEAR(T.create_date) = #{years}
            </if>
            <if test="months != null">
                and MONTH(T.create_date) = #{months}
            </if>
        </where>
        order by T.create_date desc
    </select>

    <insert id="refund" parameterType="com.api.model.chargeManagement.SysAdvancePaymentRefundRecord">
        insert into sys_advance_payment_refund_record(
            estate_id,
            refund_price,
            refund_type,
            create_id,
            create_date
        )values (
            #{estateId},
            #{refundPrice},
            #{refundType},
            #{createId},
            #{createDate}
        )
    </insert>

</mapper>