From d0fe2eb9cefc7e3bfdddc5fa66633057c42697fc Mon Sep 17 00:00:00 2001
From: 于紫祥_1901 <email@yuzixiang_1910>
Date: Wed, 18 Nov 2020 14:39:30 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
src/main/java/com/moral/service/impl/HistoryServiceImpl.java | 112 +++++++++++++++++++++------
src/main/java/com/moral/mapper/HistoryMapper.java | 51 ++++++------
src/main/resources/mapper/HistoryMapper.xml | 27 +++---
src/main/resources/mapper/HistoryHourlyMapper.xml | 11 ++
src/main/java/com/moral/controller/ScreenController.java | 5
src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java | 33 +++++---
6 files changed, 159 insertions(+), 80 deletions(-)
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index cd20e80..dd33e5f 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -1487,12 +1487,13 @@
public ModelAndView pollutionSource(ModelAndView model, HttpServletRequest request) throws Exception {
Map<String, Object> parameters = WebUtils.getParametersStartingWith(request, null);
String timeStr = parameters.get("time").toString();
- String YearAndDay = timeStr.substring(0, timeStr.lastIndexOf("-"));
+ String yearAndDay = timeStr.substring(0, timeStr.lastIndexOf("-"));
String Hour = timeStr.substring(timeStr.lastIndexOf("-") + 1);
- String time = YearAndDay + " " + Hour + ":00:00";
+ String time = yearAndDay + " " + Hour + ":00:00";
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime timeLocalDateTime = LocalDateTime.parse(time, dateTimeFormatter);
int month = timeLocalDateTime.getMonth().getValue();
+ parameters.put("yearAndDay",yearAndDay);
Point dirPoint = historyHourlyService.getDirPoint(parameters);
Map<String, Object> getPollutionSourceData = historyHourlyService.getPollutionSourceDataByHour(parameters);
if (MapUtils.isEmpty(getPollutionSourceData)) {
diff --git a/src/main/java/com/moral/mapper/HistoryMapper.java b/src/main/java/com/moral/mapper/HistoryMapper.java
index 74c339e..eae009e 100644
--- a/src/main/java/com/moral/mapper/HistoryMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMapper.java
@@ -6,43 +6,46 @@
import org.apache.ibatis.annotations.Param;
-public interface HistoryMapper{
- String selectValueByMacAndTime(@Param("mac")String mac, @Param("time")Date time);
- String getValueByMacAndTime(@Param("mac")String mac, @Param("time")String time,@Param("time1")String time1);
- List<Map<String, Object>> getAreaAllDataByAccount(Map<String, Object> parameters);
+public interface HistoryMapper {
+ String selectValueByMacAndTime(@Param("mac") String mac, @Param("time") Date time, @Param("timeUnits") String timeUnits);
- List<Map<String, Object>> getRegionRankingData(Map<String, Object> parameters);
+ String getValueByMacAndTime(@Param("mac") String mac, @Param("time") String time, @Param("time1") String time1, @Param("timeUnits") String timeUnits);
- List<String> selectNextLevelRegion(Map<String, Object> parameters);
+ List<Map<String, Object>> getAreaAllDataByAccount(Map<String, Object> parameters);
- List<Map<String, Object>> getDischargeByRegion(Map<String, Object> parameters);
-
- List<Map<String, Object>> getValueByMacAndSize(Map<String, Object> parameters);
+ List<Map<String, Object>> getRegionRankingData(Map<String, Object> parameters);
- Map<String, Object> getActualDataByRegion(Map<String, Object> parameters);
-
- Map<String, Object> getAVGValueByMacAndTimeslot(@Param("mac")String mac, @Param("starttime")String starttime, @Param("endtime")String endtime);
+ List<String> selectNextLevelRegion(Map<String, Object> parameters);
+
+ List<Map<String, Object>> getDischargeByRegion(Map<String, Object> parameters);
+
+ List<Map<String, Object>> getValueByMacAndSize(Map<String, Object> parameters);
+
+ Map<String, Object> getActualDataByRegion(Map<String, Object> parameters);
+
+ Map<String, Object> getAVGValueByMacAndTimeslot(@Param("mac") String mac, @Param("starttime") String starttime, @Param("endtime") String endtime, @Param("timeUnits") String timeUnits);
List<Map<String, Object>> getSensorDataToday(Map<String, Object> parameters);
-
- List<Map<String, Object>> getAVGSensorRankByMonitorPointIdList(@Param("sensor")String sensor, @Param("monitorPointIdList") List<Object> monitorPointIdList, @Param("before5Time")String before5Time, @Param("endTime")String endTime);
-
- Map<String, Object> getMonitorPointAVGValueByMonitorPointIdAndTimeslot(@Param("monitor_point_id")String monitor_point_id, @Param("starttime")String starttime, @Param("endtime")String endtime);
+
+ List<Map<String, Object>> getAVGSensorRankByMonitorPointIdList(@Param("sensor") String sensor, @Param("monitorPointIdList") List<Object> monitorPointIdList, @Param("before5Time") String before5Time, @Param("endTime") String endTime,@Param("timeUnits") String timeUnits);
+
+ Map<String, Object> getMonitorPointAVGValueByMonitorPointIdAndTimeslot(@Param("monitor_point_id") String monitor_point_id, @Param("starttime") String starttime, @Param("endtime") String endtime, @Param("timeUnits") String timeUnits);
List<Map<String, Object>> getCarSensorData(Map<String, Object> parameters);
- List<Map<String, Object>> getUavSensorData(Map<String, Object> parameters);
+ List<Map<String, Object>> getUavSensorData(Map<String, Object> parameters);
List<Map<String, Object>> listGetSensorData(Map<String, Object> parameters);
- //���������������������������
- List<Map<String, Object>> getO3AVG(Map<String, Object> parameters);
+ //���������������������������
+ List<Map<String, Object>> getO3AVG(Map<String, Object> parameters);
- int getNum(@Param("timef") String timef,
- @Param("timea") String timea);
- String getJsonData(@Param("mac") String mac,
- @Param("time") String time,
- @Param("table") String table);
+ int getNum(@Param("timef") String timef,
+ @Param("timea") String timea);
+
+ String getJsonData(@Param("mac") String mac,
+ @Param("time") String time,
+ @Param("table") String table);
Map<String,Object> getDailyAvgData(@Param("mId")String mId,@Param("time")String time);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
index 4beb266..a0c6711 100644
--- a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
@@ -68,6 +68,9 @@
@Override
public Map<String, Object> getPollutionSourceDataByHour(Map<String, Object> parameters) throws Exception {
+ String yearAndDay = String.valueOf(parameters.get("yearAndDay"));
+ yearAndDay = yearAndDay.replace("-","");
+ parameters.put("yearAndDay",yearAndDay);
List<Sensor> sensors = sensorMapper.getSensorsByMac(parameters);
List<String> sensorKeys = new ArrayList<>();
for (Sensor sensor : sensors) {
@@ -90,6 +93,9 @@
@Override
public Map<String, Object> getPollutionSourceDataAll(Map<String, Object> parameters) throws Exception {
+ String yearAndDay = String.valueOf(parameters.get("yearAndDay"));
+ yearAndDay = yearAndDay.replace("-","");
+ parameters.put("yearAndDay",yearAndDay);
List<Sensor> sensors = sensorMapper.getSensorsByMac(parameters);
List<String> sensorKeys = new ArrayList<>();
for (Sensor sensor : sensors) {
@@ -295,7 +301,7 @@
timeOfHour=timeOfHour+1;
}
O3_8.put("time", String.valueOf(timeOfHour));
- O3_8.put("O3_8h", String.valueOf(avg1));
+ O3_8.put("O3-8h", String.valueOf(avg1));
mapList1.add(O3_8);
}else {
continue;
@@ -310,24 +316,25 @@
List<Map<String, String>> mapList1 = listArrayList.get(0);
List<Double> O3List = new ArrayList();
for (Map<String, String> stringStringMap : mapList1) {
- O3List.add(Double.parseDouble(stringStringMap.get("O3_8h")));
+ O3List.add(Double.parseDouble(stringStringMap.get("O3-8h")));
}
+
maxO3 = Collections.max(O3List);
for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
- dataAvbByMIdAndTime.get(i).put("O3_8h",null);
+ dataAvbByMIdAndTime.get(i).put("O3-8h",null);
int time = Integer.parseInt(((Object)dataAvbByMIdAndTime.get(i).get("time")).toString().substring(11,13)) ;
for (int j = 0; j <mapList1.size() ; j++) {
int time1 = Integer.parseInt(mapList1.get(j).get("time").toString()) ;
if (time==time1){
- Double O3_8h = Double.parseDouble(mapList1.get(j).get("O3_8h").toString());
- dataAvbByMIdAndTime.get(i).put("O3_8h",new BigDecimal(O3_8h).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ Double O3_8h = Double.parseDouble(mapList1.get(j).get("O3-8h").toString());
+ dataAvbByMIdAndTime.get(i).put("O3-8h",new BigDecimal(O3_8h).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
}
}
}
}
Map<String, Object> dailyAvgData = historyMapper.getDailyAvgData(mId, startTime);
if (dailyAvgData!=null){
- dailyAvgData.put("maxO3_8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
+ dailyAvgData.put("maxO3-8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
dailyAvgData.put("time",startTime);
}
if (dataAvbByMIdAndTime.size()>24){
@@ -337,9 +344,9 @@
dataAvbByMIdAndTime.get(i).put("AQI",Double.parseDouble(map1.get("AQI").toString()));
if (Double.parseDouble(map1.get("AQI").toString())>=50){
String name = map1.get("maxSensor").toString();
- dataAvbByMIdAndTime.get(i).put("primary_pollutants",name);
+ dataAvbByMIdAndTime.get(i).put("���������������",name);
}else {
- dataAvbByMIdAndTime.get(i).put("primary_pollutants","-");
+ dataAvbByMIdAndTime.get(i).put("���������������","-");
}
}
}else {
@@ -349,14 +356,14 @@
dataAvbByMIdAndTime.get(i).put("AQI",Double.parseDouble(map1.get("AQI").toString()));
if (Double.parseDouble(map1.get("AQI").toString())>=50){
String name = map1.get("maxSensor").toString();
- dataAvbByMIdAndTime.get(i).put("primary_pollutants",name);
+ dataAvbByMIdAndTime.get(i).put("���������������",name);
}else {
- dataAvbByMIdAndTime.get(i).put("primary_pollutants","-");
+ dataAvbByMIdAndTime.get(i).put("���������������","-");
}
}
}
Map<String, Object> dayAQI = AQICalculation2.dayAQI(dailyAvgData);
- dailyAvgData.put("primary_pollutants",dayAQI.get("maxSensor"));
+ dailyAvgData.put("���������������",dayAQI.get("maxSensor"));
dailyAvgData.put("AQI",dayAQI.get("AQI"));
dataAvbByMIdAndTime.add(dataAvbByMIdAndTime.size(),dailyAvgData);
@@ -366,13 +373,13 @@
if (i!=dataAvbByMIdAndTime.size()-1){
String time = dataAvbByMIdAndTime.get(i).get("time").toString().substring(11,13);
dataAvbByMIdAndTime.get(i).remove("time");
- timeAndDate.put("time",time+"H");
+ timeAndDate.put("time",time);
timeAndDate.put("data",dataAvbByMIdAndTime.get(i));
finalList.add(timeAndDate);
}else {
String time = dataAvbByMIdAndTime.get(i).get("time").toString().substring(0,11);
dataAvbByMIdAndTime.get(i).remove("time");
- timeAndDate.put("time",time+"H");
+ timeAndDate.put("time",time);
timeAndDate.put("data",dataAvbByMIdAndTime.get(i));
finalList.add(timeAndDate);
}
diff --git a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
index 343cdd3..3f5d6d6 100644
--- a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
@@ -75,15 +75,19 @@
accountService.setOrgIdsByAccount(parameters);
LocalDateTime time = LocalDateTime.now();
// ������������ -10������
- parameters.put("start", time.minusMinutes(10));
+ LocalDateTime start = time.minusMinutes(10);
+ parameters.put("start", start);
// ������������ -5������
- parameters.put("end", time.minusMinutes(5));
+ LocalDateTime end = time.minusMinutes(5);
+ parameters.put("end", end);
List<Sensor> Sensors = sensorMapper.getSensorsByCriteria(parameters);
List<String> sensorKeys = new ArrayList<String>();
for (Sensor sensor : Sensors) {
sensorKeys.add(sensor.getSensorKey());
}
parameters.put("sensorKeys", sensorKeys);
+ DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMM");
+ parameters.put("timeUnits", "minutely_" + df.format(start));
List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);
resultMap.putAll(list.get(0));
return resultMap;
@@ -105,6 +109,7 @@
List<String> sensorKeys = new ArrayList<String>();
sensorKeys.add(macKey.toString());
parameters.put("sensorKeys", sensorKeys);
+ parameters.put("timeUnits", "hourly");
List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);
for (Map<String, Object> map : list) {
@@ -115,11 +120,16 @@
@Override
public String queryValueByMacAndTime(String mac, Date time) {
- return historyMapper.selectValueByMacAndTime(mac, time);
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
+ String timeUnits = sdf.format(time);
+ return historyMapper.selectValueByMacAndTime(mac, time, timeUnits);
}
- public String getValueByMacAndTime(String mac, String time,String time1) {
- return historyMapper.getValueByMacAndTime(mac, time,time1);
+
+ public String getValueByMacAndTime(String mac, String time, String time1) {
+ String timeUnits = time1.substring(0, 10).replace("-", "");
+ return historyMapper.getValueByMacAndTime(mac, time, time1, timeUnits);
}
+
@Override
public List<Map<String, Object>> getRegionRankingData(Map<String, Object> parameters) {
ValidateUtil.notNull(parameters.get("regionCode"), "param.is.null");
@@ -152,10 +162,12 @@
parameters.put("nextLevel", nextLevel);
LocalDate localDate = LocalDate.now();
+ DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMM");
Object timeType = parameters.get("timeType");
+
String table = "history";
- String column = "value";
- if ("month".equals(timeType)) {
+ String column = "json";
+ /*if ("month".equals(timeType)) {
if (1 != localDate.getDayOfMonth()) {
table = "history_daily";
column = "json";
@@ -167,7 +179,19 @@
parameters.put("start", LocalDateTime.now().truncatedTo(ChronoUnit.HOURS));
} else {
parameters.put("start", LocalDateTime.now().minusMinutes(5));
+ }*/
+
+ if ("month".equals(timeType)) {
+ table = "history_daily";
+ parameters.put("start", localDate.with(TemporalAdjusters.firstDayOfMonth()));
+ } else if ("day".equals(timeType)) {
+ table = "history_hourly";
+ parameters.put("start", localDate);
+ } else if ("hour".equals(timeType)) {
+ table = "history_minutely_" + df.format(localDate);
+ parameters.put("start", LocalDateTime.now().truncatedTo(ChronoUnit.HOURS));
}
+
parameters.put("table", table);
parameters.put("column", column);
@@ -178,7 +202,6 @@
}
accountService.setOrgIdsByAccount(parameters);
-
List<Map<String, Object>> result = historyMapper.getRegionRankingData(parameters);
for (Map<String, Object> map : result) {
if (ObjectUtils.isEmpty(map.get("avg"))) {
@@ -252,6 +275,10 @@
ValidateUtil.notNull(parameters.get("deviceId"), "param.is.null");
List<Map<String, Object>> sensors = sensorMapper.getSensorsByDeviceVersionId(parameters);
parameters.put("size", 1);
+ Date date = new Date();
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
+ String timeUnits = sdf.format(date);
+ parameters.put("timeUnits", timeUnits);
List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);
JSONObject map = null;
if (!ObjectUtils.isEmpty(values)) {
@@ -291,6 +318,10 @@
ValidateUtil.notNull(parameters.get("mac"), "param.is.null");
ValidateUtil.notNull(parameters.get("size"), "param.is.null");
parameters.put("size", Integer.valueOf(parameters.remove("size").toString()));
+ Date date = new Date();
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
+ String timeUnits = sdf.format(date);
+ parameters.put("timeUnits", timeUnits);
List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);
Map<String, Object> sensorUnits = getSensorUnitByDeviceMac(parameters);
for (Map<String, Object> value : values) {
@@ -333,6 +364,9 @@
parameters.put("end", end);
parameters.put("sensorKeys", Arrays.asList(((String) sensorKey).split(",")));
accountService.setOrgIdsByAccount(parameters);
+ DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyyMMdd");
+ //���history������������������
+ parameters.put("timeUnits", df.format(start));
Map<String, Object> result = historyMapper.getActualDataByRegion(parameters);
if (ObjectUtils.isEmpty(result)) {
result = new HashMap<String, Object>();
@@ -372,7 +406,9 @@
int startHour = endHour - 1;
startTime = localDate + " " + startHour + ":00:00";
}
- Map<String, Object> map = historyMapper.getAVGValueByMacAndTimeslot(mac, startTime, endTime);
+ //���������history������������������
+ String timeUnits = startTime.substring(0, 10).replace("-", "");
+ Map<String, Object> map = historyMapper.getAVGValueByMacAndTimeslot(mac, startTime, endTime, timeUnits);
Map<String, Object> returnMap = new HashMap<>();
if (map.isEmpty()) {
returnMap.put("AQI", "N/V");
@@ -386,7 +422,7 @@
returnMap = AQICalculation.hourlyAQI(AQIMap);
}
long aqi = Math.round(Double.valueOf(returnMap.get("AQI").toString()));
- returnMap.put("AQI",aqi);
+ returnMap.put("AQI", aqi);
return returnMap;
}
@@ -436,10 +472,31 @@
Date end = rightNow.getTime();
String endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(end);
String before5Time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(beforeD); // ������������������
-
String sensor = parameters.get("sensor").toString();
- List<Map<String, Object>> result = historyMapper.getAVGSensorRankByMonitorPointIdList(sensor, monitorPointIdList, before5Time, endTime);
- return result;
+ String timeUnits1 = before5Time.substring(0, 10).replace("-", "");
+ List<Map<String, Object>> result1 = historyMapper.getAVGSensorRankByMonitorPointIdList(sensor, monitorPointIdList, before5Time, endTime, timeUnits1);
+ String timeUnits2 = before5Time.substring(0, 10).replace("-", "");
+ List<Map<String, Object>> result2 = historyMapper.getAVGSensorRankByMonitorPointIdList(sensor, monitorPointIdList, before5Time, endTime, timeUnits2);
+ if (result1.isEmpty()) {
+ return result2;
+ } else if (result2.isEmpty()) {
+ return result1;
+ } else {
+ for (Map<String, Object> map1 : result1) {
+ Object name1 = map1.get("name");
+ double value1 = Double.parseDouble(map1.get("sensor").toString());
+ double avg = 0.0;
+ for (Map<String, Object> map2 : result2) {
+ Object name2 = map2.get("name");
+ double value2 = Double.parseDouble(map2.get("sensor").toString());
+ if (name1.equals(name2)) {
+ avg = (value1 + value2) / 2;
+ map1.put("sensor", avg);
+ }
+ }
+ }
+ }
+ return result1;
}
@Override
@@ -459,7 +516,10 @@
int startHour = endHour - 1;
startTime = localDate + " " + startHour + ":00:00";
}
- Map<String, Object> map = historyMapper.getMonitorPointAVGValueByMonitorPointIdAndTimeslot(monitor_point_id, startTime, endTime);
+ //���������history������������������
+ String timeUnits = startTime.substring(0, 10).replace("-", "");
+
+ Map<String, Object> map = historyMapper.getMonitorPointAVGValueByMonitorPointIdAndTimeslot(monitor_point_id, startTime, endTime, timeUnits);
Map<String, Object> returnMap = new HashMap<>();
if (map.isEmpty()) {
returnMap.put("AQI", "N/V");
@@ -494,20 +554,20 @@
}
parameters.put("sensorKeys", sensorKeys);
List<Map<String, Object>> listMap = null;
- if (today.compareTo(startTimeDay) == 0) {
+ /*if (today.compareTo(startTimeDay) == 0) {
listMap = historyMapper.listGetSensorData(parameters);
- }
+ }*/
+ //if (CollectionUtils.isEmpty(listMap)) {
+ listMap = historyMapper.getCarSensorData(parameters);
if (CollectionUtils.isEmpty(listMap)) {
- listMap = historyMapper.getCarSensorData(parameters);
- if (CollectionUtils.isEmpty(listMap)) {
- if (year <= 2019) {
- listMap = historyMinutelyMapper.getSensorData(parameters);
- } else {
- parameters.put("yearAndMonth", yearAndMonth);
- listMap = historyMinutelyMapper.getSensorData2020(parameters);
- }
+ if (year <= 2019) {
+ listMap = historyMinutelyMapper.getSensorData(parameters);
+ } else {
+ parameters.put("yearAndMonth", yearAndMonth);
+ listMap = historyMinutelyMapper.getSensorData2020(parameters);
}
}
+ //}
List<List<Map<String, Object>>> listMaps = new ArrayList<>();
List<Map<String, Object>> listMapAvg = new ArrayList<>();
List<Map<String, Object>> listMapMin = new ArrayList<>();
@@ -651,8 +711,8 @@
}
@Override
- public String getJsonData(String mac, String time,String table) {
- return historyMapper.getJsonData(mac,time,table);
+ public String getJsonData(String mac, String time, String table) {
+ return historyMapper.getJsonData(mac, time, table);
}
}
diff --git a/src/main/resources/mapper/HistoryHourlyMapper.xml b/src/main/resources/mapper/HistoryHourlyMapper.xml
index 8e819ef..3d5ca95 100644
--- a/src/main/resources/mapper/HistoryHourlyMapper.xml
+++ b/src/main/resources/mapper/HistoryHourlyMapper.xml
@@ -26,7 +26,7 @@
</foreach>
FROM
<if test="mac!=null and time!=null">
- (select mac,value,time,version from history
+ (select mac,value,time,version from history_${yearAndDay}
where mac = #{mac} and time BETWEEN DATE_SUB(#{time}, INTERVAL 1 HOUR) and #{time}) h
</if>
GROUP BY
@@ -44,7 +44,7 @@
</foreach>
FROM
<if test="mac!=null">
- (select mac,value,time,version from history
+ (select mac,value,time,version from history_${yearAndDay}
where mac = #{mac} and time BETWEEN DATE_SUB(#{time}, INTERVAL 1 DAY) and #{time}) h
</if>
GROUP BY
@@ -100,7 +100,12 @@
</select>
<select id="getDataByMacAndTime" resultType="java.util.Map">
- SELECT DATE_FORMAT(`time`,'%Y-%m-%d %H:%i:%s') as `time`,json->'$.${sensor}[0]' as ${sensor} FROM `history_hourly` where `mac`=#{mac} and `time`>=#{startTime} and `time`<#{endTime}
+ SELECT DATE_FORMAT(`time`,'%Y-%m-%d %H:%i:%s') as `time`,
+ json->'$.${sensor}[0]' as ${sensor}
+ FROM `history_hourly`
+ where `mac`=#{mac}
+ and `time`>=#{startTime}
+ and `time`<#{endTime}
</select>
<select id="getDataByMacAndTime1" resultType="java.util.Map">
SELECT DATE_FORMAT(`time`,'%Y-%m-%d %H:%i:%s') as `time`,json->'$.${sensor}[0]' as ${sensor} FROM `history_hourly` where `mac`=#{mac} and `time`>=#{startTime} and `time`<![CDATA[<]]>#{endTime}
diff --git a/src/main/resources/mapper/HistoryMapper.xml b/src/main/resources/mapper/HistoryMapper.xml
index 8a44f87..0fd5fcf 100644
--- a/src/main/resources/mapper/HistoryMapper.xml
+++ b/src/main/resources/mapper/HistoryMapper.xml
@@ -2,7 +2,7 @@
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.HistoryMapper">
<select id="selectValueByMacAndTime" resultType="java.lang.String">
- select `value` from history
+ select `value` from history_${timeUnits}
where time = #{time} and mac = #{mac}
limit 0,1
</select>
@@ -13,10 +13,10 @@
d.`name`,
</if>
<foreach collection="sensorKeys" separator="," item="sensorKey">
- AVG(h.`value` ->'$.${sensorKey}[0]') AS '${sensorKey}'
+ AVG(h.`value` ->'$.${sensorKey}') AS '${sensorKey}'
</foreach>
FROM
- history h,
+ history_${timeUnits} h,
device d,
monitor_point mp
WHERE
@@ -132,7 +132,7 @@
value,
time
FROM
- history
+ history_${timeUnits}
WHERE
mac = #{mac}
ORDER BY
@@ -143,10 +143,10 @@
<select id="getActualDataByRegion" resultType="java.util.Map">
SELECT
<foreach collection="sensorKeys" separator="," item="sensorKey">
- MAX( `value` ->> '$.${sensorKey}' ) * 1 AS '${sensorKey}'
+ MAX( `value` -> '$.${sensorKey}' ) * 1 AS '${sensorKey}'
</foreach>
FROM
- history h ,
+ history_${timeUnits} h ,
device d,
monitor_point mp
<where>
@@ -165,9 +165,10 @@
<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
- FROM history
+ FROM history_${timeUnits}
WHERE mac = #{mac}
- AND time BETWEEN #{starttime} AND #{endtime}
+ AND time >= #{starttime}
+ AND time <![CDATA[<]]> #{endtime}
</select>
<!-- ���������sensor������������ -->
@@ -196,7 +197,7 @@
<select id="getAVGSensorRankByMonitorPointIdList" resultType="java.util.Map">
SELECT mp.name, AVG(JSON_EXTRACT(h.value,'$.${sensor}[0]')) sensor
- FROM device d, history h, monitor_point mp
+ FROM device d, history_${timeUnits} h, monitor_point mp
WHERE d.mac=h.mac
And mp.id=d.monitor_point_id
AND d.monitor_point_id IN
@@ -210,10 +211,11 @@
<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
- FROM device d,history h
+ FROM device d,history_${timeUnits} h
WHERE d.monitor_point_id = #{monitor_point_id}
AND d.mac = h.mac
- AND h.time BETWEEN #{starttime} AND #{endtime}
+ AND h.time >= #{starttime}
+ AND h.time <![CDATA[<]]> #{endtime}
</select>
<!-- ���������sensor������ -->
@@ -292,8 +294,9 @@
select `json` from ${table}
where time = #{time} and mac = #{mac}
</select>
+
<select id="getValueByMacAndTime" resultType="java.lang.String">
- select `value` from history
+ select `value` from history_${timeUnits}
where mac = #{mac} and time >= #{time1} and time <=#{time}
limit 0,1
</select>
--
Gitblit v1.8.0