<?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.point.PointBaseInfoMapper">
	<select id="getPointInfo" resultType="PointInfo">
		SELECT a.pt1 pt1,
		a.pt2 pt2,
		a.ct1 ct1,
		a.ct2 ct2,
		a.devcmp devcmp,
		a.name name,
		a.dlcmp dlcmp,
		a.jzcmp jzcmp,
		a.xycmp xycmp,
		f.objname objName,
        f.superiors_Substation,
		f.hang_Line,
		f.owner,
		f.owner_Duty,
		f.owner_Tel,
		f.wiring_diagram wiringDiagram,
		b.dic_name voltageClasses,
		c.ip ip,
		c.logontime loginDate,
		c.status iState,
		(select t1.dic_name
		from pqs_dicdata t1, pq_devicedetail t2, pq_line t3
		where t1.dic_index = t2.manufacturer
		and t3.dev_index = t2.dev_index
		and t3.line_index = #{pointIndex}) factory,
		(select tt1.dic_name
		from pqs_dicdata tt1, pq_device tt2, pq_line tt3
		where tt1.dic_index = tt2.devtype
		and tt3.dev_index = tt2.dev_index
		and tt3.line_index = #{pointIndex}) devType,
		(select a.dic_name
		from pqs_dicdata a, pq_linedetail b
		where a.dic_index = b.loadtype
		and b.line_index = #{pointIndex}) loadType,
		(select a.dic_name
		from pqs_dicdata a, pq_linedetail b
		where a.dic_index = b.CLASSIFICATION_GRADE
		and b.line_index = #{pointIndex}) as calssificationGrade,
		(select a.dic_name
		from pqs_dicdata a, pq_linedetail b
		where a.dic_index = b.BUSINESSTYPE
		and b.line_index = #{pointIndex}) businessType,
		d.name pName,
		e.name bdName
		FROM pq_line a,
		pqs_dicdata b,
		pq_device c,
		pq_subvoltage d,
		pq_substation e,
		pq_linedetail f
		WHERE b.dic_index = a.scale
		and a.dev_index = c.dev_index
		and d.subv_index = a.subv_index
		and e.sub_index = a.sub_index
		and a.line_index = f.line_index
		AND a.line_index = #{pointIndex}
	</select>

	<select id="getFlowInfo" resultType="PointInfo">
		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>

	<select id="getOverLimit" resultType="map">
     select * from overlimit where myindex = #{pointIndex}
	</select>

	<select id="getLineLedger" resultType="com.njcn.pojo.pointInfo.LineLedger">
		SELECT
		j.dic_name province,
		a.line_index lineIndex,
		mod(a.line_index,10) as lineNum,
		a.name as lineName,
		e.name||'_'||sv.NAME||'_'||a.name as electricityQualityName,
		sv.NAME as busBarName,
		a.dev_index devIndex,
		a.status lineStatus,
		b.pttype ptType,
		b.POWERID powerId,
		b.MONITOR_ID monitorId,
		a.devcmp devCmp,
		a.dlcmp dlCmp,
		a.xycmp xyCmp,
		b.objname objName,
		nvl(b.POWER_SUBSTATION_NAME,e.name) as powerSubstationName,
		g.dic_name scale,
		h.dic_name loadType,
		d.name devName,
		d.updateTime updateTime,
		d.status,
		e.name subName,
		f.name gdName,
		c.freq_dev freqDev,
		c.voltage_dev voltageDev,
		c.uvoltage_dev uVoltageDev,
		c.ubalance uBalance,
		c.flicker,
		c.uaberrance aberrance,
		c.i_neg iNeg,
		c.iharm_2 iharm2,
		c.iharm_3 iharm3,
		c.iharm_4 iharm4,
		c.iharm_5 iharm5,
		c.iharm_6 iharm6,
		c.iharm_7 iharm7,
		c.iharm_8 iharm8,
		c.iharm_9 iharm9,
		c.iharm_10 iharm10,
		c.iharm_11 iharm11,
		c.iharm_12 iharm12,
		c.iharm_13 iharm13,
		c.iharm_14 iharm14,
		c.iharm_15 iharm15,
		c.iharm_16 iharm16,
		c.iharm_17 iharm17,
		c.iharm_18 iharm18,
		c.iharm_19 iharm19,
		c.iharm_20 iharm20,
		c.iharm_21 iharm21,
		c.iharm_22 iharm22,
		c.iharm_23 iharm23,
		c.iharm_24 iharm24,
		c.iharm_25 iharm25,
		c.uharm_2 uharm2,
		c.uharm_3 uharm3,
		c.inuharm_3 inuharmAll,
		c.inuharm_2 inuharm2,
		k.NODE_NAME nodeName
		FROM
		pq_line a
		LEFT JOIN pq_linedetail b ON a.line_index = b.line_index
		LEFT JOIN overlimit c ON a.line_index = myindex
		LEFT JOIN pq_device d ON a.dev_index = d.dev_index
		LEFT JOIN pq_substation e ON a.sub_index = e.sub_index
		LEFT JOIN pq_subvoltage sv ON a.SUBV_INDEX = sv.SUBV_INDEX
		LEFT JOIN pq_gdinformation f ON a.gd_index = f.gd_index
		LEFT JOIN pqs_dicdata g ON a.scale = g.dic_index
		LEFT JOIN pqs_dicdata h ON b.loadtype = h.dic_index
		LEFT JOIN pq_province i ON f.province_index = i.province_index
		LEFT JOIN pqs_dicdata j ON i.name = j.dic_index
		LEFT JOIN NODEINFORMATION k ON d.NODE_INDEX = k.NODE_INDEX
		where a.line_index in
		<foreach collection="lineList" item="item" separator="," open="(" close=")">
			#{item}
		</foreach>
		<if test="loadTypeList!=null">
			and h.dic_index in
			<foreach collection="loadTypeList" item="item" separator="," open="(" close=")">
				#{item}
			</foreach>
		</if>
		<if test="scaleList!=null">
			and g.dic_index in
			<foreach collection="scaleList" item="item" separator="," open="(" close=")">
				#{item}
			</foreach>
		</if>
		<if test="status!=null">
			and d.status = #{status}
		</if>
		<if test="powerId!=null">
			and b.POWERID = #{powerId}
		</if>
		<choose>
			<when test="type == 0"> and b.MONITOR_ID is not null and b.MONITOR_ID != ' '</when>
			<when test="type == 1"> and (b.MONITOR_ID IS NULL or b.MONITOR_ID IN (' '))</when>
		</choose>
		order by province,gdName,devName,lineNum,subName,busBarName,lineName
	</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.devmodel = 1
	</select>

	<select id="lineUnitDetail" resultType="DeviceUnitVO">
		select c.*,a.line_index as lineId
		from pq_line a inner join
		pq_device b on a.dev_index = b.dev_index
		left join pqs_device_unit c on b.dev_index = c.dev_index
		where a.line_index = #{lineId}
	</select>
</mapper>
