<?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.si.dao.DistLineSwitchSectionMapper">

    <insert id="saveDistLineSwitchSectionShow">
        insert into fhzg_dist_line_switch_section_show (obj_id, psr_id, psr_name, psr_type, show_switch_id,
                                                        show_switch_name, switch_loads, users_count, create_time, last_time)
        with recursive res_tab as (
            select t.psr_id parent_psr_id,t.name parent_name,t.psr_id,t.name,s.end_switch_id,t.feeder,t.psr_type,t.psr_id link_id
            from t_feeder_topo t join fhzg_dist_line_switch_section s on t.psr_id = s.start_switch_id and s.obj_id = #{objId}
            where t.feeder = #{feederId}
            union all
            select p.parent_psr_id, p.parent_name, t.psr_id, t.name, p.end_switch_id, t.feeder, t.psr_type,
                   if(find_in_set(t.psr_id,p.end_switch_id) > 0, '~', t.psr_id) link_id
            from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id where t.feeder = #{feederId}
            and t.psr_type in <foreach collection='psrType' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        )
        select #{objId},t.psr_id,t.name,t.psr_type,t.parent_psr_id,t.parent_name,0,0,now(),now() from res_tab t
        where t.link_id != '~' and t.psr_type in <foreach collection='kgType' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        union all
        select #{objId},t.psr_id,t.name,t.psr_type,t.parent_psr_id,t.parent_name,0,0,now(),now()
        from res_tab t join fhzg_dist_transformer_customer f on t.psr_id = f.transformer_no
        where t.link_id != '~' and t.psr_type in <foreach collection='pbType' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </insert>
    <insert id="saveDistDynamicSvgParameter">
        insert into fhzg_dist_dynamic_svg_parameter
            (obj_id, obj_type, switch_id, parameter_info, call_result, create_time)
        values (#{objId}, #{objType}, #{switchId}, #{parameterInfo}, #{callResult},now())
    </insert>

    <update id="batchUpdateFileIds">
        update fhzg_dist_line_switch_section set file_id = case id
        <foreach collection="switchSectionList" item="item">
            when #{item.id} then #{item.fileId}
        </foreach>
        end where id in
        <foreach collection="switchSectionList" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <delete id="deleteDistLineSwitchSectionShowByLineId">
        delete from fhzg_dist_line_switch_section_show where obj_id = #{objId}
    </delete>

    <select id="getSwitchSection" resultType="java.util.Map">
        with device_tab as (
            <foreach collection="psrIds" item="psrId" separator=" psr_id union ">
                select #{psrId}
            </foreach>
        ), switch_res as (select * from (
            with recursive res_tab as (
                select t.name start_name, t.psr_id start_id, t.name end_name, t.psr_id end_id,
                    if(t.is_start_kg='1','~',t.start_kg_id) link_id, t.sszf_psr_id, t.sszf_name
                from t_feeder_topo t where t.feeder = #{feederId}
                and t.start_kg_id is not null and t.psr_id in (select psr_id from device_tab)
                union all
                select t.name start_name, t.psr_id start_id, p.end_name, p.end_id,
                    if(t.psr_id in (select psr_id from device_tab),'~',t.start_kg_id) link_id, t.sszf_psr_id, t.sszf_name
                from t_feeder_topo t join res_tab p on t.psr_id = p.link_id
                where t.feeder = #{feederId} and t.psr_type in
                <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
            ) select distinct b.* from res_tab b where b.link_id = '~') t where end_id in (select * from device_tab)
        ) SELECT * from switch_res t
        union
        SELECT end_name start_name, end_id start_id, '/',  '/' ,null,null,null
        from switch_res t where t.end_id not in (SELECT start_id from switch_res t)
    </select>

    <select id="getSwitchCount" resultType="java.lang.Long">
        with recursive res_tab as (
            select t.*, t.psr_id link_id
            from t_feeder_topo t
            where t.feeder = #{feederId} and t.psr_id = #{startId}
            union all
            select t.*, if(t.psr_id in
                <foreach collection='endIds' item='item' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
                ,'~',t.psr_id) link_id
            from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id
            where t.feeder = #{feederId}
            and t.psr_type in
                <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
        )
        select count(DISTINCT psr_id)
        from res_tab
        where psr_id != #{startId} and psr_id not in
            <foreach collection = 'endIds' item = 'item' open = '(' separator = ',' close = ')'>
                #{item}
            </foreach>
         and psr_type in
              <foreach collection = 'kgPsrType' item = 'item' open = '(' separator = ',' close = ')'>
                #{item}
              </foreach>
    </select>

    <select id="getTransformerCount" resultType="java.util.Map">
        with recursive res_tab as (
            select t.*, t.psr_id link_id
            from t_feeder_topo t
            where t.feeder = #{feederId} and t.psr_id = #{startId}
            union all
            select t.*, if(t.psr_id in
                <foreach collection='endIds' item='item' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
                ,'~',t.psr_id) link_id
            from t_feeder_topo t join res_tab p on t.start_kg_id = p.link_id
            where t.feeder = #{feederId} and t.psr_type in
            <foreach collection = 'psrType' item = 'item' open = '(' separator = ',' close = ')'>
                #{item}
            </foreach>
        )
        select count(case when f.publ_clg_flag = '01' then 1 end) gb_num ,count(*) pb_num
        from res_tab t
        join fhzg_dist_transformer_customer f on t.psr_id = f.transformer_no
        where t.psr_type in
        <foreach collection = 'pbPsrType' item = 'item' open = '(' separator = ',' close = ')'>
            #{item}
        </foreach>
    </select>

    <select id="getLineSwitchSectionDetails" resultType="com.hdkj.lt.modle.dto.svg.AppendDeviceDTO">
        select x.psr_id, x.psr_name, x.show_switch_id switch_id, x.show_switch_name switch_name, y.id kgd_id
        from fhzg_dist_line_switch_section_show x join fhzg_dist_line_switch_section y on x.obj_id = y.obj_id
        and x.show_switch_id = y.start_switch_id where x.obj_id = #{objId} and x.psr_id != x.show_switch_id
        and x.psr_type in
        <foreach collection = 'kgPsrTypes' item = 'item' open = '(' separator = ',' close = ')'>
            #{item}
        </foreach>
    </select>
</mapper>