<?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.InvestRegionInvestmentMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
		type="cn.com.countrygarden.bi.main.model.investment.InvestRegionInvestment">
		<id column="id" property="id" />
		<result column="bip_user_account" property="bipUserAccount" />
		<result column="user_name" property="userName" />
		<result column="region_id" property="regionId" />
		<result column="region_name" property="regionName" />
		<result column="project_name" property="projectName" />
		<result column="project_id" property="projectId" />
		<result column="follow_capital" property="followCapital" />
		<result column="follow_time" property="followTime" />
		<result column="follow_capital_account"
			property="followCapitalAccount" />
		<result column="follow_capital_account_time"
			property="followCapitalAccountTime" />
		<result column="financial_income_capital"
			property="financialIncomeCapital" />
		<result column="financial_income_capital_account"
			property="financialIncomeCapitalAccount" />
		<result column="raising_balance_capital"
			property="raisingBalanceCapital" />
		<result column="raising_balance_capital_account"
			property="raisingBalanceCapitalAccount" />
		<result column="available_balance_capital"
			property="availableBalanceCapital" />
		<result column="available_balance_capital_account"
			property="availableBalanceCapitalAccount" />
		<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="selectInvestRegionInvestmentlist"
		resultMap="BaseResultMap" parameterType="String">

		select i.id ,
		i.bip_user_account ,
		i.user_name ,
		i.region_id ,
		i.region_name ,
		i.project_name,
		i.project_id,
		i.follow_capital,
		i.follow_time ,
		i.follow_capital_account ,
		i.follow_capital_account_time ,
		i.financial_income_capital,
		i.financial_income_capital_account ,
		i.raising_balance_capital ,
		i.raising_balance_capital_account ,
		i.available_balance_capital ,
		i.available_balance_capital_account ,
		i.create_by ,
		i.create_time ,
		i.update_by ,
		i.update_time ,
		i.flag
		from invest_region_investment i LEFT
		JOIN invest_region_total t on i.region_id=t.id where 1=1
		<if test="regionId!=null and regionId!=''">
			and i.region_id =#{regionId}
		</if>
		<if test="userName!=null and userName!=''">
			and i.user_name like concat('%',#{userName},'%')
		</if>
		<if test="organCode!='01'">
			and t.region_id =#{organCode}

		</if>

		group by i.id desc

	</select>
	<select id="selectInvestExcelVO"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectExcelVO">
		select distinct
		i.id regionInvestmentId,
		i.region_id
		regionId,i.project_name,
		i.user_name userName,
		i.region_name regionName,
		p.project_name
		projectName,
		i.follow_capital followCapital,
		i.financial_income_capital
		financialIncomeCapital,
		i.financial_income_capital_account
		financialIncomeCapitalAccount,
		i.raising_balance_capital
		raisingBalanceCapital,
		i.raising_balance_capital_account
		raisingBalanceCapitalAccount,
		i.available_balance_capital
		availableBalanceCapital,
		i.available_balance_capital_account
		availableBalanceCapitalAccount,
		i.follow_time followTime,
		i.follow_capital_account
		followCapitalAccount,
		i.follow_capital_account_time
		followCapitalAccountTime,
		p.property_distribution_capital
		propertyDistributionCapital,
		p.property_distribution_capital_account
		propertyDistributionCapitalAccount,
		p.issued_capital issuedCapital,
		p.issued_capital_account issuedCapitalAccount,
		p.unissued_capital
		unissuedCapital,
		p.unissued_capital_account unissuedCapitalAccount,
		p.region_follow_capital regionFollowCapital,
		p.region_follow_capital_account regionFollowCapitalAccount,
		p.cash_return_capital cashReturnCapital,
		p.cash_return_capital_account
		cashReturnCapitalAccount,
		p.profit_distribution_capital
		profitDistributionCapital,
		p.profit_distribution_capital_account
		profitDistributionCapitalAccount,
		p.principal_return_capital
		principalReturnCapital,
		p.principal_return_capital_account
		principalReturnCapitalAccount,
		p.principal_notrecovered_capital
		principalNotrecoveredCapital,
		p.principal_notrecovered_capital_account
		principalNotrecoveredCapitalAccount
		from invest_region_investment i
		LEFT JOIN invest_project_profit p on i.id=p.person_investment_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>
		<if test="investRegionInvestment!=null">
			and i.id=#{investRegionInvestment}
		</if>
		order by i.region_id,i.id,i.project_name
	</select>
<select id="selectInvestAppInfo"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectExcelVO">
		select distinct
		i.id regionInvestmentId,
		i.region_id
		regionId,i.project_name,
		i.user_name userName,
		i.region_name regionName,
		p.project_name
		projectName,
		i.follow_capital followCapital,
		i.financial_income_capital
		financialIncomeCapital,
		i.financial_income_capital_account
		financialIncomeCapitalAccount,
		i.raising_balance_capital
		raisingBalanceCapital,
		i.raising_balance_capital_account
		raisingBalanceCapitalAccount,
		i.available_balance_capital
		availableBalanceCapital,
		i.available_balance_capital_account
		availableBalanceCapitalAccount,
		i.follow_time followTime,
		i.follow_capital_account
		followCapitalAccount,
		i.follow_capital_account_time
		followCapitalAccountTime,
		p.property_distribution_capital
		propertyDistributionCapital,
		p.property_distribution_capital_account
		propertyDistributionCapitalAccount,
		p.issued_capital issuedCapital,
		p.issued_capital_account issuedCapitalAccount,
		p.unissued_capital
		unissuedCapital,
		p.unissued_capital_account unissuedCapitalAccount,
		p.region_follow_capital regionFollowCapital,
		p.region_follow_capital_account regionFollowCapitalAccount,
		p.cash_return_capital cashReturnCapital,
		p.cash_return_capital_account
		cashReturnCapitalAccount,
		p.profit_distribution_capital
		profitDistributionCapital,
		p.profit_distribution_capital_account
		profitDistributionCapitalAccount,
		p.principal_return_capital
		principalReturnCapital,
		p.principal_return_capital_account
		principalReturnCapitalAccount,
		p.principal_notrecovered_capital
		principalNotrecoveredCapital,
		p.principal_notrecovered_capital_account
		principalNotrecoveredCapitalAccount
		from invest_region_investment i
		LEFT JOIN invest_project_profit p on i.id=p.person_investment_id
		where
		p.region_follow_capital>0
		<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>
		<if test="investRegionInvestment!=null">
			and i.id=#{investRegionInvestment}
		</if>
		order by i.region_id,i.id,i.project_name
	</select>

	<select id="findApp" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectAppVO">

		select i.id id,
		i.bip_user_account
		bipUserAccount,i.user_name userName,i.follow_capital
		followCapital,i.follow_capital_account followCapitalAccount,
		i.financial_income_capital
		financialIncomeCapital,i.financial_income_capital_account
		financialIncomeCapitalAccount,
		i.available_balance_capital
		availableBalanceCapital,i.available_balance_capital_account
		availableBalanceCapitalAccount,sum(p.region_follow_capital)
		regionFollowCapital,sum(p.region_follow_capital_account)

		regionFollowCapitalAccount
		from invest_region_investment i
		left join
		invest_project_profit p on p.person_investment_id=i.id
		where
		i.bip_user_account=#{bipUserAccount} group by i.id


	</select>
	<select id="selectInvestExcelVOSum" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestRegionInvestmentVO">
		select
		sum(p.property_distribution_capital)
		propertyDistributionCapitalTotal,
		sum(p.unissued_capital)
		unissuedCapitalTotal,sum(p.unissued_capital_account)
		unissuedCapitalAccountTotal,
		sum(p.property_distribution_capital_account)
		propertyDistributionCapitalAccountTotal,
		sum(p.region_follow_capital)
		regionFollowCapitalTotal,sum(p.region_follow_capital_account)
		regionFollowCapitalAccountTotal, sum(p.principal_return_capital)
		principalReturnCapitalTotal,
		sum(p.principal_return_capital_account)
		principalReturnCapitalAccountTotal,
		sum(p.profit_distribution_capital)
		profitDistributionCapitalTotal,
		sum(p.profit_distribution_capital_account)
		profitDistributionCapitalAccountTotal,
		sum(p.issued_capital)
		issuedCapitalTotal,sum(p.issued_capital_account)
		issuedCapitalAccountTotal,
		sum(p.cash_return_capital)
		cashReturnCapitalTotal,sum(p.cash_return_capital_account)
		cashReturnCapitalAccountTotal,
		sum(p.principal_notrecovered_capital)
		principalNotrecoveredCapitalTotal,
		sum(p.principal_notrecovered_capital_account)
		principalNotrecoveredCapitalAccountTotal,
		sum(i.follow_capital)
		followCapitalTotal,
		sum(i.follow_capital_account)
		followCapitalAccountTotal,
		sum(i.financial_income_capital)
		financialIncomeCapitalTotal,
		sum(i.financial_income_capital_account)
		financialIncomeCapitalAccountTotal,
		sum(i.raising_balance_capital)
		raisingBalanceCapitalTotal,
		sum(i.raising_balance_capital_account)
		raisingBalanceCapitalAccountTotal,
		sum(i.available_balance_capital)
		availableBalanceCapitalTotal,
		sum(i.available_balance_capital_account)
		availableBalanceCapitalAccountTotal
		from invest_project_profit p
		left
		join invest_region_investment i on i.id=p.person_investment_id
		where
		1=1
		<if test="regionIds!=null">
			and i.region_id in

			<foreach collection="regionIds" item="regionIds"
				index="index" open="(" close=")" separator=",">

				#{regionIds}

			</foreach>
		</if>
		<if test="personInvestmentId!=null and personInvestmentId!=''">
			and person_investment_id=#{personInvestmentId}
		</if>
	</select>

	<select id="selectInvestExcelVOTotal" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectExcelVO">
		select
		sum(i.follow_capital) followCapital,
		sum(i.follow_capital_account) followCapitalAccount,
		sum(i.financial_income_capital)
		financialIncomeCapital,
		sum(i.financial_income_capital_account)
		financialIncomeCapitalAccount,
		sum(i.raising_balance_capital)
		raisingBalanceCapital,
		sum(i.raising_balance_capital_account)
		raisingBalanceCapitalAccount,
		sum(i.available_balance_capital)
		availableBalanceCapital,
		sum(i.available_balance_capital_account)
		availableBalanceCapitalAccount
		from invest_region_investment i 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>
	</select>

	<select id="findRegionInvestmentInfo" resultMap="BaseResultMap"
		parameterType="String">
		SELECT DISTINCT
		i.id ,
		i.bip_user_account ,
		i.user_name ,
		i.region_id ,
		i.region_name ,
		i.project_name,
		i.project_id,
		i.follow_capital,
		i.follow_time ,
		i.follow_capital_account ,
		i.follow_capital_account_time ,
		i.financial_income_capital,
		i.financial_income_capital_account ,
		i.raising_balance_capital ,
		i.raising_balance_capital_account ,
		i.available_balance_capital ,
		i.available_balance_capital_account ,
		i.create_by ,
		i.create_time ,
		i.update_by ,
		i.update_time ,
		i.flag
		FROM
		invest_project_account a
		LEFT
		JOIN invest_project_investment p ON p.id
		=
		a.invest_project_investment_id
		LEFT JOIN invest_region_investment i ON
		i.id =
		a.invest_region_investment_id
		WHERE
		1 = 1
		AND i.region_id
		=#{regionId}
		And p.project_name=#{projectName}


	</select>

</mapper>