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

    <resultMap type="DeviceAlarmRecord" id="DeviceAlarmRecordResult">
        <result property="objId" column="obj_id"/>
        <result property="deviceCode" column="device_code"/>
        <result property="deviceName" column="device_name"/>
        <result property="deviceType" column="device_type"/>
        <result property="alarmInfo" column="alarm_info"/>
        <result property="alarmTime" column="alarm_time"/>
        <result property="remark" column="remark"/>
        <result property="isFlag" column="is_flag"/>
        <result property="createdBy" column="created_by"/>
        <result property="createdTime" column="created_time"/>
        <result property="batchId" column="batch_id"/>
        <result property="paramCode" column="param_code"/>
        <result property="paramValue" column="param_value"/>
        <result property="productLineCode" column="PRODUCT_LINE_CODE"/>
        <result property="productLineName" column="PRODUCT_LINE_NAME"/>
        <result property="alarmEndTime" column="alarmEndTime"/>
        <result property="alarmDuration" column="alarmDuration"/>
    </resultMap>

    <sql id="selectDeviceAlarmRecordVo">
        select rad.obj_id,
               rad.device_code,
               bd.DEVICE_NAME,
               rad.device_type,
               rad.alarm_info,
               rad.alarm_time,
               rad.remark,
               rad.is_flag,
               rad.created_by,
               rad.created_time,
               rad.batch_id,
               rad.param_code,
               rad.param_value
        from record_alarm_device rad
                 left join BASE_DEVICELEDGER bd on bd.DEVICE_CODE = rad.DEVICE_CODE
    </sql>

    <select id="selectDeviceAlarmRecordList" parameterType="DeviceAlarmRecord" resultMap="DeviceAlarmRecordResult">
        SELECT PL.PRODUCT_LINE_CODE,
        PL.PRODUCT_LINE_NAME,
        RAD.device_code,
        BD.DEVICE_NAME,
        RAD.param_code,
        RAD.device_type,
        RAD.alarm_info,
        RAD.alarm_time,
        RAD.next_alarm_time alarmEndTime,
        ROUND((RAD.next_alarm_time - RAD.alarm_time) * 60 * 24, 2) alarmDuration
        FROM (
        SELECT SD.device_code,
        SD.param_code,
        SD.device_type,
        SD.alarm_info,
        MIN(SD.alarm_time)      alarm_time,
        MAX(SD.next_alarm_time) next_alarm_time
        FROM (
        SELECT device_code,
        param_code,
        device_type,
        alarm_info,
        alarm_time,
        next_alarm_time,
        SUM(CASE WHEN time_diff > 30 THEN 1 ELSE 0 END)
        OVER (PARTITION BY device_code, param_code ORDER BY alarm_time) AS GRP
        FROM (SELECT rad.device_code,
        rad.param_code,
        rad.device_type,
        rad.alarm_info,
        rad.alarm_time,
        LEAD(rad.alarm_time)
        OVER (PARTITION BY rad.device_code, rad.param_code ORDER BY rad.alarm_time) AS next_alarm_time,
        (LEAD(rad.alarm_time)
        OVER (PARTITION BY rad.device_code, rad.param_code ORDER BY rad.alarm_time) -
        rad.alarm_time) * 60 * 60 *
        24                                                                               AS time_diff
        FROM record_alarm_device rad
        <where>
            <if test="deviceCode != null  and deviceCode != ''">and rad.device_code = #{deviceCode}</if>
            <if test="deviceName != null  and deviceName != ''">and rad.device_name like concat(concat('%', #{deviceName}),
                '%')
            </if>
            <if test="deviceType != null  and deviceType != ''">and rad.device_type = #{deviceType}</if>
            <if test="alarmInfo != null  and alarmInfo != ''">and rad.alarm_info = #{alarmInfo}</if>
            <if test="params.beginAlarmTime != null and params.beginAlarmTime != '' and params.endAlarmTime != null and params.endAlarmTime != ''">
                and rad.alarm_time between to_date(#{params.beginAlarmTime}, 'yyyy-mm-dd hh24:mi:ss') and
                to_date(#{params.endAlarmTime}, 'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="isFlag != null ">and rad.is_flag = #{isFlag}</if>
            <if test="createdBy != null  and createdBy != ''">and rad.created_by = #{createdBy}</if>
            <if test="createdTime != null ">and rad.created_time = #{createdTime}</if>
            <if test="batchId != null  and batchId != ''">and rad.batch_id = #{batchId}</if>
            <if test="paramCode != null  and paramCode != ''"> and rad.param_code = #{paramCode}</if>
            <if test="paramValue != null  and paramValue != ''"> and rad.param_value = #{paramValue}</if>
        </where>
        )
        ) SD
        GROUP BY SD.device_code, SD.param_code, SD.device_type, SD.alarm_info, SD.GRP
        ) RAD
        LEFT JOIN BASE_DEVICELEDGER BD on BD.DEVICE_CODE = RAD.DEVICE_CODE
        LEFT JOIN BASE_PRODUCTLINE PL ON BD.PRODUCT_LINE_CODE = PL.PRODUCT_LINE_CODE
        <where>
            <if test="productLineCode != null  and productLineCode != ''">and PL.PRODUCT_LINE_CODE = #{productLineCode}</if>
        </where>
        ORDER BY ALARM_TIME DESC
    </select>

    <select id="selectDeviceAlarmRecordByObjId" parameterType="Long" resultMap="DeviceAlarmRecordResult">
        <include refid="selectDeviceAlarmRecordVo"/>
        where rad.obj_id = #{objId}
    </select>

    <insert id="insertDeviceAlarmRecord" parameterType="DeviceAlarmRecord">
        <selectKey keyProperty="objId" resultType="long" order="BEFORE">
            SELECT seq_device_alarm_record.NEXTVAL as objId FROM DUAL
        </selectKey>
        insert into RECORD_ALARM_DEVICE
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="objId != null">obj_id,</if>
            <if test="deviceCode != null">device_code,</if>
            <if test="deviceName != null">device_name,</if>
            <if test="deviceType != null">device_type,</if>
            <if test="alarmInfo != null">alarm_info,</if>
            <if test="alarmTime != null">alarm_time,</if>
            <if test="remark != null">remark,</if>
            <if test="isFlag != null">is_flag,</if>
            <if test="createdBy != null">created_by,</if>
            <if test="createdTime != null">created_time,</if>
            <if test="batchId != null">batch_id,</if>
            <if test="paramCode != null">param_code,</if>
            <if test="paramValue != null">param_value,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="objId != null">#{objId},</if>
            <if test="deviceCode != null">#{deviceCode},</if>
            <if test="deviceName != null">#{deviceName},</if>
            <if test="deviceType != null">#{deviceType},</if>
            <if test="alarmInfo != null">#{alarmInfo},</if>
            <if test="alarmTime != null">#{alarmTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="isFlag != null">#{isFlag},</if>
            <if test="createdBy != null">#{createdBy},</if>
            <if test="createdTime != null">#{createdTime},</if>
            <if test="batchId != null">#{batchId},</if>
            <if test="paramCode != null">#{paramCode},</if>
            <if test="paramValue != null">#{paramValue},</if>
        </trim>
    </insert>

    <update id="updateDeviceAlarmRecord" parameterType="DeviceAlarmRecord">
        update RECORD_ALARM_DEVICE
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceCode != null">device_code = #{deviceCode},</if>
            <if test="deviceName != null">device_name = #{deviceName},</if>
            <if test="deviceType != null">device_type = #{deviceType},</if>
            <if test="alarmInfo != null">alarm_info = #{alarmInfo},</if>
            <if test="alarmTime != null">alarm_time = #{alarmTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="isFlag != null">is_flag = #{isFlag},</if>
            <if test="createdBy != null">created_by = #{createdBy},</if>
            <if test="createdTime != null">created_time = #{createdTime},</if>
            <if test="batchId != null">batch_id = #{batchId},</if>
            <if test="paramCode != null">param_code = #{paramCode},</if>
            <if test="paramValue != null">param_value = #{paramValue},</if>
        </trim>
        where obj_id = #{objId}
    </update>

    <delete id="deleteDeviceAlarmRecordByObjId" parameterType="Long">
        delete
        from RECORD_ALARM_DEVICE
        where obj_id = #{objId}
    </delete>

    <delete id="deleteDeviceAlarmRecordByObjIds" parameterType="String">
        delete from RECORD_ALARM_DEVICE where obj_id in
        <foreach item="objId" collection="array" open="(" separator="," close=")">
            #{objId}
        </foreach>
    </delete>
    <delete id="deleteDeviceAlarmRecordByDates" parameterType="Long">
        delete
        from RECORD_ALARM_DEVICE
        where   (sysdate-#{dates})>ALARM_TIME
    </delete>
</mapper>
