获取城市天气，aqi更新

jinpengyong

2020-09-15 d6cb70708e6bb9e18cbc2fbc0034f3dd998fcd38

获取城市天气，aqi更新

 src/main/java/com/moral/mapper/DeviceMapper.java

@@ -68,7 +68,7 @@
    List<DeviceAndWind> byMonitorIdGetDeviceAndWind(@Param("id") int id,@Param("time") String time, @Param("table") String table);

    DeviceAndWind byMacGetDeviceAndWind(@Param("mac") String mac,@Param("time") String time, @Param("table") String table);



    List<Device> getDeviceListByMonitorPointIds(List<Integer> list);

    List<String> getMacsByMonitorPointIds(List<Integer> list);



    Map<String, Object> selectAllFieldByMac(Map<String, Object> params);




 src/main/java/com/moral/mapper/HangzhouAqiMapper.java

@@ -20,9 +20,15 @@

    List<Map<String, Object>> getAqiDataByAreaCode(Map<String, Object> parameters);

    List<Map<String, Object>> getHistoryO3(Map<String, Object> parameters);

    List<Map<String, Object>> getAqi(Map<String, Object> parameters);

    List<Map<String, Object>> getHistoryAqi(Map<String, Object> parameters);

    Map<String, Object> getAvgO3EightHours(Map<String, Object> parameters);

    Map<String, Object> getHistoryO3EightHours (Map<String, Object> parameters);

    Map<String, Object> getSumO3(Map<String, Object> parameters);
}

 src/main/java/com/moral/mapper/MonitorPointMapper.java

@@ -4,27 +4,38 @@
import java.util.Map;



import com.moral.entity.Device;



import org.apache.ibatis.annotations.Param;



import com.moral.common.mapper.BaseMapper;

import com.moral.entity.MonitorPoint;



import tk.mybatis.mapper.entity.Example;



public interface MonitorPointMapper extends BaseMapper<MonitorPoint>{

    List<MonitorPoint> selectWithAreaNameByExample(Example example);

    List<MonitorPoint> getMonitorPointsByAreaName(Map<String, Object> parameters);

public interface MonitorPointMapper extends BaseMapper<MonitorPoint> {

    List<MonitorPoint> selectWithAreaNameByExample(Example example);



    List<MonitorPoint> getMonitorPointsByAreaName(Map<String, Object> parameters);



    List<MonitorPoint> selectByMap(Map<String, Object> params);



    List<Integer> selectOrganizationIds(int id);



    List<Integer> selectVersionsById(int id);



    Map<String, Object> selectAllById(int id);



    List<Map<String, Object>> getIdByOrganizationId(@Param("organizationIdList") List<Object> organizationIdList);



    //通过monitorPointId获取该公司区域的所有设备

    List<Device> getDeviceList(@Param("id") int id);



    //获取公司信息，通过id

    MonitorPoint getMonitorPointById(@Param("id") int id);



    List<MonitorPoint> getMonitorPointListByAccountId(@Param("id") int id);

    MonitorPoint byIdGetMonitorPoint(@Param("id")int id);



    MonitorPoint byIdGetMonitorPoint(@Param("id") int id);



    MonitorPoint getFirstMonitorPointByAreaCode(@Param("areaCode") int areaCode);



@@ -32,5 +43,5 @@


    MonitorPoint getFirstMonitorPointByProvinceCode(@Param("provinceCode") int provinceCode);



    List<MonitorPoint>  getMonitorList(Map<String, Object> params);

    List<Integer> getMonitorPointIds(Map<String, Object> params);

}

 src/main/java/com/moral/mapper/RealWeatherMapper.java

@@ -7,5 +7,7 @@

    List<Map<String,Object>> getRealWeather(Map<String, Object> parameters);

    List<Map<String,Object>> getHistoryWeather(Map<String, Object> parameters);

    Map<String, Object> getPrecip6Hour(Map<String, Object> parameters);
}

 src/main/java/com/moral/mapper/ShAreaMapper.java

@@ -7,6 +7,6 @@

public interface ShAreaMapper extends BaseMapper<Area> {

    Map<String,Object> getgeography(String name);
    Map<String,Object> getGeography(String name);

}

 src/main/java/com/moral/service/WeatherService.java

@@ -17,4 +17,5 @@
    List<Map<String, Object>> getWeatherDataByDay(Map<String, Object> parameters) throws ParseException;



    void updateForecastWeather(Map<String, Object> parameters);



}


 src/main/java/com/moral/service/impl/WeatherServiceImpl.java

@@ -205,10 +205,20 @@
        List<Map<String, Object>> aqiList = hangzhouAqiMapper.getAqiDataByAreaCode(parameters);

        List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecastHour(parameters);



        if (aqiList.size() == 0) {

            parameters.put("cityCode", monitorPoint.getCityCode());

            aqiList = hangzhouAqiMapper.getAqiDataByAreaCode(parameters);

        String timeUnits=time.substring(5,7);

        if(aqiList.size() == 0){

            parameters.put("timeUnits", timeUnits);

            aqiList = hangzhouAqiMapper.getHistoryO3(parameters);

            if (aqiList.size() == 0) {

                parameters.put("cityCode", monitorPoint.getCityCode());

                aqiList = hangzhouAqiMapper.getAqiDataByAreaCode(parameters);

                if(aqiList.size() == 0){

                    parameters.put("cityCode", monitorPoint.getAreaCode());

                    aqiList = hangzhouAqiMapper.getHistoryO3(parameters);

                }

            }

        }



        for (Map<String, Object> map : aqiList) {

            String O3 = map.get("O3").toString().replace("\"", "");

            map.put("O3", O3);

@@ -283,482 +293,6 @@
    }



    @Override

    public List<Map<String, Object>> getWeatherData(Map<String, Object> parameters) throws ParseException {

        parameters.put("typeFormat", "%Y-%m-%d %H:%i:%s");

        if (parameters.get("city") == null) {

            return new ArrayList<Map<String, Object>>();

        }

        String cityName = parameters.get("city").toString();

        Area area = areaMapper.getAreaByName(cityName);

        Integer code;

        String name;

        if (area == null) {

            City city = cityMapper.getCityByName(cityName);

            if (city == null) {

                Province province = provinceMapper.getProvinceByName(cityName);

                code = province.getProvinceCode();

                name = province.getProvinceName();

            } else {

                code = city.getCityCode();

                name = city.getCityName();

            }

        } else {

            code = area.getAreaCode();

            name = area.getAreaName();

        }



        parameters.put("cityCode", code);

        parameters.put("name", name);

        String time = parameters.get("time").toString();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH");

        Date now = new Date();

        Date selectTime = sdf1.parse(time + " 01:00:00");

        Calendar cal = Calendar.getInstance();

        cal.setTime(selectTime);

        cal.add(Calendar.DAY_OF_MONTH, 1);

        Date end = cal.getTime();

        cal.setTime(sdf.parse(time));

        cal.add(Calendar.DAY_OF_MONTH, -1);

        String beforeDay = sdf.format(cal.getTime());

        parameters.put("start", selectTime);

        parameters.put("end", end);

        Integer fcode = 0;

        List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecast(parameters);



        if (forecastList.size() == 0) {

            List<Area> areaList = areaMapper.getAreaByCityCode(code);

            for (Area area1 : areaList) {

                if (area1 != null) {

                    parameters.put("cityCode", area1.getAreaCode());

                    fcode = area1.getAreaCode();

                    forecastList = forecastWeatherMapper.getForecast(parameters);

                    if (forecastList.size() != 0) {

                        break;

                    }

                }

            }

        } else {

            fcode = Integer.valueOf(parameters.get("cityCode").toString());

        }



        List<Map<String, Object>> foreList = new ArrayList<>();

        for (Map<String, Object> forecastMap : forecastList) {

            Map<String, Object> hashMap = new HashMap<>();

            String ftime = forecastMap.get("time").toString();

            hashMap.put("time", ftime);

            hashMap.put("type", "预测");

            hashMap.put("TVOC", "");

            hashMap.put("altitude", "");

            hashMap.put("vegetation", "");

            hashMap.put("river", "");

            hashMap.put("AQI", "");

            hashMap.put("SO2C", "");

            hashMap.put("NO2C", "");

            hashMap.put("COC", "");

            if (forecastMap.get("O3C") == null) {

                hashMap.put("O3C", "");

            }

            hashMap.put("PM25C", "");

            hashMap.put("PM10C", "");

            hashMap.put("city", parameters.get("name").toString());

            hashMap.put("code", fcode);

            if (sdf.parse(time).getTime() <= now.getTime()) {

                hashMap.put("fxTime", (beforeDay + " 15:00").substring(5, 16));

            } else {

                String format = sdf1.format(now).split(" ")[0];

                Date date1 = sdf1.parse(format + " 07:00:00");

                Date date2 = sdf1.parse(format + " 15:00:00");

                if (now.getTime() > date2.getTime()) {

                    hashMap.put("fxTime", (beforeDay + " 15:00").substring(5, 16));

                } else if (now.getTime() > date1.getTime() && (now.getTime() < date2.getTime() || now.getTime() == date2.getTime())) {

                    hashMap.put("fxTime", (beforeDay + " 07:00").substring(5, 16));

                }

            }

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(forecastMap.get("json").toString());

            hashMap.putAll(jsonMap);

            foreList.add(hashMap);

        }



        //预测6小时降水量

        for (Map<String, Object> foreMap : foreList) {

            String ftime = foreMap.get("time").toString();

            Date endTime = sdf1.parse(ftime);

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -6);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

            Map<String, Object> precip6HourMap = forecastWeatherMapper.getPrecip6Hour(hashMap);

            foreMap.put("precip6", "");

            if (precip6HourMap != null) {

                foreMap.put("precip6", precip6HourMap.get("precip6").toString());

            }

        }



        //预测O3C_8H

        for (Map<String, Object> map : foreList) {

            Date endTime = sdf1.parse(map.get("time").toString());

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -8);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            Map<String, Object> O3Map = forecastWeatherMapper.getO38Hours(hashMap);

            if (O3Map != null) {

                Double O3C_8H = Double.valueOf(O3Map.get("O3C_8H").toString());

                map.put("O3C_8H", String.valueOf(Math.round(O3C_8H)));

            }

        }



        String ac = parameters.get("cityCode").toString();

        if(name.equals("沧州市")){

            ac="130900";

        }

        List<Map<String, Object>> aqiList = hangzhouAqiMapper.getAqi(parameters);

        if (aqiList.size() == 0) {

            parameters.put("cityCode", code);

            aqiList = hangzhouAqiMapper.getAqi(parameters);

            if (aqiList.size() == 0) {

                List<Area> areaList = areaMapper.getAreaByCityCode(code);

                for (Area area1 : areaList) {

                    if (area1 != null) {

                        parameters.put("cityCode", area1.getAreaCode());

                        aqiList = hangzhouAqiMapper.getAqi(parameters);

                        if (aqiList.size() != 0) {

                            break;

                        }

                    }

                }

            }

        }

        DecimalFormat df = new DecimalFormat(".####");

        List<Map<String, Object>> realAqilist = new ArrayList<>();

        for (Map<String, Object> aqiMap : aqiList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", aqiMap.get("time"));

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(aqiMap.get("json").toString());

            hashMap.putAll(jsonMap);

            hashMap.put("city", parameters.get("name").toString());

            realAqilist.add(hashMap);

        }



        for (Map<String, Object> map : realAqilist) {

            String rtime = map.get("time").toString();

            Date endTime = sdf1.parse(rtime);

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -8);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            Map<String, Object> O3Map = hangzhouAqiMapper.getAvgO3EightHours(hashMap);

            map.put("O3C_8H", O3Map.get("O3C_8H").toString());

        }

        //光照

        Map<String, Object> params = new HashMap<>();

        if (ac.endsWith("00")) {

            params.put("cityCode", Integer.valueOf(ac));

        } else {

            params.put("areaCode", Integer.valueOf(ac));

        }



        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<String> macList1 = new ArrayList<>();

        List<Map<String, Object>> beamList = new ArrayList<>();

        List<Map<String, Object>> tvocList = new ArrayList<>();

        if (deviceList.size() != 0) {

            for (Device device : deviceList) {

                macList.add(device.getMac());

            }

            parameters.put("macs", macList);

            beamList = historyHourlyMapper.getBeamByMacs(parameters);

            Map<String, Object> map = new HashMap<>();

            if (name.equals("沧州市")) {

                List<Integer> list = new ArrayList<>();

                list.add(48);

                List<Device> pointIds = deviceMapper.getDeviceListByMonitorPointIds(list);

                for (Device device : pointIds) {

                    if (device.getId() != 1075) {

                        macList1.add(device.getMac());

                    }

                }

            } else {

                macList1 = macList;

            }

            map.put("macs", macList1);

            map.put("start", parameters.get("start"));

            map.put("end", parameters.get("end"));

            tvocList = historyHourlyMapper.getTVOCByMacs(map);

        }

        List<Map<String, Object>> realList = realWeatherMapper.getRealWeather(parameters);

        List<Map<String, Object>> realWeatherList = new ArrayList<>();

        for (Map<String, Object> realMap : realList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", realMap.get("time"));

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(realMap.get("json").toString());

            hashMap.putAll(jsonMap);

            hashMap.put("city", parameters.get("name").toString());

            realWeatherList.add(hashMap);

        }



        for (Map<String, Object> realMap : realWeatherList) {

            String rtime = realMap.get("time").toString();

            for (Map<String, Object> beamMap : beamList) {

                String btime = beamMap.get("time").toString();

                if (btime.equals(rtime)) {

                    realMap.put("beam", Math.floor((Double) beamMap.get("beam")));

                }

            }



            Date endTime = sdf1.parse(rtime);

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -6);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);



            Map<String, Object> precip6HourMap = realWeatherMapper.getPrecip6Hour(hashMap);



            if (precip6HourMap != null) {

                realMap.put("precip6", precip6HourMap.get("precip6").toString());

            }



            for (Map<String, Object> tvocMap : tvocList) {

                String ttime = tvocMap.get("time").toString();

                if (ttime.equals(rtime)) {

                    Double tvoc = Double.valueOf(tvocMap.get("TVOC").toString());

                    BigDecimal bd = new BigDecimal(tvoc);

                    tvoc = bd.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();

                    realMap.put("TVOC", tvoc);

                }

            }



        }



        List<Map<String, Object>> list = new ArrayList<>();

        for (int i = 0; i < 24; i++) {

            list.add(null);

        }



        for (Map<String, Object> map : realWeatherList) {

            Integer t1 = Integer.valueOf(map.get("time").toString().split(" ")[1].split(":")[0]);

            if (t1 == 0) {

                list.set(list.size() - 1, map);

            } else {

                list.set(t1 - 1, map);

            }

        }



        for (Map<String, Object> map : realAqilist) {

            Integer t1 = Integer.valueOf(map.get("time").toString().split(" ")[1].split(":")[0]);

            if (t1 == 0) {

                Map<String, Object> map1 = list.get(list.size() - 1);

                if (map1 == null) {

                    list.set(list.size() - 1, map);

                } else {

                    map1.putAll(map);

                    list.set(list.size() - 1, map1);

                }

            } else {

                Map<String, Object> map1 = list.get(t1 - 1);

                if (map1 == null) {

                    list.set(t1 - 1, map);

                } else {

                    map1.putAll(map);

                    list.set(t1 - 1, map1);

                }

            }

        }



        list.removeAll(Collections.singleton(null));

        for (Map<String, Object> map : list) {

            map.put("type", "实测");

            map.put("city", parameters.get("name").toString());

        }



        List<Map<String, Object>> resultList = new ArrayList<>();



        if (foreList.size() == 0) {

            for (Map<String, Object> map : realWeatherList) {

                Map<String,Object> hashMap=new LinkedHashMap<>();

                hashMap.put("time",map.get("time").toString());

                hashMap.put("type","预测");

                hashMap.put("city",map.get("city").toString());

                foreList.add(hashMap);

            }

        }



        for (Map<String, Object> foreMap : foreList) {

            for (Map<String, Object> realMap : list) {

                if (foreMap.get("time").equals(realMap.get("time"))) {

                    resultList.add(foreMap);

                    resultList.add(realMap);

                }

            }

        }

        for (int i = list.size(); i < foreList.size(); i++) {

            Map<String, Object> map = foreList.get(i);

            resultList.add(map);

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", map.get("time").toString());

            hashMap.put("type", "实测");

            hashMap.put("city", parameters.get("name").toString());

            resultList.add(hashMap);

        }



        for (Map<String, Object> map : resultList) {

            String date = map.get("time").toString().substring(0, 16);

            map.put("time", date);

            if (map.get("AQI") != null) {

                String aqi = map.get("AQI").toString().split("\\.")[0];

                map.put("AQI", aqi);

            }

            if (map.get("precip") != null) {

                String aqi = map.get("precip").toString();

                if (aqi.equals("0.0")) {

                    map.put("precip", "");

                }

            }

            if (map.get("precip6") != null) {

                String aqi = map.get("precip6").toString();

                if (aqi.equals("0.0")) {

                    map.put("precip6", "");

                }

            }

            if ((!map.containsKey("SO2C")) && map.containsKey("SO2")) {

                map.put("SO2C", map.get("SO2").toString());

            }

            if ((!map.containsKey("NO2C")) && map.containsKey("NO2")) {

                map.put("NO2C", map.get("NO2").toString());

            }

            if ((!map.containsKey("O3C")) && map.containsKey("O3")) {

                map.put("O3C", map.get("O3").toString());

            }

            if ((!map.containsKey("COC")) && map.containsKey("CO")) {

                map.put("COC", map.get("CO").toString());

            }

            if ((!map.containsKey("PM25C")) && map.containsKey("PM2_5")) {

                map.put("PM25C", map.get("PM2_5").toString());

            }

            if ((!map.containsKey("PM10C")) && map.containsKey("PM10")) {

                map.put("PM10C", map.get("PM10").toString());

            }

            if (map.get("windScale") != null) {

                String windScale = map.get("windScale").toString().replace("-", "~");

                map.put("windScale", windScale);

            }

        }



        //后4小时保良值

        double value = 160.0 * 8;

        int nowHour = Integer.valueOf(sdf1.format(now).substring(11, 13));

        int nowMinute = Integer.valueOf(sdf1.format(now).substring(14, 16));

        String format = sdf1.format(now).substring(0, 13) + ":00:00";

        if (nowHour < 8) {

            return resultList;

        }



        if (nowMinute >= 46) {

            nowHour = nowHour + 1;

            cal.setTime(now);

            cal.add(Calendar.HOUR_OF_DAY, 1);

            format = sdf1.format(cal.getTime()).substring(0, 13) + ":00:00";

        }



        for (Map<String, Object> resultMap : resultList) {

            if (resultMap.get("type").equals("实测")) {

                if (sdf.parse(time).getTime() == sdf.parse(sdf.format(now)).getTime()) {

                    String rtime = resultMap.get("time").toString() + ":00";

                    int rhour = Integer.valueOf(rtime.substring(11, 13));

                    cal.setTime(sdf1.parse(rtime));

                    cal.add(Calendar.HOUR_OF_DAY, -7);

                    Date startTime = cal.getTime();

                    Date endTime = sdf1.parse(format);

                    Map<String, Object> hashMap = new HashMap<>();

                    hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

                    hashMap.put("start", startTime);

                    hashMap.put("end", endTime);

                    if (rhour == 0) {

                        rhour = 24;

                    }



                    if (rhour == nowHour) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = value - Double.valueOf(sumO3Map.get("O3Sum").toString());

                        resultMap.put("goodValue", v);

                    } else if (rhour == nowHour + 1) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 2;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    } else if (rhour == nowHour + 2) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 3;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    } else if (rhour == nowHour + 3) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 4;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    }

                }

            }

            if (resultMap.get("O3C_8H") != null && !resultMap.get("O3C_8H").equals("")) {

                Double O3C_8H = Double.valueOf(resultMap.get("O3C_8H").toString());

                resultMap.put("O3C_8H", String.valueOf(Math.round(O3C_8H)));

            }



            if (resultMap.get("PM25C") != null && !resultMap.get("PM25C").equals("")) {

                Double PM25C = Double.valueOf(resultMap.get("PM25C").toString());

                resultMap.put("PM25C", String.valueOf(Math.round(PM25C)));

            }



            if (resultMap.get("PM10C") != null && !resultMap.get("PM10C").equals("")) {

                Double PM10C = Double.valueOf(resultMap.get("PM10C").toString());

                resultMap.put("PM10C", String.valueOf(Math.round(PM10C)));

            }



            if (resultMap.get("O3C") != null && !resultMap.get("O3C").equals("")) {

                Double O3C = Double.valueOf(resultMap.get("O3C").toString());

                resultMap.put("O3C", String.valueOf(Math.round(O3C)));

            }

        }



        //地理特征

        Map<String, Object> point = shAreaMapper.getgeography(name);

        for (Map<String, Object> map : resultList) {

            double longitude = Double.valueOf(point.get("lng").toString());

            double latitude = Double.valueOf(point.get("lat").toString());

            map.put("longitude", df.format(longitude));

            map.put("latitude", df.format(latitude));

            if (point.get("altitude") != null) {

                map.put("altitude", point.get("altitude").toString());

            }

            if (point.get("vegetation") != null) {

                map.put("vegetation", point.get("vegetation").toString());

            }

            if (point.get("river") != null) {

                map.put("river", point.get("river").toString());

            }

        }

        return resultList;

    }



    @Override

    public void updateForecastWeather(Map<String, Object> parameters) {

        List<Integer> list = new ArrayList<>();

        List<Map<String, Object>> resultList = new ArrayList<>();

@@ -823,4 +357,472 @@
        return null;

    }



    @Override

    public List<Map<String, Object>> getWeatherData(Map<String, Object> parameters) throws ParseException {

        parameters.put("typeFormat", "%Y-%m-%d %H:%i:%s");

        if (parameters.get("city") == null) {

            return new ArrayList<Map<String, Object>>();

        }

        String cityName = parameters.get("city").toString();

        Area area = areaMapper.getAreaByName(cityName);

        Integer code;

        String name;

        Integer parentCode;

        if (area == null) {

            City city = cityMapper.getCityByName(cityName);

            if (city == null) {

                Province province = provinceMapper.getProvinceByName(cityName);

                code = province.getProvinceCode();

                name = province.getProvinceName();

                parentCode = 0;

            } else {

                code = city.getCityCode();

                name = city.getCityName();

                parentCode = city.getProvinceCode();

            }

        } else {

            code = area.getAreaCode();

            name = area.getAreaName();

            parentCode = area.getCityCode();

        }

        parameters.put("cityCode", code);

        parameters.put("name", name);

        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 + " 01:00:00");

        Calendar cal = Calendar.getInstance();

        cal.setTime(selectTime);

        cal.add(Calendar.DAY_OF_MONTH, 1);

        Date end = cal.getTime();

        cal.setTime(sdf.parse(time));

        cal.add(Calendar.DAY_OF_MONTH, -1);

        String beforeDay = sdf.format(cal.getTime());

        parameters.put("start", selectTime);

        parameters.put("end", end);

        List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecast(parameters);



        if (forecastList.size() == 0) {

            if (!String.valueOf(code).endsWith("0000")) {

                parameters.put("cityCode", parentCode);

                forecastList = forecastWeatherMapper.getForecast(parameters);

            }

        }



        //添加预测时间，每天07,15点预测一次

        List<Map<String, Object>> foreList = new ArrayList<>();

        for (Map<String, Object> forecastMap : forecastList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", forecastMap.get("time").toString());

            hashMap.put("type", "预测");

            hashMap.put("city", name);

            if (sdf.parse(time).getTime() <= now.getTime()) {

                hashMap.put("fxTime", (beforeDay + " 15:00").substring(5, 16));

            } else {

                String format = sdf1.format(now).split(" ")[0];

                Date date1 = sdf1.parse(format + " 07:00:00");

                Date date2 = sdf1.parse(format + " 15:00:00");

                if (now.getTime() > date2.getTime()) {

                    hashMap.put("fxTime", (beforeDay + " 15:00").substring(5, 16));

                } else if (now.getTime() > date1.getTime()) {

                    hashMap.put("fxTime", (beforeDay + " 07:00").substring(5, 16));

                }

            }

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(forecastMap.get("json").toString());

            hashMap.putAll(jsonMap);

            foreList.add(hashMap);

        }



        for (Map<String, Object> foreMap : foreList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", parameters.get("cityCode").toString());

            Date endTime = sdf1.parse(foreMap.get("time").toString());

            hashMap.put("end", endTime);

            //过去6小时总降水量(预测)

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -6);

            Date startTime = cal.getTime();

            hashMap.put("start", startTime);

            Map<String, Object> precip6HourMap = forecastWeatherMapper.getPrecip6Hour(hashMap);

            if (precip6HourMap != null) {

                foreMap.put("precip6", precip6HourMap.get("precip6").toString());

            }

            //过去8小时O3平均值(预测)

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -8);

            startTime = cal.getTime();

            hashMap.put("start", startTime);

            Map<String, Object> O3Map = forecastWeatherMapper.getO38Hours(hashMap);

            if (O3Map != null) {

                foreMap.put("O3C_8H", O3Map.get("O3_8H").toString());

            }

        }

        //获取实测aqi

        parameters.put("cityCode", code);

        //从hangzhou_aqi获取，没有再从history_aqi_月分表获取

        int p = 0;

        String timeUnits1 = time.substring(5, 7);

        String timeUnits2 = sdf1.format(end).substring(5, 7);

        List<Map<String, Object>> aqiList = hangzhouAqiMapper.getAqi(parameters);

        if (aqiList.size() == 0) {

            p = 1;

            //1-23点数据必然是同一个月份，最后一个可能是下一个月

            parameters.put("timeUnits", timeUnits1);

            List<Map<String, Object>> aqiList1 = hangzhouAqiMapper.getHistoryAqi(parameters);

            parameters.put("timeUnits", timeUnits2);

            List<Map<String, Object>> aqiList2 = hangzhouAqiMapper.getHistoryAqi(parameters);

            aqiList.addAll(aqiList1);

            aqiList.addAll(aqiList2);

            //获取上一级城市数据

            if (aqiList.size() == 0) {

                p = 0;

                parameters.put("cityCode", parentCode);

                aqiList = hangzhouAqiMapper.getAqi(parameters);

                if (aqiList.size() == 0) {

                    p = 1;

                    parameters.put("timeUnits", timeUnits1);

                    aqiList1 = hangzhouAqiMapper.getHistoryAqi(parameters);

                    parameters.put("timeUnits", timeUnits2);

                    aqiList2 = hangzhouAqiMapper.getHistoryAqi(parameters);

                    aqiList.addAll(aqiList1);

                    aqiList.addAll(aqiList2);

                }

            }

        }



        DecimalFormat df = new DecimalFormat(".####");

        DecimalFormat df1 = new DecimalFormat("#.#");

        List<Map<String, Object>> realAqilist = new ArrayList<>();

        for (Map<String, Object> aqiMap : aqiList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", aqiMap.get("time"));

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(aqiMap.get("json").toString());

            for (String key : jsonMap.keySet()) {

                if ("NO2".equals(key) || "NO2C".equals(key) || "O3".equals(key) || "O3C".equals(key) ||

                        "SO2".equals(key) || "SO2C".equals(key) || "PM2_5".equals(key) || "PM25C".equals(key) || "PM10".equals(key) || "PM10C".equals(key)) {

                    long v = Math.round(Double.valueOf(jsonMap.get(key).toString()));

                    jsonMap.put(key, String.valueOf(v));

                } else if ("COC".equals(key) || "CO".equals(key)) {

                    String CO = df1.format(Double.valueOf(jsonMap.get(key).toString()));

                    jsonMap.put(key, CO);

                } else if ("AQI".equals(key)) {

                    long AQI = Math.round(Double.valueOf(jsonMap.get(key).toString()));

                    jsonMap.put(key, String.valueOf(AQI));

                }



            }

            hashMap.putAll(jsonMap);

            hashMap.put("city", name);

            realAqilist.add(hashMap);

        }



        //计算o3_8H(实测)

        for (Map<String, Object> map : realAqilist) {

            String s = map.get("time").toString();

            Date endTime = sdf1.parse(s);

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -8);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", parameters.get("cityCode").toString());

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            Map<String, Object> O3Map = new HashMap<>();

            if (p == 0) {

                O3Map = hangzhouAqiMapper.getAvgO3EightHours(hashMap);

            } else {

                hashMap.put("time", endTime);

                hashMap.put("timeUnits", s.substring(5, 7));

                O3Map = hangzhouAqiMapper.getHistoryO3EightHours(hashMap);

            }

            map.put("O3C_8H", O3Map.get("O3_8H").toString().replace("\"", ""));

        }

        //光照，该城市下所有设备光照平均值以及tvoc

        Map<String, Object> params = new HashMap<>();

        if (String.valueOf(code).endsWith("0000")) {

            params.put("provinceCode", code);

        } else if (String.valueOf(code).endsWith("00")) {

            params.put("cityCode", code);

        } else {

            params.put("areaCode", code);

        }

        List<Integer> monitorPointIds = monitorPointMapper.getMonitorPointIds(params);

        List<String> macs = deviceMapper.getMacsByMonitorPointIds(monitorPointIds);

        List<Map<String, Object>> beamList = new ArrayList<>();

        List<Map<String, Object>> tvocList = new ArrayList<>();

        if (macs.size() != 0) {

            parameters.put("macs", macs);

            beamList = historyHourlyMapper.getBeamByMacs(parameters);

            tvocList = historyHourlyMapper.getTVOCByMacs(parameters);

        }



        //实测天气

        int x = 0;

        List<Map<String, Object>> realList = realWeatherMapper.getRealWeather(parameters);

        if (realList.size() == 0) {

            //1-23点数据必然是同一个月份，最后一个可能是下一个月

            x = 1;

            timeUnits1 = time.substring(5, 7);

            timeUnits2 = sdf1.format(end).substring(5, 7);

            parameters.put("timeUnits", timeUnits1);

            List<Map<String, Object>> realList1 = realWeatherMapper.getHistoryWeather(parameters);

            parameters.put("timeUnits", timeUnits2);

            List<Map<String, Object>> realList2 = realWeatherMapper.getHistoryWeather(parameters);

            realList.addAll(realList1);

            realList.addAll(realList2);

            //获取上一级城市数据

            if (realList.size() == 0) {

                x = 0;

                parameters.put("cityCode", parentCode);

                realList = realWeatherMapper.getRealWeather(parameters);

                if (realList.size() == 0) {

                    x = 1;

                    parameters.put("timeUnits", timeUnits1);

                    realList1 = realWeatherMapper.getHistoryWeather(parameters);

                    parameters.put("timeUnits", timeUnits2);

                    realList2 = realWeatherMapper.getHistoryWeather(parameters);

                    realList.addAll(realList1);

                    realList.addAll(realList2);

                }

            }

        }

        List<Map<String, Object>> realWeatherList = new ArrayList<>();

        for (Map<String, Object> realMap : realList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", realMap.get("time"));

            Map<String, Object> jsonMap = new HashMap<>();

            if (x == 0) {

                jsonMap = (Map<String, Object>) JSONObject.parse(realMap.get("json").toString());

            } else {

                jsonMap = (Map<String, Object>) JSONObject.parse(realMap.get("value").toString());

            }

            hashMap.putAll(jsonMap);

            hashMap.put("city", name);

            realWeatherList.add(hashMap);

        }



        for (Map<String, Object> realMap : realWeatherList) {

            String rtime = realMap.get("time").toString();

            //光照数据

            for (Map<String, Object> beamMap : beamList) {

                if (beamMap.get("time").equals(rtime)) {

                    realMap.put("beam", beamMap.get("beam").toString());

                }

            }

            //TVOC

            for (Map<String, Object> tvocMap : tvocList) {

                if (tvocMap.get("time").equals(rtime)) {

                    realMap.put("TVOC", tvocMap.get("TVOC").toString());

                }

            }

            //过去6小时降水量(实测)

            Date endTime = sdf1.parse(rtime);

            cal.setTime(endTime);

            cal.add(Calendar.HOUR, -6);

            Date startTime = cal.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("cityCode", code);

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            Map<String, Object> precip6HourMap = realWeatherMapper.getPrecip6Hour(hashMap);

            if (precip6HourMap != null) {

                realMap.put("precip6", precip6HourMap.get("precip6").toString());

            }

        }



        List<Map<String, Object>> list = new ArrayList<>();

        for (int i = 0; i < 24; i++) {

            list.add(null);

        }



        for (Map<String, Object> map : realWeatherList) {

            Integer t1 = Integer.valueOf(map.get("time").toString().split(" ")[1].split(":")[0]);

            if (t1 == 0) {

                list.set(list.size() - 1, map);

            } else {

                list.set(t1 - 1, map);

            }

        }



        for (Map<String, Object> map : realAqilist) {

            Integer t1 = Integer.valueOf(map.get("time").toString().split(" ")[1].split(":")[0]);

            if (t1 == 0) {

                Map<String, Object> map1 = list.get(list.size() - 1);

                if (map1 == null) {

                    list.set(list.size() - 1, map);

                } else {

                    map1.putAll(map);

                    list.set(list.size() - 1, map1);

                }

            } else {

                Map<String, Object> map1 = list.get(t1 - 1);

                if (map1 == null) {

                    list.set(t1 - 1, map);

                } else {

                    map1.putAll(map);

                    list.set(t1 - 1, map1);

                }

            }

        }

        list.removeAll(Collections.singleton(null));

        for (Map<String, Object> map : list) {

            map.put("type", "实测");

            map.put("city", name);

        }



        List<Map<String, Object>> resultList = new ArrayList<>();



        if (foreList.size() == 0) {

            cal.setTime(selectTime);

            for (long d = cal.getTimeInMillis(); d <= end.getTime();

                 cal.set(Calendar.HOUR_OF_DAY, cal.get(Calendar.HOUR_OF_DAY) + 1),

                         d = cal.getTimeInMillis()) {

                Map<String, Object> hashMap = new LinkedHashMap<>();

                hashMap.put("type", "预测");

                hashMap.put("city", name);

                hashMap.put("time", sdf1.format(d));

                foreList.add(hashMap);

            }

        }



        for (Map<String, Object> foreMap : foreList) {

            for (Map<String, Object> realMap : list) {

                if (foreMap.get("time").equals(realMap.get("time"))) {

                    resultList.add(foreMap);

                    resultList.add(realMap);

                }

            }

        }

        for (int i = list.size(); i < foreList.size(); i++) {

            Map<String, Object> map = foreList.get(i);

            resultList.add(map);

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", map.get("time").toString());

            hashMap.put("type", "实测");

            hashMap.put("city", name);

            resultList.add(hashMap);

        }



        for (Map<String, Object> map : resultList) {

            String date = map.get("time").toString().substring(0, 16);

            map.put("time", date);

            if (map.get("precip") != null) {

                if ("0.0".equals(map.get("precip"))) {

                    map.put("precip", "");

                }

            }

            if (map.get("precip6") != null) {

                if ("0.0".equals(map.get("precip6"))) {

                    map.put("precip6", "");

                }

            }

            if ((!map.containsKey("SO2C")) && map.containsKey("SO2")) {

                map.put("SO2C", map.get("SO2").toString().replace("\\.", ""));

                map.remove("SO2");

            }

            if ((!map.containsKey("NO2C")) && map.containsKey("NO2")) {

                map.put("NO2C", map.get("NO2").toString().replace("\\.", ""));

                map.remove("NO2");

            }

            if ((!map.containsKey("O3C")) && map.containsKey("O3")) {

                map.put("O3C", map.get("O3").toString().replace("\\.", ""));

                map.remove("O3");

            }

            if ((!map.containsKey("COC")) && map.containsKey("CO")) {

                map.put("COC", map.get("CO").toString());

                map.remove("CO");

            }

            if ((!map.containsKey("PM25C")) && map.containsKey("PM2_5")) {

                map.put("PM25C", map.get("PM2_5").toString().replace("\\.", ""));

                map.remove("PM2_5");

            }

            if ((!map.containsKey("PM10C")) && map.containsKey("PM10")) {

                map.put("PM10C", map.get("PM10").toString().replace("\\.", ""));

                map.remove("PM10");

            }

            if (map.get("windScale") != null) {

                String windScale = map.get("windScale").toString().replace("-", "~");

                map.put("windScale", windScale);

            }

            //温度取整

            if (map.get("temp") != null) {

                long temp = Math.round(Double.valueOf(map.get("temp").toString()));

                map.put("temp", String.valueOf(temp));

            }

        }



        //后4小时保良值

        double value = 160.0 * 8;

        int nowHour = Integer.valueOf(sdf1.format(now).substring(11, 13));

        int nowMinute = Integer.valueOf(sdf1.format(now).substring(14, 16));

        String format = sdf1.format(now).substring(0, 13) + ":00:00";

        if (nowHour < 8) {

            return resultList;

        }



        if (nowMinute >= 46) {

            nowHour = nowHour + 1;

            cal.setTime(now);

            cal.add(Calendar.HOUR_OF_DAY, 1);

            format = sdf1.format(cal.getTime()).substring(0, 13) + ":00:00";

        }



        for (Map<String, Object> resultMap : resultList) {

            if (resultMap.get("type").equals("实测")) {

                if (sdf.parse(time).getTime() == sdf.parse(sdf.format(now)).getTime()) {

                    String rtime = resultMap.get("time").toString() + ":00";

                    int rhour = Integer.valueOf(rtime.substring(11, 13));

                    cal.setTime(sdf1.parse(rtime));

                    cal.add(Calendar.HOUR_OF_DAY, -7);

                    Date startTime = cal.getTime();

                    Date endTime = sdf1.parse(format);

                    Map<String, Object> hashMap = new HashMap<>();

                    hashMap.put("cityCode", Integer.valueOf(parameters.get("cityCode").toString()));

                    hashMap.put("start", startTime);

                    hashMap.put("end", endTime);

                    if (rhour == 0) {

                        rhour = 24;

                    }



                    if (rhour == nowHour) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = value - Double.valueOf(sumO3Map.get("O3Sum").toString());

                        resultMap.put("goodValue", v);

                    } else if (rhour == nowHour + 1) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 2;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    } else if (rhour == nowHour + 2) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 3;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    } else if (rhour == nowHour + 3) {

                        Map<String, Object> sumO3Map = hangzhouAqiMapper.getSumO3(hashMap);

                        double v = (value - Double.valueOf(sumO3Map.get("O3Sum").toString())) / 4;

                        resultMap.put("goodValue", String.valueOf(Math.round(v)));

                    }

                }

            }

        }



        //地理特征

        Map<String, Object> geographyMap = shAreaMapper.getGeography(name);

        for (Map<String, Object> map : resultList) {

            double longitude = Double.valueOf(geographyMap.get("lng").toString());

            double latitude = Double.valueOf(geographyMap.get("lat").toString());

            map.put("longitude", df.format(longitude));

            map.put("latitude", df.format(latitude));

            if (geographyMap.get("altitude") != null) {

                map.put("altitude", geographyMap.get("altitude").toString());

            }

            if (geographyMap.get("vegetation") != null) {

                map.put("vegetation", geographyMap.get("vegetation").toString());

            }

            if (geographyMap.get("river") != null) {

                map.put("river", geographyMap.get("river").toString());

            }

        }

        return resultList;

    }

}


 src/main/resources/mapper/DeviceMapper.xml

@@ -514,9 +514,10 @@
)
    </select>

    <select id="getDeviceListByMonitorPointIds" resultType="com.moral.entity.Device">
        select * from device where monitor_point_id in
    <select id="getMacsByMonitorPointIds" resultType="java.lang.String">
        select mac from device where monitor_point_id in
        <foreach item="item" collection="list" index="index" open="(" separator="," close=")">#{item}</foreach>
        and is_delete=0
    </select>

    <select id="selectAllFieldByMac" resultType="java.util.Map">

 src/main/resources/mapper/ForecastWeatherMapper.xml

@@ -45,7 +45,7 @@

    <select id="getO38Hours" resultType="java.util.Map">
        select
        AVG(json->'$.O3C')  as 'O3C_8H'
        cast(AVG(json->'$.O3C') as UNSIGNED  integer) 'O3_8H'
        FROM
        forecast_weather
        WHERE

 src/main/resources/mapper/HangzhouAqiMapper.xml

@@ -73,11 +73,25 @@
        DATE_FORMAT(time, #{typeFormat}) time,
        ifnull(aqi_json->'$.O3C',aqi_json->'$.O3') as 'O3'
        FROM
        hangzhou_aqi ha
        hangzhou_aqi
        WHERE
        ha.time >= #{start}
        AND ha.time <![CDATA[<]]> #{end}
        AND ha.city_code = #{cityCode}
        time >= #{start}
        AND time <![CDATA[<]]> #{end}
        AND city_code = #{cityCode}
        ORDER BY
        time
  </select>

    <select id="getHistoryO3" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(time, #{typeFormat}) time,
        value->'$.O3' as 'O3'
        FROM
        history_aqi_${timeUnits}
        WHERE
        time >= #{start}
        AND time <![CDATA[<]]> #{end}
        AND city_code = #{cityCode}
        ORDER BY
        time
  </select>
@@ -96,9 +110,23 @@
        time
    </select>

    <select id="getHistoryAqi" resultType="java.util.Map">
        select
        DATE_FORMAT(time, #{typeFormat}) time,
        value json
        FROM
        history_aqi_${timeUnits}
        WHERE
        time >= #{start}
        AND time <![CDATA[<]]> #{end}
        AND city_code = #{cityCode}
        ORDER BY
        time
    </select>

    <select id="getAvgO3EightHours" resultType="java.util.Map">
        select
        AVG(case when aqi_json->'$.O3C' is null then aqi_json->'$.O3' else aqi_json->'$.O3C' end)  as 'O3C_8H'
        cast(AVG(case when aqi_json->'$.O3C' is null then aqi_json->'$.O3' else aqi_json->'$.O3C' end) as UNSIGNED  integer) 'O3_8H'
        FROM
        hangzhou_aqi
        WHERE
@@ -107,6 +135,16 @@
        AND city_code = #{cityCode}
    </select>

    <select id="getHistoryO3EightHours" resultType="java.util.Map">
        select
        value->'$.O3_8h' 'O3_8H'
        FROM
        history_aqi_${timeUnits}
        WHERE
        time = #{time}
        and city_code=#{cityCode}
    </select>

    <select id="getSumO3" resultType="java.util.Map">
        select
        SUM(case when aqi_json->'$.O3C' is null then aqi_json->'$.O3' else aqi_json->'$.O3C' end) 'O3Sum'

 src/main/resources/mapper/HistoryHourlyMapper.xml

@@ -68,14 +68,14 @@
    <select id="getBeamByMacs" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(h.time,#{typeFormat}) time,
        avg(h.json->'$.e12[0]') beam
        cast(AVG(h.json->'$.e12[0]') as UNSIGNED integer) 'beam'
        FROM history_hourly h
        WHERE h.mac in
        <foreach  item="item" collection="macs" index="index"  open="(" separator="," close=")">#{item}</foreach>
        <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')
        group by h.time
    </select>

    <!-- 根据设备mac地址，时间，污染因子查询一天的平均值 -->
@@ -88,16 +88,17 @@

    <select id="getTVOCByMacs" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s') time,
        avg(h.json->'$.e17[0]') 'TVOC'
        DATE_FORMAT(h.time,#{typeFormat}) time,
        round(avg(h.json->'$.e17[0]'),3) 'TVOC'
        FROM history_hourly h
        WHERE h.mac in
        <foreach  item="item" collection="macs" index="index"  open="(" separator="," close=")">#{item}</foreach>
        <foreach item="item" collection="macs" index="index" open="(" separator="," close=")">#{item}</foreach>
        and h.time >= #{start}
        AND h.time <![CDATA[<]]> #{end}
        and h.json->'$.e17[0]' is not null
        group by h.time
    </select>

    <select id="getDataByMacAndTime" resultType="java.util.Map">
        SELECT DATE_FORMAT(`time`,'%Y-%m-%d %H:%i:%s') as `time`,json->'$.${sensor}[0]' as ${sensor} FROM `history_hourly` where `mac`=#{mac} and `time`&gt;=#{startTime} and `time`&lt;#{endTime}
    </select>

 src/main/resources/mapper/MonitorPointMapper.xml

@@ -190,8 +190,8 @@
        select * from monitor_point where province_code=#{provinceCode} limit 0,1
    </select>

    <select id="getMonitorList" resultMap="BaseResultMap">
        select * from monitor_point
    <select id="getMonitorPointIds" resultType="java.lang.Integer">
        select id from monitor_point
        where
        <if test="areaCode != null">
            area_code = #{areaCode}

 src/main/resources/mapper/RealWeatherMapper.xml

@@ -18,6 +18,20 @@
        time
   </select>

    <select id="getHistoryWeather" resultType="java.util.Map">
        select
        DATE_FORMAT(time, #{typeFormat}) time,
        value
        FROM
        history_weather_${timeUnits}
        WHERE
        time >= #{start}
        AND time <![CDATA[<]]> #{end}
        AND city_code = #{cityCode}
        ORDER BY
        time
   </select>

    <select id="getPrecip6Hour" resultType="java.util.Map">
        select
        round(sum(json->'$.precip'),2) 'precip6'

 src/main/resources/mapper/ShAreaMapper.xml

@@ -1,7 +1,7 @@
<?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.ShAreaMapper" >
  <select id="getgeography" resultType="java.util.Map">
  <select id="getGeography" resultType="java.util.Map">
    select lng,lat,altitude,vegetation,river from sh_area
    where name=#{name}
  </select>