<?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.DeviceMapper'>
	<update id="updateDevice">
    update pq_device set DEVFLAG=#{devflag} where dev_index=#{name}
    </update>
	<select id="getDevicesByDevIndexs" resultType="device">
		SELECT
			T.DEV_INDEX,
			T.DEVFLAG
		FROM
			PQ_DEVICE t
		where 
			t.DEV_INDEX in	
		<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
				#{item}
		</foreach>	
	</select>

<!-- 	to_Char(T1.LOGONTIME,'yyyy-MM') between #{startTime} and  #{endTime} -->
<!--根据供电公司找出合理的终端索引-->
	<select id="getDeviceIndexs" resultType="int">
		select 
			t.dev_index
		from 
			pq_device t
		where
			t.gd_index in
		<foreach collection="list" open="(" close=")" separator="," item="item">
			#{item}
		</foreach>
		AND
		t.DEVModel= #{devModel}
		AND
		t.DataType in
		<foreach collection="type" open="(" close=")" separator="," item="item">
			#{item}
		</foreach>
	</select>

	<select id="getDeviceIndexsByCompanyName" resultType="Integer">
		SELECT T2.DEV_INDEX from PQ_DEVICE t2
		INNER JOIN(
		SELECT
		*
		FROM
		PQ_DEVICEDETAIL
		WHERE
		MANUFACTURER = #{comIndex}
		) t1  ON
		T1.dev_index=T2.DEV_INDEX
		AND
		T2.DEVMODEL =#{devModel}
		AND
		T2.DataType in
		<foreach collection="type" open="(" close=")" separator="," item="item">
			#{item}
		</foreach>
		AND
		T2.Dev_Index IN
		<foreach collection="list" open="(" close=")" separator="," item="item1">
			#{item1}
		</foreach>
	</select>

	<select id="getIPByEventIndex" resultType="String">
		SELECT
			T3.IP
		FROM
			PQS_EVENTDETAIL t1,
			PQ_LINE t2,
			PQ_DEVICE t3
		WHERE
			T1.LINEID = T2.LINE_INDEX
		AND T2.DEV_INDEX = T3.DEV_INDEX
		AND T1.EVENTDETAIL_INDEX = #{index}
	</select>

	<select id="getRunDeviceIndex" resultType="Integer">
			SELECT
				DEV_INDEX
			FROM
				PQ_DEVICE
			WHERE
				DEV_INDEX IN
			<foreach collection="list" open="(" close=")" separator="," item="item1">
				#{item1}
			</foreach>
			AND DEVFLAG = 0
			AND DEVMODEL IN (0,1)
	</select>

	<select id="getDeviceIndexByLineIndex" resultType="int">
		SELECT
		DISTINCT(DEV_INDEX)
		FROM
		PQ_LINE
		WHERE
		LINE_INDEX IN
		<foreach collection="list" open="(" close=")" separator="," item="item">
			#{item}
		</foreach>
	</select>

	<select id="deviceDescribe" resultType="String">
		SELECT
				'将'|| gd.name || sub.name || '下的' || device.name || '(' || device.ip || ')'
		FROM
			PQ_DEVICE device
				LEFT JOIN PQ_GDINFORMATION gd ON device.GD_INDEX = gd.GD_INDEX
				LEFT JOIN PQ_SUBSTATION sub ON device.SUB_INDEX = sub.SUB_INDEX
		WHERE
			device.DEV_INDEX = #{devIndex}
	</select>

	<select id="getDevIndex" resultType="int">
		SELECT
			dev_index
		FROM
			PQ_DEVICE
		WHERE
			DATATYPE IN
		<foreach collection="list" open="(" close=")" separator="," item="item1">
			#{item1}
		</foreach>
		  AND DEVMODEL = 1
	</select>
</mapper>