<?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.hdkj.lt.bf.mapper.CustomerPowerProtectionConfigMapper">

    <select id="getConfigPage" resultType="com.hdkj.lt.bf.entity.vo.CustomerPowerProtectionConfigVO">
        select pc.id,
               pc.transformer_no,
        pc.transformer_name,
        pc.transformer_list,
        pc.publ_clg_flag,
        tc.is_critical_user,
        tc.is_sensitive_user,
        CASE
        WHEN pc.protection_start_time &lt; now() AND pc.protection_end_time > now() THEN '01'
        WHEN pc.protection_start_time > now() THEN '02'
        WHEN ((pc.protection_start_time IS NULL AND pc.protection_end_time IS NULL) OR
        pc.protection_end_time &lt; now()) THEN '03'
        ELSE '-' END AS powerProtectionState,
        pc.protection_start_time protectionStartTime,
        pc.protection_end_time protectionEndTime
        from fhzg_customer_power_protection_config pc
        left join fhzg_dist_transformer_customer tc on pc.transformer_no = tc.transformer_no
        <where>
            <if test="params.transformerName != null and params.transformerName != ''">
                AND pc.transformer_name LIKE CONCAT('%',#{params.transformerName},'%')
            </if>
            <if test="params.publClgFlag != null and params.publClgFlag != ''">
                AND pc.publ_clg_flag = #{params.publClgFlag}
            </if>
            <if test="params.isCriticalUser != null and params.isCriticalUser != ''">
                AND tc.is_critical_user = #{params.isCriticalUser}
            </if>
            <if test="params.isSensitiveUser != null and params.isSensitiveUser != ''">
                AND tc.is_sensitive_user = #{params.isSensitiveUser}
            </if>
            <if test=" params.powerProtectionState == '01' ">
                AND pc.protection_start_time &lt; now() AND pc.protection_end_time &gt; now()
            </if>
            <if test=" params.powerProtectionState == '02' ">
                AND pc.protection_start_time &gt; now()
            </if>
            <if test=" params.powerProtectionState == '03' ">
                AND ((pc.protection_start_time IS NULL AND pc.protection_end_time IS NULL) OR pc.protection_end_time
                &lt; now())
            </if>

        </where>
        order by powerProtectionState asc, protectionStartTime desc, pc.id desc

        <!-- fhzg_dist_transformer_customer防止数据重复的处理 -->
        <!--        SELECT
                pc.id,
                tc.transformer_no transformerNo,
                tc.transformer_name transformerName,
                max(tc.publ_clg_flag) publClgFlag,
                max(tc.is_critical_user) isCriticalUser,
                max(tc.is_sensitive_user) isSensitiveUser,
                max(CASE
                WHEN pc.protection_start_time &lt; now() AND pc.protection_end_time &gt; now() THEN '01'
                WHEN pc.protection_start_time &gt; now() THEN '02'
                WHEN ((pc.protection_start_time IS NULL AND pc.protection_end_time IS NULL) OR pc.protection_end_time &lt;
                now()) THEN '03'
                ELSE '-'
                END) AS powerProtectionState,
                max(pc.protection_start_time) protectionStartTime,
                max(pc.protection_end_time) protectionEndTime
                FROM fhzg_dist_transformer_customer tc
                LEFT JOIN fhzg_customer_power_protection_config pc ON tc.transformer_no = pc.transformer_no
                <where>
                    <if test="params.transformerName != null and params.transformerName != ''">
                        AND tc.transformer_name LIKE CONCAT('%',#{params.transformerName},'%')
                    </if>
                    <if test="params.publClgFlag != null and params.publClgFlag != ''">
                        AND tc.publ_clg_flag = #{params.publClgFlag}
                    </if>
                    <if test="params.isCriticalUser != null and params.isCriticalUser != ''">
                        AND tc.is_critical_user = #{params.isCriticalUser}
                    </if>
                    <if test="params.isSensitiveUser != null and params.isSensitiveUser != ''">
                        AND tc.is_sensitive_user = #{params.isSensitiveUser}
                    </if>
                    <if test=" params.powerProtectionState == '01' ">
                        AND pc.protection_start_time &lt; now() AND pc.protection_end_time &gt; now()
                    </if>
                    <if test=" params.powerProtectionState == '02' ">
                        AND pc.protection_start_time &gt; now()
                    </if>
                    <if test=" params.powerProtectionState == '03' ">
                        AND ((pc.protection_start_time IS NULL AND pc.protection_end_time IS NULL) OR pc.protection_end_time &lt; now())
                    </if>
                </where>
                group by pc.id, tc.transformer_no, tc.transformer_name
                order by powerProtectionState asc, protectionStartTime desc, pc.id desc-->
    </select>
    <select id="getJoinecType" resultType="com.hdkj.lt.bf.entity.vo.CustomerPowerProtectionConfigVO">
        select join_ec as transformerNo,
        ifnull(0,max(is_critical_user)) as is_critical_user,
        ifnull(0,max(is_sensitive_user)) as is_sensitive_user
        from dwd_shb_ds_joinec_transformer_base jt
        left join dwd_shb_ds_joinec_base jb on jb.psr_id = jt.join_ec
        left join dwd_shb_ds_transformer_base tb on jt.psr_id = tb.psr_id and tb.psr_state = '20'
        left join fhzg_dist_transformer_customer tc on jt.psr_id = tc.transformer_no
        where jt.psr_state = '20' and join_ec in
        <foreach collection="transformerNoList" item="transformerNo" open="(" separator="," close=")">
            #{transformerNo}
        </foreach>
        group by join_ec;
    </select>

    <insert id="insertAllData">
        <!-- fhzg_dist_transformer_customer代理主键变更 且防止数据重复的处理 -->
        INSERT INTO fhzg_customer_power_protection_config(id, transformer_no, transformer_name, create_time)
        with max_num as (select max(id) id from fhzg_customer_power_protection_config)
        select (select id from max_num) + row_number()over() as id, t.*, now() as create_time
        from (SELECT distinct transformer_no, transformer_name FROM fhzg_dist_transformer_customer) t
    </insert>

</mapper>
