<?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.keyPersonVisit" name="重点人员回访">
   <insert id="saveKeyPersonVisit" parameterType="map">
       INSERT INTO jl_key_person_visit(
           id,
           rating_dict_type,
           sys_type_office_value,
           sys_type_office_label,
           plan_name,
           plan_type,
           rating_type,
           status,
           visit_start_day,
           visit_end_day,
           overdue_warning_day,
           create_by,
           create_time
       ) VALUES (
           UUID(),
           #{rating_dict_type},
           #{sys_type_office_value},
           #{sys_type_office_label},
           #{plan_name},
           #{plan_type},
           #{rating_type},
           #{status},
           #{visit_start_day},
           #{visit_end_day},
           #{overdue_warning_day},
           #{_user.id},
           NOW()
       )
   </insert>

    <update id="updateKeyPersonVisit" parameterType="map">
        UPDATE jl_key_person_visit
        SET
            rating_dict_type = #{rating_dict_type},
            sys_type_office_value = #{sys_type_office_value},
            sys_type_office_label = #{sys_type_office_label},
            plan_name = #{plan_name},
            plan_type = #{plan_type},
            rating_type = #{rating_type},
            status = #{status},
            visit_start_day = #{visit_start_day},
            visit_end_day = #{visit_end_day},
            overdue_warning_day = #{overdue_warning_day},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>


    <delete id="deletePersonVisit" parameterType="map">
        DELETE FROM jl_key_person_visit WHERE id = #{id}
    </delete>

    <select id="findKeyPersonVisitRecordList" parameterType="page" resultType="map">
        SELECT
        jkp.id,
        jkp.name,
        jkp.sys_type,
        jkp.card_no,
        jkp.sex,
        jkp.folk,
        jkp.phone,
        jlpvr.visit_time,
        jlpv.visit_start_day,
        jlpv.visit_end_day
        FROM
        jl_key_person jkp
        LEFT JOIN jl_key_person_visit_record jlpvr ON jkp.id = jlpvr.key_person_id AND jlpvr.is_history = '2'
        LEFT JOIN jl_key_person_visit jlpv ON jlpvr.key_person_visit_id = jlpv.id
        WHERE
        (DATEDIFF(jlpvr.visit_time, CURDATE())> 0 OR jlpvr.id IS NULL)
    </select>

    <select id="getPersonVisitRecord" parameterType="map" resultType="map">
        SELECT
        jkp.id,
        jkp.name,
        jkp.sys_type,
        jkp.card_no,
        jkp.sex,
        jkp.folk,
        jkp.phone,
        jlpvr.visit_time,
        jlpv.visit_start_day,
        jlpv.visit_end_day,
        so1.name city,
        so2.name area,
        so3.name street,
        so4.name community,
        jlpv.plan_name
        FROM
        jl_key_person jkp
        LEFT JOIN jl_key_person_visit_record jlpvr ON jkp.id = jlpvr.key_person_id AND jlpvr.is_history = '2'
        LEFT JOIN jl_key_person_visit jlpv ON jlpvr.key_person_visit_id = jlpv.id
        LEFT JOIN sys_office so1 ON jkp.city_id = so1.id
        LEFT JOIN sys_office so2 ON jkp.area_id = so2.id
        LEFT JOIN sys_office so3 ON jkp.street_id = so3.id
        LEFT JOIN sys_office so4 ON jkp.community_id = so4.id
        WHERE
        jkp.id = #{id}
    </select>
    <select id="getRatingSummary" parameterType="map" resultType="String">
        SELECT
        rating_type
        FROM
        jl_key_person_rating_summary
        WHERE
        key_person_id = #{id}
    </select>
    <select id="selectPersonVisitRecordList" parameterType="map" resultType="map">
        SELECT
        id,
        key_person_id,
        key_person_visit_id,
        visit_time,
        visit_record,
        img,
        is_history
        FROM
        jl_key_person_visit_record
        WHERE
        key_person_id = #{id}
        ORDER BY create_time DESC
    </select>
    <update id="updatePersonVisitHistory" parameterType="map">
        UPDATE
        jl_key_person_visit_record
        SET
        is_history = '1',
        update_by = #{_user.id},
        update_time = NOW()
        WHERE
        key_person_id = #{key_person_id}
    </update>
    <insert id="insertPersonVisitRecord" parameterType="map">
        INSERT INTO  jl_key_person_visit_record(
        id,
        key_person_id,
        key_person_visit_id,
        visit_time,
        visit_record,
        img,
        is_history,
        create_by,
        create_time
        ) VALUES (
        UUID(),
        #{key_person_id},
        #{key_person_visit_id},
        #{visit_time},
        #{visit_record},
        #{img},
        '2',
        #{_user.id},
        NOW()
        )
    </insert>






   <select id="findPersonVisitList" parameterType="page" resultType="map">
       SELECT
           id,
           rating_dict_type,
           sys_type_office_value,
           sys_type_office_label,
           plan_name,
           plan_type,
           rating_type,
           status,
           visit_start_day,
           visit_end_day,
           overdue_warning_day
       FROM
           jl_key_person_visit
       <where>
           <if test="params.rating_dict_type != null and params.rating_dict_type != ''">
               AND rating_dict_type = #{params.rating_dict_type}
           </if>
           <if test="params.plan_name != null and params.plan_name != ''">
               AND plan_name LIKE CONCAT('%', #{params.plan_name}, '%')
           </if>
           <if test="params.rating_type != null and params.rating_type != ''">
               AND rating_type = #{params.rating_type}
           </if>
           <if test="params.sys_type_office_value != null and params.sys_type_office_value != ''">
               AND sys_type_office_value = #{params.sys_type_office_value}
           </if>
           <if test="params.status != null and params.status != ''">
               AND status = #{params.status}
           </if>
       </where>
       ORDER BY create_time DESC
   </select>

   <update id="updatePersonVisitStatus" parameterType="map">
       UPDATE jl_key_person_visit
       SET status = #{status},
           update_by = #{_user.id},
           update_time = NOW()
       WHERE id = #{id}
   </update>

    <!-- 禁止同类同等级多条同时启用：在启用某条前将同分类同风险的其他置为停用 -->
    <update id="disableOtherActives" parameterType="map">
        UPDATE jl_key_person_visit
        SET status = '2', update_by = #{_user.id}, update_time = NOW()
        WHERE rating_dict_type = #{rating_dict_type}
          AND rating_type = #{rating_type}
          AND IFNULL(sys_type_office_value, '') = IFNULL(#{sys_type_office_value}, '')
          AND status = '1'
          <if test="id != null and id != ''">
              AND id &lt;> #{id}
          </if>
    </update>
    
    <!-- 检查唯一性：同一部门、同一类别、同一风险等级是否已存在 -->
    <select id="checkUniqueVisitPlan" parameterType="map" resultType="int">
        SELECT COUNT(1)
        FROM jl_key_person_visit
        WHERE rating_dict_type = #{rating_dict_type}
          AND rating_type = #{rating_type}
          AND IFNULL(sys_type_office_value, '') = IFNULL(#{sys_type_office_value}, '')
          <if test="id != null and id != ''">
              AND id &lt;> #{id}
          </if>
    </select>













</mapper>
