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"))) { 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") 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); } 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 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; } 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; 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; } } 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"); 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> 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>
