<?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.record.mapper.DerateRecordQueryMapper">

    <select id="getList" resultType="com.bonus.material.settlement.domain.SltAgreementReduceRecord">
        SELECT
        sra.id as id,
        sra.create_time as createTime,
        su.user_name as createBy,
        bu.unit_name as unitName,
        bp.pro_name as projectName,
        SUM(srd.lease_price * srd.num) as leasePrice,
        SUM(srd.lease_money) as leaseMoney,
        swr.workflow_status as status,
        a.leaseMoneyAll as leaseMoneyAll
        FROM
        slt_reduce_apply sra
        LEFT JOIN slt_reduce_details srd on sra.id=srd.apply_id
        LEFT JOIN sys_user su on su.user_id=sra.creator
        LEFT JOIN bm_agreement_info bai on bai.agreement_id=sra.agreement_id
        LEFT JOIN bm_project bp ON bp.pro_id = bai.project_id
        LEFT JOIN bm_unit bu ON bu.unit_id = bai.unit_id
        LEFT JOIN sys_workflow_record swr ON swr.task_id = sra.id
        LEFT JOIN
        (
        SELECT
        sra.agreement_id as agreementId,
        SUM(srd.lease_money) as leaseMoneyAll
        FROM
        slt_reduce_apply sra
        LEFT JOIN slt_reduce_details srd on sra.id=srd.apply_id
        GROUP BY sra.agreement_id
        ) a on a.agreementId=sra.agreement_id
        where 1=1

        <if test="startTime != null and endTime != ''">
            AND DATE_FORMAT(sra.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="startDate != null and endDate != ''">
            AND DATE_FORMAT(sra.create_time, '%Y-%m-%d' ) BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            su.user_name LIKE concat('%', #{keyWord}, '%') or
            bu.unit_name LIKE concat('%', #{keyWord}, '%') or
            bp.pro_name LIKE concat('%', #{keyWord}, '%')
            )
        </if>
        <if test="status != null and status!=''">
            AND swr.workflow_status = #{status}
        </if>
        <if test="taskStatus !=null">
            AND sra.status = #{taskStatus}
        </if>
        GROUP BY sra.id
        ORDER BY sra.create_time DESC
    </select>
    <select id="getInfo" resultType="com.bonus.material.settlement.domain.SltAgreementReduce">
        SELECT sra.id,
               sra.agreement_id as agreementId,
               sra.r_start_time as reduceStartTime,
               sra.r_end_time   as reduceEndTime,
               sra.remark,
               bp.pro_id as proId,
               bp.pro_name as projectName,
               bu.unit_id as unitId,
               bu.unit_name as unitName
        FROM slt_reduce_apply sra
                 LEFT JOIN bm_agreement_info bai ON sra.agreement_id = bai.agreement_id
                 LEFT JOIN bm_project bp ON bp.pro_id = bai.project_id
                 LEFT JOIN bm_unit bu ON bu.unit_id = bai.unit_id
        where sra.id = #{id}
    </select>
    <select id="getBmFileInfos" resultType="com.bonus.common.biz.domain.BmFileInfo">
        select id,
               task_type as taskType,
               task_id as taskId,
               model_id as modelId,
               name,
               url,
               file_type as fileType,
               create_by as createBy,
               create_time as createTime
        from bm_file_info
        where model_id = #{id}
    </select>
    <select id="getDetailList" resultType="com.bonus.material.settlement.domain.SltAgreementReduce">
        SELECT
            srd.reduce_num as reduceNum,
            mt2.type_name as maName,
            mt.type_name as maModel,
            srd.type_id as typeId,
            srd.num,
            srd.start_time as startTime,
            srd.end_time as endTime,
            srd.lease_price as leasePrice,
            srd.days as reduceDays,
            srd.lease_money as reduceLeaseMoney,
            srd.r_start_time as reduceStartTime,
            srd.r_end_time as reduceEndTime
        FROM
            slt_reduce_details srd
                LEFT JOIN ma_type mt on mt.type_id=srd.type_id
                LEFT JOIN ma_type mt2 on mt2.type_id=mt.parent_id
        WHERE
            srd.apply_id=#{id}
    </select>


    <delete id="delData">
        delete
        from slt_reduce_apply
        where id = #{id};
        delete
        from slt_reduce_details
        where apply_id = #{id}
    </delete>

    <select id="getAuditList" resultType="com.bonus.material.settlement.domain.SltAgreementReduce">
        SELECT
        sra.id as id,swr.task_id as taskId,
        sra.create_time as createTime,
        su.user_name as createBy,
        bu.unit_name as unitName,
        bp.pro_name as projectName,
        swr.workflow_status as status,
        swrs.node_id AS nodeId,
        swrs.next_node_id AS nextNodeId,
        swrs.record_id AS recordId,
        swn.node_sign_type as nodeSignType
        FROM
        slt_reduce_apply sra
        LEFT JOIN slt_reduce_details srd on sra.id=srd.apply_id
        LEFT JOIN sys_user su on su.user_id=sra.creator
        LEFT JOIN bm_agreement_info bai on bai.agreement_id=sra.agreement_id
        LEFT JOIN bm_project bp ON bp.pro_id = bai.project_id
        LEFT JOIN bm_unit bu ON bu.unit_id = bai.unit_id
        LEFT JOIN sys_workflow_record swr on sra.id = swr.task_id
        LEFT JOIN sys_workflow_type swt on swr.workflow_id = swt.id
        LEFT JOIN sys_workflow_node swn on swt.id = swn.type_id
        LEFT JOIN sys_workflow_config swc on swn.id = swc.node_id
        left join sys_workflow_record_history  swrs on swr.id = swrs.record_id
        where 1=1
        <if test="userId != null and userId != ''">
            AND swc.config_value = #{userId}
        </if>
        <if test="startTime != null and endTime != ''">
            AND DATE_FORMAT(sra.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="startDate != null and endDate != ''">
            AND DATE_FORMAT(sra.create_time, '%Y-%m-%d' ) BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            su.user_name LIKE concat('%', #{keyWord}, '%') or
            bu.unit_name LIKE concat('%', #{keyWord}, '%') or
            bp.pro_name LIKE concat('%', #{keyWord}, '%')
            )
        </if>
        <if test="status != null and status!=''">
            AND sra.status = #{status}
        </if>
        <if test="taskStatus !=null">
            AND sra.status = #{taskStatus}
        </if>
        GROUP BY sra.id
        ORDER BY sra.create_time DESC
    </select>
    <select id="getAuditPrice" resultType="com.bonus.material.settlement.domain.SltAgreementReduce">
        SELECT
            sra.id as id,
            sra.create_time as createTime,
            SUM(srd.lease_price * srd.num) as leasePrice,
            SUM(srd.lease_money) as leaseMoney,
            a.leaseMoneyAll as leaseMoneyAll
        FROM
            slt_reduce_apply sra
                LEFT JOIN slt_reduce_details srd on sra.id=srd.apply_id
                LEFT JOIN bm_agreement_info bai on bai.agreement_id=sra.agreement_id
                LEFT JOIN
            (
                SELECT
                    sra.agreement_id as agreementId,
                    SUM(srd.lease_money) as leaseMoneyAll
                FROM
                    slt_reduce_apply sra
                        LEFT JOIN slt_reduce_details srd on sra.id=srd.apply_id
                GROUP BY sra.agreement_id
            ) a on a.agreementId=sra.agreement_id
        where sra.id = #{id}
        GROUP BY sra.id
        ORDER BY sra.create_time DESC

    </select>
</mapper>
