传感器平均数据 报表接口

xufenglei

2017-11-14 91e5d3d85c737b96b2c4a1994e2b861cc083453c

传感器平均数据 报表接口

 pom.xml

@@ -222,7 +222,11 @@
            <artifactId>spring-data-mongodb</artifactId>

            <version>2.0.0.RELEASE</version>

        </dependency>



        <dependency>

            <groupId>net.sourceforge.jexcelapi</groupId>

            <artifactId>jxl</artifactId>

            <version>2.6.12</version>

        </dependency>

    </dependencies>






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

New file
@@ -0,0 +1,84 @@
package com.moral.monitor.controller;



import java.io.OutputStream;

import java.util.HashMap;

import java.util.List;

import java.util.Map;



import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;



import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.util.ObjectUtils;

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

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

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

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



import com.moral.monitor.service.ReportService;

import com.moral.monitor.util.BusinessException;

import com.moral.monitor.util.ExportExcelUtils;

import com.moral.monitor.util.WebUtils;



@RestController

@RequestMapping(value = "report")

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

public class ReportController {



    @Autowired

    ReportService reportService;

	
    @RequestMapping(value = "/sensors-average", method = RequestMethod.GET)

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

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

        try {

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

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

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

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

            if (ObjectUtils.isEmpty(mac) || ObjectUtils.isEmpty(time) || ObjectUtils.isEmpty(type)) {

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

            } else {

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

                    new String[] { "Pm2.5", "10", "e1" }, 
                    new String[] { "Pm10", "20", "e2" },

                    new String[] { "0.1升0.3um量", "30", "e3" }, 
                    new String[] { "0.1升2.5um量", "10", "e4" },

                    new String[] { "甲醛", "10", "e5" },

                    new String[] { "湿度", "10", "e6" },

                    new String[] { "温度", "10", "e7" },

                    new String[] { "氧气(O2)", "10", "e8" },

                    new String[] { "氯气(CL2)", "10", "e9" },

                    new String[] { "一氧化碳(CO)", "10", "e10" },

                    new String[] { "二氧化硫(SO2)", "10", "e11" },

                    new String[] { "光照", "10", "e12" },

                    new String[] { "噪音", "10", "e13" },

                    new String[] { "氨气(NH3)", "10", "e14" },

                    new String[] { "臭氧(O3)", "10", "e15" },

                    new String[] { "二氧化氮(NO2)", "10", "e16" },

                    new String[] { "挥发性有机气体", "10", "e17" },

                    new String[] { "风速", "10", "e18" },

                    new String[] { "二氧化碳(CO2)", "10", "e19" }

                };

                List<Map<String, Object>> sensorsAverage = reportService.getSensorsAverageByEquipment(parameters);

                if (ObjectUtils.isEmpty(sensorsAverage)) {

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

                } else {

                    OutputStream outputStream = ExportExcelUtils.exportData(response, time + "日" + "设备：" + mac + type + "数据", sensorsAverage, exportColumn);

                    outputStream.flush();

                    outputStream.close();

                    result.put("msg", "成功下载");

                }

            }

        } catch (BusinessException be) {

            be.printStackTrace();

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

        } catch (Exception e) {

            e.printStackTrace();

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

        }

        return result;

    }



}


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

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



    String getMacLogByLast(); 



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

}

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

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



import java.util.List;

import java.util.Map;



public interface ReportService {



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



}


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

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



import java.text.ParseException;

import java.util.Date;

import java.util.List;

import java.util.Map;



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

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;



import com.moral.monitor.dao.HistoryEntityMapper;

import com.moral.monitor.service.ReportService;

import com.moral.monitor.util.BusinessException;



@Service

public class ReportServiceImpl implements ReportService {



    @Autowired

    private HistoryEntityMapper historyMapper;



    @Override

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

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

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

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

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

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

        } else {

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

        }

		
        try {

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

            parameters.put("start", start);

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

        } catch (ParseException e) {

            e.printStackTrace();

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

        }

		
        return historyMapper.getSensorsAverageByEquipment(parameters);

    }



}


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

New file
@@ -0,0 +1,106 @@
package com.moral.monitor.util;



import java.io.OutputStream;

import java.io.UnsupportedEncodingException;

import java.util.List;



import javax.servlet.http.HttpServletResponse;



import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import net.sf.json.JSONObject;



@SuppressWarnings("rawtypes")

public class ExportExcelUtils {



    public static OutputStream exportData(HttpServletResponse response, String fileName, List list,String[][] exportColumn)

            throws Exception {



        // 文件名加后缀信

        String excelFileName = fileName + ".xls";



        OutputStream outputStream = response.getOutputStream();

        // 设定输出文件头

        response.setHeader("Content-Type", "application/force-download");

        // 把文件名转码使前台可以显示中文名

        response.setHeader("Content-Disposition", "attachment; filename=\""

                + new String(excelFileName.getBytes("gb2312"), "ISO8859-1")

                + "\"");

        // 生成excel表格

        exportExcel(outputStream,list, getColumn(exportColumn),getName(exportColumn), fileName);



        return outputStream;

    }



    public static void exportExcel(OutputStream os, List list, String[] colTitles, String[] fieldNames, String fileName) throws Exception {

        WritableWorkbook wwb = Workbook.createWorkbook(os);



        createSheet(wwb, fileName, 0, list, colTitles, fieldNames);



        wwb.write();

        wwb.close();

    }



    private static void createSheet(WritableWorkbook wwb, String sheetName, int index, List list, String[] colTitles,

            String[] fieldNames)

            throws RowsExceededException, WriteException, UnsupportedEncodingException {



        WritableSheet ws = wwb.createSheet(sheetName, index);

        // 标题定义样式

        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD);

        WritableCellFormat wcf = new WritableCellFormat(font);

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

            String title = colTitles[i];

            ws.addCell(new Label(i, 0, title.split(",")[0], wcf));

            ws.setColumnView(i, Integer.valueOf(title.split(",")[1]));

        }

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

            }

        }

    }



    /**

     * 获得导出 列标题 和 列宽度

     * 
     * @return

     */

    public static String[] getColumn(String[][] exportColumn) {

        if (exportColumn != null) {

            String[] result = new String[exportColumn.length];

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

                result[i] = exportColumn[i][0] + "," + exportColumn[i][1];

            }

            return result;

        } else {

            return new String[0];

        }

    }



    /**

     * 获得 表针对字段

     * 
     * @return

     */

    public static String[] getName(String[][] exportColumn) {

        if (exportColumn != null) {

            String[] result = new String[exportColumn.length];

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

                result[i] = exportColumn[i][2];

            }

            return result;

        } else {

            return new String[0];

        }

    }



}


 src/main/resources/mapper/HistoryEntityMapper.xml

@@ -292,4 +292,48 @@
    <select id="getMacLogByLast" resultType="string">

        SELECT mac_log FROM logger ORDER BY time DESC LIMIT 1

    </select>



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

        SELECT

            concat(time,':00') AS 'time',

            MAX(CASE WHEN  mac_key='e1' THEN mac_value END) AS 'e1',

            MAX(CASE WHEN  mac_key='e2' THEN mac_value END) AS 'e2',

            MAX(CASE WHEN  mac_key='e3' THEN mac_value END) AS 'e3',

            MAX(CASE WHEN  mac_key='e4' THEN mac_value END) AS 'e4',

            MAX(CASE WHEN  mac_key='e5' THEN mac_value END) AS 'e5',

            MAX(CASE WHEN  mac_key='e6' THEN mac_value END) AS 'e6',

            MAX(CASE WHEN  mac_key='e7' THEN mac_value END) AS 'e7',

            MAX(CASE WHEN  mac_key='e8' THEN mac_value END) AS 'e8',

            MAX(CASE WHEN  mac_key='e9' THEN mac_value END) AS 'e9',

            MAX(CASE WHEN  mac_key='e10' THEN mac_value END) AS 'e10',

            MAX(CASE WHEN  mac_key='e11' THEN mac_value END) AS 'e11',

            MAX(CASE WHEN  mac_key='e12' THEN mac_value END) AS 'e12',

            MAX(CASE WHEN  mac_key='e13' THEN mac_value END) AS 'e13',

            MAX(CASE WHEN  mac_key='e14' THEN mac_value END) AS 'e14',

            MAX(CASE WHEN  mac_key='e15' THEN mac_value END) AS 'e15',

            MAX(CASE WHEN  mac_key='e16' THEN mac_value END) AS 'e16',

            MAX(CASE WHEN  mac_key='e17' THEN mac_value END) AS 'e17',

            MAX(CASE WHEN  mac_key='e18' THEN mac_value END) AS 'e18',

            MAX(CASE WHEN  mac_key='e19' THEN mac_value END) AS 'e19'

        FROM

            (

                SELECT

                    h.mac_key,

                    DATE_FORMAT(time, #{type}) time,

                    AVG(h.mac_value) mac_value

                FROM

                    history h

                WHERE

                    h.mac = #{mac}

                AND h.time >= #{start}

                AND h.time &lt; #{end}

                GROUP BY

                    h.mac_key,

                    DATE_FORMAT(time, #{type})

                ORDER BY

                    DATE_FORMAT(time, #{type})

            ) a

        GROUP BY

            time

    </select>

</mapper>