<?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.api.mapper.PdaBindingMapper">


    <select id="selectBoxNameByCode" resultType="string">
        SELECT BO.MATERIAL_NAME
        FROM PRINT_BARCODE PB
                 LEFT JOIN BASE_ORDERINFO BO ON BO.ORDER_CODE = PB.ORDER_CODE
        WHERE PB.MATERIAL_BARCODE = #{boxCode}
        GROUP BY BO.MATERIAL_NAME
    </select>

    <select id="selectBoxNameBarCode" resultType="string">
        select MATERIAL_NAME from BASE_MATERIALINFO where MATERIAL_CODE = #{boxCode}
    </select>

    <insert id="insertBindingInfo">
        INSERT INTO CODE_BINDING(OBJ_ID, BOX_CODE, BOX_NAME, LINER_CODE, BINDING_RESULT, RECORD_TIME1)
        VALUES (SEQ_CODE_BINDING.nextval, #{boxCode}, #{boxName,jdbcType=NVARCHAR}, #{innerCode}, '成功', SYSDATE)
    </insert>
    <update id="updateCodeBinding">
        UPDATE CODE_BINDING
        SET PRODUCT_CODE = #{innerCode}, BINDING_RESULT = '成功',RECORD_TIME2   = SYSDATE,IS_PASS= 1
        where BOX_CODE = #{boxCode}
    </update>

    <insert id="insertCodeBinding">
        INSERT INTO CODE_BINDING(OBJ_ID, BOX_CODE)
        VALUES (SEQ_CODE_BINDING.nextval, #{boxCode})
    </insert>

    <select id="countQualityRecordByCode" resultType="int">
        select count(OBJ_ID)
        from REPORT_QUALITY_INSPECTION
        where BAR_CODE = #{code} and TREATMENT_MEASURE = 1  and PROCESS_RESULT is null
    </select>
    <update id="submitRepair" parameterType="com.aucma.api.domain.dto.RepairSubmitInfoDto">
        UPDATE REPORT_QUALITY_INSPECTION
        SET PROCESS_RESULT = #{info.repairName},
            REWORK_NUMBER  = 1,
            FINISH_TIME    = sysdate,
            IS_FLAG        = #{info.isFlag},
            UPDATED_BY     = #{info.loginName},
            UPDATED_TIME   = sysdate
        WHERE PROCESS_RESULT IS NULL AND OBJ_ID IN
        <foreach item="objId" collection="info.list" open="(" separator="," close=")">
            #{objId}
        </foreach>

    </update>

    <select id="findCheckInfoByCode" resultMap="com.aucma.report.mapper.ReportQualityInspectionMapper.ReportQualityInspectionResult">
        select OBJ_ID,
               MATERIAL_NAME,
               QUALITY_DEFECT_CODE,
               QUALITY_DEFECT_NAME
        from REPORT_QUALITY_INSPECTION
        where BAR_CODE = #{code}
          AND TREATMENT_MEASURE = '1'
          AND PROCESS_RESULT IS NULL
    </select>

    <select id="checkSelectRepairInfo" resultMap="com.aucma.report.mapper.ReportQualityInspectionMapper.ReportQualityInspectionResult">
        select rqi.OBJ_ID,
               rqi.MATERIAL_NAME,
               rqi.STATION_CODE                as STATION_CODE,
               rqi.QUALITY_DEFECT_CODE,
               nvl(rqi.QUALITY_DEFECT_NAME,'完成') QUALITY_DEFECT_NAME,
               nvl(rqi.PROCESS_RESULT, '未完成') as PROCESS_RESULT,
               br.PRODUCT_LINE_NAME,
               rqi.INSPECTOR_TIME inspector_time
        from REPORT_QUALITY_INSPECTION rqi
                 left join BASE_PRODUCTLINE br on rqi.STATION_CODE=br.PRODUCT_LINE_CODE
        where BAR_CODE =#{code}
          and TREATMENT_MEASURE in (1, 3)
        order by rqi.TREATMENT_MEASURE, rqi.INSPECTOR_TIME
    </select>

    <select id="selectPrintBarcode" resultType="java.util.HashMap">
        SELECT PB.ORDER_CODE,
               PB.MATERIAL_CODE,
               PB.MATERIAL_NAME,
               PB.MATERIAL_BARCODE,
               PB.PRINT_BARTYPE,
               OI.PRINT_NAME,
               NVL(MIN(PS.IS_PRINT), 1) IS_PRINT
        FROM BASE_ORDERINFO OI
                 JOIN PRINT_BARCODE PB ON PB.ORDER_CODE = OI.ORDER_CODE
                 LEFT JOIN PRINT_SUPPLEMENTBARCODE PS ON PS.MATERIAL_BARCODE = PB.MATERIAL_BARCODE
        WHERE PB.MATERIAL_BARCODE = #{boxCode}
        GROUP BY PB.ORDER_CODE, PB.MATERIAL_CODE, PB.MATERIAL_NAME, PB.MATERIAL_BARCODE, PB.PRINT_BARTYPE,
                 OI.PRINT_NAME
    </select>

    <select id="findCheckInfoByCodeUserId" resultMap="com.aucma.report.mapper.ReportQualityInspectionMapper.ReportQualityInspectionResult">
        select OBJ_ID,
               MATERIAL_NAME,
               QUALITY_DEFECT_CODE,
               QUALITY_DEFECT_NAME
        from REPORT_QUALITY_INSPECTION
        where BAR_CODE = #{code}
          AND TREATMENT_MEASURE = '1'
    </select>
    <select id="downgradedProductCardControl" resultType="java.util.HashMap">
        SELECT REP.BOX_BARCODE, REP.PRODUCTION_USER_FLAG
        FROM REPORT_QUALITY_INSPECTION RQI
                 LEFT JOIN RECORD_EXCEPTION_PROCESS REP ON  REP.BOX_BARCODE =  RQI.BAR_CODE
        WHERE REP.PRODUCTION_USER_FLAG = '9' AND RQI.OBJ_ID = #{objId}
    </select>

    <select id="messageNotifications" resultType="java.util.HashMap">
        SELECT rep.OBJ_ID,
               rep.BOX_BARCODE,
               NVL(rep.INSPECTION_USER_CODE, 'NULL') INSPECTION_USER_CODE,
               NVL(bt.TEAM_NAME, '')                 TEAM_NAME
        FROM RECORD_EXCEPTION_PROCESS rep
                 left join BASE_TEAMMEMBERS bt on bt.TEAM_CODE = rep.TEAM_CODE
        WHERE rep.PRODUCTION_USER_FLAG = '9'
          AND rep.QUALITY_MANAGER_TIME > (SYSDATE - 5.25)
          AND (rep.INSPECTION_USER_CODE IS NULL OR rep.INSPECTION_USER_CODE NOT LIKE '%' || #{userName} || '%')
    </select>
    <select id="checkShellBinding" resultType="java.util.HashMap">
        select BOX_CODE,
               BOX_NAME,
               PRODUCT_CODE,
               BINDING_RESULT,
               RECORD_TIME1,
               RECORD_TIME2,
               LINER_CODE
        from CODE_BINDING S
        WHERE LINER_CODE IS NOT NULL
          AND BOX_CODE = #{boxCode}
    </select>


    <update id="unbindBarCode">
        UPDATE CODE_BINDING
        SET PRODUCT_CODE = NULL, RECORD_TIME2 = NULL
        WHERE BOX_CODE = #{boxCode}
    </update>


    <insert id="printSupplementBarcode">
        INSERT INTO PRINT_SUPPLEMENTBARCODE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ORDER_CODE != null">ORDER_CODE,</if>
            <if test="MATERIAL_CODE != null">MATERIAL_CODE,</if>
            <if test="MATERIAL_NAME != null">MATERIAL_NAME,</if>
            <if test="MATERIAL_BARCODE != null">MATERIAL_BARCODE,</if>
            <if test="PRINT_BARTYPE != null">PRINT_BARTYPE,</if>
            <if test="PRINT_NAME != null">PRINT_NAME,</if>
            <if test="print_reason != null">print_reason,</if>
            <if test="CREATE_BY != null">CREATE_BY,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="ORDER_CODE != null">#{ORDER_CODE},</if>
            <if test="MATERIAL_CODE != null">#{MATERIAL_CODE},</if>
            <if test="MATERIAL_NAME != null">#{MATERIAL_NAME},</if>
            <if test="MATERIAL_BARCODE != null">#{MATERIAL_BARCODE},</if>
            <if test="PRINT_BARTYPE != null">#{PRINT_BARTYPE},</if>
            <if test="PRINT_NAME != null">#{PRINT_NAME},</if>
            <if test="print_reason != null">#{print_reason},</if>
            <if test="CREATE_BY != null">#{CREATE_BY},</if>
        </trim>
    </insert>

</mapper>