<?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.aucma.report.mapper.RecordExceptionProcessMapper">

    <resultMap type="RecordExceptionProcess" id="RecordExceptionProcessResult">
        <result property="objId" column="obj_id"/>
        <result property="boxBarcode" column="box_barcode"/>
        <result property="productionUserFlag" column="production_user_flag"/>
        <result property="teamCode" column="team_code"/>
        <result property="teamName" column="team_name"/>
        <result property="applyReason" column="apply_reason"/>
        <result property="applyTime" column="apply_time"/>
        <result property="qualityUserFlag" column="quality_user_flag"/>
        <result property="failureReason" column="failure_reason"/>
        <result property="failureType" column="failure_type"/>
        <result property="qualityUserTime" column="quality_user_time"/>
        <result property="productionManagerFlag" column="production_manager_flag"/>
        <result property="productionManagerTime" column="production_manager_time"/>
        <result property="qualityManagerFlag" column="quality_manager_flag"/>
        <result property="qualityManagerTime" column="quality_manager_time"/>
        <result property="inspectionUserCode" column="inspection_user_code"/>
        <result property="inspectionUserFlag" column="inspection_user_flag"/>
        <result property="imageAddress" column="image_address"/>
        <result property="remark" column="remark"/>
        <result property="createdBy" column="created_by"/>
        <result property="createdTime" column="created_time"/>
        <result property="updatedBy" column="updated_by"/>
        <result property="updatedTime" column="updated_time"/>
        <result property="productLineCode" column="productLineCode"/>
        <result property="productLineName" column="productLineName"/>
        <result property="orderCode" column="orderCode"/>
        <result property="materialModel" column="materialModel"/>
    </resultMap>

    <sql id="selectRecordExceptionProcessVo">
        select rep.obj_id,
               PB.ORDER_CODE              orderCode,
               NVL(BP.PRODUCT_LINE_CODE, 'CX_01') productLineCode,
               NVL(BP.PRODUCT_LINE_NAME, '一线')    productLineName,
               rep.box_barcode,
               ML.MATERIAL_SPECIFICATIONS materialModel,
               rep.production_user_flag,
               rep.team_code,
               bt.TEAM_NAME,
               rep.apply_reason,
               rep.apply_time,
               rep.quality_user_flag,
               rep.failure_reason,
               rep.failure_type,
               rep.quality_user_time,
               rep.production_manager_flag,
               rep.production_manager_time,
               rep.quality_manager_flag,
               rep.quality_manager_time,
               rep.inspection_user_code,
               rep.inspection_user_flag,
               rep.image_address,
               rep.remark,
               rep.created_by,
               rep.created_time,
               rep.updated_by,
               rep.updated_time
        from record_exception_process rep
                 left join PRINT_BARCODE PB ON PB.MATERIAL_BARCODE = rep.BOX_BARCODE
                 left join BASE_PRODUCTLINE BP ON BP.PRODUCT_LINE_CODE = PB.PRODUCT_LINE_CODE
                 left JOIN BASE_MATERIALINFO ML ON ML.MATERIAL_CODE = PB.MATERIAL_CODE
                 left join BASE_TEAMMEMBERS bt on bt.TEAM_CODE = rep.TEAM_CODE
    </sql>

    <select id="selectRecordExceptionProcessList" parameterType="RecordExceptionProcess"
            resultMap="RecordExceptionProcessResult">
        <include refid="selectRecordExceptionProcessVo"/>
        <where>
            <if test="boxBarcode != null  and boxBarcode != ''">and rep.box_barcode = #{boxBarcode}</if>
            <if test="productionUserFlag != null  and productionUserFlag != ''">and rep.production_user_flag =
                #{productionUserFlag}
            </if>
            <if test="teamCode != null  and teamCode != ''">and rep.team_code = #{teamCode}</if>
            <if test="applyReason != null  and applyReason != ''">and rep.apply_reason = #{applyReason}</if>
            <if test="params.beginApplyTime != null and params.beginApplyTime != '' and params.endApplyTime != null and params.endApplyTime != ''">
                and rep.apply_time between to_date(#{params.beginApplyTime}, 'yyyy-mm-dd hh24:mi:ss') and
                to_date(#{params.endApplyTime}, 'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="qualityUserFlag != null  and qualityUserFlag != 9">and rep.quality_user_flag = #{qualityUserFlag}
            </if>
            <if test="failureReason != null  and failureReason != ''">and rep.failure_reason = #{failureReason}</if>
            <if test="failureType != null  and failureType != ''">and rep.failure_type = #{failureType}</if>
            <if test="qualityUserTime != null ">and rep.quality_user_time = #{qualityUserTime}</if>
            <if test="productionManagerFlag != null  and productionManagerFlag != 9">and rep.production_manager_flag =
                #{productionManagerFlag}
            </if>
            <if test="productionManagerTime != null ">and rep.production_manager_time = #{productionManagerTime}</if>
            <if test="qualityManagerFlag != null  and qualityManagerFlag != 9">and rep.quality_manager_flag =
                #{qualityManagerFlag}
            </if>
            <if test="qualityManagerTime != null ">and rep.quality_manager_time = #{qualityManagerTime}</if>
            <if test="inspectionUserCode != null  and inspectionUserCode != ''">and rep.inspection_user_code =
                #{inspectionUserCode}
            </if>
            <if test="inspectionUserFlag != null  and inspectionUserFlag != ''">and rep.inspection_user_flag =
                #{inspectionUserFlag}
            </if>
            <if test="imageAddress != null  and imageAddress != ''">and rep.image_address = #{imageAddress}</if>
            <if test="createdBy != null  and createdBy != ''">and rep.created_by = #{createdBy}</if>
            <if test="createdTime != null ">and rep.created_time = #{createdTime}</if>
            <if test="updatedBy != null  and updatedBy != ''">and rep.updated_by = #{updatedBy}</if>
            <if test="updatedTime != null ">and rep.updated_time = #{updatedTime}</if>
            <if test="qualityUserFlag == 9 ">and rep.quality_user_flag IS NULL </if>
            <if test="productionManagerFlag == 9 ">and rep.production_manager_flag IS NULL</if>
            <if test="qualityManagerFlag == 9 ">and rep.quality_manager_flag IS NULL</if>
        </where>
        order by rep.apply_time desc
    </select>

    <select id="selectRecordExceptionProcessByObjId" parameterType="Long" resultMap="RecordExceptionProcessResult">
        <include refid="selectRecordExceptionProcessVo"/>
        where rep.obj_id = #{objId}
    </select>

    <select id="checkBoxBarcode" resultType="java.lang.String">
        SELECT MATERIAL_BARCODE FROM  PRINT_BARCODE WHERE MATERIAL_BARCODE = #{boxBarcode}
    </select>


    <insert id="insertRecordExceptionProcess" parameterType="RecordExceptionProcess">
        <selectKey keyProperty="objId" resultType="long" order="BEFORE">
            SELECT seq_record_exception_process.NEXTVAL as objId FROM DUAL
        </selectKey>
        insert into record_exception_process
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="objId != null">obj_id,</if>
            <if test="boxBarcode != null and boxBarcode != ''">box_barcode,</if>
            <if test="productionUserFlag != null">production_user_flag,</if>
            <if test="teamCode != null">team_code,</if>
            <if test="applyReason != null">apply_reason,</if>
            <if test="applyTime != null">apply_time,</if>
            <if test="qualityUserFlag != null">quality_user_flag,</if>
            <if test="failureReason != null">failure_reason,</if>
            <if test="failureType != null">failure_type,</if>
            <if test="qualityUserTime != null">quality_user_time,</if>
            <if test="productionManagerFlag != null">production_manager_flag,</if>
            <if test="productionManagerTime != null">production_manager_time,</if>
            <if test="qualityManagerFlag != null">quality_manager_flag,</if>
            <if test="qualityManagerTime != null">quality_manager_time,</if>
            <if test="inspectionUserCode != null">inspection_user_code,</if>
            <if test="inspectionUserFlag != null">inspection_user_flag,</if>
            <if test="imageAddress != null">image_address,</if>
            <if test="remark != null">remark,</if>
            <if test="createdBy != null">created_by,</if>
            <if test="createdTime != null">created_time,</if>
            <if test="updatedBy != null">updated_by,</if>
            <if test="updatedTime != null">updated_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="objId != null">#{objId},</if>
            <if test="boxBarcode != null and boxBarcode != ''">#{boxBarcode},</if>
            <if test="productionUserFlag != null">#{productionUserFlag},</if>
            <if test="teamCode != null">#{teamCode},</if>
            <if test="applyReason != null">#{applyReason},</if>
            <if test="applyTime != null">#{applyTime},</if>
            <if test="qualityUserFlag != null">#{qualityUserFlag},</if>
            <if test="failureReason != null">#{failureReason},</if>
            <if test="failureType != null">#{failureType},</if>
            <if test="qualityUserTime != null">#{qualityUserTime},</if>
            <if test="productionManagerFlag != null">#{productionManagerFlag},</if>
            <if test="productionManagerTime != null">#{productionManagerTime},</if>
            <if test="qualityManagerFlag != null">#{qualityManagerFlag},</if>
            <if test="qualityManagerTime != null">#{qualityManagerTime},</if>
            <if test="inspectionUserCode != null">#{inspectionUserCode},</if>
            <if test="inspectionUserFlag != null">#{inspectionUserFlag},</if>
            <if test="imageAddress != null">#{imageAddress},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createdBy != null">#{createdBy},</if>
            <if test="createdTime != null">#{createdTime},</if>
            <if test="updatedBy != null">#{updatedBy},</if>
            <if test="updatedTime != null">#{updatedTime},</if>
        </trim>
    </insert>

    <update id="updateRecordExceptionProcess" parameterType="RecordExceptionProcess">
        update record_exception_process
        <trim prefix="SET" suffixOverrides=",">
            <if test="boxBarcode != null and boxBarcode != ''">box_barcode = #{boxBarcode},</if>
            <if test="productionUserFlag != null">production_user_flag = #{productionUserFlag},</if>
            <if test="teamCode != null">team_code = #{teamCode},</if>
            <if test="applyReason != null">apply_reason = #{applyReason},</if>
            <if test="applyTime != null">apply_time = #{applyTime},</if>
            <if test="qualityUserFlag != null">quality_user_flag = #{qualityUserFlag},</if>
            <if test="failureReason != null">failure_reason = #{failureReason},</if>
            <if test="failureType != null">failure_type = #{failureType},</if>
            <if test="qualityUserTime != null">quality_user_time = #{qualityUserTime},</if>
            <if test="productionManagerFlag != null">production_manager_flag = #{productionManagerFlag},</if>
            <if test="productionManagerTime != null">production_manager_time = #{productionManagerTime},</if>
            <if test="qualityManagerFlag != null">quality_manager_flag = #{qualityManagerFlag},</if>
            <if test="qualityManagerTime != null">quality_manager_time = #{qualityManagerTime},</if>
            <if test="inspectionUserCode != null">inspection_user_code = #{inspectionUserCode},</if>
            <if test="inspectionUserFlag != null">inspection_user_flag = #{inspectionUserFlag},</if>
            <if test="imageAddress != null">image_address = #{imageAddress},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createdBy != null">created_by = #{createdBy},</if>
            <if test="createdTime != null">created_time = #{createdTime},</if>
            <if test="updatedBy != null">updated_by = #{updatedBy},</if>
            <if test="updatedTime != null">updated_time = #{updatedTime},</if>
        </trim>
        where obj_id = #{objId}
    </update>

    <delete id="deleteRecordExceptionProcessByObjId" parameterType="Long">
        delete
        from record_exception_process
        where obj_id = #{objId}
    </delete>

    <delete id="deleteRecordExceptionProcessByObjIds" parameterType="String">
        delete from record_exception_process where obj_id in
        <foreach item="objId" collection="array" open="(" separator="," close=")">
            #{objId}
        </foreach>
    </delete>
</mapper>