<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.eventwaiting" name="预警告警">

    <select id="getWarnDealTime" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.bo.EventWarningSettingBo">
            SELECT
                DATE_FORMAT( DATE_SUB( NOW(), INTERVAL CONCAT( '-', complete_days-tip_days ) DAY ), '%Y-%m-%d %H:00:00' ) early_warningTime,
                DATE_FORMAT( DATE_SUB( NOW(), INTERVAL CONCAT( '-', complete_days) DAY ), '%Y-%m-%d %H:00:00' ) alarmTime
            FROM
                jl_event_warning_setting
            WHERE
                event_type = #{eventType}
    </select>

    <select id="findEventTriggerList" parameterType="page" resultType="map">
        select id,
        event_category_id as eventCategoryId,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND event_category_id = `value`) as eventCategoryName,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND event_type_id = `value` )  as eventTypeName,
        (
        SELECT
            count(*)
        FROM
            `jl_event`
        WHERE
            event_category_id = jl_event_trigger.event_category_id
            AND event_type_id = jl_event_trigger.event_type_id
            AND event_type = '2'
            AND street_id = jl_event_trigger.street_id

            GROUP BY event_category_id,event_type_id
        ) as triggerCount,
        trigger_type as triggerType,
        trigger_time as triggerTime ,
        city_id as cityId, area_id as areaId, street_id as streetId, community_id as communityId
        from jl_event_trigger
        <where>
            <if test="params._user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                AND community_id = #{params._user.company_id}
            </if>
            <if test="params.eventCategoryId != null and params.eventCategoryId != ''">
                AND event_category_id = #{params.eventCategoryId}
            </if>
            <if test="params.eventTypeId != null and params.eventTypeId != ''">
                AND event_type_id = #{params.eventTypeId}
            </if>
            <if test="params.eventTypeName != null and params.eventTypeId != ''">
                AND event_type_id = #{params.eventTypeId}
            </if>
            <if test="params.triggerType == null or params.triggerType == ''">
                AND trigger_type = '2'
            </if>
            <if test="params.alarmLevel != null and params.alarmLevel != ''">
                AND alarm_level = #{params.alarmLevel}
            </if>
            <if test="params.status!=null">
                AND status = #{params.status}
            </if>
            <if test="params.cityId != null and params.cityId != ''">
                AND city_id = #{params.cityId}
            </if>
            <if test="params.areaId != null and params.areaId != ''">
                AND area_id = #{params.areaId}
            </if>
        </where>
        order by trigger_time desc
    </select>

    <select id="findEventTriggerHistoryPageList" parameterType="page" resultType="map">
        select id,
        event_category_id as eventCategoryId,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND event_category_id = `value`) as eventCategoryName,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND event_type_id = `value` )  as eventTypeName,
        (
            SELECT
            count(*)
            FROM
            `jl_event`
            WHERE
            event_category_id = jl_event_trigger.event_category_id
            AND event_type_id = jl_event_trigger.event_type_id
            AND event_type = '2'
            AND street_id = jl_event_trigger.street_id
            GROUP BY event_category_id,event_type_id
        ) as triggerCount,
        trigger_type as triggerType,
        trigger_time as triggerTime ,
        city_id as cityId, area_id as areaId, street_id as streetId, community_id as communityId
        from jl_event_trigger
        where status = '2'
        <if test="params._user.company_grade == '3'.toString()">
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND community_id = #{params._user.company_id}
        </if>
        <if test="params.eventCategoryId != null and params.eventCategoryId != ''">
            AND event_category_id = #{params.eventCategoryId}
        </if>
        <if test="params.eventTypeId != null and params.eventTypeId != ''">
            AND event_type_id = #{params.eventTypeId}
        </if>
        <if test="params.eventTypeName != null and params.eventTypeId != ''">
            AND event_type_id = #{params.eventTypeId}
        </if>
        <if test="params.triggerType == null or params.triggerType == ''">
            AND trigger_type = '2'
        </if>
        <if test="params.alarmLevel != null and params.alarmLevel != ''">
            AND alarm_level = #{params.alarmLevel}
        </if>
        <if test="params.cityId != null and params.cityId != ''">
            AND city_id = #{params.cityId}
        </if>
        <if test="params.areaId != null and params.areaId != ''">
            AND area_id = #{params.areaId}
        </if>
        <if test="params.streetId != null and params.streetId != ''">
            AND street_id = #{params.streetId}
        </if>
        <if test="params.communityId != null and params.communityId != ''">
            AND community_id = #{params.communityId}
        </if>
        order by trigger_time desc
    </select>

    <select id="countEventTrigger" resultType="map" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
        select count(1) as count
        from jl_event_trigger
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
        </where>
    </select>

    <insert id="insertEventWaitingDealNotice" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        INSERT INTO jl_event_waiting_deal_notice(
            id,
            dept_id,
            event_id,
            content,
            read_status,
            create_time
        ) VALUES (
            UUID(),
            #{deptId},
            #{eventId},
            #{content},
            '2',
            NOW()
        )
    </insert>

    <select id="getGroupEventWarningCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.GroupEventWarningDTO" resultType="Integer">
        select count(1) as count
        from jl_event_trigger
        <where>
            <if test="triggerType!=null and triggerType != ''">
                AND trigger_type = #{triggerType}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="cityId != null and cityId != ''">
                AND city_id = #{cityId}
            </if>
            <if test="areaId != null and areaId != ''">
                AND area_id = #{areaId}
            </if>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="alarmLevel != null and alarmLevel != ''">
                AND alarm_level = #{alarmLevel}
            </if>
        </where>
    </select>

    <select id="getGroupEventWarningCountHistory" parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.GroupEventWarningDTO" resultType="Integer">
        select count(1) as count
        from jl_event_trigger
        where `status` = 2
        <if test="triggerType!=null and triggerType != ''">
            AND trigger_type = #{triggerType}
        </if>
        <if test="cityId != null and cityId != ''">
            AND city_id = #{cityId}
        </if>
        <if test="areaId != null and areaId != ''">
            AND area_id = #{areaId}
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND community_id = #{_user.company_id}
        </if>
        <if test="alarmLevel != null and alarmLevel != ''">
            AND alarm_level = #{alarmLevel}
        </if>
    </select>

</mapper>
