<?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.murderManagement" name="命案管理">


    <select id="homicideCaseStatisticsCount" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase" >
        select count(1) as count from jl_homicide_case
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
    </select>

    <select id="homicideCaseStatisticsCountDeadNumber" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase" >
        select sum(dead_number) as count from jl_homicide_case
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
    </select>

    <select id="homicideCaseStatisticsCountOneKillOnePerson" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase" >
        select  count(1) as count from jl_homicide_case where dead_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})
            </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="_user.company_grade != '2'.toString()">
            and (
            jl_homicide_case.create_by=#{_user.id}
            or jl_homicide_case.create_by in (
            select id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
            del_flag='0'
            ) and del_flag='0'
            )
            )
        </if>
    </select>

    <select id="homicideCaseStatisticsCountOneKillTwoPerson" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase" >
        select  count(1) as count from jl_homicide_case where dead_type = '2'
            <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="_user.company_grade != '2'.toString()">
            and (
            jl_homicide_case.create_by=#{_user.id}
            or jl_homicide_case.create_by in (
            select id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
            del_flag='0'
            ) and del_flag='0'
            )
            )
        </if>
    </select>

    <select id="homicideCaseStatisticsCountOneKillMorePerson" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase" >
        select  count(1) as count from jl_homicide_case where dead_type = '3'
            <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="_user.company_grade != '2'.toString()">
            and (
            jl_homicide_case.create_by=#{_user.id}
            or jl_homicide_case.create_by in (
            select id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
            del_flag='0'
            ) and del_flag='0'
            )
            )
        </if>
    </select>

    <select id="keyPersonStatisticsForCity"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '3',uc_gov_region_info.region_name,uc_gov_region_info.county)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{_user.company_id},parent_ids))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.county order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForProvince"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select sys_area.name as cityName, count(distinct card_no) as count
        from jl_key_person
        left  join sys_area on jl_key_person.city_id = sys_area.id
        WHERE
        city_id is not null AND  city_id != ''
<!--        group by jl_key_person.city_id order by jl_key_person.city_id-->
<!--        <where>-->
            <if test="sysType != null and sysType != ''">
                and sys_type = #{sysType}
            </if>
<!--        </where>-->
        group by jl_key_person.city_id order by jl_key_person.city_id
    </select>

    <select id="keyPersonStatisticsForArea"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '4',uc_gov_region_info.region_name,uc_gov_region_info.town)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{_user.company_id},parent_ids))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.town order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForStreet"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '5',uc_gov_region_info.region_name,uc_gov_region_info.village)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{_user.company_id},parent_ids))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.village order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForCommunity"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '6',uc_gov_region_info.region_name,uc_gov_region_info.village)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where sys_user.company_id = #{_user.company_id}
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.village order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForRiskLevel"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select risk_level as riskLevel, count(card_no) as count
        from (select distinct card_no, risk_level
        from jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} )and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (select company_id from sys_user where id = create_user) = #{_user.company_id}
            </if>
        </where>
        ) a
        group by risk_level
    </select>

    <select id="findKeyPersonList" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select a.*
        from (select jl_key_person.id as id,
        jl_key_person.name as name,
        sys_type                             as                                 sysType,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) cardNo,
        registered_residence                 as                                 registeredResidence,
        phone,
        responsible_company_by_assistance    as                                 responsibleCompanyByAssistance,
        responsible_person_by_assistance     as                                 responsiblePersonByAssistance,
        responsible_company_by_key_personnel as                                 responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel     as                                 responsiblePersonKeyPersonnel,
        responsible_person                   as                                 responsiblePerson,
        responsible_company                  as                                 responsibleCompany,
        sex,
        address,
        criminal_tendency                    as                                 criminalTendency,
        criminal_tendency_situation          as                                 criminalTendencySituation,
        letters_visits_type                                                     lettersVisitsType,
        folk,
        involving_conflicts_disputes         as                                 involvingConflictsDisputes,
        region                              as                                  region,
        city_id                              as                                 cityId,
        area_id                              as                                 areaId,
        street_id                            as                                 streetId,
        community_id                         as                                 communityId,
        community.name communityName,
        jl_key_person.remarks as remarks,
        hazard_assessment_level              as                                 hazardAssessmentLevel,
        city.name      cityName,
        area.name      areaName,
        street.name    streetName,
        city.sort as sort1,
        area.sort as sort2,
        street.sort as sort3,
        related_person_name                  as                                 relatedPersonName,
        related_person_relation                                                 relatedPersonRelation,
        related_person_phone                 as                                 relatedPersonPhone,
        related_person_address               as                                 relatedPersonAddress,
        control_company_supervisor           as                                 controlCompanySupervisor,
        control_company_supervisor_person    as                                 controlCompanySupervisorPerson,
        risk_level                           as                                 riskLevel,
        jl_key_person.create_date                          as                                 createDate,
        jl_key_person.update_date                          as                                 updateDate,
        jl_key_person.create_user                          as                                 createUser,
        jl_key_person.update_user                          as                                 updateUser
        from jl_key_person
        left join sys_area city on jl_key_person.city_id = city.code
        left join sys_area area on jl_key_person.area_id = area.code
        left join sys_area street on jl_key_person.street_id = street.code
        left join sys_area community on jl_key_person.community_id = community.code
        <where> 1=1
            <if test="params.involvingConflictsDisputes != null and params.involvingConflictsDisputes != ''">
                and involving_conflicts_disputes like concat('%',#{params.involvingConflictsDisputes},'%')
            </if>
            <if test="params.name != null and params.name != ''">
                and jl_key_person.name like concat('%',#{params.name},'%')
            </if>
            <if test="params.sysType != null and params.sysType != ''">
                and sys_type = #{params.sysType}
            </if>
            <if test="params.cardNo != null and params.cardNo != ''">
                <!--and card_no like concat('%',#{params.cardNo},'%')-->
                and card_no like concat('%',TO_BASE64(AES_ENCRYPT(#{params.cardNo},'*#id#')),'%')
            </if>
            <if test="params.registeredResidence != null and params.registeredResidence != ''">
                and registered_residence = #{params.registeredResidence}
            </if>
            <if test="params.phone != null and params.phone != ''">
                and phone like concat('%',#{params.phone},'%')
            </if>
            <if test="params.hazardAssessmentLevel != null and params.hazardAssessmentLevel != ''">
                and hazard_assessment_level like concat('%',#{params.hazardAssessmentLevel},'%')
            </if>
            <if test="params.responsibleCompanyByAssistance != null and params.responsibleCompanyByAssistance != ''">
                and responsible_company_by_assistance like concat('%',#{params.responsibleCompanyByAssistance},'%')
            </if>
            <if test="params.responsiblePersonByAssistance != null and params.responsiblePersonByAssistance != ''">
                and responsible_person_by_assistance like concat('%',#{params.responsiblePersonByAssistance},'%')
            </if>
            <if test="params.responsibleCompanyByKeyPersonnel != null and params.responsibleCompanyByKeyPersonnel != ''">
                and responsible_company_by_key_personnel like concat('%',#{params.responsibleCompanyByKeyPersonnel},'%')
            </if>
            <if test="params.responsiblePersonKeyPersonnel != null and params.responsiblePersonKeyPersonnel != ''">
                and responsible_person_key_personnel like concat('%',#{params.responsiblePersonKeyPersonnel},'%')
            </if>
            <if test="params.responsiblePerson != null and params.responsiblePerson != ''">
                and responsible_person like concat('%',#{params.responsiblePerson},'%')
            </if>
            <if test="params.responsibleCompany != null and params.responsibleCompany != ''">
                and responsible_company like concat('%',#{params.responsibleCompany},'%')
            </if>
            <if test="params.sex != null and params.sex != ''">
                and sex = #{params.sex}
            </if>
            <if test="params.address != null and params.address != ''">
                and address = #{params.address}
            </if>
            <if test="params.criminalTendency != null and params.criminalTendency != ''">
                and criminal_tendency = #{params.criminalTendency}
            </if>
            <if test="params.criminalTendencySituation != null and params.criminalTendencySituation != ''">
                and criminal_tendency_situation = #{params.criminalTendencySituation}
            </if>
            <if test="params.lettersVisitsType != null and params.lettersVisitsType != ''">
                and letters_visits_type = #{params.lettersVisitsType}
            </if>
            <if test="params.folk != null and params.folk != ''">
                and folk = #{params.folk}
            </if>
            <if test="params.relatedPersonName != null and params.relatedPersonName != ''">
                and related_person_name = #{params.relatedPersonName}
            </if>
            <if test="params.relatedPersonRelation != null and params.relatedPersonRelation != ''">
                and related_person_relation = #{params.relatedPersonRelation}
            </if>
            <if test="params.relatedPersonPhone != null and params.relatedPersonPhone != ''">
                and related_person_phone = #{params.relatedPersonPhone}
            </if>
            <if test="params.relatedPersonAddress != null and params.relatedPersonAddress != ''">
                and related_person_address = #{params.relatedPersonAddress}
            </if>
            <if test="params.controlCompanySupervisor != null and params.controlCompanySupervisor != ''">
                and control_company_supervisor = #{params.controlCompanySupervisor}
            </if>
            <if test="params.controlCompanySupervisorPerson != null and params.controlCompanySupervisorPerson != ''">
                and control_company_supervisor_person = #{params.controlCompanySupervisorPerson}
            </if>
            <if test="params.riskLevel != null and params.riskLevel != ''">
                and risk_level = #{params.riskLevel}
            </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>-->
            <if test="params._user.company_grade != '2'.toString()">
                and (
                create_user=#{params._user.id}
                or create_user in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{params._user.company_id}, '%') and del_flag='0'
                ) and del_flag='0'
                )
                )
            </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>
            <if test="params.distCard != null and params.distCard !=''">
                AND card_no IN (SELECT card_no FROM jl_key_person
                <where>
                    sys_type = #{params.sysType}
                    <if test="params._user.company_grade != '2'.toString()">
                        and (
                        create_user=#{params._user.id}
                        or create_user in (
                        select id from sys_user where company_id in (
                        select id from sys_office where parent_ids LIKE CONCAT('%', #{params._user.company_id}, '%') and del_flag='0'
                        ) and del_flag='0' ) )
                    </if>
                </where>
                GROUP BY card_no HAVING COUNT(1) > 1)
            </if>
        </where>
        ) a
        <if test="params.distCard == null or params.distCard ==''">
            order by a.cardNo DESC, a.sort1, a.sort2, a.sort3
        </if>
        <if test="params.distCard != null and params.distCard !=''">
            order by a.sort1, a.sort2, a.sort3, a.cardNo
        </if>
    </select>


    <select id="findPersonListByRiskLevel" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
        GROUP_CONCAT(jl_key_person.NAME SEPARATOR ',') AS name,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) cardNo,
        risk_level as riskLevel
        from jl_key_person
        left join sys_area city on jl_key_person.city_id = city.code
        left join sys_area area on jl_key_person.area_id = area.code
        left join sys_area street on jl_key_person.street_id = street.code
        left join sys_area community on jl_key_person.community_id = community.code
        where 1=1
        <if test="params.riskLevel != null and params.riskLevel != ''">
            and risk_level = #{params.riskLevel}
        </if>
        <if test="params._user.company_grade != '2'.toString()">
            and (
            jl_key_person.create_user=#{params._user.id}
            or
            jl_key_person. create_user in
            ( select
            id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{params._user.company_id}, '%') and del_flag='0'
            ) and del_flag='0'
            )
            )
        </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>
        GROUP BY card_no
    </select>

    <select id="findKeyPersonListExport" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
        @rownum := @rownum + 1 AS xuhao,
        a.*
        from
        (select jl_key_person.id as id,
        jl_key_person.name as name,
        sys_type as sysType,
        <!--card_no cardNo,-->
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) cardNo,
        registered_residence as registeredResidence,
        phone,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as responsiblePersonKeyPersonnel,
        responsible_person as responsiblePerson,
        responsible_company as responsibleCompany,
        <!--sex,-->
        case sex when '1' then '男' when '2' then '女' end as sex,
        address,
        <!--criminal_tendency as criminalTendency,-->
        case criminal_tendency when '1' then '有' when '2' then '无' end as criminalTendency,
        (select group_concat(highly_concerned_situation) from jl_key_person_highly_concerned_rel where key_person_id=jl_key_person.id) as highly_concerned_situation,
        criminal_tendency_situation as criminalTendencySituation,
        letters_visits_type lettersVisitsType,
        folk,
        involving_conflicts_disputes as involvingConflictsDisputes,
        city_id as cityId,
        area_id as areaId,
        street_id as streetId,
        community_id as communityId,
        community.name communityName,
        jl_key_person.remarks as remarks,
        hazard_assessment_level as hazardAssessmentLevel,
        city.name cityName,
        area.name areaName,
        street.name streetName,
        city.sort as sort1,
        area.sort as sort2,
        street.sort as sort3,
        related_person_name as relatedPersonName,
        related_person_relation relatedPersonRelation,
        related_person_phone as relatedPersonPhone,
        related_person_address as relatedPersonAddress,
        control_company_supervisor as controlCompanySupervisor,
        control_company_supervisor_person as controlCompanySupervisorPerson,
        risk_level as riskLevel,
        jl_key_person.create_date as createDate,
        jl_key_person.update_date as updateDate,
        jl_key_person.create_user as createUser,
        jl_key_person.update_user as updateUser
        from jl_key_person
        left join sys_area city on jl_key_person.city_id = city.code
        left join sys_area area on jl_key_person.area_id = area.code
        left join sys_area street on jl_key_person.street_id = street.code
        left join sys_area community on jl_key_person.community_id = community.code
        <where> 1=1
            <if test="involvingConflictsDisputes != null and involvingConflictsDisputes != ''">
                and involving_conflicts_disputes like concat('%',#{involvingConflictsDisputes},'%')
            </if>
            <if test="name != null and name != ''">
                and jl_key_person.name like concat('%',#{name},'%')
            </if>
            <if test="sysType != null and sysType != ''">
                and sys_type = #{sysType}
            </if>
            <if test="cardNo != null and cardNo != ''">
                and card_no like concat('%',#{cardNo},'%')
            </if>
            <if test="registeredResidence != null and registeredResidence != ''">
                and registered_residence = #{registeredResidence}
            </if>
            <if test="phone != null and phone != ''">
                and phone like concat('%',#{phone},'%')
            </if>
            <if test="hazardAssessmentLevel != null and hazardAssessmentLevel != ''">
                and hazard_assessment_level like concat('%',#{hazardAssessmentLevel},'%')
            </if>
            <if test="responsibleCompanyByAssistance != null and responsibleCompanyByAssistance != ''">
                and responsible_company_by_assistance like concat('%',#{responsibleCompanyByAssistance},'%')
            </if>
            <if test="responsiblePersonByAssistance != null and responsiblePersonByAssistance != ''">
                and responsible_person_by_assistance like concat('%',#{responsiblePersonByAssistance},'%')
            </if>
            <if test="responsibleCompanyByKeyPersonnel != null and responsibleCompanyByKeyPersonnel != ''">
                and responsible_company_by_key_personnel like concat('%',#{responsibleCompanyByKeyPersonnel},'%')
            </if>
            <if test="responsiblePersonKeyPersonnel != null and responsiblePersonKeyPersonnel != ''">
                and responsible_person_key_personnel like concat('%',#{responsiblePersonKeyPersonnel},'%')
            </if>
            <if test="responsiblePerson != null and responsiblePerson != ''">
                and responsible_person like concat('%',#{responsiblePerson},'%')
            </if>
            <if test="responsibleCompany != null and responsibleCompany != ''">
                and responsible_company like concat('%',#{responsibleCompany},'%')
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="criminalTendency != null and criminalTendency != ''">
                and criminal_tendency = #{criminalTendency}
            </if>
            <if test="criminalTendencySituation != null and criminalTendencySituation != ''">
                and criminal_tendency_situation = #{criminalTendencySituation}
            </if>
            <if test="lettersVisitsType != null and lettersVisitsType != ''">
                and letters_visits_type = #{lettersVisitsType}
            </if>
            <if test="folk != null and folk != ''">
                and folk = #{folk}
            </if>
            <if test="relatedPersonName != null and relatedPersonName != ''">
                and related_person_name = #{relatedPersonName}
            </if>
            <if test="relatedPersonRelation != null and relatedPersonRelation != ''">
                and related_person_relation = #{relatedPersonRelation}
            </if>
            <if test="relatedPersonPhone != null and relatedPersonPhone != ''">
                and related_person_phone = #{relatedPersonPhone}
            </if>
            <if test="relatedPersonAddress != null and relatedPersonAddress != ''">
                and related_person_address = #{relatedPersonAddress}
            </if>
            <if test="controlCompanySupervisor != null and controlCompanySupervisor != ''">
                and control_company_supervisor = #{controlCompanySupervisor}
            </if>
            <if test="controlCompanySupervisorPerson != null and controlCompanySupervisorPerson != ''">
                and control_company_supervisor_person = #{controlCompanySupervisorPerson}
            </if>
            <if test="riskLevel != null and riskLevel != ''">
                and risk_level = #{riskLevel}
            </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="_user.company_grade != '2'.toString()">
                and (
                create_user=#{_user.id}
                or create_user in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and del_flag='0'
                ) and del_flag='0'
                )
                )
            </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="streetId!=null and streetId!=''">
                AND street_id = #{streetId}
            </if>
            <if test="communityId!=null and communityId!=''">
                AND community_id = #{communityId}
            </if>
        </where>
        ) a,
        (SELECT @rownum := 0) r
        order by a.sort1, a.sort2, a.sort3
    </select>

    <select id="findKeyPersonHighlyConcernedRelListByKeyPersonId"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonHighlyConcernedRel">
        select id,
        key_person_id as keyPersonId,
        card_no as cardNo,
        highly_concerned_situation as highlyConcernedSituation,
        create_date,
        update_date,
        create_user,
        update_user
        from jl_key_person_highly_concerned_rel where key_person_id = #{id}
    </select>

    <select id="findKeyPersonHighlyConcernedRelListByKeyPersonIdStr"
            parameterType="string"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonHighlyConcernedRel">
        select id,
        key_person_id as keyPersonId,
        card_no as cardNo,
        highly_concerned_situation as highlyConcernedSituation,
        create_date,
        update_date,
        create_user,
        update_user
        from jl_key_person_highly_concerned_rel where key_person_id = #{id}
    </select>

    <delete id="deleteKeyPersonById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        delete from jl_key_person where id = #{id}
    </delete>

    <select id="findKeyPersonStatistics"  resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select count(a.card_no) as count, sys_type
        from (select distinct rel.card_no, sys_type
        from jl_key_person_highly_concerned_rel rel
        left join jl_key_person jkp on jkp.id = rel.key_person_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (select company_id from sys_user where id = jkp.create_user) = #{_user.company_id}
            </if>
        </where>
        ) a
        group by a.sys_type
    </select>
    <select id="checkCardNoRepeatList" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
            id
        from
           jl_key_person
        where
        card_no = TO_BASE64(AES_ENCRYPT(#{cardNo,jdbcType=VARCHAR},'*#id#'))
        and community_id =#{communityId}
        and sys_type = #{sysType}
    </select>
    <insert id="insertKeyPersonById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        insert into jl_key_person
        (
        id,
        hazard_assessment_level,
        name,
        sys_type,
        card_no,
        registered_residence,
        phone,
        responsible_company_by_assistance,
        responsible_person_by_assistance,
        responsible_company_by_key_personnel,
        responsible_person_key_personnel,
        responsible_person,
        responsible_company,
        sex,
        address,
        criminal_tendency,
        criminal_tendency_situation,
        letters_visits_type,
        folk,
        related_person_name,
        related_person_relation,
        related_person_phone,
        related_person_address,
        control_company_supervisor,
        control_company_supervisor_person,
        risk_level,
        create_date,
        update_date,
        create_user,
        update_user,
        remarks,
        region,
        city_id,
        area_id,
        street_id,
        community_id,
        involving_conflicts_disputes
        ) values
        (
        #{id},
        #{hazardAssessmentLevel},
        #{name},
        #{sysType},
        <!--#{cardNo},-->
        TO_BASE64(AES_ENCRYPT(#{cardNo,jdbcType=VARCHAR},'*#id#')),
        #{registeredResidence},
        #{phone},
        #{responsibleCompanyByAssistance},
        #{responsiblePersonByAssistance},
        #{responsibleCompanyByKeyPersonnel},
        #{responsiblePersonKeyPersonnel},
        #{responsiblePerson},
        #{responsibleCompany},
        #{sex},
        #{address},
        #{criminalTendency},
        #{criminalTendencySituation},
        #{lettersVisitsType},
        #{folk},
        #{relatedPersonName},
        #{relatedPersonRelation},
        #{relatedPersonPhone},
        #{relatedPersonAddress},
        #{controlCompanySupervisor},
        #{controlCompanySupervisorPerson},
        #{riskLevel},
        now(),
        now(),
        #{_user.id},
        #{_user.id},
        #{remarks},
        #{region},
        #{cityId},
        #{areaId},
        #{streetId},
        #{communityId},
        #{involvingConflictsDisputes}
        )
    </insert>

    <insert id="insertKeyPersonHighlyConcernedRel" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonHighlyConcernedRel">
        insert into jl_key_person_highly_concerned_rel
        (
        id,
        key_person_id,
        card_no,
        highly_concerned_situation,
        create_date,
        update_date,
        create_user,
        update_user
        ) values
        (
        #{id},
        #{keyPersonId},
        #{cardNo},
        #{highlyConcernedSituation},
        now(),
        now(),
        #{_user.id},
        #{_user.id}
        )
    </insert>

    <update id="updateKeyPersonById"  parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson" >
        update jl_key_person
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="hazardAssessmentLevel != null and hazardAssessmentLevel != ''">
                hazard_assessment_level = #{hazardAssessmentLevel},
            </if>
            <if test="sysType != null and sysType != ''">
                sys_type = #{sysType},
            </if>
            <if test="cardNo != null and cardNo != ''">
                <!--card_no = #{cardNo},-->
                card_no=TO_BASE64(AES_ENCRYPT(#{cardNo,jdbcType=VARCHAR},'*#id#')),
            </if>
            <if test="registeredResidence != null and registeredResidence != ''">
                registered_residence = #{registeredResidence},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="responsibleCompanyByAssistance != null and responsibleCompanyByAssistance != ''">
                responsible_company_by_assistance = #{responsibleCompanyByAssistance},
            </if>
            <if test="responsiblePersonByAssistance != null and responsiblePersonByAssistance != ''">
                responsible_person_by_assistance = #{responsiblePersonByAssistance},
            </if>
            <if test="responsibleCompanyByKeyPersonnel != null and responsibleCompanyByKeyPersonnel != ''">
                responsible_company_by_key_personnel = #{responsibleCompanyByKeyPersonnel},
            </if>
            <if test="responsiblePersonKeyPersonnel != null and responsiblePersonKeyPersonnel != ''">
                responsible_person_key_personnel = #{responsiblePersonKeyPersonnel},
            </if>
            <if test="responsiblePerson != null and responsiblePerson != ''">
                responsible_person = #{responsiblePerson},
            </if>
            <if test="responsibleCompany != null and responsibleCompany != ''">
                responsible_company = #{responsibleCompany},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="criminalTendency != null and criminalTendency != ''">
                criminal_tendency = #{criminalTendency},
            </if>
            <if test="criminalTendencySituation != null and criminalTendencySituation != ''">
                criminal_tendency_situation = #{criminalTendencySituation},
            </if>
            <if test="lettersVisitsType != null and lettersVisitsType != ''">
                letters_visits_type = #{lettersVisitsType},
            </if>
            <if test="folk != null and folk != ''">
                folk = #{folk},
            </if>
            <if test="relatedPersonName != null and relatedPersonName != ''">
                related_person_name = #{relatedPersonName},
            </if>
            <if test="relatedPersonRelation != null and relatedPersonRelation != ''">
                related_person_relation = #{relatedPersonRelation},
            </if>
            <if test="relatedPersonPhone != null and relatedPersonPhone != ''">
                related_person_phone = #{relatedPersonPhone},
            </if>
            <if test="relatedPersonAddress != null and relatedPersonAddress != ''">
                related_person_address = #{relatedPersonAddress},
            </if>
            <if test="controlCompanySupervisor != null and controlCompanySupervisor != ''">
                control_company_supervisor = #{controlCompanySupervisor},
            </if>
            <if test="controlCompanySupervisorPerson != null and controlCompanySupervisorPerson != ''">
                control_company_supervisor_person = #{controlCompanySupervisorPerson},
            </if>
            <if test="riskLevel != null and riskLevel != ''">
                risk_level = #{riskLevel},
            </if>
            <if test="remarks != null and remarks != ''">
                remarks = #{remarks},
            </if>
            <if test="involvingConflictsDisputes != null and involvingConflictsDisputes != ''">
                involving_conflicts_disputes = #{involvingConflictsDisputes},
            </if>
            <if test="region != null and region != ''">
                region = #{region},
            </if>
<!--            <if test="cityId != null and cityId != ''">-->
                city_id = #{cityId},
<!--            </if>-->
<!--            <if test="areaId != null and areaId != ''">-->
                area_id = #{areaId},
<!--            </if>-->
<!--            <if test="streetId != null and streetId != ''">-->
                street_id = #{streetId},
<!--            </if>-->
<!--            <if test="communityId != null and communityId != ''">-->
                community_id = #{communityId},
<!--            </if>-->
            update_date = now(),
            update_user = #{_user.id}
        </set>
        where id = #{id}
    </update>

    <select id="findKeyPersonStatisticsForType" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
        count(distinct card_no) as count,
        sys_type as sysType
        from
        jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND city_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND area_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND street_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
        </where>
        <!--<where>
            <if test="_user.company_grade == '3'.toString()">
                 AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} )and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (select company_id from sys_user where id = create_user) = #{_user.company_id}
            </if>
        </where>-->
        group by
        sys_type
    </select>

    <select id="keyPersonStatisticsForAge" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select distinct card_no as cardNo
        from jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
        </where>
    </select>

    <select id="keyPersonStatisticsForTypeAll" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
    resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select count(distinct card_no) as count
        from
        jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND city_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND area_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND street_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
        </where>
        <!--(select distinct card_no as cardNo from jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} )and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') OR id = #{_user.company_id} ) and del_flag='0'
                ) and del_flag='0'
                )
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (select company_id from sys_user where id = create_user) = #{_user.company_id}
            </if>
        </where>
        ) a-->
    </select>

    <delete id="deleteKeyPersonHighlyConcernedRelByPersonId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        delete from jl_key_person_highly_concerned_rel where key_person_id = #{id}
    </delete>

    <insert id="createJlHomicideCase" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_homicide_case WHERE id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE
                jl_homicide_case
            SET
                source_case = #{source_case},
                case_number = #{case_number},
                case_type = #{case_type},
                case_status = #{case_status},
                incident_time = #{incident_time},
                city_id = #{city_id},
                area_id = #{area_id},
                street_id = #{street_id},
                community_id = #{community_id},
                garden_id = #{garden_id},
                happening_place = #{happening_place},
                reporting_time = #{reporting_time},
                reporter_name = #{reporter_name},
                reporter_tel = #{reporter_tel},
                reporter_cardno = TO_BASE64(AES_ENCRYPT(#{reporter_cardno},'*#id#')),
                is_case = #{is_case},
                is_evil = #{is_evil},
                case_description = #{case_description},
                location_storage = #{location_storage},
                investigation_records = #{investigation_records},
                legal_procedure_record = #{legal_procedure_record},
                closing_situation = #{closing_situation},
                criminal_motivation = #{criminal_motivation},
                tools_crimes = #{tools_crimes},
                involved_number = #{involved_number},
            dead_number = #{dead_number},
            dead_type = #{dead_type},
                area_type = #{area_type},
            reason_incident = #{reason_incident},
                update_by = #{_user.id},
                update_time = NOW()
                WHERE
                    id = #{id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_homicide_case (
                id,
                source_case,
                case_number,
                case_type,
                case_status,
                incident_time,
                city_id,
                area_id,
                street_id,
                community_id,
                garden_id,
                happening_place,
                reporting_time,
                reporter_name,
                reporter_tel,
                reporter_cardno,
                is_case,
                is_evil,
                case_description,
                location_storage,
                investigation_records,
                legal_procedure_record,
                closing_situation,
                criminal_motivation,
                tools_crimes,
                involved_number,
            dead_number,
            dead_type,
                area_type,
            reason_incident,
                create_by,
                create_time)
            VALUES (
                #{id},
                #{source_case},
                #{case_number},
                #{case_type},
                #{case_status},
                #{incident_time},
                #{city_id},
                #{area_id},
                #{street_id},
                #{community_id},
                #{garden_id},
                #{happening_place},
                #{reporting_time},
                #{reporter_name},
                #{reporter_tel},
                TO_BASE64(AES_ENCRYPT(#{reporter_cardno},'*#id#')),
                #{is_case},
                #{is_evil},
                #{case_description},
                #{location_storage},
                #{investigation_records},
                #{legal_procedure_record},
                #{closing_situation},
                #{criminal_motivation},
                #{tools_crimes},
                #{involved_number},
            #{dead_number},
            #{dead_type},
                #{area_type},
            #{reason_incident},
                #{_user.id},
                NOW()
                   )
        </if>
    </insert>
    <delete id="deleteJlHomicideCaseALL" parameterType="map">
        DELETE
        FROM jl_homicide_case
        WHERE id = #{homicide_id}
    </delete>
    <delete id="deleteJlHomicideVictimeFamilyALL" parameterType="map">
        DELETE
        FROM jl_homicide_victime
        WHERE id = #{id}
    </delete>
    <insert id="createJlHomicideVictime" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_homicide_victime WHERE id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_homicide_victime
            SET
                victim_name = #{victim_name},
                victim_tel = #{victim_tel},
                victim_cardno = TO_BASE64(AES_ENCRYPT(#{victim_cardno},'*#id#')),
                death_time = #{death_time},
                death_reason = #{death_reason},
                update_by = #{_user.id},
                update_time = NOW()
            WHERE
                id = #{id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_homicide_victime (
                id,
                homicide_id,
                victim_name,
                victim_tel,
                victim_cardno,
                death_time,
                death_reason,
                create_by,
                create_time
            )
            VALUES (
                       UUID(),
                       #{homicide_id},
                       #{victim_name},
                       #{victim_tel},
                       TO_BASE64(AES_ENCRYPT(#{victim_cardno},'*#id#')),
                       #{death_time},
                       #{death_reason},
                        #{_user.id},
                       NOW()
                   )
        </if>
    </insert>
    <delete id="deleteJlHomicideVictime" parameterType="map">
        DELETE FROM jl_homicide_victime WHERE id = #{id}
    </delete>
    <insert id="createJlHomicideFamily" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_homicide_family WHERE id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_homicide_family
            SET
                family_name = #{family_name},
                family_tel = #{family_tel},
                family_cardno = TO_BASE64(AES_ENCRYPT(#{family_cardno},'*#id#')),
                relationship_victim = #{relationship_victim},
                update_by = #{_user.id},
                update_time = NOW()
            WHERE
                id = #{id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_homicide_family (
                id,
                homicide_victime_id,
                family_name,
                family_tel,
                family_cardno,
                relationship_victim,
                create_by,
                create_time
            )
            VALUES (
                       UUID(),
                       #{homicide_victime_id},
                       #{family_name},
                       #{family_tel},
                       TO_BASE64(AES_ENCRYPT(#{family_cardno},'*#id#')),
                       #{relationship_victim},
                       #{_user.id},
                       NOW()
                   )
        </if>
    </insert>
    <delete id="deleteJlHomicideFamily" parameterType="map">
        DELETE FROM jl_homicide_family WHERE id = #{id}
    </delete>

    <select id="homicideSuspectStatisticsForProvince"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select  * from (
        select count(id)    as count,
        city_id       as cityId,
        (select name from sys_area where sys_area.id = jl_homicide_case.city_id) as cityName,
        (select sort from sys_area where sys_area.id = jl_homicide_case.city_id) as sort
        from jl_homicide_case
        group by city_id
        ) aa where aa.cityId is not null order by sort
    </select>

    <select id="homicideSuspectStatisticsForCommunity"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select  * from (
        select count(id)    as count,
        community_id       as cityId,
        (select name from sys_area where sys_area.id = jl_homicide_case.community_id) as cityName,
        (select sort from sys_area where sys_area.id = jl_homicide_case.community_id) as sort
        from jl_homicide_case
        <where>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by community_id
        ) aa where aa.cityId is not null order by sort
    </select>

    <select id="homicideSuspectStatisticsForStreet"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select  * from (
        select count(id)    as count,
        community_id       as cityId,
        (select name from sys_area where sys_area.id = jl_homicide_case.community_id) as cityName,
        (select sort from sys_area where sys_area.id = jl_homicide_case.community_id) as sort
        from jl_homicide_case
        <where>
            <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 != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by community_id
        ) aa where aa.cityId is not null order by sort
    </select>

    <select id="homicideSuspectStatisticsForArea"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select  * from (
        select count(id)    as count,
        street_id       as cityId,
        (select name from sys_area where sys_area.id = jl_homicide_case.street_id) as cityName,
        (select sort from sys_area where sys_area.id = jl_homicide_case.street_id) as sort
        from jl_homicide_case
        <where>
            <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 != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by street_id
        ) aa where aa.cityId is not null order by sort
    </select>

    <select id="homicideSuspectStatisticsForCity"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select  * from (
        select count(id)    as count,
        area_id       as cityId,
        (select name from sys_area where sys_area.id = jl_homicide_case.area_id) as cityName,
        (select sort from sys_area where sys_area.id = jl_homicide_case.area_id) as sort
        from jl_homicide_case
        <where>
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{_user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by area_id
        ) aa where aa.cityId is not null order by sort
    </select>

    <select id="homicideSuspectStatisticsForAge" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select distinct CAST(AES_DECRYPT(FROM_BASE64(suspect_cardno),'*#id#') AS CHAR) as suspectCardno
        from jl_homicide_suspect
        inner join jl_homicide_case on jl_homicide_case.id = jl_homicide_suspect.homicide_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_suspect.create_by=#{_user.id}
                or jl_homicide_suspect.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
    </select>

    <select id="homicideSuspectStatisticsEduLevel" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select count(suspect_cardno) as suspectCardno, edu_level as eduLevel
        from jl_homicide_suspect inner join jl_homicide_case on jl_homicide_case.id = jl_homicide_suspect.homicide_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_suspect.create_by=#{_user.id}
                or jl_homicide_suspect.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by edu_level
    </select>

    <select id="homicideSuspectStatisticsForCareerSituation" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select count(suspect_cardno) as suspectCardno, career_situation as careerSituation
        from jl_homicide_suspect inner join jl_homicide_case on jl_homicide_case.id = jl_homicide_suspect.homicide_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_suspect.create_by=#{_user.id}
                or jl_homicide_suspect.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by career_situation
    </select>

    <select id="homicideSuspectStatisticsForReasonIncident" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        select count(1) as caseNumber, reason_incident as reasonIncident
        from jl_homicide_case inner join jl_homicide_suspect on jl_homicide_case.id = jl_homicide_suspect.homicide_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{_user.company_id})
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND community_id = #{_user.company_id}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                jl_homicide_suspect.create_by=#{_user.id}
                or jl_homicide_suspect.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        group by reason_incident

    </select>

    <select id="getSuspect" parameterType="map" resultType="Integer">
        SELECT
        COUNT(1)
        FROM
        jl_homicide_suspect
        WHERE
        suspect_cardno = #{suspect_cardno}
        AND homicide_id = #{homicide_id}
    </select>

    <insert id="createJlHomicideSuspect" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_homicide_suspect WHERE id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_homicide_suspect
            SET
                suspect_name = #{suspect_name},
                suspect_cardno = TO_BASE64(AES_ENCRYPT(#{suspect_cardno},'*#id#')),
                is_key_personnel = #{is_key_personnel},
                key_personnel_type = #{key_personnel_type},
                sex = #{sex},
                birthday = #{birthday},
                edu_level = #{edu_level},
                career_situation = #{career_situation},
                update_by = #{_user.id},
                update_time = NOW()
            WHERE
                id = #{id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_homicide_suspect (
                id,
                homicide_id,
                suspect_name,
                suspect_cardno,
                is_key_personnel,
                key_personnel_type,
                sex,
                birthday,
                edu_level,
                career_situation,
                create_by,
                create_time
            )
            VALUES (
                UUID(),
                #{homicide_id},
                #{suspect_name},
                TO_BASE64(AES_ENCRYPT(#{suspect_cardno},'*#id#')),
                #{is_key_personnel},
                #{key_personnel_type},
                #{sex},
                #{birthday},
                #{edu_level},
                #{career_situation},
                #{_user.id},
                NOW()
            )
        </if>
    </insert>
    <delete id="deleteJlHomicideSuspect" parameterType="map">
        DELETE FROM jl_homicide_suspect WHERE id = #{id}
    </delete>
    <insert id="createJlHomicideWitness" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_homicide_witness WHERE id = #{id}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_homicide_witness
            SET
                witness_name = #{witness_name},
                witness_cardno = TO_BASE64(AES_ENCRYPT(#{witness_cardno},'*#id#')),
                witness_tel = #{witness_tel},
                update_by = #{_user.id},
                update_time = NOW()
            WHERE
                id = #{id}
        </if>
        <if test="count == 0">
           INSERT INTO jl_homicide_witness (
               id,
               homicide_id,
               witness_name,
               witness_cardno,
               witness_tel,
               create_by,
               create_time
           )
            VALUES (
                       UUID(),
                       #{homicide_id},
                       #{witness_name},
                       TO_BASE64(AES_ENCRYPT(#{witness_cardno},'*#id#')),
                        #{witness_tel},
                        #{_user.id},
                       NOW()
                   )
        </if>
    </insert>
    <delete id="deleteJlHomicideWitness" parameterType="map">
        DELETE FROM jl_homicide_witness WHERE id = #{id}
    </delete>
    <select id="getJlHomicideCasePageList" parameterType="page" resultType="map">
        SELECT *
        FROM (select jl_homicide_case.id,
        source_case,
        case_number,
        case_type,
        case_status,
        DATE_FORMAT(incident_time, '%Y-%m-%d %T')                        AS incident_time,
        city_id,
        city.name               city,
        area_id,
        area.name                   area,
        street_id,
        street.name                  street,
        community_id,
        community.name             community,
        city.sort as sort1,
        area.sort as sort2,
        street.sort as sort3,
        garden_id,
        happening_place,
        DATE_FORMAT(reporting_time, '%Y-%m-%d %T')                       AS reporting_time,
        reporter_name,
        reporter_tel,
        CAST(AES_DECRYPT(FROM_BASE64(reporter_cardno), '*#id#') AS CHAR) AS reporter_cardno,
        is_case,
        is_evil,
        case_description,
        location_storage,
        investigation_records,
        legal_procedure_record,
        closing_situation,
        criminal_motivation,
        tools_crimes,
        involved_number,
        dead_number,
        dead_type,
        area_type,
        reason_incident,
        jl_homicide_case.create_by,
        DATE_FORMAT(jl_homicide_case.create_time, '%Y-%m-%d %T')                          AS create_time,
        jl_homicide_case.update_by,
        DATE_FORMAT(jl_homicide_case.update_time, '%Y-%m-%d %T')                          AS update_time
        FROM jl_homicide_case
            left join sys_area city on jl_homicide_case.city_id = city.code
            left join sys_area area on jl_homicide_case.area_id = area.code
            left join sys_area street on jl_homicide_case.street_id = street.code
            left join sys_area community on jl_homicide_case.community_id = community.code
        <where>
            <choose>
                <when test="params.community_id != null and params.community_id != '' and params.street_id != null and params.street_id != '' and params.area_id != null and params.area_id != '' and params.city_id != null and params.city_id != ''">
                    AND community_id = #{params.community_id}
                    AND street_id = #{params.street_id}
                    AND area_id = #{params.area_id}
                    AND city_id = #{params.city_id}
                </when>
                <when test="params.street_id != null and params.street_id != '' and params.area_id != null and params.area_id != '' and params.city_id != null and params.city_id != ''">
                    AND street_id = #{params.street_id}
                    AND area_id = #{params.area_id}
                    AND city_id = #{params.city_id}
                </when>
                <when test="params.area_id != null and params.area_id != '' and params.city_id != null and params.city_id != ''">
                    AND area_id = #{params.area_id}
                    AND city_id = #{params.city_id}
                </when>
                <when test="params.city_id != null and params.city_id != ''">
                    AND city_id = #{params.city_id}
                </when>
                <when test="params._user.company_grade == '2'.toString()">
                    AND city_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
                </when>
                <when test="params._user.company_grade == '3'.toString()">
                    AND area_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
                </when>
                <when test="params._user.company_grade == '4'.toString()">
                    AND street_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
                </when>
                <when test="params._user.company_grade == '5'.toString()">
                    AND community_id in (SELECT area_id FROM sys_office WHERE parent_id = #{params._user.company_id})
                </when>
                <when test="params._user.company_grade == '6'.toString()">
                    AND community_id = #{params._user.company_id}
                </when>
                <otherwise>
                    AND city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
                </otherwise>
            </choose>
            <if test="params.case_number != null and params.case_number != ''">
                AND jl_homicide_case.case_number = #{params.case_number}
            </if>
            <if test="params.is_evil != null and params.is_evil != ''">
                AND jl_homicide_case.is_evil = #{params.is_evil}
            </if>
            <if test="params.incident_time_start != null and params.incident_time_start != '' and params.incident_time_end != null and params.incident_time_end != ''">
                AND jl_homicide_case.incident_time BETWEEN #{params.incident_time_start} AND #{params.incident_time_end}
            </if>
            <if test="params.reporting_time_start != null and params.reporting_time_start != '' and params.reporting_time_end != null and params.reporting_time_end != ''">
                AND jl_homicide_case.reporting_time BETWEEN #{params.reporting_time_start} AND #{params.reporting_time_end}
            </if>
            <if test="params.suspect_name != null and params.suspect_name != ''">
                AND jl_homicide_case.id in(select homicide_id from jl_homicide_suspect where suspect_name LIKE CONCAT('%',#{params.suspect_name},'%') )
            </if>
            <if test="params.suspect_cardno != null and params.suspect_cardno != ''">
                AND jl_homicide_case.id in(select homicide_id from jl_homicide_suspect where suspect_cardno = TO_BASE64(AES_ENCRYPT(#{params.suspect_cardno},'*#id#')))
            </if>
            <if test="params._user.company_grade != '2'.toString()">
                and (
                jl_homicide_case.create_by=#{params._user.id}
                or jl_homicide_case.create_by in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{params._user.company_id}, '%') and
                del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
        </where>
        ) A order by sort1, sort2, sort3
    </select>
    <select id="getJlHomicideVictimeList" parameterType="map" resultType="map">
        select id,
               homicide_id,
               victim_name,
               victim_tel,
               CAST(AES_DECRYPT(FROM_BASE64(victim_cardno),'*#id#') AS CHAR) AS victim_cardno,
               DATE_FORMAT(death_time,'%Y-%m-%d %T') AS death_time,
               death_reason,
               create_by,
               DATE_FORMAT(create_time,'%Y-%m-%d %T') AS create_time,
               update_by,
               DATE_FORMAT(update_time,'%Y-%m-%d %T') AS update_time
        from jl_homicide_victime
        where homicide_id = #{homicide_id}
        ORDER BY create_time DESC
    </select>
    <select id="getJlHomicideFamilyList" parameterType="map" resultType="map">
        select id,
               homicide_victime_id,
               family_name,
               family_tel,
               CAST(AES_DECRYPT(FROM_BASE64(family_cardno),'*#id#') AS CHAR) AS family_cardno,
               relationship_victim,
               create_by,
               update_by,
               DATE_FORMAT(update_time,'%Y-%m-%d %T') AS update_time,
               DATE_FORMAT(create_time,'%Y-%m-%d %T') AS create_time
        from jl_homicide_family
        where homicide_victime_id = #{homicide_victime_id}
        ORDER BY create_time DESC
    </select>
    <select id="getJlHomicideSuspectList" parameterType="map" resultType="map">
        select
            id,
            homicide_id,
            suspect_name,
            CAST(AES_DECRYPT(FROM_BASE64(suspect_cardno),'*#id#') AS CHAR) AS suspect_cardno,
            is_key_personnel,
            key_personnel_type,
            sex,
            <!--birthday,-->
            DATE_FORMAT(birthday,'%Y-%m-%d') birthday,
            edu_level,
            career_situation,
            create_by,
            update_by,
            DATE_FORMAT(update_time,'%Y-%m-%d %T') AS update_time,
            DATE_FORMAT(create_time,'%Y-%m-%d %T') AS create_time
        from jl_homicide_suspect
        where 1=1
        <if test="homicide_id!=null and homicide_id!=''">
            and homicide_id = #{homicide_id}
        </if>
        ORDER BY create_time DESC
    </select>
    <select id="getJlHomicideWitnessList" parameterType="map" resultType="map">
        select id,
               homicide_id,
               witness_name,
               CAST(AES_DECRYPT(FROM_BASE64(witness_cardno),'*#id#') AS CHAR) AS witness_cardno,
               witness_tel,
               create_by,
               DATE_FORMAT(create_time,'%Y-%m-%d %T') AS create_time,
               update_by,
               DATE_FORMAT(update_time,'%Y-%m-%d %T') AS update_time
        from jl_homicide_witness
        where homicide_id = #{homicide_id}
        ORDER BY create_time DESC
    </select>

    <select id="selectHomicideCaseDataList" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCase">
        SELECT
            DATE_FORMAT(jhc.incident_time,'%Y-%m-%d') AS incidentTime,
            (SELECT name FROM sys_area WHERE code = jhc.city_id) city,
            (SELECT name FROM sys_area WHERE code = jhc.area_id) area,
            (SELECT name FROM sys_area WHERE code = jhc.street_id) street,
            (SELECT name FROM sys_area WHERE code = jhc.community_id) community,
            jhc.case_description AS caseDescription,
            jhc.involved_number AS involvedNumber,
            jhc.dead_number AS deadNumber,
            jhc.dead_type AS deadType,
            CASE jhc.area_type
            WHEN '1' THEN '城市(区)'
            WHEN '2' THEN '农村'
            ELSE '' END AS areaType,
            jhs.suspect_name AS suspectName,
            CASE jhs.is_key_personnel
            WHEN '1' THEN '是'
            WHEN '2' THEN '否'
            ELSE '' END AS isKeyPersonnel,
            CASE jhs.edu_level
            WHEN '1' THEN '文盲'
            WHEN '2' THEN '小学学历'
            WHEN '3' THEN '初中学历'
            WHEN '4' THEN '高中学历'
            WHEN '5' THEN '专科学历'
            WHEN '6' THEN '本科学历'
            WHEN '7' THEN '硕士学历'
            WHEN '8' THEN '博士学历'
            WHEN '9' THEN '其他'
            ELSE '' END  AS eduLevel,
            CASE jhs.career_situation
            WHEN '1' THEN '党政机关人员'
            WHEN '2' THEN '企事业人员'
            WHEN '3' THEN '退休人员'
            WHEN '4' THEN '无业人员'
            WHEN '5' THEN '农民'
            WHEN '6' THEN '个体户'
            WHEN '7' THEN '务工人员'
            WHEN '8' THEN '其他'
            ELSE '' END  AS careerSituation,
            CONCAT('`',CAST(AES_DECRYPT(FROM_BASE64(jhs.suspect_cardno),'*#id#') AS CHAR)) AS suspectCardno
        FROM
            jl_homicide_case jhc
        LEFT JOIN jl_homicide_suspect jhs ON jhc.id = jhs.homicide_id
        <where>
            <choose>
                <when test="community_id != null and community_id != '' and street_id != null and street_id != '' and area_id != null and area_id != '' and city_id != null and city_id != ''">
                    AND jhc.community_id = #{community_id}
                    AND jhc.street_id = #{street_id}
                    AND jhc.area_id = #{area_id}
                    AND jhc.city_id = #{city_id}
                </when>
                <when test="street_id != null and street_id != '' and area_id != null and area_id != '' and city_id != null and city_id != ''">
                    AND jhc.street_id = #{street_id}
                    AND jhc.area_id = #{area_id}
                    AND jhc.city_id = #{city_id}
                </when>
                <when test="area_id != null and area_id != '' and city_id != null and city_id != ''">
                    AND jhc.area_id = #{area_id}
                    AND jhc.city_id = #{city_id}
                </when>
                <when test="city_id != null and city_id != ''">
                    AND jhc.city_id = #{city_id}
                </when>
                <when test="_user.company_grade == '4'.toString()">
                    AND jhc.area_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                </when>
                <when test="_user.company_grade == '5'.toString()">
                    AND jhc.street_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                </when>
                <when test="_user.company_grade == '6'.toString()">
                    AND jhc.community_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                </when>
                <otherwise>
                    AND jhc.city_id = (SELECT area_id FROM sys_office WHERE id = #{_user.company_id})
                </otherwise>
            </choose>
            <if test="case_number != null and case_number != ''">
                AND case_number = #{case_number}
            </if>
            <if test="is_evil != null and is_evil != ''">
                AND is_evil = #{is_evil}
            </if>
            <if test="city_id != null and city_id != ''">
                AND city_id = #{city_id}
            </if>
            <if test="area_id != null and area_id != ''">
                AND area_id = #{area_id}
            </if>
            <if test="street_id != null and street_id != ''">
                AND street_id = #{street_id}
            </if>
            <if test="community_id != null and community_id != ''">
                AND community_id = #{community_id}
            </if>
            <if test="incident_time_start != null and incident_time_start != '' and incident_time_end != null and incident_time_end != ''">
                AND incident_time BETWEEN #{incident_time_start} AND #{incident_time_end}
            </if>
            <if test="reporting_time_start != null and reporting_time_start != '' and reporting_time_end != null and reporting_time_end != ''">
                AND reporting_time BETWEEN #{reporting_time_start} AND #{reporting_time_end}
            </if>
        </where>
        ORDER BY jhc.create_time DESC
    </select>

    <select id="selectHomicideCaseDataListNew" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlHomicideCaseNew">
        SELECT
            jhc.id,
            DATE_FORMAT(jhc.incident_time,'%Y-%m-%d') AS incidentTime,
            (SELECT name FROM sys_area WHERE code = jhc.city_id) city,
            (SELECT name FROM sys_area WHERE code = jhc.area_id) area,
            (SELECT name FROM sys_area WHERE code = jhc.street_id) street,
            (SELECT name FROM sys_area WHERE code = jhc.community_id) community,
            jhc.case_description AS caseDescription,
            jhc.involved_number AS involvedNumber,
            jhc.dead_number AS deadNumber,
            jhc.dead_type AS deadType,
            CASE jhc.area_type
            WHEN '1' THEN '城市(区)'
            WHEN '2' THEN '农村'
            ELSE '' END AS areaType,
            CASE jhs.is_key_personnel
            WHEN '1' THEN '是'
            WHEN '2' THEN '否'
            ELSE '' END AS isKeyPersonnel,
            CASE jhs.edu_level
            WHEN '1' THEN '文盲'
            WHEN '2' THEN '小学学历'
            WHEN '3' THEN '初中学历'
            WHEN '4' THEN '高中学历'
            WHEN '5' THEN '专科学历'
            WHEN '6' THEN '本科学历'
            WHEN '7' THEN '硕士学历'
            WHEN '8' THEN '博士学历'
            WHEN '9' THEN '其他'
            ELSE '' END  AS eduLevel,
            CASE jhs.career_situation
            WHEN '1' THEN '党政机关人员'
            WHEN '2' THEN '企事业人员'
            WHEN '3' THEN '退休人员'
            WHEN '4' THEN '无业人员'
            WHEN '5' THEN '农民'
            WHEN '6' THEN '个体户'
            WHEN '7' THEN '务工人员'
            WHEN '8' THEN '其他'
            ELSE '' END  AS careerSituation,
            CONCAT('`',CAST(AES_DECRYPT(FROM_BASE64(jhs.suspect_cardno),'*#id#') AS CHAR)) AS suspectCardno,
            jhs.sex,
            YEAR(CURDATE()) - YEAR(jhs.birthday) - (
                DATE_FORMAT(CURDATE(), '%m%d') &lt; DATE_FORMAT(jhs.birthday, '%m%d')
            ) AS birthday,
            CASE jhc.reason_incident
            WHEN '1' THEN '口角琐事'
            WHEN '2' THEN '家庭矛盾'
            WHEN '3' THEN '情感（婚恋）'
            WHEN '4' THEN '邻里矛盾'
            WHEN '5' THEN '土地纠纷'
            WHEN '6' THEN '经济（借贷）纠纷'
            WHEN '7' THEN '精神障碍患者肇事肇祸'
            WHEN '8' THEN '悲观厌世'
            WHEN '9' THEN '抢劫杀人'
            WHEN '10' THEN '强奸杀人'
            WHEN '11' THEN '吸毒致幻杀人'
            WHEN '12' THEN '其他'
            ELSE '' END  AS reason_incident
        FROM
        jl_homicide_case jhc
        LEFT JOIN jl_homicide_suspect jhs ON jhc.id = jhs.homicide_id
        where 1=1 and jhc.city_id is not null
        <if test="case_number != null and case_number != ''">
            AND jhc.case_number = #{case_number}
        </if>
        <if test="is_evil != null and is_evil != ''">
            AND jhc.is_evil = #{is_evil}
        </if>
        <if test="city_id != null and city_id != ''">
            AND jhc.city_id = #{city_id}
        </if>
        <if test="area_id != null and area_id != ''">
            AND jhc.area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND jhc.street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND jhc.community_id = #{community_id}
        </if>
        <if test="incident_time_start != null and incident_time_start != '' and incident_time_end != null and incident_time_end != ''">
            AND jhc.incident_time BETWEEN #{incident_time_start} AND #{incident_time_end}
        </if>
        <if test="reporting_time_start != null and reporting_time_start != '' and reporting_time_end != null and reporting_time_end != ''">
            AND jhc.reporting_time BETWEEN #{reporting_time_start} AND #{reporting_time_end}
        </if>
        <if test="suspect_name != null and suspect_name != ''">
            AND jhs.suspect_name = #{suspect_name}
        </if>
        <if test="suspect_cardno != null and suspect_cardno != ''">
            AND jhs.suspect_cardno = TO_BASE64(AES_ENCRYPT(#{suspect_cardno},'*#id#'))
        </if>
        <if test="_user.company_grade != '2'.toString()">
            and (
            jhc.create_by=#{_user.id}
            or jhc.create_by in (
            select id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
            del_flag='0'
            ) and del_flag='0'
            )
            )
        </if>
        ORDER BY jhc.create_time DESC
    </select>

    <select id="selectHomicideCaseStatisticsList" parameterType="map" resultType="map" expose="true">
        SELECT
            <if test="city_id == null or city_id == ''">
                so.name,
            </if>
            <if test="city_id != null and city_id != ''">
                so1.name,
            </if>
            COUNT(jhc.id) homicideNumber,
            SUM(jhc.involved_number) involvedNumber,
            COUNT(jhs.id) suspectNumber,
            IFNULL(SUM(IF(is_key_personnel = 1, 1, 0)),0) personnelNumber
        FROM
            jl_homicide_case jhc
        LEFT JOIN jl_homicide_suspect jhs ON jhc.id = jhs.homicide_id
        <if test="city_id == null or city_id == ''">
            LEFT JOIN sys_office so ON jhc.city_id = so.id
        </if>
        <if test="city_id != null and city_id != ''">
            LEFT JOIN sys_office so1 ON jhc.area_id = so1.id
        </if>
        <where>
            <if test="city_id != null and city_id != ''">
                AND jhc.city_id = #{city_id}
            </if>
            <if test="incident_time_start != null and incident_time_start != '' and incident_time_end != null and incident_time_end != ''">
                AND incident_time BETWEEN #{incident_time_start} AND #{incident_time_end}
            </if>
        </where>
    </select>

    <select id="getLoginUserAreaInfo" parameterType="String" resultType="map">
        SELECT
        parent_id as parentId,
        parent_ids as parentIds,
        name,
        code,
        type
        FROM
        sys_area
        WHERE del_flag = '0'
        and
            id = #{id}
    </select>

    <!--查询重点人员 重复类型统计 -->
    <select id="getRepeatTypeStatistics" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.RepeatTypeStatisticsVo">
        select * from (
        SELECT
        update_date,
        GROUP_CONCAT(DISTINCT NAME ) as name,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) as cardNo,
        count( DISTINCT sys_type ) AS count,
        GROUP_CONCAT( DISTINCT
        case sys_type
        when 1 then '“三失一偏”人员'
        when 2 then '刑满释放人员'
        when 3 then '严重精神障碍患者'
        when 4 then '涉重点信访事项人员'
        when 5 then '涉重点涉法涉诉类矛盾人员' end) as sysTypeName,
        GROUP_CONCAT(sys_type) as sysType
        FROM
        jl_key_person
        <where>
            <if test="params._user.company_grade == '3'.toString()">
                AND city_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                AND area_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                AND street_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                AND community_id = #{params._user.company_id}
            </if>
        </where>
        GROUP BY
        card_no
        HAVING
        count >1
        )  as a
        <where>
            <if test="params.name != null and params.name != ''">
                AND name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.cardNo != null and params.cardNo != ''">
                AND cardNo = #{params.cardNo}
            </if>
            <if test="params.sysType != null and params.sysType.size()>0 ">
                AND
                <foreach collection="params.sysType" item="item" open="(" separator="AND" close=")" index="index">
                    sysType like concat('%',#{params.sysType[${index}]},'%')
                </foreach>
            </if>
        </where>
        <if test="params.orderType == 1">
            ORDER BY count DESC
        </if>
        <if test="params.orderType == 2">
            ORDER BY count ASC
        </if>
        <if test="params.orderType == 0">
            order by update_date desc
        </if>
    </select>

    <select id="getRepeatTypeStatisticsDetail" parameterType="map" resultType="map">
       select
        id,
        name,
        phone,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) as cardNo,
        registered_residence as registeredResidence,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as  responsiblePersonKeyPersonnel,
        responsible_person as responsiblePerson,
        responsible_company as responsibleCompany,
        sex,
        address,
        criminal_tendency as criminalTendency,
        criminal_tendency_situation as criminalTendencySituation,
        folk,
        related_person_name as relatedPersonName,
        related_person_relation as relatedPersonRelation,
        related_person_phone as relatedPersonPhone,
        related_person_address as relatedPersonAddress,
        control_company_supervisor as controlCompanySupervisor,
        control_company_supervisor_person as controlCompanySupervisorPerson,
        risk_level as riskLevel,
        involving_conflicts_disputes as involvingConflictsDisputes,
        remarks,
        hazard_assessment_level as hazardAssessmentLevel,
        letters_visits_type as lettersVisitsType,
        (case sys_type
        when 1 then '“三失一偏”人员'
        when 2 then '刑满释放人员'
        when 3 then '严重精神障碍患者'
        when 4 then '涉重点信访事项人员'
        when 5 then '涉重点涉法涉诉类矛盾人员' end) as sysType,
        sys_type as sysTypeKey,
        update_date as updateDate,
        city_id as cityId,
        area_id as areaId,
        street_id as streetId,
        community_id as communityId,
        (select name from sys_area where id = city_id)as cityName,
        (select name from sys_area where id = area_id) as areaName,
        (select name from sys_area where id = street_id) as streetName,
        (select name from sys_area where id = community_id) as communityName
        from jl_key_person
        where card_no = TO_BASE64(AES_ENCRYPT(#{cardNo},'*#id#'))
        <if test="_user.company_grade == '3'.toString()">
            AND city_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND area_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND street_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND community_id = #{_user.company_id}
        </if>
        order by update_date desc
    </select>

<!--    2025-10-27开始修改的内容 =====================================================================  -->

    <select id="doStatusAllCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        SELECT
        SUM(CASE WHEN history.is_overdue = '0' THEN 1 ELSE 0 END) AS doneCount,
        SUM(CASE WHEN history.is_overdue = '1' THEN 1 ELSE 0 END) AS todoCount
        FROM
        jl_key_person
        LEFT JOIN jl_key_person_rating_history history
        ON jl_key_person.id = history.key_person_id
        AND history.rating_dict_type = #{sysType}
        AND history.is_history = '1'
        AND history.do_status = '0'
    </select>

    <select id="getUserLoginInfo" parameterType="java.lang.String" resultType="map">
        SELECT
            su.id,
            su.company_id,
            sc. CODE AS company_code,
            sc.name AS company_name,
            sc.type company_type,
            sc.grade company_grade,
            sc.sys_area company_sys_area,
            sc.phone company_phone,
            sc.parent_id parent_id,
            scparent.code parent_code,
            sc.parent_ids parent_ids,
            sc.area_id area_id,
            su.login_name loginName,
            su.no,
            su.name,
            su.email,
            su.phone,
            su.mobile,
            su.user_type userType,
            '' AS office_childs_code,
            sc. CODE AS bind_seller_company_code,
            su.login_name bind_seller_login_code,
            sc.sys_area bind_seller_sys_area,
            sc.binding_place company_binding_place,
            su.cardno
        FROM
            sys_user su
        inner join sys_office sc on su.company_id = sc.id
        left join sys_office scparent on sc.parent_id = scparent.id
        WHERE
            su.del_flag = '0'
            AND su.login_flag = '1'
            AND sc.del_flag = '0'
            AND sc.type = '1'
            AND su.no = #{no}
    </select>

    <!-- 长春独立统计 -->
    <select id="keyPersonStatisticsForTypeAll_changchun" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select count(distinct card_no) as count
        from
        jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND ((city_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where city_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND ((area_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where area_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND ((street_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where street_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND ((community_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where community_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
        </where>
    </select>

    <select id="findKeyPersonStatisticsForType_changchun" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
        count(distinct card_no) as count,
        sys_type as sysType
        from
        jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND ((city_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where city_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND ((area_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where area_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND ((street_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where street_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND ((community_id = #{scopeAreaId} and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where community_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
        </where>
        group by
        sys_type
    </select>

    <select id="keyPersonStatisticsForRiskLevel_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select risk_level as riskLevel, count(card_no) as count
        from (select distinct card_no, risk_level
        from jl_key_person
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND ((jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} )and del_flag='0'
                ) and del_flag='0'
                ) and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where city_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND ((jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} ) and del_flag='0'
                ) and del_flag='0'
                ) and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where area_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND ((jl_key_person.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} ) and del_flag='0'
                ) and del_flag='0'
                ) and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where street_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (((select company_id from sys_user where id = create_user) = #{scopeAreaId}
                and jl_key_person.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where community_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
            </if>
        </where>
        ) a
        group by risk_level
    </select>

    <select id="findKeyPersonStatistics_changchun"  resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select count(a.card_no) as count, sys_type
        from (select distinct rel.card_no, sys_type
        from jl_key_person_highly_concerned_rel rel
        left join jl_key_person jkp on jkp.id = rel.key_person_id
        <where>
            <if test="_user.company_grade == '3'.toString()">
                AND ((jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} ) and del_flag='0'
                ) and del_flag='0'
                )
                and jkp.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where city_id = #{scopeAreaId} AND key_person_id=jkp.id))
            </if>
            <if test="_user.company_grade == '4'.toString()">
                AND ((jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} ) and del_flag='0'
                ) and del_flag='0'
                )
                and jkp.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where area_id = #{scopeAreaId} AND key_person_id=jkp.id))
            </if>
            <if test="_user.company_grade == '5'.toString()">
                AND ((jkp.create_user  in (
                select id from sys_user where company_id in (
                select id from sys_office where ( parent_ids LIKE CONCAT('%', #{scopeAreaId}, '%') OR id = #{scopeAreaId} ) and del_flag='0'
                ) and del_flag='0'
                )
                and jkp.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where street_id = #{scopeAreaId} AND key_person_id=jkp.id))
            </if>
            <if test="_user.company_grade == '6'.toString()">
                AND (((select company_id from sys_user where id = jkp.create_user) = #{scopeAreaId}
                and jkp.id not in (select key_person_id from jl_key_person_rating_dual_person))
                OR exists(select id from jl_key_person_rating_dual_person where community_id = #{scopeAreaId} AND key_person_id=jkp.id))
            </if>
        </where>
        ) a
        group by a.sys_type
    </select>

    <select id="keyPersonStatisticsForCity_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '3',uc_gov_region_info.region_name,uc_gov_region_info.county)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where (sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{scopeAreaId},parent_ids))
        OR exists(select id from jl_key_person_rating_dual_person where city_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.county order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForArea_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '4',uc_gov_region_info.region_name,uc_gov_region_info.town)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where (sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{scopeAreaId},parent_ids))
        OR exists(select id from jl_key_person_rating_dual_person where area_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.town order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForStreet_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '5',uc_gov_region_info.region_name,uc_gov_region_info.village)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where (sys_user.company_id in (SELECT area_id FROM sys_office where find_in_set(#{scopeAreaId},parent_ids))
        OR exists(select id from jl_key_person_rating_dual_person where street_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.village order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForCommunity_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select if(sys_office.grade = '6',uc_gov_region_info.region_name,uc_gov_region_info.village)  as cityName, count(distinct card_no) as count
        from jl_key_person
        left join sys_user on jl_key_person.create_user = sys_user.id
        left join sys_office on sys_office.id = sys_user.company_id
        left join sys_area on sys_office.area_id = sys_area.id
        left join uc_gov_region_info on uc_gov_region_info.region_code = sys_area.id
        where (sys_user.company_id = #{scopeAreaId}
        OR exists(select id from jl_key_person_rating_dual_person where community_id = #{scopeAreaId} AND key_person_id=jl_key_person.id))
        <if test="sysType != null and sysType != ''">
            and jl_key_person.sys_type = #{sysType}
        </if>
        group by uc_gov_region_info.village order by uc_gov_region_info.region_req
    </select>

    <select id="keyPersonStatisticsForProvince_changchun"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select sys_area.name as cityName, count(distinct card_no) as count
        from jl_key_person
        left  join sys_area on jl_key_person.city_id = sys_area.id
        WHERE
        city_id is not null AND  city_id != ''
        <!--        group by jl_key_person.city_id order by jl_key_person.city_id-->
<!--        <where>-->
            <if test="sysType != null and sysType != ''">
                and sys_type = #{sysType}
            </if>
<!--        </where>-->
        group by jl_key_person.city_id order by jl_key_person.city_id
    </select>

    <select id="findKeyPersonList_changchun" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select a.*
        from (select jl_key_person.id as id,
        jl_key_person.name as name,
        jl_key_person.sys_type                             as                                 sysType,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) cardNo,
        registered_residence                 as                                 registeredResidence,
        phone,
        responsible_company_by_assistance    as                                 responsibleCompanyByAssistance,
        responsible_person_by_assistance     as                                 responsiblePersonByAssistance,
        responsible_company_by_key_personnel as                                 responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel     as                                 responsiblePersonKeyPersonnel,
        responsible_person                   as                                 responsiblePerson,
        responsible_company                  as                                 responsibleCompany,
        sex,
        address,
        criminal_tendency                    as                                 criminalTendency,
        criminal_tendency_situation          as                                 criminalTendencySituation,
        letters_visits_type                                                     lettersVisitsType,
        folk,
        involving_conflicts_disputes         as                                 involvingConflictsDisputes,
        jl_key_person.region                              as                                  region,
        jl_key_person.city_id                              as                                 cityId,
        jl_key_person.area_id                              as                                 areaId,
        jl_key_person.street_id                            as                                 streetId,
        jl_key_person.community_id                         as                                 communityId,
        community.name communityName,
        jl_key_person.remarks as remarks,
        hazard_assessment_level              as                                 hazardAssessmentLevel,
        city.name      cityName,
        area.name      areaName,
        street.name    streetName,
        city.sort as sort1,
        area.sort as sort2,
        street.sort as sort3,
        related_person_name                  as                                 relatedPersonName,
        related_person_relation                                                 relatedPersonRelation,
        related_person_phone                 as                                 relatedPersonPhone,
        related_person_address               as                                 relatedPersonAddress,
        control_company_supervisor           as                                 controlCompanySupervisor,
        control_company_supervisor_person    as                                 controlCompanySupervisorPerson,
        risk_level                           as                                 riskLevel,
        jl_key_person.create_date                          as                                 createDate,
        jl_key_person.update_date                          as                                 updateDate,
        jl_key_person.create_user                          as                                 createUser,
        jl_key_person.update_user                          as                                 updateUser,
        summary.rating_type as ratingType,
        rating_dict.label as ratingTypeName,
        summary.rating as rating,
        DATE_FORMAT(history.plan_start_data,'%Y-%m-%d') planStartData,
        DATE_FORMAT(history.plan_end_data,'%Y-%m-%d') planEndData,
        DATE_FORMAT(history.overdue_date,'%Y-%m-%d') overdueDate,
        DATE_FORMAT(summary.rating_time,'%Y-%m-%d %T') ratingTime,
        case
        when history.id is null then 1
        when curdate() &gt;= history.plan_start_data
        and curdate() &lt;= history.plan_end_data
        then 1
        else 0
        end as canClick,
        history.id as historyId,
        summary.id as summaryId,
        case when history.do_status = '1' then 1 when history.do_status = '2' then 2 else '0' end as doStatus,
        CASE
        WHEN DATEDIFF(history.plan_end_data, CURDATE()) &lt; 0 THEN 0
        ELSE DATEDIFF(history.plan_end_data, CURDATE())
        END AS remainDays,
        history.is_overdue as isOverdue,
        <choose>
            <when test="params._scope.scope_grade == '3'.toString()">
                case when jl_key_person_rating_dual_person.id is not null and jl_key_person.city_id = #{params._scope.scope_area_id}
                and jl_key_person.city_id != jl_key_person_rating_dual_person.city_id then '1' else '0' end as dualPersonBelong,
            </when>
            <when test="params._scope.scope_grade == '4'.toString()">
                case when jl_key_person_rating_dual_person.id is not null and jl_key_person.area_id = #{params._scope.scope_area_id}
                and jl_key_person.area_id != jl_key_person_rating_dual_person.area_id then '1' else '0' end as dualPersonBelong,
            </when>
            <when test="params._scope.scope_grade == '5'.toString()">
                case when jl_key_person_rating_dual_person.id is not null and jl_key_person.street_id = #{params._scope.scope_area_id}
                and jl_key_person.street_id != jl_key_person_rating_dual_person.street_id then '1' else '0' end as dualPersonBelong,
            </when>
            <when test="params._scope.scope_grade == '6'.toString()">
                case when jl_key_person_rating_dual_person.id is not null and jl_key_person.community_id = #{params._scope.scope_area_id}
                and jl_key_person.community_id != jl_key_person_rating_dual_person.community_id then '1' else '0' end as dualPersonBelong,
            </when>
            <otherwise>
                '0' as dualPersonBelong,
            </otherwise>
        </choose>
        <choose>
            <when test="params._scope.scope_grade == '3'.toString()">
                case when jl_key_person_rating_dual_person.city_id = #{params._scope.scope_area_id}
                and jl_key_person.city_id != jl_key_person_rating_dual_person.city_id
                then '1' else '0' end as dualPerson
            </when>
            <when test="params._scope.scope_grade == '4'.toString()">
                case when jl_key_person_rating_dual_person.area_id = #{params._scope.scope_area_id}
                and jl_key_person.area_id != jl_key_person_rating_dual_person.area_id
                then '1' else '0' end as dualPerson
            </when>
            <when test="params._scope.scope_grade == '5'.toString()">
                case when jl_key_person_rating_dual_person.street_id = #{params._scope.scope_area_id}
                and jl_key_person.street_id != jl_key_person_rating_dual_person.street_id
                then '1' else '0' end as dualPerson
            </when>
            <when test="params._scope.scope_grade == '6'.toString()">
                case when jl_key_person_rating_dual_person.community_id = #{params._scope.scope_area_id}
                and jl_key_person.community_id != jl_key_person_rating_dual_person.community_id
                then '1' else '0' end as dualPerson
            </when>
            <otherwise>
                '0' as dualPerson
            </otherwise>
        </choose>
        from jl_key_person
        left join sys_area city on jl_key_person.city_id = city.code
        left join sys_area area on jl_key_person.area_id = area.code
        left join sys_area street on jl_key_person.street_id = street.code
        left join sys_area community on jl_key_person.community_id = community.code
        left join jl_key_person_rating_history history on jl_key_person.id = history.key_person_id and history.rating_dict_type = #{params.sysType} and history.is_history = '1'
        left join jl_key_person_rating_summary summary on jl_key_person.id = summary.key_person_id and summary.rating_dict_type = #{params.sysType} and summary.is_history = '1'
        left join sys_dict rating_dict on rating_dict.type = 'rating_level' and rating_dict.del_flag = '0' and rating_dict.value = summary.rating_type
        left join jl_key_person_rating_dual_person on jl_key_person.id = jl_key_person_rating_dual_person.key_person_id
        <where> 1=1
            <if test="params.involvingConflictsDisputes != null and params.involvingConflictsDisputes != ''">
                and involving_conflicts_disputes like concat('%',#{params.involvingConflictsDisputes},'%')
            </if>
            <if test="params.ratingLevel != null and params.ratingLevel != ''">
                and summary.rating_type like concat('%',#{params.ratingLevel},'%')
            </if>
            <!-- 处理doStatus=0的情况：查询do_status为null -->
            <if test="params.doStatus != null and params.doStatus == 0">
                and history.do_status is null
            </if>
            <!-- 处理doStatus=1的情况：模糊查询do_status包含1 -->
            <if test="params.doStatus != null and params.doStatus == 1">
                and history.do_status like concat('%',#{params.doStatus},'%')
            </if>
            <if test="params.doStatus != null and params.doStatus == 2">
                and history.do_status like concat('%',#{params.doStatus},'%')
            </if>
            <if test="params.isOverdue != null and params.isOverdue != ''">
                and history.is_overdue like concat('%',#{params.isOverdue},'%')
            </if>
            <if test="params.name != null and params.name != ''">
                and jl_key_person.name like concat('%',#{params.name},'%')
            </if>
            <if test="params.sysType != null and params.sysType != ''">
                and jl_key_person.sys_type = #{params.sysType}
            </if>
            <if test="params.cardNo != null and params.cardNo != ''">
                <!--and card_no like concat('%',#{params.cardNo},'%')-->
                and card_no like concat('%',TO_BASE64(AES_ENCRYPT(#{params.cardNo},'*#id#')),'%')
            </if>
            <if test="params.registeredResidence != null and params.registeredResidence != ''">
                and registered_residence = #{params.registeredResidence}
            </if>
            <if test="params.phone != null and params.phone != ''">
                and phone like concat('%',#{params.phone},'%')
            </if>
            <if test="params.hazardAssessmentLevel != null and params.hazardAssessmentLevel != ''">
                and hazard_assessment_level like concat('%',#{params.hazardAssessmentLevel},'%')
            </if>
            <if test="params.responsibleCompanyByAssistance != null and params.responsibleCompanyByAssistance != ''">
                and responsible_company_by_assistance like concat('%',#{params.responsibleCompanyByAssistance},'%')
            </if>
            <if test="params.responsiblePersonByAssistance != null and params.responsiblePersonByAssistance != ''">
                and responsible_person_by_assistance like concat('%',#{params.responsiblePersonByAssistance},'%')
            </if>
            <if test="params.responsibleCompanyByKeyPersonnel != null and params.responsibleCompanyByKeyPersonnel != ''">
                and responsible_company_by_key_personnel like concat('%',#{params.responsibleCompanyByKeyPersonnel},'%')
            </if>
            <if test="params.responsiblePersonKeyPersonnel != null and params.responsiblePersonKeyPersonnel != ''">
                and responsible_person_key_personnel like concat('%',#{params.responsiblePersonKeyPersonnel},'%')
            </if>
            <if test="params.responsiblePerson != null and params.responsiblePerson != ''">
                and responsible_person like concat('%',#{params.responsiblePerson},'%')
            </if>
            <if test="params.responsibleCompany != null and params.responsibleCompany != ''">
                and responsible_company like concat('%',#{params.responsibleCompany},'%')
            </if>
            <if test="params.sex != null and params.sex != ''">
                and sex = #{params.sex}
            </if>
            <if test="params.address != null and params.address != ''">
                and address = #{params.address}
            </if>
            <if test="params.criminalTendency != null and params.criminalTendency != ''">
                and criminal_tendency = #{params.criminalTendency}
            </if>
            <if test="params.criminalTendencySituation != null and params.criminalTendencySituation != ''">
                and criminal_tendency_situation = #{params.criminalTendencySituation}
            </if>
            <if test="params.lettersVisitsType != null and params.lettersVisitsType != ''">
                and letters_visits_type = #{params.lettersVisitsType}
            </if>
            <if test="params.folk != null and params.folk != ''">
                and folk = #{params.folk}
            </if>
            <if test="params.relatedPersonName != null and params.relatedPersonName != ''">
                and related_person_name = #{params.relatedPersonName}
            </if>
            <if test="params.relatedPersonRelation != null and params.relatedPersonRelation != ''">
                and related_person_relation = #{params.relatedPersonRelation}
            </if>
            <if test="params.relatedPersonPhone != null and params.relatedPersonPhone != ''">
                and related_person_phone = #{params.relatedPersonPhone}
            </if>
            <if test="params.relatedPersonAddress != null and params.relatedPersonAddress != ''">
                and related_person_address = #{params.relatedPersonAddress}
            </if>
            <if test="params.controlCompanySupervisor != null and params.controlCompanySupervisor != ''">
                and control_company_supervisor = #{params.controlCompanySupervisor}
            </if>
            <if test="params.controlCompanySupervisorPerson != null and params.controlCompanySupervisorPerson != ''">
                and control_company_supervisor_person = #{params.controlCompanySupervisorPerson}
            </if>
            <if test="params.riskLevel != null and params.riskLevel != ''">
                and risk_level = #{params.riskLevel}
            </if>
            <if test="params.cityId!=null and params.cityId!=''">
                AND jl_key_person.city_id = #{params.cityId}
            </if>
            <if test="params.areaId!=null and params.areaId!=''">
                AND jl_key_person.area_id = #{params.areaId}
            </if>
            <if test="params.streetId!=null and params.streetId!=''">
                AND jl_key_person.street_id = #{params.streetId}
            </if>
            <if test="params.communityId!=null and params.communityId!=''">
                AND jl_key_person.community_id = #{params.communityId}
            </if>
            <!-- 数据范围 -->
            <choose>
                <when test="params._scope.scope_grade == '3'.toString()">
                    AND jl_key_person.city_id = #{params._scope.scope_area_id}
                </when>
                <when test="params._scope.scope_grade == '4'.toString()">
                    AND jl_key_person.area_id = #{params._scope.scope_area_id}
                </when>
                <when test="params._scope.scope_grade == '5'.toString()">
                    AND jl_key_person.street_id = #{params._scope.scope_area_id}
                </when>
                <when test="params._scope.scope_grade == '6'.toString()">
                    AND jl_key_person.community_id = #{params._scope.scope_area_id}
                </when>
            </choose>
            <if test="params.distCard != null and params.distCard !=''">
                AND card_no IN (SELECT card_no FROM jl_key_person
                <where>
                    sys_type = #{params.sysType}
                    <!-- 数据范围 -->
                    <choose>
                        <when test="params._scope.scope_grade == '3'.toString()">
                            AND (jl_key_person.city_id = #{params._scope.scope_area_id}
                            OR exists(select id from jl_key_person_rating_dual_person where city_id = #{params._scope.scope_area_id} AND key_person_id=jl_key_person.id))
                        </when>
                        <when test="params._scope.scope_grade == '4'.toString()">
                            AND (jl_key_person.area_id = #{params._scope.scope_area_id}
                            OR exists(select id from jl_key_person_rating_dual_person where area_id = #{params._scope.scope_area_id} AND key_person_id=jl_key_person.id))
                        </when>
                        <when test="params._scope.scope_grade == '5'.toString()">
                            AND (jl_key_person.street_id = #{params._scope.scope_area_id}
                            OR exists(select id from jl_key_person_rating_dual_person where street_id = #{params._scope.scope_area_id} AND key_person_id=jl_key_person.id))
                        </when>
                        <when test="params._scope.scope_grade == '6'.toString()">
                            AND (jl_key_person.community_id = #{params._scope.scope_area_id}
                            OR exists(select id from jl_key_person_rating_dual_person where community_id = #{params._scope.scope_area_id} AND key_person_id=jl_key_person.id))
                        </when>
                    </choose>
                </where>
                GROUP BY card_no HAVING COUNT(1) > 1)
            </if>
        </where>
        ) a
        <if test="params.distCard == null or params.distCard ==''">
            order by a.cardNo DESC, a.sort1, a.sort2, a.sort3
        </if>
        <if test="params.distCard != null and params.distCard !=''">
            order by a.sort1, a.sort2, a.sort3, a.cardNo
        </if>
    </select>

    <select id="findKeyPersonListExport_changchun" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson">
        select
        @rownum := @rownum + 1 AS xuhao,
        a.*
        from
        (select jl_key_person.id as id,
        jl_key_person.name as name,
        sys_type as sysType,
        <!--card_no cardNo,-->
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) cardNo,
        registered_residence as registeredResidence,
        phone,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as responsiblePersonKeyPersonnel,
        responsible_person as responsiblePerson,
        responsible_company as responsibleCompany,
        <!--sex,-->
        case sex when '1' then '男' when '2' then '女' end as sex,
        address,
        <!--criminal_tendency as criminalTendency,-->
        case criminal_tendency when '1' then '有' when '2' then '无' end as criminalTendency,
        (select group_concat(highly_concerned_situation) from jl_key_person_highly_concerned_rel where key_person_id=jl_key_person.id) as highly_concerned_situation,
        criminal_tendency_situation as criminalTendencySituation,
        letters_visits_type lettersVisitsType,
        folk,
        involving_conflicts_disputes as involvingConflictsDisputes,
        city_id as cityId,
        area_id as areaId,
        street_id as streetId,
        community_id as communityId,
        community.name communityName,
        jl_key_person.remarks as remarks,
        hazard_assessment_level as hazardAssessmentLevel,
        city.name cityName,
        area.name areaName,
        street.name streetName,
        city.sort as sort1,
        area.sort as sort2,
        street.sort as sort3,
        related_person_name as relatedPersonName,
        related_person_relation relatedPersonRelation,
        related_person_phone as relatedPersonPhone,
        related_person_address as relatedPersonAddress,
        control_company_supervisor as controlCompanySupervisor,
        control_company_supervisor_person as controlCompanySupervisorPerson,
        risk_level as riskLevel,
        jl_key_person.create_date as createDate,
        jl_key_person.update_date as updateDate,
        jl_key_person.create_user as createUser,
        jl_key_person.update_user as updateUser
        from jl_key_person
        left join sys_area city on jl_key_person.city_id = city.code
        left join sys_area area on jl_key_person.area_id = area.code
        left join sys_area street on jl_key_person.street_id = street.code
        left join sys_area community on jl_key_person.community_id = community.code
        <where> 1=1
            <if test="involvingConflictsDisputes != null and involvingConflictsDisputes != ''">
                and involving_conflicts_disputes like concat('%',#{involvingConflictsDisputes},'%')
            </if>
            <if test="name != null and name != ''">
                and jl_key_person.name like concat('%',#{name},'%')
            </if>
            <if test="sysType != null and sysType != ''">
                and sys_type = #{sysType}
            </if>
            <if test="cardNo != null and cardNo != ''">
                and card_no like concat('%',#{cardNo},'%')
            </if>
            <if test="registeredResidence != null and registeredResidence != ''">
                and registered_residence = #{registeredResidence}
            </if>
            <if test="phone != null and phone != ''">
                and phone like concat('%',#{phone},'%')
            </if>
            <if test="hazardAssessmentLevel != null and hazardAssessmentLevel != ''">
                and hazard_assessment_level like concat('%',#{hazardAssessmentLevel},'%')
            </if>
            <if test="responsibleCompanyByAssistance != null and responsibleCompanyByAssistance != ''">
                and responsible_company_by_assistance like concat('%',#{responsibleCompanyByAssistance},'%')
            </if>
            <if test="responsiblePersonByAssistance != null and responsiblePersonByAssistance != ''">
                and responsible_person_by_assistance like concat('%',#{responsiblePersonByAssistance},'%')
            </if>
            <if test="responsibleCompanyByKeyPersonnel != null and responsibleCompanyByKeyPersonnel != ''">
                and responsible_company_by_key_personnel like concat('%',#{responsibleCompanyByKeyPersonnel},'%')
            </if>
            <if test="responsiblePersonKeyPersonnel != null and responsiblePersonKeyPersonnel != ''">
                and responsible_person_key_personnel like concat('%',#{responsiblePersonKeyPersonnel},'%')
            </if>
            <if test="responsiblePerson != null and responsiblePerson != ''">
                and responsible_person like concat('%',#{responsiblePerson},'%')
            </if>
            <if test="responsibleCompany != null and responsibleCompany != ''">
                and responsible_company like concat('%',#{responsibleCompany},'%')
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="criminalTendency != null and criminalTendency != ''">
                and criminal_tendency = #{criminalTendency}
            </if>
            <if test="criminalTendencySituation != null and criminalTendencySituation != ''">
                and criminal_tendency_situation = #{criminalTendencySituation}
            </if>
            <if test="lettersVisitsType != null and lettersVisitsType != ''">
                and letters_visits_type = #{lettersVisitsType}
            </if>
            <if test="folk != null and folk != ''">
                and folk = #{folk}
            </if>
            <if test="relatedPersonName != null and relatedPersonName != ''">
                and related_person_name = #{relatedPersonName}
            </if>
            <if test="relatedPersonRelation != null and relatedPersonRelation != ''">
                and related_person_relation = #{relatedPersonRelation}
            </if>
            <if test="relatedPersonPhone != null and relatedPersonPhone != ''">
                and related_person_phone = #{relatedPersonPhone}
            </if>
            <if test="relatedPersonAddress != null and relatedPersonAddress != ''">
                and related_person_address = #{relatedPersonAddress}
            </if>
            <if test="controlCompanySupervisor != null and controlCompanySupervisor != ''">
                and control_company_supervisor = #{controlCompanySupervisor}
            </if>
            <if test="controlCompanySupervisorPerson != null and controlCompanySupervisorPerson != ''">
                and control_company_supervisor_person = #{controlCompanySupervisorPerson}
            </if>
            <if test="riskLevel != null and riskLevel != ''">
                and risk_level = #{riskLevel}
            </if>
            <if test="_user.company_grade != '2'.toString()">
                and (
                create_user=#{_user.id}
                or create_user in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and del_flag='0'
                ) and del_flag='0'
                )
                )
            </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="streetId!=null and streetId!=''">
                AND street_id = #{streetId}
            </if>
            <if test="communityId!=null and communityId!=''">
                AND community_id = #{communityId}
            </if>
        </where>
        ) a,
        (SELECT @rownum := 0) r
        order by a.sort1, a.sort2, a.sort3
    </select>
</mapper>
