<?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.ViewHsprFeesSerialStaticMapper">
    <select id="selectIncomeByCommIDCorpCostID"
            resultType="cn.com.countrygarden.bi.main.model.twerp.ViewHsprFeesSerialStatic">
        SELECT
        s.CommID,
        s.CorpCostID,
        s.RoomID,
        s.ChargesModeFeesDueDate,
        s.DealAmount,
        s.BussType,
        s.CustTypeName,
        s.CorpStanID,
        s.FeesID
        INTO #FeesSerial
        FROM
        HM_wygl_new_1853_TFCrm.dbo.TF_View_HSPR_FeesSerial_Static s WITH (NOLOCK)
        WHERE
        <if test="commIdList != null">
            s.CommID IN
            <foreach item="commId" index="index" collection="commIdList" open="(" separator="," close=")">
                #{commId}
            </foreach>
        </if>
        AND s.BussType IN (
        '新增',
        '删除'
        )
        AND s.CorpCostID IN
        <foreach item="corpCostID" index="index" collection="corpCostIDList" open="(" separator="," close=")">
            #{corpCostID}
        </foreach>
        AND s.ChargesModeFeesDueDate &gt;= #{startDate}
        AND s.ChargesModeFeesDueDate &lt; #{endDate};
--         AND YEAR (s.DealDate) = YEAR (s.ChargesModeFeesDueDate)
--         AND MONTH (s.DealDate) = MONTH (s.ChargesModeFeesDueDate);

        SELECT
        CommID,
        FeesID,
        增值税率,
        地产代付,
        客户类别
        INTO #FeesProcessLog
        FROM
        HM_wygl_new_1853.dbo.Tb_HSPR_FeesProcessLog with (NOLOCK)
        WHERE
        IsDelete = 0
        <if test="commIdList != null">
            AND CommID IN
            <foreach item="commId" index="index" collection="commIdList" open="(" separator="," close=")">
                #{commId}
            </foreach>
        </if>
        ;

        SELECT
        s.CommID,
        s.CorpCostID,
        YEAR (s.ChargesModeFeesDueDate) YEAR,
        MONTH (s.ChargesModeFeesDueDate) MONTH,
        ISNULL(s.DealAmount, 0) includeTax,
        ROUND(ISNULL(s.DealAmount, 0) / (1 + [增值税率]),2) AS notIncludeTax,
        s.BussType,
        ROUND(ISNULL(o.OffsetAmount, 0) / (1 + [增值税率]),2) AS OffsetAmount,
        aa.[客户类别] as CustTypeName,
        f.StanName,
        ISNULL(rs.FeesRate, 1) FeesRate,
        o.WaiversCategory,
        ISNULL(aa.[地产代付], 0) AS Pay
        FROM
        #FeesSerial s
        INNER JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Community c WITH (NOLOCK) ON c.CommID = s.CommID
        LEFT JOIN #FeesProcessLog aa ON s.FeesID = aa.FeesID
        LEFT JOIN HM_wygl_new_1853_TFCrm.dbo.TF_View_OffsetWaivDetail o ON s.feesID = o.FeesID and o.isDelete = 0
        LEFT JOIN HM_wygl_new_1853.dbo.view_HSPR_CorpCostStandard_Filter f ON s.CorpStanID = f.CorpStanID and f.IsDelete = 0
        LEFT JOIN HM_wygl_new_1853.dbo.Tb_HSPR_Room r on s.RoomID = r.RoomID and s.CommID = r.CommID
        LEFT JOIN HM_wygl_new_1853.dbo.Tb_HSPR_RoomState rs on r.RoomState = rs.RoomState ;

        drop table #FeesSerial;
        drop table #FeesProcessLog;
    </select>


    <select id="selectArea" resultType="cn.com.countrygarden.bi.main.model.financial.AreaData"
            statementType="STATEMENT">
        SELECT
        r.CommID,
        SUM (ISNULL(r.BuildArea, 0)+ISNULL(r.GardenArea, 0)) area,
        YEAR (${areaType}) year,
        MONTH (${areaType}) month
        FROM
        HM_wygl_new_1853_TFCrm.dbo.TF_View_HSPR_Room r
        LEFT JOIN
        HM_wygl_new_1853_TFCrm.dbo.TF_View_HSPR_Building b ON r.BuildID = b.BuildID
        WHERE
        1=1
        <if test="commIdList != null">
            AND b.CommID IN
            <foreach item="commId" index="index" collection="commIdList" open="(" separator="," close=")">
                ${commId}
            </foreach>
        </if>

        and ${areaType} &lt; ${endDate}
        <if test="buildType != null">
            and r.PropertyUses = ${buildType}
            <if test="subBuildType != null">
                and b.BuildType like '%'+${subBuildType}
            </if>
        </if>
        GROUP BY
        r.CommID,
        YEAR (${areaType}),
        MONTH (${areaType})
        ORDER BY
        YEAR (${areaType}),
        MONTH (${areaType})
    </select>
</mapper>