monitor平均值导出数据

于紫祥_1901

2020-11-16 298f89f02b65783ba5cb79cf57f4fd097868ee5b

monitor平均值导出数据

 src/main/java/com/moral/controller/ScreenController.java

@@ -2654,4 +2654,37 @@
        return i;
    }


    @GetMapping("/monitorAVGData")
    @ApiOperation(value = "monitor平均数据导出", notes = "monitor平均数据导出")
    @ApiImplicitParams(value = {
            @ApiImplicitParam(name = "monitorPointIds", value = "多个monitorid", required = true, paramType = "query", dataType = "String"),
            @ApiImplicitParam(name = "time", value = "时间(格式：2020-03-19)", required = true, paramType = "query", dataType = "String")})
    public  List<List<Map>> monitorAVGData(HttpServletRequest request) throws Exception {
        Map<String, Object> parameters = getParametersStartingWith(request, null);
        String monitorPointIds = (String) parameters.get("monitorPointIds");
        String time = (String) parameters.get("time");
        time = time+" 00:00:00";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd mm:HH:ss");//注意月份是MM
        Date date = simpleDateFormat.parse(time);
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.add(calendar.DATE,+1);
        String date1= simpleDateFormat.format(date);
        String date2= simpleDateFormat.format(calendar.getTime());
        String[] mIds = monitorPointIds.split(",");
        List<List<Map>> lists = new ArrayList<>();
        if (mIds.length==1 && mIds.length!=0){
            String mId = mIds[0];
            List<Map> dataAvbByMIdAndTime = historyHourlyService.getDataAvbByMIdAndTime(mId, date1, date2);
            lists.add(dataAvbByMIdAndTime);
            //List<Device> devicesByMonitorPointId = deviceService.getDevicesByMonitorPointId(Integer.valueOf(mId));
        }else {
            for (int i = 0; i <mIds.length ; i++) {
                List<Map> dataAvbByMIdAndTime = historyHourlyService.getDataAvbByMIdAndTime(mIds[i], date1, date2);
                lists.add(dataAvbByMIdAndTime);
            }
        }
        return lists;
    }
}

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

@@ -30,4 +30,12 @@
                                  @Param("sensor") String sensor,
                                  @Param("startTime") String startTime,
                                  @Param("endTime") String endTime);
    List<Map> getDataByMacAndTime1(@Param("mac") String mac,
                                  @Param("sensor") String sensor,
                                  @Param("startTime") String startTime,
                                  @Param("endTime") String endTime);

    List<Map<String,Object>> getDataAvbByMIdAndTime( @Param("mId")String mId,
                                                     @Param("startTime")String startTime,
                                                     @Param("endTime")String endTime);
}

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

@@ -43,4 +43,6 @@
    String getJsonData(@Param("mac") String mac,

                       @Param("time") String time,

                       @Param("table") String table);



    Map<String,Object> getDailyAvgData(@Param("mId")String mId,@Param("time")String time);

}

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

@@ -26,4 +26,6 @@
    List<Map<String, Object>> getDataByTimeSlot(String mac, String startTime, String endTime) throws Exception;

    List<Map> getDataByMacAndTime(Map param);

    List<Map> getDataAvbByMIdAndTime(String mId,String startTime,String endTime);
}

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

@@ -1,10 +1,16 @@
package com.moral.service.impl;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.moral.entity.MachineActivate;
import com.moral.mapper.HistoryMapper;
import com.moral.util.AQICalculation;
import com.moral.util.AQICalculation2;
import io.swagger.models.auth.In;
import org.apache.commons.collections.MapUtils;
import org.springframework.stereotype.Service;

@@ -23,6 +29,9 @@
public class HistoryHourlyServiceImpl implements HistoryHourlyService {
    @Resource
    private HistoryHourlyMapper historyHourlyMapper;

    @Resource
    private HistoryMapper historyMapper;

    @Resource
    private DeviceService deviceService;
@@ -146,7 +155,6 @@
                String date2 = simpleDateFormat.format(date1);
                List<Map> list = historyHourlyMapper.getDataByMacAndTime(mac, sensor, perTime, date2);
                List<Map> newList = new ArrayList<Map>();
                //System.out.println(list);
                for (int i = 0; i < 24; i++) {
                    Map map=new HashMap();
                    if (i < 10) {
@@ -159,7 +167,6 @@
                        newList.add(map);
                    }
                }
                //System.out.println(newList);
                if (list.size() <= 24 && list.size() >= 0) {
                    for (int i = 0; i <list.size() ; i++) {
                        String timeS = list.get(i).get("time").toString().substring(11, 13);
@@ -216,6 +223,136 @@
    }

    @Override
    public List<Map> getDataAvbByMIdAndTime(String mId, String startTime, String endTime) {
        List<Device> devicesByMonitorPointId = deviceService.getDevicesByMonitorPointId(Integer.valueOf(mId));
        List<List> lists = new ArrayList<>();
        List<List<Map<String,String>>> listArrayList = new ArrayList<>();
        List<Map<String, Object>> dataAvbByMIdAndTime = historyHourlyMapper.getDataAvbByMIdAndTime(mId, startTime, endTime);
        List<Map> mapList = new ArrayList<>();
        for (Map<String, Object> stringStringMap : dataAvbByMIdAndTime) {
            Map<String,String> map = new HashMap();
            String s = stringStringMap.get("O3").toString();
            Object time = stringStringMap.get("time");
            map.put("O3",s);
            map.put("time",time.toString());
            mapList.add(map);
        }
        if (mapList.size()>=8){
            List<Map<String,String>> mapList1 = new ArrayList<>();
        for (int i = 7; i <=mapList.size()-1 ; i++) {
            if (mapList.get(i).get("O3")!=null){
                List<Integer> numList = new ArrayList();
                Map<String,String> O3_8 = new HashMap<>();
                for (int j =i; j<= mapList.size(); j--) {
                    if (j<0){
                        break;
                    }
                    numList.add(j);
                    if (numList.size()==8){
                        List e15 = new ArrayList();
                        for (int k = 0; k <numList.size() ; k++) {
                            String value = mapList.get(Integer.valueOf(numList.get(k))).get("O3").toString();
                            e15.add(value);
                        }
                        double sum =0;
                        for (Object o : e15) {
                            sum=sum+Double.parseDouble(o.toString());
                        }
                        Double avg1 = sum/e15.size();
                        int timeOfHour = numList.get(0);
                        if (timeOfHour==23){
                            timeOfHour=0;
                        }else {
                            timeOfHour=timeOfHour+1;
                        }
                        O3_8.put("time", String.valueOf(timeOfHour));
                        O3_8.put("O3-8h", String.valueOf(avg1));
                        mapList1.add(O3_8);
                    }else {
                        continue;
                    }
            }
            }
            listArrayList.add(mapList1);
        }
        }
        Double maxO3=0.0;
        if(listArrayList.size()>0){
            List<Map<String, String>> mapList1 = listArrayList.get(0);
            List<Double> O3List = new ArrayList();
            for (Map<String, String> stringStringMap : mapList1) {
                O3List.add(Double.parseDouble(stringStringMap.get("O3-8h")));
            }

            maxO3 = Collections.max(O3List);
            for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
                dataAvbByMIdAndTime.get(i).put("O3-8h",null);
                int time = Integer.parseInt(((Object)dataAvbByMIdAndTime.get(i).get("time")).toString().substring(11,13)) ;
                for (int j = 0; j <mapList1.size() ; j++) {
                    int time1 = Integer.parseInt(mapList1.get(j).get("time").toString()) ;
                    if (time==time1){
                        Double O3_8h = Double.parseDouble(mapList1.get(j).get("O3-8h").toString());
                        dataAvbByMIdAndTime.get(i).put("O3-8h",new BigDecimal(O3_8h).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
                    }
                }
            }
        }
        Map<String, Object> dailyAvgData = historyMapper.getDailyAvgData(mId, startTime);
        if (dailyAvgData!=null){
            dailyAvgData.put("maxO3-8h",new BigDecimal(maxO3).setScale(4,BigDecimal.ROUND_HALF_UP).doubleValue());
            dailyAvgData.put("time",startTime);
        }
        if (dataAvbByMIdAndTime.size()>24){
            for (int i = 0; i <dataAvbByMIdAndTime.size()-1 ; i++) {
                Map<String, Object> map =dataAvbByMIdAndTime.get(i);
                Map<String, Object> map1 = AQICalculation2.hourlyAQI(map);
                dataAvbByMIdAndTime.get(i).put("AQI",Double.parseDouble(map1.get("AQI").toString()));
                if (Double.parseDouble(map1.get("AQI").toString())>=50){
                    String name = map1.get("maxSensor").toString();
                    dataAvbByMIdAndTime.get(i).put("首要污染物",name);
                }else {
                    dataAvbByMIdAndTime.get(i).put("首要污染物","-");
                }
            }
        }else {
            for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
                Map<String, Object> map =dataAvbByMIdAndTime.get(i);
                Map<String, Object> map1 = AQICalculation2.hourlyAQI(map);
                dataAvbByMIdAndTime.get(i).put("AQI",Double.parseDouble(map1.get("AQI").toString()));
                if (Double.parseDouble(map1.get("AQI").toString())>=50){
                    String name = map1.get("maxSensor").toString();
                    dataAvbByMIdAndTime.get(i).put("首要污染物",name);
                }else {
                    dataAvbByMIdAndTime.get(i).put("首要污染物","-");
                }
            }
        }
        Map<String, Object> dayAQI = AQICalculation2.dayAQI(dailyAvgData);
        dailyAvgData.put("首要污染物",dayAQI.get("maxSensor"));
        dailyAvgData.put("AQI",dayAQI.get("AQI"));
        dataAvbByMIdAndTime.add(dataAvbByMIdAndTime.size(),dailyAvgData);

        List<Map> finalList = new ArrayList<>();
        for (int i = 0; i <dataAvbByMIdAndTime.size() ; i++) {
            Map timeAndDate = new HashMap();
            if (i!=dataAvbByMIdAndTime.size()-1){
                String time = dataAvbByMIdAndTime.get(i).get("time").toString().substring(11,13);
                dataAvbByMIdAndTime.get(i).remove("time");
                timeAndDate.put("time",time);
                timeAndDate.put("data",dataAvbByMIdAndTime.get(i));
                finalList.add(timeAndDate);
            }else {
                String time = dataAvbByMIdAndTime.get(i).get("time").toString().substring(0,11);
                dataAvbByMIdAndTime.get(i).remove("time");
                timeAndDate.put("time",time);
                timeAndDate.put("data",dataAvbByMIdAndTime.get(i));
                finalList.add(timeAndDate);
            }
        }
        return finalList;
    }

    @Override
    public Point getDirPoint(Map<String, Object> parameters) throws Exception {
        Map<String, Object> pollutionSourceData = getPollutionSourceDataByHour(parameters);
        if (MapUtils.isEmpty(pollutionSourceData)) {

 src/main/java/com/moral/util/AQICalculation2.java

New file
@@ -0,0 +1,332 @@
package com.moral.util;

import java.util.*;

public class AQICalculation2 {
	
    public static Map<String, Object> hourlyAQI(Map<String, Object> map){
        List<Double> AQIList = new ArrayList<>();
        Map maxMap = new HashMap();
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            if (entry.getValue() instanceof Double){
                Double value = Double.parseDouble(entry.getValue().toString());
                if (key!="O3-8h"){
                    maxMap.put(key,value);
                }
                Double PM2_5AQI;
                Double PM10AQI;
                Double SO2AQI;
                Double NO2AQI;
                Double COAQI;
                Double O3AQI;
                switch (key) {
                    case "PM2_5":PM2_5AQI = PM2_5AQI(value);
                        AQIList.add(PM2_5AQI);
                        break;
                    case "PM10":PM10AQI = PM10AQI(value);
                        AQIList.add(PM10AQI);
                        break;
                    case "SO2":SO2AQI = SO2AQI(value);
                        AQIList.add(SO2AQI);
                        break;
                    case "e16":NO2AQI = NO2AQI(value);
                        AQIList.add(NO2AQI);
                        break;
                    case "CO":COAQI = COAQI(value);
                        AQIList.add(COAQI);
                        break;
                    case "O3":O3AQI = O3AQI(value);
                        AQIList.add(O3AQI);
                        break;

                    default:
                        break;
                }
            }else {
                continue;
            }
            }

        Double AQIMAX = Collections.max(AQIList);
        Map<String, Object> returnMap = new HashMap<>();
        //System.out.println("-------"+maxMap);
        Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
                .stream()
                .max(Map.Entry.comparingByValue());
        //System.out.println("-------"+max0.get().getKey());

        returnMap.put("maxSensor",max0.get().getKey());
        returnMap.put("AQI", AQIMAX);
        return returnMap;
    }
    public static Map<String, Object> dayAQI(Map<String, Object> map){
        List<Double> AQIList = new ArrayList<>();
        Map maxMap = new HashMap();
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            if (entry.getValue() instanceof Double){
                Double value = Double.parseDouble(entry.getValue().toString());
                if (key!="O3-day"){
                    maxMap.put(key,value);
                }
                Double PM2_5AQI;
                Double PM10AQI;
                Double SO2AQI;
                Double NO2AQI;
                Double COAQI;
                Double maxO38hAQI;
                switch (key) {
                    case "PM2_5":PM2_5AQI = PM2_5AQI(value);
                        AQIList.add(PM2_5AQI);
                        break;
                    case "PM10":PM10AQI = PM10AQI(value);
                        AQIList.add(PM10AQI);
                        break;
                    case "SO2":SO2AQI = SO2DayAQI(value);
                        AQIList.add(SO2AQI);
                        break;
                    case "e16":NO2AQI = NO2DayAQI(value);
                        AQIList.add(NO2AQI);
                        break;
                    case "CO":COAQI = CODayAQI(value);
                        AQIList.add(COAQI);
                        break;
                    case "O3":maxO38hAQI = O3DayAQI(value);
                        AQIList.add(maxO38hAQI);
                        break;
                    default:
                        break;
                }
            }else {
                continue;
            }
        }

        Double AQIMAX = Collections.max(AQIList);
        Map<String, Object> returnMap = new HashMap<>();
        System.out.println("-------"+maxMap);
        Optional<Map.Entry<String, Integer>> max0 = maxMap.entrySet()
                .stream()
                .max(Map.Entry.comparingByValue());
        //System.out.println("-------"+max0.get().getKey());

        returnMap.put("maxSensor",max0.get().getKey());
        returnMap.put("AQI", AQIMAX);
        return returnMap;
    }
    static double PM2_5AQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=35) {
            result = 50/35*(value-0)+0;
        }else if (value<=75) {
            result = 50/40*(value-35)+50;
        }else if (value<=115) {
            result = 50/40*(value-75)+100;
        }else if (value<=150) {
            result = 50/35*(value-115)+150;
        }else if (value<=250) {
            result = 100/100*(value-150)+200;
        }else if (value<=350) {
            result = 100/100*(value-250)+300;
        }else {
            result = 100/150*(value-350)+400;
        }
        return result;
    }
	
    static double PM10AQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=50) {
            result = 50/50*(value-0)+0;
        }else if (value<=150) {
            result = 50/100*(value-50)+50;
        }else if (value<=250) {
            result = 50/100*(value-150)+100;
        }else if (value<=350) {
            result = 50/100*(value-250)+150;
        }else if (value<=420) {
            result = 100/70*(value-350)+200;
        }else if (value<=500) {
            result = 100/80*(value-420)+300;
        }else {
            result = 100/100*(value-500)+400;
        }
        return result;
    }

    static double SO2AQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=150) {
            result = 50/150*(value-0)+0;
        }else if (value<=500) {
            result = 50/350*(value-150)+50;
        }else if (value<=650) {
            result = 50/150*(value-500)+100;
        }else {
            result = 50/150*(value-650)+150;
        }
        return result;
    }

    static double NO2AQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=100) {
            result = 50/100*(value-0)+0;
        }else if (value<=200) {
            result = 50/100*(value-100)+50;
        }else if (value<=700) {
            result = 50/500*(value-200)+100;
        }else if (value<=1200) {
            result = 50/500*(value-700)+150;
        }else if (value<=2340) {
            result = 100/1140*(value-1200)+200;
        }else if (value<=3090) {
            result = 100/750*(value-2340)+300;
        }else {
            result = 100/750*(value-3090)+400;
        }
        return result;
    }

    static double COAQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=5) {
            result = 50/5*(value-0)+0;
        }else if (value<=10) {
            result = 50/5*(value-5)+50;
        }else if (value<=35) {
            result = 50/25*(value-10)+100;
        }else if (value<=60) {
            result = 50/25*(value-35)+150;
        }else if (value<=90) {
            result = 100/30*(value-60)+200;
        }else if (value<=120) {
            result = 100/30*(value-90)+300;
        }else {
            result = 100/30*(value-120)+400;
        }
        return result;
    }

    static double O3AQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=100) {
            result = 50/160*(value-0)+0;
        }else if (value<=160) {
            result = 50/40*(value-160)+50;
        }else if (value<=215) {
            result = 50/100*(value-200)+100;
        }else if (value<=265) {
            result = 50/100*(value-300)+150;
        }else if (value<=800) {
            result = 100/400*(value-400)+200;
        }else if (value<=2100) {
            result = 100/200*(value-800)+300;
        }else {
            result = 100/200*(value-1000)+400;
        }
        return result;
    }

    static double O3DayAQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=100) {
            result = 50/100*(value-0)+0;
        }else if (value<=160) {
            result = 50/60*(value-100)+50;
        }else if (value<=215) {
            result = 50/55*(value-160)+100;
        }else if (value<=265) {
            result = 50/150*(value-215)+150;
        }else if (value<=800) {
            result = 100/535*(value-265)+200;
        }else if (value<=2100) {
            result = 100/12*(value-800)+300;
        }else {
            result = 100/12*(value-48)+400;
        }
        return result;
    }

    static double SO2DayAQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=50) {
            result = 50/50*(value-0)+0;
        }else if (value<=150) {
            result = 50/100*(value-50)+50;
        }else if (value<=475) {
            result = 50/325*(value-150)+100;
        }else if (value<=800) {
            result = 50/325*(value-475)+150;
        }else if (value<=1600) {
            result = 100/800*(value-800)+200;
        }else if (value<=2100) {
            result = 100/500*(value-1600)+300;
        }else {
            result = 100/520*(value-2100)+400;
        }
        return result;
    }
    static double NO2DayAQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=40) {
            result = 50/40*(value-0)+0;
        }else if (value<=80) {
            result = 50/40*(value-40)+50;
        }else if (value<=180) {
            result = 50/100*(value-80)+100;
        }else if (value<=280) {
            result = 50/100*(value-180)+150;
        }else if (value<=565) {
            result = 100/285*(value-280)+200;
        }else if (value<=750) {
            result = 100/185*(value-565)+300;
        }else {
            result = 100/190*(value-750)+400;
        }
        return result;
    }

    static double CODayAQI(Double value) {
        Double result = (double) 0;
        if(value<=0) {
            result=(double) 0;
        }else if (value<=5) {
            result = 50/2*(value-0)+0;
        }else if (value<=10) {
            result = 50/2*(value-2)+50;
        }else if (value<=35) {
            result = 50/10*(value-4)+100;
        }else if (value<=60) {
            result = 50/10*(value-14)+150;
        }else if (value<=90) {
            result = 100/12*(value-24)+200;
        }else if (value<=120) {
            result = 100/12*(value-36)+300;
        }else {
            result = 100/12*(value-48)+400;
        }
        return result;
    }


}

 src/main/resources/mapper/HistoryHourlyMapper.xml

@@ -102,4 +102,14 @@
    <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>
    <select id="getDataByMacAndTime1" 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`<![CDATA[<]]>#{endTime}
    </select>
    <select id="getDataAvbByMIdAndTime" resultType="java.util.Map">
        select hh.time as `time`,
            AVG(hh.json->"$.e1[0]") as `PM2_5`,AVG(hh.json->"$.e2[0]") as 'PM10',AVG(hh.json->"$.e10[0]") as 'CO',
            AVG(hh.json->"$.e11[0]") as 'SO2',AVG(hh.json->"$.e16[0]") as 'NO2',AVG(hh.json->"$.e15[0]") as 'O3'
            from monitor_point mt,device d,history_hourly hh
            where mt.id=d.monitor_point_id and d.mac=hh.mac and mt.id=#{mId} and hh.time>#{startTime} and hh.time<![CDATA[<=]]>#{endTime} GROUP BY hh.time;
    </select>
</mapper>

 src/main/resources/mapper/HistoryMapper.xml

@@ -297,5 +297,11 @@
        where  mac = #{mac} and time &gt;= #{time1} and time &lt;=#{time}

        limit 0,1

    </select>

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

        SELECT  AVG(hd.json->"$.e1[0]") as `PM2_5`,AVG(hd.json->"$.e2[0]") as 'PM10',AVG(hd.json->"$.e10[0]") as 'CO',

            AVG(hd.json->"$.e11[0]") as 'SO2',AVG(hd.json->"$.e16[0]") as 'NO2',AVG(hd.json->"$.e15[0]") as 'O3-day'

FROM `history_daily` hd,monitor_point mt,device d where mt.id=d.monitor_point_id

and d.mac=hd.mac and mt.id=#{mId} and time=#{time}

    </select>



</mapper>