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,32 +1,41 @@
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){
        organizationService.deleteByIds(ids);
        ResultBean resultBean = new ResultBean(ResultBean.SUCCESS);
        return resultBean;
    }

    @PostMapping("add-or-modify")
    public ResultBean addOrModify(@RequestBody Organization organization){
        organizationService.addOrModify(organization);
@@ -46,9 +55,17 @@
        Organization organization = organizationService.getOrganizationById(userContext.getOrganizationId());
        return new ResultBean<>(organization);
    }

    @GetMapping("getforg")
    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(), "");

            }

        }

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

        }

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

        outputStream.flush();

        outputStream.close();

        return new ResultBean<Boolean>(true);

                sheet.add(data);

            }

            sheets.add(sheet);

        }

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

        }

        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,20 +25,24 @@
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){
        return sensorService.queryByVersionId(versionId);
    }

    @PostMapping("delete-by-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){
        sensorService.addOrModify(sensor);
@@ -42,4 +56,15 @@
        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 {
@@ -34,7 +39,6 @@
            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);
        }
        return token;
    }

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



    /**

     * 所有下级组织，包括自己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);
@@ -29,4 +30,6 @@
    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

@@ -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,7 +5,6 @@
import java.util.Set;



import com.moral.common.bean.PageBean;

import com.moral.entity.DeviceVersion;

import com.moral.entity.Organization;



public interface OrganizationService {

@@ -20,7 +19,10 @@
    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);

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

@@ -31,6 +29,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 {

@@ -88,6 +88,7 @@
        List<Organization> organizationList = organizationMapper.selectWithAreaNameByExample(example);

        return new PageBean(organizationList);

    }



    @Override

    public void addOrModify(Organization organization){

        try{

@@ -115,11 +116,11 @@
            }else {

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

            }

        }

        catch (Exception ex){

        } catch (Exception ex) {

            throw  ex;

        }

    }



    @Transient

    private void addOrModifyOrgRelation(Organization organization){

        OrganizationRelation relation = new OrganizationRelation();

@@ -156,6 +157,7 @@
                }

        }

    }



    @Override

    public void deleteByIds(Integer... ids) {

        Organization organization = new Organization();

@@ -207,4 +209,10 @@
        }

        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,24 +1,26 @@
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{
    private static Logger log = Logger.getLogger(SensorService.class);
    @Resource
    private SensorUnitMapper sensorUnitMapper;

    @Override
    public boolean addOrModify(SensorUnit sensorUnit) {
        try {
@@ -45,6 +47,7 @@

    /**
     * 根据传感器id 获取 所属传感器数组
     *
     * @param sensorId
     * @return
     */
@@ -58,6 +61,7 @@

    /**
     * 根据传感器单位实体id 删除
     *
     * @param id
     */
    @Override
@@ -71,6 +75,7 @@

    /**
     * 获取传感器单位数组分组
     *
     * @param sensorIds
     * @return
     */
@@ -99,7 +104,6 @@
                String name = (String) sensorMap.get("name");
                sensorUnitMap.put(sensor_key, name);
            }
            System.out.println(sensorUnitMap);
            return sensorUnitMap;
        } else {
            Map<String, Object> sensorUnitMap =  null;
@@ -110,7 +114,6 @@
    @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) {

 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

@@ -44,7 +44,8 @@
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                                    <foreach close=")" collection="criterion.value" item="listItem" open="("
                                             separator=",">
                    #{listItem}
                  </foreach>
                </when>
@@ -103,4 +104,14 @@
    <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>