<?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.pqs9000.mapper.monitorMap.MonitorScopeMapper">
    <select id="getMonitorScope" resultType="AreaSubLineVO">
		select
		gg.lineId,
		gg.pt1 pt1,
		gg.pt2 pt2,
		gg.ct1 ct1,
		gg.ct2 ct2,
		gg.lineName,
		gg.devflag runFlag,
		gg.status comFlag,
		gg.hang_Line,
		gg.wiringDiagram,
		gg.voltageLevel,
		gg.ip,
		gg.busBarName,
		gg.busBarId,
		gg.subName,
		gg.lng,
		gg.lat,
		gg.manufacturer,
		nvl(hh.eventCount,0) eventCount
		from (
		SELECT
		a.line_index lineId,
		a.pt1 pt1,
		a.pt2 pt2,
		a.ct1 ct1,
		a.ct2 ct2,
		a.name lineName,
		f.hang_Line,
		f.wiring_diagram wiringDiagram,
		b.dic_name voltageLevel,
		c.ip ip,
		c.devflag,
		c.status,
		d.name busBarName,
		d.SUBV_INDEX busBarId,
		e.name subName,
		g.LONGITUDE lng,
		g.LATITUDE lat,
		h.dic_name manufacturer
		FROM
		pq_line a,
		pqs_dicdata b,
		pq_device c,
		pq_subvoltage d,
		pq_substation e,
		pq_linedetail f,
		pqs_map g,
		pqs_dicdata h,
		pq_devicedetail i
		WHERE b.dic_index = a.scale
		and a.dev_index = c.dev_index
		and c.dev_index = i.dev_index
		and d.subv_index = a.subv_index
		and e.sub_index = a.sub_index
		and a.line_index = f.line_index
		and e.sub_index = g.sub_index
		and h.dic_index = i.MANUFACTURER
		AND a.line_index in
		<foreach collection="lineIds" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		) gg
		left join
		(
		select count(1) eventCount ,LINEID from PQS_EVENTDETAIL
		where TIMEID between #{startTime}
		AND #{endTime}
		AND LINEID in
		<foreach collection="lineIds" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		group by LINEID
		) hh on gg.lineId = hh.LINEID
	</select>


	<select id="getAreaLineEventCount" resultType="int">
		select count(1) from pq_line a
		left join pq_device b on a.dev_index = b.dev_index
		where b.status = 1
		and a.line_index in
		<foreach collection="lineIds" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>


	<select id="getEventCount" resultType="int">
		select count(1) from PQS_EVENTDETAIL
		where TIMEID between #{startTime} and #{endTime}
		and LINEID in
		<foreach collection="lineIds" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>


</mapper>
