<?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.TbDeskTopFeesDailyMapper">
  <resultMap id="TbDeskTopDailyResultMap" type="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">
    <id column="IID" jdbcType="CHAR" property="iid" />
    <result column="OrganCode" jdbcType="NVARCHAR" property="organCode" />
    <result column="CommID" jdbcType="INTEGER" property="commID" />
    <result column="StatDate" jdbcType="TIMESTAMP" property="statDate" />
    <result column="TypeCode" jdbcType="NVARCHAR" property="typeCode" />
    <result column="FeesCommID" jdbcType="INTEGER" property="feesCommID" />
    <result column="CustID" jdbcType="BIGINT" property="custID" />
    <result column="ChargeMode" jdbcType="NVARCHAR" property="chargeMode" />
    <result column="DealDate" jdbcType="TIMESTAMP" property="dealDate" />
    <result column="DealAmount" jdbcType="NUMERIC" property="dealAmount" />
    <result column="LateAmount" jdbcType="NUMERIC" property="lateAmount" />
    <result column="UpdatedTime" jdbcType="TIMESTAMP" property="UpdatedTime" />
    <result column="charge_area" jdbcType="VARCHAR" property="chargeArea" />
    <result column="season" jdbcType="INTEGER" property="season" />
  </resultMap>
  <!-- 昨天首笔 -->
  <select id="getFirstFeesOfLastDay"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">
SELECT
	TOP 1 o.OrganName,
	m.CommName,
	c.CustName,
	c.MobilePhone,
	f.ChargeMode,
	CONVERT(VARCHAR(50), f.DealDate, 121) AS DealDate,
	f.DealAmount,
	f.LateAmount
FROM
	Rts.dbo.Tb_DeskTop_FeesDaily f
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = f.TypeCode
AND t.TypeName = '首笔缴纳'
AND f.StatDate = DATEADD(DAY ,- 1, CAST(GETDATE() AS DATE))
AND f.CommID = 0
AND f.OrganCode != '01'
INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK) ON o.OrganCode = f.OrganCode
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community m WITH(NOLOCK) ON m.CommID = f.FeesCommID
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Customer c WITH(NOLOCK) ON c.CustID = f.CustID
ORDER BY
	dealdate ASC
</select>
<!-- 昨天最高 -->
<select id="getMaxFeesOfLastDay"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">
    SELECT
	TOP 1 o.OrganName,
	m.CommName,
	c.CustName,
	c.MobilePhone,
	f.ChargeMode,
	CONVERT(VARCHAR(50), f.DealDate, 121) AS DealDate,
	f.DealAmount,
	f.LateAmount
FROM
	Rts.dbo.Tb_DeskTop_FeesDaily f
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = f.TypeCode
AND t.TypeName = '最高缴纳'
AND f.StatDate = DATEADD(DAY ,- 1, CAST(GETDATE() AS DATE))
AND f.CommID = 0
AND f.OrganCode != '01'
INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK) ON o.OrganCode = f.OrganCode
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community m WITH(NOLOCK) ON m.CommID = f.FeesCommID
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Customer c WITH(NOLOCK) ON c.CustID = f.CustID
ORDER BY
	DealAmount DESC
</select>
<!-- 今天首笔 -->
<select id="getFirstFeesOfToday"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">
--今天的首笔缴纳
SELECT
	TOP 1 o.OrganName,
	m.CommName,
	c.CustName,
	c.MobilePhone,
	CONVERT(VARCHAR(50), f.DealDate, 121) AS DealDate,
	f.DealAmount,
	f.LateAmount
FROM
	Rts.dbo.Tb_DeskTop_FeesDaily f
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = f.TypeCode
AND t.TypeName = '首笔缴纳'
AND f.StatDate = CAST(GETDATE() AS DATE)
AND f.CommID = 0
AND f.OrganCode != '01'
INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK) ON o.OrganCode = f.OrganCode
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community m WITH(NOLOCK) ON m.CommID = f.FeesCommID
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Customer c WITH(NOLOCK) ON c.CustID = f.CustID
ORDER BY
	f.DealDate ASC
</select>
<!-- 今日最高 -->
<select id="getMaxFeesOfToday"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">

SELECT
	TOP 1 o.OrganName,
	m.CommName,
	c.CustName,
	ISNULL(c.MobilePhone, '') as mobilePhone,
	ISNULL(f.ChargeMode, '') as ChargeMode,
	CONVERT(VARCHAR(50), f.DealDate, 121) AS DealDate,
	f.DealAmount,
	f.LateAmount
FROM
	Rts.dbo.Tb_DeskTop_FeesDaily f
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = f.TypeCode
AND t.TypeName = '最高缴纳'
AND f.StatDate = CAST(GETDATE() AS DATE)
AND f.CommID = 0
AND f.OrganCode != '01'
INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK) ON o.OrganCode = f.OrganCode
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community m WITH(NOLOCK) ON m.CommID = f.FeesCommID
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Customer c WITH(NOLOCK) ON c.CustID = f.CustID
ORDER BY
	DealAmount DESC
</select>
<!-- 昨日缴纳最高区域 -->
<select id="getMaxFeesOfOrganInLastDay"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">

SELECT
	TOP 1 o.OrganName,
	ISNULL(a.Amount, 0) as DealAmount,
	CONVERT(VARCHAR(50), a.StatDate, 121) AS DealDate
FROM
	Rts.dbo.Tb_DeskTop_CommAmountDaily a
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = a.TypeCode
AND t.TypeName = '服务费实收'
AND a.StatDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND a.CommID = 0
AND a.OrganCode != '01'
INNER JOIN HM_wygl_new_1853.dbo.Tb_Sys_Organ o WITH(NOLOCK) ON o.OrganCode = a.OrganCode
where o.rentid=1853 and a.rentid=1853 and t.rentid=1853
ORDER BY
	DealAmount DESC
</select>
<!-- 昨日缴纳最高项目 -->
<select id="getMaxFeesOfCommInLastDay"
          fetchSize="1"
          resultMap="TbDeskTopDailyResultMap"
          parameterType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDeskTopFeesDaily">

SELECT
	TOP 1 c.CommName,
	ISNULL(a.Amount, 0) as DealAmount,
	CONVERT(VARCHAR(50), a.StatDate, 121) AS DealDate
FROM
	Rts.dbo.Tb_DeskTop_CommAmountDaily a
INNER JOIN Rts.dbo.Tb_Dictionary_AmountType t ON t.TypeCode = a.TypeCode
AND t.TypeName = '服务费实收'
AND a.StatDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND a.CommID != 0
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community c WITH(NOLOCK) ON c.CommID = a.CommID
ORDER BY
	DealAmount DESC
</select>

	<select id="thisAndLastMonthChargeArea"
			resultMap="TbDeskTopDailyResultMap">
		<![CDATA[

select sum(buildarea)as charge_area from HM_wygl_new_1853.dbo.Tb_HSPR_Room m
where
		m.roomid <>'0' and
       m.roomid in
(
select distinct(roomid)
from Rts_FeesSerial..Tb_HSPR_FeesSerial a, HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo  c
where  a.CommID=c.CommID
		and c.DevelopmentSubject ='外拓'
		and (a.BussType = '新增' or a.BussType = '删除')
		and  CONVERT(char(7),ChargesModeFeesDueDate,120) = CONVERT(char(7),DATEADD(MONTH,-1,GETDATE()),120)
		and isnull(ParkID,0) = 0
		and a.CorpCostID in (100000251,100000253,100000299,100000444,100000445,100000464,100000465,100000466,100000469,100000474)
		group by roomid having sum(a.DealAmount)>'0'
		)
union all
select sum(buildarea)as 面积 from HM_wygl_new_1853.dbo.Tb_HSPR_Room m
where
	m.roomid <>'0' and
	  m.roomid in
(
select distinct(roomid)
from Rts_FeesSerial..Tb_HSPR_FeesSerial a, HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo  c
where  a.CommID=c.CommID
		and c.DevelopmentSubject ='外拓'
		and (a.BussType = '新增' or a.BussType = '删除')
		and  CONVERT(char(7),ChargesModeFeesDueDate,120) = CONVERT(char(7),DATEADD(MONTH,0,GETDATE()),120)
		and isnull(ParkID,0) = 0
		and a.CorpCostID in (100000251,100000253,100000299,100000444,100000445,100000464,100000465,100000466,100000469,100000474)
		group by roomid having sum(a.DealAmount)>'0'
		)


        ]]>

	</select>

	<!--市场拓展总的收费面积-->
	<select id="totalChargeArea" resultMap="TbDeskTopDailyResultMap">
		select sum(buildarea)as charge_area from HM_wygl_new_1853.dbo.Tb_HSPR_Room m
		where
		m.roomid !='0' and m.rentid=1853 and
		m.roomid in
		(
		select distinct(roomid)
		from Rts_FeesSerial..Tb_HSPR_FeesSerial a, HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo  c
		where  a.CommID=c.CommID and c.rentid=1853
		and c.DevelopmentSubject ='外拓'
		and (a.BussType = '新增' or a.BussType = '删除')
		--and  CONVERT(char(7),ChargesModeFeesDueDate,120) = CONVERT(char(7),DATEADD(MONTH,-1,GETDATE()),120)
		and isnull(ParkID,0) = 0
		and a.CorpCostID in (100000251,100000253,100000299,100000444,100000445,100000464,100000465,100000466,100000469,100000474)		--收费项目
		and a.rentid=1853
		group by roomid having sum(a.DealAmount)>'0'
		)

	</select>

	<!--市场拓展季度实际收费面积-->
	<select id="seasonRealChargeArea" resultMap="TbDeskTopDailyResultMap">
		;WITH t AS
(
 SELECT *,
  ROW_NUMBER() OVER (PARTITION BY RoomID ORDER BY quarter) AS rn
    FROM (
  SELECT RoomID,DATEPART(QUARTER,ChargesModeFeesDueDate) quarter
  FROM Rts_FeesSerial.dbo.Tb_HSPR_FeesSerial s WITH(NOLOCK)
  INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_BasicInfo i WITH(NOLOCK)
   ON i.CommID = s.CommID AND ISNULL(i.IsDelete,0) = 0
    AND i.DevelopmentSubject = '外拓'
    AND (s.BussType = '新增' OR s.BussType = '删除') and i.rentid=1853
    AND DATEPART(YEAR,s.ChargesModeFeesDueDate) = DATEPART(YEAR,GETDATE())
    AND ISNULL(s.RoomID,0) > 0 AND ISNULL(s.ParkID,0) = 0
    AND s.CorpCostID IN (100000251,100000253,100000299,100000444,100000445
     ,100000464,100000465,100000466,100000469,100000474)
   ) t1
)
SELECT quarter as season,SUM(BuildArea) as charge_area
FROM t
INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Room r WITH(NOLOCK) ON r.RoomID = t.RoomID and r.rentid=1853
WHERE rn = 1
GROUP BY quarter
	</select>

</mapper>
