<?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">
	<select id="getDetailed" resultType="com.njcn.pojo.data.Detailed">
		select b.name pointName,b.line_index lineId, c.name gdName, d.name bdName,e.tinterval tinterval,a.timeid dTime,
		a.freq_dev_overtime freqDevOverTime,
		a.voltage_dev_overtime voltageDevOvertime,
		a.ubalance_overtime UBalanceOverTime,
		a.flicker_overtime flickerOverTime,
		a.uaberrance_overtime UAberranceOverTime,
		<foreach collection="list" item="item" separator=",">
			a.uharm_${item}_overtime UHarm${item}OverTime,
			a.iharm_${item}_overtime IHarm${item}OverTime
		</foreach>
		from limit_rate a, pq_line b, pq_gdinformation c, pq_substation d,pq_linedetail e
		where a.myindex = b.line_index
		and a.myindex in
		<foreach collection="listLine" item="item" index="index" open="("
				 separator="," close=")">
			#{item}
		</foreach>
		and b.gd_index = c.gd_index
		and b.sub_index = d.sub_index
		and e.line_index = a.myindex
		and a.phasic_type = 'T' and a.timeid between #{start} and #{end}
	</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="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="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>

	<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="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="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="getIntervalByIndex" resultType="int">
		SELECT
			tinterval
		FROM
			pq_lineDetail
		WHERE
			line_index = #{lineIndex}
	</select>

	<select id="queryByLineIndex" resultType="ResponsibilityData">
		SELECT
			T1.NAME lineName,
			T1.LINE_INDEX lineIndex,
			T2.NAME gdName,
			T3.NAME subName,
			T4.NAME devName,
			T4.IP
		FROM
			PQ_LINE t1,
			PQ_GDINFORMATION t2,
			PQ_SUBSTATION t3,
			PQ_DEVICE t4
		WHERE
			t1.gd_index = t2.gd_index
		AND t1.sub_index = t3.sub_index
		AND t1.dev_index = t4.dev_index
		AND t1.line_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="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="getLineName" resultType="com.njcn.pojo.commons.device.MachineAccount">
        select a.name name, b.dic_name Scale, c.name subName,d.pttype pttype
          from pq_line a, pqs_dicdata b, pq_substation c,pq_linedetail d
         where a.line_index = #{lineId}
           and a.scale = b.dic_index
           and a.sub_index = c.sub_index
           and a.line_index = d.line_index
    </select>

	<select id="getSubDeptsIndex" resultType="com.njcn.pojo.user.Depts">
		select t.depts_index deptsIndex,
		t.area area,
		t.deptsname deptsName,
		a.dic_name areaName,
		t.custom_dept customDept
		from pqs_depts t, pqs_dicdata a
		where t.parentnodeid = #{parentDeptsIndex}
		and t.area = a.dic_index
		and t.state = 1
		<if test="listArea !=null">
			and t.depts_index in
			<foreach collection="listArea" item="item" separator="," open="("
					 close=")">
				#{item}
			</foreach>
		</if>
	</select>

	<select id="getDeptsLineIndex" resultType="java.lang.Integer" databaseId="Oracle">
        select distinct (a.line_index)
        from pqs_deptsline a, pq_device b, pq_line c
        where a.depts_index in
        (select depts_index
        from pqs_depts
        start with parentnodeid = #{deptsIndex}
        connect by prior depts_index = parentnodeid)
        and a.systype = #{sysIndex}
        and c.line_index = a.line_index
        and c.dev_index = b.dev_index
        and b.devflag = 0
        and b.devmodel = 1
    </select>

	<select id="getDeptsLineIndex" resultType="java.lang.Integer"
		databaseId="MariaDB">
		SELECT DISTINCT
		( a.line_index )
		FROM
		pqs_deptsline a,
		pq_device b,
		pq_line c
		WHERE
		a.depts_index IN (
		WITH recursive t2 AS (
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1 where
		t1.parentnodeid = #{deptsIndex} UNION ALL
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1,
		t2
		WHERE
		t1.parentnodeid = #{deptsIndex}
		and t1.depts_index = t2.parentnodeid
		) SELECT
		depts_index
		FROM
		t2
		)
		AND
		a.systype = #{sysIndex}
		AND c.line_index = a.line_index
		AND c.dev_index
		= b.dev_index
		AND b.devflag = 0
		AND b.devmodel = 1
	</select>

	<select id="getPointIndexbyVoltage" resultType="java.lang.Integer" databaseId="Oracle">
        select a.line_index
        from pqs_deptsline a, pq_device b, pq_line c
        where a.depts_index in
        (select depts_index
        from pqs_depts
        start with parentnodeid = #{deptsIndex}
        connect by prior depts_index = parentnodeid)
        and a.systype = #{sysIndex}
        and c.line_index = a.line_index
        and c.dev_index = b.dev_index
        and b.devflag = 0
        and b.devmodel = 1
        and c.scale = #{guid}
    </select>

	<select id="getPointIndexbyVoltage" resultType="java.lang.Integer"
		databaseId="MariaDB">>
		select a.line_index
		from pqs_deptsline a, pq_device b,
		pq_line c
		where a.depts_index in (
		WITH recursive t2 AS (
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1 where t1.parentnodeid =
		#{deptsIndex} UNION ALL
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1,
		t2
		WHERE
		t1.parentnodeid = #{deptsIndex}
		and t1.depts_index
		= t2.parentnodeid
		) SELECT
		depts_index
		FROM
		t2
		)
		and a.systype = #{sysIndex}
		and c.line_index = a.line_index
		and c.dev_index = b.dev_index
		and
		b.devflag = 0
		and b.devmodel = 1
		and c.scale = #{guid}
	</select>

	<select id="getPointIndexbyType" resultType="java.lang.Integer" databaseId="Oracle">
        select a.line_index
        from pqs_deptsline a, pq_device b, pq_line c, pq_linedetail d
        where a.depts_index in
        (select depts_index
        from pqs_depts
        start with parentnodeid = #{deptsIndex}
        connect by prior depts_index = parentnodeid)
        and a.systype = #{sysIndex}
        and c.line_index = a.line_index
        and c.dev_index = b.dev_index
        and b.devflag = 0
        and b.devmodel = 1
        and c.line_index = d.line_index and d.loadtype = #{guid}
    </select>

	<select id="getPointbyMonitor" resultType="java.lang.Integer" databaseId="Oracle">
		select a.line_index
		from pqs_deptsline a, pq_device b, pq_line c, pq_linedetail d
		where a.depts_index in
		(select depts_index
		from pqs_depts
		start with parentnodeid = #{deptsIndex}
		connect by prior depts_index = parentnodeid)
		and a.systype = #{sysIndex}
		and c.line_index = a.line_index
		and c.dev_index = b.dev_index
		and b.devflag = 0
		and b.devmodel = 1
		and c.line_index = d.line_index
		<choose>
			<when test="guid == 0">
				and d.monitor_id is not null
			</when>
			<otherwise>
				and d.monitor_id is null
			</otherwise>
		</choose>
	</select>

	<select id="getPointIndexbyType" resultType="java.lang.Integer"
		databaseId="MariaDB">
		select a.line_index
		from pqs_deptsline a, pq_device b,
		pq_line c, pq_linedetail d
		where a.depts_index in (
		WITH recursive t2 AS
		(
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1 where
		t1.parentnodeid = #{deptsIndex} UNION ALL
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1,
		t2
		WHERE
		t1.parentnodeid = #{deptsIndex}
		and t1.depts_index = t2.parentnodeid
		) SELECT
		depts_index
		FROM
		t2
		)
		and
		a.systype = #{sysIndex}
		and c.line_index = a.line_index
		and c.dev_index
		= b.dev_index
		and b.devflag = 0
		and b.devmodel = 1
		and c.line_index =
		d.line_index and d.loadtype = #{guid}
	</select>

	<select id="getPointbyMonitor" resultType="java.lang.Integer"
		databaseId="MariaDB">
		select a.line_index
		from pqs_deptsline a, pq_device b, pq_line c,
		pq_linedetail d
		where a.depts_index in (
		WITH recursive t2 AS (
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1 where t1.parentnodeid =
		#{deptsIndex} UNION ALL
		SELECT
		t1.depts_index,
		t1.parentnodeid
		FROM
		pqs_depts t1,
		t2
		WHERE
		t1.parentnodeid = #{deptsIndex}
		and t1.depts_index
		= t2.parentnodeid
		) SELECT
		depts_index
		FROM
		t2
		)
		and a.systype = #{sysIndex}
		and c.line_index =
		a.line_index
		and c.dev_index = b.dev_index
		and
		b.devflag = 0
		and
		b.devmodel = 1
		and c.line_index = d.line_index
		<choose>
			<when test="guid == 0">
				and d.monitor_id is not null
			</when>
			<otherwise>
				and d.monitor_id is null
			</otherwise>
		</choose>
	</select>

	<select id="getDeptsLineIndexs" resultType="java.lang.Integer">
        select distinct (a.line_index)
        from pqs_deptsline a, pq_device b, pq_line c
        where a.depts_index = #{deptsIndex}
        and a.systype = #{sysIndex}
        and c.line_index = a.line_index
        and c.dev_index = b.dev_index
        and b.devflag = 0
        and b.devmodel = 1
    </select>



	<select id="getVoltageData" resultType="com.njcn.pojo.data.Legder$Data">
		select count(a.line_index) lineCount,
		count(distinct a.sub_index) gdCount,
		count(distinct a.dev_index) subCount,
		a.scale dicIndex,
		b.dic_name dicName
		from pq_line a, pqs_dicdata b, pq_linedetail c
		where a.scale = b.dic_index
		and a.line_index = c.line_index
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="listVoltage != null">
			and a.scale in
			<foreach collection="listVoltage" item="item" separator="," open="("
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="nature != -1">
			and c.powerid = #{nature}
		</if>
		group by a.scale, b.dic_name
	</select>




	<select id="getTypeData" resultType="com.njcn.pojo.data.Legder$Data">
		select count(a.line_index) lineCount,
		count(distinct a.sub_index) gdCount,
		count(distinct a.dev_index) subCount,
		c.loadtype dicIndex,
		b.dic_name dicName
		from pq_line a, pqs_dicdata b, pq_linedetail c
		where c.loadtype = b.dic_index
		and a.line_index = c.line_index
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="listType != null">
			and c.loadtype in
			<foreach collection="listType" item="item" separator="," open="("
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="nature != -1">
			and c.powerid = #{nature}
		</if>
		group by c.loadtype, b.dic_name
	</select>

	<select id="getFactoryData" resultType="com.njcn.pojo.data.Legder$Data">
		select count(a.line_index) lineCount,
		count(distinct a.sub_index) gdCount,
		count(distinct a.dev_index) subCount,
		c.manufacturer dicIndex,
		b.dic_name dicName
		from pq_line a, pqs_dicdata b, pq_devicedetail c,pq_linedetail d
		where c.dev_index = a.dev_index
		and c.manufacturer = b.dic_index
		and a.line_index = d.line_index
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="listFactory != null">
			and c.manufacturer in
			<foreach collection="listFactory" item="item" separator="," open="("
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="nature != -1">
			and d.powerid = #{nature}
		</if>
		group by c.manufacturer, b.dic_name
	</select>




	<select id="getMonitorData" resultType="com.njcn.pojo.data.Legder$Data">
		select count(a.line_index) lineCount,
		count(distinct a.sub_index) gdCount,
		count(distinct a.dev_index) subCount,
		c.monitor_id dicIndex
		from pq_line a, pq_linedetail c
		where c.line_index = a.line_index and
		a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="appear != -1 and appear != null">
			<choose>
				<when test="appear == 0">
					and c.monitor_id is not null
				</when>
				<otherwise>
					and c.monitor_id is null
				</otherwise>
			</choose>
		</if>
		<if test="nature != -1">
			and c.powerid = #{nature}
		</if>
		group by c.monitor_id
	</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="siftLineIndexs" resultType="Integer">
		select a.line_index
		from pq_line a, pq_linedetail b, pq_device c, pq_devicedetail d
		where b.line_index = a.line_index
		and c.dev_index = a.dev_index
		and d.dev_index = c.dev_index
		<if test="scale != null">
			<if test="scale != 'null'">
				and a.scale = #{scale}
			</if>
		</if>
		<if test="manc != null">
			<if test="manc != 'null'">
				and d.manufacturer = #{manc}
			</if>
		</if>
		<if test="loadtype != null">
			<if test="loadtype != 'null'">
				and b.loadtype = #{loadtype}
			</if>
		</if>
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
	</select>

	<select id="siftAllIndexs" resultType="IndexsCount">
		select a.line_index lineIndex,a.dev_index devIndex,a.sub_index subIndex,a.gd_index gdIndex
		from pq_line a, pq_linedetail b, pq_device c, pq_devicedetail d
		where b.line_index = a.line_index
		and c.dev_index = a.dev_index
		and d.dev_index = c.dev_index
		<if test="scale != null">
			<if test="scale != 'null'">
				and a.scale = #{scale}
			</if>
		</if>
		<if test="manc != null">
			<if test="manc != 'null'">
				and d.manufacturer = #{manc}
			</if>
		</if>
		<if test="loadtype != null">
			<if test="loadtype != 'null'">
				and b.loadtype = #{loadtype}
			</if>
		</if>
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 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.pttype
		from pq_line a,pq_device b ,pq_subvoltage c,pq_linedetail d
		    where
		      a.dev_index = b.dev_index
		      and a.line_index = d.line_index
		      and a.subv_index = c.subv_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="getAreaBytime" resultType="com.njcn.pojo.data.InfoData">
		select count(distinct c.myindex) countTotal, c.timeid totalDate, #{index} as dicIndex,#{index} as dicName
		from limit_rate c
		where c.myindex in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="sysType == 1">
			and ${typeName}
		</if>
		and c.timeid between #{start} and #{end}
		and c.phasic_type = 'T'
		group by c.timeid
	</select>

	<select id="getVoltageBytime" resultType="com.njcn.pojo.data.InfoData">
		select count(distinct c.myindex) countTotal,
		c.timeid totalDate,
		b.dic_index dicIndex,
		b.dic_name dicName
		from limit_rate c, pq_line a, pqs_dicdata b
		where c.myindex in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="sysType == 1">
			and ${typeName}
		</if>
		and c.timeid between #{start} and #{end}
		and c.phasic_type = 'T'
		and a.line_index = c.myindex
		and a.scale = b.dic_index
		group by c.timeid, b.dic_index, b.dic_name
	</select>

	<select id="getLoadTypeBytime" resultType="com.njcn.pojo.data.InfoData">
		select count(distinct c.myindex) countTotal,
		c.timeid totalDate,
		b.dic_index dicIndex,
		b.dic_name dicName
		from limit_rate c, pq_linedetail a, pqs_dicdata b
		where c.myindex in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="sysType == 1">
			and ${typeName}
		</if>
		and c.timeid between #{start} and #{end}
		and c.phasic_type = 'T'
		and a.line_index = c.myindex
		and a.loadtype = b.dic_index
		group by c.timeid, b.dic_index, b.dic_name
	</select>


	<select id="getMonitorBytime" resultType="com.njcn.pojo.data.InfoData">
		select count(distinct c.myindex) countTotal,
		c.timeid totalDate,
		a.monitor_id dicName
		from limit_rate c, pq_linedetail a
		where c.myindex in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
		<if test="sysType == 1">
			and ${typeName}
		</if>
		and c.timeid between #{start} and #{end}
		and c.phasic_type = 'T'
		and a.line_index = c.myindex
		group by c.timeid, a.monitor_id
	</select>

	<select id="getVoltage" resultType="com.njcn.pojo.data.InfoData">
		select distinct (b.dic_index) dicIndex, b.dic_name dicName
		from pq_line a, pqs_dicdata b
		where a.scale = b.dic_index
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
	</select>

	<select id="getLoadType" resultType="com.njcn.pojo.data.InfoData">
		select distinct (b.dic_index) dicIndex, b.dic_name dicName
		from pq_linedetail a, pqs_dicdata b
		where a.loadtype = b.dic_index
		and a.line_index in
		<foreach collection="list" item="item" separator="," open="("
				 close=")">
			#{item}
		</foreach>
	</select>

</mapper>