<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.countrygarden.bi.main.dao.twerp.hm.TbSysOrganMapper">
	<resultMap id="tbSysOrganResultMap" type="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
		<id column="OrganCode" jdbcType="NVARCHAR" property="organCode"/>
		<result column="OrganName" jdbcType="NVARCHAR" property="organName"/>
		<result column="IsDelete" jdbcType="SMALLINT" property="isdelete"/>
		<result column="IsComp" jdbcType="SMALLINT" property="iscomp"/>
		<result column="Num" jdbcType="SMALLINT" property="num"/>
		<result column="fdNo" jdbcType="NVARCHAR" property="fdno"/>
		<result column="TimeStamp" jdbcType="BINARY" property="timestamp"/>
		<result column="userCode" jdbcType="BINARY" property="userCode"/>
	</resultMap>

	<sql id="selectAll">
    <![CDATA[
        SELECT o.OrganCode, o.OrganName
        FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] AS o
        ]]>
    </sql>

	<select id="findByOrganCode" parameterType="string"
			resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
		<include refid="selectAll"/>
		<![CDATA[
          WHERE o.IsDelete = 0 AND o.OrganCode = #{organCode} and o.rentid=1853
        ]]>
	</select>

	<select id="findByUserCode" parameterType="string"
			resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
        <![CDATA[


SELECT TOP
	1 u.UserCode,
	ur.RoleCode,
	SUBSTRING ( d.OrganCode, 1, 4 ) AS OrganCode,
	d.CommID,
	o.OrganName
FROM
	[HM_wygl_new_1853].[dbo].[Tb_Sys_User] u
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_UserRole] ur ON ur.UserCode = u.UserCode AND u.rentid=ur.rentid
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_RoleData] d ON d.RoleCode = ur.RoleCode AND d.rentid=ur.rentid
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] o ON ( SUBSTRING ( d.OrganCode, 1, 4 ) = o.OrganCode AND o.isDelete = 0 ) AND d.rentid=o.rentid
WHERE
	u.UserCode = #{userCode} AND u.rentid=1853


        ]]>
    </select>

	<select id="findByOrganName" fetchSize="1" parameterType="string"
			resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
        <![CDATA[


SELECT TOP 1 o.OrganCode, o.OrganName, o.IsDelete, o.IsComp, o.Num, o.fdNo, o.TimeStamp
FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] AS o
WHERE o.IsDelete = 0 AND o.OrganName = #{organName} and o.rentid=1853


        ]]>
    </select>

	<select id="findAllActive" resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
		<include refid="selectAll"></include>
		<![CDATA[
          WHERE o.IsDelete = 0 AND o.rentid=1853
        ]]>
	</select>

	<!--查询所有的纯案场或者案场常规混合的区域-->
	<select id="findCaseFieldOrgans" resultMap="tbSysOrganResultMap">

		select o.OrganName,o.OrganCode
		FROM HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo i WITH(NOLOCK)
		INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK)
		ON o.OrganCode = i.OrganCode AND ISNULL(o.IsDelete,0) = 0
		AND ISNULL(i.IsDelete,0) = 0 and o.rentid=1853 and i.rentid=1853
		where (i.DevelopmentState='案场常规混合' or i.DevelopmentState='纯案场')
		<if test="organCode !='01'">
			and o.organCode = #{organCode}
		</if>


		group by o.OrganName,o.OrganCode

	</select>
	<!--<if test="organCode !=null and organCode != ''"></if>-->

	<!-- 有效的绩效区域 -->
	<select id="selectPerformanceArea" resultMap="tbSysOrganResultMap">
        select * from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ]
        WHERE IsDelete = 0 and IsComp = 1 AND rentid=1853 and OrganName LIKE '%区域%' and OrganName NOT LIKE '%测试%'
        ORDER BY OrganCode
    </select>

	<!--所有的绩效区域-->
	<select id="selectPerformanceAllArea" resultMap="tbSysOrganResultMap">
        select * from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ]
        WHERE OrganName LIKE '%区域%' AND rentid=1853 and OrganName NOT LIKE '%测试%'
        ORDER BY OrganCode
    </select>

	<!--城市公司-->
	<select id="selectPerformanceCompany" resultMap="tbSysOrganResultMap">
        select * from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ]
        WHERE IsDelete = 0 and IsComp = 1 AND rentid=1853 AND OrganName LIKE '%公司%' and OrganName NOT LIKE '%测试%'
        ORDER BY OrganCode
    </select>

	<!--绩效区域/城市公司-->
	<select id="selectPerformanceAll" resultMap="tbSysOrganResultMap">
		select * from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ]
		where rentid=1853 and OrganCode in (select a.OrganCode from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a WHERE a.IsDelete = 0 and a.IsComp = 1 AND a.rentid=1853 AND a.OrganName LIKE '%区域%' and a.OrganName NOT LIKE '%测试%')
		or OrganCode in (select b.OrganCode from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] b WHERE b.IsDelete = 0 and b.IsComp = 1 AND b.rentid=1853 AND b.OrganName LIKE '%公司%' and b.OrganName NOT LIKE '%测试%')
		ORDER BY OrganCode
	</select>

	<select id="selectAll" resultMap="tbSysOrganResultMap">
        select * from [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ]
        where rentid=1853
    </select>

	<resultMap id="BigholderRightManagerMap"
			   type="cn.com.countrygarden.bi.main.model.BigholderRightManager">
		<id column="id" jdbcType="INTEGER" property="id" />
		<result column="area" jdbcType="VARCHAR" property="area" />
		<result column="hand_user" jdbcType="VARCHAR" property="handUser" />
		<result  column="area_id" jdbcType="VARCHAR" property="areaId" />
		<result column="user_id" jdbcType="VARCHAR" property="userId" />
	</resultMap>
	<select id="getAreas" resultMap="BigholderRightManagerMap">
		SELECT
			org.OrganCode area_id,
			OrganName area
		FROM
			HM_wygl_new_1853.dbo.[Tb_Sys_Organ] org
		WHERE org.IsDelete = '0'
		AND org.OrganCode IS NOT NULL and org.rentid=1853
	</select>

	<select id="getAreasOnly" resultType="string">
		SELECT
			OrganName area
		FROM
			HM_wygl_new_1853.dbo.[Tb_Sys_Organ] org
		WHERE org.IsDelete = '0'
		AND org.OrganCode IS NOT NULL and org.rentid=1853
	</select>

	<select id="getAreasWithCondition" parameterType="map" resultMap="BigholderRightManagerMap">
		SELECT
		org.OrganCode area_id,
		OrganName area
		FROM
		HM_wygl_new_1853.dbo.[Tb_Sys_Organ] org
		WHERE org.IsDelete = '0'
		AND org.OrganCode IS NOT NULL and org.rentid=1853
		<if test="area!=null and area!=''">
			AND	org.OrganName LIKE CONCAT ('%',#{area},'%')
		</if>
		<if test="areaIds.size()>0 and areaIds!= null">
			AND	org.OrganCode IN
			<foreach item="item" index="index" collection="areaIds" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</select>

	<select id="getUsers" parameterType="string" resultType="hashmap">
		SELECT
		u.UserCode userId,
		d.DepName depName,
		u.UserName handUser
		FROM
		HM_wygl_new_1853.[dbo].[Tb_Sys_Department] d
		LEFT JOIN HM_wygl_new_1853.[dbo].[Tb_Sys_User] u ON d.DepCode = u.DepCode
		WHERE
		u.IsDelete = '0'
		AND
		d.rentid=1853 and u.rentid=1853 and
		u.UserCode IS NOT NULL
		<if test="user != null and user != ''">
			AND u.UserName LIKE CONCAT ('%',#{user},'%')
		</if>
	</select>

	<select id="findArea" resultType="cn.com.countrygarden.bi.main.dto.advise.RegionListVO">
		SELECT DISTINCT a.OrganCode organCode, a.OrganName organName
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>

			<if test="organCode!=null and organCode!=''">
				and  a.OrganCode=#{organCode}
			</if>
			and u.rentid=1853
			and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
			'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环','北京国瑞')
		</where>

	</select>

	<select id="findAreaBasicInfo" resultType="cn.com.countrygarden.bi.main.dto.advise.RegionListVO">
		SELECT DISTINCT a.OrganCode organCode, a.OrganName organName
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>

			<if test="organCode!=null and organCode!=''">
				and  a.OrganCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and  c.CommID=#{commID}
			</if>
			and u.rentid=1853
			 and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
			'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','凤凰会产品体验内测区域','长沙天环','北京国瑞')
			</where>

	</select>

	<select id="findProject" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.CommName commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
			<if test="organs!=null and organs.size()!=0">
				and a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
			</if>
          and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
			'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环','北京国瑞','四川和盟')
		</where>
	</select>

	<select id="findProjectBasicInfo" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
           SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			and c.StateProperty='已收楼' and (c.PropertyForm='纯住宅' or c.PropertyForm='混合类物业') and
			(c.DevelopmentState='纯常规' or c.DevelopmentState='案场常规混合') and c.ProjectType='住宅物业'
			and (c.TakeoverState='已接管进驻' or c.TakeoverState='已接管未进驻') and
		    c.PropertyProject not like CONCAT('%','（酬金制）','%') and c.PropertyProject not like CONCAT('%','（酬金）','%')
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
			<if test="organs!=null and organs.size()!=0">
				and a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
			</if>
		</where>
	</select>

	<select id="findProjectBasicInfoAdddsq" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode,c.ContractProperty as contractProperty
		,c.CGManageTime as actualEnterTime
		FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		where  COALESCE(c.IsDelete,0) = 0 and COALESCE(a.IsDelete,0) = 0 and a.OrganCode is not null and c.rentid=1853  and c.StateProperty='已收楼'
		and (c.PropertyForm='纯住宅' or c.PropertyForm='混合类物业') and
			(c.DevelopmentState='纯常规' or c.DevelopmentState='案场常规混合') and c.ProjectType='住宅物业'
			and (c.TakeoverState='已接管进驻' or c.TakeoverState='已接管未进驻' ) and
		  c.PropertyProject not like CONCAT('%','（酬金制）','%') and c.PropertyProject not like CONCAT('%','（酬金）','%')
		and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','凤凰会产品体验内测区域','长沙天环','北京国瑞','四川和盟')
       <if test="commID!=null and commID!=''">
		   and c.CommID=#{commID}
	   </if>
	</select>

	<select id="findProjectBasicInfoSelect" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		         SELECT CommID commId, PropertyProject commName, PropertyArea organName, OrganCode organCode,ContractProperty as contractProperty,CGManageTime as actualEnterTime
		         FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo]
            where rentid=1853 and StateProperty='已收楼'
             and (PropertyForm='纯住宅' or PropertyForm='混合类物业') and
			(DevelopmentState='纯常规' or DevelopmentState='案场常规混合') and ProjectType='住宅物业'
		     and (TakeoverState='已接管进驻' or TakeoverState='已接管未进驻' ) and
		     PropertyProject not like CONCAT('%','（酬金制）','%') and PropertyProject not like CONCAT('%','（酬金）','%')
             and PropertyArea not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环')
            and COALESCE(IsDelete,0) = 0 and ContractProperty BETWEEN #{startTime} and #{endTime}
		<if test="ckRegionRole==true">
			<choose>
				<when test="regionList!=null and regionList.size()>0 ">
					<foreach collection="regionList" item="item" open="and OrganCode in (" close=")"
							 separator=",">
						#{item.organCode}
					</foreach>
				</when>
			</choose>

		</if>

      <if test="orgCode!=null and orgCode!=''">
		  and OrganCode=#{orgCode}
	  </if>
	</select>

	<select id="findProjectBasicInfoCommIdNum" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
              SELECT PropertyArea organName, OrganCode organCode ,count(CommID) as commIdNum FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo]
            where rentid=1853 and StateProperty='已收楼'
             and (PropertyForm='纯住宅' or PropertyForm='混合类物业') and
			(DevelopmentState='纯常规' or DevelopmentState='案场常规混合') and ProjectType='住宅物业'
			and (TakeoverState='已接管进驻' or TakeoverState='已接管未进驻' ) and
		PropertyProject not like CONCAT('%','（酬金制）','%') and PropertyProject not like CONCAT('%','（酬金）','%')
             and PropertyArea not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环','北京国瑞')
            and COALESCE(IsDelete,0) = 0
            <if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''">
				and ContractProperty BETWEEN #{startTime} and #{endTime}
			</if>
		<if test="orgCode!=null and orgCode!=''">
			and OrganCode=#{orgCode}
		</if>
		<if test="ckRegionRole==true">
			<choose>
				<when test="regionList!=null and regionList.size()>0 ">
					<foreach collection="regionList" item="item" open="and OrganCode in (" close=")"
							 separator=",">
						#{item.organCode}
					</foreach>
				</when>
			</choose>

		</if>
			group by PropertyArea,OrganCode
	</select>

	<select id="findProjectBasicInfoSelectCount" resultType="int">
		         SELECT count(CommID) FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo]
            where rentid=1853 and StateProperty='已收楼'
            and (PropertyForm='纯住宅' or PropertyForm='混合类物业') and
			(DevelopmentState='纯常规' or DevelopmentState='案场常规混合') and ProjectType='住宅物业'
		    and (TakeoverState='已接管进驻' or TakeoverState='已接管未进驻')
		    and PropertyProject not like CONCAT('%','（酬金制）','%') and PropertyProject not like CONCAT('%','（酬金）','%')
            and PropertyArea not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环')
            and COALESCE(IsDelete,0) = 0 and ContractProperty BETWEEN #{startTime} and #{endTime}
		<if test="ckRegionRole==true">
			<choose>
				<when test="regionList!=null and regionList.size()>0 ">
					<foreach collection="regionList" item="item" open="and OrganCode in (" close=")"
							 separator=",">
						#{item.organCode}
					</foreach>
				</when>
			</choose>

		</if>
		<if test="orgCode!=null and orgCode!=''">
			and OrganCode=#{orgCode}
		</if>
	</select>

    <select id="findProjectBasicInfoCount" resultType="int">
                     SELECT count(CommID) FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo]
            where rentid=1853 and StateProperty='已收楼' and PropertyArea not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','长沙天环')
		and COALESCE(IsDelete,0) = 0
    </select>

	<select id="findOrgCoProject" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.CommName commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and (c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
			<if test="organs!=null and organs.size()!=0">
				or a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
				)
			</if>

		</where>
	</select>

	<select id="findOrgCot" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.CommName commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="organs!=null and organs.size()!=0">
				and a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
			</if>

		</where>
	</select>

	<select id="findCommPro" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode,c.ContractProperty contractProperty
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
		</where>
	</select>

	<select id="allAreas" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.CommName commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.OrganCode = a.OrganCode and COALESCE(c.IsDelete,0) = 0 and c.rentid=a.rentid
		<where> a.OrganCode is not null and a.rentid=1853
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID not in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
		</where>
	</select>

	<select id="findByOrganNameNew" fetchSize="1" parameterType="string"
			resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan">
        <![CDATA[


SELECT TOP 1 o.OrganCode, o.OrganName, o.IsDelete, o.IsComp, o.Num, o.fdNo
FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] AS o
WHERE o.IsDelete = 0 AND o.OrganName = #{organName} and o.rentid=1853


        ]]>
    </select>

	<select id="findProjectOrder" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.CommName commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="organName!=null and organName!=''">
				and a.OrganName=#{organName}
			</if>
			<if test="commId!=null and commId!=''">
				and c.CommID=#{commId}
			</if>
			<if test="commName!=null and commName!=''">
				and c.CommName=#{commName}
			</if>
			and u.rentid=1853
		</where>
	</select>


	<select id="findAreaSatisfied" resultType="cn.com.countrygarden.bi.main.dto.advise.RegionListVO">
		SELECT DISTINCT a.OrganCode organCode, a.OrganName organName
		FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a
		<where>
		 a.OrganCode is not null and a.rentid=1853 and a.IsDelete=0
			and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
			'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','碧桂园生活服务集团股份有限公司','凤凰会产品体验内测区域',
		  '碧桂园西域（兰州）城市服务有限公司','青岛碧桂园润锦物业服务有限公司','长沙天环','北京国瑞')
		</where>

	</select>

	<select id="findProjectSatisfied" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null and u.rentid=1853
			<if test="organName!=null and organName!=''">
				and a.OrganName = #{organName}
			</if>
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commName!=null and commName!=''">
				and c.PropertyProject=#{commName}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
			<if test="organs!=null and organs.size()!=0">
				and a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
			</if>
		</where>
	</select>

	<select id="findProjectSatisfiedDaoqu" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a
		where
		a.OrganCode is not null and a.rentid=1853 and a.isdelete = 0
		and a.OrganName not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','碧桂园生活服务集团股份有限公司',
		  '凤凰会产品体验内测区域','碧桂园西域（兰州）城市服务有限公司','青岛碧桂园润锦物业服务有限公司','长沙天环','北京国瑞','四川和盟')
		<if test="organName!=null and organName!=''">
			and a.OrganName = #{organName}
		</if>
		<if test="organCode!=null and organCode!=''">
			and a.organCode=#{organCode}
		</if>
	</select>

	<select id="findProjectSatisfiedDaoxiangmu" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName,c.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c
		where c.rentid=1853 and COALESCE(c.IsDelete,0) = 0
		and c.PropertyArea not in ('深圳盛孚','农商行调试区域','物业总公司','大连德泰城市公司（物业公司）','辽宁交投','碧嘉','上海联源','上海明君','新工产业','上海金晨',
		'南昌市洁佳物业有限公司','北京盛世','港联广州','港联武汉','港联上海','广东元海','广州宁信城市公司','苏州物源','韩城城市公司','港联天津','内蒙仁和','碧桂园生活服务集团股份有限公司',
       '凤凰会产品体验内测区域','碧桂园西域（兰州）城市服务有限公司','青岛碧桂园润锦物业服务有限公司','长沙天环','北京国瑞','四川和盟')
		<if test="organName!=null and organName!=''">
			and c.PropertyArea = #{organName}
		</if>
		<if test="commID!=null and commID!=''">
			and c.CommID=#{commID}
		</if>
		<if test="commName!=null and commName!=''">
			and c.PropertyProject=#{commName}
		</if>
	</select>

	<select id="findProjectBenefitsZation" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			and (c.ProjectType='住宅物业' or c.ProjectType='商业物业') and
		a.OrganCode is not null and u.rentid=1853
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
            <if test="comtr!=null and comtr.size()!=0">
				and c.CommID not in
				<foreach item="comtr" index="index" collection="comtr" open="(" separator="," close=")">
					#{comtr}
				</foreach>
			</if>
		</where>
	</select>

	<select id="findProjectBenefits" resultType="cn.com.countrygarden.bi.main.dto.advise.ProjectListVO">
		SELECT DISTINCT c.CommID commId, c.PropertyProject commName, a.OrganName organName, a.OrganCode organCode
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_BasicInfo c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			and (c.ProjectType='住宅物业' or c.ProjectType='商业物业') and
			a.OrganCode is not null and u.rentid=1853
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>
			<if test="organCode!=null and organCode!=''">
				and a.organCode=#{organCode}
			</if>
			<if test="commID!=null and commID!=''">
				and c.CommID=#{commID}
			</if>
			<if test="commIds!=null and commIds.size()!=0">
				and c.CommID in
				<foreach item="commIds" index="index" collection="commIds" open="(" separator="," close=")">
					#{commIds}
				</foreach>
			</if>
			<if test="organs!=null and organs.size()!=0">
				and a.organCode in
				<foreach item="organs" index="index" collection="organs" open="(" separator="," close=")">
					#{organs}
				</foreach>
			</if>
		</where>
	</select>

	<select id="findAreaBene" resultType="cn.com.countrygarden.bi.main.dto.advise.RegionListVO">
		SELECT DISTINCT a.OrganCode organCode, a.OrganName organName
		FROM [HM_wygl_new_1853].[dbo].Tb_Sys_User u
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_UserRole ur ON ur.UserCode = u.UserCode and ur.rentid=u.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_Role r ON r.RoleCode = ur.RoleCode and r.rentid=ur.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_Sys_RoleData rd ON rd.RoleCode = r.RoleCode and rd.rentid=r.rentid
		INNER JOIN [HM_wygl_new_1853].[dbo].Tb_HSPR_Community c ON c.CommID = rd.CommID and COALESCE(c.IsDelete,0) = 0 and c.rentid=rd.rentid
		left join [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] a ON a.OrganCode = c.OrganCode and a.isdelete = 0 and a.rentid=c.rentid
		<where>
			a.OrganCode is not null
			<if test="userCode!=null and userCode!=''">
				and u.UserCode = #{userCode}
			</if>

			<if test="organCode!=null and organCode!=''">
				and  a.OrganCode=#{organCode}
			</if>
			and u.rentid=1853
		</where>

	</select>

</mapper>