<?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.user" name="网格人员">
    <select id="getUserByCardNo" parameterType="map" resultType="map">
        SELECT
            (SELECT `name` FROM sys_area WHERE `code` = city_id) city,
            (SELECT `name` FROM sys_area WHERE `code` = area_id) area,
            (SELECT `name` FROM sys_area WHERE `code` = street_id) street,
            (SELECT `name` FROM sys_area WHERE `code` = community_id) community
        FROM
            jl_grid_user
        WHERE
            cardno = TO_BASE64(AES_ENCRYPT(upper(#{cardno}),'*#id#'))
    </select>
    
    <select id="decryptStr" parameterType="string" resultType="map">
    select CAST(AES_DECRYPT(FROM_BASE64(#{cardno}),'*#id#') AS CHAR) cardno,
    CONCAT( LEFT ( cast(AES_DECRYPT(FROM_BASE64(#{cardno}),'*#id#') as char), 6 ), '****', RIGHT ( cast(AES_DECRYPT(FROM_BASE64(#{cardno}),'*#id#') as char), 4 ) ) TMcardno
    </select>
    
    <select id="queryAreaName" parameterType="string" resultType="map">
    	select community.name community,street.name street,area.name area,city.name city from sys_area community left join sys_area street on community.parent_id=street.id
    	left join sys_area area on street.parent_id=area.id
    	left join sys_area city on area.parent_id=city.id 
    	where community.code=#{community_id}
    </select>

    <select id="selectPageGridUserList" parameterType="page" resultType="map" expose="true">
        select * from (
        SELECT
        jgu.id,
        jgu.user_type,
        jgu.huji_type,
        jgu.is_kgh,
        jgu.relation,
        jgu.juzhu_type,
        jgu.living_condition,
        jgu.user_name,
        CAST(AES_DECRYPT(FROM_BASE64(jgu.cardno),'*#id#') AS CHAR) cardno,
        jgu.card_front_url,
        jgu.card_back_url,
        jgu.tel,
        jgmc.mzname folk,
        jgu.sex,
        jgu.political_outlook,
        jgu.avatar_url,
        jgu.city_id,
        sa.`name` city,
        jgu.area_id,
        sa1.`name` area,
        jgu.street_id,
        sa2.`name` street,
        jgu.community_id,
        sa3.`name` community,
        jgu.community_code,
        jgu.garden_id,
        jgu.building_id,
        jgu.unit_id,
        jgu.floor_id,
        jgu.doorplate_id,
        jgu.is_zdry,
        jgu.hj_address,
        jgu.remarks,
        <if test="params.company_grade == '6'.toString()">
            0 flag,
        </if>
        <if test="params.company_grade != '6'.toString()">
            1 flag,
        </if>
        jgu.now_address
        FROM
        jl_grid_user jgu
        <if test="params.focusGroupList != null and params.focusGroupList.size() > 0">
            <foreach collection="params.focusGroupList" separator=" " item="item" index="index">
                INNER join (select distinct cardno from jl_grid_user_focusgroup where lable_id =
                #{params.focusGroupList[${index}]}
                ) foucs${index} on jgu.cardno=foucs${index}.cardno
            </foreach>
        </if>
        LEFT JOIN jl_grid_mz_code jgmc ON jgu.folk = jgmc.mzcode
        LEFT JOIN sys_area sa ON jgu.city_id = sa.`code`
        LEFT JOIN sys_area sa1 ON jgu.area_id = sa1.`code`
        LEFT JOIN sys_area sa2 ON jgu.street_id = sa2.`code`
        LEFT JOIN sys_area sa3 ON jgu.community_id = sa3.`code`
        <where>
            <if test="params._user.company_grade == '3'.toString()">
                and jgu.city_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '4'.toString()">
                and jgu.area_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '5'.toString()">
                and jgu.street_id = #{params._user.company_id}
            </if>
            <if test="params._user.company_grade == '6'.toString()">
                and jgu.community_id = #{params._user.company_id}
            </if>
            <if test="params.user_type != null and params.user_type != ''">
                AND jgu.user_type = #{params.user_type}
            </if>
            <if test="params.huji_type != null and params.huji_type != ''">
                AND jgu.huji_type = #{params.huji_type}
            </if>
            <if test="params.is_kgh != null and params.is_kgh != ''">
                AND jgu.is_kgh = #{params.is_kgh}
            </if>
            <if test="params.relation != null and params.relation != ''">
                AND jgu.relation = #{params.relation}
            </if>
            <if test="params.juzhu_type != null and params.juzhu_type != ''">
                AND jgu.juzhu_type = #{params.juzhu_type}
            </if>
            <if test="params.living_condition != null and params.living_condition != ''">
                AND jgu.living_condition = #{params.living_condition}
            </if>
            <if test="params.user_name != null and params.user_name != ''">
                AND jgu.user_name LIKE CONCAT(#{params.user_name},'%')
            </if>
            <if test="params.cardno != null and params.cardno != ''">
                AND jgu.cardno = TO_BASE64(AES_ENCRYPT(#{params.cardno},'*#id#'))
            </if>
            <if test="params.tel != null and params.tel != ''">
                AND jgu.tel LIKE CONCAT(#{params.user_name},'%')
            </if>
            <if test="params.folk != null and params.folk != ''">
                AND jgu.folk = #{params.folk}
            </if>
            <if test="params.sex != null and params.sex != ''">
                AND jgu.sex = #{params.sex}
            </if>
            <if test="params.folk != null and params.folk != ''">
                AND jgu.folk = #{params.folk}
            </if>
            <if test="params.political_outlook != null and params.political_outlook != ''">
                AND jgu.political_outlook = #{params.political_outlook}
            </if>
            <if test="params.city_id != null and params.city_id != ''">
                AND jgu.city_id = #{params.city_id}
            </if>
            <if test="params.area_id != null and params.area_id != ''">
                AND jgu.area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != ''">
                AND jgu.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != ''">
                AND jgu.community_id = #{params.community_id}
            </if>
            <if test="params.garden_id != null and params.garden_id != ''">
                AND jgu.garden_id = #{params.garden_id}
            </if>
            <if test="params.building_id != null and params.building_id != ''">
                AND jgu.building_id = #{params.building_id}
            </if>
            <if test="params.unit_id != null and params.unit_id != ''">
                AND jgu.unit_id = #{params.unit_id}
            </if>
            <if test="params.floor_id != null and params.floor_id != ''">
                AND jgu.floor_id = #{params.floor_id}
            </if>
            <if test="params.doorplate_id != null and params.doorplate_id != ''">
                AND jgu.doorplate_id = #{params.doorplate_id}
            </if>
            <if test="params.is_zdry != null and params.is_zdry != ''">
                AND jgu.is_zdry = #{params.is_zdry}
            </if>
            <if test="params.hj_address != null and params.hj_address != ''">
                AND jgu.hj_address  LIKE CONCAT(#{params.hj_address},'%')
            </if>
            <if test="params.now_address != null and params.now_address != ''">
                AND jgu.now_address  LIKE CONCAT(#{params.now_address},'%')
            </if>
            <if test="params.remarks != null and params.remarks != ''">
                AND jgu.remarks  LIKE CONCAT(#{params.remarks},'%')
            </if>
            <if test="params.lable_id != null and params.lable_id != ''">
                jguf.lable_id = #{lable_id}
            </if>
        </where>
        ) a
    </select>

    <select id="getGridUserByCardNo" parameterType="map" resultType="map" expose="true" name="根据身份证查询人员信息">
        SELECT
            *
        FROM
            jl_grid_user
        WHERE
        	cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </select>


    <select id="getGridUserByCardNoCount" parameterType="string" resultType="integer" expose="true" name="根据身份证查询人员信息">
        SELECT
        count(*)
        FROM
        jl_grid_user
        WHERE
        cardno = TO_BASE64(AES_ENCRYPT(upper(#{cardno}),'*#id#'))
    </select>

    <select id="findPatrolRecordList" parameterType="page" resultType="map" expose="true" name="网格员巡查列表">
        SELECT
            id,
            community_id,
            garden_id,
            (SELECT name FROM sys_area WHERE code = community_id) community,
            (SELECT name FROM jl_grid_garden WHERE id = garden_id) garden,
            patrol_name,
            cardno,
            patrol_project,
            start_time,
            end_time,
            patrol_distance,
            patrol_type,
            patrol_region,
            patrol_location,
            photo,
            current_duration,
            time,
            current_item,
            current_distance,
            checked_items,
            patrol_status,
            del_flag,
            cost_seconds,
            item_count,
            report_count
        FROM
            jl_grid_patrol_record
        WHERE
            del_flag = '0'
        AND community_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        <if test="params.garden_id != null and params.garden_id != ''">
            AND garden_id = #{params.garden_id}
        </if>
    </select>

    <select id="selectGardenList" parameterType="map" resultType="map" expose="true" name="查询网格">
        SELECT
            id,
            `name`
        FROM
            jl_grid_garden
        <where>
        	<choose>
        		<when test="community_id !=null and community_id !=''">
        			community_id = #{community_id}
        		</when>
        		<otherwise>
        			community_id = #{_user.company_id}
        		</otherwise>
        	</choose>
        </where>    
    </select>

    <select id="selectBuildingList" parameterType="map" resultType="map" expose="true" name="查询楼">
        SELECT
            id,
            `name`
        FROM
            jl_grid_building
        WHERE
            garden_id = #{garden_id}
            order by create_time asc
    </select>

    <select id="selectUnitList" parameterType="map" resultType="map" expose="true" name="查询单元">
        SELECT
            id,
            `name`
        FROM
            jl_grid_unit
        WHERE
            building_id = #{building_id}
            order by create_time asc
    </select>

    <select id="selectFloorList" parameterType="map" resultType="map" expose="true" name="查询层">
        SELECT
            id,
            `name`
        FROM
            jl_grid_floor
        WHERE
            unit_id = #{unit_id}
            order by sort asc
    </select>

    <select id="selectDoorplateList" parameterType="map" resultType="map" expose="true" name="查询户">
        SELECT
            id,
            `name`
        FROM
            jl_grid_doorplate
        WHERE
            floor_id = #{floor_id}
            order by sort asc
    </select>

    <select id="findFolkList" parameterType="map" resultType="map" expose="true" name="民族列表">
        SELECT
            id,
            mzcode,
            mzname
        FROM
            jl_grid_mz_code
    </select>


    <select id="findFolkCodeByName" parameterType="string" resultType="string">
        SELECT
        mzcode
        FROM
        jl_grid_mz_code
        where
        mzname = #{mzname}
    </select>

    <select id="selectUserFocusGroupList" parameterType="map" resultType="map" expose="true">
        SELECT
            a.id,
            a.cardno,
            a.lable_id,
            b.`name`lableName,
            a.two_lable_id,
            c.`name` twoLableName
        FROM
            jl_grid_user_focusgroup a
        LEFT JOIN jl_grid_focusgroup_type b ON a.lable_id = b.id
        LEFT JOIN jl_grid_focusgroup_type c ON a.two_lable_id = c.id
        WHERE
            a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </select>

    <insert id="insertUserGrid" 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,
            remarks,
            del_flag
        ) VALUE (
            UUID(),
            #{user_type},
            #{huji_type},
            #{is_kgh},
            #{relation},
            #{juzhu_type},
            #{living_condition},
            #{user_name},
            TO_BASE64(AES_ENCRYPT(upper(#{cardno}),'*#id#')),
            #{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},
            #{_user.id},
            NOW(),
            #{is_zdry},
            #{hj_address},
            #{now_address},
            #{remarks},
            '0'
        )
    </insert>


    <insert id="insertUserGridForTask" 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,
        remarks,
        del_flag,
        data_source
        ) VALUE (
        UUID(),
        #{user_type},
        #{huji_type},
        1,
        #{relation},
        #{juzhu_type},
        #{living_condition},
        #{user_name},
        TO_BASE64(AES_ENCRYPT(upper(#{cardno}),'*#id#')),
        #{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},
        NOW(),
        #{is_zdry},
        #{hj_address},
        #{now_address},
        #{remarks},
        '0',
        #{dataSource}
        )
    </insert>

    <insert id="insertFocusGroup" parameterType="map" expose="true">
        INSERT INTO jl_grid_user_focusgroup(
            id,
            cardno,
            lable_id,
            two_lable_id,
            create_by,
            create_time
        )VALUES(
            UUID(),
            TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
            #{lable_id},
            #{two_lable_id},
            #{_user.id},
            NOW()
        )
    </insert>

    <insert id="insertFocusGroupForTask" parameterType="map" expose="true">
        INSERT INTO jl_grid_user_focusgroup(
        id,
        cardno,
        lable_id,
        two_lable_id,
        create_by,
        create_time
        )VALUES(
        UUID(),
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        #{lable_id},
        #{two_lable_id},
        #{createBy},
        NOW()
        )
    </insert>
    
    <update id="updateGridUser" parameterType="map">
        UPDATE
            jl_grid_user
        SET
            <if test="user_type != null and user_type != ''">
                user_type = #{user_type},
            </if>
            <if test="huji_type != null and huji_type != ''">
                huji_type = #{huji_type},
            </if>
            <if test="is_kgh != null and is_kgh != ''">
                is_kgh = #{is_kgh},
            </if>
            <if test="relation != null and relation != ''">
                relation = #{relation},
            </if>
            <if test="juzhu_type != null and juzhu_type != ''">
                juzhu_type = #{juzhu_type},
            </if>
            <if test="living_condition != null and living_condition != ''">
                living_condition = #{living_condition},
            </if>
            <if test="user_name != null and user_name != ''">
                user_name = #{user_name},
            </if>
            <if test="cardno != null and cardno != ''">
                cardno = TO_BASE64(AES_ENCRYPT(upper(#{cardno}),'*#id#')),
            </if>
            <if test="card_front_url != null and card_front_url != ''">
                card_front_url = #{card_front_url},
            </if>
            <if test="card_back_url != null and card_back_url != ''">
                card_back_url = #{card_back_url},
            </if>
            <if test="tel != null and tel != ''">
                tel = #{tel},
            </if>
            <if test="folk != null and folk != ''">
                folk = #{folk},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="political_outlook != null and political_outlook != ''">
                political_outlook = #{political_outlook},
            </if>
            <if test="avatar_url != null and avatar_url != ''">
                avatar_url = #{avatar_url},
            </if>
            <choose>
            	<when test="is_kgh != null and is_kgh != '' and is_kgh == '1'.toString() ">
            	<!-- 是否空挂户   0:是，1:否 -->
            	<if test="garden_id != null and garden_id != ''">
                	garden_id = #{garden_id},
	            </if>
	            <if test="building_id != null and building_id != ''">
	                building_id = #{building_id},
	            </if>
	            <if test="unit_id != null and unit_id != ''">
	                unit_id = #{unit_id},
	            </if>
	            <if test="floor_id != null and floor_id != ''">
	                floor_id = #{floor_id},
	            </if>
	            <if test="doorplate_id != null and doorplate_id != ''">
	                doorplate_id = #{doorplate_id},
	            </if>
            	</when>
            	<otherwise>
            		garden_id = null,
            		building_id = null,
            		unit_id = null,
            		floor_id = null,
            		doorplate_id = null,
            	</otherwise>
            </choose>
            <if test="is_zdry != null and is_zdry != ''">
                is_zdry = #{is_zdry},
            </if>
            <if test="hj_address != null and hj_address != ''">
                hj_address = #{hj_address},
            </if>
            <if test="now_address != null and now_address != ''">
                now_address = #{now_address},
            </if>
            <if test="remarks != null and remarks != ''">
                remarks = #{remarks},
            </if>
            create_by = #{_user.id},
            create_time = NOW()
        WHERE
            id = #{id}
    </update>

    <update id="updateGridDoorplate" parameterType="map">
        UPDATE jl_grid_doorplate set people_num = people_num + 1 WHERE id = #{doorplate_id}
    </update>
    
    <select id="getUserById" parameterType="string" resultType="map">
        SELECT
            cardno
        FROM
            jl_grid_user
        WHERE
            id = #{id}
    </select>

    <delete id="deleteUserFocusGroupById" parameterType="map" expose="true">
        DELETE FROM jl_grid_user_focusgroup WHERE id = #{id}
    </delete>
</mapper>
