更新 时间api用法

xufenglei

2018-02-10 f2857dd60023bb0ddb87f7f06993987f97a4b2be

更新 时间api用法

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

@@ -188,9 +188,10 @@
     * @param request

     *            the request

     * @return the average by all

     * @throws Exception 
     */

    @GetMapping("all-average")

    public Map<String, Object> getAllSensorAverageByDevice(HttpServletRequest request) {

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

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

        return historyService.getAllSensorAverageByDevice(parameters);

    }


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

@@ -22,4 +22,7 @@
    List<Device> selectByOrgIdAndMpId(@Param("orgId")Integer orgId,@Param("mpId")Integer mpId);

    List<Device> selectByMap(Map<String, Object> params);

    Device selectWithOrgIdsByMac(String mac);

	
    List<Integer> getDeviceVersionIdByAreaCode(Map<String, Object> parameters);

	
}

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

@@ -5,10 +5,8 @@


public interface HistoryService {



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

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



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



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



}


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

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



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

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

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

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

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



import java.math.BigDecimal;

import java.math.RoundingMode;

import java.time.LocalDate;

import java.time.temporal.TemporalAdjusters;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

@@ -30,19 +28,15 @@
import javax.annotation.Resource;



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

import org.springframework.data.mongodb.core.aggregation.Aggregation;

import org.springframework.data.mongodb.core.aggregation.AggregationResults;

import org.springframework.data.mongodb.core.query.Criteria;

import org.springframework.stereotype.Service;

import org.springframework.util.ObjectUtils;



import com.mongodb.BasicDBObject;

import com.moral.common.util.CalculateUtils;

import com.moral.common.util.ResourceUtil;

import com.moral.common.util.ValidateUtil;

import com.moral.entity.Device;

import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.SensorMapper;

import com.moral.service.HistoryMinutelyService;



@@ -63,11 +57,12 @@
    @Override

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

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

        Date date = new Date();

        LocalDate localDate = LocalDate.now();

        // 昨日00:00:00

        parameters.put("start", truncate(addDays(date, -1), Calendar.DATE));

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

		
        // 今日00:00:00

        parameters.put("end", truncate(date, Calendar.DATE));

        parameters.put("end", localDate);

        String[] macKeys = { "e1", "e2", "e10", "e11", "e15", "e16" };

        String sensorKeyColumn = "";

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

@@ -383,19 +378,17 @@
        ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

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

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

        Date date = new Date();

        Date end = truncate(date, Calendar.DATE), start;

        LocalDate end = LocalDate.now(),start;

        // 每月一日的数据取上月的数据

        if (1 == Calendar.getInstance().get(Calendar.DAY_OF_MONTH)) {

        if (1 == end.getDayOfMonth()) {

            // 上个月1日00:00:00

            start = truncate(addMonths(date, -1), Calendar.MONTH);

            start = end.plusDays(-1).with(TemporalAdjusters.firstDayOfMonth());

        } else {

            // 这个月1日00:00:00

            start = truncate(date, Calendar.MONTH);

            start = end.with(TemporalAdjusters.firstDayOfMonth());

        }

        parameters.put("start", start);

        parameters.put("end", end);



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

        String sensorKeyColumn = "AVG(json -> '$." + sensorKey + "[0]') " + sensorKey;

        parameters.put("sensorKeyColumn", sensorKeyColumn);


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

@@ -1,30 +1,28 @@
package com.moral.service.impl;



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

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

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

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

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

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



import java.util.Calendar;

import java.util.Date;

import java.math.BigDecimal;

import java.math.RoundingMode;

import java.time.LocalDateTime;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import java.util.concurrent.Callable;

import java.util.concurrent.CompletionService;

import java.util.concurrent.ExecutorCompletionService;

import java.util.concurrent.ExecutorService;

import java.util.concurrent.Executors;



import javax.annotation.Resource;



import org.springframework.data.mongodb.core.MongoTemplate;

import org.springframework.data.mongodb.core.aggregation.Aggregation;

import org.springframework.data.mongodb.core.aggregation.AggregationResults;

import org.springframework.data.mongodb.core.query.Criteria;

import org.springframework.stereotype.Service;



import com.mongodb.BasicDBObject;

import com.moral.common.util.ValidateUtil;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.HistoryMapper;

import com.moral.mapper.SensorMapper;

import com.moral.service.AccountService;

import com.moral.service.HistoryService;



@@ -38,19 +36,82 @@
    private HistoryMapper historyMapper;



    @Resource

    private MongoTemplate mongoTemplate;

    private DeviceMapper deviceMapper;



    @Resource

    private SensorMapper sensorMapper;



    @Override

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

    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, Number[]> result = new HashMap<String, Number[]>();

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

        accountService.setOrgIdsByAccount(parameters);

        Date date = new Date();

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -10分钟

        parameters.put("start", addMinutes(date, -10));

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

        // 当前时间 -5分钟

        parameters.put("end", addMinutes(date, -5));

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

        parameters.put("macKey", "all");

        List<Integer> deviceVersionIds = deviceMapper.getDeviceVersionIdByAreaCode(parameters);

        ExecutorService threadPool = Executors.newCachedThreadPool();

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

        for (Integer deviceVersionId : deviceVersionIds) {

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

            String queryColumns = "";

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

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

                String sensorKey = (String) sensors.get(i).get("sensor_key");

                queryColumns += "AVG(value -> '$." + sensorKey + "') " + sensorKey;

                if (i != sensors.size() - 1) {

                    queryColumns += " ,";

                } 
            }

            parameter.put("queryColumns", queryColumns);

            parameter.put("deviceVersionId", deviceVersionId);

            cs.submit(new Callable<Map<String, Object>>() {

                @Override

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

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

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

                    map.put(deviceVersionId.toString(), list.get(0));

                    return map;

                }

            });

        }

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

        for (Integer deviceVersionId : deviceVersionIds) {

            list.add(cs.take().get());

        }

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

            Collection<Object> values = map.values();

            for (Object object : values) {

                Map<String, Object> result1 = (Map<String, Object>)object;

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

                    Number[] numbers = new Number[2];

                    Double sum = (Double) result1.get(key);

                    Integer count = 1;

                    if (result.containsKey(key)) {

                        numbers = result.get(key);

                        sum += (Double)numbers[0];

                        count += (Integer)numbers[1];

                    }

                    numbers[0] = sum;

                    numbers[1] = count;

                    result.put(key, numbers);

                    resultMap.put(key, new BigDecimal(sum / count).setScale(3, RoundingMode.HALF_UP).doubleValue());

                }

            }

        }



        return resultMap;

        /*Date date = new Date();

        // 当前时间 -10分钟

        parameters.put("start", DateUtils.addMinutes(date, -10));

        // 当前时间 -5分钟

        parameters.put("end", DateUtils.addMinutes(date, -5));

        String queryColumns = "";

        for (int i = 1; i < 20; i++) {

            if (i == 1) {

@@ -63,7 +124,8 @@
        parameters.put("macKey", "all");

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



        return list.get(0);

        return list.get(0);*/



    }



    @Override

@@ -74,10 +136,10 @@


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

        accountService.setOrgIdsByAccount(parameters);

        Date date = new Date();

        LocalDateTime time = LocalDateTime.now();

        // 当前时间 -1小时

        parameters.put("start", addHours(date, -1));

        parameters.put("end", date);

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

        parameters.put("end", time);

        parameters.put("macKey", "'$." + parameters.get("macKey") + "'");

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



@@ -87,36 +149,5 @@
        return result;

    }



    @Override

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

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

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

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

        Date date = new Date();

        Long end = truncate(date, Calendar.DATE).getTime(), start;

        // 每月一日的数据取上月的数据

        if (1 == Calendar.getInstance().get(Calendar.DAY_OF_MONTH)) {

            // 上个月1日00:00:00

            start = truncate(addMonths(date, -1), Calendar.MONTH).getTime();

        } else {

            // 这个月1日00:00:00

            start = truncate(date, Calendar.MONTH).getTime();

        }

        Aggregation aggregation = Aggregation.newAggregation(

                Aggregation.match(Criteria.where("mac").is(parameters.get("mac"))),

                Aggregation.match(Criteria.where("time").gte(start)), Aggregation.match(Criteria.where("time").lt(end)),

                Aggregation.group("mac").avg((String) parameters.get("macKey")).as("average"));

        AggregationResults<BasicDBObject> results = mongoTemplate.aggregate(aggregation, "data", BasicDBObject.class);

        List<BasicDBObject> list = results.getMappedResults();

        if (isEmpty(list)) {

            result.put("average", NULL_VALUE);

        } else {

            result = list.get(0);

            result.put("average", String.format("%.2f", result.get("average")));

        }

        return result;

    }



	


}


 src/main/resources/mapper/DeviceMapper.xml

@@ -116,22 +116,38 @@
    </select>
    <select id="getDeviceVersionIdByMonitorPoint" resultType="map">
        SELECT
        device_version_id deviceVersionId
            device_version_id deviceVersionId
        FROM
        device
            device
        WHERE
        monitor_point_id = #{monitorPoint}
            monitor_point_id = #{monitorPoint}
        GROUP BY
        device_version_id
            device_version_id
    </select>
    <select id="getDeviceVersionIdByAreaCode" resultType="int">
        SELECT DISTINCT
            d.device_version_id 
        FROM
            monitor_point mp,
            device d 
        WHERE
            mp.area_code = #{areaCode} 
            AND d.monitor_point_id = mp.id
        <if test="orgIds != null and orgIds.size &gt; 0">
            AND mp.organization_id IN
            <foreach  collection="orgIds" open="(" separator="," close=")" item="listItem">
                #{listItem}
            </foreach>
        </if>
    </select>
    <select id="getDeviceMacByMonitorPointAndDeviceVersion" resultType="string">
        SELECT
        mac
            mac
        FROM
        device
            device
        WHERE
        monitor_point_id = #{monitorPointId}
        AND device_version_id = #{deviceVersionId}
            monitor_point_id = #{monitorPointId}
            AND device_version_id = #{deviceVersionId}
    </select>
    <select id="selectByOrgIdAndDevName" resultMap="BaseResultMap">
        SELECT dev.*,dve.version as device_version_value from device dev

 src/main/resources/mapper/HistoryMapper.xml

@@ -18,14 +18,17 @@
            mp.area_code = #{areaCode} 

            AND h.time >= #{start} 

            AND h.time &lt; #{end}

            AND h.mac = d.mac 
            AND d.monitor_point_id = mp.id 
        <if test="orgIds != null and orgIds.size &gt; 0">

            AND mp.organization_id IN

            <foreach  collection="orgIds" open="(" separator="," close=")" item="listItem">

                #{listItem}

            </foreach>

        </if>

            AND h.mac = d.mac 
            AND d.monitor_point_id = mp.id 
        <if test="deviceVersionId != null">

            AND d.device_version_id = #{deviceVersionId} 
        </if>

        <if test="macKey != 'all'">

            GROUP BY d.id

            ORDER BY avg desc