<?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.njcn.mapper.user.TfLgAssMapper">

    <resultMap type="com.njcn.pojo.user.TfLgAss" id="TfLgAssMapperMap" autoMapping="true">
        <id property="tfLgAss_Index" column="TFLGASS_INDEX"/>
    </resultMap>
    <!-- 通过变压器查询节点 -->
    <select id="selectIdAll" resultMap="TfLgAssMapperMap">
    select * from PQS_TfLgAss where tf_index=#{tfIndex}
    </select>
	<select id="selectByMe" resultType="TfLgAssByMe">
	SELECT
	subvl.SUBV_INDEX logicBefore,
	subvl.name logicBeforeName,
	subvr.SUBV_INDEX logicNext,
	subvr.name logicNextName
FROM
	( SELECT * FROM PQS_TfLgAss WHERE tf_index = #{tfIndex} ) t
	LEFT JOIN PQ_SUBVOLTAGE subvl ON subvl.SUBV_INDEX = t.LOGIC_BEFORE
	LEFT JOIN PQ_SUBVOLTAGE subvr ON subvr.SUBV_INDEX = t.LOGIC_NEXT
	</select>
    <!-- 新增 -->
    <insert id="insertTfLgAss">
    INSERT INTO PQS_TfLgAss  ( TFLGASS_INDEX,TF_INDEX,LOGIC_BEFORE,LOGIC_NEXT,USER_INDEX,UPDATETIME,State ) VALUES( #{tfLgAss_Index},#{tfIndex},#{logicBefore},#{logicNext},#{userIndex},#{updatetime},#{state} )
    </insert>
    <!-- 修改 -->
    <update id="updateTfLgAss">
    UPDATE PQS_TfLgAss SET USER_INDEX=#{userIndex},logic_before=#{logicBefore},logic_next=#{logicNext} where tf_index=#{tfIndex}
    </update>
    <select id="getLogicBefore" resultType="com.njcn.pojo.user.LogicBefore">
SELECT
	SUBV_INDEX subvIndex,
	name subvName
FROM
	PQ_SubVoltage
WHERE
	sub_index = #{subIndex}
	order by SUBV_INDEX asc
    </select>
    <select id="getLogicNext" resultType="com.njcn.pojo.user.LogicNext">
SELECT
	SUBV_INDEX subvIndex,
	name subvName
FROM
	PQ_SubVoltage
WHERE
	sub_index = #{subIndex}
	AND SUBV_INDEX NOT IN (
	SELECT
		LOGIC_next
	FROM
		PQS_TFLGASS
	WHERE
	TF_INDEX IN ( SELECT TF_INDEX FROM PQS_TRANSFORMER WHERE SUB_INDEX = #{subIndex} ))
	AND SUBV_INDEX NOT IN (
	SELECT
		LOGIC_BEFORE
	FROM
		PQS_TFLGASS
	WHERE
	TF_INDEX = #{tfIndex})
	order by SUBV_INDEX asc
    </select>
	<select id="getKNext" resultType="com.njcn.pojo.user.LogicKNext">
	SELECT DISTINCT
	t.subvIndex subvIndex,
	t.subvName subvName,
	t.SUBNAME subName
FROM
	(
	SELECT
	subv.subv_index subvIndex,
	subv.name subvName,
		subv.sub_index subIndex,
		MAX(to_number(substr(dic_name,1,instr(dic_name,'kV')-1))) max,
		sub.name subName
	FROM
		PQ_SubVoltage subv,
		PQS_DICDATA,
		PQ_SUBSTATION sub
	WHERE
		subv.sub_index IN ( SELECT SUB_INDEX FROM PQ_SUBSTATION WHERE SUB_INDEX != #{subIndex} )
		AND DIC_INDEX = subv.SCALE
		and sub.SUB_INDEX = subv.sub_index
	GROUP BY
		subv.subv_index,subv.name,subv.sub_index,sub.name
	) t
	LEFT JOIN PQS_DICDATA dic ON  to_number(
				substr( dic.DIC_NAME, 1, instr( dic.dic_name, 'kV' ) - 1 )) = t.max

	ORDER BY t.subvIndex asc
	</select>
	<select id="getKBefore" resultType="com.njcn.pojo.user.LogicBefore">
		SELECT
	subv.subv_index subvIndex,
	subv.name subvName
FROM
	(
	SELECT
		sub_index subIndex,
		MAX(to_number(substr(dic_name,1,instr(dic_name,'kV')-1))) max
	FROM
		PQ_SubVoltage,
		PQS_DICDATA
	WHERE
		sub_index =#{subIndex}
		AND DIC_INDEX = SCALE
	GROUP BY
		sub_index
	) t
	LEFT JOIN PQS_DICDATA dic ON  to_number(
				substr( dic.DIC_NAME, 1, instr( dic_name, 'kV' ) - 1 )) = t.max
	LEFT JOIN PQ_SubVoltage subv ON subv.scale = dic.dic_index
	AND subv.sub_index = t.subIndex
	order by subv.subv_index asc
	</select>
	<select id="getSubvIndex" resultType="int">
		SELECT COUNT(SUBV_INDEX) from PQ_SUBVOLTAGE where sub_index =#{subIndex}
	</select>
</mapper>