<?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.investment.InvestRegionTotalMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
		type="cn.com.countrygarden.bi.main.model.investment.InvestRegionTotal">
		<id column="id" property="id" />
		<result column="investment_name" property="investmentName" />
		<result column="region_name" property="regionName" />
		<result column="region_id" property="regionId" />
		<result column="region_amount_raised"
			property="regionAmountRaised" />
		<result column="single_amount_raised"
			property="singleAmountRaised" />
		<result column="user_name" property="userName" />
		<result column="create_by" property="createBy" />
		<result column="create_time" property="createTime" />
		<result column="update_by" property="updateBy" />
		<result column="update_time" property="updateTime" />
		<result column="flag" property="flag" />
	</resultMap>
	<select id="findAllInvestRegionTotalIds" parameterType="String"
		resultType="String">
		select id from invest_region_total where region_id in
		<foreach collection="regionIds" item="regionIds" index="index"
			open="(" close=")" separator=",">
			#{regionIds}
		</foreach>
	</select>
	<select id="findInvestRegionExcelSummaryVo"
		parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionExcelSummaryVo">
		select
		sum(i.profit_distribution_capital) incomeCapitalSummary,
		sum(i.profit_distribution_capital_account)
		incomeCapitalAccountSummary,
		sum(i.issued_income_capital)
		issuedIncomeCapitalSummary,
		sum(
		i.issued_income_capital_account
		)
		issuedIncomeCapitalAccountSummary,
		sum(
		i.project_follow_total_capital
		)
		projectFollowTotalCapitalSummary,
		sum(i.project_follow_capital)
		projectFollowCapitalSummary,
		sum(
		i.project_follow_single_capital
		)
		projectFollowSingleCapitalSummary,
		sum(i.cash_return_money)
		cashReturnMoneySummary,
		sum(i.cash_return_capital)
		cashReturnCapitalSummary,
		sum(
		i.cash_return_capital_account
		)
		cashReturnCapitalAccountSummary,
		sum(i.principal_return_money)
		principalReturnMoneySummary,
		sum(i.principal_return_capital)
		principalReturnCapitalSummary,
		sum(
		i.principal_return_capital_account
		)
		principalReturnCapitalAccountSummary,
		sum(
		i.profit_distribution_capital
		) profitDistributionCapitalSummary,
		sum(
		i.profit_distribution_capital_account
		)
		profitDistributionCapitalAccountSummary,
		sum(
		i.principal_notrecovered_capital
		) principalNotrecoveredCapitalSummary,
		sum(
		i.principal_notrecovered_capital_account
		)
		principalNotrecoveredCapitalAccountSummary,
		sum(i.financial_income_capital) financialIncomeCapitalSummary,
		sum(
		i.financial_income_capital_account
		)
		financialIncomeCapitalAccountSummary
		from
		invest_project_investment i
		left join invest_region_total t ON t.id = i.region_id where 1=1
		<if test="organCode!=null">
			and i.region_id={organCode}

		</if>
		<if test="regionName!=null">

			and i.region_name like concat('%','#{regionName}','%')  
		</if>
		<if test="regionId!=null">

			and i.region_id =#{regionId}
		</if>
		<if test="regionIds !=null">
			and i.region_id in
			<foreach collection="regionIds" item="regionIds"
				index="index" open="(" close=")" separator=",">
				#{regionIds}
			</foreach>

		</if>
	</select>

	<select id="selectRegionName" resultMap="BaseResultMap">
		select distinct
		region_id,investment_name,flag from invest_region_total

	</select>

	<select id="selectRegionTotalTreeByOrganCode"
		resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO">
		select distinct region_id regionId,region_name name from
		invest_region_total
		t WHERE t.investment_name = #{regionName}

	</select>
	<select id="getRegionList" resultMap="BaseResultMap"
		parameterType="String">

		select
		id,investment_name,region_name from invest_region_total
		<if test="organCode!='01'">
			region_id=#{organCode}
		</if>

	</select>
	<select id="findInvestRegionTotalVo"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionTotalVo"
		parameterType="String">

		select sum(financial_income_capital)
		financialIncomeCapitalRegionTotal,sum(financial_income_capital_account)
		financialIncomeCapitalAccountRegionTotal
		from invest_project_investment
		where region_id=#{regionId}
	</select>

	<select id="findAllRegionInvestment" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionTotalDTO">
		select id id,investment_name investmentName ,region_name regionName,
		region_id regionId,region_amount_raised regionAmountRaised,
		single_amount_raised singleAmountRaised,user_name userName, create_by
		createBy,create_time
		createTime,
		update_by updateBy,update_time
		updateTime,flag flag
		from
		invest_region_total where 1=1
		<if test="regionIds!=null and regionIds!=''">
			and region_id in
			<foreach collection="regionIds" item="regionIds"
				index="index" open="(" close=")" separator=",">

				#{regionIds}

			</foreach>

		</if>
		<if test="organCode!='01'">
			and region_id=#{organCode}
		</if>
		order by id desc
	</select>


	<select id="selectInvestRegionTotalDTO" parameterType="int"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionTotalDTO">
		select id id,investment_name investmentName ,region_name
		regionName,user_name userName,
		region_id regionId,region_amount_raised
		regionAmountRaised,
		single_amount_raised singleAmountRaised,
		create_by
		createBy,create_time
		createTime,
		update_by updateBy,update_time
		updateTime,flag flag FROM
		invest_region_total
		WHERE id =#{id}

	</select>

	<select id="findInvestRegionExcelVo"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionExcelVo"
		parameterType="String">
		select i.id id,i.region_id regionId,r.region_name
		regionName,i.project_id
		projectId,i.project_name
		projectName,i.income_capital incomeCapital,
		i.income_capital_account
		incomeCapitalAccount,i.issued_income_capital
		issuedIncomeCapital,
		i.issued_income_capital_time
		issuedIncomeCapitalTime,
		i.issued_income_capital_account
		issuedIncomeCapitalAccount,i.issued_income_capital_account_time
		issuedIncomeCapitalAccountTime,
		i.project_follow_total_capital
		projectFollowTotalCapital,
		i.project_follow_capital
		projectFollowCapital,
		i.project_follow_capital_time
		projectFollowCapitalTime,
		i.project_follow_single_capital
		projectFollowSingleCapital,i.project_follow_single_capital_time
		projectFollowSingleCapitalTime,
		i.cash_return_money
		cashReturnMoney,i.cash_return_capital
		cashReturnCapital
		,i.cash_return_capital_account
		cashReturnCapitalAccount,i.cash_return_time cashReturnTime,
		principal_return_money principalReturnMoney,i.principal_return_capital
		principalReturnCapital,
		i.principal_return_capital_account
		principalReturnCapitalAccount,
		i.principal_return_time
		principalReturnTime,i.profit_distribution_capital
		profitDistributionCapital,
		i.profit_distribution_capital_time
		profitDistributionCapitalTime,
		i.project_follow_single_capital_time
		projectFollowSingleCapitalTime,
		i.profit_distribution_capital_account
		profitDistributionCapitalAccount,
		i.profit_distribution_capital_account_time
		profitDistributionCapitalAccountTime,
		i.principal_notrecovered_capital
		principalNotrecoveredCapital,
		i.principal_notrecovered_capital_account
		principalNotrecoveredCapitalAccount,
		i.financial_income_capital
		financialIncomeCapital,
		i.financial_income_capital_time
		financialIncomeCapitalTime,
		i.financial_income_capital_account
		financialIncomeCapitalAccount,
		i.financial_income_capital_account_time
		financialIncomeCapitalAccountTime,r.region_amount_raised
		regionAmountRaised,r.single_amount_raised singleAmountRaised from
		invest_project_investment i left join invest_region_total r on
		r.id=i.region_id where 1=1
		<if test="organCode!=null">
			and i.region_id=#{organCode}
		</if>
		<if test="regionIds!=null">
			and i.region_id in
			<foreach collection="regionIds" item="regionIds"
				index="index" open="(" close=")" separator=",">

				#{regionIds}

			</foreach>
		</if>
		order by i.region_id,i.project_name

	</select>

	<select id="findAllInvestRegionToTotal" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.model.investment.InvestRegionTotal">
		select sum(region_amount_raised) regionAmountRaised,
		sum(single_amount_raised) singleAmountRaised

		from invest_region_total
		where id in(SELECT DISTINCT  region_id from invest_project_investment )
		<if test="organCode!=null">
			and region_id=#{organCode}
		</if>
		<if test="regionIds!=null">
			and region_id in
			<foreach collection="regionIds" item="regionIds"
				index="index" open="(" close=")" separator=",">
				#{regionIds}
			</foreach>
		</if>


	</select>


</mapper>