<?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.pqs9200.mapper.weeklyreport.GetAreaPointInfoMapper">
	<select id="getLineInfo" parameterType="java.util.List"
		resultType="com.pqs9200.serviceimpl.weeklyreport.WeeklyReportServiceImpl$Info">
		select distinct(a.line_index) id,a.name name from pq_line
		a,pqs_deptsLine b,pq_device c where
		b.depts_index in
		<foreach collection="deptIndexs" item="item" open="(" close=")"
			separator=",">
			#{item}
		</foreach>
		and a.line_index = b.line_index
		and a.dev_index = c.dev_index
		and
		c.devmodel = 1
		and c.devflag = 0
		and c.datatype in (0, 2)
		and systype =
		#{systype}
	</select>

	<select id="getOnline" parameterType="java.util.List"
		resultType="java.lang.Integer">
		select count(a.line_index) total from pq_line a,pq_device b where
		a.line_index in
		<foreach collection="gdIndexs" item="item" open="(" close=")"
			separator=",">
			#{item}
		</foreach>
		and a.dev_index = b.dev_index and b.status = 1 and b.devmodel = 1 and
		b.devflag = 0 and
		b.datatype != 1
	</select>

	<select id="getEventCount" parameterType="java.util.List"
		resultType="java.lang.Integer">
		select count(*) total from pqs_eventdetail a, pq_device
		b,pq_line c
		where a.lineid in
		<foreach collection="lineIndexs" item="item" open="(" close=")"
			separator=",">
			#{item}
		</foreach>
		and a.lineid = c.line_index and c.dev_index = b.dev_index and
		a.wavetype = 1 and
		b.devmodel = 1 and b.datatype != 1 and b.devflag = 0
		and timeid between #{start}
		and
		#{end}
	</select>

	<select id="getBreakCount" parameterType="java.util.List"
		resultType="java.lang.Integer">
		select count(*) total from pqs_eventdetail a, pq_device
		b,pq_line c
		where a.lineid in
		<foreach collection="lineIndexs" item="item" open="(" close=")"
			separator=",">
			#{item}
		</foreach>
		and a.lineid = c.line_index and c.dev_index = b.dev_index and
		a.wavetype = 3 and
		b.devmodel = 1 and b.datatype != 1 and b.devflag = 0
		and timeid between #{start}
		and
		#{end}
	</select>

	<select id="getUpCount" parameterType="java.util.List"
		resultType="java.lang.Integer">
		select count(*) total from pqs_eventdetail a, pq_device
		b,pq_line c
		where a.lineid in
		<foreach collection="lineIndexs" item="item" open="(" close=")"
			separator=",">
			#{item}
		</foreach>
		and a.lineid = c.line_index and c.dev_index = b.dev_index and
		a.wavetype = 2 and
		b.devmodel = 1 and b.datatype != 1 and b.devflag = 0
		and timeid between #{start}
		and
		#{end}
	</select>

	<resultMap type="com.njcn.pojo.data.LineMaps" id="lineMaps"
		autoMapping="true">
		<id property="lineindex" column="LINE_INDEX" />
	</resultMap>
	<select id="getMapsBylines" parameterType="String" resultType="lineMaps">
		select
		a.line_index line_index,a.name line_name,c.name sub_name,d.name
		subv_name,
		e.devflag devflag,e.status status,e.ip ip,
		f.longitude
		longitude,f.latitude latitude
		from
		pq_line a,pq_substation
		c,pq_subvoltage d,pq_device e,pqs_map
		f,pq_devicedetail g
		where
		a.sub_index=c.sub_index and a.subv_index=d.subv_index and
		a.dev_index=e.dev_index
		and a.sub_index=f.sub_index and
		e.dev_index=g.dev_index and f.state =1
		and e.devmodel=1
		and e.datatype
		in(0,2)
		and line_index in
		<foreach collection="list" item="item" open="(" separator=","
			close=")">
			#{item}
		</foreach>
	</select>

	<resultMap type="com.njcn.pojo.commons.device.LineInfo" id="lineInfoMap"
		autoMapping="true">
		<id property="lineindex" column="LINE_INDEX" />
	</resultMap>
	<select id="getTenList" resultMap="lineInfoMap">
		select
		count(b.eventdetail_index) eventcount,
		a.name name,
		f.dic_name Scale,
		h.dic_name loadType,
		c.name subName,
		g.dic_name subScale,
		d.name gdName,
		nvl(i.objname,'/') objName,
		nvl(i.power_substation_name,'/') powerSubstationName
		from pq_line a,
		pqs_eventdetail b,
		pq_substation c,
		pq_gdinformation d,

		pqs_dicdata f,
		pqs_dicdata g,
		pqs_dicdata h,
		pq_linedetail i
		where a.line_index = b.lineid
		and b.wavetype = #{wavetype}
		and c.sub_index = a.sub_index
		and d.gd_index = a.gd_index
		and a.line_index = i.line_index
		and a.scale = f.dic_index
		and c.scale = g.dic_index
		and i.loadtype = h.dic_index
		and a.line_index in
		<foreach collection="list" open="(" separator="," close=")" item="item">
			#{item}
		</foreach>
		and b.timeid between #{start} and #{end}
		group by a.name,f.dic_name,h.dic_name,g.dic_name,i.objname,i.power_substation_name,
		c.name, d.name
		order by eventcount desc
	</select>
</mapper>
