From e786ed5d01635555dac37c69003bdbbf757d69d8 Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Wed, 18 Nov 2020 17:46:21 +0800
Subject: [PATCH] 从history获取数据接口更新为从history日表获取
---
src/main/java/com/moral/service/impl/HistoryServiceImpl.java | 12 ++++--------
src/main/resources/mapper/HistoryMapper.xml | 10 +++++-----
2 files changed, 9 insertions(+), 13 deletions(-)
diff --git a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
index 3f5d6d6..8007e0a 100644
--- a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
@@ -74,12 +74,10 @@
Map<String, Object> resultMap = new LinkedHashMap<String, Object>();
accountService.setOrgIdsByAccount(parameters);
LocalDateTime time = LocalDateTime.now();
- // ������������ -10������
- LocalDateTime start = time.minusMinutes(10);
- parameters.put("start", start);
+ parameters.put("end", time);
// ������������ -5������
- LocalDateTime end = time.minusMinutes(5);
- parameters.put("end", end);
+ LocalDateTime start = time.minusMinutes(5);
+ parameters.put("start", start);
List<Sensor> Sensors = sensorMapper.getSensorsByCriteria(parameters);
List<String> sensorKeys = new ArrayList<String>();
for (Sensor sensor : Sensors) {
@@ -87,7 +85,7 @@
}
parameters.put("sensorKeys", sensorKeys);
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMM");
- parameters.put("timeUnits", "minutely_" + df.format(start));
+ parameters.put("timeUnits", "five_minutely_" + df.format(time));
List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);
resultMap.putAll(list.get(0));
return resultMap;
@@ -111,7 +109,6 @@
parameters.put("sensorKeys", sensorKeys);
parameters.put("timeUnits", "hourly");
List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);
-
for (Map<String, Object> map : list) {
result.put((String) map.get("name"), map.get(macKey.toString()));
}
@@ -386,7 +383,6 @@
resultMap.put(map.remove("sensor_key").toString(), map);
}
return resultMap;
-
}
@Override
diff --git a/src/main/resources/mapper/HistoryMapper.xml b/src/main/resources/mapper/HistoryMapper.xml
index 0fd5fcf..7d08573 100644
--- a/src/main/resources/mapper/HistoryMapper.xml
+++ b/src/main/resources/mapper/HistoryMapper.xml
@@ -13,7 +13,7 @@
d.`name`,
</if>
<foreach collection="sensorKeys" separator="," item="sensorKey">
- AVG(h.`value` ->'$.${sensorKey}') AS '${sensorKey}'
+ AVG(h.json ->'$.${sensorKey}[0]') AS '${sensorKey}'
</foreach>
FROM
history_${timeUnits} h,
@@ -164,7 +164,7 @@
</select>
<select id="getAVGValueByMacAndTimeslot" resultType="java.util.Map">
- SELECT AVG(JSON_EXTRACT(value,'$.e1[0]')) e1,AVG(JSON_EXTRACT(value,'$.e2[0]')) e2,AVG(JSON_EXTRACT(value,'$.e10[0]')) e10,AVG(JSON_EXTRACT(value,'$.e11[0]')) e11,AVG(JSON_EXTRACT(value,'$.e16[0]')) e16,AVG(JSON_EXTRACT(value,'$.e15[0]')) e15
+ SELECT AVG(JSON_EXTRACT(value,'$.e1')) e1,AVG(JSON_EXTRACT(value,'$.e2')) e2,AVG(JSON_EXTRACT(value,'$.e10')) e10,AVG(JSON_EXTRACT(value,'$.e11')) e11,AVG(JSON_EXTRACT(value,'$.e16')) e16,AVG(JSON_EXTRACT(value,'$.e15')) e15
FROM history_${timeUnits}
WHERE mac = #{mac}
AND time >= #{starttime}
@@ -196,7 +196,7 @@
</select>
<select id="getAVGSensorRankByMonitorPointIdList" resultType="java.util.Map">
- SELECT mp.name, AVG(JSON_EXTRACT(h.value,'$.${sensor}[0]')) sensor
+ SELECT mp.name, AVG(JSON_EXTRACT(h.value,'$.${sensor}')) sensor
FROM device d, history_${timeUnits} h, monitor_point mp
WHERE d.mac=h.mac
And mp.id=d.monitor_point_id
@@ -206,11 +206,11 @@
</foreach>
AND h.time BETWEEN #{before5Time} AND #{endTime}
GROUP BY d.monitor_point_id
- ORDER BY AVG(JSON_EXTRACT(h.value,'$.${sensor}[0]')) DESC
+ ORDER BY AVG(JSON_EXTRACT(h.value,'$.${sensor}')) DESC
</select>
<select id="getMonitorPointAVGValueByMonitorPointIdAndTimeslot" resultType="java.util.Map">
- SELECT AVG(JSON_EXTRACT(value,'$.e1[0]')) e1,AVG(JSON_EXTRACT(value,'$.e2[0]')) e2,AVG(JSON_EXTRACT(value,'$.e10[0]')) e10,AVG(JSON_EXTRACT(value,'$.e11[0]')) e11,AVG(JSON_EXTRACT(value,'$.e16[0]')) e16,AVG(JSON_EXTRACT(value,'$.e15[0]')) e15
+ SELECT AVG(JSON_EXTRACT(value,'$.e1')) e1,AVG(JSON_EXTRACT(value,'$.e2')) e2,AVG(JSON_EXTRACT(value,'$.e10')) e10,AVG(JSON_EXTRACT(value,'$.e11')) e11,AVG(JSON_EXTRACT(value,'$.e16')) e16,AVG(JSON_EXTRACT(value,'$.e15')) e15
FROM device d,history_${timeUnits} h
WHERE d.monitor_point_id = #{monitor_point_id}
AND d.mac = h.mac
--
Gitblit v1.8.0