<?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.keyPersonVisitRecord" name="重点人员回访记录">

    <!-- 查询回访记录列表 -->
    <select id="findVisitRecordList" parameterType="page" resultType="map">
        SELECT
            t1.*,
            so1.name AS city_name,
            so2.name AS area_name,
            so3.name AS street_name,
            so4.name AS community_name,
            t1.rating_type as rating_level,
            v.sys_type_office_label,
            v.sys_type_office_value,
            v.id AS key_person_visit_id,
            t1.sys_type AS rating_dict_type,
            v.plan_name AS plan_name,
            v.plan_type AS plan_type,
            v.rating_type AS plan_rating_type,
            CASE
                WHEN r.id is NULL THEN
            '1'
                WHEN
                v.id is NOT NULL AND r.id is not NULL
                AND DATE_FORMAT(NOW(),'%Y-%m-%d') &lt; DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_start_day DAY), '%Y-%m-%d') THEN
            '0'
                WHEN
                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') THEN
            '1'
                WHEN
                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') THEN
            '2'
                WHEN
                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') THEN
            '3'
                ELSE
            '1'
            END AS 'visit_status'

        FROM
        (
            SELECT
                kp.id AS person_id,
                kp.name,
                CAST(AES_DECRYPT(FROM_BASE64(kp.card_no),'*#id#') AS CHAR) AS card_no,
                kp.phone,
                kp.sys_type,
                kp.city_id,
                kp.area_id,
                kp.street_id,
                kp.community_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 = #{params.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 = #{params.office_label}
        LEFT JOIN sys_office so1 ON t1.city_id = so1.id
        LEFT JOIN sys_office so2 ON t1.area_id = so2.id
        LEFT JOIN sys_office so3 ON t1.street_id = so3.id
        LEFT JOIN sys_office so4 ON t1.community_id = so4.id
        <where>
            <if test="params.rating_level != null and params.rating_level != ''">
                AND v.rating_type = #{params.rating_level}
            </if>
            <if test="params.visit_status != null and params.visit_status != ''">
                <choose>
                    <when test='params.visit_status == "0"'>
                        AND v.id is NOT NULL
                        AND r.id is not NULL
                        AND DATE_FORMAT(NOW(),'%Y-%m-%d') &lt; DATE_FORMAT(DATE_ADD(r.visit_time, INTERVAL v.visit_start_day DAY), '%Y-%m-%d')
                    </when>
                    <when test='params.visit_status == "1"'>
                        AND (
                            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')
                            )
                        )
                    </when>
                    <when test='params.visit_status == "2"'>
                        AND 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')
                    </when>
                    <when test='params.visit_status == "3"'>
                        AND 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')
                    </when>
                </choose>
            </if>
        </where>
    </select>

    <sql id="getPsersonList">
        SELECT t1.* FROM
            <choose>
                <when test="params.sys_type != null and (params.sys_type == '2'.toString() or params.sys_type == '3'.toString())">
                    jl_key_person t1
                </when>
                <when test="params.sys_type != null and params.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="params.sys_type != null and (params.sys_type == '2'.toString() or params.sys_type == '3'.toString())">
                                jl_key_person t1,
                            </when>
                            <when test="params.sys_type != null and params.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="params.scope_grade == '3'.toString()">
                                AND t1.city_id = #{params.scope_area_id}
                            </when>
                            <when test="params.scope_grade == '4'.toString()">
                                AND t1.area_id = #{params.scope_area_id}
                            </when>
                            <when test="params.scope_grade == '5'.toString()">
                                AND t1.street_id = #{params.scope_area_id}
                            </when>
                            <when test="params.scope_grade == '6'.toString()    ">
                                AND t1.community_id = #{params.scope_area_id}
                            </when>
                        </choose>
                    </where>
                )
            </where>

        union ALL

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

    </sql>

    <sql id="getPersonWhere">
        AND t1.sys_type = #{params.sys_type}
        <choose>
            <when test="params.scope_grade == '3'.toString()">
                AND t1.city_id = #{params.scope_area_id}
            </when>
            <when test="params.scope_grade == '4'.toString()">
                AND t1.area_id = #{params.scope_area_id}
            </when>
            <when test="params.scope_grade == '5'.toString()">
                AND t1.street_id = #{params.scope_area_id}
            </when>
            <when test="params.scope_grade == '6'.toString()    ">
                AND t1.community_id = #{params.scope_area_id}
            </when>
        </choose>
        <if test="params.name != null and params.name != ''">
            AND t1.name LIKE CONCAT('%', #{params.name}, '%')
        </if>
        <if test="params.card_no != null and params.card_no != ''">
            AND CAST(AES_DECRYPT(FROM_BASE64(t1.card_no),'*#id#') AS CHAR) LIKE CONCAT('%', #{params.card_no}, '%')
        </if>
        <if test="params.city_id != null and params.city_id != ''">
            AND t1.city_id = #{params.city_id}
        </if>
        <if test="params.area_id != null and params.area_id != ''">
            AND t1.area_id = #{params.area_id}
        </if>
        <if test="params.street_id != null and params.street_id != ''">
            AND t1.street_id = #{params.street_id}
        </if>
        <if test="params.community_id != null and params.community_id != ''">
            AND t1.community_id = #{params.community_id}
        </if>
    </sql>

    <!-- 获取人员信息（用于回访） -->
    <select id="getPersonInfoForVisit" parameterType="map" resultType="map">
        SELECT
            kp.id,
            kp.name,
            kp.card_no,
            kp.phone,
            kp.sex,
            kp.folk,
            s.rating_type AS rating_level,
            so1.name AS city_name,
            so2.name AS area_name,
            so3.name AS street_name,
            so4.name AS community_name
        FROM (
            <choose>
                <!-- 当sys_type为'2'或'3'时，使用jl_key_person表 -->
                <when test="sys_type != null and (sys_type == '2'.toString() or sys_type == '3'.toString())">
                    SELECT
                        id,
                        name,
                        CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                        phone,
                        sex,
                        folk,
                        city_id,
                        area_id,
                        street_id,
                        community_id
                    FROM jl_key_person
                    WHERE id = #{id} AND sys_type = #{sys_type}
                </when>
                <!-- 当sys_type为'6'时，使用jl_key_person_minor表 -->
                <when test="sys_type != null and sys_type == '6'.toString()">
                    SELECT
                        id,
                        name,
                        CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                        phone,
                        sex,
                        folk,
                        city_id,
                        area_id,
                        street_id,
                        community_id
                    FROM jl_key_person_minor
                    WHERE id = #{id} AND sys_type = #{sys_type}
                </when>
                <!-- 默认情况：兼容旧逻辑，查询两个表 -->
                <otherwise>
                    SELECT
                        id,
                        name,
                        CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                        phone,
                        sex,
                        folk,
                        city_id,
                        area_id,
                        street_id,
                        community_id
                    FROM jl_key_person
                    WHERE id = #{id} AND sys_type IN ('2', '3')

                    UNION ALL

                    SELECT
                        id,
                        name,
                        CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                        phone,
                        sex,
                        folk,
                        city_id,
                        area_id,
                        street_id,
                        community_id
                    FROM jl_key_person_minor
                    WHERE id = #{id} AND sys_type = '6'
                </otherwise>
            </choose>
        ) kp
        LEFT JOIN jl_key_person_rating_summary s
            ON s.key_person_id = kp.id
            AND s.is_history = '1'
            AND s.rating_dict_type = #{rating_dict_type}
        LEFT JOIN sys_office so1 ON kp.city_id = so1.id
        LEFT JOIN sys_office so2 ON kp.area_id = so2.id
        LEFT JOIN sys_office so3 ON kp.street_id = so3.id
        LEFT JOIN sys_office so4 ON kp.community_id = so4.id
        LIMIT 1
    </select>

    <!-- 更新回访历史记录状态 -->
    <update id="updateVisitHistory" parameterType="map">
        UPDATE jl_key_person_visit_record
        SET is_new = '0',
            update_by = #{_user.id},
            update_time = NOW()
        WHERE key_person_id = #{key_person_id}
            AND is_new = '1'
    </update>

    <!-- 插入回访记录 -->
    <insert id="insertVisitRecord" parameterType="map">
        INSERT INTO jl_key_person_visit_record(
            id,
            key_person_id,
            key_person_visit_id,
            rating_dict_type,
            sys_type_office_label,
            sys_type_office_value,
            plan_name,
            plan_type,
            rating_type,
            is_new,
            visit_record,
            visit_img,
            visit_time,
            visit_user_id,
            visit_user_name,
            visit_user_company_name,
            visit_user_company_code,
            create_by,
            create_time
        )
        VALUES (
            #{id},
            #{key_person_id},
            #{key_person_visit_id},
            #{rating_dict_type},
            #{sys_type_office_label},
            #{sys_type_office_value},
            #{plan_name},
            #{plan_type},
            #{rating_type},
            '1',
            #{visit_record},
            #{visit_img},
            #{visit_time},
            #{visit_user_id},
            #{visit_user_name},
            #{visit_user_company_name},
            #{visit_user_company_code},
            #{_user.id},
            NOW()
        )
    </insert>

    <!-- 查询回访历史（分页） -->
    <select id="selectVisitHistoryPage" parameterType="page" resultType="map">
        SELECT
            r.id,
            r.key_person_id,
            r.rating_type,
            r.visit_time,
            r.visit_user_name AS visit_user,
            r.visit_user_company_name AS visit_user_company,
            r.visit_record,
            r.visit_img AS img,
            r.is_new,
            CASE
                WHEN r.is_new = '1' THEN '1'
                ELSE '2'
            END AS do_status
        FROM jl_key_person_visit_record r
        <where>
            <if test="params.key_person_id != null and params.key_person_id != ''">
                AND r.key_person_id = #{params.key_person_id}
            </if>
<!--            <if test="params.sys_type_office_label != null and params.sys_type_office_label != ''">-->
<!--                AND r.sys_type_office_label = #{params.sys_type_office_label}-->
<!--            </if>-->
            <if test="params.rating_dict_type != null and params.rating_dict_type != ''">
                AND r.rating_dict_type = #{params.rating_dict_type}
            </if>
            <if test="params.labels != null and params.labels != ''">
                AND #{params.labels} like concat('%,', r.sys_type_office_label, ',%')
            </if>
            <if test="params.exclude_id != null and params.exclude_id != ''">
                AND r.id != #{params.exclude_id}
            </if>
        </where>
        ORDER BY r.visit_time DESC, r.create_time DESC
    </select>

    <!-- 查询关联机构信息 -->
    <select id="selectVisitOffice" parameterType="map" resultType="map">
        SELECT
        r.relation_office_value,
        r.relation_office_label
        FROM jl_key_person_visit_office r
        <where>
            AND r.relation_office_label like CONCAT('%', #{label}, '%')
        </where>
    </select>

    <!-- 查询回访记录详情 -->
    <select id="getVisitRecordDetail" parameterType="map" resultType="map">
        SELECT
            r.id,
            r.key_person_id,
            r.rating_type,
            r.visit_time,
            r.visit_user_name AS visit_user,
            r.visit_user_company_name AS visit_user_company,
            r.visit_record,
            r.visit_img AS img,
            r.is_new,
            kp.name,
            kp.card_no,
            kp.phone,
            kp.sex,
            kp.folk,
            so1.name AS city,
            so2.name AS area,
            so3.name AS street,
            so4.name AS community,
            CASE
                WHEN r.is_new = '1' THEN '1'
                ELSE '2'
            END AS do_status,
            r.id AS current_visit_record_id
        FROM jl_key_person_visit_record r
        LEFT JOIN (
            SELECT
                id,
                name,
                CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                phone,
                sex,
                folk,
                city_id,
                area_id,
                street_id,
                community_id
            FROM jl_key_person
            WHERE id = (SELECT key_person_id FROM jl_key_person_visit_record WHERE id = #{id})

            UNION ALL

            SELECT
                id,
                name,
                CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
                phone,
                sex,
                folk,
                city_id,
                area_id,
                street_id,
                community_id
            FROM jl_key_person_minor
            WHERE id = (SELECT key_person_id FROM jl_key_person_visit_record WHERE id = #{id})
        ) kp ON kp.id = r.key_person_id
        LEFT JOIN sys_office so1 ON kp.city_id = so1.id
        LEFT JOIN sys_office so2 ON kp.area_id = so2.id
        LEFT JOIN sys_office so3 ON kp.street_id = so3.id
        LEFT JOIN sys_office so4 ON kp.community_id = so4.id
        WHERE r.id = #{id}
    </select>

</mapper>
