<?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.xzzn.ems.mapper.EmsAlarmRecordsMapper">

    <resultMap type="EmsAlarmRecords" id="EmsAlarmRecordsResult">
        <result property="id"    column="id"    />
        <result property="deviceType"    column="device_type"    />
        <result property="alarmLevel"    column="alarm_level"    />
        <result property="alarmContent"    column="alarm_content"    />
        <result property="alarmStartTime"    column="alarm_start_time"    />
        <result property="alarmEndTime"    column="alarm_end_time"    />
        <result property="alarmPoint"    column="alarm_point"    />
        <result property="status"    column="status"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="siteId"    column="site_id"    />
        <result property="deviceId"    column="device_id"    />
        <result property="ticketNo"    column="ticket_no"    />
    </resultMap>

    <sql id="selectEmsAlarmRecordsVo">
        select id, device_type, alarm_level, alarm_content, alarm_start_time, alarm_end_time, alarm_point, status, create_by, create_time, update_by, update_time, remark, site_id, device_id, ticket_no from ems_alarm_records
    </sql>

    <select id="selectEmsAlarmRecordsList" parameterType="EmsAlarmRecords" resultMap="EmsAlarmRecordsResult">
        <include refid="selectEmsAlarmRecordsVo"/>
        <where>
            <if test="deviceType != null  and deviceType != ''"> and device_type = #{deviceType}</if>
            <if test="alarmLevel != null  and alarmLevel != ''"> and alarm_level = #{alarmLevel}</if>
            <if test="alarmContent != null  and alarmContent != ''"> and alarm_content = #{alarmContent}</if>
            <if test="alarmStartTime != null "> and alarm_start_time = #{alarmStartTime}</if>
            <if test="alarmEndTime != null "> and alarm_end_time = #{alarmEndTime}</if>
            <if test="alarmPoint != null  and alarmPoint != ''"> and alarm_point = #{alarmPoint}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="siteId != null  and siteId != ''"> and site_id = #{siteId}</if>
            <if test="deviceId != null  and deviceId != ''"> and device_id = #{deviceId}</if>
            <if test="ticketNo != null  and ticketNo != ''"> and ticket_no = #{ticketNo}</if>
        </where>
    </select>

    <select id="selectEmsAlarmRecordsById" parameterType="Long" resultMap="EmsAlarmRecordsResult">
        <include refid="selectEmsAlarmRecordsVo"/>
        where id = #{id}
    </select>

    <insert id="insertEmsAlarmRecords" parameterType="EmsAlarmRecords" useGeneratedKeys="true" keyProperty="id">
        insert into ems_alarm_records
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceType != null">device_type,</if>
            <if test="alarmLevel != null">alarm_level,</if>
            <if test="alarmContent != null">alarm_content,</if>
            <if test="alarmStartTime != null">alarm_start_time,</if>
            <if test="alarmEndTime != null">alarm_end_time,</if>
            <if test="alarmPoint != null">alarm_point,</if>
            <if test="status != null">status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="siteId != null">site_id,</if>
            <if test="deviceId != null and deviceId != ''">device_id,</if>
            <if test="ticketNo != null">ticket_no,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceType != null">#{deviceType},</if>
            <if test="alarmLevel != null">#{alarmLevel},</if>
            <if test="alarmContent != null">#{alarmContent},</if>
            <if test="alarmStartTime != null">#{alarmStartTime},</if>
            <if test="alarmEndTime != null">#{alarmEndTime},</if>
            <if test="alarmPoint != null">#{alarmPoint},</if>
            <if test="status != null">#{status},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="siteId != null">#{siteId},</if>
            <if test="deviceId != null and deviceId != ''">#{deviceId},</if>
            <if test="ticketNo != null">#{ticketNo},</if>
        </trim>
    </insert>

    <update id="updateEmsAlarmRecords" parameterType="EmsAlarmRecords">
        update ems_alarm_records
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceType != null">device_type = #{deviceType},</if>
            <if test="alarmLevel != null">alarm_level = #{alarmLevel},</if>
            <if test="alarmContent != null">alarm_content = #{alarmContent},</if>
            <if test="alarmStartTime != null">alarm_start_time = #{alarmStartTime},</if>
            <if test="alarmEndTime != null">alarm_end_time = #{alarmEndTime},</if>
            <if test="alarmPoint != null">alarm_point = #{alarmPoint},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="siteId != null">site_id = #{siteId},</if>
            <if test="deviceId != null and deviceId != ''">device_id = #{deviceId},</if>
            <if test="ticketNo != null">ticket_no = #{ticketNo},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteEmsAlarmRecordsById" parameterType="Long">
        delete from ems_alarm_records where id = #{id}
    </delete>

    <delete id="deleteEmsAlarmRecordsByIds" parameterType="String">
        delete from ems_alarm_records where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="getAlarmRecordsBySiteId" parameterType="String" resultType="com.xzzn.ems.domain.vo.SiteMonitorHomeAlarmVo">
        select t2.device_name as deviceName,
               t.status,
               t.alarm_content as alarmContent,
               t.ticket_no as ticketNo,
               t.id
        from ems_alarm_records t INNER JOIN ems_devices_setting t2 on t.site_id = t2.site_id and t.device_id = t2.device_id
        where t.site_id = #{siteId}
        and t.status != 1
        and t.alarm_level in ('C','D')
        order by t.alarm_start_time desc
    </select>

    <select id="getAlarmTrendList" resultType="com.xzzn.ems.domain.vo.AlarmTrendList">
        select MONTH(alarm_start_time) as dateMonth,COUNT(1) as alarmNum
        from ems_alarm_records
        group by MONTH(alarm_start_time)
    </select>

    <select id="getDeviceAlarmPropList" resultType="com.xzzn.ems.domain.vo.DeviceAlarmProportionList">
        select device_type as type,COUNT(1) as alarmNum
        from ems_alarm_records
        group by device_type
    </select>

    <select id="getAlarmGradeList" resultType="com.xzzn.ems.domain.vo.AlarmGradeList">
        select alarm_level as level,COUNT(1) as dataNum from ems_alarm_records group by alarm_level;
    </select>

    <select id="getAlarmRecordDetailList" parameterType="AlarmRecordListRequestVo" resultType="com.xzzn.ems.domain.vo.AlarmRecordListResponseVo">
        select  t2.device_name as deviceName,
                t.alarm_level as alarmLevel,
                t.alarm_content as alarmContent,t.status as status,
                t.alarm_start_time as alarmStartTime,t.alarm_end_time as alarmEndTime,
                t.ticket_no as ticketNo,
                t.id
        from ems_alarm_records t
        LEFT JOIN ems_devices_setting t2 on t.site_id = t2.site_id and t.device_id = t2.device_id
        where t.site_id = #{siteId}
        <if test="deviceType != null and deviceType != ''">
            AND t.device_type = #{deviceType}
        </if>
        <if test="alarmLevel != null and alarmLevel != ''">
            AND t.alarm_level = #{alarmLevel}
        </if>
        <if test="deviceId != null and deviceId != ''">
            AND t.device_id = #{deviceId}
        </if>
        <if test="status != null and status != ''">
            AND t.status IN (
            <foreach collection="status.split(',')" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        <if test="alarmStartTime != null and alarmStartTime != null and alarmEndTime != null and alarmEndTime != ''">
            AND (date_format(t.alarm_start_time,'%Y%m%d') BETWEEN date_format(#{alarmStartTime},'%Y%m%d') and date_format(#{alarmEndTime},'%Y%m%d')
            or date_format(t.alarm_end_time,'%Y%m%d') BETWEEN date_format(#{alarmStartTime},'%Y%m%d') and date_format(#{alarmEndTime},'%Y%m%d') )
        </if>
        order by t.alarm_start_time desc
    </select>

    <select id="getDeviceAlarmNum" resultType="java.lang.Integer">
        select COUNT(*) as alarmNum from ems_alarm_records
        where site_id = #{siteId} and device_id = #{deviceId}
        and status <![CDATA[ != ]]> 1
    </select>

    <select id="getFailedRecord" resultMap="EmsAlarmRecordsResult">
        <include refid="selectEmsAlarmRecordsVo"/>
        where site_id = #{siteId}
        and alarm_content = #{content}
        and alarm_level = #{level}
        and status <![CDATA[ != ]]> 1
        limit 1
    </select>

    <select id="getAllUnfinishedRecords" resultMap="EmsAlarmRecordsResult">
        <include refid="selectEmsAlarmRecordsVo"/>
        WHERE  status <![CDATA[ != ]]> 1
        and site_id = #{siteId}
        and device_id = #{deviceId}
        and alarm_point IN
        <foreach collection="needUpdateKeys"  item="key" open="(" separator=","  close=")">
            #{key}
        </foreach>
    </select>

    <insert id="batchUpsert">
        INSERT into ems_alarm_records (
        id,
        device_type,
        alarm_level,
        alarm_content,
        alarm_start_time,
        alarm_end_time,
        alarm_point,
        status,
        create_by,
        create_time,
        update_by,
        update_time,
        remark,
        site_id,
        device_id,
        ticket_no
        ) values
        <foreach collection="recordsList" item="item" separator=",">
            (
            #{item.id},
            #{item.deviceType},
            #{item.alarmLevel},
            #{item.alarmContent},
            #{item.alarmStartTime},
            #{item.alarmEndTime},
            #{item.alarmPoint},
            #{item.status},
            #{item.createBy},
            #{item.createTime},
            #{item.updateBy},
            #{item.updateTime},
            #{item.remark},
            #{item.siteId},
            #{item.deviceId},
            #{item.ticketNo}
            )
        </foreach>
        on duplicate key update
         alarm_end_time = values(alarm_end_time),
         status = values(status),
         device_id = values(device_id)
     </insert>

    <select id="getAlarmByTicketNo" resultType="com.xzzn.ems.domain.EmsAlarmRecords">
        <include refid="selectEmsAlarmRecordsVo"/>
        WHERE ticket_no = #{ticketNo}
        limit 1
    </select>

    <select id="getNeedPutRedisAlarm" resultType="com.xzzn.ems.domain.EmsAlarmRecords">
        <include refid="selectEmsAlarmRecordsVo"/>
        WHERE status <![CDATA[ != ]]> 1
        and site_id = #{siteId}
        and device_id = #{deviceId}
        and alarm_point is not null
    </select>
</mapper>