<?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.ekp.mapper.EkpSocialInfoMapper">

  <resultMap id="ekpSocialInfoMap" type="com.yifu.cloud.plus.v1.ekp.entity.EkpSocialInfo">
    <id property="fd_id" column="fd_id"/>
    <result property="fd_3add9dd7833db8" column="fd_3add9dd7833db8"/>
    <result property="fd_3add9de0be85e4" column="fd_3add9de0be85e4"/>
    <result property="fd_3add9e1a670144" column="fd_3add9e1a670144"/>
    <result property="fd_3adfe8c70d3fd4" column="fd_3adfe8c70d3fd4"/>
    <result property="fd_3adfe8c8468e54" column="fd_3adfe8c8468e54"/>
    <result property="fd_3adfe95c169c48" column="fd_3adfe95c169c48"/>
    <result property="fd_3adfe8c73cb5a4" column="fd_3adfe8c73cb5a4"/>
    <result property="fd_3adfe8c81a0e42" column="fd_3adfe8c81a0e42"/>
    <result property="fd_3adfe8c79989d4" column="fd_3adfe8c79989d4"/>
    <result property="fd_3adfe8c7e4cf7a" column="fd_3adfe8c7e4cf7a"/>
    <result property="fd_3adfe8cb96c41e" column="fd_3adfe8cb96c41e"/>
    <result property="fd_3adfe8cf632700" column="fd_3adfe8cf632700"/>
    <result property="fd_3adfe8cff746bc" column="fd_3adfe8cff746bc"/>
    <result property="fd_3adfeb4e8064a8" column="fd_3adfeb4e8064a8"/>
    <result property="fd_3adfeb5366dd82" column="fd_3adfeb5366dd82"/>
    <result property="fd_3adfeb53c70f72" column="fd_3adfeb53c70f72"/>
    <result property="fd_3adfeb5413fb44" column="fd_3adfeb5413fb44"/>
    <result property="fd_3adfeb7b624f06" column="fd_3adfeb7b624f06"/>
    <result property="fd_3add9ea428879a" column="fd_3add9ea428879a"/>
    <result property="fd_3add9eaeed2560" column="fd_3add9eaeed2560"/>
    <result property="fd_3adfeb830523b6" column="fd_3adfeb830523b6"/>
    <result property="fd_3adfeb8489e6c2" column="fd_3adfeb8489e6c2"/>
    <result property="fd_3adfeb7bd97464" column="fd_3adfeb7bd97464"/>
    <result property="fd_3add9edfbc6f7e" column="fd_3add9edfbc6f7e"/>
    <result property="fd_3add9eed23894a" column="fd_3add9eed23894a"/>
    <result property="fd_3adfeb83a704c8" column="fd_3adfeb83a704c8"/>
    <result property="fd_3adfeb84175f28" column="fd_3adfeb84175f28"/>
    <result property="fd_3aeafa25916e82" column="fd_3aeafa25916e82"/>
    <result property="fd_3af9f2fa79ee72" column="fd_3af9f2fa79ee72"/>
    <result property="fd_3af9f2ec516c30" column="fd_3af9f2ec516c30"/>
    <result property="fd_3af9f2fcde9158" column="fd_3af9f2fcde9158"/>
    <result property="fd_3af9f285ee1e38" column="fd_3af9f285ee1e38"/>
    <result property="fd_3af9f2dd5d8fb8" column="fd_3af9f2dd5d8fb8"/>
    <result property="fd_3af9f2e03295dc" column="fd_3af9f2e03295dc"/>
    <result property="fd_3af9f27a3974e6" column="fd_3af9f27a3974e6"/>
    <result property="fd_3af9f25b851e94" column="fd_3af9f25b851e94"/>
    <result property="fd_3af9f3059e5b9c" column="fd_3af9f3059e5b9c"/>
    <result property="fd_3af9f26a3cae94" column="fd_3af9f26a3cae94"/>
    <result property="fd_3af9f263e094c6" column="fd_3af9f263e094c6"/>
    <result property="fd_3af9f2617e530a" column="fd_3af9f2617e530a"/>
    <result property="fd_3af9f281651734" column="fd_3af9f281651734"/>
    <result property="fd_3af9f2e9208e4e" column="fd_3af9f2e9208e4e"/>
    <result property="fd_3af9f2e6dfb3f4" column="fd_3af9f2e6dfb3f4"/>
    <result property="fd_3af9f2f8290f24" column="fd_3af9f2f8290f24"/>
    <result property="fd_3af9f2e237f794" column="fd_3af9f2e237f794"/>
    <result property="fd_3af9f2e45a5d78" column="fd_3af9f2e45a5d78"/>
    <result property="fd_3af9f283a6f288" column="fd_3af9f283a6f288"/>
    <result property="fd_3af9f2db1e5e16" column="fd_3af9f2db1e5e16"/>
    <result property="fd_3af9f27ce947ac" column="fd_3af9f27ce947ac"/>
    <result property="fd_3af9f2ff2c9f42" column="fd_3af9f2ff2c9f42"/>
    <result property="fd_3af9f25f5e9cc4" column="fd_3af9f25f5e9cc4"/>
    <result property="fd_3af9f2883941b4" column="fd_3af9f2883941b4"/>
    <result property="fd_3af9f267a03bd6" column="fd_3af9f267a03bd6"/>
    <result property="fd_3af9f303037214" column="fd_3af9f303037214"/>
    <result property="fd_3af9f2d8df88c0" column="fd_3af9f2d8df88c0"/>
    <result property="fd_3af9f257b1b586" column="fd_3af9f257b1b586"/>
    <result property="fd_3b0194c7336de0" column="fd_3b0194c7336de0"/>
    <result property="fd_3b0b716e771e06" column="fd_3b0b716e771e06"/>
    <result property="fd_3b1354c3add8c2" column="fd_3b1354c3add8c2"/>
    <result property="fd_3b16e2f436ff98_text" column="fd_3b16e2f436ff98_text"/>
    <result property="fd_3b16e2f436ff98" column="fd_3b16e2f436ff98"/>
    <result property="fd_3b178e3ba5e258" column="fd_3b178e3ba5e258"/>
    <result property="fd_3b35a57b6e9d0a" column="fd_3b35a57b6e9d0a"/>
    <result property="fd_3b35a5b0b04d54" column="fd_3b35a5b0b04d54"/>
    <result property="fd_3b35a57aee1428" column="fd_3b35a57aee1428"/>
    <result property="fd_3b35a5b03100c4" column="fd_3b35a5b03100c4"/>
    <result property="fd_3b3cab5f343a90" column="fd_3b3cab5f343a90"/>
    <result property="fd_3b3e41ac916e66" column="fd_3b3e41ac916e66"/>
    <result property="fd_3b438e61af0a56" column="fd_3b438e61af0a56"/>
    <result property="fd_3b5cc5e697079a" column="fd_3b5cc5e697079a"/>
    <result property="fd_3b6495a5ad6e10" column="fd_3b6495a5ad6e10"/>
    <result property="createTime" column="create_time"/>
  </resultMap>

	<!--查询项目信息-->
	<select id="getAllEkpDeptInfo" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpDeptInfoVo">
		SELECT
			a.fd_id as fdId,
			a.fd_3b1480cd9dcb60 as settleType,
			a.fd_3b15f64dd59a28 as isBpo,
			a.fd_3b13479cedebd4 as socialType,
			a.fd_3a2b38c61ef9aa as deptName,
			a.fd_3a37fe508071fe as deptNo
		from ekp_24a8e6a7fc143bb8c48a a
		group by fd_3a37fe508071fe
	</select>

	<!--查询社保明细信息-->
	<select id="getCostSumInfo" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpSocialSumInfoVo">
		SELECT
			round(sum(ifnull(fd_3af9f2883941b4,0)),2) as ygSum,
			round(sum(ifnull(fd_3af9f285ee1e38,0)),2) as ygPersonSum,
			round(sum(ifnull(fd_3af9f3059e5b9c,0)),2) as sjSum,
			round(sum(ifnull(fd_3adfeb4e8064a8,0)),2) as ygDwSum,
			round(sum(ifnull(fd_3af9f2e9208e4e,0)),2) as sjDwSum,
			round(sum(ifnull(fd_3af9f303037214,0)),2) as sjPersonSum,
			round(sum(ifnull(fd_3adfeb7b624f06,0)),2) as ys,
			round(sum(ifnull(fd_3b35a57aee1428,0)),2) as personYs,
			round(sum(ifnull(fd_3b35a57b6e9d0a,0)),2) as dwYs,
			round(sum(ifnull(fd_3adfeb7bd97464,0)),2) as yzc,
			round(sum(ifnull(fd_3b35a5b03100c4,0)),2) as personYzc,
			round(sum(ifnull(fd_3b35a5b0b04d54,0)),2) as dwYzc
		from ekp_social_info
		where fd_3adfe8c7e4cf7a = #{card} and fd_3adfe8cb96c41e = #{month}
	</select>

	<update id="updateSocialStatus">
		#{sql}
	</update>

	<!--查询当天的所有入账记录数据, 查账号和流水号-->
	<select id="getAllserioNo" resultType="Map">
		SELECT fd_3b573843db30ce as onlySequence,fd_3b62f3470beb30 as receiveBkNo from ekp_79c234bf64d412294f23
		where DATE_FORMAT(fd_3aa4531d3b86e0, '%Y-%m-%d') between DATE_SUB(curdate(), INTERVAL 1 day) and DATE_ADD(now(), INTERVAL 1 day)
		and fd_3b57f8de9df354 = '是'
	</select>

	<!--ekp1.9.12查询近三天的所有入账记录数据, 查账号和流水号-->
	<select id="getAllserioNoLsatSevenDays" resultType="Map">
		SELECT fd_3b573843db30ce as onlySequence,fd_3b62f3470beb30 as receiveBkNo from ekp_79c234bf64d412294f23
		where DATE_FORMAT(fd_3aa4531d3b86e0, '%Y-%m-%d') between DATE_SUB(curdate(), INTERVAL 3 day) and DATE_ADD(now(), INTERVAL 1 day)
		  and fd_3b57f8de9df354 = '是'
	</select>

	<!--查询所有需要查询的单号-主表-->
	<select id="getNoToQueryByMain" resultType="com.yifu.cloud.plus.v1.yifu.common.core.vo.BaseIdNameNoVo">
		SELECT a.fd_wx_no id,a.fd_atta_src no FROM ekp_bank_pay_task a
		where a.fd_pay_type in ('非扣税','报销') and a.fd_atta_src is not null
			and a.fd_create_time >= DATE_SUB(curdate(), INTERVAL 3 MONTH)
			and not EXISTS (
				select 1 from ekp_bank_atta b where b.fd_link_type='4' and a.fd_wx_no=b.fd_link_id
			)
	</select>

	<!--查询所有需要查询的单号-明细表-->
	<select id="getNoToQueryByDetail" resultType="com.yifu.cloud.plus.v1.yifu.common.core.vo.BaseIdNameNoVo">
		SELECT a.fd_wx_no id,a.fd_atta_src no FROM ekp_bank_grant_detail a
		left join ekp_bank_pay_task m on m.fd_id=a.fd_parent_id
		where m.fd_pay_type in ('非扣税','报销') and a.fd_atta_src is not null
		  and a.fd_create_time >= DATE_SUB(curdate(), INTERVAL 6 MONTH)
		  and not EXISTS (
				select 1 from ekp_bank_atta b where b.fd_link_type='4' and a.fd_wx_no=b.fd_link_id
			)
	</select>

	<!--查询付款账户信息-->
	<select id="getAccountList" resultType="Map">
		select fd_3b14a2430ac214 as bank_name,fd_3b14a24376d86a as bank_no from ekp_fe8247b4cbb4b27881dd where fd_3b14a24b9adb52='启用' and fd_3b56747b811872='是';
	</select>

	<!--查询付款账户信息-->
	<select id="selectContractInfoByDetptNo" resultType="com.yifu.cloud.plus.v1.ekp.vo.EkpDeptContractInfoVo">
		select
			a.fd_3b148031607a30 as contractType,
			if(a.fd_3b148031607a30 = '合同之后',DATE_FORMAT(b.fd_3ad8da6ba4bd2c, '%Y-%m-%d'),null) as contractEndTime,
			a.fd_3a37fe508071fe as deptNo
			from ekp_24a8e6a7fc143bb8c48a a
			left join ekp_0103c453b2848c3bb31c b on a.fd_3b14803c09db8e = b.fd_id
		where a.fd_3a37fe508071fe = #{deptNo}
		limit 1
	</select>

	<select id="getAllIcbcCardBinInfo" resultType="java.lang.String">
		select
			bank_code
		from ekp_icbc_code_config
	</select>
</mapper>
