<?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.InvestProjectInvestmentMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
		type="cn.com.countrygarden.bi.main.model.investment.InvestProjectInvestment">
		<id column="id" property="id" />
		<result column="project_id" property="projectId" />
		<result column="region_id" property="regionId" />
		<result column="project_name" property="projectName" />
		<result column="region_name" property="regionName" />
		<result column="project_follow_single_capital_time"
			property="projectFollowSingleCapitalTime" />
		<result column="income_capital" property="incomeCapital" />
		<result column="income_capital_account"
			property="incomeCapitalAccount" />
		<result column="issued_income_capital"
			property="issuedIncomeCapital" />
		<result column="issued_income_capital_time"
			property="issuedIncomeCapitalTime" />
		<result column="issued_income_capital_account"
			property="issuedIncomeCapitalAccount" />
		<result column="issued_income_capital_account_time"
			property="issuedIncomeCapitalAccountTime" />
		<result column="project_follow_total_capital"
			property="projectFollowTotalCapital" />
		<result column="project_follow_capital_time"
			property="projectFollowCapitalTime" />
		<result column="project_follow_capital"
			property="projectFollowCapital" />
		<result column="project_follow_single_capital"
			property="projectFollowSingleCapital" />
		<result column="cash_return_money" property="cashReturnMoney" />
		<result column="cash_return_capital"
			property="cashReturnCapital" />
		<result column="cash_return_capital_account"
			property="cashReturnCapitalAccount" />
		<result column="cash_return_time" property="cashReturnTime" />
		<result column="principal_return_money"
			property="principalReturnMoney" />
		<result column="principal_return_capital"
			property="principalReturnCapital" />
		<result column="principal_return_capital_account"
			property="principalReturnCapitalAccount" />
		<result column="principal_return_time"
			property="principalReturnTime" />
		<result column="profit_distribution_capital"
			property="profitDistributionCapital" />
		<result column="profit_distribution_capital_time"
			property="profitDistributionCapitalTime" />
		<result column="profit_distribution_capital_account"
			property="profitDistributionCapitalAccount" />
		<result column="profit_distribution_capital_account_time"
			property="profitDistributionCapitalAccountTime" />
		<result column="principal_notrecovered_capital"
			property="principalNotrecoveredCapital" />
		<result column="principal_notrecovered_capital_account"
			property="principalNotrecoveredCapitalAccount" />
		<result column="financial_income_capital"
			property="financialIncomeCapital" />
		<result column="financial_income_capital_time"
			property="financialIncomeCapitalTime" />
		<result column="financial_income_capital_account"
			property="financialIncomeCapitalAccount" />
		<result column="financial_income_capital_account_time"
			property="financialIncomeCapitalAccountTime" />
		<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="selectAllRegionTree" resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO"> 
		select distinct region_id regionId,investment_name name from invest_region_total 
		</select> -->
	<select id="findProjectIdAndProjectName" parameterType="String"
		resultMap="BaseResultMap">
		select id,project_name from invest_project_investment
		where 1=1
		<if test="regionId!=null">
			and region_id=#{regionId}
		</if>
		order by project_name

	</select>
	<select id="selectAllRegionTree"
		resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO">
		select distinct region_id regionId,investment_name name
		from
		invest_region_total
	</select>
	<select id="selectAllProjectTree"
		resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO">
		select distinct id code, region_id regionId,investment_name
		name from
		invest_region_total
	</select>

	<select id="selectCountProject" resultType="int">
		select count(distinct
		project_name) from invest_project_investment where
		region_id=#{regionId}

	</select>
	<select id="findCondInvestProjectInvestment"
		parameterType="String" resultType="Integer">

		select count(id) from
		invest_project_account where
		invest_project_investment_id in (select id
		from
		invest_project_investment where project_name=#{projectName})


	</select>

	<select id="selectInvestProjectInvestmentAll"
		parameterType="String" resultMap="BaseResultMap">
		select
		i.id,
		i.project_id,
		i.region_id,
		i.project_name,
		t.region_name,
		i.income_capital,
		i.income_capital_account,
		i.issued_income_capital,
		i.issued_income_capital_time,
		i.issued_income_capital_account,
		i.issued_income_capital_account_time,
		i.project_follow_total_capital,
		i.project_follow_capital,
		i.project_follow_capital_time,
		i.project_follow_single_capital,
		i.project_follow_single_capital_time,
		i.cash_return_money,
		i.cash_return_capital,
		i.cash_return_capital_account,
		i.cash_return_time,
		i.principal_return_money,
		i.principal_return_capital,
		i.principal_return_capital_account,
		i.principal_return_time,
		i.profit_distribution_capital,
		i.profit_distribution_capital_time,
		i.profit_distribution_capital_account,
		i.profit_distribution_capital_account_time,
		i.principal_notrecovered_capital,
		i.principal_notrecovered_capital_account,
		i.financial_income_capital,
		i.financial_income_capital_time,
		i.financial_income_capital_account,
		i.financial_income_capital_account_time,
		i.create_by,
		i.create_time,
		i.update_by,
		i.update_time,
		i.flag
		from
		invest_project_investment i left
		join invest_region_total t on
		t.id=i.region_id
		where 1=1


		<if test="organCode!='01'">
			and t.region_id= #{organCode}
		</if>
		<if test="regionId!=null and regionId!=''">
			and i.region_id= #{regionId}
		</if>
		<if test="projectName!=null and projectName!=''">
			and i.project_name= #{projectName}
		</if>

		order by i.id desc
	</select>

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

		select distinct project_id projectId,region_id regionId,project_name
		projectName,region_name regionName from invest_project_investment
		where 1=1
		<if test="regionId!=null and regionId!=''">
			and region_id=#{regionId}
		</if>

	</select>
	<select id="selectProjectIdAndprojectName"
		parameterType="String" resultMap="BaseResultMap">
		select distinct project_id,
		project_name from invest_project_investment where
		region_id=#{regionId}
	</select>
	<select id="queryInvestProjectInvestmentVoExcel"
		parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectInvestmentVo">
		select sum(issued_income_capital)
		issuedIncomeCapitalTotal,sum(issued_income_capital_account)
		issuedIncomeCapitalAccountTotal,
		sum(project_follow_total_capital)
		projectFollowTotalCapitalTotal,sum(project_follow_capital)
		projectFollowCapitalTotal,
		sum(project_follow_single_capital)
		projectFollowSingleCapitalTotal,sum(cash_return_money)
		cashReturnMoneyTotal,sum(cash_return_capital) cashReturnCapitalTotal,
		sum(cash_return_capital_account)
		cashReturnCapitalAccountTotal,sum(principal_return_money)
		principalReturnMoneyTotal,
		sum(principal_return_capital)
		principalReturnCapitalTotal,sum(principal_return_capital_account)
		principalReturnCapitalAccountTotal,
		sum(profit_distribution_capital)
		profitDistributionCapitalTotal,sum(profit_distribution_capital_account)
		profitDistributionCapitalAccountTotal,
		sum(principal_notrecovered_capital)
		principalNotrecoveredCapitalTotal,sum(principal_notrecovered_capital_account)
		principalNotrecoveredCapitalAccount,
		sum(profit_distribution_capital)
		incomeCapitalTotal,sum(profit_distribution_capital_account )
		incomeCapitalAccountTotal,
		sum(financial_income_capital )
		financialIncomeCapitalTotal,sum(financial_income_capital_account)
		financialIncomeCapitalAccountTotal

		from invest_project_investment
		where
		project_name=#{projectName}
		and region_id=#{regionId}

	</select>


	<select id="findInvestProjectInvestmentVoData"
		parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.investment.InvestProjectInvestmentVo">
		select
		sum(income_capital)
		incomeCapitalTotal,sum(income_capital_account )
		incomeCapitalAccountTotal,
		sum(issued_income_capital )
		issuedIncomeCapitalTotal,sum(issued_income_capital_account)
		issuedIncomeCapitalAccountTotal,
		sum(project_follow_capital )
		projectFollowCapitalTotal,sum(project_follow_single_capital)
		projectFollowSingleCapitalTotal

		from invest_project_investment
		where
		region_id=#{regionId}

	</select>
	<select id="findInvestProjectInvestmentAll"
		parameterType="String">

		SELECT
		id,project_id,region_id,project_name,region_name,income_capital,income_capital_account,issued_income_capital,issued_income_capital_time,issued_income_capital_account,issued_income_capital_account_time,project_follow_total_capital,project_follow_capital,project_follow_capital_time,project_follow_single_capital,project_follow_single_capital_time,cash_return_money,cash_return_capital,cash_return_capital_account,cash_return_time,principal_return_money,principal_return_capital,principal_return_capital_account,principal_return_time,profit_distribution_capital,profit_distribution_capital_time,profit_distribution_capital_account,profit_distribution_capital_account_time,principal_notrecovered_capital,principal_notrecovered_capital_account,financial_income_capital,financial_income_capital_time,financial_income_capital_account,financial_income_capital_account_time,create_by,create_time,update_by,update_time,flag
		FROM invest_project_investment WHERE 1=1
		<if test="regionId!=null">

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

				#{projectIds}

			</foreach>

		</if>


	</select>




	<select id="findProjectInvestmentRegionTreeDTO"
		parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO">

		select distinct region_id code, region_id regionId,region_name name
		from invest_project_investment
		<if test="regionId!=null and regionId!=''">
			where region_id = #{regionId}
		</if>
	</select>


	<select id="findProjectInvestmentProjectTreeDTO"
		resultType="cn.com.countrygarden.bi.main.dto.ManagerMapOrganTreeDTO"
		parameterType="String">
		select distinct region_id regionId,project_id code,
		project_name name from
		invest_project_investment where region_id =
		#{regionId}

	</select>
	<select id="findProjectAccount" parameterType="String"
		resultMap="BaseResultMap">
		SELECT DISTINCT p.id,p.project_name FROM invest_project_account a
		LEFT
		JOIN invest_project_investment p on
		p.id=a.invest_project_investment_id
		LEFT JOIN invest_region_investment
		r on r.id=a.invest_region_investment_id
		where 1=1
		<if test="regionId!=null">
			and r.region_id=#{regionId}
		</if>
		order by p.project_name

	</select>

	<select id="findInvestProjectInvestment"
		resultType="cn.com.countrygarden.bi.main.model.investment.InvestProjectInvestment"
		parameterType="map">
		SELECT DISTINCT
		t.project_name projectName, (
		SELECT
		MIN(id)
		FROM
		invest_project_investment where t.project_name = project_name
		) id

		FROM invest_project_investment t
		where 1=1
		<if test="map.projectName!=null and map.projectName!=''">
			and t.project_name=#{map.projectName}

		</if>
		<if test="map.regionId!=null and map.regionId!=''">
			and t.region_id=#{map.regionId}

		</if>

	</select>

</mapper>