<?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 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.OrganCode <> '-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} and u.rentid=1853 and r.rentid=1853 and d.rentid=1853 and o.rentid=1853
GROUP BY
	SUBSTRING ( d.OrganCode, 1, 4 )
	) AS t ON ( t.OrganCode = m.OrganCode )
        ]]>
    </sql>

    <select id="sumByUserCode"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktop经营主题收款方式">
        <![CDATA[
SELECT SUM
	( m.现金 ) AS 现金,
	SUM ( m.房产代付 ) AS 房产代付,
	SUM ( m.银行代扣 ) AS 银行代扣,
	SUM ( m.POS刷卡 ) AS POS刷卡,
	SUM ( m.房产支付空置房 ) AS 房产支付空置房,
	SUM ( m.其他 ) AS 其他
FROM
	Tb_DeskTop_经营主题_收款方式 AS m
        ]]>
        <include refid="sqlInnerJoinUserRoleData"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                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>
    </select>


    <!-- 区域收费排行 -->
    <select id="sumByOrganByUserCode"
            parameterType="map"
            resultType="cn.com.countrygarden.bi.main.model.twerp.hm.TbDesktop经营主题收款方式">
        <choose>
        <when test="isHeadquarters == 1">
        <![CDATA[
SELECT
	m.OrganName,
        ]]>
        </when>
        <otherwise>
            <![CDATA[
SELECT
	m.CommName AS OrganName,
            ]]>
        </otherwise>
        </choose>
<![CDATA[
	SUM ( m.现金 ) AS 现金,
	SUM ( m.房产代付 ) AS 房产代付,
	SUM ( m.银行代扣 ) AS 银行代扣,
	SUM ( m.POS刷卡 ) AS POS刷卡,
	SUM ( m.房产支付空置房 ) AS 房产支付空置房,
	SUM ( m.其他 ) AS 其他,
	SUM ( m.现金 + m.房产代付 + m.银行代扣 + m.POS刷卡 + m.房产支付空置房 + m.其他 ) AS total
FROM
	Tb_DeskTop_经营主题_收款方式 AS m
]]>
        <include refid="sqlInnerJoinUserRoleData"/>
        <where>
            <if test="1 == 1">
                <![CDATA[
                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>

        <choose>
            <when test="isHeadquarters == 1">
                <![CDATA[
GROUP BY
	m.OrganName
ORDER BY
	total DESC
        ]]>
            </when>
            <otherwise>
                <![CDATA[
GROUP BY
	m.CommName
ORDER BY
	total DESC
        ]]>
            </otherwise>
        </choose>
    </select>

</mapper>