<?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="cn.ezhizao.project.business.mapper.BizDayworkItemRejectMapper">

    <resultMap type="cn.ezhizao.project.business.domain.BizDayworkItemReject" id="BizDayworkItemRejectResult">
        <id column="id" property="id"/>
    </resultMap>
    <select id="getList" parameterType="BizDayworkItemReject" resultMap="BizDayworkItemRejectResult">
        SELECT * FROM biz_daywork_item_reject
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            deleted = 0 and process_inspection_id = 0
            <if test="dayworkItemId != null "> AND daywork_item_id = #{dayworkItemId}</if>
            <if test="dayworkId != null "> AND daywork_id = #{dayworkId}</if>
            <if test="reason != null  and reason != ''"> AND reason = #{reason}</if>
            <if test="rejectNum != null "> AND reject_num = #{rejectNum}</if>
        </trim>
    </select>
    <select id="getSortCheckStandard" resultMap="BizDayworkItemRejectResult">
        select DISTINCT t1.inspection_instruction_id,t4.standard as checkStandard from  biz_daywork_item_reject t1
        left join biz_lot t2 on t1.lot_id = t2.id
        left join biz_production_plan_detail t3 on t1.production_plan_detail_id = t3.id
        left join biz_inspection_instruction t4 on t1.inspection_instruction_id = t4.id
        left join biz_daywork_item t5 on t1.daywork_item_id = t5.id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.type = 1 and t1.inspection_type = 0 and t1.process_inspection_id = 0 and t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0
            and t4.deleted = 0 and t5.deleted = 0 and t5.status = 3 and t5.is_sort = 1
            <if test="lotCode != null "> AND t2.lot_code like concat('%', #{lotCode}, '%')</if>
            <if test="productDescription != null "> AND t3.product_description like concat('%', #{productDescription}, '%')</if>
            <if test="startTime != null ">AND t1.create_time &gt; #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt; #{endTime}</if>
        </trim>
    </select>
    <select id="getSortStandardLot" resultMap="BizDayworkItemRejectResult">
        SELECT DISTINCT
        t1.lot_id,
        DATE_FORMAT( t1.create_time, '%Y-%m-%d' ) AS date,
        t3.specification,
        t3.product_description,
        t3.id as productionPlanDetailId,
        t2.lot_code
        FROM
        biz_daywork_item_reject t1
        LEFT JOIN biz_lot t2 ON t1.lot_id = t2.id
        LEFT JOIN biz_production_plan_detail t3 ON t1.production_plan_detail_id = t3.id
        left join biz_daywork_item t4 on t1.daywork_item_id = t4.id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.type = 1 and t1.inspection_type = 0 and t1.process_inspection_id = 0 and t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0
            and t4.status = 3 and t4.deleted = 0 and t4.is_sort = 1
            <if test="lotCode != null "> AND t2.lot_code like concat('%', #{lotCode}, '%')</if>
            <if test="productDescription != null "> AND t3.product_description like concat('%', #{productDescription}, '%')</if>
            <if test="startTime != null ">AND t1.create_time &gt; #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt; #{endTime}</if>
        </trim>
<!--        先按产品再按时间排序-->
        order by t2.lot_code,t1.create_time
    </select>
    <select id="getSortCheckStandardList" resultMap="BizDayworkItemRejectResult">
        select t1.* from biz_daywork_item_reject t1 left join biz_lot t2 on t1.lot_id = t2.id
        left join biz_daywork_item t3 on t1.daywork_item_id = t3.id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.type = 1 and t1.inspection_type = 0 and t1.process_inspection_id = 0 and t1.deleted = 0 and t2.deleted = 0 and t3.status = 3 and t3.deleted = 0
            and t3.is_sort = 1
            <if test="lotIds != null "> AND t1.lot_id in
                <foreach collection="lotIds" item="item" separator="," open="(" close=")">
                #{item}
            </foreach></if>
            <if test="startTime != null ">AND t1.create_time &gt; #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt; #{endTime}</if>
        </trim>
    </select>
    <select id="getRejectListByItemId" resultMap="BizDayworkItemRejectResult">
        select t1.reject_num,t1.reason,t2.standard as checkStandard from biz_daywork_item_reject t1
        left join biz_inspection_instruction t2 on t1.inspection_instruction_id = t2.id
        where t1.process_inspection_id = 0 and t1.deleted = 0 and t1.inspection_type = 0 and t1.daywork_item_id = #{dayworkItemId}
        order by t1.create_time
    </select>
    <select id="getSortRejectList" resultMap="BizDayworkItemRejectResult">
        select DISTINCT t1.*,t3.specification,t3.product_description,t2.lot_code from  biz_daywork_item_reject t1
        left join biz_lot t2 on t1.lot_id = t2.id
        left join biz_production_plan_detail t3 on t1.production_plan_detail_id = t3.id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.type = 1 and t1.inspection_type = 0 and t1.process_inspection_id = 0 and t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0
            <if test="lotCode != null "> AND t2.lot_code like concat('%', #{lotCode}, '%')</if>
            <if test="productDescription != null "> AND t3.product_description like concat('%', #{productDescription}, '%')</if>
            <if test="startTime != null ">AND t1.create_time &gt; #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt; #{endTime}</if>
        </trim>
        order by t1.create_time,t2.lot_code
    </select>
    <select id="getRejectReport" resultMap="BizDayworkItemRejectResult">
        select t1.dept_id,t1.lot_id,max(t1.create_time) as create_time,t2.lot_code,t3.product_description,t2.technology_version,t4.dept_name,sum(reject_num) as totalRejectNum from biz_daywork_item_reject t1 left join biz_lot t2 on t1.lot_id=t2.id
        left join biz_production_plan_detail t3 on t2.production_plan_detail_id=t3.id left join sys_dept t4 on t1.dept_id=t4.dept_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0 and t4.del_flag = 0 and t1.process_inspection_id = 0
            <if test="deptId != null and deptId != 0">AND t1.dept_id = #{deptId}</if>
            <if test="startTime != null ">AND t1.create_time &gt;= #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt;= #{endTime}</if>
            <if test="productDescription != null and productDescription != ''">AND t3.product_description  LIKE CONCAT('%', #{productDescription}, '%')</if>
            <if test="lotCode != null and lotCode != ''">AND t2.lot_code  LIKE CONCAT('%', #{lotCode}, '%')</if>
        </trim>
         group by t1.dept_id,t1.lot_id
        ORDER BY  max(t1.create_time) DESC
    </select>
    <select id="getRejectRecords" resultMap="BizDayworkItemRejectResult">
        SELECT
        t3.nick_name,
        t1.create_time,
        t1.reject_num,
        t4.process_alias,
        CASE
        WHEN t1.type = 0 THEN
        ( SELECT t2.dict_label FROM sys_dict_data t2 WHERE t2.dict_type = 'waste_causes' AND t2.dict_value = t1.reason and t2.status = 0 ) ELSE ( SELECT standard FROM biz_inspection_instruction t5 WHERE t5.id = t1.inspection_instruction_id and t5.deleted = 0  )
        END AS reason
        FROM
        biz_daywork_item_reject t1
        LEFT JOIN sys_user t3 ON t1.creator_id = t3.user_id
        LEFT JOIN biz_process t4 ON t4.id = t1.process_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
        t1.deleted = 0  and t3.del_flag = 0 and t4.deleted = 0
        and t1.dept_id = #{deptId}
        AND t1.lot_id = #{lotId}
            <if test="startTime != null ">AND t1.create_time &gt;= #{startTime}</if>
            <if test="endTime != null ">AND t1.create_time &lt;= #{endTime}</if>
        </trim>
        order by t1.create_time DESC
    </select>

    <update id="updateDayworkItemId"  parameterType="java.util.List">
        <foreach collection="rejects" item="item" separator=";" >
            UPDATE biz_daywork_item_reject r SET r.daywork_item_id = COALESCE((SELECT id FROM biz_daywork_item d WHERE d.form_daywork_item_id = #{item.dayworkItemId} ), #{item.dayworkItemId})
            WHERE id=#{item.id}
        </foreach>
    </update>

    <update id="removeSpecial" parameterType="java.util.List">
        UPDATE biz_daywork_item_reject
        SET deleted = 1
        WHERE daywork_id IN
        <foreach collection="firstDayworkIds" item="daywork" index="index" open="(" separator="," close=")">
            #{daywork}
        </foreach>
    </update>



    <delete id="physicalDelete">
        DELETE FROM biz_daywork_item_reject
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
       <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>
</mapper>
