<?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.event" name="事件">


    <select id="getEventFirstTime" resultType='date'>
        select occurrence_time from jl_event order by occurrence_time limit 1
    </select>

    <select id="findFollowUpRecordsList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecords"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecords">
        select id,
        record_time as recordTime,
        event_code as eventCode,
        satisfaction_level as satisfactionLevel,
        record_log as recordLog,
        opinions,
        date_format(create_time, '%Y-%m-%d %T' ) as createTime,
        update_time as updateTime,
        create_user as createUser,
        update_user as updateUser,
        del_flag as delFlag
        from jl_follow_up_records
        where event_code = #{eventCode}
        and del_flag = '0'
    </select>

    <select id="findFollowUpRecordsListFile"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecordsFiles"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecordsFiles">
        select id,
        jl_follow_up_records_id as JlFollowUpRecordsId,
        obs_key as obsKey,
        file_name as fileName,
        create_time as createTime,
        update_time as updateTime,
        create_user as createUser,
        update_user as updateUser,
        del_flag as delFlag
        from jl_follow_up_records_file a
        where jl_follow_up_records_id = #{JlFollowUpRecordsId}
        and del_flag = '0'
        and create_user = #{_user.id}
    </select>

    <select id="find30DayEvent"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        SELECT
        jl_event.id AS id,
        community_id,
        event_type_id,
        event_category_id
        FROM
        jl_event
        WHERE
        jl_event.del_flag = '0'
        AND status='2'
        AND occurrence_time >= ( #{occurrenceTime} - INTERVAL 30 DAY )
        AND occurrence_time &lt; ( #{occurrenceTime} + INTERVAL 1 DAY )
        and street_id=#{streetId}
        and event_category_id=#{eventCategoryId}
        and event_type_id=#{eventTypeId}
    </select>

    <select id='getCategoryId' resultType="string" parameterType="string">
        select event_category_id from jl_event where id = #{id}
    </select>

    <select id='getStreetId' resultType="string" parameterType="string">
        select street_id from jl_event where id = #{id}
    </select>

    <select id="selectWarningAreaPageList" parameterType="page" resultType="map" expose="true">
        select
        je.event_category_id as eventCategoryId,
        sd.label as eventCategoryName,
        je.count as count,
        je.statistical_time as statisticalTime,
        je.percentage as percentage
        from
        ji_warning_focus_areas je
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        and sd.parent_id = 0 and sd.type = 'event_category'
        <where>
            <if test="params.eventCategoryId !=null and params.eventCategoryId !=''">
                AND je.event_category_id=#{params.eventCategoryId}
            </if>
            <if test="params.occurrenceStartDate!=null and params.occurrenceStartDate!=''">
                AND je.statistical_time >= #{params.occurrenceStartDate}
            </if>
            <if test="params.occurrenceEndDate!=null and params.occurrenceEndDate!=''">
                AND je.statistical_time &lt;= #{params.occurrenceEndDate}
            </if>
            <if test="params.cityId != null and params.cityId!=''">
                <if test="params.areaId == null and params.areaId==''">
                    AND je.area_level = 2 AND region_code = #{params.cityId}
                </if>
                <if test="params.areaId != null and params.areaId!=''">
                    AND je.area_level = 3 AND region_code = #{params.areaId}
                </if>
            </if>
            <if test="params._user.company_grade == '2'.toString()">
                AND je.area_level = 1
            </if>
            <if test="params._user.company_grade == '3'.toString()">
                AND je.area_level = 2 AND region_code = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND je.area_level = 3 AND region_code = #{params._user.company_id}
            </if>
            <if test="params.alarmLevel != null and params.alarmLevel != ''">
                AND je.alarm_level = #{params.alarmLevel}
            </if>
        </where>
        order by statistical_time desc
    </select>

    <select id="find10DayEvent"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        <!--select jl_event.id as id, community_id, event_type_id,event_category_id
        from jl_event
        where jl_event.del_flag = 0
        and occurrence_time >= (#{occurrenceTime} - INTERVAL 7 DAY)
        and occurrence_time &lt;= #{occurrenceTime}
        and community_id in (select community_id
        from jl_event
        where del_flag = 0
        and occurrence_time >= (#{occurrenceTime} - INTERVAL 7 DAY)
        and occurrence_time &lt;= #{occurrenceTime}
        and event_type_id = #{eventTypeId}
        group by community_id
        having count(community_id) >= 5)
        and event_type_id in (select event_type_id
        from jl_event
        where del_flag = 0
        and occurrence_time >= (#{occurrenceTime} - INTERVAL 7 DAY)
        and occurrence_time &lt;= #{occurrenceTime}
        and event_type_id = #{eventTypeId}
        group by event_type_id
        having count(event_type_id) >= 5)-->
        SELECT
        jl_event.id AS id,
        community_id,
        event_type_id,
        event_category_id
        FROM
        jl_event
        WHERE
        jl_event.del_flag = '0'
        AND occurrence_time >= ( #{occurrenceTime} - INTERVAL 7 DAY )
        AND occurrence_time &lt;= #{occurrenceTime}
        and community_id=#{communityId}
        and event_category_id=#{eventCategoryId}
        and event_type_id=#{eventTypeId}
    </select>

    <select id="insertEventTriggerContradictoryDisputes"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTriggerContradictoryDisputes">
        insert IGNORE into jl_event_trigger_contradictory_disputes (id, jl_event_trigger, jl_event_id)
        values (#{id}, #{jlEventTrigger}, #{jlEventId})
    </select>

    <insert id="insertFollowUpRecordsFiles"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecordsFiles">
        insert into jl_follow_up_records_file
        (id, jl_follow_up_records_id,obs_key,file_name,create_time,update_time,create_user,update_user,del_flag)
        values
        (#{id}, #{JlFollowUpRecordsId},#{obsKey},#{fileName}, now(), now(),#{_user.id},#{_user.id}, 0)
    </insert>

    <insert id="insertFollowUpRecords" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecords">
        insert into jl_follow_up_records
        (id,record_time,event_code,satisfaction_level,record_log,opinions,create_time,update_time,create_user,update_user,del_flag)
        values
        (#{id}, #{recordTime},#{eventCode},#{satisfactionLevel},#{recordLog},#{opinions}, now(),
        now(),#{_user.id},#{_user.id}, 0)
    </insert>

    <update id="updateFollowUpRecords" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecords">
        update jl_follow_up_records set
        record_time = #{recordTime},
        event_code = #{eventCode},
        satisfaction_level = #{satisfactionLevel},
        record_log = #{recordLog},
        opinions = #{opinions},
        update_time = now(),
        update_user = #{_user.id}
        where id = #{id}
    </update>

    <update id="deleteFollowUpRecords" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.FollowUpRecords">
        update jl_follow_up_records set del_flag = '1' where id = #{id}
    </update>

    <select id="getEventLogByCreateId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        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.event_category_id as eventCategoryName,
        je.event_type_id as eventTypeName,
        je.is_hidden_danger ishHiddenDanger,
        je.is_special_tasks isSpecialTasks,
        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.event_category_other_name,
        je.event_category_mini_other_name,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        je.risk_name riskName,
        je.event_name,
        je.event_tel,
        je.create_by as createBy,
        jefl.id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.assignment_type assignmentType,
        jefl.log_result logResult,
        jefl.event_check eventCheck,
        jefl.resolve_way resolveWay,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.risk_level riskLevel,
        DATE_FORMAT(jefl.deal_warn_time,'%Y-%m-%d %T') dealWarnTime,
        DATE_FORMAT(jefl.deal_end_time,'%Y-%m-%d %T') dealEndTime,
        (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        je.del_flag = '0'
        <if test="_user.company_grade != '2'.toString()">
            AND jefl.office_id = #{_user.company_id}
        </if>
        AND jefl.del_flag = '0'
        AND je.id = #{id}
        ORDER BY jefl.create_time DESC
        LIMIT 1
    </select>

    <select id="getEventLogById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        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.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.risk_name as riskName,
        je.event_category_other_name,
        je.event_category_mini_other_name,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        je.event_category_other_name,
        je.event_category_mini_other_name,
        je.event_name,
        je.event_tel,
        je.create_by as createBy,
        jefl.id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.assignment_type assignmentType,
        jefl.log_result logResult,
        jefl.event_check eventCheck,
        jefl.resolve_way resolveWay,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
        DATE_FORMAT(jefl.term_time,'%Y-%m-%d %T') termTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.risk_level risklevel,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime,
        (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        <!--        jefl.office_id = #{_user.company_id}-->
        jefl.grade_user_id IS NULL
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.id = #{id}
        ORDER BY jefl.create_time DESC
        LIMIT 1
    </select>

    <select id="getEventLogByCompanyId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        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.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,
        jefl.id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.assignment_type assignmentType,
        jefl.log_result logResult,
        jefl.event_check eventCheck,
        jefl.resolve_way resolveWay,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
        DATE_FORMAT(jefl.term_time,'%Y-%m-%d %T') termTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.risk_level riskLevel,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime,
        (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        jefl.office_id = #{_user.company_id}
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.id = #{id}
        ORDER BY jefl.create_time DESC
        LIMIT 1
    </select>

    <select id="getEventAllAsc" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        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.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,
        IF(je.risk_name != '' AND je.risk_name IS NOT NULL,je.risk_name,'') as riskName,
        je.event_category_other_name,
        je.event_category_mini_other_name,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        je.status as eventStatus,
        je.event_name,
        je.event_tel,
        jefl.id,
        jefl.event_id eventId,
        jefl.flow_version flowVersion,
        jefl.flow_id flowId,
        jefl.office_id officeId,
        jefl.status,
        jefl.log_type logType,
        jefl.assignment_type assignmentType,
        jefl.log_result logResult,
        jefl.resolve_name resolveName,
        jefl.resolve_unit resolveUnit,
        jefl.resolve_hierarchy resolveHierarchy,
        DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
        jefl.is_satisfaction isSatisfaction,
        jefl.resolve_process resolveProcess,
        jefl.is_resolve isResolve,
        jefl.warning_level warningLevel,
        jefl.contacts,
        jefl.attachment,
        jefl.deal_warn_time dealWarnTime,
        jefl.deal_end_time dealEndTime,
        jefl.event_check eventCheck,
        jefl.resolve_way resolveWay,
        DATE_FORMAT(jefl.term_time,'%Y-%m-%d %T') termTime,
        (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,
        (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.id = #{id}
        ORDER BY jefl.create_time DESC
        LIMIT 1
    </select>

    <!--网格员分页查询指定行数据-->
    <select id="findEventLogByCreateList"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        if(d.event_id IS not NULL ,'1','0') AS 'returnVisit',
        b.yellow_warn_days AS 'yellowWarnDays',
        b.red_warn_days AS 'redWarnDays',
        if(c.approve_days IS NULL ,0,c.approve_days) AS 'approveDays',
        DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
        DATE_FORMAT(a.createTime,'%Y-%m-%d')) AS 'day',
        a.*
        FROM (
        SELECT
        DISTINCT je.id,
        je.id eventId,
        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.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,
        je.risk_name as riskName,
        (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.complete_time,
        je.floor_id floorId,
        je.doorplate_id doorplateId,
        date_format( je.complete_time, '%Y-%m-%d %T' ) as completeTime,
        je.event_code eventCode,
        je.event_source eventSource,
        je.address,
        je.risk_level as 'riskLevel',
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.create_by as createBy,
        je.is_acceptance as isAcceptance,
        je.status as eventStatus,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='0' order by
        approve_time desc limit 1) as deal_result_0,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='1' order by
        approve_time desc limit 1) as deal_result_1,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='2' order by
        approve_time desc limit 1) as deal_result_2,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='3' order by
        approve_time desc limit 1) as deal_result_3,
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id = #{params._user.id} ORDER BY
        create_time DESC LIMIT 1) status,
        (SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id = #{params._user.id} ORDER BY
        create_time DESC LIMIT 1) isResolve,
        (SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time DESC LIMIT 1) logType,
        (SELECT deal_log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' AND office_id
        =#{params._user.company_id} and grade_user_id=#{params._user.id} and complete_time is not null ORDER BY
        create_time DESC LIMIT 1) mineLatestLogTypeNew,
        (SELECT resolve_way FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND event_id = je.id
        ORDER BY create_time DESC LIMIT 1) resolveWay,
        (SELECT IFNULL(fax,'') FROM sys_office WHERE id = jefl.office_id) fax,
        (SELECT DATE_FORMAT(term_time,'%Y-%m-%d %T') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS
        NULL
        ORDER BY create_time DESC LIMIT 1) termTime,
        (SELECT IFNULL(IF(grade_user_id != '' AND grade_user_id IS NOT NULL AND log_type = '1', 2, 1), 2) FROM
        jl_event_flow_log WHERE event_id = je.id ORDER BY create_time DESC LIMIT 1) logStatus,
        IF(#{params._user.id} = (SELECT create_by FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time ASC
        LIMIT 1),IF( 2 > (SELECT COUNT(1) FROM jl_event_flow_log WHERE event_id = je.id AND status = '3'),1,0),0)
        deleteType,
        je.is_involve_everyone,
        je.is_involve_everyone_count
        FROM
        jl_event je
        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
        jefl.grade_user_id = #{params._user.id}
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        and je.risk_level is not null
        <if test="params.id != null and params.id != ''">
            AND je.id = #{params.id}
        </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.eventUserName != null and params.eventUserName != ''">
            AND jeu.event_user_name LIKE CONCAT(#{params.eventUserName},'%')
        </if>
        <if test="params.eventType != null and params.eventType != ''">
            AND je.event_type = #{params.eventType}
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </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.eventStatus != null and params.eventStatus != ''">
            AND je.status = #{params.eventStatus}
        </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.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </if>-->
        <if test="params.isSatisfaction != null and params.isSatisfaction != ''">
            AND jefl.is_satisfaction = #{params.isSatisfaction}
        </if>
        <if test="params.resolveWay != null and params.resolveWay != ''">
            AND jefl.resolve_way = #{params.resolveWay}
        </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.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone = #{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count = #{params.is_involve_everyone_count}
        </if>
        <if test="params.resolveWay != null and params.resolveWay != ''">
            AND jefl.resolve_way = #{params.resolveWay}
        </if>
        <!--<if Test="params.logType != null and params.logType != ''">
            HAVING mineLatestLogTypeNew = #{params.logType}
        </if>-->
        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                HAVING mineLatestLogTypeNew = #{params.logType} and je.status!='1'
            </if>
        </if>
        ) a
        LEFT JOIN (SELECT event_id from jl_event_approve where del_flag='0' and approve_type='1' GROUP BY event_id) d ON
        d.event_id=a.id
        INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
        LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and
        approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
        where 1=1
        <if test="params.status!=null and params.status!=''">
            AND status = #{params.status}
        </if>
        <if test="params.isResolve != null and params.isResolve != ''">
            AND a.isResolve = #{params.isResolve}
        </if>
        ORDER BY createTime DESC,`status`
    </select>

    <select id="findEarlyWarningList" parameterType="page"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        if(d.event_id IS not NULL ,'1','0') AS 'returnVisit',
        b.yellow_warn_days AS 'yellowWarnDays',
        b.red_warn_days AS 'redWarnDays',
        if(c.approve_days IS NULL ,0,c.approve_days) AS 'approveDays',
        DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
        DATE_FORMAT(a.createTime,'%Y-%m-%d')) AS 'day',
        a.*
        FROM (
        SELECT
        DISTINCT
        je.id,
        je.id eventId,
        je.event_type eventType,
        sd.label eventCategoryId,
        sd1.label eventTypeId,
        je.is_hidden_danger isHiddenDanger,
        je.risk_name as riskName,
        je.is_special_tasks isSpecialTasks,
        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,
        je.area_id areaId,
        je.street_id streetId,
        je.community_id communityId,
        sa.name city,
        sa1. name area,
        sa2. name street,
        sa3. name 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.create_by as createBy,
        CONCAT(so.name,'-',su.name) createName,
        je.complete_time,
        DATE_FORMAT(je.complete_time,'%Y-%m-%d %T') completeTime,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.risk_level riskLevel,
        je.status as eventStatus,
        <!--(SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id = #{params._user.id} ORDER BY
        create_time DESC LIMIT 1) status,-->
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND office_id =
        #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) status,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        IF(#{params._user.id} = (SELECT create_by FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time ASC
        LIMIT 1),IF( 2 > (SELECT COUNT(1) FROM jl_event_flow_log WHERE event_id = je.id AND status = '3'),1,0),0)
        deleteType,
        (select log_type from jl_event_flow_log where event_id=je.id and del_flag='0' order by create_time desc limit 1)
        as logType
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_user su ON je.create_by = su.id
        LEFT JOIN sys_office so ON so.id = su.company_id
        LEFT JOIN sys_dict sd ON sd.`value` = je.event_category_id AND sd.type = 'event_category' AND sd.parent_id = '0'
        LEFT JOIN sys_dict sd1 ON sd1.`value` = je.event_type_id AND sd1.type = 'event_category' AND sd1.parent_id !=
        '0'
        LEFT JOIN sys_area sa ON sa.code = je.city_id
        LEFT JOIN sys_area sa1 ON sa1.code = je.area_id
        LEFT JOIN sys_area sa2 ON sa2.code = je.street_id
        LEFT JOIN sys_area sa3 ON sa3.code = je.community_id
        WHERE
        je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.risk_level IS NOT NULL
        <!--        AND je.is_early_warning > 2-->
        <!--        AND je.status = '2'-->
        <if test="params._user.company_grade == '2'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND je.community_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        </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}
            AND jefl.id = (SELECT id FROM jl_event_flow_log WHERE event_id = je.id AND is_satisfaction IS NOT NULL and
            is_satisfaction != '' ORDER BY create_time desc LIMIT 1)
        </if>
        <if test="params.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_cardno =
            TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#')))
        </if>
        <if test="params.eventTel != null and params.eventTel != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_tel = #{params.eventTel})
        </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>
        <if test="params.createName!=null and params.createName!=''">
            and su.name LIKE CONCAT(#{params.createName}, '%')
        </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.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.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params.resolveTimeEnd} >= je.complete_time
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.createTimeStart!=null and params.createTimeStart!=''">
            AND je.create_time <![CDATA[>=]]> #{params.createTimeStart}
        </if>
        <if test="params.createTimeEnd!=null and params.createTimeEnd!=''">
            AND je.create_time  <![CDATA[<=]]> #{params.createTimeEnd}
        </if>
        <if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone =#{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count =#{params.is_involve_everyone_count}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning =#{params.isEarlyWarning}
        </if>
        <if test="params.createBy != null and params.createBy != ''">
            AND je.create_by =#{params.createBy}
        </if>
        <if test="params.createTime!=null and params.createTime!=''">
            AND DATE_FORMAT(je.create_time,'%Y-%m-%d') = #{params.createTime}
        </if>
        <!--<if test="params.logType!=null and params.logType!=''">
            having logType=#{params.logType}
        </if>-->
        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                having logType=#{params.logType} and je.status!='1'
            </if>
        </if>
        ) a
        LEFT JOIN (SELECT event_id from jl_event_approve where del_flag='0' and approve_type='1' GROUP BY event_id) d ON
        d.event_id=a.id
        INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
        LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and
        approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
        <if test="params.waring!=null and params.waring!=''">
            WHERE DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
            DATE_FORMAT(a.createTime,'%Y-%m-%d'))> (b.yellow_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
            OR
            DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
            DATE_FORMAT(a.createTime,'%Y-%m-%d'))>(b.red_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
        </if>
        <!--ORDER BY occurrenceTime DESC-->
        ORDER BY createTime DESC
    </select>

    <select id="findEventAllList" parameterType="page"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        je.id,
        je.id eventId,
        je.event_type eventType,
        sd.label eventCategoryId,
        sd1.label eventTypeId,
        je.is_hidden_danger isHiddenDanger,
        je.risk_name as riskName,
        je.is_special_tasks isSpecialTasks,
        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,
        je.area_id areaId,
        je.street_id streetId,
        je.community_id communityId,
        sa.name city,
        sa1. name area,
        sa2. name street,
        sa3. name 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.create_by as createBy,
        CONCAT(so.name,'-',su.name) createName,
        je.complete_time,
        DATE_FORMAT(je.complete_time,'%Y-%m-%d %T') completeTime,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.risk_level riskLevel,
        je.status as eventStatus,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        DATEDIFF(DATE_FORMAT(if(je.complete_time IS NULL ,NOW(),je.complete_time),'%Y-%m-%d'),
        DATE_FORMAT(je.create_time,'%Y-%m-%d')) AS 'day'
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        LEFT JOIN sys_user su ON je.create_by = su.id
        LEFT JOIN sys_office so ON so.id = su.company_id
        LEFT JOIN sys_dict sd ON sd.`value` = je.event_category_id AND sd.type = 'event_category' AND sd.parent_id = '0'
        LEFT JOIN sys_dict sd1 ON sd1.`value` = je.event_type_id AND sd1.type = 'event_category' AND sd1.parent_id !=
        '0'
        LEFT JOIN sys_area sa ON sa.code = je.city_id
        LEFT JOIN sys_area sa1 ON sa1.code = je.area_id
        LEFT JOIN sys_area sa2 ON sa2.code = je.street_id
        LEFT JOIN sys_area sa3 ON sa3.code = je.community_id
        WHERE
        je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.risk_level IS NOT NULL
        <if test="params._user.company_grade == '2'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND je.community_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        </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}
            AND jefl.id = (SELECT id FROM jl_event_flow_log WHERE event_id = je.id AND is_satisfaction IS NOT NULL and
            is_satisfaction != '' ORDER BY create_time desc LIMIT 1)
        </if>
        <if test="params.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_cardno =
            TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#')))
        </if>
        <if test="params.eventTel != null and params.eventTel != ''">
            AND je.id in (SELECT event_id FROM jl_event_user WHERE event_tel = #{params.eventTel})
        </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>
        <if test="params.createName!=null and params.createName!=''">
            and su.name LIKE CONCAT(#{params.createName}, '%')
        </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.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.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params.resolveTimeEnd} >= je.complete_time
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.createTimeStart!=null and params.createTimeStart!=''">
            AND je.create_time <![CDATA[>=]]> #{params.createTimeStart}
        </if>
        <if test="params.createTimeEnd!=null and params.createTimeEnd!=''">
            AND je.create_time  <![CDATA[<=]]> #{params.createTimeEnd}
        </if>
        <if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone =#{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count =#{params.is_involve_everyone_count}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning =#{params.isEarlyWarning}
        </if>
        <if test="params.createBy != null and params.createBy != ''">
            AND je.create_by =#{params.createBy}
        </if>
        <if test="params.createTime!=null and params.createTime!=''">
            AND DATE_FORMAT(je.create_time,'%Y-%m-%d') = #{params.createTime}
        </if>
        <if test="params.resolveWay!=null and params.resolveWay!=''">
            AND jefl.resolve_way = #{params.resolveWay}
        </if>
        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                having logType=#{params.logType} and je.status!='1'
            </if>
        </if>
        GROUP BY je.id
        ORDER BY je.create_time DESC
    </select>

    <select id="getEventStatus" parameterType="map" resultType="String">
        SELECT
        status
        FROM
        jl_event_flow_log
        WHERE
        event_id = #{id}
        AND grade_user_id IS NULL
        AND office_id = #{_user.company_id}
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="selectEventFlowLogCreateByList" parameterType="map" resultType="map">
        SELECT
        create_by
        FROM
        jl_event_flow_log
        WHERE
        event_id = #{id}
        AND status = '3'
        ORDER BY create_time ASC
    </select>

    <select id="getLogType" parameterType="map" resultType="String">
        SELECT
        log_type
        FROM
        jl_event_flow_log
        WHERE
        event_id = #{id}
        AND del_flag='0'
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="getApproveDays" parameterType="map" resultType="Integer">
        SELECT
        IFNULL(sum(approve_days),0) AS 'approveDays'
        FROM
        jl_event_approve
        WHERE
        del_flag='0'
        AND approve_type='0'
        AND event_id = #{id}
        GROUP BY event_id
    </select>

    <select id="getReturnVisit" parameterType="map" resultType="String">
        SELECT
        IF(event_id IS NULL ,'0','1') AS 'returnVisit'
        FROM
        jl_event_approve
        WHERE
        del_flag='0'
        AND approve_type='1'
        AND event_id = #{id}
        GROUP BY event_id
    </select>

    <select id="selectOverdueWarningConfigList" resultType="map">
        SELECT
        yellow_warn_days,
        red_warn_days,
        risk_type
        FROM
        jl_event_overdue_warning_config
        WHERE
        del_flag = '0'
    </select>

    <select id="selectCreateByList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" expose="true">
        SELECT
        DISTINCT
        je.create_by as createBy,
        CONCAT(so.name,'-',su.name) createName
        FROM
        jl_event je
        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
        LEFT JOIN sys_user su ON je.create_by = su.id
        LEFT JOIN sys_office so ON so.id = su.company_id
        WHERE
        je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.risk_level IS NOT NULL
        <if test="_user.company_grade == '3'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is
                not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '4'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is
                not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '5'.toString()">
            <if test="fax!=null and fax!=''">
                and jefl.office_id=#{_user.company_id}
            </if>
            <if test="fax==null or fax==''">
                AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{_user.id} and del_flag='0' and fax is
                not null)
                )
            </if>
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND je.community_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
        </if>
        <if test="status != null and status != ''">
            AND je.status = #{status}
        </if>
        <if test="eventCode != null and eventCode != ''">
            AND je.event_code = #{eventCode}
        </if>
        <if test="isSatisfaction != null and isSatisfaction != ''">
            AND jefl.is_satisfaction = #{isSatisfaction}
        </if>
        <if test="isResolve != null and isResolve != ''">
            AND jefl.is_resolve = #{isResolve}
        </if>
        <if test="eventCardno != null and eventCardno != ''">
            AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
        </if>
        <if test="eventTel != null and eventTel != ''">
            AND jeu.event_tel = #{eventTel}
        </if>
        <if test="eventUserName!=null and eventUserName!=''">
            and jeu.event_user_name LIKE CONCAT(#{eventUserName}, '%')
        </if>
        <if test="createName!=null and createName!=''">
            and su.name LIKE CONCAT(#{createName}, '%')
        </if>
        <if test="eventType != null and eventType != ''">
            AND je.event_type = #{eventType}
        </if>
        <if test="cityId != null and cityId != ''">
            AND je.city_id = #{cityId}
        </if>
        <if test="areaId != null and areaId != ''">
            AND je.area_id = #{areaId}
        </if>
        <if test="streetId != null and streetId != ''">
            AND je.street_id = #{streetId}
        </if>
        <if test="communityId != null and communityId != ''">
            AND je.community_id = #{communityId}
        </if>
        <if test="gardenId != null and gardenId != ''">
            AND je.garden_id = #{gardenId}
        </if>
        <if test="buildingId != null and buildingId != ''">
            AND je.building_id = #{buildingId}
        </if>
        <if test="floorId != null and floorId != ''">
            AND je.floor_id = #{floorId}
        </if>
        <if test="doorplateId != null and doorplateId != ''">
            AND je.doorplate_id = #{doorplateId}
        </if>
        <if test="eventCategoryId != null and eventCategoryId != ''">
            AND je.event_category_id = #{eventCategoryId}
        </if>
        <if test="eventTypeId != null and eventTypeId != ''">
            AND je.event_type_id = #{eventTypeId}
        </if>
        <if test="isHiddenDanger != null and isHiddenDanger != ''">
            AND je.is_hidden_danger = #{isHiddenDanger}
        </if>
        <if test="isDifficult != null and isDifficult != ''">
            AND je.is_difficult = #{isDifficult}
        </if>
        <if test="isPublicOpinion != null and isPublicOpinion != ''">
            AND je.is_public_opinion = #{isPublicOpinion}
        </if>
        <if test="isPointEvent != null and isPointEvent != ''">
            AND je.is_point_event = #{isPointEvent}
        </if>
        <if test="occurrenceTime != null and occurrenceTime != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') = #{occurrenceTime}
        </if>
        <if test="address != null and address != ''">
            AND je.address LIKE CONCAT(#{address},'%')
        </if>
        <if test="occurrenceTimeStart != null and occurrenceTimeStart != '' and occurrenceTimeEnd != null and occurrenceTimeEnd != ''">
            AND #{occurrenceTimeEnd} >= je.occurrence_time
            AND je.occurrence_time >= #{occurrenceTimeStart}
        </if>
        <if test="resolveTimeStart != null and resolveTimeStart != '' and occurrenceTimeEnd != null and occurrenceTimeEnd != ''">
            AND #{occurrenceTimeEnd} >= je.complete_time
            AND je.complete_time >= #{resolveTimeStart}
        </if>
        <if test="is_involve_everyone != null and is_involve_everyone != ''">
            AND je.is_involve_everyone =#{is_involve_everyone}
        </if>
        <if test="is_involve_everyone_count != null and is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count =#{is_involve_everyone_count}
        </if>
        <if test="isEarlyWarning != null and isEarlyWarning != ''">
            AND je.is_early_warning =#{isEarlyWarning}
        </if>
        <if test="createTime!=null and createTime!=''">
            AND DATE_FORMAT(je.create_time,'%Y-%m-%d') = #{createTime}
        </if>
        <if test="logType!=null and logType!=''">
            <if test="logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="logType!='0'.toString()">
                having logType=#{logType} and je.status!='1'
            </if>
        </if>
        ORDER BY je.create_time DESC
    </select>

    <select id="findEarlyWarningStatusList" parameterType="page"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        b.yellow_warn_days AS 'yellowWarnDays',
        b.red_warn_days AS 'redWarnDays',
        if(c.approve_days IS NULL ,0,c.approve_days) AS 'approveDays',
        DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
        DATE_FORMAT(a.createTime,'%Y-%m-%d')) AS 'day',
        a.*
        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.risk_name as riskName,
        je.is_special_tasks isSpecialTasks,
        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.complete_time,
        je.dispute_description disputeDescription,
        je.reporting_name reportingName,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.risk_level riskLevel,
        je.status as eventStatus,
        <!--(SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id = #{params._user.id} ORDER BY
        create_time DESC LIMIT 1) status,-->
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND office_id =
        #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) status,
        je.is_involve_everyone,
        je.is_involve_everyone_count,
        (select log_type from jl_event_flow_log where event_id=je.id and del_flag='0' order by create_time desc limit 1)
        as logType
        FROM
        jl_event je
        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 jefl.del_flag = '0'
        AND je.risk_level IS NOT NULL
        <if test="params._user.company_grade == '3'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.area_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            <if test="params.fax!=null and params.fax!=''">
                and jefl.office_id=#{params._user.company_id}
            </if>
            <if test="params.fax==null or params.fax==''">
                AND (je.street_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                or jefl.office_id in (select id from sys_office where create_by=#{params._user.id} and del_flag='0' and
                fax is not null)
                )
            </if>
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND je.community_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        </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.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </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.eventUserName!=null and params.eventUserName!=''">
            and jeu.event_user_name LIKE CONCAT( #{params.eventUserName}, '%')
        </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.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.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params..resolveTimeEnd} >= je.complete_time
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.createTimeStart!=null and params.createTimeStart!=''">
            AND je.create_time <![CDATA[>=]]> #{params.createTimeStart}
        </if>
        <if test="params.createTimeEnd!=null and params.createTimeEnd!=''">
            AND je.create_time  <![CDATA[<=]]> #{params.createTimeEnd}
        </if>
        <if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone =#{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count =#{params.is_involve_everyone_count}
        </if>
        <if test="params.isEarlyWarning != null and params.isEarlyWarning != ''">
            AND je.is_early_warning =#{params.isEarlyWarning}
        </if>
        <if test="params.createTime!=null and params.createTime!=''">
            AND DATE_FORMAT(je.create_time,'%Y-%m-%d') = #{params.createTime}
        </if>
        <if test="params.resolveWay!=null and params.resolveWay!=''">
            AND jefl.resolve_way = #{params.resolveWay}
        </if>
        <!--<if Test="params.logType!=null and params.logType!=''">
            having logType=#{params.logType}
        </if>-->
        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                having logType=#{params.logType} and je.status!='1'
            </if>
        </if>
        ) a
        INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
        LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and
        approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
        <if test="params.waring!=null and params.waring!=''">
            WHERE DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
            DATE_FORMAT(a.createTime,'%Y-%m-%d'))> (b.yellow_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
            OR
            DATEDIFF(DATE_FORMAT(if(a.complete_time IS NULL ,NOW(),a.complete_time),'%Y-%m-%d'),
            DATE_FORMAT(a.createTime,'%Y-%m-%d'))>(b.red_warn_days+if(c.approve_days IS NULL ,0,c.approve_days))
        </if>
        <!--ORDER BY occurrenceTime DESC-->
        ORDER BY createTime DESC
    </select>

    <!--新增数据-->
    <insert id="insertEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        INSERT INTO jl_event(
        id,
        risk_name,
        risk_level,
        event_type,
        event_category_id,
        event_type_id,
        is_hidden_danger,
        is_special_tasks,
        is_difficult,
        is_public_opinion,
        is_point_event,
        involved_number,
        occurrence_time,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        doorplate_id,
        event_code,
        event_source,
        address,
        dispute_description,
        reporting_name,
        reporting_unit,
        reporting_garden,
        `status`,
        del_flag,
        create_by,
        create_time,
        scanning_time,
        is_early_warning,
        early_warning_time,
        alarm_time,
        is_acceptance,
        record_id,
        event_category_other_name,
        reporting_unit_code,
        event_category_mini_other_name
        <if test="is_involve_everyone!=null and is_involve_everyone!=''">
            ,is_involve_everyone
        </if>
        <if test="is_involve_everyone_count!=null and is_involve_everyone_count!=''">
            ,is_involve_everyone_count
        </if>
        <if test="event_name!=null and event_name!=''">
            ,event_name
        </if>
        <if test="event_tel!=null and event_tel!=''">
            ,event_tel
        </if>
        )VALUES(
        #{id},
        #{riskName},
        #{riskLevel},
        #{eventType},
        #{eventCategoryId},
        #{eventTypeId},
        #{isHiddenDanger},
        #{isSpecialTasks},
        #{isDifficult},
        #{isPublicOpinion},
        #{isPointEvent},
        #{involvedNumber},
        #{occurrenceTime},
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId},
        #{gardenId},
        #{buildingId},
        #{unitId},
        #{floorId},
        #{doorplateId},
        #{eventCode},
        #{eventSource},
        #{address},
        #{disputeDescription},
        #{reportingName},
        #{reportingUnit},
        #{reportingGarden},
        #{status},
        0,
        #{_user.id},
        NOW(),
        NOW(),
        '1',
        #{earlyWarningTime},
        #{alarmTime},
        '1',
        #{recordId},
        #{event_category_other_name},
        #{reportingUnitCode},
        #{event_category_mini_other_name}
        <if test="is_involve_everyone!=null and is_involve_everyone!=''">
            ,#{is_involve_everyone}
        </if>
        <if test="is_involve_everyone_count!=null and is_involve_everyone_count!=''">
            ,#{is_involve_everyone_count}
        </if>
        <if test="event_name!=null and event_name!=''">
            ,#{event_name}
        </if>
        <if test="event_tel!=null and event_tel!=''">
            ,#{event_tel}
        </if>
        )

    </insert>

    <insert id="insertEventCategoryOther" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT count(*) FROM jl_event_category_other WHERE event_id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_event_category_other
            <set>
                update_time = now(),
                update_by = #{_user.id},
                <if test="event_category_other_name != null and event_category_other_name != ''">
                    event_category_other_name = #{event_category_other_name},
                </if>
                <if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
                    event_category_mini_other_name = #{event_category_mini_other_name},
                </if>
            </set>
            WHERE event_id = #{id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_event_category_other (
            id,
            event_id,
            <if test="event_category_other_name != null and event_category_other_name != ''">
                event_category_other_name,
            </if>
            <if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
                event_category_mini_other_name,
            </if>
            create_by, create_time
            ) VALUES (
            uuid(),
            #{id},
            <if test="event_category_other_name != null and event_category_other_name != ''">
                #{event_category_other_name},
            </if>
            <if test="event_category_mini_other_name != null and event_category_mini_other_name != ''">
                #{event_category_mini_other_name},
            </if>
            #{_user.id}, now()
            )
        </if>
    </insert>

    <!-- 更新数据 -->
    <update id="updateEvent" parameterType="map">
        UPDATE jl_event
        <set>
            <if test="event_type != null and event_type != ''">
                event_type = #{event_type},
            </if>
            <if test="event_category_id != null and event_category_id != ''">
                event_category_id = #{event_category_id},
            </if>
            <if test="event_type_id != null and event_type_id != ''">
                event_type_id = #{event_type_id},
            </if>
            <if test="is_hidden_danger != null and is_hidden_danger != ''">
                is_hidden_danger = #{is_hidden_danger},
            </if>
            <if test="is_special_tasks != null and is_special_tasks != ''">
                is_special_tasks = #{is_special_tasks},
            </if>
            <if test="is_difficult != null and is_difficult != ''">
                is_difficult = #{is_difficult},
            </if>
            <if test="is_public_opinion != null and is_public_opinion != ''">
                is_public_opinion = #{is_public_opinion},
            </if>
            <if test="is_early_warning != null and is_early_warning != ''">
                is_early_warning = #{is_early_warning},
            </if>
            <if test="is_point_event != null and is_point_event != ''">
                is_point_event = #{is_point_event},
            </if>
            <if test="involved_number != null and involved_number != ''">
                involved_number = #{involved_number},
            </if>
            <if test="occurrence_time != null and occurrence_time != ''">
                occurrence_time = #{occurrence_time},
            </if>
            <if test="city_id != null and city_id != ''">
                city_id = #{city_id},
            </if>
            <if test="area_id != null and area_id != ''">
                area_id = #{area_id},
            </if>
            <if test="street_id != null and street_id != ''">
                street_id = #{street_id},
            </if>
            <if test="community_id != null and community_id != ''">
                community_id = #{community_id},
            </if>
            <if test="garden_id != null and garden_id != ''">
                garden_id = #{garden_id},
            </if>
            <if test="building_id != null and building_id != ''">
                building_id = #{building_id},
            </if>
            <if test="unit_id != null and unit_id != ''">
                unit_id = #{unit_id},
            </if>
            <if test="floor_id != null and floor_id != ''">
                floor_id = #{floor_id},
            </if>
            <if test="doorplate_id != null and doorplate_id != ''">
                doorplate_id = #{doorplate_id},
            </if>
            <if test="event_code != null and event_code != ''">
                event_code = #{event_code},
            </if>
            <if test="event_source != null and event_source != ''">
                event_source = #{event_source},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="dispute_description != null and dispute_description != ''">
                dispute_description = #{dispute_description},
            </if>
            <if test="reporting_name != null and reporting_name != ''">
                reporting_name = #{reporting_name},
            </if>
            <if test="reporting_unit != null and reporting_unit != ''">
                reporting_unit = #{reporting_unit},
            </if>
            <if test="reporting_garden != null and reporting_garden != ''">
                reporting_garden = #{reporting_garden},
            </if>
            <if test="status != null and status != ''">
                status = #{status},
            </if>
            <if test="del_flag != null and del_flag != ''">
                del_flag = #{del_flag},
            </if>
            <if test="is_involve_everyone != null and is_involve_everyone != ''">
                is_involve_everyone = #{is_involve_everyone},
            </if>
            <if test="is_involve_everyone_count != null and is_involve_everyone_count != ''">
                is_involve_everyone_count = #{is_involve_everyone_count},
            </if>
            <if test="event_name != null and event_name != ''">
                event_name = #{event_name},
            </if>
            <if test="event_tel != null and event_tel != ''">
                event_tel = #{event_tel},
            </if>
            update_by = #{_user.id},
            update_time = NOW(),
            scanning_time = NOW(),
            is_scanned = '0'
        </set>
        WHERE
        id = #{id}
    </update>

    <delete id="deleteClearEventUser" parameterType="map">
        delete from jl_event_user where event_id=#{id}
    </delete>

    <update id="updateEventStatusById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        UPDATE
        jl_event
        SET
        status = '1',
        complete_time = NOW(),
        update_by = #{_user.id},
        update_time = NOW(),
        scanning_time = NOW(),
        is_scanned = '0'
        WHERE
        id = #{eventId}
    </update>

    <select id="selectEventIdByUserIdList"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        jefl.event_id eventId,
        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.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
        FROM
        jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
        je.status = '2'
        AND jefl.grade_user_id = #{_user.id}
        AND jefl.log_type = '2'
        AND jefl.`status` = '1'
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
    </select>

    <select id="getWaningAlarmTime" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo"
            resultType="map">
        SELECT
        DATE_FORMAT( DATE_SUB(create_time, INTERVAL CONCAT( '-', IFNULL(#{yellowWarnDays}+(SELECT sum(approve_days) from
        jl_event_approve where jl_event_approve.del_flag='0' and
        jl_event_approve.approve_type='0' and jl_event_approve.event_id = jl_event.id GROUP BY event_id) ,5)) DAY ),
        '%Y-%m-%d %H:00:00' ) early_warning_time,
        DATE_FORMAT( DATE_SUB( create_time, INTERVAL CONCAT( '-', IFNULL(#{redWarnDays}+(SELECT sum(approve_days) from
        jl_event_approve where jl_event_approve.del_flag='0' and
        jl_event_approve.approve_type='0' and jl_event_approve.event_id = jl_event.id GROUP BY event_id),7)) DAY ),
        '%Y-%m-%d %H:00:00' ) alarm_time
        FROM
        jl_event
        WHERE id = #{eventId}
    </select>

    <select id="getOverdueWarning" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        SELECT
        yellow_warn_days yellowWarnDays,
        red_warn_days redWarnDays
        FROM
        jl_event_overdue_warning_config
        WHERE
        del_flag = '0'
        AND risk_type = #{riskLevel}
    </select>

    <select id="selectWarningList" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        SELECT
        id eventId,
        risk_level riskLevel,
        event_code eventCode
        FROM
        jl_event
        WHERE
        status = '2'
        AND del_flag = '0'
        LIMIT 10
    </select>

    <select id="selectAlarmList" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        SELECT
        id eventId,
        event_code eventCode
        FROM
        jl_event
        WHERE
        is_early_warning = '3'
        AND del_flag = '0'
        AND 0 > TIMESTAMPDIFF(HOUR,DATE_FORMAT(NOW(),'%Y-%m-%d %H'),alarm_time)
        AND complete_time IS NULL
    </select>

    <update id="updateEventDel" parameterType="map">
        UPDATE
        jl_event
        SET
        del_flag = '1',
        update_by = #{_user.id},
        update_time = NOW(),
        scanning_time = NOW(),
        is_scanned = '0'
        WHERE
        id = #{id}
    </update>

    <update id="updateEventAcceptance" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        UPDATE
        jl_event
        SET
        is_acceptance = '1',
        update_by = #{_user.id},
        update_time = NOW(),
        scanning_time = NOW(),
        is_scanned = '0'
        WHERE
        id = #{id}
    </update>

    <insert id="insertEventEnd" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        INSERT INTO jl_event(
        id,
        event_type,
        event_category_id,
        event_type_id,
        event_category_other_name,
        event_category_mini_other_name,
        event_name,
        event_tel,
        is_hidden_danger,
        is_special_tasks,
        is_difficult,
        is_public_opinion,
        is_point_event,
        involved_number,
        occurrence_time,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        doorplate_id,
        event_code,
        event_source,
        address,
        dispute_description,
        reporting_name,
        reporting_unit,
        reporting_garden,
        `status`,
        del_flag,
        create_by,
        create_time,
        scanning_time,
        complete_time,
        is_early_warning,
        early_warning_time,
        alarm_time,
        is_scanned,
        is_acceptance,
        risk_level,
        risk_name,
        reporting_unit_code
        )VALUES(
        #{id},
        #{eventType},
        #{eventCategoryId},
        #{eventTypeId},
        #{event_category_other_name},
        #{event_category_mini_other_name},
        #{event_name},
        #{event_tel},
        #{isHiddenDanger},
        #{isSpecialTasks},
        #{isDifficult},
        #{isPublicOpinion},
        #{isPointEvent},
        #{involvedNumber},
        #{occurrenceTime},
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId},
        #{gardenId},
        #{buildingId},
        #{unitId},
        #{floorId},
        #{doorplateId},
        #{eventCode},
        #{eventSource},
        #{address},
        #{disputeDescription},
        #{reportingName},
        #{reportingUnit},
        #{reportingGarden},
        #{status},
        0,
        #{_user.id},
        NOW(),
        NOW(),
        NOW(),
        '1',
        #{earlyWarningTime},
        #{alarmTime},
        '0',
        '1',
        #{riskLevel},
        #{riskName},
        #{reportingUnitCode}
        )
    </insert>

    <select id="findEventLogByOfficeStatusList"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        if(d.event_id IS not NULL ,'1','0') AS 'returnVisit',
        b.yellow_warn_days AS 'yellowWarnDays',
        b.red_warn_days AS 'redWarnDays',
        if(c.approve_days IS NULL ,0,c.approve_days) AS 'approveDays',
        DATEDIFF(DATE_FORMAT(if(a.completeTime IS NULL ,NOW(),a.completeTime),'%Y-%m-%d'),
        DATE_FORMAT(a.createTime,'%Y-%m-%d')) AS 'day',
        a.*
        FROM (
        SELECT
        DISTINCT je.id,
        je.id eventId,
        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,
        je.risk_level as 'riskLevel',
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrenceTime,
        je.city_id cityId,
        date_format(je.complete_time, '%Y-%m-%d %T' ) as completeTime,
        (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.create_by as createBy,
        je.reporting_unit reportingUnit,
        je.reporting_garden reportingGarden,
        je.create_time createTime,
        je.is_acceptance as isAcceptance,
        je.status as eventStatus,
        jefl.STATUS as flowStatus,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='0' order by
        approve_time desc limit 1) as deal_result_0,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='1' order by
        approve_time desc limit 1) as deal_result_1,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='2' order by
        approve_time desc limit 1) as deal_result_2,
        (select deal_result from jl_event_approve where del_flag='0' and event_id=je.id and approve_type='3' order by
        approve_time desc limit 1) as deal_result_3,
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND office_id =
        #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) status,
        (SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND event_id = je.id
        AND status = '3' ORDER BY create_time DESC LIMIT 1) isResolve,
        (SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id AND office_id = #{params._user.company_id}
        ORDER BY create_time DESC LIMIT 1) isResolveMine,
        (SELECT DATE_FORMAT(term_time,'%Y-%m-%d %T') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS
        NULL AND event_id = je.id
        ORDER BY create_time DESC LIMIT 1) termTime,
        (SELECT resolve_way FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL AND event_id = je.id
        ORDER BY create_time DESC LIMIT 1) resolveWay,
        (SELECT IF(log_type = '4' AND status = '3','2',log_type) FROM jl_event_flow_log WHERE event_id = je.id AND
        grade_user_id IS NULL AND office_id =
        #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) logType,
        (SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' ORDER BY create_time DESC LIMIT
        1) logTypeNew,
        (SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' AND office_id
        =#{params._user.company_id} ORDER BY create_time DESC LIMIT 1) mineLatestLogType,
        (SELECT deal_log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' AND office_id
        =#{params._user.company_id} and grade_user_id is null and complete_time is not null ORDER BY create_time DESC
        LIMIT 1) mineLatestLogTypeNew,
        jefl.office_id officeId,
        (SELECT IFNULL(fax,'') FROM sys_office WHERE id = jefl.office_id) fax,
        (SELECT IFNULL(assignment_type,'9999') FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS NULL
        AND office_id = #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) assignmentType,
        (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting,
        (SELECT IFNULL(IF(status = '3', 2, 1), 2) FROM jl_event_flow_log WHERE event_id = je.id AND grade_user_id IS
        NULL
        AND office_id = #{params._user.company_id} ORDER BY create_time DESC LIMIT 1) logStatus,
        (select count(office_id) from jl_event_flow_log where event_id=je.id and del_flag='0' and
        is_dispatch_office='1') as is_dispatch_office_counts,
        <!--(select count(office_id) from jl_event_flow_log where event_id=je.id and del_flag='0' and is_dispatch_office='1' and log_type='4') as is_dispatch_office_back_counts,-->
        (select count(office_id) from jl_event_flow_log where event_id=je.id and del_flag='0' and is_dispatch_office='1'
        and status='3' and is_resolve='2') as is_dispatch_office_back_counts,<!--这种情况是横向部门退回-->
        je.is_involve_everyone,
        je.is_involve_everyone_count
        FROM
        jl_event je
        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
        jefl.office_id = #{params._user.company_id}
        AND je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND jefl.grade_user_id IS NULL
        and je.risk_level is not null
        <if test="params.id != null and params.id != ''">
            AND je.id = #{params.id}
        </if>
        <if test="params.eventCardno != null and params.eventCardno != ''">
            AND jeu.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.eventCardno},'*#id#'))
        </if>
        <if test="params.eventUserName != null and params.eventUserName != ''">
            AND jeu.event_user_name LIKE CONCAT(#{params.eventUserName},'%')
        </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.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </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.status != null and params.status != ''">
            AND je.status = #{params.status}
        </if>
        <if test="params.jeflStatus != null and params.jeflStatus != ''">
            AND jefl.status != '3'
        </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.resolveWay != null and params.resolveWay != ''">
            AND jefl.resolve_way = #{params.resolveWay}
        </if>
        <if test="params.isResolve != null and params.isResolve != ''">
            AND jefl.is_resolve = #{params.isResolve}
        </if>
        <if test="params.isSatisfaction != null and params.isSatisfaction != ''">
            AND jefl.is_satisfaction = #{params.isSatisfaction}
        </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.resolveTimeEnd != null and params.resolveTimeEnd != ''">
            AND #{params.resolveTimeEnd} >= je.complete_time
            AND je.complete_time >= #{params.resolveTimeStart}
        </if>
        <if test="params.createTimeStart!=null and params.createTimeStart!=''">
            AND je.create_time <![CDATA[ >= ]]> #{params.createTimeStart}
        </if>
        <if test="params.createTimeEnd!=null and params.createTimeEnd!=''">
            AND je.create_time  <![CDATA[ <= ]]> #{params.createTimeEnd}
        </if>
        <if test="params.is_involve_everyone != null and params.is_involve_everyone != ''">
            AND je.is_involve_everyone = #{params.is_involve_everyone}
        </if>
        <if test="params.is_involve_everyone_count != null and params.is_involve_everyone_count != ''">
            AND je.is_involve_everyone_count = #{params.is_involve_everyone_count}
        </if>

        <if test="params.logType!=null and params.logType!=''">
            <if test="params.logType=='0'.toString()">
                and je.status='1'
            </if>
            <if test="params.logType!='0'.toString()">
                HAVING mineLatestLogTypeNew = #{params.logType} and je.status!='1'
            </if>
        </if>
        ) a
        LEFT JOIN (SELECT event_id from jl_event_approve where del_flag='0' and approve_type='1' GROUP BY event_id) d ON
        d.event_id=a.id
        INNER JOIN jl_event_overdue_warning_config b ON b.risk_type=a.riskLevel
        LEFT JOIN (SELECT sum(approve_days) AS approve_days ,event_id from jl_event_approve where del_flag='0' and
        approve_type='0' GROUP BY event_id) c ON c.event_id=a.id
        where 1=1
        <!--and (
            CASE
                WHEN a.createBy = #{params._user.id} THEN
                    (a.deal_result_1!='1' or a.deal_result_1 is null)
                ELSE
                    a.eventStatus!='1'
            END
        )-->
        <!--and a.eventStatus!='1'-->
        <if test="params.status!=null and params.status!=''">
            AND status = #{params.status}
        </if>
        ORDER BY
        a.flowStatus asc,
        a.completeTime ASC
    </select>

    <select id="findEventPageList" 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
        FROM
        jl_event je
        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 jefl.del_flag = '0'
        <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.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.createTime!=null">
            AND DATE_FORMAT(je.create_time,'%Y-%m-%d') = #{params.createTime}
        </if>
        <if test="params.resolveWay != null and params.resolveWay != ''">
            AND jefl.resolve_way = #{params.resolveWay}
        </if>
        ) a
        ORDER BY `status` DESC, createTime DESC
    </select>

    <update id="updateEarlyWarning" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        UPDATE
        jl_event
        SET
        <if test="type != null and type != ''">
            is_scanned = '0',
            scanning_time = NOW(),
        </if>
        is_early_warning = #{isEarlyWarning}
        WHERE
        id = #{eventId}
    </update>

    <select id="findEventCount" parameterType="map" resultType="map">
        SELECT
        count(*) num
        FROM
        jl_event
        WHERE
        del_flag = '0'
        <if test="eventType != null and eventType != ''">
            and event_type = #{eventType}
        </if>
        <if test="currentYear != null and currentYear != ''">
            and YEAR(create_time) = #{currentYear}
        </if>
        <if test="communityId != null and communityId != ''">
            and community_id = #{communityId}
        </if>
    </select>

    <update id="updateSuperviseHandle" parameterType="map">
        UPDATE
        jl_event
        SET
        is_supervise_handle = '1'
        WHERE
        id = #{eventId}
    </update>

    <select id="findEventFocusAreasList"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" expose="true">
        SELECT * FROM (
        SELECT
        je.id,
        je.address,
        je.dispute_description disputeDescription,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d') occurrenceTime,
        (SELECT is_resolve FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time DESC LIMIT 1) isResolve,
        (SELECT risk_level FROM jl_event_flow_log WHERE event_id = je.id ORDER BY create_time DESC LIMIT 1) riskLevel,
        sd.`label` eventCategoryName,
        sd1.`label`eventTypeName
        FROM
        jl_event je
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        LEFT JOIN sys_dict sd1 ON sd.id = sd1.`parent_id`
        WHERE
        sd.type = 'event_category'
        AND sd1.type = 'event_category'
        AND je.event_type_id = sd1.`value`
        AND sd.parent_id = '0'
        <if test="params.occurrenceDate != null and params.occurrenceDate != ''">
            AND je.occurrence_time LIKE CONCAT(#{params.occurrenceDate},'%')
        </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.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>) A
    </select>

    <select id="getStatisticsTriggerNumber" resultType="map" expose="true">
        SELECT
        (SELECT COUNT(1) FROM jl_trigger_event where type='1'
        <if test="_user.company_grade == '3'.toString()"><!--市-->
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id} or
            city_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} or
            area_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} or
            area_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade == '6'.toString()"><!--社区-->
            AND community_id = #{_user.company_id}
        </if>
        ) triggerNum,
        <!--(SELECT COUNT(1) FROM jl_event_focus_areas_type) typeNum,-->
        (select
        count(1)
        from jl_focus_area_new a,sys_dict b,sys_area c
        where 1=1 and a.event_category_id=b.value and b.type='event_category'
        and a.area_code=c.code
        and a.data_type in ('0','1')
        <if test="_user.company_grade=='3'.toString()">
            and a.area_code in (select id from sys_office where parent_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade=='4'.toString()">
            and a.area_code=#{_user.company_id}
        </if>
        <if test="_user.company_grade == '5'.toString()"><!--街道-->
            AND a.area_code in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id} or
            area_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade == '6'.toString()"><!--社区-->
            AND a.area_code = #{_user.company_id}
        </if>
        ) typeNum,
        (select
        count(1)
        from jl_focus_region_new a,sys_area b
        where 1=1 and a.area_code=b.code
        and a.data_type in ('0','1')
        <if test="_user.company_grade=='3'.toString()">
            and a.area_code in (select id from sys_office where parent_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade=='4'.toString()">
            and a.area_code=#{_user.company_id}
        </if>
        <if test="_user.company_grade == '5'.toString()"><!--街道-->
            AND a.area_code in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id} or
            area_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade == '6'.toString()"><!--社区-->
            AND a.area_code = #{_user.company_id}
        </if>
        ) cityNum,
        <!--0 eventNum-->
        (SELECT COUNT(1) FROM jl_event_trigger where status='1'
        <if test="_user.company_grade == '3'.toString()"><!--市-->
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id} or
            city_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} or
            area_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} or
            area_id=#{_user.company_id})
        </if>
        <if test="_user.company_grade == '6'.toString()"><!--社区-->
            AND community_id = #{_user.company_id}
        </if>
        ) eventNum
    </select>

    <!--<update id="updateEventRiskLevel" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        update jl_event set risk_level=#{riskLevel},is_scanned=0
        where id=#{eventId} and risk_level!=#{riskLevel}
    </update>-->

    <update id="updateEventRiskLevel" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        UPDATE jl_event
        <set>
            <if test="riskLevel != null and riskLevel != ''">
                risk_level = #{riskLevel},
            </if>
            <if test="riskName != null and riskName != ''">
                risk_name = #{riskName},
            </if>
            is_scanned = 0
        </set>
        WHERE id = #{eventId} <!--AND risk_level != #{riskLevel}-->
    </update>

    <select id="getOverdueEventCount"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.OverdueEventStatisticsDTO" resultType="Integer">
        SELECT count(*) FROM jl_event
        WHERE is_early_warning = '4'
        AND del_flag = '0'
        AND DATE_ADD(early_warning_time, INTERVAL 5 DAY) &lt; CURDATE()
        AND (risk_level = '复杂矛盾纠纷' or risk_level = '重大矛盾纠纷')
        <if test="status != null and status != ''">
            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>
    </select>

    <select id="getOverdueEventCountHistory"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.OverdueEventStatisticsDTO" resultType="Integer">
        SELECT count(*) FROM jl_event
        WHERE is_early_warning = '4'
        AND del_flag = '0'
        AND early_warning_time is not null
        AND complete_time is not null
        AND DATE_ADD(early_warning_time, INTERVAL 5 DAY) &lt; complete_time
        AND (risk_level = '复杂矛盾纠纷' or risk_level = '重大矛盾纠纷')
        <if test="status != null and status != ''">
            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>
    </select>

    <select id="findOverdueEventHistoryPageList"
            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,
        DATEDIFF(je.complete_time, DATE_ADD(je.early_warning_time, INTERVAL 5 DAY)) as overdueDays
        FROM
        jl_event je
        LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id
        WHERE
        je.del_flag = '0'
        AND je.is_early_warning = '4'
        AND je.early_warning_time is not null
        AND je.complete_time is not null
        AND DATE_ADD(je.early_warning_time, INTERVAL 5 DAY) &lt; complete_time
        AND (je.risk_level = '复杂矛盾纠纷' or je.risk_level = '重大矛盾纠纷')
        AND je.status = '1'
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </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.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.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._user.company_grade == '3'.toString()">
            AND je.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 je.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 je.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 je.community_id = #{params._user.company_id}
        </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 `status` DESC, createTime DESC
    </select>

    <select id="findOverdueEventPageList"
            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,
        DATEDIFF(CURDATE(), DATE_ADD(je.early_warning_time, INTERVAL 5 DAY)) as overdueDays
        FROM
        jl_event je
        LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id
        WHERE
        je.del_flag = '0'
        AND je.is_early_warning = '4'
        AND DATE_ADD(je.early_warning_time, INTERVAL 5 DAY) &lt; CURDATE()
        AND (je.risk_level = '复杂矛盾纠纷' or je.risk_level = '重大矛盾纠纷')
        <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.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.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.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._user.company_grade == '3'.toString()">
            AND je.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 je.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 je.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 je.community_id = #{params._user.company_id}
        </if>
        ) a
        ORDER BY `status` DESC, createTime DESC
    </select>

    <select id="getInvolveEveryoneCount"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.InvolveEveryoneStatisticsDTO" resultType="Integer">
        select count(1) as count
        from jl_event_trigger
        <where>
            <if test="triggerType != null">
                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 count(*) FROM jl_event
        WHERE del_flag = '0'
        <if test="status != null and status != ''">
            AND status = #{status}
        </if>
        <if test="isInvolveEveryoneCount == '0'.toString() or isInvolveEveryoneCount == '1'.toString() or isInvolveEveryoneCount == '2'.toString()">
            AND is_involve_everyone_count = #{isInvolveEveryoneCount}
        </if>
        <if test="isInvolveEveryoneCount == null or isInvolveEveryoneCount == ''">
            AND is_involve_everyone = '1'
        </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="cityId == null and _user.company_grade == '3'.toString()">
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="cityId == null and _user.company_grade == '4'.toString()">
            AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="cityId == null and _user.company_grade == '5'.toString()">
            AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
        </if>
        <if test="cityId == null and _user.company_grade == '6'.toString()">
            AND community_id = #{_user.company_id}
        </if>-->
    </select>

    <select id="getInvolveEveryoneCountHistory"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.InvolveEveryoneStatisticsDTO" resultType="Integer">
        select count(1) as count
        from jl_event_trigger
        where `status` = 2
        <if test="triggerType != null">
            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>

    <select id="findInvolveEveryonePageList"
            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_trigger as 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.trigger_type='3'
        <if test="params._user.company_grade == '3'.toString()">
            AND je.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 je.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 je.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 je.community_id = #{params._user.company_id}
        </if>
        <if test="params.status != null and params.status != ''">
            AND je.status = #{params.status}
        </if>
        <if test="params.status == null">
            AND je.status = '2'
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </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.startTime != null and params.startTime != ''">
            AND je.occurrence_time >= #{params.startTime}
        </if>
        <if test="params.endTime != null and params.endTime != ''">
            AND #{params.endTime} >= je.occurrence_time
        </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.alarmLevel == 1 or params.alarmLevel == 2 or params.alarmLevel == 3">
            AND t.alarm_level = #{params.alarmLevel}
        </if>
        <!--<if test="params.is_involve_everyone_count == '0'.toString() or params.is_involve_everyone_count == '1'.toString() or params.is_involve_everyone_count == '2'.toString()">
            AND je.is_involve_everyone_count = #{params.is_involve_everyone_count}
        </if>-->
        <!--<if test="params.is_involve_everyone_count == null or params.is_involve_everyone_count == ''">
            AND je.is_involve_everyone = '1'
        </if>-->
        ) a
        ORDER BY `status` DESC, createTime DESC
    </select>

    <select id="findInvolveEveryoneHistoryPageList"
            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_trigger as 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.trigger_type='3'
        AND t.status = '2'
        <if test="params._user.company_grade == '3'.toString()">
            AND je.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 je.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 je.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 je.community_id = #{params._user.company_id}
        </if>
        <if test="params.eventCode != null and params.eventCode != ''">
            AND je.event_code = #{params.eventCode}
        </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.startTime != null and params.startTime != ''">
            AND je.occurrence_time >= #{params.startTime}
        </if>
        <if test="params.endTime != null and params.endTime != ''">
            AND #{params.endTime} >= je.occurrence_time
        </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.alarmLevel == 1 or params.alarmLevel == 2 or params.alarmLevel == 3">
            AND t.alarm_level = #{params.alarmLevel}
        </if>
        ) a
        ORDER BY `status` DESC, createTime DESC
    </select>

    <update id="delEventTrigger" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        update jl_event_trigger set status= '2' WHERE trigger_type='2'
        and status= '1'
        and community_id=#{communityId}
        and event_category_id=#{eventCategoryId}
        and event_type_id=#{eventTypeId}
    </update>

    <update id="delStakeholderEventTrigger" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        update jl_event_trigger set status= '2' WHERE event_id=#{eventId}
    </update>

    <select id="getFax" parameterType="String" resultType="String">
        select fax from sys_office where id=#{id}
    </select>

    <select id="getOfficeId" parameterType="String" resultType="String">
        select office_id from sys_user
        where id=(select create_by from sys_office where id=#{id})
    </select>

    <select id="getOfficeIdByLogId" parameterType="String" resultType="String">
        select office_id from sys_user
        where id=(select create_by from jl_event_flow_log where id=#{id})
    </select>

    <select id="getEventCountAll" parameterType="map" resultType="Integer">
        select count(id)
        from
        jl_event
        WHERE
        occurrence_time &gt;= #{occurrenceStartTime}
        and
        occurrence_time &lt;= #{occurrenceEndTime}
        <if test="eventCategoryId!=null and eventCategoryId !=''">
            event_category_id = #{eventCategoryId}
        </if>
    </select>

    <select id="getEventCountAllByCode" parameterType="map" resultType="Integer">
        SELECT
        count( je.id )
        FROM
        jl_event je
        left join sys_dict sd on je.event_category_id = sd.value and sd.type = 'event_category' and sd.parent_id = '0'
        and sd.`value` != 'event_category20'
        WHERE
        je.occurrence_time &gt;= #{occurrenceStartTime}
        and
        je.occurrence_time &lt;= #{occurrenceEndTime}
        <if test="regionLevel == '2'.toString()">
            AND je.city_id = #{regionId}
        </if>
        <if test="regionLevel == '3'.toString()">
            AND je.area_id = #{regionId}
        </if>
        <if test="eventCategoryId!=null and eventCategoryId !=''">
            and je.event_category_id = #{eventCategoryId}
        </if>
    </select>

    <select id="getEventIdAllByCode" parameterType="map" resultType="map">
        SELECT
        je.id  id
        FROM
        jl_event je
        left join sys_dict sd on je.event_category_id = sd.value and sd.type = 'event_category' and sd.parent_id = '0'
        and sd.`value` != 'event_category20'
        WHERE
        je.occurrence_time &gt;= #{occurrenceStartTime}
        and
        je.occurrence_time &lt;= #{occurrenceEndTime}
        <if test="regionLevel == '2'.toString()">
            AND je.city_id = #{regionId}
        </if>
        <if test="regionLevel == '3'.toString()">
            AND je.area_id = #{regionId}
        </if>
        <if test="eventCategoryId!=null and eventCategoryId !=''">
            and je.event_category_id = #{eventCategoryId}
        </if>
    </select>
    <select id="getAreaFousWarringList" parameterType="map"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.FousAreaVo">
        select
        je.id id,
        je.event_code eventCode,
        je.event_category_id eventCategoryId,
        je.event_type_id eventTypeId,
        je.address address,
        je.dispute_description disputeDescription,
        je.occurrence_time occurrenceTime,
        je.`status` `status`,
        sd.`label` eventCategoryName,
        sd1.`label`eventTypeName
        FROM
        jl_event je
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        LEFT JOIN sys_dict sd1 ON sd.id = sd1.`parent_id` and sd.`value` != 'event_category20'
        WHERE
        sd.type = 'event_category'
        AND sd1.type = 'event_category'
        AND je.event_type_id = sd1.`value`
        AND sd.parent_id = '0'
        <if test="params.company_grade != null and params.company_grade !=''">
            <choose>
                <when test="params.company_grade == '3'.toString() ">
                    and city_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '4'.toString() ">
                    and area_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '5'.toString() ">
                    and street_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '6'.toString() ">
                    and community_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
            </choose>
        </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.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.month != null and params.month != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m') = #{params.month}
        </if>
        order by occurrence_time DESC
    </select>


    <select id="getAreaFousWarringListhistory" parameterType="map"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.FousAreaVo">
        select
        je.id id,
        je.event_code eventCode,
        je.event_category_id eventCategoryId,
        je.event_type_id eventTypeId,
        je.address address,
        je.dispute_description disputeDescription,
        je.occurrence_time occurrenceTime,
        je.`status` `status`,
        sd.`label` eventCategoryName,
        sd1.`label`eventTypeName
        FROM
        ji_warning_focus_areas_history jh
        left join jl_warning_history_event jhe on jh.fid = jhe.history_id
        left join jl_event je on jhe.event_id = je.id
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        LEFT JOIN sys_dict sd1 ON sd.id = sd1.`parent_id` and sd.`value` != 'event_category20'
        WHERE
        sd.type = 'event_category'
        AND sd1.type = 'event_category'
        AND je.event_type_id = sd1.`value`
        AND sd.parent_id = '0'
        <if test="params.company_grade != null and params.company_grade !=''">
            <choose>
                <when test="params.company_grade == '3'.toString() ">
                    and city_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '4'.toString() ">
                    and area_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '5'.toString() ">
                    and street_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
                <when test="params.company_grade == '6'.toString() ">
                    and community_id=#{params.company_id , jdbcType=VARCHAR}
                </when>
            </choose>
        </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.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.month != null and params.month != ''">
            AND DATE_FORMAT(je.occurrence_time,'%Y-%m') = #{params.month}
        </if>
        order by occurrence_time DESC
    </select>

    <select id="getEventCreateAndOffice"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="map">
        <!--select a.create_by,b.office_id from jl_event a,sys_user b
        where a.id=#{eventId} and a.create_by=b.id-->

        select flow_version,flow_id,office_id,grade_user_id
        from jl_event_flow_log
        where event_id=#{eventId}
        order by create_time asc limit 1

        <!--select a.flow_version,a.flow_id,b.office_id,a.grade_user_id
        from jl_event_flow_log a,sys_user b
        where a.event_id=#{eventId} and a.office_id=#{_user.company_id}
        and a.create_by=b.id
        order by a.create_time desc limit 1-->

    </select>

    <delete id="deleteEventFLowLogCountContradiction" parameterType="map">
        delete from jl_event_flow_log_count_contradiction where event_id = #{id}
    </delete>
    <delete id="deleteTriggerEvent" parameterType="map">
        delete from jl_trigger_event where trigger_event_id = #{id}
    </delete>
    <delete id="deleteEventTrigger" parameterType="map">
        delete from jl_event_trigger where event_id = #{id}
    </delete>
    <delete id="deleteEventRiskFactor" parameterType="map">
        delete from jl_event_risk_factor where event_id = #{id}
    </delete>
    <delete id="deleteEvent" parameterType="map">
        delete from jl_event where id = #{id}
    </delete>
    <delete id="deleteEventUser" parameterType="map">
        delete from jl_event_user where event_id = #{id}
    </delete>
    <delete id="deleteEventFlowLog" parameterType="map">
        delete from jl_event_flow_log where event_id = #{id}
    </delete>
    <delete id="deleteEventCountContradiction" parameterType="map">
        delete from jl_event_count_contradiction where id = #{id}
    </delete>
    <delete id="deleteSupplementInfo" parameterType="map">
        delete from jl_warring_supplement_event where event_id = #{id}
    </delete>

    <select id="getReportUnitStatisticCountList" parameterType="page"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo">
        select b.name,b.id as areaId,count(a.del_flag=0) as zzzxCount,b.type
        from jl_event as a
        right join sys_area as b on a.reporting_unit_code = b.id and a.del_flag=0
        <if test="params.createStartTime !=null and params.createStartTime!=''">
            and a.create_time >= #{params.createStartTime}
        </if>
        <if test="params.createEndTime !=null and params.createEndTime!=''">
            and create_time <![CDATA[ < ]]> ( #{params.createEndTime} + INTERVAL 1 DAY )
        </if>
        where 1=1
        and (b.parent_id=#{params.areaId} or b.id =#{params.areaId})
        group by b.name,b.id order by b.type,b.id
    </select>

    <select id="getReportUnitStatisticTotalCountList"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo">
        select b.name,b.id as areaId,count(a.del_flag=0) as zzzxCount,b.type
        from jl_event as a
        right join sys_area as b on a.reporting_unit_code = b.id and a.del_flag=0
        <if test="createStartTime !=null and createStartTime!=''">
            and a.create_time >= #{createStartTime}
        </if>
        <if test="createEndTime !=null and createEndTime!=''">
            and create_time <![CDATA[ < ]]> ( #{createEndTime} + INTERVAL 1 DAY )
        </if>
        where 1=1
        and (b.parent_id=#{areaId} or b.id =#{areaId})
        group by b.name,b.id order by b.type,b.id
    </select>

    <select id="getZzzxChildReportCount"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo" resultType="Integer">
        select
        count(*)
        from jl_event
        where
        reporting_unit_code in (select id from sys_area where find_in_set(#{areaId},parent_ids))
        and del_flag=0
        <if test="createStartTime !=null and createStartTime!=''">
            and create_time >= #{createStartTime}
        </if>
        <if test="createEndTime !=null and createEndTime!=''">
            and create_time <![CDATA[ < ]]> ( #{createEndTime} + INTERVAL 1 DAY )
        </if>
    </select>


    <select id="getHxbmChildReportCount"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo" resultType="Integer">
        select
        count(*)
        from jl_event
        where
        reporting_unit_code in (select id from sys_office where find_in_set(#{areaId},parent_ids) AND fax is not null )
        and del_flag=0
        <if test="createStartTime !=null and createStartTime!=''">
            and create_time >= #{createStartTime}
        </if>
        <if test="createEndTime !=null and createEndTime!=''">
            and create_time <![CDATA[ < ]]> ( #{createEndTime} + INTERVAL 1 DAY )
        </if>
    </select>

    <select id="getHxbmReportCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.EventReportUnitStatisticVo"
            resultType="Integer">
        select
        count(*)
        from jl_event
        where
        reporting_unit_code in (select id from sys_office where fax is not null and area_id = (#{areaId}))
        and del_flag=0
        <if test="createStartTime !=null and createStartTime!=''">
            and create_time >= #{createStartTime}
        </if>
        <if test="createEndTime !=null and createEndTime!=''">
            and create_time <![CDATA[ < ]]> ( #{createEndTime} + INTERVAL 1 DAY )
        </if>
    </select>

    <select id="getUserAreaId" parameterType="String" resultType="String">
        select area_id from sys_user as a left join sys_office as b on a.office_id = b.id where a.id = #{id}
    </select>

    <select id="getEventById" parameterType="map"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        select
        id,
        event_type as eventType,
        event_category_id as eventCategoryId,
        event_type_id as eventTypeId,
        city_id as cityId,
        area_id as areaId,
        street_id as streetId,
        community_id as communityId,
        event_code as eventCode,
        occurrence_time as occurrenceTime
        from jl_event
        where event_type='2'
        and del_flag='0'
        and id = #{id}
    </select>


    <select id="selectWarningAreaPageHistoryList" parameterType="page" resultType="map" expose="true">
        select
        je.fid fid,
        je.event_category_id as eventCategoryId,
        sd.label as eventCategoryName,
        je.count as count,
        je.statistical_time as statisticalTime,
        je.percentage as percentage
        from
        ji_warning_focus_areas_history je
        LEFT JOIN sys_dict sd ON je.event_category_id = sd.`value`
        and sd.parent_id = 0 and sd.type = 'event_category'
        <where>
            <if test="params.eventCategoryId !=null and params.eventCategoryId !=''">
                AND je.event_category_id=#{params.eventCategoryId}
            </if>
            <if test="params.occurrenceStartDate!=null and params.occurrenceStartDate!=''">
                AND je.statistical_time >= #{params.occurrenceStartDate}
            </if>
            <if test="params.occurrenceEndDate!=null and params.occurrenceEndDate!=''">
                AND je.statistical_time &lt;= #{params.occurrenceEndDate}
            </if>
            <if test="params.cityId != null and params.cityId!=''">
                <if test="params.areaId == null and params.areaId==''">
                    AND je.area_level = 2 AND region_code = #{params.cityId}
                </if>
                <if test="params.areaId != null and params.areaId!=''">
                    AND je.area_level = 3 AND region_code = #{params.areaId}
                </if>
            </if>
            <if test="params._user.company_grade == '2'.toString()">
                AND je.area_level = 1
            </if>
            <if test="params._user.company_grade == '3'.toString()">
                AND je.area_level = 2 AND region_code = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND je.area_level = 3 AND region_code = #{params._user.company_id}
            </if>
            <if test="params.alarmLevel != null and params.alarmLevel != ''">
                AND je.alarm_level = #{params.alarmLevel}
            </if>
        </where>
        order by statistical_time desc
    </select>
</mapper>
