<?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.keyPersonRatingDual" name="双属地管理">

    <sql id="personUnion">
        SELECT
            id,
            sys_type,
            name,
            CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
            city_id,
            area_id,
            street_id,
            community_id
        FROM jl_key_person
        UNION ALL
        SELECT
            id,
            sys_type,
            name,
            CAST(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') AS CHAR) AS card_no,
            city_id,
            area_id,
            street_id,
            community_id
        FROM jl_key_person_minor
    </sql>

    <!-- 推送tab列表（push_type=0） -->
    <select id="selectPushList" parameterType="page" resultType="map">
        SELECT
            dualRecord.id,
            dualRecord.key_person_id,
            dualRecord.sys_type,
            dualRecord.push_status,
            dualRecord.push_reason,
            dualRecord.push_type,
            dualRecord.push_time,
            dualRecord.push_user,
            dualRecord.recvie_region,
            dualRecord.recvie_city_id,
            dualRecord.recvie_area_id,
            dualRecord.recvie_street_id,
            dualRecord.recvie_community_id,
            person.name,
            person.card_no,
            recCity.name AS recvie_city_name,
            recArea.name AS recvie_area_name,
            recStreet.name AS recvie_street_name,
            recCommunity.name AS recvie_community_name,
            pushUser.name AS push_user_name,
            auditUser.name AS audit_user_name,
            returnUser.name AS return_user_name
        FROM jl_key_person_rating_dual dualRecord
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dualRecord.key_person_id
        LEFT JOIN sys_office recCity ON recCity.id = dualRecord.recvie_city_id
        LEFT JOIN sys_office recArea ON recArea.id = dualRecord.recvie_area_id
        LEFT JOIN sys_office recStreet ON recStreet.id = dualRecord.recvie_street_id
        LEFT JOIN sys_office recCommunity ON recCommunity.id = dualRecord.recvie_community_id
        LEFT JOIN sys_user pushUser ON pushUser.id = dualRecord.push_user
        LEFT JOIN sys_user auditUser ON auditUser.id = dualRecord.audit_user
        LEFT JOIN sys_user returnUser ON returnUser.id = dualRecord.return_user
        <where>
            AND dualRecord.sys_type = #{params.sys_type}
            AND dualRecord.push_type = '0'
            AND dualRecord.push_area_id = #{params.operator_area_id}
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dualRecord.recvie_city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dualRecord.recvie_area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dualRecord.recvie_street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dualRecord.recvie_community_id = #{params.community_id}
            </if>
            <if test="params.push_status != null and params.push_status != ''">
                AND dualRecord.push_status = #{params.push_status}
            </if>
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND person.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND person.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND person.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND person.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dualRecord.create_time DESC
    </select>

    <!-- 转发tab列表（push_type=1） -->
    <select id="selectForwardList" parameterType="page" resultType="map">
        SELECT
            dualRecord.id,
            dualRecord.key_person_id,
            dualRecord.sys_type,
            dualRecord.push_status,
            dualRecord.push_reason,
            dualRecord.push_type,
            dualRecord.push_time,
            dualRecord.push_user,
            dualRecord.recvie_region,
            dualRecord.recvie_city_id,
            dualRecord.recvie_area_id,
            dualRecord.recvie_street_id,
            dualRecord.recvie_community_id,
            dualRecord.audit_time,
            person.name,
            person.card_no,
            recCity.name AS recvie_city_name,
            recArea.name AS recvie_area_name,
            recStreet.name AS recvie_street_name,
            recCommunity.name AS recvie_community_name,
            pushUser.name AS push_user_name,
            auditUser.name AS audit_user_name
        FROM jl_key_person_rating_dual dualRecord
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dualRecord.key_person_id
        LEFT JOIN sys_office recCity ON recCity.id = dualRecord.recvie_city_id
        LEFT JOIN sys_office recArea ON recArea.id = dualRecord.recvie_area_id
        LEFT JOIN sys_office recStreet ON recStreet.id = dualRecord.recvie_street_id
        LEFT JOIN sys_office recCommunity ON recCommunity.id = dualRecord.recvie_community_id
        LEFT JOIN sys_user pushUser ON pushUser.id = dualRecord.push_user
        LEFT JOIN sys_user auditUser ON auditUser.id = dualRecord.audit_user
        <where>
            AND dualRecord.sys_type = #{params.sys_type}
            AND dualRecord.push_type = '1'
            AND dualRecord.push_area_id = #{params.operator_area_id}
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dualRecord.recvie_city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dualRecord.recvie_area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dualRecord.recvie_street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dualRecord.recvie_community_id = #{params.community_id}
            </if>
            <if test="params.push_status != null and params.push_status != ''">
                AND dualRecord.push_status = #{params.push_status}
            </if>
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND person.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND person.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND person.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND person.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dualRecord.create_time DESC
    </select>

    <!-- 待接收tab -->
    <select id="selectPendingList" parameterType="page" resultType="map">
        SELECT
            dualRecord.id,
            dualRecord.key_person_id,
            dualRecord.sys_type,
            dualRecord.push_status,
            dualRecord.push_reason,
            dualRecord.reject_reason,
            dualRecord.push_type,
            dualRecord.push_time,
            dualRecord.audit_time,
            dualRecord.belong_region,
            dualRecord.belong_city_id,
            dualRecord.belong_area_id,
            dualRecord.belong_street_id,
            dualRecord.belong_community_id,
            dualRecord.recvie_region,
            dualRecord.recvie_city_id,
            dualRecord.recvie_area_id,
            dualRecord.recvie_street_id,
            dualRecord.recvie_community_id,
            belongCity.name AS belong_city_name,
            belongArea.name AS belong_area_name,
            belongStreet.name AS belong_street_name,
            belongCommunity.name AS belong_community_name,
            pushCity.name AS push_city_name,
            pushArea.name AS push_area_name,
            pushStreet.name AS push_street_name,
            pushCommunity.name AS push_community_name,
            person.name,
            person.card_no,
            pushUser.name AS push_user_name,
            auditUser.name AS audit_user_name
        FROM jl_key_person_rating_dual dualRecord
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dualRecord.key_person_id
        LEFT JOIN sys_office pushCity ON pushCity.id = dualRecord.push_city_id
        LEFT JOIN sys_office pushArea ON pushArea.id = dualRecord.push_area_id
        LEFT JOIN sys_office pushStreet ON pushStreet.id = dualRecord.push_street_id
        LEFT JOIN sys_office pushCommunity ON pushCommunity.id = dualRecord.push_community_id
        LEFT JOIN sys_office belongCity ON belongCity.id = dualRecord.belong_city_id
        LEFT JOIN sys_office belongArea ON belongArea.id = dualRecord.belong_area_id
        LEFT JOIN sys_office belongStreet ON belongStreet.id = dualRecord.belong_street_id
        LEFT JOIN sys_office belongCommunity ON belongCommunity.id = dualRecord.belong_community_id
        LEFT JOIN sys_user pushUser ON pushUser.id = dualRecord.push_user
        LEFT JOIN sys_user auditUser ON auditUser.id = dualRecord.audit_user
        <where>
            AND dualRecord.sys_type = #{params.sys_type}
            AND dualRecord.recvie_area_id = #{params.operator_area_id}
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dualRecord.push_city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dualRecord.push_area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dualRecord.push_street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dualRecord.push_community_id = #{params.community_id}
            </if>
            <if test="params.push_status != null and params.push_status != ''">
                AND dualRecord.push_status = #{params.push_status}
            </if>
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND person.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND person.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND person.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND person.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dualRecord.create_time DESC
    </select>

    <!-- 接收中人员tab（D2当前归属为我） -->
    <select id="selectReceivingList" parameterType="page" resultType="map">
        SELECT
            dp.id AS dual_person_id,
            dp.key_person_id,
            dp.sys_type,
            dp.turn_status,
            dp.dual_id,
            dp.region,
            dp.city_id,
            dp.area_id,
            dp.street_id,
            dp.community_id,
            person.name,
            person.card_no,
            city.name AS city_name,
            area.name AS area_name,
            street.name AS street_name,
            community.name AS community_name,
            dualRecord.belong_city_id,
            dualRecord.belong_area_id,
            dualRecord.belong_street_id,
            dualRecord.belong_community_id,
            belongCity.name AS belong_city_name,
            belongArea.name AS belong_area_name,
            belongStreet.name AS belong_street_name,
            belongCommunity.name AS belong_community_name,
            dualRecord.push_reason,
            dualRecord.push_status,
            dualRecord.push_type
        FROM jl_key_person_rating_dual_person dp
        LEFT JOIN jl_key_person_rating_dual dualRecord ON dualRecord.id = dp.dual_id
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dp.key_person_id
        LEFT JOIN sys_office city ON city.id = dp.city_id
        LEFT JOIN sys_office area ON area.id = dp.area_id
        LEFT JOIN sys_office street ON street.id = dp.street_id
        LEFT JOIN sys_office community ON community.id = dp.community_id
        LEFT JOIN sys_office belongCity ON belongCity.id = dualRecord.belong_city_id
        LEFT JOIN sys_office belongArea ON belongArea.id = dualRecord.belong_area_id
        LEFT JOIN sys_office belongStreet ON belongStreet.id = dualRecord.belong_street_id
        LEFT JOIN sys_office belongCommunity ON belongCommunity.id = dualRecord.belong_community_id
        <where>
            AND dp.area_id = #{params.operator_area_id}
            AND dp.sys_type = #{params.sys_type}
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dp.city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dp.area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dp.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dp.community_id = #{params.community_id}
            </if>
            <if test="params.turn_status != null and params.turn_status != ''">
                AND dp.turn_status = #{params.turn_status}
            </if>
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND dp.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND dp.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND dp.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND dp.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dp.update_time DESC, dp.create_time DESC
    </select>

    <!-- 被接收人员tab（我辖区发出的，当前在他区） -->
    <select id="selectBeReceivedList" parameterType="page" resultType="map">
        SELECT
            dp.key_person_id,
            dp.sys_type,
            dp.dual_id,
            person.name,
            person.card_no,
            city.name AS recvie_city_name,
            area.name AS recvie_area_name,
            street.name AS recvie_street_name,
            community.name AS recvie_community_name
        FROM jl_key_person_rating_dual_person dp
        INNER JOIN jl_key_person_rating_dual dualRecord ON dualRecord.id = dp.dual_id
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dp.key_person_id
        LEFT JOIN sys_office city ON city.id = dp.city_id
        LEFT JOIN sys_office area ON area.id = dp.area_id
        LEFT JOIN sys_office street ON street.id = dp.street_id
        LEFT JOIN sys_office community ON community.id = dp.community_id
        <where>
            AND dualRecord.belong_area_id = #{params.operator_area_id}
            AND dp.sys_type = #{params.sys_type}
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dp.city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dp.area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dp.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dp.community_id = #{params.community_id}
            </if>
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND dp.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND dp.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND dp.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND dp.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dp.update_time DESC, dp.create_time DESC
    </select>

    <!-- 推送人员选择 -->
    <select id="selectBelongPersonList" parameterType="page" resultType="map">
        SELECT
            p.id AS key_person_id,
            p.name,
            CAST(AES_DECRYPT(FROM_BASE64(p.card_no),'*#id#') AS CHAR) AS card_no,
            p.city_id,
            p.area_id,
            p.street_id,
            p.community_id,
            p.sys_type,
            city.name AS city_name,
            area.name AS area_name,
            street.name AS street_name,
            community.name AS community_name
        FROM
        <choose>
            <when test='params.sys_type == "6"'>
                jl_key_person_minor p
            </when>
            <otherwise>
                jl_key_person p
            </otherwise>
        </choose>
        LEFT JOIN sys_office city ON city.id = p.city_id
        LEFT JOIN sys_office area ON area.id = p.area_id
        LEFT JOIN sys_office street ON street.id = p.street_id
        LEFT JOIN sys_office community ON community.id = p.community_id
        <where>
            AND p.area_id = #{params.operator_area_id}
            <if test='params.sys_type != "6"'>
                AND p.sys_type = #{params.sys_type}
            </if>
            <if test="params.name != null and params.name != ''">
                AND p.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND p.card_no = TO_BASE64(AES_ENCRYPT(#{params.card_no},'*#id#'))
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND p.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND p.community_id = #{params.community_id}
            </if>
            AND NOT EXISTS (
                SELECT 1 FROM jl_key_person_rating_dual dualRecord
                WHERE dualRecord.key_person_id = p.id
                  AND dualRecord.push_status = '1'
            )
            AND NOT EXISTS (
                SELECT 1 FROM jl_key_person_rating_dual_person dp
                WHERE dp.key_person_id = p.id
            )
            <!-- 数据范围 -->
            <if test="params.scope_grade != null and params.scope_grade != ''">
                <choose>
                    <when test='params.scope_grade == "3"'>
                        AND p.city_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "4"'>
                        AND p.area_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "5"'>
                        AND p.street_id = #{params._user.area_id}
                    </when>
                    <when test='params.scope_grade == "6"'>
                        AND p.community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY p.create_date DESC
    </select>

    <select id="getDualDetail" parameterType="map" resultType="map">
        SELECT
            dualRecord.*,
            person.name AS person_name,
            person.card_no,
            pushCity.name AS push_city_name,
            pushArea.name AS push_area_name,
            pushStreet.name AS push_street_name,
            pushCommunity.name AS push_community_name,
            recCity.name AS recvie_city_name,
            recArea.name AS recvie_area_name,
            recStreet.name AS recvie_street_name,
            recCommunity.name AS recvie_community_name,
            belongCity.name AS belong_city_name,
            belongArea.name AS belong_area_name,
            belongStreet.name AS belong_street_name,
            belongCommunity.name AS belong_community_name,
            pushUser.name AS push_user_name,
            auditUser.name AS audit_user_name,
            returnUser.name AS return_user_name
        FROM jl_key_person_rating_dual dualRecord
        LEFT JOIN (
            <include refid="personUnion"/>
        ) person ON person.id = dualRecord.key_person_id
        LEFT JOIN sys_office pushCity ON pushCity.id = dualRecord.push_city_id
        LEFT JOIN sys_office pushArea ON pushArea.id = dualRecord.push_area_id
        LEFT JOIN sys_office pushStreet ON pushStreet.id = dualRecord.push_street_id
        LEFT JOIN sys_office pushCommunity ON pushCommunity.id = dualRecord.push_community_id
        LEFT JOIN sys_office recCity ON recCity.id = dualRecord.recvie_city_id
        LEFT JOIN sys_office recArea ON recArea.id = dualRecord.recvie_area_id
        LEFT JOIN sys_office recStreet ON recStreet.id = dualRecord.recvie_street_id
        LEFT JOIN sys_office recCommunity ON recCommunity.id = dualRecord.recvie_community_id
        LEFT JOIN sys_office belongCity ON belongCity.id = dualRecord.belong_city_id
        LEFT JOIN sys_office belongArea ON belongArea.id = dualRecord.belong_area_id
        LEFT JOIN sys_office belongStreet ON belongStreet.id = dualRecord.belong_street_id
        LEFT JOIN sys_office belongCommunity ON belongCommunity.id = dualRecord.belong_community_id
        LEFT JOIN sys_user pushUser ON pushUser.id = dualRecord.push_user
        LEFT JOIN sys_user auditUser ON auditUser.id = dualRecord.audit_user
        LEFT JOIN sys_user returnUser ON returnUser.id = dualRecord.return_user
        WHERE dualRecord.id = #{id}
    </select>

    <select id="getDualRecordById" parameterType="map" resultType="map">
        SELECT *
        FROM jl_key_person_rating_dual
        WHERE id = #{id}
    </select>

    <select id="getDualPersonById" parameterType="map" resultType="map">
        SELECT *
        FROM jl_key_person_rating_dual_person
        WHERE id = #{dual_person_id}
    </select>

    <select id="getPersonBase" parameterType="map" resultType="map">
        SELECT
            id,
            sys_type,
            region,
            city_id,
            area_id,
            street_id,
            community_id
        FROM
        <choose>
            <when test='sys_type == "6"'>
                jl_key_person_minor
            </when>
            <otherwise>
                jl_key_person
            </otherwise>
        </choose>
        WHERE id = #{key_person_id}
    </select>

    <select id="getDualContextByKeyPersonId" parameterType="map" resultType="map">
        SELECT
            dp.*,
            dualRecord.belong_region,
            dualRecord.belong_city_id,
            dualRecord.belong_area_id,
            dualRecord.belong_street_id,
            dualRecord.belong_community_id,
            dualRecord.belong_user_id
        FROM jl_key_person_rating_dual_person dp
        LEFT JOIN jl_key_person_rating_dual dualRecord ON dualRecord.id = dp.dual_id
        WHERE dp.key_person_id = #{key_person_id}
        ORDER BY dp.update_time DESC, dp.create_time DESC
        LIMIT 1
    </select>

    <select id="countPendingPush" parameterType="map" resultType="string">
        SELECT COUNT(1) FROM jl_key_person_rating_dual
        WHERE key_person_id = #{key_person_id}
          AND push_status = '1'
    </select>

    <insert id="insertDualRecord" parameterType="map">
        INSERT INTO jl_key_person_rating_dual(
            id,
            key_person_id,
            sys_type,
            belong_user_id,
            belong_region,
            belong_city_id,
            belong_area_id,
            belong_street_id,
            belong_community_id,
            push_region,
            push_city_id,
            push_area_id,
            push_street_id,
            push_community_id,
            recvie_region,
            recvie_city_id,
            recvie_area_id,
            recvie_street_id,
            recvie_community_id,
            push_status,
            push_reason,
            push_type,
            push_time,
            push_user,
            create_user,
            create_time,
            update_user,
            update_time
        ) VALUES (
            #{id},
            #{key_person_id},
            #{sys_type},
            #{belong_user_id},
            #{belong_region},
            #{belong_city_id},
            #{belong_area_id},
            #{belong_street_id},
            #{belong_community_id},
            #{push_region},
            #{push_city_id},
            #{push_area_id},
            #{push_street_id},
            #{push_community_id},
            #{recvie_region},
            #{recvie_city_id},
            #{recvie_area_id},
            #{recvie_street_id},
            #{recvie_community_id},
            #{push_status},
            #{push_reason},
            #{push_type},
            #{push_time},
            #{push_user},
            #{create_user},
            #{create_time},
            #{update_user},
            #{update_time}
        )
    </insert>

    <update id="revokeDualRecord" parameterType="map">
        UPDATE jl_key_person_rating_dual
        SET push_status = '0',
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
          AND push_status = '1'
    </update>

    <update id="acceptDualRecord" parameterType="map">
        UPDATE jl_key_person_rating_dual
        SET push_status = '2',
            recvie_community_id = #{recvie_community_id},
            audit_user = #{audit_user},
            audit_time = #{audit_time},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
    </update>

    <update id="rejectDualRecord" parameterType="map">
        UPDATE jl_key_person_rating_dual
        SET push_status = '3',
            reject_reason = #{reject_reason},
            audit_user = #{audit_user},
            audit_time = #{audit_time},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
    </update>

    <update id="returnDualRecord" parameterType="map">
        UPDATE jl_key_person_rating_dual
        SET push_status = '4',
            return_reason = #{return_reason},
            return_user = #{return_user},
            return_time = #{return_time},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
    </update>

    <insert id="insertDualPerson" parameterType="map">
        INSERT INTO jl_key_person_rating_dual_person(
            id,
            key_person_id,
            sys_type,
            dual_id,
            region,
            city_id,
            area_id,
            street_id,
            community_id,
            turn_status,
            create_user,
            create_time
        ) VALUES (
            #{id},
            #{key_person_id},
            #{sys_type},
            #{dual_id},
            #{region},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            #{turn_status},
            #{create_user},
            #{create_time}
        )
    </insert>

    <update id="updateDualPerson" parameterType="map">
        UPDATE jl_key_person_rating_dual_person
        SET dual_id = #{dual_id},
            region = #{region},
            city_id = #{city_id},
            area_id = #{area_id},
            street_id = #{street_id},
            community_id = #{community_id},
            turn_status = #{turn_status},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
    </update>

    <update id="updateDualPersonTurnStatus" parameterType="map">
        UPDATE jl_key_person_rating_dual_person
        SET turn_status = #{turn_status},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE id = #{id}
    </update>

    <update id="resetDualPersonTurnStatus" parameterType="map">
        UPDATE jl_key_person_rating_dual_person
        SET turn_status = #{turn_status},
            update_user = #{update_user},
            update_time = #{update_time}
        WHERE key_person_id = #{key_person_id}
    </update>

    <delete id="deleteDualPerson" parameterType="map">
        DELETE FROM jl_key_person_rating_dual_person
        WHERE id = #{id}
    </delete>

<!--    双属地通讯录， 目前只有长春范围-->
    <select id="selectDualPhoneList" parameterType="page" resultType="map">
        select a.id,a.city_id, a.city_name, a.area_id, a.area_name, a.street_id, a.street_name, a.community_id, a.community_name, a.lev, b.phone_number, b.id as mark_id
        from (
        select id,parent_id,parent_ids, CONCAT(parent_ids, ',', id) as px, sort, '3' as lev,
        id as city_id, name as city_name, null as area_id, null as area_name, null as street_id, null as street_name, null as community_id, null as community_name
        from sys_office city where city.id = '220100000000' and city.del_flag ='0' and city.is_manage_center =1
        union all
        select area.id,area.parent_id, area.parent_ids, CONCAT(area.parent_ids, ',', area.id) as px, area.sort,  '4' as lev,
        city.id as city_id, city.name as city_name, area.id as area_id, area.name as area_name, null as street_id, null as street_name, null as community_id, null as community_name
        from sys_office city left join sys_office area on area.parent_id = city.id
        where city.id = '220100000000' and city.del_flag ='0' and area.del_flag ='0' and city.is_manage_center =1 and area.is_manage_center =1
        union all
        select street.id,street.parent_id, street.parent_ids, CONCAT(street.parent_ids, ',', street.id) as px, street.sort, '5' as lev,
        city.id as city_id, city.name as city_name, area.id as area_id, area.name as area_name, street.id as street_id, street.name as street_name, null as community_id, null as community_name
        from sys_office city left join sys_office area on area.parent_id = city.id left join sys_office street on street.parent_id = area.id
        where city.id = '220100000000' and city.del_flag ='0' and area.del_flag ='0' and street.del_flag ='0' and city.is_manage_center =1 and area.is_manage_center =1  and street.is_manage_center =1
<!--        union all-->
<!--        select community.id,community.parent_id, community.parent_ids, CONCAT(community.parent_ids, ',', community.id) as px, community.sort, '6' as lev,-->
<!--        city.id as city_id, city.name as city_name, area.id as area_id, area.name as area_name, street.id as street_id, street.name as street_name, community.id as community_id, community.name as community_name-->
<!--        from sys_office city left join sys_office area on area.parent_id = city.id left join sys_office street on street.parent_id = area.id left join sys_office community on community.parent_id = street.id-->
<!--        where city.id = '220100000000' and city.del_flag ='0' and area.del_flag ='0' and street.del_flag ='0' and community.del_flag ='0'-->
<!--        and city.is_manage_center =1 and area.is_manage_center =1  and street.is_manage_center =1-->
        ) a left join jl_key_person_rating_dual_phone b on b.office_id = a.id
        <where>
            AND 1 = 1
            <if test="params.city_id != null and params.city_id != ''">
                AND a.city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND a.area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND a.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND a.community_id = #{params.community_id}
            </if>
        </where>
        ORDER BY a.px, a.sort
    </select>

    <insert id="insertDualPhone" parameterType="map">
        INSERT INTO jl_key_person_rating_dual_phone(
        id,
        office_id,
        city_id,
        area_id,
        street_id,
        community_id,
        phone_number,
        create_user,
        create_date
        ) VALUES (
        #{mark_id},
        #{id},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{phone_number},
        #{userId},
        NOW()
        )
    </insert>

    <update id="updateDualPhone" parameterType="map">
        UPDATE jl_key_person_rating_dual_phone
        SET phone_number = #{phone_number},
        update_user = #{userId},
        update_date = NOW()
        WHERE id = #{mark_id}
    </update>

<!--    双属地推送预警查询-->
    <select id="selectDualWarnPushList" parameterType="page" resultType="map">
        SELECT
        dualRecord.id,
        dualRecord.key_person_id,
        dualRecord.sys_type,
        dualRecord.push_status,
        dualRecord.push_reason,
        dualRecord.push_type,
        dualRecord.push_time,
        dualRecord.push_user,
        dualRecord.recvie_region,
        dualRecord.recvie_city_id,
        dualRecord.recvie_area_id,
        dualRecord.recvie_street_id,
        dualRecord.recvie_community_id,
        person.name,
        person.card_no,
        recCity.name AS recvie_city_name,
        recArea.name AS recvie_area_name,
        recStreet.name AS recvie_street_name,
        recCommunity.name AS recvie_community_name,
        pushUser.name AS push_user_name,
        auditUser.name AS audit_user_name,
        returnUser.name AS return_user_name
        FROM jl_key_person_rating_dual dualRecord
        LEFT JOIN (
        <include refid="personUnion"/>
        ) person ON person.id = dualRecord.key_person_id
        LEFT JOIN sys_office recCity ON recCity.id = dualRecord.recvie_city_id
        LEFT JOIN sys_office recArea ON recArea.id = dualRecord.recvie_area_id
        LEFT JOIN sys_office recStreet ON recStreet.id = dualRecord.recvie_street_id
        LEFT JOIN sys_office recCommunity ON recCommunity.id = dualRecord.recvie_community_id
        LEFT JOIN sys_user pushUser ON pushUser.id = dualRecord.push_user
        LEFT JOIN sys_user auditUser ON auditUser.id = dualRecord.audit_user
        LEFT JOIN sys_user returnUser ON returnUser.id = dualRecord.return_user
        <where>
            AND dualRecord.sys_type = #{params.sys_type}
            AND dualRecord.push_type = '0'
            AND dualRecord.push_status = 1
            AND NOW() - INTERVAL 24 HOUR > dualRecord.push_time
            <if test="params.name != null and params.name != ''">
                AND person.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.card_no != null and params.card_no != ''">
                AND person.card_no = #{params.card_no}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND dualRecord.recvie_city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND dualRecord.recvie_area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND dualRecord.recvie_street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND dualRecord.recvie_community_id = #{params.community_id}
            </if>
            <if test="params.push_status != null and params.push_status != ''">
                AND dualRecord.push_status = #{params.push_status}
            </if>
            <!-- 数据范围 -->
            <if test="params._user.company_grade != null and params._user.company_grade != ''">
                <choose>
                    <when test='params._user.company_grade == "3"'>
                        AND dualRecord.belong_city_id = #{params._user.area_id}
                    </when>
                    <when test='params._user.company_grade == "4"'>
                        AND dualRecord.belong_area_id = #{params._user.area_id}
                    </when>
                    <when test='params._user.company_grade == "5"'>
                        AND dualRecord.belong_street_id = #{params._user.area_id}
                    </when>
                    <when test='params._user.company_grade == "6"'>
                        AND dualRecord.belong_community_id = #{params._user.area_id}
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY dualRecord.create_time DESC
    </select>


</mapper>
