update excel多个工作表导出

ZhuDongming

2020-04-26 dba54bf8c8f9299d61c05bf3a941708ea399c998

update excel多个工作表导出

 pom.xml

@@ -203,6 +203,19 @@
            <artifactId>aviator</artifactId>
            <version>4.2.0</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

    <build>

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

@@ -1,54 +1,71 @@
package com.moral.controller;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
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.PageBean;
import com.moral.common.bean.ResultBean;
import com.moral.entity.Organization;
import com.moral.security.auth.JwtAuthenticationToken;
import com.moral.security.model.UserContext;
import com.moral.service.OrganizationService;
import org.springframework.web.bind.annotation.*;

import java.util.List;

import javax.annotation.Resource;

@RestController
@RequestMapping("organization")
public class OrganizationController {
    @Resource
    OrganizationService organizationService;

    @GetMapping("page-list")
    public PageBean pageList(PageBean pageBean) {
        return organizationService.queryByPageBean(pageBean);
    }

    @PostMapping("delete-by-ids")
    public ResultBean deleteByIds(@RequestBody Integer [] ids){
    public ResultBean deleteByIds(@RequestBody Integer[] ids) {
        organizationService.deleteByIds(ids);
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        return resultBean;
    }

    @PostMapping("add-or-modify")
    public ResultBean addOrModify(@RequestBody Organization organization){
    public ResultBean addOrModify(@RequestBody Organization organization) {
        organizationService.addOrModify(organization);
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        return resultBean;
    }
    

    @GetMapping("list/{name}")
    public ResultBean<List<Organization>> getOrganizationsByName(@PathVariable("name") String name) {
        List<Organization> organizations = organizationService.getOrganizationsByName(name);
        return new ResultBean<List<Organization>>(organizations);
    }
    public ResultBean<List<Organization>> getOrganizationsByName(@PathVariable("name") String name) {
        List<Organization> organizations = organizationService.getOrganizationsByName(name);
        return new ResultBean<List<Organization>>(organizations);
    }

    @GetMapping("get-my-org")
    public ResultBean<Organization> getMyOrganization(JwtAuthenticationToken token){
    public ResultBean<Organization> getMyOrganization(JwtAuthenticationToken token) {
        UserContext userContext = token.getPrincipal();
        Organization organization = organizationService.getOrganizationById(userContext.getOrganizationId());
        return new ResultBean<>(organization);
    }

    @GetMapping("getforg")
    public ResultBean<Organization> getFatherOrg(Integer cid){
    public ResultBean<Organization> getFatherOrg(Integer cid) {
        Organization organization = organizationService.getFatherOrg(cid);
        return new ResultBean<>(organization);
    }

    @GetMapping("organization-list")
    public ResultBean getOrganizationList(String organizationName) {
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        resultBean.setData(organizationService.getOrganizationList(organizationName));
        return resultBean;
    }
}

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

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


import java.io.OutputStream;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;



import javax.annotation.Resource;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;



@@ -20,6 +20,7 @@
import org.springframework.web.bind.annotation.RequestMapping;

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



import com.alibaba.fastjson.JSONObject;

import com.moral.common.bean.ResultBean;

import com.moral.common.util.ParameterUtils;

import com.moral.common.util.WebUtils;

@@ -32,6 +33,9 @@
import com.moral.service.HistoryDailyService;

import com.moral.service.HistoryMinutelyService;

import com.moral.service.MonitorPointService;



import cn.hutool.core.io.IoUtil;

import cn.hutool.poi.excel.ExcelWriter;



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

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

@@ -139,9 +143,26 @@
    public ResultBean<Boolean> getCustomMadeExcelReport(HttpServletRequest request, HttpServletResponse response) throws Exception {

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

        ParameterUtils.getTimeType4Time(parameters);

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

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

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

        String sensorsInfo = parameters.get("sensors").toString();

        String[] sensorsStr = sensorsInfo.split(",");

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

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

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

        for (int index = 0; index < sensorsStr.length; index++) {

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

            String key;

            if (index == 0) {

                key = split[0].replace("\"", "").substring(1);

            } else {

                key = split[0].replace("\"", "");

            }

            String name = split[1].replace("\"", "");

            String Unit = split[2].replace("\"", "");

            sensorKeys.add(key);

            sensorKeysNames.add(name);

            sensorKeysUnits.add(Unit);

        }

        parameters.put("sensorKeys", sensorKeys);

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

        List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsAndDevicesByRegion(parameters);

        for (MonitorPoint m : monitorPoints) {

@@ -152,27 +173,63 @@
        parameters.put("macs", macList);

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

        if (!CollectionUtils.isEmpty(macList)) {

            list = historyMinutelyService.getDevicesAvgDataToExcel(parameters);

            list = historyMinutelyService.getDevicesSensorsAvgDataToExcel(parameters);

        }

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

        Map<Integer, String> mapNew = new LinkedHashMap<>();

        int i = 0;

        for (String key : map.keySet()) {

            if (!("monitorPointName".equals(key) || "name".equals(key))) {

                mapNew.put(i, key);

                i++;

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

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

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

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

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

                for (Map.Entry<String, Object> kv : list.get(j).entrySet()) {

                    if ("monitorPointName".equals(kv.getKey())) {

                        data.put("站点名称(单位:" + sensorKeysUnits.get(i) + ")", kv.getValue());

                    } else if ("name".equals(kv.getKey())) {

                        data.put("设备名称", kv.getValue());

                    } else {

                        String sensorsValue = kv.getValue().toString();

                        JSONObject jsonObject = JSONObject.parseObject(sensorsValue);

                        if (jsonObject != null) {

                            List<Object> sensorsValueList = (List<Object>) jsonObject.get(sensorKeys.get(i));

                            if (sensorsValueList != null) {

                                data.put(kv.getKey(), sensorsValueList.get(0));

                            } else {

                                data.put(kv.getKey(), "");

                            }

                        } else {

                            data.put(kv.getKey(), "");

                        }

                    }

                }

                sheet.add(data);

            }

            sheets.add(sheet);

        }

        String[][] exportColumn = new String[map.size()][];

        exportColumn[0] = new String[]{"站点名称-" + sensorInfo, "30", "monitorPointName"};

        exportColumn[1] = new String[]{"设备名称", "20", "name"};

        for (int index = 0; index < map.size() - 2; index++) {

            exportColumn[index + 2] = new String[]{mapNew.get(index), "20", mapNew.get(index)};

        if (!CollectionUtils.isEmpty(sheets)) {

            ExcelWriter writer = new ExcelWriter(false, sensorKeysNames.get(0));

            writer.write(sheets.get(0), true);

            writer.autoSizeColumnAll();

            writer.setColumnWidth(0, 25);

            writer.setColumnWidth(1, 25);

            if (sheets.size() >= 2) {

                for (int i = 1; i < sheets.size(); i++) {

                    writer.setSheet(sensorKeysNames.get(i));

                    writer.write(sheets.get(i), true);

                    writer.autoSizeColumnAll();

                    writer.setColumnWidth(0, 25);

                    writer.setColumnWidth(1, 25);

                }

            }

            response.setContentType("application/vnd.ms-excel;charset=utf-8");

            String codedFileName = java.net.URLEncoder.encode("Excel数据", "UTF-8");

            response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");

            ServletOutputStream out = response.getOutputStream();

            writer.flush(out, true);

            // 关闭writer，释放内存

            writer.close();

            //此处记得关闭输出Servlet流

            IoUtil.close(out);

        }

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

        outputStream.flush();

        outputStream.close();

        return new ResultBean<Boolean>(true);

        return new ResultBean<>(true);

    }



}


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

@@ -18,8 +18,10 @@
import java.util.List;

import java.util.Map;

import java.util.Optional;

import java.util.Set;

import java.util.stream.Collectors;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;



import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.collections.MapUtils;

@@ -100,9 +102,6 @@
import io.swagger.annotations.ApiImplicitParam;

import io.swagger.annotations.ApiImplicitParams;

import io.swagger.annotations.ApiOperation;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;



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

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

@@ -1069,7 +1068,6 @@
        Map<String, Object> map = list.get(0);

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

        List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);

        System.out.println(sensorUnitList);

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

            for (Map<String, Object> sensorUnitMap : sensorUnitList) {

                if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensor_key"))) {

@@ -1090,8 +1088,6 @@
        String monitor_point_id = (String) parameters.get("monitor_point_id");

        List<Map<String, Object>> list = sensorService.getSensorByMonitorPointId(monitor_point_id);

        List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);

        System.out.println("list:" + list);

        System.out.println("sensorUnitList:" + sensorUnitList);

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

            for (Map<String, Object> sensorUnitMap : sensorUnitList) {

                if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensorKey"))) {

@@ -1306,9 +1302,7 @@
        sensorMap.put("e10", Double.parseDouble(map.get("co").toString()));

        sensorMap.put("e15", Double.parseDouble(map.get("o3").toString()));

        Map<String, Object> StateControl = AQICalculation.hourlyAQI(sensorMap);

        System.out.println("StateControl:" + StateControl);

        Map<String, Object> hourAqi = historyService.gitHourlyAQIByMonitorPointIdAndTimeslot(parameters);

        System.out.println("hourAqi:" + hourAqi);

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

        returnMap.put("MonitorPointAQI", hourAqi.get("AQI"));

        returnMap.put("StateControlAQI", StateControl.get("AQI"));


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

@@ -1,13 +1,23 @@
package com.moral.controller;

import com.moral.common.bean.PageBean;
import com.moral.common.bean.ResultBean;
import com.moral.entity.Sensor;
import com.moral.service.SensorService;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import java.util.List;

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.PageBean;
import com.moral.common.bean.ResultBean;
import com.moral.common.util.StringUtils;
import com.moral.entity.Sensor;
import com.moral.service.SensorService;

@RestController
@RequestMapping("sensor")
@@ -15,31 +25,46 @@
public class SensorController {
    @Resource
    SensorService sensorService;

    @GetMapping("page-list")
    public PageBean pageList(PageBean pageBean) {
        return sensorService.queryByPageBean(pageBean);
    }

    @GetMapping("list-by-vid")
    public PageBean pageListByVersionId(Integer versionId){
    public PageBean pageListByVersionId(Integer versionId) {
        return sensorService.queryByVersionId(versionId);
    }

    @PostMapping("delete-by-ids")
    public ResultBean deleteByIds(@RequestBody Integer [] ids){
    public ResultBean deleteByIds(@RequestBody Integer[] ids) {
        sensorService.deleteByIds(ids);
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        return resultBean;
    }

    @PostMapping("add-or-modify")
    public ResultBean addOrModify(@RequestBody Sensor sensor){
    public ResultBean addOrModify(@RequestBody Sensor sensor) {
        sensorService.addOrModify(sensor);
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        return resultBean;
    }
    

    @GetMapping("all")
    public ResultBean<List<Sensor>> getAllSensors() {
        List<Sensor> sensors = sensorService.getAllSensors();
        List<Sensor> sensors = sensorService.getAllSensors();
        return new ResultBean<List<Sensor>>(sensors);
    }

    @GetMapping("getSenosrsByOrgId")
    public ResultBean<List<Sensor>> getSenosrsByOrgId(Integer organizationId, String regionCode) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("organizationId", organizationId);
        if (!StringUtils.isNullOrEmpty(regionCode)) {
            parameters.put("regionCode", Long.valueOf(regionCode));
        }
        List<Sensor> sensors = sensorService.selectSenosrsByOrgId(parameters);
        return new ResultBean<>(sensors);
    }

}

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

@@ -1,8 +1,14 @@
package com.moral.controller;

import com.moral.entity.auth.AuthToken;
import java.util.Arrays;
import java.util.LinkedHashMap;

import org.apache.commons.codec.binary.Base64;
import org.springframework.http.*;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
@@ -10,8 +16,7 @@
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.client.RestTemplate;

import java.util.Arrays;
import java.util.LinkedHashMap;
import com.moral.entity.auth.AuthToken;

@Controller
public class TokenController {
@@ -24,29 +29,28 @@

        RestTemplate restTemplate = new RestTemplate();
        HttpEntity<String> request = new HttpEntity<String>(getHeadersWithClientCredentials());
        ResponseEntity<Object> response = restTemplate.exchange(AUTH_SERVER_URI+"username="+username+"&password="+password, HttpMethod.POST, request, Object.class);
        LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>)response.getBody();
        ResponseEntity<Object> response = restTemplate.exchange(AUTH_SERVER_URI + "username=" + username + "&password=" + password, HttpMethod.POST, request, Object.class);
        LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) response.getBody();
        AuthToken token = null;
        if(map!=null){
        if (map != null) {
            token = new AuthToken();
            token.setAccess_token((String)map.get("access_token"));
            token.setToken_type((String)map.get("token_type"));
            token.setRefresh_token((String)map.get("refresh_token"));
            token.setExpires_in((Integer)map.get("expires_in"));
            token.setScope((String)map.get("scope"));
            System.out.println(token);
            token.setAccess_token((String) map.get("access_token"));
            token.setToken_type((String) map.get("token_type"));
            token.setRefresh_token((String) map.get("refresh_token"));
            token.setExpires_in((Integer) map.get("expires_in"));
            token.setScope((String) map.get("scope"));
        }
        return token;
    }

    private static HttpHeaders getHeaders(){
    private static HttpHeaders getHeaders() {
        HttpHeaders headers = new HttpHeaders();
        headers.setAccept(Arrays.asList(MediaType.APPLICATION_JSON));
        return headers;
    }

    private static HttpHeaders getHeadersWithClientCredentials(){
        String plainClientCredentials="my-trusted-client:secret";
    private static HttpHeaders getHeadersWithClientCredentials() {
        String plainClientCredentials = "my-trusted-client:secret";
        String base64ClientCredentials = new String(Base64.encodeBase64(plainClientCredentials.getBytes()));
        HttpHeaders headers = getHeaders();
        headers.add("Authorization", "Basic " + base64ClientCredentials);

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

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



import com.moral.entity.charts.DataCondition;

import com.moral.entity.charts.TimePeriod;

import java.util.List;

import java.util.Map;



import org.apache.ibatis.annotations.Param;



import java.util.List;

import java.util.Map;

import com.moral.entity.charts.DataCondition;

import com.moral.entity.charts.TimePeriod;



public interface HistoryMinutelyMapper {



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



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



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

}

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

@@ -3,18 +3,25 @@
import java.util.List;

import java.util.Map;



import org.apache.ibatis.annotations.Param;



import com.moral.common.mapper.BaseMapper;

import com.moral.entity.Organization;



import tk.mybatis.mapper.entity.Example;



public interface OrganizationMapper extends BaseMapper<Organization> {

    List<Organization> selectWithAreaNameByExample(Example example);

    List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);

    List<Organization> selectWithAreaNameByExample(Example example);



    /**

     * 所有下级组织，包括自己id，自己id位置在0

     * @param orgId

     * @return

     */

    List<Integer> selectLowerOrgIds(Integer orgId);

    List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);



    /**

     * 所有下级组织，包括自己id，自己id位置在0

     *

     * @param orgId

     * @return

     */

    List<Integer> selectLowerOrgIds(Integer orgId);



    List<Organization> getOrganizationList(@Param("organizationName") String organizationName);

}

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

@@ -1,11 +1,12 @@
package com.moral.mapper;

import com.moral.common.mapper.BaseMapper;
import com.moral.entity.Sensor;
import org.apache.ibatis.annotations.Param;

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

import org.apache.ibatis.annotations.Param;

import com.moral.common.mapper.BaseMapper;
import com.moral.entity.Sensor;

public interface SensorMapper extends BaseMapper<Sensor> {
    List<Sensor> selectByVersionId(Integer deviceVersionId);
@@ -16,17 +17,19 @@

    List<Sensor> selectByOrgId(Integer organizationId);

    List<Map<String, Object>> getSensorsByDeviceVersionId(Map<String, Object> parameters);
	
    List<Sensor> getSensorsByCriteria(Map<String, Object> parameters);
    
    Map<String,Object> getSensorBySensorKey(@Param("sensor_key")String sensor_key);
    
    List<Map<String,Object>> getSensorByDeviceId(@Param("id")String id);
    List<Map<String, Object>> getSensorsByDeviceVersionId(Map<String, Object> parameters);

    List<Map<String,Object>> getSensorByMonitorPointId(@Param("monitor_point_id")String monitor_point_id);
    List<Sensor> getSensorsByCriteria(Map<String, Object> parameters);

    Map<String, Object> getSensorBySensorKey(@Param("sensor_key") String sensor_key);

    List<Map<String, Object>> getSensorByDeviceId(@Param("id") String id);

    List<Map<String, Object>> getSensorByMonitorPointId(@Param("monitor_point_id") String monitor_point_id);

    List<Sensor> getSensorsByMac(Map<String, Object> parameters);

    List<Sensor> getSensorsInfoByMac(@Param("mac") String mac);

    List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters);
}

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

@@ -10,7 +10,7 @@
public interface HistoryMinutelyService {



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

    


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



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

@@ -19,7 +19,6 @@


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





    Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);



    void convertQueryParam(Map<String, Object> parameters) throws ParseException;

@@ -27,4 +26,6 @@
    List<List<Map<String, Object>>> getSensorData(Map<String, Object> parameters) throws Exception;



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



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

}


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

@@ -5,22 +5,24 @@
import java.util.Set;



import com.moral.common.bean.PageBean;

import com.moral.entity.DeviceVersion;

import com.moral.entity.Organization;



public interface OrganizationService {

    Set<Integer> getChildOrganizationIds(Integer orgId);

    Set<Integer> getChildOrganizationIds(Integer orgId);



    List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);

    List<Organization> getOrganizationsByAreaName(Map<String, Object> parameters);



    public PageBean queryByPageBean(PageBean pageBean);

    public PageBean queryByPageBean(PageBean pageBean);



    public void addOrModify(Organization organization);

    public void addOrModify(Organization organization);



    public void deleteByIds(Integer... ids);

	
    List<Organization> getOrganizationsByName(String name);

    public void deleteByIds(Integer... ids);



    List<Organization> getOrganizationsByName(String name);



    public Organization getOrganizationById(int id);



    public Organization getFatherOrg(int childId);



    List<Organization> getOrganizationList(String organizationName);

}


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

@@ -6,8 +6,6 @@
import com.moral.common.bean.PageBean;
import com.moral.entity.Sensor;

import java.util.List;

public interface SensorService {
    List<Sensor> queryAll();

@@ -37,4 +35,6 @@

    List<Map<String, String>> getSensorsMaps(String mac);

    List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters);

}

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

@@ -27,6 +27,8 @@
import java.util.concurrent.Executors;

import java.util.stream.Collectors;



import javax.annotation.Resource;



import org.apache.commons.collections.CollectionUtils;

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

import org.springframework.stereotype.Service;

@@ -49,8 +51,6 @@
import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.SensorMapper;

import com.moral.service.HistoryMinutelyService;



import javax.annotation.Resource;



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

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

@@ -106,16 +106,13 @@
    public Map<String, Object> getHourAQIByDevice(Map<String, Object> parameters) {

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

        LocalDate localDate = LocalDate.now();

        System.out.println("localDate:" + localDate);

        // 昨日00:00:00

        //parameters.put("start", localDate.minusDays(1));



        Calendar calendar = Calendar.getInstance();

        calendar.setTime(new Date());

        calendar.set(Calendar.HOUR, calendar.get(Calendar.HOUR) - 1);

        System.out.println("++++++" + calendar);

        int hour = calendar.get(Calendar.HOUR) - 1;

        System.out.println("当前小时减一" + hour);



        // 今日00:00:00

        parameters.put("end", localDate);

@@ -595,7 +592,7 @@
                        }

                    }

                }

                if("0°".equals(mapAvg.get("e76")) || "0".equals(mapMin.get("e76")) || "0".equals(mapMax.get("e76")) || "0°".equals(mapAvg.get("e77")) || "0".equals(mapMin.get("e77")) || "0".equals(mapMax.get("e77"))){

                if ("0°".equals(mapAvg.get("e76")) || "0".equals(mapMin.get("e76")) || "0".equals(mapMax.get("e76")) || "0°".equals(mapAvg.get("e77")) || "0".equals(mapMin.get("e77")) || "0".equals(mapMax.get("e77"))) {

                    continue;

                }

                listMapAvg.add(mapAvg);

@@ -614,28 +611,28 @@
        if ("month".equals(parameters.get("type"))) {

            parameters.put("timeUnits", "daily");

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

            String time = parameters.get("time")+"-01T00:00:00";

            String time = parameters.get("time") + "-01T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());

            LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());

            parameters.put("start", start);

            parameters.put("end", end);

            int day=end.getDayOfMonth();

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

            for(int i=0;i<=day-1;i++){

            int day = end.getDayOfMonth();

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

            for (int i = 0; i <= day - 1; i++) {

                timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));

            }

            parameters.put("timeList", timeList);

        }else if("day".equals(parameters.get("type"))){

            String time = parameters.get("time")+"T00:00:00";

        } else if ("day".equals(parameters.get("type"))) {

            String time = parameters.get("time") + "T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime end=value.plusHours(23);

            LocalDateTime end = value.plusHours(23);

            parameters.put("timeUnits", "hourly");

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

            parameters.put("start", time);

            parameters.put("end", end);

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

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

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

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

                timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));

            }

            parameters.put("timeList", timeList);

@@ -643,4 +640,38 @@
        return historyMinutelyMapper.getDevicesAvgDataToExcel(parameters);

    }



    @Override

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

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

            parameters.put("timeUnits", "daily");

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

            String time = parameters.get("time") + "-01T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());

            LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());

            parameters.put("start", start);

            parameters.put("end", end);

            int day = end.getDayOfMonth();

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

            for (int i = 0; i <= day - 1; i++) {

                timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));

            }

            parameters.put("timeList", timeList);

        } else if ("day".equals(parameters.get("type"))) {

            String time = parameters.get("time") + "T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime end = value.plusHours(23);

            parameters.put("timeUnits", "hourly");

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

            parameters.put("start", time);

            parameters.put("end", end);

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

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

                timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));

            }

            parameters.put("timeList", timeList);

        }

        return historyMinutelyMapper.getDevicesSensorsAvgDataToExcel(parameters);

    }



}


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

@@ -378,7 +378,6 @@
            startTime = localDate + " " + startHour + ":00:00";

        }

        Map<String, Object> map = historyMapper.getAVGValueByMacAndTimeslot(mac, startTime, endTime);

        System.out.println(map);

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

        if (map.isEmpty()) {

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

@@ -465,7 +464,6 @@
            startTime = localDate + " " + startHour + ":00:00";

        }

        Map<String, Object> map = historyMapper.getMonitorPointAVGValueByMonitorPointIdAndTimeslot(monitor_point_id, startTime, endTime);

        System.out.println(map);

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

        if (map.isEmpty()) {

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


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

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



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



import java.util.Arrays;

import java.util.HashSet;

import java.util.List;

@@ -32,6 +30,8 @@
import tk.mybatis.mapper.entity.Example;

import tk.mybatis.mapper.entity.Example.Criteria;



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



@Service

public class OrganizationServiceImpl implements OrganizationService {



@@ -41,170 +41,178 @@
    @Resource

    private OrganizationRelationMapper organizationRelationMapper;



    private static Class ENTITY_CLASS = Organization.class;

    private static Class ENTITY_CLASS = Organization.class;



    @Override

    public Set<Integer> getChildOrganizationIds(Integer orgId){

        Set<Integer> orgIds = new HashSet<Integer>();

        orgIds.add(orgId);

        OrganizationRelation relation = new OrganizationRelation();

        relation.setParentId(orgId);

        Organization organization = organizationMapper.selectByPrimaryKey(orgId);

        if (IS_DELETE_FALSE.equals(organization.getIsDelete())) {

            List<OrganizationRelation> organizationRelations = organizationRelationMapper.select(relation);

            for (OrganizationRelation organizationRelation : organizationRelations) {

                Set<Integer> organizationIds = getChildOrganizationIds(organizationRelation.getChildId());

                orgIds.addAll(organizationIds);

            }

        }

        return orgIds;

    public Set<Integer> getChildOrganizationIds(Integer orgId) {

        Set<Integer> orgIds = new HashSet<Integer>();

        orgIds.add(orgId);

        OrganizationRelation relation = new OrganizationRelation();

        relation.setParentId(orgId);

        Organization organization = organizationMapper.selectByPrimaryKey(orgId);

        if (IS_DELETE_FALSE.equals(organization.getIsDelete())) {

            List<OrganizationRelation> organizationRelations = organizationRelationMapper.select(relation);

            for (OrganizationRelation organizationRelation : organizationRelations) {

                Set<Integer> organizationIds = getChildOrganizationIds(organizationRelation.getChildId());

                orgIds.addAll(organizationIds);

            }

        }

        return orgIds;

    }





    @Override

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

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

        List<Organization> organizations = organizationMapper.getOrganizationsByAreaName(parameters);

        return organizations;

    }

    @Override

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

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

        List<Organization> organizations = organizationMapper.getOrganizationsByAreaName(parameters);

        return organizations;

    }



    @Override

    public PageBean queryByPageBean(PageBean pageBean){

        Example example = ExampleUtil.generateExample(ENTITY_CLASS,pageBean);

        List<Example.Criteria> criteriaList = example.getOredCriteria();

        if(criteriaList!=null&&criteriaList.size()>0){

            for(Example.Criteria cri : criteriaList){

                cri.andNotEqualTo("isDelete",Constants.IS_DELETE_TRUE);

            }

        }else {

            example.or().andNotEqualTo("isDelete",Constants.IS_DELETE_TRUE);

        }

        if(pageBean.getPageSize()>0){

            PageHelper.startPage(pageBean.getPageIndex(),pageBean.getPageSize());

        }

        if(example.getOrderByClause() == null || example.getOrderByClause().isEmpty()) {

            example.setOrderByClause("create_time desc");

        }

        List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);

        return new PageBean(organizationList);

    }

    @Override

    public void addOrModify(Organization organization){

        try{

            if(organization.getId()==null){

                //新建数据，默认为未删除状态

                organization.setIsDelete(Constants.IS_DELETE_FALSE);

                organizationMapper.insertSelective(organization);

            }else{

                organizationMapper.updateByPrimaryKey(organization);

            }

            if(organization.getId()!=null && !organization.getId().equals(organization.getParentId())){

                List<Integer> orgIds = organizationMapper.selectLowerOrgIds(organization.getId());

                boolean isChild = orgIds.stream().anyMatch(

                        item -> {

                            return item!=null && item.equals(organization.getParentId());

                        }

                );

                // 非子孙组织才能设为父组织，避免闭环脏数据

                if(!isChild){

                    //更新父组织关系

                    addOrModifyOrgRelation(organization);

                }else{

                    throw new BusinessException("cannot set the children as father,organization:"+ JSON.toJSONString(organization));

                }

            }else {

                throw new BusinessException("id of organization can't equal to it's parentId,organization:"+ JSON.toJSONString(organization));

            }

        }

        catch (Exception ex){

            throw  ex;

        }

    }

    @Transient

    private void addOrModifyOrgRelation(Organization organization){

        OrganizationRelation relation = new OrganizationRelation();

        relation.setChildId(organization.getId());

        List<OrganizationRelation> relations =  organizationRelationMapper.select(relation);

        if(CollectionUtils.isEmpty(relations)){

            if (!ObjectUtils.isEmpty(organization.getParentId())) {

                relation.setParentId(organization.getParentId());

                organizationRelationMapper.insert(relation);

            }

        }else{

                OrganizationRelation relationOfSelect = relations.get(0);

                //当关系发生修改时，更新关系

                if(organization.getParentId() !=null &&

                   !organization.getParentId().equals(relationOfSelect.getParentId())

                    ) {

                    relationOfSelect.setParentId(organization.getParentId());

                    organizationRelationMapper.updateByPrimaryKey(relationOfSelect);

                }else if(organization.getParentId() ==null){

                    //ParentI为null，删除该条记录

                    organizationRelationMapper.deleteByPrimaryKey(relationOfSelect.getId());

    @Override

    public PageBean queryByPageBean(PageBean pageBean) {

        Example example = ExampleUtil.generateExample(ENTITY_CLASS, pageBean);

        List<Example.Criteria> criteriaList = example.getOredCriteria();

        if (criteriaList != null && criteriaList.size() > 0) {

            for (Example.Criteria cri : criteriaList) {

                cri.andNotEqualTo("isDelete", Constants.IS_DELETE_TRUE);

            }

        } else {

            example.or().andNotEqualTo("isDelete", Constants.IS_DELETE_TRUE);

        }

        if (pageBean.getPageSize() > 0) {

            PageHelper.startPage(pageBean.getPageIndex(), pageBean.getPageSize());

        }

        if (example.getOrderByClause() == null || example.getOrderByClause().isEmpty()) {

            example.setOrderByClause("create_time desc");

        }

        List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);

        return new PageBean(organizationList);

    }



    @Override

    public void addOrModify(Organization organization) {

        try {

            if (organization.getId() == null) {

                //新建数据，默认为未删除状态

                organization.setIsDelete(Constants.IS_DELETE_FALSE);

                organizationMapper.insertSelective(organization);

            } else {

                organizationMapper.updateByPrimaryKey(organization);

            }

            if (organization.getId() != null && !organization.getId().equals(organization.getParentId())) {

                List<Integer> orgIds = organizationMapper.selectLowerOrgIds(organization.getId());

                boolean isChild = orgIds.stream().anyMatch(

                        item -> {

                            return item != null && item.equals(organization.getParentId());

                        }

                );

                // 非子孙组织才能设为父组织，避免闭环脏数据

                if (!isChild) {

                    //更新父组织关系

                    addOrModifyOrgRelation(organization);

                } else {

                    throw new BusinessException("cannot set the children as father,organization:" + JSON.toJSONString(organization));

                }

                //删除多余关系

                if(relations.size()>1){

                    relations.remove(0);

                    List<Integer> relationIds = relations.stream().map(

                        element->{

                            return  element.getId();

                        }

                    ).collect(Collectors.toList());

                    Example example = new Example(OrganizationRelation.class);

                    example.or().andIn("id",relationIds);

                    organizationRelationMapper.deleteByExample(example);

                }

        }

    }

    @Override

    public void deleteByIds(Integer... ids) {

        Organization organization = new Organization();

        organization.setIsDelete(Constants.IS_DELETE_TRUE);

        if(ids!=null&&ids.length>0){

            if(ids.length==1){

                organization.setId(ids[0]);

                organizationMapper.updateByPrimaryKeySelective(organization);

            }else{

                Example example = new Example(ENTITY_CLASS);

                example.or().andIn("id", Arrays.asList(ids));

                organizationMapper.updateByExampleSelective(organization,example);

            }

            } else {

                throw new BusinessException("id of organization can't equal to it's parentId,organization:" + JSON.toJSONString(organization));

            }

        } catch (Exception ex) {

            throw ex;

        }

    }



        }

    }

	
    @Override

    public List<Organization> getOrganizationsByName(String name) {

        ValidateUtil.notEmpty(name, "param.is.null");

        Example example = new Example(Organization.class);

        Criteria criteria = example.createCriteria();

    @Transient

    private void addOrModifyOrgRelation(Organization organization) {

        OrganizationRelation relation = new OrganizationRelation();

        relation.setChildId(organization.getId());

        List<OrganizationRelation> relations = organizationRelationMapper.select(relation);

        if (CollectionUtils.isEmpty(relations)) {

            if (!ObjectUtils.isEmpty(organization.getParentId())) {

                relation.setParentId(organization.getParentId());

                organizationRelationMapper.insert(relation);

            }

        } else {

            OrganizationRelation relationOfSelect = relations.get(0);

            //当关系发生修改时，更新关系

            if (organization.getParentId() != null &&

                    !organization.getParentId().equals(relationOfSelect.getParentId())

            ) {

                relationOfSelect.setParentId(organization.getParentId());

                organizationRelationMapper.updateByPrimaryKey(relationOfSelect);

            } else if (organization.getParentId() == null) {

                //ParentI为null，删除该条记录

                organizationRelationMapper.deleteByPrimaryKey(relationOfSelect.getId());

            }

            //删除多余关系

            if (relations.size() > 1) {

                relations.remove(0);

                List<Integer> relationIds = relations.stream().map(

                        element -> {

                            return element.getId();

                        }

                ).collect(Collectors.toList());

                Example example = new Example(OrganizationRelation.class);

                example.or().andIn("id", relationIds);

                organizationRelationMapper.deleteByExample(example);

            }

        }

    }



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

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

    @Override

    public void deleteByIds(Integer... ids) {

        Organization organization = new Organization();

        organization.setIsDelete(Constants.IS_DELETE_TRUE);

        if (ids != null && ids.length > 0) {

            if (ids.length == 1) {

                organization.setId(ids[0]);

                organizationMapper.updateByPrimaryKeySelective(organization);

            } else {

                Example example = new Example(ENTITY_CLASS);

                example.or().andIn("id", Arrays.asList(ids));

                organizationMapper.updateByExampleSelective(organization, example);

            }



        List<Organization> organizations = organizationMapper.selectByExample(example);

        return organizations;

    }

        }

    }



    @Override

    public Organization getOrganizationById(int id) {

        Example example = new Example(ENTITY_CLASS);

        example.or().andEqualTo("id",id);

        List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);

        return organizationList.size()>0?organizationList.get(0):null;

    }

    @Override

    public List<Organization> getOrganizationsByName(String name) {

        ValidateUtil.notEmpty(name, "param.is.null");

        Example example = new Example(Organization.class);

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



        List<Organization> organizations = organizationMapper.selectByExample(example);

        return organizations;

    }



    @Override

    public Organization getOrganizationById(int id) {

        Example example = new Example(ENTITY_CLASS);

        example.or().andEqualTo("id", id);

        List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);

        return organizationList.size() > 0 ? organizationList.get(0) : null;

    }



    @Override

    public Organization getFatherOrg(int childId) {

        OrganizationRelation relation = new OrganizationRelation();

        relation.setChildId(childId);

        List<OrganizationRelation> relations = organizationRelationMapper.select(relation);

        if (!CollectionUtils.isEmpty(relations)){

        if (!CollectionUtils.isEmpty(relations)) {

            OrganizationRelation relationOfSelect = relations.get(0);

            if(relationOfSelect.getParentId()!=null){

            if (relationOfSelect.getParentId() != null) {

                return organizationMapper.selectByPrimaryKey(relationOfSelect.getParentId());

            }

        }

        return null;

    }



    @Override

    public List<Organization> getOrganizationList(String organizationName) {

        List<Organization> organizationList = organizationMapper.getOrganizationList(organizationName);

        return organizationList;

    }

}


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

@@ -1,27 +1,34 @@
package com.moral.service.impl;

import com.moral.common.bean.PageBean;
import com.moral.common.util.MyBatisBaseMapUtil;
import com.moral.entity.Sensor;
import com.moral.mapper.SensorMapper;
import com.moral.service.SensorService;

import org.springframework.stereotype.Service;

import tk.mybatis.mapper.entity.Example;

import javax.annotation.Resource;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Service;

import com.moral.common.bean.PageBean;
import com.moral.common.util.MyBatisBaseMapUtil;
import com.moral.entity.Device;
import com.moral.entity.MonitorPoint;
import com.moral.entity.Sensor;
import com.moral.mapper.SensorMapper;
import com.moral.service.MonitorPointService;
import com.moral.service.SensorService;

import tk.mybatis.mapper.entity.Example;

@Service
public class SensorServiceImpl implements SensorService {
    @Resource
    SensorMapper sensorMapper;

    @Resource
    MonitorPointService monitorPointService;

    private static Class ENTITY_CLASS = Sensor.class;

@@ -142,4 +149,23 @@
        return sensorsMapList;
    }

    @Override
    public List<Sensor> selectSenosrsByOrgId(Map<String, Object> parameters) {
        List<String> macList = new ArrayList<>();
        List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsAndDevicesByRegion(parameters);
        for (MonitorPoint m : monitorPoints) {
            for (Device d : m.getDevices()) {
                macList.add(d.getMac());
            }
        }
        parameters.put("macs", macList);
        List<Sensor> sensors = new ArrayList<>();
        if (!CollectionUtils.isEmpty(macList)) {
            sensors = sensorMapper.selectSenosrsByOrgId(parameters);
        } else {
            sensors = sensorMapper.selectAll();
        }
        return sensors;
    }

}

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

@@ -1,31 +1,33 @@
package com.moral.service.impl;

import com.moral.entity.SensorUnit;
import com.moral.mapper.SensorUnitMapper;
import com.moral.service.SensorService;
import com.moral.service.SensorUnitService;
import org.apache.log4j.Logger;
import org.springframework.data.annotation.Transient;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.moral.entity.SensorUnit;
import com.moral.mapper.SensorUnitMapper;
import com.moral.service.SensorService;
import com.moral.service.SensorUnitService;

@Service
public class SensorUnitServiceImpl implements SensorUnitService{
public class SensorUnitServiceImpl implements SensorUnitService {
    private static Logger log = Logger.getLogger(SensorService.class);
    @Resource
    private SensorUnitMapper sensorUnitMapper;

    @Override
    public boolean addOrModify(SensorUnit sensorUnit) {
        try {
            if(sensorUnit.getId()!=null) {
            if (sensorUnit.getId() != null) {
                sensorUnit.setUpdateTime(new Date());
                sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
            }else{
            } else {
                sensorUnit.setIsDelete(false);
                sensorUnit.setCreateTime(new Date());
                sensorUnitMapper.insertSelective(sensorUnit);
@@ -40,11 +42,12 @@

    @Override
    public List<SensorUnit> queryListBySensorId(int sensorId) {
        return queryListBySensorId(sensorId,false);
        return queryListBySensorId(sensorId, false);
    }

    /**
     * 根据传感器id 获取 所属传感器数组
     *
     * @param sensorId
     * @return
     */
@@ -53,85 +56,85 @@
        SensorUnit sensorUnitQuery = new SensorUnit();
        sensorUnitQuery.setSensorId(sensorId);
        sensorUnitQuery.setIsDelete(isDelete);
        return  sensorUnitMapper.select(sensorUnitQuery);
        return sensorUnitMapper.select(sensorUnitQuery);
    }

    /**
     * 根据传感器单位实体id 删除
     *
     * @param id
     */
    @Override
    public void remove(Integer id) {
         SensorUnit sensorUnit = new SensorUnit();
         sensorUnit.setId(id);
         sensorUnit.setIsDelete(true);
         sensorUnit.setUpdateTime(new Date());
         sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
        SensorUnit sensorUnit = new SensorUnit();
        sensorUnit.setId(id);
        sensorUnit.setIsDelete(true);
        sensorUnit.setUpdateTime(new Date());
        sensorUnitMapper.updateByPrimaryKeySelective(sensorUnit);
    }

    /**
     * 获取传感器单位数组分组
     *
     * @param sensorIds
     * @return
     */
    @Override
    public Map<Integer,List<SensorUnit>> queryGroupSensorBySids(List<Integer> sensorIds){
    public Map<Integer, List<SensorUnit>> queryGroupSensorBySids(List<Integer> sensorIds) {
        SensorUnit sensorUnitQuery = new SensorUnit();
        Map<Integer,List<SensorUnit>> resultListMap = new HashMap<>();
        for(Integer sensorId: sensorIds) {
        Map<Integer, List<SensorUnit>> resultListMap = new HashMap<>();
        for (Integer sensorId : sensorIds) {
            sensorUnitQuery.setSensorId(sensorId);
            sensorUnitQuery.setIsDelete(false);
            List<SensorUnit> sensorUnitList = sensorUnitMapper.select(sensorUnitQuery);
            if(sensorUnitList!=null&&sensorUnitList.size()>0){
                resultListMap.put(sensorId,sensorUnitList);
            if (sensorUnitList != null && sensorUnitList.size() > 0) {
                resultListMap.put(sensorId, sensorUnitList);
            }
        }
        return  resultListMap;
        return resultListMap;
    }

    @Override
    public Map<String, Object> getSensorByMac(String mac) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByDeviceMac(mac);
        if (list!=null && !list.isEmpty()) {
            Map<String, Object> sensorUnitMap = new HashMap<>();
            for(Map<String, Object> sensorMap : list) {
                String sensor_key = (String) sensorMap.get("sensor_key");
                String name = (String) sensorMap.get("name");
                sensorUnitMap.put(sensor_key, name);
            }
            System.out.println(sensorUnitMap);
            return sensorUnitMap;
        } else {
            Map<String, Object> sensorUnitMap =  null;
            return sensorUnitMap;
        }
    }
	
    @Override
    public Map<String, Map<String, Object>> getSensorsByMonitPointId(String id) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
        System.out.println(list);
        if (list!=null && !list.isEmpty()) {
            Map<String, Map<String, Object>> sensorUnitMap = new HashMap<>();
            for(Map<String, Object> sensorMap : list) {
                Map<String, Object> innerMap = new HashMap<>();
                innerMap.put("unit", sensorMap.get("unit"));
                innerMap.put("name", sensorMap.get("name"));
                innerMap.put("rules", sensorMap.get("rules"));
                String sensor_key = (String) sensorMap.get("sensor_key");
                sensorUnitMap.put(sensor_key, innerMap);
            }
            //System.out.println(sensorUnitMap);
            return sensorUnitMap;
        } else {
            Map<String, Map<String, Object>> sensorUnitMap =  null;
            return sensorUnitMap;
        }
    }
    @Override
    public Map<String, Object> getSensorByMac(String mac) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByDeviceMac(mac);
        if (list != null && !list.isEmpty()) {
            Map<String, Object> sensorUnitMap = new HashMap<>();
            for (Map<String, Object> sensorMap : list) {
                String sensor_key = (String) sensorMap.get("sensor_key");
                String name = (String) sensorMap.get("name");
                sensorUnitMap.put(sensor_key, name);
            }
            return sensorUnitMap;
        } else {
            Map<String, Object> sensorUnitMap = null;
            return sensorUnitMap;
        }
    }

    @Override
    public List<Map<String, Object>> getSensorsByMonitPointId2(String id) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
        return list;
    }
    @Override
    public Map<String, Map<String, Object>> getSensorsByMonitPointId(String id) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
        if (list != null && !list.isEmpty()) {
            Map<String, Map<String, Object>> sensorUnitMap = new HashMap<>();
            for (Map<String, Object> sensorMap : list) {
                Map<String, Object> innerMap = new HashMap<>();
                innerMap.put("unit", sensorMap.get("unit"));
                innerMap.put("name", sensorMap.get("name"));
                innerMap.put("rules", sensorMap.get("rules"));
                String sensor_key = (String) sensorMap.get("sensor_key");
                sensorUnitMap.put(sensor_key, innerMap);
            }
            //System.out.println(sensorUnitMap);
            return sensorUnitMap;
        } else {
            Map<String, Map<String, Object>> sensorUnitMap = null;
            return sensorUnitMap;
        }
    }

    @Override
    public List<Map<String, Object>> getSensorsByMonitPointId2(String id) {
        List<Map<String, Object>> list = sensorUnitMapper.getSensorsByMonitPointId(id);
        return list;
    }
}

 src/main/resources/mapper/HistoryMinutelyMapper.xml

@@ -213,4 +213,33 @@
        order by rs.monitorPointName

    </select>



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

        select

        rs.monitorPointName,rs.name,

        <foreach collection="timeList" separator="," item="time">

            max(case time when #{time} then rs.json else "" end) as #{time}

        </foreach>

        from

        (SELECT

        m.name as monitorPointName,d.name,DATE_FORMAT(time, #{typeFormat}) time,h.json

        FROM

        history_${timeUnits} h,

        device d,

        monitor_point m

        WHERE

        h.mac=d.mac

        AND d.monitor_point_id=m.id

        AND h.time >= #{start}

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

        AND h.mac in

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

                 item="mac">

            #{mac}

        </foreach>

        ORDER BY

        h.mac) rs

        GROUP BY rs.monitorPointName,rs.name

        order by rs.monitorPointName

    </select>



</mapper>

 src/main/resources/mapper/OrganizationMapper.xml

@@ -1,106 +1,117 @@
<?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.OrganizationMapper">
  <resultMap id="BaseResultMap" type="com.moral.entity.Organization">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="rank" jdbcType="INTEGER" property="rank" />
    <result column="province_code" jdbcType="INTEGER" property="provinceCode" />
    <result column="city_code" jdbcType="INTEGER" property="cityCode" />
    <result column="area_code" jdbcType="INTEGER" property="areaCode" />
    <result column="town_code" jdbcType="BIGINT" property="townCode" />
    <result column="village_code" jdbcType="BIGINT" property="villageCode" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="telephone" jdbcType="VARCHAR" property="telephone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="is_delete" jdbcType="CHAR" property="isDelete" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="expire_time" jdbcType="TIMESTAMP" property="expireTime" />
    <result column="description" jdbcType="VARCHAR" property="description" />
      <!-- 与省市县一对一的关系 -->
      <association property="areaNames" javaType="com.moral.entity.AreaNames">
          <result column="province_name" property="provinceName" jdbcType="VARCHAR" />
          <result column="city_name" property="cityName" jdbcType="VARCHAR" />
          <result column="area_name" property="areaName" jdbcType="VARCHAR" />
          <result column="town_name" property="townName" jdbcType="VARCHAR" />
          <result column="village_name" property="villageName" jdbcType="VARCHAR" />
      </association>
  </resultMap>
  <sql id="Example_Where_Clause">
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
    <resultMap id="BaseResultMap" type="com.moral.entity.Organization">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="rank" jdbcType="INTEGER" property="rank"/>
        <result column="province_code" jdbcType="INTEGER" property="provinceCode"/>
        <result column="city_code" jdbcType="INTEGER" property="cityCode"/>
        <result column="area_code" jdbcType="INTEGER" property="areaCode"/>
        <result column="town_code" jdbcType="BIGINT" property="townCode"/>
        <result column="village_code" jdbcType="BIGINT" property="villageCode"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="telephone" jdbcType="VARCHAR" property="telephone"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="is_delete" jdbcType="CHAR" property="isDelete"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="expire_time" jdbcType="TIMESTAMP" property="expireTime"/>
        <result column="description" jdbcType="VARCHAR" property="description"/>
        <!-- 与省市县一对一的关系 -->
        <association property="areaNames" javaType="com.moral.entity.AreaNames">
            <result column="province_name" property="provinceName" jdbcType="VARCHAR"/>
            <result column="city_name" property="cityName" jdbcType="VARCHAR"/>
            <result column="area_name" property="areaName" jdbcType="VARCHAR"/>
            <result column="town_name" property="townName" jdbcType="VARCHAR"/>
            <result column="village_name" property="villageName" jdbcType="VARCHAR"/>
        </association>
    </resultMap>
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="("
                                             separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
        </where>
    </sql>
    <sql id="Base_Column_List">
    id, name, rank, province_code, city_code, area_code, address, telephone, email, is_delete, 
    create_time, expire_time, description
  </sql>
   <sql id="Relation_Province_City_Area_List">
    <sql id="Relation_Province_City_Area_List">
       are.area_name, cti.city_name, pro.province_name,tow.town_name,vil.village_name
  </sql>
  <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
    select
    <if test="distinct">
    <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
        select
        <if test="distinct">
      distinct
    </if>
      organization.*
      ,
      <include refid="Relation_Province_City_Area_List" />
      from organization
      left join village vil on organization.village_code = vil.village_code
      left join town tow on organization.town_code = tow.town_code
      left join area are on organization.area_code = are.area_code
      left join city cti on organization.city_code = cti.city_code
      left join province pro on organization.province_code = pro.province_code
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
        organization.*
        ,
        <include refid="Relation_Province_City_Area_List"/>
        from organization
        left join village vil on organization.village_code = vil.village_code
        left join town tow on organization.town_code = tow.town_code
        left join area are on organization.area_code = are.area_code
        left join city cti on organization.city_code = cti.city_code
        left join province pro on organization.province_code = pro.province_code
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
        <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
      <select id="getOrganizationsByAreaName" resultType="com.moral.entity.Organization">
        SELECT
            o.*
        FROM
            organization o,
            area a
        <if test="cityName != null">
    </select>
    <select id="getOrganizationsByAreaName" resultType="com.moral.entity.Organization">
        SELECT
        o.*
        FROM
        organization o,
        area a
        <if test="cityName != null">
            ,city c
        </if>
        WHERE
            o.area_code = a.area_code
            AND a.area_name = #{areaName}
        <if test="cityName != null">
        WHERE
        o.area_code = a.area_code
        AND a.area_name = #{areaName}
        <if test="cityName != null">
            AND c.city_name = #{cityName}
            AND o.city_code = c.city_code
        </if>
    </select>
    </select>
    <select id="selectLowerOrgIds" resultType="java.lang.Integer">
            call proc_lowerOrgIds_GetByOrgId(#{orgId,jdbcType=INTEGER});
    </select>

    <select id="getOrganizationList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from organization
        where is_delete=0
        <if test="organizationName != 'null'">
            and name like concat('%',#{organizationName},'%')
        </if>
    </select>
</mapper>

 src/main/resources/mapper/SensorMapper.xml

@@ -174,4 +174,28 @@
            AND d.mac = #{mac}
        </if>
    </select>

    <select id="selectSenosrsByOrgId" resultMap="BaseResultMap">
        select
        distinct dvs.sensor_id as id,
        s.name,s.sensor_key,s.unit,s.description
        from
        device_version_sensor dvs,
        sensor s
        where
        dvs.device_version_id
        in(
        select
        distinct d.device_version_id
        from
        device d
        where
        d.mac in
        <foreach collection="macs" open="(" separator="," close=")"
                 item="mac">
            #{mac}
        </foreach>
        )
        and dvs.sensor_id=s.id
    </select>
</mapper>