<?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.TbDesktop本日新增Mapper">

    <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} and u.rentid=1853 and r.rentid=1853 and d.rentid=1853 and o.rentid=1853 and c.rentid=1853
	) AS t ON ( t.OrganCode = m.OrganCode AND t.CommID = m.CommID )
        ]]>
    </sql>


    <sql id="sqlSumHomeMainIncome">
        <![CDATA[
SELECT
	isnull( SUM (zrss), 0 ) AS zrss,
	isnull( SUM (zrzj), 0 ) AS zrzj,
	isnull( SUM (brcwsr), 0 ) AS brcwsr
FROM
	tb_desktop_本日新增 AS m
        ]]>
    </sql>
    <select id="sumHomeMainIncomeByUserCode"
            fetchSize="1"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktop本日新增">

        <include refid="sqlSumHomeMainIncome"/>
        <include refid="sqlInnerJoinUserRoleData"/>
    </select>
    <select id="sumHomeMainIncomeByUserCodeAndOrganCode"
            fetchSize="1"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktop本日新增">

        <include refid="sqlSumHomeMainIncome"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        WHERE m.OrganCode = #{organCode}
    </select>
    <select id="sumHomeMainIncomeByUserCodeAndOrganCodeAndCommID"
            fetchSize="1"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktop本日新增">

        <include refid="sqlSumHomeMainIncome"/>
        <include refid="sqlInnerJoinUserRoleData"/>
        WHERE m.OrganCode = #{organCode} AND m.CommID = #{commID}
    </select>

</mapper>