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

    <!-- 查询工单统计（维修+保养+巡检工单总数、已完成、停机，全量历史） -->
    <!-- 维修工单: DMS_BILLS_FAULT_INSTANCE, WHERE IS_FLAG = '1' -->
    <!-- 保养工单: DMS_BILLS_MAINT_INSTANCE（无IS_FLAG字段，全量统计） -->
    <!-- 巡检工单: DMS_BILLS_INSPECT_INSTANCE, WHERE IS_FLAG = '1' -->
    <select id="selectOrderWorkStatistics" resultType="java.util.HashMap">
        SELECT
            -- 工单总数 = 维修工单 + 保养工单 + 巡检工单
            (SELECT COUNT(*) FROM DMS_BILLS_FAULT_INSTANCE WHERE IS_FLAG = '1') +
            (SELECT COUNT(*) FROM DMS_BILLS_MAINT_INSTANCE) +
            (SELECT COUNT(*) FROM DMS_BILLS_INSPECT_INSTANCE WHERE IS_FLAG = '1') AS TOTAL_COUNT,

            -- 已完成 = 维修完成(BILLS_STATUS='2') + 保养完成(MAINT_STATUS='3') + 巡检完成(INSPECT_STATUS='3')
            (SELECT COUNT(*) FROM DMS_BILLS_FAULT_INSTANCE WHERE IS_FLAG = '1' AND BILLS_STATUS = '2') +
            (SELECT COUNT(*) FROM DMS_BILLS_MAINT_INSTANCE WHERE MAINT_STATUS = '3') +
            (SELECT COUNT(*) FROM DMS_BILLS_INSPECT_INSTANCE WHERE IS_FLAG = '1' AND INSPECT_STATUS = '3') AS COMPLETE_COUNT,

            -- 停机工单 = 维修中(BILLS_STATUS='1')
            (SELECT COUNT(*) FROM DMS_BILLS_FAULT_INSTANCE WHERE IS_FLAG = '1' AND BILLS_STATUS = '1') AS STOP_COUNT
        FROM DUAL
    </select>

    <!-- 查询维修时间统计（平均响应时间、平均维修时间，全量历史） -->
    <!-- 平均响应时间 = (REAL_BEGIN_TIME - APPLY_TIME) 的平均值，单位分钟 -->
    <!-- 平均维修时间 = (REAL_END_TIME - REAL_BEGIN_TIME) 的平均值，单位小时 -->
    <!-- 数据来源：DMS_BILLS_FAULT_INSTANCE -->
    <!-- BILLS_STATUS: 0=待维修, 1=维修中, 2=维修完成 -->
    <select id="selectRepairTimeStatistics" resultType="java.util.HashMap">
        SELECT ROUND(NVL(AVG((REAL_BEGIN_TIME - APPLY_TIME) * 24 * 60), 0), 2) AS AVG_RESPONSE_TIME,
               ROUND(NVL(AVG((REAL_END_TIME - REAL_BEGIN_TIME) * 24), 0), 2) AS AVG_REPAIR_TIME
        FROM DMS_BILLS_FAULT_INSTANCE
        WHERE IS_FLAG = '1'
          AND REAL_BEGIN_TIME IS NOT NULL
          AND REAL_END_TIME IS NOT NULL
          AND BILLS_STATUS = '2'
          AND APPLY_TIME IS NOT NULL
    </select>

    <!-- 查询保养执行情况统计（全量历史） -->
    <!-- MAINT_STATUS: 1=待保养, 2=保养中, 3=已完成 -->
    <!-- 注意：此表没有 IS_FLAG 字段；MAINT_STATUS 是 CHAR(1) 类型 -->
    <!-- 注意：工单表没有 MAINT_STATUS=4（待验证）状态，该字段返回0 -->
    <select id="selectMaintStatistics" resultType="java.util.HashMap">
        SELECT COUNT(*) AS PLAN_COUNT,
               SUM(CASE WHEN MAINT_STATUS = '3' THEN 1 ELSE 0 END) AS COMPLETE_COUNT,
               SUM(CASE WHEN MAINT_STATUS = '1' THEN 1 ELSE 0 END) AS WAITING_COUNT,
               SUM(CASE WHEN MAINT_STATUS = '2' THEN 1 ELSE 0 END) AS DOING_COUNT,
               0 AS VERIFY_COUNT
        FROM DMS_BILLS_MAINT_INSTANCE
    </select>

    <!-- 查询巡检执行情况统计（全量历史） -->
    <!-- 应检设备 = DMS_BILLS_INSPECT_INSTANCE 全量（历史累计） -->
    <!-- 已检设备 = DMS_BILLS_INSPECT_INSTANCE 中 INSPECT_STATUS='3'(已完成) 的工单数量 -->
    <!-- 数据来源：DMS_BILLS_INSPECT_INSTANCE -->
    <select id="selectInspectStatistics" resultType="java.util.HashMap">
        SELECT (SELECT COUNT(*) FROM DMS_BILLS_INSPECT_INSTANCE WHERE IS_FLAG = '1') AS DEVICE_COUNT,
               (SELECT COUNT(*) FROM DMS_BILLS_INSPECT_INSTANCE WHERE IS_FLAG = '1' AND INSPECT_STATUS = '3') AS COMPLETE_COUNT
        FROM DUAL
    </select>

    <!-- 查询设备状态分布（有效设备全量） -->
    <!-- DEVICE_STATUS: NUMBER(1,0)类型，0=使用/正常, 1=停用/异常, 2=报废 -->
    <!-- 实验设备、未检设备、正常设备、异常设备 的比例分布 -->
    <!-- 数据来源：BASE_DEVICELEDGER -->
    <select id="selectDeviceStatusDistribution" 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 ABNORMAL_COUNT,
               SUM(CASE WHEN DEVICE_STATUS = 2 THEN 1 ELSE 0 END) AS SCRAP_COUNT,
               SUM(CASE WHEN DEVICE_TYPE = '2' THEN 1 ELSE 0 END) AS EXPERIMENT_COUNT
        FROM BASE_DEVICELEDGER
        WHERE IS_FLAG = 1
    </select>

    <!-- 查询故障来源分布（全量历史） -->
    <!-- FAULT_SOURCE_TYPE: 1=检修(维护故障), 2=点检, 3=巡检(运行故障), 9=其他(自然故障) -->
    <!-- 数据来源：DMS_BILLS_FAULT_INSTANCE -->
    <select id="selectFaultSourceDistribution" resultType="java.util.HashMap">
        SELECT COUNT(*) AS TOTAL_COUNT,
               SUM(CASE WHEN FAULT_SOURCE_TYPE = 1 THEN 1 ELSE 0 END) AS MAINTAIN_COUNT,
               SUM(CASE WHEN FAULT_SOURCE_TYPE IN (2, 3) THEN 1 ELSE 0 END) AS RUNNING_COUNT,
               SUM(CASE WHEN FAULT_SOURCE_TYPE = 9 THEN 1 ELSE 0 END) AS NATURAL_COUNT
        FROM DMS_BILLS_FAULT_INSTANCE
        WHERE IS_FLAG = '1'
    </select>

    <!-- 查询故障数量曲线（近30天每日告警数量） -->
    <!-- 数据来源：DMS_RECORD_ALARM_INFO -->
    <select id="selectFaultTrendList" resultType="com.aucma.report.domain.vo.Board5FaultTrendVo">
        SELECT TO_CHAR(ALARM_BEGIN_TIME, 'DD') AS dateValue,
               COUNT(*) AS faultCount
        FROM DMS_RECORD_ALARM_INFO
        WHERE ALARM_BEGIN_TIME &gt;= TRUNC(SYSDATE) - 30
        GROUP BY TO_CHAR(ALARM_BEGIN_TIME, 'DD'), TRUNC(ALARM_BEGIN_TIME)
        ORDER BY TRUNC(ALARM_BEGIN_TIME)
    </select>

    <!-- 查询产量机台TOP5（当天00:00-23:59） -->
    <!-- 从BASE_DEVICE_PARAM_VAL获取当天每台设备的最新产量总数 -->
    <!-- 数据来源：BASE_DEVICE_PARAM_VAL + BASE_DEVICELEDGER -->
    <select id="selectProductionTop5" resultType="com.aucma.report.domain.vo.Board5ProductionTopVo">
        SELECT * FROM (
            SELECT d.DEVICE_NAME AS deviceName,
                   NVL(TO_NUMBER(p.PARAM_VALUE), 0) AS production,
                   0 AS percent
            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
            ORDER BY production DESC
        ) WHERE ROWNUM &lt;= 5
    </select>

    <!-- 查询最大产量（当天00:00-23:59，用于计算百分比） -->
    <select id="selectMaxProduction" resultType="java.lang.Long">
        SELECT NVL(MAX(TO_NUMBER(PARAM_VALUE)), 1)
        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
    </select>

</mapper>
