From 66fd957202742202804a614c766b9b973a26d836 Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Thu, 07 May 2020 17:15:04 +0800
Subject: [PATCH] update
---
src/main/java/com/moral/service/HistoryMinutelyService.java | 2
src/main/java/com/moral/mapper/HistoryMinutelyMapper.java | 2
src/main/resources/mapper/HistoryMinutelyMapper.xml | 16 --
src/main/java/com/moral/controller/ReportController.java | 89 ++++++++----
src/main/resources/mapper/HangzhouAqiMapper.xml | 72 ++++++----
src/main/java/com/moral/service/HangzhouAqiService.java | 3
src/main/java/com/moral/controller/ScreenController.java | 110 +++++----------
src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java | 57 ++++++++
src/main/java/com/moral/mapper/HangzhouAqiMapper.java | 1
src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java | 10 -
10 files changed, 199 insertions(+), 163 deletions(-)
diff --git a/src/main/java/com/moral/controller/ReportController.java b/src/main/java/com/moral/controller/ReportController.java
index 5d90395..d3c7e6c 100644
--- a/src/main/java/com/moral/controller/ReportController.java
+++ b/src/main/java/com/moral/controller/ReportController.java
@@ -4,6 +4,8 @@
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
+import java.util.Collections;
+import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@@ -99,23 +101,13 @@
@GetMapping("newExcel")
public ResultBean<Boolean> getNExcelReport(HttpServletRequest request, HttpServletResponse response) throws Exception {
Map<String, Object> parameters = getParametersStartingWith(request, null);
- List<Map<String, Object>> list = historyMinutelyService.getMonitorPointOrDeviceAvgData(parameters);
- ArrayList<String> sensorKeys = (ArrayList<String>) parameters.get("sensorKeys");
- for (Map<String, Object> map : list) {
- for (String sensorKey : sensorKeys) {
- if (map.get(sensorKey) == null) {
- map.put(sensorKey, "");
- }
- }
- }
-
String type = parameters.get("type").toString();
String time = parameters.get("time").toString();
- String timeb = "";
+ String timeb;
if (parameters.get("timeb") == null) {
- timeb = parameters.get("time").toString();
- } else {
- timeb = parameters.get("timeb").toString();
+ timeb = time;
+ }else {
+ timeb=parameters.get("timeb").toString();
}
String[] endTimes = timeb.split("-");
@@ -123,21 +115,12 @@
String beginTime = "";
String endTime = "";
List<String> monthList1 = new ArrayList<>();
- monthList1.add("01");
- monthList1.add("03");
- monthList1.add("05");
- monthList1.add("07");
- monthList1.add("08");
- monthList1.add("10");
- monthList1.add("12");
+ Collections.addAll(monthList1,"01","03","05","07","08","10","12");
List<String> monthList2 = new ArrayList<>();
- monthList2.add("04");
- monthList2.add("06");
- monthList2.add("09");
- monthList2.add("11");
+ Collections.addAll(monthList2,"04","06","09","11");
+
Integer year = Integer.valueOf(endTimes[0]);
int i = 0;
-
if (type.equals("year")) {
dateFormat = "yyyy-MM";
beginTime = time + "-01";
@@ -163,11 +146,11 @@
beginTime = time + " 00";
endTime = timeb + " 23";
i = Calendar.HOUR_OF_DAY;
- } else if (type.equals("hour")) {
- dateFormat = "yyyy-MM-dd HH:mm";
- beginTime = time + ":00:00";
- endTime = timeb + ":59:00";
- i = Calendar.MINUTE;
+ }else if(type.equals("hour")){
+ dateFormat="yyyy-MM-dd HH:mm";
+ beginTime=time+":00:00";
+ endTime=timeb+":59:00";
+ i=Calendar.MINUTE;
}
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
@@ -182,6 +165,50 @@
resultList.add(map);
}
+ if(type.equals("year")){
+ SimpleDateFormat sd = new SimpleDateFormat("yyyy");
+ Date dt = sd.parse(timeb);
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(dt);
+ calendar.add(Calendar.YEAR, 1);
+ Date dt1 = calendar.getTime();
+ timeb = sd.format(dt1);
+ }else if(type.equals("month")){
+ SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM");
+ Date dt = sd.parse(timeb);
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(dt);
+ calendar.add(Calendar.MONTH, 1);
+ Date dt1 = calendar.getTime();
+ timeb = sd.format(dt1);
+ }else if(type.equals("day")){
+ SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
+ Date dt = sd.parse(timeb);
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(dt);
+ calendar.add(Calendar.DAY_OF_MONTH, 1);
+ Date dt1 = calendar.getTime();
+ timeb = sd.format(dt1);
+ }else if(type.equals("hour")){
+ SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH");
+ Date dt = sd.parse(timeb);
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(dt);
+ calendar.add(Calendar.HOUR_OF_DAY, 1);
+ Date dt1 = calendar.getTime();
+ timeb = sd.format(dt1);
+ }
+ parameters.put("timeb",timeb);
+ List<Map<String, Object>> list = historyMinutelyService.getMonitorPointOrDeviceAvgData(parameters);
+ ArrayList<String> sensorKeys = (ArrayList<String>)parameters.get("sensorKeys");
+ for (Map<String, Object> map : list) {
+ for (String sensorKey : sensorKeys) {
+ if(map.get(sensorKey)==null){
+ map.put(sensorKey,"");
+ }
+ }
+ }
+
for (Map<String, Object> map : resultList) {
for (Map<String, Object> resultMap : list) {
if (resultMap.get("time").equals(map.get("time"))) {
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index fea81f7..63ba21f 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -17,7 +17,6 @@
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
-import org.apache.commons.lang.StringUtils;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.log4j.Logger;
import org.dom4j.Document;
@@ -839,45 +838,15 @@
parameters.put("monitorPointId", parameters.remove("monitorPoint"));
parameters.put("areaCode", parameters.remove("areaCode"));
String[] sensorKeys = parameters.remove("sensorKey").toString().split(",");
+ String type = parameters.get("type").toString();
String s = sensorKeys[0];
parameters.put("sensors", Arrays.asList(sensorKeys));
parameters.put("sensors2", s);
- List<Map<String, Object>> monitorList = historyMinutelyService.getMonitorPointOrDeviceAvgData(parameters);
- String type = parameters.get("type").toString();
- ArrayList<Map<String, Object>> resultList = new ArrayList<>();
- String sensorKey1 = s;
- switch (s) {
- case "e1":
- sensorKey1 = "PM2_5";
- break;
- case "e2":
- sensorKey1 = "PM10";
- break;
- case "e10":
- sensorKey1 = "CO";
- break;
- case "e11":
- sensorKey1 = "SO2";
- break;
- case "e15":
- sensorKey1 = "O3";
- break;
- case "e16":
- sensorKey1 = "NO2";
- break;
- default:
- break;
- }
- parameters.put("sensors1", sensorKey1);
- List<Map<String, Object>> areaList = historyMinutelyService.getAreaAvgDataByAreaCode(parameters);
- if (areaList.isEmpty()) {
- Integer areaCode = Integer.valueOf(request.getParameter("areaCode"));
- areaCode = hangzhouAqiService.queryCityCode(areaCode);
- parameters.remove("areaCode");
- parameters.put("areaCode", String.valueOf(areaCode));
- areaList = historyMinutelyService.getAreaAvgDataByAreaCode(parameters);
- }
+ ArrayList<Map<String, Object>> resultList = new ArrayList<>();
+
+ List<Map<String, Object>> monitorList = historyMinutelyService.getMonitorPointOrDeviceAvgData(parameters);
+ List<Map<String, Object>> areaList = hangzhouAqiService.getAreaAvgDataByAreaCode(parameters);
if (areaList.isEmpty() && monitorList.isEmpty()) {
return new ResultBean<List<Map<String, Object>>>(resultList);
@@ -911,7 +880,6 @@
String time = map.get("time").toString();
time = time.substring(time.length() - 2);
map.put("time", Integer.valueOf(time));
-
}
for (Map<String, Object> m : monitorList) {
@@ -920,7 +888,7 @@
m.put("time", Integer.valueOf(time));
}
- Integer maxTime = 0;
+ Integer maxTime;
Map<String, Object> areaMap = areaList.get(areaList.size() - 1);
Integer areaMax = Integer.valueOf(areaMap.get("time").toString());
Map<String, Object> monitorMap = monitorList.get(monitorList.size() - 1);
@@ -951,59 +919,53 @@
} else {
resultList.set(time - 1, map);
}
-
}
- if (type.equals("day")) {
- for (int i = 0; i < resultList.size(); i++) {
- if (resultList.get(i) == null) {
- Map<String, Object> hashMap = new HashMap<>();
+ for (int i = 0; i <resultList.size() ; i++) {
+ if (resultList.get(i) == null) {
+ Map<String, Object> hashMap = new HashMap<>();
+ if(type.equals("day")){
hashMap.put("time", i);
- hashMap.put(s, "");
- resultList.set(i, hashMap);
- }
- }
- } else {
- for (int i = 0; i < resultList.size(); i++) {
- if (resultList.get(i) == null) {
- Map<String, Object> hashMap = new HashMap<>();
+ }else {
hashMap.put("time", i + 1);
- hashMap.put(s, "");
- resultList.set(i, hashMap);
}
+ hashMap.put(s, "");
+ resultList.set(i, hashMap);
}
}
- for (Map<String, Object> map : resultList) {
- List<Object> values = new ArrayList<>();
- for (Map<String, Object> map1 : monitorList) {
- if (map.get("time").toString().equals(map1.get("time").toString())) {
- values.add(map.remove(s));
- values.add(map1.remove(s));
- map.put("values", values);
- }
+ for (Map<String, Object> map1 : monitorList) {
+ ArrayList<String> values = new ArrayList<>();
+ Integer time = Integer.valueOf(map1.get("time").toString());
+ Map<String, Object> map;
+ if (type.equals("day")) {
+ map = resultList.get(time);
+ } else {
+ map = resultList.get(time - 1);
}
+ values.add(map.remove(s).toString());
+ values.add(map1.get(s).toString());
+ map.put("values", values);
}
for (Map<String, Object> map : resultList) {
- List<Object> values = new ArrayList<>();
if (map.containsKey(s)) {
- values.add(map.remove(s));
+ ArrayList<String> values = new ArrayList<>();
+ values.add(map.remove(s).toString());
values.add("");
- map.put("values", values);
- }
- }
- List<Map<String, Object>> mapList = new ArrayList<>();
- for (Map<String, Object> map : resultList) {
- String values = map.get("values").toString();
- String string = StringUtils.strip(values, "[]");
- String[] strings = string.split(",");
- if (!strings[0].equals("") || !strings[1].equals(" ")) {
- mapList.add(map);
+ map.put("values",values);
}
}
- return new ResultBean<List<Map<String, Object>>>(mapList);
+ Iterator<Map<String, Object>> it = resultList.iterator();
+ while(it.hasNext()){
+ Map<String, Object> next = it.next();
+ ArrayList<String> values = (ArrayList<String>)next.get("values");
+ if(values.get(0).equals("")&&values.get(1).equals("")){
+ it.remove();
+ }
+ }
+ return new ResultBean<List<Map<String, Object>>>(resultList);
}
@GetMapping("getmpoint-byid")
diff --git a/src/main/java/com/moral/mapper/HangzhouAqiMapper.java b/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
index 76cdbcf..9a61b07 100644
--- a/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
+++ b/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
@@ -16,5 +16,6 @@
Integer queryCityCode(@Param("areaCode") Integer areaCode);
+ List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters);
}
diff --git a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
index ca530b5..ecb0b2d 100644
--- a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
@@ -16,8 +16,6 @@
List<Map<String, Object>> getMonitorPointOrDeviceAvgData(Map<String, Object> parameters);
- List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters);
-
/**
* @param sensorKeys
* @param timePeriod
diff --git a/src/main/java/com/moral/service/HangzhouAqiService.java b/src/main/java/com/moral/service/HangzhouAqiService.java
index 041bdc6..eca4008 100644
--- a/src/main/java/com/moral/service/HangzhouAqiService.java
+++ b/src/main/java/com/moral/service/HangzhouAqiService.java
@@ -13,4 +13,7 @@
public List<Map<String, Object>> queryAqi24Hours(@NotNull Integer orgId);
public Integer queryCityCode(@NotNull Integer areaCode);
+
+ List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters) throws Exception;
+
}
diff --git a/src/main/java/com/moral/service/HistoryMinutelyService.java b/src/main/java/com/moral/service/HistoryMinutelyService.java
index 7ce8b6f..45b8517 100644
--- a/src/main/java/com/moral/service/HistoryMinutelyService.java
+++ b/src/main/java/com/moral/service/HistoryMinutelyService.java
@@ -21,8 +21,6 @@
Map<String, Object> getAverageBySensor(Map<String, Object> parameters);
- List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters) throws Exception;
-
Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);
void convertQueryParam(Map<String, Object> parameters) throws ParseException;
diff --git a/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java b/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
index f3feac4..445693c 100644
--- a/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
@@ -18,6 +18,7 @@
import com.moral.mapper.HangzhouAqiMapper;
import com.moral.mapper.OrganizationMapper;
import com.moral.service.HangzhouAqiService;
+import com.moral.service.HistoryMinutelyService;
/**
* @Auther: fengxiang
@@ -30,6 +31,9 @@
private OrganizationMapper organizationMapper;
@Resource
private HangzhouAqiMapper hangzhouAqiMapper;
+
+ @Resource
+ private HistoryMinutelyService historyMinutelyService;
public List<Map> queryAqiOfTimePeriod(String code, TimePeriod timePeriod) {
return hangzhouAqiMapper.selectAqisByCodeAndTimePeriod(code, timePeriod);
@@ -109,4 +113,57 @@
public Integer queryCityCode(Integer areaCode) {
return hangzhouAqiMapper.queryCityCode(areaCode);
}
+
+ @Override
+ public List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters) throws Exception {
+ historyMinutelyService.convertQueryParam(parameters);
+ if (!ObjectUtils.isEmpty(parameters.get("compensate"))) {
+ parameters.put("timeUnits", "10min");
+ }
+
+ String sensors2 = parameters.get("sensors2").toString();
+
+ String sensors1 = sensors2;
+ String sensors="";
+ switch (sensors2) {
+ case "e1":
+ sensors1 = "PM2_5";
+ sensors="PM25C";
+ break;
+ case "e2":
+ sensors1 = "PM10";
+ sensors="PM10C";
+ break;
+ case "e10":
+ sensors1 = "CO";
+ sensors="COC";
+ break;
+ case "e11":
+ sensors1 = "SO2";
+ sensors="SO2C";
+ break;
+ case "e15":
+ sensors1 = "O3";
+ sensors="O3C";
+ break;
+ case "e16":
+ sensors1 = "NO2";
+ sensors="NO2C";
+ break;
+ default:
+ break;
+ }
+
+ parameters.put("sensors1", sensors1);
+ parameters.put("sensors",sensors);
+ List<Map<String, Object>> areaList = hangzhouAqiMapper.getAreaAvgDataByAreaCode(parameters);
+ if (areaList.isEmpty()) {
+ Integer areaCode = Integer.valueOf(parameters.get("areaCode").toString());
+ areaCode = hangzhouAqiMapper.queryCityCode(areaCode);
+ parameters.put("areaCode", String.valueOf(areaCode));
+ areaList = hangzhouAqiMapper.getAreaAvgDataByAreaCode(parameters);
+ }
+ return areaList;
+
+ }
}
diff --git a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
index f435eee..5d0c824 100644
--- a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
@@ -361,16 +361,6 @@
}
@Override
- public List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters) throws Exception {
- convertQueryParam(parameters);
- if (!ObjectUtils.isEmpty(parameters.get("compensate"))) {
- parameters.put("timeUnits", "10min");
- }
- return historyMinutelyMapper.getAreaAvgDataByAreaCode(parameters);
-
- }
-
- @Override
public void convertQueryParam(Map<String, Object> parameters) throws ParseException {
if (!parameters.containsKey("field")) {
// String type = (String) parameters.get("type");
diff --git a/src/main/resources/mapper/HangzhouAqiMapper.xml b/src/main/resources/mapper/HangzhouAqiMapper.xml
index 81fe13b..26e4898 100644
--- a/src/main/resources/mapper/HangzhouAqiMapper.xml
+++ b/src/main/resources/mapper/HangzhouAqiMapper.xml
@@ -1,21 +1,21 @@
<?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.HangzhouAqiMapper">
- <resultMap id="BaseResultMap" type="com.moral.entity.HangzhouAqi">
- <id column="time" property="time" jdbcType="TIMESTAMP"/>
- <result column="aqi_json" property="aqiJson" jdbcType="OTHER"/>
- <result column="city_code" property="cityCode" jdbcType="VARCHAR"/>
- </resultMap>
- <sql id="Base_Column_List">
+ <resultMap id="BaseResultMap" type="com.moral.entity.HangzhouAqi">
+ <id column="time" property="time" jdbcType="TIMESTAMP"/>
+ <result column="aqi_json" property="aqiJson" jdbcType="OTHER"/>
+ <result column="city_code" property="cityCode" jdbcType="VARCHAR"/>
+ </resultMap>
+ <sql id="Base_Column_List">
time, aqi_json, city_code
</sql>
- <!--<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.util.Date" >-->
- <!--select-->
- <!--<include refid="Base_Column_List" />-->
- <!--from hangzhou_aqi-->
- <!--where time = #{time,jdbcType=TIMESTAMP}-->
- <!--</select>-->
- <select id="selectAqisByCodeAndTimePeriod" resultType="map" parameterType="java.util.Date">
+ <!--<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.util.Date" >-->
+ <!--select-->
+ <!--<include refid="Base_Column_List" />-->
+ <!--from hangzhou_aqi-->
+ <!--where time = #{time,jdbcType=TIMESTAMP}-->
+ <!--</select>-->
+ <select id="selectAqisByCodeAndTimePeriod" resultType="map" parameterType="java.util.Date">
select
aqi_json->'$.AQI' as aqi,DATE_FORMAT(time,'${@com.moral.common.util.ReportTimeFormat@toMySqlTimeFormat(timePeriod.timeUnits)}') as 'time'
from hangzhou_aqi
@@ -33,22 +33,38 @@
order by time asc
</select>
- <select id="getAqisByOrganizationId" resultType="map">
- SELECT
- h.aqi_json ->> '$.AQI' * 1 aqi,
- DATE_FORMAT( h.time, '%H' ) * 1 time
- FROM
- hangzhou_aqi h
- <where>
- h.city_code = #{cityCode}
- AND h.time <![CDATA[<]]> #{end}
- </where>
- ORDER BY
- h.time DESC
- LIMIT 24
- </select>
+ <select id="getAqisByOrganizationId" resultType="map">
+ SELECT
+ h.aqi_json ->> '$.AQI' * 1 aqi,
+ DATE_FORMAT( h.time, '%H' ) * 1 time
+ FROM
+ hangzhou_aqi h
+ <where>
+ h.city_code = #{cityCode}
+ AND h.time <![CDATA[<]]> #{end}
+ </where>
+ ORDER BY
+ h.time DESC
+ LIMIT 24
+ </select>
- <select id="queryCityCode" resultType="integer">
+ <select id="queryCityCode" resultType="integer">
select city_code from area where area_code = #{areaCode}
</select>
+
+ <select id="getAreaAvgDataByAreaCode" resultType="java.util.Map">
+ SELECT
+ DATE_FORMAT(time, #{typeFormat}) time,
+ round(AVG(case when aqi_json->'$.${sensors}' is null then aqi_json->'$.${sensors1}' else aqi_json->'$.${sensors}' end),4) as '${sensors2}'
+ FROM
+ hangzhou_aqi ha
+ WHERE
+ ha.time >= #{start}
+ AND ha.time <![CDATA[<]]> #{end}
+ AND ha.city_code = #{areaCode}
+ GROUP BY
+ DATE_FORMAT(time, #{typeFormat})
+ ORDER BY
+ time
+ </select>
</mapper>
diff --git a/src/main/resources/mapper/HistoryMinutelyMapper.xml b/src/main/resources/mapper/HistoryMinutelyMapper.xml
index 3f4c176..c202150 100644
--- a/src/main/resources/mapper/HistoryMinutelyMapper.xml
+++ b/src/main/resources/mapper/HistoryMinutelyMapper.xml
@@ -104,22 +104,6 @@
time
</select>
- <select id="getAreaAvgDataByAreaCode" resultType="java.util.Map">
- SELECT
- DATE_FORMAT(time, #{typeFormat}) time,
- AVG(aqi_json->'$.${sensors1}') AS '${sensors2}'
- FROM
- hangzhou_aqi ha
- WHERE
- ha.time >= #{start}
- AND ha.time <![CDATA[<]]> #{end}
- AND ha.city_code = #{areaCode}
- GROUP BY
- DATE_FORMAT(time, #{typeFormat})
- ORDER BY
- time
- </select>
-
<select id="selectLineChartDateByCrieria" resultType="java.util.Map">
SELECT
<choose>
--
Gitblit v1.8.0