报表 优化

xufenglei

2018-03-28 530aeddc3a2fc1310f68f6c76c82fb4aa99145b4

报表 优化

 src/main/java/com/moral/common/util/ExportExcelUtils.java

@@ -64,7 +64,9 @@
        for (int i = 0; i < list.size(); i++) {

            JSONObject jsonObject = JSONObject.fromObject(list.get(i));

            for (int j = 0; j < colTitles.length; j++) {

                ws.addCell(new Label(j, i + 1, jsonObject.getString(fieldNames[j])));

                if (jsonObject.containsKey(fieldNames[j])) {

                    ws.addCell(new Label(j, i + 1, jsonObject.getString(fieldNames[j])));

                }

            }

        }

    }


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

@@ -4,6 +4,7 @@
import static com.moral.common.util.WebUtils.getParametersStartingWith;



import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

import java.util.Set;

@@ -12,14 +13,19 @@
import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;



import com.alibaba.fastjson.JSON;

import com.moral.entity.charts.LineChartCriteria;

import com.moral.entity.charts.PairData;

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

import org.springframework.util.ObjectUtils;

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

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



import com.moral.common.bean.ResultBean;

import com.moral.entity.charts.LineChartCriteria;

import com.moral.service.HistoryMinutelyService;



@SuppressWarnings("unchecked")

@RestController

@RequestMapping("report")

@CrossOrigin(origins = "*", maxAge = 3600)

@@ -27,39 +33,36 @@


    @Resource

    private HistoryMinutelyService historyMinutelyService;

	


    @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 = historyMinutelyService.getCompareReport(parameters); 
        return new ResultBean<Map<String,List<Object>>>(demo);

        Map<String, List<Object>> demo = historyMinutelyService.getCompareReport(parameters);

        return new ResultBean<Map<String, List<Object>>>(demo);

    }



    @PostMapping("line-chart")

    public ResultBean <Map<String, List<List<Double>>>> lineChart(@RequestBody LineChartCriteria lineChartCriteria){

        return  new ResultBean<>(historyMinutelyService.queryLineChartDateByCrieria(lineChartCriteria));

    public ResultBean<Map<String, List<List<Double>>>> lineChart(@RequestBody LineChartCriteria lineChartCriteria) {

        return new ResultBean<>(historyMinutelyService.queryLineChartDateByCrieria(lineChartCriteria));

    }



    @GetMapping("excel")

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

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

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

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

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

        }

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

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

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

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

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

            String[] split = sensors.get(index).split("-");

            String name = split[1];

            String key = split[0];

            String unit = split[2];

            if (!ObjectUtils.isEmpty(unit) && !"null".equals(unit) ) {

                name += "-" + unit;

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

        }

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

        outputStream.flush();

        outputStream.close();


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

@@ -429,8 +429,6 @@
    public ResultBean<List<Map<String, Object>>> getMonitorPointOrDeviceAvgData(HttpServletRequest request) throws Exception {

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

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

        parameters.put("format", "yyyy-MM");

        parameters.put("typeFormat", "%Y-%m-%d");

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

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

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


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

@@ -9,6 +9,7 @@
import java.time.temporal.TemporalAdjusters;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Calendar;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

@@ -144,32 +145,29 @@
    public Map<String, List<Object>> getCompareReport(Map<String, Object> parameters) throws Exception {

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

        List<Map<String, Object>> list = JSON.parseObject((String)parameters.remove("items"), new TypeReference<List<Map<String, Object>>>() {});

        Map<String, Object> timeType = JSON.parseObject((String)parameters.remove("timeType"), new TypeReference<Map<String, Object>>() {});

        parameters.putAll(timeType);

        parameters.put("type", parameters.remove("value"));

        

        ExecutorService threadPool = Executors.newCachedThreadPool();

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



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

        parameters.putAll(getElementByType(type));

        Integer timeLength = Integer.valueOf(parameters.remove("timeLength").toString());

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

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

                int timeLength = Integer.valueOf(parameters.get("timeLength").toString());

                String[] formatTime = map.get("formatTime").toString().split("-");

                LocalDate localDate = LocalDate.of(Integer.valueOf(formatTime[0]), Integer.valueOf(formatTime[1]), 1);

                int lengthOfMonth = localDate.lengthOfMonth();

                if (lengthOfMonth > timeLength) {

                    parameters.put("timeLength", lengthOfMonth);

                    timeLength = lengthOfMonth;

                }

            }

        }

        

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

        for (int i = 0; i <Integer.valueOf(parameters.get("timeLength").toString()); i++) {

        for (int i = 0; i < timeLength; i++) {

            timeList.add(i, String.format("%02d", "day".equals(type) || "hour".equals(type) ? i : i + 1));

        }

        parameters.put("timeList", timeList);

        

        ExecutorService threadPool = Executors.newCachedThreadPool();

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

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

            Map<String, Object> map = list.get(i);

            map.put("part", i);

@@ -264,27 +262,20 @@
        List<Map<String, Object>> resultList ;



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

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

            parameters.putAll(getElementByType(type));

        }

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

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

        Integer field = Integer.valueOf(parameters.get("field").toString());

        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;

            }

			
            Calendar instance = Calendar.getInstance();

            instance.setTime(start);

            instance.add(field, 1);

            end = instance.getTime();

        }

        parameters.put("start", start);

        parameters.put("end", end);

@@ -518,4 +509,35 @@
        }

        return  lineChartDatasWithEmpty;

    }

	
    private Map<String, Object> getElementByType(Object type){

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

        switch (type.toString()) {

        case "year":

            resultMap.put("format", "yyyy");

            resultMap.put("typeFormat", "%Y-%m");

            resultMap.put("timeLength", 12);

            resultMap.put("field", Calendar.YEAR);

            break;

        case "month":

            resultMap.put("format", "yyyy-MM");

            resultMap.put("typeFormat", "%Y-%m-%d");

            resultMap.put("timeLength", 28);

            resultMap.put("field", Calendar.MONTH);

            break;

        case "day":

            resultMap.put("format", "yyyy-MM-dd");

            resultMap.put("typeFormat", "%Y-%m-%d %H");

            resultMap.put("timeLength", 24);

            resultMap.put("field", Calendar.DATE);

            break;

        case "hour":

            resultMap.put("format", "yyyy-MM-dd HH");

            resultMap.put("typeFormat", "%Y-%m-%d %H:%i");

            resultMap.put("timeLength", 60);

            resultMap.put("field", Calendar.HOUR);

            break;

        }

        return resultMap;

    }

}


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

@@ -153,7 +153,7 @@
        Criteria criteria = example.createCriteria();



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

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

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



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

        return monitorPoints;