<?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.yifu.social.mapper.TSocialFriendMapper">

	<!-- 税友基本查询-->
	<sql id="getSocialSoldierBaseOne" >
		SELECT
			s.id as socialId,
			if(a.TYPE='0','派增','派减') type,
			a.SOCIAL_HOUSEHOLD_NAME socialHouseholdName,
			shh.UNIT_CREDIT_CODE unitCreditCode,
			shh.AREA_CODE areaCode,
			shh.SOCIAL_ACCOUNT socialAccount,
			shh.SOCIAL_PASSWORD socialPassword,
			shh.MEDICL_ACCOUNT mediclAccount,
			shh.MEDICL_PASSWORD mediclPassword,
			shh.CUSTOMER_NO customerNo,
			shh.CUSTOMER_NO_YSD customerNoYsd,
			a.EMP_NAME empName,
			a.EMP_IDCARD empIdcard,
			if (a.EMP_NATIONAL in ('2','3','5','6','7','8','9','11') and nation.label is not null, nation.label,'汉族') empNational,
			a.EMP_MOBILE empMobile,
			DATE_FORMAT(s.SOCIAL_START_DATE,"%Y-%m-%d") socialStartDate,
			if(a.EDUCATION_NAME is null, '大学专科',
			   case a.EDUCATION_NAME
				   when '博士' then '博士研究生'
				   when '技工' then '技工学校'
				   when '高中' then '普通中学（高中）'
				   when '小学' then '小学'
				   when '文盲' then '其他'
				   when '中专' then '中等专科'
				   when '初中' then '初级中学'
				   when '本科' then '大学本科'
				   when '大专' then '大学专科'
				   when '硕士' then '研究生'
				   when '职高' then '职业高中'
				   else '其他'
				   end) educationName,
			s.RECORD_BASE recordBase,
			DATE_FORMAT(s.SOCIAL_START_DATE,"%Y%m") socialStartMonth,
			case a.CONTRACT_TYPE
				when '0' then '订立以完成一定工作任务为期限的劳动合同'
				when '1' then '订立固定期限劳动合同'
				when '2' then '订立无固定期限劳动合同'
				else '其他' end  contractType,
			case a.WORKING_HOURS
				when '1' then '标准工作时间制度'
				when '2' then '综合计算工时制度'
				when '3' then '不定时工作时间制度'
				else '标准工作时间制度' end workingHours,
			case a.CONTRACT_TYPE
				when '0' then '以完成一定工作为期限的劳动合同'
				when '1' then '固定期限劳动合同'
				when '2' then '无固定期限劳动合同'
				else '固定期限劳动合同' end  contractTypeTwo,
			a.CONTRACT_END contractEnd,
			if(a.REDUCE_REASON = '16','其他原因中断缴费','在职人员主动解除劳动合同') tbyy,
			case a.REDUCE_REASON
				when '21' then '职工与用人单位协商一致解除劳动合同(18条1款)'
				when '5' then '第四十四条第二项，劳动者开始依法享受基本养老保险待遇'
				when '16' then '劳动者死亡，或者被人民法院宣告死亡或者宣告失踪'
				when '1' then '劳动者提前30天书面通知解除或试用期提前3天通知解除合同'
				when '2' then '劳动者严重违反用人单位的规章制度'
				else '法律、行政法规规定的其他情形' end zzyy,
				s.SOCIAL_PROVINCE socialProvince,
				s.SOCIAL_CITY socialCity,
				s.SOCIAL_TOWN socialTown

	</sql>

	<!-- 税友基本查询-->
	<sql id="getSocialSoldierBaseTwo" >

		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		left join sys_house_hold_info shh on shh.id = s.SOCIAL_HOUSEHOLD
		left join view_sys_dict_item nation on nation.value=a.EMP_NATIONAL and nation.type='emp_national'

		<include refid="where_getSocialSoldier_base_handler"/>
		and DATE_FORMAT(a.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')
	</sql>

	<!-- 税友养工失增加模板-->
	<select id="getSocialSoldierYgsAddVoList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SociaFriendYgsAddVo">
		<include refid="getSocialSoldierBaseOne"/>
		<include refid="getSocialSoldierBaseTwo"/>
		and a.type = '0' and s.YGS_HANDLE_STATUS in ('0','1','3')
		and (
			if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = '0' or
			if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = '0' or
			if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = '0'
		)
		group by s.id
	</select>

	<sql id="where_getSocialSoldier_base_handler">
		<where>
			a.DELETE_FLAG = '0' and a.SOCIAL_PROVINCE = '12'
			<if test="idsStr != null and idsStr.size > 0">
				AND a.ID in
				<foreach item="items" index="index" collection="idsStr" open="(" separator="," close=")">
					#{items}
				</foreach>
			</if>
			/** 社保办理状态：0待办理/1已办理2办理失败3已派减4 办理中 5 部分办理失败 派单社保办理状态 0 未办理 1 全部办理成功(原-已办理) 2 全部办理失败（原-办理失败）  3 部分办理成功 4 办理中  **/
			/*社保代办理 1.派单审核通过 2.派增(type=0)：待办理(social_handle_status='0')  or 派减(type=1)：已办理完成或部分办理失败 且已派减*/
			AND a.social_id is not null
			AND a.STATUS = "2"
			and (( a.type = '0' AND (s.HANDLE_STATUS = "0" or s.HANDLE_STATUS = "4") ) or ( a.type ='1'  AND (if(ifnull(h.id,0) != '0',h.HANDLE_STATUS,s.HANDLE_STATUS) in ('1','5','6','3','7') or (s.HANDLE_STATUS in ('1','5','6','3','7') and s.REDUCE_CAN = '1'))  ))
			and a.SOCIAL_HANDLE_STATUS in ('0','4')
			and a.AUTO_FLAG = '0'
		</where>
	</sql>

	<!-- 税友养工失减少模板-->
	<select id="getSocialSoldierYgsReduceVoList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SociaFriendYgsAddVo">
		<include refid="getSocialSoldierBaseOne"/>
		<include refid="getSocialSoldierBaseTwo"/>
		and a.type = '1' and s.YGS_HANDLE_STATUS in ('0','1','3')
		and (
			if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = '5' or
			if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = '5' or
			if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = '5'
		)
		group by a.id
	</select>

	<!-- 税友医生大增加模板-->
	<select id="getSocialSoldierYsdAddVoList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SociaFriendYgsAddVo">
		<include refid="getSocialSoldierBaseOne"/>
		,if(s.UNIT_MEDICAL_MONEY != 0,1,0) unitMedicalMoney,if(s.UNIT_BIRTH_MONEY != 0,1,0) unitBirthMoney,if(s.UNIT_BIGAILMENT_MONEY != 0,1,0) unitBigailmentMoney
		<include refid="getSocialSoldierBaseTwo"/>
		and a.type = '0' and s.YSD_HANDLE_STATUS in ('0','1','3')
		and (
			if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = '0' or
			if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = '0' or
			if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = '0'
		)
		group by s.id
	</select>

	<!-- 税友医生大减少模板-->
	<select id="getSocialSoldierYsdReduceVoList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SociaFriendYgsAddVo">
		SELECT
		s.id as socialId,
		if(a.TYPE='0','派增','派减') type,
		a.SOCIAL_HOUSEHOLD_NAME socialHouseholdName,
		shh.UNIT_CREDIT_CODE unitCreditCode,
		shh.AREA_CODE areaCode,
		shh.SOCIAL_ACCOUNT socialAccount,
		shh.SOCIAL_PASSWORD socialPassword,
		shh.MEDICL_ACCOUNT mediclAccount,
		shh.MEDICL_PASSWORD mediclPassword,
		shh.CUSTOMER_NO customerNo,
		shh.CUSTOMER_NO_YSD customerNoYsd,
		a.EMP_NAME empName,
		a.EMP_IDCARD empIdcard,
		if (a.EMP_NATIONAL in ('2','3','5','6','7','8','9','11') and nation.label is not null, nation.label,'汉族') empNational,
		a.EMP_MOBILE empMobile,
		DATE_FORMAT(s.SOCIAL_START_DATE,"%Y-%m-%d") socialStartDate,
		if(a.EDUCATION_NAME is null, '大学专科',
		case a.EDUCATION_NAME
		when '博士' then '博士研究生'
		when '技工' then '技工学校'
		when '高中' then '普通中学（高中）'
		when '小学' then '小学'
		when '文盲' then '其他'
		when '中专' then '中等专科'
		when '初中' then '初级中学'
		when '本科' then '大学本科'
		when '大专' then '大学专科'
		when '硕士' then '研究生'
		when '职高' then '职业高中'
		else '其他'
		end) educationName,
		s.RECORD_BASE recordBase,
		DATE_FORMAT(s.SOCIAL_START_DATE,"%Y%m") socialStartMonth,
		case a.CONTRACT_TYPE
		when '0' then '订立以完成一定工作任务为期限的劳动合同'
		when '1' then '订立固定期限劳动合同'
		when '2' then '订立无固定期限劳动合同'
		else '其他' end  contractType,
		case a.WORKING_HOURS
		when '1' then '标准工作时间制度'
		when '2' then '综合计算工时制度'
		when '3' then '不定时工作时间制度'
		else '标准工作时间制度' end workingHours,
		case a.CONTRACT_TYPE
		when '0' then '以完成一定工作为期限的劳动合同'
		when '1' then '固定期限劳动合同'
		when '2' then '无固定期限劳动合同'
		else '固定期限劳动合同' end  contractTypeTwo,
		a.CONTRACT_END contractEnd,
		if(a.REDUCE_REASON = '16','劳动者死亡','在职人员解除/终止劳动合同') tbyy,
		case a.REDUCE_REASON
		when '21' then '职工与用人单位协商一致解除劳动合同(18条1款)'
		when '5' then '第四十四条第二项，劳动者开始依法享受基本养老保险待遇'
		when '16' then '劳动者死亡，或者被人民法院宣告死亡或者宣告失踪'
		when '1' then '劳动者提前30天书面通知解除或试用期提前3天通知解除合同'
		when '2' then '劳动者严重违反用人单位的规章制度'
		else '法律、行政法规规定的其他情形' end zzyy,
		s.SOCIAL_PROVINCE socialProvince,
		s.SOCIAL_CITY socialCity,
		s.SOCIAL_TOWN socialTown
		<include refid="getSocialSoldierBaseTwo"/>
		and a.type = '1' and s.YSD_HANDLE_STATUS in ('0','1','3')
		and (
			if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = '5' or
			if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = '5' or
			if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = '5'
		)
		group by s.id
	</select>

</mapper>
