<?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.gridResidential" name="小区功能">
    <insert id="createGridResidential" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(1) from jl_grid_residential where id = #{id}
        </selectKey>
        <if test="count == 0">
        INSERT INTO jl_grid_residential(
            id,
            `name`,
            address,
            measure,
            grade,
            csc_mapjson,
            city_id,
            area_id,
            street_id,
            community_id,
            garden_id,
            create_by,
            create_time,
            szdwbb,
            szdzbbb,
            type,
            csc_mapjson_ht
        )VALUES(
            #{id},
            #{name},
            #{address},
            #{measure},
            #{grade},
            #{csc_mapjson},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            #{garden_id},
            #{_user.id},
            now(),
            #{szdwbb},
            #{szdzbbb},
            #{type},
            #{csc_mapjson_ht}
        )
        </if>
        <if test="count > 0">
            UPDATE jl_grid_residential
            SET
                name = #{name},
                address = #{address},
                measure = #{measure},
                <if test="csc_mapjson != null and csc_mapjson != ''">
                    csc_mapjson = #{csc_mapjson},
                </if>
                szdwbb = #{szdwbb},
                szdzbbb = #{szdzbbb},
                type = #{type},
                csc_mapjson_ht = #{csc_mapjson_ht}
            WHERE
                id = #{id}
        </if>
    </insert>
    <select id="findResidentialList" parameterType="map" resultType="map" expose="true">
        SELECT
            id,
            `name`,
            address,
            measure,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            area_id,
            street_id,
            community_id,
            create_by,
            date_format( create_time, '%Y-%m-%d %T' ) AS create_time,
            update_by,
            update_time,
            map_type,
            manager,
            binding,
            del_flag,
            parent_ids,
            company_code,
            szdwbb,
            szdzbbb,
            type
        FROM
            jl_grid_residential
        WHERE
            1 = 1
        <choose>
            <when test="_user.company_grade == '3'.toString()">
                and area_id =  #{_user.company_id}
            </when>
            <otherwise>
                and community_id = #{_user.company_id}
            </otherwise>
        </choose>
        <if test="id != null and id != ''">
            AND id != #{id}
        </if>
    </select>
    <select id="getResidentialGarden" parameterType="map" resultType="map">
        SELECT
            id,
            examine_code,
            examine_type,
            community_id,
            garden_id,
            residential_id,
            create_time,
            create_by,
            company_code,
            change_num_json,
            begin_time,
            end_time,
            random,
            update_time
        FROM
            jl_grid_residential_garden
        WHERE
            company_code = #{_user.company_code}
            AND examine_type = '0'
            AND garden_id = #{garden_id}
    </select>
    <insert id="createGridResidentialTask" parameterType="map">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(1) from jl_grid_residential_garden where examine_code = #{examine_code}
        </selectKey>
        <if test="count == 0">
        INSERT INTO jl_grid_residential_garden(
            id,
            examine_code,
            community_id,
            garden_id,
            residential_id,
            create_time,
            create_by,
            company_code
        )VALUES(
            UUID(),
            #{examine_code},
            #{_user.company_id},
            #{garden_id},
            #{residential_id},
            NOW(),
            #{_user.loginName},
            #{_user.company_code}
        )
        </if>
        <if test="count > 0">
            UPDATE jl_grid_residential_garden
            SET residential_id = #{residential_id},
                update_time = now()
            WHERE examine_code = #{examine_code}
        </if>
    </insert>
    <select id="findGridResidentialList" parameterType="page" resultType="map" name="查询社区小区绑定网格列表" desc="查询社区小区绑定网格列表"  expose="true">
            SELECT *
            FROM (
                SELECT
                    a.id,
                    a.examine_code,
                    a.examine_type,
                    a.community_id,
                    ( SELECT `name` FROM jl_grid_garden WHERE id = a.garden_id ) AS garden_name,
                    ( SELECT `name` FROM jl_grid_residential WHERE id = a.residential_id ) AS residential_name,
                    a.garden_id,
                    a.residential_id,
                    date_format( a.create_time, '%Y-%m-%d %T' ) AS create_time,
                    a.create_by,
                    a.company_code,
                    a.change_num_json,
                    date_format( a.begin_time, '%Y-%m-%d %T' ) AS begin_time,
                    date_format( a.end_time, '%Y-%m-%d %T' ) AS end_time,
                    a.random,
                    a.update_time
                FROM
                    jl_grid_residential_garden a
                WHERE
                 a.company_code = #{params._user.company_code}
            ) a
    </select>
    <select id="getCommunityResidential" parameterType="map" resultType="map">
    SELECT
        id,
        examine_code,
        examine_type,
        community_id,
        garden_id,
        residential_id,
        create_time,
        create_by,
        company_code,
        change_num_json,
        begin_time,
        end_time,
        random,
        update_time
    FROM
        jl_grid_residential_garden
    WHERE
        examine_type = '0'
    ORDER BY
        create_time
    LIMIT 1
    </select>
    <update id="updateCommunityResidentialType" parameterType="map">
        UPDATE jl_grid_residential_garden
        <set>
            <if test="change_type == '1'.toString()">
                examine_type = '1',
                begin_time = now(),
            </if>
            <if test="change_type == '2'.toString()">
                examine_type = '2',
                random = UUID(),
                change_num_json = #{change_num_json},
                end_time = now(),
            </if>
            <if test="change_type == '3'.toString()">
                examine_type = '3',
            </if>
        </set>
        WHERE examine_code = #{examine_code}
    </update>
    <update id="updateResidentialForGarden" parameterType="map">
        UPDATE jl_grid_garden
        SET residential_id = #{residential_id}
        WHERE id = #{garden_id}
    </update>
    <update id="updateResidentialForBuilding" parameterType="map">
        UPDATE jl_grid_building
        SET residential_id = #{residential_id}
        WHERE garden_id = #{garden_id}
    </update>
    <update id="updateResidentialForUnit" parameterType="map">
        UPDATE jl_grid_unit
        SET residential_id = #{residential_id}
        WHERE garden_id = #{garden_id}
    </update>
    <update id="updateResidentialForFloor" parameterType="map">
        UPDATE jl_grid_floor
        SET residential_id = #{residential_id}
        WHERE garden_id = #{garden_id}
    </update>
    <update id="updateResidentialForDoorplate" parameterType="map">
        UPDATE jl_grid_doorplate
        SET residential_id = #{residential_id}
        WHERE garden_id = #{garden_id}
    </update>
    <update id="updateResidentialForUser" parameterType="map">
        UPDATE jl_grid_user
        SET residential_id = #{residential_id}
        WHERE garden_id = #{garden_id}
    </update>
    <select id="getResidentialByID" parameterType="map" resultType="map">
        SELECT
            id,
            `name`,
            address,
            measure,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            area_id,
            street_id,
            community_id,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type,
            manager,
            binding,
            del_flag,
            parent_ids,
            company_code
        FROM
            jl_grid_residential
        WHERE
            id = #{residential_id}
    </select>
    <select id="getGardenByID" parameterType="map" resultType="map">
        SELECT
            id,
            `name`,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            parent_ids,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            residential_id,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type,
            manager,
            binding
        FROM
            jl_grid_garden
        WHERE
            id = #{garden_id}
    </select>
    <select id="getResidentialByResidentialID" parameterType="map" resultType="map">
        SELECT
            id,

            `name`,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            id garden_id
        FROM
            jl_grid_garden
        WHERE
            id = #{garden_id}
    </select>
    <select id="findGardenByResidentialList" parameterType="map" resultType="map">
        SELECT
            id,
            `name`,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            parent_ids,
            company_code,
            area_id,
            street_id,
            community_id,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type,
            manager,
            binding,
            grid_type
        FROM
            jl_grid_garden
        WHERE
            residential_id = #{residential_id}
        <if test="id != null and id != ''">
            AND id != #{id}
        </if>
    </select>
    <insert id="insertGridGardenNew" parameterType="map">
        INSERT INTO jl_grid_garden(
            id,
            grid_type,
            name,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            city_id,
            area_id,
            street_id,
            community_id,
            create_time,
            create_by,
            residential_id,
            company_code
        ) values (
            #{id},
            #{grid_type},
            #{name},
            #{grade},
            #{csc_mapjson},
            #{csc_mapjson_ht},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            now(),
            #{_user.id},
            #{residential_id},
            #{_user.company_code}
        )
    </insert>
    <update id="updateGridGardenNew" parameterType="map">
        UPDATE jl_grid_garden
        SET name = #{name},
            grid_type = #{grid_type},
            <if test="csc_mapjson != null and csc_mapjson != ''">
                csc_mapjson = #{csc_mapjson},
            </if>
            csc_mapjson_ht = #{csc_mapjson_ht}
        WHERE id = #{id}
    </update>
    <insert id="insertGridBuildingNew" parameterType="map">
        INSERT INTO jl_grid_building(
            id,
            name,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            garden_id,
            create_time,
            create_by,
            brand,
            residential_id
        ) values (
            #{id},
            #{name},
            #{grade},
            #{csc_mapjson},
            #{csc_mapjson_ht},
            #{_user.company_code},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            #{garden_id},
            now(),
            #{_user.id},
            #{brand},
            #{residential_id}
        )
    </insert>
    <update id="updateGridBuildingNew" parameterType="map">
        UPDATE jl_grid_building
            SET name = #{name},
            csc_mapjson = #{csc_mapjson},
            csc_mapjson_ht = #{csc_mapjson_ht},
            brand = #{brand}
        WHERE id = #{id}
    </update>
    <select id="getBuildingByID" parameterType="map" resultType="map">
        SELECT
            id,
            brand,
            `name`,
            grade,
            csc_mapjson,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            residential_id,
            garden_id,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type,
            manager,
            parent_ids
        FROM
            jl_grid_building
        WHERE
            id = #{building_id}
    </select>
    <update id="updateGridUnitNew" parameterType="map">
        UPDATE jl_grid_unit
        SET name = #{name}
        WHERE id = #{id}
    </update>
    <insert id="insertGridUnitNew" parameterType="map">
        INSERT INTO jl_grid_unit(
        id,
        name,
        grade,
        company_code,
        city_id,
        area_id,
        street_id,
        community_id,
        create_time,
        create_by,
        residential_id,
        garden_id,
        building_id
        ) values (
        #{id},
        #{name},
        #{grade},
        #{_user.company_code},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        now(),
        #{_user.id},
        #{residential_id},
        #{garden_id},
        #{building_id}
        )
    </insert>
    <select id="findUnitById" parameterType="map" resultType="map">
        SELECT
            id,
            name,
            name as title,
            grade,
            csc_mapjson,
            parent_ids,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            residential_id,
            garden_id,
            building_id,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        FROM
            jl_grid_unit
        WHERE
            id=#{unit_id}
    </select>
    <select id="findFloorById" parameterType="map" resultType="map">
        SELECT
            id,
            `name`,
            sort,
            grade,
            csc_mapjson,
            parent_ids,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            residential_id,
            garden_id,
            building_id,
            unit_id,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type
        FROM
            jl_grid_floor
        WHERE
            id=#{floor_id}
    </select>
    <insert id="insertGridFloorNew" parameterType="map">
        INSERT INTO jl_grid_floor(
            id,
            name,
            grade,
            csc_mapjson,
            parent_ids,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            create_time,
            create_by,
            residential_id,
            garden_id,
            building_id,
            unit_id,
            sort
        ) values (
            #{id},
            #{name},
            #{grade},
            #{csc_mapjson},
            '',
            #{_user.company_code},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            now(),
            #{_user.id},
            #{residential_id},
            #{garden_id},
            #{building_id},
            #{unit_id},
            #{sort}
        )
    </insert>
    <insert id="insertGridDoorplateNew" parameterType="map">
        INSERT INTO jl_grid_doorplate(
            id,
            name,
            grade,
            csc_mapjson,
            parent_ids,
            company_code,
            city_id,
            area_id,
            street_id,
            community_id,
            create_time,
            create_by,
            residential_id,
            garden_id,
            building_id,
            floor_id,
            unit_id,
            sort
        ) values (
            #{id},
            #{name},
            #{grade},
            #{csc_mapjson},
            '',
            #{_user.company_code},
            #{city_id},
            #{area_id},
            #{street_id},
            #{community_id},
            now(),
            #{_user.id},
            #{residential_id},
            #{garden_id},
            #{building_id},
            #{floor_id},
            #{unit_id},
            #{sort}
        )
    </insert>
    <update id="updateCommunityCompanyScope" parameterType="map">
        UPDATE sys_office
        SET company_scope = #{company_scope}
        WHERE id = #{_user.company_id}
    </update>
    <update id="updateCommunityCscmapjson" parameterType="map">
        UPDATE sys_office set
        csc_mapjson_ht = #{csc_mapjson_ht}
        WHERE id = #{id}
    </update>
    <select id="findGardenByCommunityList" parameterType="map" resultType="map">
        SELECT
           *
        FROM
            jl_grid_garden
        WHERE
            1 = 1
        <if test="community_id != null and community_id != ''">
            and community_id = #{community_id}
        </if>
        <choose>
            <when test="_user.company_grade == '3'.toString()">
                and area_id =  #{_user.company_id}
            </when>
            <otherwise>
                and community_id = #{_user.company_id}
            </otherwise>
        </choose>
        <if test="id != null and id != ''">
            AND id != #{id}
        </if>
    </select>
    <select id="getCommunityAreaCode" parameterType="map" resultType="map">
        SELECT
            id,
            city,
            city_code,
            city_area_code,
            street,
            street_code,
            street_area_code,
            community,
            community_code,
            community_area_code,
            title,
            area_code,
            company_code,
            create_time,
            `level`,
            pid,
            path,
            del_flag,
            ids
        FROM
            jl_region
        WHERE
            del_flag = '0'
        AND
            level = '4'
        AND
            company_code = #{_user.company_code}
    </select>
    <select id="updateResidentialQHCode" parameterType="map" resultType="map">
        update jl_grid_residential
        set qh_code = #{result_code}
        where id = #{id}
    </select>
    <select id="updateGardenQHCodePL" parameterType="map" resultType="map">
        update jl_grid_garden
        set qh_code = replace(qh_code,#{old_qh_code},#{result_code})
        where residential_id = #{id}
    </select>
    <select id="getCommunityResidentialCode" parameterType="map" resultType="map">
        SELECT
            id,
            `name`,
            address,
            measure,
            grade,
            csc_mapjson,
            csc_mapjson_ht,
            area_id,
            street_id,
            community_id,
            create_by,
            create_time,
            update_by,
            update_time,
            map_type,
            manager,
            binding,
            del_flag,
            parent_ids,
            company_code,
            qh_code
        FROM
            jl_grid_residential
        WHERE
            id = #{residential_id}
        AND
            qh_code IS NOT NULL
    </select>
    <select id="updateGardenQHCode" parameterType="map" resultType="map">
        update jl_grid_garden
        set qh_code = #{result_code}
        where id = #{id}
    </select>
    <update id="updateFLoorSort" parameterType="map">
        UPDATE jl_grid_floor f,
        (
            SELECT
                *
            FROM
                (
                SELECT
                    @ROW := @ROW + 1 AS floor_num,
                    f_tmp.id AS id,
                    f_tmp.sort
                FROM
                    jl_grid_floor f_tmp,
                    ( SELECT @ROW := 0 ) tmp_row
                WHERE
                    unit_id = #{unit_id}
                ORDER BY
                    sort ASC
                ) tmp
        ) tmps
        SET f.sort = tmps.floor_num
        WHERE
        f.id = tmps.id
    </update>

    <select id="getResidentialIdByResidentialNameAndGardenId" parameterType="map" resultType="string">
        select
           id
        from
           jl_grid_residential
        where
           name = #{residentialName}
        and
           garden_id = #{garDenId}
        and
           del_flag = 0
        limit 1
    </select>
    <insert id="saveResidentialinfo" parameterType="map">
        insert into
              jl_grid_residential
        (
          id,
          name,
          grade,
          city_id,
          area_id,
          street_id,
          community_id,
          garden_id,
          create_by,
          create_time,
          data_source
        )
        values
        (
          #{id},
          #{name},
          #{grade},
          #{cityId},
          #{areaId},
          #{streetId},
          #{communityId},
          #{gardenId},
          #{createBy},
          #{createTime},
          #{dataSource}
        )
    </insert>
</mapper>