<?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="com.yifu.cloud.plus.v1.yifu.insurances.mapper.ekp.EkpSettleMapper">

	<resultMap id="BaseResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.SettleVo">
		<id property="id" column="fd_id" jdbcType="VARCHAR"/>
		<result property="detailId" column="fd_3b05ba46bb042e" jdbcType="VARCHAR"/>
		<result property="estimateStatus" column="fd_3b05ba4808a3ca" jdbcType="VARCHAR"/>
		<result property="actualStatus" column="fd_3b05bd5ed0b976" jdbcType="VARCHAR"/>
	</resultMap>

	<resultMap id="BaseDeptResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.DeptSettleVo">
		<id property="id" column="fd_id" jdbcType="VARCHAR"/>
		<result property="detailId" column="fd_3b0a5743acab7e" jdbcType="VARCHAR"/>
		<result property="settleStatus" column="fd_3adfe6ec6a8cbe" jdbcType="VARCHAR"/>
		<result property="paySettleStatus" column="fd_3adfe6eda67236" jdbcType="VARCHAR"/>
	</resultMap>

	<resultMap id="BaseResultStatusMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpInsuranceViewVo">
		<id property="id" column="detailsId" jdbcType="VARCHAR"/>
		<result property="incomeSettleFlag" column="earningStatus" jdbcType="VARCHAR"/>
		<result property="incomeCollectFlag" column="receiptStatus" jdbcType="VARCHAR"/>
		<result property="paySettleFlag" column="zhichuEarningStatus" jdbcType="VARCHAR"/>
		<result property="payCollectFlag" column="payStatus" jdbcType="VARCHAR"/>
		<result property="orderType" column="orderType" jdbcType="VARCHAR"/>
	</resultMap>

	<resultMap id="BaseResultSocialStatusMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpSocialViewVo">
		<id property="id" column="detailsId" jdbcType="VARCHAR"/>
		<result property="incomeSettleFlag" column="earningStatus" jdbcType="VARCHAR"/>
		<result property="incomeCollectFlag" column="receiptStatus" jdbcType="VARCHAR"/>
		<result property="paySettleFlag" column="zhichuEarningStatus" jdbcType="VARCHAR"/>
		<result property="payCollectFlag" column="payStatus" jdbcType="VARCHAR"/>
		<result property="settleFlag" column="orderType" jdbcType="VARCHAR"/>
	</resultMap>

	<sql id="Base_Column_List">
		fd_id,fd_3b05ba46bb042e,fd_3b05ba4808a3ca,fd_3b05bd5ed0b976
	</sql>

	<sql id="Base_Column_List_Status">
		detailsId,earningStatus,receiptStatus,zhichuEarningStatus,payStatus,orderType
	</sql>

	<sql id="Base_Column_Dept">
		fd_id,fd_3b0a5743acab7e,fd_3adfe6ec6a8cbe,fd_3adfe6eda67236
	</sql>

	<select id="getSettle" resultMap="BaseResultMap">
		select
			fd_id,
			fd_3b0a5743acab7e as fd_3b05ba46bb042e,
			MAX(case when fd_3adfe6af71a1cc = '预估' THEN fd_3adfe6ec6a8cbe END) AS fd_3b05ba4808a3ca,
			MAX(case when fd_3adfe6af71a1cc = '实缴' THEN fd_3adfe6ec6a8cbe END) AS fd_3b05bd5ed0b976
		from
			ekp_insurances_info
		where
			fd_3b0a5743acab7e = #{id}
		group by fd_3b0a5743acab7e
	</select>

	<select id="getSettleStatusFromEkpInsuranceDetail" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpSettleStatusVo">
		select
			fd_3adfe6ec6a8cbe settleStatus,fd_3adfe6eda67236 payStatus,fd_3adfe6ef5dfaac paymentStatus
		from
			ekp_insurances_info
		where
		fd_3b0a5743acab7e  like CONCAT(#{id},'%') and fd_3adfe6af71a1cc = '实缴' limit 1
	</select>

	<select id="getSettleStatusFromEkpAsso" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpSettleStatusVo">
		select
			fd_3adfe6ec6a8cbe settleStatus,fd_3adfe6eda67236 payStatus,fd_3adfe6ef5dfaac paymentStatus,fd_3b0a5743acab7e detailId
		from
			ekp_insurances_info
		where
			fd_3b0a5743acab7e = #{id} and fd_3adfe6af71a1cc = '预估' and fd_jfcd='是' limit 1
	</select>
	<select id="getSettleStatusFromEkpAssoByIds" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpSettleStatusVo">
		select
			fd_3adfe6ec6a8cbe settleStatus,fd_3adfe6eda67236 payStatus,fd_3adfe6ef5dfaac paymentStatus,fd_3b0a5743acab7e detailId,update_time_status statusUpdateTime
		from
			ekp_insurances_info
		where
			fd_3adfe6af71a1cc = '预估' and fd_jfcd='是'
		  <if test="ids != null and ids.size() > 0">
			  and fd_3b0a5743acab7e in
			  <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
				  #{id}
			  </foreach>
		  </if>

	</select>
	<select id="getBalanceByInsuranceId" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpSettleStatusVo">
		select
		fd_3adfe6ec6a8cbe settleStatus,fd_3adfe6eda67236 payStatus,fd_3adfe6ef5dfaac paymentStatus,fd_3b0a5743acab7e detailId,fd_3adfe6e30f2a3c paymentBalance
		from
		ekp_insurances_info
		where
		fd_3adfe6af71a1cc = '差额' and fd_jfcd='是' and
		fd_3b0a5743acab7e like CONCAT(#{id},'%')
	</select>
	<delete id="deleteEkpInsuranceDetail">
		delete from ekp_insurances_info where fd_3b0a5743acab7e=#{id} and fd_jfcd='是' and fd_3adfe6af71a1cc = '预估'
	</delete>

	<delete id="deleteEkpInsuranceDetailAsso">
		delete from ekp_insurances_info where fd_3b0a5743acab7e like CONCAT(#{id},'%')  and fd_jfcd='是' and (fd_3adfe6af71a1cc = '差额' or fd_3adfe6af71a1cc = '实缴')
	</delete>

	<delete id="callBackBalance">
		delete from ekp_insurances_info where fd_3b0a5743acab7e like CONCAT(#{id},'%')  and fd_jfcd='是' and fd_3adfe6af71a1cc = '差额'
	</delete>

	<delete id="deleteEkpManagerInfo">
		delete from ekp_manager_info where fd_3b13dc864a3052 = #{id}
	</delete>
	<delete id="deleteEkpEkpRiskInfo">
		delete from ekp_risk_info where fd_3b13db120a8224 = #{id}
	</delete>
	<select id="getDeptSettle" resultMap="BaseDeptResultMap">
		select
		<include refid="Base_Column_Dept"></include>
		from
		ekp_insurances_info
		where
		fd_3b0a5743acab7e = #{id} and fd_3adfe6af71a1cc = '实缴' limit 1
	</select>

	<select id="getDeptYgSettle" resultMap="BaseDeptResultMap">
		select
		<include refid="Base_Column_Dept"></include>
		from
		ekp_insurances_info
		where
		fd_3b0a5743acab7e = #{id} and fd_3adfe6af71a1cc = '预估' limit 1
	</select>

	<!-- 根据结算单编码获取视图信息 -->
	<select id="selectSettleStatusBySettleNo" resultMap="BaseResultStatusMap">
		select
			b.detailsId,
			if(b.earningStatus = '已结算','0',if(b.earningStatus = '结算中','1',if(b.earningStatus = '无需结算','3','2'))) earningStatus,
			if(b.receiptStatus = '已收','0',if(b.receiptStatus = '垫付','2','1')) receiptStatus,
			if(b.zhichuEarningStatus = '已结算','0',if(b.zhichuEarningStatus = '结算中','1',if(b.zhichuEarningStatus = '无需结算','3','2'))) zhichuEarningStatus,
			if(b.payStatus = '已付','0','1') payStatus,
			b.orderType
		from
			view_insurance_info b
		where
			b.detailsId is not null
			<if test="payFlag != null  and payFlag != '' and payFlag == 0">
				and b.earningNo = #{settleNo}
			</if>
			<if test="payFlag != null  and payFlag != '' and payFlag == 1">
				and b.zhichuEarningNo = #{settleNo}
			</if>
	</select>

	<!-- 根据结算单编码获取社保视图信息 -->
	<select id="selectSocialViewStatusBySettleNo" resultMap="BaseResultSocialStatusMap">
		select
			a.detailsId,
			if(a.earningStatus = '已结算','0',if(a.earningStatus = '结算中','1',if(a.earningStatus = '已冻结','3','2'))) earningStatus,
			if(a.receiptStatus = '已收','0',if(a.receiptStatus = '垫付','2','1')) receiptStatus,
			if(a.zhichuEarningStatus = '已结算','0',if(a.zhichuEarningStatus = '结算中','1','2')) zhichuEarningStatus,
			if(a.payStatus = '已付','0','1') payStatus,
			a.orderType
		from
			view_social_info a
		where
			a.detailsId is not null
			<if test="payFlag != null  and payFlag != '' and payFlag == 0">
				and a.earningNo = #{settleNo}
			</if>
			<if test="payFlag != null  and payFlag != '' and payFlag == 1">
				and a.zhichuEarningNo = #{settleNo}
			</if>
	</select>

	<!-- 根据结算单编码获取公积金视图信息 -->
	<select id="selectFundViewBySettleNo" resultMap="BaseResultSocialStatusMap">
		select
			a.detailsId,
			if(a.earningStatus = '已结算','0',if(a.earningStatus = '结算中','1',if(a.earningStatus = '已冻结','3','2'))) earningStatus,
			if(a.receiptStatus = '已收','0',if(a.receiptStatus = '垫付','2','1')) receiptStatus,
			if(a.zhichuEarningStatus = '已结算','0',if(a.zhichuEarningStatus = '结算中','1','2')) zhichuEarningStatus,
			if(a.payStatus = '已付','0','1') payStatus,
			a.orderType
		from
			view_funds_info a
		where
			a.detailsId is not null
			<if test="payFlag != null  and payFlag != '' and payFlag == 0">
				and a.earningNo = #{settleNo}
			</if>
			<if test="payFlag != null  and payFlag != '' and payFlag == 1">
				and a.zhichuEarningNo = #{settleNo}
			</if>
	</select>

	<!-- 根据结算单编码获取视图信息 -->
	<select id="selectFIncomelViewBySettleNo" resultMap="BaseResultStatusMap">
		select
			a.detailsId,
			if(a.earningStatus = '已结算','0',if(a.earningStatus = '结算中','1',if(a.earningStatus = '已冻结','3','2'))) earningStatus,
			if(a.receiptStatus = '已收','0',if(a.receiptStatus = '垫付','2','1')) receiptStatus
		from
			view_riskfund_info a
		where
			a.earningNo = #{settleNo}
	</select>

	<!-- 根据结算单编码获取视图信息 -->
	<select id="selectIncomeViewBySettleNo" resultMap="BaseResultStatusMap">
		select
			a.detailsId,
			if(a.earningStatus = '已结算','0',if(a.earningStatus = '结算中','1',if(a.earningStatus = '已冻结','3','2'))) earningStatus,
			if(a.receiptStatus = '已收','0',if(a.receiptStatus = '垫付','2','1')) receiptStatus
		from
			view_management_info a
		where
			a.earningNo = #{settleNo}
	</select>

	<!-- 根据结算单编码获取视图信息 -->
	<select id="selectSalarylViewBySettleNo" resultMap="BaseResultSocialStatusMap">
		SELECT
		gongzi.fd_3b10afe8c70742 AS detailsId,
		if(gongzi.fd_3adfee4ba5ad36 = '已结算','0',if(gongzi.fd_3adfee4ba5ad36 = '结算中','1',if(gongzi.fd_3adfee4ba5ad36 = '已冻结','3','2'))) earningStatus,
		if(gongzi.fd_3adfee4c0c59ee = '已收','0',if(gongzi.fd_3adfee4c0c59ee = '垫付','2','1')) receiptStatus,
		if(gongzi.fd_3adfeef9440ab2 = '已结算','0',if(gongzi.fd_3adfeef9440ab2 = '结算中','1','2')) zhichuEarningStatus,
		if(fukuan.fd_3b148bc0c28e48 = '已付','0','1') payStatus
		FROM
		ekp_salary_info gongzi
		LEFT JOIN ekp_db4ed54e25543f4b7a9a fukuan ON gongzi.fd_id = fukuan.fd_3b1fe00b0f5dde
		where
		1 = 1
		<if test="payFlag != null  and payFlag != '' and payFlag == 0">
			and gongzi.fd_3adfee5dd14866 = #{settleNo}
		</if>
		<if test="payFlag != null  and payFlag != '' and payFlag == 1">
			and gongzi.fd_3aeadf17522642 = #{settleNo}
		</if>
	</select>

	<!--查询项目信息-->
	<select id="getInsuranceEkpDeptInfo" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpInsuranceDeptInfoVo">
		SELECT
			a.fd_id as fdId,
			a.fd_3b1480cd9dcb60 as settleType,
			a.fd_3b15f64dd59a28 as isBpo,
			a.fd_3b13b272078eda as insurancesType,
			a.fd_3b1480c328d7b2_text as insurancesTitle,
			a.fd_usebpo_settle as bpoSettle,
			a.fd_3a37fe508071fe as deptNo
		from ekp_24a8e6a7fc143bb8c48a a
		where a.fd_3a37fe508071fe = #{deptNo}
		limit 1
	</select>

	<!--查询EKP项目信息-->
	<select id="getEkpDeptInfoByDeptNo" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.EkpInsuranceDeptInfoVo">
		SELECT
			a.fd_id as fdId,
			a.fd_3b1480cd9dcb60 as settleType,
			a.fd_3b15f64dd59a28 as isBpo,
			a.fd_3b13b272078eda as insurancesType,
			a.fd_3b1480c328d7b2_text as insurancesTitle,
			a.fd_usebpo_settle as bpoSettle,
			a.fd_3a37fe508071fe as deptNo
		from ekp_24a8e6a7fc143bb8c48a a
		where a.fd_3a37fe508071fe in
		<foreach item="idStr" index="index" collection="deptNoList" open="(" separator="," close=")">
			#{idStr}
		</foreach>
		GROUP BY a.fd_3a37fe508071fe
	</select>
</mapper>




