From b1935a09641bc915c61f1868d911adf455d79833 Mon Sep 17 00:00:00 2001
From: ZhuDongming <773644075@qq.com>
Date: Tue, 28 Apr 2020 09:33:41 +0800
Subject: [PATCH] update大屏本月平均值查询表

---
 src/main/java/com/moral/service/HistoryMinutelyService.java          |    2 ++
 src/main/java/com/moral/mapper/HistoryMinutelyMapper.java            |    2 ++
 src/main/resources/mapper/HistoryMinutelyMapper.xml                  |   26 ++++++++++++++++++++++++++
 src/main/java/com/moral/controller/ScreenController.java             |    2 +-
 src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java |   28 ++++++++++++++++++++++++++++
 5 files changed, 59 insertions(+), 1 deletions(-)

diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index 3920b26..9403bfd 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -463,7 +463,7 @@
     @GetMapping("month-sensor-average")
     public Map<String, Object> getMonthAverageBySensor(HttpServletRequest request) {
         Map<String, Object> parameters = getParametersStartingWith(request, null);
-        return historyMinutelyService.getMonthAverageBySensor(parameters);
+        return historyMinutelyService.getAverageBySensor(parameters);
     }
 
     @GetMapping("check-activate")
diff --git a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
index 4e1b68f..12ba243 100644
--- a/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMinutelyMapper.java
@@ -12,6 +12,8 @@
 
   Map<String, Double> getSersionAvgByDevice(Map<String, Object> parameters);
 
+  Map<String, Double> getAvgByDevice(Map<String, Object> parameters);
+
   List<Map<String, Object>> getMonitorPointOrDeviceAvgData(Map<String, Object> parameters);
 
   List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters);
diff --git a/src/main/java/com/moral/service/HistoryMinutelyService.java b/src/main/java/com/moral/service/HistoryMinutelyService.java
index 2408384..7ce8b6f 100644
--- a/src/main/java/com/moral/service/HistoryMinutelyService.java
+++ b/src/main/java/com/moral/service/HistoryMinutelyService.java
@@ -19,6 +19,8 @@
 
     Map<String, Object> getMonthAverageBySensor(Map<String, Object> parameters);
 
+    Map<String, Object> getAverageBySensor(Map<String, Object> parameters);
+
     List<Map<String, Object>> getAreaAvgDataByAreaCode(Map<String, Object> parameters) throws Exception;
 
     Map<String, List<List<Double>>> queryLineChartDateByCrieria(LineChartCriteria lineChartCriteria);
diff --git a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
index f7b8b50..21f7b13 100644
--- a/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java
@@ -444,6 +444,34 @@
     return result;
   }
 
+    @Override
+    public Map<String, Object> getAverageBySensor(Map<String, Object> parameters) {
+        //ValidateUtil.notNull(parameters.get("mac"), "param.is.null");
+        Object sensorKey = parameters.remove("macKey");
+        ValidateUtil.notNull(sensorKey, "param.is.null");
+        Map<String, Object> result = new HashMap<String, Object>();
+        LocalDate end = LocalDate.now(), start;
+        // ���������������������������������������
+        if (1 == end.getDayOfMonth()) {
+            // ���������1���00:00:00
+            start = end.plusDays(-1).with(TemporalAdjusters.firstDayOfMonth());
+        } else {
+            // ���������1���00:00:00
+            start = end.with(TemporalAdjusters.firstDayOfMonth());
+        }
+        parameters.put("start", start);
+        parameters.put("end", end);
+        parameters.put("sensorKeys", Arrays.asList(sensorKey));
+
+        Map<String, Double> average = historyMinutelyMapper.getAvgByDevice(parameters);
+        if (isEmpty(average)) {
+            result.put("average", NULL_VALUE);
+        } else {
+            result.put("average", String.format("%.2f", average.get(sensorKey)));
+        }
+        return result;
+    }
+
   /**
    * ������������������������������������������������������������������
    *
diff --git a/src/main/resources/mapper/HistoryMinutelyMapper.xml b/src/main/resources/mapper/HistoryMinutelyMapper.xml
index 4ba0029..bf83a38 100644
--- a/src/main/resources/mapper/HistoryMinutelyMapper.xml
+++ b/src/main/resources/mapper/HistoryMinutelyMapper.xml
@@ -31,6 +31,32 @@
         AND time &lt; #{end}
     </select>
 
+    <select id="getAvgByDevice" resultType="java.util.Map">
+        SELECT
+        <foreach collection="sensorKeys" separator="," item="sensorKey">
+      AVG(json->'$.${sensorKey}[0]') AS '${sensorKey}'
+    </foreach>
+        FROM
+        history_daily
+        WHERE
+        mac IN
+        (SELECT
+        d.mac
+        FROM
+        device d
+        WHERE
+        d.is_delete = 0
+        <if test="monitorPointId != null">
+      AND d.monitor_point_id = #{monitorPointId}
+    </if>
+        <if test="mac != null">
+      AND d.mac = #{mac}
+    </if>
+        )
+        AND time >= #{start}
+        AND time &lt; #{end}
+    </select>
+
     <select id="getMonitorPointOrDeviceAvgData" resultType="java.util.Map">
         SELECT
         DATE_FORMAT(time, #{typeFormat}) time

--
Gitblit v1.8.0