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

    <select id="selectPowerCutSwitchList" resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanPowerCutSwitch"
            parameterType="com.hdkj.lt.core.bizms.modle.dto.SchemeQueryDTO">
        select a.id,
               a.plan_id,
               a.start_switch,
               a.end_switch,
               a.remark,
               a.status,
               a.is_full_line
        from fhzg_plan_power_cut_switch a
        where a.plan_id = #{planId}
    </select>


    <select id="selectFhzgPlanById" resultType="com.hdkj.lt.bf.entity.FhzgPlanPowerCut">
        select a.id,
               a.plan_no,
               a.plan_start_time,
               a.plan_end_time,
               a.plan_source,
               a.line_id,
               a.line_name,
               a.line_content,
               a.work_content,
               a.strategy_id,
               a.feeder_name,
               a.feeder_list_json,
               a.city_code,
               a.city_name,
               a.plan_state,
               a.scheme_state
        from fhzg_plan_power_cut a
        where a.id = #{planId}
    </select>

    <select id="selectSwitchSectionList"
            resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanAwaitTransferSwitchSection">
        select a.id,
               a.plan_id,
               a.switch_name,
               a.start_switch_id,
               a.start_switch_name,
               a.start_switch_type,
               a.end_switch_id,
               a.end_switch_name
        from fhzg_plan_await_transfer_switch_section a
        where a.plan_id = #{planId}
    </select>

    <select id="selectCustomerById" resultType="com.hdkj.lt.bf.entity.FhzgDistCustomer">
        select a.id,
               a.cust_no,
               a.cust_name,
               a.cust_voltage,
               a.transformer_no,
               a.transformer_name,
               a.zone_no,
               a.zone_name,
               a.user_type,
               a.is_critical_user,
               a.is_sensitive_user
        from fhzg_dist_pv_customer a
        where a.id = #{customerId}
    </select>


    <select id="selectSwitchByCustomer" resultType="com.hdkj.lt.bf.entity.vo.SwitchByUserVO">
        select b.psr_id, a.psr_id as join_id
        from t_feeder_topo a
        left join t_feeder_topo b on a.start_kg_id = b.psr_id and b.psr_type in
        <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        where a.psr_id = #{psrId}
    </select>

    <select id="getOppositeLineCarryCapacity"
            resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanOppositeLineCarryCapacity">
        select id,
               plan_id,
               rated_capacity,
               rated_current,
               max_load_factor,
               deliverable_capacity,
               line_id,
               line_name
        from fhzg_plan_opposite_line_carry_capacity t
        where t.id = #{capacityId}
    </select>

    <select id="getOppositeLineConcatSwitch"
            resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanOppositeLineConcatSwitch">
        select t.id, t.scheme_id, t.capacity_id, t.concat_psr_id, t.concat_psr_name
        from fhzg_plan_opposite_line_concat_switch t,
             fhzg_plan_transfer_scheme s
        where t.scheme_id = s.id
          and s.plan_id = #{planId}
    </select>

    <select id="getUserNumsByLine" resultType="com.hdkj.lt.bf.entity.vo.LoadSwitchUserNumsVO">
        select show_switch_id as psrId,
        sum(users_count) as userNum,
        sum(if(psr_id = show_switch_id, null, users_count)) as switchNum
        from fhzg_dist_line_switch_section_show
        where obj_id = #{feeder} and psr_type in
        <foreach collection='kgPsrTypes' item='item' open="(" separator=',' close=')'>#{item}</foreach>
        group by show_switch_id;
    </select>

    <select id="selectSwitchByCustomerId" resultType="java.lang.String">
        SELECT d.show_switch_id load_switch_id
        from fhzg_plan_power_cut_pv_users a
                 join fhzg_dist_pv_customer b on a.cust_id = b.cust_id
                 join t_feeder_topo c on b.transformer_no = c.psr_id
                 join fhzg_dist_line_switch_section_show d on c.start_kg_id = d.psr_id and d.obj_id = c.feeder
        where a.cust_id = #{customerId}
        GROUP BY show_switch_id;
    </select>
    <select id="getAllUserNumsByLine" resultType="com.hdkj.lt.bf.entity.vo.LoadSwitchUserNumsVO">
        SELECT distinct c.start_kg_id AS psr_id,
        user_count AS userNum
        FROM t_feeder_topo a
        JOIN (SELECT psr_id FROM t_feeder_topo WHERE feeder = #{feeder} AND normal_open = 'true') b ON a.psr_id =
        b.psr_id
        left join (select t.feeder, start_kg_id, count(distinct if(d.psr_id is null,t.psr_id,d.join_ec)) user_count
        from t_feeder_topo t left join dwd_shb_ds_joinec_transformer_base d on t.psr_id = d.psr_id
        where t.psr_type in
        <foreach collection='pbPsrTypes' item='item' open='(' separator=',' close=')'>#{item}</foreach>
        and t.feeder = #{feeder} group by t.feeder,t.start_kg_id) c ON c.feeder = a.feeder
    </select>

    <select id="getUserListByLoadSwitchSegmentPage" resultType="com.hdkj.lt.core.bizms.modle.vo.UserListVO">
        <if test=" params.queryType == 1 ">
            with recursive res_tab as (select t.feeder,
            t.feeder_name,
            t.psr_id,
            t.name,
            t.psr_type,
            t.start_kg_id,
            t.psr_id link_id
            from t_feeder_topo t
            where psr_id = #{params.loadSwitchId}
            and feeder = #{params.lineId}
            union
            select t.feeder,
            t.feeder_name,
            t.psr_id,
            t.name,
            t.psr_type,
            t.start_kg_id,
            if(t.zxfz_type='主线','-',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 = #{params.lineId})
        </if>
        <if test=" params.queryType == 2 ">
            with res_tab as (
            select * from t_feeder_topo t where t.start_kg_id = #{params.loadSwitchId}
            )
        </if>
        select userId,
        userName,
        max(userType) userType,
        group_concat(psrId order by psrId desc SEPARATOR ',') prsIdList,
        group_concat(name order by psrId desc SEPARATOR ',') nameList,
        max(isCriticalUser) isCriticalUser,
        max(isSensitiveUser) isSensitiveUser,
        max(isDualPower) isDualPower
        from (select distinct if(b.psr_id is null, a.psr_id, b.join_ec) userId,
        if(b.psr_id is null, a.name, c.name) userName,
        if(b.psr_id is null, '0', '1') userType,
        if(b.psr_id is null, '', b.psr_id) psrId,
        if(b.psr_id is null, '', b.name) name,
        d.is_critical_user isCriticalUser,
        d.is_sensitive_user isSensitiveUser,
        d.is_dual_power isDualPower
        from res_tab a
        left join dwd_shb_ds_joinec_transformer_base b on a.psr_id = b.psr_id
        left join dwd_shb_ds_joinec_base c on b.join_ec = c.psr_id
        left join fhzg_dist_transformer_customer d on a.psr_id = d.transformer_no
        where a.psr_type in
        <foreach collection='pbPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        <if test=" params.queryType == 2 ">
            and a.start_kg_id =#{params.loadSwitchId}
        </if>
        ) temp
        group by userId, userName
        order by userType asc, userId

    </select>

    <select id="isMainFeeder" resultType="java.lang.Integer">
        SELECT count(psr_id)
        FROM t_feeder_topo
        WHERE feeder = #{feeder}
          AND normal_open = 'true'
    </select>
    <select id="getBreakerPsrId" resultType="java.lang.String">
        with recursive res_tab as (select t.*, t.psr_id link_id
        from t_feeder_topo t
        where t.feeder = #{feeder}
        and psr_id = #{startKg}
        union all
        select t.*,
        if(FIND_IN_SET(t.psr_id, #{endKg})>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 = #{feeder}
        and t.normal_open !='true'
        and t.psr_type in <foreach collection='kgPsrTypes' item='item' open="(" separator=',' close=')'>
        #{item}</foreach>)
        select psr_id
        from res_tab
        where link_id not in ('-', #{startKg});
    </select>

    <select id="getUserListByLoadSwitchSegmentPageNew" resultType="com.hdkj.lt.core.bizms.modle.vo.UserListVO">
        with res_tab as (
        select t.psr_id,t.psr_name from fhzg_dist_line_switch_section_show t where t.show_switch_id =
        #{params.loadSwitchId} and
        t.obj_id =#{params.objId} and t.psr_type in
        <foreach collection='pbPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        )
        select userId,
        userName,
        max(userType) userType,
        group_concat(psrId order by psrId desc SEPARATOR ',') prsIdList,
        group_concat(name order by psrId desc SEPARATOR ',') nameList,
        max(isCriticalUser) isCriticalUser,
        max(isSensitiveUser) isSensitiveUser,
        max(isDualPower) isDualPower
        from (select distinct if(b.psr_id is null, a.psr_id, b.join_ec) userId,
        if(b.psr_id is null, a.psr_name, c.name) userName,
        if(b.psr_id is null, '0', '1') userType,
        if(b.psr_id is null, '', b.psr_id) psrId,
        if(b.psr_id is null, '', b.name) name,
        d.is_critical_user isCriticalUser,
        d.is_sensitive_user isSensitiveUser,
        d.is_dual_power isDualPower
        from res_tab a
        left join dwd_shb_ds_joinec_transformer_base b on a.psr_id = b.psr_id
        left join dwd_shb_ds_joinec_base c on b.join_ec = c.psr_id
        left join fhzg_dist_transformer_customer d on a.psr_id = d.transformer_no
        ) temp
        group by userId, userName
        order by userType asc, userId
    </select>


    <select id="getSwitchInfoByLoadSwitchId" resultType="com.hdkj.lt.core.bizms.modle.vo.SwitchListVO">
        select t1.psr_name, t1.psr_id, t2.oc1_value, t2.oc1_delay, t2.oc2_value, t2.oc2_delay, t2.oc3_value,
        t2.oc3_delay, t2.reclose_enable_status
        from fhzg_dist_line_switch_section_show t1
        left join fhzg_switch_protection_settings t2 on t1.psr_id = t2.device_prs_id
        where show_switch_id = #{params.loadSwitchId} and obj_id = #{params.objId}
        and t1.psr_id != #{params.loadSwitchId} and t1.psr_type in
        <foreach collection='kgType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
    </select>

    <select id="getOtherSwitchByObjId" resultType="java.util.Map">
        select distinct t1.psr_id, t1.show_switch_id
        from fhzg_dist_line_switch_section_show t1
        join fhzg_dist_line_switch_section t2 on t1.show_switch_id = t2.start_switch_id and t1.obj_id = t2.obj_id
        where t1.psr_id != t1.show_switch_id and t1.obj_id = #{objId}
        and t1.psr_type in
        <foreach collection='kgType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
    </select>

    <select id="getBreakerPsrIdByPlanId" resultType="java.util.Map">
        select ifnull(end_switch, start_switch) start_switch_id, c.psr_id end_switch_id
        from fhzg_plan_power_cut_switch a
                 join fhzg_plan_power_cut b on a.plan_id = b.id
                 join t_feeder_topo c on b.line_id = c.feeder and c.normal_open = 'true'
        where a.plan_id = #{planId}
    </select>

    <select id="getBreakerPsrIdByFaultId" resultType="java.util.Map">
        select ifnull(end_switch, start_switch) start_switch_id, c.psr_id end_switch_id
        from fhzg_fault_power_cut_switch a
                 join fhzg_fault_power_cut b on a.fault_id = b.id
                 join t_feeder_topo c on b.line_id = c.feeder and c.normal_open = 'true'
        where a.fault_id = #{faultId}
    </select>

    <select id="getBusbarBySwitchIds" resultType="java.lang.String">
        select psr_id from t_feeder_topo t where t.psr_type = 'PD_31100000'
        and t.start_kg_id in
        <foreach collection='switchIds' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </select>
    <select id="getStationName" resultType="java.lang.String">
        SELECT GROUP_CONCAT(DISTINCT tb.station_name SEPARATOR '-') AS name
        FROM dwd_shb_ds_joinec_transformer_base jt
                 LEFT JOIN dwd_shb_ds_joinec_base jb on jb.psr_id = jt.join_ec
                 JOIN dwd_shb_ds_transformer_base tb on jt.psr_id = tb.psr_id and tb.psr_state = '20'
        WHERE jt.join_ec = #{psrId}
    </select>
    <select id="getSwitchListByOutArea" resultType="java.lang.String">
        select out_area
        from fhzg_graph_result
        where plan_id = #{id} and out_area is not null
    </select>
    <select id="getPathOverviewSwitch" resultType="java.lang.String">
        select path_overview_switch
        from fhzg_graph_result
        where plan_id = #{id}
          and scheme_id = #{schemeId} and path_overview_switch is not null
    </select>
    <select id="getSwitchListBySwitchSection" resultType="java.lang.String">
        select switch_list_by_switch_section
        from fhzg_graph_result
        where plan_id = #{id} and switch_list_by_switch_section is not null
    </select>
    <select id="getZbPsrId" resultType="java.lang.String">
        SELECT join_ec
        FROM dwd_shb_ds_joinec_transformer_base
        WHERE psr_id = #{psrId}
    </select>
    <insert id="saveSwitchListByOutArea">
        insert into fhzg_graph_result (plan_id, out_area) value (#{id}, #{outArea});
    </insert>
    <insert id="savePathOverviewSwitch">
        insert into fhzg_graph_result (plan_id, scheme_id, path_overview_switch) value (#{id}, #{schemeId}, #{PathOverviewSwitch});
    </insert>
    <insert id="saveSwitchListBySwitchSection">
        insert into fhzg_graph_result (plan_id, switch_list_by_switch_section) value (#{id},#{SwitchListBySwitchSection});
    </insert>

</mapper>
