<?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.eventuser" name="当事人">

    <select id="selectUserByEventIdList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser">
        SELECT
            id,
            event_user_type eventUserType,
            event_user_name eventUserName,
            is_keynote isKeynote,
            keynote_type keynoteType,
            CAST(AES_DECRYPT(FROM_BASE64(event_cardno),'*#id#') AS CHAR) AS eventCardno,
            event_tel eventTel,
            tyshxydm,
            event_represent eventRepresent,
            remark
        FROM
            jl_event_user
        WHERE
            event_id = #{eventId}
    </select>

    <!-- 更新数据 -->
    <update id="updateEventUserById" parameterType="map" name="修改当事人数据" desc="修改当事人数据" expose="true">
        UPDATE
            jl_event_user
        SET
            <if test="event_user_type != null and event_user_type != ''">
                event_user_type = #{event_user_type},
            </if>
            <if test="event_user_name != null and event_user_name != ''">
                event_user_name = #{event_user_name},
            </if>
            <if test="event_cardno != null and event_cardno != ''">
                event_cardno = TO_BASE64(AES_ENCRYPT(#{event_cardno},'*#id#')),
            </if>
            <if test="event_tel != null and event_tel != ''">
                event_tel = #{event_tel},
            </if>
            <if test="tyshxydm != null and tyshxydm != ''">
                tyshxydm = #{tyshxydm},
            </if>
            <if test="event_represent != null and event_represent != ''">
                event_represent = #{event_represent},
            </if>
            <if test="remark != null and remark != ''">
                remark = #{remark},
            </if>
                update_by = #{_user.id},
                update_time = NOW()
        WHERE
            id = #{id}
    </update>

    <insert id="insertEventUserKey" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo">
            INSERT INTO jl_event_user(
                id,
                event_id,
                event_user_type,
                event_user_name,
                event_cardno,
                event_tel,
                tyshxydm,
                event_represent,
                remark,
                is_keynote,
                keynote_type,
                create_by,
                create_time
            ) VALUES (
                UUID(),
                #{eventId},
                #{eventUserType},
                #{eventUserName},
                TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#')),
                #{eventTel},
                #{tyshxydm},
                #{eventRepresent},
                #{remark},
                #{isKeynote},
                #{keynoteType},
                #{_user.id},
                NOW()
            )
    </insert>

    <delete id="updateEventUserDel" parameterType="map"  name="物理删除人员" desc="物理删除人员" expose="true">
        DELETE FROM jl_event_user WHERE event_id = #{event_id}
    </delete>

    <select id="getUserEventNumber" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo" resultType="Integer">
        SELECT
            IFNULL(COUNT(b.id),0)
        FROM
            jl_event_user a
        INNER JOIN jl_event b ON a.event_id = b.id
        WHERE
            b.del_flag = '0'
        AND b.`status` = '2'
        AND a.event_card_type = '1'
        AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
    </select>

    <select id="getTriggerEventUserNumber" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser" resultType="Integer">
        SELECT
            IFNULL(COUNT(id),0)
        FROM
            jl_trigger_event
        WHERE
            cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
        AND type = '1'
    </select>

    <select id="getEventTriggerInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
        select
        id,
        event_category_id as eventCategoryId,
        event_type_id as eventTypeId,
        trigger_type as triggerType,
        trigger_time as triggerTime,
        city_id as cityId,
        area_id as areaId,
        street_id as streetId,
        community_id as communityId
        from jl_event_trigger
        where
        event_category_id=#{eventCategoryId}
        and event_type_id=#{eventTypeId}
        <!--and trigger_type=#{triggerType}-->
        and trigger_type=#{triggerType}
        and city_id=#{cityId}
        and area_id=#{areaId}
        and street_id=#{streetId}
        and community_id=#{communityId}
        <if test="alarmLevel != null">
            and alarm_level=#{alarmLevel}
        </if>
        limit 1
    </select>

    <insert id="insertEventTriggerForRisk" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
        INSERT IGNORE INTO jl_event_trigger(
        id,
        event_category_id,
        event_type_id,
        trigger_type,
        trigger_time,
        city_id,
        area_id,
        street_id,
        community_id,
        alarm_level,
        event_id
        ) VALUES (
        #{id},
        #{eventCategoryId},
        #{eventTypeId},
        #{triggerType},
        now(),
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId},
        #{alarmLevel},
        #{eventId}
        )
    </insert>

    <update id="updateEventTriggerForRisk" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventTrigger">
            UPDATE
            jl_event_trigger
            SET
            alarm_level = #{alarmLevel},
            status = 1
            WHERE
            id=#{id}
    </update>

    <insert id="insertTriggerEvent" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventUserInsertVo">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_trigger_event WHERE cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
        </selectKey>
        <if test="count > 0">
            UPDATE
            jl_trigger_event a,
            (SELECT
                GROUP_CONCAT( DISTINCT a.event_user_name) name,
                GROUP_CONCAT( DISTINCT a.event_tel) tel,
                GROUP_CONCAT( DISTINCT a.keynote_type) user_type,
                GROUP_CONCAT( DISTINCT b.city_id) city_id,
                GROUP_CONCAT( DISTINCT b.area_id) area_id,
                GROUP_CONCAT( DISTINCT b.street_id) street_id,
                GROUP_CONCAT( DISTINCT b.community_id) community_id,
                COUNT(DISTINCT b.id) num,
                GROUP_CONCAT(DISTINCT b.id) eventNum
            FROM
                jl_event_user a
            INNER JOIN jl_event b ON a.event_id = b.id
            WHERE
                b.del_flag = '0'
            AND b.`status` = '2'
            AND a.event_card_type = '1'
            AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))) b
            SET
                a.`name` = b.`name`,
                a.city_id = b.city_id,
                a.area_id = b.area_id,
                a.street_id = b.street_id,
                a.community_id = b.community_id,
                a.tel = b.tel,
                a.user_type = b.user_type,
                a.trigger_num = num,
                a.trigger_event_id = eventNum,
                a.trigger_time = NOW(),
                a.update_time = NOW(),
                a.type = '1'
            WHERE
            a.cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
        </if>
        <if test="count == 0">
            INSERT INTO jl_trigger_event
            SELECT
                UUID(),
                GROUP_CONCAT( DISTINCT a.event_user_name),
                a.event_cardno,
                GROUP_CONCAT( DISTINCT a.event_tel),
                GROUP_CONCAT( DISTINCT a.keynote_type),
                GROUP_CONCAT( DISTINCT b.city_id),
                GROUP_CONCAT( DISTINCT b.area_id),
                GROUP_CONCAT( DISTINCT b.street_id),
                GROUP_CONCAT( DISTINCT b.community_id),
                NOW(),
                COUNT(DISTINCT b.id),
                GROUP_CONCAT(DISTINCT b.id),
                '1',
                NOW(),
                NOW()
            FROM
                jl_event_user a
            INNER JOIN jl_event b ON a.event_id = b.id
            WHERE
                b.del_flag = '0'
            AND b.`status` = '2'
            AND a.event_card_type = '1'
            AND a.event_cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#'))
        </if>
    </insert>

    <update id="updateTriggerEventType" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventUser">
        UPDATE jl_trigger_event SET type = '2' WHERE cardno = TO_BASE64(AES_ENCRYPT(#{eventCardno},'*#id#')) AND type = '1'
    </update>

    <select id="findTriggerEventList" parameterType="page" resultType="map" name="风险预警列表" expose="true">
        SELECT
            id,
            name,
            CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),4)) AS cardno,
            tel,
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(user_type,'1','刑满释放人员') ,'2','严重精神障碍患者') ,'3','“三失一偏”人员') ,'4','涉重点涉法涉诉类矛盾人员') ,'5','涉重点信访事项人员'), '6', '社区矫正人员'), '7', '吸毒人员'), '8', '问题青少年') user_type,
            DATE_FORMAT(trigger_time,'%Y-%m-%d %T')trigger_time,
            trigger_num,
            trigger_event_id,
            type
        FROM
            jl_trigger_event
        <where>
            <if test="params.cityId != null and params.cityId != ''">
                AND city_id = #{params.cityId}
            </if>
            <if test="params.areaId != null and params.areaId != ''">
                AND area_id = #{params.areaId}
            </if>
            <if test="params.type == null or params.type == ''">
                AND type = '1'
            </if>
            <if test="params.type != null and params.type != ''">
                AND type = #{params.type}
            </if>
            <if test="params.cardno != null and params.cardno != ''">
                AND cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
            </if>
            <if test="params.trigger_num != null and params.trigger_num != ''">
                AND trigger_num = #{params.trigger_num}
            </if>
            <if test="params.name != null and params.name != ''">
                AND FIND_IN_SET(#{params.name},name)
            </if>
            <if test="params.tel != null and params.tel != ''">
                AND FIND_IN_SET(#{params.tel},tel)
            </if>
            <if test="params.user_type != null and params.user_type != ''">
                AND user_type REGEXP #{params.user_type}
            </if>
            <!--<if test="params._user.company_grade == '3'.toString()">
                AND FIND_IN_SET(#{params._user.company_id},city_id)
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND FIND_IN_SET(#{params._user.company_id},area_id)
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                AND FIND_IN_SET(#{params._user.company_id},street_id)
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                AND FIND_IN_SET(#{params._user.company_id},community_id)
            </if>-->
            <if test="params._user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                AND community_id = #{params._user.company_id}
            </if>
        </where>
        order by trigger_time desc
    </select>

    <select id="findTriggerEventIdList" parameterType="page" resultType="map" expose="true">
        SELECT * FROM (
        SELECT
        DISTINCT je.id,
        je.id event_id,
        je.event_type,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id =
        `value`)event_category_id,
        (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` ) event_type_id,
        je.is_hidden_danger,
        je.is_special_tasks,
        je.is_difficult,
        je.is_public_opinion,
        je.is_early_warning,
        je.is_point_event,
        je.involved_number,
        DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrence_time,
        je.city_id,
        (SELECT name FROM sys_area WHERE code = je.city_id) city,
        je.area_id,
        (SELECT name FROM sys_area WHERE code = je.area_id) area,
        je.street_id,
        (SELECT name FROM sys_area WHERE code = je.street_id) street,
        je.community_id,
        (SELECT name FROM sys_area WHERE code = je.community_id) community,
        je.garden_id,
        je.building_id,
        je.unit_id,
        je.floor_id,
        je.doorplate_id,
        je.event_code,
        je.event_source,
        je.address,
        je.dispute_description,
        je.reporting_name,
        je.reporting_unit,
        je.reporting_garden,
        je.create_time,
        je.status,
        (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) risk_level,
        (SELECT status FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' ORDER BY create_time DESC LIMIT 1) log_status,
        (SELECT log_type FROM jl_event_flow_log WHERE event_id = je.id and del_flag='0' ORDER BY create_time DESC LIMIT 1) log_type
        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
        FIND_IN_SET(je.id,#{params.trigger_event_id})
        AND je.del_flag = '0'
        ) A
        ORDER BY occurrence_time DESC
    </select>

    <select id="findTriggerEventIdListForTriggerList" parameterType="page" resultType="map" expose="true">
        <!--SELECT *
        FROM (SELECT DISTINCT je.id,
        je.id                                                                               event_id,
        je.event_type,
        (SELECT label
        FROM sys_dict
        WHERE type = 'event_category'
        AND parent_id = '0'
        AND je.event_category_id =
        `value`)                                                                     event_category_id,
        (SELECT label
        FROM sys_dict
        WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value`) event_type_id,
        je.is_hidden_danger,
        je.is_special_tasks,
        je.is_difficult,
        je.is_public_opinion,
        je.is_early_warning,
        je.risk_name riskName,
        je.is_point_event,
        je.involved_number,
        DATE_FORMAT(je.occurrence_time, '%Y-%m-%d %T')                                      occurrence_time,
        je.city_id,
        (SELECT name FROM sys_area WHERE code = je.city_id)                                 city,
        je.area_id,
        (SELECT name FROM sys_area WHERE code = je.area_id)                                 area,
        je.street_id,
        (SELECT name FROM sys_area WHERE code = je.street_id)                               street,
        je.community_id,
        (SELECT name FROM sys_area WHERE code = je.community_id)                            community,
        je.garden_id,
        je.building_id,
        je.unit_id,
        je.floor_id,
        je.doorplate_id,
        je.event_code,
        je.event_source,
        je.address,
        je.dispute_description,
        je.reporting_name,
        je.reporting_unit,
        je.reporting_garden,
        je.create_time,
        je.status,
        (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)                                                                           risk_level
        FROM jl_event_trigger_contradictory_disputes left join  jl_event je on jl_event_trigger_contradictory_disputes.jl_event_id = je.id
        and jl_event_trigger_contradictory_disputes.jl_event_trigger = #{params.eventTrigger}
        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') A
        ORDER BY occurrence_time DESC-->
        SELECT
        *
        FROM
        (
        SELECT DISTINCT
        je.id,
        je.id event_id,
        je.event_type,
        ( SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id = `value` ) event_category_id,
        ( SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value` ) event_type_id,
        je.is_hidden_danger,
        je.is_special_tasks,
        je.is_difficult,
        je.is_public_opinion,
        je.is_early_warning,
        je.risk_name riskName,
        je.is_point_event,
        je.involved_number,
        DATE_FORMAT( je.occurrence_time, '%Y-%m-%d %T' ) occurrence_time,
        je.city_id,
        ( SELECT NAME FROM sys_area WHERE CODE = je.city_id ) city,
        je.area_id,
        ( SELECT NAME FROM sys_area WHERE CODE = je.area_id ) area,
        je.street_id,
        ( SELECT NAME FROM sys_area WHERE CODE = je.street_id ) street,
        je.community_id,
        ( SELECT NAME FROM sys_area WHERE CODE = je.community_id ) community,
        je.garden_id,
        je.building_id,
        je.unit_id,
        je.floor_id,
        je.doorplate_id,
        je.event_code,
        je.event_source,
        je.address,
        je.dispute_description,
        je.reporting_name,
        je.reporting_unit,
        je.reporting_garden,
        je.create_time,
        je.status,
        ( 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 ) risk_level
        FROM
        jl_event_trigger jet
        LEFT JOIN jl_event je ON jet.event_category_id = je.event_category_id
        AND jet.event_type_id = je.event_type_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 jefl.is_resolve = 2-->
        AND jet.id = #{params.eventTrigger}
        AND je.street_id = jet.street_id

        ) A
        ORDER BY
        occurrence_time DESC
    </select>

    <select id="getTriggerEventCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.ImportantUserStatisticsDTO" resultType="Integer">
        SELECT
        count(*)
        FROM
        jl_trigger_event
        where type = '1'
        <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="getTriggerEventCountHistory" parameterType="com.scpyun.platform.jilinsscgsdp.bean.dto.ImportantUserStatisticsDTO" resultType="Integer">
        SELECT count(1) FROM jl_trigger_event where `type` = '2'
        <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="findImportantUserHistoryPageList"  parameterType="page" resultType="map">
        SELECT
        id,
        name,
        CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),4)) AS cardno,
        tel,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(user_type,'1','刑满释放人员') ,'2','严重精神障碍患者') ,'3','“三失一偏”人员') ,'4','涉重点涉法涉诉类矛盾人员') ,'5','涉重点信访事项人员'), '6', '社区矫正人员'), '7', '吸毒人员'), '8', '问题青少年') user_type,
        DATE_FORMAT(trigger_time,'%Y-%m-%d %T') trigger_time,
        DATE_FORMAT(trigger_time,'%Y-%m-%d %T') lift_time,
        trigger_num,
        trigger_event_id,
        type
        FROM
        jl_trigger_event
        where type = '2'
        <if test="params.cityId != null and params.cityId != ''">
            AND city_id = #{params.cityId}
        </if>
        <if test="params.areaId != null and params.areaId != ''">
            AND area_id = #{params.areaId}
        </if>
        <if test="params.cardno != null and params.cardno != ''">
            AND cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
        </if>
        <if test="params.trigger_num != null and params.trigger_num != ''">
            AND trigger_num = #{params.trigger_num}
        </if>
        <if test="params.name != null and params.name != ''">
            AND FIND_IN_SET(#{params.name},name)
        </if>
        <if test="params.tel != null and params.tel != ''">
            AND FIND_IN_SET(#{params.tel},tel)
        </if>
        <if test="params.user_type != null and params.user_type != ''">
            AND user_type REGEXP #{params.user_type}
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND community_id = #{params._user.company_id}
        </if>
        order by update_time desc, trigger_time desc
    </select>

    <insert id="insertTriggerEventHistory" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.TriggerEventHistory">
        INSERT INTO jl_trigger_event_history(
        id,
        name,
        cardno,
        event_id,
        trigger_time,
        city_id,
        area_id,
        street_id,
        community_id
        ) VALUES (
        #{id},
        #{name},
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        #{eventId},
        now(),
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId}
        )
    </insert>

    <!--重点人历史记录-->
    <select id="getTriggerEventHistoryList" parameterType="page" resultType="map" expose="true">
        SELECT
        `name`,
        CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR),4)) AS cardno,
        MAX( trigger_time ) as triggerTime,
        ( SELECT count(*) FROM jl_trigger_event WHERE cardno = a.cardno AND type != 3 ) AS triggerCount,
        ( SELECT GROUP_CONCAT(event_id) FROM jl_event_user left join jl_event b on jl_event_user.event_id = b.id WHERE jl_event_user.event_cardno = a.cardno AND b.del_flag = 0 group by jl_event_user.event_cardno ) AS triggerEventId
        FROM
        jl_trigger_event_history a
        <where>
            <if test="params._user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                AND community_id = #{params._user.company_id}
            </if>
            <if test="params.cardno != null and params.cardno != ''">
                AND cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
            </if>
            <if test="params.name != null and params.name != ''">
                AND name = #{params.name}
            </if>
            <if test="params.startTime != null and params.startTime != ''">
                AND create_time BETWEEN #{params.startTime} AND #{params.endTime}
            </if>
        </where>
        GROUP BY cardno, `name`
    </select>

    <select id="getDisCountByIdCardPage" parameterType="page" resultType="map">
        select
        cast( AES_DECRYPT( FROM_BASE64 ( `card_no` ), '*#id#' ) AS CHAR ) cardNo,
        GROUP_CONCAT(DISTINCT name SEPARATOR '，') AS name,
        COUNT(*) AS recordCount
        from
        jl_key_person
        <where>
            1=1
            <if test="params.cardno != null and params.cardno != ''">
                and card_no = TO_BASE64(AES_ENCRYPT(upper(#{params.cardno}),'*#id#'))
            </if>
            <if test="params.name != null and params.name != ''">
                and name = #{params.name}
            </if>
            <if test="params.cityId != null and params.cityId != ''">
                and city_id = #{params.cityId}
            </if>
            <if test="params.areaId != null and params.areaId != ''">
                and area_id = #{params.areaId}
            </if>
            <if test="params.streetId != null and params.streetId != ''">
                and street_id = #{params.streetId}
            </if>
            <if test="params.communityId != null and params.communityId != ''">
                and community_id = #{params.communityId}
            </if>
        </where>
        group by card_no,sys_type
        having COUNT(*) >= 2
        order by
        <choose>
            <when test="params.orderBy == 'countAsc'">recordCount asc</when>
            <when test="params.orderBy == 'countDesc'">recordCount desc</when>
            <otherwise>create_date desc</otherwise>
        </choose>
    </select>

    <select id="getDisCountByIdCardList" parameterType="map" resultType="map">
        select
        t.`id` AS id,
        t.`name` AS name,
        t.`sys_type` AS sysType,
        cast( AES_DECRYPT( FROM_BASE64 ( t.`card_no` ), '*#id#' ) AS CHAR ) cardNo,
        t.`registered_residence` AS registeredResidence,
        t.`phone` AS phone,
        t.`responsible_company_by_assistance` AS responsibleCompanyByAssistance,
        t.`responsible_person_by_assistance` AS responsiblePersonByAssistance,
        t.`responsible_company_by_key_personnel` AS responsibleCompanyByKeyPersonnel,
        t.`responsible_person_key_personnel` AS responsiblePersonKeyPersonnel,
        t.`responsible_person` AS responsiblePerson,
        t.`responsible_company` AS responsibleCompany,
        t.`sex` AS sex,
        t.`address` AS address,
        t.`criminal_tendency` AS criminalTendency,
        t.`criminal_tendency_situation` AS criminalTendencySituation,
        t.`letters_visits_type` AS lettersVisitsType,
        t.`folk` AS folk,
        t.`related_person_name` AS relatedPersonName,
        t.`related_person_relation` AS relatedPersonRelation,
        t.`related_person_phone` AS relatedPersonPhone,
        t.`related_person_address` AS relatedPersonAddress,
        t.`control_company_supervisor` AS controlCompanySupervisor,
        t.`control_company_supervisor_person` AS controlCompanySupervisorPerson,
        t.`risk_level` AS riskLevel,
        t.`involving_conflicts_disputes` AS involvingConflictsDisputes,
        t.`create_date` AS createDate,
        t.`update_date` AS updateDate,
        t.`create_user` AS createUser,
        t.`update_user` AS updateUser,
        t.`region` AS region,
        t.`city_id` AS cityId,
        t.`area_id` AS areaId,
        t.`street_id` AS streetId,
        t.`community_id` AS communityId,
        t.`remarks` AS remarks,
        t.`hazard_assessment_level` AS hazardAssessmentLevel,
        CASE t.sys_type
        WHEN '1' THEN '“三失一偏”人员'
        WHEN '2' THEN '刑满释放人员'
        WHEN '3' THEN '严重精神障碍患者'
        WHEN '4' THEN '涉重点信访事项人员'
        WHEN '5' THEN '涉重点涉法涉诉类人员'
        ELSE '未知类别'
        END AS sysTypeText,
        city.`name` AS areaCity,
        area.`name` AS areaArea,
        street.`name` AS areaStreet,
        community.`name` AS areaCommunity
        from
        jl_key_person t
        LEFT JOIN sys_area city ON city.id = t.city_id
        LEFT JOIN sys_area area ON area.id = t.area_id
        LEFT JOIN sys_area street ON street.id = t.street_id
        LEFT JOIN sys_area community ON community.id = t.community_id
        where
        t.card_no =  TO_BASE64(AES_ENCRYPT(upper(#{cardNo}),'*#id#'))
        <if test="cityId != null and cityId != ''">
            and t.city_id = #{cityId}
        </if>
        <if test="areaId != null and areaId != ''">
            and t.area_id = #{areaId}
        </if>
        <if test="streetId != null and streetId != ''">
            and t.street_id = #{streetId}
        </if>
        <if test="communityId != null and communityId != ''">
            and t.community_id = #{communityId}
        </if>
        order by
        t.update_date desc
    </select>
</mapper>
