From 91e5d3d85c737b96b2c4a1994e2b861cc083453c Mon Sep 17 00:00:00 2001
From: xufenglei <xufenglei>
Date: Tue, 14 Nov 2017 10:24:46 +0800
Subject: [PATCH] 传感器平均数据 报表接口
---
src/main/java/com/moral/monitor/controller/ReportController.java | 84 ++++++++++++++++
src/main/resources/mapper/HistoryEntityMapper.xml | 44 ++++++++
src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java | 2
pom.xml | 6 +
src/main/java/com/moral/monitor/service/ReportService.java | 10 ++
src/main/java/com/moral/monitor/service/impl/ReportServiceImpl.java | 45 +++++++++
src/main/java/com/moral/monitor/util/ExportExcelUtils.java | 106 +++++++++++++++++++++
7 files changed, 296 insertions(+), 1 deletions(-)
diff --git a/pom.xml b/pom.xml
index 5389700..0a1b78b 100644
--- a/pom.xml
+++ b/pom.xml
@@ -222,7 +222,11 @@
<artifactId>spring-data-mongodb</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
-
+ <dependency>
+ <groupId>net.sourceforge.jexcelapi</groupId>
+ <artifactId>jxl</artifactId>
+ <version>2.6.12</version>
+ </dependency>
</dependencies>
diff --git a/src/main/java/com/moral/monitor/controller/ReportController.java b/src/main/java/com/moral/monitor/controller/ReportController.java
new file mode 100644
index 0000000..509ad8b
--- /dev/null
+++ b/src/main/java/com/moral/monitor/controller/ReportController.java
@@ -0,0 +1,84 @@
+package com.moral.monitor.controller;
+
+import java.io.OutputStream;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.util.ObjectUtils;
+import org.springframework.web.bind.annotation.CrossOrigin;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RequestMethod;
+import org.springframework.web.bind.annotation.RestController;
+
+import com.moral.monitor.service.ReportService;
+import com.moral.monitor.util.BusinessException;
+import com.moral.monitor.util.ExportExcelUtils;
+import com.moral.monitor.util.WebUtils;
+
+@RestController
+@RequestMapping(value = "report")
+@CrossOrigin(origins = "*", maxAge = 3600)
+public class ReportController {
+
+ @Autowired
+ ReportService reportService;
+
+ @RequestMapping(value = "/sensors-average", method = RequestMethod.GET)
+ public Map<String, Object> getSensorsAverageByEquipment(HttpServletRequest request,HttpServletResponse response) {
+ Map<String, Object> result = new HashMap<String, Object>();
+ try {
+ Map<String, Object> parameters = WebUtils.getParametersStartingWith(request, null);
+ Object mac = parameters.get("mac");
+ Object time = parameters.get("time");
+ Object type = parameters.get("type");
+ if (ObjectUtils.isEmpty(mac) || ObjectUtils.isEmpty(time) || ObjectUtils.isEmpty(type)) {
+ result.put("msg", "���������������������");
+ } else {
+ String[][] exportColumn = new String[][] {
+ new String[] { "������", "20", "time" },
+ new String[] { "Pm2.5", "10", "e1" },
+ new String[] { "Pm10", "20", "e2" },
+ new String[] { "0.1���0.3um���", "30", "e3" },
+ new String[] { "0.1���2.5um���", "10", "e4" },
+ new String[] { "������", "10", "e5" },
+ new String[] { "������", "10", "e6" },
+ new String[] { "������", "10", "e7" },
+ new String[] { "������(O2)", "10", "e8" },
+ new String[] { "������(CL2)", "10", "e9" },
+ new String[] { "������������(CO)", "10", "e10" },
+ new String[] { "������������(SO2)", "10", "e11" },
+ new String[] { "������", "10", "e12" },
+ new String[] { "������", "10", "e13" },
+ new String[] { "������(NH3)", "10", "e14" },
+ new String[] { "������(O3)", "10", "e15" },
+ new String[] { "������������(NO2)", "10", "e16" },
+ new String[] { "���������������������", "10", "e17" },
+ new String[] { "������", "10", "e18" },
+ new String[] { "������������(CO2)", "10", "e19" }
+ };
+ List<Map<String, Object>> sensorsAverage = reportService.getSensorsAverageByEquipment(parameters);
+ if (ObjectUtils.isEmpty(sensorsAverage)) {
+ result.put("msg", "���������������");
+ } else {
+ OutputStream outputStream = ExportExcelUtils.exportData(response, time + "���" + "���������" + mac + type + "������", sensorsAverage, exportColumn);
+ outputStream.flush();
+ outputStream.close();
+ result.put("msg", "������������");
+ }
+ }
+ } catch (BusinessException be) {
+ be.printStackTrace();
+ result.put("msg", be.getMessage());
+ } catch (Exception e) {
+ e.printStackTrace();
+ result.put("msg", "���������������������������������������������������"+e.getMessage());
+ }
+ return result;
+ }
+
+}
diff --git a/src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java b/src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java
index 4a097f7..ed6a3b1 100644
--- a/src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java
+++ b/src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java
@@ -15,4 +15,6 @@
List<Map<String, Object>> getEquipmentStates(Map<String, Object> parameters);
String getMacLogByLast();
+
+ List<Map<String, Object>> getSensorsAverageByEquipment(Map<String, Object> parameters);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/monitor/service/ReportService.java b/src/main/java/com/moral/monitor/service/ReportService.java
new file mode 100644
index 0000000..864996b
--- /dev/null
+++ b/src/main/java/com/moral/monitor/service/ReportService.java
@@ -0,0 +1,10 @@
+package com.moral.monitor.service;
+
+import java.util.List;
+import java.util.Map;
+
+public interface ReportService {
+
+ List<Map<String, Object>> getSensorsAverageByEquipment(Map<String, Object> parameters);
+
+}
diff --git a/src/main/java/com/moral/monitor/service/impl/ReportServiceImpl.java b/src/main/java/com/moral/monitor/service/impl/ReportServiceImpl.java
new file mode 100644
index 0000000..c48358f
--- /dev/null
+++ b/src/main/java/com/moral/monitor/service/impl/ReportServiceImpl.java
@@ -0,0 +1,45 @@
+package com.moral.monitor.service.impl;
+
+import java.text.ParseException;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.commons.lang3.time.DateUtils;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import com.moral.monitor.dao.HistoryEntityMapper;
+import com.moral.monitor.service.ReportService;
+import com.moral.monitor.util.BusinessException;
+
+@Service
+public class ReportServiceImpl implements ReportService {
+
+ @Autowired
+ private HistoryEntityMapper historyMapper;
+
+ @Override
+ public List<Map<String, Object>> getSensorsAverageByEquipment(Map<String, Object> parameters) {
+ Object type = parameters.get("type");
+ if ("hour".equals(type)) {
+ parameters.put("type", "%Y-%m-%d %H");
+ } else if ("minute".equals(type)) {
+ parameters.put("type", "%Y-%m-%d %H:%i");
+ } else {
+ throw new BusinessException("type���������������������");
+ }
+
+ try {
+ Date start = DateUtils.parseDate((String)parameters.get("time"), "yyyy-MM-dd");
+ parameters.put("start", start);
+ parameters.put("end", DateUtils.addDays(start, 1));
+ } catch (ParseException e) {
+ e.printStackTrace();
+ throw new BusinessException("time���������������������");
+ }
+
+ return historyMapper.getSensorsAverageByEquipment(parameters);
+ }
+
+}
diff --git a/src/main/java/com/moral/monitor/util/ExportExcelUtils.java b/src/main/java/com/moral/monitor/util/ExportExcelUtils.java
new file mode 100644
index 0000000..7490f11
--- /dev/null
+++ b/src/main/java/com/moral/monitor/util/ExportExcelUtils.java
@@ -0,0 +1,106 @@
+package com.moral.monitor.util;
+
+import java.io.OutputStream;
+import java.io.UnsupportedEncodingException;
+import java.util.List;
+
+import javax.servlet.http.HttpServletResponse;
+
+import jxl.Workbook;
+import jxl.write.Label;
+import jxl.write.WritableCellFormat;
+import jxl.write.WritableFont;
+import jxl.write.WritableSheet;
+import jxl.write.WritableWorkbook;
+import jxl.write.WriteException;
+import jxl.write.biff.RowsExceededException;
+import net.sf.json.JSONObject;
+
+@SuppressWarnings("rawtypes")
+public class ExportExcelUtils {
+
+ public static OutputStream exportData(HttpServletResponse response, String fileName, List list,String[][] exportColumn)
+ throws Exception {
+
+ // ���������������������
+ String excelFileName = fileName + ".xls";
+
+ OutputStream outputStream = response.getOutputStream();
+ // ���������������������
+ response.setHeader("Content-Type", "application/force-download");
+ // ������������������������������������������������
+ response.setHeader("Content-Disposition", "attachment; filename=\""
+ + new String(excelFileName.getBytes("gb2312"), "ISO8859-1")
+ + "\"");
+ // ������excel������
+ exportExcel(outputStream,list, getColumn(exportColumn),getName(exportColumn), fileName);
+
+ return outputStream;
+ }
+
+ public static void exportExcel(OutputStream os, List list, String[] colTitles, String[] fieldNames, String fileName) throws Exception {
+ WritableWorkbook wwb = Workbook.createWorkbook(os);
+
+ createSheet(wwb, fileName, 0, list, colTitles, fieldNames);
+
+ wwb.write();
+ wwb.close();
+ }
+
+ private static void createSheet(WritableWorkbook wwb, String sheetName, int index, List list, String[] colTitles,
+ String[] fieldNames)
+ throws RowsExceededException, WriteException, UnsupportedEncodingException {
+
+ WritableSheet ws = wwb.createSheet(sheetName, index);
+ // ������������������
+ WritableFont font = new WritableFont(WritableFont.createFont("������"), 10, WritableFont.BOLD);
+ WritableCellFormat wcf = new WritableCellFormat(font);
+
+ for (int i = 0; i < colTitles.length; i++) {
+ String title = colTitles[i];
+ ws.addCell(new Label(i, 0, title.split(",")[0], wcf));
+ ws.setColumnView(i, Integer.valueOf(title.split(",")[1]));
+ }
+ for (int i = 0; i < list.size(); i++) {
+ JSONObject jsonObject = JSONObject.fromObject(list.get(i));
+ for (int j = 0; j < colTitles.length; j++) {
+ ws.addCell(new Label(j, i + 1, jsonObject.getString(fieldNames[j])));
+ }
+ }
+ }
+
+ /**
+ * ������������ ��������� ��� ���������
+ *
+ * @return
+ */
+ public static String[] getColumn(String[][] exportColumn) {
+ if (exportColumn != null) {
+ String[] result = new String[exportColumn.length];
+ for (int i = 0; i < exportColumn.length; i++) {
+ result[i] = exportColumn[i][0] + "," + exportColumn[i][1];
+ }
+ return result;
+ } else {
+ return new String[0];
+ }
+ }
+
+ /**
+ * ������ ���������������
+ *
+ * @return
+ */
+ public static String[] getName(String[][] exportColumn) {
+ if (exportColumn != null) {
+ String[] result = new String[exportColumn.length];
+ for (int i = 0; i < exportColumn.length; i++) {
+ result[i] = exportColumn[i][2];
+ }
+ return result;
+ } else {
+ return new String[0];
+ }
+ }
+
+}
diff --git a/src/main/resources/mapper/HistoryEntityMapper.xml b/src/main/resources/mapper/HistoryEntityMapper.xml
index cddc593..85374e0 100644
--- a/src/main/resources/mapper/HistoryEntityMapper.xml
+++ b/src/main/resources/mapper/HistoryEntityMapper.xml
@@ -292,4 +292,48 @@
<select id="getMacLogByLast" resultType="string">
SELECT mac_log FROM logger ORDER BY time DESC LIMIT 1
</select>
+
+ <select id="getSensorsAverageByEquipment" resultType="map">
+ SELECT
+ concat(time,':00') AS 'time',
+ MAX(CASE WHEN mac_key='e1' THEN mac_value END) AS 'e1',
+ MAX(CASE WHEN mac_key='e2' THEN mac_value END) AS 'e2',
+ MAX(CASE WHEN mac_key='e3' THEN mac_value END) AS 'e3',
+ MAX(CASE WHEN mac_key='e4' THEN mac_value END) AS 'e4',
+ MAX(CASE WHEN mac_key='e5' THEN mac_value END) AS 'e5',
+ MAX(CASE WHEN mac_key='e6' THEN mac_value END) AS 'e6',
+ MAX(CASE WHEN mac_key='e7' THEN mac_value END) AS 'e7',
+ MAX(CASE WHEN mac_key='e8' THEN mac_value END) AS 'e8',
+ MAX(CASE WHEN mac_key='e9' THEN mac_value END) AS 'e9',
+ MAX(CASE WHEN mac_key='e10' THEN mac_value END) AS 'e10',
+ MAX(CASE WHEN mac_key='e11' THEN mac_value END) AS 'e11',
+ MAX(CASE WHEN mac_key='e12' THEN mac_value END) AS 'e12',
+ MAX(CASE WHEN mac_key='e13' THEN mac_value END) AS 'e13',
+ MAX(CASE WHEN mac_key='e14' THEN mac_value END) AS 'e14',
+ MAX(CASE WHEN mac_key='e15' THEN mac_value END) AS 'e15',
+ MAX(CASE WHEN mac_key='e16' THEN mac_value END) AS 'e16',
+ MAX(CASE WHEN mac_key='e17' THEN mac_value END) AS 'e17',
+ MAX(CASE WHEN mac_key='e18' THEN mac_value END) AS 'e18',
+ MAX(CASE WHEN mac_key='e19' THEN mac_value END) AS 'e19'
+ FROM
+ (
+ SELECT
+ h.mac_key,
+ DATE_FORMAT(time, #{type}) time,
+ AVG(h.mac_value) mac_value
+ FROM
+ history h
+ WHERE
+ h.mac = #{mac}
+ AND h.time >= #{start}
+ AND h.time < #{end}
+ GROUP BY
+ h.mac_key,
+ DATE_FORMAT(time, #{type})
+ ORDER BY
+ DATE_FORMAT(time, #{type})
+ ) a
+ GROUP BY
+ time
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0