更新 查询

xufenglei

2018-01-15 2e2861fd0bbba403a15a212c550bed9f8331f467

更新 查询

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

@@ -232,7 +232,8 @@
            if (!parameters.containsKey("mac")) {

                result.put("msg", "参数不能为空！");

            } else {

                result = screenService.getDayAQIBySensor(parameters);

                //result = screenService.getDayAQIBySensor(parameters);

                result = screenService.getDayAQIByDevice(parameters);

            }

        } catch (BusinessException be) {

            be.printStackTrace();


 src/main/java/com/moral/monitor/dao/HistoryEntityMapper.java

@@ -15,4 +15,6 @@
    List<Map<String, Object>> getEquipmentStates(Map<String, Object> parameters); 



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



    Map<String, Double> getDayAQIByDevice(Map<String, Object> parameters);

}

 src/main/java/com/moral/monitor/service/ScreenService.java

@@ -21,4 +21,6 @@


    Map<String, Object> getDayAQIBySensor(Map<String, Object> parameters) throws Exception;



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



}


 src/main/java/com/moral/monitor/service/impl/ScreenServiceImpl.java

@@ -1,6 +1,9 @@
package com.moral.monitor.service.impl;



import java.util.ArrayList;

import static org.apache.commons.lang3.time.DateUtils.addDays;

import static org.apache.commons.lang3.time.DateUtils.truncate;

import static org.springframework.util.ObjectUtils.isEmpty;



import java.util.Calendar;

import java.util.Collections;

import java.util.Date;

@@ -237,22 +240,29 @@
    public Map<String, Object> getMonthAverageBySensor(Map<String, Object> parameters) {

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

        Date date = new Date();

        Long end = DateUtils.truncate(date, Calendar.DATE).getTime(),start;

        //Long end = DateUtils.truncate(date, Calendar.DATE).getTime(),start;

        Date end = DateUtils.truncate(date, Calendar.DATE),start;

		
        // 每月一日的数据取上月的数据

        if (1 == Calendar.getInstance().get(Calendar.DAY_OF_MONTH)) {

            // 上个月1日00:00:00

            start = DateUtils.truncate(DateUtils.addMonths(date, -1), Calendar.MONTH).getTime();

            //start = DateUtils.truncate(DateUtils.addMonths(date, -1), Calendar.MONTH).getTime();

            start = DateUtils.truncate(DateUtils.addMonths(date, -1), Calendar.MONTH);

        } else {

            // 这个月1日00:00:00

            start =  DateUtils.truncate(date, Calendar.MONTH).getTime();

            //start =  DateUtils.truncate(date, Calendar.MONTH).getTime();

            start =  DateUtils.truncate(date, Calendar.MONTH);

        }

        start = DateUtils.addHours(start, 8);

        end = DateUtils.addHours(end, 8);

        Aggregation aggregation = Aggregation.newAggregation(

                Aggregation.match(Criteria.where("mac").is(parameters.get("mac"))),

                Aggregation.match(Criteria.where("time").gte(start)),

                Aggregation.match(Criteria.where("time").lt(end)),

                Aggregation.group("mac").avg((String) parameters.get("macKey")).as("avg")

            );

        AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation, "data",BasicDBObject.class);

        //AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation, "data",BasicDBObject.class);

        AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation, "data_hourly",BasicDBObject.class);

        List<BasicDBObject> list = results.getMappedResults();

        if (!ObjectUtils.isEmpty(list)) {

            result = list.get(0);

@@ -326,4 +336,63 @@
        }

        return resultMap;

    }



    @Override

    public Map<String, Object> getDayAQIByDevice(Map<String, Object> parameters) {

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

        Date date = new Date();

        // 昨日00:00:00

        parameters.put("start", truncate(addDays(date, -1), Calendar.DATE));

        // 今日00:00:00

        parameters.put("end", truncate(date, Calendar.DATE));

        String[] IAQIValues = ResourceUtil.getArrValue("IAQI");

        Map<String, Double> average = historyMapper.getDayAQIByDevice(parameters);

        if (isEmpty(average)) {

            resultMap.put("AQI", "N/V");

        } else {

            Set<Double> IAQIs = new HashSet<Double>();

            for (Map.Entry<String, Double> entry : average.entrySet()) {

                double minMacKey = 0, maxMacKey = 0, minIAQI = 0, maxIAQI = 0;

                String[] macKeyValues = ResourceUtil.getArrValue(entry.getKey());

                Double avg = entry.getValue();

                if (isEmpty(avg)) {

                    IAQIs.add(null);

                } else {

                    int index = -1;

                    for (int i = 0; i < macKeyValues.length; i++) {

                        if (avg <= Double.valueOf(macKeyValues[i])) {

                            if (i == 0) {

                                index = i;

                            } else {

                                index = i - 1;

                            }

                            break;

                        }

                    }

                    if (index == -1) {

                        IAQIs.add(Double.MAX_VALUE);

                    } else {

                        minMacKey = Double.valueOf(macKeyValues[index]);

                        maxMacKey = Double.valueOf(macKeyValues[index + 1]);

                        minIAQI = Double.valueOf(IAQIValues[index]);

                        maxIAQI = Double.valueOf(IAQIValues[index + 1]);

                        Double result = calculateIAQI(maxIAQI, minIAQI, maxMacKey, minMacKey, avg);

                        IAQIs.add(result);

                    }

                }

            }

            IAQIs.remove(null);

            if (isEmpty(IAQIs)) {

                resultMap.put("AQI", "N/V");

            } else {

                Double AQI = Collections.max(IAQIs);

                if (AQI == Double.MAX_VALUE) {

                    resultMap.put("AQI", IAQIValues[IAQIValues.length - 1]);

                } else {

                    resultMap.put("AQI", String.format("%.0f", AQI));

                }

            }

        }

        return resultMap;

    }

}


 src/main/resources/mapper/HistoryEntityMapper.xml

@@ -332,4 +332,21 @@
        GROUP BY

            time

    </select>

	
    <select id="getDayAQIByDevice" resultType="map">

        SELECT

            AVG(json -> '$.e1[0]') e1,

            AVG(json -> '$.e2[0]') e2,

            AVG(json -> '$.e10[0]') e10,

            AVG(json -> '$.e11[0]') e11,

            AVG(json -> '$.e15[0]') e15,

            AVG(json -> '$.e16[0]') e16

        FROM

            data

        WHERE

            mac = #{mac}

        AND time >= #{start}

        AND time &lt; #{end}

    </select>

	
</mapper>