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

    <!-- 查询年累计产量（上一自然年：上一年1月1日到12月31日，非滚动12个月） -->
    <select id="selectYearProductionTotal" resultType="java.lang.Long">
        SELECT NVL(SUM(COMPLETE_AMOUNT), 0)
        FROM BASE_ORDERINFO
        WHERE TO_CHAR(BEGIN_DATE, 'YYYY') = TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY')
    </select>

    <!-- 查询月累计产量（本自然月：当月1日至月末） -->
    <!-- 新逻辑：与日累计一致，按设备参数表取每台设备本月最新的“机台状态-实际产出数量”汇总 -->
    <!-- 原工单累计逻辑保留注释：
        SELECT NVL(SUM(COMPLETE_AMOUNT), 0)
        FROM BASE_ORDERINFO
        WHERE TO_CHAR(BEGIN_DATE, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
    -->
    <!-- <select id="selectMonthProductionTotal" resultType="java.lang.Long">
        /* 按设备取本月窗口内的最早值与最新值差值，再汇总；无起始值则按0计算 */
        SELECT NVL(SUM(latest_val - earliest_val), 0)
        FROM (
            SELECT device_code,
                   NVL(MAX(CASE WHEN rn_desc = 1 THEN val END), 0) AS latest_val,
                   NVL(MAX(CASE WHEN rn_asc = 1 THEN val END), 0) AS earliest_val
            FROM (
                SELECT DEVICE_CODE,
                       TO_NUMBER(PARAM_VALUE) AS val,
                       ROW_NUMBER() OVER (PARTITION BY DEVICE_CODE ORDER BY COLLECT_TIME DESC) AS rn_desc,
                       ROW_NUMBER() OVER (PARTITION BY DEVICE_CODE ORDER BY COLLECT_TIME ASC) AS rn_asc
                FROM BASE_DEVICE_PARAM_VAL
                WHERE PARAM_NAME = '机台状态-实际产出数量'
                  AND COLLECT_TIME &gt;= TRUNC(SYSDATE, 'MM')
                  AND COLLECT_TIME &lt; ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
            )
            GROUP BY device_code
        )
    </select> -->
    <select id="selectMonthProductionTotal" resultType="java.lang.Long">
        /* 按天取“生产计数-当前日期生产总数”每台设备当日最新值，再汇总本月所有天 */
        SELECT NVL(SUM(daily_val), 0)
        FROM (
            SELECT TRUNC(COLLECT_TIME) AS collect_day,
                   DEVICE_CODE,
                   MAX(TO_NUMBER(PARAM_VALUE)) KEEP (DENSE_RANK LAST ORDER BY COLLECT_TIME) AS daily_val
            FROM BASE_DEVICE_PARAM_VAL
            WHERE PARAM_NAME = '生产计数-当前日期生产总数'
              AND COLLECT_TIME &gt;= TRUNC(SYSDATE, 'MM')
              AND COLLECT_TIME &lt; ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
            GROUP BY TRUNC(COLLECT_TIME), DEVICE_CODE
        )
    </select>

    <!-- 查询日累计产量（当天：00:00-23:59 工艺参数） -->
    <!-- 使用BASE_DEVICE_PARAM_VAL表，通过PARAM_NAME='机台状态-实际产出数量'定位参数 -->
    <!-- 注意：PARAM_CODE可能因设备不同而变化，但PARAM_NAME是固定的 -->
    <!-- 取每台设备当天最新的一条记录的实际产出数量进行汇总 -->
    <!-- <select id="selectDayProductionTotal" resultType="java.lang.Long">
        /* 按设备取当天窗口内的最早值与最新值差值，再汇总；无起始值则按0计算 */
        SELECT NVL(SUM(latest_val - earliest_val), 0)
        FROM (
            SELECT device_code,
                   NVL(MAX(CASE WHEN rn_desc = 1 THEN val END), 0) AS latest_val,
                   NVL(MAX(CASE WHEN rn_asc = 1 THEN val END), 0) AS earliest_val
            FROM (
                SELECT DEVICE_CODE,
                       TO_NUMBER(PARAM_VALUE) AS val,
                       ROW_NUMBER() OVER (PARTITION BY DEVICE_CODE ORDER BY COLLECT_TIME DESC) AS rn_desc,
                       ROW_NUMBER() OVER (PARTITION BY DEVICE_CODE ORDER BY COLLECT_TIME ASC) AS rn_asc
                FROM BASE_DEVICE_PARAM_VAL
                WHERE PARAM_NAME = '机台状态-实际产出数量'
                  AND COLLECT_TIME &gt;= TRUNC(SYSDATE)
                  AND COLLECT_TIME &lt; TRUNC(SYSDATE) + 1
            )
            GROUP BY device_code
        )
    </select> -->
    <select id="selectDayProductionTotal" resultType="java.lang.Long">
        /* 取当天每台设备“生产计数-当前日期生产总数”的最新值再汇总 */
        SELECT NVL(SUM(daily_val), 0)
        FROM (
            SELECT DEVICE_CODE,
                   MAX(TO_NUMBER(PARAM_VALUE)) KEEP (DENSE_RANK LAST ORDER BY COLLECT_TIME) AS daily_val
            FROM BASE_DEVICE_PARAM_VAL
            WHERE PARAM_NAME = '生产计数-当前日期生产总数'
              AND COLLECT_TIME &gt;= TRUNC(SYSDATE)
              AND COLLECT_TIME &lt; TRUNC(SYSDATE) + 1
            GROUP BY DEVICE_CODE
        )
    </select>

    <!-- 查询本周工单统计（计划数、完成数，周一00:00 - 周日23:59） -->
    <select id="selectTodayOrderStatistics" resultType="java.util.HashMap">
        SELECT NVL(SUM(ORDER_AMOUNT), 0) AS PLAN_COUNT,
               NVL(SUM(COMPLETE_AMOUNT), 0) AS COMPLETE_COUNT
        FROM BASE_ORDERINFO
        WHERE BEGIN_DATE &gt;= TRUNC(SYSDATE, 'IW')
          AND BEGIN_DATE &lt;  TRUNC(SYSDATE, 'IW') + 7
    </select>

    <!-- 查询今日工单进度列表（注释当天00:00-23:59） -->
    <select id="selectTodayOrderProgressList" resultType="com.aucma.report.domain.vo.Board4OrderProgressVo">
        SELECT MATERIAL_NAME AS materialName,
               ORDER_AMOUNT AS planCount,
               COMPLETE_AMOUNT AS completeCount,
               (ORDER_AMOUNT - COMPLETE_AMOUNT) AS diffCount,
               CASE WHEN ORDER_AMOUNT &gt; 0 
                    THEN ROUND(COMPLETE_AMOUNT / ORDER_AMOUNT * 100, 0) || '%'
                    ELSE '0%' END AS progress
        FROM BASE_ORDERINFO
        <!-- WHERE TO_CHAR(BEGIN_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') -->
        ORDER BY BEGIN_DATE DESC
    </select>

    <!-- 查询设备状态统计 -->
    <!-- DEVICE_STATUS: NUMBER(1,0)类型，0=使用/正常, 1=停用/告警, 2=报废/停机 -->
    <!-- IS_FLAG: NUMBER(1,0)类型，0=有效 -->
    <select id="selectDeviceStatusStatistics" resultType="java.util.HashMap">
        SELECT COUNT(*) AS TOTAL_COUNT,
               SUM(CASE WHEN DEVICE_STATUS = 0 THEN 1 ELSE 0 END) AS NORMAL_COUNT,
               SUM(CASE WHEN DEVICE_STATUS = 1 THEN 1 ELSE 0 END) AS ALARM_COUNT,
               SUM(CASE WHEN DEVICE_STATUS = 2 THEN 1 ELSE 0 END) AS STOP_COUNT
        FROM BASE_DEVICELEDGER
        WHERE IS_FLAG = 0
    </select>

    <!-- 查询总维修次数 -->
    <select id="selectTotalRepairCount" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM DMS_BILLS_FAULT_INSTANCE
        WHERE IS_FLAG = '1'
    </select>

    <!-- 查询最新报警信息（当前一小时内） -->
    <!-- 数据来源：DMS_RECORD_ALARM_INFO + BASE_DEVICELEDGER  -->
    <select id="selectLatestAlarmInfo" resultType="java.util.HashMap">
        SELECT * FROM (
            SELECT d.DEVICE_NAME AS DEVICE_NAME,
                   a.ALARM_REASON AS ALARM_REASON,
                   TO_CHAR(a.ALARM_BEGIN_TIME, 'HH24:MI:SS') AS ALARM_TIME
            FROM DMS_RECORD_ALARM_INFO a
            LEFT JOIN BASE_DEVICELEDGER d ON a.DEVICE_ID = d.OBJ_ID
            WHERE a.ALARM_BEGIN_TIME &gt;= SYSDATE - 1/24
            ORDER BY a.ALARM_BEGIN_TIME DESC
        ) WHERE ROWNUM = 1
    </select>

    <!-- 查询维修分析列表（按设备聚合） -->
    <!-- 设备表使用 BASE_DEVICELEDGER，主键是 OBJ_ID -->
    <select id="selectRepairAnalysisByDevice" resultType="com.aucma.report.domain.vo.Board4RepairAnalysisVo">
        SELECT * FROM (
            SELECT d.DEVICE_NAME AS deviceName,
                   COUNT(*) AS repairCount,
                   ROUND(COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM DMS_BILLS_FAULT_INSTANCE WHERE IS_FLAG = '1'), 0), 0) AS percent
            FROM DMS_BILLS_FAULT_INSTANCE f
            LEFT JOIN BASE_DEVICELEDGER d ON f.DEVICE_ID = d.OBJ_ID
            WHERE f.IS_FLAG = '1'
              AND f.DEVICE_ID IS NOT NULL
            GROUP BY d.OBJ_ID, d.DEVICE_NAME
            ORDER BY repairCount DESC
        ) WHERE ROWNUM &lt;= 10
    </select>

    <!-- 查询报修趋势（按小时段统计，当天00:00-23:59） -->
    <select id="selectRepairTrendList" resultType="com.aucma.report.domain.vo.Board4RepairTrendVo">
        SELECT TO_CHAR(APPLY_TIME, 'HH24') || ':00' AS timeHour,
               COUNT(*) AS repairCount
        FROM DMS_BILLS_FAULT_INSTANCE
        WHERE TO_CHAR(APPLY_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
          AND IS_FLAG = '1'
        GROUP BY TO_CHAR(APPLY_TIME, 'HH24')
        ORDER BY timeHour
    </select>

    <!-- 查询质量统计（合格数/不合格数，注释当天00:00-23:59） -->
    <!-- TREATMENT_MEASURE: 3=合格, 1=返修 -->
    <select id="selectQualityStatistics" resultType="java.util.HashMap">
        SELECT NVL(SUM(CASE WHEN TREATMENT_MEASURE = '3' THEN 1 ELSE 0 END), 0) AS QUALIFIED_COUNT,
               NVL(SUM(CASE WHEN TREATMENT_MEASURE = '1' THEN 1 ELSE 0 END), 0) AS UNQUALIFIED_COUNT
        FROM REPORT_QUALITY_INSPECTION
        <!-- WHERE TO_CHAR(INSPECTOR_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') -->
    </select>

    <!-- 查询质量追溯列表（注释近7天含当天，按产品统计合格率） -->
    <select id="selectQualityTraceList" resultType="com.aucma.report.domain.vo.Board4QualityTraceVo">
        SELECT * FROM (
            SELECT MATERIAL_NAME AS materialName,
                   ROUND(SUM(CASE WHEN TREATMENT_MEASURE = '3' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 0) AS passRate,
                   100 AS passLimit
            FROM REPORT_QUALITY_INSPECTION
            WHERE MATERIAL_NAME IS NOT NULL
              <!-- AND TO_CHAR(INSPECTOR_TIME, 'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 7, 'YYYY-MM-DD') -->
            GROUP BY MATERIAL_NAME
            ORDER BY passRate DESC
        ) WHERE ROWNUM &lt;= 10
    </select>

    <!-- 查询设备分析列表（各设备机台实际产量） -->
    <!-- 从BASE_DEVICE_PARAM_VAL获取当天每台设备的最新实际产出数量 -->
    <!-- 注意：通过PARAM_NAME='机台状态-实际产出数量'定位参数，而非PARAM_CODE -->
    <!-- 因为PARAM_CODE可能因设备不同而变化，但PARAM_NAME是固定的 -->
    <select id="selectDeviceProductionAnalysis" resultType="com.aucma.report.domain.vo.Board4DeviceProductionVo">
        SELECT d.DEVICE_NAME AS deviceName,
               NVL(TO_NUMBER(p.PARAM_VALUE), 0) AS production
        FROM BASE_DEVICELEDGER d
        LEFT JOIN (
            SELECT DEVICE_CODE, PARAM_VALUE
            FROM (
                SELECT DEVICE_CODE, PARAM_VALUE,
                       ROW_NUMBER() OVER (PARTITION BY DEVICE_CODE ORDER BY COLLECT_TIME DESC) AS RN
                FROM BASE_DEVICE_PARAM_VAL
                WHERE PARAM_NAME = '机台状态-实际产出数量'
                  AND COLLECT_TIME &gt;= TRUNC(SYSDATE) AND COLLECT_TIME &lt; TRUNC(SYSDATE) + 1
            )
            WHERE RN = 1
        ) p ON d.DEVICE_CODE = p.DEVICE_CODE
        WHERE d.IS_FLAG = 1
        ORDER BY production DESC
    </select>

</mapper>
