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


    <select id="getTransferSchemePathDetailBySchemeId"
            resultType="com.hdkj.lt.core.bizms.modle.dto.FhzgPlanTransferSchemePathDetailDTO">
        SELECT
            de.id,
            de.scheme_id schemeId,
            de.opposite_line_id oppositeLineId,
            de.opposite_line_name oppositeLineName,
            de.switch_id switchId,
            de.switch_name switchName,
            de.switch_section_id switchSectionId,
            de.switch_section_name switchSectionName,
            de.switch_section_loads switchSectionLoads,
            de.load_factor loadFactor,
            de.start_switch_id startSwitchId,
            de.start_switch_name startSwitchName,
            de.end_switch_id endSwitchId,
            de.end_switch_name endSwitchName
        FROM
            fhzg_plan_transfer_scheme_path_detail de
        WHERE
            de.scheme_id =#{params.id}
        ORDER BY
            id DESC
    </select>

    <select id="getTransferSchemePathDetailByCombinationSchemeId"
            resultType="com.hdkj.lt.core.bizms.modle.dto.FhzgPlanTransferSchemePathDetailDTO">
        SELECT
            de.id,
            de.scheme_id schemeId,
            de.opposite_line_id oppositeLineId,
            de.opposite_line_name oppositeLineName,
            de.switch_id switchId,
            de.switch_name switchName,
            de.start_switch_id startSwitchId,
            de.start_switch_name startSwitchName,
            de.end_switch_id endSwitchId,
            de.end_switch_name endSwitchName,
            de.switch_section_id switchSectionId,
            de.switch_section_name switchSectionName,
            de.switch_section_loads switchSectionLoads,
            de.load_factor loadFactor
        FROM
            fhzg_plan_transfer_scheme_path_detail de
        WHERE
            de.scheme_id =#{id}
    </select>
    <select id="getTotalSwitchSectionLoads"
            resultType="com.hdkj.lt.core.bizms.modle.dto.FhzgPlanTransferSchemePathDetailDTO">
        select round(sum(t.switch_section_loads),6) totalSwitchSectionLoads from fhzg_plan_transfer_scheme_path_detail t where t.scheme_id=#{id}
    </select>

    <select id="getTransferSchemePathAndSwitchById"
            resultType="com.hdkj.lt.bf.entity.dto.FhzgPlanTransferSchemeSwitchingSequenceAndPathDTO">
        SELECT
            ss.id,
            ss.scheme_id schemeId,
            ss.switch_id switchId,
            ss.switch_name switchName,
            ss.switch_type switchType,
            ss.is_auto_switch isAutoSwitch,
            ss.operation_content operationContent,
            ss.switching_seq switchingSeq,
            ss.loop_close_check loopCloseCheck,
            ss.final_state_check finalStateCheck,
            de.opposite_line_id oppositeLineId,
            de.opposite_line_name oppositeLineName,
            de.switch_section_loads switchSectionLoads
        FROM
            fhzg_plan_transfer_scheme_switching_sequence ss
                left join  fhzg_plan_transfer_scheme_path_detail de
                on ss.switch_id = de.switch_id and ss.scheme_id = de.scheme_id
        WHERE
            ss.scheme_id =#{params.id}
        <if test="params.switchType != null and params.switchType !='' ">
            and ss.switch_type = #{params.switchType}
        </if>
        order by  ss.switching_seq asc
    </select>

    <select id="getTotalSwitchSectionTransfer"
            resultType="com.hdkj.lt.core.bizms.modle.dto.FhzgPlanTransferSchemePathDetailDTO">
        select sum(t.switch_section_transfer) totalSwitchSectionTransfer from fhzg_plan_transfer_scheme_path_detail t where t.scheme_id=#{id}
    </select>

    <select id="getFeederBusbarBySchemeId" resultType="com.alibaba.fastjson.JSONObject">
          with recursive res_tab as (
              select t.psr_id, t.psr_type, t.feeder, d.opposite_line_id xl_id, t.psr_id link_id, d.start_switch_id, d.end_switch_id
                from t_feeder_topo t join fhzg_plan_power_cut c on t.feeder=c.line_id
                join fhzg_plan_transfer_scheme s on c.id = s.plan_id
                join fhzg_plan_transfer_scheme_path_detail d on s.id = d.scheme_id
               where t.psr_id = d.start_switch_id and d.scheme_id = #{schemeId}
               union all
              select t.psr_id, t.psr_type, t.feeder, p.xl_id,
                     if(find_in_set(t.psr_id,p.end_switch_id)>0,'-',t.psr_id) link_id, p.start_switch_id, p.end_switch_id
                from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id where t.feeder = p.feeder
                 and t.psr_type in <foreach collection='kgPsrTypes' item='item' open="('PD_31100000'," separator=',' close=')'>#{item}</foreach>
          ) select * from res_tab where psr_type ='PD_31100000' and link_id not in ('-',start_switch_id)
    </select>

</mapper>
