<?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.dup.DivertStatisticalMapper">

	<resultMap type="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" id="DivertStatisticalMap">
		<result column="OrganCode" property="organCode" />
		<result column="OrganName" property="organName" />
		<result column="CommName" property="commName" />
		<result column="CommID" property="commId" />
		<result column="houseQuantity" property="houseQuantity" />
		<result column="actualProperty" property="actualProperty" />

		<result column="receiptsFloor" property="receiptsFloor" />
		<result column="NCSystemNumber" property="NCSystemNumber" />
		<result column="dwellRatio" property="dwellRatio" />

		<!-- 数量 -->
		<result column="stepLadderHouse" property="stepLadderHouse" />
		<result column="elevatorHouse" property="elevatorHouse" />
		<result column="autocephalyVilla" property="autocephalyVilla" />
		<result column="doubleSpellVilla" property="doubleSpellVilla" />
		<result column="fourSpellVilla" property="fourSpellVilla" />
		<result column="conjoinedVilla" property="conjoinedVilla" />
		<result column="shop" property="shop" />
		<result column="parkingSpaces" property="parkingSpaces" />
		<result column="other" property="other" />
		<result column="park" property="park" />
		<result column="writingFloor" property="writingFloor" />
		<result column="scenicArea" property="scenicArea" />
		<result column="school" property="school" />
		<result column="apartment" property="apartment" />
		<result column="market" property="market" />
		<result column="officebuilding" property="officebuilding" />
		<result column="wineshop" property="wineshop" />
		<result column="industrialPlants" property="industrialPlants" />
		<result column="industryHouse" property="industryHouse" />
		<result column="warehouse" property="warehouse" />
		<result column="hospital" property="hospital" />
		<result column="stadium" property="stadium" />
		<result column="library" property="library" />
		<result column="exhibitionCenter" property="exhibitionCenter" />
		<result column="carpark" property="carpark" />
		<result column="racecourse" property="racecourse" />
		<result column="golfCourse" property="golfCourse" />
		<result column="carGAS" property="carGAS" />
		<result column="airport" property="airport" />
		<result column="busStation" property="busStation" />
		<result column="pier" property="pier" />
		<result column="municipalRoads" property="municipalRoads" />
		<result column="bridge" property="bridge" />
		<result column="tunnel" property="tunnel" />


		<result column="Province" property="province" />
		<result column="City" property="city" />
		<result column="Borough" property="borough" />
	</resultMap>


	<!-- 模糊搜索基础数据 -->
	<select id="findDivertStaticalLike" parameterType="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" resultMap="DivertStatisticalMap">
	SELECT
			tso.OrganCode, 
			tso.OrganName, 
			thc.CommName,  
			thc.CommID,
			thc.Province,
			thc.City,
			thc.Borough,
			thp.HouseQuantity, 
			thb.ActualProperty,
			thb.ProjectNumber,	 
			thb.NCSystemNumber	 
		FROM
			HM_wygl_new_1853.dbo.Tb_Sys_Organ as tso
		LEFT JOIN
			HM_wygl_new_1853.dbo.Tb_HSPR_Community as thc 
		ON
		tso.OrganCode=thc.OrganCode AND tso.rentid=thc.rentid
		AND
		tso.IsDelete not in(1)
		LEFT join  
		HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo as thb
		ON
		thb.CommID=thc.CommID AND thb.rentid=thc.rentid
		AND
		thb.IsDelete not in(1)
		LEFT JOIN
		HM_wygl_new_1853.dbo.Tb_HSPR_PlanInfo as thp 
		ON
		thp.CommID=thc.CommID AND thp.rentid=thc.rentid
		AND
		thp.IsDelete not in (1)
	<where>
		tso.rentid=1853 and thc.rentid=1853 and thb.rentid=1853
	<if test="commId !=0 and commId!=null">AND thc.CommID=#{commId}</if>
	<if test="organCode!=null and organCode!=''"> AND tso.organCode=#{organCode}</if>
	</where>
	order by thb.ActualProperty desc
	</select>
	<!--<if test="actualPropertyStart!=null and actualPropertyStart!=''">AND thb.ActualProperty &gt;=#{actualPropertyStart}</if>-->
	<!--<if test="actualPropertyEnd!=null and actualPropertyEnd!=''">AND thb.ActualProperty &lt;=#{actualPropertyEnd}</if>-->
	<!-- 查询后面应该存在几列数据 -->
	<select id="findRoomType" resultType="String">
		SELECT
			a.DictionaryName
		FROM
		HM_wygl_new_1853.dbo.Tb_Dictionary_BuildType as a
		where a.rentid=1853
		</select>
	
	<!-- 查询项目的不同楼宇数 -->
	<select id="findRootNumber" parameterType="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" resultMap="DivertStatisticalMap">
		SELECT 步梯洋房 AS stepLadderHouse,电梯洋房 AS elevatorHouse,独立别墅 AS autocephalyVilla,
	      		双拼别墅 AS doubleSpellVilla,四拼别墅 AS fourSpellVilla,联体别墅 AS conjoinedVilla,
				商铺 AS shop,车位 AS parkingSpaces,其它 AS other,公园 AS park,写字楼 AS writingFloor,景区 AS scenicArea,
				学校 AS school,公寓 AS apartment,商场 AS market,办公楼 AS officebuilding,酒店 AS wineshop,工业厂房 AS industrialPlants,
				产业用房 AS industryHouse,仓库 AS warehouse,医院 AS hospital,体育馆 AS stadium,
        		图书馆 AS library,会展中心 AS exhibitionCenter,停车场 AS carpark,赛马场 AS racecourse,高尔夫球场 AS golfCourse,
				汽车加油站 AS carGAS,飞机场 AS airport,车站 AS busStation,码头 AS pier,市政道路 AS municipalRoads,
        		桥梁 AS bridge,隧道 AS tunnel
		FROM (
    		SELECT bt.DictionaryName,ISNULL(c.RoomCount,0) RoomCount
    		FROM HM_wygl_new_1853.dbo.Tb_Dictionary_BuildType bt WITH(NOLOCK)
    		LEFT JOIN (
        		SELECT b.BuildType,COUNT(*) RoomCount
        		FROM HM_wygl_new_1853.dbo.view_HSPR_Customer_SaleFilter f
        		INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Building b WITH(NOLOCK)
            	ON b.CommID = f.CommID AND b.rentid=f.rentid AND convert(varchar,b.BuildSNum) = f.BuildSNum
        		INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_CustType ct WITH(NOLOCK)
            	ON ct.CustTypeID = f.CustTypeID AND ct.rentid=f.rentid
       		 	<where>
       		 		f.IsDelete = 0 AND f.IsSale = 1 AND f.LiveType1 = 1 AND f.rentid=1853
	            	AND ISNULL(b.IsDelete,0) = 0 and
					(ct.CustTypeName = '二手业主' OR ct.CustTypeName = '三手业主'
					OR ct.CustTypeName = '四手业主' OR ct.CustTypeName = '五手业主'
					OR ct.CustTypeName = '六手业主')
					<if test="commId !=0 and commId!=null">AND f.CommID=#{commId}</if>
	            	<if test="actualPropertyStart!=null and actualPropertyStart!=''">AND f.ChargeTime  &gt;=#{actualPropertyStart}</if>
	          		<if test="actualPropertyEnd!=null and actualPropertyEnd!=''">AND f.ChargeTime &lt;#{actualPropertyEnd}</if>

	            </where> 
       		    
        	GROUP BY b.BuildType
    		) c ON c.BuildType = bt.DictionaryCode and bt.rentid=1853
		) t
		PIVOT (
    		MAX(RoomCount)
    		FOR DictionaryName IN (
				步梯洋房,电梯洋房,独立别墅,双拼别墅,四拼别墅,联体别墅,商铺,车位,其它,公园,
    			写字楼,景区,学校,公寓,商场,办公楼,酒店,工业厂房,产业用房,仓库,医院,体育馆,
    			图书馆,会展中心,停车场,赛马场,高尔夫球场,汽车加油站,飞机场,车站,码头,市政道路,
    			桥梁,隧道)
		) piv
	</select>
	
  	<!--查询某项目的已收楼的数量  -->
  	<select id="findReceiptsFloor" parameterType="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" resultType="int">
  		SELECT
			COUNT(a.RoomID)
		FROM
			HM_wygl_new_1853.dbo.Tb_HSPR_Room as a
		<where>
		a.ActualSubDate &lt;=GETDATE()
		and 
		a.IsDelete NOT in (1)
		and 
		a.CommID=#{commId}
		and a.rentid=1853
		</where>
  	</select>
  	
  	<!-- 计算常住的房间有几套 -->
  	<select id="findReceiptsFloorNumber" parameterType="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" resultType="int">
  			SELECT
				COUNT([HM_wygl_new_1853].[dbo].Tb_HSPR_Room.CommID)
			FROM
				[HM_wygl_new_1853].[dbo].Tb_HSPR_Room
			WHERE
				[HM_wygl_new_1853].[dbo].Tb_HSPR_Room.LiveStates='0001'
			AND	
			[HM_wygl_new_1853].[dbo].Tb_HSPR_Room.CommID=#{commId}
			and rentid=1853
  	</select>
  	
  	<!-- 计算当前项目存在几套房   -->
  	<select id="findCommReceiptsFloorNumber"  parameterType="cn.com.countrygarden.bi.main.model.dup.DivertStatistical" resultType="int">
  		SELECT
			COUNT([HM_wygl_new_1853].[dbo].Tb_HSPR_Room.CommID)
		FROM
			[HM_wygl_new_1853].[dbo].Tb_HSPR_Room
		WHERE
			[HM_wygl_new_1853].[dbo].Tb_HSPR_Room.CommID=#{commId}
			and rentid=1853
  	</select>
  	
</mapper>