<?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.bonus.material.push.mapper.IwsCostPushMapper">

    <select id="findAgreement" resultType="com.bonus.material.push.domain.IwsCostPushBean">
        SELECT
            bp.pro_id AS projectId, bp.pro_name AS projectName, bp.pro_code AS projectCode,
            bu.unit_name AS unitName,
            bma.agreement_id AS agreementId,bma.`agreement_code` AS agreementCode, bma.is_slt AS isSettlement,
            bma.sign_time AS signTime, bma.is_push AS isPush,
            saa.audit_time AS settlementTime
        FROM bm_agreement_info bma
        LEFT JOIN bm_project bp ON bp.pro_id = bma.project_id
        LEFT JOIN bm_unit bu ON bu.unit_id = bma.unit_id
        LEFT JOIN slt_agreement_apply saa ON saa.agreement_id = bma.agreement_id AND bma.is_slt = 1
        <where>
            <if test="agreementCode != null and agreementCode != ''">
                AND bma.agreement_code LIKE CONCAT('%',#{agreementCode},'%')
            </if>
            <if test="projectId != null and projectId != ''">
                AND bma.project_id = #{projectId}
            </if>
            <if test="unitId != null and unitId != ''">
                AND bma.unit_id = #{unitId}
            </if>
            <if test="isPush != null">
                AND bma.is_push = #{isPush}
            </if>
            <if test="isSettlement != null and isSettlement != ''">
                AND bma.is_slt = #{isSettlement}
            </if>
        </where>
        GROUP BY bma.agreement_id
    </select>

    <update id="updateAgreementIsPush">
        UPDATE bm_agreement_info SET is_push = #{isPush} WHERE agreement_id = #{agreementId}
    </update>

    <select id="getCostPushCheckList" resultType="com.bonus.material.push.domain.IwsCostPushBean">
        SELECT
            pmc.id AS id, pmc.AGREEMENT_ID AS agreementId, IF(ISNULL(pmc.check_status), 0, pmc.check_status) AS checkStatus,
            pmc.LEASE_MONEY AS leaseMoney, pmc.LOST_MONEY AS lostMoney, pmc.REPAIR_MONEY AS repairMoney, pmc.SCRAP_MONEY AS scrapMoney,
            bma.agreement_code AS agreementCode, bma.is_slt AS isSettlement,
            bp.pro_name AS projectName,
            bu.unit_name AS unitName,
            ROUND(
                SUM(ifnull( pmc.LEASE_MONEY, 0 )+ ifnull( pmc.LOST_MONEY, 0 )+ ifnull( pmc.REPAIR_MONEY, 0 )+ ifnull( pmc.SCRAP_MONEY, 0 )), 2
            ) AS money
        FROM
            project_month_costs pmc
        LEFT JOIN bm_agreement_info bma ON pmc.AGREEMENT_ID = bma.agreement_id
        LEFT JOIN bm_project bp ON bp.pro_id = bma.project_id
        LEFT JOIN bm_unit bu ON bu.unit_id = bma.unit_id
        WHERE
            ( pmc.LEASE_MONEY > 0 OR pmc.LOST_MONEY > 0 OR pmc.REPAIR_MONEY > 0 OR pmc.SCRAP_MONEY > 0 )
            <if test="checkStatus != null">
                AND pmc.check_status = #{checkStatus}
            </if>
            <if test="agreementCode != null and agreementCode != ''">
                AND bma.agreement_code LIKE CONCAT('%',#{agreementCode},'%')
            </if>
            <if test="projectId != null and projectId != ''">
                AND bma.project_id = #{projectId}
            </if>
            <if test="unitId != null and unitId != ''">
                AND bma.unit_id = #{unitId}
            </if>
            <if test="isSettlement != null and isSettlement != ''">
                AND bma.is_slt = #{isSettlement}
            </if>

        GROUP BY
            pmc.AGREEMENT_ID
    </select>

    <insert id="insertCalcMonthRecord" parameterType="com.bonus.material.push.domain.IwsCostPushBean" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO calc_project_month (MONTH)
        VALUES (#{month})
    </insert>

    <select id="getPaidSltBaseInfo" resultType="com.bonus.material.push.domain.IwsCostPushBean">
        SELECT
            mt1.type_name as typeName,
            mt.type_name as modelName,
            mt.unit_name AS unitName,
            sai.lease_price as leasePrice,
            bma.agreement_id AS agreementId,sai.type_id as typeId,
            IFNULL(mt.lease_price ,0) as nowLeasePrice,
            sai.buy_price as buyPrice,sai.num AS leaseNum,sai.start_time AS leaseDate,sai.end_time AS backDate,
            IF(sai.`status` = 0,sai.num,0) as returnNum,
            (datediff(sai.end_time,sai.start_time)+1) * (sai.num) * (sai.lease_price) AS leaseMoney,
            bma.`agreement_code` AS agreementCode,bma.project_id as projectId,
            bma.is_slt AS isSettlement,
            bma.sign_time AS settlementTime,
            bma.is_push AS isPush
        FROM bm_agreement_info bma
        LEFT JOIN slt_agreement_info sai ON bma.agreement_id = sai.agreement_id
        LEFT JOIN ma_type mt ON mt.type_id = sai.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        <where>
            bma.agreement_id = #{agreementId}
        </where>
    </select>

    <insert id="insertProjectMonthCosts">
        INSERT INTO project_month_costs(
            AGREEMENT_ID,
            LEASE_MONEY,
            LOST_MONEY,
            REPAIR_MONEY,
            SCRAP_MONEY,
            CHECK_STATUS,
            MONTH
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.agreementId},
                #{item.leaseMoney},
                #{item.lostMoney},
                #{item.repairMoney},
                #{item.scrapMoney},
                #{item.checkStatus},
                #{item.month}
            )
        </foreach>
    </insert>
</mapper>