From 298f89f02b65783ba5cb79cf57f4fd097868ee5b Mon Sep 17 00:00:00 2001
From: 于紫祥_1901 <email@yuzixiang_1910>
Date: Mon, 16 Nov 2020 09:30:54 +0800
Subject: [PATCH] monitor平均值导出数据
---
src/main/java/com/moral/mapper/HistoryHourlyMapper.java | 8
src/main/java/com/moral/mapper/HistoryMapper.java | 2
src/main/java/com/moral/util/AQICalculation2.java | 332 ++++++++++++++++++++++++++++++++++++
src/main/resources/mapper/HistoryMapper.xml | 6
src/main/java/com/moral/service/HistoryHourlyService.java | 2
src/main/resources/mapper/HistoryHourlyMapper.xml | 10 +
src/main/java/com/moral/controller/ScreenController.java | 33 +++
src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java | 141 +++++++++++++++
8 files changed, 532 insertions(+), 2 deletions(-)
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index 84a1798..1a0708c 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -2654,4 +2654,37 @@
return i;
}
+
+ @GetMapping("/monitorAVGData")
+ @ApiOperation(value = "monitor������������������", notes = "monitor������������������")
+ @ApiImplicitParams(value = {
+ @ApiImplicitParam(name = "monitorPointIds", value = "������monitorid", required = true, paramType = "query", dataType = "String"),
+ @ApiImplicitParam(name = "time", value = "������(���������2020-03-19)", required = true, paramType = "query", dataType = "String")})
+ public List<List<Map>> monitorAVGData(HttpServletRequest request) throws Exception {
+ Map<String, Object> parameters = getParametersStartingWith(request, null);
+ String monitorPointIds = (String) parameters.get("monitorPointIds");
+ String time = (String) parameters.get("time");
+ time = time+" 00:00:00";
+ SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd mm:HH:ss");//���������������MM
+ Date date = simpleDateFormat.parse(time);
+ Calendar calendar = new GregorianCalendar();
+ calendar.setTime(date);
+ calendar.add(calendar.DATE,+1);
+ String date1= simpleDateFormat.format(date);
+ String date2= simpleDateFormat.format(calendar.getTime());
+ String[] mIds = monitorPointIds.split(",");
+ List<List<Map>> lists = new ArrayList<>();
+ if (mIds.length==1 && mIds.length!=0){
+ String mId = mIds[0];
+ List<Map> dataAvbByMIdAndTime = historyHourlyService.getDataAvbByMIdAndTime(mId, date1, date2);
+ lists.add(dataAvbByMIdAndTime);
+ //List<Device> devicesByMonitorPointId = deviceService.getDevicesByMonitorPointId(Integer.valueOf(mId));
+ }else {
+ for (int i = 0; i <mIds.length ; i++) {
+ List<Map> dataAvbByMIdAndTime = historyHourlyService.getDataAvbByMIdAndTime(mIds[i], date1, date2);
+ lists.add(dataAvbByMIdAndTime);
+ }
+ }
+ return lists;
+ }
}
diff --git a/src/main/java/com/moral/mapper/HistoryHourlyMapper.java b/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
index a7f3c4c..b81c969 100644
--- a/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
@@ -30,4 +30,12 @@
@Param("sensor") String sensor,
@Param("startTime") String startTime,
@Param("endTime") String endTime);
+ List<Map> getDataByMacAndTime1(@Param("mac") String mac,
+ @Param("sensor") String sensor,
+ @Param("startTime") String startTime,
+ @Param("endTime") String endTime);
+
+ List<Map<String,Object>> getDataAvbByMIdAndTime( @Param("mId")String mId,
+ @Param("startTime")String startTime,
+ @Param("endTime")String endTime);
}
diff --git a/src/main/java/com/moral/mapper/HistoryMapper.java b/src/main/java/com/moral/mapper/HistoryMapper.java
index 5ab081a..74c339e 100644
--- a/src/main/java/com/moral/mapper/HistoryMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMapper.java
@@ -43,4 +43,6 @@
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/HistoryHourlyService.java b/src/main/java/com/moral/service/HistoryHourlyService.java
index ded5838..0e99fea 100644
--- a/src/main/java/com/moral/service/HistoryHourlyService.java
+++ b/src/main/java/com/moral/service/HistoryHourlyService.java
@@ -26,4 +26,6 @@
List<Map<String, Object>> getDataByTimeSlot(String mac, String startTime, String endTime) throws Exception;
List<Map> getDataByMacAndTime(Map param);
+
+ List<Map> getDataAvbByMIdAndTime(String mId,String startTime,String endTime);
}
diff --git a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
index 45ae468..d6942ce 100644
--- a/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryHourlyServiceImpl.java
@@ -1,10 +1,16 @@
package com.moral.service.impl;
+import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
+import com.moral.entity.MachineActivate;
+import com.moral.mapper.HistoryMapper;
+import com.moral.util.AQICalculation;
+import com.moral.util.AQICalculation2;
+import io.swagger.models.auth.In;
import org.apache.commons.collections.MapUtils;
import org.springframework.stereotype.Service;
@@ -23,6 +29,9 @@
public class HistoryHourlyServiceImpl implements HistoryHourlyService {
@Resource
private HistoryHourlyMapper historyHourlyMapper;
+
+ @Resource
+ private HistoryMapper historyMapper;
@Resource
private DeviceService deviceService;
@@ -146,7 +155,6 @@
String date2 = simpleDateFormat.format(date1);
List<Map> list = historyHourlyMapper.getDataByMacAndTime(mac, sensor, perTime, date2);
List<Map> newList = new ArrayList<Map>();
- //System.out.println(list);
for (int i = 0; i < 24; i++) {
Map map=new HashMap();
if (i < 10) {
@@ -159,7 +167,6 @@
newList.add(map);
}
}
- //System.out.println(newList);
if (list.size() <= 24 && list.size() >= 0) {
for (int i = 0; i <list.size() ; i++) {
String timeS = list.get(i).get("time").toString().substring(11, 13);
@@ -216,6 +223,136 @@
}
@Override
+ public List<Map> getDataAvbByMIdAndTime(String mId, String startTime, String endTime) {
+ List<Device> devicesByMonitorPointId = deviceService.getDevicesByMonitorPointId(Integer.valueOf(mId));
+ List<List> lists = new ArrayList<>();
+ List<List<Map<String,String>>> listArrayList = new ArrayList<>();
+ List<Map<String, Object>> dataAvbByMIdAndTime = historyHourlyMapper.getDataAvbByMIdAndTime(mId, startTime, endTime);
+ List<Map> mapList = new ArrayList<>();
+ for (Map<String, Object> stringStringMap : dataAvbByMIdAndTime) {
+ Map<String,String> map = new HashMap();
+ String s = stringStringMap.get("O3").toString();
+ Object time = stringStringMap.get("time");
+ map.put("O3",s);
+ map.put("time",time.toString());
+ mapList.add(map);
+ }
+ if (mapList.size()>=8){
+ List<Map<String,String>> mapList1 = new ArrayList<>();
+ for (int i = 7; i <=mapList.size()-1 ; i++) {
+ if (mapList.get(i).get("O3")!=null){
+ List<Integer> numList = new ArrayList();
+ Map<String,String> O3_8 = new HashMap<>();
+ for (int j =i; j<= mapList.size(); j--) {
+ if (j<0){
+ break;
+ }
+ numList.add(j);
+ if (numList.size()==8){
+ List e15 = new ArrayList();
+ for (int k = 0; k <numList.size() ; k++) {
+ String value = mapList.get(Integer.valueOf(numList.get(k))).get("O3").toString();
+ e15.add(value);
+ }
+ double sum =0;
+ for (Object o : e15) {
+ sum=sum+Double.parseDouble(o.toString());
+ }
+ Double avg1 = sum/e15.size();
+ int timeOfHour = numList.get(0);
+ if (timeOfHour==23){
+ timeOfHour=0;
+ }else {
+ timeOfHour=timeOfHour+1;
+ }
+ O3_8.put("time", String.valueOf(timeOfHour));
+ O3_8.put("O3-8h", String.valueOf(avg1));
+ mapList1.add(O3_8);
+ }else {
+ continue;
+ }
+ }
+ }
+ listArrayList.add(mapList1);
+ }
+ }
+ Double maxO3=0.0;
+ if(listArrayList.size()>0){
+ 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")));
+ }
+
+ maxO3 = Collections.max(O3List);
+ for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
+ 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());
+ }
+ }
+ }
+ }
+ 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("time",startTime);
+ }
+ if (dataAvbByMIdAndTime.size()>24){
+ for (int i = 0; i <dataAvbByMIdAndTime.size()-1 ; i++) {
+ Map<String, Object> map =dataAvbByMIdAndTime.get(i);
+ Map<String, Object> map1 = AQICalculation2.hourlyAQI(map);
+ 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("���������������",name);
+ }else {
+ dataAvbByMIdAndTime.get(i).put("���������������","-");
+ }
+ }
+ }else {
+ for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
+ Map<String, Object> map =dataAvbByMIdAndTime.get(i);
+ Map<String, Object> map1 = AQICalculation2.hourlyAQI(map);
+ 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("���������������",name);
+ }else {
+ dataAvbByMIdAndTime.get(i).put("���������������","-");
+ }
+ }
+ }
+ Map<String, Object> dayAQI = AQICalculation2.dayAQI(dailyAvgData);
+ dailyAvgData.put("���������������",dayAQI.get("maxSensor"));
+ dailyAvgData.put("AQI",dayAQI.get("AQI"));
+ dataAvbByMIdAndTime.add(dataAvbByMIdAndTime.size(),dailyAvgData);
+
+ List<Map> finalList = new ArrayList<>();
+ for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
+ Map timeAndDate = new HashMap();
+ 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);
+ 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);
+ timeAndDate.put("data",dataAvbByMIdAndTime.get(i));
+ finalList.add(timeAndDate);
+ }
+ }
+ return finalList;
+ }
+
+ @Override
public Point getDirPoint(Map<String, Object> parameters) throws Exception {
Map<String, Object> pollutionSourceData = getPollutionSourceDataByHour(parameters);
if (MapUtils.isEmpty(pollutionSourceData)) {
diff --git a/src/main/java/com/moral/util/AQICalculation2.java b/src/main/java/com/moral/util/AQICalculation2.java
new file mode 100644
index 0000000..92d435c
--- /dev/null
+++ b/src/main/java/com/moral/util/AQICalculation2.java
@@ -0,0 +1,332 @@
+package com.moral.util;
+
+import java.util.*;
+
+public class AQICalculation2 {
+
+ public static Map<String, Object> hourlyAQI(Map<String, Object> map){
+ List<Double> AQIList = new ArrayList<>();
+ Map maxMap = new HashMap();
+ for (Map.Entry<String, Object> entry : map.entrySet()) {
+ String key = entry.getKey();
+ if (entry.getValue() instanceof Double){
+ Double value = Double.parseDouble(entry.getValue().toString());
+ if (key!="O3-8h"){
+ maxMap.put(key,value);
+ }
+ Double PM2_5AQI;
+ Double PM10AQI;
+ Double SO2AQI;
+ Double NO2AQI;
+ Double COAQI;
+ Double O3AQI;
+ switch (key) {
+ case "PM2_5":PM2_5AQI = PM2_5AQI(value);
+ AQIList.add(PM2_5AQI);
+ break;
+ case "PM10":PM10AQI = PM10AQI(value);
+ AQIList.add(PM10AQI);
+ break;
+ case "SO2":SO2AQI = SO2AQI(value);
+ AQIList.add(SO2AQI);
+ break;
+ case "e16":NO2AQI = NO2AQI(value);
+ AQIList.add(NO2AQI);
+ break;
+ case "CO":COAQI = COAQI(value);
+ AQIList.add(COAQI);
+ break;
+ case "O3":O3AQI = O3AQI(value);
+ AQIList.add(O3AQI);
+ break;
+
+ default:
+ break;
+ }
+ }else {
+ continue;
+ }
+ }
+
+ Double AQIMAX = Collections.max(AQIList);
+ Map<String, Object> returnMap = new HashMap<>();
+ //System.out.println("-------"+maxMap);
+ Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
+ .stream()
+ .max(Map.Entry.comparingByValue());
+ //System.out.println("-------"+max0.get().getKey());
+
+ returnMap.put("maxSensor",max0.get().getKey());
+ returnMap.put("AQI", AQIMAX);
+ return returnMap;
+ }
+ public static Map<String, Object> dayAQI(Map<String, Object> map){
+ List<Double> AQIList = new ArrayList<>();
+ Map maxMap = new HashMap();
+ for (Map.Entry<String, Object> entry : map.entrySet()) {
+ String key = entry.getKey();
+ if (entry.getValue() instanceof Double){
+ Double value = Double.parseDouble(entry.getValue().toString());
+ if (key!="O3-day"){
+ maxMap.put(key,value);
+ }
+ Double PM2_5AQI;
+ Double PM10AQI;
+ Double SO2AQI;
+ Double NO2AQI;
+ Double COAQI;
+ Double maxO38hAQI;
+ switch (key) {
+ case "PM2_5":PM2_5AQI = PM2_5AQI(value);
+ AQIList.add(PM2_5AQI);
+ break;
+ case "PM10":PM10AQI = PM10AQI(value);
+ AQIList.add(PM10AQI);
+ break;
+ case "SO2":SO2AQI = SO2DayAQI(value);
+ AQIList.add(SO2AQI);
+ break;
+ case "e16":NO2AQI = NO2DayAQI(value);
+ AQIList.add(NO2AQI);
+ break;
+ case "CO":COAQI = CODayAQI(value);
+ AQIList.add(COAQI);
+ break;
+ case "O3":maxO38hAQI = O3DayAQI(value);
+ AQIList.add(maxO38hAQI);
+ break;
+ default:
+ break;
+ }
+ }else {
+ continue;
+ }
+ }
+
+ Double AQIMAX = Collections.max(AQIList);
+ Map<String, Object> returnMap = new HashMap<>();
+ System.out.println("-------"+maxMap);
+ Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
+ .stream()
+ .max(Map.Entry.comparingByValue());
+ //System.out.println("-------"+max0.get().getKey());
+
+ returnMap.put("maxSensor",max0.get().getKey());
+ returnMap.put("AQI", AQIMAX);
+ return returnMap;
+ }
+ static double PM2_5AQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=35) {
+ result = 50/35*(value-0)+0;
+ }else if (value<=75) {
+ result = 50/40*(value-35)+50;
+ }else if (value<=115) {
+ result = 50/40*(value-75)+100;
+ }else if (value<=150) {
+ result = 50/35*(value-115)+150;
+ }else if (value<=250) {
+ result = 100/100*(value-150)+200;
+ }else if (value<=350) {
+ result = 100/100*(value-250)+300;
+ }else {
+ result = 100/150*(value-350)+400;
+ }
+ return result;
+ }
+
+ static double PM10AQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=50) {
+ result = 50/50*(value-0)+0;
+ }else if (value<=150) {
+ result = 50/100*(value-50)+50;
+ }else if (value<=250) {
+ result = 50/100*(value-150)+100;
+ }else if (value<=350) {
+ result = 50/100*(value-250)+150;
+ }else if (value<=420) {
+ result = 100/70*(value-350)+200;
+ }else if (value<=500) {
+ result = 100/80*(value-420)+300;
+ }else {
+ result = 100/100*(value-500)+400;
+ }
+ return result;
+ }
+
+ static double SO2AQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=150) {
+ result = 50/150*(value-0)+0;
+ }else if (value<=500) {
+ result = 50/350*(value-150)+50;
+ }else if (value<=650) {
+ result = 50/150*(value-500)+100;
+ }else {
+ result = 50/150*(value-650)+150;
+ }
+ return result;
+ }
+
+ static double NO2AQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=100) {
+ result = 50/100*(value-0)+0;
+ }else if (value<=200) {
+ result = 50/100*(value-100)+50;
+ }else if (value<=700) {
+ result = 50/500*(value-200)+100;
+ }else if (value<=1200) {
+ result = 50/500*(value-700)+150;
+ }else if (value<=2340) {
+ result = 100/1140*(value-1200)+200;
+ }else if (value<=3090) {
+ result = 100/750*(value-2340)+300;
+ }else {
+ result = 100/750*(value-3090)+400;
+ }
+ return result;
+ }
+
+ static double COAQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=5) {
+ result = 50/5*(value-0)+0;
+ }else if (value<=10) {
+ result = 50/5*(value-5)+50;
+ }else if (value<=35) {
+ result = 50/25*(value-10)+100;
+ }else if (value<=60) {
+ result = 50/25*(value-35)+150;
+ }else if (value<=90) {
+ result = 100/30*(value-60)+200;
+ }else if (value<=120) {
+ result = 100/30*(value-90)+300;
+ }else {
+ result = 100/30*(value-120)+400;
+ }
+ return result;
+ }
+
+ static double O3AQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=100) {
+ result = 50/160*(value-0)+0;
+ }else if (value<=160) {
+ result = 50/40*(value-160)+50;
+ }else if (value<=215) {
+ result = 50/100*(value-200)+100;
+ }else if (value<=265) {
+ result = 50/100*(value-300)+150;
+ }else if (value<=800) {
+ result = 100/400*(value-400)+200;
+ }else if (value<=2100) {
+ result = 100/200*(value-800)+300;
+ }else {
+ result = 100/200*(value-1000)+400;
+ }
+ return result;
+ }
+
+ static double O3DayAQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=100) {
+ result = 50/100*(value-0)+0;
+ }else if (value<=160) {
+ result = 50/60*(value-100)+50;
+ }else if (value<=215) {
+ result = 50/55*(value-160)+100;
+ }else if (value<=265) {
+ result = 50/150*(value-215)+150;
+ }else if (value<=800) {
+ result = 100/535*(value-265)+200;
+ }else if (value<=2100) {
+ result = 100/12*(value-800)+300;
+ }else {
+ result = 100/12*(value-48)+400;
+ }
+ return result;
+ }
+
+ static double SO2DayAQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=50) {
+ result = 50/50*(value-0)+0;
+ }else if (value<=150) {
+ result = 50/100*(value-50)+50;
+ }else if (value<=475) {
+ result = 50/325*(value-150)+100;
+ }else if (value<=800) {
+ result = 50/325*(value-475)+150;
+ }else if (value<=1600) {
+ result = 100/800*(value-800)+200;
+ }else if (value<=2100) {
+ result = 100/500*(value-1600)+300;
+ }else {
+ result = 100/520*(value-2100)+400;
+ }
+ return result;
+ }
+ static double NO2DayAQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=40) {
+ result = 50/40*(value-0)+0;
+ }else if (value<=80) {
+ result = 50/40*(value-40)+50;
+ }else if (value<=180) {
+ result = 50/100*(value-80)+100;
+ }else if (value<=280) {
+ result = 50/100*(value-180)+150;
+ }else if (value<=565) {
+ result = 100/285*(value-280)+200;
+ }else if (value<=750) {
+ result = 100/185*(value-565)+300;
+ }else {
+ result = 100/190*(value-750)+400;
+ }
+ return result;
+ }
+
+ static double CODayAQI(Double value) {
+ Double result = (double) 0;
+ if(value<=0) {
+ result=(double) 0;
+ }else if (value<=5) {
+ result = 50/2*(value-0)+0;
+ }else if (value<=10) {
+ result = 50/2*(value-2)+50;
+ }else if (value<=35) {
+ result = 50/10*(value-4)+100;
+ }else if (value<=60) {
+ result = 50/10*(value-14)+150;
+ }else if (value<=90) {
+ result = 100/12*(value-24)+200;
+ }else if (value<=120) {
+ result = 100/12*(value-36)+300;
+ }else {
+ result = 100/12*(value-48)+400;
+ }
+ return result;
+ }
+
+
+}
diff --git a/src/main/resources/mapper/HistoryHourlyMapper.xml b/src/main/resources/mapper/HistoryHourlyMapper.xml
index ceee292..8e819ef 100644
--- a/src/main/resources/mapper/HistoryHourlyMapper.xml
+++ b/src/main/resources/mapper/HistoryHourlyMapper.xml
@@ -102,4 +102,14 @@
<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>
+ <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}
+ </select>
+ <select id="getDataAvbByMIdAndTime" resultType="java.util.Map">
+ select hh.time as `time`,
+ AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
+ AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3'
+ from monitor_point mt,device d,history_hourly hh
+ where mt.id=d.monitor_point_id and d.mac=hh.mac and mt.id=#{mId} and hh.time>#{startTime} and hh.time<![CDATA[<=]]>#{endTime} GROUP BY hh.time;
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/HistoryMapper.xml b/src/main/resources/mapper/HistoryMapper.xml
index 8af3236..47b11ab 100644
--- a/src/main/resources/mapper/HistoryMapper.xml
+++ b/src/main/resources/mapper/HistoryMapper.xml
@@ -297,5 +297,11 @@
where mac = #{mac} and time >= #{time1} and time <=#{time}
limit 0,1
</select>
+ <select id="getDailyAvgData" resultType="java.util.Map">
+ SELECT AVG(hd.json->"$.e1[0]") as `PM2_5`,AVG(hd.json->"$.e2[0]") as 'PM10',AVG(hd.json->"$.e10[0]") as 'CO',
+ AVG(hd.json->"$.e11[0]") as 'SO2',AVG(hd.json->"$.e16[0]") as 'NO2',AVG(hd.json->"$.e15[0]") as 'O3-day'
+FROM `history_daily` hd,monitor_point mt,device d where mt.id=d.monitor_point_id
+and d.mac=hd.mac and mt.id=#{mId} and time=#{time}
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0