<?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.app.dao.butler.AppDailyPaymentDao">
    <select id="list" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDailyPaymentVo">
        select year(end_date) as years,count(*) as payment_num from sys_daily_payment where building_unit_estate_id = #{estateId} and status != 3 group by year(end_date) order by year(end_date) DESC
    </select>

    <select id="listType" parameterType="com.api.model.app.AppDailyPaymentDetail" resultType="com.api.vo.app.AppDailyPaymentTypeVo">
        select DISTINCT sctd.id,sctd.name from sys_daily_payment as sdp,sys_charges_template_detail as sctd
        where sdp.charges_template_detail_id = sctd.id and sdp.building_unit_estate_id = #{estateId} and year(sdp.end_date) = #{years} and sdp.status != 3
    </select>

    <select id="listDetailed" parameterType="com.api.model.app.AppDailyPaymentDetail" resultType="com.api.vo.app.AppDailyPaymentDetailedVo">
        select T.group_id,sum(T.payment_price) as payment_price,0 as overdue_fine from (
            select sdp.*,CEILING((MONTH(sdp.end_date)/7)) as group_id
            from sys_daily_payment as sdp
            where sdp.building_unit_estate_id = #{estateId} and sdp.charges_template_detail_id =#{chargesTemplateDetailId} and year(sdp.end_date) = #{years} and sdp.status != 3
        ) as T GROUP BY T.group_id order by T.group_id
    </select>

    <select id="listDetails" parameterType="com.api.model.app.AppDailyPaymentDetail" resultType="com.api.vo.app.AppDailyPaymentDetailsVo">
        select T.*,concat(year(T.end_date),"年",MONTH(T.end_date),"月") as month from (
            select sdp.*,CEILING((MONTH(sdp.end_date)/7)) as group_id,concat(sdp.unit_price,sdd.show_name) as unit_price_type
            from sys_daily_payment as sdp,sys_data_dictionary as sdd
            where sdp.building_unit_estate_id = #{estateId} and sdp.charges_template_detail_id =#{chargesTemplateDetailId} and year(sdp.end_date) = #{years} and sdp.status != 3
            and sdd.type_name = "sys_charges_template_detail_type" and sdd.show_value = sdp.type
        ) as T where T.group_id = #{groupId} order by MONTH(T.end_date)
    </select>

    <select id="findPaymentPriceById" parameterType="com.api.model.app.AppDailyPaymentOrder" resultType="java.math.BigDecimal">
        select sum(payment_price) from sys_daily_payment where id in
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </select>

    <select id="findDailPaymentPriceById" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select payment_price from sys_daily_payment where id = #{id}
    </select>

    <insert id="insertOrder" parameterType="com.api.model.app.AppDailyPaymentOrder">
        insert into sys_daily_payment_order(
            code,
            name,
            tel,
            pay_type,
            pay_price,
            create_id,
            create_date,
            status
        ) values (
            #{code},
            #{name},
            #{tel},
            #{payType},
            #{payPrice},
            #{createId},
            #{createDate},
            #{status}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <insert id="insertOrderList" parameterType="com.api.model.chargeManagement.DailyPaymentOrderList">
        insert into sys_daily_payment_order_list(
            daily_payment_order_id,
            daily_payment_id,
            daily_payment_price
        )values (
            #{dailyPaymentOrderId},
            #{dailyPaymentId},
            #{dailyPaymentPrice}
        )
    </insert>

    <update id="updatePaidPriceAndPaymentPrice" parameterType="java.lang.Integer">
        update sys_daily_payment
        <set>
            paid_price = cost_price,
            payment_price = 0,
            status = 3
        </set>
        where id = #{id}
    </update>


    <update id="updatePaidPriceAndPaymentPrice2" parameterType="com.api.model.chargeManagement.DailyPaymentOrderList">
        update sys_daily_payment
        <set>
            paid_price = paid_price + #{dailyPaymentPrice},
            payment_price = payment_price - #{dailyPaymentPrice},
            status = 2
        </set>
        where id = #{dailyPaymentId}
    </update>


    <select id="findEstateIsPayment" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDailPaymentChooseEstate">
        select cbue.id,concat(cb.no,"幢",cbu.no,"单元",cbue.room_number) as room_name,sum((case sdp.status is null when true then 0 else case sdp.status != 3 when true then 1 else 0 end end)) as status
        from cpm_resident_estate as cre,user_resident as ur,cpm_building as cb,cpm_building_unit as cbu,cpm_building_unit_estate as cbue
        LEFT JOIN sys_daily_payment as sdp
        on sdp.building_unit_estate_id = cbue.id
        where cre.resident_id = ur.id and cbue.id = cre.building_unit_estate_id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and ur.id = #{id}
        GROUP BY cbue.id
    </select>

    <select id="findEstateIdByResidentId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select building_unit_estate_id from cpm_resident_estate where resident_id = #{id}
    </select>

    <select id="paymentRecord" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppPaymentRecordVo">
        select a.* from (
            select sdpol.id,sctd.name as charges_template_detail_name,concat(cb.no,"幢",cbu.no,"单元",cbue.room_number) as room_name,
            YEAR(sdpo.create_date) as years,sdpol.daily_payment_price as paid_price,sdpo.create_date,sdpo.pay_type,sdpo.code,sdpo.name as create_name
            from sys_daily_payment as sdp,sys_charges_template_detail as sctd,sys_daily_payment_order_list as sdpol
            ,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_daily_payment_order as sdpo
            where sdp.charges_template_detail_id = sctd.id and sdp.id = sdpol.daily_payment_id and sdpo.id = sdpol.daily_payment_order_id
            and sdp.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id
            and sdp.building_unit_estate_id = #{estateId}
            union all
            select sapo.id,'预缴充值' as charges_template_detail_name,concat(cb.no,"幢",cbu.no,"单元",cbue.room_number) as room_name,
            YEAR(sapo.create_date) as years,sapo.pay_price as paid_price,sapo.create_date,sapo.pay_type,sapo.code,sapo.name as create_name
            from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_advance_payment_order as sapo
            where sapo.estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id
            and sapo.estate_id = #{estateId}
        ) as a
        order by a.create_date DESC
    </select>

    <select id="findDailPaymentOrderByCode" parameterType="java.lang.String" resultType="com.api.model.app.AppDailyPaymentOrder">
        select * from sys_daily_payment_order where code = #{outTradeNo}
    </select>
    <select id="findDailyPaymentIdsByOrderCode" parameterType="java.lang.String" resultType="com.api.vo.app.AppDailyPaymentDetailsVo">
        select sdp.*
        from sys_daily_payment as sdp,sys_daily_payment_order as sdpo,sys_daily_payment_order_list as sdpol
        where sdp.id = sdpol.daily_payment_id and sdpo.id = sdpol.daily_payment_order_id and sdpo.code = #{outTradeNo}
    </select>
    <select id="findUnPaymentOrder" resultType="com.api.model.app.AppDailyPaymentOrder">
        select * from sys_daily_payment_order where status = 0 and NOW() <![CDATA[ >= ]]> DATE_ADD(create_date,INTERVAL 30 MINUTE)
    </select>

    <update id="updateDPOrderStatusByCode" parameterType="com.api.model.app.AppDailyPaymentOrder">
        update sys_daily_payment_order
        <set>
            <if test="tradeNo != null and tradeNo != ''">
                trade_no = #{tradeNo},
            </if>
            status = #{status}
        </set>
        where code = #{code}
    </update>

    <select id="findDailyPaymentByIds" parameterType="com.api.model.app.AppDailyPaymentOrder" resultType="com.api.vo.app.AppDailyPaymentDetailsVo">
        select * from sys_daily_payment where id in
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </select>

    <update id="updatePaidPAndPaymentPAndOverdueFine" parameterType="com.api.vo.app.AppDailyPaymentDetailsVo">
        update sys_daily_payment
        <set>
            paid_price = cost_price,
            payment_price = 0,
            status = 3,
            overdue_fine = #{overdueFine}
        </set>
        where id = #{id}
    </update>

    <select id="findAdvancePaymentPriceByEstateId" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select advance_payment_price from cpm_building_unit_estate where id = #{estateId}
    </select>

    <select id="findArrivePaymentTerm" parameterType="java.util.Date" resultType="com.api.model.chargeManagement.DailyPayment">
        select * from sys_daily_payment where status in (1,2) and year(payment_term) = year(#{date}) and MONTH(payment_term) = MONTH(#{date}) and DAY(payment_term) = DAY(#{date})
    </select>

    <update id="deductingAdvancePaymentByEstateId" parameterType="com.api.model.alipay.EstateIdAndAdvancePaymentPrice">
        update cpm_building_unit_estate
        set advance_payment_price = advance_payment_price - #{advancePaymentPrice}
        where id = #{estateId}
    </update>

    <select id="findThreeDaysLaterPayment" parameterType="java.util.Date" resultType="com.api.model.chargeManagement.DailyPayment">
        select * from sys_daily_payment where status in (1,2) and year(payment_term) = year(#{threeDaysLater}) and MONTH(payment_term) = MONTH(#{threeDaysLater}) and DAY(payment_term) = DAY(#{threeDaysLater})
    </select>


</mapper>