<?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.EventRepeatbleDao">

    <select id="selectEventRepeatbleList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRepeatble" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRepeatble">
        SELECT
            b.event_user_name,
            <!--b.event_cardno,-->
            CAST(AES_DECRYPT(FROM_BASE64(b.event_cardno),'*#id#') AS CHAR) as 'event_cardno',
            a.event_category_id,
            a.event_type_id,
            COUNT(DISTINCT b.event_id) AS event_count,
            (select label from sys_dict where type='event_category' and del_flag='0' and value=a.event_category_id) as event_category_id_dict,
            (select label from sys_dict where type='event_category' and del_flag='0' and value=a.event_type_id) as event_type_id_dict
        FROM
            jl_event_user b
        JOIN
            jl_event a ON b.event_id = a.id and a.del_flag='0'
        where b.event_cardno is not null and a.status='2'
        <if test="params.event_user_name!=null and params.event_user_name!=''">
            and event_user_name LIKE CONCAT(#{params.event_user_name}, '%')
        </if>
        <if test="params.event_cardno!=null and params.event_cardno!=''">
            AND b.event_cardno = TO_BASE64(AES_ENCRYPT(#{params.event_cardno},'*#id#'))
        </if>
        GROUP BY
            b.event_cardno, a.event_category_id, a.event_type_id
        having event_count>1
        order by b.event_cardno,a.event_category_id,a.event_type_id
    </select>

    <select id="eventRepeatbleInfoList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRepeatbleInfo" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRepeatbleInfo">
        select
        a.id,
        a.status,
        a.event_code,
        a.risk_level,
        DATE_FORMAT(a.create_time,'%Y-%m-%d %T') create_time,
        a.dispute_description,
        a.address,
        c.name as create_by,
        d.name as community_id,
        a.event_category_id,a.event_type_id,
        (select label from sys_dict where type='event_category' and del_flag='0' and value=a.event_category_id) as event_category_id_dict,
        (select label from sys_dict where type='event_category' and del_flag='0' and value=a.event_type_id) as event_type_id_dict,
        CAST(AES_DECRYPT(FROM_BASE64(b.event_cardno),'*#id#') AS CHAR) as 'event_cardno'
        from jl_event a
        join jl_event_user b on a.id=b.event_id
        join sys_user c on a.create_by=c.id
        join sys_area d on a.community_id=d.id
        where a.del_flag='0' and a.event_category_id=#{event_category_id} and a.event_type_id=#{event_type_id}
        and a.status='2' and b.event_cardno=TO_BASE64(AES_ENCRYPT(#{event_cardno},'*#id#'))
        order by a.create_time desc
    </select>

    <update id="updateEventRepeatbleInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.EventRepeatbleInfo">
        update jl_event set del_flag='1' where id=#{id}
    </update>



</mapper>
