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

    <insert id="savePlanDistMappingFile">
        insert into fhzg_plan_dist_mapping_file (plan_id, line_id, file_id, file_type,
                                                 creator, create_time, last_editor, last_time, del_flag)
        values (#{objId}, #{distFile.lineId}, #{distFile.fileId}, #{distFile.fileType},
                #{distFile.creator}, #{distFile.createTime}, #{distFile.lastEditor}, #{distFile.lastTime}, '0')
    </insert>

    <insert id="saveFaultDistMappingFile">
        insert into fhzg_fault_dist_mapping_file (fault_id, line_id, file_id, file_type,
                                                  creator, create_time, last_editor, last_time, del_flag)
        values (#{objId}, #{distFile.lineId}, #{distFile.fileId}, #{distFile.fileType},
                #{distFile.creator}, #{distFile.createTime}, #{distFile.lastEditor}, #{distFile.lastTime}, '0')
    </insert>

    <insert id="insertDistLineSwitchStatus">
        insert into fhzg_dist_line_switch_status (obj_id, line_id, switch_id, closed_state, is_auto_switch, create_time, last_time)
        values
        <foreach collection = 'lineSwitchList' item = 'item' separator = ',' >
            (#{item.objId},#{item.lineId},#{item.switchId},#{item.closedState},#{item.isAutoSwitch},#{item.createTime},#{item.lastTime})
        </foreach>
    </insert>

    <insert id="saveDistLineDynamicSvgDirectCurrent">
        insert into fhzg_dist_line_dynamic_svg_direct_current(obj_id, cl_list_json, kg_list_json, create_time)
        values (#{objId}, #{clJsonStr}, #{kgJsonStr}, now())
    </insert>

    <update id="updatePlanSchemeFileIdById">
        update fhzg_plan_transfer_scheme set file_id = #{fileId} where id = #{schemeId}
    </update>

    <update id="updateFaultSchemeFileIdById">
        update fhzg_fault_transfer_scheme set file_id = #{fileId} where id = #{schemeId}
    </update>

    <update id="updateOperationAbnormalityFileIdById">
        update fhzg_grid_operation_abnormality set file_id = #{fileId} where id = #{oaId}
    </update>

    <update id="updateOperationAbnormalitySchemeFileIdById">
        update fhzg_operation_abnormality_transfer_scheme set file_id = #{fileId} where id = #{schemeId}
    </update>

    <delete id="deletePlanDistMappingFile">
        delete from fhzg_plan_dist_mapping_file where plan_id = #{objId} and file_type = #{fileType}
    </delete>

    <delete id="deleteFaultDistMappingFile">
        delete from fhzg_fault_dist_mapping_file where fault_id = #{objId} and file_type = #{fileType}
    </delete>

    <delete id="deleteDistLineSwitchStatus">
        delete from fhzg_dist_line_switch_status where obj_id = #{objId}
    </delete>

    <delete id="deleteDistLineDynamicSvgDirectCurrent">
        delete from fhzg_dist_line_dynamic_svg_direct_current where obj_id = #{objId}
    </delete>

    <select id="getDeviceInfoById" resultType="com.hdkj.lt.modle.vo.svg.FeederBaseVO">
        select f.psr_id, f.name, ifnull(s.dict_label,f.city) city, ifnull(s.dict_label,f.city_name) city_name
        from dwd_shb_ds_feeder_base f
        inner join (select a.feeder, a.feeder_name from t_feeder_topo a
            join t_feeder_topo b on a.psr_id=b.psr_id where b.normal_open='true' and b.feeder=#{feederId}
            union select psr_id, `name` from dwd_shb_ds_feeder_base t where psr_id = #{feederId}
        ) p on f.psr_id = p.feeder left join sys_dict_data s on f.city = s.dict_value
        left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
    </select>

    <select id="getSwitchInfoById" resultType="com.hdkj.lt.modle.dto.svg.AppendSwitchDTO">
        <choose>
            <!-- objType：1-计划场景，2-计划方案场景，3-故障场景，4-故障方案场景 -->
            <when test='objType == "1"'>
                select p.line_id xlId, group_concat(distinct concat_ws(',',s.start_switch,s.end_switch)) kgId
                from fhzg_plan_power_cut_switch s join fhzg_plan_power_cut p on s.plan_id = p.id
                where s.plan_id = #{objId} group by p.line_id
            </when>
            <when test='objType == "2"'>
                select p.line_id xlId, group_concat(distinct concat_ws(',',a.switch_id,b.start_switch,b.end_switch)) kgId
                from fhzg_plan_transfer_scheme_switching_sequence a
                join fhzg_plan_transfer_scheme t on a.scheme_id = t.id
                join fhzg_plan_power_cut_switch b on t.plan_id = b.plan_id
                join fhzg_plan_power_cut p on b.plan_id = p.id where a.scheme_id = #{objId} group by p.line_id
                <!-- union all
                select s.feeder xlId, group_concat(distinct a.switch_id) kgId from (
                    SELECT switch_id,switching_id from fhzg_plan_transfer_scheme_switching_final_sw_current_detail where switch_current_check='0'
                    union all
                    SELECT switch_id,switching_id from fhzg_plan_transfer_scheme_switching_final_sw_current_detail where switch_current_check='0'
                ) a join fhzg_plan_transfer_scheme_switching_sequence b on a.switching_id = b.id
                join dwd_shb_ds_switch_base s on a.switch_id = s.psr_id and s.psr_state = '20' where b.scheme_id = #{objId} group by s.feeder -->
            </when>
            <when test='objType == "3"'>
                select p.line_id xlId, group_concat(distinct concat_ws(',',s.start_switch,s.end_switch)) kgId
                from fhzg_fault_power_cut_switch s join fhzg_fault_power_cut p on s.fault_id = p.id
                where s.fault_id = #{objId} group by p.line_id
            </when>
            <otherwise>
                select p.line_id xlId, group_concat(distinct concat_ws(',',a.switch_id,b.start_switch,b.end_switch)) kgId
                from fhzg_fault_transfer_scheme_switching_sequence a
                join fhzg_fault_transfer_scheme t on a.scheme_id = t.id
                join fhzg_fault_power_cut_switch b on t.fault_id = b.fault_id
                join fhzg_fault_power_cut p on b.fault_id = p.id where a.scheme_id = #{objId} group by p.line_id
                <!-- union all
                SELECT s.feeder xlId, group_concat(distinct a.switch_id) kgId
                from fhzg_fault_transfer_scheme_switching_final_sw_current_detail a
                join fhzg_fault_transfer_scheme_switching_sequence b on a.switching_id = b.id
                join dwd_shb_ds_switch_base s on a.switch_id = s.psr_id and s.psr_state = '20'
                where a.switch_current_check = '0' and b.scheme_id = #{objId} group by s.feeder -->
            </otherwise>
        </choose>
    </select>

    <select id="getDeviceInfoByObjId" resultType="com.hdkj.lt.modle.vo.svg.FeederBaseVO">
        <choose>
            <!-- objType：5-调优事件，6-调优方案 -->
            <when test='objType == "5"'>
                SELECT f.psr_id, f.name, ifnull(s.dict_label,f.city) city, ifnull(s.bak2,f.city_name) city_name
                from dwd_shb_ds_feeder_base f
                join fhzg_operation_abnormality_line_loadfactor_loss x on f.psr_id = x.psr_id
                left join sys_dict_data s on f.city = s.dict_value
                left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
                where x.oa_id = #{objId}
            </when>
            <when test='objType == "7"'>
                SELECT distinct f.psr_id, f.name, ifnull(s.dict_label,f.city) city, ifnull(s.bak2,f.city_name) city_name
                from dwd_shb_ds_feeder_base f
                join fhzg_voltage_limit_violation_transfer_scheme_detail x on f.psr_id = x.line_id
                left join sys_dict_data s on f.city = s.dict_value
                left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
                where x.limit_id = #{objId} and (x.line_tag = 1 or x.line_tag = 0)
            </when>
            <when test='objType == "8"'>
                SELECT distinct f.psr_id, f.name, ifnull(s.dict_label,f.city) city, ifnull(s.bak2,f.city_name) city_name
                from dwd_shb_ds_feeder_base f
                join fhzg_voltage_limit_violation_transfer_scheme_detail x on f.psr_id = x.line_id
                join fhzg_info_limit_transfer_scheme y on x.limit_id = y.limit_id
                left join sys_dict_data s on f.city = s.dict_value
                left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
                where y.id = #{objId} and (x.line_tag = 1 or x.line_tag = 0)
            </when>
            <otherwise>
                SELECT f.psr_id, f.name, ifnull(s.dict_label,f.city) city, ifnull(s.bak2,f.city_name) city_name
                from dwd_shb_ds_feeder_base f
                join fhzg_operation_abnormality_line_loadfactor_loss x on f.psr_id = x.psr_id
                join fhzg_operation_abnormality_transfer_scheme y on x.oa_id = y.oa_id
                left join sys_dict_data s on f.city = s.dict_value
                left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
                where y.id = #{objId}
            </otherwise>
        </choose>
    </select>
    <select id="getLineSwitchInfo" resultType="com.hdkj.lt.modle.dto.svg.LineDeviceDTO">
        select t.feeder, t.feeder_name, t.psr_id, t.name psr_name, x.operation_type switch_state
          from fhzg_operation_abnormality_switching_sequence x
          join fhzg_operation_abnormality_transfer_scheme y on x.oa_id = y.oa_id
          join t_feeder_topo t on x.switch_id = t.psr_id where y.id = #{objId}
    </select>

    <select id="getLineInfo" resultType="java.lang.String">
        select x.psr_id from dwd_shb_ds_feeder_base x
        join fhzg_dist_organ_table_suffix_config y on x.city = y.org_id
        where y.tab_suffix=#{cityShort} and x.psr_state='20' and x.pub_priv_flag='0'
    </select>

    <select id="getNonLineSceneInfo" resultType="com.hdkj.lt.si.modle.TransferSceneVO">
        SELECT t.* from (
            select t.line_id, t.id obj_id, '1' obj_type from fhzg_plan_power_cut t
            union all
            select c.line_id, t.id obj_id, '2' obj_type from fhzg_plan_power_cut c
            join fhzg_plan_transfer_scheme t on t.plan_id = c.id
            union all
            select t.line_id, t.id obj_id, '3' obj_type from fhzg_fault_power_cut t
            union all
            select c.line_id, t.id obj_id, '4' obj_type from fhzg_fault_power_cut c
            join fhzg_fault_transfer_scheme t on t.fault_id = c.id
            union all
            select t.line_id, t.id obj_id, '5' obj_type from fhzg_grid_operation_abnormality t
            union all
            select a.line_id, t.id obj_id, '6' obj_type from fhzg_grid_operation_abnormality a
            join fhzg_operation_abnormality_transfer_scheme t on t.oa_id = a.id
        ) t join dwd_shb_ds_feeder_base x on t.line_id = x.psr_id
        join fhzg_dist_organ_table_suffix_config y on x.city = y.org_id
        where y.tab_suffix=#{cityShort} and x.psr_state='20' and x.pub_priv_flag='0'
    </select>

    <select id="getTieSwitchInfoByFeeder" resultType="com.hdkj.lt.modle.dto.svg.LineDeviceDTO">
        select b.XL_ID_ONE feeder, b.XL_NAME_ONE feeder_name, b.llsb_id psr_id,
               b.LLSB_NAME psr_name, if(a.llsb_type in ('2','3'),'valid','invalid') switch_state
          from t_disable_llkg_config a join ct_xl_rel b on a.llsb_id = b.llsb_id
         where b.XL_ID_ONE in <foreach collection="feederIds" open="(" item="feeder" separator="," close=")">#{feeder}</foreach>
        union all
        select b.XL_ID_TWO feeder, b.XL_NAME_TWO feeder_name, b.llsb_id psr_id,
               b.LLSB_NAME psr_name, if(a.llsb_type in ('2','3'),'valid','invalid') switch_state
          from t_disable_llkg_config a join ct_xl_rel b on a.llsb_id = b.llsb_id
         where b.XL_ID_TWO in <foreach collection="feederIds" open="(" item="feeder" separator="," close=")">#{feeder}</foreach>
    </select>

    <select id="getFeederAndSwitchInfoByStation" resultType="com.hdkj.lt.modle.dto.svg.StationDeviceDTO">
        with station_feeder as (
            select f.start_station station, f.start_station_name station_name, f.ctime,
                   f.psr_id feeder, f.`name` feeder_name, tft.psr_id, tft.name psr_name,
                   ifnull(s.dict_label,f.city) city, ifnull(s.bak2,f.city_name) city_name
            from dwd_shb_ds_feeder_base f left join (select distinct feeder,psr_id,`name`
                from t_feeder_topo where normal_open = 'true') tft on f.psr_id = tft.feeder
                left join sys_dict_data s on f.city = s.dict_value
                left join sys_dict_type t on s.dict_type = t.dict_type and t.dict_type = 'custom_storage'
            where f.psr_state = '20' and f.pub_priv_flag = '0'
            and f.start_station in <foreach collection="stationIds" open="(" item="station" separator="," close=")">#{station}</foreach>
        ) select station,station_name,feeder,feeder_name,psr_id,psr_name,switch_state,city,city_name,station_type
        from (select t.*, if(lc.llsb_type in ('2','3'),'valid','invalid') switch_state, '1' station_type
            from station_feeder t join t_disable_llkg_config lc on t.psr_id=lc.llsb_id order by t.ctime) sf
        union
        select sf.station, sf.station_name, tft.feeder, tft.feeder_name, tft.psr_id, tft.name psr_name,
            if(lc.llsb_type in ('2', '3'), 'valid', 'invalid') switch_state, sf.city, sf.city_name,
            if(tft.feeder in (select distinct feeder from station_feeder), '1', '2') station_type
        from station_feeder sf join t_feeder_topo tft on sf.psr_id = tft.psr_id and tft.normal_open = 'true'
        join t_disable_llkg_config lc on sf.psr_id = lc.llsb_id
    </select>

    <select id="getLineSwitchSectionDetails" resultType="com.hdkj.lt.modle.dto.svg.LineDeviceDTO">
        with recursive res_tab as (
            select t1.psr_id,t1.name psr_name,t1.feeder,t1.feeder_name,t1.psr_id start_id,t2.psr_id end_id,t1.psr_id link_id
            from t_feeder_topo t1 left join t_feeder_topo t2 on t1.feeder = t2.feeder and t2.psr_id = #{endKgId}
            where t1.psr_id = #{startKgId}
            union all
            select t.psr_id,t.name psr_name,t.feeder,t.feeder_name,p.start_id,p.end_id,if(t.psr_id=p.end_id,'-',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 = p.feeder
            and t.psr_type in <foreach collection='kgPsrTypes' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        ) select * from res_tab where link_id not in ('-', start_id)
    </select>
    <select id="getAutoSwitchInfo"
            resultType="com.hdkj.lt.core.bizms.modle.dto.StateEstimation$StateEstimationSwitchResult">
        select switch_id psr_id, is_auto, feeder_id from fhzg_feeder_auto_switch_mapping
         where feeder_id in <foreach collection='lineIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>
    </select>
    <select id="getLineSvgResetInfo" resultType="com.hdkj.lt.si.modle.TransferSceneVO">
        select feeder line_id, obj_id, obj_type from fhzg_dist_dynamic_svg_reset where del_flag = '0'
    </select>
    <update id="updateLineSvgReset">
        update fhzg_dist_dynamic_svg_reset set del_flag = '1' where feeder=#{feeder} and obj_type=#{objType}
        <if test="objId != null"> and obj_id = #{objId}</if>
    </update>

    <update id="updateLimitSchemeFileFileIdById">
        update fhzg_info_limit_transfer_scheme set file_id = #{fileId} where id = #{schemeId}
    </update>
    <update id="updateLimitFileFileIdById">
        update fhzg_voltage_limit_violation_power_cut set file_id = #{fileId} where id = #{schemeId}
    </update>
</mapper>
