From 3d665d84b1a4ffd6749ca9354247838ce0622f4b Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Tue, 13 Dec 2022 13:47:53 +0800
Subject: [PATCH] 修改sql语句问题
---
screen-api/src/main/java/com/moral/api/service/impl/DataDisplayServiceImpl.java | 293 +++++++++++++++++++++++++++++++++++++++++++++++++++-------
1 files changed, 255 insertions(+), 38 deletions(-)
diff --git a/screen-api/src/main/java/com/moral/api/service/impl/DataDisplayServiceImpl.java b/screen-api/src/main/java/com/moral/api/service/impl/DataDisplayServiceImpl.java
index 1f34fd1..b33e752 100644
--- a/screen-api/src/main/java/com/moral/api/service/impl/DataDisplayServiceImpl.java
+++ b/screen-api/src/main/java/com/moral/api/service/impl/DataDisplayServiceImpl.java
@@ -1,13 +1,16 @@
package com.moral.api.service.impl;
import com.alibaba.fastjson.JSON;
+import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.moral.api.entity.*;
+import com.moral.api.mapper.HistoryMonthlyMapper;
import com.moral.api.pojo.dto.dataDisplay.MonitorPointDataDisplayDTO;
import com.moral.api.pojo.dto.dataDisplay.SensorComparisonDisplayDTO;
import com.moral.api.pojo.form.dataDisplay.MonitorPointDataDisplayForm;
import com.moral.api.pojo.form.dataDisplay.SensorComparisonDisplayForm;
import com.moral.api.service.*;
import com.moral.constant.Constants;
+import com.moral.pojo.AQI;
import com.moral.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -31,6 +34,8 @@
HistoryDailyService historyDailyService;
@Autowired
HistoryHourlyService historyHourlyService;
+ @Autowired
+ HistoryMonthlyMapper historyMonthlyMapper;
@Autowired
HistoryWeeklyService historyWeeklyService;
@Autowired
@@ -76,21 +81,21 @@
macDataMap.put(value, datas.get(0));
});
if (macDataMap.size() != 0)
- dtos = calculateReportData(macDataMap, deviceMap, startTime);
+ dtos = calculateReportData(macDataMap, deviceMap, reportType, startTime);
}
//���������������������������������������������
else if (reportType.equals(Constants.DAILY_REPORT)) {
//������������
Map<String, HistoryDaily> macDataMap = historyDailyService.getHistoryDailyByMacsAndDate(macs, startTime);
if (macDataMap.size() != 0)
- dtos = calculateReportData(macDataMap, deviceMap, startTime);
+ dtos = calculateReportData(macDataMap, deviceMap, reportType, startTime);
}
//���������������������������������������������
else if (reportType.equals(Constants.WEEKLY_REPORT)) {
//������������
Map<String, HistoryWeekly> macDataMap = historyWeeklyService.getHistoryWeeklyByMacsAndDate(macs, startTime);
if (macDataMap.size() != 0)
- dtos = calculateReportData(macDataMap, deviceMap, startTime, endTime);
+ dtos = calculateReportData(macDataMap, deviceMap, reportType, startTime, endTime);
}
//������������������
else if (reportType.equals(Constants.MONTHLY_REPORT)) {
@@ -103,7 +108,7 @@
macDataDailyMap.put(mac, dailyDatas);
});
if (macDataMonthlyMap.size() != 0)
- dtos = calculateReportData(macDataMonthlyMap, deviceMap, startTime);
+ dtos = calculateReportData(macDataMonthlyMap, deviceMap, reportType, startTime);
//������������������
injectComprehensiveIndex(dtos, macDataDailyMap);
}
@@ -124,9 +129,20 @@
List<HistoryHourly> hourlies = historyHourlyService.getValueByMacAndTime(mac, startDate, endDate);
for (HistoryHourly historyHourly : hourlies) {
Date time = historyHourly.getTime();
- int hour = DateUtils.getHour(time);
+ String dateStr = DateUtils.dateToDateString(time, "yyyy-MM-dd HH");
String value = historyHourly.getValue();
- timeValueMap.put(String.valueOf(hour), value);
+ timeValueMap.put(dateStr, value);
+ }
+ //���������������������������������������24������������������
+ for (int i = 0; i < 24; i++) {
+ //������������
+ String time = DateUtils.dateToDateString(startDate, "yyyy-MM-dd");
+ if (i < 10)
+ time = time + " 0" + i;
+ else
+ time = time + " " + i;
+ if (timeValueMap.get(time) == null)
+ timeValueMap.put(time, null);
}
}
//������������������
@@ -134,38 +150,239 @@
List<HistoryDaily> dailies = historyDailyService.getHistoryDailyByMacAndTimeSlot(mac, startDate, endDate);
for (HistoryDaily historyDaily : dailies) {
Date time = historyDaily.getTime();
- int day = DateUtils.getDay(time);
+ String dateStr = DateUtils.dateToDateString(time, "yyyy-MM-dd");
String value = historyDaily.getValue();
- timeValueMap.put(String.valueOf(day), value);
+ timeValueMap.put(String.valueOf(dateStr), value);
+ }
+ //������������������������������������������������������
+ int days = DateUtils.getMonthDay(startDate);
+ for (int i = 1; i <= days; i++) {
+ //������������
+ String time = DateUtils.dateToDateString(startDate, "yyyy-MM");
+ if (i < 10)
+ time = time + "-0" + i;
+ else
+ time = time + "-" + i;
+ if (timeValueMap.get(time) == null)
+ timeValueMap.put(time, null);
}
} else
return null;
+ //������������
+ timeValueMap = sortMapByTime(timeValueMap, reportType);
//������������������
List<SensorComparisonDisplayDTO> dtos = new ArrayList<>();
for (String sensor : sensors) {
SensorComparisonDisplayDTO dto = new SensorComparisonDisplayDTO();
- Map<String,Double> dtoTimeValueMap = new LinkedHashMap<>();
+ List<Map<String, Object>> dtoTimeValueList = new ArrayList<>();
dto.setSensorCode(sensor);
timeValueMap.forEach((time, valueJson) -> {
+ Map<String, Object> listMap = new HashMap<>();
+ //���������������������������������������������
+ if (valueJson == null) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
+ return;
+ }
Map<String, Object> valueMap = JSON.parseObject(valueJson, Map.class);
Object sensorValueObject = valueMap.get(sensor);
- if(sensorValueObject==null)
+ //���������������������������������������������
+ if (sensorValueObject == null) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
return;
- Double sensorValue = Double.parseDouble(sensorValueObject.toString());
- //������������������������������������������
- if(reportType.equals(Constants.HOURLY_REPORT)){
- if(!Constants.NORMAL_FLAG.equals(valueMap.get(sensor+"-Flag")))
- return;
}
+ //������������������������������������������
+ if (reportType.equals(Constants.DAILY_REPORT)) {
+ //���������������������N���������������
+ if (!Constants.NORMAL_FLAG.equals(valueMap.get(sensor + "-Flag"))) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
+ return;
+ }
+ }
+ //������������
+ Double sensorValue = Double.parseDouble(sensorValueObject.toString());
//������������
- dtoTimeValueMap.put(time,sensorValue);
- dto.setTimeValueMap(dtoTimeValueMap);
+ listMap.put("time", time);
+ listMap.put("value", sensorValue);
+ dtoTimeValueList.add(listMap);
});
+ dto.setTimeValueList(dtoTimeValueList);
dtos.add(dto);
}
return dtos;
}
+ @Override
+ public List<SensorComparisonDisplayDTO> getSensorComparisonDisplayDataV2(Map<String, Object> params) {
+ //������
+ List<String> sensors = (List<String>) params.get("sensorCodes");
+ /*Date startDate = form.getStartDate();
+ Date endDate = form.getEndDate();*/
+ //������������
+ List<String> times = (List<String>) params.get("times");
+ String startTime = times.get(0);
+ String endTime = times.get(1);
+ String mac = params.get("mac").toString();
+ String reportType = params.get("reportType").toString();
+ Map<String, String> timeValueMap = new LinkedHashMap<>();//key���time���value������������json
+ //���������������
+ if (reportType.equals(Constants.MONTHLY_REPORT)) {
+ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_EN);
+ Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_EN);
+ //List<HistoryHourly> hourlies = historyHourlyService.getValueByMacAndTime(mac, startDate, endDate);
+ QueryWrapper<HistoryMonthly> historyMonthlyQueryWrapper = new QueryWrapper<>();
+ historyMonthlyQueryWrapper.eq("mac",mac);
+ historyMonthlyQueryWrapper.between("time",startTime.substring(0,7)+"-01 00:00:00",endTime.substring(0,7)+"-01 00:00:00");
+ List<HistoryMonthly> monthlies = historyMonthlyMapper.selectList(historyMonthlyQueryWrapper);
+ for (HistoryMonthly historyMonthly : monthlies) {
+ Date time = historyMonthly.getTime();
+ String dateStr = DateUtils.dateToDateString(time, "yyyy-MM");
+ String value = historyMonthly.getValue();
+ timeValueMap.put(dateStr, value);
+ }
+ //���������������������
+ Date middleDate = startDate;
+ while (DateUtils.compareDateStr(DateUtils.dateToDateString(endDate,DateUtils.yyyy_MM_EN),DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_EN),DateUtils.yyyy_MM_EN)<=0){
+ if (timeValueMap.get(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_EN)) == null)
+ timeValueMap.put(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_EN), null);
+ middleDate = DateUtils.addMonths(middleDate,1);
+ }
+ }
+ //������������������
+ else if (reportType.equals(Constants.HOURLY_REPORT)) {
+ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_dd_HH_EN);
+ Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_dd_HH_EN);
+ List<HistoryHourly> hourlies = historyHourlyService.getValueByMacAndTime(mac, startDate, endDate);
+ for (HistoryHourly historyHourly : hourlies) {
+ Date time = historyHourly.getTime();
+ String dateStr = DateUtils.dateToDateString(time, "yyyy-MM-dd HH");
+ String value = historyHourly.getValue();
+ timeValueMap.put(dateStr, value);
+ }
+ //���������������������
+ Date middleDate = startDate;
+ while (DateUtils.compareDateStr(DateUtils.dateToDateString(endDate,DateUtils.yyyy_MM_dd_HH_EN),DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_HH_EN),DateUtils.yyyy_MM_dd_HH_EN)<=0){
+ if (timeValueMap.get(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_HH_EN)) == null)
+ timeValueMap.put(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_HH_EN), null);
+ middleDate = DateUtils.addHours(middleDate,1);
+ }
+ }
+ //���������������
+ else if (reportType.equals(Constants.DAILY_REPORT)) {
+ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_dd_EN);
+ Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_dd_EN);
+ List<HistoryDaily> dailies = historyDailyService.getHistoryDailyByMacAndTimeSlot(mac, startDate, endDate);
+ for (HistoryDaily historyDaily : dailies) {
+ Date time = historyDaily.getTime();
+ String dateStr = DateUtils.dateToDateString(time, "yyyy-MM-dd");
+ String value = historyDaily.getValue();
+ timeValueMap.put(String.valueOf(dateStr), value);
+ }
+ //���������������������
+ Date middleDate = startDate;
+ while (DateUtils.compareDateStr(DateUtils.dateToDateString(endDate,DateUtils.yyyy_MM_dd_EN),DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_EN),DateUtils.yyyy_MM_dd_EN)<=0){
+ if (timeValueMap.get(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_EN)) == null)
+ timeValueMap.put(DateUtils.dateToDateString(middleDate,DateUtils.yyyy_MM_dd_EN), null);
+ middleDate = DateUtils.addDays(middleDate,1);
+ }
+ } else
+ return null;
+ //������������
+ //timeValueMap = sortMapByTime(timeValueMap, reportType);
+ //������������������
+ List<SensorComparisonDisplayDTO> dtos = new ArrayList<>();
+ for (String sensor : sensors) {
+ SensorComparisonDisplayDTO dto = new SensorComparisonDisplayDTO();
+ List<Map<String, Object>> dtoTimeValueList = new ArrayList<>();
+ dto.setSensorCode(sensor);
+ timeValueMap.forEach((time, valueJson) -> {
+ Map<String, Object> listMap = new HashMap<>();
+ //���������������������������������������������
+ if (valueJson == null) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
+ return;
+ }
+ Map<String, Object> valueMap = JSON.parseObject(valueJson, Map.class);
+ Object sensorValueObject = valueMap.get(sensor);
+ //���������������������������������������������
+ if (sensorValueObject == null) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
+ return;
+ }
+ //������������������������������������������
+ if (reportType.equals(Constants.HOURLY_REPORT)) {
+ //���������������������N���������������
+ if (!Constants.NORMAL_FLAG.equals(valueMap.get(sensor + "-Flag"))) {
+ listMap.put("time", time);
+ listMap.put("value", "");
+ dtoTimeValueList.add(listMap);
+ return;
+ }
+ }
+ //������������
+ Double sensorValue = Double.parseDouble(sensorValueObject.toString());
+ //������������
+ listMap.put("time", time);
+ listMap.put("value", sensorValue);
+ dtoTimeValueList.add(listMap);
+ });
+ Collections.sort(dtoTimeValueList, new Comparator<Map<String, Object>>() {
+ public int compare(Map<String, Object> o1, Map<String, Object> o2) {
+ String id1 = (String) o1.get("time");
+ String id2 = (String) o2.get("time");
+ return id1.compareTo(id2);
+ }
+
+ });
+ dto.setTimeValueList(dtoTimeValueList);
+ dtos.add(dto);
+ }
+ return dtos;
+ }
+
+ /**
+ * @Description: ������������������������
+ * @Param: [timeValueMap, reportType]
+ * @return: java.util.Map<java.lang.String , java.lang.String>
+ * @Author: ���������
+ * @Date: 2021/11/3
+ */
+ private Map<String, String> sortMapByTime(Map<String, String> timeValueMap, String reportType) {
+ List<Map.Entry<String, String>> entries = new ArrayList(timeValueMap.entrySet());
+ Collections.sort(entries, new Comparator<Map.Entry<String, String>>() {
+ @Override
+ public int compare(Map.Entry<String, String> o1, Map.Entry<String, String> o2) {
+ if (Constants.DAILY_REPORT.equals(reportType)) {
+ String atime = o1.getKey();
+ String btime = o2.getKey();
+ atime = atime.substring(11, 13);
+ btime = btime.substring(11, 13);
+ return Integer.parseInt(atime) - Integer.parseInt(btime);
+ } else if (Constants.MONTHLY_REPORT.equals(reportType)) {
+ String atime = o1.getKey();
+ String btime = o2.getKey();
+ atime = atime.substring(8, 10);
+ btime = btime.substring(8, 10);
+ return Integer.parseInt(atime) - Integer.parseInt(btime);
+ }
+ return 0;
+ }
+ });
+ Map<String, String> sortedMap = new LinkedHashMap<>();
+ for (Map.Entry<String, String> entry : entries) {
+ sortedMap.put(entry.getKey(), entry.getValue());
+ }
+ return sortedMap;
+ }
/**
* @Description: ������������������������������
@@ -186,7 +403,6 @@
String endTime = DateUtils.dateToDateString(endDate, "yyyy-MM-dd");
String time = startTime + " - " + endTime;
//������������������
- Double AQINum = 0d;
Double PM25Num = 0d;
Double PM10Num = 0d;
Double SO2Num = 0d;
@@ -194,8 +410,7 @@
Double CONum = 0d;
Double O3Num = 0d;
Double TVOCNum = 0d;
- //������AQI,PM2.5,PM10,SO2,NO2,CO,O3,TVOC���������
- Double AQISum = 0d;
+ //������PM2.5,PM10,SO2,NO2,CO,O3,TVOC���������
Double PM25Sum = 0d;
Double PM10Sum = 0d;
Double SO2Sum = 0d;
@@ -241,18 +456,6 @@
TVOCSum = MathUtils.add(TVOCSum, Double.valueOf(TVOC.toString()));
TVOCNum++;
}
- //���AQI
- int aqi = AQIUtils.hourlyAqi(valueMap);
- if (aqi != 0) {
- AQISum += Double.valueOf(aqi);
- AQINum++;
- }
- }
- //������aqi������
- if (AQINum != 0d) {
- Double AQID = AmendUtils.sciCal(AQISum / AQINum, 0);
- int AQIAvg = new Double(AQID).intValue();
- dto.setAQI(AQIAvg);
}
//������PM2.5������
if (PM25Num != 0d) {
@@ -294,6 +497,16 @@
Double TVOCAvg = AmendUtils.sciCal(TVOCSum / TVOCNum, 2);
dto.setA99054(TVOCAvg);
}
+ //������AQI
+ Map<String,Object> sixParamMap = new HashMap<>();
+ sixParamMap.put("a05024",dto.getA05024());
+ sixParamMap.put("a21004",dto.getA21004());
+ sixParamMap.put("a21005",dto.getA21005());
+ sixParamMap.put("a34002",dto.getA34002());
+ sixParamMap.put("a34004",dto.getA34004());
+ sixParamMap.put("a21026",dto.getA21026());
+ AQI aqi = AQIUtils.dailyAQI(sixParamMap);
+ dto.setAQI(aqi.getAQIValue());
dto.setDeviceName(deviceName);
dto.setTime(time);
dtos.add(dto);
@@ -309,7 +522,7 @@
* @Author: ���������
* @Date: 2021/9/28
*/
- private <T> List<MonitorPointDataDisplayDTO> calculateReportData(Map<String, T> macDataMap, Map<String, Device> deviceMap, Date... date) {
+ private <T> List<MonitorPointDataDisplayDTO> calculateReportData(Map<String, T> macDataMap, Map<String, Device> deviceMap, String reportType, Date... date) {
List<MonitorPointDataDisplayDTO> dtos = new ArrayList<>();
macDataMap.forEach((key, valueObject) -> {
MonitorPointDataDisplayDTO dto = new MonitorPointDataDisplayDTO();
@@ -337,7 +550,7 @@
startTime = DateUtils.dateToDateString(date[0], "yyyy-MM");
dto.setTime(startTime);
//������������
- injectDataToDto(valueMap, false, dto);
+ injectDataToDto(valueMap, false, dto, reportType);
dtos.add(dto);
});
return dtos;
@@ -351,7 +564,7 @@
* @Author: ���������
* @Date: 2021/9/28
*/
- private void injectDataToDto(Map<String, Object> valueMap, boolean flag, MonitorPointDataDisplayDTO dto) {
+ private void injectDataToDto(Map<String, Object> valueMap, boolean flag, MonitorPointDataDisplayDTO dto, String reportType) {
//������CO
String COFlag = (String) valueMap.get("a21005-Flag");
Object COObject = valueMap.get("a21005");
@@ -402,9 +615,13 @@
dto.setA05024(new Double(AmendUtils.sciCal(O3, 0)).intValue());
}
//������AQI
- int aqi = AQIUtils.hourlyAqi(valueMap);
- if (aqi != 0)
- dto.setAQI(aqi);
+ Integer aqi;
+ if (reportType.equals(Constants.HOURLY_REPORT)) {
+ aqi = AQIUtils.hourlyAQI(valueMap).getAQIValue();
+ } else {
+ aqi = AQIUtils.dailyAQI(valueMap).getAQIValue();
+ }
+ dto.setAQI(aqi);
}
--
Gitblit v1.8.0