<?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.BackApplyMapper">

    <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="companyId" column="company_id"/>
        <result property="fileName" column="file_name"/>
        <result property="fileUrl" column="file_url"/>
    </resultMap>
    <insert id="insertTask" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.base.api.domain.BackApplyInfo" useGeneratedKeys="true">
        insert into tm_task (
        <if test="taskType != null">task_type, </if>
        <if test="taskStatus != null">task_status, </if>
        <if test="code != null and code != ''">code, </if>
        <if test="createBy != null and createBy != ''">create_by, </if>
        <if test="updateBy != null and updateBy != ''">update_by, </if>
        <if test="updateTime != null">update_time, </if>
        <if test="remark != null and remark != ''">remark, </if>
        <if test="companyId != null">company_id, </if>
        create_time
        ) values (
        <if test="taskType != null">#{taskType}, </if>
        <if test="taskStatus != null">#{taskStatus}, </if>
        <if test="code != null and code != ''">#{code}, </if>
        <if test="createBy != null and createBy != ''">#{createBy}, </if>
        <if test="updateBy != null and updateBy != ''">#{updateBy}, </if>
        <if test="updateTime != null">#{updateTime}, </if>
        <if test="remark != null and remark != ''">#{remark}, </if>
        <if test="companyId != null">#{companyId}, </if>
        NOW()
        )
    </insert>

    <insert id="insertTaskAgreement">
        insert into tm_task_agreement
        (
        <if test="taskId != null">
            task_id,
        </if>
        <if test="agreementId != null">
            agreement_id,
        </if>
        <if test="createBy != null and createBy != ''">
            create_by,
        </if>
        <if test="updateBy != null and updateBy != ''">
            update_by,
        </if>
        <if test="updateTime != null">
            update_time,
        </if>
        <if test="remark != null and remark != ''">
            remark,
        </if>
        <if test="companyId != null">
            company_id,
        </if>
        create_time
        ) values (
        <if test="taskId != null">
            #{taskId},
        </if>
        <if test="agreementId != null">
            #{agreementId},
        </if>
        <if test="createBy != null and createBy != ''">
            #{createBy},
        </if>
        <if test="updateBy != null and updateBy != ''">
            #{updateBy},
        </if>
        <if test="updateTime != null">
            #{updateTime},
        </if>
        <if test="remark != null and remark != ''">
            #{remark},
        </if>
        <if test="companyId != null">
            #{companyId},
        </if>
        NOW()
        )
    </insert>

    <insert id="insertBackApply" keyColumn="id" keyProperty="id" parameterType="com.bonus.sgzb.base.api.domain.BackApplyInfo" useGeneratedKeys="true">
        insert into back_apply_info
        (
        <if test="code != null">
            code,
        </if>
        <if test="taskId != null">
            task_id,
        </if>
        <if test="backPerson != null and backPerson != ''">
            back_person,
        </if>
        <if test="backTime != null">
            back_time,
        </if>
        <if test="phone != null and phone != ''">
            phone,
        </if>
        <if test="directAuditBy != null">
            direct_audit_by,
        </if>
        <if test="directAuditTime != null">
            direct_audit_time,
        </if>
        <if test="directAuditRemark != null">
            direct_audit_remark,
        </if>
        <if test="createBy != null and createBy != ''">
            create_by,
        </if>
        <if test="updateBy != null and updateBy != ''">
            update_by,
        </if>
        <if test="updateTime != null">
            update_time,
        </if>
        <if test="remark != null and remark != ''">
            remark,
        </if>
        <if test="companyId != null">
            company_id,
        </if>
        <if test="status != null and status != ''">
            status,
        </if>
        create_time
        ) values (
        <if test="code != null">
            #{code},
        </if>
        <if test="taskId != null">
            #{taskId},
        </if>
        <if test="backPerson != null and backPerson != ''">
            #{backPerson},
        </if>
        <if test="backTime != null and backTime != ''">
            #{backTime},
        </if>
        <if test="phone != null and phone != ''">
            #{phone},
        </if>
        <if test="directAuditBy != null">
            #{directAuditBy},
        </if>
        <if test="directAuditTime != null and directAuditTime != ''">
            #{directAuditTime},
        </if>
        <if test="directAuditRemark != null">
            #{directAuditRemark},
        </if>
        <if test="createBy != null and createBy != ''">
            #{createBy},
        </if>
        <if test="updateBy != null and updateBy != ''">
            #{updateBy},
        </if>
        <if test="updateTime != null">
            #{updateTime},
        </if>
        <if test="remark != null and remark != ''">
            #{remark},
        </if>
        <if test="companyId != null">
            #{companyId},
        </if>
        <if test="status != null and status != ''">
            #{status},
        </if>
        NOW()
        )
    </insert>

    <insert id="upload" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
        insert into back_apply_details
        (
        <if test="id != null">
            parent_id,
        </if>
        <if test="typeId != null">
            type_id,
        </if>
        <if test="num != null">
            pre_num,
        </if>
        status,
        <if test="createBy != null and createBy != ''">
            create_by,
        </if>
        update_time,
        <if test="remark != null and remark != ''">
            remark,
        </if>
        <if test="companyId != null">
            company_id,
        </if>
        <if test="auditNum != null">
            audit_num,
        </if>
        create_time
        )
        values (
        <if test="id != null">
            #{id},
        </if>
        <if test="typeId != null">
            #{typeId},
        </if>
        <if test="num != null">
            #{num},
        </if>
        '0',
        <if test="createBy != null and createBy != ''">
            #{createBy},
        </if>
        NOW(),
        <if test="remark != null and remark != ''">
            #{remark},
        </if>
        <if test="companyId != null">
            #{companyId},
        </if>
        <if test="auditNum != null">
            #{auditNum},
        </if>
        NOW()
        )
    </insert>


    <insert id="uploadByCq" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
        insert into back_apply_details
        (
        <if test="id != null">
            parent_id,
        </if>
        <if test="typeId != null">
            type_id,
        </if>
        <if test="num != null">
            pre_num,audit_num,
        </if>
        status,
        <if test="createBy != null and createBy != ''">
            create_by,
        </if>
        update_time,
        <if test="remark != null and remark != ''">
            remark,
        </if>
        <if test="companyId != null">
            company_id,
        </if>
        create_time
        )
        values (
        <if test="id != null">
            #{id},
        </if>
        <if test="typeId != null">
            #{typeId},
        </if>
        <if test="num != null">
            #{num},#{num},
        </if>
        '0',
        <if test="createBy != null and createBy != ''">
            #{createBy},
        </if>
        NOW(),
        <if test="remark != null and remark != ''">
            #{remark},
        </if>
        <if test="companyId != null">
            #{companyId},
        </if>
        NOW()
        )
    </insert>

    <update id="setModify">
        update back_apply_details
        set pre_num =#{num},
            update_time = NOW()
        where id = #{detailsId}
    </update>

    <update id="audit">
        UPDATE tm_task tt
            LEFT JOIN back_apply_info bai ON bai.task_id = tt.task_id
            LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
            SET bad.audit_num=#{auditNum},bai.direct_audit_by=#{createBy},bai.status=#{status},bai.direct_audit_time=NOW()
        WHERE
        bai.id = #{id} and bad.type_id = #{typeId}
        <if test="companyId != null and companyId != ''">
         and bai.company_id = #{companyId}
        </if>
    </update>

    <update id="refuse">
        UPDATE tm_task tt
        LEFT JOIN back_apply_info bai ON bai.task_id = tt.task_id
        LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
        SET bad.audit_num=bad.pre_num,bai.direct_audit_by=#{createBy},bai.status=#{status},bai.direct_audit_time=NOW()
        WHERE
        bai.id = #{id}
        <if test="companyId != null and companyId != ''">
            and  bai.company_id = #{companyId}
        </if>
    </update>
    <update id="updateTmTask">
        UPDATE tm_task tt
            INNER JOIN back_apply_info bai ON bai.task_id = tt.task_id
            SET tt.task_status = #{taskStatus}
        WHERE
            bai.id = #{id}
    </update>
    <update id="updateTmTaskBytaskId">
        UPDATE tm_task tt
            SET tt.task_status = #{taskStatus}
        WHERE
            tt.task_id = #{taskId}
    </update>
    <update id="auditAll">
        UPDATE tm_task tt
        LEFT JOIN back_apply_info bai ON bai.task_id = tt.task_id
        LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
        SET bad.audit_num=#{auditNum},bai.direct_audit_by=#{createBy},bai.status=#{status},bai.direct_audit_time=NOW()
        WHERE
        bai.id = #{innerId} and bad.type_id = #{typeId}
        <if test="companyId != null and companyId != ''">
            and bai.company_id = #{companyId}
        </if>
    </update>
    <update id="updateTmTaskAll">
        UPDATE tm_task tt
            INNER JOIN back_apply_info bai ON bai.task_id = tt.task_id
            SET tt.task_status = #{taskStatus}
        WHERE
            bai.id = #{id}
    </update>

    <delete id="del">
        DELETE
        FROM tm_task_agreement
        WHERE task_id IN (SELECT task_id FROM back_apply_info WHERE id = #{id});

        DELETE
        FROM tm_task
        WHERE task_id IN (SELECT task_id FROM back_apply_info WHERE id = #{id});


        DELETE
        FROM back_apply_details
        WHERE parent_id IN (SELECT id FROM back_apply_info WHERE id = #{id});

        DELETE
        FROM back_apply_info
        WHERE id = #{id};
    </delete>
    <delete id="delApply">
        DELETE
        FROM tm_task_agreement
        WHERE task_id IN (SELECT task_id FROM back_apply_info WHERE id = #{id});

        DELETE
        FROM tm_task
        WHERE task_id IN (SELECT task_id FROM back_apply_info WHERE id = #{id});

        DELETE
        FROM back_apply_details
        WHERE parent_id IN
        (select id FROM back_apply_info
        WHERE task_id in (SELECT task_id FROM back_apply_info WHERE id = #{id}));

        DELETE bai1
        FROM back_apply_info bai1
        JOIN (
        SELECT task_id
        FROM back_apply_info
        WHERE id = #{id}) bai2 ON bai1.task_id = bai2.task_id;

    </delete>

    <select id="getBackApplyList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
            bai.id,
            bai.`code`,
            bai.back_person as backPerson,
            bai.`status` as applyStatus,
            bai.phone,
            bpl.lot_id as lotId,
            bpl.lot_name as lotName,
            bui.unit_id as unitId,
            bui.unit_name as unitName,
            bai.back_time as backTime,
            bagi.agreement_code as agreementCode,
            tt.task_status as taskStatus,
            tta.agreement_id as agreementId,
            GROUP_CONCAT(DISTINCT bad.type_id) as typeId,
            GROUP_CONCAT(mt2.type_name) AS typeName,
            GROUP_CONCAT(bad.status) AS status
        FROM
            back_apply_info bai
                LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
                LEFT JOIN tm_task tt on tt.task_id=bai.task_id
                LEFT JOIN tm_task_agreement tta on tta.task_id=tt.task_id
                LEFT JOIN bm_agreement_info bagi on bagi.agreement_id=tta.agreement_id
                LEFT JOIN bm_project_lot bpl on bpl.lot_id=bagi.project_id
                LEFT JOIN bm_unit_info bui on bui.unit_id=bagi.unit_id
                LEFT JOIN sys_user us on us.user_id=bai.create_by
                LEFT JOIN ma_type mt1 ON mt1.type_id=bad.type_id
                LEFT JOIN ma_type mt2 ON mt2.type_id=mt1.parent_id
        WHERE
        tt.create_by = #{createBy}
        <if test="keyWord != null  and keyWord != ''">
            and (bai.`code` like concat('%', #{keyWord}, '%') or
            bai.back_person like concat('%', #{keyWord}, '%') or
            bai.phone like concat('%', #{keyWord}, '%'))
        </if>
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="lotId != null  and lotId != ''">
            and bpl.lot_id = #{lotId}
        </if>
        <if test="proId != null  and proId != ''">
            and bpl.lot_id = #{proId}
        </if>
        <if test="taskStatus != null  and taskStatus != ''">
            and tt.task_status = #{taskStatus}
        </if>
        <if test="agreementCode != null  and agreementCode != ''">
            and bagi.agreement_code like concat('%', #{agreementCode}, '%')
        </if>
        <if test="time != null  and time != ''">
            and bai.back_time =#{time}
        </if>
        GROUP BY bai.task_id, us.user_name, bai.phone, bpl.lot_name, bui.unit_name, bagi.plan_start_time
        ORDER BY bai.create_time desc
    </select>

    <select id="getMaterialList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT subquery1.type_id as typeId,
        subquery1.typeName typeCode,
        subquery1.typeNames as typeName,
        subquery1.out_num - COALESCE(subquery2.audit_num, 0) AS num
        FROM (
        -- 第一个查询作为子查询
        SELECT mt.type_id,
        mt.type_name as typeName,
        mt2.type_name AS typeNames,
        SUM(lod.out_num) as out_num
        FROM lease_apply_info lai
        LEFT JOIN tm_task_agreement tta on lai.task_id = tta.task_id
        LEFT JOIN lease_out_details lod on lod.parent_id = lai.id
        LEFT JOIN tm_task tt on tt.task_id = tta.task_id
        LEFT JOIN ma_type mt on lod.type_id=mt.type_id
        LEFT JOIN ma_type mt1 ON mt1.type_id = lod.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        LEFT JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE tta.agreement_id = #{agreementId}
        and tt.task_type = '29'
        and mt.`level`='4'

        <if test="keyWord != null  and keyWord != ''">
            and (mt.type_name like concat('%', #{keyWord}, '%') or
            mt2.type_name like concat('%', #{keyWord}, '%'))
        </if>
        GROUP BY mt.type_id
        ) AS subquery1
        LEFT JOIN
        (
        -- 第二个查询作为子查询
        SELECT mt.type_id,
        mt.type_name,
        mt2.type_name AS typeNames,
        SUM(bad.audit_num) as audit_num
        FROM back_apply_info bai
        LEFT JOIN tm_task tt on tt.task_id=bai.task_id
        LEFT JOIN back_apply_details bad on bad.parent_id = bai.id
        LEFT JOIN tm_task_agreement tta on tta.task_id = tt.task_id
        LEFT JOIN ma_type mt on mt.type_id = bad.type_id
        LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        LEFT JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE tta.agreement_id = #{agreementId}
        and tt.task_type = '36'
        and mt.`level`='4'
        <if test="keyWord != null  and keyWord != ''">
            and (mt.type_name like concat('%', #{keyWord}, '%') or
            mt2.type_name like concat('%', #{keyWord}, '%'))
        </if>
        GROUP BY mt.type_id
        ) AS subquery2
        ON subquery1.type_id = subquery2.type_id
        WHERE
        subquery1.out_num - COALESCE(subquery2.audit_num, 0)>0
    </select>
    <select id="selectTaskNumByMonth" resultType="java.lang.String">
        SELECT SUBSTRING(`code`, - 4) as code
        FROM tm_task
        WHERE DATE_FORMAT(create_time, '%y%m') = DATE_FORMAT(#{date}, '%y%m')
          AND task_type = #{taskType}
        ORDER BY create_time DESC LIMIT 1
    </select>

    <select id="getView" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
            bai.back_person AS backPerson,
            bai.phone AS phone,
            bai.back_time as backTime,
            lot.lot_name AS lotName,
            unit.unit_name AS unitName,
            mt.type_name typeCode,
            mt.manage_type as manageType,
            mt2.type_name AS typeName,
            bagi.agreement_code as agreementCode,
            bad.pre_num AS preNum,
            bad.type_id as typeId,
            bad.audit_num AS num
        FROM
            back_apply_details bad
                LEFT JOIN back_apply_info bai ON bai.id = bad.parent_id
                LEFT JOIN tm_task tt on bai.task_id = tt.task_id
                LEFT JOIN tm_task_agreement tta ON tta.task_id = bai.task_id
                LEFT JOIN bm_agreement_info bagi ON bagi.agreement_id = tta.agreement_id
                LEFT JOIN bm_project_lot lot ON lot.lot_id = bagi.project_id
                LEFT JOIN bm_unit_info unit ON unit.unit_id = bagi.unit_id
                LEFT JOIN ma_type mt ON mt.type_id = bad.type_id
                LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id
                LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
                LEFT JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
                LEFT JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
            bai.task_id = #{taskId}
        <if test="split != null and split.length > 0">
            and bad.id in
           <foreach item="split" index="index" collection="split" open="(" separator="," close=")">
                #{split}
           </foreach>
        </if>
        <if test="companyId != null  and companyId != ''">
            and bai.company_id = #{companyId}
        </if>
        <if test="keyWord != null  and keyWord != ''">
            and (mt2.type_name like concat('%', #{keyWord}, '%') or
            mt.type_name like concat('%', #{keyWord}, '%'))
        </if>
    </select>
<!--    <select id="getUseTypeTree" resultType="com.bonus.sgzb.material.domain.TypeTreeNode">-->
<!--        WITH RECURSIVE type_hierarchy AS (&#45;&#45; 基础情况：从第四级开始-->
<!--            SELECT-->
<!--                mt.type_id,-->
<!--                mt.type_name,-->
<!--                mt.parent_id,-->
<!--                mt.unit_name as unitNames,-->
<!--                mt.company_id,-->
<!--                SUM( CASE WHEN sai.agreement_id = #{agreementId} AND sai.STATUS = '0' THEN sai.num ELSE 0 END ) AS num,-->
<!--                mt.LEVEL-->
<!--            FROM-->
<!--                ma_type mt-->
<!--                    LEFT JOIN slt_agreement_info sai ON mt.type_id = sai.type_id-->
<!--            WHERE-->
<!--                EXISTS ( SELECT 1 FROM slt_agreement_info sai2 WHERE sai2.type_id = mt.type_id AND sai2.agreement_id = #{agreementId} AND sai2.STATUS = '0' and sai.lease_type = 0)-->
<!--            GROUP BY-->
<!--                mt.type_id UNION ALL&#45;&#45; 递归情况：向上查找父级-->
<!--            SELECT-->
<!--                mt.type_id,-->
<!--                mt.type_name,-->
<!--                mt.parent_id,-->
<!--                mt.unit_name,-->
<!--                mt.company_id,-->
<!--                0 AS num,&#45;&#45; 父级没有直接关联的 num，可以设为0或NULL-->
<!--                mt.LEVEL-->
<!--            FROM-->
<!--                ma_type mt-->
<!--                    INNER JOIN type_hierarchy th ON mt.type_id = th.parent_id-->
<!--        ) &#45;&#45; 查询结果，包括从第四级到第一级的所有元素-->
<!--        SELECT-->
<!--            *-->
<!--        FROM-->
<!--            type_hierarchy-->
<!--        GROUP BY-->
<!--            type_id-->
<!--        ORDER BY-->
<!--            LEVEL ASC;-->
<!--    </select>-->

    <select id="getUseTypeTreeL4" resultType="com.bonus.sgzb.material.domain.TypeTreeNode">
        SELECT
            mt.type_id,
            mt.type_name,
            mt.parent_id,
            mt.unit_name as unitNames,
            mt.company_id,
            SUM( CASE WHEN sai.agreement_id = #{agreementId} AND sai.STATUS = '0' THEN sai.num ELSE 0 END ) AS num,
            mt.LEVEL
        FROM
            ma_type mt
                LEFT JOIN slt_agreement_info sai ON mt.type_id = sai.type_id
        WHERE
            EXISTS ( SELECT 1 FROM slt_agreement_info sai2 WHERE sai2.type_id = mt.type_id AND sai2.agreement_id = #{agreementId}
                                                             AND sai2.STATUS = '0' and sai.lease_type = 0 and sai2.num > 0)
        GROUP BY
            mt.type_id
    </select>

    <select id="getUseTypeTreeL3" resultType="com.bonus.sgzb.material.domain.TypeTreeNode">
        SELECT
            mt3.type_id,
            mt3.type_name,
            mt3.parent_id,
            mt3.unit_name,
            mt3.company_id,
            0 AS num,
            mt3.LEVEL
        FROM
            ma_type mt3 where type_id in
            <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
                #{item}
            </foreach>
    </select>

    <select id="getUseTypeTreeL21" resultType="com.bonus.sgzb.material.domain.TypeTreeNode">
        SELECT
            mt2.type_id,
            mt2.type_name,
            mt2.parent_id,
            mt2.unit_name,
            mt2.company_id,
            0 AS num,
            mt2.LEVEL
        FROM
            ma_type mt2 where type_id in
            <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
                #{item}
            </foreach>

        union

        SELECT
            mt1.type_id,
            mt1.type_name,
            mt1.parent_id,
            mt1.unit_name,
            mt1.company_id,
            0 AS num,
            mt1.LEVEL
        FROM
            ma_type mt1 left join (
                SELECT
                    mt2.type_id,
                    mt2.type_name,
                    mt2.parent_id,
                    mt2.unit_name,
                    mt2.company_id,
                    0 AS num,
                    mt2.LEVEL
                FROM
                    ma_type mt2) mt2 on mt2.parent_id=mt1.type_id  where mt2.type_id in
                    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
                        #{item}
                    </foreach>
    </select>

    <select id="exportList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
            bai.`code`,
            bai.back_person as backPerson,
            bai.phone,
            bpl.lot_name as lotName,
            bui.unit_name as unitName,
            bai.back_time as backTime,
            bagi.agreement_code as agreementCode,
            tt.task_status as taskStatus,
            mt2.type_name AS typeName,
            mt1.type_name as typeCode
        FROM
            back_apply_details bad
                LEFT JOIN back_apply_info bai on bad.parent_id=bai.id
                LEFT JOIN tm_task tt on tt.task_id=bai.task_id
                LEFT JOIN tm_task_agreement tta on tta.task_id=tt.task_id
                LEFT JOIN bm_agreement_info bagi on bagi.agreement_id=tta.agreement_id
                LEFT JOIN bm_project_lot bpl on bpl.lot_id=bagi.project_id
                LEFT JOIN bm_unit_info bui on bui.unit_id=bagi.unit_id
                LEFT JOIN sys_user us on us.user_id=bai.create_by
                LEFT JOIN ma_type mt1 ON mt1.type_id=bad.type_id
                LEFT JOIN ma_type mt2 ON mt2.type_id=mt1.parent_id
        where
        1=1
        <if test="keyWord != null  and keyWord != ''">
            and (bai.`code` like concat('%', #{keyWord}, '%') or
            bai.back_person like concat('%', #{keyWord}, '%') or
            bai.phone like concat('%', #{keyWord}, '%'))
        </if>
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="lotId != null  and lotId != ''">
            and bpl.lot_id = #{lotId}
        </if>
        <if test="taskStatus != null  and taskStatus != ''">
            and tt.task_status = #{taskStatus}
        </if>
        <if test="agreementCode != null  and agreementCode != ''">
            and bagi.agreement_code like concat('%', #{agreementCode}, '%')
        </if>
        <if test="startTime != null  and startTime != '' and endTime != null  and endTime != ''">
            and bai.back_time >=#{startTime}
            and #{endTime} >=bai.back_time
        </if>
        ORDER BY bai.task_id desc
    </select>
    <select id="getBackApplyListByAdmin" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
        bai.id,
        bai.`code`,
        bai.`status` as applyStatus,
        bai.back_person as backPerson,
        bai.phone,
        bai.task_id as taskId,
        bpl.lot_id as lotId,
        bpl.lot_name as lotName,
        bui.unit_id as unitId,
        bai.remark as remark,
        bui.unit_name as unitName,
        bai.back_time as backTime,
        bagi.agreement_code as agreementCode,
        tt.task_status as taskStatus,
        tta.agreement_id as agreementId,
        GROUP_CONCAT(bai.company_id) as companyId,
        GROUP_CONCAT(bad.id) as badId,
        GROUP_CONCAT(DISTINCT mt2.type_id) as typeId,
        GROUP_CONCAT(mt2.type_name) AS typeName,
        GROUP_CONCAT(bad.status) AS status
        FROM
        back_apply_info bai
        LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
        LEFT JOIN tm_task tt on tt.task_id=bai.task_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=tt.task_id
        LEFT JOIN bm_agreement_info bagi on bagi.agreement_id=tta.agreement_id
        LEFT JOIN bm_project_lot bpl on bpl.lot_id=bagi.project_id
        LEFT JOIN bm_unit_info bui on bui.unit_id=bagi.unit_id
        LEFT JOIN sys_user us on us.user_id=bai.create_by
        LEFT JOIN ma_type mt1 ON mt1.type_id=bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id=mt1.parent_id
        WHERE
        1=1
        <if test="keyWord != null  and keyWord != ''">
            and (bai.`code` like concat('%', #{keyWord}, '%') or
            bai.back_person like concat('%', #{keyWord}, '%') or
            bai.phone like concat('%', #{keyWord}, '%'))
        </if>
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="lotId != null  and lotId != ''">
            and bpl.lot_id = #{lotId}
        </if>
        <if test="proId != null  and proId != ''">
            and bpl.lot_id = #{proId}
        </if>
        <if test="taskStatus != null  and taskStatus != ''">
            and tt.task_status = #{taskStatus}
        </if>
        <if test="agreementCode != null  and agreementCode != ''">
            and bagi.agreement_code like concat('%', #{agreementCode}, '%')
        </if>
        <if test="time != null  and time != ''">
            and bai.back_time =#{time}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[AND DATE_FORMAT( bai.back_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        GROUP BY bai.id, us.user_name, bai.phone, bpl.lot_name, bui.unit_name, bagi.plan_start_time
        <if test="typeId != null and typeId != ''">
            HAVING FIND_IN_SET(#{typeId}, typeId) > 0
        </if>
        ORDER BY bai.create_time desc
    </select>

    <select id="getTaskIdById" resultType="java.lang.Integer">
        SELECT bai1.status FROM  back_apply_info bai1
        INNER JOIN
        (SELECT * FROM back_apply_info bai2 WHERE bai2.id = #{id}) dd
        on bai1.task_id = dd.task_id
    </select>
    <select id="getViewByApply" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
        bai.back_person AS backPerson,
        bai.phone AS phone,
        bai.back_time as backTime,
        lot.lot_name AS lotName,
        lot.lot_id as proId,
        bad.company_id as companyId,
        bad.create_by as createBy,
        lot.pro_id as projectId,
        lot.lot_name as proName,
        unit.unit_name AS unitName,
        unit.unit_id AS unitId,
        mt.type_name typeCode,
        mt.type_id as typeId,
        mt.unit_name as unitNames,
        tt.task_id as taskId,
        case WHEN mt.manage_type = '0' then '编号' else '计数' end manageTypeName,
        mt2.type_name AS typeName,
        bagi.agreement_code as agreementCode,
        bagi.agreement_id as agreementId,
        bai.`status` as `status`,
        bad.id as detailsId,
        bad.pre_num AS num,
        bad.audit_num AS auditNum,
        IFNULL( aa.num, 0 ) AS useNum
        FROM
        back_apply_details bad
        LEFT JOIN back_apply_info bai ON bai.id = bad.parent_id
        LEFT JOIN tm_task tt on bai.task_id = tt.task_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = bai.task_id
        LEFT JOIN bm_agreement_info bagi ON bagi.agreement_id = tta.agreement_id
        LEFT JOIN bm_project_lot lot ON lot.lot_id = bagi.project_id
        LEFT JOIN bm_unit_info unit ON unit.unit_id = bagi.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = bad.type_id
        LEFT JOIN (
        SELECT
        SUM( sai.num ) AS num, sai.type_id FROM slt_agreement_info sai
        WHERE
        sai.`status` = '0'
        <if test = "agreementId!=null and agreementId!=''" >
           AND sai.agreement_id = #{agreementId}
        </if>
        GROUP BY sai.type_id
        ) aa ON aa.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        LEFT JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
        bai.task_id in (SELECT task_id from back_apply_info WHERE id = #{id})
        <if test="keyWord != null  and keyWord != ''">
            and (mt2.type_name like concat('%', #{keyWord}, '%') or
            mt.type_name like concat('%', #{keyWord}, '%'))
        </if>
    </select>
    <select id="getBackApplyListByAdmin1" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        SELECT
        bai.id,
        bai.`code`,
        bai.`status` as applyStatus,
        bai.back_person as backPerson,
        bai.phone,
        bai.task_id as taskId,
        bpl.lot_id as lotId,
        bai.remark as remark,
        bpl.lot_name as lotName,
        bui.unit_id as unitId,
        bui.unit_name as unitName,
        bai.back_time as backTime,
        bagi.agreement_code as agreementCode,
        tt.task_status as taskStatus,
        tta.agreement_id as agreementId,
        GROUP_CONCAT(bai.company_id) as companyId,
        GROUP_CONCAT(DISTINCT mt2.type_id) as typeId,
        GROUP_CONCAT(mt2.type_name) AS typeName,
        GROUP_CONCAT(bad.status) AS status
        FROM
        back_apply_info bai
        LEFT JOIN back_apply_details bad on bad.parent_id=bai.id
        LEFT JOIN tm_task tt on tt.task_id=bai.task_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=tt.task_id
        LEFT JOIN bm_agreement_info bagi on bagi.agreement_id=tta.agreement_id
        LEFT JOIN bm_project_lot bpl on bpl.lot_id=bagi.project_id
        LEFT JOIN bm_unit_info bui on bui.unit_id=bagi.unit_id
        LEFT JOIN sys_user us on us.user_id=bai.create_by
        LEFT JOIN ma_type mt1 ON mt1.type_id=bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id=mt1.parent_id
        WHERE
        1=1
        <if test="keyWord != null  and keyWord != ''">
            and (bai.`code` like concat('%', #{keyWord}, '%') or
            bai.back_person like concat('%', #{keyWord}, '%') or
            bai.phone like concat('%', #{keyWord}, '%'))
        </if>
        <if test="unitId != null  and unitId != ''">
            and bui.unit_id = #{unitId}
        </if>
        <if test="lotId != null  and lotId != ''">
            and bpl.lot_id = #{lotId}
        </if>
        <if test="proId != null  and proId != ''">
            and bpl.lot_id = #{proId}
        </if>
        <if test="taskStatus != null  and taskStatus != ''">
            and tt.task_status = #{taskStatus}
        </if>
        <if test="agreementCode != null  and agreementCode != ''">
            and bagi.agreement_code like concat('%', #{agreementCode}, '%')
        </if>
        <if test="time != null  and time != ''">
            and bai.back_time =#{time}
        </if>
        GROUP BY bai.task_id, us.user_name, bai.phone, bpl.lot_name, bui.unit_name, bagi.plan_start_time
        <if test="typeId != null and typeId != ''">
            HAVING FIND_IN_SET(#{typeId}, typeId) > 0
        </if>
        ORDER BY bai.create_time desc
    </select>
    <select id="selectIdByTaskId" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
        select * from back_apply_info where  task_id = #{taskId}
    </select>
    <select id="getUseNumByTypeId" resultType="java.lang.Integer">
        select count(*) as useNum from ma_machine where type_id = #{typeId} and ma_status in (16,84)
    </select>
    <select id="getManageType" resultType="java.lang.Integer">
        select mt.manage_type AS manageType FROM ma_type mt WHERE mt.type_id = #{typeId}
    </select>
    <select id="getLeaseOutNum" resultType="java.lang.Integer">
        SELECT IFNULL(sum(lod.out_num),0) FROM lease_out_details lod WHERE lod.type_id = #{typeId}
    </select>
    <select id="getbackCheckNum" resultType="java.lang.Integer">
        SELECT IFNULL(sum(bcd.back_num),0) FROM back_check_details bcd WHERE bcd.type_id = #{typeId}
    </select>
    <select id="getTaskIdByIdAll" resultType="java.lang.Integer">
        SELECT bai1.status FROM  back_apply_info bai1
        INNER JOIN
        (SELECT * FROM back_apply_info bai2 WHERE bai2.id = #{id}) dd
        on bai1.task_id = dd.task_id
    </select>

</mapper>