<?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.TbDesktopResourceMapper">
    <sql id="sqlSumAll">
    <![CDATA[
        SELECT
            ISNULL( SUM (m.面积), 0 ) Area,
            ISNULL( SUM (m.业主数), 0 ) Owner,
            ISNULL( SUM (m.住宅数), 0 ) House,
            ISNULL( SUM (m.车位), 0 ) Parking,
            ISNULL( SUM (m.商铺), 0 ) Business,
            ISNULL( SUM (m.楼宇数), 0 ) Building,
            ISNULL( SUM (m.房屋数), 0 ) Room,
            ISNULL( SUM (m.客户数), 0 ) Customer,
            ISNULL( SUM (m.套内面积), 0 ) InnerArea,
            ISNULL( SUM (m.公摊面积), 0 ) ShareArea,
            ISNULL( SUM (m.花园面积), 0 ) GardenArea,
            ISNULL( SUM (m.商铺面积), 0 ) BusinessArea,
            ISNULL( SUM (m.地下室面积), 0 ) BasementArea,
            ISNULL( SUM (m.住宅面积), 0 ) HouseArea,
            ISNULL( SUM (m.本月增加面积), 0 ) MonthNewArea,
            ISNULL( SUM (m.本月增加业主数), 0 ) MonthNewOwner,
            ISNULL( SUM (m.本月增加住宅数), 0 ) MonthNewHouse,
            ISNULL( SUM (m.本月增加车位), 0 ) MonthNewParking,
            ISNULL( SUM (m.本月增加商铺), 0 ) MonthNewBusiness,
            ISNULL( SUM (m.本月增加楼宇数), 0 ) MonthNewBuilding,
            ISNULL( SUM (m.本月增加房屋数), 0 ) MonthNewRoom,
            ISNULL( SUM (m.本月增加客户数), 0 ) MonthNewCustomer
        FROM
            [Rts].[dbo].[Tb_DeskTop_Resource] AS m
        ]]>
    </sql>

    <sql id="sqlSumAreaByOrgan">
        <![CDATA[
        SELECT
            t.OrganName,
            SUM (m.面积) Area,
            SUM (m.车位) Parking,
            SUM (m.套内面积) InnerArea,
            SUM (m.公摊面积) ShareArea,
            SUM (m.花园面积) GardenArea,
            SUM (m.商铺面积) BusinessArea,
            SUM (m.地下室面积) BasementArea,
            SUM (m.住宅面积) HouseArea,
            SUM (m.住宅面积 + m.商铺面积 + m.地下室面积) Total
        FROM
            [Rts].[dbo].[Tb_DeskTop_Resource] AS m
        ]]>
    </sql>

    <sql id="sqlSumAreaByOrganGroupBy">
        <![CDATA[
        GROUP BY
            t.OrganName
        ORDER BY
            Total DESC
        ]]>
    </sql>

    <sql id="sqlInnerJoinUserRoleData">
        <![CDATA[
	INNER JOIN (
SELECT
	u.UserCode,
	d.CommID,
	c.OrganCode,
	c.CommName,
	o.OrganName
FROM
	[HM_wygl_new_1853].[dbo].[Tb_Sys_User] AS u
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_UserRole] AS r ON ( r.UserCode = u.UserCode AND u.IsDelete = 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_RoleData] AS d ON ( d.RoleCode = r.RoleCode AND d.CommID > 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_Community] AS c ON ( c.CommID = d.CommID AND c.isDelete = 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] AS o ON ( o.OrganCode = c.OrganCode AND o.isDelete = 0 )
WHERE
	u.UserCode = #{userCode}
	) AS t ON ( t.OrganCode = m.OrganCode AND t.CommID = m.CommID )
        ]]>
    </sql>

    <select id="sumAll"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAll"/>
    </select>

    <select id="sumByUserCode"
            fetchSize="1"
            parameterType="string"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAll"/>
        <include refid="sqlInnerJoinUserRoleData"/>
    </select>

    <select id="sumByUserCodeAndOrganCode"
            fetchSize="1"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAll"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <![CDATA[
          WHERE m.organcode = #{organCode}
        ]]>
    </select>

    <select id="sumByUserCodeAndOrganCodeAndCommID"
            fetchSize="1"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAll"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <![CDATA[
          WHERE m.organcode = #{organCode} AND m.CommID = #{commID}
        ]]>
    </select>

    <!-- 接管面积一览 -->
    <select id="sumByOrganByUserCode"
            parameterType="string"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAreaByOrgan"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <include refid="sqlSumAreaByOrganGroupBy"/>
    </select>

    <select id="sumByOrganByUserCodeAndOrganCode"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAreaByOrgan"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <![CDATA[
          WHERE m.organcode = #{organCode}
        ]]>
        <include refid="sqlSumAreaByOrganGroupBy"/>
    </select>

    <select id="sumByOrganByOrganByUserCodeAndOrganCodeAndCommID"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumAreaByOrgan"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <![CDATA[
          WHERE m.organcode = #{organCode} AND m.CommID = #{commID}
        ]]>
        <include refid="sqlSumAreaByOrganGroupBy"/>
    </select>


    <sql id="sqlInnerJoinUserRoleDataForOrgan">
        <![CDATA[
	INNER JOIN (
SELECT SUBSTRING
	( d.OrganCode, 1, 4 ) AS OrganCode
FROM
	[HM_wygl_new_1853].[dbo].[Tb_Sys_User] AS u
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_UserRole] AS r ON ( r.UserCode = u.UserCode AND u.IsDelete = 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_RoleData] AS d ON ( d.RoleCode = r.RoleCode AND d.CommID <> '-1' )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_Organ] AS o ON ( o.OrganCode = SUBSTRING ( d.OrganCode, 1, 4 ) AND o.isDelete = 0 )
WHERE
	u.UserCode = #{userCode}
GROUP BY
	SUBSTRING ( d.OrganCode, 1, 4 )
	) AS t ON ( t.OrganCode = m.OrganCode )
        ]]>
    </sql>

    <!-- 物业业态 -->
    <sql id="sqlSumPropertyFormat">
        <![CDATA[
SELECT
	isnull( b.PropertyForm, '未定义') AS PropertyForm,
	CONVERT (
	INT,
	SUM ( m.房屋数 )) AS room
	,
	CONVERT (
	INT,
	SUM ( m.面积 )) AS area
FROM
	[Rts].[dbo].[Tb_DeskTop_Resource] m
	LEFT JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] b ON ( b.CommID = m.CommID AND b.OrganCode = m.OrganCode AND b.IsDelete = 0 AND b.CommID <> 186098 )
        ]]>
    </sql>

    <sql id="sqlSumPropertyFormatGroupBy">
        <![CDATA[
GROUP BY
	isnull( b.PropertyForm, '未定义')
        ]]>
    </sql>

    <select id="sumPropertyFormatByUserCode"
            parameterType="string"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumPropertyFormat"/>
        <include refid="sqlInnerJoinUserRoleDataForOrgan"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                AND m.房屋数 > 0 AND m.CommID <> '-1'
                ]]>
            </if>
            <if test="organCode != null and organCode != ''">
                AND m.OrganCode = #{organCode}
            </if>
            <if test="commID != null and commID != ''">
                AND m.CommID = #{commID}
            </if>
        </where>

        <include refid="sqlSumPropertyFormatGroupBy"/>
    </select>

    <!-- 开发主体 -->
    <sql id="sqlSumDevelopmentSubject">
        <![CDATA[
SELECT
	isnull( b.DevelopmentSubject, '其他' ) AS DevelopmentSubject,
	CONVERT (
	INT,
	SUM ( m.房屋数 )) AS room,
	CONVERT (
	INT,
	SUM ( m.面积 )) AS area
FROM
	[Rts].[dbo].[Tb_DeskTop_Resource] m
	LEFT JOIN [HM_wygl_new_1853].[dbo].[Tb_HSPR_BasicInfo] b ON ( b.CommID = m.CommID AND b.OrganCode = m.OrganCode AND b.IsDelete = 0 AND b.CommID <> 186098 )
        ]]>
    </sql>

    <sql id="sqlSumDevelopmentSubjectGroupBy">
        <![CDATA[
GROUP BY
	isnull(b.DevelopmentSubject, '其他')
        ]]>
    </sql>

    <select id="sumDevelopmentSubjectByUserCode"
            parameterType="Map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumDevelopmentSubject"/>
        <include refid="sqlInnerJoinUserRoleDataForOrgan"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                AND m.房屋数 > 0 AND m.CommID <> '-1'
                ]]>
            </if>
            <if test="organCode != null and organCode != ''">
                AND m.OrganCode = #{organCode}
            </if>
            <if test="commID != null and commID != ''">
                AND m.CommID = #{commID}
            </if>
        </where>
        <include refid="sqlSumDevelopmentSubjectGroupBy"/>
    </select>

    <sql id="sqlSumByProvince">
        <![CDATA[
SELECT
	m.Province,
	isnull( SUM ( m.面积 ), 0 ) AS area,
	COUNT ( 1 ) AS comms
FROM
	[Rts].[dbo].[Tb_DeskTop_Resource] AS m
        ]]>
    </sql>

    <sql id="sqlSumByProvinceGroupBy">
        <![CDATA[
GROUP BY
	m.Province
ORDER BY
	area DESC
        ]]>
    </sql>

    <select id="sumByUserCodeByProvince"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResource">
        <include refid="sqlSumByProvince"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        <where>
            <if test="organCode != null and organCode != ''">
                AND m.organcode = #{organCode}
            </if>

            <if test="commID != null and commID != ''">
                AND m.CommID = #{commID}
            </if>
        </where>
        <include refid="sqlSumByProvinceGroupBy"/>
    </select>

</mapper>