<?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.basic.mapper.ComplexQueryMapper">

    <select id="getProjUsingRecordList" resultType="com.bonus.material.basic.domain.ProjUsingRecord">
        SELECT
        subquery1.agreementCode as agreementCode,
        subquery1.unitName as unitName,
        subquery1.proName as proName,
        subquery1.contractPart as contractPart,
        subquery1.impUnitName as impUnitName,
        subquery1.typeName as typeName,
        subquery1.typeModelName as typeModelName,
        subquery1.unit as unit,
        IFNULL(subquery1.leaseNum, 0) as leaseNum,
        IFNULL(subquery2.backNum, 0) as backNum,
        CASE
        WHEN IFNULL(subquery1.leaseNum, 0) - IFNULL(subquery2.backNum, 0) > 0
        THEN IFNULL(subquery1.leaseNum, 0) - IFNULL(subquery2.backNum, 0)
        ELSE 0.000
        END as usNum,
        CASE
        WHEN IFNULL(subquery1.leaseNum, 0) - IFNULL(subquery2.backNum, 0) > 0 THEN
        ROUND(IFNULL(subquery1.buy_price, 0) *
        (IFNULL(subquery1.leaseNum, 0) - IFNULL(subquery2.backNum, 0)), 2)
        ELSE
        0.00
        END as usPrice,
        ROUND(IFNULL(subquery1.leaseNum, 0) * IFNULL(subquery1.buy_price, 0), 2) as totalPrice,
        subquery1.isSlt as isSlt,
        subquery1.isSltName as isSltName
        FROM (SELECT bai.agreement_id,
        mt.type_id,
        mt.buy_price,
        bai.agreement_code AS agreementCode,
        bui.unit_name AS unitName,
        bpl.pro_name AS proName,
        bpl.contract_part as contractPart,
        sd.dept_name as impUnitName,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit,
        SUM(IFNULL(lod.out_num, 0)) AS leaseNum,
        sai.is_slt as isSlt,
        CASE
        sai.is_slt
        WHEN '0' then '未结算'
        WHEN '1' then '已结算'
        ELSE ''
        END as isSltName
        FROM lease_out_details lod
        LEFT JOIN lease_apply_info lai ON lai.id = lod.parent_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = lai.task_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
        LEFT JOIN (
        SELECT agreement_id, is_slt
        FROM slt_agreement_info
        GROUP BY agreement_id
        ) sai ON bai.agreement_id = sai.agreement_id
        LEFT JOIN bm_project bpl ON bpl.pro_id = bai.project_id
        LEFT JOIN sys_dept sd on sd.dept_id = bpl.imp_unit
        LEFT JOIN bm_unit bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = lod.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = lod.ma_id
        where 1=1
        <if test="keyWord != null and keyWord != ''">
            and (bai.agreement_code like concat('%',#{keyWord},'%') or
            bui.unit_name like concat('%',#{keyWord},'%') or
            bpl.pro_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mt.unit_name like concat('%',#{keyWord},'%') or
            bpl.contract_part like concat('%', #{keyWord}, '%') or
            sd.dept_name like concat('%', #{keyWord}, '%')
            )
        </if>
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND bai.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        <if test="isSlt != null and isSlt != ''">
            and sai.is_slt = #{isSlt}
        </if>
        GROUP BY bai.agreement_id,
        mt.type_id ORDER BY bai.agreement_code) AS subquery1
        LEFT JOIN (SELECT bai.agreement_id,
        mt.type_id,
        mt.rent_price,
        bai.agreement_code AS agreementCode,
        bui.unit_name AS unitName,
        bpl.pro_name AS proName,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit,
        bpl.contract_part as contractPart,
        sd.dept_name as impUnitName,
        SUM(IFNULL(bcd.back_num, 0)) backNum,
        sai.is_slt as isSlt,
        CASE
        sai.is_slt
        WHEN '0' then '未结算'
        WHEN '1' then '已结算'
        ELSE ''
        END as isSltName
        FROM back_check_details bcd
        LEFT JOIN back_apply_info baif ON baif.id = bcd.parent_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = baif.task_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
        LEFT JOIN (
        SELECT agreement_id, is_slt
        FROM slt_agreement_info
        GROUP BY agreement_id
        ) sai ON bai.agreement_id = sai.agreement_id
        LEFT JOIN bm_project bpl ON bpl.pro_id = bai.project_id
        LEFT JOIN sys_dept sd on sd.dept_id = bpl.imp_unit
        LEFT JOIN bm_unit bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = bcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
        where 1=1 and bcd.is_finished = '1'
        <if test="keyWord != null and keyWord != ''">
            and (bai.agreement_code like concat('%',#{keyWord},'%') or
            bui.unit_name like concat('%',#{keyWord},'%') or
            bpl.pro_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mt.unit_name like concat('%',#{keyWord},'%') or
            bpl.contract_part like concat('%', #{keyWord}, '%') or
            sd.dept_name like concat('%', #{keyWord}, '%')
            )
        </if>
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND bai.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        <if test="isSlt != null and isSlt != ''">
            and sai.is_slt = #{isSlt}
        </if>
        GROUP BY bai.agreement_id,
        mt.type_id ORDER BY bai.agreement_code) AS subquery2 ON subquery1.type_id = subquery2.type_id
        AND subquery1.agreement_id = subquery2.agreement_id
    </select>

    <select id="getMachineHistoryRecordList" resultType="com.bonus.material.basic.domain.MachineHistoryRecordBean">
        SELECT
        bai.agreement_id as agreementId,
        mt.type_id as typeId,
        bai.agreement_code AS agreementCode,
        bui.unit_name AS unitName,
        bpl.pro_name AS proName,
        bpl.contract_part as contractPart,
        sd.dept_name as impUnitName,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit,
        lai.lease_person AS leaseMan,
        lai.create_time AS leaseDate,
        mm.ma_code AS maCode,
        baif.back_person AS backMan,
        sai.end_time AS backDate,
        CASE
        WHEN baif.back_person IS NULL THEN '在用'
        ELSE '已退'
        END AS statusName
        FROM
        slt_agreement_info sai
        LEFT JOIN lease_apply_info lai ON lai.id = sai.lease_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = sai.agreement_id
        LEFT JOIN bm_project bpl ON bpl.pro_id = bai.project_id
        LEFT JOIN sys_dept sd on sd.dept_id = bpl.imp_unit
        LEFT JOIN bm_unit bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = sai.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = sai.ma_id
        LEFT JOIN back_apply_info baif ON baif.id = sai.back_id
        WHERE
            1 = 1
          AND sai.ma_id IS NOT NULL
        <if test="keyWord != null and keyWord != ''">
            and (bai.agreement_code like concat('%',#{keyWord},'%') or
            bui.unit_name like concat('%',#{keyWord},'%') or
            bpl.pro_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mt.unit_name like concat('%',#{keyWord},'%') or
            mm.ma_code like concat('%',#{keyWord},'%') or
            lai.lease_person like concat('%',#{keyWord},'%') or
            baif.back_person like concat('%',#{keyWord},'%') or
            bpl.contract_part like concat('%', #{keyWord}, '%') or
            sd.dept_name like concat('%', #{keyWord}, '%')
            )
        </if>
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND (lai.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
                or baif.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59'))
        </if>
        <if test="statusName != null and statusName != ''">
            AND (
            CASE
            WHEN baif.back_person IS NULL THEN '在用'
            ELSE '已退'
            END = #{statusName}
            )
        </if>
        ORDER BY
            sai.end_time
    </select>

    <select id="getInputRecordList" resultType="com.bonus.material.basic.domain.InputRecordInfo">
        SELECT
            mt1.type_name as typeName,
            mt.type_name as typeModelName,
            bs.ma_code as maCode,
            mt.unit_name as unit,
            bs.in_num as inputNum,
            bs.creator as inputUser,
            bs.create_time as inputTime,
            tt.`code` as inputCode,
            CASE
                WHEN tt.`code` LIKE 'XG%' THEN '新购入库'
                WHEN tt.`code` LIKE 'R%' THEN '修试入库'
                WHEN tt.`code` LIKE 'PD%' THEN '盘点入库'
                ELSE '未知入库类型'
                END AS inputType,
            CASE
                WHEN bs.input_type = '0' THEN '编码'
                WHEN bs.input_type = '1' THEN '数量'
                WHEN bs.input_type = '2' THEN '二维码'
                WHEN bs.input_type = '3' THEN '标准箱'
                ELSE '未知入库方式'
                END AS inputStyle
        FROM
            bm_storage_log bs
                LEFT JOIN ma_type mt ON bs.type_id = mt.type_id
                AND mt.del_flag = '0'
                LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
                AND mt1.del_flag = '0'
                LEFT JOIN tm_task tt ON bs.task_id = tt.task_id
        WHERE bs.in_num != 0 and (bs.result_msg = '操作成功' OR bs.result_msg = '入库成功')
        <if test="inputType != null and inputType != ''">
            and (
            CASE
            WHEN tt.`code` LIKE 'XG%' THEN '新购入库'
            WHEN tt.`code` LIKE 'R%' THEN '修试入库'
            WHEN tt.`code` LIKE 'PD%' THEN '盘点入库'
            ELSE '未知入库类型'
            END = #{inputType}
            )
        </if>
        <if test="typeName != null and typeName != ''">
            and mt1.type_name like CONCAT('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like CONCAT('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND bs.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        order by bs.create_time desc
    </select>

    <select id="getOutRecordList" resultType="com.bonus.material.basic.domain.OutRecordInfo">
        SELECT
        bpl.pro_name AS proName,
        bui.unit_name AS unitName,
        bpl.contract_part as contractPart,
        sd.dept_name as impUnitName,
        bai.agreement_code AS agreementCode,
        mt1.type_name as typeName,
        mt.type_name as typeModelName,
        bs.ma_code as maCode,
        mt.unit_name as unit,
        bs.out_num as outNum,
        bs.creator as outUser,
        bs.create_time as outTime,
        tt.`code` as leaseCode,
        CASE
        WHEN bs.out_type = '0' THEN '编码'
        WHEN bs.out_type = '1' THEN '数量'
        WHEN bs.out_type = '2' THEN '二维码'
        WHEN bs.out_type = '3' THEN '标准箱'
        ELSE '未知出库方式'
        END AS outStyle,
        mt3.type_id as firstTypeId,
        mt2.type_id as secondTypeId
        FROM
        bm_storage_log bs
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = bs.agreement_id
        LEFT JOIN bm_project bpl ON bpl.pro_id = bai.project_id
        LEFT JOIN sys_dept sd on sd.dept_id = bpl.imp_unit
        LEFT JOIN bm_unit bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON bs.type_id = mt.type_id
        AND mt.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt1.parent_id = mt2.type_id
        AND mt2.del_flag = '0'
        LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id
        AND mt3.del_flag = '0'
        LEFT JOIN tm_task tt ON bs.task_id = tt.task_id
        WHERE bs.out_num != 0 and bs.result_msg = '操作成功'
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.pro_id = #{proId}
        </if>
        <if test="outStyle != null and outStyle != ''">
            and (
            CASE
            WHEN bs.out_type = '0' THEN '编码'
            WHEN bs.out_type = '1' THEN '数量'
            WHEN bs.out_type = '2' THEN '二维码'
            WHEN bs.out_type = '3' THEN '标准箱'
            ELSE '未知出库方式'
            END = #{outStyle}
            )
        </if>
        <if test="typeName != null and typeName != ''">
            and bs.type_name like CONCAT('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and bs.type_model_name like CONCAT('%',#{typeModelName},'%')
        </if>
        <if test="firstTypeIdList != null and firstTypeIdList.size > 0">
            and mt3.type_id in
            <foreach collection="firstTypeIdList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND bs.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        order by bs.create_time desc
    </select>

    <select id="getRetainedEquipmentList" resultType="com.bonus.material.basic.domain.RetainedEquipmentInfo">
        SELECT
        mt.type_id                          AS typeId,
        mt4.type_name                       AS constructionType,
        mt4.type_id                         AS firstTypeId,
        mt3.type_name                       AS materialType,
        mt3.type_id                         AS secondTypeId,
        mt2.type_name                       AS typeName,
        mt2.type_id                         AS thirdTypeId,
        mt.type_name                        AS typeModelName,
        mt.unit_name                        AS unit,
        IFNULL(mt.buy_price, 0)             AS buyPrice,
        CASE mt.manage_type
        WHEN 0 THEN
        IFNULL(subquery0.num, 0)
        ELSE
        IFNULL(mt.storage_num, 0)
        END                            AS storeNum,
        IFNULL(subquery1.usNum, 0)          AS usNum,
        IFNULL(subquery2.repairNum, 0)      AS repairNum,
        IFNULL(subquery3.repairInputNum, 0) AS repairInputNum,
        IFNULL(subquery4.inputNum, 0)       AS inputNum,
        IFNULL(subquery6.pendingScrapNum, 0) AS pendingScrapNum,
        IFNULL(subquery6.scrapNum, 0)       AS scrapNum,
        CASE mt.manage_type
        WHEN 0 THEN
        IFNULL(subquery0.num, 0)+ IFNULL(subquery1.usNum, 0) + IFNULL(subquery2.repairNum, 0) + IFNULL(subquery3.repairInputNum, 0)
            + IFNULL(subquery4.inputNum, 0) + IFNULL(subquery6.pendingScrapNum, 0)
        ELSE
        IFNULL(mt.storage_num, 0)+ IFNULL(subquery1.usNum, 0) + IFNULL(subquery2.repairNum, 0) + IFNULL(subquery3.repairInputNum, 0)
            + IFNULL(subquery4.inputNum, 0) + IFNULL(subquery6.pendingScrapNum, 0)
        END                             AS allNum,
        CASE mt.manage_type
        WHEN 0 THEN
        '编码'
        ELSE
        '数量'
        END                                manageType,
        IFNULL(subquery5.fiveReplacementNum, 0) AS fiveReplacementNum,
        IFNULL(subquery5.tenReplacementNum, 0) AS tenReplacementNum,
        IFNULL(subquery5.tenPlusReplacementNum, 0) AS tenPlusReplacementNum
        FROM ma_type mt
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt4.type_name AS constructionType,
        mt4.type_id   AS firstTypeId,
        mt3.type_name AS materialType,
        mt3.type_id   AS secondTypeId,
        mt2.type_name AS typeName,
        mt2.type_id   AS thirdTypeId,
        mt.type_name  AS typeModelName,
        count(mm.ma_id) AS num
        FROM ma_machine mm
        LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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 mm.ma_code is not null and mm.ma_status in (1)
        GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        subquery1.type_id,
        subquery1.typeName,
        subquery1.typeModelName,
        IFNULL(subquery1.outNum, 0)  AS outNum,
        IFNULL(subquery2.backNum, 0) AS backNum,
        CASE

        WHEN IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0) > 0 THEN
        IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0)
        ELSE 0
        END                      AS usNum
        FROM (
        SELECT
        mt.type_id,
        mt4.type_name               AS constructionType,
        mt4.type_id                 AS firstTypeId,
        mt3.type_name               AS materialType,
        mt3.type_id                 AS secondTypeId,
        mt2.type_name               AS typeName,
        mt2.type_id                 AS thirdTypeId,
        mt.type_name                AS typeModelName,
        SUM(IFNULL(lod.out_num, 0)) AS outNum
        FROM lease_out_details lod
        LEFT JOIN ma_type mt ON mt.type_id = lod.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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
        GROUP BY mt.type_id) AS subquery1
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt4.type_name AS constructionType,
        mt4.type_id   AS firstTypeId,
        mt3.type_name AS materialType,
        mt3.type_id          AS secondTypeId,
        mt2.type_name AS             typeName,
        mt2.type_id          AS thirdTypeId,
        mt.type_name  AS             typeModelName,
        SUM(IFNULL(bcd.back_num, 0)) backNum
        FROM back_check_details bcd
        LEFT JOIN ma_type mt ON mt.type_id = bcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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
        LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
        WHERE bcd.is_finished = '1'
        GROUP BY mt.type_id) AS subquery2
        ON subquery1.type_id = subquery2.type_id) AS subquery1
        ON mt.type_id = subquery1.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt4.type_name                         AS constructionType,
        mt4.type_id                           AS firstTypeId,
        mt3.type_name                         AS materialType,
        mt3.type_id                           AS secondTypeId,
        mt2.type_name                         AS typeName,
        mt2.type_id                           AS thirdTypeId,
        mt.type_name                          AS typeModelName,
        SUM(
        IFNULL(rad.repair_num, 0) - IFNULL(rad.repaired_num, 0) -
        IFNULL(rad.scrap_num, 0)) AS repairNum
        FROM repair_apply_details rad
        LEFT JOIN ma_type mt ON mt.type_id = rad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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
        rad.status = '0'
        GROUP BY
        mt.type_id) AS subquery2 ON subquery2.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt4.type_name                           AS constructionType,
        mt4.type_id                             AS firstTypeId,
        mt3.type_name                           AS materialType,
        mt3.type_id                             AS secondTypeId,
        mt2.type_name                           AS typeName,
        mt2.type_id                             AS thirdTypeId,
        mt.type_name                            AS typeModelName,
        SUM(
        IFNULL(rid.repair_num, 0) - IFNULL(rid.input_num, 0) - IFNULL(rid.reject_num, 0)) AS repairInputNum
        FROM repair_input_details rid
        LEFT JOIN ma_type mt ON mt.type_id = rid.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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 IFNULL(rid.repair_num, 0) - IFNULL(rid.input_num, 0) - IFNULL(rid.reject_num, 0) > 0
        GROUP BY
        mt.type_id) AS subquery3 ON subquery3.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt4.type_name                          AS constructionType,
        mt4.type_id                            AS firstTypeId,
        mt3.type_name                          AS materialType,
        mt3.type_id                            AS secondTypeId,
        mt2.type_name                          AS typeName,
        mt2.type_id                            AS thirdTypeId,
        mt.type_name                           AS typeModelName,
        SUM(
        IFNULL(pcd.check_num, 0) - IFNULL(pcd.input_num, 0)) AS inputNum
        FROM purchase_check_details pcd
        LEFT JOIN ma_type mt  ON mt.type_id = pcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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 IFNULL(pcd.check_num, 0) - IFNULL(pcd.input_num, 0) > 0
        and pcd.status in (3,4, 13,14)
        GROUP BY
        mt.type_id) AS subquery4 ON subquery4.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id AS typeId,
        IFNULL(SUM(IF(TIMESTAMPDIFF(YEAR,pcd.create_time,now()) &lt; 5, pcd.input_num,0)),0) AS fiveReplacementNum,
        IFNULL(SUM(IF(TIMESTAMPDIFF(YEAR,pcd.create_time,now()) &gt;= 5 and TIMESTAMPDIFF(YEAR,pcd.create_time,now()) &lt; 10, pcd.input_num,0)),0) AS tenReplacementNum,
        IFNULL(SUM(IF(TIMESTAMPDIFF(YEAR,pcd.create_time,now()) &gt;= 10, pcd.input_num,0)),0) AS tenPlusReplacementNum
        FROM purchase_check_details pcd
        LEFT JOIN ma_type mt ON pcd.type_id = mt.type_id
        GROUP BY mt.type_id
        ) subquery5 ON subquery5.typeId = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id                            AS typeId,
        mt4.type_name                         AS constructionType,
        mt4.type_id                           AS firstTypeId,
        mt3.type_name                         AS materialType,
        mt3.type_id                           AS secondTypeId,
        mt2.type_name                         AS typeName,
        mt2.type_id                           AS thirdTypeId,
        mt.type_name                          AS typeModelName,
        SUM(IF(sad.ledger_status = '0', IFNULL(sad.scrap_num, 0), 0)) AS pendingScrapNum,
        SUM(IF(sad.ledger_status = '1', IFNULL(sad.scrap_num, 0), 0)) AS scrapNum
        FROM scrap_apply_details sad
        LEFT JOIN ma_type mt ON mt.type_id = sad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.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
        GROUP BY
        mt.type_id
        ) subquery6 ON subquery6.typeId = mt.type_id
        LEFT JOIN ma_type mt2 on mt2.type_id = mt.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 mt.`level` = 4
        and mt.del_flag = '0'
        <if test="typeId != null">
          <if test="level == 3">
              and mt.parent_id = #{typeId}
          </if>
          <if test="level == 2">
              and mt2.parent_id = #{typeId}
          </if>
        </if>
        <if test="keyWord != null and keyWord != ''">
            and (
            mt4.type_name like concat('%',#{keyWord},'%') or
            mt3.type_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%')
            )
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
    </select>

    <select id="getMaCodeList" resultType="com.bonus.material.basic.domain.StorageInfo">
        SELECT
        bs.type_id as typeId,
        mt1.type_name as typeName,
        mt.type_name as typeModelName,
        bs.ma_code as maCode,
        bs.in_num as storeNum,
        bs.creator as inputUser,
        bs.create_time as inputTime,
        CASE
        WHEN tt.`code` LIKE 'XG%' THEN '新购入库'
        WHEN tt.`code` LIKE 'R%' THEN '修饰入库'
        WHEN tt.`code` LIKE 'PD%' THEN '盘点入库'
        ELSE '未知入库类型'
        END AS inputType,
        mt.buy_price as buyPrice,
        GROUP_CONCAT( DISTINCT su.nick_name ORDER BY su.nick_name SEPARATOR ', ' ) AS maKeeper
        FROM
        bm_storage_log bs
        LEFT JOIN ma_type mt ON bs.type_id = mt.type_id
        AND mt.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN tm_task tt ON bs.task_id = tt.task_id
        LEFT JOIN ma_type_keeper mtk ON mtk.type_id = bs.type_id
        LEFT JOIN sys_user su ON mtk.user_id = su.user_id
        WHERE bs.in_num != 0 and (bs.result_msg = '操作成功' OR bs.result_msg = '入库成功')
        <if test="typeId != null">
            AND bs.type_id = #{typeId}
        </if>
        GROUP BY bs.ma_code, mt.type_id, mt1.type_name, mt.type_name, bs.ma_code,
        bs.in_num, bs.creator, bs.create_time, tt.CODE, mt.buy_price
    </select>

    <select id="getUserRecords" resultType="com.bonus.material.basic.domain.UseStorageInfo">
        SELECT
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.buy_price AS buyPrice,
        sai.num AS usNum,
        mm.ma_code AS maCode,
        bp.pro_name AS projectName,
        sai.lease_id AS leaseId,
        sai.type_id AS typeId
        FROM
        slt_agreement_info sai
        LEFT JOIN ma_type mt ON mt.type_id = sai.type_id
        LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
        LEFT JOIN ma_machine mm ON mm.ma_id = sai.ma_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = sai.agreement_id
        LEFT JOIN bm_project bp ON bai.project_id = bp.pro_id
        WHERE
            sai.end_time IS NULL
            AND sai.back_id IS NULL
        <if test="typeId != null">
            AND sai.type_id = #{typeId}
        </if>
    </select>

    <select id="getRepairRecordList" resultType="com.bonus.material.basic.domain.RepairStorageInfo">
        SELECT
            mt2.type_name AS typeName,
            mt.type_name AS typeModelName,
            mt.buy_price AS buyPrice,
            IFNULL( rad.repair_num, 0 ) - IFNULL( rad.repaired_num, 0 ) - IFNULL( rad.scrap_num, 0 ) AS repairNum,
            tt.`code` AS repairCode,
            bai.create_time AS leaseTime,
            GROUP_CONCAT(DISTINCT su.nick_name ORDER BY su.nick_name SEPARATOR ', ') AS repairer,
            bai.back_person AS creator,
            mm.ma_code as maCode
        FROM
            repair_apply_details rad
                LEFT JOIN ma_type mt ON mt.type_id = rad.type_id
                LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
                LEFT JOIN ma_type_repair mtr ON mtr.type_id = rad.type_id
                LEFT JOIN sys_user su ON mtr.user_id = su.user_id
                LEFT JOIN ma_machine mm ON mm.ma_id = rad.ma_id
                LEFT JOIN tm_task tt ON rad.task_id = tt.task_id
                LEFT JOIN back_apply_info bai ON rad.back_id = bai.id
        WHERE rad.`status` = '0'
        <if test="typeId != null">
            AND rad.type_id = #{typeId}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mm.ma_code like concat('%',#{keyWord},'%') or
            tt.`code` like concat('%',#{keyWord},'%') or
            rad.repairer like concat('%',#{keyWord},'%') or
            bai.back_person like concat('%',#{keyWord},'%') or
            su.nick_name like concat('%',#{keyWord},'%')
            )
        </if>
        GROUP BY mm.ma_code,tt.`code`,mt.type_id
    </select>

    <select id="getPurchaseRecordList" resultType="com.bonus.material.basic.domain.PurchaseInputInfo">
        SELECT
            pcd.task_id as taskId,
            mt2.type_name AS typeName,
            mt.type_name AS typeModelName,
            mt.buy_price AS buyPrice,
            IFNULL( pcd.check_num, 0 ) - IFNULL( pcd.input_num, 0 ) AS inputNum,
            tt.`code` AS code,
            GROUP_CONCAT( DISTINCT su.nick_name ORDER BY su.nick_name SEPARATOR ', ' ) AS maKeeper,
            pcd.check_time AS checkTime,
            mt.manage_type as manageType
        FROM
            purchase_check_details pcd
        LEFT JOIN ma_type mt ON mt.type_id = pcd.type_id
        LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
        LEFT JOIN tm_task tt ON pcd.task_id = tt.task_id
        LEFT JOIN ma_type_keeper mtk ON mtk.type_id = pcd.type_id
        LEFT JOIN sys_user su ON mtk.user_id = su.user_id
        WHERE
            pcd.`status` IN ( 3, 4, 13, 14 )
        <if test="typeId != null">
            AND pcd.type_id = #{typeId}
        </if>
        GROUP BY tt.`code`,
                 mt.type_id
    </select>

    <select id="getRepairInputList" resultType="com.bonus.material.basic.domain.RepairInputRecord">
        SELECT
            mt2.type_name AS typeName,
            mt.type_name AS typeModelName,
            mt.buy_price AS buyPrice,
            IFNULL(rid.repair_num, 0) - IFNULL(rid.input_num, 0) AS repairInputNum,
            tt.`code` AS inputCode,
            GROUP_CONCAT(DISTINCT su.nick_name ORDER BY su.nick_name SEPARATOR ', ') AS maKeeper,
            GROUP_CONCAT(DISTINCT su2.nick_name ORDER BY su2.nick_name SEPARATOR ', ') AS repairer,
            rid.create_time as repairInputTime,
            mm.ma_code as maCode
        FROM
            repair_input_details rid
                LEFT JOIN ma_type mt ON mt.type_id = rid.type_id
                LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
                LEFT JOIN tm_task tt ON rid.task_id = tt.task_id
                LEFT JOIN ma_type_keeper mtk ON mtk.type_id = rid.type_id
                LEFT JOIN sys_user su ON mtk.user_id = su.user_id
                LEFT JOIN ma_type_repair mtr ON mtr.type_id = rid.type_id
                LEFT JOIN sys_user su2 ON mtr.user_id = su2.user_id
                LEFT JOIN repair_apply_details rad ON rid.repair_id = rad.id
                LEFT JOIN ma_machine mm ON mm.ma_id = rid.ma_id
        WHERE
            rid.`status` IN (0)
        <if test="typeId != null">
            AND rid.type_id = #{typeId}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            tt.`code` like concat('%',#{keyWord},'%') or
            su.nick_name like concat('%',#{keyWord},'%') or
            su2.nick_name like concat('%',#{keyWord},'%') or
            mm.ma_code like concat('%',#{keyWord},'%')
            )
        </if>
        GROUP BY mm.ma_code,tt.`code`,mt.type_id
    </select>

    <select id="selectMaCodeByTaskIdAndTypeId" resultType="com.bonus.material.basic.domain.PurchaseInputInfo">
        SELECT
            pmi.id as id,
            pmi.task_id as taskId,
            pmi.type_id as typeId,
            pmi.ma_code as maCode
        FROM
            bm_qrcode_info pmi
        LEFT JOIN ma_machine mm ON pmi.ma_code = mm.ma_code AND mm.type_id = #{typeId}
        WHERE
            pmi.task_id = #{taskId}
            AND pmi.type_id = #{typeId}
            AND mm.ma_code IS NULL
            AND pmi.ma_code IS NOT NULL
    </select>

    <select id="selectMaCodeByTypeId" resultType="com.bonus.material.basic.domain.StorageInfo">
        SELECT
            type_id as typeId,
            ma_code as maCode
        FROM
            ma_machine
        WHERE
            ma_status = '1' and type_id = #{typeId}
    </select>

    <select id="selectMaTypeName" resultType="com.bonus.material.basic.domain.UseStorageInfo">
        SELECT
            GROUP_CONCAT(DISTINCT su.nick_name ORDER BY su.nick_name SEPARATOR ', ') AS maKeeper
        FROM ma_type_keeper mtk
                 LEFT JOIN sys_user su ON mtk.user_id = su.user_id
        WHERE 1 = 1
        <if test="typeId != null">
            AND mtk.type_id = #{typeId}
        </if>
    </select>

    <select id="selectInFo" resultType="com.bonus.material.basic.domain.UseStorageInfo">
        SELECT
            create_time as outTime,
            GROUP_CONCAT( DISTINCT create_by ) AS creator
        FROM
            lease_out_details
        WHERE parent_id = #{leaseId}
        GROUP BY parent_id
        LIMIT 1
    </select>

    <select id="getScrapList" resultType="com.bonus.material.basic.domain.ScrapRecordInfo">
        SELECT mt2.type_name   AS typeName,
               mt.type_name    AS typeModelName,
               mt.buy_price    AS buyPrice,
               mm.ma_code      AS maCode,
               sad.create_by    AS scrapBy,
               su.nick_name    AS auditBy,
               sad.create_time AS scrapTime,
               CASE
                   sad.scrap_source
                   WHEN '1' then '退料报废'
                   WHEN '2' then '维修报废'
                   WHEN '3' then '盘点报废'
                   ELSE ''
                   END
                as scrapType,
               sad.scrap_num   AS scrapNum
        FROM scrap_apply_details sad
                 LEFT JOIN ma_type mt ON mt.type_id = sad.type_id
                 LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
                 LEFT JOIN ma_machine mm ON mm.ma_id = sad.ma_id
                 LEFT JOIN sys_user su ON sad.audit_by = su.user_id
        WHERE sad.ledger_status = '0'
        <if test="typeId != null">
            AND sad.type_id = #{typeId}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            su.nick_name like concat('%',#{keyWord},'%') or
            sad.create_by like concat('%',#{keyWord},'%') or
            mm.ma_code like concat('%',#{keyWord},'%')
            )
        </if>
        GROUP BY mm.ma_code ,mt.type_id
        ORDER BY sad.create_time DESC
    </select>

    <select id="getScrapAuditList" resultType="com.bonus.material.basic.domain.ScrapAuditInfo">
        SELECT mt2.type_name   AS typeName,
        mt.type_name    AS typeModelName,
        mt.buy_price    AS buyPrice,
        mm.ma_code      AS maCode,
        sad.create_by    AS scrapBy,
        su2.nick_name    AS auditBy,
        sad.ledger_time AS auditTime,
        CASE
        sad.scrap_source
        WHEN '1' then '退料报废'
        WHEN '2' then '维修报废'
        WHEN '3' then '盘点报废'
        ELSE ''
        END
        as scrapType,
        sad.scrap_num AS scrapNum
        FROM scrap_apply_details sad
        LEFT JOIN ma_type mt ON mt.type_id = sad.type_id
        LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
        LEFT JOIN ma_machine mm ON mm.ma_id = sad.ma_id
        LEFT JOIN sys_user su2 ON sad.ledger_by = su2.user_id
        WHERE
        sad.ledger_status = '1'
        <if test="typeId != null">
            AND sad.type_id = #{typeId}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            sad.create_by like concat('%',#{keyWord},'%') or
            su2.nick_name like concat('%',#{keyWord},'%') or
            mm.ma_code like concat('%',#{keyWord},'%')
            )
        </if>
        GROUP BY mm.ma_code ,mt.type_id
        ORDER BY sad.ledger_time DESC
    </select>
    <select id="getMaTypeSelectList" resultType="com.bonus.material.basic.domain.vo.MaTypeSelectInfo">
        (
        SELECT
        bagi.agreement_code AS agreementCode,
        bu.unit_name AS bmUnitName,
        bp.pro_name AS bmProName,
        mt2.type_name AS typeName,
        mt1.type_name AS typeModelName,
        mm.ma_code AS maCode,
        mt1.unit_name AS unitName,
        tt.`code` AS code,
        bcd.create_by AS createBy,
        bcd.create_time AS createTime,
        '退料' AS operationType,
        bcd.parent_id AS id,
        tt.task_type AS taskType,
        a.task_id AS taskId,
        bcd.type_id AS typeId,
        bcd.ma_id AS maId
        FROM
        back_check_details bcd
        LEFT JOIN ( SELECT id, task_id FROM back_apply_info GROUP BY id ) a ON bcd.parent_id = a.id
        LEFT JOIN tm_task tt ON tt.task_id = a.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
        AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id
        AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id
        AND bu.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt1.type_id = bcd.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        AND mt2.del_flag = '0'
        LEFT JOIN ma_machine mm ON bcd.ma_id = mm.ma_id
        WHERE 1 = 1
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[and DATE_FORMAT( bcd.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        <if test="unitId != null">
            and bu.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bp.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt1.type_name like concat('%',#{typeModelName},'%')
        </if>
        )
        UNION ALL
        (
        SELECT
        bagi.agreement_code AS agreementCode,
        bu.unit_name AS bmUnitName,
        bp.pro_name AS bmProName,
        mt2.type_name AS typeName,
        mt1.type_name AS typeModelName,
        mm.ma_code AS maCode,
        mt1.unit_name AS unitName,
        tt.`code` AS code,
        su.nick_name AS createBy,
        rad.create_time AS createTime,
        '维修' AS operationType,
        rad.repair_id AS id,
        tt.task_type AS taskType,
        a.task_id AS taskId,
        a.type_id AS typeId,
        a.ma_id AS maId
        FROM
        repair_audit_details rad
        LEFT JOIN ( SELECT id, task_id, type_id, ma_id FROM repair_apply_details GROUP BY id ) a ON rad.repair_id = a.id
        LEFT JOIN tm_task tt ON tt.task_id = a.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
        AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id
        AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id
        AND bu.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt1.type_id = rad.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        AND mt2.del_flag = '0'
        LEFT JOIN ma_machine mm ON rad.ma_id = mm.ma_id
        LEFT JOIN sys_user su ON rad.create_by = su.user_id
        WHERE
        rad.`status` = '1'
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[and DATE_FORMAT( rad.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        <if test="unitId != null">
            and bu.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bp.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt1.type_name like concat('%',#{typeModelName},'%')
        </if>
        )
        UNION ALL
        (
        SELECT
        bagi.agreement_code AS agreementCode,
        bu.unit_name AS bmUnitName,
        bp.pro_name AS bmProName,
        mt1.type_name AS typeName,
        mt.type_name AS typeModelName,
        bs.ma_code AS maCode,
        mt.unit_name AS unitName,
        tt.`code` AS code,
        bs.creator AS createBy,
        bs.create_time AS createTime,
        '新购入库' AS operationType,
        bs.id AS id,
        tt.task_type AS taskType,
        bs.task_id AS taskId,
        bs.type_id AS typeId,
        NULL AS maId
        FROM
        bm_storage_log bs
        LEFT JOIN ma_type mt ON bs.type_id = mt.type_id
        AND mt.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN tm_task tt ON bs.task_id = tt.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
        AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id
        AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id
        AND bu.del_flag = '0'
        WHERE
        bs.in_num != 0 AND bs.result_msg = '入库成功'
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[and DATE_FORMAT( bs.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        <if test="unitId != null">
            and bu.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bp.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt1.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        )
        UNION ALL
        (
        SELECT
        bagi.agreement_code AS agreementCode,
        bu.unit_name AS bmUnitName,
        bp.pro_name AS bmProName,
        mt2.type_name AS typeName,
        mt1.type_name AS typeModelName,
        mm.ma_code AS maCode,
        mt1.unit_name AS unitName,
        tt.`code` AS code,
        lod.create_by AS createBy,
        lod.create_time AS createTime,
        '领料' AS operationType,
        lod.parent_id AS id,
        tt.task_type AS taskType,
        a.task_id AS taskId,
        lod.type_id AS typeId,
        lod.ma_id AS maId
        FROM
        lease_out_details lod
        LEFT JOIN ( SELECT id, task_id FROM lease_apply_info GROUP BY id ) a ON lod.parent_id = a.id
        LEFT JOIN tm_task tt ON tt.task_id = a.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
        AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id
        AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id
        AND bu.del_flag = '0'
        LEFT JOIN ma_type mt1 ON mt1.type_id = lod.type_id
        AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        AND mt2.del_flag = '0'
        LEFT JOIN ma_machine mm ON lod.ma_id = mm.ma_id
        where 1 = 1
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[and DATE_FORMAT( lod.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        <if test="unitId != null">
            and bu.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bp.pro_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt1.type_name like concat('%',#{typeModelName},'%')
        </if>
        )
        ORDER BY createTime DESC
    </select>

    <select id="selectUnitAndProAndAgreementInfo"
            resultType="com.bonus.material.basic.domain.vo.MaTypeSelectInfo">
        SELECT
            bagi.agreement_code AS agreementCode,
            bu.unit_name AS bmUnitName,
            bp.pro_name AS bmProName
        FROM
            lease_apply_info lai
                LEFT JOIN bm_agreement_info bagi ON bagi.unit_id = lai.unit_id
                AND bagi.project_id = lai.project_id
                AND bagi.`status` = '1'
                LEFT JOIN bm_unit bu ON lai.unit_id = bu.unit_id
                LEFT JOIN bm_project bp ON bp.pro_id = lai.project_id
        WHERE
            lai.id = #{id}
    </select>

    <select id="getPartInfoList" resultType="java.lang.String">
        SELECT
        CONCAT(GROUP_CONCAT(
        DISTINCT
        CASE
        WHEN rar.part_name IS NOT NULL
        OR rar.part_id IS NOT NULL THEN
        CONCAT_WS(
        ',',
        rar.part_name,
        CASE
        WHEN rar.part_id IS NOT NULL THEN
        concat( mpt1.pa_name, '-', mpt.pa_name )
        END
        ) ELSE NULL
        END
        ), ',',SUM(
        CASE
        WHEN rar.part_type = 1
        AND rar.part_id IS NOT NULL THEN
        rar.part_num * rar.part_price
        WHEN rar.part_type = 1
        AND rar.part_id IS NULL THEN
        rar.part_price ELSE 0
        END
        ), ',','收费')
        FROM
        repair_apply_record rar
        LEFT JOIN ma_part_type mpt ON rar.part_id = mpt.pa_id
        AND mpt.del_flag = '0'
        LEFT JOIN ma_part_type mpt1 ON mpt.parent_id = mpt1.pa_id
        AND mpt1.del_flag = '0'
        WHERE
        rar.`status` = '1'
        AND rar.part_type = '1'
          AND rar.task_id = #{taskId}
        <if test="typeId != null">
            AND rar.type_id = #{typeId}
        </if>
        <if test="maId != null">
            AND rar.ma_id = #{maId}
        </if>
    </select>

</mapper>
