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

  <resultMap id="budgetWarningMap" type="com.yifu.cloud.plus.v1.ekp.vo.BudgetWarningVo">
    <id property="km" column="km"/>
    <result property="at" column="at"/>
    <result property="ye" column="ye"/>
    <result property="bl" column="bl"/>
    <result property="cbzx" column="cbzx"/>
	<result property="txr" column="txr"/>

  </resultMap>
	<resultMap id="ekpAlertTableMap" type="com.yifu.cloud.plus.v1.ekp.entity.EkpAlertTable">
		<id property="fdId" column="fd_id"/>
		<result property="fdAlertSubject" column="fd_alert_subject"/>
		<result property="fdAlertSubjectText" column="fd_alert_subject_text"/>
		<result property="fdEffectiveStartDate" column="fd_effective_start_date"/>
		<result property="fdEffectiveEndDate" column="fd_effective_end_date"/>
		<result property="fdDetectionMethod" column="fd_detection_method"/>
		<result property="fdThresholdValue" column="fd_threshold_value"/>
		<result property="fdIsActive" column="fd_is_active"/>
		<result property="fdAlertMessage" column="fd_alert_message"/>
		<result property="fdEverydayTime" column="fd_everyday_time"/>
		<result property="fdChecktype" column="fd_checktype"/>
		<result property="fdMonthday" column="fd_monthday"/>
	</resultMap>
    <sql id="Base_Column_List">
                a.fd_id,
                a.fd_alert_subject,
                a.fd_alert_subject_text,
                a.fd_effective_start_date,
                a.fd_effective_end_date,
                a.fd_detection_method,
                a.fd_threshold_value,
                a.fd_is_active,
                a.fd_alert_message,
                a.fd_everyday_time,
                a.fd_checktype,
                a.fd_monthday
    </sql>
    <sql id="ekpAlertTable_where">
        <if test="ekpAlertTable != null">
                    <if test="ekpAlertTable.fdId != null and ekpAlertTable.fdId.trim() != ''">
                        AND a.fd_id = #{ekpAlertTable.fdId}
                    </if>
                    <if test="ekpAlertTable.fdAlertSubject != null and ekpAlertTable.fdAlertSubject.trim() != ''">
                        AND a.fd_alert_subject = #{ekpAlertTable.fdAlertSubject}
                    </if>
                    <if test="ekpAlertTable.fdAlertSubjectText != null and ekpAlertTable.fdAlertSubjectText.trim() != ''">
                        AND a.fd_alert_subject_text = #{ekpAlertTable.fdAlertSubjectText}
                    </if>
                    <if test="ekpAlertTable.fdEffectiveStartDate != null">
                        AND a.fd_effective_start_date = #{ekpAlertTable.fdEffectiveStartDate}
                    </if>
                    <if test="ekpAlertTable.fdEffectiveEndDate != null">
                        AND a.fd_effective_end_date = #{ekpAlertTable.fdEffectiveEndDate}
                    </if>
                    <if test="ekpAlertTable.fdDetectionMethod != null and ekpAlertTable.fdDetectionMethod.trim() != ''">
                        AND a.fd_detection_method = #{ekpAlertTable.fdDetectionMethod}
                    </if>
                    <if test="ekpAlertTable.fdThresholdValue != null">
                        AND a.fd_threshold_value = #{ekpAlertTable.fdThresholdValue}
                    </if>
                    <if test="ekpAlertTable.fdIsActive != null and ekpAlertTable.fdIsActive.trim() != ''">
                        AND a.fd_is_active = #{ekpAlertTable.fdIsActive}
                    </if>
                    <if test="ekpAlertTable.fdAlertMessage != null and ekpAlertTable.fdAlertMessage.trim() != ''">
                        AND a.fd_alert_message = #{ekpAlertTable.fdAlertMessage}
                    </if>
                    <if test="ekpAlertTable.fdEverydayTime != null">
                        AND a.fd_everyday_time = #{ekpAlertTable.fdEverydayTime}
                    </if>
                    <if test="ekpAlertTable.fdChecktype != null and ekpAlertTable.fdChecktype.trim() != ''">
                        AND a.fd_checktype = #{ekpAlertTable.fdChecktype}
                    </if>
                    <if test="ekpAlertTable.fdMonthday != null">
                        AND a.fd_monthday = #{ekpAlertTable.fdMonthday}
                    </if>
        </if>
    </sql>
    <!--ekpAlertTable简单分页查询-->
    <select id="getEkpAlertTablePage" resultMap="ekpAlertTableMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM ekp_alert_table a
        <where>
            1=1
            <include refid="ekpAlertTable_where"/>
        </where>
    </select>

	<select id="getBudgetWarningVos" resultMap="budgetWarningMap">
		select
			a.fd_ke_mu as 'km',
				DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i") as 'at',
				ROUND((sum(a.fd_money) - sum(ifnull(b.fd_money,0))),2) as 'ye'
				,ROUND(sum(ifnull(b.fd_money,0))/sum(a.fd_money)*100,2) as 'bl',
				a.fd_cheng_ben_two as 'cbzx',c.txr
		from (select sum(a.fd_money) fd_money,a.fd_cheng_ben_two,a.fd_ke_mu,a.fd_fee_type,a.fd_id from view_xing_zheng_yu_suan_all a where a.fd_date_month like concat(DATE_FORMAT(now(),'%Y-'),'%') GROUP BY a.fd_ke_mu,a.fd_cheng_ben_two) a
			left join (
			select
				ifnull(sum(a.fd_money),0) fd_money,fd_ke_mu,fd_cheng_ben_two
			from view_xing_zheng_yu_suan a where DATE_FORMAT(a.fd_date,'%Y') = DATE_FORMAT(now(),'%Y')
			GROUP BY fd_ke_mu,fd_cheng_ben_two
		) b on b.fd_ke_mu = a.fd_ke_mu and b.fd_cheng_ben_two = a.fd_cheng_ben_two
			LEFT JOIN (
			select  GROUP_CONCAT(DISTINCT CONCAT(d.fd_login_name,"_",d.fd_id)) as 'txr',cb.fd_3c290891cded14 from  ekp_3cf9b7112e544ba9fe18 cb
			LEFT JOIN ekporg_18ca06406b8e5d35104b t on t.fd_parent_id=cb.fd_id
			LEFT JOIN sys_org_person d on t.sys_org_person_id=d.fd_id
			GROUP BY cb.fd_id
		) c on c.fd_3c290891cded14 = a.fd_cheng_ben_two
		where a.fd_ke_mu = #{km}
		GROUP BY a.fd_ke_mu,a.fd_cheng_ben_two ORDER BY a.fd_id
	</select>

	<select id="getEkpAlertTableList" resultMap="ekpAlertTableMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM ekp_alert_table a
		<where>
			1=1
			AND a.fd_effective_start_date <![CDATA[ <= ]]>  NOW()
			AND a.fd_effective_end_date >= NOW()
			AND a.fd_is_active = '是'
			<if test="type ==0 ">
				AND fd_checktype = '每月'
			</if>
			<if test="type ==1 ">
				AND fd_checktype = '每日'
			</if>
		</where>
	</select>

	<select id="insertTest" resultMap="ekpAlertTableMap">
		SET @row_number = 0;

		INSERT INTO ekp_social_settle_detail  (fd_id,fd_customer_name,fd_payment_unit,fd_project_no,fd_project_name,fd_person_count,fd_unit_persion,fd_unit_medical
											  ,fd_unit_unemployment,fd_unit_injury,fd_unit_birth,fd_unit_accrual,fd_unit_bigailment,fd_unit_sum,fd_personal_persion
											  ,fd_personal_medical,fd_personal_unemployment,fd_personal_accrual,fd_personal_bigailment,fd_personal_sum,fd_sum
											  ,fd_remark,fd_sort,fd_order_no)
		SELECT
			UUID() fd_id,
			fd_3adfe8c81a0e42  'fd_customer_name' ,
				fd_3b1480c04814fc_text  'fd_payment_unit' ,
				fd_3adfe8c70d3fd4  'fd_project_no' ,

				fd_3adfe8c8468e54  'fd_project_name' ,
				sum(1)  'fd_person_count' ,
				CAST(COALESCE (sum(fd_3af9f2d8df88c0), 0) AS DECIMAL (12, 2))  'fd_unit_persion' ,
				CAST(COALESCE (sum(fd_3af9f2db1e5e16), 0) AS DECIMAL (12, 2))  'fd_unit_medical' ,
				CAST(COALESCE (sum(fd_3af9f2dd5d8fb8), 0) AS DECIMAL (12, 2))  'fd_unit_unemployment' ,
				CAST(COALESCE (sum(fd_3af9f2e03295dc), 0) AS DECIMAL (12, 2))  'fd_unit_injury' ,
				CAST(COALESCE (sum(fd_3af9f2e237f794), 0) AS DECIMAL (12, 2))  'fd_unit_birth' ,
				CAST(COALESCE (sum(fd_3af9f2e45a5d78), 0) AS DECIMAL (12, 2))  'fd_unit_accrual' ,
				CAST(COALESCE (sum(fd_3af9f2e6dfb3f4), 0) AS DECIMAL (12, 2))  'fd_unit_bigailment' ,
				CAST(COALESCE (sum(fd_3af9f2e9208e4e), 0) AS DECIMAL (12, 2))  'fd_unit_sum' ,
				CAST(COALESCE (sum(fd_3af9f2ec516c30), 0) AS DECIMAL (12, 2))  'fd_personal_persion' ,
				CAST(COALESCE (sum(fd_3af9f2f8290f24), 0) AS DECIMAL (12, 2))  'fd_personal_medical' ,
				CAST(COALESCE (sum(fd_3af9f2fa79ee72), 0) AS DECIMAL (12, 2))  'fd_personal_unemployment' ,
				CAST(COALESCE (sum(fd_3af9f2fcde9158), 0) AS DECIMAL (12, 2))  'fd_personal_accrual' ,
				CAST(COALESCE (sum(fd_3af9f2ff2c9f42), 0) AS DECIMAL (12, 2))  'fd_personal_bigailment' ,
				CAST(COALESCE (sum(fd_3af9f303037214), 0) AS DECIMAL (12, 2))  'fd_personal_sum' ,
				CAST(COALESCE (sum(fd_3af9f3059e5b9c), 0) AS DECIMAL (12, 2))  'fd_sum' ,
				''  as 'fd_remark',
				(@row_number := @row_number + 1) as 'fd_sort',
				fd_3adfeb83a704c8 as 'fd_order_no'
		FROM
			ekp_social_info
				LEFT JOIN ekp_24a8e6a7fc143bb8c48a project ON fd_3adfe8c70d3fd4 = fd_3a37fe508071fe
		WHERE
				fd_3adfeb83a704c8 = #{str}

		GROUP BY
			fd_3adfe8c81a0e42,
			fd_3adfe8c8468e54,
			fd_3adfe8c70d3fd4,
			fd_3b1480c04814fc_text
	</select>

	<select id="getNos" resultType="java.lang.String">
		select a.fd_3adfeb83a704c8 from  ekp_social_info a where a.fd_3adfeb83a704c8 like concat(#{no}, '%')  and a.fd_3adfeb83a704c8 is not NULL GROUP BY a.fd_3adfeb83a704c8
	</select>

</mapper>
