报表优化

xufenglei

2018-03-29 9ed19d9bfc20d9661210c3398077852c9bc2d413

报表优化

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

@@ -7,6 +7,7 @@
import java.io.ByteArrayInputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.LinkedHashMap;

import java.util.List;

@@ -430,8 +431,12 @@
        Map<String, Object> parameters = getParametersStartingWith(request, null);

        parameters.put("type", "month");

        parameters.put("monitorPointId", parameters.remove("monitorPoint"));

        String sensorKey = parameters.remove("sensorKey").toString();

        List<String> sensorKeys = new ArrayList<String>();

        sensorKeys.add(sensorKey);

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> list = historyMinutelyService.getMonitorPointOrDeviceAvgData(parameters);

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

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

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

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


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

@@ -77,14 +77,7 @@
        // 今日00:00:00

        parameters.put("end", localDate);

        String[] macKeys = { "e1", "e2", "e10", "e11", "e15", "e16" };

        String sensorKeyColumn = "";

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

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

            if (i != macKeys.length -1) {

                sensorKeyColumn += " ,";

            } 
        }

        parameters.put("sensorKeyColumn", sensorKeyColumn);

        parameters.put("sensorKeys", Arrays.asList(macKeys));

        Map<String, Double> average = historyMinutelyMapper.getSersionAvgByDevice(parameters);

        return getAQIByDataMap(average);

    }

@@ -280,25 +273,19 @@
        parameters.put("start", start);

        parameters.put("end", end);



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

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

            List<String> sensors;

            try {

                sensors = JSON.parseObject((String)parameters.get("sensors"), new TypeReference<List<String>>() {});

                parameters.put("sensors", new HashSet<String>(sensors));

                List<String> sensorKeys = new ArrayList<String>();

                for (String sensor : sensors) {

                    sensorKeys.add(sensor.split("-")[0]);

                }

                parameters.put("sensorKeys", sensorKeys);

            } catch (Exception e) {

                sensors = new ArrayList<String>();

                sensors.add((String)parameters.get("sensorKey"));

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

            }

            String sensorKeyColumn = "";

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

                String sensorKey = sensors.get(i).split("-")[0];

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

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

                    sensorKeyColumn += " ,";

                } 
				
            }

            parameters.put("sensorKeyColumn", sensorKeyColumn);

        }



        //sensorKeys.clear();

@@ -316,10 +303,10 @@
                    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")) {

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

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

                            String sensorKeyColumn = getSensorKeyColumnBySensors(sensors, parameters);

                            parameter.put("sensorKeyColumn", sensorKeyColumn);

                            List<String> sensorKeys = getSensorKeyColumnBySensors(sensors, parameters);

                            parameter.put("sensorKeys", sensorKeys);

                        }

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

                        if (ObjectUtils.isEmpty(macs) || macs.contains("null") ) {

@@ -373,14 +360,14 @@
        // 设备 平均值    

        } else {

            // 所有传感器

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

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

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

                parameters.put("sensorKeyColumn", sensorKeyColumn);

                List<String> sensorKeys = getSensorKeyColumnBySensors(sensors,parameters);

                parameters.put("sensorKeys", sensorKeys);

                

            }

            resultList = historyMinutelyMapper.getMonitorPointOrDeviceAvgData(parameters);

@@ -388,24 +375,21 @@
        return resultList;

    }



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

        String sensorKeyColumn = "";

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

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

        Set<String> sensorSet = new HashSet<String>();

        List<String> sensorKeys = new ArrayList<String>();

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

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

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

        }

        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 + "-" + unit );

            parameters.put("sensors", sensorKeys);

        for (Map<String, Object> sensor : sensors) {

            String sensorKey = (String) sensor.get("sensor_key");

            String name = (String) sensor.get("name");

            String unit = (String) sensor.get("unit");

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

            sensorKeys.add(sensorKey);

        }

        return sensorKeyColumn;

        parameters.put("sensors", sensorSet);

        return  sensorKeys;

    }



    @Override

@@ -424,9 +408,10 @@
        }

        parameters.put("start", start);

        parameters.put("end", end);

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

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

        parameters.put("sensorKeyColumn", sensorKeyColumn);

        String sensorKey = (String) parameters.remove("macKey");

        List<String> sensorKeys = new ArrayList<String>();

        sensorKeys.add(sensorKey);

        parameters.put("sensorKeys", sensorKeys);



        Map<String, Double> average = historyMinutelyMapper.getSersionAvgByDevice(parameters);

        if (isEmpty(average)) {


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

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



import java.util.ArrayList;

import java.util.Arrays;

import java.util.HashMap;

import java.util.List;

@@ -153,12 +154,27 @@
        Criteria criteria = example.createCriteria();



        criteria.andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andLike("name", "%" + name + "%");

        example.or().andEqualTo("isDelete", Constants.IS_DELETE_FALSE).andCondition("getPY(REPLACE (REPLACE (name,'（',''),'）','')) like ", "%" + name + "%");

        example.or().andEqualTo("isDelete", Constants.IS_DELETE_FALSE)

                .andCondition("getPY(" + getReplaceStr("name") + ") like ", "%" + name + "%");



        List<MonitorPoint> monitorPoints = monitorPointMapper.selectByExample(example);

        return monitorPoints;

    }



    private String getReplaceStr(String name){

        List<String[]> list = new ArrayList<String[]>();

        list.add(new String[]{"（",""});

        list.add(new String[]{"）",""});

        for (String[] string : list) {

            name = replace(name,string[0],string[1]);

        }

        return name;

    }

	
    private  String replace(String name,String fromStr,String toStr){

        return "REPLACE (" + name + ",'" + fromStr + "','" + toStr + "')";

    }



    /**

     *

     * @param idList


 src/main/resources/mapper/HistoryMinutelyMapper.xml

@@ -7,7 +7,9 @@
    </resultMap>

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

        SELECT

            ${sensorKeyColumn}

            <foreach  collection="sensorKeys" separator="," item="sensorKey">

                AVG(json->'$.${sensorKey}[0]') AS '${sensorKey}'

            </foreach>

        FROM

            history_minutely

        WHERE

@@ -19,7 +21,9 @@
    <select id="getMonitorPointOrDeviceAvgData" resultType="java.util.Map">

        SELECT

            DATE_FORMAT(time, #{typeFormat}) time,

            ${sensorKeyColumn}

            <foreach  collection="sensorKeys" separator="," item="sensorKey">

                AVG(json->'$.${sensorKey}[0]') AS '${sensorKey}'

            </foreach>

        FROM

            history_minutely h

        WHERE