<?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.TSocialAlertInfoMapper">

  <resultMap id="tSocialAlertInfoMap" type="com.yifu.cloud.plus.v1.yifu.social.entity.TSocialAlertInfo">
    <id property="id" column="ID"/>
    <result property="deptNo" column="DEPT_NO"/>
    <result property="deptName" column="DEPT_NAME"/>
    <result property="type" column="TYPE"/>
    <result property="empName" column="EMP_NAME"/>
    <result property="empIdcard" column="EMP_IDCARD"/>
    <result property="reason" column="REASON"/>
    <result property="receiveUser" column="RECEIVE_USER"/>
    <result property="createBy" column="CREATE_BY"/>
    <result property="updateBy" column="UPDATE_BY"/>
    <result property="createName" column="CREATE_NAME"/>
    <result property="updateTime" column="UPDATE_TIME"/>
    <result property="createTime" column="CREATE_TIME"/>
	<result property="handleTime" column="HANDLE_TIME"/>
	<result property="houseHoldName" column="HOUSE_HOLD_NAME"/>
	<result property="socialProvince" column="SOCIAL_PROVINCE"/>
	<result property="socialCity" column="SOCIAL_CITY"/>
	<result property="socialTown" column="SOCIAL_TOWN"/>
	<result property="fundProvince" column="FUND_PROVINCE"/>
	<result property="fundCity" column="FUND_CITY"/>
	<result property="fundTown" column="FUND_TOWN"/>
	<result property="dispatchId" column="DISPATCH_ID"/>
	<result property="applyName" column="APPLY_NAME"/>

  </resultMap>
  
    <sql id="Base_Column_List">
		a.ID,
		a.DEPT_NO,
		a.DEPT_NAME,
		a.TYPE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.REASON,
		a.RECEIVE_USER,
		a.CREATE_BY,
		a.UPDATE_BY,
		a.CREATE_NAME,
		a.UPDATE_TIME,
		a.CREATE_TIME,
		a.HOUSE_HOLD_NAME,
		a.SOCIAL_PROVINCE,
		a.SOCIAL_CITY,
		a.SOCIAL_TOWN,
		a.FUND_PROVINCE,
		a.FUND_CITY,
		a.FUND_TOWN,
		a.HANDLE_TIME,
		a.DISPATCH_ID,a.APPLY_NAME
    </sql>
    
    <sql id="tSocialAlertInfo_where">
        <if test="tSocialAlertInfo != null">
            <if test="tSocialAlertInfo.id != null and tSocialAlertInfo.id.trim() != ''">
                AND a.ID = #{tSocialAlertInfo.id}
            </if>
            <if test="tSocialAlertInfo.deptNo != null and tSocialAlertInfo.deptNo.trim() != ''">
                AND a.DEPT_NO = #{tSocialAlertInfo.deptNo}
            </if>
            <if test="tSocialAlertInfo.deptName != null and tSocialAlertInfo.deptName.trim() != ''">
                AND a.DEPT_NAME = #{tSocialAlertInfo.deptName}
            </if>
            <if test="tSocialAlertInfo.type != null and tSocialAlertInfo.type.trim() != ''">
                AND a.TYPE = #{tSocialAlertInfo.type}
            </if>
            <if test="tSocialAlertInfo.empName != null and tSocialAlertInfo.empName.trim() != ''">
                AND a.EMP_NAME = #{tSocialAlertInfo.empName}
            </if>
            <if test="tSocialAlertInfo.empIdcard != null and tSocialAlertInfo.empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{tSocialAlertInfo.empIdcard}
            </if>
            <if test="tSocialAlertInfo.reason != null and tSocialAlertInfo.reason.trim() != ''">
                AND a.REASON = #{tSocialAlertInfo.reason}
            </if>
            <if test="tSocialAlertInfo.receiveUser != null and tSocialAlertInfo.receiveUser.trim() != ''">
                AND a.RECEIVE_USER = #{tSocialAlertInfo.receiveUser}
            </if>
            <if test="tSocialAlertInfo.createBy != null and tSocialAlertInfo.createBy.trim() != ''">
                AND a.CREATE_BY = #{tSocialAlertInfo.createBy}
            </if>
            <if test="tSocialAlertInfo.updateBy != null and tSocialAlertInfo.updateBy.trim() != ''">
                AND a.UPDATE_BY = #{tSocialAlertInfo.updateBy}
            </if>
            <if test="tSocialAlertInfo.createName != null and tSocialAlertInfo.createName.trim() != ''">
                AND a.CREATE_NAME = #{tSocialAlertInfo.createName}
            </if>
            <if test="tSocialAlertInfo.updateTime != null">
                AND a.UPDATE_TIME = #{tSocialAlertInfo.updateTime}
            </if>
            <if test="tSocialAlertInfo.createTime != null">
                AND a.CREATE_TIME = #{tSocialAlertInfo.createTime}
            </if>
			<if test="tSocialAlertInfo.infoDate != null">
				AND DATE_FORMAT(a.CREATE_TIME,'%Y%m%d') = #{tSocialAlertInfo.infoDate}
			</if>

        </if>
    </sql>
    
    <!--tSocialAlertInfo简单分页查询-->
    <!--<select id="getTSocialAlertInfoPage" resultMap="tSocialAlertInfoMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_social_alert_info a
        <where>
            1=1
            <include refid="tSocialAlertInfo_where"/>
        </where>
    </select>-->
	<!--tSocialAlertInfo简单分页查询-->
	<select id="getTSocialAlertInfoPage" resultMap="tSocialAlertInfoMap">
		select
		a.DEPT_NAME,
		GROUP_CONCAT(
		CASE WHEN a.TYPE=0 THEN '社保'
		WHEN a.TYPE=1 THEN '公积金'
		ELSE NULL END  SEPARATOR '、'
		) as type,
		a.EMP_NAME,
		GROUP_CONCAT(
		CASE
		WHEN a.TYPE = 0 THEN
		CONCAT('社保:',a.REASON)
		WHEN a.TYPE = 1 THEN
		CONCAT('公积金:',a.REASON)
		ELSE NULL END
		) as REASON
		FROM  t_social_alert_info  a

		<where>
			1=1
			<include refid="tSocialAlertInfo_where"/>
		</where>
		GROUP BY a.DISPATCH_ID
	</select>

	<!--tSocialAlertInfo数据获取-->
	<select id="getTSocialAlertInfoList" resultMap="tSocialAlertInfoMap">
		SELECT
			di.SETTLE_DOMAIN_CODE as DEPT_NO,
			di.SETTLE_DOMAIN_NAME as DEPT_NAME,
			'0' AS TYPE,
			di.EMP_NAME AS EMP_NAME,
			di.EMP_IDCARD AS EMP_IDCARD,
			di.CREATE_NAME AS APPLY_NAME,
			di.CREATE_BY AS CREATE_BY,
			di.CREATE_BY AS RECEIVE_USER,
			di.SOCIAL_HOUSEHOLD_NAME as HOUSE_HOLD_NAME,
			di.SOCIAL_PROVINCE as 	SOCIAL_PROVINCE,
			di.SOCIAL_CITY as 	SOCIAL_CITY,
			di.SOCIAL_TOWN as 	SOCIAL_TOWN,
			NULL as FUND_PROVINCE,
			NULL as FUND_CITY,
			NULL as FUND_TOWN,
		    di.id as DISPATCH_ID,
			CASE
				WHEN di.TYPE =0 THEN si.HANDLE_TIME
				WHEN di.TYPE =1 THEN si.REDUCE_HANDLE_TIME
				ELSE NULL
				END AS HANDLE_TIME,
			rc.reasons AS REASON
		FROM
			t_dispatch_info as  di
				LEFT JOIN
			(
				SELECT
					ID,
					HANDLE_TIME,
					REDUCE_HANDLE_TIME
				FROM t_social_info
				WHERE
					CREATE_TIME > DATE_SUB(CURDATE(), INTERVAL 4 MONTH)
			) AS si ON di.SOCIAL_ID = si.ID
			LEFT JOIN
			(SELECT
					SOCIAL_ID,
					GROUP_CONCAT(DISTINCT AUDIT_REMARK   SEPARATOR '#') as 'reasons',
					DISPATCH_INFO_ID
						FROM
				t_audit_info
			WHERE
				audit_time > DATE_SUB(CURDATE(), INTERVAL 3 DAY)
			AND AUDIT_STATUS IN ('4', '5') AND SOCIAL_ID is not NULL
			GROUP BY DISPATCH_INFO_ID
			    ) rc ON rc.DISPATCH_INFO_ID =di.id
		WHERE
			(
					(si.HANDLE_TIME >= CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' 10:00:00')
						AND si.HANDLE_TIME <![CDATA[ <= ]]> CONCAT(CURDATE(), ' 10:00:00'))
					OR
					(si.REDUCE_HANDLE_TIME >= CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' 10:00:00')
						AND si.REDUCE_HANDLE_TIME <![CDATA[ <= ]]> CONCAT(CURDATE(), ' 10:00:00'))
				)
		  AND di.SOCIAL_HANDLE_STATUS IN ('2', '3')
		  AND di.DELETE_FLAG = '0' AND di.social_id is not null

	</select>
	<!--tSocialAlertInfo数据获取-->
	<select id="getTFundAlertInfoList" resultMap="tSocialAlertInfoMap">
		SELECT
			di.SETTLE_DOMAIN_CODE as DEPT_NO,
			di.SETTLE_DOMAIN_NAME as DEPT_NAME,
			'1' AS TYPE,
			di.EMP_NAME AS EMP_NAME,
			di.EMP_IDCARD AS EMP_IDCARD,
			di.CREATE_NAME AS APPLY_NAME,
			di.CREATE_BY AS CREATE_BY,
			di.CREATE_BY AS RECEIVE_USER,
			di.PROVIDENT_HOUSEHOLD_NAME as HOUSE_HOLD_NAME,
			NULL as 	SOCIAL_PROVINCE,
			NULL as 	SOCIAL_CITY,
			NULL as 	SOCIAL_TOWN,
			di.FUND_PROVINCE as FUND_PROVINCE,
			di.FUND_CITY as FUND_CITY,
			di.FUND_TOWN as FUND_TOWN,
			di.id as DISPATCH_ID,
			CASE
				WHEN di.TYPE =0 THEN pf.HANDLE_TIME
				WHEN di.TYPE =1 THEN pf.REDUCE_HANDLE_TIME
				ELSE NULL
				END AS HANDLE_TIME,
			rc.reasons AS REASON
		FROM
			t_dispatch_info as  di
				LEFT JOIN
			(
				SELECT
					HANDLE_TIME,
					ID,
					REDUCE_HANDLE_TIME
				FROM t_provident_fund
				WHERE
					CREATE_TIME > DATE_SUB(CURDATE(), INTERVAL 4 MONTH )
			) AS pf ON di.FUND_ID = pf.ID
				LEFT JOIN
				(SELECT
					PROVIDENT_ID,
					GROUP_CONCAT(DISTINCT AUDIT_REMARK  SEPARATOR '#') as 'reasons',
					DISPATCH_INFO_ID
						FROM
				t_audit_info
			WHERE
				audit_time > DATE_SUB(CURDATE(), INTERVAL 3 DAY)
			AND AUDIT_STATUS IN ('4', '5') AND PROVIDENT_ID is not NULL
			GROUP BY DISPATCH_INFO_ID
				) rc ON rc.DISPATCH_INFO_ID =di.id
		WHERE
			(
					(pf.HANDLE_TIME >= CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' 10:00:00')
						AND pf.HANDLE_TIME <![CDATA[ <= ]]> CONCAT(CURDATE(), ' 10:00:00'))
					OR
					(pf.REDUCE_HANDLE_TIME >= CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' 10:00:00')
						AND pf.REDUCE_HANDLE_TIME <![CDATA[ <= ]]> CONCAT(CURDATE(), ' 10:00:00'))
				)
		  AND di.FUND_HANDLE_STATUS = '2'
		  AND di.DELETE_FLAG = '0' AND di.fund_id is not null
</select>

	<delete id="deleteCUrrentDayData">
		delete from t_social_alert_info where CREATE_TIME >= CONCAT(CURDATE(), ' 10:00:00')
	</delete>
</mapper>