<?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 bi.OBJ_ID,
               ROWNUM                                                                                             ROWSEQ,
               boi.FACTORY_CODE,
               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
        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
    </select>
    <select id="homeColumnarInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')                            BEGIN_DATE,
               SUM(boi.COMPLETE_AMOUNT)                                         COMPLETE_AMOUNT,
               ROUND(SUM(boi.COMPLETE_AMOUNT) / SUM(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')
        WHERE TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') BETWEEN TO_CHAR(SYSDATE - 2, 'YYYY-MM-DD') AND TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')
        GROUP BY TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')
        ORDER BY 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,
               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')
        WHERE TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD') BETWEEN TO_CHAR(SYSDATE - 2, 'YYYY-MM-DD') AND TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')
        GROUP BY TO_CHAR(boi.BEGIN_DATE, 'YYYY-MM-DD')
        ORDER BY BEGIN_DATE
    </select>
    <select id="homePieChartInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT QUALITY_DEFECT_NAME, COUNT(*) SUM
        FROM
            REPORT_QUALITY_INSPECTION
        WHERE QUALITY_DEFECT_NAME IS NOT NULL
        GROUP BY QUALITY_DEFECT_NAME
    </select>

    <select id="boxFoamingProductionInterface" resultType="java.util.HashMap" parameterType="java.util.HashMap">
        SELECT BD.SORT                                                   EQUIPMENT_CODE,
               VBF.FIXTUREBOXTYPE,
               CASE WHEN VBF.FIXTURESTATUS = 1 THEN '正常生产' ELSE '暂停生产' END FIXTURESTATUS,
               TO_NUMBER(BCD.CUSTOM_DATA)                                TARGET,
               (CASE
                    WHEN '20:00' > TO_CHAR(SYSDATE, 'HH24:MI') AND TO_CHAR(SYSDATE, 'HH24:MI') >= '08:00'
                        THEN round(TO_NUMBER(BCD.CUSTOM_DATA) / 12, 0) * (TO_CHAR(SYSDATE, 'HH24') - 7)
                    WHEN TO_CHAR(SYSDATE, 'HH24:MI') >= '20:00'
                        THEN round(TO_NUMBER(BCD.CUSTOM_DATA) / 12, 0) * (TO_CHAR(SYSDATE, 'HH24') - 19)
                    WHEN '08:00' > TO_CHAR(SYSDATE, 'HH24:MI')
                        THEN round(TO_NUMBER(BCD.CUSTOM_DATA) / 12, 0) * (TO_CHAR(SYSDATE, 'HH24') + 5)
                   END
                   )                                                     PLAN_SUM,
               VBF.PRODUCTION                                            PRO_SUM,
               VBF.ANHOUR,
               VBF.TWOHOUR,
               VBF.THREEHOUR,
               VBF.FOURHOUR,
               VBF.FIVEHOUR,
               VBF.SIXHOUR,
               VBF.SEVENHOUR,
               VBF.EIGHTHOUR,
               VBF.NINEHOUR,
               VBF.TENHOUR,
               VBF.ELEVENHOUR,
               VBF.TWELVEHOUR,
               VBF.ACTUALVALUE,
               TO_CHAR(VBF.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS')          UPDATETIME
        FROM VIEW_BOX_FOAMDATA VBF
                 LEFT JOIN VIEW_BOX_DEVICEINFO BD ON BD.OBJ_ID = VBF.MAINID
                 LEFT JOIN BASE_CUSTOM_DATA BCD ON BCD.CUSTOM_CODE = 'XTFP_TARGET_' || #{PRODUCT_LINE_CODE}
        WHERE VBF.PRODUCT_LINE_CODE = #{PRODUCT_LINE_CODE}
        ORDER BY BD.SORT
    </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>

</mapper>
