报表优化

xufenglei

2018-03-05 fbec1c270bfbeac936035cbc9db61d8a1162ccf9

报表优化

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

@@ -1,32 +1,22 @@
package com.moral.controller;



import static com.moral.common.util.RedisUtil.get;

import static com.moral.common.util.RedisUtil.hasKey;

import static com.moral.common.util.ResourceUtil.getValue;

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



import java.io.*;

import java.lang.reflect.InvocationTargetException;

import java.util.*;

import java.io.BufferedReader;

import java.io.ByteArrayInputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.util.HashMap;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import java.util.stream.Collectors;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;



import com.alibaba.fastjson.*;

import com.moral.common.bean.JsonData;

import com.moral.common.bean.PageResult;

import com.moral.common.xml.Version;

import com.moral.entity.Account;

import com.moral.entity.Device;

import com.moral.entity.MapBounds;

import com.moral.entity.MonitorPoint;

import com.moral.entity.alarm.AlarmConfigValue;

import com.moral.entity.alarm.AlarmSensorLevel;

import com.moral.service.*;

import org.apache.commons.beanutils.BeanUtils;

import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.lang.ArrayUtils;

import org.apache.commons.net.ftp.FTPClient;

import org.apache.log4j.Logger;

import org.dom4j.Document;

@@ -35,13 +25,40 @@
import org.springframework.beans.factory.annotation.Value;

import org.springframework.data.redis.core.RedisTemplate;

import org.springframework.security.access.prepost.PreAuthorize;

import org.springframework.ui.Model;

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



import com.moral.common.bean.ResultBean;

import com.moral.common.util.ValidateUtil;

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

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

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

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

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

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

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

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

import org.springframework.web.servlet.ModelAndView;

import org.xml.sax.InputSource;



import com.alibaba.fastjson.JSON;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import com.moral.common.bean.JsonData;

import com.moral.common.bean.PageResult;

import com.moral.common.bean.ResultBean;

import com.moral.common.util.ValidateUtil;

import com.moral.common.xml.Version;

import com.moral.entity.Account;

import com.moral.entity.Device;

import com.moral.entity.MapBounds;

import com.moral.entity.MonitorPoint;

import com.moral.entity.alarm.AlarmConfigValue;

import com.moral.entity.alarm.AlarmSensorLevel;

import com.moral.service.AccountService;

import com.moral.service.AlarmConfigService;

import com.moral.service.AreaService;

import com.moral.service.DeviceService;

import com.moral.service.HistoryMinutelyService;

import com.moral.service.HistoryService;

import com.moral.service.MachineActivateService;

import com.moral.service.MonitorPointService;

import com.moral.service.SensorService;



/**

 * The Class ScreenController.大屏接口

@@ -400,4 +417,11 @@
        jsonData.setExtData(returnMap);

        return jsonData;

    }

	
    @GetMapping("monitor_points")

    public ResultBean<List<MonitorPoint>> getMonitorPointsByOrganizationId(Integer orgId) {

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

        List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsByOrganizationId(orgId);

        return new ResultBean<List<MonitorPoint>>(monitorPoints);

    }

}


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

@@ -22,4 +22,6 @@
    

    List<MonitorPoint> getMonitorPointsByName(String name);

    List<Map<String,String>> queryMonitroPointsState(List<Integer> idList);



    List<MonitorPoint> getMonitorPointsByOrganizationId(Integer orgId);

}


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

@@ -19,6 +19,7 @@
import java.util.Map;

import java.util.Map.Entry;

import java.util.Set;

import java.util.TreeSet;

import java.util.concurrent.Callable;

import java.util.concurrent.CompletionService;

import java.util.concurrent.ExecutorCompletionService;

@@ -35,6 +36,7 @@
import com.moral.common.util.ResourceUtil;

import com.moral.common.util.ValidateUtil;

import com.moral.entity.Device;

import com.moral.entity.Sensor;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.SensorMapper;

@@ -52,6 +54,7 @@
    @Resource

    private SensorMapper sensorMapper;



    // volatile

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



    @Override

@@ -128,29 +131,6 @@
        return resultMap;

    }



    public Map<String, List<Object>> getMonitorPointOrDeviceAvgData4Compare(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;

        }

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

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

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

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

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

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

@@ -231,6 +211,73 @@
        return resultMap;

    }



	
    public Map<String, List<Object>> getMonitorPointOrDeviceAvgData4Compare(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;

        }

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

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

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

        Set<String> sensors ;

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

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

//            Map<String, List<Double>> doubleMap = new HashMap<String, List<Double>>();

//            List<Double> doubles = new ArrayList<Double>();

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

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

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

//                timeList.add(time);

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

//                doubles.add((Double) map.get(sensorKey));

//            }

//            doubleMap.put(sensorKey, doubles);

//            dataList.add(doubleMap);

            Sensor sensor = new Sensor();

            sensor.setSensorKey(sensorKey);

            sensor = sensorMapper.selectOne(sensor );

            sensors = new HashSet<String>();

            sensors.add(sensorKey + "-" + sensor.getName() + "-" + sensor.getUnit());

        } else {

            sensors = new TreeSet<String>(new Comparator<String>() {

                @Override

                public int compare(String o1, String o2) {

                    return Integer.compare(Integer.valueOf(o1.split("-")[0].replace("e", "")), Integer.valueOf(o2.split("-")[0].replace("e", "")));

                }

            });

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

        }

        Map<String, List<Double>> doubleMap = new LinkedHashMap<String, List<Double>>();

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

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

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

            timeList.add(time);

            for (String sensor : sensors) {

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

                String sensorKey = split[0];

                if (map.containsKey(sensorKey)) {

                    List<Double> doubles;

                    if (doubleMap.containsKey(sensor)) {

                        doubles = doubleMap.get(sensor);

                    } else {

                        doubles = new ArrayList<Double>();

                    }

                    doubles.add((Double) map.get(sensorKey));

                    doubleMap.put(sensor, doubles);

					
                }

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

            }

        }

        dataList.add(doubleMap);

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

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

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

        return resultMap;

    }

    

    @Override

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

@@ -364,11 +411,12 @@
        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");

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

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

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

                sensorKeyColumn += " ,";

            } 

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

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

        }

        return sensorKeyColumn;

    }


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

@@ -171,4 +171,14 @@
        }).collect(Collectors.toList());

        return list;

    }



    @Override

    public List<MonitorPoint> getMonitorPointsByOrganizationId(Integer orgId) {

        MonitorPoint monitorPoint = new MonitorPoint();

        monitorPoint.setIsDelete(Constants.IS_DELETE_FALSE);

        if (Constants.isNotSpecialOrgId(orgId)) {

            monitorPoint.setOrganizationId(orgId);

        }

        return monitorPointMapper.select(monitorPoint);

    }

}


 src/main/resources/mapper/HistoryMinutelyMapper.xml

@@ -2,7 +2,7 @@
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.moral.mapper.HistoryMinutelyMapper">


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

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

        SELECT

            ${sensorKeyColumn}

        FROM

@@ -13,7 +13,7 @@
        AND time &lt; #{end}

    </select>

    

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

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

        SELECT

            DATE_FORMAT(time, #{typeFormat}) time,

            ${sensorKeyColumn}