<?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.gridLevel" 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 == 'LM20061111'.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 == 'LM20061111'.toString()">
            AND FIND_IN_SET( id, #{_user.building_id} )
        </if>
    </sql>
    <select id="findOfficeInfoList" 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 id not in (
        '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 grade=#{grade}
        AND system REGEXP 'grid'
        <if test="parent_id != null and parent_id != ''">
            AND parent_id=#{parent_id}
        </if>
        <if test="parent_ids != null and parent_ids != ''">
            AND parent_ids like concat('%',#{parent_ids},'%')
        </if>
    </select>
    <select id="getSuperior" 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
        id=#{id}
    </select>
    <select id="findAllGardenList" parameterType="map" resultType="map">
        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
        map_type='Amap'
        <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>
        <include refid="workerDivideJurisdictionForGraden"/>
        order by name
    </select>
    <select id="findAllBuildingList" parameterType="map" resultType="map" 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
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND
        map_type='Amap'
        <if test="_user != null and _user.company_grade == '3'.toString()">
            AND area_id=#{_user.company_id}
        </if>
        <if test="_user != null and _user.company_grade == '4'.toString()">
            AND street_id=#{_user.company_id}
        </if>
        <if test="_user != null and _user.company_grade == '5'.toString()">
            AND community_id=#{_user.company_id}
        </if>
        <if test="company_code != null and company_code != ''">
            and company_code=#{company_code}
        </if>
        <include refid="workerDivideJurisdictionForBuilding"/>
    </select>
    <select id="findMerchantList" parameterType="map" resultType="map" expose="true" name="查询商户" desc="查询商户">
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        DATE_FORMAT(a.place_time_begin,'%H:%i:%s') place_time_begin,
        DATE_FORMAT(a.place_time_end,'%H:%i:%s') place_time_end,
        a.place_tyshxydm,
        DATE_FORMAT(a.place_cl_time,'%Y-%m-%d %H:%i:%s') place_cl_time,
        a.place_remarks,
        a.place_img,
        a.place_area,
        a.new_format,
        a.csc_mapjson_ht,
        a.zhfwss_jsfs,
        a.zhfwss_cqdw,
        a.zhfwss_bgmj,
        a.zhfwss_bgmjzb,
        a.zhfwss_fwmj,
        a.zhfwss_fwmjzb,
        a.zhfwss_yyfwgn,
        a.zhfwss_gplx,
        a.zhfwss_gpmc,
        a.zhfwss_tsqksm,
        (select icon from jl_grid_place where type = 'P' and id = a.merchant_type) as icon
        FROM
        jl_grid_merchant a
        WHERE
        a.garden_id = #{id}
        <if test="name != null and name != ''">
            AND a.name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
        ORDER BY create_time DESC
    </select>
    <select id="findMerchantForCommunityList" parameterType="map" resultType="map" expose="true" name="查询商户"
            desc="查询商户">
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        DATE_FORMAT(a.place_time_begin,'%H:%i:%s') place_time_begin,
        DATE_FORMAT(a.place_time_end,'%H:%i:%s') place_time_end,
        a.place_tyshxydm,
        DATE_FORMAT(a.place_cl_time,'%Y-%m-%d %H:%i:%s') place_cl_time,
        a.place_remarks,
        a.place_img,
        a.place_area,
        a.new_format,
        a.csc_mapjson_ht,
        (select icon from jl_grid_place where type = 'P' and id = a.merchant_type) as icon,
        (select name from jl_grid_garden where id = a.garden_id) as garden_name,
        (select name from jl_grid_residential where id = (select residential_id from jl_grid_garden where id =
        a.garden_id) ) as residential_name
        FROM
        jl_grid_merchant a
        WHERE
        <!--			AND a.map_type = 'Amap' -->
        1 = 1
        <choose>
            <when test="select_type != null and select_type != ''">
                AND a.merchant_type LIKE CONCAT(#{select_type,jdbcType=VARCHAR},'%')
            </when>
            <otherwise>
                AND a.merchant_type LIKE 'P%'
            </otherwise>
        </choose>
        AND a.community_id = #{community_id}
        <if test="name != null and name != ''">
            AND a.name like CONCAT('%', #{name,jdbcType=VARCHAR},'%')
        </if>
    </select>
    <select id="findMerchantPageSList" parameterType="page" resultType="map" name="查询商户" desc="查询商户">
        SELECT
        agm.id,
        agm.merchant_type,
        agp.`name` merchant_type_name,
        agm.`name`,
        agm.grade,
        agm.csc_mapjson,
        agm.parent_ids,
        agm.company_code,
        agm.area_id,
        agm.street_id,
        agm.community_id,
        agm.garden_id,
        agm.del_flag,
        agm.create_by,
        agm.create_time,
        agm.update_by,
        agm.update_time,
        agm.map_type,
        agm.place_fzr,
        agm.place_fzr_phone,
        agm.place_address,
        DATE_FORMAT( agm.place_time_begin, '%H:%i:%s' ) place_time_begin,
        DATE_FORMAT( agm.place_time_end, '%H:%i:%s' ) place_time_end,
        agm.place_tyshxydm,
        DATE_FORMAT( agm.place_cl_time, '%Y-%m-%d %H:%i:%s' ) place_cl_time,
        agm.place_remarks,
        agm.place_img,
        agm.place_area
        FROM
        jl_grid_merchant agm
        left join jl_grid_place agp on agm.merchant_type = agp.id
        WHERE
        agm.del_flag = '0'
        AND agm.map_type = 'Amap'
        AND agp.type = 'p'
        <if test="params._user != null and params._user.company_grade == '3'.toString()">
            AND agm.area_id=#{params._user.company_id}
        </if>
        <if test="params._user != null and params._user.company_grade == '4'.toString()">
            AND agm.street_id=#{params._user.company_id}
        </if>
        <if test="params._user != null and params._user.company_grade == '5'.toString()">
            AND agm.community_id=#{params._user.company_id}
        </if>
        <if test="params.area_id != null and params.area_id != '' ">
            AND agm.area_id = #{params.area_id}
        </if>
        <if test="params.street_id != null and params.street_id != '' ">
            AND agm.street_id = #{params.street_id}
        </if>
        <if test="params.community_id != null and params.community_id != '' ">
            AND agm.community_id = #{params.community_id}
        </if>
        <if test="params.company_code != null and params.company_code != ''">
            and agm.company_code=#{params.company_code}
        </if>
        <if test="params.name != null and params.name != ''">
            AND agm.name like CONCAT('%', #{params.name,jdbcType=VARCHAR},'%')
        </if>
        <if test="params.merchant_type != null and params.merchant_type != ''">
            AND agm.merchant_type = #{params.merchant_type,jdbcType=VARCHAR}
        </if>
        <if test="params.area_type != null and params.area_type != ''">
            <choose>
                <when test="params.area_type == '4'.toString()">
                    AND agm.place_area >= #{params.place_area_start,jdbcType=VARCHAR} and
                    #{params.place_area_end,jdbcType=VARCHAR} >= agm.place_area
                </when>
                <when test="params.area_type == '3'.toString()">
                    AND agm.place_area = #{params.place_area_one,jdbcType=VARCHAR}
                </when>
                <when test="params.area_type == '2'.toString()">
                    AND #{params.place_area_one,jdbcType=VARCHAR} > agm.place_area
                </when>
                <when test="params.area_type == '1'.toString()">
                    AND agm.place_area > #{params.place_area_one,jdbcType=VARCHAR}
                </when>
            </choose>
        </if>
    </select>
    <select id="findMerchantPageList" parameterType="page" resultType="map" expose="true" name="查询商户" desc="查询商户">
        <!--		select *-->
        <!--	from (-->
        <!--	SELECT-->
        <!--	agm.id,-->
        <!--	agm.merchant_type,-->
        <!--	agp.`name` merchant_type_name,-->
        <!--	agm.`name`,-->
        <!--	agm.grade,-->
        <!--	agm.csc_mapjson,-->
        <!--	agm.parent_ids,-->
        <!--	agm.company_code,-->
        <!--	agm.area_id,-->
        <!--	agm.street_id,-->
        <!--	agm.community_id,-->
        <!--	agm.garden_id,-->
        <!--	agm.del_flag,-->
        <!--	agm.create_by,-->
        <!--	agm.create_time,-->
        <!--	agm.update_by,-->
        <!--	agm.update_time,-->
        <!--	agm.map_type,-->
        <!--	agm.place_fzr,-->
        <!--	agm.place_fzr_phone,-->
        <!--	agm.place_address,-->
        <!--	DATE_FORMAT( agm.place_time_begin, '%H:%i:%s' ) place_time_begin,-->
        <!--	DATE_FORMAT( agm.place_time_end, '%H:%i:%s' ) place_time_end,-->
        <!--	agm.place_tyshxydm,-->
        <!--	DATE_FORMAT( agm.place_cl_time, '%Y-%m-%d %H:%i:%s' ) place_cl_time,-->
        <!--	agm.place_remarks,-->
        <!--	agm.place_img,-->
        <!--	agm.place_area-->
        <!--	FROM-->
        <!--	jl_grid_merchant agm-->
        <!--	left join jl_grid_place agp on agm.merchant_type = agp.id-->
        <!--	WHERE-->
        <!--	agm.del_flag = '0'-->
        <!--	AND agm.map_type = 'Amap'-->
        <!--	AND agp.type = 'p'-->
        <!--	<if test="params._user != null and params._user.company_grade == '3'.toString()">-->
        <!--		AND agm.area_id=#{params._user.company_id}-->
        <!--	</if>-->
        <!--	<if test="params._user != null and params._user.company_grade == '4'.toString()">-->
        <!--		AND agm.street_id=#{params._user.company_id}-->
        <!--	</if>-->
        <!--	<if test="params._user != null and params._user.company_grade == '5'.toString()">-->
        <!--		AND agm.community_id=#{params._user.company_id}-->
        <!--	</if>-->
        <!--	<if test="params.area_id != null and params.area_id != '' ">-->
        <!--		AND agm.area_id = #{params.area_id}-->
        <!--	</if>-->
        <!--	<if test="params.street_id != null and params.street_id != '' ">-->
        <!--		AND agm.street_id = #{params.street_id}-->
        <!--	</if>-->
        <!--	<if test="params.community_id != null and params.community_id != '' ">-->
        <!--		AND agm.community_id = #{params.community_id}-->
        <!--	</if>-->
        <!--	<if test="params.company_code != null and params.company_code != ''">-->
        <!--		and	agm.company_code=#{params.company_code}-->
        <!--	</if>-->
        <!--	<if test="params.name != null and params.name != ''">-->
        <!--		AND agm.name like CONCAT('%', #{params.name,jdbcType=VARCHAR},'%')-->
        <!--	</if>-->
        <!--	<if test="params.merchant_type != null and params.merchant_type != ''">-->
        <!--		AND agm.merchant_type = #{params.merchant_type,jdbcType=VARCHAR}-->
        <!--	</if>-->
        <!--	<if test="params.area_type != null and params.area_type != ''">-->
        <!--		<choose>-->
        <!--			<when test="params.area_type == '4'.toString()">-->
        <!--				AND agm.place_area >= #{params.place_area_start,jdbcType=VARCHAR} and #{params.place_area_end,jdbcType=VARCHAR} >= agm.place_area-->
        <!--			</when>-->
        <!--			<when test="params.area_type == '3'.toString()">-->
        <!--				AND agm.place_area = #{params.place_area_one,jdbcType=VARCHAR}-->
        <!--			</when>-->
        <!--			<when test="params.area_type == '2'.toString()">-->
        <!--				AND #{params.place_area_one,jdbcType=VARCHAR} > agm.place_area-->
        <!--			</when>-->
        <!--			<when test="params.area_type == '1'.toString()">-->
        <!--				AND agm.place_area > #{params.place_area_one,jdbcType=VARCHAR}-->
        <!--			</when>-->
        <!--		</choose>-->
        <!--	</if>-->
        <!--	) aaa-->
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        <!--		DATE_FORMAT( a.place_time_begin, '%H:%i:%s' ) place_time_begin,-->
        <!--		DATE_FORMAT( a.place_time_end, '%H:%i:%s' ) place_time_end,-->
        a.place_tyshxydm,
        <!--		DATE_FORMAT( a.place_cl_time, '%Y-%m-%d' ) place_cl_time,-->
        a.place_remarks,
        a.place_img,
        a.place_area,
        a.shipin_url,
        a.placeindex,
        a.new_format,
        a.csc_mapjson_ht
        FROM
        jl_grid_merchant a
        <!--	LEFT JOIN jl_grid_place b ON a.merchant_type = b.id-->
        <where>
            <if test="params._user != null and params._user.company_grade == '3'.toString()">
                AND a.area_id=#{params._user.company_id}
            </if>
            <if test="params._user != null and params._user.company_grade == '4'.toString()">
                AND a.street_id=#{params._user.company_id}
            </if>
            <if test="params._user != null and params._user.company_grade == '5'.toString()">
                AND a.community_id=#{params._user.company_id}
            </if>
            <if test="params.area_id != null and params.area_id != '' ">
                AND a.area_id = #{params.area_id}
            </if>
            <if test="params.street_id != null and params.street_id != '' ">
                AND a.street_id = #{params.street_id}
            </if>
            <if test="params.community_id != null and params.community_id != '' ">
                AND a.community_id = #{params.community_id}
            </if>
            <if test="params.company_code != null and params.company_code != ''">
                and a.company_code=#{params.company_code}
            </if>
            <if test="params.name != null and params.name != ''">
                AND a.name like CONCAT('%', #{params.name,jdbcType=VARCHAR},'%')
            </if>
            <if test="params.merchant_type != null and params.merchant_type != ''">
                AND a.merchant_type = #{params.merchant_type,jdbcType=VARCHAR}
            </if>
        </where>
    </select>
    <update id="updatePlace" parameterType="map" expose="true" name="修改信息" desc="修改信息">
        UPDATE jl_grid_merchant
        SET name = #{name},
        merchant_type = #{merchant_type},
        csc_mapjson = #{csc_mapjson},
        csc_mapjson_ht = #{csc_mapjson_ht},
        place_fzr = #{place_fzr},
        place_fzr_phone = #{place_fzr_phone},
        place_address = #{place_address},
        <if test="place_time_begin != null and place_time_begin != ''">
            place_time_begin = #{place_time_begin},
        </if>
        <if test="place_time_end != null and place_time_end != ''">
            place_time_end = #{place_time_end},
        </if>
        place_tyshxydm = #{place_tyshxydm},
        <if test="place_cl_time != null and place_cl_time != ''">
            place_cl_time = #{place_cl_time},
        </if>
        place_remarks = #{place_remarks},
        place_img = #{place_img},
        place_area = #{place_area},
        <if test="merchant_type == 'Z0001'.toString()">
            zhfwss_jsfs = #{zhfwss_jsfs},
            zhfwss_cqdw = #{zhfwss_cqdw},
            zhfwss_bgmj = #{zhfwss_bgmj},
            zhfwss_bgmjzb = #{zhfwss_bgmjzb},
            zhfwss_fwmj = #{zhfwss_fwmj},
            zhfwss_fwmjzb = #{zhfwss_fwmjzb},
            zhfwss_yyfwgn = #{zhfwss_yyfwgn},
            zhfwss_gplx = #{zhfwss_gplx},
            zhfwss_gpmc = #{zhfwss_gpmc},
            zhfwss_tsqksm = #{zhfwss_tsqksm},
        </if>
        new_format = #{new_format}
        WHERE
        id = #{id}
    </update>
    <delete id="deletePlace" parameterType="map" expose="true" name="删除信息" desc="删除信息">
        <!-- UPDATE  jl_grid_merchant
        SET del_flag = '1'
        WHERE
            id = #{id} -->
        DELETE FROM jl_grid_merchant WHERE id = #{id}
    </delete>
    <select id="getGridGardenById" parameterType="map" resultType="map">
        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=#{id}
    </select>
    <insert id="insertPlace" parameterType="map">
        INSERT INTO jl_grid_merchant(
        id,
        merchant_type,
        name,
        csc_mapjson,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        create_by,
        create_time,
        place_fzr,
        place_fzr_phone,
        place_address,
        <if test="place_time_begin != null and place_time_begin != ''">
            place_time_begin,
        </if>
        <if test="place_time_end != null and place_time_end != ''">
            place_time_end,
        </if>
        place_tyshxydm,
        <if test="place_cl_time != null and place_cl_time != ''">
            place_cl_time,
        </if>
        place_remarks,
        place_img,
        place_area,
        new_format,
        <if test="merchant_type == 'Z0001'.toString()">
            zhfwss_jsfs,
            zhfwss_cqdw,
            zhfwss_bgmj,
            zhfwss_bgmjzb,
            zhfwss_fwmj,
            zhfwss_fwmjzb,
            zhfwss_yyfwgn,
            zhfwss_gplx,
            zhfwss_gpmc,
            zhfwss_tsqksm,
        </if>
        csc_mapjson_ht
        )VALUES(
        UUID(),
        #{merchant_type},
        #{name},
        #{csc_mapjson},
        #{company_code},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{company_code},
        now(),
        #{place_fzr},
        #{place_fzr_phone},
        #{place_address},
        <if test="place_time_begin != null and place_time_begin != ''">
            #{place_time_begin},
        </if>
        <if test="place_time_end != null and place_time_end != ''">
            #{place_time_end},
        </if>
        #{place_tyshxydm},
        <if test="place_cl_time != null and place_cl_time != ''">
            #{place_cl_time},
        </if>
        #{place_remarks},
        #{place_img},
        #{place_area},
        #{new_format},
        <if test="merchant_type == 'Z0001'.toString()">
            #{zhfwss_jsfs},
            #{zhfwss_cqdw},
            #{zhfwss_bgmj},
            #{zhfwss_bgmjzb},
            #{zhfwss_fwmj},
            #{zhfwss_fwmjzb},
            #{zhfwss_yyfwgn},
            #{zhfwss_gplx},
            #{zhfwss_gpmc},
            #{zhfwss_tsqksm},
        </if>
        #{csc_mapjson_ht}
        )
    </insert>
    <select id="findSocietyList" parameterType="map" resultType="map" expose="true" name="查询社会组织列表" desc="查询社会组织列表">
        SELECT
        a.id,
        a.name,
        a.fr_name,
        cast(AES_DECRYPT(FROM_BASE64(a.fr_cardno),'*#id#') as char) as fr_cardno,
        a.fr_phone,
        a.tyshxydm,
        a.fzjg,
        a.csc_mapjson,
        a.csc_mapjson_ht,
        a.company_code,
        a.city_id,
        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.address,
        a.remarks,
        a.shzz_img,
        a.shzz_area,
        a.societyindex,
        (select icon from jl_grid_place where type = 'S' and id = 'S0001') as icon
        FROM
        jl_grid_society a
        WHERE
        a.del_flag = '0'
        AND a.map_type = 'Amap'
        AND a.garden_id = #{id}
        <if test="name != null and name !=''">
            AND a.name = #{name}
        </if>
        order by societyindex
    </select>

    <insert id="insertSociety" parameterType="map">
        insert into jl_grid_society (
        id,
        name,
        fr_name,
        fr_cardno,
        fr_phone,
        tyshxydm,
        fzjg,
        csc_mapjson,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        create_by,
        create_time,
        address,
        remarks,
        shzz_img,
        shzz_area,
        csc_mapjson_ht,
        societyindex
        ) values(
        uuid(),
        #{name},
        #{fr_name},
        TO_BASE64(AES_ENCRYPT(#{fr_cardno},'*#id#')),
        #{fr_phone},
        #{tyshxydm},
        #{fzjg},
        #{csc_mapjson},
        #{company_code},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{company_code},
        now(),
        #{address},
        #{remarks},
        #{shzz_img},
        #{shzz_area},
        #{csc_mapjson_ht},
        #{societyindex}
        )
    </insert>
    <update id="updateSociety" parameterType="map">
        UPDATE jl_grid_society
        SET
        name = #{name},
        fr_name = #{fr_name},
        fr_cardno =TO_BASE64(AES_ENCRYPT(#{fr_cardno},'*#id#')),
        fr_phone = #{fr_phone},
        tyshxydm = #{tyshxydm},
        fzjg = #{fzjg},
        csc_mapjson = #{csc_mapjson},
        csc_mapjson_ht = #{csc_mapjson_ht},
        address = #{address},
        remarks = #{remarks},
        shzz_img = #{shzz_img},
        shzz_area = #{shzz_area},
        societyindex = #{societyindex}
        WHERE
        id = #{id}
    </update>
    <update id="deleteSociety" parameterType="map" expose="true" name="删除社会组织" desc="删除社会组织">
        <!-- UPDATE jl_grid_society
        SET
            del_flag = '1'
        WHERE
            id = #{id} -->
        DELETE FROM jl_grid_society WHERE id = #{id}
    </update>
    <insert id="insertFr" parameterType="map" name="创建法人信息" desc="创建法人信息" expose="true">
        INSERT INTO jl_grid_fr (
        id,
        fr_name,
        fr_cardno,
        fzjg,
        qymc,
        qyzch,
        clrq,
        zczb,
        jyzt,
        company_code,
        create_by,
        create_time
        )VALUES(
        uuid(),
        #{fr_name},
        TO_BASE64(AES_ENCRYPT(#{fr_cardno},'*#id#')),
        #{fzjg},
        #{qymc},
        #{qyzch},
        #{clrq},
        #{zczb},
        #{jyzt},
        #{_user.company_code},
        #{_user.company_code},
        now()
        )
    </insert>
    <insert id="insertSw" parameterType="map" name="导入死亡信息" desc="导入死亡信息" expose="true">
        insert jl_grid_sw(
        id,
        cardno,
        sw_time,
        company_code,
        create_by,
        create_time
        )values(
        uuid(),
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        #{sw_time},
        #{_user.company_code},
        #{_user.company_code},
        now()
        )
    </insert>
    <select id="findSubstanceList" parameterType="map" resultType="map" name="查询物件" desc="查询物件" expose="true">
        SELECT
        id,
        `name`,
        remark,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        wj_code,
        wj_fzr,
        wj_fzr_phone,
        csc_mapjson,
        csc_mapjson_ht,
        wj_img,
        wj_address,
        (select icon from jl_grid_place where type = 'W' and id = 'W0001') as icon
        FROM
        jl_grid_substance
        WHERE
        del_flag = '0'
        AND map_type='Amap'
        AND garden_id = #{id}
        <if test="name != null and name !=''">
            AND `name` LIKE CONCAT( '%', #{name}, '%' )
        </if>
        order by create_time desc
    </select>
    <update id="deleteSubstance" parameterType="map" name="删除物件" desc="删除物件" expose="true">
        update jl_grid_substance
        set del_flag='1'
        where id=#{id}
    </update>
    <update id="updateSubstance" parameterType="map" name="修改物件" desc="修改物件" expose="true">
        update jl_grid_substance
        set name=#{name},
        remark=#{remark},
        wj_code = #{wj_code},
        wj_fzr = #{wj_fzr},
        wj_fzr_phone = #{wj_fzr_phone},
        csc_mapjson = #{csc_mapjson},
        csc_mapjson_ht = #{csc_mapjson_ht},
        wj_img = #{wj_img},
        wj_address = #{wj_address}
        where id=#{id}
    </update>
    <insert id="insertSubstance" parameterType="map" name="新增物件" desc="新增物件" expose="true">
        insert into jl_grid_substance(
        id,
        name,
        remark,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        create_by,
        create_time,
        wj_code,
        wj_fzr,
        wj_fzr_phone,
        csc_mapjson,
        wj_img,
        wj_address,
        csc_mapjson_ht,
        substanceindex
        ) values(
        uuid(),
        #{name},
        #{remark},
        #{_user.company_code},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{_user.company_code},
        now(),
        #{wj_code},
        #{wj_fzr},
        #{wj_fzr_phone},
        #{csc_mapjson},
        #{wj_img},
        #{wj_address},
        #{csc_mapjson_ht},
        #{substanceindex}
        )
    </insert>
    <select id="findGradenManagerList" parameterType="map" resultType="map">
        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,
        manager_tel
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        <if test="id != null and id != ''">
            AND residential_id = #{id}
        </if>
        <if test="select_id != null and select_id != ''">
            AND id= #{select_id}
        </if>
        order by name
    </select>
    <select id="findBuildingManagerList" 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,
        manager_tel
        FROM
        jl_grid_building
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        <if test="id != null and id != ''">
            AND garden_id=#{id}
        </if>
        <if test="select_id != null and select_id != ''">
            AND id= #{select_id}
        </if>
    </select>
    <select id="findUnitManagerList" parameterType="map" resultType="map">
        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,
        map_type,
        manager,
        manager_tel
        FROM
        jl_grid_unit
        WHERE
        del_flag = '0'
        AND map_type = 'Amap'
        <if test="id != null and id != ''">
            AND building_id=#{id}
        </if>
        <if test="select_id != null and select_id != ''">
            AND id= #{select_id}
        </if>
    </select>
    <update id="updateGradenManager" parameterType="map">
        update jl_grid_garden
        set
        manager=#{manager},
        manager_tel = #{manager_tel}
        where id=#{id}
    </update>
    <update id="updateBuildingManager" parameterType="map">
        update jl_grid_building
        set
        manager=#{manager},
        manager_tel = #{manager_tel}
        where id=#{id}
    </update>
    <update id="updateUnitManager" parameterType="map">
        update jl_grid_unit
        set
        manager=#{manager},
        manager_tel = #{manager_tel}
        where id=#{id}
    </update>
    <select id="findXQList" parameterType="page" resultType="map" expose="true" name="查询社区列表" desc="查询社区列表">
        SELECT
        id,
        residence_name,
        brand,
        type,
        area,
        area_id,
        create_by,
        create_time,
        update_by,
        update_time,
        del_flag
        FROM
        jl_grid_residence_area
        WHERE
        del_flag = '0'
        <if test="params.type != null and params.type !=''">
            AND type = #{params.type}
        </if>
        <if test="params.residence_name != null and params.residence_name !=''">
            AND residence_name LIKE CONCAT( '%', #{params.residence_name}, '%' )
        </if>
    </select>
    <update id="updateBinding" parameterType="map">
        update jl_grid_garden
        set binding = #{newbinding}
        where id = #{id}
    </update>
    <select id="getGardenByID" parameterType="map" resultType="map">
        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,
        binding
        FROM
        jl_grid_garden
        WHERE
        del_flag = '0'
        AND id = #{id}
    </select>
    <select id="findbdxqList" parameterType="map" resultType="map">
        SELECT
        id,
        residence_name,
        brand,
        type,
        area,
        area_id,
        create_by,
        create_time,
        update_by,
        update_time,
        del_flag
        FROM
        jl_grid_residence_area
        WHERE
        del_flag = '0'
        and id in
        <foreach collection="bindingList" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <insert id="savexqInfo" parameterType="map" expose="true" name="小区信息操作" desc="小区信息操作">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT count(1) FROM jl_grid_residence_area WHERE id = #{id, jdbcType=VARCHAR}
        </selectKey>
        <if test="count > 0">
            update jl_grid_residence_area
            set
            residence_name = #{residence_name},
            brand = #{brand},
            type = #{type},
            area = #{area},
            update_by = #{_user.loginName},
            update_time = now()
            where id = #{id}
        </if>
        <if test="count == 0">
            insert into jl_grid_residence_area(
            id,
            residence_name,
            brand,
            type,
            area,
            create_by,
            create_time
            )values(
            uuid(),
            #{residence_name},
            #{brand},
            #{type},
            #{area},
            #{_user.loginName},
            now()
            )
        </if>
    </insert>
    <select id="findxiaoquList" parameterType="map" resultType="map">
        SELECT
        id,
        residence_name,
        brand,
        type,
        area,
        area_id,
        create_by,
        create_time,
        update_by,
        update_time,
        del_flag
        FROM
        jl_grid_residence_area
        WHERE
        del_flag = '0'
        AND residence_name = #{residence_name}
        AND area = #{area}
    </select>
    <select id="getPlaceByCscmapjson" parameterType="map" resultType="map" expose="true" name="查询场所" desc="查询场所">
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        DATE_FORMAT(a.place_time_begin,'%H:%i:%s') place_time_begin,
        DATE_FORMAT(a.place_time_end,'%H:%i:%s') place_time_end,
        DATE_FORMAT(a.place_cl_time,'%Y-%m-%d %H:%i:%s') place_cl_time,
        a.place_tyshxydm,
        a.place_remarks,
        a.place_img,
        a.place_area,
        a.shipin_url,
        (select name from jl_grid_garden where id = a.garden_id) as 'garden_name',
        (select name from sys_office where id = a.area_id) as 'area_name',
        (select name from sys_office where id = a.street_id) as 'street_name',
        (select name from sys_office where id = a.community_id) as 'community_name'
        FROM
        jl_grid_merchant a
        WHERE
        a.csc_mapjson = #{csc_mapjson}
    </select>
    <select id="getSocietyByCscmapjson" parameterType="map" resultType="map" expose="true" name="查询社会组织" desc="查询社会组织">
        SELECT
        a.id,
        a.name,
        a.fr_name,
        a.fr_cardno,
        CONCAT( LEFT ( cast(AES_DECRYPT(FROM_BASE64(a.fr_cardno),'*#id#') as char), 6 ), '********', RIGHT (
        cast(AES_DECRYPT(FROM_BASE64(a.fr_cardno),'*#id#') as char), 4 ) ) TMcardno,
        a.fr_phone,
        a.tyshxydm,
        a.fzjg,
        a.csc_mapjson,
        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.address,
        a.remarks,
        a.shzz_img,
        a.shzz_area,
        a.societyindex,
        (select name from jl_grid_garden where id = a.garden_id) as 'garden_name',
        (select name from sys_office where id = a.area_id) as 'area_name',
        (select name from sys_office where id = a.street_id) as 'street_name',
        (select name from sys_office where id = a.community_id) as 'community_name'
        FROM
        jl_grid_society a
        WHERE
        a.csc_mapjson = #{csc_mapjson}
    </select>
    <select id="getSubstanceByCscmapjson" parameterType="map" resultType="map" expose="true" name="查询物件" desc="查询物件">
        SELECT
        a.id,
        a.`name`,
        a.remark,
        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.wj_code,
        a.wj_fzr,
        a.wj_fzr_phone,
        a.csc_mapjson,
        a.wj_img,
        a.wj_address,
        a.shipin_url,
        (select name from jl_grid_garden where id = a.garden_id) as 'garden_name',
        (select name from sys_office where id = a.area_id) as 'area_name',
        (select name from sys_office where id = a.street_id) as 'street_name',
        (select name from sys_office where id = a.community_id) as 'community_name'
        FROM
        jl_grid_substance a
        WHERE
        a.csc_mapjson = #{csc_mapjson}
    </select>
    <select id="findGridZYList" parameterType="map" resultType="map" expose="true" name="查询物件社会组织场所" desc="查询物件社会组织场所">
        SELECT
        id,
        type,
        `name`,
        icon,
        create_by,
        create_time,
        del_flag,
        pid
        FROM
        jl_grid_place
        WHERE
        1 = 1
        <if test="type != null and type !=''">
            and type = #{type}
        </if>
        <if test="pid != null and pid !=''">
            and pid = #{pid}
        </if>
    </select>
    <select id="findMerchantByMapList" parameterType="map" resultType="map" expose="true" name="地图查询商户使用"
            desc="地图查询商户使用">
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        DATE_FORMAT(a.place_time_begin,'%H:%i:%s') place_time_begin,
        DATE_FORMAT(a.place_time_end,'%H:%i:%s') place_time_end,
        a.place_tyshxydm,
        DATE_FORMAT(a.place_cl_time,'%Y-%m-%d %H:%i:%s') place_cl_time,
        a.place_remarks,
        a.place_img,
        a.place_area,
        (select icon from jl_grid_place where type = 'P' and id = a.merchant_type) as icon
        FROM
        jl_grid_merchant a
        WHERE
        a.del_flag = '0'
        AND a.map_type = 'Amap'
        <if test="grade == '3'.toString()">
            AND a.area_id = #{id}
        </if>
        <if test="grade == '4'.toString()">
            AND a.street_id = #{id}
        </if>
        <if test="grade == '5'.toString()">
            AND a.community_id = #{id}
        </if>
        <if test="grade == '6'.toString()">
            AND a.garden_id = #{id}
        </if>
        <if test="merchant_type != null and merchant_type !=''">
            AND a.merchant_type = #{merchant_type}
        </if>
        <if test="pid != null and pid !=''">
            AND a.merchant_type in (SELECT id FROM jl_grid_place WHERE pid = #{pid})
        </if>
    </select>
    <select id="findMerchantByMapNewList" parameterType="map" resultType="map" expose="true" name="地图查询商户使用"
            desc="地图查询商户使用">
        SELECT
        a.id,
        a.merchant_type,
        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.place_fzr,
        a.place_fzr_phone,
        a.place_address,
        a.place_time_begin,
        a.place_time_end,
        a.place_tyshxydm,
        a.place_cl_time,
        a.place_remarks,
        a.place_img,
        a.place_area,
        a.shipin_url,
        a.placeindex,
        a.new_format,
        a.csc_mapjson_ht,
        (select icon from jl_grid_place where type = 'P' and id = a.merchant_type) as icon
        FROM
        jl_grid_merchant a
        WHERE
        a.del_flag = '0'
        AND a.map_type = 'Amap'
        <if test="grade == '3'.toString()">
            AND a.area_id = #{id}
        </if>
        <if test="grade == '4'.toString()">
            AND a.street_id = #{id}
        </if>
        <if test="grade == '5'.toString()">
            AND a.community_id = #{id}
        </if>
        <if test="grade == '5.5'.toString()">
            AND a.garden_id in (select id from jl_grid_garden where residential = #{id})
        </if>
        <if test="grade == '6'.toString()">
            AND a.garden_id = #{id}
        </if>
        <if test="merchant_type != null and merchant_type !=''">
            AND a.merchant_type = #{merchant_type}
        </if>
        <if test="merchant_type_list != null and merchant_type_list.size() > 0">
            AND a.merchant_type in
            <foreach collection="merchant_type_list" open="(" close=")" separator="," item="item" index="index">
                #{merchant_type_list[${index}]}
            </foreach>
        </if>
        <if test="pid != null and pid !=''">
            AND a.merchant_type in (SELECT id FROM jl_grid_place WHERE pid = #{pid})
        </if>
    </select>
    <select id="findSocietyByMapList" parameterType="map" resultType="map" expose="true" name="地图查询社会组织列表"
            desc="地图查询社会组织列表">
        SELECT
        a.id,
        a.name,
        a.fr_name,
        cast(AES_DECRYPT(FROM_BASE64(a.fr_cardno),'*#id#') as char) as fr_cardno,
        a.fr_phone,
        a.tyshxydm,
        a.fzjg,
        a.csc_mapjson,
        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.address,
        a.remarks,
        a.shzz_img,
        a.shzz_area,
        a.csc_mapjson_ht,
        (select icon from jl_grid_place where type = 'S' and id = 'S0001') as icon
        FROM
        jl_grid_society a
        WHERE
        a.del_flag = '0'
        AND a.map_type = 'Amap'
        <if test="grade == '3'.toString()">
            AND a.area_id = #{id}
        </if>
        <if test="grade == '4'.toString()">
            AND a.street_id = #{id}
        </if>
        <if test="grade == '5'.toString()">
            AND a.community_id = #{id}
        </if>
        <if test="grade == '6'.toString()">
            AND a.garden_id = #{id}
        </if>
    </select>
    <select id="findSubstanceByMapList" parameterType="map" resultType="map" name="地图查询物件" desc="地图查询物件" expose="true">
        SELECT
        id,
        `name`,
        remark,
        company_code,
        area_id,
        street_id,
        community_id,
        garden_id,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        map_type,
        wj_code,
        wj_fzr,
        wj_fzr_phone,
        csc_mapjson,
        wj_img,
        wj_address,
        csc_mapjson_ht,
        (select icon from jl_grid_place where type = 'W' and id = 'W0001') as icon
        FROM
        jl_grid_substance
        WHERE
        del_flag = '0'
        AND map_type='Amap'
        <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>
    </select>
    <select id="getCompanyDetailByCode" parameterType="map" resultType="map" name="查询社区详细信息（社区简介，社区图片）"
            desc="查询社区详细信息（社区简介，社区图片）" expose="true">
        SELECT
        *
        FROM
        jl_grid_community
        WHERE
        community_id = #{_user.company_id}
    </select>
    <select id="getCompanyDetailByID" parameterType="map" resultType="map" name="查询社区详细信息（社区简介，社区图片）"
            desc="查询社区详细信息（社区简介，社区图片）" expose="true">
        SELECT
        id,
        detail,
        area,
        label_img,
        company_code,
        community_id,
        community_mobile,
        address,
        sqsj,
        sqsjdh
        FROM
        jl_grid_community
        WHERE
        community_id = #{community_id}
    </select>
    <insert id="saveCompanyDetail" parameterType="map" expose="true" name="社区详细信息（社区简介，社区图片）" desc="社区详细信息（社区简介，社区图片）">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT count(1) FROM jl_grid_community WHERE id = #{id}
        </selectKey>
        <if test="count == 0">
            insert into jl_grid_community(
            id,
            detail,
            area,
            label_img,
            community_code,
            community_id,
            community_mobile,
            address
            )values(
            uuid(),
            #{detail},
            #{area},
            #{label_img},
            #{_user.company_code},
            #{_user.company_id},
            #{community_mobile},
            #{address}
            )
        </if>
        <if test="count > 0">
            UPDATE jl_grid_community
            SET detail = #{detail},
            area = #{area},
            label_img = #{label_img},
            community_mobile = #{community_mobile},
            address = #{address}
            WHERE id = #{id}
        </if>
    </insert>
    <select id="getCodeByID" parameterType="map" resultType="map" name="根据ID查询code" desc="根据ID查询code" expose="true">
        SELECT
        `code`
        FROM
        sys_office
        WHERE
        id = #{community_id}
    </select>
    <select id="getDoorplateByID" parameterType="map" resultType="map">
        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,
        map_type,
        num,
        people_num,
        jz_num
        FROM
        jl_grid_doorplate
        WHERE
        id = #{doorplate_id}
    </select>
    <select id="getPeopleNumByDoorplate" parameterType="map" resultType="map">
        SELECT
        COUNT( 1 ) AS num
        FROM
        jl_grid_user
        WHERE
        doorplate_id = #{doorplate_id}
    </select>
    <insert id="saveDoorplateRemarks" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT count(1) FROM jl_grid_doorplate_special WHERE doorplate_id = #{doorplate_id, jdbcType=VARCHAR}
        </selectKey>
        <if test="count > 0">
            UPDATE jl_grid_doorplate_special
            SET
            remarks = #{remarks}
            WHERE doorplate_id = #{doorplate_id}
        </if>
        <if test="count == 0">
            INSERT INTO jl_grid_doorplate_special(
            id,
            company_code,
            area_id,
            street_id,
            community_id,
            garden_id,
            building_id,
            unit_id,
            floor_id,
            doorplate_id,
            remarks
            ) VALUES(
            uuid(),
            #{company_code},
            #{area_id},
            #{street_id},
            #{community_id},
            #{garden_id},
            #{building_id},
            #{unit_id},
            #{floor_id},
            #{doorplate_id},
            #{remarks}
            )
        </if>
    </insert>
    <select id="findCommunityMessageList" parameterType="page" resultType="map" expose="true" nologin="true">
        SELECT *
        FROM (
        SELECT
        a.id,
        a.detail,
        a.area,
        a.label_img,
        a.company_code,
        a.community_mobile,
        c.parent_id AS area_id,
        ( SELECT `name` FROM sys_office WHERE id = c.parent_id ) AS area_name,
        b.parent_id AS street_id,
        ( SELECT `name` FROM sys_office WHERE id = b.parent_id ) AS street_name,
        a.community_id,
        ( SELECT `name` FROM sys_office WHERE id = a.community_id ) AS community_name
        FROM
        jl_grid_community a
        LEFT JOIN sys_office b ON a.community_id = b.id
        LEFT JOIN sys_office c ON b.parent_id = c.id
        WHERE
        community_mobile IS NOT NULL
        <if test="params.area_id != null and params.area_id !=''">
            AND c.parent_id = #{params.area_id}
        </if>
        <if test="params.street_id != null and params.street_id !=''">
            AND b.parent_id = #{params.street_id}
        </if>
        <if test="params.community_id != null and params.community_id !=''">
            AND a.community_id = #{params.community_id}
        </if>
        ) aa
    </select>

    <select id="findAllSubstanceList" parameterType="page" resultType="map" expose="true">
        SELECT
            jgs.name,
            jgs.city_id,
            jgs.area_id,
            jgs.street_id,
            jgs.community_id,
            jgs.garden_id,
            so1.name cityName,
            so2.name areaName,
            so3.name streetName,
            so4.name communityName,
            so5.name gardenName
        FROM
            jl_grid_substance jgs
        LEFT JOIN sys_office so1 ON jgs.city_id = so1.id
        LEFT JOIN sys_office so2 ON jgs.area_id = so2.id
        LEFT JOIN sys_office so3 ON jgs.street_id = so3.id
        LEFT JOIN sys_office so4 ON jgs.community_id = so4.id
        LEFT JOIN jl_grid_garden so5 ON jgs.garden_id = so5.id
        <where>
           	jgs.del_flag = '0'
	        <if test="params.city_id != null and params.city_id != ''">
	            AND jgs.city_id = #{params.city_id}
	        </if>
	        <if test="params.area_id != null and params.area_id != ''">
	            AND jgs.area_id = #{params.area_id}
	        </if>
	        <if test="params.street_id != null and params.street_id != ''">
	            AND jgs.street_id = #{params.street_id}
	        </if>
	        <if test="params.community_id != null and params.community_id != ''">
	            AND jgs.community_id = #{params.community_id}
	        </if>
        	<choose>
            	<when test="params._user!=null and params._user.company_grade == '3'.toString()">
            		and jgs.city_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '4'.toString()">
            		and jgs.area_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '5'.toString()">
            		and jgs.street_id = #{params._user.company_id}
            	</when>
            	<when test="params._user!=null and params._user.company_grade == '6'.toString()">
            		and jgs.community_id = #{params._user.company_id}
            	</when>
            </choose>
        </where>
		order by so1.id,so2.id,so3.id,so4.id,so5.id,jgs.name asc
    </select>
</mapper>