From 0bd7f0dee66640bab77123827cd083db4bec53a7 Mon Sep 17 00:00:00 2001
From: cjl <chenjl@deyt.cn>
Date: Thu, 06 Jul 2023 22:29:24 +0800
Subject: [PATCH] refactor: 导出格式和内容进行优化重构
---
screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java | 9 +
screen-api/pom.xml | 11 +
screen-common/src/main/java/com/moral/util/DateUtils.java | 16 ++
screen-api/src/main/java/com/moral/api/service/MonitorPointService.java | 7 +
screen-api/src/main/java/com/moral/api/utils/EasyExcelUtils.java | 77 +++++++++++
screen-api/src/main/java/com/moral/api/utils/SimpleWriteData.java | 11 +
screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java | 59 +++++++
screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java | 9 +
screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java | 151 +++++++++++++++++++++
screen-api/src/main/java/com/moral/api/utils/NoModelWriteData.java | 15 ++
10 files changed, 360 insertions(+), 5 deletions(-)
diff --git a/screen-api/pom.xml b/screen-api/pom.xml
index 21b3486..1d30c2f 100644
--- a/screen-api/pom.xml
+++ b/screen-api/pom.xml
@@ -37,6 +37,17 @@
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
+ <dependency>
+ <groupId>com.alibaba</groupId>
+ <artifactId>easyexcel</artifactId>
+ <version>2.1.6</version>
+ </dependency>
+ <dependency>
+ <groupId>com.alibaba</groupId>
+ <artifactId>fastjson</artifactId>
+ <version>1.2.66</version>
+ </dependency>
+
</dependencies>
<build>
diff --git a/screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java b/screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java
index 02a53c6..93383e4 100644
--- a/screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java
+++ b/screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java
@@ -5,6 +5,8 @@
import com.moral.api.pojo.vo.monitorPoint.MonitorPointsVO;
import com.moral.api.service.HistoryFiveMinutelyService;
import com.moral.api.service.MonitorPointService;
+import com.moral.api.utils.EasyExcelUtils;
+import com.moral.api.utils.NoModelWriteData;
import com.moral.constant.ResponseCodeEnum;
import com.moral.constant.ResultMessage;
import com.moral.util.WebUtils;
@@ -14,6 +16,8 @@
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.collections4.CollectionUtils;
+import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
@@ -22,13 +26,11 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
/**
* @ClassName DeviceController
@@ -97,7 +99,7 @@
return ResultMessage.fail(ResponseCodeEnum.PARAMETERS_IS_MISSING.getCode(), ResponseCodeEnum.PARAMETERS_IS_MISSING.getMsg());
}
String[] monitorPointIds = params.remove("monitorPointIds").toString().split(",");
- if (monitorPointIds.length >3){
+ if (monitorPointIds.length > 3) {
return ResultMessage.ok();
}
params.put("monitorPointIds", monitorPointIds);
@@ -137,4 +139,51 @@
return ResultMessage.ok(resList);
}
+
+ @PostMapping("getHourlyDataExcelNew")
+ public ResultMessage getHourlyDataExcelNew(@RequestBody Map<String, Object> params) {
+ if (!params.containsKey("macs") || !params.containsKey("sensors") || !params.containsKey("times") || !params.containsKey("type")) {
+ return ResultMessage.fail(ResponseCodeEnum.PARAMETERS_IS_MISSING.getCode(), ResponseCodeEnum.PARAMETERS_IS_MISSING.getMsg());
+ }
+ List<Map<String, Object>> resList = monitorPointService.getHourlyDataDataV3Excel(params);
+ return ResultMessage.ok(resList);
+ }
+
+ @PostMapping("/exlOut")
+ @ApiImplicitParams(value = {
+ @ApiImplicitParam(name = "token", value = "token", required = true, paramType = "header", dataType = "String")
+ })
+ public void exlOut(HttpServletResponse response) {
+ //������������������
+ // List<ExcelHeader> excelHeaders = Arrays.asList(new ExcelHeader("phone", "���������"), new ExcelHeader("sexStr", "������"));
+ Map<String, Object> params = new HashMap<>();
+ params.put("macs", Arrays.asList("p5dnd7a0245446", "p5dnd7a0745450"));
+ params.put("sensors", "a34004,a34002");
+ params.put("type", "hours");
+ params.put("times", Arrays.asList("2023-07-01 00", "2023-07-02 00"));
+ //������������
+ List<Map<String, Object>> resList = monitorPointService.getHourlyDataDataV3Excel(params);
+ if (CollectionUtils.isEmpty(resList)) {
+ return;
+ }
+ Map<String, Object> map = resList.get(0);
+ List<String> list = new ArrayList<>();
+ for (String key : map.keySet()) {
+ list.add(key);
+ }
+ String[] s2 = new String[list.size()];
+ list.toArray(s2);
+ NoModelWriteData d = new NoModelWriteData();
+ d.setFileName("������������");
+ d.setHeadMap(s2);
+ d.setDataStrMap(s2);
+ d.setDataList(resList);
+ try {
+ EasyExcelUtils easyExcelUtils = new EasyExcelUtils();
+ easyExcelUtils.noModleWrite(d, response);
+ } catch (Exception e) {
+ int i = 0;
+ }
+
+ }
}
diff --git a/screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java b/screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java
index 0c87a8c..22e5ca4 100644
--- a/screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java
+++ b/screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java
@@ -92,6 +92,15 @@
List<HistoryHourly> getValueByMacAndTime(String mac, Date startDate, Date endDate);
/**
+ * @Description: ������mac������������������������
+ * @Param: [mac, startDate, endDate]
+ * @return: java.util.List<com.moral.api.entity.HistoryHourly>
+ * @Author: ���������
+ * @Date: 2021/9/28
+ */
+ List<HistoryHourly> getValueByMacAndTime(List<String> mac, Date startDate, Date endDate);
+
+ /**
* @param macs List<String>
* @param time String ������2021-12-23
* @description: ���������������������������������������
diff --git a/screen-api/src/main/java/com/moral/api/service/MonitorPointService.java b/screen-api/src/main/java/com/moral/api/service/MonitorPointService.java
index 8094db6..9dd3484 100644
--- a/screen-api/src/main/java/com/moral/api/service/MonitorPointService.java
+++ b/screen-api/src/main/java/com/moral/api/service/MonitorPointService.java
@@ -45,6 +45,13 @@
*/
List<Map<String, Object>> getHourlyDataDataV3(Map<String, Object> params);
+ /**
+ * ������������������������
+ * @param params
+ * @return
+ */
+ List<Map<String, Object>> getHourlyDataDataV3Excel(Map<String, Object> params);
+
}
diff --git a/screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java b/screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java
index ebd124d..f2a4445 100644
--- a/screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java
+++ b/screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java
@@ -755,6 +755,15 @@
List<HistoryHourly> datas = multiTableQuery(wrapper, tableNames);
return datas;
}
+ @Override
+ public List<HistoryHourly> getValueByMacAndTime(List<String> mac, Date startDate, Date endDate) {
+ QueryWrapper<HistoryHourly> wrapper = new QueryWrapper<>();
+ wrapper.in("mac", mac);
+ wrapper.between("time", startDate, endDate);
+ List<String> tableNames = MybatisPLUSUtils.getTableNamesByWrapper(startDate, endDate, SeparateTableType.MONTH);
+ List<HistoryHourly> datas = multiTableQuery(wrapper, tableNames);
+ return datas;
+ }
@Override
public List<HistoryHourly> getValueByMacs(List<String> macs, String time) {
diff --git a/screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java b/screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java
index f2aa5c0..27bd715 100644
--- a/screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java
+++ b/screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java
@@ -314,6 +314,145 @@
return resultList;
}
+ @Override
+ public List<Map<String, Object>> getHourlyDataDataV3Excel(Map<String, Object> params) {
+ List<String> macs = (List<String>) params.remove("macs");
+ List<String> times = (List<String>) params.remove("times");
+ String type = params.get("type").toString();
+ String startTime = times.get(0);
+ String endTime = times.get(1);
+
+// String[] macs = params.remove("macs").toString().split(",");
+// List<String> macsList = Arrays.asList(macs);
+ QueryWrapper<Device> queryWrapper = new QueryWrapper<>();
+ queryWrapper.eq("is_delete",Constants.NOT_DELETE);
+ queryWrapper.in("mac", macs);
+ List<Device> devices = deviceMapper.selectList(queryWrapper);
+ if (devices.size()<=0){
+ return null;
+ }
+ String[] sensors = params.remove("sensors").toString().split(",");
+ List<String> sensorsList = Arrays.asList(sensors);
+ List<Map<String,Object>> resultList = new ArrayList<>();
+
+// List<String> sensorsList = (List<String>) params.remove("sensors");
+// String startTime = params.get("startTime").toString();
+// String endTime = params.get("endTime").toString();
+ if (type.equals("hours")){
+ 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(macs, DateUtils.getDate(startTime, DateUtils.yyyy_MM_dd_HH_EN), DateUtils.getDate(endTime, DateUtils.yyyy_MM_dd_HH_EN));
+ Map<String,Map<String,Object>> map = resultMap(hourlies);
+ Map<Integer,String> pointMap = pointMap(devices);
+ Map<String,String> sensorMap = sensorMap(sensorsList);
+
+ for (Device device:devices) {
+ Date ks = DateUtils.getDate(startTime,"yyyy-MM-dd HH");
+ Date js = DateUtils.getDateAddHour(DateUtils.getDate(endTime,"yyyy-MM-dd HH"),1);
+ while (DateUtils.isTimeBefor(js,ks)){
+ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key���time���value������������json
+ timeValueMap.put("������������",pointMap.get(device.getMonitorPointId()));
+ timeValueMap.put("name",device.getName());
+ String c = DateUtils.dateToDateString(ks,DateUtils.yyyy_MM_dd_HH_EN);
+ timeValueMap.put("������",c);
+ for (String sensor:sensorsList) {
+ String k = c+"_"+device.getMac();
+ Double num = 0d;
+ if(map.containsKey(k)){
+ Object o = map.get(k).get(sensor);
+ num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d;
+ }
+ timeValueMap.put(sensorMap.get(sensor),num);
+ }
+ resultList.add(timeValueMap);
+ ks = DateUtils.getDateAddHour(ks,1);
+ }
+ }
+ } else if("day".equals(type)){
+ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_dd_HH_EN);
+ Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_dd_HH_EN);
+ List<HistoryDaily> historyDailyByMacAndTimeSlot = historyDailyService.getHistoryDailyByMacAndTimeSlot(macs, startDate, endDate);
+ Map<String,Map<String,Object>> map = resultMap(historyDailyByMacAndTimeSlot,new ArrayList<>());
+ Map<Integer,String> pointMap = pointMap(devices);
+ Map<String,String> sensorMap = sensorMap(sensorsList);
+ for (Device device:devices) {
+ Date ks = DateUtils.getDate(startTime,"yyyy-MM-dd");
+ Date js = DateUtils.getDateOfDay(DateUtils.getDate(endTime,"yyyy-MM-dd"),1);
+ while (DateUtils.isTimeBefor(js,ks)){
+ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key���time���value������������json
+ timeValueMap.put("������������",pointMap.get(device.getMonitorPointId()));
+ timeValueMap.put("name",device.getName());
+ String c = DateUtils.dateToDateString(ks,"yyyy-MM-dd");
+ timeValueMap.put("������",c);
+ for (String sensor:sensorsList) {
+ String k = c+"_"+device.getMac();
+ Double num = 0d;
+ if(map.containsKey(k)){
+ Object o = map.get(k).get(sensor);
+ num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d;
+ }
+ timeValueMap.put(sensorMap.get(sensor),num);
+ }
+ resultList.add(timeValueMap);
+ ks = DateUtils.getDateOfDay(ks,1);
+ }
+ }
+
+ }else {
+ QueryWrapper<HistoryMonthly> HistoryMonthlyWrapper = new QueryWrapper<>();
+ HistoryMonthlyWrapper.in("mac",devices.stream().map(Device::getMac).collect(Collectors.toList()));
+ HistoryMonthlyWrapper.between("time",startTime,endTime);
+ List<HistoryMonthly> historyMonthlyList = historyMonthlyMapper.selectList(HistoryMonthlyWrapper);
+ Map<String,Map<String,Object>> map = resultMap(new ArrayList<>(),historyMonthlyList);
+ Map<Integer,String> pointMap = pointMap(devices);
+ Map<String,String> sensorMap = sensorMap(sensorsList);
+ for (Device device:devices) {
+ Date ks = DateUtils.getDate(startTime,"yyyy-MM");
+ Date js = DateUtils.addMonths(DateUtils.getDate(endTime,"yyyy-MM"),1);
+ while (DateUtils.isTimeBefor(js,ks)){
+ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key���time���value������������json
+ timeValueMap.put("������������",pointMap.get(device.getMonitorPointId()));
+ timeValueMap.put("name",device.getName());
+ String c = DateUtils.dateToDateString(ks,"yyyy-MM");
+ timeValueMap.put("������",c);
+ for (String sensor:sensorsList) {
+ String k = c+"_"+device.getMac();
+ Double num = 0d;
+ if(map.containsKey(k)){
+ Object o = map.get(k).get(sensor);
+ num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d;
+ }
+ timeValueMap.put(sensorMap.get(sensor),num);
+ }
+ resultList.add(timeValueMap);
+ ks = DateUtils.addMonths(ks,1);
+ }
+ /*for (String sensor:sensorsList) {
+ Date ks = DateUtils.getDate(startTime,"yyyy-MM");
+ Date js = DateUtils.addMonths(DateUtils.getDate(endTime,"yyyy-MM"),1);
+ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key���time���value������������json
+ timeValueMap.put("������������",pointMap.get(device.getMonitorPointId()));
+ timeValueMap.put("name",device.getName());
+ timeValueMap.put("sensor",sensorMap.get(sensor));
+ while (DateUtils.isTimeBefor(js,ks)){
+ String c = DateUtils.dateToDateString(ks,"yyyy-MM");
+ String k = c+"_"+device.getMac();
+ if(map.containsKey(k)){
+ Object o = map.get(k).get(sensor);
+ timeValueMap.put(c,Objects.nonNull(o)?Double.valueOf(o.toString()):0);
+ }else {
+ timeValueMap.put(c,0);
+ }
+ ks = DateUtils.addMonths(ks,1);
+ }
+ resultList.add(timeValueMap);
+ }*/
+ }
+ }
+ return resultList;
+ }
+
+
private Map<String,Map<String,Object>> resultMap(List<HistoryDaily> list ,List<HistoryMonthly> historyMonthlyList){
Map<String,Map<String,Object>> map = new HashMap<>();
for(HistoryDaily h : list){
@@ -333,6 +472,18 @@
return map;
}
+ private Map<String,Map<String,Object>> resultMap(List<HistoryHourly> list){
+ Map<String,Map<String,Object>> map = new HashMap<>();
+ for(HistoryHourly h : list){
+ String dateStr = DateUtils.dateToDateString( h.getTime(), DateUtils.yyyy_MM_dd_HH_EN)+"_"+h.getMac();
+ Map<String,Object> jsonMap = new HashMap<>();
+ JSONObject jsonObject = JSONObject.parseObject(h.getValue());
+ jsonMap = jsonObject.getInnerMap();
+ map.put(dateStr,jsonMap);
+ }
+
+ return map;
+ }
private Map<String,String> sensorMap(List<String> sensorsList){
Map<String,String> map = new HashMap<>();
QueryWrapper<Sensor> sensorQueryWrapper = new QueryWrapper<>();
diff --git a/screen-api/src/main/java/com/moral/api/utils/EasyExcelUtils.java b/screen-api/src/main/java/com/moral/api/utils/EasyExcelUtils.java
new file mode 100644
index 0000000..318a132
--- /dev/null
+++ b/screen-api/src/main/java/com/moral/api/utils/EasyExcelUtils.java
@@ -0,0 +1,77 @@
+package com.moral.api.utils;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.ExcelWriter;
+import com.alibaba.excel.support.ExcelTypeEnum;
+import com.alibaba.excel.write.metadata.WriteSheet;
+import com.alibaba.fastjson.JSON;
+import com.google.common.net.HttpHeaders;
+import org.apache.poi.ss.formula.functions.T;
+import org.springframework.web.bind.annotation.RequestBody;
+
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.net.URLEncoder;
+import java.util.*;
+
+public class EasyExcelUtils {
+ //������������������������
+ public void noModleWrite(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException {
+ // ������������ ���������������������swagger ������������������������������������������������������postman
+ try {
+// response.setContentType("application/vnd.ms-excel");
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ // ������URLEncoder.encode������������������������ ���������easyexcel������������
+ String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+ // ������������������������������
+ EasyExcel.write(response.getOutputStream()).head(head(data.getHeadMap())).sheet(data.getFileName()).doWrite(dataList(data.getDataList(), data.getDataStrMap()));
+ } catch (Exception e) {
+ // ������response
+ response.reset();
+ response.setContentType("application/json");
+ response.setCharacterEncoding("utf-8");
+ Map<String, String> map = new HashMap<String, String>();
+ map.put("status", "failure");
+ map.put("message", "������������������" + e.getMessage());
+ response.getWriter().println(JSON.toJSONString(map));
+ }
+ }
+
+ //���������������������
+ public <T> void simpleWrite(@RequestBody SimpleWriteData data,Class<T> clazz, HttpServletResponse response) throws IOException {
+ // ������������ ���������������������swagger ������������������������������������������������������postman
+// response.setContentType("application/vnd.ms-excel");
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ // ������URLEncoder.encode������������������������ ���������easyexcel������������
+ String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+ EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList());
+ }
+
+ //������������
+ private List<List<String>> head(String[] headMap) {
+ List<List<String>> list = new ArrayList<List<String>>();
+
+ for (String head : headMap) {
+ List<String> headList = new ArrayList<String>();
+ headList.add(head);
+ list.add(headList);
+ }
+ return list;
+ }
+
+ //���������������������������
+ private List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {
+ List<List<Object>> list = new ArrayList<List<Object>>();
+ for (Map<String, Object> map : dataList) {
+ List<Object> data = new ArrayList<Object>();
+ for (int i = 0; i < dataStrMap.length; i++) {
+ data.add(map.get(dataStrMap[i]));
+ }
+ list.add(data);
+ }
+ return list;
+ }
+}
diff --git a/screen-api/src/main/java/com/moral/api/utils/NoModelWriteData.java b/screen-api/src/main/java/com/moral/api/utils/NoModelWriteData.java
new file mode 100644
index 0000000..a1cb923
--- /dev/null
+++ b/screen-api/src/main/java/com/moral/api/utils/NoModelWriteData.java
@@ -0,0 +1,15 @@
+package com.moral.api.utils;
+
+import lombok.Data;
+
+import java.io.Serializable;
+import java.util.List;
+import java.util.Map;
+@Data
+public class NoModelWriteData implements Serializable {
+ private String fileName;//���������
+ private String[] headMap;//������������
+ private String[] dataStrMap;//������������������������
+ private List<Map<String, Object>> dataList;//������������
+
+}
diff --git a/screen-api/src/main/java/com/moral/api/utils/SimpleWriteData.java b/screen-api/src/main/java/com/moral/api/utils/SimpleWriteData.java
new file mode 100644
index 0000000..e59bec2
--- /dev/null
+++ b/screen-api/src/main/java/com/moral/api/utils/SimpleWriteData.java
@@ -0,0 +1,11 @@
+package com.moral.api.utils;
+
+import lombok.Data;
+
+import java.io.Serializable;
+import java.util.List;
+@Data
+public class SimpleWriteData implements Serializable {
+ private String fileName;//���������
+ private List<?> dataList;//������������
+}
diff --git a/screen-common/src/main/java/com/moral/util/DateUtils.java b/screen-common/src/main/java/com/moral/util/DateUtils.java
index 25c78ae..2de5766 100644
--- a/screen-common/src/main/java/com/moral/util/DateUtils.java
+++ b/screen-common/src/main/java/com/moral/util/DateUtils.java
@@ -622,6 +622,22 @@
return dateToDateString(now.getTime(), formatStr);
}
+
+ /**
+ * ������������������day���������������(formatStr)������������
+ *
+ * @param date
+ * @param date,
+ * @param hours
+ * @return
+ */
+ public static Date getDateAddHour(Date date,int hours) {
+ Calendar now = Calendar.getInstance(TimeZone.getDefault());
+ now.setTime(date);
+ now.add(Calendar.HOUR_OF_DAY, hours);
+ return now.getTime();
+ }
+
/**
* @Description: ������������������day���������������
* @Param: [date, day]
--
Gitblit v1.8.0