From dba54bf8c8f9299d61c05bf3a941708ea399c998 Mon Sep 17 00:00:00 2001
From: ZhuDongming <773644075@qq.com>
Date: Sun, 26 Apr 2020 15:20:22 +0800
Subject: [PATCH] update excel多个工作表导出
---
src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java | 169 +++++++++++++++++++++++++++++++++++++++++++------------
1 files changed, 131 insertions(+), 38 deletions(-)
diff --git a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
index 472bba7..0b9267e 100644
--- a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
@@ -1,8 +1,5 @@
package com.moral.service.impl;
-import static com.moral.common.bean.Constants.NULL_VALUE;
-import static org.springframework.util.ObjectUtils.isEmpty;
-
import java.math.BigDecimal;
import java.text.ParseException;
import java.time.LocalDate;
@@ -43,7 +40,6 @@
import com.moral.common.util.ParameterUtils;
import com.moral.common.util.ReportTimeFormat;
import com.moral.common.util.ResourceUtil;
-import com.moral.common.util.StringUtils;
import com.moral.common.util.ValidateUtil;
import com.moral.entity.Sensor;
import com.moral.entity.charts.DataCondition;
@@ -55,9 +51,9 @@
import com.moral.mapper.HistoryMinutelyMapper;
import com.moral.mapper.SensorMapper;
import com.moral.service.HistoryMinutelyService;
-import com.moral.service.SensorService;
-import lombok.val;
+import static com.moral.common.bean.Constants.NULL_VALUE;
+import static org.springframework.util.ObjectUtils.isEmpty;
@Service
@SuppressWarnings({"unchecked", "unused", "rawtypes"})
@@ -81,12 +77,26 @@
@Override
public Map<String, Object> getDayAQIByDevice(Map<String, Object> parameters) {
//ValidateUtil.notNull(parameters.get("mac"), "param.is.null");
- LocalDate localDate = LocalDate.now();
+ LocalDate time = LocalDate.now();
+ int year = time.getYear();
+ int month = time.getMonthValue();
+ int day = time.getDayOfMonth();
+ if (day == 1) {
+ if (month == 1) {
+ month = 12;
+ year = year - 1;
+ } else {
+ month = month - 1;
+ }
+ }
+ String monthStr = month < 10 ? ("0" + month) : month + "";
+ String yearAndMonth = year + monthStr;
// ������00:00:00
- parameters.put("start", localDate.minusDays(1));
+ parameters.put("start", time.minusDays(1));
// ������00:00:00
- parameters.put("end", localDate);
+ parameters.put("end", time);
+ parameters.put("yearAndMonth", yearAndMonth);
parameters.put("sensorKeys", Arrays.asList("e1", "e2", "e10", "e11", "e15", "e16"));
Map<String, Double> average = historyMinutelyMapper.getSersionAvgByDevice(parameters);
return getAQIByDataMap(average);
@@ -96,16 +106,13 @@
public Map<String, Object> getHourAQIByDevice(Map<String, Object> parameters) {
//ValidateUtil.notNull(parameters.get("mac"), "param.is.null");
LocalDate localDate = LocalDate.now();
- System.out.println("localDate:" + localDate);
// ������00:00:00
//parameters.put("start", localDate.minusDays(1));
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
calendar.set(Calendar.HOUR, calendar.get(Calendar.HOUR) - 1);
- System.out.println("++++++" + calendar);
int hour = calendar.get(Calendar.HOUR) - 1;
- System.out.println("������������������" + hour);
// ������00:00:00
parameters.put("end", localDate);
@@ -541,44 +548,130 @@
* @return
*/
@Override
- public List<Map<String, Object>> getSensorData(Map<String, Object> parameters) {
- String startTime=parameters.get("startTime").toString();
- DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
- LocalDateTime now = LocalDateTime.now();
- LocalDateTime datetime = LocalDateTime.parse(startTime, formatter);
- Boolean flag= datetime.isAfter(now.minusHours(1));
- List<Sensor> sensors=sensorMapper.getSensorsByMac(parameters);
- List<String> sensorKeys=new ArrayList<>();
- for(Sensor sensor:sensors){
+ public List<List<Map<String, Object>>> getSensorData(Map<String, Object> parameters) {
+ String startTime = parameters.get("startTime").toString();
+ DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
+ LocalDateTime startTimeLocalDateTime = LocalDateTime.parse(startTime, dateTimeFormatter);
+ int year = startTimeLocalDateTime.getYear();
+ int month = startTimeLocalDateTime.getMonthValue();
+ String monthStr = month < 10 ? ("0" + month) : month + "";
+ String yearAndMonth = year + monthStr;
+ List<Sensor> sensors = sensorMapper.getSensorsByMac(parameters);
+ List<String> sensorKeys = new ArrayList<>();
+ for (Sensor sensor : sensors) {
sensorKeys.add(sensor.getSensorKey());
}
parameters.put("sensorKeys", sensorKeys);
- List<Map<String, Object>> listMap=null;
- if(flag){
- listMap = historyMapper.getSensorDataToday(parameters);
- }else{
+ List<Map<String, Object>> listMap = null;
+ if (year <= 2019) {
listMap = historyMinutelyMapper.getSensorData(parameters);
+ } else {
+ parameters.put("yearAndMonth", yearAndMonth);
+ listMap = historyMinutelyMapper.getSensorData2020(parameters);
}
- List<Map<String, Object>> listMapNew = new ArrayList<>();
- Map<String, Object> new_map = new LinkedHashMap<>();
+ List<List<Map<String, Object>>> listMaps = new ArrayList<>();
+ List<Map<String, Object>> listMapAvg = new ArrayList<>();
+ List<Map<String, Object>> listMapMin = new ArrayList<>();
+ List<Map<String, Object>> listMapMax = new ArrayList<>();
if (CollectionUtils.isNotEmpty(listMap)) {
for (Map<String, Object> map : listMap) {
- new_map.put("������", map.get("time"));
- for (String key : map.keySet()) {
+ Map<String, Object> mapAvg = new LinkedHashMap<>();
+ Map<String, Object> mapMin = new LinkedHashMap<>();
+ Map<String, Object> mapMax = new LinkedHashMap<>();
+ mapAvg.put("time", map.get("time"));
+ mapMin.put("time", map.get("time"));
+ mapMax.put("time", map.get("time"));
+ for (Entry<String, Object> entry : map.entrySet()) {
for (Sensor sensor : sensors) {
- if (sensor.getSensorKey().equals(key)) {
- if("e79".equals(key)||"e80".equals(key)||"e81".equals(key)){
- new_map.put(sensor.getDescription(), StringUtils.scientificCountingConversion(map.get(key).toString())+sensor.getUnit());
- }else{
- new_map.put(sensor.getDescription(), map.get(key)+sensor.getUnit());
- }
+ if (sensor.getSensorKey().equals(entry.getKey())) {
+ mapAvg.put(entry.getKey(), new BigDecimal(entry.getValue().toString()).stripTrailingZeros().toPlainString() + sensor.getUnit());
+ } else if (("min" + sensor.getSensorKey()).equals(entry.getKey())) {
+ mapMin.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());
+ } else if (("max" + sensor.getSensorKey()).equals(entry.getKey())) {
+ mapMax.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());
}
}
}
- listMapNew.add(new_map);
- new_map = new LinkedHashMap<>();
+ if ("0��".equals(mapAvg.get("e76")) || "0".equals(mapMin.get("e76")) || "0".equals(mapMax.get("e76")) || "0��".equals(mapAvg.get("e77")) || "0".equals(mapMin.get("e77")) || "0".equals(mapMax.get("e77"))) {
+ continue;
+ }
+ listMapAvg.add(mapAvg);
+ listMapMin.add(mapMin);
+ listMapMax.add(mapMax);
}
+ listMaps.add(listMapAvg);
+ listMaps.add(listMapMin);
+ listMaps.add(listMapMax);
}
- return listMapNew;
+ return listMaps;
}
+
+ @Override
+ public List<Map<String, Object>> getDevicesAvgDataToExcel(Map<String, Object> parameters) throws Exception {
+ if ("month".equals(parameters.get("type"))) {
+ parameters.put("timeUnits", "daily");
+ parameters.put("typeFormat", "%Y-%m-%d");
+ String time = parameters.get("time") + "-01T00:00:00";
+ LocalDateTime value = LocalDateTime.parse(time);
+ LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());
+ LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());
+ parameters.put("start", start);
+ parameters.put("end", end);
+ int day = end.getDayOfMonth();
+ List<String> timeList = new ArrayList<>();
+ for (int i = 0; i <= day - 1; i++) {
+ timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
+ }
+ parameters.put("timeList", timeList);
+ } else if ("day".equals(parameters.get("type"))) {
+ String time = parameters.get("time") + "T00:00:00";
+ LocalDateTime value = LocalDateTime.parse(time);
+ LocalDateTime end = value.plusHours(23);
+ parameters.put("timeUnits", "hourly");
+ parameters.put("typeFormat", "%Y-%m-%d %H:%i");
+ parameters.put("start", time);
+ parameters.put("end", end);
+ List<String> timeList = new ArrayList<>();
+ for (int i = 0; i <= 23; i++) {
+ timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));
+ }
+ parameters.put("timeList", timeList);
+ }
+ return historyMinutelyMapper.getDevicesAvgDataToExcel(parameters);
+ }
+
+ @Override
+ public List<Map<String, Object>> getDevicesSensorsAvgDataToExcel(Map<String, Object> parameters) throws Exception {
+ if ("month".equals(parameters.get("type"))) {
+ parameters.put("timeUnits", "daily");
+ parameters.put("typeFormat", "%Y-%m-%d");
+ String time = parameters.get("time") + "-01T00:00:00";
+ LocalDateTime value = LocalDateTime.parse(time);
+ LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());
+ LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());
+ parameters.put("start", start);
+ parameters.put("end", end);
+ int day = end.getDayOfMonth();
+ List<String> timeList = new ArrayList<>();
+ for (int i = 0; i <= day - 1; i++) {
+ timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
+ }
+ parameters.put("timeList", timeList);
+ } else if ("day".equals(parameters.get("type"))) {
+ String time = parameters.get("time") + "T00:00:00";
+ LocalDateTime value = LocalDateTime.parse(time);
+ LocalDateTime end = value.plusHours(23);
+ parameters.put("timeUnits", "hourly");
+ parameters.put("typeFormat", "%Y-%m-%d %H:%i");
+ parameters.put("start", time);
+ parameters.put("end", end);
+ List<String> timeList = new ArrayList<>();
+ for (int i = 0; i <= 23; i++) {
+ timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));
+ }
+ parameters.put("timeList", timeList);
+ }
+ return historyMinutelyMapper.getDevicesSensorsAvgDataToExcel(parameters);
+ }
+
}
--
Gitblit v1.8.0