<?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.gridCardInfo"
	name="统计（首页网格展示卡片统计）">
	<sql id="communityLevelInfo">
		AND community_code IN (
		SELECT `code`
		FROM sys_office
		WHERE del_flag = '0'
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		)
	</sql>
	<sql id="communityLevelInfoForID">
		AND community_id IN (
		SELECT id
		FROM sys_office
		WHERE del_flag
		= '0'
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		)
	</sql>
	<sql id="userShardingConditionSql">
		<if test="_user.company_grade=='3'.toString()">
			and city_id = #{_user.company_id}
		</if>
		<if test="_user.company_grade=='4'.toString() or _user.company_grade=='5'.toString() or _user.company_grade=='6'.toString()">
			and city_id =
			SUBSTRING_INDEX(SUBSTRING_INDEX(#{_user.parent_ids}, ',', 3), ',',-1)
		</if>
	</sql>

	<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 == '6'.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 == '6'.toString() and _user.loginName == 'LM20061111'.toString()">
			AND FIND_IN_SET( id, #{_user.building_id} )
		</if>
	</sql>
	<sql id="workerDivideJurisdictionForDoorplate">
		<!-- <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( building_id, #{_user.building_id} )
		</if>
	</sql>
	<sql id="workerDivideJurisdictionForUesr">
		<!-- <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( building_id, #{_user.building_id} )
		</if>
	</sql>
	<select id="getLevelInfo" parameterType="map" resultType="map">
		select id,
		name,
		<if test="tableName != 'sys_office'">
			company_code,
			community_id,
		</if>
		grade
		from ${tableName}
		where id = #{id}
	</select>
	<select id="getOfficeAreaNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		AND parent_id = #{id}
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		and fax is null
		and grade = '4'
		<!--AND `level` = '20' -->
		<!--AND `code` IN ( 'LM00001', 'LM00002', 'LM00003', 'LM00004', 'LM00005', 
			'LM00006', 'LM00007', 'LM00008', 'LM00009', 'LM00010', 'LM00011', 'LM00012', 
			'LM00013' ) -->

	</select>
	<select id="getOfficeStreetNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		<!--AND `level` = '30' -->
		<if test="grade == '3'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '4'.toString()">
			AND parent_id = #{id}
		</if>
		and grade = '5'
		and fax is null
	</select>
	<select id="getOfficeXzNum" parameterType="map" resultType="map">
		SELECT
		count( 1 ) AS num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		AND `name` NOT LIKE '%残联%'
		<!--AND `level` = '35' -->
		<if test="grade == '3'.toString()">
			AND parent_id = #{id}
		</if>
		and fax is null
	</select>
	<select id="getOfficeCommunityNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		<!--AND `level` = '40' -->
		<if test="grade == '3'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '4'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '5'.toString()">
			AND parent_id = #{id}
		</if>
		and grade = '6'
		and fax is null
	</select>
	<select id="getOfficeCunNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		<!--AND `level` = '45' -->
		<if test="grade == '3'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '4'.toString()">
			AND parent_id = #{id}
		</if>
		and fax is null
	</select>
	<select id="getPaintOfficeCommunityNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		<!--AND `level` = '40' -->
		<if test="grade == '3'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '4'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '5'.toString()">
			AND parent_id = #{id}
		</if>
		and grade = '6'
		and fax is null
		AND id IN ( SELECT community_id FROM jl_grid_garden WHERE del_flag = '0'
		AND map_type = 'Amap' )
	</select>
	<select id="getPaintOfficeCunNum" parameterType="map"
		resultType="map">
		SELECT
		count(1) as num
		FROM
		sys_office
		WHERE
		del_flag = '0'
		<!--AND system REGEXP 'grid' -->
		AND `name` NOT LIKE '%测试%'
		AND `name` NOT LIKE '%客服%'
		<!--AND `level` = '45' -->
		<if test="grade == '3'.toString()">
			AND parent_ids like CONCAT('%', #{id},'%')
		</if>
		<if test="grade == '4'.toString()">
			AND parent_id = #{id}
		</if>
		and fax is null
		AND id IN ( SELECT community_id FROM jl_grid_garden WHERE del_flag = '0'
		AND map_type = 'Amap' )
	</select>
	<select id="getGardenNum" parameterType="map" resultType="map">
		SELECT
		COUNT( 1 ) as num
		FROM
		jl_grid_garden
		WHERE
		del_flag = '0'
		AND map_type = 'Amap'
		<include refid="communityLevelInfoForID" />
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<include refid="workerDivideJurisdictionForGraden" />
	</select>
	<!-- <select id="getResidentialNum" parameterType="map" resultType="map"> 
		SELECT COUNT( 1 ) as num FROM jl_grid_residential WHERE del_flag = '0' <include 
		refid="communityLevelInfoForID"/> <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 == '5.5'.toString()"> AND id = #{id} </if> <include 
		refid="workerDivideJurisdictionForGraden"/> </select> -->
	<select id="getBuildingNum" parameterType="map" resultType="map">
		SELECT
		COUNT( 1 ) as num
		FROM
		jl_grid_building
		WHERE
		del_flag = '0'
		AND map_type = 'Amap'
		<include refid="communityLevelInfoForID" />
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		<!-- <include refid="workerDivideJurisdictionForBuilding"/> -->
	</select>
	<select id="getDoorplateNum" parameterType="map"
		resultType="map">
		SELECT
		COUNT(1) as num
		FROM
		jl_grid_doorplate
		WHERE
		del_flag = '0'
		<include refid="communityLevelInfoForID" />
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		<if test="grade == '8'.toString()">
			AND building_id = #{id}
		</if>
		<!-- <include refid="workerDivideJurisdictionForDoorplate"/> -->
	</select>
	<select id="getUserNum" parameterType="map" resultType="map">
		SELECT
		COUNT(1) as num
		FROM
		jl_grid_user
		<where>
			del_flag = '0'
			<if test="grade == '3'.toString()">
				AND city_id = #{id}
			</if>
			<if test="grade == '4'.toString()">
				AND area_id = #{id}
			</if>
			<if test="grade == '5'.toString()">
				AND street_id = #{id}
			</if>
			<if test="grade == '6'.toString()">
				AND community_id = #{id}
			</if>
			<if test="grade == '7'.toString()">
				AND garden_id = #{id}
			</if>
			<if test="grade == '8'.toString()">
				AND building_id = #{id}
			</if>
			<include refid="userShardingConditionSql"/>
		</where>
	</select>
	<select id="getCompanyInfoByCode" parameterType="map"
		resultType="map">
		SELECT
		id,
		parent_code,
		p_parent_code,
		parent_codes,
		parent_id,
		parent_ids,
		`name`,
		sort,
		area_id,
		`code`,
		type,
		grade,
		<!--`level`, -->
		<!--system, -->
		csc_mapjson
		FROM
		sys_office
		WHERE
		`code` = #{company_code}
	</select>
	<select id="getCompanyInfoByid" parameterType="map"
		resultType="map">
		SELECT
		id,
		parent_code,
		p_parent_code,
		parent_codes,
		parent_id,
		parent_ids,
		`name`,
		sort,
		area_id,
		`code`,
		type,
		grade
		<!--, -->
		<!--`level` -->
		<!--, -->
		<!--system -->
		FROM
		sys_office
		WHERE
		id = #{community_id}
	</select>
	<select id="getUserTypeNum" parameterType="map" resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		WHERE
		b.del_flag = '0'
		AND b.user_type = '0'
		<if test="grade == '4'.toString()">
			AND b.street_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND b.community_id = #{id}
		</if>
	</select>
	<select id="getHuJiTypeNum" parameterType="map" resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		WHERE
		b.del_flag = '0'
		AND b.huji_type = '0'
		<if test="grade == '4'.toString()">
			AND b.street_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND b.community_id = #{id}
		</if>
	</select>
	<select id="getLiuDongTypeNum" parameterType="map"
		resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		WHERE
		b.del_flag = '0'
		AND b.user_type = '1'
		<if test="grade == '4'.toString()">
			AND b.street_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND b.community_id = #{id}
		</if>
	</select>
	<select id="getHuJiJuZhuTypeNum" parameterType="map"
		resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		WHERE
		b.del_flag = '0'
		AND b.huji_type = '0'
		AND b.juzhu_type = '0'
		<if test="grade == '4'.toString()">
			AND b.street_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND b.community_id = #{id}
		</if>
	</select>
	<select id="getChangZhuJuZhuTypeNum" parameterType="map"
		resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		WHERE
		b.del_flag = '0'
		AND b.user_type = '0'
		AND b.juzhu_type = '0'
		<if test="grade == '4'.toString()">
			AND b.street_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND b.community_id = #{id}
		</if>
	</select>
	<select id="getUserNumAll" parameterType="map" resultType="map">
		SELECT
		COUNT(1) as num
		FROM
		jl_grid_user
		WHERE
		del_flag = '0'
		<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>
	<sql id="ym_yes">
		AND EXISTS ( SELECT cardno FROM jl_grid_user_focusgroup WHERE cardno =
		a.cardno AND lable_id = 'L006' AND two_lable_id = 'L006-1622614288234'
		)
	</sql>
	<sql id="ym_no">
		AND NOT EXISTS ( SELECT cardno FROM jl_grid_user_focusgroup WHERE cardno
		= a.cardno AND lable_id = 'L006' AND two_lable_id =
		'L006-1622614288234' )
	</sql>
	<select id="getUserNumForYmYes" parameterType="map"
		resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		<where>
			1 = 1
			<if test="grade == '3'.toString()">
				AND b.area_id = #{id}
			</if>
			<if test="grade == '4'.toString()">
				AND b.street_id = #{id}
			</if>
			<if test="grade == '5'.toString()">
				AND b.community_id = #{id}
			</if>
			<include refid="ym_yes" />
		</where>
	</select>
	<select id="getUserNumForYmNo" parameterType="map"
		resultType="map">
		SELECT
		count( DISTINCT b.cardno ) AS num
		FROM
		jl_sq_user a
		INNER JOIN jl_grid_user b ON a.cardno = b.cardno
		<where>
			1 = 1
			<if test="grade == '3'.toString()">
				AND b.area_id = #{id}
			</if>
			<if test="grade == '4'.toString()">
				AND b.street_id = #{id}
			</if>
			<if test="grade == '5'.toString()">
				AND b.community_id = #{id}
			</if>
			<include refid="ym_no" />
		</where>
	</select>
	<select id="getDistInctUserNum" parameterType="map" resultType="map">
		SELECT
		COUNT( DISTINCT cardno ) as num
		FROM
		jl_grid_user b
		<where>
		<include refid="userShardingConditionSql"/>
		and del_flag='0'
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		<if test="grade == '8'.toString()">
			AND building_id = #{id}
		</if>
		</where>
	</select>
	<select id="getActualResidenceNum" parameterType="map"
		resultType="map">
		SELECT
		COUNT( 1 ) as num
		FROM
		jl_grid_doorplate
		WHERE
		del_flag = '0'
		AND people_num != 0
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND building_id = #{id}
		</if>
		<include refid="communityLevelInfoForID" />
	</select>
	<select id="getKHNum" parameterType="map" resultType="map">
		SELECT
		COUNT( 1 ) as num
		FROM
		jl_grid_doorplate
		WHERE
		people_num = 0
		<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 == '5.5'.toString()">
			AND residential_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND garden_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND building_id = #{id}
		</if>
		<include refid="communityLevelInfoForID" />
	</select>
	
	<select id="getMerchantNum" parameterType="map" resultType="long">
		SELECT
		COUNT(id) as num
		FROM
		jl_grid_merchant b
		<where>
		and del_flag='0'
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		</where>
	</select>
	
	<select id="getSocietyNum" parameterType="map" resultType="long">
		SELECT
		COUNT(id) as num
		FROM
		jl_grid_society b
		<where>
		and del_flag='0'
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		</where>
	</select>
	
	<select id="getSubstanceNum" parameterType="map" resultType="long">
		SELECT
		COUNT(id) as num
		FROM
		jl_grid_substance b
		<where>
		and del_flag='0'
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		</where>
	</select>
	
	<select id="getOrgNum" parameterType="map" resultType="long">
		SELECT
		COUNT(id) as num
		FROM
		jl_grid_org b
		<where>
		and del_flag='0'
		<if test="grade == '3'.toString()">
			AND city_id = #{id}
		</if>
		<if test="grade == '4'.toString()">
			AND area_id = #{id}
		</if>
		<if test="grade == '5'.toString()">
			AND street_id = #{id}
		</if>
		<if test="grade == '6'.toString()">
			AND community_id = #{id}
		</if>
		<if test="grade == '7'.toString()">
			AND garden_id = #{id}
		</if>
		</where>
	</select>
</mapper>