<?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.marketing.ChargeMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.marketing.Charge">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="year" jdbcType="SMALLINT" property="year"/>
        <result column="month" jdbcType="TINYINT" property="month"/>
        <result column="comm_id" jdbcType="INTEGER" property="commId"/>
        <result column="is_delete" jdbcType="TINYINT" property="isDelete"/>
        <result column="charge_area" jdbcType="DECIMAL" property="chargeArea"/>
        <result column="memo" jdbcType="VARCHAR" property="memo"/>
        <result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
        <result column="modified_by" jdbcType="VARCHAR" property="modifiedBy"/>
        <result column="nc_system_name" jdbcType="VARCHAR" property="ncSystemName"/>
        <result column="nc_system_code" jdbcType="VARCHAR" property="ncSystemCode"/>
        <result column="additional_charge" jdbcType="BIT" property="additionalCharge"/>
        <result column="increase_charge" jdbcType="BIT" property="increaseCharge"/>
        <result column="start_date" jdbcType="DATE" property="startDate"/>
        <result column="end_date" jdbcType="DATE" property="endDate"/>
        <result column="last_month_charge_area" jdbcType="VARCHAR" property="lastMonthChargeArea"/>
        <result column="this_month_charge_area" jdbcType="VARCHAR" property="thisMonthChargeArea"/>
    </resultMap>

    <resultMap id="AggreResultMap" type="cn.com.countrygarden.bi.main.model.marketing.ChargeResult">

        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="comm_id" jdbcType="INTEGER" property="commId"/>
        <result column="comm_name" jdbcType="VARCHAR" property="commName"/>
        <result column="organ_id" jdbcType="VARCHAR" property="organId"/>
        <result column="charge_area" jdbcType="DECIMAL" property="chargeArea"/>
    </resultMap>

    <select id="sumIncreaseChargeArea" resultType="java.math.BigDecimal">
        <![CDATA[
      SELECT IFNULL(SUM(charge_area),0) FROM marketing_charge
      WHERE increase_charge = 1
    ]]>
        <if test="additionalCharge!=null">
            <![CDATA[
        AND additional_charge = #{additionalCharge}
      ]]>
        </if>
        <if test="startDate!=null and endDate!=null">
            <![CDATA[
        AND start_date >= #{startDate} AND end_date<=#{endDate}
      ]]>
        </if>
    </select>

    <select id="ranking" resultMap="AggreResultMap">
        <![CDATA[
SELECT
	g.comm_id,
	c.`name` as comm_name,
	c.organ_id,
	sum( g.charge_area ) AS charge_area
FROM
	marketing_charge AS g
	LEFT JOIN comm AS c ON ( g.comm_id = c.id )
WHERE
	g.increase_charge = '0'
    ]]>
        <if test="startDate!=null">
            <![CDATA[
        AND g.start_date >= #{startDate}
      ]]>
        </if>

        <if test="endDate!=null">
            <![CDATA[
        AND g.end_date <= #{endDate}
      ]]>
        </if>
        <![CDATA[
        GROUP BY
	c.organ_id
ORDER BY
	charge_area DESC
      ]]>
    </select>


    <select id="thisAndLastMonthChargeArea"
            resultMap="BaseResultMap">
    <![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>
</mapper>