<?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.keyPersonStatistics" name="重点人员统计">

    <select id="selectMentalPatientRatingCount" parameterType="map" resultType="map">
        select '3' as sys_type,
        coalesce(sum(case when jkprs.rating_type = 'A' then 1 else 0 end), 0) as count_a,
        coalesce(sum(case when jkprs.rating_type = 'B' then 1 else 0 end), 0) as count_b,
        coalesce(sum(case when jkprs.rating_type = 'C' then 1 else 0 end), 0) as count_c
        from jl_key_person_rating_summary jkprs
        left join jl_key_person jkp on jkp.sys_type = '3' and jkp.id = jkprs.key_person_id
        <where>
        and jkp.id is not null
        and jkprs.is_history = '1'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND jkp.city_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND jkp.area_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND jkp.street_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND jkp.community_id = #{scope_area_id}
                </when>
            </choose>
        </where>
    </select>

    <select id="selectMinorsRatingCount" parameterType="map" resultType="map">
        select '6' as sys_type,
        coalesce(sum(case when jkprs.rating_type = 'A' then 1 else 0 end), 0) as count_a,
        coalesce(sum(case when jkprs.rating_type = 'B' then 1 else 0 end), 0) as count_b,
        coalesce(sum(case when jkprs.rating_type = 'C' then 1 else 0 end), 0) as count_c
        from jl_key_person_rating_summary jkprs
        left join jl_key_person_minor jkpm on jkpm.sys_type = '6' and jkpm.id = jkprs.key_person_id
        <where>
        and jkpm.id is not null
        and jkprs.is_history = '1'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((jkpm.city_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((jkpm.area_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((jkpm.street_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((jkpm.community_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
            </choose>
        </where>
    </select>

    <select id="selectReleasedRatingCount" parameterType="map" resultType="map">
        select '2' as sys_type,
        coalesce(sum(case when jkprs.rating_type = 'A' then 1 else 0 end), 0) as count_a,
        coalesce(sum(case when jkprs.rating_type = 'B' then 1 else 0 end), 0) as count_b,
        coalesce(sum(case when jkprs.rating_type = 'C' then 1 else 0 end), 0) as count_c
        from jl_key_person_rating_summary jkprs
        left join jl_key_person jkp on jkp.sys_type = '2' and jkp.id = jkprs.key_person_id
        <where>
        and jkp.id is not null
        and jkprs.is_history = '1'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND jkp.city_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND jkp.area_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND jkp.street_id = #{scope_area_id}
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND jkp.community_id = #{scope_area_id}
                </when>
            </choose>
        </where>
    </select>

    <select id="selectPeopleCount" parameterType="map" resultType="map">
        select
        sum(case when sys_type = '3' then total else 0 end) as mental_patient_count,
        sum(case when sys_type = '6' then total else 0 end) as minors_count,
        sum(case when sys_type = '2' then total else 0 end) as released_count
        from (
        select '3' as sys_type, count(1) as total
        from jl_key_person
        <where>
            and sys_type = '3'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((city_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((area_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((street_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((community_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
            </choose>
        </where>
        union all
        select '6' as sys_type, count(1) as total
        from jl_key_person_minor
        <where>
            and sys_type = '6'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((city_id = #{scope_area_id} and jl_key_person_minor.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 = #{scope_area_id} AND key_person_id=jl_key_person_minor.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((area_id = #{scope_area_id} and jl_key_person_minor.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 = #{scope_area_id} AND key_person_id=jl_key_person_minor.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((street_id = #{scope_area_id} and jl_key_person_minor.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 = #{scope_area_id} AND key_person_id=jl_key_person_minor.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((community_id = #{scope_area_id} and jl_key_person_minor.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 = #{scope_area_id} AND key_person_id=jl_key_person_minor.id))
                </when>
            </choose>
        </where>
        union all
        select '2' as sys_type, count(1) as total
        from jl_key_person
        <where>
            and sys_type = '2'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((city_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((area_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((street_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((community_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jl_key_person.id))
                </when>
            </choose>
        </where>
        ) t
    </select>


    <select id="getVisitRecord" parameterType="map" resultType="map">

        SELECT
            (
                SELECT COUNT(*)
                FROM jl_key_person_visit_record
                WHERE FIND_IN_SET(key_person_id, GROUP_CONCAT(DISTINCT t1.person_id))
            ) AS total_done,
            SUM(if(
                    r.id is NULL
                or
                    (
                        v.id is NOT NULL
                        AND r.id is not NULL
                        AND DATE_FORMAT(NOW(),'%Y-%m-%d') >= DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_start_day DAY), '%Y-%m-%d')
                        AND DATE_FORMAT(NOW(),'%Y-%m-%d') &lt;  DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_end_day - v.overdue_warning_day DAY), '%Y-%m-%d')
                    )
            , 1, 0)) AS total_pending,

            SUM(if(
                v.id is NOT NULL
                AND r.id is not NULL
                AND DATE_FORMAT(NOW(),'%Y-%m-%d') >= DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_end_day - v.overdue_warning_day DAY), '%Y-%m-%d')
                AND DATE_FORMAT(NOW(),'%Y-%m-%d') &lt;= DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_end_day DAY), '%Y-%m-%d')
            , 1, 0)) AS total_overdue_pending,

            SUM(if(
                v.id is NOT NULL
                AND r.id is not NULL
                AND DATE_FORMAT(NOW(),'%Y-%m-%d') >  DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_end_day DAY), '%Y-%m-%d')
            , 1, 0)) AS total_overdue
        FROM
        (
            SELECT
                kp.id AS person_id,
                kp.sys_type,
                s.id AS rating_id,
                s.rating_type
            FROM
                (<include refid="getPsersonList"></include>) kp,
                jl_key_person_rating_summary s
            WHERE
                s.key_person_id = kp.id
                AND s.is_history = '1'
        ) t1
        LEFT JOIN jl_key_person_visit v ON v.rating_type = t1.rating_type AND v.status = '1' AND v.sys_type_office_label = #{office_label} AND v.rating_dict_type = t1.sys_type
        LEFT JOIN jl_key_person_visit_record r ON t1.person_id = r.key_person_id AND r.is_new = '1' AND r.sys_type_office_label = #{office_label}
    </select>

    <sql id="getPsersonList">
        SELECT t1.* FROM
        <choose>
            <when test="sys_type != null and (sys_type == '2'.toString() or sys_type == '3'.toString())">
                jl_key_person t1
            </when>
            <when test="sys_type != null and sys_type == '6'.toString()">
                jl_key_person_minor t1
            </when>
        </choose>
        <where>
            <include refid="getPersonWhere"></include>
            AND t1.id not in (
                select
                    t1.id
                from
                    <choose>
                        <when test="sys_type != null and (sys_type == '2'.toString() or sys_type == '3'.toString())">
                            jl_key_person t1,
                        </when>
                        <when test="sys_type != null and sys_type == '6'.toString()">
                            jl_key_person_minor t1,
                        </when>
                    </choose>
                    jl_key_person_rating_dual_person t2
                <where>
                    AND t1.id = t2.key_person_id
                    <choose>
                        <when test="scope_grade == '3'.toString()">
                            AND t1.city_id = #{scope_area_id}
                        </when>
                        <when test="scope_grade == '4'.toString()">
                            AND t1.area_id = #{scope_area_id}
                        </when>
                        <when test="scope_grade == '5'.toString()">
                            AND t1.street_id = #{scope_area_id}
                        </when>
                        <when test="scope_grade == '6'.toString()    ">
                            AND t1.community_id = #{scope_area_id}
                        </when>
                    </choose>
                </where>
            )
        </where>


        union ALL

        SELECT t2.* FROM
        <choose>
            <when test="sys_type != null and (sys_type == '2'.toString() or sys_type == '3'.toString())">
                jl_key_person t2,
            </when>
            <when test="sys_type != null and sys_type == '6'.toString()">
                jl_key_person_minor t2,
            </when>
            <otherwise>
                jl_key_person t2,
            </otherwise>
        </choose>
        jl_key_person_rating_dual_person t1
        where t2.id = t1.key_person_id
            <include refid="getPersonWhere"></include>
    </sql>

    <sql id="getPersonWhere">
        <choose>
            <when test="scope_grade == '3'.toString()">
                AND t1.city_id = #{scope_area_id}
            </when>
            <when test="scope_grade == '4'.toString()">
                AND t1.area_id = #{scope_area_id}
            </when>
            <when test="scope_grade == '5'.toString()">
                AND t1.street_id = #{scope_area_id}
            </when>
            <when test="scope_grade == '6'.toString()    ">
                AND t1.community_id = #{scope_area_id}
            </when>
        </choose>
        AND t1.sys_type = #{sys_type}
    </sql>




    <select id="selectMentalPatientOverdueWarningCount" parameterType="map" resultType="map">
        select
            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '0' then 1 else 0 end), 0) as total_pending,
            coalesce(sum(case when jkpvr.do_status = '1' then 1 else 0 end), 0) as total_done,
            coalesce(sum(case when jkpvr.do_status = '2' then 1 else 0 end), 0) as total_overdue,
            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending
        from jl_key_person_visit_record jkpvr
                 left join jl_key_person jkp on jkp.sys_type = '3' and jkp.id = jkpvr.key_person_id
        <where>
            and jkp.id is not null
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((jkp.city_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((jkp.area_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((jkp.street_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((jkp.community_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
            </choose>
        </where>

    </select>

<!--    <select id="selectMinorsOverdueWarningCount" parameterType="map" resultType="map">-->
<!--        select-->
<!--            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '0' then 1 else 0 end), 0) as total_pending,-->
<!--            coalesce(sum(case when jkpvr.do_status = '1' then 1 else 0 end), 0) as total_done,-->
<!--            coalesce(sum(case when jkpvr.do_status = '2' then 1 else 0 end), 0) as total_overdue,-->
<!--            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending-->
<!--        from jl_key_person_visit_record jkpvr-->
<!--                 left join jl_key_person_minor jkpm on jkpm.sys_type = '6' and jkpm.id = jkpvr.key_person_id-->
<!--        <where>-->
<!--            and jkpm.id is not null-->
<!--            <choose>-->
<!--                <when test="scope_grade == '3'.toString()">-->
<!--                    AND ((jkpm.city_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '4'.toString()">-->
<!--                    AND ((jkpm.area_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '5'.toString()">-->
<!--                    AND ((jkpm.street_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '6'.toString()">-->
<!--                    AND ((jkpm.community_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))-->
<!--                </when>-->
<!--            </choose>-->
<!--        </where>-->
<!--    </select>-->

<!--    <select id="selectReleasedOverdueWarningCount" parameterType="map" resultType="map">-->
<!--        select-->
<!--            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '0' then 1 else 0 end), 0) as total_pending,-->
<!--            coalesce(sum(case when jkpvr.do_status = '1' then 1 else 0 end), 0) as total_done,-->
<!--            coalesce(sum(case when jkpvr.do_status = '2' then 1 else 0 end), 0) as total_overdue,-->
<!--            coalesce(sum(case when jkpvr.do_status = '0' and jkpvr.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending-->
<!--        from jl_key_person_visit_record jkpvr-->
<!--                 left join jl_key_person jkp on jkp.sys_type = '2' and jkp.id = jkpvr.key_person_id-->
<!--        <where>-->
<!--            and jkp.id is not null-->
<!--            <choose>-->
<!--                <when test="scope_grade == '3'">-->
<!--                    AND ((jkp.city_id = #{scope_area_id} 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 = #{_user.company_id} AND key_person_id=jkp.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '4'">-->
<!--                    AND ((jkp.area_id = #{scope_area_id} 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 = #{_user.company_id} AND key_person_id=jkp.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '5'">-->
<!--                    AND ((jkp.street_id = #{scope_area_id} 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 = #{_user.company_id} AND key_person_id=jkp.id))-->
<!--                </when>-->
<!--                <when test="scope_grade == '6'">-->
<!--                    AND ((jkp.community_id = #{scope_area_id} 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 = #{_user.company_id} AND key_person_id=jkp.id))-->
<!--                </when>-->
<!--            </choose>-->
<!--        </where>-->
<!--    </select>-->

    <select id="selectMentalPatientRatingCount1" parameterType="map" resultType="map">
        select
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '0' then 1 else 0 end), 0) as total_pending,
            coalesce(sum(case when jkprh.do_status in ('1','3') then 1 else 0 end), 0) as total_done,
            coalesce(sum(case when jkprh.do_status = '2' then 1 else 0 end), 0) as total_overdue,
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending,
            coalesce(sum(case when jkprh.do_status = '3' then 1 else 0 end), 0) as total_overdue_done
        from jl_key_person_rating_history jkprh
                 left join jl_key_person jkp on jkp.sys_type = '3' and jkp.id = jkprh.key_person_id
        <where>
        and jkp.id is not null
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((jkp.city_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((jkp.area_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((jkp.street_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((jkp.community_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
            </choose>
        </where>
    </select>

    <select id="selectMinorsRatingCount1" parameterType="map" resultType="map">
        select
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '0' then 1 else 0 end), 0) as total_pending,
            coalesce(sum(case when jkprh.do_status in ('1','3') then 1 else 0 end), 0) as total_done,
            coalesce(sum(case when jkprh.do_status = '2' then 1 else 0 end), 0) as total_overdue,
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending,
            coalesce(sum(case when jkprh.do_status = '3' then 1 else 0 end), 0) as total_overdue_done
        from jl_key_person_rating_history jkprh
                 left join jl_key_person_minor jkpm on jkpm.sys_type = '6' and jkpm.id = jkprh.key_person_id
        <where>
        and jkpm.id is not null
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((jkpm.city_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((jkpm.area_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((jkpm.street_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((jkpm.community_id = #{scope_area_id} and jkpm.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 = #{scope_area_id} AND key_person_id=jkpm.id))
                </when>
            </choose>
        </where>
    </select>

    <select id="selectReleasedRatingCount1" parameterType="map" resultType="map">
        select
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '0' then 1 else 0 end), 0) as total_pending,
            coalesce(sum(case when jkprh.do_status in ('1','3') then 1 else 0 end), 0) as total_done,
            coalesce(sum(case when jkprh.do_status = '2' then 1 else 0 end), 0) as total_overdue,
            coalesce(sum(case when jkprh.do_status = '0' and jkprh.is_overdue = '1' then 1 else 0 end), 0) as total_overdue_pending,
            coalesce(sum(case when jkprh.do_status = '3' then 1 else 0 end), 0) as total_overdue_done
        from jl_key_person_rating_history jkprh
                 left join jl_key_person jkp on jkp.sys_type = '2' and jkp.id = jkprh.key_person_id
        <where>
        and jkp.id is not null
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND ((jkp.city_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND ((jkp.area_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND ((jkp.street_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND ((jkp.community_id = #{scope_area_id} 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 = #{scope_area_id} AND key_person_id=jkp.id))
                </when>
            </choose>
        </where>
    </select>

<!--    新统计方法，根据配置动态获取分类 =========================================================================-->

    <select id="selectItemRatingCount" parameterType="map" resultType="map">
        select
        sd.value as mark,
        sd.label as name,
        case when tab.sl is null then 0 else tab.sl end value
        from sys_dict sd left join
        (select jkprs.rating_type, count(*) sl from jl_key_person_rating_summary jkprs
        left join
        <choose>
            <when test="sysType == '6'.toString()">
                jl_key_person_minor jkp
            </when>
            <otherwise>
                jl_key_person jkp
            </otherwise>
        </choose>
        on jkp.sys_type = #{sysType} and jkp.id = jkprs.key_person_id
        left join jl_key_person_rating_dual_person dp on jkp.id = dp.key_person_id
        <where>
            and jkp.id is not null
            and jkprs.is_history = '1'
            <choose>
                <when test="scope_grade == '3'.toString()">
                    AND jkp.city_id = #{scope_area_id} and (dp.city_id is null or dp.city_id=jkp.city_id)
                </when>
                <when test="scope_grade == '4'.toString()">
                    AND jkp.area_id = #{scope_area_id} and (dp.area_id is null or dp.area_id=jkp.area_id)
                </when>
                <when test="scope_grade == '5'.toString()">
                    AND jkp.street_id = #{scope_area_id} and (dp.area_id is null or dp.street_id=jkp.street_id)
                </when>
                <when test="scope_grade == '6'.toString()">
                    AND jkp.community_id = #{scope_area_id} and (dp.community_id is null or dp.community_id=jkp.community_id)
                </when>
            </choose>
        </where>
        group by jkprs.rating_type) tab
        on sd.value = tab.rating_type
        where sd.`type`  = 'rating_level' and sd.del_flag = '0'
        order by sd.sort
    </select>
</mapper>
