add大屏报表接口和走航车轨迹接口

ZhuDongming

2020-04-13 14753bc669a106315717f0f4a70bf2bf5c3e8995

add大屏报表接口和走航车轨迹接口

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

@@ -18,6 +18,7 @@
import java.util.List;

import java.util.Map;

import java.util.Optional;

import java.util.Set;

import java.util.stream.Collectors;



import org.apache.commons.collections.CollectionUtils;

@@ -1339,4 +1340,66 @@
        return new ResultBean<List<Map<String, Object>>>(returnList);

    }



    @GetMapping("/carTrajectory")

    @ApiOperation(value = "显示走航车轨迹", notes = "显示走航车轨迹")

    @ApiImplicitParams(value = {

            @ApiImplicitParam(name = "mac", value = "设备mac（格式：p5dnd7a0392082）", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "startTime", value = "开始时间(格式：2020-03-19-14)", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "endTime", value = "结束时间(格式：2020-03-19-17)", required = true, paramType = "query", dataType = "String")

    })

    public ModelAndView carTrajectory(ModelAndView model, HttpServletRequest request) throws Exception {

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

        String st = parameters.get("startTime").toString();

        String startYearAndDay = st.substring(0, st.lastIndexOf("-"));

        String startHour = st.substring(st.lastIndexOf("-") + 1);

        String startTime = startYearAndDay + " " + startHour + ":00:00";

        String et = parameters.get("endTime").toString();

        String endYearAndDay = et.substring(0, et.lastIndexOf("-"));

        String endHour = et.substring(et.lastIndexOf("-") + 1);

        String endTime = endYearAndDay + " " + endHour + ":00:00";

        parameters.put("startTime", startTime);

        parameters.put("endTime", endTime);

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

        if (mac != null && mac.length() != 0) {

            List<List<Map<String, Object>>> sensorData = historyService.getCarSensorData(parameters);

            Map<String, String> sensorsMap = sensorService.getSensorsMap(parameters);

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

            if (sensorData.size() == 3) {

                sensorList = sensorData.get(0);

            }

            JSONObject params = new JSONObject();

            params.put("sensorInfo", sensorList);

            params.put("sensorsMap", sensorsMap);

            String paramsJson = params.toJSONString();

            model.addObject("carTrajectoryParams", paramsJson);

            model.setViewName("cartrajectory");

            return model;

        } else {

            StringBuilder msg = new StringBuilder();

            msg.append(" param[0] mac:");

            msg.append(mac);

            log.warn(msg);

            model.setViewName("403");

            return model;

        }

    }



    @GetMapping("device-excel")

    @ApiOperation(value = "获取设备数据报表", notes = "获取设备数据报表")

    @ApiImplicitParams(value = {

            @ApiImplicitParam(name = "sensorKey", value = "传感器key", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "organizationId", value = "登录账号的组织id(如99)", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "time", value = "时间(格式：2020-04-09或2019-04)", required = true, paramType = "query", dataType = "String")})

    public ResultBean<List<Map<String, Object>>> getDevicesDataToExcel(HttpServletRequest request) throws Exception {

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

        ParameterUtils.getTimeType4Time(parameters);

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

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

        Set<Integer> organizationIds = organizationService.getChildOrganizationIds(Integer.valueOf(organizationId.toString()));

        List<String> macList = deviceService.getMacsByOrganizationId(new ArrayList<>(organizationIds));

        parameters.put("macs", macList);

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

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

    }



}


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

@@ -42,4 +42,6 @@


    List<Device> getDeviceList(Example example);

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



    List<String> getMacsByOrganizationId(@Param("organizationIdList") List<Object> organizationIdList);

}

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

@@ -28,4 +28,5 @@
    

    Map<String, Object> getMonitorPointAVGValueByMonitorPointIdAndTimeslot(@Param("monitor_point_id")String monitor_point_id, @Param("starttime")String starttime, @Param("endtime")String endtime);



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

}

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

@@ -26,4 +26,5 @@


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



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

}

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

@@ -59,4 +59,6 @@
    String getLimitDataByDevice(Map<String, Object> parameters);



    Device getDeviceByLongitudeAsc(String mac);



    List<String> getMacsByOrganizationId(List<Object> organizationIdList);

}


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

@@ -26,4 +26,5 @@


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



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

}


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

@@ -29,4 +29,5 @@
    

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



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

}


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

@@ -631,4 +631,9 @@
        }

        return null;

    }



    @Override

    public List<String> getMacsByOrganizationId(List<Object> organizationIdList) {

        return deviceMapper.getMacsByOrganizationId(organizationIdList);

    }

}


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

@@ -555,7 +555,6 @@
        String startTime = parameters.get("startTime").toString();

        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        LocalDateTime startTimeLocalDateTime = LocalDateTime.parse(startTime, dateTimeFormatter);

        LocalDate time = LocalDate.now();

        int year = startTimeLocalDateTime.getYear();

        int month = startTimeLocalDateTime.getMonthValue();

        String monthStr = month < 10 ? ("0" + month) : month + "";

@@ -596,7 +595,7 @@
                        }

                    }

                }

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

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

@@ -610,4 +609,27 @@
        return listMaps;

    }



    @Override

    public List<Map<String, Object>> getDevicesAvgDataToExcel(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);

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

            parameters.put("start", time);

            parameters.put("end", end);

        }

        return historyMinutelyMapper.getDevicesAvgDataToExcel(parameters);

    }



}


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

@@ -1,11 +1,13 @@
package com.moral.service.impl;



import java.math.BigDecimal;

import java.text.SimpleDateFormat;

import java.time.Instant;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.ZoneId;

import java.time.ZoneOffset;

import java.time.format.DateTimeFormatter;

import java.time.temporal.ChronoUnit;

import java.time.temporal.TemporalAdjusters;

import java.util.ArrayList;

@@ -21,6 +23,7 @@


import javax.annotation.Resource;



import org.apache.commons.collections.CollectionUtils;

import org.springframework.stereotype.Service;

import org.springframework.util.ObjectUtils;



@@ -34,6 +37,7 @@
import com.moral.entity.Sensor;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.HistoryMapper;

import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.MonitorPointMapper;

import com.moral.mapper.OrganizationRelationMapper;

import com.moral.mapper.SensorMapper;

@@ -46,430 +50,497 @@
@Service

public class HistoryServiceImpl implements HistoryService {



    @Resource

    private AccountService accountService;

    @Resource

    private AccountService accountService;



    @Resource

    private HistoryMapper historyMapper;

    @Resource

    private HistoryMapper historyMapper;



    @Resource

    private DeviceMapper deviceMapper;

    @Resource

    private HistoryMinutelyMapper historyMinutelyMapper;



    @Resource

    private SensorMapper sensorMapper;

    @Resource

    private DeviceMapper deviceMapper;



    @Resource

    private SensorMapper sensorMapper;



    @Resource

    private ProfessionService professionService;

    


    @Resource

    private OrganizationRelationMapper organizationRelationMapper;

    


    @Resource

    private MonitorPointMapper monitorPointMapper;



    @Override

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

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

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

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

        accountService.setOrgIdsByAccount(parameters);

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -10分钟

        parameters.put("start", time.minusMinutes(10));

        // 当前时间 -5分钟

        parameters.put("end", time.minusMinutes(5));

        List<Sensor> Sensors = sensorMapper.getSensorsByCriteria(parameters);

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

        for (Sensor sensor : Sensors) {

            sensorKeys.add(sensor.getSensorKey());

        }

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);

        resultMap.putAll(list.get(0));

        return resultMap;

    }

    @Override

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

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

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

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

        accountService.setOrgIdsByAccount(parameters);

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -10分钟

        parameters.put("start", time.minusMinutes(10));

        // 当前时间 -5分钟

        parameters.put("end", time.minusMinutes(5));

        List<Sensor> Sensors = sensorMapper.getSensorsByCriteria(parameters);

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

        for (Sensor sensor : Sensors) {

            sensorKeys.add(sensor.getSensorKey());

        }

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);

        resultMap.putAll(list.get(0));

        return resultMap;

    }



    @Override

    public Map<String, Object> getDeviceRankingBySensorAverage(Map<String, Object> parameters) {

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

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

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

        ValidateUtil.notNull(macKey, "param.is.null");

    @Override

    public Map<String, Object> getDeviceRankingBySensorAverage(Map<String, Object> parameters) {

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

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

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

        ValidateUtil.notNull(macKey, "param.is.null");



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

        accountService.setOrgIdsByAccount(parameters);

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -1小时

        parameters.put("start", time.minusHours(1));

        parameters.put("end", time);

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

        sensorKeys.add(macKey.toString());

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);

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

        accountService.setOrgIdsByAccount(parameters);

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -1小时

        parameters.put("start", time.minusHours(1));

        parameters.put("end", time);

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

        sensorKeys.add(macKey.toString());

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> list = historyMapper.getAreaAllDataByAccount(parameters);



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

            result.put((String) map.get("name"), map.get(macKey.toString()));

        }

        return result;

    }

    @Override

    public String queryValueByMacAndTime(String mac, Date time){

        return  historyMapper.selectValueByMacAndTime(mac, time);

    }

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

            result.put((String) map.get("name"), map.get(macKey.toString()));

        }

        return result;

    }



    @Override

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

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

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

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

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

    @Override

    public String queryValueByMacAndTime(String mac, Date time) {

        return historyMapper.selectValueByMacAndTime(mac, time);

    }



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

        parameters.put("provinceCode", regionCode.substring(0,2).concat("0000"));

        String regionType = "village";

        String nextLevel = "";

        if (regionCode.length() == 6) {

            if (regionCode.endsWith("0000")) {

                regionType = "province";

                nextLevel = "city";

            } else if (regionCode.endsWith("00")) {

                regionType = "city";

                nextLevel = "area";

            } else {

                regionType = "area";

                nextLevel = "town";

            }

        } else {

            if (regionCode.endsWith("000")) {

                regionType = "town";

                nextLevel = "village";

            }

        }

        parameters.put("regionType", regionType);

        parameters.put("nextLevel", nextLevel);

    @Override

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

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

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

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

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



        LocalDate localDate = LocalDate.now();

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

        String table = "history";

        String column = "value";

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

            if (1 != localDate.getDayOfMonth()) {

                table = "history_daily";

                column = "json";

            }

            parameters.put("start", localDate.with(TemporalAdjusters.firstDayOfMonth()));

        } else if ("day".equals(timeType)) {

            parameters.put("start", localDate);

        } else if ("hour".equals(timeType)) {

            parameters.put("start", LocalDateTime.now().truncatedTo(ChronoUnit.HOURS));

        } else {

            parameters.put("start", LocalDateTime.now().minusMinutes(5));

        }

        parameters.put("table", table);

        parameters.put("column", column);

		
        String dimension = "";

        if ("discharge".equals(parameters.get("dimension"))) {

            dimension = "discharge";

            parameters.put("dimension","monitorPoint");

        }

		
        accountService.setOrgIdsByAccount(parameters);

		
        List<Map<String, Object>> result = historyMapper.getRegionRankingData(parameters);

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

            if (ObjectUtils.isEmpty(map.get("avg"))) {

                map.put("avg", 0);

            }

        }

        if ("profession".equals(parameters.get("dimension"))) {

            List<Profession> professions = professionService.getProfessiontList(parameters);

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

                for (Profession profession : professions) {

                    if (map.get("name").equals(profession.getName())) {

                        professions.remove(profession);

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

        parameters.put("provinceCode", regionCode.substring(0, 2).concat("0000"));

        String regionType = "village";

        String nextLevel = "";

        if (regionCode.length() == 6) {

            if (regionCode.endsWith("0000")) {

                regionType = "province";

                nextLevel = "city";

            } else if (regionCode.endsWith("00")) {

                regionType = "city";

                nextLevel = "area";

            } else {

                regionType = "area";

                nextLevel = "town";

            }

        } else {

            if (regionCode.endsWith("000")) {

                regionType = "town";

                nextLevel = "village";

            }

        }

        parameters.put("regionType", regionType);

        parameters.put("nextLevel", nextLevel);



        LocalDate localDate = LocalDate.now();

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

        String table = "history";

        String column = "value";

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

            if (1 != localDate.getDayOfMonth()) {

                table = "history_daily";

                column = "json";

            }

            parameters.put("start", localDate.with(TemporalAdjusters.firstDayOfMonth()));

        } else if ("day".equals(timeType)) {

            parameters.put("start", localDate);

        } else if ("hour".equals(timeType)) {

            parameters.put("start", LocalDateTime.now().truncatedTo(ChronoUnit.HOURS));

        } else {

            parameters.put("start", LocalDateTime.now().minusMinutes(5));

        }

        parameters.put("table", table);

        parameters.put("column", column);



        String dimension = "";

        if ("discharge".equals(parameters.get("dimension"))) {

            dimension = "discharge";

            parameters.put("dimension", "monitorPoint");

        }



        accountService.setOrgIdsByAccount(parameters);



        List<Map<String, Object>> result = historyMapper.getRegionRankingData(parameters);

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

            if (ObjectUtils.isEmpty(map.get("avg"))) {

                map.put("avg", 0);

            }

        }

        if ("profession".equals(parameters.get("dimension"))) {

            List<Profession> professions = professionService.getProfessiontList(parameters);

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

                for (Profession profession : professions) {

                    if (map.get("name").equals(profession.getName())) {

                        professions.remove(profession);

//                        if (ObjectUtils.isEmpty(map.get("avg"))) {

//                            map.put("avg", 0);

//                        }

                        break;

                    }

                }

            }

            for (Profession profession : professions) {

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

                map.put("name", profession.getName());

                map.put("avg", 0);

                result.add(map);

            }

        } else if ("region".equals(parameters.get("dimension"))) {

            List<String> regions = historyMapper.selectNextLevelRegion(parameters);

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

                if (regions.contains(map.get("name"))) {

                    regions.remove(map.get("name"));

                        break;

                    }

                }

            }

            for (Profession profession : professions) {

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

                map.put("name", profession.getName());

                map.put("avg", 0);

                result.add(map);

            }

        } else if ("region".equals(parameters.get("dimension"))) {

            List<String> regions = historyMapper.selectNextLevelRegion(parameters);

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

                if (regions.contains(map.get("name"))) {

                    regions.remove(map.get("name"));

//                    if (ObjectUtils.isEmpty(map.get("avg"))) {

//                        map.put("avg", 0);

//                    }

                }

				
            }

            for (String region : regions) {

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

                map.put("name", region);

                map.put("avg", 0);

                result.add(map);

            }

        }

        if ("discharge".equals(dimension)) {

            parameters.put("year", LocalDate.now().getYear());

            List<Map<String, Object>> discharges = historyMapper.getDischargeByRegion(parameters);

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

                for (Map<String, Object> discharge : discharges) {

                    if (map.get("name").equals(discharge.get("name"))) {

                        map.put("value", discharge.get("value"));

                        discharges.remove(discharge);

                        break;

                    } 
                }

            }

            Collections.sort(result, new Comparator<Map<String, Object>>() {

                @Override

                public int compare(Map<String, Object> o1, Map<String, Object> o2) {

                    return Double.compare(((Double) o2.get("avg"))- Double.valueOf((String) o2.get("value")),

                            ((Double) o1.get("avg")) - Double.valueOf((String) o1.get("value")));

                }

            });

        }

        return result;

    }

	
                }



            }

            for (String region : regions) {

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

                map.put("name", region);

                map.put("avg", 0);

                result.add(map);

            }

        }

        if ("discharge".equals(dimension)) {

            parameters.put("year", LocalDate.now().getYear());

            List<Map<String, Object>> discharges = historyMapper.getDischargeByRegion(parameters);

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

                for (Map<String, Object> discharge : discharges) {

                    if (map.get("name").equals(discharge.get("name"))) {

                        map.put("value", discharge.get("value"));

                        discharges.remove(discharge);

                        break;

                    }

                }

            }

            Collections.sort(result, new Comparator<Map<String, Object>>() {

                @Override

                public int compare(Map<String, Object> o1, Map<String, Object> o2) {

                    return Double.compare(((Double) o2.get("avg")) - Double.valueOf((String) o2.get("value")),

                            ((Double) o1.get("avg")) - Double.valueOf((String) o1.get("value")));

                }

            });

        }

        return result;

    }



    @Override

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

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

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

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

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

        parameters.put("size", 1);

        List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);

        JSONObject map = null;

        if (!ObjectUtils.isEmpty(values)) {

            map = JSON.parseObject(values.get(0).get("value").toString());

        }

        Device device = deviceMapper.selectByPrimaryKey(Integer.valueOf((String) parameters.get("deviceId")));

        Map<String, Object> sensorUnits = getSensorUnitByDeviceMac(parameters);

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

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

            sensor.put("name", sensor.get("description"));

            sensor.put("state", device.getState());

            if (!ObjectUtils.isEmpty(map)) {

                sensor.put("value", map.remove(sensor.get("sensor_key")));

            }

            if (sensorUnits.containsKey(sensorKey)) {

                Map<String, Object> sensorUnit  = (Map<String, Object>) sensorUnits.get(sensorKey);

                sensor.put("unit", sensorUnit.get("name"));

                if (sensor.containsKey("value")) {

                    Object value = sensor.get("value");

                    String rules = sensorUnit.get("rules").toString();

                    rules = rules.replace("d", "").replace("{0}", "value");

                    Expression expression = AviatorEvaluator.compile(rules);

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

                    env.put("value", Double.valueOf(value.toString()));

                    value = expression.execute(env);

                    sensor.put("value",String.format("%.2f", value));

                }

            }

        }

        return sensors;

    }

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

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

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

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

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

        parameters.put("size", 1);

        List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);

        JSONObject map = null;

        if (!ObjectUtils.isEmpty(values)) {

            map = JSON.parseObject(values.get(0).get("value").toString());

        }

        Device device = deviceMapper.selectByPrimaryKey(Integer.valueOf((String) parameters.get("deviceId")));

        Map<String, Object> sensorUnits = getSensorUnitByDeviceMac(parameters);

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

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

            sensor.put("name", sensor.get("description"));

            sensor.put("state", device.getState());

            if (!ObjectUtils.isEmpty(map)) {

                sensor.put("value", map.remove(sensor.get("sensor_key")));

            }

            if (sensorUnits.containsKey(sensorKey)) {

                Map<String, Object> sensorUnit = (Map<String, Object>) sensorUnits.get(sensorKey);

                sensor.put("unit", sensorUnit.get("name"));

                if (sensor.containsKey("value")) {

                    Object value = sensor.get("value");

                    String rules = sensorUnit.get("rules").toString();

                    rules = rules.replace("d", "").replace("{0}", "value");

                    Expression expression = AviatorEvaluator.compile(rules);

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

                    env.put("value", Double.valueOf(value.toString()));

                    value = expression.execute(env);

                    sensor.put("value", String.format("%.2f", value));

                }

            }

        }

        return sensors;

    }



    @Override

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

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

        ValidateUtil.notNull(sensorKey, "param.is.null");

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

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

        parameters.put("size", Integer.valueOf(parameters.remove("size").toString()));

        List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);

        Map<String, Object> sensorUnits = getSensorUnitByDeviceMac(parameters);

        for (Map<String, Object> value : values) {

            JSONObject json = JSON.parseObject(value.remove("value").toString());

            Object sensorKeyValue = json.get(sensorKey);

            if (sensorUnits.containsKey(sensorKey)) {

                Map<String, Object> sensorUnit  = (Map<String, Object>) sensorUnits.get(sensorKey);

                String rules = sensorUnit.get("rules").toString();

                rules = rules.replace("d", "").replace("{0}", "value");

                Expression expression = AviatorEvaluator.compile(rules);

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

                env.put("value", Double.valueOf(sensorKeyValue.toString()));

                sensorKeyValue = expression.execute(env);

                sensorKeyValue = String.format("%.2f", sensorKeyValue);

            }

            value.put("value", sensorKeyValue);

        }

        return values;

    }

    @Override

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

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

        ValidateUtil.notNull(sensorKey, "param.is.null");

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

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

        parameters.put("size", Integer.valueOf(parameters.remove("size").toString()));

        List<Map<String, Object>> values = historyMapper.getValueByMacAndSize(parameters);

        Map<String, Object> sensorUnits = getSensorUnitByDeviceMac(parameters);

        for (Map<String, Object> value : values) {

            JSONObject json = JSON.parseObject(value.remove("value").toString());

            Object sensorKeyValue = json.get(sensorKey);

            if (sensorUnits.containsKey(sensorKey)) {

                Map<String, Object> sensorUnit = (Map<String, Object>) sensorUnits.get(sensorKey);

                String rules = sensorUnit.get("rules").toString();

                rules = rules.replace("d", "").replace("{0}", "value");

                Expression expression = AviatorEvaluator.compile(rules);

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

                env.put("value", Double.valueOf(sensorKeyValue.toString()));

                sensorKeyValue = expression.execute(env);

                sensorKeyValue = String.format("%.2f", sensorKeyValue);

            }

            value.put("value", sensorKeyValue);

        }

        return values;

    }



    @Override

    public Map<String, Object> getActualDataByRegion(Map<String, Object> parameters) {

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

        ValidateUtil.notNull(sensorKey, "param.is.null");

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

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

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

        LocalDateTime start, end;

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

            end = LocalDateTime.now();

            int second = end.getSecond() / interval * interval;

            end = end.truncatedTo(ChronoUnit.MINUTES).plusSeconds(second);

            start = end.minusSeconds(interval);

        } else {

            //start = LocalDateTime.ofEpochSecond(Long.valueOf(parameters.get("time").toString()) / 1000, 0, ZoneOffset.ofHours(8));

            start = LocalDateTime.ofInstant(Instant.ofEpochMilli(Long.valueOf(parameters.get("time").toString())),ZoneId.of("+8"));

            end = start.plusSeconds(interval);

        }

        parameters.put("start", start);

        parameters.put("end", end);

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

        accountService.setOrgIdsByAccount(parameters);

        Map<String, Object> result = historyMapper.getActualDataByRegion(parameters);

        if (ObjectUtils.isEmpty(result)) {

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

        }

        result.put("time", end.toInstant(ZoneOffset.of("+8")).toEpochMilli());

        return result;

    }

    @Override

    public Map<String, Object> getActualDataByRegion(Map<String, Object> parameters) {

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

        ValidateUtil.notNull(sensorKey, "param.is.null");

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

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

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

        LocalDateTime start, end;

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

            end = LocalDateTime.now();

            int second = end.getSecond() / interval * interval;

            end = end.truncatedTo(ChronoUnit.MINUTES).plusSeconds(second);

            start = end.minusSeconds(interval);

        } else {

            //start = LocalDateTime.ofEpochSecond(Long.valueOf(parameters.get("time").toString()) / 1000, 0, ZoneOffset.ofHours(8));

            start = LocalDateTime.ofInstant(Instant.ofEpochMilli(Long.valueOf(parameters.get("time").toString())), ZoneId.of("+8"));

            end = start.plusSeconds(interval);

        }

        parameters.put("start", start);

        parameters.put("end", end);

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

        accountService.setOrgIdsByAccount(parameters);

        Map<String, Object> result = historyMapper.getActualDataByRegion(parameters);

        if (ObjectUtils.isEmpty(result)) {

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

        }

        result.put("time", end.toInstant(ZoneOffset.of("+8")).toEpochMilli());

        return result;

    }



    @Resource

    private SensorUnitMapper sensorUnitMapper;

    @Override

    public Map<String, Object> getSensorUnitByDeviceMac(Map<String, Object> parameters){

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

        List<Map<String, Object>> list = sensorUnitMapper.getSensorUnitByDeviceMac(parameters);

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

            resultMap.put(map.remove("sensor_key").toString(), map);

        }

        return resultMap;

		
    }

    @Resource

    private SensorUnitMapper sensorUnitMapper;



    @Override

    public Map<String, Object> gitHourlyAQIByMacAndTimeslot(Map<String, Object> parameters) {

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

        //获取日期

        LocalDate localDate = LocalDate.now();

        Calendar c = Calendar.getInstance();//可以对每个时间域单独修改

        int endHour = c.get(Calendar.HOUR_OF_DAY);

        String endTime = localDate+" "+endHour+":00:00";

    	
        String startTime;

        if(endHour == 0) {

            LocalDate startDate = localDate.minusDays(1);

            startTime = startDate+" "+"23:00:00";

        }else {

            int startHour = endHour-1;

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

        } else {

            Map<String, Double> AQIMap = new HashMap<>();

            for (Map.Entry<String, Object> entry : map.entrySet()) {

                String key = entry.getKey();

                Double value = Double.parseDouble(entry.getValue().toString());

                AQIMap.put(key, value);

            }

            returnMap = AQICalculation.hourlyAQI(AQIMap);

        }

		
        return returnMap;

    }

    @Override

    public Map<String, Object> getSensorUnitByDeviceMac(Map<String, Object> parameters) {

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

        List<Map<String, Object>> list = sensorUnitMapper.getSensorUnitByDeviceMac(parameters);

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

            resultMap.put(map.remove("sensor_key").toString(), map);

        }

        return resultMap;



    @Override

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

        //获取组织id

        String organizationId = parameters.get("organization_id").toString();

        //声明一个集合存放要查询站点数据的组织id

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

        organizationIdList.add(organizationId);

        //声明一个集合存放父组织id

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

        parentIdList.add(organizationId);

		
        //进行循环

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

            if(!parentIdList.isEmpty()) {

                //或去该组织下的子组织

                List<Map<String, Object>> childIdList = organizationRelationMapper.getChildIdByParentId(parentIdList);

                //清空父组织id集合

                parentIdList.clear();

                //将子组织id放入到集合中

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

                    organizationIdList.add(map.get("child_id"));

                    parentIdList.add(map.get("child_id"));

                }

            }else {

                break;

            }

        }

		
        //根据组织id获取站点id

        List<Map<String, Object>> monitorPointIdListMap = monitorPointMapper.getIdByOrganizationId(organizationIdList);

        //将List<Map<String, Object>>装换为List<Object>

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

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

            monitorPointIdList.add(map.get("id"));

        }

		
        //通过站点id获取设备因子的排名

        //获取系统当前时间

        Calendar rightNow = Calendar.getInstance();

        //获取五分钟前的时间

        Calendar beforeTime = Calendar.getInstance();

        beforeTime.add(Calendar.MINUTE, -5);// 5分钟之前的时间

        Date beforeD = beforeTime.getTime();

        Date end = rightNow.getTime();

        String endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(end);

        String before5Time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(beforeD);  // 前五分钟时间

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

        List<Map<String, Object>> result = historyMapper.getAVGSensorRankByMonitorPointIdList(sensor, monitorPointIdList, before5Time, endTime);

        return result;

    }

    }



    @Override

    public Map<String, Object> gitHourlyAQIByMonitorPointIdAndTimeslot(Map<String, Object> parameters) {

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

        //获取日期

        LocalDate localDate = LocalDate.now();

        Calendar c = Calendar.getInstance();//可以对每个时间域单独修改

        int endHour = c.get(Calendar.HOUR_OF_DAY);

        String endTime = localDate+" "+endHour+":00:00";

    	
        String startTime;

        if(endHour == 0) {

            LocalDate startDate = localDate.minusDays(1);

            startTime = startDate+" "+"23:00:00";

        }else {

            int startHour = endHour-1;

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

        } else {

            Map<String, Double> AQIMap = new HashMap<>();

            for (Map.Entry<String, Object> entry : map.entrySet()) {

                String key = entry.getKey();

                Double value = Double.parseDouble(entry.getValue().toString());

                AQIMap.put(key, value);

            }

            returnMap = AQICalculation.hourlyAQI(AQIMap);

        }

		
        return returnMap;

    }

    @Override

    public Map<String, Object> gitHourlyAQIByMacAndTimeslot(Map<String, Object> parameters) {

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

        //获取日期

        LocalDate localDate = LocalDate.now();

        Calendar c = Calendar.getInstance();//可以对每个时间域单独修改

        int endHour = c.get(Calendar.HOUR_OF_DAY);

        String endTime = localDate + " " + endHour + ":00:00";



        String startTime;

        if (endHour == 0) {

            LocalDate startDate = localDate.minusDays(1);

            startTime = startDate + " " + "23:00:00";

        } else {

            int startHour = endHour - 1;

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

        } else {

            Map<String, Double> AQIMap = new HashMap<>();

            for (Map.Entry<String, Object> entry : map.entrySet()) {

                String key = entry.getKey();

                Double value = Double.parseDouble(entry.getValue().toString());

                AQIMap.put(key, value);

            }

            returnMap = AQICalculation.hourlyAQI(AQIMap);

        }



        return returnMap;

    }



    @Override

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

        //获取组织id

        String organizationId = parameters.get("organization_id").toString();

        //声明一个集合存放要查询站点数据的组织id

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

        organizationIdList.add(organizationId);

        //声明一个集合存放父组织id

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

        parentIdList.add(organizationId);



        //进行循环

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

            if (!parentIdList.isEmpty()) {

                //或去该组织下的子组织

                List<Map<String, Object>> childIdList = organizationRelationMapper.getChildIdByParentId(parentIdList);

                //清空父组织id集合

                parentIdList.clear();

                //将子组织id放入到集合中

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

                    organizationIdList.add(map.get("child_id"));

                    parentIdList.add(map.get("child_id"));

                }

            } else {

                break;

            }

        }



        //根据组织id获取站点id

        List<Map<String, Object>> monitorPointIdListMap = monitorPointMapper.getIdByOrganizationId(organizationIdList);

        //将List<Map<String, Object>>装换为List<Object>

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

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

            monitorPointIdList.add(map.get("id"));

        }



        //通过站点id获取设备因子的排名

        //获取系统当前时间

        Calendar rightNow = Calendar.getInstance();

        //获取五分钟前的时间

        Calendar beforeTime = Calendar.getInstance();

        beforeTime.add(Calendar.MINUTE, -5);// 5分钟之前的时间

        Date beforeD = beforeTime.getTime();

        Date end = rightNow.getTime();

        String endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(end);

        String before5Time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(beforeD);  // 前五分钟时间



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

        List<Map<String, Object>> result = historyMapper.getAVGSensorRankByMonitorPointIdList(sensor, monitorPointIdList, before5Time, endTime);

        return result;

    }



    @Override

    public Map<String, Object> gitHourlyAQIByMonitorPointIdAndTimeslot(Map<String, Object> parameters) {

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

        //获取日期

        LocalDate localDate = LocalDate.now();

        Calendar c = Calendar.getInstance();//可以对每个时间域单独修改

        int endHour = c.get(Calendar.HOUR_OF_DAY);

        String endTime = localDate + " " + endHour + ":00:00";



        String startTime;

        if (endHour == 0) {

            LocalDate startDate = localDate.minusDays(1);

            startTime = startDate + " " + "23:00:00";

        } else {

            int startHour = endHour - 1;

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

        } else {

            Map<String, Double> AQIMap = new HashMap<>();

            for (Map.Entry<String, Object> entry : map.entrySet()) {

                String key = entry.getKey();

                Double value = Double.parseDouble(entry.getValue().toString());

                AQIMap.put(key, value);

            }

            returnMap = AQICalculation.hourlyAQI(AQIMap);

        }



        return returnMap;

    }



    @Override

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

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

        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        LocalDateTime startTimeLocalDateTime = LocalDateTime.parse(startTime, dateTimeFormatter);

        int year = startTimeLocalDateTime.getYear();

        int month = startTimeLocalDateTime.getMonthValue();

        String monthStr = month < 10 ? ("0" + month) : month + "";

        String yearAndMonth = year + monthStr;

        List<Sensor> sensors = sensorMapper.getSensorsByMac(parameters);

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

        for (Sensor sensor : sensors) {

            sensorKeys.add(sensor.getSensorKey());

        }

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> listMap = null;

        listMap = historyMapper.getCarSensorData(parameters);

        if (CollectionUtils.isEmpty(listMap)) {

            if (year <= 2019) {

                listMap = historyMinutelyMapper.getSensorData(parameters);

            } else {

                parameters.put("yearAndMonth", yearAndMonth);

                listMap = historyMinutelyMapper.getSensorData2020(parameters);

            }

        }

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

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

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

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

        if (CollectionUtils.isNotEmpty(listMap)) {

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

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

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

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

                mapAvg.put("time", map.get("time"));

                mapMin.put("time", map.get("time"));

                mapMax.put("time", map.get("time"));

                for (Map.Entry<String, Object> entry : map.entrySet()) {

                    for (Sensor sensor : sensors) {

                        if (sensor.getSensorKey().equals(entry.getKey())) {

                            mapAvg.put(entry.getKey(), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString() + sensor.getUnit());

                        } else if (("min" + sensor.getSensorKey()).equals(entry.getKey())) {

                            mapMin.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());

                        } else if (("max" + sensor.getSensorKey()).equals(entry.getKey())) {

                            mapMax.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());

                        }

                    }

                }

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

                listMapMin.add(mapMin);

                listMapMax.add(mapMax);

            }

            listMaps.add(listMapAvg);

            listMaps.add(listMapMin);

            listMaps.add(listMapMax);

        }

        return listMaps;

    }

}


 src/main/resources/mapper/DeviceMapper.xml

@@ -216,7 +216,7 @@
        </where>
    </select>
    <select id="selectByOrgIdAndMpId" resultMap="BaseResultMap">
        SELECT dev.*,dve.version as device_version_value from device dev
        SELECT dev.*,dve.version as device_version_value,mpt.* from device dev
        left join monitor_point mpt on dev.monitor_point_id = mpt.id
        left join device_version dve on dev.device_version_id = dve.id
        <where>
@@ -418,4 +418,16 @@
    <select id="getLimitDataByDevice" resultType="String">
        SELECT limit_val FROM view_device_density WHERE mac = #{mac}
    </select>

    <select id="getMacsByOrganizationId" resultType="string">
        SELECT d.mac
        FROM monitor_point mp, device d
        WHERE mp.id = d.monitor_point_id
        AND mp.organization_id IN
        <foreach collection="organizationIdList" index="index" item="organizationId" open="(" separator="," close=")">
            #{organizationId}
        </foreach>
        AND d.is_delete = '0'
    </select>

</mapper>

 src/main/resources/mapper/HistoryMapper.xml

@@ -216,4 +216,24 @@
        AND h.time BETWEEN #{starttime} AND #{endtime}

    </select>



    <!-- 走航车sensor查询 -->

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

        SELECT

        DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s') time,

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

            h.value->'$.${sensorKey}' as '${sensorKey}'

        </foreach>

        FROM

        history as h

        <where>

            <if test="mac!=null">

                and h.mac=#{mac}

            </if>

            and h.time >= #{startTime}

            and h.time <![CDATA[<]]> #{endTime}

        </where>

        ORDER BY

        h.time

    </select>



</mapper>

 src/main/resources/mapper/HistoryMinutelyMapper.xml

@@ -152,7 +152,7 @@
                and hm.mac=#{mac}

            </if>

            and hm.time >= #{startTime}

            and hm.time <![CDATA[<=]]> #{endTime}

            and hm.time <![CDATA[<]]> #{endTime}

        </where>

        ORDER BY

        hm.time

@@ -173,10 +173,32 @@
                and hm.mac=#{mac}

            </if>

            and hm.time >= #{startTime}

            and hm.time <![CDATA[<=]]> #{endTime}

            and hm.time <![CDATA[<]]> #{endTime}

        </where>

        ORDER BY

        hm.time

    </select>



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

        SELECT

        h.mac,DATE_FORMAT(time, #{typeFormat}) time,

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

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

        </foreach>

        FROM

        history_${timeUnits} h

        WHERE

        h.time >= #{start}

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

        AND    h.mac in

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

                 item="mac">

            #{mac}

        </foreach>

        GROUP BY

        h.mac,DATE_FORMAT(time, #{typeFormat})

        ORDER BY

        h.mac

    </select>



</mapper>

 src/main/resources/mapper/mybatis-config.xml

@@ -4,6 +4,9 @@
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <setting name="callSettersOnNulls" value="true"/>
    </settings>
    <typeAliases>
       <typeAlias type="com.moral.mapper.type.AlarmConfigValueHandle" alias="AlarmConfigValueHandle"/>
        <typeAlias type="com.moral.mapper.type.AdjustValueTypeHandle" alias="AdjustValueTypeHandle"/>

 src/main/webapp/img/car.png

 src/main/webapp/js/moralmap.js

@@ -1063,6 +1063,7 @@
                }
            }
        }
        listView.refreshState(deviceStates);
        for (var i in states) {
            var state = states[i];
            var mpoint = moralMap.getMpoint(state.id);

 src/main/webapp/view/cartrajectory.jsp

New file
@@ -0,0 +1,409 @@
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<head>
    <meta charset="utf-8"/>
    <title></title>
    <script type="text/javascript"
            src="http://api.map.baidu.com/api?v=2.0&ak=rER1sgBIcQxkfNSlm2wmBGZGgEERrooM"></script>
    <script type="text/javascript" src="/js/jquery.min.js"></script>
</head>
<style type="text/css">
    body,
    html,
    #mapCanvas {
        width: 100%;
        height: 100%;
        overflow: hidden;
        margin: 0;
        z-index: 0;
        font-size: 14px;
        font-family: "微软雅黑";
    }

    .main_body {
        border: 0;
        margin: 0;
        width: 100%;
        height: 100%;
        position: relative;
    }

    * {
        margin: 0;
        padding: 0;
        list-style: none;
    }

    #cpm {
        width: 300px;
        height: 100px;
        position: absolute;
        background-color: #ffffff;
        display: none;
        left: 50%;
        top: 50%;
        margin-left: -150px;
        margin-top: -50px;
        z-index: 11;
        color: #000000;
        border: 2px solid #FF7F50;
        font-size: 28px;
        line-height: 100px;
        text-align: center;
    }

    .BMap_top {
        display: none;
    }

    .BMap_center {
        display: none;
    }

    .BMap_bubble_title {
        color: white;
        padding: 0 5px 0 5px;
        background-color: #2DA0EB;
    }

    .BMap_bubble_content {
        background-color: white;
        padding: 5px;
    }

    .BMap_pop > img {
        top: 42px !important;
        margin-left: -10px;
    }

    .BMap_pop div:nth-child(1) div {
        display: none;
    }

    .BMap_pop div:nth-child(3) {
        display: none;
    }

    .BMap_pop div:nth-child(5) {
        display: none;
    }

    .BMap_pop div:nth-child(7) {
        display: none;
    }

    .BMap_pop div:nth-child(9) {
        top: 35px !important;
        border-radius: 5px;
    }

</style>

<body>
<div class="main_body">
    <div id="cpm">查无走航车轨迹</div>
    <div id="mapCanvas"></div> <!-- 百度地图 -->
    <!-- 传sensorInfo,regionCode,regionName,monitorPoint,device-->
    <div id="carTrajectoryParams" style="display: none;">
        ${requestScope.carTrajectoryParams}
    </div>
</div>
</body>
</html>

<script type="text/javascript">
    var moralMap = {};
    var GPS = {
        PI: 3.14159265358979324,
        x_pi: 3.14159265358979324 * 3000.0 / 180.0,
        delta: function (lat, lon) {
            var a = 6378245.0; //  a: 卫星椭球坐标投影到平面地图坐标系的投影因子。
            var ee = 0.00669342162296594323; //  ee: 椭球的偏心率。
            var dLat = this.transformLat(lon - 105.0, lat - 35.0);
            var dLon = this.transformLon(lon - 105.0, lat - 35.0);
            var radLat = lat / 180.0 * this.PI;
            var magic = Math.sin(radLat);
            magic = 1 - ee * magic * magic;
            var sqrtMagic = Math.sqrt(magic);
            dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * this.PI);
            dLon = (dLon * 180.0) / (a / sqrtMagic * Math.cos(radLat) * this.PI);
            return {'lat': dLat, 'lon': dLon};
        },

        //WGS-84 to GCJ-02
        gcj_encrypt: function (wgsLat, wgsLon) {
            if (this.outOfChina(wgsLat, wgsLon))
                return {'lat': wgsLat, 'lon': wgsLon};

            var d = this.delta(wgsLat, wgsLon);
            return {'lat': wgsLat + d.lat, 'lon': wgsLon + d.lon};
        },
        //GCJ-02 to WGS-84
        gcj_decrypt: function (gcjLat, gcjLon) {
            if (this.outOfChina(gcjLat, gcjLon))
                return {'lat': gcjLat, 'lon': gcjLon};

            var d = this.delta(gcjLat, gcjLon);
            return {'lat': gcjLat - d.lat, 'lon': gcjLon - d.lon};
        },
        //GCJ-02 to WGS-84 exactly
        gcj_decrypt_exact: function (gcjLat, gcjLon) {
            var initDelta = 0.01;
            var threshold = 0.000000001;
            var dLat = initDelta, dLon = initDelta;
            var mLat = gcjLat - dLat, mLon = gcjLon - dLon;
            var pLat = gcjLat + dLat, pLon = gcjLon + dLon;
            var wgsLat, wgsLon, i = 0;
            while (1) {
                wgsLat = (mLat + pLat) / 2;
                wgsLon = (mLon + pLon) / 2;
                var tmp = this.gcj_encrypt(wgsLat, wgsLon)
                dLat = tmp.lat - gcjLat;
                dLon = tmp.lon - gcjLon;
                if ((Math.abs(dLat) < threshold) && (Math.abs(dLon) < threshold))
                    break;

                if (dLat > 0) pLat = wgsLat; else mLat = wgsLat;
                if (dLon > 0) pLon = wgsLon; else mLon = wgsLon;

                if (++i > 10000) break;
            }
            //console.log(i);
            return {'lat': wgsLat, 'lon': wgsLon};
        },
        //GCJ-02 to BD-09
        bd_encrypt: function (gcjLat, gcjLon) {
            var x = gcjLon, y = gcjLat;
            var z = Math.sqrt(x * x + y * y) + 0.00002 * Math.sin(y * this.x_pi);
            var theta = Math.atan2(y, x) + 0.000003 * Math.cos(x * this.x_pi);
            bdLon = z * Math.cos(theta) + 0.0065;
            bdLat = z * Math.sin(theta) + 0.006;
            return {'lat': bdLat, 'lon': bdLon};
        },
        //BD-09 to GCJ-02
        bd_decrypt: function (bdLat, bdLon) {
            var x = bdLon - 0.0065, y = bdLat - 0.006;
            var z = Math.sqrt(x * x + y * y) - 0.00002 * Math.sin(y * this.x_pi);
            var theta = Math.atan2(y, x) - 0.000003 * Math.cos(x * this.x_pi);
            var gcjLon = z * Math.cos(theta);
            var gcjLat = z * Math.sin(theta);
            return {'lat': gcjLat, 'lon': gcjLon};
        },
        //WGS-84 to Web mercator
        //mercatorLat -> y mercatorLon -> x
        mercator_encrypt: function (wgsLat, wgsLon) {
            var x = wgsLon * 20037508.34 / 180.;
            var y = Math.log(Math.tan((90. + wgsLat) * this.PI / 360.)) / (this.PI / 180.);
            y = y * 20037508.34 / 180.;
            return {'lat': y, 'lon': x};
        },
        // Web mercator to WGS-84
        // mercatorLat -> y mercatorLon -> x
        mercator_decrypt: function (mercatorLat, mercatorLon) {
            var x = mercatorLon / 20037508.34 * 180.;
            var y = mercatorLat / 20037508.34 * 180.;
            y = 180 / this.PI * (2 * Math.atan(Math.exp(y * this.PI / 180.)) - this.PI / 2);
            return {'lat': y, 'lon': x};
        },
        // two point's distance
        distance: function (latA, lonA, latB, lonB) {
            var earthR = 6371000.;
            var x = Math.cos(latA * this.PI / 180.) * Math.cos(latB * this.PI / 180.) * Math.cos((lonA - lonB) * this.PI / 180);
            var y = Math.sin(latA * this.PI / 180.) * Math.sin(latB * this.PI / 180.);
            var s = x + y;
            if (s > 1) s = 1;
            if (s < -1) s = -1;
            var alpha = Math.acos(s);
            var distance = alpha * earthR;
            return distance;
        },
        outOfChina: function (lat, lon) {
            if (lon < 72.004 || lon > 137.8347)
                return true;
            if (lat < 0.8293 || lat > 55.8271)
                return true;
            return false;
        },
        transformLat: function (x, y) {
            var ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * Math.sqrt(Math.abs(x));
            ret += (20.0 * Math.sin(6.0 * x * this.PI) + 20.0 * Math.sin(2.0 * x * this.PI)) * 2.0 / 3.0;
            ret += (20.0 * Math.sin(y * this.PI) + 40.0 * Math.sin(y / 3.0 * this.PI)) * 2.0 / 3.0;
            ret += (160.0 * Math.sin(y / 12.0 * this.PI) + 320 * Math.sin(y * this.PI / 30.0)) * 2.0 / 3.0;
            return ret;
        },
        transformLon: function (x, y) {
            var ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * Math.sqrt(Math.abs(x));
            ret += (20.0 * Math.sin(6.0 * x * this.PI) + 20.0 * Math.sin(2.0 * x * this.PI)) * 2.0 / 3.0;
            ret += (20.0 * Math.sin(x * this.PI) + 40.0 * Math.sin(x / 3.0 * this.PI)) * 2.0 / 3.0;
            ret += (150.0 * Math.sin(x / 12.0 * this.PI) + 300.0 * Math.sin(x / 30.0 * this.PI)) * 2.0 / 3.0;
            return ret;
        }
    };

    var mapStyle = {
        features: ["road", "building", "water", "land"], // 隐藏地图上的poi
        style: "normal" // 设置地图风格为高端黑
    };
    var params = $.parseJSON($("#carTrajectoryParams").html());
    var sensorInfo = params["sensorInfo"];
    var sensorsMap = params['sensorsMap'];
    //var map = new BMap.Map("mapCanvas", defaultParam);
    var map = new BMap.Map("mapCanvas", {enableMapClick: false});
    map.setMapStyle(mapStyle);
    map.enableScrollWheelZoom(true); // 开启鼠标滚轮缩放
    var navigation = new BMap.NavigationControl({
        anchor: BMAP_ANCHOR_BOTTOM_RIGHT,
        type: BMAP_NAVIGATION_CONTROL_LARGE
    });
    map.addControl(navigation);
    map.addControl(new BMap.ScaleControl());
    if (sensorInfo.length == 0) {
        showNoFlightTrajectory();
    }
    var trackPoints = []; //用来存放从后台获取到的所有历史轨迹点的数据
    if (sensorInfo.length > 0) {
        $.each(sensorInfo, function (item, value) {
            if (typeof (value.e76) == "undefined") {
                showNoFlightTrajectory();
            } else {
                var lon = parseFloat(value.e76.substr(0, value.e76.length - 1));
                var lat = parseFloat(value.e77.substr(0, value.e77.length - 1));
                var lon1 = GPS.gcj_encrypt(lat, lon).lon;
                var lat1 = GPS.gcj_encrypt(lat, lon).lat;
                var lon2 = GPS.bd_encrypt(lat1, lon1).lon;
                var lat2 = GPS.bd_encrypt(lat1, lon1).lat;
                trackPoints.push(new BMap.Point(lon2, lat2));
            }
        })
        map.centerAndZoom(trackPoints, 13);// 根据经纬度显示地图的范围
        map.setViewport(trackPoints);// 根据提供的地理区域或坐标设置地图视野
        addStartMarker(new BMap.Point(trackPoints[0].lng, trackPoints[0].lat), '起点', map);
        var carMk;
        //小车行驶图标
        var drivingPoint = new BMap.Icon('/img/car.png', new BMap.Size(52, 26));
        //终点图标
        var terminalPoint = new BMap.Icon('/img/end.png', new BMap.Size(48, 48));
        var i = 0;
        var interval = setInterval(function () {
            if (i >= trackPoints.length) {
                clearInterval(interval);
                return;
            }
            var pm2_5 = sensorInfo[i].e1.substr(0, sensorInfo[i].e1.length - 5);
            var traceColor;
            if (pm2_5 <= 35) {
                traceColor = "#00EB5E";
            } else if (pm2_5 > 35 && pm2_5 <= 115) {
                traceColor = "#FFF000";
            } else if (pm2_5 > 115 && pm2_5 <= 150) {
                traceColor = "#FF8400";
            } else if (pm2_5 > 150) {
                traceColor = "#FF0000";
            }
            drowLine(map, trackPoints[i], trackPoints[i + 1], traceColor);//画线调用
            i = i + 1;
        }, 250);
    }

    //添加起始图标
    function addStartMarker(point, name, mapInit) {
        if (name == "起点") {
            var myIcon = new BMap.Icon("/img/start.png", new BMap.Size(48, 48));
            window.marker = new BMap.Marker(point, {icon: myIcon, offset: new BMap.Size(0, -20)});
            mapInit.addOverlay(marker);
        }
    }

    // 划线
    function drowLine(map, PointArr, PointArrNext, traceColor) {
        if (PointArrNext != undefined) {
            var polyline = new BMap.Polyline(
                [
                    new BMap.Point(PointArr.lng, PointArr.lat),
                    new BMap.Point(PointArrNext.lng, PointArrNext.lat)
                ],
                {
                    strokeColor: traceColor,
                    strokeWeight: 4,
                    strokeOpacity: 1
                });   //创建折线
            map.addOverlay(polyline);
            addMarkerEnd(new BMap.Point(PointArrNext.lng, PointArrNext.lat), '小车行驶', map, PointArrNext, new BMap.Point(PointArr.lng, PointArr.lat));//添加图标
        } else {
            addMarkerEnd(new BMap.Point(PointArr.lng, PointArr.lat), '终点', map);//添加终点图标
        }
    }

    //添加行驶和终点图标
    function addMarkerEnd(point, name, mapInit, trackUnit, prePoint) {
        if (name == "小车行驶") {
            if (carMk) {//先判断第一次进来的时候这个值有没有定义，有的话就清除掉上一次的。然后在进行画图标。第一次进来时候没有定义也就不走这块，直接进行画图标
                mapInit.removeOverlay(carMk);
            }
            carMk = new BMap.Marker(point, {icon: drivingPoint});  // 创建标注
            carMk.setRotation(trackUnit.route);//trackUnit.route
            //getAngle(point,prePoint);// js求解两点之间的角度
            carMk.setRotation(getAngle(point, prePoint) - 90);// 旋转的角度
            mapInit.addOverlay(carMk);               // 将标注添加到地图中
            //carMk.setAnimation(BMAP_ANIMATION_BOUNCE); //跳动的动画
        } else {
            mapInit.removeOverlay(carMk);
            carMk = new BMap.Marker(point, {icon: terminalPoint, offset: new BMap.Size(0, -20)});  // 创建标注
            mapInit.addOverlay(carMk);
        }
    }

    //获得角度的函数
    function getAngle(n, next) {
        var ret
        var w1 = n.lat / 180 * Math.PI
        var j1 = n.lng / 180 * Math.PI

        var w2 = next.lat / 180 * Math.PI
        var j2 = next.lng / 180 * Math.PI

        ret = 4 * Math.pow(Math.sin((w1 - w2) / 2), 2) - Math.pow(Math.sin((j1 - j2) / 2) * (Math.cos(w1) - Math.cos(w2)), 2);
        ret = Math.sqrt(ret);

        // var temp = Math.sin(Math.abs(j1 - j2) / 2) * (Math.cos(w1) + Math.cos(w2));
        var temp = Math.sin((j1 - j2) / 2) * (Math.cos(w1) + Math.cos(w2));
        ret = ret / temp;

        ret = Math.atan(ret) / Math.PI * 180;
        ret += 90;

        // 这里用如此臃肿的if..else是为了判定追踪单个点的具体情况,从而调整ret的值
        if (j1 - j2 < 0) {
            // console.log('j1<j2')
            if (w1 - w2 < 0) {
                // console.log('w1<w2')
                ret;
            } else {
                // console.log('w1>w2')
                ret = -ret + 180;
            }
        } else {
            // console.log('j1>j2')
            if (w1 - w2 < 0) {
                // console.log('w1<w2')
                ret = 180 + ret;
            } else {
                // console.log('w1>w2')
                ret = -ret;
            }
        }
        return ret;
    }

    function showNoFlightTrajectory() {
        var longitude = 120.987287;
        var latitude = 31.391562;
        var point = new BMap.Point(longitude, latitude);
        map.centerAndZoom(point, 17);
        setTimeout(function () {
            document.getElementById("cpm").style.display = 'block';
        }, 250);
    };

</script>