<?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.peopleInfo" name="人员信息">
    <sql id="peopleInfo">
            a.id,
            a.user_type,
            a.huji_type,
            a.is_kgh,
            a.relation,
            a.juzhu_type,
            a.living_condition,
            a.user_name,
            a.cardno encrypt_cardno, 
            CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') AS CHAR),4)) as cardno,
            a.card_front_url,
            a.card_back_url,
            a.tel,
            a.folk,
            a.sex,
            a.political_outlook,
            a.avatar_url,
            a.city_id,
            a.area_id,
            a.street_id,
            a.community_id,
            a.community_code,
            a.garden_id,
            a.building_id,
            a.unit_id,
            a.floor_id,
            a.doorplate_id,
        a.key_focus_personnel_type,
        a.key_focus_personnel_type_json,
            a.del_flag,
            a.create_by,
            a.create_time,
            a.update_by,
            a.update_time,
            a.is_zdry,
            a.hj_address,
            a.now_address,
            a.remarks
    </sql>
    
    <select id="getPeopleLabelStaticCount" parameterType="map" resultType="map">
    	SELECT
    		b.cardno encrypt_cardno,
			JSON_ARRAYAGG( c.`name` ) label_name,
			JSON_ARRAYAGG( c.id ) label_id 
		FROM
			jl_grid_focusgroup_type c
			INNER JOIN jl_grid_user_focusgroup b ON c.id = b.lable_id 
		<where>
			b.cardno is not null and 
			b.cardno in 
			<foreach collection="cardnoList" item="item" index="index" open="(" separator="," close=")">
	            #{item}
	        </foreach>
		</where>
			GROUP BY b.cardno
    </select>
    
    <select id="getPeopleEventStaticCount" parameterType="map" resultType="map">
		SELECT
			jeu.event_cardno encrypt_cardno,
			COUNT(DISTINCT je.id ) cardNumber,
			COUNT(CASE WHEN STATUS = '1' THEN je.id ELSE NULL END) statusNumber 
		FROM
			jl_event je
			LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id 
		<where>
			jeu.event_cardno is not null and 
			jeu.event_cardno in 
			<foreach collection="cardnoList" item="item" index="index" open="(" separator="," close=")">
	            #{item}
	        </foreach>
		</where>
			GROUP BY jeu.event_cardno
    </select>
    

    <select id="findPeopleInfoListForSharding" parameterType="page" resultType="map">
        SELECT
        <!-- 
            jgg.name gardenName,
            jgb.name buildName,
            jgu.name unitName,
            jgf.name floorName,
            jgd.name doorplateName, -->
        <include refid="peopleInfo"/>
        FROM
            jl_grid_user AS a
            <!-- 
        LEFT JOIN jl_grid_garden jgg ON a.garden_id = jgg.id
        LEFT JOIN jl_grid_building jgb ON a.building_id = jgb.id
        LEFT JOIN jl_grid_unit jgu ON a.unit_id = jgu.id
        LEFT JOIN jl_grid_floor jgf ON a.floor_id = jgf.id
        LEFT JOIN jl_grid_doorplate jgd ON a.doorplate_id = jgd.id-->
        <if test="params.label_id != null and params.label_id != ''">
            inner join jl_grid_user_focusgroup label on label.city_id=a.city_id and label.cardno = a.cardno
        </if>
        WHERE
            a.del_flag = '0'
        <if test="params.label_id != null and params.label_id != ''">
            and label.lable_id = #{params.label_id}
        </if>    
        <if test="params._user.company_grade == '2'.toString()">
            AND 1 = 1
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            AND a.city_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND a.area_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND a.street_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND a.community_id = #{params.current_id}
        </if>
        <if test="params.user_name != null and params.user_name != ''">
            and a.user_name like concat('%', #{params.user_name, jdbcType=VARCHAR}, '%')
        </if>
        <if test="params.cardno != null and params.cardno != ''">
            and a.cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
        </if>
        <if test="params.city_id != null and params.city_id != ''">
            and a.city_id = #{params.city_id}
        </if>
        <if test="params.tel != null and params.tel != ''">
            and a.tel = #{params.tel}
        </if>
        <if test="params.is_zdry != null and params.is_zdry != ''">
            and a.is_zdry = #{params.is_zdry}
        </if>
        ORDER BY a.create_time DESC
    </select>
    
    <select id="findPeopleInfoList" parameterType="page" resultType="map">
        SELECT
            ( SELECT JSON_ARRAYAGG( c.name ) FROM jl_grid_focusgroup_type c WHERE c.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup b WHERE cardno = a.cardno ) ) AS label_name,
            ( SELECT JSON_ARRAYAGG( c.id ) FROM jl_grid_focusgroup_type c WHERE c.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup b WHERE cardno = a.cardno ) ) AS label_id,
            (SELECT COUNT(DISTINCT je.id) FROM jl_event je  LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id WHERE jeu.event_cardno = cardno) cardNumber,
            (SELECT COUNT(je.id) FROM jl_event je LEFT JOIN jl_event_user jeu ON je.id = jeu.event_id WHERE status = '1' AND jeu.event_cardno = cardno) statusNumber,
            jgg.name gardenName,
            jgb.name buildName,
            jgu.name unitName,
            jgf.name floorName,
            jgd.name doorplateName,
        <include refid="peopleInfo"/>
        FROM
            jl_grid_user AS a
        LEFT JOIN jl_grid_garden jgg ON a.garden_id = jgg.id
        LEFT JOIN jl_grid_building jgb ON a.building_id = jgb.id
        LEFT JOIN jl_grid_unit jgu ON a.unit_id = jgu.id
        LEFT JOIN jl_grid_floor jgf ON a.floor_id = jgf.id
        LEFT JOIN jl_grid_doorplate jgd ON a.doorplate_id = jgd.id
        WHERE
            a.del_flag = '0'
        <if test="params._user.company_grade == '2'.toString()">
            AND 1 = 1
        </if>
        <if test="params._user.company_grade == '3'.toString()">
            AND a.city_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND a.area_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND a.street_id = #{params.current_id}
        </if>
        <if test="params._user.company_grade == '6'.toString()">
            AND a.community_id = #{params.current_id}
        </if>
        <!--网格员字段-->
        <if test="params.garden != null">
            and a.garden_id = #{params.garden.garden_id}
        </if>
        <if test="params.user_name != null and params.user_name != ''">
            and a.user_name like concat('%', #{params.user_name, jdbcType=VARCHAR}, '%')
        </if>
        <if test="params.cardno != null and params.cardno != ''">
            and a.cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
        </if>
        <if test="params.tel != null and params.tel != ''">
            and a.tel = #{params.tel}
        </if>
        <if test="params.is_special != null and params.is_special != ''">
            and a.is_special = #{params.is_special}
        </if>
        <if test="params.is_zdry != null and params.is_zdry != ''">
            and a.is_zdry = #{params.is_zdry}
        </if>
        <if test="params.label_id != null and params.label_id != ''">
            and EXISTS (select id from jl_grid_user_focusgroup where cardno = a.cardno and lable_id = #{params.label_id})
        </if>
        ORDER BY a.create_time DESC
    </select>

    <update id="updateIsSpecial" parameterType="map" expose="true">
        UPDATE
            jl_grid_user
        SET
            is_special = '2',
            update_by = #{_user.company_code},
            update_time = NOW()
        WHERE
            id = #{id}
    </update>

    <select id="getPeopleInfoNum" parameterType="map" resultType="map">
        SELECT
            count(1) as num
        FROM jl_grid_user AS a
        WHERE
            a.del_flag = '0'
        <if test="_user.company_grade == '2'.toString()">
            AND 1 = 1
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND a.city_id = #{current_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND a.area_id = #{current_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND a.street_id = #{current_id}
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND a.community_id = #{current_id}
        </if>
        <!--网格员字段-->
        <if test="garden != null">
            and a.garden_id = #{garden.garden_id}
        </if>
        <if test="user_name != null and user_name != ''">
            and a.user_name like concat('%', #{user_name, jdbcType=VARCHAR}, '%')
        </if>
        <if test="cardno != null and cardno != ''">
            and a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
        </if>
        <if test="tel != null and tel != ''">
            and a.tel = #{tel}
        </if>
        <if test="is_special != null and is_special != ''">
            and a.is_special = #{is_special}
        </if>
        <if test="is_zdry != null and is_zdry != ''">
            and a.is_zdry = #{is_zdry}
        </if>
        <if test="label_id != null and label_id != ''">
            and EXISTS (select id from jl_grid_user_focusgroup where cardno = a.cardno and lable_id = #{label_id})
        </if>
    </select>
    <insert id="createPeopleInfo" parameterType="map">
        INSERT INTO jl_grid_user (
            id,
            user_type,
            huji_type,
            is_kgh,
            relation,
            juzhu_type,
            living_condition,
            user_name,
            cardno,
            card_front_url,
            card_back_url,
            tel,
            folk,
            sex,
            political_outlook,
            avatar_url,
            city_id,
            area_id,
            street_id,
            community_id,
            community_code,
            garden_id,
            building_id,
            unit_id,
            floor_id,
            doorplate_id,
            create_by,
            create_time,
            is_zdry,
            hj_address,
            now_address,
            <if test="is_special != null and is_special != ''">
                is_special,
            </if>
            remarks,
            key_focus_personnel_type,
            key_focus_personnel_type_json
        )VALUES(
            UUID(),
            #{user_type},
            #{huji_type},
            #{is_kgh},
            #{relation},
            #{juzhu_type},
            #{living_condition},
            #{user_name},
            TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
            #{card_front_url},
            #{card_back_url},
            #{tel},
            #{folk},
            #{sex},
            #{political_outlook},
            #{avatar_url},
            #{city_id},
            #{area_id},
            #{street_id},
            #{current_id},
            #{_user.company_code},
            #{garden_id},
            #{building_id},
            #{unit_id},
            #{floor_id},
            #{doorplate_id},
            #{_user.loginName},
            NOW(),
            #{is_zdry},
            #{hj_address},
            #{now_address},
            <if test="is_special != null and is_special != ''">
                #{is_special},
            </if>
            #{remarks},
        #{key_focus_personnel_type},
        #{key_focus_personnel_type_json}
        )
    </insert>
    <insert id="createPeopleLabel" parameterType="map">
        insert into jl_grid_user_focusgroup(
            id,
            cardno,
            lable_id,
            create_by,
            create_time
        )VALUES
        <foreach collection="dis_label_id" item="item" separator="," index="index">
            (
            UUID(),
            TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
            #{item},
            #{_user.company_id},
            now()
            )
        </foreach>
    </insert>
    <update id="updatePeopleInfo" parameterType="map">
        UPDATE jl_grid_user
        SET
            user_type = #{user_type},
            huji_type = #{huji_type},
            is_kgh = #{is_kgh},
            relation = #{relation},
            juzhu_type = #{juzhu_type},
            living_condition = #{living_condition},
            user_name = #{user_name},
            card_front_url = #{card_front_url},
            card_back_url = #{card_back_url},
            tel = #{tel},
            folk = #{folk},
            sex = #{sex},
            political_outlook = #{political_outlook},
            avatar_url = #{avatar_url},
            garden_id = #{garden_id},
            building_id = #{building_id},
            unit_id = #{unit_id},
            floor_id = #{floor_id},
            doorplate_id = #{doorplate_id},
            update_by = #{_user.company_id},
            update_time = NOW(),
            is_zdry = #{is_zdry},
            hj_address = #{hj_address},
            now_address = #{now_address},
        key_focus_personnel_type = #{key_focus_personnel_type},
        key_focus_personnel_type_json = #{key_focus_personnel_type_json},
            remarks = #{remarks}
        WHERE cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </update>
    <delete id="deletePeopleLabel" parameterType="map">
        DELETE FROM jl_grid_user_focusgroup WHERE cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </delete>
    <select id="getPeopleInfoByCardno" parameterType="map" resultType="map">
        select
            ( SELECT JSON_ARRAYAGG( c.`name` ) FROM jl_grid_focusgroup_type c WHERE c.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup b WHERE cardno = a.cardno ) ) AS label_name,
            ( SELECT JSON_ARRAYAGG( c.id ) FROM jl_grid_focusgroup_type c WHERE c.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup b WHERE cardno = a.cardno ) ) AS label_id,
            jgg.name gardenName,
            jgb.name buildName,
            jgu.name unitName,
            jgf.name floorName,
            jgd.name doorplateName,
            so.name cityName,
            so1.name areaName,
            so2.name streetName,
            so3.name communityName,
        <include refid="peopleInfo"/>
        from jl_grid_user a
        LEFT JOIN sys_office so ON a.city_id = so.id
        LEFT JOIN sys_office so1 ON a.area_id = so1.id
        LEFT JOIN sys_office so2 ON a.street_id = so2.id
        LEFT JOIN sys_office so3 ON a.community_id = so3.id
        LEFT JOIN jl_grid_garden jgg ON a.garden_id = jgg.id
        LEFT JOIN jl_grid_building jgb ON a.building_id = jgb.id
        LEFT JOIN jl_grid_unit jgu ON a.unit_id = jgu.id
        LEFT JOIN jl_grid_floor jgf ON a.floor_id = jgf.id
        LEFT JOIN jl_grid_doorplate jgd ON a.doorplate_id = jgd.id
        where a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </select>
    <select id="disposeOfficeCodeLevel" parameterType="map" resultType="map">
        SELECT
            ID,
            PARENT_ID,
            PARENT_IDS,
            `NAME`,
            SORT,
            `CODE`,
            TYPE,
            CREATE_BY,
            CREATE_DATE,
            UPDATE_BY,
            UPDATE_DATE,
            REMARKS,
            DEL_FLAG
        FROM
            sys_area
        WHERE
            DEL_FLAG = '0'
        AND
            ID = (
            SELECT
                AREA_ID
            FROM
                sys_office
            WHERE
                `CODE` = #{_user.company_code}
        )
    </select>
    <select id="findLabelList" parameterType="map" resultType="map">
        SELECT
            id,
            sort,
            `name`,
            label_img,
            label_type,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        FROM
            jl_grid_focusgroup_type
        WHERE
            del_flag = '0'
        ORDER BY
            sort
    </select>
    <select id="findMZList" parameterType="map" resultType="map">
        SELECT
            id,
            mzcode,
            mzname
        FROM
            jl_grid_mz_code
    </select>
    <insert id="createPeopleZFLog" parameterType="map">
        insert into jl_visit_log(
            id,
            city_id,
            area_id,
            street_id,
            community_id,
            user_id,
            visit_date,
            grid_user_cardno,
            is_pitfall,
            is_follow,
            visit_content,
            attachment,
            <if test="abnormal != null and abnormal != ''">
                abnormal,
            </if>
            <if test="risk_level != null and risk_level != ''">
                risk_level,
            </if>
            create_by,
            create_time
        )VALUES(
            UUID(),
            #{city_id},
            #{area_id},
            #{street_id},
            #{current_id},
            #{_user.id},
            #{visit_date},
            TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
            #{is_pitfall},
            #{is_follow},
            #{visit_content},
            #{attachment},
            <if test="abnormal != null and abnormal != ''">
                #{abnormal},
            </if>
            <if test="risk_level != null and risk_level != ''">
                #{risk_level},
            </if>
            #{_user.loginName},
            now()
        )
    </insert>

    <select id="getCardNoById" parameterType="map" resultType="map" expose="true">
        SELECT
            CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR) AS cardno
        FROM
            jl_grid_user
        WHERE
            id = #{id}
    </select>

    <select id="findPeopleZFLogList" parameterType="map" resultType="map">
        SELECT
            a.id,
            a.city_id,
            a.area_id,
            a.street_id,
            a.community_id,
            a.user_id,
            DATE(a.visit_date) as visit_date,
            CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),4)) as cardno,
            CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),4)) as grid_user_cardno,
<!--            CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),-->
<!--            CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR) as grid_user_cardno,-->
            a.is_pitfall,
            a.is_follow,
            a.visit_content,
            a.attachment,
            a.create_by,
            a.create_time,
            a.update_by,
            a.update_time,
            a.abnormal,
            a.risk_level,
            b.NAME as community_user_name,
            c.user_name,
            c.tel,
            c.garden_id,
            c.building_id,
            c.unit_id,
            c.floor_id,
            c.doorplate_id,
            jgg.name gardenName,
            jgb.name buildName,
            jgu.name unitName,
            jgf.name floorName,
            jgd.name doorplateName,
            so.name cityName,
            so1.name areaName,
            so2.name streetName,
            so3.name communityName,
            ( SELECT JSON_ARRAYAGG( d.`name` ) FROM jl_grid_focusgroup_type d WHERE d.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup WHERE cardno = a.grid_user_cardno ) ) AS label_name
        FROM
            jl_visit_log a
        LEFT JOIN sys_user b on a.user_id = b.id
        LEFT JOIN jl_grid_user c on a.grid_user_cardno = c.cardno
        LEFT JOIN sys_office so ON c.city_id = so.id
        LEFT JOIN sys_office so1 ON c.area_id = so1.id
        LEFT JOIN sys_office so2 ON c.street_id = so2.id
        LEFT JOIN sys_office so3 ON c.community_id = so3.id
        LEFT JOIN jl_grid_garden jgg ON c.garden_id = jgg.id
        LEFT JOIN jl_grid_building jgb ON c.building_id = jgb.id
        LEFT JOIN jl_grid_unit jgu ON c.unit_id = jgu.id
        LEFT JOIN jl_grid_floor jgf ON c.floor_id = jgf.id
        LEFT JOIN jl_grid_doorplate jgd ON c.doorplate_id = jgd.id
        WHERE
            a.grid_user_cardno = TO_BASE64(AES_ENCRYPT(#{card_no},'*#id#'))
        <if test="_user.company_grade == '2'.toString()">
            AND 1 = 1
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND a.city_id = #{current_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND a.area_id = #{current_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND a.street_id = #{current_id}
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND a.community_id = #{current_id}
        </if>
        ORDER BY
            a.visit_date DESC
    </select>


    <select id="findPeopleZFLogList2" parameterType="map" resultType="map">
        SELECT
        a.id,
        a.city_id,
        a.area_id,
        a.street_id,
        a.community_id,
        a.user_id,
        DATE(a.visit_date) as visit_date,
        CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),4)) as cardno,
        CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(a.grid_user_cardno),'*#id#') AS CHAR),4)) as grid_user_cardno,
        a.is_pitfall,
        a.is_follow,
        a.visit_content,
        a.attachment,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.abnormal,
        a.risk_level,
        b.NAME as community_user_name,
        c.user_name,
        c.tel,
        c.garden_id,
        c.building_id,
        c.unit_id,
        c.floor_id,
        c.doorplate_id,
        jgg.name gardenName,
        jgb.name buildName,
        jgu.name unitName,
        jgf.name floorName,
        jgd.name doorplateName,
        so.name cityName,
        so1.name areaName,
        so2.name streetName,
        so3.name communityName,
        ( SELECT JSON_ARRAYAGG( d.`name` ) FROM jl_grid_focusgroup_type d WHERE d.id IN ( SELECT lable_id FROM jl_grid_user_focusgroup WHERE cardno = a.grid_user_cardno ) ) AS label_name
        FROM
        jl_visit_log a
        LEFT JOIN sys_user b on a.user_id = b.id
        LEFT JOIN jl_grid_user c on a.grid_user_cardno = c.cardno
        LEFT JOIN sys_office so ON c.city_id = so.id
        LEFT JOIN sys_office so1 ON c.area_id = so1.id
        LEFT JOIN sys_office so2 ON c.street_id = so2.id
        LEFT JOIN sys_office so3 ON c.community_id = so3.id
        LEFT JOIN jl_grid_garden jgg ON c.garden_id = jgg.id
        LEFT JOIN jl_grid_building jgb ON c.building_id = jgb.id
        LEFT JOIN jl_grid_unit jgu ON c.unit_id = jgu.id
        LEFT JOIN jl_grid_floor jgf ON c.floor_id = jgf.id
        LEFT JOIN jl_grid_doorplate jgd ON c.doorplate_id = jgd.id
        WHERE
            a.grid_user_cardno = TO_BASE64(AES_ENCRYPT(#{card_no},'*#id#'))
        <if test="_user.company_grade == '2'.toString()">
            AND 1 = 1
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND a.city_id = #{current_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND a.area_id = #{current_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND a.street_id = #{current_id}
        </if>
        <if test="_user.company_grade == '6'.toString()">
            AND a.community_id = #{current_id}
        </if>
        ORDER BY
        a.visit_date DESC
    </select>

    <update id="updatePeopleZFLog" parameterType="map">
        UPDATE jl_visit_log
        SET is_pitfall = #{is_pitfall},
            is_follow = #{is_follow},
        <if test="abnormal != null and abnormal != ''">
            abnormal = #{abnormal},
        </if>
        <if test="risk_level != null and risk_level != ''">
            risk_level = #{risk_level},
        </if>
            visit_content = #{visit_content}
        WHERE
            id = #{id}
    </update>
    <delete id="deletePeopleZFLog" parameterType="map">
        DELETE FROM jl_visit_log where id = #{id}
    </delete>

    <select id="selectUserFamilyList" parameterType="map" resultType="map" expose="true">
        SELECT
            id,
            user_id,
            name,
            CAST(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') AS CHAR) as cardno,
            tel,
            address,
            relationship
        FROM
            jl_grid_user_family
        WHERE
            user_id = #{user_id}
        AND del_flag = '0'
    </select>

    <insert id="saveUserFamily" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_grid_user_family WHERE id = #{id}
        </selectKey>
        <if test="count == 0">
            INSERT INTO jl_grid_user_family(
                id,
                user_id,
                name,
                cardno,
                tel,
                address,
                relationship,
                create_by,
                create_time
            ) VALUES (
                UUID(),
                #{user_id},
                #{name},
                TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
                #{tel},
                #{address},
                #{relationship},
                #{_user.id},
                NOW()
            )
        </if>
        <if test="count > 0">
            UPDATE
                jl_grid_user_family
            SET
                name = #{name},
                cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
                tel = #{tel},
                address = #{address},
                relationship = #{relationship},
                <if test="del_flag != null and del_flag != ''">
                    del_flag = #{del_flag},
                </if>
                create_by = #{_user.id},
                update_time = NOW()
            WHERE
                id = #{id}
        </if>
    </insert>
</mapper>