<?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.userGrid" name="用户网格">
	<select id="findUserGridList" parameterType="map" resultType="map" expose="true" name="用户网格" desc="用户网格">
		SELECT
			a.id,
			a.user_type,
			a.user_name,
			cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
			a.community_code,
			a.area_id,
			a.street_id,
			a.community_id,
			a.city_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,
			a.juzhu_type,
			a.huji_type,
			a.relation,
			a.is_kgh
		FROM
			jl_grid_user a
		WHERE
			1 = 1
			AND a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))	
			AND a.community_id = #{_user.company_id}
	</select>
	<delete id="deleteUserGrid" parameterType="map" expose="true" name="用户网格" desc="用户网格">
		DELETE FROM jl_grid_user WHERE id = #{id}
	</delete>
    
    <delete id="deleteUserFocusGroupByCardNo" parameterType="map" expose="true">
        DELETE FROM jl_grid_user_focusgroup WHERE cardno = #{cardno}
    </delete>
<select id="findUserGridNumByCardNo" parameterType="map" resultType="int">
		SELECT
			count(1) num
		FROM
			jl_grid_user a
		WHERE
			 a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
</select>
	
	<insert id="insertNoteDelete" parameterType="map">
		INSERT INTO jl_grid_user_note_delete (
			id,
			user_name,
			tel,
			cardno,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			building_id,
			unit_id,
			floor_id,
			doorplate_id,
			create_by,
			create_time,
			update_by,
			update_time,
			relation,
			delete_type,
			note,
			delete_time,
			company_code
		) SELECT
			id,
			user_name,
			tel,
			cardno,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			building_id,
			unit_id,
			floor_id,
			doorplate_id,
			create_by,
			create_time,
			update_by,
			update_time,
			relation,
			#{delete_type},
			#{note},
			NOW(),
			community_code
		FROM
			jl_grid_user
		WHERE
		<if test='delete_type == 1 || delete_type == "1".toString()'>
			id = #{id}
		</if>
		<if test='delete_type == 2 || delete_type == "2".toString()'>
			building_id = #{building_id}
		</if>
		<if test='delete_type == 3 || delete_type == "3".toString()'>
			unit_id = #{unit_id}
		</if>
		<if test='delete_type == 4 || delete_type == "4".toString()'>
			floor_id = #{floor_id}
		</if>
		<if test='delete_type == 5 || delete_type == "5".toString()'>
			doorplate_id = #{doorplate_id}
		</if>
		and city_id=#{city_id}
		and is_kgh='0'
	</insert>
	
	<select id="getDeleteUserShardingData" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.bo.GridPeopleBo">
		SELECT
			id,
			user_name,
			tel,
			cardno,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			building_id,
			unit_id,
			floor_id,
			doorplate_id,
			create_by,
			create_time,
			update_by,
			update_time,
			relation,
			#{delete_type} delete_type,
			#{note} note,
			community_code,
			is_kgh kghState 
		FROM
			jl_grid_user
		WHERE
		<if test='delete_type == 1 || delete_type == "1".toString()'>
			id = #{id}
		</if>
		<if test='delete_type == 2 || delete_type == "2".toString()'>
			building_id = #{building_id}
		</if>
		<if test='delete_type == 3 || delete_type == "3".toString()'>
			unit_id = #{unit_id}
		</if>
		<if test='delete_type == 4 || delete_type == "4".toString()'>
			floor_id = #{floor_id}
		</if>
		<if test='delete_type == 5 || delete_type == "5".toString()'>
			doorplate_id = #{doorplate_id}
		</if>
	</select>
	
	<insert id="insertNoteDeleteByShardingUser" parameterType="com.scpyun.platform.jilinsscgsdp.bean.bo.GridPeopleBo">
		INSERT INTO jl_grid_user_note_delete (
			id,
			user_name,
			tel,
			cardno,
			city_id,
			area_id,
			street_id,
			community_id,
			garden_id,
			building_id,
			unit_id,
			floor_id,
			doorplate_id,
			create_by,
			create_time,
			update_by,
			update_time,
			relation,
			delete_type,
			note,
			delete_time,
			company_code
		)  VALUE (
        	#{id},
			#{user_name},
			#{tel},
			#{cardno},
			#{city_id},
			#{area_id},
			#{street_id},
			#{community_id},
			#{garden_id},
			#{building_id},
			#{unit_id},
			#{floor_id},
			#{doorplate_id},
			#{create_by},
			#{create_time},
			#{update_by},
			#{update_time},
			#{relation},
			#{delete_type},
			#{note},
			NOW(),
			<choose>
				<when test="community_code != null and community_code != ''">
					#{community_code}
				</when>
				<otherwise>
					#{community_id}
				</otherwise>
			</choose>
        )
	</insert>
	
	
<select id="getUserGrid" parameterType="map" resultType="map">
		SELECT
			id,
			user_type,
			user_name,
			cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
			community_code,
			city_id,
			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
			1 = 1
			AND cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')) 
			AND garden_id = #{garden_id} 
			AND building_id = #{building_id} 
			AND unit_id = #{unit_id}
			AND floor_id = #{floor_id} 
			AND doorplate_id = #{doorplate_id}
</select>
<insert id="saveUserGrid" parameterType="map">
INSERT INTO jl_grid_user (
		id,
		<if test="user_type != null and user_type != ''">
		user_type, 
		</if>
		user_name,
		cardno,
		community_code,
		city_id,
		area_id,
		street_id,
		community_id,
		garden_id,
		building_id,
		unit_id,
		floor_id,
		doorplate_id,
		create_by,
		create_time,
		<if test="juzhu_type != null and juzhu_type != ''">
		juzhu_type,
		</if>
		<if test="huji_type != null and huji_type != ''">
		huji_type,
		</if>
		relation,
		<if test="is_kgh != null and is_kgh != ''">
		is_kgh
		</if>
	)VALUES( 
		UUID(), 
		<if test="user_type != null and user_type != ''">
		#{user_type}, 
		</if>
		#{user_name},
		TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
		#{_user.company_code},
		#{city_id},
		#{area_id},
		#{street_id},
		#{community_id},
		#{garden_id},
		#{building_id},
		#{unit_id},
		#{floor_id},
		#{doorplate_id},
		#{_user.company_code},
		now(),
		<if test="juzhu_type != null and juzhu_type != ''">
		#{juzhu_type},
		</if>
		<if test="huji_type != null and huji_type != ''">
		#{huji_type},
		</if>
		#{relation},
		<if test="is_kgh != null and is_kgh != ''">
		#{is_kgh}
		</if>
	)		
</insert>
	<insert id="saveUserGridNew" parameterType="map">
		INSERT INTO jl_grid_user (
		id,
		<if test="user_type != null and user_type != ''">
			user_type,
		</if>
		user_name,
		cardno,
		community_code,
		area_id,
		street_id,
		community_id,
		city_id,
		garden_id,
		building_id,
		unit_id,
		floor_id,
		doorplate_id,
		create_by,
		create_time,
		<if test="juzhu_type != null and juzhu_type != ''">
			juzhu_type,
		</if>
		<if test="huji_type != null and huji_type != ''">
			huji_type,
		</if>
		relation,
		<if test="is_kgh != null and is_kgh != ''">
			is_kgh
		</if>
		)VALUES(
		UUID(),
		<if test="user_type != null and user_type != ''">
			#{user_type},
		</if>
		#{user_name},
		TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
		#{_user.company_code},
		#{area_id},
		#{street_id},
		#{community_id},
		#{city_id},
		#{garden_id},
		#{building_id},
		#{unit_id},
		#{floor_id},
		#{doorplate_id},
		#{_user.company_code},
		now(),
		<if test="juzhu_type != null and juzhu_type != ''">
			#{juzhu_type},
		</if>
		<if test="huji_type != null and huji_type != ''">
			#{huji_type},
		</if>
		#{relation},
		<if test="is_kgh != null and is_kgh != ''">
			#{is_kgh}
		</if>
		)
	</insert>
<update id="updateUserGrid" parameterType="map">
		UPDATE jl_grid_user
		SET user_name = #{user_name},
			<if test="user_type != null and user_type != ''">
			user_type = #{user_type},
			</if>
			area_id = #{area_id},
			street_id = #{street_id},
			community_id = #{community_id},
			city_id = #{city_id},
			garden_id = #{garden_id},
			building_id = #{building_id},
			unit_id = #{unit_id},
			floor_id = #{floor_id},
			doorplate_id = #{doorplate_id},
			update_by = #{_user.company_code},
			update_time = now(),
			<if test="huji_type != null and huji_type != ''">
			huji_type = #{huji_type},
			</if>
			<if test="juzhu_type != null and juzhu_type != ''">
			juzhu_type = #{juzhu_type},
			</if>
			relation=#{relation},
			<if test="is_kgh != null and is_kgh != ''">
			is_kgh = #{is_kgh}
			</if>
		WHERE
			id = #{id}
</update>
	<update id="updateUserGridNew" parameterType="map">
		UPDATE jl_grid_user
			SET user_name = #{user_name},
			<if test="user_type != null and user_type != ''">
				user_type = #{user_type},
			</if>
			area_id = #{area_id},
			street_id = #{street_id},
			community_id = #{community_id},
			city_id = #{city_id},
			garden_id = #{garden_id},
			building_id = #{building_id},
			unit_id = #{unit_id},
			floor_id = #{floor_id},
			doorplate_id = #{doorplate_id},
			update_by = #{_user.company_code},
			update_time = now(),
			<if test="huji_type != null and huji_type != ''">
				huji_type = #{huji_type},
			</if>
			<if test="juzhu_type != null and juzhu_type != ''">
				juzhu_type = #{juzhu_type},
			</if>
			relation=#{relation},
			<if test="is_kgh != null and is_kgh != ''">
				is_kgh = #{is_kgh}
			</if>
		WHERE
		id = #{id}
	</update>
<select id="getUserType0ByCardNOList" parameterType="map" resultType="map">
	select id,area_id,street_id,community_id,community_code from jl_grid_user
	where cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')) and user_type = '0' order by create_time
</select>
<select id="getHuJiType0ByCardNOList" parameterType="map" resultType="map">
	select id,area_id,street_id,community_id from jl_grid_user
	where cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')) and huji_type = '0'
</select>
<select id="getKGHByCardNOList" parameterType="map" resultType="map">
	select id,area_id,street_id,community_id from jl_grid_user
	where cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')) and is_kgh = '0'
</select>
<select id="getGridInfoByName" parameterType="map" resultType="map">
		SELECT
			a.area_id,
			a.street_id,
			a.community_id,
			a.id AS garden_id,
			b.id AS building_id,
			c.id AS unit_id,
			d.id AS floor_id,
			e.id AS doorplate_id 
		FROM
			jl_grid_garden a
			JOIN jl_grid_building b ON a.id = b.garden_id
			JOIN jl_grid_unit c ON b.id = c.building_id
			JOIN jl_grid_floor d ON c.id = d.unit_id
			JOIN jl_grid_doorplate e ON d.id = e.floor_id
		WHERE
			a.community_id = #{_user.company_id} 
			AND a.`name` = #{garden_name}
			AND b.`name` = #{building_name}
			AND c.`name` = #{unit_name}
			AND d.`name` = #{floor_name}
			AND e.`name` = #{doorplate_name}
</select>
	<select id="getGridInfoByNameNew" parameterType="map" resultType="map">
		SELECT
		a.area_id,
		a.street_id,
		a.community_id,
		a.id AS garden_id,
		b.id AS building_id,
		c.id AS unit_id,
		d.id AS floor_id,
		e.id AS doorplate_id
		FROM
		jl_grid_garden a
		JOIN jl_grid_building b ON a.id = b.garden_id
		JOIN jl_grid_unit c ON b.id = c.building_id
		JOIN jl_grid_floor d ON c.id = d.unit_id
		JOIN jl_grid_doorplate e ON d.id = e.floor_id
		WHERE
		a.community_id = #{_user.company_id}
		AND a.name = #{garden_name}
		AND b.name = #{building_name}
		AND c.name = #{unit_name}
		AND d.name = #{floor_name}
		AND e.name = #{doorplate_name}
	</select>
<select id="findTeShuList" parameterType="map" resultType="map" expose="true" name="查询特殊人群位置" desc="查询特殊人群位置">
		SELECT
			count( 1 ) AS peopleNum,
			c.csc_mapjson,
			a.building_id,
			( SELECT count( 1 ) FROM jl_grid_doorplate WHERE del_flag = '0' AND building_id = a.building_id ) AS doorplateNum,
			ROUND(
				count( 1 ) / ( SELECT count( 1 ) FROM jl_grid_doorplate WHERE del_flag = '0' AND building_id = a.building_id ),
				4 
			) * 100 AS percent 
		FROM
			jl_grid_user a
			JOIN jl_sq_user b ON a.cardno = b.cardno
			JOIN jl_grid_building c ON a.building_id = c.id
		WHERE
			a.del_flag = '0' 
			AND c.del_flag = '0' 
			AND b.focus_group IS NOT NULL 
			AND b.focus_group != '' 
		<if test="_user.company_grade == '2'.toString()">
			and 1=1
		</if>
		<if test="_user.company_grade == '3'.toString()">
			and a.area_id=#{_user.company_id}
		</if>
		<if test="_user.company_grade == '4'.toString()">
			and a.street_id=#{_user.company_id}
		</if>
		<if test="_user.company_grade == '5'.toString()">
			and a.community_id=#{_user.company_id}
		</if>
		<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="grade == '7'.toString()">
			and a.building_id=#{id}
		</if>
		GROUP BY
			a.building_id
</select>
<select id="getTeShuNum" parameterType="map" resultType="map" name="查询不同种类重点人群数量" desc="查询不同种类重点人群数量" expose="true">
		SELECT
			count( 1 ) as num
		FROM
			jl_sq_user a 
		WHERE
			focus_group != ''
			AND focus_group is not null
		<if test="focus_group != null and focus_group != ''">
			AND focus_group LIKE CONCAT('%',#{focus_group},'%') 
		</if>	
			AND EXISTS ( 
				SELECT cardno 
				FROM jl_grid_user
				WHERE cardno = a.cardno 
				AND del_flag = '0' 
		<!-- <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="findRuHuList" parameterType="map" name="入户人员列表" desc="入户人员列表" resultType="map" expose="true">
		SELECT
			IF(LENGTH( a.user_name ) > 6,CONCAT( LEFT ( a.user_name, 1 ), '**' ),CONCAT( LEFT ( a.user_name, 1 ), '*' )) AS '姓名',
			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 ) ) AS '身份证号', 
			( SELECT `name` FROM sys_office WHERE id = a.area_id ) AS '区',
			( SELECT `name` FROM sys_office WHERE id = a.street_id ) AS '街道',
			( SELECT `name` FROM sys_office WHERE id = a.community_id ) AS '社区',
			b.`name` AS '小区',
			c.`name` AS '楼',
			d.`name` AS '单元',
			e.`name` AS '层',
			f.`name` AS '户' 
		FROM
			jl_grid_user a
			JOIN jl_grid_garden b ON a.garden_id = b.id
			JOIN jl_grid_building c ON a.building_id = c.id
			JOIN jl_grid_unit d ON a.unit_id = d.id
			JOIN jl_grid_floor e ON a.floor_id = e.id
			JOIN jl_grid_doorplate f ON a.doorplate_id = f.id
		WHERE
			a.del_flag = '0' 
			AND a.doorplate_id IS NOT NULL 
			AND a.doorplate_id != '' 
			AND a.area_id = #{area_id}
</select>
<insert id="insertGridPeopleExcel" parameterType="map">
			insert into jl_grid_excel(
				id,
				area_id,
				url,
				create_by,
				create_time
			)values(
				uuid(),
				#{area_id},
				#{url},
				'system',
				now()
			)
</insert>
<select id="getGridPeopleRHExcel" parameterType="map" resultType="map" expose="true" name="导出入户人员信息excel" desc="导出入户人员信息excel">
		SELECT
			id,
			area_id,
			url,
			del_flag,
			create_by,
			create_time,
			update_by,
			update_time 
		FROM
			jl_grid_excel
		WHERE
			area_id = #{_user.company_id}
		ORDER BY
			create_time DESC 
		LIMIT 1
</select>
<insert id="saveImportLog" parameterType="map">
		insert into jl_grid_import_log(
			id,
			lid,
			cardno,
			message,
			community_code,
			create_by,
			create_time,
			type
		)values(
			uuid(),
			#{lid},
			TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
			#{message},
			#{_user.company_code},
			#{_user.loginName},
			now(),
			#{type}
		)
</insert>
<select id="findFailedList" parameterType="map" resultType="map" expose="true" name="查询导入失败数据" desc="查询导入失败数据">
		SELECT
			id,
			lid,
			cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
			message,
			community_code,
			create_by,
			date_format(create_time, '%Y-%m-%d %H:%i:%s') as create_time
		FROM
			jl_grid_import_log
		WHERE
			create_by = #{_user.loginName} 
			AND type = '1'
			AND lid = ( SELECT lid FROM jl_grid_import_log WHERE create_by = #{_user.loginName} AND type = '1' ORDER BY create_time DESC LIMIT 1 )
</select>
<select id="getInfoByCardno" parameterType="map" resultType="map">
		SELECT
			id,
			`name`,
			ename,
			card_type,
			cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
			mobile,
			register_country,
			register_province,
			register_city,
			register_area,
			register_street,
			register_community,
			register_address,
			sex,
			birthday,
			folk,
			idphoto,
			photo,
			education,
			marriage,
			political_outlook,
			occupation,
			military,
			personal_income,
			work_unit,
			present_address,
			living_condition,
			fq_xm,
			fq_card_type,
			cast(AES_DECRYPT(FROM_BASE64(fq_card_no),'*#id#') as char) as fq_card_no,
			mq_xm,
			mq_card_type,
			cast(AES_DECRYPT(FROM_BASE64(mq_card_no),'*#id#') as char) as mq_card_no,
			po_xm,
			po_card_type,
			cast(AES_DECRYPT(FROM_BASE64(po_card_no),'*#id#') as char) as po_card_no,
			child,
			focus_group,
			remarks,
			create_by,
			create_time,
			update_time,
			update_by,
			source_type 
		FROM
			jl_sq_user
		WHERE
			cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
</select>
<update id="updateFocusGroupInfo" parameterType="map">
		update jl_sq_user
		set focus_group=#{newFocus_group},
			education=#{education}
		where cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
</update>
<select id="findTsFailedList" parameterType="map" resultType="map" expose="true" name="查询导入失败数据" desc="查询导入失败数据">
		SELECT
			id,
			lid,
			cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
			message,
			community_code,
			create_by,
			date_format(create_time, '%Y-%m-%d %H:%i:%s') as create_time
		FROM
			jl_grid_import_log
		WHERE
			create_by = #{_user.loginName} 
			AND type = '2'
			AND lid = ( SELECT lid FROM jl_grid_import_log WHERE create_by = #{_user.loginName} ORDER BY create_time DESC LIMIT 1 )
</select>
<select id="findTsNewFailedList" parameterType="map" resultType="map" expose="true" name="查询导入失败数据" desc="查询导入失败数据">
		SELECT
			id,
			lid,
			cast(AES_DECRYPT(FROM_BASE64(cardno),'*#id#') as char) as cardno,
			message,
			community_code,
			create_by,
			date_format(create_time, '%Y-%m-%d %H:%i:%s') as create_time
		FROM
			jl_grid_import_log
		WHERE
			create_by = #{_user.loginName} 
			AND type = '3'
			AND lid = ( SELECT lid FROM jl_grid_import_log WHERE create_by = #{_user.loginName} AND type = '3' ORDER BY create_time DESC LIMIT 1 )
</select>
<select id="findUserGridInfoList" parameterType="map" resultType="map" expose="true">
		SELECT
			a.id,
			a.user_type,
			a.huji_type,
			a.is_kgh,
			a.user_name,
			a.juzhu_type,
			cast(AES_DECRYPT(FROM_BASE64(a.cardno),'*#id#') as char) as cardno,
			a.community_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,
			a.source_type,
			a.relation,
			b.name as garden_name,
			c.name as building_name,
			d.name as unit_name,
			e.name as floor_name,
			f.name as doorplate_name,
			g.name as area_name,
			h.name as street_name,
			i.name as community_name
		FROM
			jl_grid_user a
			JOIN jl_grid_garden b ON a.garden_id = b.id
			JOIN jl_grid_building c ON a.building_id = c.id
			JOIN jl_grid_unit d ON a.unit_id = d.id
			JOIN jl_grid_floor e ON a.floor_id = e.id
			JOIN jl_grid_doorplate f ON a.doorplate_id = f.id
			JOIN sys_office g ON a.area_id = g.id
			JOIN sys_office h ON a.street_id = h.id
			JOIN sys_office i ON a.community_id = i.id
		WHERE
			a.del_flag = '0' 
			AND a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
</select>
<select id="findFocusGroupNumList" parameterType="map" resultType="map" expose="true" name="查询重点人群数量" desc="查询重点人群数量">
		SELECT
			COUNT( DISTINCT a.cardno ) as zdNum
		FROM
			jl_grid_user_focusgroup a
		<where>
			EXISTS ( 
				SELECT cardno 
				FROM jl_grid_user
				WHERE del_flag = '0'
				<if test="grade == '2'.toString()">
				AND 1 = 1
				</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>
				AND cardno = a.cardno 
				AND ( is_kgh = '0' OR ( is_kgh = '1' AND doorplate_id != '' AND doorplate_id IS NOT NULL ))
				AND user_type = '0')
		</where>	
</select>
<insert id="insertUserTypeLog" parameterType="map">
			insert into jl_grid_user_type_log(
				id,
				cardno,
				old_area_id,
				old_street_id,
				old_community_id,
				old_community_code,
				new_area_id,
				new_street_id,
				new_community_id,
				new_community_code,
				create_by,
				create_time 
			)values(
				uuid(),
				TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
				#{old_area_id},
				#{old_street_id},
				#{old_community_id},
				#{old_company_code},
				#{area_id},
				#{street_id},
				#{community_id},
				#{_user.company_code},
				#{_user.company_code},
				now()
			)
</insert>
<update id="updateUserType" parameterType="map">
			update jl_grid_user
			set user_type = '1'
			where id = #{id}
</update>
<select id="findUserTypeLogList" parameterType="map" resultType="map" expose="true">
			SELECT
				id,
				cardno,
				old_area_id,
				old_street_id,
				old_community_id,
				old_community_code,
				old_community_code old_company_name$sys_office_code_name,
				new_area_id,
				new_street_id,
				new_community_id,
				new_community_code,
				new_community_code new_company_name$sys_office_code_name,
				create_by,
				date_format(create_time, '%Y-%m-%d %T') as create_time 
			FROM
				jl_grid_user_type_log
			WHERE
				cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
</select>
<select id="findDoorplateUserNumList" parameterType="map" resultType="map" expose="true">
SELECT
	( 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',
	( SELECT `name` FROM jl_grid_garden WHERE id = a.garden_id ) AS 'garden_name',
	( SELECT `name` FROM jl_grid_building WHERE id = a.building_id ) AS 'building_name',
	( SELECT `name` FROM jl_grid_unit WHERE id = a.unit_id ) AS 'unit_name',
	( SELECT `name` FROM jl_grid_floor WHERE id = a.floor_id ) AS 'floor_name',
	( SELECT `name` FROM jl_grid_doorplate WHERE id = a.doorplate_id ) AS 'doorplate_name',
	a.doorplate_id,
	COUNT( 1 ) AS num,
	( SELECT remarks FROM jl_grid_doorplate_special WHERE doorplate_id = a.doorplate_id ) AS remarks
FROM
	jl_grid_user a
WHERE
	a.garden_id = #{garden_id}
	AND a.doorplate_id != '0123456789ABCDEF' 
GROUP BY
	a.doorplate_id 
ORDER BY
	num DESC
</select>
<select id="findMZCodeList" parameterType="map" resultType="map" expose="true" nologin="true">
	SELECT
		id,
		mzcode,
		mzname
	FROM
		jl_grid_mz_code
</select>
	<insert id="createPeopleInfoLog" parameterType="map">
		insert into jl_grid_peopleinfo_operatelog(
			id,
			cardno,
			community_id,
			info_type,
			source_type,
			user_id,
			create_time,
			dbcz_type
		)values(
			uuid(),
			TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
			#{_user.company_id},
			#{info_type},
			#{source_type},
			#{_user.id},
			now(),
			#{dbcz_type}
		)
	</insert>
	<select id="findUserLogByCardnoList" parameterType="map" resultType="map">
		select * from (
		SELECT
			a.id,
			a.user_name name,
			a.cardno,
			a.city_id,
			f.`name` AS city_name,
			a.area_id,
			c.`name` AS area_name,
			a.street_id,
			d.`name` AS street_name,
			a.community_id,
			e.`name` AS community_name,
			a.garden_id,
			g.`name` AS garden_name,
			a.building_id,
			h.`name` AS building_name,
			a.unit_id,
			i.`name` AS unit_name,
			a.floor_id,
			j.`name` AS floor_name,
			a.doorplate_id,
			k.`name` AS doorplate_name,
			IF
			( a.update_time IS NULL, date_format(a.create_time, '%Y-%m-%d %T'), date_format(a.update_time, '%Y-%m-%d %T') ) AS time,
			'' AS note,
			'迁入' AS type
		FROM
			jl_grid_user a
		LEFT JOIN sys_office f ON a.city_id = f.id
		LEFT JOIN sys_office c ON a.area_id = c.id
		LEFT JOIN sys_office d ON a.street_id = d.id
		LEFT JOIN sys_office e ON a.community_id = e.id
		LEFT JOIN jl_grid_garden g ON a.garden_id = g.id
		LEFT JOIN jl_grid_building h ON a.building_id = h.id
		LEFT JOIN jl_grid_unit i ON a.unit_id = i.id
		LEFT JOIN jl_grid_floor j ON a.floor_id = j.id
		LEFT JOIN jl_grid_doorplate k ON a.doorplate_id = k.id
		WHERE
			a.city_id=#{city_id} and
			a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
		UNION ALL
		SELECT
			a.id,
			a.user_name name,
			a.cardno,
			a.city_id,
			f.`name` AS city_name,
			a.area_id,
			c.`name` AS area_name,
			a.street_id,
			d.`name` AS street_name,
			a.community_id,
			e.`name` AS community_name,
			a.garden_id,
			g.`name` AS garden_name,
			a.building_id,
			h.`name` AS building_name,
			a.unit_id,
			i.`name` AS unit_name,
			a.floor_id,
			j.`name` AS floor_name,
			a.doorplate_id,
			k.`name` AS doorplate_name,
			date_format(a.delete_time, '%Y-%m-%d %T') AS time,
			a.note,
			'迁出' AS type
		FROM
			jl_grid_user_note_delete a
		LEFT JOIN sys_office f ON a.city_id = f.id
		LEFT JOIN sys_office c ON a.area_id = c.id
		LEFT JOIN sys_office d ON a.street_id = d.id
		LEFT JOIN sys_office e ON a.community_id = e.id
		LEFT JOIN jl_grid_garden g ON a.garden_id = g.id
		LEFT JOIN jl_grid_building h ON a.building_id = h.id
		LEFT JOIN jl_grid_unit i ON a.unit_id = i.id
		LEFT JOIN jl_grid_floor j ON a.floor_id = j.id
		LEFT JOIN jl_grid_doorplate k ON a.doorplate_id = k.id
		WHERE
			a.cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
		) temp	
		ORDER BY
			time desc
	</select>
	<select id="findUserLogByDoorplateList" parameterType="map" resultType="map">
		select * from 
		(
			SELECT
				a.id,
				a.user_name 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 ) ) AS cardno, 
				a.tel mobile,
				a.relation,
				IF
				( a.update_time IS NULL, date_format(a.create_time, '%Y-%m-%d %T'), date_format(a.update_time, '%Y-%m-%d %T')) AS time,
				'' AS note,
				'迁入' AS type
			FROM
				jl_grid_user a
			WHERE
				a.city_id = #{city_id} and
				a.doorplate_id = #{doorplate_id}
			union all	
			SELECT
				a.id,
				a.user_name 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 ) ) AS cardno, 
				a.tel mobile,
				a.relation,
				a.delete_time AS time,
				note,
				'迁出' AS type
			FROM
				jl_grid_user_note_delete a
			WHERE
				a.doorplate_id = #{doorplate_id}
			) temp
		ORDER BY
			time desc
	</select>
</mapper>