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


    <select id="generalInterfaceList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT *
        FROM TABLE(${method})
    </select>

    <select id="homePageInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT ROWNUM ROWSEQ, SD.*
        FROM (
                 select bi.OBJ_ID,

                        boi.FACTORY_CODE,
                        bf.FACTORY_NAME,
                        bi.PRODUCT_LINE_NAME,
                        boi.ORDER_CODE,
                        boi.SALEORDER_CODE,
                        boi.MATERIAL_CODE,
                        boi.MATERIAL_NAME,
                        TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')                         PLAN_DATE,
                        CASE
                            WHEN TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') THEN 0
                            ELSE 1 END                                                STATUS,
                        boi.ORDER_AMOUNT,
                        boi.COMPLETE_AMOUNT,
                        ROUND(boi.COMPLETE_AMOUNT / boi.ORDER_AMOUNT * 100, 0) || '%' RATE
                 from BASE_ORDERINFO boi
                          JOIN BASE_MATERIALINFO bm ON bm.MATERIAL_CODE = boi.material_code AND bm.DISPO IN ('100')
                          left join BASE_PRODUCTLINE bi ON bi.WORK_CENTER_CODE = boi.WORK_CENTER_CODE
                          left join BASE_FACTORY bf ON bf.FACTORY_CODE = bi.PLANT_CODE
                 WHERE TO_CHAR(SYSDATE, 'YYYY-MM-DD') >= TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')
                   AND TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 7, 'YYYY-MM-DD')
                 ORDER BY boi.BEGIN_DATE DESC, RATE
             ) SD
    </select>
    <select id="homeColumnarInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT PL.PRODUCT_LINE_CODE,
        day_of_week                                        BEGIN_DATE,
        NVL(OFFLINE_SUM, 0)                                COMPLETE_AMOUNT,
        ROUND(NVL(OFFLINE_SUM / ORDER_AMOUNT * 100, 0), 2) RATE
        FROM (
        SELECT day_of_week, PRODUCT_LINE_CODE
        FROM (
        SELECT TO_CHAR(TRUNC(SYSDATE, 'IW') + LEVEL - 1, 'YYYY-MM-DD') AS day_of_week
        FROM DUAL
        CONNECT BY LEVEL &lt;= 7) WK
        CROSS JOIN BASE_PRODUCTLINE
        WHERE PRODUCT_LINE_TYPE = 1) PL
        LEFT JOIN (SELECT PRODUCT_LINE_CODE,
        TO_CHAR(PRODUCT_SCANTIME, 'YYYY-MM-DD') PRODUCT_SCANTIME,
        COUNT(*)                                OFFLINE_SUM
        FROM PRODUCT_OFFLINE
        GROUP BY PRODUCT_LINE_CODE, TO_CHAR(PRODUCT_SCANTIME, 'YYYY-MM-DD')) OL
        ON PL.PRODUCT_LINE_CODE = OL.PRODUCT_LINE_CODE AND
        PL.day_of_week = OL.PRODUCT_SCANTIME
        LEFT JOIN (SELECT PL.PRODUCT_LINE_CODE,
        TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') BEGIN_DATE,
        SUM(ORDER_AMOUNT)                     ORDER_AMOUNT
        FROM BASE_ORDERINFO boi
        JOIN BASE_MATERIALINFO bm ON bm.MATERIAL_CODE = boi.material_code AND bm.DISPO IN ('100')
        JOIN BASE_PRODUCTLINE PL ON PL.WORK_CENTER_CODE = boi.WORK_CENTER_CODE
        GROUP BY PL.PRODUCT_LINE_CODE, TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')) OD
        ON OD.PRODUCT_LINE_CODE = OL.PRODUCT_LINE_CODE AND PL.day_of_week = OD.BEGIN_DATE
        ORDER BY PRODUCT_LINE_CODE,BEGIN_DATE
    </select>
    <select id="homeLineInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') BEGIN_DATE,
        bm.PRODUCT_LINE_CODE,
        ROUND(SUM(CASE WHEN boi.COMPLETE_AMOUNT = boi.ORDER_AMOUNT THEN 1 ELSE 0 END) / COUNT(*) * 100,
        2)                              COMPLETE_AMOUNT
        FROM BASE_ORDERINFO boi
        JOIN BASE_MATERIALINFO bm ON bm.MATERIAL_CODE = boi.material_code AND bm.DISPO IN ('100')
        JOIN BASE_PRODUCTLINE bm ON bm.WORK_CENTER_CODE = boi.WORK_CENTER_CODE
        WHERE TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') IN
        (SELECT TO_CHAR(TRUNC(SYSDATE, 'IW') + LEVEL - 1, 'YYYY-MM-DD') AS day_of_week
        FROM DUAL
        CONNECT BY LEVEL &lt;= 7)
        GROUP BY TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD'), bm.PRODUCT_LINE_CODE
        ORDER BY BEGIN_DATE
    </select>
    <select id="homePieChartInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT QUALITY_DEFECT_NAME, SUM
        FROM (
        SELECT xl.DICT_LABEL QUALITY_DEFECT_NAME, COUNT(*) SUM
        FROM REPORT_QUALITY_INSPECTION QI
        LEFT JOIN BASE_QUALITY_INSPECTION_ITEM QII ON QII.QUALITY_DEFECT_CODE = QI.QUALITY_DEFECT_CODE
        left join Z_SYS_DICT_DATA xl
        on xl.DICT_TYPE = 'defect_subclass' and QII.MATERIAL_SUBCLASS = xl.DICT_VALUE
        WHERE QI.QUALITY_DEFECT_NAME IS NOT NULL
        AND TO_CHAR(INSPECTOR_TIME, 'YYYY-MM-DD') IN
        (SELECT TO_CHAR(TRUNC(SYSDATE, 'IW') + LEVEL - 1, 'YYYY-MM-DD') AS day_of_week
        FROM DUAL
        CONNECT BY LEVEL &lt;= 7)
        GROUP BY xl.DICT_LABEL
        ORDER BY SUM DESC
        )
        WHERE ROWNUM &lt;= 10
    </select>

    <select id="boxFoamingProductionInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT VBF.FIXTURE_CODE                                                 EQUIPMENT_CODE,
               MAX(TP.FIXTUREBOXTYPE)                                           FIXTUREBOXTYPE,
               CASE
                   WHEN MAX(TP.FIXTUREBOXTYPE) = '0' THEN '暂停生产'
                   WHEN MAX(TP.FIXTURESTATUS) IN (1, 2) THEN '正常生产'
                   ELSE '暂停生产' END                                              FIXTURESTATUS,
               TO_NUMBER(BCD.CUSTOM_DATA)                                       TARGET,
               NVL(MAX(SD.PLAN_SUM), 0)                                         PLAN_SUM,
               NVL(SUM(VBF.AMOUNT), 0)                                          PRO_SUM,
               SUM(CASE WHEN VBF.RECORD_HOUR = '08' THEN VBF.AMOUNT ELSE 0 END) ANHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '09' THEN VBF.AMOUNT ELSE 0 END) TWOHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '10' THEN VBF.AMOUNT ELSE 0 END) THREEHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '11' THEN VBF.AMOUNT ELSE 0 END) FOURHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '12' THEN VBF.AMOUNT ELSE 0 END) FIVEHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '13' THEN VBF.AMOUNT ELSE 0 END) SIXHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '14' THEN VBF.AMOUNT ELSE 0 END) SEVENHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '15' THEN VBF.AMOUNT ELSE 0 END) EIGHTHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '16' THEN VBF.AMOUNT ELSE 0 END) NINEHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '17' THEN VBF.AMOUNT ELSE 0 END) TENHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '18' THEN VBF.AMOUNT ELSE 0 END) ELEVENHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '19' THEN VBF.AMOUNT ELSE 0 END) TWELVEHOUR,
               SUM(CASE WHEN VBF.RECORD_HOUR = '20' THEN VBF.AMOUNT ELSE 0 END) ACTUALVALUE
        FROM (SELECT DF.PRODUCTION_LINE,
                     DF.FIXTURE_CODE,
                     DF.LOGIN_TEAM,
                     TO_CHAR(DF.RECORDTIME, 'HH24') RECORD_HOUR,
                     NVL(COUNT(*), 0)               AMOUNT
              FROM DATA_BOXFOAM_FIXTURECOMPLATE DF
                       CROSS JOIN (SELECT MIN(START_TIME) START_TIME, MAX(END_TIME) END_TIME, TEAM_NAME
                                   FROM VIEW_CURRENT_TEAM_TIME
                                   GROUP BY TEAM_NAME) CT
              WHERE PRODUCTION_LINE = #{PRODUCT_LINE_CODE}
                AND DF.LOGIN_TEAM LIKE '%' || CT.TEAM_NAME
              GROUP BY DF.PRODUCTION_LINE, DF.FIXTURE_CODE, DF.LOGIN_TEAM, TO_CHAR(DF.RECORDTIME, 'HH24')) VBF
                 LEFT JOIN BASE_CUSTOM_DATA BCD ON BCD.CUSTOM_CODE = 'XTFP_TARGET_' || #{PRODUCT_LINE_CODE}
                 LEFT JOIN (SELECT BF.MAINID,
                                   BF.FIXTUREBOXTYPE FIXTUREBOXTYPE,
                                   BF.FIXTURESTATUS,
                                   BF.PRODUCT_LINE_CODE
                            FROM VIEW_BOX_FOAMDATA BF) TP
                           ON TP.MAINID = VBF.FIXTURE_CODE AND TP.PRODUCT_LINE_CODE = VBF.PRODUCTION_LINE
                 LEFT JOIN (SELECT B2.FIXTUREBOXTYPE,
                                   COUNT(*)                              TYPE_SUM,
                                   MAX(PLAN_AMOUNT)                      PLAN_AMOUNT,
                                   ROUND(MAX(PLAN_AMOUNT) / COUNT(*), 0) PLAN_SUM
                            FROM (
                                     SELECT MAINID, MAX(UPDATETIME) UPDATETIME
                                     FROM VIEW_BOX_FOAMDATA
                                     WHERE PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
                                     GROUP BY MAINID
                                 ) BF
                                     JOIN VIEW_BOX_FOAMDATA B2 ON B2.MAINID = BF.MAINID AND B2.UPDATETIME = BF.UPDATETIME
                                     JOIN (SELECT get_short_material_type(BOX_MATERIALNAME) BOX_MATERIALNAME,
                                                  SUM(PLAN_AMOUNT)                          PLAN_AMOUNT
                                           FROM VIEW_BOX_FOAMPLAN
                                           WHERE PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
                                             AND SHIFTTYPE = 1
                                           GROUP BY get_short_material_type(BOX_MATERIALNAME)) F2
                                          ON F2.BOX_MATERIALNAME LIKE B2.FIXTUREBOXTYPE || '%'
                            WHERE B2.FIXTUREBOXTYPE != 0
                            GROUP BY B2.FIXTUREBOXTYPE) SD ON TP.FIXTUREBOXTYPE = SD.FIXTUREBOXTYPE
        WHERE VBF.PRODUCTION_LINE = #{PRODUCT_LINE_CODE}
        GROUP BY VBF.FIXTURE_CODE, TO_NUMBER(BCD.CUSTOM_DATA)
        ORDER BY VBF.FIXTURE_CODE
    </select>

    <select id="getCurrentTeamTimeList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT SEQ,
               TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS') START_TIME,
               TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS')   END_TIME,
               HOUR_TIME,
               TEAM_NAME
        FROM VIEW_CURRENT_TEAM_TIME
    </select>

    <select id="todayOfflineProduction" resultType="java.util.HashMap">
        SELECT COUNT(*) OFFLINE_SUM
        FROM PRODUCT_OFFLINE
        WHERE TO_CHAR(PRODUCT_SCANTIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
    </select>

</mapper>
