<?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.sgzb.material.mapper.SltAgreementInfoMapper">

    <resultMap type="com.bonus.sgzb.material.domain.AgreementInfo" id="BmAgreementInfoResult">
        <result property="agreementId" column="agreement_id"/>
        <result property="agreementCode" column="agreement_code"/>
        <result property="signTime" column="sign_time"/>
        <result property="unitId" column="unit_id"/>
        <result property="unitName" column="unit_name"/>
        <result property="projectId" column="project_id"/>
        <result property="createBy" column="create_by"/>
        <result property="leaseDay" column="lease_day"/>
        <result property="planStartTime" column="plan_start_time"/>
        <result property="contractCode" column="contract_code"/>
        <result property="authPerson" column="auth_person"/>
        <result property="phone" column="phone"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <result property="protocol" column="protocol"/>
        <result property="companyId" column="company_id"/>
        <result property="fileName" column="file_name"/>
        <result property="fileUrl" column="file_url"/>
    </resultMap>
    <insert id="insApply" useGeneratedKeys="true" keyProperty="id">
        insert into slt_agreement_apply (code,creator,create_time,status,company_id,cost)
        values (#{code},#{creator},now(),'0',#{companyId},#{cost});
    </insert>
    <insert id="insRelation">
        insert into slt_agreement_relation (apply_id,agreement_id,add_cost,sub_cost,remark,cost,status,lease_one_cost,lease_three_cost,repair_cost,scrap_cost,lose_cost)
        values (#{applyId},#{agreementId},#{addCost},#{subCost},#{remark},#{cost},'0',#{leaseCostOne},#{leaseCostThree},#{repairCost},#{scrapCost},#{loseCost});
    </insert>
    <insert id="insDetails">
        insert into slt_agreement_details
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="applyId != null">apply_id,</if>
            <if test="typeId != null">type_id,</if>
            <if test="maId != null">ma_id,</if>
            <if test="sltType != null">slt_type,</if>
            <if test="num != null">num,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="leasePrice != null">price,</if>
            <if test="costs != null">money,</if>
            <if test="partType != null">is_charge,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="applyId != null">#{applyId},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="maId != null">#{maId},</if>
            <if test="sltType != null">#{sltType},</if>
            <if test="num != null">#{num},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="leasePrice != null">#{leasePrice},</if>
            <if test="costs != null">#{costs},</if>
            <if test="partType != null">#{partType},</if>
        </trim>
    </insert>
    <update id="updateInfoStatus">
        update slt_agreement_info
        set is_slt = '1',
            slt_time = now()
        where agreement_id = #{agreementId}
    </update>
    <update id="updateRelation">
        update slt_agreement_relation
        set status = #{status}
        where agreement_id = #{agreementId}
    </update>
    <update id="updateApply">
        update slt_agreement_apply
        set status = #{status},
            auditor = #{auditor},
            audit_time = now(),
            remark = #{remark}
        where id = #{id}
    </update>
    <update id="updateMaStatus">
        update ma_machine
        set ma_status = '103'
        where ma_id = #{maId}
    </update>
    <update id="updateTrimDay">
        update slt_agreement_info
        <set>
            <if test="trimDay != null">
                trim_day = #{trimDay},
            </if>
            <if test="endTime != null and endTime != ''">
                end_time = #{endTime},
            </if>
        </set>
        where id = #{id}
    </update>
    <update id="updateInfoOneStatus">
        UPDATE slt_agreement_info sai
            LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
            SET sai.is_slt = '1'
        WHERE
            sai.agreement_id = #{agreementId}
          AND sai.lease_type = 0
          AND lai.cost_bearing_party = #{costBearingParty}
    </update>
    <update id="updateOutSourceCosts">
        update repair_cost set out_source_costs = #{outSourceCosts} where id = #{id}
    </update>
    <update id="updateBmAgreementInfo">
        update bm_agreement_info set is_slt = 1 where agreement_id = #{agreementId}
    </update>
    <update id="updateRecodeIsSlt">
        update repair_apply_record set is_slt = 1 where id = #{id}
    </update>
    <select id="getSltAgreementInfo" resultType="com.bonus.sgzb.material.domain.AgreementInfo">
        SELECT bai.agreement_id, bai.agreement_code , contract_code,file_url ,file_name,sign_time,
        bui.unit_id,bui.unit_name , bp.lot_id as projectId , bp.lot_name as projectName,
        plan_start_time,lease_day,auth_person,phone,bai.remark,bai.protocol,sar.cost as cost,
        bai.is_slt as isSlt
        FROM bm_agreement_info bai
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        left join slt_agreement_relation sar on bai.agreement_id = sar.agreement_id
        where bai.status = '1'
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="projectId != null  and projectId != ''">
            and bp.lot_id = #{projectId}
        </if>
        <if test="sltStatus != null">
            and bai.is_slt = #{sltStatus}
        </if>
        ORDER BY bai.agreement_id desc
    </select>

    <select id="getLeaseList" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select sai.id,
               sai.agreement_id as agreementId,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
               sai.company_id as companyId,
               sai.type_id as typeId,
               sai.ma_id as maId,
               mt1.type_name as typeName,
               mt.type_name as modelName,
               mt.unit_name as nuitName,
               sai.lease_price as leasePrice,
               sai.num as num,
               DATE(sai.start_time) as startTime,
               DATE(sai.end_time) as endTime,
               DATEDIFF(IF(sai.end_time is null,CURDATE(),sai.end_time), sai.start_time) + 1 as leaseDays
        from slt_agreement_info sai
        LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
        LEFT JOIN bm_agreement_info bai on sai.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        left join ma_type mt on sai.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        where sai.agreement_id = #{agreementId}
    </select>
    <select id="getLoseList" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select sai.id,
               sai.agreement_id as agreementId,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
               sai.company_id as companyId,
               sai.type_id as typeId,
               sai.ma_id as maId,
               mt1.type_name as typeName,
               mt.type_name as modelName,
               mt.unit_name as nuitName,
               sai.buy_price as buyPrice,
               sai.num as num,
               mm.ma_code as maCode,
               sai.start_time as startTime,
               sai.end_time as endTime,
               DATEDIFF(sai.end_time, sai.start_time) + 1 as leaseDays
        from slt_agreement_info sai
                 LEFT JOIN bm_agreement_info bai on sai.agreement_id = bai.agreement_id
                 LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
                 LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
                 left join ma_type mt on sai.type_id = mt.type_id
                 left join ma_type mt1 on mt.parent_id = mt1.type_id
                 left join ma_machine mm on mm.ma_id = sai.ma_id
        where sai.agreement_id = #{agreementId} and sai.lease_type = 0 and sai.end_time is null
    </select>
    <select id="getTaskList" resultType="com.bonus.sgzb.material.domain.TmTask">
        select tt.task_id as taskId
        from tm_task_agreement tta
        left join tm_task tt on tta.task_id = tt.task_id
        where tta.agreement_id = #{bean.agreementId} and tt.task_type = #{taskType}
    </select>
    <select id="getRepairDetailsList" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        SELECT
        rar.id,
        rar.is_slt AS isSlt,
        tta.agreement_id AS agreementId,
        bui.unit_name AS unitName,
        bp.lot_name AS projectName,
        rar.type_id AS typeId,
        rar.ma_id AS maId,
        mt1.type_name AS typeName,
        mt.type_name AS modelName,
        mt.unit_name AS nuitName,
        rar.repair_num AS num,
        rar.part_num AS partNum,
        rar.part_price AS partPrice,
        rar.part_num * rar.part_price AS partAllCosts,
        mpt.pa_name partModelName,
        mpt2.pa_name partName
        FROM
        repair_apply_record rar
        LEFT JOIN tm_task_agreement tta ON rar.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON rar.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        LEFT JOIN ma_part_type mpt ON rar.part_id = mpt.pa_id
        LEFT JOIN ma_part_type mpt2 ON mpt.parent_id = mpt2.pa_id
        where rar.repair_type in (1,2) and rar.part_num != 0 and rar.status = 1
        <if test="taskList != null and taskList.size() > 0">
            and rar.task_id in
            <foreach item="task" collection="taskList" open="(" separator="," close=")">
                #{task.taskId}
            </foreach>
        </if>
    </select>
    <select id="getScrapDetailsList" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select tta.agreement_id as agreementId,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
                sad.type_id as typeId,
                sad.ma_id as maId,
                mt1.type_name as typeName,
                mt.type_name as modelName,
                mt.unit_name as nuitName,
                sum(sad.scrap_num) as num,
                mm.ma_code as maCode,
                mt.buy_price as buyPrice,
                case sad.scrap_type when '0' then '自然' when '1' then '人为' else '' end as scrapType,
                sad.company_id as companyId
        from scrap_apply_details sad
        left join tm_task_agreement tta on sad.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai on tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        left join ma_type mt on sad.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        left join ma_machine mm on mm.ma_id = sad.ma_id
        where sad.status = 1
        <if test="taskList != null and taskList.size() > 0">
            and sad.task_id in
            <foreach item="task" collection="taskList" open="(" separator="," close=")">
                #{task.taskId}
            </foreach>
        </if>
        group by sad.type_id,sad.ma_id,sad.scrap_type
    </select>
    <select id="selectNumByDate" resultType="java.lang.Integer">
        select count(*) from slt_agreement_apply where DATE_FORMAT(create_time,'%y%m') = DATE_FORMAT(#{date},'%y%m')
    </select>
    <select id="getSltExam" resultType="com.bonus.sgzb.base.api.domain.SltAgreementApply">
        SELECT
        sar.agreement_id AS agreementId,
        bui.unit_name AS unitName,
        bp.lot_name AS projectName,
        sar.cost,
        sar.STATUS
        FROM
        slt_agreement_relation sar
        LEFT JOIN bm_agreement_info bai ON sar.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        where 1=1
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="projectId != null  and projectId != ''">
            and bp.lot_id = #{projectId}
        </if>
        <if test="sltStatus != null">
            and sar.status = #{sltStatus}
        </if>
    </select>
    <select id="getRelations" resultType="com.bonus.sgzb.base.api.domain.SltAgreementRelation">
        select sar.id,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
               sar.apply_id as applyId,
               sar.agreement_id as agreementId,
               sar.add_cost as addCost,
               sar.sub_cost as subCost,
               sar.remark as remark,
               sar.cost as cost,
               sar.lease_cost as leaseCost,
               sar.repair_cost as repairCost,
               sar.scrap_cost as scrapCost,
               sar.lose_cost as loseCost
        from slt_agreement_relation sar
        LEFT JOIN bm_agreement_info bai on sar.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        where sar.apply_id = #{id}
    </select>
    <select id="getCost" resultType="java.lang.String">
        select cost
        from slt_agreement_apply
        where id = #{id}
    </select>
    <select id="getLeaseListOne" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select sai.id,
               sai.agreement_id as agreementId,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
               sai.company_id as companyId,
               sai.type_id as typeId,
               sai.ma_id as maId,
               mt1.type_name as typeName,
               mt.type_name as modelName,
               mt.unit_name as nuitName,
               sai.lease_price as leasePrice,
               sai.num as num,
               sai.is_slt as isSlt,
               sai.trim_day as trimDay,
               DATE(sai.start_time) as startTime,
               DATE(sai.end_time) as endTime,
               DATEDIFF(IF(sai.end_time is null,CURDATE(),sai.end_time), sai.start_time) + 1 as leaseDays
        from slt_agreement_info sai
            LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
            LEFT JOIN bm_agreement_info bai on sai.agreement_id = bai.agreement_id
            LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
            LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
            left join ma_type mt on sai.type_id = mt.type_id
            left join ma_type mt1 on mt.parent_id = mt1.type_id
        where sai.agreement_id = #{agreementId} and sai.lease_type = 0 and lai.cost_bearing_party = '01'
    </select>
    <select id="getLeaseListThree" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select sai.id,
               sai.agreement_id as agreementId,
               bui.unit_name as unitName,
               bp.lot_name as projectName,
               sai.company_id as companyId,
               sai.type_id as typeId,
               sai.ma_id as maId,
               mt1.type_name as typeName,
               mt.type_name as modelName,
               mt.unit_name as nuitName,
               sai.trim_day as trimDay,
               sai.lease_price as leasePrice,
               sai.num as num,
               sai.is_slt as isSlt,
            DATE(sai.start_time) as startTime,
            DATE(sai.end_time) as endTime,
            DATEDIFF(IF(sai.end_time is null,CURDATE(),sai.end_time), sai.start_time) + 1 as leaseDays
        from slt_agreement_info sai
            LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
            LEFT JOIN bm_agreement_info bai on sai.agreement_id = bai.agreement_id
            LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
            LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
            left join ma_type mt on sai.type_id = mt.type_id
            left join ma_type mt1 on mt.parent_id = mt1.type_id
        where sai.agreement_id = #{agreementId} and sai.lease_type = 0 and lai.cost_bearing_party = '03'
    </select>
    <select id="getLeaseListOneMonth" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        SELECT
        sai.id,
        sai.agreement_id AS agreementId,
        bui.unit_name AS unitName,
        bp.lot_name AS projectName,
        sai.company_id AS companyId,
        sai.type_id AS typeId,
        sai.ma_id AS maId,
        mt1.type_name AS typeName,
        mt.type_name AS modelName,
        mt.unit_name AS nuitName,
        sai.lease_price AS leasePrice,
        sai.num AS num,
        sai.is_slt AS isSlt,
        lai.cost_bearing_party AS costBearingParty,
        sai.trim_day AS trimDay,
        DATE(#{bean.startTime}) AS startTime,
        DATE(#{bean.endTime}) AS endTime,
        DATEDIFF(#{bean.endTime}, #{bean.startTime}) + 1 AS leaseDays
        FROM
        slt_agreement_info sai
        LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
        LEFT JOIN bm_agreement_info bai ON sai.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON sai.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        WHERE
            sai.agreement_id = #{bean.agreementId}
            AND sai.lease_type = 0
            <if test="bean.costBearingParty != null and bean.costBearingParty != ''">
                AND lai.cost_bearing_party = #{bean.costBearingParty}
            </if>
            AND sai.id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
    </select>
    <select id="getLeaseListThreeMonth" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        SELECT
            sai.id,
            sai.agreement_id AS agreementId,
            bui.unit_name AS unitName,
            bp.lot_name AS projectName,
            sai.company_id AS companyId,
            sai.type_id AS typeId,
            sai.ma_id AS maId,
            mt1.type_name AS typeName,
            mt.type_name AS modelName,
            mt.unit_name AS nuitName,
            sai.lease_price AS leasePrice,
            sai.num AS num,
            sai.is_slt AS isSlt,
            sai.trim_day AS trimDay,
            DATE(sai.start_time) AS startTime,
            DATE(COALESCE(sai.end_time, CURDATE())) AS endTime,
            DATEDIFF(COALESCE(sai.end_time, CURDATE()), sai.start_time) + 1 AS leaseDays
        FROM
            slt_agreement_info sai
            LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
            LEFT JOIN bm_agreement_info bai ON sai.agreement_id = bai.agreement_id
            LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
            LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
            LEFT JOIN ma_type mt ON sai.type_id = mt.type_id
            LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        WHERE
            sai.agreement_id = #{agreementId}
          AND sai.lease_type = 0
          AND lai.cost_bearing_party = '03'
        <if test="startTime != null  and startTime != ''">
            AND DATE(sai.start_time) BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="endTime != null  and endTime != ''">
            AND (DATE(COALESCE(sai.end_time, CURDATE())) &lt;= #{endTime})
        </if>
    </select>
    <select id="getLeaseListMonth" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        SELECT
        DATE_FORMAT( DATE_ADD( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m' ) MONTH,

        (CASE
        WHEN DATE_FORMAT( DATE_SUB( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-21' ) >= DATE( sai.start_time ) THEN
        DATE_FORMAT( DATE_SUB( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-21' ) ELSE DATE( sai.start_time )
        END) AS beginTime,

        (CASE
        WHEN DATE_FORMAT( DATE_ADD( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-20' ) >= DATE( COALESCE ( sai.end_time, CURDATE() ) ) THEN
        DATE(
        COALESCE (
        sai.end_time,
        CURDATE())) ELSE DATE_FORMAT( DATE_ADD( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-20' )
        END) AS offTime,
        GROUP_CONCAT( sai.id ) AS ids,
        sai.agreement_id AS agreementId,
        bui.unit_name AS unitName,
        bp.lot_name AS projectName,
        DATE( sai.start_time ) AS startTime,
        lai.cost_bearing_party AS costBearingParty,
        DATE( COALESCE ( sai.end_time, CURDATE() ) ) AS endTime
        FROM
        slt_agreement_info sai
        LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
        LEFT JOIN bm_agreement_info bai ON sai.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON sai.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        WHERE
        sai.agreement_id = #{agreementId}
        AND sai.lease_type = 0
        AND (-- 当前月21号到月末
        ( DAYOFMONTH( sai.start_time ) >= 21 AND DAYOFMONTH( sai.start_time ) &lt;= 31 )
        OR -- 次月1号到20号
        (
        DAYOFMONTH( sai.start_time ) >= 1
        AND DAYOFMONTH( sai.start_time ) &lt;= 20
        AND MONTH ( sai.start_time ) + 1 = MONTH (
        COALESCE (
        sai.end_time,
        CURDATE())))
        )
        GROUP BY
        (
        CASE

        WHEN DATE_FORMAT( DATE_SUB( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-21' ) >= DATE( sai.start_time ) THEN
        DATE_FORMAT( DATE_SUB( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-21' ) ELSE DATE( sai.start_time )
        END
        ),
        (
        CASE

        WHEN DATE_FORMAT( DATE_ADD( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-20' ) >= DATE( COALESCE ( sai.end_time, CURDATE() ) ) THEN
        DATE(
        COALESCE (
        sai.end_time,
        CURDATE())) ELSE DATE_FORMAT( DATE_ADD( sai.start_time, INTERVAL 1 MONTH ), '%Y-%m-20' )
        END
        ),
        lai.cost_bearing_party
        ORDER BY
        DATE_FORMAT(
        DATE_ADD( DATE( sai.start_time ), INTERVAL 1 - DAYOFMONTH( sai.start_time ) DAY ),
        '%Y-%m'
        )
    </select>
    <select id="getLeaseListMonthNotNull" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        SELECT
        ( CASE WHEN #{startTime} >= DATE( sai.start_time ) THEN #{startTime} ELSE DATE( sai.start_time ) END ) AS beginTime,
        (
        CASE

        WHEN #{endTime} >= DATE( COALESCE ( sai.end_time, CURDATE() ) ) THEN
        DATE(
        COALESCE (
        sai.end_time,
        CURDATE())) ELSE #{endTime}
        END
        ) AS offTime,
        GROUP_CONCAT(sai.id) AS ids,
        sai.agreement_id AS agreementId,
        bui.unit_name AS unitName,
        bui.unit_id AS unitId,
        bp.lot_name AS projectName,
        bp.lot_id AS lotId,
        DATE( sai.start_time ) AS startTime,
        lai.cost_bearing_party AS costBearingParty,
        DATE( COALESCE ( sai.end_time, CURDATE() ) ) AS endTime
        FROM
        slt_agreement_info sai
        LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
        LEFT JOIN bm_agreement_info bai ON sai.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON sai.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        WHERE
        sai.agreement_id = #{agreementId}
        AND sai.lease_type = 0
        <if test="costBearingParty != null and costBearingParty != ''">
            AND lai.cost_bearing_party = #{costBearingParty}
        </if>
        AND sai.start_time &lt;= #{endTime}
        AND (sai.end_time >= #{startTime} OR sai.end_time IS NULL)
        GROUP BY
        beginTime,offTime,lai.cost_bearing_party
        ORDER BY
        DATE_FORMAT( DATE_ADD( DATE( sai.start_time ), INTERVAL 1 - DAYOFMONTH( sai.start_time ) DAY ), '%Y-%m' )
    </select>
    <select id="getPreScrapDetailsList" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
        select tta.agreement_id as agreementId,
        bui.unit_name as unitName,
        bp.lot_name as projectName,
        sad.type_id as typeId,
        sad.ma_id as maId,
        mt1.type_name as typeName,
        mt.type_name as modelName,
        mt.unit_name as nuitName,
        sum(sad.scrap_num) as num,
        mm.ma_code as maCode,
        mt.buy_price as buyPrice,
        case sad.scrap_type when '0' then '自然' when '1' then '人为' else '' end as scrapType,
        sad.company_id as companyId
        from scrap_apply_details sad
        left join tm_task_agreement tta on sad.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai on tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        left join ma_type mt on sad.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        left join ma_machine mm on mm.ma_id = sad.ma_id
        where sad.status in (0,2)
        <if test="taskList != null and taskList.size() > 0">
            and sad.task_id in
            <foreach item="task" collection="taskList" open="(" separator="," close=")">
                #{task.taskId}
            </foreach>
        </if>
        group by sad.type_id,sad.ma_id,sad.scrap_type
    </select>
</mapper>