<?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.deleteGrid" name="删除网格功能">
	<select id="findGardenList" parameterType="map" resultType="map">
		SELECT
			id,
			`name`,
			grade,
			csc_mapjson,
			parent_ids,
			company_code,
			area_id,
			street_id,
			community_id,
			city_id,
			del_flag,
			create_by,
			create_time,
			update_by,
			update_time,
			map_type,
			manager,
			binding
		FROM
			jl_grid_garden
		WHERE
			city_id = #{city_id}
	</select>
	<delete id="deleteResidential" parameterType="map">
		DELETE
		FROM jl_grid_residential
		WHERE id  = #{city_id}
	</delete>
<select id="findBuildingList" parameterType="map" resultType="map">
	SELECT
		id,
		brand,
		`name`,
		grade,
		csc_mapjson,
		parent_ids,
		company_code,
		city_id,
		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
		garden_id = #{garden_id}
</select>
<delete id="deleteGarden" parameterType="map">
	DELETE 
	FROM
		jl_grid_garden 
	WHERE
		id =  #{garden_id}
</delete>
<delete id="deletePeopleByBuilding" parameterType="map">
	DELETE 
	FROM
		jl_grid_user 
	WHERE
		building_id = #{building_id}
</delete>
<delete id="deletePeopleByUnit" parameterType="map">
	DELETE 
	FROM
		jl_grid_user 
	WHERE
		unit_id = #{unit_id}
</delete>
<delete id="deletePeopleByFloor" parameterType="map">
	DELETE 
	FROM
		jl_grid_user 
	WHERE
		floor_id = #{floor_id}
</delete>
<delete id="deletePeopleByDoorplate" parameterType="map">
	DELETE 
	FROM
		jl_grid_user 
	WHERE
		doorplate_id = #{doorplate_id}
</delete>
<delete id="deleteDoorplate" parameterType="map">
	DELETE 
	FROM
		jl_grid_doorplate 
	WHERE
		id = #{doorplate_id}
</delete>
<delete id="deleteDoorplateByFloor" parameterType="map">
	DELETE 
	FROM
		jl_grid_doorplate 
	WHERE
		floor_id = #{floor_id}
</delete>
<delete id="deleteDoorplateByUnit" parameterType="map">
	DELETE 
	FROM
		jl_grid_doorplate 
	WHERE
		unit_id = #{unit_id}
</delete>
<delete id="deleteDoorplateByBuilding" parameterType="map">
	DELETE 
	FROM
		jl_grid_doorplate 
	WHERE
		building_id = #{building_id}
</delete>
<delete id="deleteFloor" parameterType="map">
	DELETE 
	FROM
		jl_grid_floor 
	WHERE
		id = #{floor_id}
</delete>
<delete id="deleteFloorByUnit" parameterType="map">
	DELETE 
	FROM
		jl_grid_floor 
	WHERE
		unit_id = #{unit_id}
</delete>
<delete id="deleteFloorByBuilding" parameterType="map">
	DELETE 
	FROM
		jl_grid_floor
	WHERE
		building_id = #{building_id}
</delete>
<delete id="deleteUnit" parameterType="map">
	DELETE 
	FROM
		jl_grid_unit 
	WHERE
		id = #{unit_id}
</delete>
<delete id="deleteUnitByBuilding" parameterType="map">
	DELETE 
	FROM
		jl_grid_unit 
	WHERE
		building_id = #{building_id}
</delete>
<delete id="deleteBuilding" parameterType="map">
	DELETE 
	FROM
		jl_grid_building
	WHERE
		id = #{building_id}
</delete>
	<delete id="deleteMerchant" parameterType="map">
		DELETE
		FROM
			jl_grid_merchant
		WHERE
			garden_id = #{garden_id}
	</delete>
	<delete id="deleteSociety" parameterType="map">
		DELETE
		FROM
			jl_grid_society
		WHERE
			garden_id = #{garden_id}
	</delete>
	<delete id="deleteSubstance" parameterType="map">
		DELETE
		FROM
			jl_grid_substance
		WHERE
			garden_id = #{garden_id}
	</delete>
	<select id="getGardenByID" parameterType="map" resultType="map">
		SELECT
			id,
			`name`,
			grade,
			csc_mapjson,
			company_code,
			city_id,
			area_id,
			street_id,
			community_id,
			del_flag,
			create_by,
			create_time,
			update_by,
			update_time,
			map_type,
			manager,
			manager_tel,
			binding,
			parent_ids,
			net_id,
			csc_mapjson_ht,
			count,
			time,
			qh_code
		FROM
			jl_grid_garden
		WHERE
			id = #{id}
	</select>
	<select id="getBuildingByID" parameterType="map" resultType="map">
		SELECT
			id,
			brand,
			`name`,
			grade,
			csc_mapjson,
			company_code,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			del_flag,
			create_by,
			create_time,
			update_by,
			update_time,
			map_type,
			manager,
			manager_tel,
			parent_ids,
			build_type,
			csc_mapjson_ht
		FROM
			jl_grid_building
		WHERE
			id = #{id}
	</select>
	<select id="getUnitByID" parameterType="map" resultType="map">
	SELECT
		id,
		`name`,
		grade,
		csc_mapjson,
		parent_ids,
		company_code,
		city_id,
		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
		id = #{id}
	</select>
	<select id="getFloorByID" parameterType="map" resultType="map">
		SELECT
			id,
			`name`,
			sort,
			grade,
			csc_mapjson,
			parent_ids,
			company_code,
			area_id,
			street_id,
			community_id,
			city_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 = #{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,
			city_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,
			ym_lxnum,
			ym_sjnum
		FROM
			jl_grid_doorplate
		WHERE
			id = #{id}
	</select>
	<select id="findOperateLogList" parameterType="page" resultType="map">
		SELECT
			( SELECT `name` FROM sys_office WHERE id = REPLACE ( JSON_EXTRACT( a.operate_user, '$.company_id' ), '"', '' ) ) AS operate_community,
			cast(REPLACE ( JSON_EXTRACT( a.operate_user, '$.name' ), '"', '' ) AS CHAR) AS operate_people,
			cast( AES_DECRYPT( FROM_BASE64 ( REPLACE ( JSON_EXTRACT( a.operate_user, '$.cardno_y' ), '"', '' ) ), 'HMzOraRAn6nWZmty10Lmt123' ) AS CHAR ) AS operate_people_cardno,
			date_format(a.create_time, '%Y-%m-%d %T') as create_time,
			CASE
			REPLACE ( JSON_EXTRACT( a.operate_params, '$.grade' ), '"', '' )
			WHEN '5.5' THEN
			'小区'
			WHEN '6' THEN
			'网格'
			WHEN '7' THEN
			'楼'
			WHEN '8' THEN
			'单元'
			WHEN '9' THEN
			'层'
			WHEN '10' THEN
			'户'
			END AS grade
		FROM
			jl_grid_operatelog a
		WHERE
			a.operate_type = 'DELETE'
		ORDER BY
			a.create_time DESC
	</select>
	<select id="findOperateLogNumList" parameterType="map" resultType="map">
		SELECT
			count(1) as num
		FROM
			jl_grid_operatelog a
		WHERE
			a.operate_type = 'DELETE'
		ORDER BY
			a.create_time DESC
	</select>
	<select id="findGridUserNum" parameterType="map" resultType="map">
		select count(1) num from jl_grid_user
		    <where>
				<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>
				<if test="unit_id != null and unit_id !=''">
					AND unit_id = #{unit_id}
				</if>
				<if test="floor_id != null and floor_id !=''">
					AND floor_id = #{floor_id}
				</if>
				<if test="doorplate_id != null and doorplate_id !=''">
					AND doorplate_id = #{doorplate_id}
				</if>
			</where>
	</select>
</mapper>