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

    <!-- 设备故障分析 -->
    <select id="deviceFaultAnalysisList"
            resultType="com.aucma.report.domain.DeviceFaultAnalysisReport"
            parameterType="java.util.HashMap">
        SELECT
            dl.device_code AS DEVICE_CODE,
            dl.device_name AS DEVICE_NAME,
            fa.fault_type  AS FAULT_TYPE,
            COUNT(1)       AS FAULT_COUNT,
            NVL(SUM(
                    CASE
                        WHEN fi.real_begin_time IS NOT NULL AND fi.real_end_time IS NOT NULL
                            THEN (fi.real_end_time - fi.real_begin_time) * 24 * 60
                        ELSE 0
                    END
                ), 0) AS FAULT_DURATION_MINUTES
        FROM dms_bills_fault_instance fi
                 LEFT JOIN base_deviceledger dl ON fi.device_id = dl.OBJ_ID
                 LEFT JOIN dms_fault_instance_activity fa ON fi.repair_instance_id = fa.repair_instance_id
                    AND fa.process_step_order = 1
        <where>
            fi.is_flag = '1'
            <if test="deviceCode != null and deviceCode != ''">
                AND dl.device_code = #{deviceCode}
            </if>
            <if test="faultType != null and faultType != ''">
                AND fa.fault_type = #{faultType}
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND fi.apply_time &gt;= TO_DATE(#{beginTime}, 'yyyy-mm-dd')
            </if>
            <if test="endTime != null and endTime != ''">
                AND fi.apply_time &lt;= TO_DATE(#{endTime}, 'yyyy-mm-dd')
            </if>
        </where>
        GROUP BY dl.device_code, dl.device_name, fa.fault_type
        ORDER BY FAULT_COUNT DESC
    </select>

    <!-- 维修工时统计：按执行人汇总 -->
    <select id="repairHoursReportList"
            resultType="com.aucma.report.domain.RepairHoursReport"
            parameterType="java.util.HashMap">
        SELECT
            rwo.executor_id   AS EXECUTOR_ID,
            rwo.executor_name AS EXECUTOR_NAME,
            COUNT(1)          AS ORDER_COUNT,
            NVL(SUM(rwo.repair_hours), 0) AS TOTAL_HOURS,
            CASE WHEN COUNT(1) = 0 THEN 0
                 ELSE ROUND(NVL(SUM(rwo.repair_hours), 0) / COUNT(1), 2)
            END AS AVG_HOURS
        FROM dms_repair_work_order rwo
        <where>
            rwo.is_flag = '1'
            AND rwo.order_status = '3'
            <if test="executorId != null">
                AND rwo.executor_id = #{executorId}
            </if>
            <if test="executorName != null and executorName != ''">
                AND rwo.executor_name LIKE '%' || #{executorName} || '%'
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND rwo.actual_start_time &gt;= TO_DATE(#{beginTime}, 'yyyy-mm-dd')
            </if>
            <if test="endTime != null and endTime != ''">
                AND rwo.actual_start_time &lt;= TO_DATE(#{endTime}, 'yyyy-mm-dd')
            </if>
        </where>
        GROUP BY rwo.executor_id, rwo.executor_name
        ORDER BY TOTAL_HOURS DESC
    </select>

    <!-- 设备 OEE 分析基础数据：按设备统计停机时长（分钟） -->
    <select id="deviceOeeReportList"
            resultType="com.aucma.report.domain.DeviceOeeReport"
            parameterType="java.util.HashMap">
        SELECT
            dl.device_code AS DEVICE_CODE,
            dl.device_name AS DEVICE_NAME,
            NVL(SUM(rwo.shutdown_duration), 0) AS DOWNTIME_MINUTES
        FROM dms_repair_work_order rwo
                 LEFT JOIN base_deviceledger dl ON rwo.device_id = dl.OBJ_ID
        <where>
            rwo.is_flag = '1'
            AND rwo.need_shutdown = '1'
            <if test="deviceCode != null and deviceCode != ''">
                AND dl.device_code = #{deviceCode}
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND rwo.actual_start_time &gt;= TO_DATE(#{beginTime}, 'yyyy-mm-dd')
            </if>
            <if test="endTime != null and endTime != ''">
                AND rwo.actual_start_time &lt;= TO_DATE(#{endTime}, 'yyyy-mm-dd')
            </if>
        </where>
        GROUP BY dl.device_code, dl.device_name
        ORDER BY dl.device_code
    </select>

</mapper>
