<?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.shining.cloud.mapper.AdminStatistic.StaticMapper'>
	<select id="getStatic" resultType="com.shining.cloud.pojo.AdminStatistic.StaticInfo">
    SELECT
    #{ timeStart } timeStart,#{ timeEnd } timeEnd,
	ROUND(nvl( (SUM(onlinerate.OnLineMin)/( SUM(onlinerate.OnLineMin)+ SUM(onlinerate.OffLineMin)) * 100),0),2) onlineRatez
FROM
	PQ_DEVICE device,
	PQS_ONLINERATE onlinerate

where
  device.DEV_INDEX=onlinerate.DEV_INDEX
	and device.DEV_INDEX in
		<foreach collection="devIndex" item="item" open="(" close=")" separator=",">#{item}</foreach>
	AND onlinerate.TIMEID BETWEEN #{ timeStart }
	AND #{ timeEnd }
	</select>
	<select id="getDevStatic" resultType="com.shining.cloud.pojo.AdminStatistic.DevStatic">
		SELECT
		device.dev_index devIndex,
		device.name devName,
		device.ip ip,
		ROUND(nvl( (SUM(onlinerate.OnLineMin)/( SUM(onlinerate.OnLineMin)+ SUM(onlinerate.OffLineMin)) * 100),0),2) onlineRate,
		gdinfo.name gdName,
		sub.name subName,
		device.updateTime timeId
		FROM
		PQ_DEVICE device,
		PQ_GDINFORMATION gdinfo,
		PQ_SUBSTATION sub,
		PQS_ONLINERATE onlinerate
		where
		device.GD_INDEX=gdinfo.GD_INDEX
		and device.sub_index = sub.sub_index
		and device.DEV_INDEX=onlinerate.DEV_INDEX
		and device.DEV_INDEX in
		<foreach collection="devIndex" item="item" open="(" close=")" separator=",">#{item}</foreach>
		AND onlinerate.TIMEID BETWEEN #{ timeStart }
		AND #{ timeEnd }
		GROUP BY device.dev_index,device.name,device.ip,gdinfo.name,sub.name,device.updateTime
	</select>

	<select id="getComError" resultType="int">
		select COUNT(*) comError from PQ_DEVICE dev where dev.DEV_INDEX in
		<foreach collection="devIndex" item="item" open="(" close=")" separator=",">#{item}</foreach>
		and dev.STATUS = 0
	</select>
</mapper>