<?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.voltageLimitViolation.FhzgInfoLimitTransferSchemeSwitchingSequenceMapper">

    <resultMap id="BaseResultMap"
               type="com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgInfoLimitTransferSchemeSwitchingSequence">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="schemeId" column="scheme_id" jdbcType="BIGINT"/>
        <result property="switchId" column="switch_id" jdbcType="VARCHAR"/>
        <result property="switchName" column="switch_name" jdbcType="VARCHAR"/>
        <result property="switchType" column="switch_type" jdbcType="VARCHAR"/>
        <result property="isAutoSwitch" column="is_auto_switch" jdbcType="VARCHAR"/>
        <result property="operationContent" column="operation_content" jdbcType="VARCHAR"/>
        <result property="switchingSeq" column="switching_seq" jdbcType="INTEGER"/>
        <result property="loopCloseCheck" column="loop_close_check" jdbcType="VARCHAR"/>
        <result property="finalStateCheck" column="final_state_check" jdbcType="VARCHAR"/>
        <result property="creator" column="creator" jdbcType="BIGINT"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="lastEditor" column="last_editor" jdbcType="BIGINT"/>
        <result property="lastTime" column="last_time" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        id
        ,scheme_id,switch_id,
        switch_name,switch_type,is_auto_switch,
        operation_content,switching_seq,loop_close_check,
        final_state_check,creator,create_time,
        last_editor,last_time
    </sql>

    <select id="getLineNameByPlanId"
            resultType="com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgVoltageLimitViolationPowerCut">
        select line_id, line_name
        from fhzg_voltage_limit_violation_power_cut
        where id = #{planId}
    </select>

    <select id="getSwitchSection" resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanAwaitTransferSwitchSection">
        WITH RECURSIVE hierarchy AS (
        SELECT psr_id, `name`, start_kg_id, psr_type, psr_id link_id
        FROM t_feeder_topo
        WHERE feeder = #{feeder}
        AND psr_id = #{psrId}
        UNION ALL
        SELECT child.psr_id, child.name, child.start_kg_id, child.psr_type,
        if(child.normal_open = 'true', '-', child.psr_id) link_id
        FROM t_feeder_topo child
        JOIN hierarchy parent ON child.start_kg_id = parent.psr_id
        WHERE child.feeder = #{feeder}
        AND child.psr_type IN
        <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        ) SELECT parent.psr_id AS startSwitchId, parent.name AS startSwitchName, parent.psr_type as startSwitchType,
        GROUP_CONCAT(child.psr_id SEPARATOR ',') AS endSwitchId,GROUP_CONCAT(child.name SEPARATOR ',') AS endSwitchName
        FROM hierarchy child RIGHT JOIN hierarchy parent ON child.start_kg_id = parent.psr_id where parent.link_id !=
        '-'
        GROUP BY parent.psr_id, parent.name, parent.psr_type
    </select>

    <insert id="saveSwitchSectionBatch"
            parameterType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanAwaitTransferSwitchSection"
            useGeneratedKeys="true" keyProperty="id">
        insert into
        fhzg_plan_await_transfer_switch_section(plan_id,switch_name,start_switch_id,start_switch_name,area_psr_id,
        start_switch_type,end_switch_id,end_switch_name,switch_loads,creator,create_time)
        values
        <foreach collection='switchSectionList' item='switchSection' separator=','>
            (#{switchSection.planId},#{switchSection.switchName},#{switchSection.startSwitchId},#{switchSection.startSwitchName},
            #{switchSection.areaPsrId},#{switchSection.startSwitchType},#{switchSection.endSwitchId},#{switchSection.endSwitchName},
            #{switchSection.switchLoads},'111',NOW())
        </foreach>
    </insert>

    <select id="getSwitchSectionList"
            resultType="com.hdkj.lt.core.bizms.modle.po.plan.FhzgPlanAwaitTransferSwitchSection">
        select t.* from (
        select t.psr_id, if(count(t.id) > 1, max(kg_section_id), max(id)) id,
        if(count(t.id) > 1, max(kg_id), max(start_switch_id)) start_switch_id,
        if(count(t.id) > 1, max(kg_name), max(start_switch_name)) start_switch_name
        from (
        select ifnull(c.join_ec,a.psr_id) psr_id, b.id, b.start_switch_id, b.start_switch_name,
        e.id kg_section_id, b.start_switch_id kg_id, e.start_switch_name kg_name
        from t_feeder_topo a
        join fhzg_plan_await_transfer_switch_section b on a.start_kg_id = b.start_switch_id
        left join dwd_shb_ds_joinec_transformer_base c on a.psr_id = c.psr_id
        left join t_feeder_topo d on a.start_kg_id = d.psr_id
        left join fhzg_plan_await_transfer_switch_section e on d.start_kg_id = e.start_switch_id
        where a.psr_type in
        <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        and a.feeder = #{feeder}
        and b.plan_id = #{planId}
        ) t group by t.psr_id
        ) t where t.psr_id in
        <foreach collection='psyIdList' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
    </select>
    <select id="getLineVoltageByLineId" resultType="com.hdkj.lt.mq.entity.vo.FeederDeviceVO">
        select t.psr_id,t.name,t.feeder_rate_capacity,s.dict_label volt_value
        from dwd_shb_ds_feeder_base t
        join sys_dict_data s on t.voltage_level=s.dict_value
        join sys_dict_type d on d.dict_type = s.dict_type
        where d.dict_type = 'voltage_level_map'
        and t.psr_id in
        <foreach collection='lineIds' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </select>

    <update id="updateSwitchSectionSwitchLoads">
        update fhzg_plan_await_transfer_switch_section a
        set a.switch_loads
                = (select sum(user_loads) from fhzg_plan_await_transfer_users b where b.switch_section_id = a.id)
        where a.plan_id = #{planId}
    </update>

    <update id="updateSwitchingSequenceIsConnect">
        update fhzg_plan_transfer_scheme_switching_sequence a
        set switch_type = "1"
        where exists (select 1
                      from fhzg_plan_transfer_scheme_path_detail b
                      where b.switch_id = a.switch_id and b.scheme_id = a.scheme_id)
          and a.scheme_id in (select id from fhzg_plan_transfer_scheme where plan_id = #{planId})
    </update>

    <delete id="deleteTransferUserByPlanId">
        delete
        from fhzg_plan_await_transfer_users
        where plan_id = #{planId}
    </delete>

    <delete id="deleteSwitchSectionByPlanId">
        delete
        from fhzg_plan_await_transfer_switch_section
        where plan_id = #{planId}
    </delete>

    <insert id="saveSwitchingLoopCheck"
            parameterType="com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgLimitTransferSchemeSwitchingLoopCheck"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_limit_transfer_scheme_switching_loop_check(id, switching_id, line_id, line_name,
                                                                   opposite_line_id, opposite_line_name,
                                                                   phase_angle_diff, phase_angle_diff_threshold,
                                                                   phase_angle_diff_check, line_voltage,
                                                                   opposite_line_voltage,
                                                                   line_voltage_diff, line_voltage_diff_threshold,
                                                                   line_voltage_diff_check, loop_current,
                                                                   loop_current_threshold,
                                                                   loop_current_check, switch_current_check,
                                                                   segment_current_check, creator, create_time,
                                                                   afdrate_Current, afd_Cur_Loop, rate_Current,
                                                                   bfd_Cur_Loop)
        values (#{id}, #{switchingId}, #{lineId}, #{lineName}, #{oppositeLineId}, #{oppositeLineName},
                #{phaseAngleDiff},
                #{phaseAngleDiffThreshold}, #{phaseAngleDiffCheck}, #{lineVoltage}, #{oppositeLineVoltage},
                #{lineVoltageDiff},
                #{lineVoltageDiffThreshold}, #{lineVoltageDiffCheck}, #{loopCurrent}, #{loopCurrentThreshold},
                #{loopCurrentCheck}, #{switchCurrentCheck},
                #{segmentCurrentCheck}, '111', NOW(), #{afdrateCurrent}, #{afdCurLoop}, #{rateCurrent}, #{bfdCurLoop})
    </insert>

    <insert id="saveSwitchingSequence"
            parameterType="com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgInfoLimitTransferSchemeSwitchingSequence"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_info_limit_transfer_scheme_switching_sequence(id, scheme_id, switch_id, switch_name, switch_type,
                                                                 is_auto_switch,
                                                                 operation_content, switching_seq, loop_close_check,
                                                                 final_state_check,
                                                                 creator, create_time)
        values (#{id}, #{schemeId}, #{switchId}, #{switchName}, #{switchType}, #{isAutoSwitch}, #{operationContent},
                #{switchingSeq},
                #{loopCloseCheck},
                #{finalStateCheck},
                '111', NOW())
    </insert>

    <insert id="saveSwitchingFinalCheck"
            parameterType="com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgLimitTransferSchemeSwitchingFinalCheck"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_limit_transfer_scheme_switching_final_check(id, switching_id, line_id, line_name,
                                                                    opposite_line_id, opposite_line_name,
                                                                    allow_max_current, estimate_max_current,
                                                                    estimate_max_load_factor, load_factor_threshold,
                                                                    load_factor_check, switch_current_check,
                                                                    segment_current_check, creator, create_time)
        values (#{id}, #{switchingId}, #{lineId}, #{lineName}, #{oppositeLineId}, #{oppositeLineName},
                #{allowMaxCurrent},
                #{estimateMaxCurrent}, #{estimateMaxLoadFactor}, #{loadFactorThreshold}, #{loadFactorCheck},
                #{switchCurrentCheck}, #{segmentCurrentCheck}, '111', NOW())
    </insert>

    <delete id="deleteTransferSchemeAndLinkInfoByPlanId">
        delete
        x.* from fhzg_plan_transfer_scheme_switching_loop_check x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;
        delete
        x.* from fhzg_plan_transfer_scheme_switching_final_check x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;

        delete
        x.* from fhzg_plan_transfer_scheme_switching_loop_sw_current_detail x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;
        delete
        x.* from fhzg_plan_transfer_scheme_switching_loop_seg_current_detail x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;

        delete
        x.* from fhzg_plan_transfer_scheme_switching_final_sw_current_detail x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;
        delete
        x.* from fhzg_plan_transfer_scheme_switching_final_seg_current_detail x,(
          SELECT t.id from fhzg_plan_transfer_scheme_switching_sequence t
          join fhzg_plan_transfer_scheme s on t.scheme_id=s.id and s.plan_id =
        #{planId}
        )
        y
        where
        x
        .
        switching_id
        =
        y
        .
        id;

        delete
        x.* from fhzg_plan_transfer_scheme_switching_sequence x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        x.* from fhzg_info_breaker_svg_after x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};

        delete
        x.* from fhzg_plan_transfer_scheme_path_detail x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        x.* from fhzg_plan_transfer_scheme_opposite_line_load_factor x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        x.* from fhzg_plan_transfer_scheme_users x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        x.* from fhzg_plan_transferable_scheme_users x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};

        delete
        x.* from fhzg_plan_opposite_line_concat_switch x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        from fhzg_plan_opposite_line_carry_capacity
        where plan_id = #{planId};

        delete
        x.* from fhzg_plan_transfer_scheme_evaluation_index x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};
        delete
        x.* from fhzg_plan_transfer_scheme_strategy_score x,
          fhzg_plan_transfer_scheme y where x.scheme_id = y.id and y.plan_id =
        #{planId};

        delete
        from fhzg_plan_await_transfer_users
        where plan_id = #{planId};
        delete
        from fhzg_plan_transfer_scheme
        where plan_id = #{planId};
    </delete>

    <insert id="saveConcatSwitchList" useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_plan_opposite_line_concat_switch(scheme_id,capacity_id,concat_psr_id,concat_psr_name) values
        <foreach collection='concatSwitchList' item='dto' separator=','>
            (#{dto.schemeId},#{dto.capacityId},#{dto.concatPsrId},#{dto.concatPsrName})
        </foreach>
    </insert>

    <delete id="deleteSwitchSectionByConnect">
        delete
        a.* FROM fhzg_plan_await_transfer_switch_section a
        WHERE EXISTS ( SELECT 1 FROM fhzg_plan_transfer_scheme b, fhzg_plan_opposite_line_concat_switch c WHERE b.id = c.scheme_id AND b.plan_id = a.plan_id and a.start_switch_id = c.concat_psr_id )
          AND a.plan_id =
        #{planId}
    </delete>
</mapper>
