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/HistoryMinutelyService.java | 5
src/main/java/com/moral/service/OrganizationService.java | 18
src/main/java/com/moral/service/impl/SensorUnitServiceImpl.java | 141 +++---
src/main/java/com/moral/controller/ReportController.java | 99 ++++-
src/main/java/com/moral/controller/SensorController.java | 47 +
src/main/java/com/moral/service/SensorService.java | 4
src/main/java/com/moral/mapper/SensorMapper.java | 27
pom.xml | 13
src/main/java/com/moral/controller/TokenController.java | 36 +
src/main/java/com/moral/mapper/HistoryMinutelyMapper.java | 10
src/main/java/com/moral/service/impl/SensorServiceImpl.java | 50 ++
src/main/java/com/moral/controller/OrganizationController.java | 45 +
src/main/resources/mapper/HistoryMinutelyMapper.xml | 29 +
src/main/java/com/moral/service/impl/HistoryServiceImpl.java | 2
src/main/resources/mapper/SensorMapper.xml | 24 +
src/main/java/com/moral/service/impl/OrganizationServiceImpl.java | 298 ++++++++-------
src/main/java/com/moral/controller/ScreenController.java | 12
src/main/java/com/moral/mapper/OrganizationMapper.java | 23
src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java | 61 ++
src/main/resources/mapper/OrganizationMapper.xml | 175 ++++----
20 files changed, 687 insertions(+), 432 deletions(-)
diff --git a/pom.xml b/pom.xml
index 83f8e88..fb81680 100644
--- a/pom.xml
+++ b/pom.xml
@@ -203,6 +203,19 @@
<artifactId>aviator</artifactId>
<version>4.2.0</version>
</dependency>
+
+ <dependency>
+ <groupId>cn.hutool</groupId>
+ <artifactId>hutool-all</artifactId>
+ <version>5.3.1</version>
+ </dependency>
+
+ <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi-ooxml</artifactId>
+ <version>4.1.2</version>
+ </dependency>
</dependencies>
<build>
diff --git a/src/main/java/com/moral/controller/OrganizationController.java b/src/main/java/com/moral/controller/OrganizationController.java
index b515654..43cc845 100644
--- a/src/main/java/com/moral/controller/OrganizationController.java
+++ b/src/main/java/com/moral/controller/OrganizationController.java
@@ -1,54 +1,71 @@
package com.moral.controller;
+import java.util.List;
+
+import javax.annotation.Resource;
+
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.PathVariable;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.RequestBody;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
import com.moral.common.bean.PageBean;
import com.moral.common.bean.ResultBean;
import com.moral.entity.Organization;
import com.moral.security.auth.JwtAuthenticationToken;
import com.moral.security.model.UserContext;
import com.moral.service.OrganizationService;
-import org.springframework.web.bind.annotation.*;
-
-import java.util.List;
-
-import javax.annotation.Resource;
@RestController
@RequestMapping("organization")
public class OrganizationController {
@Resource
OrganizationService organizationService;
+
@GetMapping("page-list")
public PageBean pageList(PageBean pageBean) {
return organizationService.queryByPageBean(pageBean);
}
+
@PostMapping("delete-by-ids")
- public ResultBean deleteByIds(@RequestBody Integer [] ids){
+ public ResultBean deleteByIds(@RequestBody Integer[] ids) {
organizationService.deleteByIds(ids);
ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
return resultBean;
}
+
@PostMapping("add-or-modify")
- public ResultBean addOrModify(@RequestBody Organization organization){
+ public ResultBean addOrModify(@RequestBody Organization organization) {
organizationService.addOrModify(organization);
ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
return resultBean;
}
-
+
@GetMapping("list/{name}")
- public ResultBean<List<Organization>> getOrganizationsByName(@PathVariable("name") String name) {
- List<Organization> organizations = organizationService.getOrganizationsByName(name);
- return new ResultBean<List<Organization>>(organizations);
- }
+ public ResultBean<List<Organization>> getOrganizationsByName(@PathVariable("name") String name) {
+ List<Organization> organizations = organizationService.getOrganizationsByName(name);
+ return new ResultBean<List<Organization>>(organizations);
+ }
@GetMapping("get-my-org")
- public ResultBean<Organization> getMyOrganization(JwtAuthenticationToken token){
+ public ResultBean<Organization> getMyOrganization(JwtAuthenticationToken token) {
UserContext userContext = token.getPrincipal();
Organization organization = organizationService.getOrganizationById(userContext.getOrganizationId());
return new ResultBean<>(organization);
}
+
@GetMapping("getforg")
- public ResultBean<Organization> getFatherOrg(Integer cid){
+ public ResultBean<Organization> getFatherOrg(Integer cid) {
Organization organization = organizationService.getFatherOrg(cid);
return new ResultBean<>(organization);
}
+
+ @GetMapping("organization-list")
+ public ResultBean getOrganizationList(String organizationName) {
+ ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
+ resultBean.setData(organizationService.getOrganizationList(organizationName));
+ return resultBean;
+ }
}
diff --git a/src/main/java/com/moral/controller/ReportController.java b/src/main/java/com/moral/controller/ReportController.java
index c86ffbb..b4ef1d7 100644
--- a/src/main/java/com/moral/controller/ReportController.java
+++ b/src/main/java/com/moral/controller/ReportController.java
@@ -2,12 +2,12 @@
import java.io.OutputStream;
import java.util.ArrayList;
-import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
+import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@@ -20,6 +20,7 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
+import com.alibaba.fastjson.JSONObject;
import com.moral.common.bean.ResultBean;
import com.moral.common.util.ParameterUtils;
import com.moral.common.util.WebUtils;
@@ -32,6 +33,9 @@
import com.moral.service.HistoryDailyService;
import com.moral.service.HistoryMinutelyService;
import com.moral.service.MonitorPointService;
+
+import cn.hutool.core.io.IoUtil;
+import cn.hutool.poi.excel.ExcelWriter;
import static com.moral.common.util.ExportExcelUtils.exportData;
import static com.moral.common.util.WebUtils.getParametersStartingWith;
@@ -139,9 +143,26 @@
public ResultBean<Boolean> getCustomMadeExcelReport(HttpServletRequest request, HttpServletResponse response) throws Exception {
Map<String, Object> parameters = getParametersStartingWith(request, null);
ParameterUtils.getTimeType4Time(parameters);
- Object sensorKey = parameters.get("sensorKey");
- String sensorInfo = parameters.get("sensorInfo").toString();
- parameters.put("sensorKeys", Arrays.asList(sensorKey));
+ String sensorsInfo = parameters.get("sensors").toString();
+ String[] sensorsStr = sensorsInfo.split(",");
+ List<String> sensorKeys = new ArrayList<>();
+ List<String> sensorKeysNames = new ArrayList<>();
+ List<String> sensorKeysUnits = new ArrayList<>();
+ for (int index = 0; index < sensorsStr.length; index++) {
+ String[] split = sensorsStr[index].split("-");
+ String key;
+ if (index == 0) {
+ key = split[0].replace("\"", "").substring(1);
+ } else {
+ key = split[0].replace("\"", "");
+ }
+ String name = split[1].replace("\"", "");
+ String Unit = split[2].replace("\"", "");
+ sensorKeys.add(key);
+ sensorKeysNames.add(name);
+ sensorKeysUnits.add(Unit);
+ }
+ parameters.put("sensorKeys", sensorKeys);
List<String> macList = new ArrayList<>();
List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsAndDevicesByRegion(parameters);
for (MonitorPoint m : monitorPoints) {
@@ -152,27 +173,63 @@
parameters.put("macs", macList);
List<Map<String, Object>> list = new ArrayList<>();
if (!CollectionUtils.isEmpty(macList)) {
- list = historyMinutelyService.getDevicesAvgDataToExcel(parameters);
+ list = historyMinutelyService.getDevicesSensorsAvgDataToExcel(parameters);
}
- Map<String, Object> map = list.get(0);
- Map<Integer, String> mapNew = new LinkedHashMap<>();
- int i = 0;
- for (String key : map.keySet()) {
- if (!("monitorPointName".equals(key) || "name".equals(key))) {
- mapNew.put(i, key);
- i++;
+ List<List<Map<String, Object>>> sheets = new ArrayList<>();
+ for (int i = 0; i < sensorKeys.size(); i++) {
+ List<Map<String, Object>> sheet = new ArrayList<>();
+ for (int j = 0; j < list.size(); j++) {
+ Map<String, Object> data = new LinkedHashMap<>();
+ for (Map.Entry<String, Object> kv : list.get(j).entrySet()) {
+ if ("monitorPointName".equals(kv.getKey())) {
+ data.put("������������(������:" + sensorKeysUnits.get(i) + ")", kv.getValue());
+ } else if ("name".equals(kv.getKey())) {
+ data.put("������������", kv.getValue());
+ } else {
+ String sensorsValue = kv.getValue().toString();
+ JSONObject jsonObject = JSONObject.parseObject(sensorsValue);
+ if (jsonObject != null) {
+ List<Object> sensorsValueList = (List<Object>) jsonObject.get(sensorKeys.get(i));
+ if (sensorsValueList != null) {
+ data.put(kv.getKey(), sensorsValueList.get(0));
+ } else {
+ data.put(kv.getKey(), "");
+ }
+ } else {
+ data.put(kv.getKey(), "");
+ }
+ }
+ }
+ sheet.add(data);
}
+ sheets.add(sheet);
}
- String[][] exportColumn = new String[map.size()][];
- exportColumn[0] = new String[]{"������������-" + sensorInfo, "30", "monitorPointName"};
- exportColumn[1] = new String[]{"������������", "20", "name"};
- for (int index = 0; index < map.size() - 2; index++) {
- exportColumn[index + 2] = new String[]{mapNew.get(index), "20", mapNew.get(index)};
+ if (!CollectionUtils.isEmpty(sheets)) {
+ ExcelWriter writer = new ExcelWriter(false, sensorKeysNames.get(0));
+ writer.write(sheets.get(0), true);
+ writer.autoSizeColumnAll();
+ writer.setColumnWidth(0, 25);
+ writer.setColumnWidth(1, 25);
+ if (sheets.size() >= 2) {
+ for (int i = 1; i < sheets.size(); i++) {
+ writer.setSheet(sensorKeysNames.get(i));
+ writer.write(sheets.get(i), true);
+ writer.autoSizeColumnAll();
+ writer.setColumnWidth(0, 25);
+ writer.setColumnWidth(1, 25);
+ }
+ }
+ response.setContentType("application/vnd.ms-excel;charset=utf-8");
+ String codedFileName = java.net.URLEncoder.encode("Excel������", "UTF-8");
+ response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
+ ServletOutputStream out = response.getOutputStream();
+ writer.flush(out, true);
+ // ������writer���������������
+ writer.close();
+ //������������������������Servlet���
+ IoUtil.close(out);
}
- OutputStream outputStream = exportData(response, "Excel������", list, exportColumn);
- outputStream.flush();
- outputStream.close();
- return new ResultBean<Boolean>(true);
+ return new ResultBean<>(true);
}
}
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index bcf8f89..e4bb92c 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -18,8 +18,10 @@
import java.util.List;
import java.util.Map;
import java.util.Optional;
-import java.util.Set;
import java.util.stream.Collectors;
+
+import javax.annotation.Resource;
+import javax.servlet.http.HttpServletRequest;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
@@ -100,9 +102,6 @@
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
-
-import javax.annotation.Resource;
-import javax.servlet.http.HttpServletRequest;
import static com.moral.common.util.ResourceUtil.getValue;
import static com.moral.common.util.WebUtils.getParametersStartingWith;
@@ -1069,7 +1068,6 @@
Map<String, Object> map = list.get(0);
String monitor_point_id = map.get("monitor_point_id").toString();
List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);
- System.out.println(sensorUnitList);
for (Map<String, Object> sensorMap : list) {
for (Map<String, Object> sensorUnitMap : sensorUnitList) {
if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensor_key"))) {
@@ -1090,8 +1088,6 @@
String monitor_point_id = (String) parameters.get("monitor_point_id");
List<Map<String, Object>> list = sensorService.getSensorByMonitorPointId(monitor_point_id);
List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);
- System.out.println("list:" + list);
- System.out.println("sensorUnitList:" + sensorUnitList);
for (Map<String, Object> sensorMap : list) {
for (Map<String, Object> sensorUnitMap : sensorUnitList) {
if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensorKey"))) {
@@ -1306,9 +1302,7 @@
sensorMap.put("e10", Double.parseDouble(map.get("co").toString()));
sensorMap.put("e15", Double.parseDouble(map.get("o3").toString()));
Map<String, Object> StateControl = AQICalculation.hourlyAQI(sensorMap);
- System.out.println("StateControl:" + StateControl);
Map<String, Object> hourAqi = historyService.gitHourlyAQIByMonitorPointIdAndTimeslot(parameters);
- System.out.println("hourAqi:" + hourAqi);
Map<String, Object> returnMap = new HashMap<>();
returnMap.put("MonitorPointAQI", hourAqi.get("AQI"));
returnMap.put("StateControlAQI", StateControl.get("AQI"));
diff --git a/src/main/java/com/moral/controller/SensorController.java b/src/main/java/com/moral/controller/SensorController.java
index 476714d..3e6a47c 100644
--- a/src/main/java/com/moral/controller/SensorController.java
+++ b/src/main/java/com/moral/controller/SensorController.java
@@ -1,13 +1,23 @@
package com.moral.controller;
-import com.moral.common.bean.PageBean;
-import com.moral.common.bean.ResultBean;
-import com.moral.entity.Sensor;
-import com.moral.service.SensorService;
-import org.springframework.web.bind.annotation.*;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
import javax.annotation.Resource;
-import java.util.List;
+
+import org.springframework.web.bind.annotation.CrossOrigin;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.RequestBody;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+import com.moral.common.bean.PageBean;
+import com.moral.common.bean.ResultBean;
+import com.moral.common.util.StringUtils;
+import com.moral.entity.Sensor;
+import com.moral.service.SensorService;
@RestController
@RequestMapping("sensor")
@@ -15,31 +25,46 @@
public class SensorController {
@Resource
SensorService sensorService;
+
@GetMapping("page-list")
public PageBean pageList(PageBean pageBean) {
return sensorService.queryByPageBean(pageBean);
}
+
@GetMapping("list-by-vid")
- public PageBean pageListByVersionId(Integer versionId){
+ public PageBean pageListByVersionId(Integer versionId) {
return sensorService.queryByVersionId(versionId);
}
+
@PostMapping("delete-by-ids")
- public ResultBean deleteByIds(@RequestBody Integer [] ids){
+ public ResultBean deleteByIds(@RequestBody Integer[] ids) {
sensorService.deleteByIds(ids);
ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
return resultBean;
}
+
@PostMapping("add-or-modify")
- public ResultBean addOrModify(@RequestBody Sensor sensor){
+ public ResultBean addOrModify(@RequestBody Sensor sensor) {
sensorService.addOrModify(sensor);
ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
return resultBean;
}
-
+
@GetMapping("all")
public ResultBean<List<Sensor>> getAllSensors() {
- List<Sensor> sensors = sensorService.getAllSensors();
+ List<Sensor> sensors = sensorService.getAllSensors();
return new ResultBean<List<Sensor>>(sensors);
}
+ @GetMapping("getSenosrsByOrgId")
+ public ResultBean<List<Sensor>> getSenosrsByOrgId(Integer organizationId, String regionCode) {
+ Map<String, Object> parameters = new HashMap<>();
+ parameters.put("organizationId", organizationId);
+ if (!StringUtils.isNullOrEmpty(regionCode)) {
+ parameters.put("regionCode", Long.valueOf(regionCode));
+ }
+ List<Sensor> sensors = sensorService.selectSenosrsByOrgId(parameters);
+ return new ResultBean<>(sensors);
+ }
+
}
diff --git a/src/main/java/com/moral/controller/TokenController.java b/src/main/java/com/moral/controller/TokenController.java
index 2b5fead..85b6dec 100644
--- a/src/main/java/com/moral/controller/TokenController.java
+++ b/src/main/java/com/moral/controller/TokenController.java
@@ -1,8 +1,14 @@
package com.moral.controller;
-import com.moral.entity.auth.AuthToken;
+import java.util.Arrays;
+import java.util.LinkedHashMap;
+
import org.apache.commons.codec.binary.Base64;
-import org.springframework.http.*;
+import org.springframework.http.HttpEntity;
+import org.springframework.http.HttpHeaders;
+import org.springframework.http.HttpMethod;
+import org.springframework.http.MediaType;
+import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
@@ -10,8 +16,7 @@
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.client.RestTemplate;
-import java.util.Arrays;
-import java.util.LinkedHashMap;
+import com.moral.entity.auth.AuthToken;
@Controller
public class TokenController {
@@ -24,29 +29,28 @@
RestTemplate restTemplate = new RestTemplate();
HttpEntity<String> request = new HttpEntity<String>(getHeadersWithClientCredentials());
- ResponseEntity<Object> response = restTemplate.exchange(AUTH_SERVER_URI+"username="+username+"&password="+password, HttpMethod.POST, request, Object.class);
- LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>)response.getBody();
+ ResponseEntity<Object> response = restTemplate.exchange(AUTH_SERVER_URI + "username=" + username + "&password=" + password, HttpMethod.POST, request, Object.class);
+ LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) response.getBody();
AuthToken token = null;
- if(map!=null){
+ if (map != null) {
token = new AuthToken();
- token.setAccess_token((String)map.get("access_token"));
- token.setToken_type((String)map.get("token_type"));
- token.setRefresh_token((String)map.get("refresh_token"));
- token.setExpires_in((Integer)map.get("expires_in"));
- token.setScope((String)map.get("scope"));
- System.out.println(token);
+ token.setAccess_token((String) map.get("access_token"));
+ token.setToken_type((String) map.get("token_type"));
+ token.setRefresh_token((String) map.get("refresh_token"));
+ token.setExpires_in((Integer) map.get("expires_in"));
+ token.setScope((String) map.get("scope"));
}
return token;
}
- private static HttpHeaders getHeaders(){
+ private static HttpHeaders getHeaders() {
HttpHeaders headers = new HttpHeaders();
headers.setAccept(Arrays.asList(MediaType.APPLICATION_JSON));
return headers;
}
- private static HttpHeaders getHeadersWithClientCredentials(){
- String plainClientCredentials="my-trusted-client:secret";
+ private static HttpHeaders getHeadersWithClientCredentials() {
+ String plainClientCredentials = "my-trusted-client:secret";
String base64ClientCredentials = new String(Base64.encodeBase64(plainClientCredentials.getBytes()));
HttpHeaders headers = getHeaders();
headers.add("Authorization", "Basic " + base64ClientCredentials);
diff --git a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
index 64fcda7..0b09df2 100644
--- a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
@@ -1,12 +1,12 @@
package com.moral.mapper;
-import com.moral.entity.charts.DataCondition;
-import com.moral.entity.charts.TimePeriod;
+import java.util.List;
+import java.util.Map;
import org.apache.ibatis.annotations.Param;
-import java.util.List;
-import java.util.Map;
+import com.moral.entity.charts.DataCondition;
+import com.moral.entity.charts.TimePeriod;
public interface HistoryMinutelyMapper {
@@ -27,4 +27,6 @@
List<Map<String, Object>> getSensorData2020(Map<String, Object> parameters);
List<Map<String, Object>> getDevicesAvgDataToExcel(Map<String, Object> parameters);
+
+ List<Map<String, Object>> getDevicesSensorsAvgDataToExcel(Map<String, Object> parameters);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/mapper/OrganizationMapper.java b/src/main/java/com/moral/mapper/OrganizationMapper.java
index 56c73bb..de48897 100644
--- a/src/main/java/com/moral/mapper/OrganizationMapper.java
+++ b/src/main/java/com/moral/mapper/OrganizationMapper.java
@@ -3,18 +3,25 @@
import java.util.List;
import java.util.Map;
+import org.apache.ibatis.annotations.Param;
+
import com.moral.common.mapper.BaseMapper;
import com.moral.entity.Organization;
+
import tk.mybatis.mapper.entity.Example;
public interface OrganizationMapper extends BaseMapper<Organization> {
- List<Organization> selectWithAreaNameByExample(Example example);
- List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);
+ List<Organization> selectWithAreaNameByExample(Example example);
- /**
- * ���������������������������������id���������id���������0
- * @param orgId
- * @return
- */
- List<Integer> selectLowerOrgIds(Integer orgId);
+ List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);
+
+ /**
+ * ���������������������������������id���������id���������0
+ *
+ * @param orgId
+ * @return
+ */
+ List<Integer> selectLowerOrgIds(Integer orgId);
+
+ List<Organization> getOrganizationList(@Param("organizationName") String organizationName);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/mapper/SensorMapper.java b/src/main/java/com/moral/mapper/SensorMapper.java
index 0a63b1e..6c8e581 100644
--- a/src/main/java/com/moral/mapper/SensorMapper.java
+++ b/src/main/java/com/moral/mapper/SensorMapper.java
@@ -1,11 +1,12 @@
package com.moral.mapper;
-import com.moral.common.mapper.BaseMapper;
-import com.moral.entity.Sensor;
-import org.apache.ibatis.annotations.Param;
-
import java.util.List;
import java.util.Map;
+
+import org.apache.ibatis.annotations.Param;
+
+import com.moral.common.mapper.BaseMapper;
+import com.moral.entity.Sensor;
public interface SensorMapper extends BaseMapper<Sensor> {
List<Sensor> selectByVersionId(Integer deviceVersionId);
@@ -16,17 +17,19 @@
List<Sensor> selectByOrgId(Integer organizationId);
- List<Map<String, Object>> getSensorsByDeviceVersionId(Map<String, Object> parameters);
-
- List<Sensor> getSensorsByCriteria(Map<String, Object> parameters);
-
- Map<String,Object> getSensorBySensorKey(@Param("sensor_key")String sensor_key);
-
- List<Map<String,Object>> getSensorByDeviceId(@Param("id")String id);
+ List<Map<String, Object>> getSensorsByDeviceVersionId(Map<String, Object> parameters);
- List<Map<String,Object>> getSensorByMonitorPointId(@Param("monitor_point_id")String monitor_point_id);
+ List<Sensor> getSensorsByCriteria(Map<String, Object> parameters);
+
+ Map<String, Object> getSensorBySensorKey(@Param("sensor_key") String sensor_key);
+
+ List<Map<String, Object>> getSensorByDeviceId(@Param("id") String id);
+
+ List<Map<String, Object>> getSensorByMonitorPointId(@Param("monitor_point_id") String monitor_point_id);
List<Sensor> getSensorsByMac(Map<String, Object> parameters);
List<Sensor> getSensorsInfoByMac(@Param("mac") String mac);
+
+ List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/HistoryMinutelyService.java b/src/main/java/com/moral/service/HistoryMinutelyService.java
index 7f461f3..ae7b1d4 100644
--- a/src/main/java/com/moral/service/HistoryMinutelyService.java
+++ b/src/main/java/com/moral/service/HistoryMinutelyService.java
@@ -10,7 +10,7 @@
public interface HistoryMinutelyService {
Map<String, Object> getDayAQIByDevice(Map<String, Object> parameters);
-
+
Map<String, Object> getHourAQIByDevice(Map<String, Object> parameters);
Map<String, List> getCompareReport(Map<String, Object> parameters) throws Exception;
@@ -19,7 +19,6 @@
Map<String, Object> getMonthAverageBySensor(Map<String, Object> parameters);
-
Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);
void convertQueryParam(Map<String, Object> parameters) throws ParseException;
@@ -27,4 +26,6 @@
List<List<Map<String, Object>>> getSensorData(Map<String, Object> parameters) throws Exception;
List<Map<String, Object>> getDevicesAvgDataToExcel(Map<String, Object> parameters) throws Exception;
+
+ List<Map<String, Object>> getDevicesSensorsAvgDataToExcel(Map<String, Object> parameters) throws Exception;
}
diff --git a/src/main/java/com/moral/service/OrganizationService.java b/src/main/java/com/moral/service/OrganizationService.java
index fe1dcc3..11b1d39 100644
--- a/src/main/java/com/moral/service/OrganizationService.java
+++ b/src/main/java/com/moral/service/OrganizationService.java
@@ -5,22 +5,24 @@
import java.util.Set;
import com.moral.common.bean.PageBean;
-import com.moral.entity.DeviceVersion;
import com.moral.entity.Organization;
public interface OrganizationService {
- Set<Integer> getChildOrganizationIds(Integer orgId);
+ Set<Integer> getChildOrganizationIds(Integer orgId);
- List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);
+ List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);
- public PageBean queryByPageBean(PageBean pageBean);
+ public PageBean queryByPageBean(PageBean pageBean);
- public void addOrModify(Organization organization);
+ public void addOrModify(Organization organization);
- public void deleteByIds(Integer... ids);
-
- List<Organization> getOrganizationsByName(String name);
+ public void deleteByIds(Integer... ids);
+
+ List<Organization> getOrganizationsByName(String name);
+
public Organization getOrganizationById(int id);
public Organization getFatherOrg(int childId);
+
+ List<Organization> getOrganizationList(String organizationName);
}
diff --git a/src/main/java/com/moral/service/SensorService.java b/src/main/java/com/moral/service/SensorService.java
index 51222a5..b96018f 100644
--- a/src/main/java/com/moral/service/SensorService.java
+++ b/src/main/java/com/moral/service/SensorService.java
@@ -6,8 +6,6 @@
import com.moral.common.bean.PageBean;
import com.moral.entity.Sensor;
-import java.util.List;
-
public interface SensorService {
List<Sensor> queryAll();
@@ -37,4 +35,6 @@
List<Map<String, String>> getSensorsMaps(String mac);
+ List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters);
+
}
diff --git a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
index 0333593..0b9267e 100644
--- a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
@@ -27,6 +27,8 @@
import java.util.concurrent.Executors;
import java.util.stream.Collectors;
+import javax.annotation.Resource;
+
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.stereotype.Service;
@@ -49,8 +51,6 @@
import com.moral.mapper.HistoryMinutelyMapper;
import com.moral.mapper.SensorMapper;
import com.moral.service.HistoryMinutelyService;
-
-import javax.annotation.Resource;
import static com.moral.common.bean.Constants.NULL_VALUE;
import static org.springframework.util.ObjectUtils.isEmpty;
@@ -106,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);
@@ -595,7 +592,7 @@
}
}
}
- 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"))){
+ 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);
@@ -614,28 +611,28 @@
if ("month".equals(parameters.get("type"))) {
parameters.put("timeUnits", "daily");
parameters.put("typeFormat", "%Y-%m-%d");
- String time = parameters.get("time")+"-01T00:00:00";
+ 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++){
+ 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";
+ } 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);
+ 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++){
+ 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);
@@ -643,4 +640,38 @@
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);
+ }
+
}
diff --git a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
index a9f03ea..9008efe 100644
--- a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
@@ -378,7 +378,6 @@
startTime = localDate + " " + startHour + ":00:00";
}
Map<String, Object> map = historyMapper.getAVGValueByMacAndTimeslot(mac, startTime, endTime);
- System.out.println(map);
Map<String, Object> returnMap = new HashMap<>();
if (map.isEmpty()) {
returnMap.put("AQI", "N/V");
@@ -465,7 +464,6 @@
startTime = localDate + " " + startHour + ":00:00";
}
Map<String, Object> map = historyMapper.getMonitorPointAVGValueByMonitorPointIdAndTimeslot(monitor_point_id, startTime, endTime);
- System.out.println(map);
Map<String, Object> returnMap = new HashMap<>();
if (map.isEmpty()) {
returnMap.put("AQI", "N/V");
diff --git a/src/main/java/com/moral/service/impl/OrganizationServiceImpl.java b/src/main/java/com/moral/service/impl/OrganizationServiceImpl.java
index da6e198..cb68b39 100644
--- a/src/main/java/com/moral/service/impl/OrganizationServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/OrganizationServiceImpl.java
@@ -1,7 +1,5 @@
package com.moral.service.impl;
-import static com.moral.common.bean.Constants.IS_DELETE_FALSE;
-
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
@@ -32,6 +30,8 @@
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;
+import static com.moral.common.bean.Constants.IS_DELETE_FALSE;
+
@Service
public class OrganizationServiceImpl implements OrganizationService {
@@ -41,170 +41,178 @@
@Resource
private OrganizationRelationMapper organizationRelationMapper;
- private static Class ENTITY_CLASS = Organization.class;
+ private static Class ENTITY_CLASS = Organization.class;
@Override
- public Set<Integer> getChildOrganizationIds(Integer orgId){
- Set<Integer> orgIds = new HashSet<Integer>();
- orgIds.add(orgId);
- OrganizationRelation relation = new OrganizationRelation();
- relation.setParentId(orgId);
- Organization organization = organizationMapper.selectByPrimaryKey(orgId);
- if (IS_DELETE_FALSE.equals(organization.getIsDelete())) {
- List<OrganizationRelation> organizationRelations = organizationRelationMapper.select(relation);
- for (OrganizationRelation organizationRelation : organizationRelations) {
- Set<Integer> organizationIds = getChildOrganizationIds(organizationRelation.getChildId());
- orgIds.addAll(organizationIds);
- }
- }
- return orgIds;
+ public Set<Integer> getChildOrganizationIds(Integer orgId) {
+ Set<Integer> orgIds = new HashSet<Integer>();
+ orgIds.add(orgId);
+ OrganizationRelation relation = new OrganizationRelation();
+ relation.setParentId(orgId);
+ Organization organization = organizationMapper.selectByPrimaryKey(orgId);
+ if (IS_DELETE_FALSE.equals(organization.getIsDelete())) {
+ List<OrganizationRelation> organizationRelations = organizationRelationMapper.select(relation);
+ for (OrganizationRelation organizationRelation : organizationRelations) {
+ Set<Integer> organizationIds = getChildOrganizationIds(organizationRelation.getChildId());
+ orgIds.addAll(organizationIds);
+ }
+ }
+ return orgIds;
}
- @Override
- public List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters) {
- ValidateUtil.notNull(parameters.get("areaName"), "param.is.null");
- List<Organization> organizations = organizationMapper.getOrganizationsByAreaName(parameters);
- return organizations;
- }
+ @Override
+ public List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters) {
+ ValidateUtil.notNull(parameters.get("areaName"), "param.is.null");
+ List<Organization> organizations = organizationMapper.getOrganizationsByAreaName(parameters);
+ return organizations;
+ }
- @Override
- public PageBean queryByPageBean(PageBean pageBean){
- Example example = ExampleUtil.generateExample(ENTITY_CLASS,pageBean);
- List<Example.Criteria> criteriaList = example.getOredCriteria();
- if(criteriaList!=null&&criteriaList.size()>0){
- for(Example.Criteria cri : criteriaList){
- cri.andNotEqualTo("isDelete",Constants.IS_DELETE_TRUE);
- }
- }else {
- example.or().andNotEqualTo("isDelete",Constants.IS_DELETE_TRUE);
- }
- if(pageBean.getPageSize()>0){
- PageHelper.startPage(pageBean.getPageIndex(),pageBean.getPageSize());
- }
- if(example.getOrderByClause() == null || example.getOrderByClause().isEmpty()) {
- example.setOrderByClause("create_time desc");
- }
- List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);
- return new PageBean(organizationList);
- }
- @Override
- public void addOrModify(Organization organization){
- try{
- if(organization.getId()==null){
- //���������������������������������������
- organization.setIsDelete(Constants.IS_DELETE_FALSE);
- organizationMapper.insertSelective(organization);
- }else{
- organizationMapper.updateByPrimaryKey(organization);
- }
- if(organization.getId()!=null && !organization.getId().equals(organization.getParentId())){
- List<Integer> orgIds = organizationMapper.selectLowerOrgIds(organization.getId());
- boolean isChild = orgIds.stream().anyMatch(
- item -> {
- return item!=null && item.equals(organization.getParentId());
- }
- );
- // ������������������������������������������������������������
- if(!isChild){
- //���������������������
- addOrModifyOrgRelation(organization);
- }else{
- throw new BusinessException("cannot set the children as father,organization:"+ JSON.toJSONString(organization));
- }
- }else {
- throw new BusinessException("id of organization can't equal to it's parentId,organization:"+ JSON.toJSONString(organization));
- }
- }
- catch (Exception ex){
- throw ex;
- }
- }
- @Transient
- private void addOrModifyOrgRelation(Organization organization){
- OrganizationRelation relation = new OrganizationRelation();
- relation.setChildId(organization.getId());
- List<OrganizationRelation> relations = organizationRelationMapper.select(relation);
- if(CollectionUtils.isEmpty(relations)){
- if (!ObjectUtils.isEmpty(organization.getParentId())) {
- relation.setParentId(organization.getParentId());
- organizationRelationMapper.insert(relation);
- }
- }else{
- OrganizationRelation relationOfSelect = relations.get(0);
- //���������������������������������������
- if(organization.getParentId() !=null &&
- !organization.getParentId().equals(relationOfSelect.getParentId())
- ) {
- relationOfSelect.setParentId(organization.getParentId());
- organizationRelationMapper.updateByPrimaryKey(relationOfSelect);
- }else if(organization.getParentId() ==null){
- //ParentI���null���������������������
- organizationRelationMapper.deleteByPrimaryKey(relationOfSelect.getId());
+ @Override
+ public PageBean queryByPageBean(PageBean pageBean) {
+ Example example = ExampleUtil.generateExample(ENTITY_CLASS, pageBean);
+ List<Example.Criteria> criteriaList = example.getOredCriteria();
+ if (criteriaList != null && criteriaList.size() > 0) {
+ for (Example.Criteria cri : criteriaList) {
+ cri.andNotEqualTo("isDelete", Constants.IS_DELETE_TRUE);
+ }
+ } else {
+ example.or().andNotEqualTo("isDelete", Constants.IS_DELETE_TRUE);
+ }
+ if (pageBean.getPageSize() > 0) {
+ PageHelper.startPage(pageBean.getPageIndex(), pageBean.getPageSize());
+ }
+ if (example.getOrderByClause() == null || example.getOrderByClause().isEmpty()) {
+ example.setOrderByClause("create_time desc");
+ }
+ List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);
+ return new PageBean(organizationList);
+ }
+
+ @Override
+ public void addOrModify(Organization organization) {
+ try {
+ if (organization.getId() == null) {
+ //���������������������������������������
+ organization.setIsDelete(Constants.IS_DELETE_FALSE);
+ organizationMapper.insertSelective(organization);
+ } else {
+ organizationMapper.updateByPrimaryKey(organization);
+ }
+ if (organization.getId() != null && !organization.getId().equals(organization.getParentId())) {
+ List<Integer> orgIds = organizationMapper.selectLowerOrgIds(organization.getId());
+ boolean isChild = orgIds.stream().anyMatch(
+ item -> {
+ return item != null && item.equals(organization.getParentId());
+ }
+ );
+ // ������������������������������������������������������������
+ if (!isChild) {
+ //���������������������
+ addOrModifyOrgRelation(organization);
+ } else {
+ throw new BusinessException("cannot set the children as father,organization:" + JSON.toJSONString(organization));
}
- //������������������
- if(relations.size()>1){
- relations.remove(0);
- List<Integer> relationIds = relations.stream().map(
- element->{
- return element.getId();
- }
- ).collect(Collectors.toList());
- Example example = new Example(OrganizationRelation.class);
- example.or().andIn("id",relationIds);
- organizationRelationMapper.deleteByExample(example);
- }
- }
- }
- @Override
- public void deleteByIds(Integer... ids) {
- Organization organization = new Organization();
- organization.setIsDelete(Constants.IS_DELETE_TRUE);
- if(ids!=null&&ids.length>0){
- if(ids.length==1){
- organization.setId(ids[0]);
- organizationMapper.updateByPrimaryKeySelective(organization);
- }else{
- Example example = new Example(ENTITY_CLASS);
- example.or().andIn("id", Arrays.asList(ids));
- organizationMapper.updateByExampleSelective(organization,example);
- }
+ } else {
+ throw new BusinessException("id of organization can't equal to it's parentId,organization:" + JSON.toJSONString(organization));
+ }
+ } catch (Exception ex) {
+ throw ex;
+ }
+ }
- }
- }
-
- @Override
- public List<Organization> getOrganizationsByName(String name) {
- ValidateUtil.notEmpty(name, "param.is.null");
- Example example = new Example(Organization.class);
- Criteria criteria = example.createCriteria();
+ @Transient
+ private void addOrModifyOrgRelation(Organization organization) {
+ OrganizationRelation relation = new OrganizationRelation();
+ relation.setChildId(organization.getId());
+ List<OrganizationRelation> relations = organizationRelationMapper.select(relation);
+ if (CollectionUtils.isEmpty(relations)) {
+ if (!ObjectUtils.isEmpty(organization.getParentId())) {
+ relation.setParentId(organization.getParentId());
+ organizationRelationMapper.insert(relation);
+ }
+ } else {
+ OrganizationRelation relationOfSelect = relations.get(0);
+ //���������������������������������������
+ if (organization.getParentId() != null &&
+ !organization.getParentId().equals(relationOfSelect.getParentId())
+ ) {
+ relationOfSelect.setParentId(organization.getParentId());
+ organizationRelationMapper.updateByPrimaryKey(relationOfSelect);
+ } else if (organization.getParentId() == null) {
+ //ParentI���null���������������������
+ organizationRelationMapper.deleteByPrimaryKey(relationOfSelect.getId());
+ }
+ //������������������
+ if (relations.size() > 1) {
+ relations.remove(0);
+ List<Integer> relationIds = relations.stream().map(
+ element -> {
+ return element.getId();
+ }
+ ).collect(Collectors.toList());
+ Example example = new Example(OrganizationRelation.class);
+ example.or().andIn("id", relationIds);
+ organizationRelationMapper.deleteByExample(example);
+ }
+ }
+ }
- criteria.andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andLike("name", "%" + name + "%");
- example.or().andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andCondition("getPY(name) like ", "%" + name + "%");
+ @Override
+ public void deleteByIds(Integer... ids) {
+ Organization organization = new Organization();
+ organization.setIsDelete(Constants.IS_DELETE_TRUE);
+ if (ids != null && ids.length > 0) {
+ if (ids.length == 1) {
+ organization.setId(ids[0]);
+ organizationMapper.updateByPrimaryKeySelective(organization);
+ } else {
+ Example example = new Example(ENTITY_CLASS);
+ example.or().andIn("id", Arrays.asList(ids));
+ organizationMapper.updateByExampleSelective(organization, example);
+ }
- List<Organization> organizations = organizationMapper.selectByExample(example);
- return organizations;
- }
+ }
+ }
- @Override
- public Organization getOrganizationById(int id) {
- Example example = new Example(ENTITY_CLASS);
- example.or().andEqualTo("id",id);
- List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);
- return organizationList.size()>0?organizationList.get(0):null;
- }
+ @Override
+ public List<Organization> getOrganizationsByName(String name) {
+ ValidateUtil.notEmpty(name, "param.is.null");
+ Example example = new Example(Organization.class);
+ Criteria criteria = example.createCriteria();
+
+ criteria.andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andLike("name", "%" + name + "%");
+ example.or().andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andCondition("getPY(name) like ", "%" + name + "%");
+
+ List<Organization> organizations = organizationMapper.selectByExample(example);
+ return organizations;
+ }
+
+ @Override
+ public Organization getOrganizationById(int id) {
+ Example example = new Example(ENTITY_CLASS);
+ example.or().andEqualTo("id", id);
+ List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);
+ return organizationList.size() > 0 ? organizationList.get(0) : null;
+ }
@Override
public Organization getFatherOrg(int childId) {
OrganizationRelation relation = new OrganizationRelation();
relation.setChildId(childId);
List<OrganizationRelation> relations = organizationRelationMapper.select(relation);
- if (!CollectionUtils.isEmpty(relations)){
+ if (!CollectionUtils.isEmpty(relations)) {
OrganizationRelation relationOfSelect = relations.get(0);
- if(relationOfSelect.getParentId()!=null){
+ if (relationOfSelect.getParentId() != null) {
return organizationMapper.selectByPrimaryKey(relationOfSelect.getParentId());
}
}
return null;
}
+
+ @Override
+ public List<Organization> getOrganizationList(String organizationName) {
+ List<Organization> organizationList = organizationMapper.getOrganizationList(organizationName);
+ return organizationList;
+ }
}
diff --git a/src/main/java/com/moral/service/impl/SensorServiceImpl.java b/src/main/java/com/moral/service/impl/SensorServiceImpl.java
index 77dca20..7d0c013 100644
--- a/src/main/java/com/moral/service/impl/SensorServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/SensorServiceImpl.java
@@ -1,27 +1,34 @@
package com.moral.service.impl;
-import com.moral.common.bean.PageBean;
-import com.moral.common.util.MyBatisBaseMapUtil;
-import com.moral.entity.Sensor;
-import com.moral.mapper.SensorMapper;
-import com.moral.service.SensorService;
-
-import org.springframework.stereotype.Service;
-
-import tk.mybatis.mapper.entity.Example;
-
-import javax.annotation.Resource;
-
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import javax.annotation.Resource;
+
+import org.apache.commons.collections.CollectionUtils;
+import org.springframework.stereotype.Service;
+
+import com.moral.common.bean.PageBean;
+import com.moral.common.util.MyBatisBaseMapUtil;
+import com.moral.entity.Device;
+import com.moral.entity.MonitorPoint;
+import com.moral.entity.Sensor;
+import com.moral.mapper.SensorMapper;
+import com.moral.service.MonitorPointService;
+import com.moral.service.SensorService;
+
+import tk.mybatis.mapper.entity.Example;
+
@Service
public class SensorServiceImpl implements SensorService {
@Resource
SensorMapper sensorMapper;
+
+ @Resource
+ MonitorPointService monitorPointService;
private static Class ENTITY_CLASS = Sensor.class;
@@ -142,4 +149,23 @@
return sensorsMapList;
}
+ @Override
+ public List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters) {
+ List<String> macList = new ArrayList<>();
+ List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsAndDevicesByRegion(parameters);
+ for (MonitorPoint m : monitorPoints) {
+ for (Device d : m.getDevices()) {
+ macList.add(d.getMac());
+ }
+ }
+ parameters.put("macs", macList);
+ List<Sensor> sensors = new ArrayList<>();
+ if (!CollectionUtils.isEmpty(macList)) {
+ sensors = sensorMapper.selectSenosrsByOrgId(parameters);
+ } else {
+ sensors = sensorMapper.selectAll();
+ }
+ return sensors;
+ }
+
}
diff --git a/src/main/java/com/moral/service/impl/SensorUnitServiceImpl.java b/src/main/java/com/moral/service/impl/SensorUnitServiceImpl.java
index 2d8b897..595a017 100644
--- a/src/main/java/com/moral/service/impl/SensorUnitServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/SensorUnitServiceImpl.java
@@ -1,31 +1,33 @@
package com.moral.service.impl;
-import com.moral.entity.SensorUnit;
-import com.moral.mapper.SensorUnitMapper;
-import com.moral.service.SensorService;
-import com.moral.service.SensorUnitService;
-import org.apache.log4j.Logger;
-import org.springframework.data.annotation.Transient;
-import org.springframework.stereotype.Service;
-
-import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import javax.annotation.Resource;
+
+import org.apache.log4j.Logger;
+import org.springframework.stereotype.Service;
+
+import com.moral.entity.SensorUnit;
+import com.moral.mapper.SensorUnitMapper;
+import com.moral.service.SensorService;
+import com.moral.service.SensorUnitService;
+
@Service
-public class SensorUnitServiceImpl implements SensorUnitService{
+public class SensorUnitServiceImpl implements SensorUnitService {
private static Logger log = Logger.getLogger(SensorService.class);
@Resource
private SensorUnitMapper sensorUnitMapper;
+
@Override
public boolean addOrModify(SensorUnit sensorUnit) {
try {
- if(sensorUnit.getId()!=null) {
+ if (sensorUnit.getId() != null) {
sensorUnit.setUpdateTime(new Date());
sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
- }else{
+ } else {
sensorUnit.setIsDelete(false);
sensorUnit.setCreateTime(new Date());
sensorUnitMapper.insertSelective(sensorUnit);
@@ -40,11 +42,12 @@
@Override
public List<SensorUnit> queryListBySensorId(int sensorId) {
- return queryListBySensorId(sensorId,false);
+ return queryListBySensorId(sensorId, false);
}
/**
* ���������������id ������ ���������������������
+ *
* @param sensorId
* @return
*/
@@ -53,85 +56,85 @@
SensorUnit sensorUnitQuery = new SensorUnit();
sensorUnitQuery.setSensorId(sensorId);
sensorUnitQuery.setIsDelete(isDelete);
- return sensorUnitMapper.select(sensorUnitQuery);
+ return sensorUnitMapper.select(sensorUnitQuery);
}
/**
* ���������������������������id ������
+ *
* @param id
*/
@Override
public void remove(Integer id) {
- SensorUnit sensorUnit = new SensorUnit();
- sensorUnit.setId(id);
- sensorUnit.setIsDelete(true);
- sensorUnit.setUpdateTime(new Date());
- sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
+ SensorUnit sensorUnit = new SensorUnit();
+ sensorUnit.setId(id);
+ sensorUnit.setIsDelete(true);
+ sensorUnit.setUpdateTime(new Date());
+ sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
}
/**
* ���������������������������������
+ *
* @param sensorIds
* @return
*/
@Override
- public Map<Integer,List<SensorUnit>> queryGroupSensorBySids(List<Integer> sensorIds){
+ public Map<Integer, List<SensorUnit>> queryGroupSensorBySids(List<Integer> sensorIds) {
SensorUnit sensorUnitQuery = new SensorUnit();
- Map<Integer,List<SensorUnit>> resultListMap = new HashMap<>();
- for(Integer sensorId: sensorIds) {
+ Map<Integer, List<SensorUnit>> resultListMap = new HashMap<>();
+ for (Integer sensorId : sensorIds) {
sensorUnitQuery.setSensorId(sensorId);
sensorUnitQuery.setIsDelete(false);
List<SensorUnit> sensorUnitList = sensorUnitMapper.select(sensorUnitQuery);
- if(sensorUnitList!=null&&sensorUnitList.size()>0){
- resultListMap.put(sensorId,sensorUnitList);
+ if (sensorUnitList != null && sensorUnitList.size() > 0) {
+ resultListMap.put(sensorId, sensorUnitList);
}
}
- return resultListMap;
+ return resultListMap;
}
- @Override
- public Map<String, Object> getSensorByMac(String mac) {
- List<Map<String, Object>> list = sensorUnitMapper.getSensorsByDeviceMac(mac);
- if (list!=null && !list.isEmpty()) {
- Map<String, Object> sensorUnitMap = new HashMap<>();
- for(Map<String, Object> sensorMap : list) {
- String sensor_key = (String) sensorMap.get("sensor_key");
- String name = (String) sensorMap.get("name");
- sensorUnitMap.put(sensor_key, name);
- }
- System.out.println(sensorUnitMap);
- return sensorUnitMap;
- } else {
- Map<String, Object> sensorUnitMap = null;
- return sensorUnitMap;
- }
- }
-
- @Override
- public Map<String, Map<String, Object>> getSensorsByMonitPointId(String id) {
- List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
- System.out.println(list);
- if (list!=null && !list.isEmpty()) {
- Map<String, Map<String, Object>> sensorUnitMap = new HashMap<>();
- for(Map<String, Object> sensorMap : list) {
- Map<String, Object> innerMap = new HashMap<>();
- innerMap.put("unit", sensorMap.get("unit"));
- innerMap.put("name", sensorMap.get("name"));
- innerMap.put("rules", sensorMap.get("rules"));
- String sensor_key = (String) sensorMap.get("sensor_key");
- sensorUnitMap.put(sensor_key, innerMap);
- }
- //System.out.println(sensorUnitMap);
- return sensorUnitMap;
- } else {
- Map<String, Map<String, Object>> sensorUnitMap = null;
- return sensorUnitMap;
- }
- }
+ @Override
+ public Map<String, Object> getSensorByMac(String mac) {
+ List<Map<String, Object>> list = sensorUnitMapper.getSensorsByDeviceMac(mac);
+ if (list != null && !list.isEmpty()) {
+ Map<String, Object> sensorUnitMap = new HashMap<>();
+ for (Map<String, Object> sensorMap : list) {
+ String sensor_key = (String) sensorMap.get("sensor_key");
+ String name = (String) sensorMap.get("name");
+ sensorUnitMap.put(sensor_key, name);
+ }
+ return sensorUnitMap;
+ } else {
+ Map<String, Object> sensorUnitMap = null;
+ return sensorUnitMap;
+ }
+ }
- @Override
- public List<Map<String, Object>> getSensorsByMonitPointId2(String id) {
- List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
- return list;
- }
+ @Override
+ public Map<String, Map<String, Object>> getSensorsByMonitPointId(String id) {
+ List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
+ if (list != null && !list.isEmpty()) {
+ Map<String, Map<String, Object>> sensorUnitMap = new HashMap<>();
+ for (Map<String, Object> sensorMap : list) {
+ Map<String, Object> innerMap = new HashMap<>();
+ innerMap.put("unit", sensorMap.get("unit"));
+ innerMap.put("name", sensorMap.get("name"));
+ innerMap.put("rules", sensorMap.get("rules"));
+ String sensor_key = (String) sensorMap.get("sensor_key");
+ sensorUnitMap.put(sensor_key, innerMap);
+ }
+ //System.out.println(sensorUnitMap);
+ return sensorUnitMap;
+ } else {
+ Map<String, Map<String, Object>> sensorUnitMap = null;
+ return sensorUnitMap;
+ }
+ }
+
+ @Override
+ public List<Map<String, Object>> getSensorsByMonitPointId2(String id) {
+ List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
+ return list;
+ }
}
diff --git a/src/main/resources/mapper/HistoryMinutelyMapper.xml b/src/main/resources/mapper/HistoryMinutelyMapper.xml
index 26ef457..933a5e7 100644
--- a/src/main/resources/mapper/HistoryMinutelyMapper.xml
+++ b/src/main/resources/mapper/HistoryMinutelyMapper.xml
@@ -213,4 +213,33 @@
order by rs.monitorPointName
</select>
+ <select id="getDevicesSensorsAvgDataToExcel" resultType="java.util.LinkedHashMap">
+ select
+ rs.monitorPointName,rs.name,
+ <foreach collection="timeList" separator="," item="time">
+ max(case time when #{time} then rs.json else "" end) as #{time}
+ </foreach>
+ from
+ (SELECT
+ m.name as monitorPointName,d.name,DATE_FORMAT(time, #{typeFormat}) time,h.json
+ FROM
+ history_${timeUnits} h,
+ device d,
+ monitor_point m
+ WHERE
+ h.mac=d.mac
+ AND d.monitor_point_id=m.id
+ AND h.time >= #{start}
+ AND h.time <![CDATA[<=]]> #{end}
+ AND h.mac in
+ <foreach collection="macs" open="(" separator="," close=")"
+ item="mac">
+ #{mac}
+ </foreach>
+ ORDER BY
+ h.mac) rs
+ GROUP BY rs.monitorPointName,rs.name
+ order by rs.monitorPointName
+ </select>
+
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/OrganizationMapper.xml b/src/main/resources/mapper/OrganizationMapper.xml
index 7865ab7..3450060 100644
--- a/src/main/resources/mapper/OrganizationMapper.xml
+++ b/src/main/resources/mapper/OrganizationMapper.xml
@@ -1,106 +1,117 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.OrganizationMapper">
- <resultMap id="BaseResultMap" type="com.moral.entity.Organization">
- <id column="id" jdbcType="INTEGER" property="id" />
- <result column="name" jdbcType="VARCHAR" property="name" />
- <result column="rank" jdbcType="INTEGER" property="rank" />
- <result column="province_code" jdbcType="INTEGER" property="provinceCode" />
- <result column="city_code" jdbcType="INTEGER" property="cityCode" />
- <result column="area_code" jdbcType="INTEGER" property="areaCode" />
- <result column="town_code" jdbcType="BIGINT" property="townCode" />
- <result column="village_code" jdbcType="BIGINT" property="villageCode" />
- <result column="address" jdbcType="VARCHAR" property="address" />
- <result column="telephone" jdbcType="VARCHAR" property="telephone" />
- <result column="email" jdbcType="VARCHAR" property="email" />
- <result column="is_delete" jdbcType="CHAR" property="isDelete" />
- <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
- <result column="expire_time" jdbcType="TIMESTAMP" property="expireTime" />
- <result column="description" jdbcType="VARCHAR" property="description" />
- <!-- ������������������������������ -->
- <association property="areaNames" javaType="com.moral.entity.AreaNames">
- <result column="province_name" property="provinceName" jdbcType="VARCHAR" />
- <result column="city_name" property="cityName" jdbcType="VARCHAR" />
- <result column="area_name" property="areaName" jdbcType="VARCHAR" />
- <result column="town_name" property="townName" jdbcType="VARCHAR" />
- <result column="village_name" property="villageName" jdbcType="VARCHAR" />
- </association>
- </resultMap>
- <sql id="Example_Where_Clause">
- <where>
- <foreach collection="oredCriteria" item="criteria" separator="or">
- <if test="criteria.valid">
- <trim prefix="(" prefixOverrides="and" suffix=")">
- <foreach collection="criteria.criteria" item="criterion">
- <choose>
- <when test="criterion.noValue">
- and ${criterion.condition}
- </when>
- <when test="criterion.singleValue">
- and ${criterion.condition} #{criterion.value}
- </when>
- <when test="criterion.betweenValue">
- and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
- </when>
- <when test="criterion.listValue">
- and ${criterion.condition}
- <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
+ <resultMap id="BaseResultMap" type="com.moral.entity.Organization">
+ <id column="id" jdbcType="INTEGER" property="id"/>
+ <result column="name" jdbcType="VARCHAR" property="name"/>
+ <result column="rank" jdbcType="INTEGER" property="rank"/>
+ <result column="province_code" jdbcType="INTEGER" property="provinceCode"/>
+ <result column="city_code" jdbcType="INTEGER" property="cityCode"/>
+ <result column="area_code" jdbcType="INTEGER" property="areaCode"/>
+ <result column="town_code" jdbcType="BIGINT" property="townCode"/>
+ <result column="village_code" jdbcType="BIGINT" property="villageCode"/>
+ <result column="address" jdbcType="VARCHAR" property="address"/>
+ <result column="telephone" jdbcType="VARCHAR" property="telephone"/>
+ <result column="email" jdbcType="VARCHAR" property="email"/>
+ <result column="is_delete" jdbcType="CHAR" property="isDelete"/>
+ <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
+ <result column="expire_time" jdbcType="TIMESTAMP" property="expireTime"/>
+ <result column="description" jdbcType="VARCHAR" property="description"/>
+ <!-- ������������������������������ -->
+ <association property="areaNames" javaType="com.moral.entity.AreaNames">
+ <result column="province_name" property="provinceName" jdbcType="VARCHAR"/>
+ <result column="city_name" property="cityName" jdbcType="VARCHAR"/>
+ <result column="area_name" property="areaName" jdbcType="VARCHAR"/>
+ <result column="town_name" property="townName" jdbcType="VARCHAR"/>
+ <result column="village_name" property="villageName" jdbcType="VARCHAR"/>
+ </association>
+ </resultMap>
+ <sql id="Example_Where_Clause">
+ <where>
+ <foreach collection="oredCriteria" item="criteria" separator="or">
+ <if test="criteria.valid">
+ <trim prefix="(" prefixOverrides="and" suffix=")">
+ <foreach collection="criteria.criteria" item="criterion">
+ <choose>
+ <when test="criterion.noValue">
+ and ${criterion.condition}
+ </when>
+ <when test="criterion.singleValue">
+ and ${criterion.condition} #{criterion.value}
+ </when>
+ <when test="criterion.betweenValue">
+ and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
+ </when>
+ <when test="criterion.listValue">
+ and ${criterion.condition}
+ <foreach close=")" collection="criterion.value" item="listItem" open="("
+ separator=",">
#{listItem}
</foreach>
- </when>
- </choose>
+ </when>
+ </choose>
+ </foreach>
+ </trim>
+ </if>
</foreach>
- </trim>
- </if>
- </foreach>
- </where>
- </sql>
- <sql id="Base_Column_List">
+ </where>
+ </sql>
+ <sql id="Base_Column_List">
id, name, rank, province_code, city_code, area_code, address, telephone, email, is_delete,
create_time, expire_time, description
</sql>
- <sql id="Relation_Province_City_Area_List">
+ <sql id="Relation_Province_City_Area_List">
are.area_name, cti.city_name, pro.province_name,tow.town_name,vil.village_name
</sql>
- <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
- select
- <if test="distinct">
+ <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
+ select
+ <if test="distinct">
distinct
</if>
- organization.*
- ,
- <include refid="Relation_Province_City_Area_List" />
- from organization
- left join village vil on organization.village_code = vil.village_code
- left join town tow on organization.town_code = tow.town_code
- left join area are on organization.area_code = are.area_code
- left join city cti on organization.city_code = cti.city_code
- left join province pro on organization.province_code = pro.province_code
- <if test="_parameter != null">
- <include refid="Example_Where_Clause" />
- </if>
- <if test="orderByClause != null">
+ organization.*
+ ,
+ <include refid="Relation_Province_City_Area_List"/>
+ from organization
+ left join village vil on organization.village_code = vil.village_code
+ left join town tow on organization.town_code = tow.town_code
+ left join area are on organization.area_code = are.area_code
+ left join city cti on organization.city_code = cti.city_code
+ left join province pro on organization.province_code = pro.province_code
+ <if test="_parameter != null">
+ <include refid="Example_Where_Clause"/>
+ </if>
+ <if test="orderByClause != null">
order by ${orderByClause}
</if>
- </select>
- <select id="getOrganizationsByAreaName" resultType="com.moral.entity.Organization">
- SELECT
- o.*
- FROM
- organization o,
- area a
- <if test="cityName != null">
+ </select>
+ <select id="getOrganizationsByAreaName" resultType="com.moral.entity.Organization">
+ SELECT
+ o.*
+ FROM
+ organization o,
+ area a
+ <if test="cityName != null">
,city c
</if>
- WHERE
- o.area_code = a.area_code
- AND a.area_name = #{areaName}
- <if test="cityName != null">
+ WHERE
+ o.area_code = a.area_code
+ AND a.area_name = #{areaName}
+ <if test="cityName != null">
AND c.city_name = #{cityName}
AND o.city_code = c.city_code
</if>
- </select>
+ </select>
<select id="selectLowerOrgIds" resultType="java.lang.Integer">
call proc_lowerOrgIds_GetByOrgId(#{orgId,jdbcType=INTEGER});
</select>
+
+ <select id="getOrganizationList" resultMap="BaseResultMap">
+ select
+ <include refid="Base_Column_List"/>
+ from organization
+ where is_delete=0
+ <if test="organizationName != 'null'">
+ and name like concat('%',#{organizationName},'%')
+ </if>
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/SensorMapper.xml b/src/main/resources/mapper/SensorMapper.xml
index b50c240..628c1b8 100644
--- a/src/main/resources/mapper/SensorMapper.xml
+++ b/src/main/resources/mapper/SensorMapper.xml
@@ -174,4 +174,28 @@
AND d.mac = #{mac}
</if>
</select>
+
+ <select id="selectSenosrsByOrgId" resultMap="BaseResultMap">
+ select
+ distinct dvs.sensor_id as id,
+ s.name,s.sensor_key,s.unit,s.description
+ from
+ device_version_sensor dvs,
+ sensor s
+ where
+ dvs.device_version_id
+ in(
+ select
+ distinct d.device_version_id
+ from
+ device d
+ where
+ d.mac in
+ <foreach collection="macs" open="(" separator="," close=")"
+ item="mac">
+ #{mac}
+ </foreach>
+ )
+ and dvs.sensor_id=s.id
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0