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


    <select id="inventoryRateReportList" resultType="com.aucma.report.domain.InventoryRateReport" parameterType="java.util.HashMap">
        SELECT boi.ORDER_CODE,
        boi.BEGIN_DATE,
        boi.MATERIAL_CODE,
        boi.MATERIAL_NAME,
        boi.ORDER_AMOUNT,
        NVL(boi.COMPLETE_AMOUNT, 0) COMPLETE_AMOUNT,
        ROUND(NVL(boi.COMPLETE_AMOUNT, 0) / boi.ORDER_AMOUNT * 100, 2) || '%' ORDER_RATE,
        PL.WORK_CENTER_CODE,
        PL.PRODUCT_LINE_NAME
        FROM BASE_ORDERINFO boi
        LEFT JOIN BASE_PRODUCTLINE PL ON PL.WORK_CENTER_CODE = boi.WORK_CENTER_CODE
        WHERE boi.MATERIAL_CODE LIKE '90%'
        <if test="WORK_CENTER_CODE != null and WORK_CENTER_CODE != ''">
            AND boi.WORK_CENTER_CODE = #{WORK_CENTER_CODE}
        </if>
        <if test="MATERIAL_CODE != null  and MATERIAL_CODE != ''">
            and boi.MATERIAL_CODE like concat(#{MATERIAL_CODE}, '%')
        </if>
        <if test="MATERIAL_NAME != null  and MATERIAL_NAME != ''">
            and boi.MATERIAL_NAME like concat(concat('%',#{MATERIAL_NAME}), '%')
        </if>
        <if test="ORDER_CODE != null  and ORDER_CODE != ''">
            and boi.ORDER_CODE like concat(concat('%',#{ORDER_CODE}), '%')
        </if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        ORDER BY ROUND(NVL(boi.COMPLETE_AMOUNT, 0) / boi.ORDER_AMOUNT * 100, 2)
    </select>

    <select id="productionExecutionReportList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT B2.PRODUCT_LINE_CODE,
        B2.PRODUCT_LINE_NAME,
        VMC.ORDER_CODE,
        BP.PRODUCT_LINE_CODE STATION_CODE,
        BP.PRODUCT_LINE_NAME STATION_NAME,
        VMC.MATERIAL_CODE,
        BM.MATERIAL_NAME,
        BO.MATERIAL_CODE PRODUCT_CODE,
        BO.MATERIAL_NAME PRODUCT_NAME,
        COUNT(*) COMP_AMOUNT
        FROM VIEW_MATERIAL_COMPLETION VMC
        LEFT JOIN BASE_PRODUCTLINE BP ON BP.PRODUCT_LINE_CODE = VMC.STATION_NAME
        LEFT JOIN BASE_PRODUCTLINE B2 ON B2.PRODUCT_LINE_CODE = BP.PARENT_ID
        LEFT JOIN BASE_ORDERINFO BO ON BO.ORDER_CODE = VMC.ORDER_CODE
        LEFT JOIN BASE_MATERIALINFO BM ON BM.MATERIAL_CODE = VMC.MATERIAL_CODE
        <where>
            <if test="productLineCode != null and productLineCode != ''">
                AND VMC.PRODUCT_LINE_CODE = #{productLineCode}
            </if>
            <if test="stationCode != null  and stationCode != ''">
                and VMC.STATION_NAME = #{stationCode}
            </if>
            <if test="ORDER_CODE != null  and ORDER_CODE != ''">
                and VMC.ORDER_CODE = #{ORDER_CODE}
            </if>
            <if test="PRODUCT_CODE != null  and PRODUCT_CODE != ''">
                and BO.MATERIAL_CODE like concat(#{PRODUCT_CODE}, '%')
            </if>
            <if test="PRODUCT_NAME != null  and PRODUCT_NAME != ''">
                and BO.MATERIAL_NAME like concat(concat('%', #{PRODUCT_NAME}), '%')
            </if>
            <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
                AND TO_CHAR(VMC.COMPLETE_DATE, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
            </if>
        </where>
        GROUP BY B2.PRODUCT_LINE_CODE, B2.PRODUCT_LINE_NAME, VMC.ORDER_CODE, BO.MATERIAL_CODE, BP.PRODUCT_LINE_CODE,
        BP.PRODUCT_LINE_NAME, VMC.MATERIAL_CODE, BM.MATERIAL_NAME, BO.MATERIAL_NAME
        ORDER BY B2.PRODUCT_LINE_CODE, VMC.ORDER_CODE, BP.PRODUCT_LINE_CODE
    </select>

    <select id="WIPInquiryReportList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT B2.PRODUCT_LINE_CODE,
        B2.PRODUCT_LINE_NAME,
        SD.ORDER_CODE,
        SD.MATERIAL_CODE,
        SD.MATERIAL_NAME,
        BO.MATERIAL_CODE PRODUCT_CODE,
        BO.MATERIAL_NAME PRODUCT_NAME,
        COUNT(*)         COMP_AMOUNT
        FROM (
        SELECT PB.ORDER_CODE, VMC.MATERIAL_BARCODE, BM.MATERIAL_CODE, BM.MATERIAL_NAME
        FROM C##AUCMA_SCADA.MATERIAL_COMPLETION VMC
        LEFT JOIN BASE_PRODUCTLINE BP ON BP.PRODUCT_LINE_CODE = VMC.STATION_NAME
        LEFT JOIN C##AUCMA_SCADA.PRINT_BARCODE PB ON VMC.MATERIAL_BARCODE = PB.MATERIAL_BARCODE AND PRINT_BARTYPE = 1
        LEFT JOIN C##AUCMA_SCADA.PRODUCT_OFFLINE PO ON PO.BOX_CODE = PB.MATERIAL_BARCODE
        LEFT JOIN BASE_MATERIALINFO BM ON BM.MATERIAL_CODE = PB.MATERIAL_CODE
        WHERE PB.MATERIAL_BARCODE IS NOT NULL
        AND PO.BOX_CODE IS NULL
        AND VMC.COMPLETE_DATE > (SYSDATE - 3)
        <if test="beginStationCode != null  and beginStationCode != ''">
            AND BP.EXECUTION_SORT >= #{beginStationCode}
        </if>
        <if test="endStationCode != null  and endStationCode != ''">
            AND #{endStationCode} > BP.EXECUTION_SORT
        </if>
        GROUP BY PB.ORDER_CODE, VMC.MATERIAL_BARCODE, BM.MATERIAL_CODE, BM.MATERIAL_NAME
        ) SD
        LEFT JOIN BASE_ORDERINFO BO ON BO.ORDER_CODE = SD.ORDER_CODE
        LEFT JOIN BASE_PRODUCTLINE B2 ON B2.WORK_CENTER_CODE = BO.WORK_CENTER_CODE
        LEFT JOIN BASE_MATERIALINFO BM ON BM.MATERIAL_CODE = BO.MATERIAL_CODE
        <where>
            <if test="productLineCode != null and productLineCode != ''">
                AND B2.PRODUCT_LINE_CODE = #{productLineCode}
            </if>
            <if test="ORDER_CODE != null  and ORDER_CODE != ''">
                and SD.ORDER_CODE = #{ORDER_CODE}
            </if>
            <if test="PRODUCT_CODE != null  and PRODUCT_CODE != ''">
                and BO.MATERIAL_CODE = #{PRODUCT_CODE}
            </if>
            <if test="PRODUCT_NAME != null  and PRODUCT_NAME != ''">
                and BO.MATERIAL_NAME like concat(concat('%', #{PRODUCT_NAME}), '%')
            </if>
        </where>
        GROUP BY B2.PRODUCT_LINE_CODE, B2.PRODUCT_LINE_NAME, SD.ORDER_CODE, SD.MATERIAL_CODE, SD.MATERIAL_NAME,
        BO.MATERIAL_CODE, BO.MATERIAL_NAME
        ORDER BY COMP_AMOUNT DESC
    </select>

    <select id="qualityDefectsStatisticalAnalysisList"
            resultType="com.aucma.report.domain.QualityDefectsStatisticalAnalysis" parameterType="java.util.HashMap">
        SELECT FACTORY_NAME,
        PRODUCT_LINE_NAME,
        STATION_CODE,
        STATION_NAME,
        COUNT(*) INSPECTION_NUMBER,
        SUM(REWORK_NUMBER) REWORK_NUMBER,
        ROUND((1 - SUM(REWORK_NUMBER) / COUNT(*)) * 100, 2) || '%' RATE
        FROM (
        SELECT FY.FACTORY_NAME,
        BPP.PRODUCT_LINE_NAME,
        BP.PRODUCT_LINE_CODE STATION_CODE,
        BP.PRODUCT_LINE_NAME STATION_NAME,
        RQI.BAR_CODE,
        MAX(CASE WHEN RQI.TREATMENT_MEASURE = '1' THEN 1 ELSE 0 END) REWORK_NUMBER
        FROM REPORT_QUALITY_INSPECTION RQI
        LEFT JOIN BASE_PRODUCTLINE BP
        ON BP.PRODUCT_LINE_CODE = RQI.STATION_CODE
        LEFT JOIN BASE_PRODUCTLINE BPP ON BPP.PRODUCT_LINE_CODE = BP.PARENT_ID
        LEFT JOIN BASE_FACTORY FY ON FY.FACTORY_CODE = BPP.PLANT_CODE
        <where>
            <if test="PRODUCT_LINE_CODE != null  and PRODUCT_LINE_CODE != ''">
                AND BPP.PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
            </if>
            <if test="stationCodeList != null and stationCodeList != ''">
                AND BP.PRODUCT_LINE_CODE IN
                <foreach collection="stationCodeList" item="stationCode" open="(" separator="," close=")">
                    #{stationCode}
                </foreach>
            </if>
            <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
                AND TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
            </if>
        </where>
        GROUP BY FY.FACTORY_NAME, BPP.PRODUCT_LINE_NAME, BP.PRODUCT_LINE_CODE, BP.PRODUCT_LINE_NAME,
        RQI.BAR_CODE
        )
        GROUP BY FACTORY_NAME, PRODUCT_LINE_NAME, STATION_CODE, STATION_NAME
        ORDER BY REWORK_NUMBER
    </select>
    <select id="fewerMistakesReportList" resultType="com.aucma.report.domain.FewerMistakesReport"
            parameterType="java.util.HashMap">
        SELECT FY.FACTORY_NAME,
        BPP.PRODUCT_LINE_NAME,
        TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD') INSPECTOR_TIME,
        RQI.STATION_CODE,
        BP.PRODUCT_LINE_NAME STATION_NAME,
        GET_MATERIAL_TYPE(RQI.MATERIAL_NAME) MATERIAL_MODEL,
        RQI.QUALITY_DEFECT_CODE,
        RQI.QUALITY_DEFECT_NAME,
        COUNT(*) DEFECT_NUMBER
        FROM REPORT_QUALITY_INSPECTION RQI
        LEFT JOIN BASE_PRODUCTLINE BP ON BP.PRODUCT_LINE_CODE = RQI.STATION_CODE
        LEFT JOIN BASE_PRODUCTLINE BPP ON BPP.PRODUCT_LINE_CODE = BP.PARENT_ID
        LEFT JOIN BASE_FACTORY FY ON FY.FACTORY_CODE = BPP.PLANT_CODE
        WHERE RQI.TREATMENT_MEASURE = 1
        AND (RQI.QUALITY_DEFECT_NAME LIKE '%少%'
        OR RQI.QUALITY_DEFECT_NAME LIKE '%错%')
        <if test="PRODUCT_LINE_CODE != null  and PRODUCT_LINE_CODE != ''">
            AND BPP.PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
        </if>
        <if test="STATION_CODE != null  and STATION_CODE != ''">
            AND RQI.STATION_CODE = #{STATION_CODE}
        </if>
        <if test="MATERIAL_MODEL != null  and MATERIAL_MODEL != ''">
            AND RQI.MATERIAL_NAME LIKE '%' || #{MATERIAL_MODEL} || '%'
        </if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        GROUP BY FY.FACTORY_NAME, BPP.PRODUCT_LINE_NAME, TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD'),
        RQI.STATION_CODE, BP.PRODUCT_LINE_NAME, GET_MATERIAL_TYPE(RQI.MATERIAL_NAME),
        RQI.QUALITY_DEFECT_CODE, RQI.QUALITY_DEFECT_NAME
        ORDER BY DEFECT_NUMBER DESC
    </select>
    <select id="qualityIssuesReportList" resultType="com.aucma.report.domain.QualityIssuesReport"
            parameterType="java.util.HashMap">
        SELECT ZSD.FACTORY_NAME,
        ZSD.PRODUCT_LINE_CODE,
        ZSD.PRODUCT_LINE_NAME,
        ZSD.MATERIAL_MODEL,
        ZSD.QUALITY_DEFECT_CODE,
        ZSD.QUALITY_DEFECT_NAME,
        COUNT(*) Q_SUM,
        TO_CHAR(ROUND(COUNT(*) / OS.OFFLINE_NUM * 100, 2), 'FM99990.00') || '%' OFFLINE_NUM
        FROM (
        SELECT BF.FACTORY_NAME,
        PL2.PRODUCT_LINE_CODE,
        PL2.PRODUCT_LINE_NAME,
        GET_MATERIAL_TYPE(RQI.MATERIAL_NAME) MATERIAL_MODEL,
        RQI.BAR_CODE,
        RQI.QUALITY_DEFECT_CODE,
        QI.QUALITY_DEFECT_NAME
        FROM REPORT_QUALITY_INSPECTION RQI
        LEFT JOIN BASE_PRODUCTLINE PL on RQI.STATION_CODE = PL.PRODUCT_LINE_CODE
        LEFT JOIN BASE_PRODUCTLINE PL2 on PL2.PRODUCT_LINE_CODE = PL.PARENT_ID
        LEFT JOIN BASE_FACTORY BF on BF.FACTORY_CODE = PL.PLANT_CODE
        LEFT JOIN BASE_QUALITY_INSPECTION_ITEM QI on QI.QUALITY_DEFECT_CODE = RQI.QUALITY_DEFECT_CODE
        WHERE RQI.TREATMENT_MEASURE = '1'
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        <if test="PRODUCT_LINE_CODE != null  and PRODUCT_LINE_CODE != ''">
            AND PL2.PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
        </if>
        <if test="MATERIAL_MODEL != null  and MATERIAL_MODEL != ''">
            AND RQI.MATERIAL_NAME LIKE '%' || #{MATERIAL_MODEL} || '%'
        </if>
        GROUP BY BF.FACTORY_NAME, PL2.PRODUCT_LINE_CODE, PL2.PRODUCT_LINE_NAME,
        GET_MATERIAL_TYPE(RQI.MATERIAL_NAME),
        RQI.BAR_CODE, RQI.QUALITY_DEFECT_CODE, QI.QUALITY_DEFECT_NAME
        ) ZSD
        CROSS JOIN (SELECT COUNT(*) OFFLINE_NUM
        FROM C##AUCMA_SCADA.PRODUCT_OFFLINE
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            WHERE TO_CHAR(PRODUCT_SCANTIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        ) OS
        GROUP BY ZSD.FACTORY_NAME, ZSD.PRODUCT_LINE_CODE, ZSD.PRODUCT_LINE_NAME,
        ZSD.MATERIAL_MODEL, ZSD.QUALITY_DEFECT_CODE, ZSD.QUALITY_DEFECT_NAME, OS.OFFLINE_NUM
        ORDER BY ZSD.PRODUCT_LINE_CODE, Q_SUM DESC
    </select>
    <select id="ScanOfflineRecordReportList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT *
        FROM VIEW_PRODUCT_OFFLINE
    </select>
    <select id="temperatureRecordReportList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT *
        FROM VIEW_RECORD_TEMPERATURE
    </select>
    <select id="pourRecordReportList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT *
        FROM VIEW_PERFUSION_RECORD
    </select>

    <select id="qualityDefectsDetailList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        WITH TotalDefects AS (
        SELECT
        COUNT(*) AS ALL_DEFECT
        FROM
        REPORT_QUALITY_INSPECTION RQI
        WHERE
        RQI.TREATMENT_MEASURE = 1
        <if test="stationCode != null  and stationCode != ''">
            AND RQI.STATION_CODE = #{stationCode}
        </if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        )
        SELECT
        ROW_NUMBER() OVER (ORDER BY RATE) AS SEQ,
        SD.QUALITY_DEFECT_NAME,
        SD.DEFECT_NUMBER,
        RATE
        FROM (
        SELECT
        DA.QUALITY_DEFECT_NAME,
        COUNT(*) AS DEFECT_NUMBER,
        TO_CHAR(ROUND(COUNT(*) / TD.ALL_DEFECT * 100, 2), 'FM99990.00') || '%' AS RATE
        FROM (
        SELECT
        RQI.QUALITY_DEFECT_NAME,
        RQI.BAR_CODE,
        RQI.QUALITY_DEFECT_CODE
        FROM
        REPORT_QUALITY_INSPECTION RQI
        WHERE
        RQI.TREATMENT_MEASURE = 1
        <if test="stationCode != null  and stationCode != ''">
            AND RQI.STATION_CODE = #{stationCode}
        </if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(RQI.INSPECTOR_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        GROUP BY
        RQI.QUALITY_DEFECT_NAME, RQI.BAR_CODE, RQI.QUALITY_DEFECT_CODE
        ) DA
        CROSS JOIN TotalDefects TD
        GROUP BY
        DA.QUALITY_DEFECT_NAME, TD.ALL_DEFECT
        ) SD
        ORDER BY RATE
    </select>

    <select id="productTraceabilityReportList" resultType="com.aucma.report.domain.ProductTraceabilityReport"
            parameterType="java.util.HashMap">
        SELECT PL.PRODUCT_LINE_CODE,
        PL.PRODUCT_LINE_NAME,
        BO.ORDER_CODE,
        BO.SALEORDER_CODE,
        BO.SALEORDER_LINENUMBER,
        BO.MATERIAL_CODE,
        BO.MATERIAL_NAME,
        PO.PRODUCT_SNCODE,
        PO.BOX_CODE,
        PO.PRODUCT_SCANTIME
        FROM C##AUCMA_SCADA.PRODUCT_OFFLINE PO
        LEFT JOIN BASE_ORDERINFO BO ON BO.ORDER_CODE = SUBSTR(PO.PRODUCT_ORDER_NO, -8)
        LEFT JOIN BASE_PRODUCTLINE PL ON PL.WORK_CENTER_CODE = BO.WORK_CENTER_CODE
        <where>
            <if test="PRODUCT_LINE_CODE != null and PRODUCT_LINE_CODE != ''">and PO.PRODUCT_LINE_CODE =
                #{PRODUCT_LINE_CODE}
            </if>
            <if test="ORDER_CODE != null and ORDER_CODE != ''">and BO.ORDER_CODE = #{ORDER_CODE}</if>
            <if test="MATERIAL_CODE != null and MATERIAL_CODE != ''">and BO.MATERIAL_CODE = #{MATERIAL_CODE}</if>
            <if test="MATERIAL_NAME != null and MATERIAL_NAME != ''">and BO.MATERIAL_NAME LIKE '%' || #{MATERIAL_NAME}
                || '%'
            </if>
            <if test="PRODUCT_SNCODE != null and PRODUCT_SNCODE != ''">and PO.PRODUCT_SNCODE LIKE '%' ||
                #{PRODUCT_SNCODE} || '%'
            </if>
            <if test="BOX_CODE != null and BOX_CODE != ''">and PO.BOX_CODE = #{BOX_CODE}</if>
            <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
                AND TO_CHAR(PO.PRODUCT_SCANTIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
            </if>
        </where>
    </select>

    <select id="productTraceabilityDetailList" resultType="java.util.HashMap">
        SELECT RQI.BAR_CODE,
               PL.EXECUTION_SORT,
               PL.PRODUCT_LINE_CODE,
               PL.PRODUCT_LINE_NAME,
               RQI.TREATMENT_MEASURE,
               RQI.PROCESS_RESULT,
               RQI.INSPECTOR_TIME,
               U.NICK_NAME USER_NAME
        FROM C##AUCMA_SCADA.PRODUCT_OFFLINE PO
                 LEFT JOIN C##AUCMA_SCADA.CODE_BINDING CB ON PO.BOX_CODE = CB.BOX_CODE
                 JOIN REPORT_QUALITY_INSPECTION RQI ON ((RQI.BAR_CODE = PO.BOX_CODE) OR (RQI.BAR_CODE = CB.LINER_CODE))
                 LEFT JOIN BASE_PRODUCTLINE PL on RQI.STATION_CODE = PL.PRODUCT_LINE_CODE
                 LEFT JOIN SYS_USER U on U.USER_NAME = RQI.INSPECTOR_CODE
        WHERE PO.BOX_CODE = #{BOX_CODE}
        GROUP BY RQI.BAR_CODE, PL.EXECUTION_SORT, PL.PRODUCT_LINE_CODE, PL.PRODUCT_LINE_NAME, RQI.TREATMENT_MEASURE,
                 RQI.PROCESS_RESULT, RQI.INSPECTOR_TIME, U.NICK_NAME
        ORDER BY PL.EXECUTION_SORT, RQI.INSPECTOR_TIME
    </select>

    <select id="boxTraceabilityReport" resultType="com.aucma.report.domain.BoxTraceabilityReport"
            parameterType="java.util.HashMap">
        SELECT ZSD.ORDER_CODE,
        ZSD.SALEORDER_CODE,
        ZSD.SALEORDER_LINENUMBER,
        ZSD.MATERIAL_CODE,
        ZSD.MATERIAL_MODEL,
        ZSD.ORDER_AMOUNT,
        SUM(ZSD.BEFORE_NUMBER) BEFORE_NUMBER,
        SUM(ZSD.SCAN_NUMBER) SCAN_NUMBER,
        SUM(ZSD.BEFORE_NUMBER) - SUM(ZSD.SCAN_NUMBER) DIFF_NUMBER,
        SUM(ZSD.SCRAP_NUMBER) SCRAP_NUMBER
        FROM (SELECT BO.ORDER_CODE,
        BO.SALEORDER_CODE,
        BO.SALEORDER_LINENUMBER,
        BO.MATERIAL_CODE,
        BM.MATERIAL_SPECIFICATIONS MATERIAL_MODEL,
        OM.BARCODE_CODE,
        BO.ORDER_AMOUNT,
        COUNT(*) BEFORE_NUMBER,
        MAX(CASE WHEN OL.BOX_CODE IS NULL THEN 0 ELSE 1 END) SCAN_NUMBER,
        MAX(CASE WHEN BF.BAR_CODE IS NULL THEN 0 ELSE 1 END) SCRAP_NUMBER
        FROM BASE_ORDERINFO BO
        JOIN BASE_MATERIALINFO BM ON BM.MATERIAL_CODE = BO.MATERIAL_CODE AND bm.DISPO IN ('100')
        JOIN (SELECT OI.ORDER_CODE, OI.WORK_CENTER_CODE, BC.MATERIAL_CODE, IE.BARCODE_CODE, MAX(IE.IN_STORE_TIME)
        IN_STORE_TIME
        FROM RECORD_INSTORE IE
        JOIN C##AUCMA_SCADA.PRINT_BARCODE BC ON BC.MATERIAL_BARCODE = IE.BARCODE_CODE
        JOIN BASE_ORDERINFO OI ON OI.ORDER_CODE = BC.ORDER_CODE
        JOIN BASE_PRODUCTLINE PL ON PL.WORK_CENTER_CODE = OI.WORK_CENTER_CODE
        WHERE IE.STORE_CODE = 'PQK-001'
        <if test="productLineCode != null and productLineCode != ''">AND PL.PRODUCT_LINE_CODE = #{productLineCode}</if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(IE.IN_STORE_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        <if test="productOrderNo != null and productOrderNo != ''">and BC.ORDER_CODE = #{productOrderNo}</if>
        GROUP BY OI.ORDER_CODE, OI.WORK_CENTER_CODE, BC.MATERIAL_CODE, IE.BARCODE_CODE) OM ON OM.ORDER_CODE =
        BO.ORDER_CODE
        LEFT JOIN C##AUCMA_SCADA.PRODUCT_OFFLINE OL ON OL.BOX_CODE = OM.BARCODE_CODE
        LEFT JOIN (SELECT BAR_CODE, SUM(SCRAP_NUMBER) SCRAP_NUMBER
        FROM (SELECT QI.BAR_CODE,
        COUNT(*) SCRAP_NUMBER
        FROM REPORT_QUALITY_INSPECTION QI
        WHERE QI.PROCESS_RESULT = '报废重做'
        GROUP BY QI.BAR_CODE)
        GROUP BY BAR_CODE) BF ON BF.BAR_CODE = OM.BARCODE_CODE
        <where>
            <if test="productCode != null and productCode != ''">and BO.MATERIAL_CODE = #{productCode}</if>
            <if test="productModel != null and productModel != ''">and BM.MATERIAL_SPECIFICATIONS LIKE '%' ||
                #{productModel}
                || '%'
            </if>
        </where>
        GROUP BY BO.ORDER_CODE, BO.SALEORDER_CODE, BO.SALEORDER_LINENUMBER, BO.MATERIAL_CODE,
        BM.MATERIAL_SPECIFICATIONS,
        BO.ORDER_AMOUNT, OM.BARCODE_CODE) ZSD
        GROUP BY ZSD.ORDER_CODE, ZSD.SALEORDER_CODE, ZSD.SALEORDER_LINENUMBER, ZSD.MATERIAL_CODE,
        ZSD.MATERIAL_MODEL, ZSD.ORDER_AMOUNT
    </select>

    <select id="boxTraceabilityDetailReport" resultType="com.aucma.report.domain.BoxTraceabilityDetailReport"
            parameterType="java.util.HashMap">
        SELECT SD.ORDER_CODE,
        SD.SALEORDER_CODE,
        SD.SALEORDER_LINENUMBER,
        SD.MATERIAL_CODE,
        SD.MATERIAL_MODEL,
        SD.BARCODE_CODE BOX_CODE,
        SD.ORDER_AMOUNT,
        SD.BEFORE_NUMBER,
        SD.SCAN_NUMBER,
        CASE WHEN SD.SCRAP_NUMBER = 0 THEN '否' ELSE '是' END SCRAP_FLAG,
        SD.IN_STORE_TIME,
        SD.EXECUTION_SORT,
        PL.PRODUCT_LINE_NAME SCAN_SPOT
        FROM (SELECT BO.ORDER_CODE,
        BO.SALEORDER_CODE,
        BO.SALEORDER_LINENUMBER,
        OM.MATERIAL_CODE,
        BM.MATERIAL_SPECIFICATIONS AS MATERIAL_MODEL,
        OM.BARCODE_CODE,
        OM.IN_STORE_TIME,
        BO.ORDER_AMOUNT,
        COUNT(*) AS BEFORE_NUMBER,
        MAX(CASE WHEN OL.BOX_CODE IS NULL THEN 0 ELSE 1 END) AS SCAN_NUMBER,
        MAX(CASE WHEN QI.BAR_CODE IS NULL THEN 0 ELSE 1 END) AS SCRAP_NUMBER,
        MAX(PL.EXECUTION_SORT) EXECUTION_SORT
        FROM BASE_ORDERINFO BO
        JOIN BASE_MATERIALINFO BM
        ON BM.MATERIAL_CODE = BO.MATERIAL_CODE AND bm.DISPO IN ('100')
        JOIN (SELECT OI.ORDER_CODE,
        OI.WORK_CENTER_CODE,
        BC.MATERIAL_CODE,
        IE.BARCODE_CODE,
        MAX(IE.IN_STORE_TIME) IN_STORE_TIME
        FROM RECORD_INSTORE IE
        JOIN C##AUCMA_SCADA.PRINT_BARCODE BC
        ON BC.MATERIAL_BARCODE = IE.BARCODE_CODE
        JOIN BASE_ORDERINFO OI ON OI.ORDER_CODE = BC.ORDER_CODE
        JOIN BASE_PRODUCTLINE PL ON PL.WORK_CENTER_CODE = OI.WORK_CENTER_CODE
        WHERE IE.STORE_CODE = 'PQK-001'
        <if test="productLineCode != null and productLineCode != ''">AND PL.PRODUCT_LINE_CODE = #{productLineCode}</if>
        <if test="beginBeginTime != null  and beginBeginTime != '' and endBeginTime != null  and endBeginTime != ''">
            AND TO_CHAR(IE.IN_STORE_TIME, 'YYYY-MM-DD HH24:MI:SS') BETWEEN #{beginBeginTime} AND #{endBeginTime}
        </if>
        <if test="productOrderNo != null and productOrderNo != ''">and BC.ORDER_CODE = #{productOrderNo}</if>
        <if test="orderCode != null and orderCode != ''">and BC.ORDER_CODE = #{orderCode}</if>
        GROUP BY OI.ORDER_CODE, OI.WORK_CENTER_CODE, BC.MATERIAL_CODE,
        IE.BARCODE_CODE) OM
        ON OM.ORDER_CODE = BO.ORDER_CODE AND OM.WORK_CENTER_CODE = BO.WORK_CENTER_CODE
        LEFT JOIN C##AUCMA_SCADA.PRODUCT_OFFLINE OL ON OL.BOX_CODE = OM.BARCODE_CODE
        LEFT JOIN REPORT_QUALITY_INSPECTION QI
        ON QI.PROCESS_RESULT = '报废重做' AND QI.BAR_CODE = OM.BARCODE_CODE
        LEFT JOIN C##AUCMA_SCADA.MATERIAL_COMPLETION MC
        ON MC.MATERIAL_BARCODE = OM.BARCODE_CODE
        LEFT JOIN BASE_PRODUCTLINE PL ON PL.PRODUCT_LINE_CODE = MC.STATION_NAME
        WHERE OL.BOX_CODE IS NULL
        <if test="productCode != null and productCode != ''">and BO.MATERIAL_CODE = #{productCode}</if>
        <if test="productModel != null and productModel != ''">and BM.MATERIAL_SPECIFICATIONS LIKE '%' ||
            #{productModel}
            || '%'
        </if>
        GROUP BY BO.ORDER_CODE, BO.SALEORDER_CODE, BO.SALEORDER_LINENUMBER, OM.MATERIAL_CODE,
        BM.MATERIAL_SPECIFICATIONS,
        BO.ORDER_AMOUNT, OM.BARCODE_CODE, OM.IN_STORE_TIME) SD
        LEFT JOIN BASE_PRODUCTLINE PL ON PL.EXECUTION_SORT = SD.EXECUTION_SORT
        ORDER BY PL.EXECUTION_SORT
    </select>

</mapper>
