<?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.keyPersonVisitRecordScheduledTaskDao" name="重点人员回访计划">
    <!-- 计划：启用中 -->
    <!--<select id="selectActiveVisitPlans" parameterType="map" resultType="map">
        SELECT id,
               plan_name,
               plan_type,
               rating_dict_type,
               rating_type,
               visit_start_day,
               visit_end_day,
               overdue_warning_day
        FROM jl_key_person_visit
        WHERE status = '1'
    </select>-->

    <!-- 人员筛选：重点人员（非未成年人），且有最新评分等级 -->
    <select id="selectPersonsForKeyPersonByPlan" parameterType="map" resultType="map">
        SELECT kp.id AS person_id
        FROM jl_key_person kp
        INNER JOIN jl_key_person_rating_summary s
                ON s.key_person_id = kp.id
        WHERE kp.sys_type = #{rating_dict_type}
          AND s.rating_type = #{rating_type}
          AND s.is_history = '1'
        GROUP BY kp.id
    </select>

    <!-- 人员筛选：重点未成年人，且有最新评分等级 -->
    <select id="selectPersonsForMinorByPlan" parameterType="map" resultType="map">
        SELECT km.id AS person_id
        FROM jl_key_person_minor km
        INNER JOIN jl_key_person_rating_summary s
                ON s.key_person_id = km.id
        WHERE s.rating_type = #{rating_type}
            AND s.is_history = '1'
        GROUP BY km.id
    </select>

    <!-- 限制：一个人仅允许一条待办 -->
<!--    <select id="selectPendingCountByPerson" parameterType="map" resultType="int">-->
<!--        SELECT COUNT(1)-->
<!--        FROM jl_key_person_visit_record-->
<!--        WHERE key_person_id = #{person_id}-->
<!--          AND do_status = '0'-->
<!--    </select>-->

    <!-- 最新一条回访记录（含计划起止、办理状态） -->
<!--    <select id="selectLatestRecordByPerson" parameterType="map" resultType="map">-->
<!--        SELECT id,-->
<!--               key_person_id,-->
<!--               key_person_visit_id,-->
<!--               do_status,-->
<!--               plan_start_data,-->
<!--               plan_end_data AS plan_end_date,-->
<!--               DATE(create_time) AS create_date-->
<!--        FROM jl_key_person_visit_record-->
<!--        WHERE key_person_id = #{person_id}-->
<!--        ORDER BY create_time DESC-->
<!--        LIMIT 1-->
<!--    </select>-->

    <!-- 若超出计划结束日期且仍为待办，则标记为超期未办（不可处理本次回访） -->
<!--    <update id="markOverdueIfExpired" parameterType="map">-->
<!--        UPDATE jl_key_person_visit_record-->
<!--        SET do_status = '2',is_history = '0'-->
<!--        WHERE id = #{id}-->
<!--          AND do_status = '0'-->
<!--          AND DATE(NOW()) &gt; plan_end_data-->
<!--    </update>-->

    <!-- 插入新的待办记录 -->
<!--    <insert id="insertVisitRecord" parameterType="map">-->
<!--        INSERT INTO jl_key_person_visit_record (-->
<!--            id,-->
<!--            key_person_id,-->
<!--            key_person_visit_id,-->
<!--            rating_dict_type,-->
<!--            plan_name,-->
<!--            plan_type,-->
<!--            rating_type,-->
<!--            plan_start_data,-->
<!--            plan_end_data,-->
<!--            overdue_date,-->
<!--            do_status,-->
<!--            is_overdue,-->
<!--            is_history,-->
<!--            create_time-->
<!--        ) VALUES (-->
<!--            UUID(),-->
<!--            #{person_id},-->
<!--            #{key_person_visit_id},-->
<!--            #{rating_dict_type},-->
<!--            #{plan_name},-->
<!--            #{plan_type},-->
<!--            #{rating_type},-->
<!--            #{plan_start_data},-->
<!--            #{plan_end_data},-->
<!--            #{overdue_date},-->
<!--            '0',-->
<!--            '0',-->
<!--            '1',-->
<!--            NOW()-->
<!--        )-->
<!--    </insert>-->

    <!-- 超过临期日期的“待办”置为 is_overdue=1（仅待办） -->
<!--    <update id="markOverdueByOverdueDate" parameterType="map">-->
<!--        UPDATE jl_key_person_visit_record-->
<!--        SET is_overdue = '1'-->
<!--        WHERE do_status = '0'-->
<!--          AND overdue_date IS NOT NULL-->
<!--          AND now() > overdue_date-->
<!--          AND (is_overdue IS NULL OR is_overdue = '0')-->
<!--    </update>-->
</mapper>
