<?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.SysExpenseBillDao">
    <select id="list" parameterType="com.api.model.chargeManagement.SearchExpenseBill" resultType="com.api.vo.chargeManagement.VoExpenseBill">
        SELECT T.building_unit_estate_id as id,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,
        sum(T.cost_price) as cost_price_sum,(case sum(T.refund_price) is null when true then 0 else sum(T.refund_price) end) as refund_price_sum,
        sum(T.total_price + T.overdue_fine_new) as total_price_sum,
        sum((case T.status = 3 when true then (T.paid_price + T.overdue_fine_new) else T.paid_price end)) as paid_price_sum,
        sum((case T.status = 3 when true then T.payment_price else (T.payment_price + T.overdue_fine_new) end)) as payment_price_sum
        from
        (
        SELECT sdp.*,(
        case
            sdp.status = 3 when true
        then
            (case sdp.overdue_fine is null when true then 0 else sdp.overdue_fine end)
        else
            (
            case
                now() > sdp.payment_term when true
            then
                round(sdp.payment_price*pow((1+sdp.rate/100),TIMESTAMPDIFF(MONTH,sdp.payment_term,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))) - sdp.payment_price,2)
            else
                0
            end
            )
        end
        ) as overdue_fine_new
        from sys_daily_payment as sdp
        ) as T
        ,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        <where>
            T.building_unit_estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and T.is_delete = 1
            <if test="roomNumber != null and roomNumber != ''">
                and cbue.room_number like concat('%',#{roomNumber},'%')
            </if>
            <if test="unitNo != null">
                and cbu.no like concat('%',#{unitNo},'%')
            </if>
            <if test="estateNo != null">
                and cb.no like concat('%',#{estateNo},'%')
            </if>
        </where>
        GROUP BY T.building_unit_estate_id
    </select>

    <select id="findStatusSNBySV" parameterType="java.lang.Integer" resultType="java.lang.String">
        select show_name from sys_data_dictionary
        where type_name = 'sys_daily_payment_status' and show_value = #{status}
    </select>
    <select id="detailList" parameterType="com.api.model.chargeManagement.SearchExpenseBillDetail" resultType="com.api.vo.chargeManagement.VoExpenseBillDetail">
        SELECT T.id,sctd.name,T.cost_price,(case T.refund_price is null when true then 0 else T.refund_price end) as refund_price,
        (T.total_price + T.overdue_fine_new) as total_price,
        (case T.status = 3 when true then (T.paid_price + T.overdue_fine_new) else T.paid_price end) as paid_price,
        (case T.status = 3 when true then T.payment_price else (T.payment_price + T.overdue_fine_new) end) as payment_price,
        T.status,T.rate,T.payment_term,T.overdue_fine_new as overdue_fine,(case T.create_id = -1 when true then '系统自动' else su.actual_name end) as create_name,T.create_date
        from
        (SELECT sdp.*,(
        case
            sdp.status = 3 when true
        then
            (case sdp.overdue_fine is null when true then 0 else sdp.overdue_fine end)
        else
            (
            case
                now() > sdp.payment_term when true
            then
                round(sdp.payment_price*pow((1+sdp.rate/100),TIMESTAMPDIFF(MONTH,sdp.payment_term,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))) - sdp.payment_price,2)
            else
                0
            end
            )
        end
        ) as overdue_fine_new
        from sys_daily_payment as sdp
        where sdp.building_unit_estate_id = #{estateId}
        ) as T
        left join sys_user as su on T.create_id = su.id
        left join sys_charges_template_detail as sctd on T.charges_template_detail_id = sctd.id
        <where>
            T.is_delete = 1
            <if test="years != null">
                and year(T.begin_date) = #{years}
            </if>
            <if test="months != null">
                and month(T.begin_date) = #{months}
            </if>
            <if test="chargesTemplateDetailId != null">
                and T.charges_template_detail_id = #{chargesTemplateDetailId}
            </if>
        </where>
        order by T.create_date
    </select>
    <select id="findPayPeopleNameByBillId" parameterType="java.lang.Integer" resultType="java.lang.String">
        select GROUP_CONCAT(DISTINCT sdpo.name)
        from sys_daily_payment_order as sdpo,sys_daily_payment_order_list as sdpol
        where sdpol.daily_payment_order_id = sdpo.id and sdpol.daily_payment_id = #{id}
        group by sdpol.daily_payment_id
    </select>

    <select id="findAssociationResidentsByEstateId" parameterType="java.lang.Integer" resultType="java.lang.String">
        select GROUP_CONCAT(ur.name)
        from cpm_resident_estate as cre,user_resident as ur
        where cre.resident_id = ur.id and cre.building_unit_estate_id = #{id}
        GROUP BY cre.building_unit_estate_id
    </select>

    <update id="refund" parameterType="com.api.model.chargeManagement.DailyPayment">
        update sys_daily_payment
        set refund_price = #{refundPrice}
        where id = #{id}
    </update>

</mapper>