From 91c1c308abec43a67335cdebb574940ed41b09cf Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Wed, 05 Aug 2020 16:56:16 +0800
Subject: [PATCH] 修改预测参数，提交到数据库
---
src/main/java/com/moral/mapper/ForecastWeatherMapper.java | 2
src/main/resources/mapper/DeviceMapper.xml | 5
src/main/resources/mapper/RealWeatherMapper.xml | 11 +
src/main/resources/mapper/HangzhouAqiMapper.xml | 4
src/main/java/com/moral/mapper/MonitorPointMapper.java | 1
src/main/java/com/moral/mapper/RealWeatherMapper.java | 2
src/main/resources/mapper/ForecastWeatherMapper.xml | 14 +
src/main/resources/mapper/MonitorPointMapper.xml | 292 +++++++++++++++-------------
src/main/java/com/moral/mapper/HistoryHourlyMapper.java | 4
src/main/java/com/moral/mapper/DeviceMapper.java | 2
src/main/java/com/moral/service/WeatherService.java | 2
src/main/resources/mapper/HistoryHourlyMapper.xml | 17 +
src/main/java/com/moral/controller/ScreenController.java | 7
src/main/java/com/moral/service/impl/WeatherServiceImpl.java | 214 +++++++++++++++++---
14 files changed, 393 insertions(+), 184 deletions(-)
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index a0149a3..ca6b595 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -1990,8 +1990,9 @@
return new ResultBean<List<Map<String, Object>>>(weatherList);
}
- @RequestMapping("updateForecastWeather")
- public void updateForecastWeather(@RequestBody List<Map<String,Object>> list) {
- weatherService.updateForecastWeather(list);
+ @PostMapping("updateForecastWeather")
+ public void updateForecastWeather(HttpServletRequest request){
+ Map<String, Object> parameters = getParametersStartingWith(request, null);
+ weatherService.updateForecastWeather(parameters);
}
}
diff --git a/src/main/java/com/moral/mapper/DeviceMapper.java b/src/main/java/com/moral/mapper/DeviceMapper.java
index ed36184..b2bd249 100644
--- a/src/main/java/com/moral/mapper/DeviceMapper.java
+++ b/src/main/java/com/moral/mapper/DeviceMapper.java
@@ -54,4 +54,6 @@
List<Integer> getHasWindDirAndWindSpeedDeviceVersion();
List<Device> selectDevicesAll(Map<String, Object> params);
+
+ List<Device> getDeviceListByMonitorPointIds(List<Integer> id);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/mapper/ForecastWeatherMapper.java b/src/main/java/com/moral/mapper/ForecastWeatherMapper.java
index fcbb705..24d8611 100644
--- a/src/main/java/com/moral/mapper/ForecastWeatherMapper.java
+++ b/src/main/java/com/moral/mapper/ForecastWeatherMapper.java
@@ -10,4 +10,6 @@
List<Map<String, Object>> getForecast(Map<String, Object> parameters);
void updateForecastWeather(Map<String, Object> parameters);
+
+ Map<String, Object> getPrecip6Hour(Map<String, Object> parameters);
}
diff --git a/src/main/java/com/moral/mapper/HistoryHourlyMapper.java b/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
index 6c05d9a..e968bf7 100644
--- a/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryHourlyMapper.java
@@ -2,6 +2,7 @@
import org.apache.ibatis.annotations.Param;
+import java.util.List;
import java.util.Map;
public interface HistoryHourlyMapper {
@@ -17,4 +18,7 @@
String getPressureByMac(@Param("mac") String mac,@Param("time") String time);
//������������
Map<String,Object> getDataByMac(@Param("mac") String mac,@Param("time") String time);
+
+ List<Map<String,Object>> getBeamByMacs(Map<String,Object> params);
+
}
diff --git a/src/main/java/com/moral/mapper/MonitorPointMapper.java b/src/main/java/com/moral/mapper/MonitorPointMapper.java
index 5023af1..7689907 100644
--- a/src/main/java/com/moral/mapper/MonitorPointMapper.java
+++ b/src/main/java/com/moral/mapper/MonitorPointMapper.java
@@ -31,4 +31,5 @@
MonitorPoint getFirstMonitorPointByProvinceCode(@Param("provinceCode") int provinceCode);
+ List<MonitorPoint> getMonitorList(Map<String, Object> params);
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/mapper/RealWeatherMapper.java b/src/main/java/com/moral/mapper/RealWeatherMapper.java
index b8a4f87..2554ade 100644
--- a/src/main/java/com/moral/mapper/RealWeatherMapper.java
+++ b/src/main/java/com/moral/mapper/RealWeatherMapper.java
@@ -6,4 +6,6 @@
public interface RealWeatherMapper {
List<Map<String,Object>> getRealWeather(Map<String, Object> parameters);
+
+ Map<String, Object> getPrecip6Hour(Map<String, Object> parameters);
}
diff --git a/src/main/java/com/moral/service/WeatherService.java b/src/main/java/com/moral/service/WeatherService.java
index 08b6d6e..27c570f 100644
--- a/src/main/java/com/moral/service/WeatherService.java
+++ b/src/main/java/com/moral/service/WeatherService.java
@@ -14,5 +14,5 @@
List<Map<String, Object>> getWeatherData(Map<String, Object> parameters) throws ParseException;
- void updateForecastWeather(List<Map<String,Object>> list);
+ void updateForecastWeather(Map<String, Object> parameters);
}
diff --git a/src/main/java/com/moral/service/impl/WeatherServiceImpl.java b/src/main/java/com/moral/service/impl/WeatherServiceImpl.java
index 62d3544..1b326bd 100644
--- a/src/main/java/com/moral/service/impl/WeatherServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/WeatherServiceImpl.java
@@ -24,13 +24,16 @@
import com.moral.entity.Area;
import com.moral.entity.AreaNames;
import com.moral.entity.City;
+import com.moral.entity.Device;
import com.moral.entity.MonitorPoint;
import com.moral.entity.Organization;
import com.moral.entity.Province;
import com.moral.mapper.AreaMapper;
import com.moral.mapper.CityMapper;
+import com.moral.mapper.DeviceMapper;
import com.moral.mapper.ForecastWeatherMapper;
import com.moral.mapper.HangzhouAqiMapper;
+import com.moral.mapper.HistoryHourlyMapper;
import com.moral.mapper.MonitorPointMapper;
import com.moral.mapper.ProvinceMapper;
import com.moral.mapper.RealWeatherMapper;
@@ -63,6 +66,12 @@
@Resource
private RealWeatherMapper realWeatherMapper;
+
+ @Resource
+ private DeviceMapper deviceMapper;
+
+ @Resource
+ private HistoryHourlyMapper historyHourlyMapper;
@Override
public Map<String, Object> getWeatherDataByRegion(Map<String, Object> parameters) {
@@ -179,7 +188,6 @@
parameters.put("start", sdf.parse(time));
parameters.put("end", sdf.parse(nextDay));
List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecastHour(parameters);
-
MonitorPoint monitorPoint = monitorPointMapper.selectByPrimaryKey(Integer.valueOf(monitorPointId));
parameters.put("areaCode", monitorPoint.getAreaCode());
String sensors = "O3C";
@@ -192,7 +200,6 @@
parameters.put("areaCode", monitorPoint.getCityCode());
aqiList = hangzhouAqiMapper.getAqiDataByAreaCode(parameters);
}
-
for (Map<String, Object> map : aqiList) {
String O3 = map.get("O3").toString().replace("\"", "");
map.put("O3", O3);
@@ -225,7 +232,11 @@
hour = "24";
}
map.put("time", Integer.valueOf(hour));
- values.add(forecastMap.get("result").toString().replace("\"", ""));
+ if (forecastMap.get("result") != null) {
+ values.add(forecastMap.get("result").toString().replace("\"", ""));
+ } else {
+ values.add("");
+ }
values.add("");
map.put("values", values);
resultList.add(map);
@@ -239,7 +250,11 @@
hour = "24";
}
map.put("time", Integer.valueOf(hour));
- values.add(forecastMap.get("result").toString().replace("\"", ""));
+ if (forecastMap.get("result") != null) {
+ values.add(forecastMap.get("result").toString().replace("\"", ""));
+ } else {
+ values.add("");
+ }
for (Map<String, Object> aqiMap : aqiList) {
if (forecastMap.get("time").equals(aqiMap.get("time"))) {
values.add(aqiMap.get(sensor1).toString());
@@ -292,6 +307,14 @@
parameters.put("monitorPointId", monitorPoint.getId());
String time = parameters.get("time").toString();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ Date now = new Date();
+ Date selectTime = sdf1.parse(time + " 00:00:00");
+ Calendar c1 = Calendar.getInstance();
+ c1.setTime(selectTime);
+ c1.add(Calendar.DAY_OF_MONTH, -1);
+ Date beforeDay = c1.getTime();
+ String format = sdf.format(beforeDay);
Calendar c = Calendar.getInstance();
c.setTime(sdf.parse(time));
c.add(Calendar.HOUR_OF_DAY, 1);
@@ -301,17 +324,55 @@
parameters.put("start", start);
parameters.put("end", end);
List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecast(parameters);
-
List<Map<String, Object>> foreList = new ArrayList<>();
-
for (Map<String, Object> map : forecastList) {
Map<String, Object> hashMap = new HashMap<>();
hashMap.put("time", map.get("time").toString());
hashMap.put("type", "������");
+ hashMap.put("TVOC", "");
+ hashMap.put("altitude", "");
+ hashMap.put("vegetation", "");
+ hashMap.put("AQI", "");
+ hashMap.put("SO2C", "");
+ hashMap.put("NO2C", "");
+ hashMap.put("COC", "");
+ hashMap.put("O3C", "");
+ hashMap.put("PM25C", "");
+ hashMap.put("PM10C", "");
hashMap.put("city", parameters.get("city").toString());
+
+ if (selectTime.getTime() <= now.getTime()) {
+ hashMap.put("fxTime", (format + " 15:00").substring(5, 16));
+ } else {
+ String s = sdf1.format(now).split(" ")[1].split(":")[0];
+ Integer hour = Integer.valueOf(s);
+ if (hour > 7) {
+ hashMap.put("fxTime", (format + " 07:00").substring(5, 16));
+ } else if (hour > 3 && hour < 7) {
+ hashMap.put("fxTime", (format + " 15:00").substring(5, 16));
+ }
+ }
Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(map.get("json").toString());
hashMap.putAll(jsonMap);
foreList.add(hashMap);
+ }
+
+ for (Map<String, Object> foreMap : foreList) {
+ String ftime = foreMap.get("time").toString();
+ Calendar calendar = Calendar.getInstance();
+ Date endTime = sdf1.parse(ftime);
+ calendar.setTime(endTime);
+ calendar.add(Calendar.HOUR, -6);
+ Date startTime = calendar.getTime();
+ Map<String, Object> hashMap = new HashMap<>();
+ hashMap.put("areaCode", Integer.valueOf(parameters.get("areaCode").toString()));
+ hashMap.put("start", startTime);
+ hashMap.put("end", endTime);
+ hashMap.put("monitorPointId", Integer.valueOf(parameters.get("monitorPointId").toString()));
+ Map<String, Object> precip6HourMap = forecastWeatherMapper.getPrecip6Hour(hashMap);
+ if (precip6HourMap != null) {
+ foreMap.put("precip6", precip6HourMap.get("precip6").toString());
+ }
}
List<Map<String, Object>> aqiList = hangzhouAqiMapper.getAqi(parameters);
@@ -330,8 +391,6 @@
realAqilist.add(hashMap);
}
- SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
-
for (Map<String, Object> map : realAqilist) {
Date endTime = sdf1.parse(map.get("time").toString());
c.setTime(endTime);
@@ -342,10 +401,40 @@
hashMap.put("start", startTime);
hashMap.put("end", endTime);
Map<String, Object> O3Map = hangzhouAqiMapper.getAvgO3EightHours(hashMap);
- map.put("O3������8������", O3Map.get("O3������8������").toString());
- if (O3Map.get("O3������8������") != null) {
- map.put("O3������8������", O3Map.get("O3������8������").toString());
+ map.put("O3_8H", O3Map.get("O3_8H").toString());
+ if (O3Map.get("O3C_8H") != null) {
+ map.put("O3C_8H", O3Map.get("O3C_8H").toString());
}
+ }
+
+ //������
+ String code = parameters.get("areaCode").toString();
+ Map<String, Object> params = new HashMap<>();
+ if (code.endsWith("00")) {
+ params.put("cityCode", Integer.valueOf(code));
+ } else {
+ params.put("areaCode", Integer.valueOf(code));
+ }
+
+ List<MonitorPoint> monitorList = monitorPointMapper.getMonitorList(params);
+
+ List<Integer> pointList = new ArrayList<>();
+ for (MonitorPoint point : monitorList) {
+ Integer pointId = point.getId();
+ pointList.add(pointId);
+ }
+
+ List<Device> deviceList = deviceMapper.getDeviceListByMonitorPointIds(pointList);
+ List<String> macList = new ArrayList<>();
+ List<Map<String, Object>> beamList = new ArrayList<>();
+ if (deviceList.size() != 0) {
+ for (Device device : deviceList) {
+ String mac = device.getMac();
+ macList.add(mac);
+ }
+
+ parameters.put("macs", macList);
+ beamList = historyHourlyMapper.getBeamByMacs(parameters);
}
List<Map<String, Object>> realList = realWeatherMapper.getRealWeather(parameters);
@@ -357,6 +446,28 @@
hashMap.putAll(jsonMap);
hashMap.put("city", parameters.get("city").toString());
realWeatherList.add(hashMap);
+ }
+
+ for (Map<String, Object> realMap : realWeatherList) {
+ String rtime = realMap.get("time").toString();
+ for (Map<String, Object> beamMap : beamList) {
+ String btime = realMap.get("time").toString();
+ if (btime.equals(rtime)) {
+ realMap.put("beam", beamMap.get("beam").toString());
+ }
+ }
+ Calendar calendar = Calendar.getInstance();
+ Date endTime = sdf1.parse(rtime);
+ calendar.setTime(endTime);
+ calendar.add(Calendar.HOUR, -6);
+ Date startTime = calendar.getTime();
+ Map<String, Object> hashMap = new HashMap<>();
+ hashMap.put("areaCode", Integer.valueOf(parameters.get("areaCode").toString()));
+ hashMap.put("start", startTime);
+ hashMap.put("end", endTime);
+ hashMap.put("monitorPointId", Integer.valueOf(parameters.get("monitorPointId").toString()));
+ Map<String, Object> precip6HourMap = realWeatherMapper.getPrecip6Hour(hashMap);
+ realMap.put("precip6", precip6HourMap.get("precip6").toString());
}
List<Map<String, Object>> list = new ArrayList<>();
@@ -393,13 +504,18 @@
}
}
}
+
+
list.removeAll(Collections.singleton(null));
+
+
for (Map<String, Object> map : list) {
map.put("type", "������");
map.put("city", parameters.get("city").toString());
}
List<Map<String, Object>> resultList = new ArrayList<>();
+
for (Map<String, Object> foreMap : foreList) {
for (Map<String, Object> realMap : list) {
if (foreMap.get("time").equals(realMap.get("time"))) {
@@ -418,39 +534,65 @@
hashMap.put("city", parameters.get("city").toString());
resultList.add(hashMap);
}
+
+ for (Map<String, Object> map : resultList) {
+ String date = map.get("time").toString().substring(0, 16);
+ map.put("time", date);
+ }
return resultList;
}
@Override
- public void updateForecastWeather(List<Map<String, Object>> list) {
- for (Map<String, Object> map : list) {
- String time = map.get("time").toString();
- Map<String, Object> parameters = new HashMap<>();
- parameters.put("time", time);
- Integer monitorPointId;
- String city = map.get("city").toString();
- Area area = areaMapper.getAreaByName(city);
- if (area == null) {
- Integer cityCode = cityMapper.getCityByName(city).getCityCode();
- monitorPointId = monitorPointMapper.getFirstMonitorPointByCityCode(cityCode).getId();
- } else {
- Integer areaCode = area.getAreaCode();
- monitorPointId = monitorPointMapper.getFirstMonitorPointByAreaCode(areaCode).getId();
- }
- parameters.put("monitorPointId", monitorPointId);
-
- Map<String, Object> jsonMap = new HashMap<>();
- for (String key : map.keySet()) {
- if (!key.equals("city") && !key.equals("time") && !key.equals("type")) {
- String value = map.get(key).toString();
- jsonMap.put(key, value);
+ public void updateForecastWeather(Map<String, Object> parameters) {
+ List<Integer> list = new ArrayList<>();
+ List<Map<String, Object>> resultList = new ArrayList<>();
+ for (int i = 0; i < 48; i++) {
+ list.add(i);
+ resultList.add(null);
+ }
+ for (Integer s : list) {
+ Map<String, Object> map = new HashMap<>();
+ for (String key : parameters.keySet()) {
+ String replace = key.split("\\[")[0];
+ if (replace.equals(s + "")) {
+ String ss = key.split("\\[")[1].replace("]", "");
+ map.put(ss, parameters.get(key).toString());
}
}
+ resultList.set(s, map);
+ }
+ for (Map<String, Object> map : resultList) {
+ if (map.get("type").equals("������")) {
+ String time = map.get("time").toString() + ":00";
+ Map<String, Object> hashMap = new HashMap<>();
+ hashMap.put("time", time);
+ String city = map.get("city").toString();
+ Area area = areaMapper.getAreaByName(city);
+ Map<String, Object> idMap = new HashMap<>();
+ if (area == null) {
+ Integer cityCode = cityMapper.getCityByName(city).getCityCode();
+ idMap.put("cityCode", cityCode);
+ } else {
+ Integer areaCode = area.getAreaCode();
+ idMap.put("areaCode", areaCode);
+ }
- String json = JSONObject.toJSONString(jsonMap);
- parameters.put("json", json);
+ List<MonitorPoint> monitorList = monitorPointMapper.getMonitorList(idMap);
+ Map<String, Object> jsonMap = new HashMap<>();
+ for (String key : map.keySet()) {
+ if (!key.equals("city") && !key.equals("time") && !key.equals("type") && !key.equals("fxTime")) {
+ String value = map.get(key).toString();
+ jsonMap.put(key, value);
+ }
+ }
- forecastWeatherMapper.updateForecastWeather(parameters);
+ String json = JSONObject.toJSONString(jsonMap);
+ hashMap.put("json", json);
+ for (MonitorPoint monitorPoint : monitorList) {
+ hashMap.put("monitorPointId", monitorPoint.getId());
+ forecastWeatherMapper.updateForecastWeather(hashMap);
+ }
+ }
}
}
}
diff --git a/src/main/resources/mapper/DeviceMapper.xml b/src/main/resources/mapper/DeviceMapper.xml
index d2c0b74..6e1dd54 100644
--- a/src/main/resources/mapper/DeviceMapper.xml
+++ b/src/main/resources/mapper/DeviceMapper.xml
@@ -481,5 +481,8 @@
</where>
</select>
-
+ <select id="getDeviceListByMonitorPointIds" resultType="com.moral.entity.Device">
+ select * from device where monitor_point_id in
+ <foreach item="item" collection="list" index="index" open="(" separator="," close=")">#{item}</foreach>
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/ForecastWeatherMapper.xml b/src/main/resources/mapper/ForecastWeatherMapper.xml
index 8c084fc..2e04576 100644
--- a/src/main/resources/mapper/ForecastWeatherMapper.xml
+++ b/src/main/resources/mapper/ForecastWeatherMapper.xml
@@ -7,7 +7,7 @@
<select id="getForecastHour" resultType="java.util.Map">
SELECT
DATE_FORMAT(time, #{typeFormat}) time,
- (case when result is null then json->'$.tem' else result end) result
+ (case when result is null then json->'$.temp' else result end) result
from forecast_weather
where time >= #{start}
AND time <![CDATA[<]]> #{end}
@@ -30,4 +30,16 @@
where monitor_point_id=#{monitorPointId}
and time=#{time}
</update>
+
+
+ <select id="getPrecip6Hour" resultType="java.util.Map">
+ select
+ round(sum(json->'$.precip'),2) 'precip6'
+ FROM
+ forecast_weather
+ WHERE
+ time >= #{start}
+ AND time <![CDATA[<]]> #{end}
+ AND monitor_point_id = #{monitorPointId}
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/HangzhouAqiMapper.xml b/src/main/resources/mapper/HangzhouAqiMapper.xml
index 753ed7f..3b0bbb0 100644
--- a/src/main/resources/mapper/HangzhouAqiMapper.xml
+++ b/src/main/resources/mapper/HangzhouAqiMapper.xml
@@ -98,8 +98,8 @@
<select id="getAvgO3EightHours" resultType="java.util.Map">
select
- AVG(aqi_json->'$.O3') 'O3������8������',
- AVG(aqi_json->'$.O3C') 'O3������8������'
+ AVG(aqi_json->'$.O3') 'O3_8H',
+ AVG(aqi_json->'$.O3C') 'O3C_8H'
FROM
hangzhou_aqi
WHERE
diff --git a/src/main/resources/mapper/HistoryHourlyMapper.xml b/src/main/resources/mapper/HistoryHourlyMapper.xml
index b428cba..aa83d96 100644
--- a/src/main/resources/mapper/HistoryHourlyMapper.xml
+++ b/src/main/resources/mapper/HistoryHourlyMapper.xml
@@ -56,10 +56,25 @@
<select id="getTVOCByMac" resultType="java.lang.String">
select json->'$.${sensor}[0]' as e17 from history_hourly where mac=#{mac} and time=#{time}
</select>
+
<select id="getPressureByMac" resultType="java.lang.String">
select json->'$.e28[0]' as e28 from history_hourly where mac=#{mac} and time=#{time}
</select>
+
<select id="getDataByMac" resultType="java.util.Map">
- select json from history_hourly where mac=#{mac} and time=#{time}
+ select json from history_hourly where mac=#{mac} and time=#{time}
+ </select>
+
+ <select id="getBeamByMacs" resultType="java.util.Map">
+ SELECT
+ DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s') time,
+ avg(h.json->'$.e12[0]') beam
+ FROM history_hourly h
+ WHERE h.mac in
+ <foreach item="item" collection="macs" index="index" open="(" separator="," close=")">#{item}</foreach>
+ and h.time >= #{start}
+ AND h.time <![CDATA[<]]> #{end}
+ and h.json->'$.e12[0]' is not null
+ group by DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s')
</select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/MonitorPointMapper.xml b/src/main/resources/mapper/MonitorPointMapper.xml
index 941d1b8..5e2d6da 100644
--- a/src/main/resources/mapper/MonitorPointMapper.xml
+++ b/src/main/resources/mapper/MonitorPointMapper.xml
@@ -1,186 +1,200 @@
<?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.MonitorPointMapper">
- <resultMap id="BaseResultMap" type="com.moral.entity.MonitorPoint">
- <id column="id" jdbcType="INTEGER" property="id" />
- <result column="name" jdbcType="VARCHAR" property="name" />
- <result column="longitude" jdbcType="REAL" property="longitude" />
- <result column="latitude" jdbcType="REAL" property="latitude" />
- <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="organization_id" jdbcType="INTEGER" property="organizationId" />
- <result column="address" jdbcType="VARCHAR" property="address" />
- <result column="is_delete" jdbcType="CHAR" property="isDelete" />
- <result column="description" jdbcType="VARCHAR" property="description" />
- <result column="state" jdbcType="INTEGER" property="state" />
- <!-- ������������������������������ -->
- <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>
- <association property="organization" javaType="com.moral.entity.Organization">
- <result column="organization_id" property="id" jdbcType="INTEGER" />
- <result column="organization_name" property="name" 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>
- </foreach>
- </trim>
- </if>
- </foreach>
- </where>
- </sql>
- <sql id="Base_Column_List">
+ <resultMap id="BaseResultMap" type="com.moral.entity.MonitorPoint">
+ <id column="id" jdbcType="INTEGER" property="id"/>
+ <result column="name" jdbcType="VARCHAR" property="name"/>
+ <result column="longitude" jdbcType="REAL" property="longitude"/>
+ <result column="latitude" jdbcType="REAL" property="latitude"/>
+ <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="organization_id" jdbcType="INTEGER" property="organizationId"/>
+ <result column="address" jdbcType="VARCHAR" property="address"/>
+ <result column="is_delete" jdbcType="CHAR" property="isDelete"/>
+ <result column="description" jdbcType="VARCHAR" property="description"/>
+ <result column="state" jdbcType="INTEGER" property="state"/>
+ <!-- ������������������������������ -->
+ <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>
+ <association property="organization" javaType="com.moral.entity.Organization">
+ <result column="organization_id" property="id" jdbcType="INTEGER"/>
+ <result column="organization_name" property="name" 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>
+ </foreach>
+ </trim>
+ </if>
+ </foreach>
+ </where>
+ </sql>
+ <sql id="Base_Column_List">
id, name, longitude, latitude, province_code, city_code, area_code, organization_id,
address, is_delete, 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, t.town_name, v.village_name
</sql>
- <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
- select
- <if test="distinct">
- distinct
- </if>
- mpt.* ,
- org.name as organization_name ,
- <include refid="Relation_Province_City_Area_List" />
- from monitor_point mpt
- left join area are on mpt.area_code = are.area_code
- left join city cti on mpt.city_code = cti.city_code
- left join province pro on mpt.province_code = pro.province_code
- left join organization org on mpt.organization_id = org.id
- left join town t on mpt.town_code = t.town_code
- left join village v on mpt.village_code = v.village_code
+ <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
+ select
+ <if test="distinct">
+ distinct
+ </if>
+ mpt.* ,
+ org.name as organization_name ,
+ <include refid="Relation_Province_City_Area_List"/>
+ from monitor_point mpt
+ left join area are on mpt.area_code = are.area_code
+ left join city cti on mpt.city_code = cti.city_code
+ left join province pro on mpt.province_code = pro.province_code
+ left join organization org on mpt.organization_id = org.id
+ left join town t on mpt.town_code = t.town_code
+ left join village v on mpt.village_code = v.village_code
where mpt.id in (
- select id from monitor_point
- <if test="_parameter != null">
- <include refid="Example_Where_Clause" />
- </if>
- <if test="orderByClause != null">
- order by ${orderByClause}
- </if>
- )
- </select>
- <select id="getMonitorPointsByAreaName" resultMap="BaseResultMap">
- SELECT
- mp.*
- FROM
- monitor_point mp,
- area a
- <if test="cityName != null">
- ,city c
- </if>
- WHERE
- mp.area_code = a.area_code
- AND a.area_name = #{areaName}
- <if test="cityName != null">
- AND c.city_name = #{cityName}
- AND mp.city_code = c.city_code
- </if>
- </select>
- <select id="selectByMap" parameterType="java.util.Map" resultMap="BaseResultMap">
- SELECT mpt.* from monitor_point mpt
- <where>
- <if test="@com.moral.common.bean.Constants@isNotSpecialOrgId(orgId)">
- (
- 1 > 1
- <if test="orgIds!=null and orgIds.size() > 0">
- or mpt.organization_id in
- <foreach collection="orgIds" item="tempOrgId" open="(" close=")" separator=",">
- #{tempOrgId,jdbcType=INTEGER}
- </foreach>
- </if>
- )
- </if>
- <![CDATA[
+ select id from monitor_point
+ <if test="_parameter != null">
+ <include refid="Example_Where_Clause"/>
+ </if>
+ <if test="orderByClause != null">
+ order by ${orderByClause}
+ </if>
+ )
+ </select>
+ <select id="getMonitorPointsByAreaName" resultMap="BaseResultMap">
+ SELECT
+ mp.*
+ FROM
+ monitor_point mp,
+ area a
+ <if test="cityName != null">
+ ,city c
+ </if>
+ WHERE
+ mp.area_code = a.area_code
+ AND a.area_name = #{areaName}
+ <if test="cityName != null">
+ AND c.city_name = #{cityName}
+ AND mp.city_code = c.city_code
+ </if>
+ </select>
+ <select id="selectByMap" parameterType="java.util.Map" resultMap="BaseResultMap">
+ SELECT mpt.* from monitor_point mpt
+ <where>
+ <if test="@com.moral.common.bean.Constants@isNotSpecialOrgId(orgId)">
+ (
+ 1 > 1
+ <if test="orgIds!=null and orgIds.size() > 0">
+ or mpt.organization_id in
+ <foreach collection="orgIds" item="tempOrgId" open="(" close=")" separator=",">
+ #{tempOrgId,jdbcType=INTEGER}
+ </foreach>
+ </if>
+ )
+ </if>
+ <![CDATA[
AND mpt.longitude < #{mapBounds.Ge,jdbcType=NUMERIC}
AND mpt.longitude > #{mapBounds.Le,jdbcType=NUMERIC}
AND mpt.latitude < #{mapBounds.Fe,jdbcType=NUMERIC}
AND mpt.latitude > #{mapBounds.Ke,jdbcType=NUMERIC}
]]>
- <if test="regionCode != null">
- AND mpt.${regionType}_code = #{regionCode}
- </if>
-
- <if test="isDelete != null">
- AND mpt.is_delete = #{isDelete,jdbcType=VARCHAR}
- </if>
- </where>
- </select>
- <select id="selectOrganizationIds" parameterType="integer" resultType="integer">
+ <if test="regionCode != null">
+ AND mpt.${regionType}_code = #{regionCode}
+ </if>
+
+ <if test="isDelete != null">
+ AND mpt.is_delete = #{isDelete,jdbcType=VARCHAR}
+ </if>
+ </where>
+ </select>
+ <select id="selectOrganizationIds" parameterType="integer" resultType="integer">
call proc_organizationIds_GetByMonitorPointId(#{id,jdbcType=NUMERIC})
</select>
- <select id="selectVersionsById" parameterType="integer" resultType="integer">
+ <select id="selectVersionsById" parameterType="integer" resultType="integer">
select version from device_version dve
right join device dev on dve.id = dev.device_version_id
right join monitor_point mpt on mpt.id = dev.monitor_point_id
where mpt.id = #{id} ORDER BY version DESC
</select>
- <select id="selectAllById" resultType="java.util.Map">
+ <select id="selectAllById" resultType="java.util.Map">
SELECT *
FROM monitor_point
WHERE id = #{id}
</select>
- <select id="getIdByOrganizationId" resultType="java.util.Map">
- SELECT id
- FROM monitor_point
- WHERE organization_id IN
- <foreach collection="organizationIdList" index="index" item="organizationId" open="(" separator="," close=")">
- #{organizationId}
- </foreach>
- </select>
+ <select id="getIdByOrganizationId" resultType="java.util.Map">
+ SELECT id
+ FROM monitor_point
+ WHERE organization_id IN
+ <foreach collection="organizationIdList" index="index" item="organizationId" open="(" separator="," close=")">
+ #{organizationId}
+ </foreach>
+ </select>
- <select id="getDeviceList" resultType="com.moral.entity.Device">
+ <select id="getDeviceList" resultType="com.moral.entity.Device">
SELECT d.* FROM `device` as d,monitor_point as mp
where d.monitor_point_id=mp.id
and mp.id=#{id}
</select>
- <select id="getMonitorPointById" resultType="com.moral.entity.MonitorPoint">
+ <select id="getMonitorPointById" resultType="com.moral.entity.MonitorPoint">
select * from monitor_point where id=#{id}
</select>
- <select id="getMonitorPointListByAccountId" resultType="com.moral.entity.MonitorPoint">
+ <select id="getMonitorPointListByAccountId" resultType="com.moral.entity.MonitorPoint">
SELECT mp.* FROM account a,`monitor_point` mp,organization o
where a.organization_id=o.id
and o.id=mp.organization_id
and a.id=#{id};
</select>
- <select id="getFirstMonitorPointByAreaCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
+ <select id="getFirstMonitorPointByAreaCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
select * from monitor_point where area_code=#{areaCode} limit 0,1
</select>
- <select id="getFirstMonitorPointByCityCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
+ <select id="getFirstMonitorPointByCityCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
select * from monitor_point where city_code=#{cityCode} limit 0,1
</select>
- <select id="getFirstMonitorPointByProvinceCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
+ <select id="getFirstMonitorPointByProvinceCode" resultType="com.moral.entity.MonitorPoint"
+ resultMap="BaseResultMap">
select * from monitor_point where province_code=#{provinceCode} limit 0,1
</select>
+
+ <select id="getMonitorList" resultMap="BaseResultMap">
+ select * from monitor_point
+ where
+ <if test="areaCode != null">
+ area_Code = #{areaCode}
+ </if>
+
+ <if test="cityCode != null">
+ city_Code = #{cityCode}
+ </if>
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/RealWeatherMapper.xml b/src/main/resources/mapper/RealWeatherMapper.xml
index ad86d99..80934c3 100644
--- a/src/main/resources/mapper/RealWeatherMapper.xml
+++ b/src/main/resources/mapper/RealWeatherMapper.xml
@@ -17,4 +17,15 @@
ORDER BY
time
</select>
+
+ <select id="getPrecip6Hour" resultType="java.util.Map">
+ select
+ round(sum(json->'$.precip'),2) 'precip6'
+ FROM
+ real_weather
+ WHERE
+ time >= #{start}
+ AND time <![CDATA[<]]> #{end}
+ AND monitor_point_id = #{monitorPointId}
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0