<?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.configuration.DeviceInfoMapper'>
	<resultMap type="LineInfo" id="deviceMap" autoMapping="true">
		<id column="Gd_index" property="nodeIndex" />
	</resultMap>
	<select id="getDeviceInfo" resultMap="deviceMap">
		select
		a.name,a.name lineName,a.subv_name,e.name as subName,b.updatetime,d.dic_name as
		manufacturer,b.devtype,b.ip,b.status,f.loadtype loadType,
		a.scale,a.pt1,a.pt2,a.ct1,a.ct2,a.jzcmp,a.dlcmp,a.xycmp,a.devcmp,f.pttype as ptvalue
		from
		pq_line a,pq_device b ,pq_devicedetail c,pqs_dicdata d,pq_substation e,pq_linedetail f
		where
		a.dev_index = b.dev_index and b.dev_index = c.dev_index and
		c.manufacturer =
		d.dic_index and e.sub_index = a.sub_index and f.line_index = a.line_index and
		a.line_index = #{line_index}
	</select>

	<select id="getRunInfo" resultType="ComInformation">
		select
		UpdateTime,Type
		from PQS_COMINFORMATION
		where State = 1
		and Line_Index = #{line_index}
		and UpdateTime between #{startTime} and #{endTime}
		order by UpdateTime
	</select>

	<select id="getFlowInfo" resultMap="deviceMap">
		SELECT nvl(fm.fm_number,
		nvl((select FM_NUMBER
		from PQS_FLOWMEAL
		where type = 0
		and SECOND_TYPE = 0),
		0)) as baseFlow,
		nvl(fm2.fm_number, 0) as extendFlow,
		nvl((SELECT af.actualflow
		FROM PQS_MONTHFLOW af
		WHERE af.DEV_INDEX = pq.DEV_INDEX
		and to_char(af.timeid, 'yyyy-MM') =
		to_char(sysdate, 'yyyy-MM')),
		0) / nullif(((nvl(fm.fm_number,
		nvl((select FM_NUMBER
		from PQS_FLOWMEAL
		where type = 0
		and SECOND_TYPE = 0),
		0))) + (nvl(fm2.fm_number, 0))) * 1024 * 1024,
		0) as useFlow
		FROM PQ_DEVICE pq
		left join PQS_DEVEDASS ded
		on pq.DEV_INDEX = ded.DEV_INDEX
		and ded.STATE = 1
		and ded.UPDATERESULT = 1
		left join PQS_EDDATA ed
		on ded.ED_INDEX = ed.ED_INDEX
		left join PQS_DEVFMASS dm
		on pq.DEV_INDEX = dm.DEV_INDEX
		left join PQS_FLOWMEAL fm
		on dm.FM_BASE_INDEX = fm.FM_INDEX
		left join PQS_FLOWMEAL fm2
		on dm.FM_REAM_INDEX = fm2.FM_INDEX
		WHERE pq.DEVMODEL = 1
		and pq.dev_index =
		(select dev_index from pq_line where line_index = #{pointIndex})
	</select>
</mapper>
