<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
		PUBLIC "-//mybatis.org//DTD Mapper 3.1//EN"
		"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.njcn.mapper.configuration.LineMapper">

	<resultMap id="lineMap" type="com.njcn.pojo.configuration.Line" autoMapping="true">
		<id column="line_index" property="lineIndex"/>
	</resultMap>

	<select id="queryAllLineByGDInfo" resultType="line">
		SELECT
		T1.LINE_INDEX
		FROM
		PQ_LINE T1
		WHERE T1.GD_INDEX IN
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>


	<!-- 按电压等级区分后的监测点数 -->
	<select id="queryLineCounts" resultType="simpleValue">
		SELECT
		COUNT (*) value,
		dic_name name
		FROM
		LINE_DICDATA
		WHERE LINE_INDEX IN
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
		GROUP BY
		DIC_NAME
	</select>



	<select id="queryLineData" resultType="line">
		SELECT
			T .GD_INDEX,
			T .SUB_INDEX,
			T.DEV_INDEX,
			T .NAME
		FROM
			PQ_LINE T
		where t.line_index=#{lineid}
	</select>

	<select id="getLineCounts"  resultType="Integer">
		select count(*) from PQ_LINE where GD_INDEX=#{gdIndex}
	</select>

	<select id="getlines" resultType="line">
		SELECT
		T1.LINE_INDEX,
		T1.NAME,
		T1.SCALE,
		T2.LOADTYPE
		FROM
		PQ_LINE t1
		LEFT JOIN PQ_LINEDETAIL t2 ON T1.LINE_INDEX = T2.LINE_INDEX
		WHERE T1."SCALE" in
		<foreach collection="vols" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
		and T2.LOADTYPE in
		<foreach collection="types" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="queryAllLineByGDInfoAndScale" resultType="line">
		SELECT
		T1.DEV_INDEX
		FROM
		PQ_LINE T1
		WHERE
		T1.SCALE=#{dicIndex}
		AND
		T1.GD_INDEX IN
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getCountsByDeviceIndex" resultType="int">
		select count(*)
		FROM
		pq_line t
		WHERE
		t.dev_index in
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getLineIndex" resultType="int">
		SELECT
		T.LINE_INDEX
		FROM
		PQ_LINE T
		WHERE
		T.Dev_Index
		IN
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getLineIndexs" resultType="int">
		SELECT
		T.LINE_INDEX
		FROM
		PQ_LINE T
		WHERE
		T.SCALE=#{dicIndex}
		AND
		T.DEV_INDEX
		IN
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getDeviceIndexs" resultType="int">
		SELECT
		DEV_INDEX
		FROM
		PQ_LINE
		WHERE
		DEV_INDEX IN
		<foreach collection="list" item="item" open="(" close=")" separator=",">
			#{item}
		</foreach>
		AND
		SCALE=#{dicIndex}
		GROUP BY
		DEV_INDEX
	</select>

	<select id="selectMaxIndex" parameterType="String" resultType="Long" >
		select max(line_index) from PQ_LINE

	</select>

	<select id="testName" resultType="Line" >
		select * from PQ_LINE where name = #{name} and line_index != #{lineIndex}
	</select>

	<select id="selectLineName" resultType="String" >
		select  g.name ||'->'|| s.name ||'->'||d.name ||'->'|| l.name name from PQ_LINE l
		LEFT JOIN PQ_GDINFORMATION g on l.GD_INDEX=g.GD_INDEX
		LEFT JOIN PQ_SUBSTATION s on l.sub_index=s.sub_index
		LEFT JOIN PQ_DEVICE d on l.dev_index=d.dev_index
		where l.LINE_INDEX=#{name}
	</select>

	<update id="updateScale">
		update pq_line set scale = #{scale}, subv_name = #{subvName},jzcmp=#{jzCmp},pt1=#{pt} where subv_index = #{subvIndex}
	</update>

	<select id="getLineByEventDetailIndex" resultType="line">
		select
			t1.*
		from
			PQ_LINE t1,PQS_EVENTDETAIL t2
		where
			t1.LINE_INDEX=t2.LINEID
		and
			t2.EVENTDETAIL_INDEX=#{eventIndex}
	</select>

	<select id="getNodeIpbylineIndex" resultType="String">
		select
		t1.Node_IP
		from
		NODEINFORMATION t1,PQ_LINE t2,PQ_DEVICE t3
		where
		t1.Node_Index=t3.NODE_INDEX
        and T2.DEV_INDEX = t3.DEV_INDEX
		and T2.LINE_INDEX =#{lineIndex}
	</select>

	<select id="getNodeIpbyDevIndex" resultType="String">
		SELECT
	t1.Node_IP
FROM
	NODEINFORMATION t1,
	PQ_DEVICE t3
WHERE
	t1.Node_Index = t3.NODE_INDEX
	and t3.DEV_INDEX =#{lineIndex}
	</select>

	<select id="getLinesByPro" resultType="line">
		select *
		  from pq_line
		 where gd_index in
		       (select gd_index from pq_gdinformation where province_index = #{proIndex})

	</select>

	<select id="getGWLines" resultType="Integer">
		SELECT
		T2.LINE_INDEX
		FROM
		PQ_LINE t1,
		PQ_LINEDETAIL t2,
		PQ_DEVICE t3
		WHERE
		T1.LINE_INDEX = T2.LINE_INDEX
		AND T1.DEV_INDEX=T3.DEV_INDEX
		AND T3.DEVFLAG=0
		AND
		T1.LINE_INDEX
		IN
		<foreach collection="list" item="item" open="(" close=")"
				 separator=",">
			#{item}
		</foreach>
		AND T2.MONITOR_ID IS NOT NULL
	</select>

	<select id="getRole" resultType="java.lang.Integer">
		select count(a.path)
		from pqs_function a, pqs_rolefunction b, pqs_functionploy c
		where a.path = #{url}
		and a.function_index = b.function_index
		and b.function_ploy = c.function_index
		and c.state = 1
		and b.role_index in
		<foreach collection="role" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="listAllIndex" resultType="java.lang.Integer">
		select line_index from pq_line
	</select>

	<select id="getLineIndexsByLoadType" resultType="int">
		SELECT
		T.LINE_INDEX
		FROM
		PQ_LINE T
		INNER JOIN
		PQ_LINEDETAIL T1
		ON T.LINE_INDEX=T1.LINE_INDEX
		WHERE
		T1.LoadType=#{dicIndex}
		AND
		T.DEV_INDEX
		IN
		<foreach collection="list" item="item" index="index" open="("
				 separator="," close=")">
			#{item}
		</foreach>
	</select>

	<resultMap type="Tree" id="lineResultMap" autoMapping="true">
		<id column="line_index" property="nodeIndex"/>
		<result column="subv_index" property="pId"/>
	</resultMap>
	<select id='getTreeBySubv' resultMap='lineResultMap'>
		select
			a.subv_index, a.name,a.line_index,b.devflag as state,b.status,d.powerid as powerType
		from pq_line a,pq_device b ,pq_subvoltage c,pq_linedetail d
		    where
		      a.dev_index = b.dev_index
		      and a.subv_index = c.subv_index
		      and a.line_index = d.line_index
		      and c.subv_index = #{subvIndex}
		      and a.line_index in
		<foreach collection="list" index="index" item="item" open="(" separator="," close=") order by subv_index">
        	#{item}
    	</foreach>
	</select>

	<select id="getDeviceIndexsByLoadType" resultType="int">
		SELECT
		DEV_INDEX
		FROM
		PQ_LINE T
		INNER JOIN PQ_LINEDETAIL T1
		ON
		T.LINE_INDEX=T1.LINE_INDEX
		WHERE
		T.DEV_INDEX
		IN
		<foreach collection="list" item="item" open="(" close=")"
				 separator=",">
			#{item}
		</foreach>
		AND
		T1.LOADTYPE=#{dicIndex}
		GROUP BY
		T.DEV_INDEX
	</select>

	<select id="getGwLinesByDeviceType" resultType="Integer">
		SELECT
		t3.LINE_INDEX
		FROM
		PQ_LINEDETAIL t3
		WHERE
		MONITOR_ID IS NOT NULL
		AND t3.LINE_INDEX IN (
		SELECT
		t2.LINE_INDEX
		FROM
		PQ_LINE t2
		WHERE
		t2.DEV_INDEX IN (
		SELECT
		t1.DEV_INDEX
		FROM
		pq_device t1
		WHERE
		t1.DATATYPE IN
		<foreach collection="type" open="(" close=")" separator="," item="item">
			#{item}
		</foreach>
		))
	</select>

	<select id="getSubNameById" resultType="java.lang.String" >
		SELECT NAME FROM PQ_SUBSTATION WHERE SUB_INDEX = #{id}
	</select>

	<select id="getLineListBySubVId" resultMap="lineMap">
		select * from pq_line where subv_index =#{subVindex}
	</select>
</mapper>
