<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~
  ~      Copyright (c) 2018-2025, lengleng All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the yifu4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: lengleng (wangiegie@gmail.com)
  ~
  -->

<!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.ekp.mapper.EkpBankGrantDetailMapper">

  <resultMap id="ekpBankGrantDetailMap" type="com.yifu.cloud.plus.v1.ekp.entity.EkpBankGrantDetail">
    <id property="fdId" column="fd_id"/>
    <result property="fdSerialNumber" column="fd_serial_number"/>
    <result property="fdPayeeName" column="fd_payee_name"/>
    <result property="fdPayeeAccount" column="fd_payee_account"/>
    <result property="fdMoney" column="fd_money"/>
    <result property="fdRecipientBank" column="fd_recipient_bank"/>
    <result property="fdRemittancePurpose" column="fd_remittance_purpose"/>
    <result property="fdRemark" column="fd_remark"/>
    <result property="fdCreateTime" column="fd_create_time"/>
    <result property="fdFailureFeedback" column="fd_failure_feedback"/>
    <result property="fdGrantStatus" column="fd_grant_status"/>
    <result property="fdHandleStatus" column="fd_handle_status"/>
    <result property="fdHandleFailNo" column="fd_handle_fail_no"/>
    <result property="fdHandleFailId" column="fd_handle_fail_id"/>
    <result property="fdAttaId" column="fd_atta_id"/>
    <result property="fdAttaName" column="fd_atta_name"/>
    <result property="fdAttaSrc" column="fd_atta_src"/>
    <result property="fdDownloadNum" column="fd_download_num"/>
    <result property="fdFailNum" column="fd_fail_num"/>
    <result property="fdGrantNum" column="fd_grant_num"/>

    <result property="fdParentId" column="fd_parent_id"/>
    <result property="fdEmpPhone" column="fd_emp_phone"/>
    <result property="fdWxNo" column="fd_wx_no"/>
    <result property="fdMdCode" column="fd_md_dcode"/>
    <result property="fdSalaryId" column="fd_salary_id"/>
  </resultMap>

	<sql id="Base_Excel_Sql">
		select
			DATE_FORMAT(now(),'%Y%m%d') nowDate,
			'2' detailTag,
			a.fd_serial_number fdSerialNumber,
			'' baoXiaoNumber,
			'0' danJuNumber,
			m.fd_bank_name fdBankName,
			m.fd_bank_no fdBankNo,
			m.fd_bank_account fdBankAccount,
			ifnull(c.fd_recipient_bank,a.fd_recipient_bank) fdRecipientBank,
			'' fdRecipientAdress,
			c.fd_recipient_bank_code fdRecipientBankCode,
			'2' payType,
			a.fd_payee_account fdPayeeAccount,
			a.fd_payee_name fdPayeeName,
			concat('',round(a.fd_money * 100,0)) fdMoney,
			a.fd_remittance_purpose fdRemittancePurpose,
			ifnull(a.fd_remark,'工资') fdCoverRemark,
			a.fd_emp_phone fdEmpPhone
		from ekp_bank_grant_detail a
			left join ekp_bank_pay_task m on a.fd_parent_id=m.fd_id
			left join ekp_bank_code_set c on c.fd_payee_account=a.fd_payee_account
		where
	</sql>
	<sql id="Base_No_Tax_Sql">
		select
			DATE_FORMAT(now(),'%Y%m%d') nowDate,
			'2' detailTag,
			a.fd_serial_number fdSerialNumber,
			'' baoXiaoNumber,
			'0' danJuNumber,
			m.fd_bank_name fdBankName,
			m.fd_bank_no fdBankNo,
			m.fd_bank_account fdBankAccount,
			ifnull(c.fd_recipient_bank,a.fd_recipient_bank) fdRecipientBank,
			'' fdRecipientAdress,
			c.fd_recipient_bank_code fdRecipientBankCode,
			'2' payType,
			a.fd_payee_account fdPayeeAccount,
			a.fd_payee_name fdPayeeName,
			concat('',round(a.fd_money * 100,0)) fdMoney,
			a.fd_remittance_purpose fdRemittancePurpose,
			ifnull(a.fd_remark,'转账') fdCoverRemark,
			a.fd_emp_phone fdEmpPhone,
			a.fd_id fdId
		from ekp_bank_grant_detail a
			left join ekp_bank_pay_task m on a.fd_parent_id=m.fd_id
			left join ekp_bank_code_set c on c.fd_payee_account=a.fd_payee_account
		where
	</sql>

	<!-- 查询待发放条数 -->
	<select id="getCountByIdAndMain" resultType="java.lang.Integer">
		select count(1)
		from ekp_bank_grant_detail a
		where (a.fd_handle_status is null or a.fd_handle_status = '待发放')
		and a.fd_parent_id = #{fdParentId}
	</select>

	<!-- 查询主表下的所有待发放数据 -->
	<select id="getEkpBankExcelVoByIdAndMain" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpBankExcelVo">
		<include refid="Base_Excel_Sql"/>
		(a.fd_handle_status is null or a.fd_handle_status = '待发放') and a.fd_parent_id = #{fdParentId}
		GROUP BY a.fd_id
		ORDER BY (a.fd_serial_number-0) asc
	</select>
	<!-- 查询主表下的所有待发放数据 -->
	<select id="getEkpBankExcelVoByIdAndMainNoTax" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpBankExcelNoTaxVo">
		<include refid="Base_No_Tax_Sql"/>
		(a.fd_handle_status is null or a.fd_handle_status = '待发放') and a.fd_parent_id = #{fdParentId}
		GROUP BY a.fd_id
		ORDER BY (a.fd_serial_number-0) asc
	</select>

	<!-- 查询主表下的所有待发放数据 -->
	<select id="getEkpBankExcelVoByIdAndDetail" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpBankExcelVo">
		<include refid="Base_Excel_Sql"/>
		a.fd_handle_status = '待发放' and a.fd_id = #{fdId}
		GROUP BY a.fd_id
		ORDER BY (a.fd_serial_number-0) asc
	</select>
	<!-- 查询主表下的所有待发放数据 -->
	<select id="getEkpBankExcelVoByIdAndDetailNoTax" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpBankExcelNoTaxVo">
		<include refid="Base_No_Tax_Sql"/>
		a.fd_handle_status = '待发放' and a.fd_id = #{fdId}
		GROUP BY a.fd_id
		ORDER BY (a.fd_serial_number-0) asc
	</select>

	<!-- 查询编号是否重复 -->
	<select id="getWxNoByWxNo" resultType="java.lang.String">
		select a.fd_wx_no from (
		   select a.fd_wx_no from ekp_bank_grant_detail a where a.fd_wx_no = #{wxNo}
		   union all
		   select a.fd_wx_no from ekp_bank_pay_task a where a.fd_wx_no = #{wxNo}
	   ) a limit 1
	</select>

	<!-- 主表发放时，更新明细表的发放时间与状态 -->
	<update id="updateStatusByGrant">
		update ekp_bank_grant_detail a set a.fd_create_time = NOW(),a.fd_grant_status='发放中' where a.fd_parent_id = #{fdParentId}
	</update>

	<!-- 主表处理失败时，批量更新明细表为发放失败，原因为处理失败 -->
	<update id="updateStatusByGrantFail">
		update ekp_bank_grant_detail a set a.fd_grant_status='发放失败',a.fd_failure_feedback = '处理失败' where a.fd_parent_id = #{fdParentId}
	</update>
	
	<select id="getListByParentId" resultMap="ekpBankGrantDetailMap">
		select
			a.fd_id,a.fd_salary_id,a.fd_serial_number,a.fd_payee_name,a.fd_recipient_bank,a.fd_fail_num
		from ekp_bank_grant_detail a where a.fd_parent_id = #{fdParentId}
	</select>

	<!-- 发放失败，批量更新中信银行交易流水表fd_3b148bc0c28e48为失败，根据 工资明细fdId：fd_3b1fe00b0f5dde -->
	<update id="updateZhongXinStatus">
		update ekp_db4ed54e25543f4b7a9a a
		set a.fd_3b148bc0c28e48='失败',a.fd_tbankempid = #{detailId},a.fd_3aff567d4b75ce = #{detailFailMsg}
		where a.fd_3b1fe00b0f5dde = #{salaryId}

	</update>

	<!-- 发放失败，批量更新中信银行交易流水Model表fd_3b148bc0c28e48为失败，根据 工资明细fdId：fd_3b1fe00b0f5dde -->
	<update id="updateZhongXinMainStatus">
		UPDATE modeling_simple_main a
		JOIN ekp_db4ed54e25543f4b7a9a b on a.fd_id=b.fd_id
		SET a.extend_data_xml=JSON_SET(a.extend_data_xml,'$[1].fd_3b148bc0c28e48', '失败','$[1].fd_tbankempid', #{detailId}, '$[1].fd_3aff567d4b75ce', #{detailFailMsg})
		where b.fd_3b56782982b8b2 = #{salaryId}

	</update>


	<!-- 明细再次-发放失败，批量更新中信银行交易流水表fd_3b148bc0c28e48为失败，根据 工资明细fdId：fd_3b1fe00b0f5dde -->
	<update id="updateZhongXinStatusAndBankInfo">
		update ekp_db4ed54e25543f4b7a9a a
		left join ekp_bank_grant_detail d on a.fd_3b1fe00b0f5dde=d.fd_salary_id
		set a.fd_3b148bc0c28e48='失败',a.fd_3c754f2a7125a0 = '1183b0b84ee4f581bba001c47a78b2d9',a.fd_3c754a976ddde2=now(),
		a.fd_3afd45029541be=d.fd_payee_name,a.fd_3afd4500f91ae6=d.fd_payee_account,a.fd_3afd4502e34bc0=d.fd_recipient_bank
		where
		<if test="salaryIdList != null and salaryIdList.size() > 0">
			a.fd_3b1fe00b0f5dde in
			<foreach collection="salaryIdList" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</update>

	<!-- 明细再次-发放失败，批量更新中信银行交易流水Model表fd_3b148bc0c28e48为失败，根据 工资明细fdId：fd_3b1fe00b0f5dde -->
	<update id="updateZhongXinMainStatusAndBankInfo">
		UPDATE modeling_simple_main a
		JOIN ekp_db4ed54e25543f4b7a9a b on a.fd_id=b.fd_id
		left join ekp_bank_grant_detail d on b.fd_3b1fe00b0f5dde=d.fd_salary_id
		SET a.extend_data_xml=JSON_SET(a.extend_data_xml,'$[1].fd_3b148bc0c28e48', '失败'
		  , '$[1].fd_3c754f2a7125a0', '1183b0b84ee4f581bba001c47a78b2d9'
		  ,'$[1].fd_3c754a976ddde2', now()
		  ,'$[1].fd_3afd45029541be', d.fd_payee_name
		  ,'$[1].fd_3afd4500f91ae6', d.fd_payee_account
		  ,'$[1].fd_3afd4502e34bc0', d.fd_recipient_bank
		  )
		where
		<if test="salaryIdList != null and salaryIdList.size() > 0">
			b.fd_3b56782982b8b2 in
			<foreach collection="salaryIdList" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</update>

	<!-- 查询工行开户行配置表 -->
	<select id="getEkpBankCodeConfiglVo" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpBankCodeConfiglVo">
		SELECT
		       bank_name_mvp bankNameMvp,bank_name fdRecipientBank,bank_code fdRecipientBankCode
		FROM ekp_bank_code_config a
		where a.bank_name_mvp is not null  and a.bank_name_mvp != ''
	</select>

	<!-- 更新工资支出 的 银企出纳 -->
	<update id="updateGongZiZhiChuTable" >
		UPDATE ${tableName} SET fd_bank_pay_chu_na = #{chuNaName} WHERE fd_id = #{fdId};
	</update>
	<!-- 更新工资支出Main表的 银企出纳 -->
	<update id="updateGongZiZhiChuMain" >
		UPDATE modeling_model_main SET extend_data_xml=JSON_SET(extend_data_xml,'$[1].fd_bank_pay_chu_na',#{chuNaName}) WHERE fd_id = #{fdId};
	</update>

</mapper>
