<?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.sysOfficeinfo" name="机构层级">
    <sql id="workerDivideJurisdictionForGraden">
        <!-- <if test="_user != null and _user.company_grade == '5'.toString() and !_user.loginName.contains('admin'.toString())"> -->
        <if test="_user != null and _user.company_grade == '5'.toString() and _user.loginName == 'LM2006111'.toString()">
            AND FIND_IN_SET( id, #{_user.garden_id} )
        </if>
    </sql>
    <sql id="workerDivideJurisdictionForBuilding">
        <!-- <if test="_user != null and _user.company_grade == '5'.toString() and !_user.loginName.contains('admin'.toString())"> -->
        <if test="_user != null and _user.company_grade == '5'.toString() and _user.loginName == 'LM2006111'.toString()">
            AND FIND_IN_SET( id, #{_user.building_id} )
        </if>
    </sql>
    <select id="getOfficeAreaIdByCompanyCode" parameterType="String" resultType="map">
		SELECT
		sya.id as areaId,
		sya.type as level
		FROM
		sys_office sof
		LEFT JOIN sys_area sya ON sof.area_id = sya.id
		WHERE
		sof.del_flag = '0'
		AND sof.CODE = #{company_code}
    </select>

    <select id="getAreaIdByOfficeId" parameterType="map" resultType="map">
        SELECT
        sya.id as areaId,
        sya.parent_ids as parentIds
        FROM
        sys_office sof
        LEFT JOIN sys_area sya ON sof.area_id = sya.id
        WHERE
        sof.del_flag = '0'
        AND sof.id = #{officeId}
    </select>
    <select id="getOfficeInfoByCompanyCode" parameterType="map" resultType="map">
        SELECT
        id,
        parent_id,
        parent_ids,
        `name`,
        area_id,
        `code`,
        type,
        grade,
        csc_mapjson,
        csc_map_fid
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND code=#{_user.company_code}
    </select>
    <select id="findOfficeInfoList" parameterType="map" resultType="map">
        SELECT
        id,
        parent_id,
        parent_ids,
        CASE
        grade
        WHEN 2 THEN `name`
        <!-- WHEN 3 THEN substring_index( `name`, RIGHT ( `name`, 3 ), 1 )  -->
        WHEN 3 THEN `name`
        WHEN 4 THEN `name`
        WHEN 5 THEN substring_index( `name`, '街道', - 1 )
        END AS `name`,
        <!-- `name`, -->
        area_id,
        `code`,
        type,
        grade,
        csc_mapjson
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND id not in (
        '100015',
        '100016',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
    </select>
    <select id="findSubordinateList" parameterType="map" resultType="map">
        SELECT
        id,
        parent_id,
        parent_ids,
        <!-- IF(grade='3',substring_index(`name`, RIGHT(`name`,3), 1),`name`) as `name`, -->
        `name`,
        area_id,
        `code`,
        type,
        grade,
        csc_mapjson
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND parent_id=#{id}
        AND id not in (
        '100015',
        '100016',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
        <if test="name != null and name != ''">
            AND name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
        ORDER BY
        sort
    </select>
    <update id="updateOfficeCoordinate" parameterType="map">
        UPDATE sys_office
        SET csc_mapjson = #{csc_mapjson}
        WHERE id=#{id}
    </update>
    <insert id="insertGridGarden" parameterType="map">
        INSERT INTO jl_grid_garden(
        id,
        name,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        create_time,
        <if test="map_type != null and map_type !=''">
            map_type,
        </if>
        create_by
        ) values (
        uuid(),
        #{name},
        #{grade},
        #{csc_mapjson},
        #{parent_ids},
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        <if test="map_type != null and map_type !=''">
            #{map_type},
        </if>
        #{_user.id}

        )
    </insert>
    <insert id="insertGridBuilding" parameterType="map">
        INSERT INTO jl_grid_building(
        id,
        name,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        create_time,
        create_by,
        <if test="map_type != null and map_type !=''">
            map_type,
        </if>
        garden_id,
        brand
        ) values (
        uuid(),
        #{name},
        #{grade},
        #{csc_mapjson},
        #{parent_ids},
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        #{_user.id},
        <if test="map_type != null and map_type !=''">
            #{map_type},
        </if>
        #{garden_id},
        #{brand}
        )
    </insert>
    <insert id="insertGridUnit" parameterType="map">
        INSERT INTO jl_grid_unit(
        id,
        name,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        create_time,
        create_by,
        garden_id,
        <if test="map_type != null and map_type !=''">
            map_type,
        </if>
        building_id
        ) values (
        uuid(),
        #{name},
        #{grade},
        #{csc_mapjson},
        #{parent_ids},
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        #{_user.id},
        #{garden_id},
        <if test="map_type != null and map_type !=''">
            #{map_type},
        </if>
        #{building_id}
        )
    </insert>
    <insert id="insertGridFloor" parameterType="map">
        INSERT INTO jl_grid_floor(
        id,
        name,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        create_time,
        create_by,
        garden_id,
        building_id,
        unit_id,
        <if test="map_type != null and map_type !=''">
            map_type,
        </if>
        sort
        ) values (
        #{uuid},
        #{name},
        #{grade},
        #{csc_mapjson},
        #{parent_ids},
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        #{_user.id},
        #{garden_id},
        #{building_id},
        #{unit_id},
        <if test="map_type != null and map_type !=''">
            #{map_type},
        </if>
        #{sort}
        )
    </insert>
    <insert id="insertGridDoorplate" parameterType="map">
        INSERT INTO jl_grid_doorplate(
        id,
        name,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        create_time,
        create_by,
        garden_id,
        building_id,
        floor_id,
        unit_id,
        <if test="map_type != null and map_type !=''">
            map_type,
        </if>
        sort
        ) values (
        uuid(),
        #{name},
        #{grade},
        #{csc_mapjson},
        #{parent_ids},
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        #{_user.id},
        #{garden_id},
        #{building_id},
        #{floor_id},
        #{unit_id},
        <if test="map_type != null and map_type !=''">
            #{map_type},
        </if>
        #{sort}
        )
    </insert>
    <update id="updateGridGarden" parameterType="map">
        UPDATE jl_grid_garden
        SET name = #{name},
        csc_mapjson = #{csc_mapjson}
        WHERE id = #{id}
    </update>
    <update id="updateGridBuilding" parameterType="map">
        UPDATE jl_grid_building
        SET name = #{name},
        csc_mapjson = #{csc_mapjson},
        brand = #{brand}
        WHERE id = #{id}
    </update>
    <update id="updateGridUnit" parameterType="map">
        UPDATE jl_grid_unit
        SET name = #{name}
        WHERE id = #{id}
    </update>
    <select id="findCommunityXJList" parameterType="map" resultType="map" expose="true" name="根据小区查询网格" desc="根据小区查询网格">
        SELECT
        id,
        id as `key`,
        name,
        name as title,
        name as label,
        grade,
        csc_mapjson,
        company_code,
        area_id,
        street_id,
        community_id,
        parent_ids,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        manager
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND community_id=#{id}
        <if test="name != null and name != ''">
            AND name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <include refid="workerDivideJurisdictionForGraden"/>
        order by name
    </select>
    <select id="getGridGardenById" parameterType="map" resultType="map" expose="true" name="查询网格" desc="查询网格">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND id=#{garden_id}
    </select>
    <select id="findGardenXJList" parameterType="map" resultType="map" expose="true" name="查询网格下级楼" desc="查询网格下级楼">
        SELECT
        id,
        name,
        name as title,
        brand,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        manager
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND garden_id=#{id}
        <if test="name != null and name != ''">
            AND name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <include refid="workerDivideJurisdictionForBuilding"/>
        order by create_time
    </select>
    <select id="findBuildingById" parameterType="map" resultType="map">
        SELECT
        id,
        name,
        name as title,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND id=#{building_id}
    </select>
    <select id="findBuildingXJList" parameterType="map" resultType="map" expose="true" name="查询楼下级" desc="查询楼下级">
        SELECT
        id,
        name,
        name as title,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        manager
        FROM
        jl_grid_unit
        WHERE
        del_flag = '0'
        AND building_id=#{id}
        <if test="name != null and name != ''">
            AND name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        order by create_time
    </select>
    <select id="findUnitById" parameterType="map" resultType="map">
        SELECT
        id,
        name,
        name as title,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_unit
        WHERE
        del_flag = '0'
        AND id=#{unit_id}
    </select>
    <select id="findUnitXJList" parameterType="map" resultType="map" expose="true" name="查询单元下级" desc="查询单元下级">
        SELECT
        id,
        name,
        name as title,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        sort
        FROM
        jl_grid_floor
        WHERE
        del_flag = '0'
        AND unit_id=#{id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        order by sort
    </select>
    <select id="findFloorXJList" parameterType="map" resultType="map" expose="true" name="查询层下级" desc="查询层下级">
        SELECT
        a.id,
        a.`name`,
        a.sort,
        a.grade,
        a.csc_mapjson,
        a.parent_ids,
        a.company_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.map_type,
        a.num,
        a.people_num,
        a.jz_num,
        a.ym_lxnum,
        IFNULL(a.ym_sjnum,0) as ym_sjnum,
        'true' as doorplateShow
        <!--( SELECT remarks FROM jl_grid_doorplate_special WHERE a.id = doorplate_id ) AS remarks,-->
        <!--( SELECT count(id) FROM v2_jl_grid_carmanage WHERE a.id = doorplate_id and del_flag='0') AS cars-->
        FROM
        jl_grid_doorplate a
        WHERE
        a.floor_id = #{floor_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        order by sort
    </select>
    <select id="findFloorById" parameterType="map" resultType="map">
        SELECT
        id,
        name,
        name as title,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        'true' as doorplateShow,
        sort
        FROM
        jl_grid_floor
        WHERE
        del_flag = '0'
        AND id=#{floor_id}
    </select>
    <update id="updateDoorplate" parameterType="map" expose="true" name="修改户信息" desc="修改户信息">
        UPDATE jl_grid_doorplate
        SET name=#{name},
        sort=#{sort}
        WHERE id=#{id}
    </update>
    <update id="deleteDoorplate" parameterType="map" expose="true" name="删除户信息" desc="删除户信息">
        UPDATE jl_grid_doorplate
        SET del_flag = '1'
        WHERE id=#{id}
    </update>
    <update id="deleteDoorplatePl" parameterType="map" expose="true" name="删除户信息批量" desc="删除户信息批量">
        UPDATE jl_grid_doorplate
        SET del_flag = '1'
        WHERE floor_id=#{id}
    </update>
    <update id="updateFloor" parameterType="map" expose="true" name="修改层信息" desc="修改层信息">
        UPDATE jl_grid_floor
        SET name=#{name},
        sort=#{sort}
        WHERE id=#{id}
    </update>
    <update id="deleteFloor" parameterType="map" expose="true" name="删除层信息" desc="删除层信息">
        UPDATE jl_grid_floor
        SET del_flag = '1'
        WHERE id=#{id}
    </update>

    <select id="findUserList" parameterType="page" resultType="map" expose="true" name="查询人员列表" desc="查询人员列表">
        select *
        from (
        SELECT
        a.id,
        a.user_name,
        CONCAT( LEFT ( cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char), 6 ), '********', RIGHT (
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char), 4 ) ) TMcardno,
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.company_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        IF
        ( ( SELECT focus_group FROM jl_sq_user b WHERE b.cardno = a.cardno ) != '', TRUE, FALSE ) AS focus_group
        FROM
        jl_grid_user a
        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.area_id=#{params._user.company_id}
        </if>
        <if test="params._user.company_grade == '4'.toString()">
            AND a.street_id=#{params._user.company_id}
        </if>
        <if test="params._user.company_grade == '5'.toString()">
            AND a.community_id=#{params._user.company_id}
        </if>
        <if test="params.grade == '2'.toString()">
            AND 1=1
        </if>
        <if test="params.grade == '3'.toString()">
            AND a.area_id=#{params.id}
        </if>
        <if test="params.grade == '4'.toString()">
            AND a.street_id=#{params.id}
        </if>
        <if test="params.grade == '5'.toString()">
            AND a.community_id=#{params.id}
        </if>
        <if test="params.grade == '6'.toString()">
            AND a.garden_id=#{params.id}
        </if>
        <if test="params.grade == '7'.toString()">
            AND a.building_id=#{params.id}
        </if>
        <if test="params.grade == '8'.toString()">
            AND a.unit_id=#{params.id}
        </if>
        <if test="params.unit_id != null and params.unit_id != ''">
            AND a.unit_id = #{params.unit_id}
        </if>
        <if test="params.user_name != null and params.user_name != ''">
            AND a.user_name like CONCAT('%', #{params.user_name},'%')
        </if>
        ) aa
    </select>
    <select id="findDoorplateUserList" parameterType="map" resultType="map" expose="true" name="根据户查询人" desc="根据户查询人">
        SELECT
        a.id,
        a.user_type,
        a.user_name,
        cast( AES_DECRYPT( FROM_BASE64 ( a.cardno ), 'HMzOraRAn6nWZmty10Lmt123' ) AS CHAR ) AS cardno,
        a.company_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.building_id,
        a.unit_id,
        a.floor_id,
        a.doorplate_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        IF
        ( ( SELECT focus_group FROM jl_sq_user b WHERE b.cardno = a.cardno ) != '', TRUE, FALSE ) AS focus_group,
        b.mobile,
        b.focus_group AS focus_group_detail

        FROM
        jl_grid_user a
        JOIN jl_sq_user b ON a.cardno = b.cardno
        WHERE
        a.del_flag = '0'
        AND a.doorplate_id = #{doorplate_id}
    </select>
    <!--
    <select id="getUserInfoByCardNo" parameterType="map" resultType="map" expose="true" name="查询用户详细信息" desc="查询用户详细信息">
        select
            a.id,
            a.user_name name,
            IF(LENGTH( a.user_name ) > 6,CONCAT( LEFT ( a.user_name, 1 ), '**' ),CONCAT( LEFT ( a.user_name, 1 ), '*' )) TMname,
            CONCAT( LEFT ( cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char), 6 ), '****', RIGHT ( cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char), 4 ) ) TMcardno,
            CONCAT( LEFT ( a.tel, 3 ), '****', RIGHT ( a.tel, 4 ) ) TMmobile,
            cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
            a.tel mobile,
            a.hj_address,
            a.now_address,
            a.political_outlook,
            a.remarks,
            a.juzhu_type,
            a.living_condition,
            a.sex,
            a.folk
            FROM
            jl_grid_user a
        WHERE
            a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </select>
     -->
    <select id="findGardenList" parameterType="map" resultType="map" expose="true" name="查询小区下拉" desc="查询小区下拉">
        SELECT
        *
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND community_code=#{_user.company_code}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <include refid="workerDivideJurisdictionForGraden"/>
    </select>
    <select id="findResidentialForIDList" parameterType="map" resultType="map" expose="true" name="查询小区下拉"
            desc="查询小区下拉">
        SELECT
        a.id,
        a.`name`,
        a.address,
        a.measure,
        a.grade,
        a.csc_mapjson,
        a.area_id,
        a.street_id,
        a.community_id,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.map_type,
        a.manager,
        a.binding,
        a.del_flag,
        a.parent_ids,
        a.company_code,
        a.szdwbb,
        a.szdzbbb,
        a.type
        FROM
        jl_grid_residential a
        WHERE
        a.community_id = #{_user.company_id}
    </select>
    <select id="findGardenForIDList" parameterType="map" resultType="map" expose="true" name="查询小区下拉" desc="查询小区下拉">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        residential_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND community_id = #{_user.company_id}
        <if test="residential_id != null and residential_id != ''">
            AND residential_id = #{residential_id}
        </if>
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <include refid="workerDivideJurisdictionForGraden"/>
    </select>
    <select id="findBuildingList" parameterType="map" resultType="map" expose="true" name="查询楼下拉" desc="查询楼下拉">
        SELECT
        *
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND garden_id=#{garden_id}
        <!--<choose>-->
        <!--<when test="map_type != null and name != ''">-->
        <!--AND map_type=#{map_type}-->
        <!--</when>-->
        <!--<otherwise>-->
        <!--AND map_type='Amap'-->
        <!--</otherwise>-->
        <!--</choose>-->
        ORDER BY
        create_date
    </select>
    <select id="findBuildingForIDList" parameterType="map" resultType="map" expose="true" name="查询楼下拉" desc="查询楼下拉">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND garden_id=#{garden_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        ORDER BY
        create_time
    </select>
    <select id="findUnitList" parameterType="map" resultType="map" expose="true" name="查询单元下拉" desc="查询单元下拉">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_unit
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND building_id=#{building_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        ORDER BY
        create_time
    </select>
    <select id="findUnitForIDList" parameterType="map" resultType="map" expose="true" name="查询单元下拉" desc="查询单元下拉">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_unit
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND building_id=#{building_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        ORDER BY
        create_time
    </select>
    <select id="findFloorList" parameterType="map" resultType="map" expose="true" name="查询层下拉" desc="查询层下拉">
        SELECT
        id,
        name,
        sort,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_floor
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND unit_id=#{unit_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        ORDER BY sort
    </select>
    <select id="findFloorForIDList" parameterType="map" resultType="map" expose="true" name="查询层下拉" desc="查询层下拉">
        SELECT
        id,
        name,
        sort,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_floor
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND unit_id=#{unit_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        ORDER BY sort
    </select>
    <select id="findDoorplateList" parameterType="map" resultType="map" expose="true" name="查询户下拉" desc="查询户下拉">
        SELECT
        id,
        `name`,
        sort,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_doorplate
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND floor_id=#{floor_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
    </select>
    <select id="findDoorplateForIDList" parameterType="map" resultType="map" expose="true" name="查询户下拉" desc="查询户下拉">
        SELECT
        id,
        `name`,
        sort,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_doorplate
        WHERE
        del_flag = '0'
        AND community_id=#{_user.company_id}
        AND floor_id=#{floor_id}
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
    </select>
    <select id="getGridUserInfo" parameterType="map" resultType="map" expose="true" name="查询网格用户信息" desc="查询网格用户信息">
        SELECT
        id,
        user_name,
        cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        doorplate_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_user
        WHERE
        del_flag = '0'
        AND cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
        AND doorplate_id = #{doorplate_id}
    </select>
    <select id="getDoorplateById" parameterType="map" resultType="map" expose="true" name="根据id查询户" desc="根据id查询户">
        SELECT
        id,
        `name`,
        sort,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        jl_grid_doorplate
        WHERE
        del_flag = '0'
        AND id = #{doorplate_id}
    </select>
    <update id="updateGridUserInfo" parameterType="map">
        UPDATE jl_grid_user
        SET del_flag = '1'
        WHERE id=#{id}
    </update>
    <insert id="creatGridUser" parameterType="map">
        INSERT INTO jl_grid_user(
        id,
        user_name,
        cardno,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        building_id,
        unit_id,
        floor_id,
        doorplate_id,
        create_time,
        create_by
        )VALUES(
        UUID(),
        #{name},
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        #{company_code},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{building_id},
        #{unit_id},
        #{floor_id},
        #{doorplate_id},
        now(),
        #{_user.company_code}
        )
    </insert>
    <select id="findAllGridList" parameterType="map" resultType="map" expose="true" name="查询全部网格" desc="查询全部网格">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND csc_mapjson IS NOT NULL
        <if test="grade == '3'.toString()">
            AND area_id=#{id}
        </if>
        <if test="grade == '4'.toString()">
            AND street_id=#{id}
        </if>
        <if test="grade == '5'.toString()">
            AND community_id=#{id}
        </if>
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <include refid="workerDivideJurisdictionForGraden"/>
        order by name
    </select>


    <select id="getCommunityByID" parameterType="map" resultType="map">
        SELECT
        *
        FROM
        sys_office
        WHERE
        id = #{community_id}
    </select>


    <select id="getAreaNum" parameterType="map" resultType="map">
        SELECT
        count( 1 ) as areaNum
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND
        parent_id=#{id}
        AND id not in (
        '100015',
        '100016',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
    </select>
    <select id="getStreetNum" parameterType="map" resultType="map">
        SELECT
        count( 1 ) as streetNum
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND id not in (
        '100015',
        '100016',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
        AND grade = '4'
        <choose>
            <when test="grade == '3'.toString()">
                AND parent_id=#{id}
            </when>
            <otherwise>
                AND parent_ids like CONCAT('%', #{id},'%')
            </otherwise>
        </choose>

    </select>
    <select id="getCommunityNum" parameterType="map" resultType="map">
        SELECT
        count( 1 ) as communityNum
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND id not in (
        '1ef0d524-976d-11eb-8fa5-0242ac110002',
        '100015',
        '100016',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
        AND grade = '5'
        AND `level` = '40'
        <choose>
            <when test="grade == '4'.toString()">
                AND parent_id=#{id}
            </when>
            <otherwise>
                AND parent_ids like CONCAT('%', #{id},'%')
            </otherwise>
        </choose>
    </select>
    <select id="getGardenNum" parameterType="map" resultType="map" expose="true" name="统计小区数量" desc="统计小区数量">
        SELECT
        count( 1 ) as gardennum
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
    </select>
    <select id="getBuildingNum" parameterType="map" resultType="map" expose="true" name="统计楼数量" desc="统计楼数量">
        SELECT
        count( 1 ) as buildingnum
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <if test="start_time != null and start_time != ''">
            AND create_time >= #{start_time}
        </if>
        <if test="end_time != null and end_time != ''">
            AND create_time &lt;= #{end_time}
        </if>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <if test="garden_id != null and garden_id != ''">
            AND garden_id = #{garden_id}
        </if>
    </select>
    <select id="getGardenNumNew" parameterType="map" resultType="map" name="统计小区数量" desc="统计小区数量">
        SELECT
        count( 1 ) as gardennum
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <include refid="workerDivideJurisdictionForGraden"/>
    </select>
    <select id="getBuildingNumNew" parameterType="map" resultType="map" name="统计楼数量" desc="统计楼数量">
        SELECT
        count( 1 ) as buildingnum
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <if test="start_time != null and start_time != ''">
            AND create_time >= #{start_time}
        </if>
        <if test="end_time != null and end_time != ''">
            AND create_time &lt;= #{end_time}
        </if>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <if test="garden_id != null and garden_id != ''">
            AND garden_id = #{garden_id}
        </if>
        <include refid="workerDivideJurisdictionForBuilding"/>
    </select>
    <select id="getDoorplateNum" parameterType="map" resultType="map" expose="true" name="统计户数量" desc="统计户数量">
        SELECT
        count( 1 ) as doorplatenum
        FROM
        jl_grid_doorplate
        WHERE
        del_flag = '0'
        <choose>
            <when test="map_type != null and name != ''">
                AND map_type=#{map_type}
            </when>
            <otherwise>
                AND map_type='Amap'
            </otherwise>
        </choose>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <if test="garden_id != null and garden_id != ''">
            AND garden_id = #{garden_id}
        </if>
        <if test="building_id != null and building_id != ''">
            AND building_id = #{building_id}
        </if>
    </select>
    <select id="getUserNum" parameterType="map" resultType="map" expose="true" name="统计人数量" desc="统计人数量">
        SELECT
        count( DISTINCT cardno ) as usernum
        FROM
        jl_grid_user
        WHERE
        del_flag = '0'
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <if test="garden_id != null and garden_id != ''">
            AND garden_id = #{garden_id}
        </if>
        <if test="building_id != null and building_id != ''">
            AND building_id = #{building_id}
        </if>
    </select>
    <select id="getGardenByCoordinates" parameterType="map" resultType="map" expose="true" name="根据左边查询网格"
            desc="根据左边查询网格">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND csc_mapjson=#{csc_mapjson}
    </select>
    <select id="getBuildingByCoordinates" parameterType="map" resultType="map" expose="true" name="根据坐标查询楼"
            desc="根据坐标查询楼">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND csc_mapjson=#{csc_mapjson}
    </select>

    <select id="getOfficeInfoByID" parameterType="map" resultType="map">
        select name from sys_office where id = #{id}
    </select>
    <select id="getPaintGridNum" parameterType="map" resultType="map" expose="true" name="统计画过网格的社区个数"
            desc="统计画过网格的社区个数">
        SELECT
        count( 1 ) num
        FROM
        sys_office
        WHERE
        `code` IN (
        SELECT company_code
        FROM jl_grid_garden
        WHERE del_flag = '0'
        AND map_type = 'Amap'
        <if test='grade=="3"'>
            AND area_id = #{id}
        </if>
        <if test='grade=="4"'>
            AND street_id = #{id}
        </if>
        <if test='grade=="5"'>
            AND community_id = #{id}
        </if>
        GROUP BY company_code
        )
        AND `level` = '40'
        AND id != '1ef0d524-976d-11eb-8fa5-0242ac110002'
    </select>
    <select id="getRuHuPeopleNum" parameterType="map" resultType="map" name="入户人员数量" desc="入户人员数量" expose="true">
        SELECT
        count( 1 ) ruHuNum
        FROM
        jl_grid_user
        WHERE
        del_flag = '0'
        AND is_kgh = '1'
        AND doorplate_id IS NOT NULL
        AND doorplate_id != ''
        <if test="_user.company_grade == '2'.toString()">
            AND 1=1
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND area_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND street_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND community_id = #{_user.company_id}
        </if>
        <if test="grade=='3'.toString()">
            AND area_id = #{id}
        </if>
        <if test="grade=='4'.toString()">
            AND street_id = #{id}
        </if>
        <if test="grade=='5'.toString()">
            AND community_id = #{id}
        </if>
        <if test="grade=='6'.toString()">
            AND garden_id = #{id}
        </if>
        <if test="grade=='7'.toString()">
            AND building_id = #{id}
        </if>
    </select>
    <select id="findGradenByAreaList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        area_id
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        area_id
    </select>
    <select id="findBuildingByAreaList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        area_id
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        area_id
    </select>
    <select id="findGradenByStreetList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        street_id
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        street_id
    </select>
    <select id="findBuildingByStreetList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        street_id
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        street_id
    </select>
    <select id="findGradenBycommunityList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        community_id
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        community_id
    </select>
    <select id="findBuildingBycommunityList" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num,
        community_id
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        GROUP BY
        community_id
    </select>
    <select id="getGridTJ" parameterType="map" resultType="map" name="政务服务统计" desc="政务服务统计" expose="true">
        SELECT
        ( SELECT count( 1 )
        FROM jl_grid_garden
        WHERE del_flag = '0'
        AND map_type = 'Amap'
        <if test="start_time != null and start_time != ''">
            AND create_time >= #{start_time}
        </if>
        <if test="end_time != null and end_time != ''">
            AND create_time &lt;= #{end_time}
        </if>
        ) AS gardennum,
        ( SELECT count( 1 )
        FROM jl_grid_building
        WHERE del_flag = '0'
        AND map_type = 'Amap'
        <if test="start_time != null and start_time != ''">
            AND create_time >= #{start_time}
        </if>
        <if test="end_time != null and end_time != ''">
            AND create_time &lt;= #{end_time}
        </if>
        ) AS buildingnum,
        (
        8094726
        ) AS peoplenum
    </select>
    <select id="getOfficeByCompanyCode" parameterType="map" resultType="map">
        SELECT
        id,
        parent_id,
        parent_ids,
        `name`,
        area_id,
        `code`,
        type,
        grade,
        csc_mapjson,
        csc_map_fid
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND code=#{company_code}
    </select>
    <select id="findzdrqByUnitList" parameterType="map" resultType="map" name="查询重点人群（根据单元）" desc="查询重点人群（根据单元）">
        SELECT
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.doorplate_id
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND a.doorplate_id IN (
        SELECT id
        FROM jl_grid_doorplate
        WHERE del_flag = '0'
        AND unit_id = #{id} )
        AND EXISTS (
        SELECT
        b.cardno
        FROM
        jl_sq_user b
        WHERE
        b.cardno = a.cardno
        AND b.focus_group != ''
        AND b.focus_group IS NOT NULL
        )
    </select>
    <select id="findzdrqByUnitNewList" parameterType="map" resultType="map">
        SELECT
        u.id,
        cast(AES_DECRYPT(FROM_BASE64(u.cardno),'*#id#') as char) as cardno,
        u.doorplate_id
        FROM
        jl_grid_user u
        WHERE
        u.del_flag = '0'
        AND u.doorplate_id IN (
        SELECT id
        FROM jl_grid_doorplate
        WHERE del_flag = '0'
        AND unit_id = #{id} )
        AND EXISTS (
        SELECT
        a.cardno
        FROM
        jl_grid_user_focusgroup a
        JOIN jl_grid_focusgroup_type b ON ( a.second_level_id = b.id AND b.label_type = '0' AND b.del_flag = '0' )
        WHERE
        u.cardno = a.cardno)
    </select>
    <select id="findzdrqByGardenList" parameterType="map" resultType="map" name="查询重点人群（根据小区）" desc="查询重点人群（根据小区）">
        SELECT
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.building_id
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND a.building_id IN (
        SELECT id
        FROM jl_grid_building
        WHERE del_flag = '0'
        AND garden_id = #{id} )
        AND EXISTS (
        SELECT
        b.cardno
        FROM
        jl_sq_user b
        WHERE
        b.cardno = a.cardno
        AND b.focus_group != ''
        AND b.focus_group IS NOT NULL
        )
    </select>
    <select id="findzdrqByGardenNewList" parameterType="map" resultType="map">
        SELECT
        u.id,
        cast(AES_DECRYPT(FROM_BASE64(u.cardno),'*#id#') as char) as cardno,
        u.building_id
        FROM
        jl_grid_user u
        WHERE
        u.del_flag = '0'
        AND u.building_id IN (
        SELECT id
        FROM jl_grid_building
        WHERE del_flag = '0'
        AND garden_id = #{id} )
        AND EXISTS (
        SELECT
        a.cardno
        FROM
        jl_grid_user_focusgroup a
        JOIN jl_grid_focusgroup_type b ON ( a.second_level_id = b.id AND b.label_type = '0' AND b.del_flag = '0' )
        WHERE
        u.cardno = a.cardno)
    </select>
    <select id="findBuildByCommunityList" parameterType="map" resultType="map" name="" desc="" expose="true">
        SELECT
        id,
        brand,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        manager
        FROM
        jl_grid_building
        WHERE
        community_id = #{id}
        AND del_flag = '0'
        <include refid="workerDivideJurisdictionForBuilding"/>
    </select>
    <select id="findzdrqByCommunityList" parameterType="map" resultType="map" name="查询重点人群（根据小区）" desc="查询重点人群（根据小区）">
        SELECT
        cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
        a.building_id
        FROM
        jl_grid_user a
        WHERE
        a.del_flag = '0'
        AND a.building_id IN (
        SELECT id
        FROM jl_grid_building
        WHERE del_flag = '0'
        AND community_id = #{id} )
        AND EXISTS (
        SELECT
        b.cardno
        FROM
        jl_sq_user b
        WHERE
        b.cardno = a.cardno
        AND b.focus_group != ''
        AND b.focus_group IS NOT NULL
        )
    </select>
    <select id="findzdrqByCommunityNewList" parameterType="map" resultType="map">
        SELECT
        u.id,
        cast(AES_DECRYPT(FROM_BASE64(u.cardno),'*#id#') as char) as cardno,
        u.building_id
        FROM
        jl_grid_user u
        WHERE
        u.del_flag = '0'
        AND u.building_id IN (
        SELECT id
        FROM jl_grid_building
        WHERE del_flag = '0'
        AND community_id = #{id} )
        AND EXISTS (
        SELECT
        a.cardno
        FROM
        jl_grid_user_focusgroup a
        JOIN jl_grid_focusgroup_type b ON ( a.second_level_id = b.id AND b.label_type = '0' AND b.del_flag = '0' )
        WHERE
        u.cardno = a.cardno)
    </select>
    <select id="getUserNumByLm" parameterType="map" resultType="map">
        SELECT
        count( 1 ) usernum
        FROM
        jl_sq_user
    </select>
    <select id="findGridGardenList" parameterType="map" resultType="map" expose="true">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        manager
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        AND company_code = #{_user.company_code}
        <if test="id != null and id != ''">
            AND id != #{id}
        </if>
    </select>
    <select id="findBuildingByCscMapjsonList" parameterType="map" resultType="map">
        SELECT
        id,
        brand,
        `name`,
        grade,
        csc_mapjson,
        parent_ids,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        manager
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND csc_mapjson = #{csc_mapjson}
        <if test="id != null and id != ''">
            AND id != #{id}
        </if>
    </select>
    <select id="getKGHNum" parameterType="map" resultType="map" name="查询空挂户" desc="查询空挂户" expose="true">
        SELECT
        COUNT( 1 ) kghNum
        FROM
        jl_grid_user
        WHERE
        del_flag = '0'
        AND is_kgh = '0'
        <if test="_user.company_grade == '2'.toString()">
            AND 1=1
        </if>
        <if test="_user.company_grade == '3'.toString()">
            AND area_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            AND street_id = #{_user.company_id}
        </if>
        <if test="_user.company_grade == '5'.toString()">
            AND community_id = #{_user.company_id}
        </if>
        <if test="grade=='3'.toString()">
            AND area_id = #{id}
        </if>
        <if test="grade=='4'.toString()">
            AND street_id = #{id}
        </if>
        <if test="grade=='5'.toString()">
            AND community_id = #{id}
        </if>
    </select>
    <select id="findBuidingRuhuList" parameterType="map" resultType="map" expose="true">
        SELECT
        a.id,
        a.brand,
        a.`name`,
        a.grade,
        a.csc_mapjson,
        a.parent_ids,
        a.company_code,
        a.area_id,
        a.street_id,
        a.community_id,
        a.garden_id,
        a.del_flag,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.map_type,
        a.manager,
        (
        SELECT
        count( 1 )
        FROM
        jl_grid_user
        WHERE
        del_flag = '0'
        AND is_kgh = '1'
        AND doorplate_id IS NOT NULL
        AND doorplate_id != ''
        AND building_id = a.id
        ) as ruhuNum
        FROM
        jl_grid_building a
        WHERE
        a.del_flag = '0'
        AND garden_id = #{id}
    </select>
    <select id="getCunNum" parameterType="map" resultType="map">
        SELECT
        count( 1 ) as num
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND id not in (
        '1ef0d524-976d-11eb-8fa5-0242ac110002',
        '100015',
        '100016',
        '1fafe819-50ab-11eb-905f-0242ac110002',
        '335fdf4e-3d18-11eb-adf5-0242ac110002',
        'ace15cac-b8e3-11ea-88c8-0242ac11000f',
        '4dbd77cb-b110-11ea-9adb-0242ac110002',
        '7ef5b6e2-a480-11ea-9adb-0242ac110002',
        'cc19af0f-a3c6-11ea-9adb-0242ac110002')
        AND name not like '%残联%'
        AND system REGEXP 'grid'
        AND `level` = '45'
        <choose>
            <when test="grade == '4'.toString()">
                AND parent_id=#{id}
            </when>
            <otherwise>
                AND parent_ids like CONCAT('%', #{id},'%')
            </otherwise>
        </choose>
    </select>
    <select id="getCunPaintGridNum" parameterType="map" resultType="map">
        SELECT
        count( 1 ) num
        FROM
        sys_office
        WHERE
        `code` IN (
        SELECT company_code
        FROM jl_grid_garden
        WHERE del_flag = '0'
        AND map_type = 'Amap'
        <if test='grade=="3"'>
            AND area_id = #{id}
        </if>
        <if test='grade=="4"'>
            AND street_id = #{id}
        </if>
        <if test='grade=="5"'>
            AND community_id = #{id}
        </if>
        GROUP BY company_code
        )
        AND `level` = '45'
        AND system REGEXP 'grid'
        AND id != '1ef0d524-976d-11eb-8fa5-0242ac110002'
    </select>
    <select id="findResidentialNewList" parameterType="map" resultType="map">
        SELECT
        id,
        `name`,
        address,
        measure,
        grade,
        csc_mapjson,
        area_id,
        street_id,
        community_id,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        manager,
        binding,
        del_flag,
        parent_ids,
        company_code,
        szdwbb,
        szdzbbb,
        type
        FROM
        jl_grid_residential
        WHERE
        community_id = #{id}
    </select>
    <select id="findGardenNewList" parameterType="map" resultType="map">
        SELECT
        id,
        `name`,
        grade,
        csc_mapjson,
        company_code,
        area_id,
        street_id,
        community_id,
        residential_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        manager,
        manager_tel,
        binding,
        parent_ids,
        net_id
        FROM
        jl_grid_garden
        WHERE
        residential_id = #{id}
    </select>
</mapper>