<?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.financial.SubbalanceMapper">
    <insert id="insertOrUpdate" parameterType="java.util.List" >
            INSERT INTO subbalance 
            (adjustperiod,assid,assValue,b_orient,begin_amou,creditamount,debitamount,sum_credit,sum_debit,end_amoumt,e_orient,period,
            pk_accasoa,accasoa_nam,pk_accountin,accountingbookName,accountingbookCode,year) 
            VALUES  
            <foreach collection="sub" item="bdlist"  separator=",">         
            (#{bdlist.adjustperiod},#{bdlist.assid},#{bdlist.assValue},#{bdlist.b_orient},
            #{bdlist.begin_amou},#{bdlist.creditamount},#{bdlist.debitamount},#{bdlist.sum_credit},
            #{bdlist.sum_debit},#{bdlist.end_amoumt},#{bdlist.e_orient},#{bdlist.period},
             #{bdlist.pk_accasoa},#{bdlist.accasoa_nam},#{bdlist.pk_accountin},#{bdlist.accountingbookName},
              #{bdlist.accountingbookCode},#{bdlist.year})             
            </foreach>                      
    </insert>
    <!--获取项目的成本数据-->
    <select id="selectProjectCost" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,assValue,creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode = #{codo}
        AND (accasoa_nam like '6401%'
        OR accasoa_nam like '6601%'
        OR accasoa_nam like '6602%'
        OR accasoa_nam like '6603%')
        <if test="flag">
            AND assValue NOT LIKE '%区域办公室%'
        </if>
        <if test="!flag">
            AND assValue LIKE '%区域办公室%'
        </if>
    </select>
    <!--获取区域的成本数据-->
    <select id="selectRegionCost" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        SELECT period,assValue,creditamount,accasoa_nam FROM subbalance WHERE year = #{year}
        AND accountingbookCode IN
        <foreach item="codo" index="index" collection="codoList" open="(" separator="," close=")">
            #{codo}
        </foreach>
        AND (accasoa_nam LIKE '6401%'
        OR accasoa_nam LIKE '6601%'
        OR accasoa_nam LIKE '6602%'
        OR accasoa_nam LIKE '6603%')
        <if test="flag">
            AND assValue NOT LIKE '%区域办公室%'
        </if>
        <if test="!flag">
            AND assValue LIKE '%区域办公室%'
        </if>
    </select>
    <!--获取总部的成本数据-->
    <select id="selectHeadquartersCost" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,assValue,creditamount,accasoa_nam from subbalance where year = #{year}
        and (accasoa_nam like '6401%'
        or accasoa_nam like '6601%'
        or accasoa_nam like '6602%'
        or accasoa_nam like '6603%')
        <if test="flag">
            AND assValue NOT LIKE '%区域办公室%'
        </if>
        <if test="!flag">
            AND assValue LIKE '%区域办公室%'
        </if>
    </select>
    <!--获取区域成本数据 税金及附加 6403 营业外收入 6301  营业外支出6711-->
    <select id="selectRegionTaxOutInCome" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode IN
        <foreach item="codo" index="index" collection="codoList" open="(" separator="," close=")">
            #{codo}
        </foreach>
        AND (accasoa_nam like '6403%'
        OR accasoa_nam like '6301%'
        OR accasoa_nam like '6711%')
    </select>
    <!--获取项目成本数据 税金及附加 6403 营业外收入 6301  营业外支出6711-->
    <select id="selectProjectTaxOutInCome" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select assValue,period,creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode = #{codo}
        AND (accasoa_nam like '6403%'
        OR accasoa_nam like '6301%'
        OR accasoa_nam like '6711%')
    </select>
    <!--获取总部成本数据 税金及附加 6403 营业外收入 6301  营业外支出6711-->
    <select id="selectHeadquartersTaxOutInCome" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select assValue,period,creditamount,accasoa_nam from subbalance where year = #{year}
        AND (accasoa_nam like '6403%'
        OR accasoa_nam like '6301%'
        OR accasoa_nam like '6711%')
    </select>

    <insert id="insertSubbalance" parameterType="cn.com.countrygarden.bi.main.model.financial.Subbalance" >
        INSERT INTO subbalance
        (adjustperiod,assid,assValue,b_orient,begin_amou,creditamount,debitamount,sum_credit,sum_debit,end_amoumt,e_orient,period,
        pk_accasoa,accasoa_nam,pk_accountin,accountingbookName,accountingbookCode,year)
        VALUES
        (#{adjustperiod},
        #{assid},
        #{assValue},
        #{b_orient},
        #{begin_amou},
        #{creditamount},
        #{debitamount},
        #{sum_credit},
        #{sum_debit},
        #{end_amoumt},
        #{e_orient},
        #{period},
        #{pk_accasoa},
        #{accasoa_nam},
        #{pk_accountin},
        #{accountingbookName},
        #{accountingbookCode},
        #{year})
    </insert>
    <select id="selectCodo" resultType="java.lang.String">
        SELECT
		ncsystemnumber
        FROM
            basicinfo
        WHERE
            IsDelete = 0
        GROUP BY
            NCSystemNumber
        HAVING
            count(DISTINCT CommID) > 1
    </select>
    <select id="selectSubblanceList" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        SELECT assValue FROM subbalance WHERE accountingbookCode = #{codo}
        and (accasoa_nam like '6401%'
            or accasoa_nam like '6601%'
            or accasoa_nam like '6602%'
            or accasoa_nam like '6603%')
    </select>
    <insert id="insertTempComm">
        INSERT INTO tempComm(assvalue,codo) VALUES(#{assvalue},#{codo})
    </insert>
    <!--获取区域汇总成本数据（assValue包含 区域办公室） 6401主营业务 6601销售费用 6602管理费用 -->
    <select id="selectRegionCostForProjectRow" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        SELECT period,assValue,creditamount,accasoa_nam FROM subbalance WHERE year = #{year}
        AND accountingbookCode IN
        <foreach item="codo" index="index" collection="codoList" open="(" separator="," close=")">
            #{codo}
        </foreach>
        AND (accasoa_nam LIKE '6401%'
        OR accasoa_nam LIKE '6601%'
        OR accasoa_nam LIKE '6602%')
       AND assValue LIKE '%区域办公室%'
    </select>

    <!--获取项目主营业务收入数据 单codo对应 单项目-->
    <select id="selectProjectInComeForOne" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,sum(creditamount) as creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode = #{codo}
        AND accasoa_nam like '6001%'
		GROUP BY accasoa_nam,period
    </select>
    <!--获取项目主营业务收入数据 单codo对应 多项目-->
    <select id="selectProjectInComeForMany" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select assValue,period,creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode = #{codo}
        AND accasoa_nam like '6001%'
    </select>
    <!--获取区域收入数据 主营业务收入 -->
    <select id="selectRegionInCome" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,sum(creditamount) as creditamount,accasoa_nam from subbalance where year = #{year}
        AND accountingbookCode IN
        <foreach item="codo" index="index" collection="codoList" open="(" separator="," close=")">
            #{codo}
        </foreach>
        AND accasoa_nam like '6001%'
        GROUP BY accasoa_nam,period
    </select>
    <!--获取总部收入数据 主营业务收入-->
    <select id="selectHeadquartersInCome" resultType="cn.com.countrygarden.bi.main.model.financial.Subbalance">
        select period,sum(creditamount) as creditamount,accasoa_nam from subbalance where year = #{year}
        AND accasoa_nam like '6001%'
		GROUP BY accasoa_nam,period
    </select>
    <delete id="deleteSubbalance">
        DELETE FROM subbalance WHERE year = #{year,jdbcType=VARCHAR} and period = #{month,jdbcType=VARCHAR}
    </delete>
</mapper>