<?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.data.OnlineRateMapper'>
		<select id="selectOnlineRateByDeviceIndexAndTime" resultType="simpleValue">
		SELECT
			SUM (T1.ONLINEMIN) countOnline,
			SUM (T1.ONLINEMIN) + SUM (T1.OFFLINEMIN) countAll
		FROM
			PQS_ONLINERATE T1
		WHERE
			T1.DEV_INDEX in
		<foreach collection="list" item="item" open="(" close=")" separator=",">
			#{item}
		</foreach>	
		AND
			T1.TIMEID BETWEEN #{startTime} AND #{endTime}			
		</select>

	<select id="selectCityOnlineRate" resultType="simpleValue">
		SELECT
		t1.RATE onlineRate,
		T2.NAME,
		T2.ip,
		t3.name gdName,
		t4.name subName
		FROM
		(
				SELECT
					T .*,
					ROWNUM row_num
				FROM
				(
						SELECT
							DEV_INDEX,
							ROUND (
							SUM (ONLINEMIN) / (
							SUM (OFFLINEMIN) + SUM (ONLINEMIN)
							),4) * 100 RATE
						FROM
							PQS_ONLINERATE
						WHERE
							DEV_INDEX IN
						<foreach collection="list" item="item" open="(" close=")" separator=",">
							#{item}
						</foreach>
						AND
							TIMEID BETWEEN #{startTime} AND #{endTime}
						GROUP BY
							DEV_INDEX
						ORDER BY
							RATE ASC
						) T
				) t1
		LEFT JOIN PQ_DEVICE t2 ON t1.dev_index = t2.dev_index
		LEFT JOIN PQ_GDINFORMATION t3 ON t2.GD_INDEX =t3.GD_INDEX
		LEFT JOIN PQ_SUBSTATION t4 ON T2.SUB_INDEX=T4.SUB_INDEX
		WHERE t1.row_num &lt; 11
		</select>

	<select id="selectOnlineRate" resultType="onlineRateData">
		SELECT
			T5. NAME powerCompany,
			T6. NAME substation,
			T2. NAME device,
			T2.IP deviceIp,
			T4.DIC_NAME company,
			T1.ONLINERATE onlineRate
		FROM
			(
				SELECT
					T1.DEV_INDEX,
					ROUND (
						SUM (T1.ONLINEMIN) / (
							SUM (T1.ONLINEMIN) + SUM (T1.OFFLINEMIN)
						),
						4
					) * 100 onlineRate
				FROM
					PQS_ONLINERATE T1
				WHERE
					T1.DEV_INDEX IN
				<foreach collection="list" item="item" open="(" close=")" separator=",">
					#{item}
				</foreach>
				AND t1.TIMEID BETWEEN #{startTime} AND #{endTime}
				GROUP BY
					T1.DEV_INDEX
			) T1,
			PQ_DEVICE T2,
			PQ_DEVICEDETAIL T3,
			PQS_DICDATA T4,
			PQ_GDINFORMATION T5,
			PQ_SUBSTATION T6
		WHERE
			T1.DEV_INDEX = T2.DEV_INDEX
		AND T1.DEV_INDEX = T3.DEV_INDEX
		AND T3.MANUFACTURER = T4.DIC_INDEX
		AND T2.GD_INDEX = T5.GD_INDEX
		AND T2.SUB_INDEX = T6.SUB_INDEX
	</select>


	<select id="getGWOnlineRate" resultType="float">
			SELECT
				AVG(ONLINERATE)
			FROM
				PQS_NGCPONLINERATE
			WHERE
				TIMEID	 BETWEEN #{startTime} AND #{endTime}
			AND
				LINE_INDEX IN
			<foreach collection="list" item="item" open="(" close=")" separator=",">
				#{item}
			</foreach>
	</select>

</mapper>