<?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.eventriskfactor" name="重点事件风险因子">


    <insert id="insertEventRiskFactor" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRiskFactor">
        INSERT IGNORE INTO jl_event_risk_factor(
        id,
        event_category_id,
        event_type_id,
        alarm_level,
        city_id,
        area_id,
        street_id,
        community_id,
        event_id
        ) VALUES (
        #{id},
        #{eventCategoryId},
        #{eventTypeId},
        #{alarmLevel},
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId},
        #{eventId}
        )
    </insert>
    <update id="delEventRiskFactor" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRiskFactor">
        update jl_event_risk_factor set status ='2' WHERE event_id=#{eventId}
    </update>

    <select id="getEventRiskFactorCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.EventRiskFactorDTO" resultType="Integer">
        SELECT count(*)
        FROM jl_event_risk_factor t
        LEFT JOIN jl_event je on t.event_id = je.id
        <where>
            <if test="status != null and status != ''">
                AND je.status = #{status}
            </if>
            <if test="cityId != null and cityId != ''">
                AND t.city_id = #{cityId}
            </if>
            <if test="areaId != null and areaId != ''">
                AND t.area_id = #{areaId}
            </if>
            <if test="_user.company_grade == '3'.toString()">
                AND t.area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND t.street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND t.community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND t.community_id = #{_user.company_id}
            </if>
            <if test="alarmLevel == 1 or alarmLevel == 2 or alarmLevel == 3">
                AND t.alarm_level = #{alarmLevel}
            </if>
        </where>
    </select>

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

    <select id="findEventRiskFactorPageList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT * FROM (
        SELECT
        DISTINCT je.id,
        je.event_type eventType,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
        `value`)eventCategoryId,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` ) eventTypeId,
        je.is_hidden_danger isHiddenDanger,
        je.is_special_tasks isSpecialTasks,
        je.risk_name as riskName,
        je.is_difficult isDifficult,
        je.is_public_opinion isPublicOpinion,
        je.is_early_warning isEarlyWarning,
        je.is_point_event isPointEvent,
        je.involved_number involvedNumber,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
        je.city_id cityId,
        (SELECT name FROM sys_area WHERE code = je.city_id) city,
        je.area_id areaId,
        (SELECT name FROM sys_area WHERE code = je.area_id) area,
        je.street_id streetId,
        (SELECT name FROM sys_area WHERE code = je.street_id) street,
        je.community_id communityId,
        (SELECT name FROM sys_area WHERE code = je.community_id) community,
        je.garden_id gardenId,
        je.building_id buildingId,
        je.unit_id unitId,
        je.floor_id floorId,
        je.doorplate_id doorplateId,
        je.event_code eventCode,
        je.event_source eventSource,
        je.address,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.status,
        DATE_FORMAT(je.complete_time,'%Y-%m-%d %T') completeTime,
        (SELECT risk_level FROM jl_event_flow_log WHERE event_id = je.id AND event_id = je.id
        ORDER BY create_time DESC LIMIT 1) riskLevel,
        je.is_involve_everyone,
        je.is_involve_everyone_count
        FROM jl_event_risk_factor t
        LEFT JOIN jl_event je on t.event_id = je.id
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id
        WHERE je.del_flag = '0' and t.status = '1'
        <!--<if test="params.status == null">
            AND je.status = '2'
        </if>-->
        <if test="params.status != null and params.status != ''">
            AND je.status = #{params.status}
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </if>
        <if test="params.isSatisfaction != null and params.isSatisfaction != ''">
            AND jefl.is_satisfaction = #{params.isSatisfaction}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#'))
        </if>
        <if test="params.eventTel != null and params.eventTel != ''">
            AND jeu.event_tel = #{params.eventTel}
        </if>
        <if test="params.eventType != null and params.eventType != ''">
            AND je.event_type = #{params.eventType}
        </if>
        <if test="params.cityId != null and params.cityId != ''">
            AND je.city_id = #{params.cityId}
        </if>
        <if test="params.areaId != null and params.areaId != ''">
            AND je.area_id = #{params.areaId}
        </if>
        <if test="params.streetId != null and params.streetId != ''">
            AND je.street_id = #{params.streetId}
        </if>
        <if test="params.communityId != null and params.communityId != ''">
            AND je.community_id = #{params.communityId}
        </if>
        <if test="params.gardenId != null and params.gardenId != ''">
            AND je.garden_id = #{params.gardenId}
        </if>
        <if test="params.buildingId != null and params.buildingId != ''">
            AND je.building_id = #{params.buildingId}
        </if>
        <if test="params.floorId != null and params.floorId != ''">
            AND je.floor_id = #{params.floorId}
        </if>
        <if test="params.doorplateId != null and params.doorplateId != ''">
            AND je.doorplate_id = #{params.doorplateId}
        </if>
        <if test="params.eventCategoryId != null and params.eventCategoryId != ''">
            AND je.event_category_id = #{params.eventCategoryId}
        </if>
        <if test="params.eventTypeId != null and params.eventTypeId != ''">
            AND je.event_type_id = #{params.eventTypeId}
        </if>
        <if test="params.isHiddenDanger != null and params.isHiddenDanger != ''">
            AND je.is_hidden_danger = #{params.isHiddenDanger}
        </if>
        <if test="params.isDifficult != null and params.isDifficult != ''">
            AND je.is_difficult = #{params.isDifficult}
        </if>
        <if test="params.isPublicOpinion != null and params.isPublicOpinion != ''">
            AND je.is_public_opinion = #{params.isPublicOpinion}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning = #{params.isEarlyWarning}
        </if>
        <if test="params.isPointEvent != null and params.isPointEvent != ''">
            AND je.is_point_event = #{params.isPointEvent}
        </if>
        <if test="params.occurrenceTime != null and params.occurrenceTime != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') = #{params.occurrenceTime}
        </if>
        <if test="params.address != null and params.address != ''">
            AND je.address LIKE CONCAT('%',#{params.address},'%')
        </if>
<!--        <if test="params.occurrenceTimeStart != null and params.occurrenceTimeStart != '' and params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">-->
<!--            AND #{params.occurrenceTimeEnd} >= je.occurrence_time-->
<!--            AND je.occurrence_time >= #{params.occurrenceTimeStart}-->
<!--        </if>-->
<!--        <if test="params.resolveTimeStart != null and params.resolveTimeStart != '' and params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">-->
<!--            AND #{params.occurrenceTimeEnd} >= je.complete_time-->
<!--            AND je.complete_time >= #{params.resolveTimeStart}-->
<!--        </if>-->
        <if test="params.occurrenceTimeStart != null and params.occurrenceTimeStart != ''">
            AND je.occurrence_time >= #{params.occurrenceTimeStart}
        </if>
        <if test="params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">
            AND #{params.occurrenceTimeEnd} >= je.occurrence_time
        </if>
        <if test="params.resolveTimeStart != null and params.resolveTimeStart != ''">
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params.resolveTimeEnd} >= je.complete_time
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            AND je.city_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND je.area_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND je.street_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND je.community_id = #{params.sysAreaId}
        </if>
        <if test="params.alarmLevel == 1 or params.alarmLevel == 2 or params.alarmLevel == 3">
            AND t.alarm_level = #{params.alarmLevel}
        </if>
        <if test="params.eventUserName != null and params.eventUserName != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_user_name LIKE CONCAT( #{params.eventUserName}, '%'))
        </if>
        ) a
        ORDER BY createTime DESC
    </select>

    <select id="findEventRiskFactorHistoryPageList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT * FROM (
        SELECT
        DISTINCT je.id,
        je.event_type eventType,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
        `value`)eventCategoryId,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` ) eventTypeId,
        je.is_hidden_danger isHiddenDanger,
        je.is_special_tasks isSpecialTasks,
        je.risk_name as riskName,
        je.is_difficult isDifficult,
        je.is_public_opinion isPublicOpinion,
        je.is_early_warning isEarlyWarning,
        je.is_point_event isPointEvent,
        je.involved_number involvedNumber,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
        je.city_id cityId,
        (SELECT name FROM sys_area WHERE code = je.city_id) city,
        je.area_id areaId,
        (SELECT name FROM sys_area WHERE code = je.area_id) area,
        je.street_id streetId,
        (SELECT name FROM sys_area WHERE code = je.street_id) street,
        je.community_id communityId,
        (SELECT name FROM sys_area WHERE code = je.community_id) community,
        je.garden_id gardenId,
        je.building_id buildingId,
        je.unit_id unitId,
        je.floor_id floorId,
        je.doorplate_id doorplateId,
        je.event_code eventCode,
        je.event_source eventSource,
        je.address,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.status,
        DATE_FORMAT(je.complete_time,'%Y-%m-%d %T') completeTime,
        (SELECT risk_level FROM jl_event_flow_log WHERE event_id = je.id AND event_id = je.id
        ORDER BY create_time DESC LIMIT 1) riskLevel,
        je.is_involve_everyone,
        je.is_involve_everyone_count
        FROM jl_event_risk_factor t
        LEFT JOIN jl_event je on t.event_id = je.id
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id
        WHERE je.del_flag = '0'
        AND t.status = '2'
        <if test="params.status != null and params.status != ''">
            AND je.status = #{params.status}
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </if>
        <if test="params.isSatisfaction != null and params.isSatisfaction != ''">
            AND jefl.is_satisfaction = #{params.isSatisfaction}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#'))
        </if>
        <if test="params.eventTel != null and params.eventTel != ''">
            AND jeu.event_tel = #{params.eventTel}
        </if>
        <if test="params.eventType != null and params.eventType != ''">
            AND je.event_type = #{params.eventType}
        </if>
        <if test="params.cityId != null and params.cityId != ''">
            AND je.city_id = #{params.cityId}
        </if>
        <if test="params.areaId != null and params.areaId != ''">
            AND je.area_id = #{params.areaId}
        </if>
        <if test="params.streetId != null and params.streetId != ''">
            AND je.street_id = #{params.streetId}
        </if>
        <if test="params.communityId != null and params.communityId != ''">
            AND je.community_id = #{params.communityId}
        </if>
        <if test="params.gardenId != null and params.gardenId != ''">
            AND je.garden_id = #{params.gardenId}
        </if>
        <if test="params.buildingId != null and params.buildingId != ''">
            AND je.building_id = #{params.buildingId}
        </if>
        <if test="params.floorId != null and params.floorId != ''">
            AND je.floor_id = #{params.floorId}
        </if>
        <if test="params.doorplateId != null and params.doorplateId != ''">
            AND je.doorplate_id = #{params.doorplateId}
        </if>
        <if test="params.eventCategoryId != null and params.eventCategoryId != ''">
            AND je.event_category_id = #{params.eventCategoryId}
        </if>
        <if test="params.eventTypeId != null and params.eventTypeId != ''">
            AND je.event_type_id = #{params.eventTypeId}
        </if>
        <if test="params.isHiddenDanger != null and params.isHiddenDanger != ''">
            AND je.is_hidden_danger = #{params.isHiddenDanger}
        </if>
        <if test="params.isDifficult != null and params.isDifficult != ''">
            AND je.is_difficult = #{params.isDifficult}
        </if>
        <if test="params.isPublicOpinion != null and params.isPublicOpinion != ''">
            AND je.is_public_opinion = #{params.isPublicOpinion}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning = #{params.isEarlyWarning}
        </if>
        <if test="params.isPointEvent != null and params.isPointEvent != ''">
            AND je.is_point_event = #{params.isPointEvent}
        </if>
        <if test="params.occurrenceTime != null and params.occurrenceTime != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') = #{params.occurrenceTime}
        </if>
        <if test="params.address != null and params.address != ''">
            AND je.address LIKE CONCAT('%',#{params.address},'%')
        </if>
        <if test="params.occurrenceTimeStart != null and params.occurrenceTimeStart != ''">
            AND je.occurrence_time >= #{params.occurrenceTimeStart}
        </if>
        <if test="params.occurrenceTimeEnd != null and params.occurrenceTimeEnd != ''">
            AND #{params.occurrenceTimeEnd} >= je.occurrence_time
        </if>
        <if test="params.resolveTimeStart != null and params.resolveTimeStart != ''">
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params.resolveTimeEnd} >= je.complete_time
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            AND je.city_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND je.area_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND je.street_id = #{params.sysAreaId}
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND je.community_id = #{params.sysAreaId}
        </if>
        <if test="params.alarmLevel == 1 or params.alarmLevel == 2 or params.alarmLevel == 3">
            AND t.alarm_level = #{params.alarmLevel}
        </if>
        <if test="params.eventUserName != null and params.eventUserName != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_user_name LIKE CONCAT( #{params.eventUserName}, '%'))
        </if>
        ) a
        ORDER BY createTime DESC
    </select>

</mapper>
