<?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.TbDesktopResourceRoomStateMapper">
    <sql id="sqlSumAll">
    <![CDATA[
        SELECT
            s.StateName,
            SUM ( s.RoomNum ) AS RoomNum,
            SUM ( s.RoomBuildArea ) AS RoomBuildArea
        FROM
            [Rts].[dbo].[Tb_DeskTop_Resource_RoomState] AS s
        ]]>
    </sql>

    <sql id="sqlSumAllGroupBy">
        <![CDATA[
        GROUP BY
            s.StateName
        ]]>
    </sql>

    <select id="sumByUserCode"
            parameterType="string"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResourceRoomState">
        <include refid="sqlSumAll"/>
        <![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 = s.OrganCode AND t.CommID = s.CommID )
        ]]>
        <include refid="sqlSumAllGroupBy"/>
    </select>

    <select id="sumByUserCodeAndOrganCode"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResourceRoomState">
        <include refid="sqlSumAll"/>
        <![CDATA[
	INNER JOIN (
SELECT
	u.UserCode,
	c.OrganCode,
	d.CommID,
	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 ur ON ( ur.UserCode = u.UserCode AND u.IsDelete = 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_Role] AS r ON r.RoleCode = ur.RoleCode
	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 = s.organcode AND t.CommID = s.CommID )
WHERE s.OrganCode = #{organCode}
        ]]>
        <include refid="sqlSumAllGroupBy"/>
    </select>

    <select id="sumByUserCodeAndOrganCodeAndCommID"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktopResourceRoomState">
        <include refid="sqlSumAll"/>
        <![CDATA[
	INNER JOIN (
SELECT
	u.UserCode,
	c.OrganCode,
	d.CommID,
	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 ur ON ( ur.UserCode = u.UserCode AND u.IsDelete = 0 )
	INNER JOIN [HM_wygl_new_1853].[dbo].[Tb_Sys_Role] AS r ON r.RoleCode = ur.RoleCode
	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 = s.organcode AND t.CommID = s.CommID )
WHERE s.OrganCode = #{organCode} AND s.CommID = #{commID}
        ]]>
        <include refid="sqlSumAllGroupBy"/>
    </select>
</mapper>