<?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.mq.mapper.plan.PlanAwaitTransferAreaResultMapper">

    <insert id="saveBreakerSvgAfterBatch">
        <if test='breakerSvgAfterList != null and !breakerSvgAfterList.isEmpty()'>
            insert into fhzg_info_breaker_svg_after(scheme_id, start_switch_id, after_p, after_q, after_i, create_time,last_time)
            values
            <foreach collection='breakerSvgAfterList' item='item' separator=','>
                (#{item.schemeId},#{item.startSwitchId},#{item.afterP},#{item.afterQ},#{item.afterI},#{item.createTime},#{item.lastTime})
            </foreach>
        </if>
    </insert>

    <select id="getTransformerCustomerInfo" resultType="com.hdkj.lt.core.bizms.modle.po.FhzgDistTransformerCustomer">
        <choose>
            <!-- 当两个参数都为空时返回空结果 -->
            <when test="(trans == null or trans.isEmpty()) and (customers == null or customers.isEmpty())">
                SELECT transformer_no, transformer_name, max(is_sensitive_user) is_sensitive_user,
                    max(is_critical_user) is_critical_user, max(is_dual_power) is_dual_power
                FROM fhzg_dist_transformer_customer WHERE 1 = 0
                group by transformer_no, transformer_name
            </when>
            <otherwise>
                <!-- 第一部分：trans参数查询 -->
                <if test="trans != null and !trans.isEmpty()">
                    SELECT
                        t.transformer_no,
                        t.transformer_name,
                        MAX(t.is_sensitive_user) is_sensitive_user,
                        MAX(t.is_critical_user) is_critical_user,
                        MAX(t.is_dual_power) is_dual_power,
                        MAX(if(c.protection_start_time &lt; #{planEndTime} and #{planStartTime} &lt; c.protection_end_time, '1', '0')) AS is_protect_power
                    FROM fhzg_dist_transformer_customer t
                    left join fhzg_customer_power_protection_config c on t.transformer_no = c.transformer_no
                    WHERE t.transformer_no IN
                    <foreach collection="trans" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    group by transformer_no, transformer_name
                </if>

                <if test="(trans != null and !trans.isEmpty()) and (customers != null and !customers.isEmpty())">
                    UNION ALL
                </if>

                <!-- 第二部分：customers参数查询 -->
                <if test="customers != null and !customers.isEmpty()">
                    SELECT
                        f.join_ec AS transformer_no,
                        f.join_ec AS transformer_name,
                        MAX(t.is_sensitive_user) AS is_sensitive_user,
                        MAX(t.is_critical_user) AS is_critical_user,
                        MAX(t.is_dual_power) AS is_dual_power,
                        MAX(if(c.protection_start_time &lt; #{planEndTime} and #{planStartTime} &lt; c.protection_end_time, '1', '0')) AS is_protect_power
                    FROM fhzg_dist_transformer_customer t
                    JOIN dwd_shb_ds_joinec_transformer_base f ON t.transformer_no = f.psr_id
                    left join fhzg_customer_power_protection_config c on t.transformer_no = c.transformer_no
                    WHERE f.join_ec IN
                    <foreach collection="customers" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    GROUP BY f.join_ec
                </if>
            </otherwise>
        </choose>
    </select>

    <select id="getSwitchSectionInfo" resultType="com.hdkj.lt.modle.dto.jsty.SwitchInfoDTO">
        with recursive res_tab as (
            select t.psr_id link_id, t.psr_id, t.name, t.start_kg_id from t_feeder_topo t
            where t.psr_id = #{startSwitchId} and t.feeder = #{lineId}
            union
            select if((t.normal_open='true' and t.psr_id != #{linkSwitchId})
            <if test='endSwitchIds != null and !endSwitchIds.isEmpty()'>
                  or t.psr_id in
                <foreach collection='endSwitchIds' item='item' open='(' separator=',' close=')'>#{item}</foreach>
            </if>,'-',t.psr_id) link_id, t.psr_id, t.name, t.start_kg_id
            from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id
            and t.psr_type in
            <foreach collection = 'switchTypes' item = 'item' open = '(' separator = ',' close = ')'>#{item}</foreach>
        ) select psr_id switch_id, `name` switch_name, if(link_id='-','end','mid') switch_type
        from res_tab where psr_id != #{startSwitchId}
    </select>

    <select id="selectPlanAndPlanTime" resultType="com.hdkj.lt.mq.entity.vo.PlanPowerCutVO">
        select cut.line_id, cut.line_name, res.plan_id,
               res.transfer_area_json, cut.plan_start_time, cut.plan_end_time,
               date_add(date(cut.create_time), interval '-7 0:0:0' day_second) period_start_time,
               date_add(date(cut.create_time), interval -1 second) period_end_time
          from fhzg_plan_power_cut cut
          join fhzg_plan_await_transfer_area_result res on cut.id = res.plan_id
         where res.plan_id = #{planId}
    </select>

    <select id="getBreakerSvgAfter" resultType="com.hdkj.lt.mq.entity.dto.SwitchTreeNode">
        with recursive res_tab as (
            select d.scheme_id, d.end_switch_id, t.feeder, t.feeder_name, t.psr_id, t.name, t.psr_type,
                t.psr_id link_id, cast(concat('scheme_id_',d.scheme_id) as char(100)) start_kg_id
            from t_feeder_topo t join fhzg_plan_transfer_scheme_path_detail d on t.psr_id=d.start_switch_id
            join fhzg_plan_transfer_scheme s on d.scheme_id = s.id and s.plan_id = #{planId}
            where t.feeder = #{feeder}
            union all
            select p.scheme_id, p.end_switch_id, t.feeder, t.feeder_name, t.psr_id, t.name, t.psr_type,
                if(find_in_set(t.psr_id, p.end_switch_id) > 0, '-', t.psr_id) link_id, t.start_kg_id
            from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id
            where t.feeder = #{feeder}
            and t.psr_type in <foreach collection = 'deviceTypes' item = 'item' open = '(' separator = ',' close = ')'>#{item}</foreach>
        ) select x.scheme_id, x.start_kg_id pid, x.psr_type, x.psr_id id, y.transformer_name name,
            y.active_power after_p, y.user_reactive_power after_q, y.user_current after_i
        from (select distinct a.scheme_id, if(b.psr_id is null, a.psr_id, b.join_ec) psr_id,
                a.start_kg_id, if(b.psr_id is null, a.psr_type, 'PD_37000000') psr_type from res_tab a
            left join dwd_shb_ds_joinec_transformer_base b on a.psr_id = b.psr_id where a.link_id != '-') x
        left join fhzg_plan_transferable_scheme_users y on x.scheme_id=y.scheme_id and x.psr_id=y.transformer_no
    </select>

</mapper>
