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

    <select id="getScrapRecordList" resultType="com.bonus.sgzb.material.domain.ScrapRecord">
        SELECT sad.id,
               unit.unit_name as unitName,
               lot.lot_name   as proName,
               tt.`code`,
               tt2.`code`     as sourceCode,
               mt2.type_name  as typeName,
               mt.type_name   as typeModelName,
               mt.unit_name   as unit,
               mm.ma_code     as maCode,
               sad.audit_time as scrapTime,
               sad.scrap_num  as scrapNum,
               sad.remark,
               su.user_name   as userName
        FROM scrap_apply_details sad
                 LEFT JOIN tm_task tt on tt.task_id = sad.task_id
                 LEFT JOIN repair_audit_details rad on rad.id = sad.parent_id
                 LEFT JOIN tm_task tt2 on tt2.task_id = rad.task_id
                 LEFT JOIN tm_task_agreement tta on tta.task_id = sad.task_id
                 LEFT JOIN bm_agreement_info bai on bai.agreement_id = tta.agreement_id
                 LEFT JOIN bm_project_lot lot on lot.lot_id = bai.project_id
                 LEFT JOIN bm_unit_info unit on unit.unit_id = bai.unit_id
                 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_machine mm on mm.ma_id = sad.ma_id
                 LEFT JOIN sys_user su on su.user_id = sad.audit_by
        WHERE sad.scrap_source = '2'
        <if test="keyWord != null and keyWord != ''">
            and (unit.unit_name like concat('%',#{keyWord},'%') or
            lot.lot_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
            su.user_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>
        <if test="unitId != null">
            and unit.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and lot.lot_id = #{proId}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND sad.audit_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>

        UNION ALL

        SELECT sad.id,
               unit.unit_name as unitName,
               lot.lot_name   as proName,
               tt.`code`,
               tt2.`code`     as sourceCode,
               mt2.type_name  as typeName,
               mt.type_name   as typeModelName,
               mt.unit_name   as unit,
               mm.ma_code     as maCode,
               sad.audit_time as scrapTime,
               sad.scrap_num  as scrapNum,
               sad.remark,
               su.user_name   as userName
        FROM scrap_apply_details sad
                 LEFT JOIN tm_task tt on tt.task_id = sad.task_id
                 LEFT JOIN back_apply_info baif on baif.id = sad.parent_id
                 LEFT JOIN tm_task tt2 on tt2.task_id = baif.task_id
                 LEFT JOIN tm_task_agreement tta on tta.task_id = sad.task_id
                 LEFT JOIN bm_agreement_info bai on bai.agreement_id = tta.agreement_id
                 LEFT JOIN bm_project_lot lot on lot.lot_id = bai.project_id
                 LEFT JOIN bm_unit_info unit on unit.unit_id = bai.unit_id
                 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_machine mm on mm.ma_id = sad.ma_id
                 LEFT JOIN sys_user su on su.user_id = sad.audit_by
        WHERE sad.scrap_source = '1'
        <if test="keyWord != null and keyWord != ''">
            and (unit.unit_name like concat('%',#{keyWord},'%') or
            lot.lot_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
            su.user_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>
        <if test="unitId != null">
            and unit.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and lot.lot_id = #{proId}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND sad.audit_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
    </select>
</mapper>