<?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.pqs9900.mapper.DataInfo.DevStateDetailMapper">

    <resultMap type="com.pqs9900.pojo.DataInfo.DevStateDetail" id="DevStateDetailMapperMap" autoMapping="true">
        <id column="DEVSTATE_INDEX" property="devStateIndex"/>
    </resultMap>
    <select id="getDevStateDetail" resultMap="DevStateDetailMapperMap">
SELECT t.devIndex,t.ram,t.spaceMemory,t.cpuOne,t.cpuTwo,DEV.ip,gd.name gdName,sub.name subName,DEV.name devName from
(select DEV_INDEX devIndex,sum(case when type='1' then Value else '0' end) ram,
	sum(case when type='2' then Value else '0' end) spaceMemory,
  sum(case when type='4' then Value else '0' end) cpuOne,
  sum(case when type='5' then Value else '0' end) cpuTwo from 	PQS_Devstate
  group by DEV_INDEX order by devIndex
) t
LEFT JOIN PQ_DEVICE dev on DEV.DEV_INDEX = t.devIndex
LEFT JOIN PQ_GDINFORMATION gd on gd.gd_index = DEV.gd_index
LEFT JOIN PQ_SUBSTATION sub on  sub.sub_index = dev.sub_index
	</select>
    <select id="getDevStateInfo" resultMap="DevStateDetailMapperMap">
SELECT t.timeid,t.devIndex,t.ram,t.spaceMemory,t.cpuOne,t.cpuTwo from
(select TIMEID timeid,DEV_INDEX devIndex,case when type='1' then Value else '0' end ram,
	case when type='2' then Value else '0' end spaceMemory,
  case when type='4' then Value else '0' end cpuOne,
  case when type='5' then Value else '0' end cpuTwo from 	PQS_Devstate
 WHERE DEV_INDEX = #{devIndex}
  and TIMEID BETWEEN #{startTime} and #{endTime}
  order by devIndex
) t
    </select>
    <select id="getDeviceDetail" resultMap="DevStateDetailMapperMap" >
SELECT t.devIndex,t.ip,gd.name gdName,sub.name subName,t.name devName from
(select DEV_INDEX devIndex ,ip ip,name name,gd_index gd_index,sub_index sub_index
   from 	PQ_DEVICE
   where DEV_INDEX = #{devIndex}
) t
LEFT JOIN PQ_GDINFORMATION gd on gd.gd_index = t.gd_index
LEFT JOIN PQ_SUBSTATION sub on  sub.sub_index = t.sub_index
    </select>
</mapper>