Merge remote-tracking branch 'origin/master'

沈斌

2018-01-30 b4cabe178f1a36433270c79048beecfab0edc487

Merge remote-tracking branch 'origin/master'

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

@@ -2,12 +2,11 @@


import static com.moral.common.util.ExportExcelUtils.exportData;

import static com.moral.common.util.WebUtils.getParametersStartingWith;

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



import java.io.OutputStream;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

@@ -18,10 +17,8 @@
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;



import com.moral.common.exception.BusinessException;

import com.moral.common.util.ValidateUtil;

import com.moral.service.DeviceService;

import com.moral.service.HistoryService;

import com.moral.common.bean.ResultBean;

import com.moral.service.DataService;



@RestController

@RequestMapping("report")

@@ -29,47 +26,40 @@
public class ReportController {



    @Resource

    private HistoryService historyService;

    private DataService dataService;

    

    @Resource

    private DeviceService deviceService;



    @GetMapping("sensors-average")

    public Map<String, Object> getSensorsAverageByDevice(HttpServletRequest request,HttpServletResponse response) {

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

        try {

            Map<String, Object> parameters = getParametersStartingWith(request, null);

            Object mac = parameters.get("mac");

            Object time = parameters.get("time");

            Object type = parameters.get("type");

            ValidateUtil.notNull(mac, "param.is.null");

            ValidateUtil.notNull(time, "param.is.null");

            ValidateUtil.notNull(type, "param.is.null");

            List<Map<String, Object>> sensors = deviceService.getSensorsByDevice(mac.toString());

            List<Map<String, Object>> sensorsAverage = historyService.getSensorsAverageByDevice4Report(parameters,sensors);

            if (isEmpty(sensorsAverage)) {

                result.put("msg", "无有效数据");

            } else {

                String[][] exportColumn = new String[sensors.size() + 1][];

                exportColumn[0] = new String[] { "时间", "20", "time" };

                for (int i = 0; i < sensors.size(); i++) {

                    String name = (String) sensors.get(i).get("name");

                    String key = (String) sensors.get(i).get("key");

                    exportColumn[i + 1] = new String[] { name, "10", key };

                }

				
                OutputStream outputStream = exportData(response, time + "_" + mac + "_" + type, sensorsAverage, exportColumn);

                outputStream.flush();

                outputStream.close();

            }

        } catch (BusinessException be) {

            be.printStackTrace();

            result.put("msg", be.getMessage());

        } catch (Exception e) {

            e.printStackTrace();

            result.put("msg", "系统错误，请联系管理员！原因如下："+e.getMessage());

        }

        return result;

    @GetMapping("compare")

    public ResultBean<Map<String, List<Object>>> getCompareReport(HttpServletRequest request) throws Exception {

        Map<String, Object> parameters = getParametersStartingWith(request, null);

        Map<String, List<Object>> demo = dataService.getCompareReport(parameters); 
        return new ResultBean<Map<String,List<Object>>>(demo);

    }



    @GetMapping("excel")

    public ResultBean<Boolean> getExcelReport(HttpServletRequest request,HttpServletResponse response) throws Exception {

        Map<String, Object> parameters = getParametersStartingWith(request, null);

        List<Map<String, Object>> list = dataService.getMonitorPointOrDeviceAvgData4Excel(parameters); 
        String[][] exportColumn =  new String[2][];

        exportColumn[0] = new String[] { "时间", "20", "time" };

        if (parameters.containsKey("sensors")) {

            Set<String> sensors = (Set<String>) parameters.get("sensors");

            exportColumn = new String[sensors.size() + 1][];

            exportColumn[0] = new String[] { "时间", "20", "time" };

            int index = 1;

            for (String sensorKey : sensors) {

                String[] split = sensorKey.split("-");

                String name = split[1];

                String key = split[0];

                exportColumn[index] = new String[] { name, "10", key };

                index++;

            }

        } else {

            exportColumn[1] = new String[] { (String) parameters.get("sensorName"), "10", (String) parameters.get("sensorKey") };

        }

		
        OutputStream outputStream = exportData(response, "Excel数据", list, exportColumn);

        outputStream.flush();

        outputStream.close();

        return new ResultBean<Boolean>(true);

    }

}


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

@@ -29,6 +29,7 @@
import com.moral.common.bean.ResultBean;

import com.moral.common.util.ValidateUtil;

import com.moral.service.AccountService;

import com.moral.service.DataService;

import com.moral.service.DeviceService;

import com.moral.service.HistoryService;

import com.moral.service.MachineActivateService;

@@ -52,7 +53,10 @@
    /** The device service. */

    @Resource

    private DeviceService deviceService;

	


    @Resource

    private DataService dataService;



    @Resource

    private MachineActivateService machineActivateService;



@@ -154,7 +158,7 @@
    @GetMapping("day-aqi")

    public Map<String, Object> getDayAQIByDevice(HttpServletRequest request) {

        Map<String, Object> parameters = getParametersStartingWith(request, null);

        return historyService.getDayAQIByDevice(parameters);

        return dataService.getDayAQIByDevice(parameters);

    }



    /**


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

New file
@@ -0,0 +1,12 @@
package com.moral.mapper;



import java.util.List;

import java.util.Map;



public interface DataMapper{



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



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



}

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

@@ -12,6 +12,12 @@
public interface DeviceMapper extends BaseMapper<Device>{



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

    List<Device>  selectWithRelationData(Example example);

    List<Map<String, Object>> getSensorsByDevice(@Param("mac")String mac);



    List<Device>  selectWithRelationData(Example example);



    List<String> getDeviceMacByMonitorPointAndDeviceVersion(@Param("monitorPointId")Integer monitorPointId,@Param("deviceVersionId")Integer deviceVersionId);



    List<Map<String, Object>> getDeviceVersionIdByMonitorPoint(Integer monitorPointId);





}

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

@@ -5,12 +5,6 @@


public interface HistoryMapper{



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



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



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



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



}

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

@@ -4,7 +4,10 @@
import com.moral.entity.Sensor;

import java.util.List;
import java.util.Map;

public interface SensorMapper extends BaseMapper<Sensor> {
    List<Sensor> selectByVersionId(Integer deviceVersionId);

    List<Map<String, Object>> getSensorsByDeviceVersionId(Integer deviceVersionId);
}

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

New file
@@ -0,0 +1,15 @@
package com.moral.service;



import java.util.List;

import java.util.Map;



public interface DataService {



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

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



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





}


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

@@ -9,8 +9,6 @@


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



    List<Map<String, Object>> getSensorsByDevice(String mac);



    void saveOrUpdateDevice(Device device);



    List<Device> getInstallDevicesByOperateUser(Integer uid, Integer pageIndex, Integer pageSize);


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

@@ -1,6 +1,5 @@
package com.moral.service;



import java.util.List;

import java.util.Map;





@@ -10,9 +9,6 @@


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



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



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



    List<Map<String, Object>> getSensorsAverageByDevice4Report(Map<String, Object> parameters, List<Map<String, Object>> sensorKeys);

}


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

New file
@@ -0,0 +1,440 @@
package com.moral.service.impl;



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.ArrayList;

import java.util.Calendar;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

import java.util.HashMap;

import java.util.HashSet;

import java.util.Iterator;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.Map.Entry;

import java.util.concurrent.Callable;

import java.util.concurrent.CompletionService;

import java.util.concurrent.ExecutorCompletionService;

import java.util.concurrent.ExecutorService;

import java.util.concurrent.Executors;



import javax.annotation.Resource;



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

import org.springframework.stereotype.Service;

import org.springframework.util.ObjectUtils;



import com.moral.common.util.CalculateUtils;

import com.moral.common.util.ResourceUtil;

import com.moral.common.util.ValidateUtil;

import com.moral.entity.Device;

import com.moral.mapper.DataMapper;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.SensorMapper;

import com.moral.service.DataService;



@Service

public class DataServiceImpl implements DataService {



    @Resource

    private DataMapper dataMapper;



    @Resource

    private DeviceMapper deviceMapper;



    @Resource

    private SensorMapper sensorMapper;



    private Set<String> sensorKeys = new HashSet<String>();



    @Override

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

        ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

        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));

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

        return getAQIByDataMap(average);

    }



    private Map<String, Object> getAQIByDataMap(Map<String, Double> average) {

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

        if (isEmpty(average)) {

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

        } else {

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

            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 = CalculateUtils.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;

    }



    public Map<String, List<Object>> getMonitorPointOrDeviceAvgData(Map<String, Object> parameters) throws Exception {

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

        if (ObjectUtils.isEmpty(parameters)) {

            resultMap.put("c", null);

            return resultMap;

        }



        String type = (String) parameters.get("type");

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

        String format = (String) parameters.get("format");

        Date start = DateUtils.parseDate(time, format), end = null ;

        if (parameters.containsKey("timeb")) {

            end = DateUtils.parseDate((String)parameters.get("timeb"), format);

        } else {

            switch (type) {

            case "year":

                end = DateUtils.addYears(start, 1);

                break;

            case "month":

                end = DateUtils.addMonths(start, 1);

                break;

            case "day":

                end = DateUtils.addDays(start, 1);

                break;

            case "hour":

                end = DateUtils.addHours(start, 1);

                break;

            }

			
        }

        parameters.put("start", start);

        parameters.put("end", end);



        String sensorKey = (String) parameters.get("sensorKey");

        String sensorKeyColumn = "AVG(json -> '$." + sensorKey + "[0]') " + sensorKey;

        parameters.put("sensorKeyColumn", sensorKeyColumn);

        List<Object> timeList = new ArrayList<Object>();

        List<Object> dataList = new ArrayList<Object>();



        if (!parameters.containsKey("mac")) {

            Integer monitorPointId = Integer.valueOf((String) parameters.get("monitorPoint"));

            List<Map<String, Object>> deviceVersions = deviceMapper.getDeviceVersionIdByMonitorPoint(monitorPointId);

            ExecutorService threadPool = Executors.newCachedThreadPool();

            CompletionService<List<Map<String, Object>>> cs = new ExecutorCompletionService<List<Map<String, Object>>>(threadPool);

            //List<Future<Long>> list = new ArrayList<Future<Long>>();

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

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

                cs.submit(new Callable<List<Map<String, Object>>>() {

                    @Override

                    public List<Map<String, Object>> call() throws Exception {

                        Integer deviceVersionId = (Integer) map.get("deviceVersionId");

                        List<String> macs = deviceMapper.getDeviceMacByMonitorPointAndDeviceVersion(monitorPointId, deviceVersionId);

                        parameters.put("macs", macs);

                        return dataMapper.getMonitorPointOrDeviceAvgData(parameters);

                    }

                });

            }

            //Future<List<Map<String, Object>>> take = cs.take();

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

                list.addAll(cs.take().get());

            }

            Map<String, Map<String, Number>> result = new LinkedHashMap<String, Map<String, Number>>();

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

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

                if (map.containsKey(sensorKey)) {

                    Double data = (Double) map.get(sensorKey);

                    Map<String, Number> number;

                    Double sum;

                    Integer count;

                    if (result.containsKey(time)) {

                        number = result.get(time);

                        count = 1 + (Integer) number.get("count");

                        sum = data + (Double) number.get("sum");

                    } else {

                        number = new HashMap<String, Number>();

                        count = 1;

                        sum = data;

                    }

                    number.put("count", count);

                    number.put("sum", sum);

                    number.put("avg", sum / count);

                    result.put(time, number);

                }

            }

            Iterator<Entry<String, Map<String, Number>>> iterator= result.entrySet().iterator();

            while(iterator.hasNext()){  
                Entry<String, Map<String, Number>> entry = iterator.next();

                String key = entry.getKey();

                timeList.add(key.substring(key.length() - 2));

                dataList.add(entry.getValue().get("avg"));

            }

			
        } else {

            List<Map<String, Object>> demo1 = dataMapper.getMonitorPointOrDeviceAvgData(parameters);

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

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

                time =time.substring(time.length() - 2);

                timeList.add(time);

                dataList.add(map.get(sensorKey));

            }

        }

        String part = (String) parameters.get("part");

        resultMap.put("time" + part, timeList);

        resultMap.put("data" + part, dataList);

        return resultMap;

    }

	
    @Override

    public Map<String, List<Object>> getCompareReport(Map<String, Object> parameters) throws Exception {

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

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

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

        if (parameters.containsKey("time") && parameters.containsKey("monitorPoint")) {

            parametersA.put("part", "A");

            parametersA.putAll(parameters);

            parametersA.remove("timeb");

        }

        if (parameters.containsKey("timeb") && parameters.containsKey("monitorPointb")) {

            parametersB.putAll(parameters);

            parametersB.put("part", "B");

            parametersB.put("time", parametersB.get("timeb"));

            parametersB.put("monitorPoint", parametersB.get("monitorPointb"));

            parametersB.put("mac", parametersB.get("macb"));

            parametersB.remove("timeb");

        }

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

        list.add(parametersA);

        list.add(parametersB);

        ExecutorService threadPool = Executors.newCachedThreadPool();

        CompletionService<Map<String, List<Object>>> cs = new ExecutorCompletionService<Map<String, List<Object>>>(threadPool);

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

            cs.submit(new Callable<Map<String, List<Object>>>() {

                @Override

                public Map<String, List<Object>> call() throws Exception {

                    return getMonitorPointOrDeviceAvgData(map);

                }

            });

			
        }

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

            resultMap.putAll(cs.take().get());

        }

        if (resultMap.containsKey("timeA") && resultMap.containsKey("timeB")) {

            List<Object> timeA = resultMap.get("timeA");

            List<Object> timeB = resultMap.get("timeB");

            List<Object> time = new ArrayList<Object>(timeA);

            List<Object> time1 = new ArrayList<Object>(timeB);

            List<Object> dataA = resultMap.get("dataA");

            List<Object> dataB = resultMap.get("dataB");

            List<Object> resultA = new ArrayList<Object>();

            List<Object> resultB = new ArrayList<Object>();

            time1.removeAll(time);

            time.addAll(time1);

            time.sort(new Comparator<Object>() {

                @Override

                public int compare(Object o1, Object o2) {

                    return Integer.compare(Integer.valueOf(o1.toString()), Integer.valueOf(o2.toString()));

                }

            });

            for (int i = 0; i < time.size(); i++) {

                Object object = time.get(i);

                int indexA = timeA.indexOf(object);

                if (indexA > -1) {

                    resultA.add(i, dataA.get(indexA));

                }else {

                    resultA.add("-");

                }

                int indexB = timeB.indexOf(object);

                if (indexB > -1) {

                    resultB.add(i, dataB.get(indexB));

                }else {

                    resultB.add("-");

                }

            }

            resultMap.put("dataA", resultA);

            resultMap.put("dataB", resultB);

            resultMap.put("time", time);

        } else if (resultMap.containsKey("timeA")) {

            resultMap.put("time", resultMap.get("timeA"));

        } else {

            resultMap.put("time", resultMap.get("timeB"));

        }

        return resultMap;

    }



	
    @Override

    public List<Map<String, Object>> getMonitorPointOrDeviceAvgData4Excel(Map<String, Object> parameters) throws Exception {

        List<Map<String, Object>> resul ;



        String type = (String) parameters.get("type");

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

        String format = (String) parameters.get("format");

        Date start = DateUtils.parseDate(time, format), end = null ;

        if (parameters.containsKey("timeb")) {

            end = DateUtils.parseDate((String)parameters.get("timeb"), format);

        } else {

            switch (type) {

            case "year":

                end = DateUtils.addYears(start, 1);

                break;

            case "month":

                end = DateUtils.addMonths(start, 1);

                break;

            case "day":

                end = DateUtils.addDays(start, 1);

                break;

            case "hour":

                end = DateUtils.addHours(start, 1);

                break;

            }

			
        }

        parameters.put("start", start);

        parameters.put("end", end);



        if (parameters.containsKey("sensorKey")) {

            String sensorKey = (String) parameters.get("sensorKey");

            String sensorKeyColumn = "AVG(json -> '$." + sensorKey + "[0]') " + sensorKey;

            parameters.put("sensorKeyColumn", sensorKeyColumn);

        }



        if (!parameters.containsKey("mac")) {

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

            Integer monitorPointId = Integer.valueOf((String) parameters.get("monitorPoint"));

            List<Map<String, Object>> deviceVersions = deviceMapper.getDeviceVersionIdByMonitorPoint(monitorPointId);

            ExecutorService threadPool = Executors.newCachedThreadPool();

            CompletionService<List<Map<String, Object>>> cs = new ExecutorCompletionService<List<Map<String, Object>>>(threadPool);

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

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

                cs.submit(new Callable<List<Map<String, Object>>>() {

                    @Override

                    public List<Map<String, Object>> call() throws Exception {

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

                        Integer deviceVersionId = (Integer) map.get("deviceVersionId");

                        if (!parameter.containsKey("sensorKeyColumn")) {

                            List<Map<String, Object>> sensors = sensorMapper.getSensorsByDeviceVersionId(deviceVersionId);

                            String sensorKeyColumn = getSensorKeyColumnBySensors(sensors);

                            parameter.put("sensorKeyColumn", sensorKeyColumn);

                            parameters.put("sensors", sensorKeys);

                        }

                        List<String> macs = deviceMapper.getDeviceMacByMonitorPointAndDeviceVersion(monitorPointId, deviceVersionId);

                        parameter.put("macs", macs);

                        return dataMapper.getMonitorPointOrDeviceAvgData(parameter);

                    }



                });

            }

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

                list.addAll(cs.take().get());

            }

            Map<String, Map<String, Number[]>> result = new LinkedHashMap<String, Map<String, Number[]>>();

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

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

                map.remove("time");

                Map<String, Number[]> times = new HashMap<String, Number[]>();

                if (result.containsKey(time)) {

                    times = (Map<String, Number[]>) result.get(time);

                } 
                for (Map.Entry<String,Object> entry  : map.entrySet()) {

                    String sensorKey = entry.getKey();

                    Number[] doubles = new Number[3];

                    Double sum = (Double) entry.getValue();

                    Integer count = 1;

                    if (times.containsKey(sensorKey)) {

                        doubles = times.get(sensorKey);

                        sum = (Double) doubles[0] + sum;

                        count = (Integer) doubles[1] + 1;

                    } 
                    doubles[0] = sum;

                    doubles[1] = count;

                    doubles[2] = (sum / count);

                    times.put(sensorKey, doubles);

                }

                result.put(time, times);

            }

            Iterator<Entry<String, Map<String, Number[]>>> iterator = result.entrySet().iterator();

            while(iterator.hasNext()){  
                Entry<String, Map<String, Number[]>> entry = iterator.next();

                String resultTime = entry.getKey();

                Map<String, Number[]> value = entry.getValue();

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

                resultMap.put("time", resultTime);

                for (String sensorKey : value.keySet()) {

                    resultMap.put(sensorKey, value.get(sensorKey)[2]);

                }

                resul.add(resultMap);

            }

			
        } else {

            if (!parameters.containsKey("sensorKeyColumn")) {

                String mac = (String) parameters.get("mac");

                Device device = new Device();

                device.setMac(mac);

                device = deviceMapper.selectOne(device);

                List<Map<String, Object>> sensors = sensorMapper.getSensorsByDeviceVersionId(device.getDeviceVersionId());

                String sensorKeyColumn = getSensorKeyColumnBySensors(sensors);

                parameters.put("sensorKeyColumn", sensorKeyColumn);

                parameters.put("sensors", sensorKeys);

				
            }

            resul = dataMapper.getMonitorPointOrDeviceAvgData(parameters);

        }

        return resul;

    }



    private String getSensorKeyColumnBySensors(List<Map<String, Object>> sensors) {

        String sensorKeyColumn = "";

        for (int i = 0; i < sensors.size(); i++) {

            String sensorKey = (String) sensors.get(i).get("sensor_key");

            String name = (String) sensors.get(i).get("name");

            if (i == sensors.size() - 1) {

                sensorKeyColumn += "AVG(json -> '$." + sensorKey + "[0]') " + sensorKey;

            } else {

                sensorKeyColumn += "AVG(json -> '$." + sensorKey + "[0]') " + sensorKey + ",";

            }

            sensorKeys.add(sensorKey + "-" + name);

        }

        return sensorKeyColumn;

    }



}


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

@@ -57,11 +57,6 @@
    }

    

    @Override

    public List<Map<String, Object>> getSensorsByDevice(String mac) {

        return deviceMapper.getSensorsByDevice(mac);

    }



    @Override

    @Transactional

    public void saveOrUpdateDevice(Device device) {

        ValidateUtil.notNull(device, "param.is.null");


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

@@ -1,24 +1,18 @@
package com.moral.service.impl;



import static com.moral.common.bean.Constants.NULL_VALUE;

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

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

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

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

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

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

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



import java.text.ParseException;

import java.util.Calendar;

import java.util.Collections;

import java.util.Date;

import java.util.HashMap;

import java.util.HashSet;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;



import javax.annotation.Resource;



@@ -29,9 +23,6 @@
import org.springframework.stereotype.Service;



import com.mongodb.BasicDBObject;

import com.moral.common.exception.BusinessException;

import com.moral.common.util.CalculateUtils;

import com.moral.common.util.ResourceUtil;

import com.moral.common.util.ValidateUtil;

import com.moral.mapper.HistoryMapper;

import com.moral.service.AccountService;

@@ -47,12 +38,13 @@
    private HistoryMapper historyMapper;



    @Resource

    private MongoTemplate mongoTemplate;

    private MongoTemplate mongoTemplate;





    @Override

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

        ValidateUtil.notNull(parameters.get("areaCode"), "param.is.null");

        ValidateUtil.notNull(parameters.get("accountId"), "param.is.null");		
        ValidateUtil.notNull(parameters.get("accountId"), "param.is.null");

        accountService.setOrgIdsByAccount(parameters);

        Date date = new Date();

        // 当前时间 -10分钟

@@ -77,8 +69,8 @@
    @Override

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

        ValidateUtil.notNull(parameters.get("areaCode"), "param.is.null");

        ValidateUtil.notNull(parameters.get("accountId"), "param.is.null");		
        ValidateUtil.notNull(parameters.get("macKey"), "param.is.null");		
        ValidateUtil.notNull(parameters.get("accountId"), "param.is.null");

        ValidateUtil.notNull(parameters.get("macKey"), "param.is.null");



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

        accountService.setOrgIdsByAccount(parameters);

@@ -86,7 +78,7 @@
        // 当前时间 -1小时

        parameters.put("start", addHours(date, -1));

        parameters.put("end", date);

        parameters.put("macKey", "'$."+ parameters.get("macKey")+"'");

        parameters.put("macKey", "'$." + parameters.get("macKey") + "'");

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);



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

@@ -96,69 +88,9 @@
    }



    @Override

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

        ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

        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", NULL_VALUE);

        } 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 = CalculateUtils.calculateIAQI(maxIAQI, minIAQI, maxMacKey, minMacKey, avg);

                        IAQIs.add(result);

                    }

                }

            }

            IAQIs.remove(null);

            if (isEmpty(IAQIs)) {

                resultMap.put("AQI", NULL_VALUE);

            } 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;

    }



    @Override

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

        ValidateUtil.notNull(parameters.get("mac"), "param.is.null");		
        ValidateUtil.notNull(parameters.get("macKey"), "param.is.null");		
        ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

        ValidateUtil.notNull(parameters.get("macKey"), "param.is.null");

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

        Date date = new Date();

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

@@ -168,14 +100,12 @@
            start = truncate(addMonths(date, -1), Calendar.MONTH).getTime();

        } else {

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

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

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

        }

        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("average")

        );

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

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

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

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

        if (isEmpty(list)) {

@@ -187,37 +117,6 @@
        return result;

    }



    @Override

    public List<Map<String, Object>> getSensorsAverageByDevice4Report(Map<String, Object> parameters,List<Map<String, Object>> sensors) {

        Object type = parameters.get("type");

        if ("hour".equals(type)) {

            parameters.put("type", "%Y-%m-%d %H:00");

        } else if ("minute".equals(type)) {

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

        } else {

            throw new BusinessException("type参数输入错误！");

        }

		
        try {

            Date start = parseDate((String)parameters.get("time"), "yyyy-MM-dd");

            parameters.put("start", start);

            parameters.put("end", addDays(start, 1));

        } catch (ParseException e) {

            e.printStackTrace();

            throw new BusinessException("time参数输入错误！");

        }

        String queryColumns = "";

        for (int i = 0; i < sensors.size(); i++) {

            String sensorKey = (String) sensors.get(i).get("key");

            if (i == sensors.size() - 1) {

                queryColumns += "AVG(value -> '$." + sensorKey + "') " + sensorKey;

            } else {

                queryColumns += "AVG(value -> '$." + sensorKey + "') " + sensorKey +",";

            }

        }

        parameters.put("queryColumns", queryColumns);

		
        return historyMapper.getSensorsAverageByDevice4Report(parameters);

    }

	


}


 src/main/resources/mapper/DataMapper.xml

New file
@@ -0,0 +1,47 @@
<?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.DataMapper">


    <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>

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

        SELECT

            DATE_FORMAT(time, #{typeFormat}) time,

            ${sensorKeyColumn}

        FROM

            data h

        WHERE

            h.time >= #{start}

        AND h.time <![CDATA[<]]> #{end}

        <if test="mac != null">

        AND    h.mac = #{mac}

        </if>

        <if test="mac == null">

        AND    h.mac IN

            <foreach  collection="macs" open="(" separator="," close=")" item="listItem">

                #{listItem}

            </foreach>

		
        </if>

		
        GROUP BY

            DATE_FORMAT(time, #{typeFormat})

        ORDER BY

            time

    </select>

	
</mapper>

 src/main/resources/mapper/DeviceMapper.xml

@@ -98,20 +98,26 @@
        </if>
        GROUP BY d.state
    </select>

    <select id="getSensorsByDevice" resultType="map">
		
    <select id="getDeviceVersionIdByMonitorPoint" resultType="map">
        SELECT
            s.`key`,
            s.`name` 
            device_version_id deviceVersionId
        FROM
            sensor s,
            device d,
            device_version_sensor dvs 
            device 
        WHERE
            d.mac = #{mac}
            AND d.device_version_id = dvs.version_id 
            AND dvs.sensor_id = s.id 
        ORDER BY
            s.id
            monitor_point_id = #{monitorPoint} 
        GROUP BY
            device_version_id
    </select>

    <select id="getDeviceMacByMonitorPointAndDeviceVersion" resultType="string">
        SELECT
            mac
        FROM
            device 
        WHERE
            monitor_point_id = #{monitorPointId} 
        AND device_version_id = #{deviceVersionId}
    </select>
	
</mapper>

 src/main/resources/mapper/HistoryMapper.xml

@@ -1,44 +1,6 @@
<?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.HistoryMapper">
    <select id="getDayAQIByDevice" resultType="map">
        SELECT
            AVG(value -> '$.e1') e1,
            AVG(value -> '$.e2') e2,
            AVG(value -> '$.e10') e10,
            AVG(value -> '$.e11') e11,
            AVG(value -> '$.e15') e15,
            AVG(value -> '$.e16') e16
        FROM
            history
        WHERE
            mac = #{mac}
        AND time >= #{start}
        AND time &lt; #{end}
    </select>

    <select id="getAllSensorAverageByDevice" resultType="java.util.LinkedHashMap">
        SELECT

            ${queryColumns}

        FROM

            history h,

            device d,

            monitor_point mp

        <if test="orgIds != null and orgIds.size &gt; 0">

            LEFT JOIN monitor_point_organization mpo ON mpo.monitor_point_id = mp.id 
            AND mpo.organization_id IN

            <foreach collection="orgIds" open="(" separator="," close=")" item="listItem">

                #{listItem}

            </foreach>

        </if>

        WHERE

            mp.area_code = #{areaCode} 
            AND h.time >= #{start} 
            AND h.time &lt; #{end}

            AND h.mac = d.mac 
            AND d.monitor_point_id = mp.id 
    </select>

	
    <select id="getAreaAllDataByAccount" resultType="java.util.LinkedHashMap">

        SELECT

        <if test="macKey == 'all'">

@@ -70,19 +32,6 @@
        </if>    

    </select>

    

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

        SELECT

            DATE_FORMAT(time, #{type}) time,

            ${queryColumns}

        FROM

            history h

        WHERE

            h.mac = #{mac}

        AND h.time >= #{start}

        AND h.time &lt; #{end}

        GROUP BY

            DATE_FORMAT(time, #{type})

        ORDER BY

            time

    </select>



	
</mapper>

 src/main/resources/mapper/SensorMapper.xml

@@ -23,4 +23,16 @@
       where sen.id = dvs.sensor_id and dvs.device_version_id = #{deviceVersionId,jdbcType=INTEGER}
    )
  </select>
  
      <select id="getSensorsByDeviceVersionId" resultType="java.util.Map">
        SELECT
            s.* 
        FROM
            device_version_sensor dvs,
            sensor s 
        WHERE
            dvs.sensor_id = s.id 
            AND dvs.device_version_id = #{deviceVersionId}		
    </select>
  
</mapper>

 src/main/resources/system/alarmLevels.json

@@ -1,116 +1,181 @@
{
    "e1":{
        "level1":35,
        "level2":115,
        "level3":250,
        "enable":1
    "alarmMode": {
        "enable": 1,
        "level1": [],
        "level2": [],
        "level3": []
    },
    "e2":{
        "level1":50,
        "level2":250,
        "level3":420,
        "enable":1
    },
    "e3":{
        "level1":20000,
        "level2":30000,
        "level3":40000,
        "enable":0
    },
    "e4":{
        "level1":30,
        "level2":75,
        "level3":100,
        "enable":0
    },
    "e5":{
        "level1":0.01,
        "level2":0.02,
        "level3":0.05,
        "enable":0
    },
    "e6":{
        "level1":60,
        "level2":100,
        "level3":160,
        "enable":0
    },
    "e7":{
        "level1":35,
        "level2":30,
        "level3":40,
        "enable":0
    },
    "e8":{
        "level1":30,
        "level2":40,
        "level3":50,
        "enable":0
    },
    "e9":{
        "level1":0.01,
        "level2":0.02,
        "level3":0.03,
        "enable":0
    },
    "e10":{
        "level1":2,
        "level2":14,
        "level3":36,
        "enable":1
    },
    "e11":{
        "level1":50,
        "level2":475,
        "level3":1600,
        "enable":1
    },
    "e12":{
        "level1":10000,
        "level2":15000,
        "level3":20000,
        "enable":0
    },
    "e13":{
        "level1":60,
        "level2":90,
        "level3":120,
        "enable":0
    },
    "e14":{
        "level1":100,
        "level2":200,
        "level3":300,
        "enable":0
    },
    "e15":{
        "level1":160,
        "level2":300,
        "level3":800,
        "enable":1
    },
    "e16":{
        "level1":40,
        "level2":180,
        "level3":565,
        "enable":1
    },
    "e17":{
        "level1":1.5,
        "level2":3,
        "level3":5,
        "enable":0
    },
    "e18":{
        "level1":5,
        "level2":6,
        "level3":8,
        "enable":0
    },
    "e19":{
        "level1":3000,
        "level2":4000,
        "level3":5000,
        "enable":0
    "alarmLevels": {
        "e1": {
            "enable": 1,
            "increment": [
                35,
                115,
                250
            ],
            "degression": null
        },
        "e2": {
            "enable": 1,
            "increment": [
                50,
                250,
                420
            ],
            "degression": null
        },
        "e3": {
            "enable": 0,
            "increment": [
                20000,
                30000,
                40000
            ],
            "degression": null
        },
        "e4": {
            "enable": 0,
            "increment": [
                30,
                75,
                100
            ],
            "degression": null
        },
        "e5": {
            "enable": 0,
            "increment": [
                0.01,
                0.02,
                0.05
            ],
            "degression": null
        },
        "e6": {
            "enable": 0,
            "increment": [
                60,
                100,
                160
            ],
            "degression": null
        },
        "e7": {
            "enable": 0,
            "increment": [
                25,
                30,
                40
            ],
            "degression": null
        },
        "e8": {
            "enable": 0,
            "increment": [
                30,
                40,
                50
            ],
            "degression": null
        },
        "e9": {
            "enable": 0,
            "increment": [
                0.01,
                0.02,
                0.03
            ],
            "degression": null
        },
        "e10": {
            "enable": 1,
            "increment": [
                2,
                14,
                36
            ],
            "degression": null
        },
        "e11": {
            "enable": 1,
            "increment": [
                50,
                475,
                1600
            ],
            "degression": null
        },
        "e12": {
            "enable": 0,
            "increment": [
                10000,
                15000,
                20000
            ],
            "degression": null
        },
        "e13": {
            "enable": 0,
            "increment": [
                60,
                90,
                120
            ],
            "degression": null
        },
        "e14": {
            "enable": 0,
            "increment": [
                100,
                200,
                300
            ],
            "degression": null
        },
        "e15": {
            "enable": 1,
            "increment": [
                160,
                300,
                800
            ],
            "degression": null
        },
        "e16": {
            "enable": 1,
            "increment": [
                40,
                180,
                565
            ],
            "degression": null
        },
        "e17": {
            "enable": 0,
            "increment": [
                1.5,
                3,
                5
            ],
            "degression": null
        },
        "e18": {
            "enable": 0,
            "increment": [
                5,
                6,
                8
            ],
            "degression": null
        },
        "e19": {
            "enable": 0,
            "increment": [
                3000,
                4000,
                5000
            ],
            "degression": null
        }
    }
}