From 33e323fb0e6959bf838867fc5582fbcd4749abb1 Mon Sep 17 00:00:00 2001
From: xufenglei <xufenglei>
Date: Fri, 15 Jun 2018 08:53:26 +0800
Subject: [PATCH] 数据排名接口
---
src/main/resources/system/sysConfig.properties | 2
src/main/java/com/moral/service/HistoryService.java | 3
src/main/resources/mapper/HistoryMinutelyMapper.xml | 2
src/main/java/com/moral/service/impl/HistoryServiceImpl.java | 80 ++++++++++++++++++++
src/main/java/com/moral/mapper/HistoryMapper.java | 4 +
src/main/resources/mapper/HistoryMapper.xml | 64 +++++++++++++++
pom.xml | 11 ++
src/main/java/com/moral/controller/ScreenController.java | 21 +++++
8 files changed, 184 insertions(+), 3 deletions(-)
diff --git a/pom.xml b/pom.xml
index 7125301..f48af0d 100644
--- a/pom.xml
+++ b/pom.xml
@@ -186,6 +186,17 @@
<scope>system</scope>
<systemPath>${project.basedir}/lib/taobao-sdk-java-auto_1455552377940-20160607.jar</systemPath>
</dependency>
+
+ <dependency>
+ <groupId>io.springfox</groupId>
+ <artifactId>springfox-swagger2</artifactId>
+ <version>2.7.0</version>
+ </dependency>
+ <dependency>
+ <groupId>io.springfox</groupId>
+ <artifactId>springfox-swagger-ui</artifactId>
+ <version>2.7.0</version>
+ </dependency>
</dependencies>
<build>
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index 5f18891..d827c2a 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -61,6 +61,11 @@
import com.moral.service.MonitorPointService;
import com.moral.service.SensorService;
+import io.swagger.annotations.Api;
+import io.swagger.annotations.ApiImplicitParam;
+import io.swagger.annotations.ApiImplicitParams;
+import io.swagger.annotations.ApiOperation;
+
/**
* The Class ScreenController.������������
*/
@@ -68,6 +73,7 @@
@RequestMapping("/screen")
//@CrossOrigin(origins = "*", maxAge = 3600)
@SuppressWarnings({ "rawtypes", "unchecked", "unused" })
+@Api(tags = "Screen", description = "������������")
public class ScreenController {
public static Logger log = Logger.getLogger(ScreenController.class);
@Resource
@@ -534,4 +540,19 @@
public ResultBean<List<Integer>> getVersions(Integer mptid){
return new ResultBean<>(monitorPointService.queryVersionsById(mptid));
}
+
+ @GetMapping("region_ranking_data")
+ @ApiOperation(value = "������������������", notes = "������������������")
+ @ApiImplicitParams(value = {
+ @ApiImplicitParam(name = "regionCode", value = "���������", required = true, paramType = "query", dataType = "String"),
+ @ApiImplicitParam(name = "dimension", value = "������(profession���region���monitorPoint������)", required = true, paramType = "query", dataType = "String"),
+ @ApiImplicitParam(name = "sensorKey", value = "���������key", required = true, paramType = "query", dataType = "String"),
+ @ApiImplicitParam(name = "accountId", value = "������id", required = true, paramType = "query", dataType = "int"),
+ })
+ public ResultBean<List<Map<String, Object>>> getRegionRankingData(HttpServletRequest request){
+ Map<String, Object> parameters = getParametersStartingWith(request, null);
+ List<Map<String, Object>> list = historyService.getRegionRankingData(parameters);
+ return new ResultBean<List<Map<String, Object>>>(list);
+ }
+
}
diff --git a/src/main/java/com/moral/mapper/HistoryMapper.java b/src/main/java/com/moral/mapper/HistoryMapper.java
index 68db071..3afddc2 100644
--- a/src/main/java/com/moral/mapper/HistoryMapper.java
+++ b/src/main/java/com/moral/mapper/HistoryMapper.java
@@ -10,4 +10,8 @@
String selectValueByMacAndTime(@Param("mac")String mac, @Param("time")Date time);
List<Map<String, Object>> getAreaAllDataByAccount(Map<String, Object> parameters);
+ List<Map<String, Object>> getRegionRankingData(Map<String, Object> parameters);
+
+ List<Map<String, Object>> selectNextLevelRegion(Map<String, Object> parameters);
+
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/HistoryService.java b/src/main/java/com/moral/service/HistoryService.java
index e08b6a6..dec4d08 100644
--- a/src/main/java/com/moral/service/HistoryService.java
+++ b/src/main/java/com/moral/service/HistoryService.java
@@ -1,6 +1,7 @@
package com.moral.service;
import java.util.Date;
+import java.util.List;
import java.util.Map;
@@ -11,4 +12,6 @@
Map<String, Object> getDeviceRankingBySensorAverage(Map<String, Object> parameters);
String queryValueByMacAndTime(String mac, Date time);
+
+ List<Map<String, Object>> getRegionRankingData(Map<String, Object> parameters);
}
diff --git a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
index 4a9748f..9eabd4d 100644
--- a/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HistoryServiceImpl.java
@@ -8,9 +8,11 @@
import org.springframework.stereotype.Service;
import com.moral.common.util.ValidateUtil;
+import com.moral.entity.Profession;
import com.moral.entity.Sensor;
import com.moral.mapper.DeviceMapper;
import com.moral.mapper.HistoryMapper;
+import com.moral.mapper.ProfessionMapper;
import com.moral.mapper.SensorMapper;
import com.moral.service.AccountService;
import com.moral.service.HistoryService;
@@ -29,6 +31,9 @@
@Resource
private SensorMapper sensorMapper;
+
+ @Resource
+ private ProfessionMapper professionMapper;
@Override
public Map<String, Object> getAllSensorAverageByDevice(Map<String, Object> parameters) throws Exception {
@@ -79,4 +84,79 @@
public String queryValueByMacAndTime(String mac, Date time){
return historyMapper.selectValueByMacAndTime(mac, time);
}
+
+ @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");
+
+ String regionCode = parameters.get("regionCode").toString();
+ String regionType = "village_code";
+ String nextLevel = "";
+ if (regionCode.length() == 6) {
+ if (regionCode.endsWith("0000")) {
+ regionType = "province_code";
+ nextLevel = "city";
+ } else if (regionCode.endsWith("00")) {
+ regionType = "city_code";
+ nextLevel = "area";
+ } else {
+ regionType = "area_code";
+ nextLevel = "town";
+ }
+ } else {
+ if (regionCode.endsWith("000")) {
+ regionType = "town_code";
+ nextLevel = "village";
+ }
+ }
+ parameters.put("regionType", regionType);
+ parameters.put("nextLevel", nextLevel);
+ parameters.put("start", LocalDateTime.now().minusMinutes(5));
+
+ accountService.setOrgIdsByAccount(parameters);
+ List<Map<String, Object>> result = historyMapper.getRegionRankingData(parameters);
+ if ("profession".equals(parameters.get("dimension"))) {
+ List<Profession> professions = professionMapper.selectAll();
+ for (Map<String, Object> map : result) {
+ for (Profession profession : professions) {
+ if (map.get("name").equals(profession.getName())) {
+ professions.remove(profession);
+ 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<Map<String, Object>> regions = historyMapper.selectNextLevelRegion(parameters);
+ for (int i = result.size() - 1; i >= 0; i--) {
+ Map<String, Object> map = result.get(i);
+ for (Map<String, Object> region : regions) {
+ if ((map.get("code") == null)) {
+ result.remove(map);
+ break;
+ }
+ if (map.get("code").equals(region.get("code"))) {
+ map.put("name", region.get("name"));
+ regions.remove(region);
+ break;
+ }
+ }
+ }
+ for (Map<String, Object> region : regions) {
+ Map<String, Object> map = new HashMap<String, Object>();
+ map.put("name", region.get("name"));
+ map.put("avg", 0);
+ result.add(map);
+ }
+ }
+ return result;
+ }
}
diff --git a/src/main/resources/mapper/HistoryMapper.xml b/src/main/resources/mapper/HistoryMapper.xml
index f7c82a8..2408a33 100644
--- a/src/main/resources/mapper/HistoryMapper.xml
+++ b/src/main/resources/mapper/HistoryMapper.xml
@@ -6,7 +6,7 @@
where time = #{time} and mac = #{mac}
limit 0,1
</select>
- <select id="getAreaAllDataByAccount" resultType="java.util.Map">
+ <select id="getAreaAllDataByAccount" resultType="java.util.LinkedHashMap">
SELECT
<if test="macKey != null">
d.`name`,
@@ -35,4 +35,66 @@
ORDER BY ${macKey} desc
</if>
</select>
+
+ <select id="getRegionRankingData" resultType="java.util.Map">
+ SELECT
+ AVG( h.`value` -> '$.${sensorKey}[0]' ) avg ,
+ <if test="dimension == 'profession'">
+ p.`name`
+ </if>
+ <if test="dimension == 'monitorPoint'">
+ mp.`name`
+ </if>
+ <if test="dimension == 'region'">
+ mp.${nextLevel}_code AS 'code'
+ </if>
+ FROM
+ <if test="dimension == 'profession'">
+ profession p ,
+ </if>
+ device d,
+ history h,
+ monitor_point mp
+ WHERE
+ d.mac = h.mac
+ AND d.is_delete = '0'
+ AND mp.is_delete = '0'
+ AND d.monitor_point_id = mp.id
+ AND h.time >= #{start}
+ AND mp.${regionType} = #{regionCode}
+ <if test="dimension == 'profession'">
+ AND d.profession_id = p.id
+ </if>
+ <if test="orgIds != null and orgIds.size > 0">
+ AND mp.organization_id IN
+ <foreach collection="orgIds" open="(" separator="," close=")" item="listItem">
+ #{listItem}
+ </foreach>
+ </if>
+ GROUP BY
+ <if test="dimension == 'profession'">
+ p.id
+ </if>
+ <if test="dimension == 'monitorPoint'">
+ mp.id
+ </if>
+ <if test="dimension == 'region'">
+ mp.${nextLevel}_code
+ </if>
+ ORDER BY
+ avg DESC
+ <if test="dimension == 'monitorPoint'">
+ LIMIT 0,50
+ </if>
+ </select>
+
+ <select id="selectNextLevelRegion" resultType="java.util.Map">
+ SELECT
+ ${nextLevel}_code AS 'code',
+ ${nextLevel}_name AS 'name'
+ FROM
+ ${nextLevel}
+ WHERE
+ ${regionType} = #{regionCode}
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/HistoryMinutelyMapper.xml b/src/main/resources/mapper/HistoryMinutelyMapper.xml
index d9c4cd8..1509a27 100644
--- a/src/main/resources/mapper/HistoryMinutelyMapper.xml
+++ b/src/main/resources/mapper/HistoryMinutelyMapper.xml
@@ -31,7 +31,7 @@
AND time < #{end}
</select>
- <select id="getMonitorPointOrDeviceAvgData" resultType="java.util.Map">
+ <select id="getMonitorPointOrDeviceAvgData" resultType="java.util.LinkedHashMap">
SELECT
DATE_FORMAT(time, #{typeFormat}) time
<foreach collection="sensorKeys" open="," separator="," item="sensorKey">
diff --git a/src/main/resources/system/sysConfig.properties b/src/main/resources/system/sysConfig.properties
index 62a5580..d7432f1 100644
--- a/src/main/resources/system/sysConfig.properties
+++ b/src/main/resources/system/sysConfig.properties
@@ -27,5 +27,5 @@
e19-standard=50
orgId=5
password=123456
-noFilters=/**/*.css,/**/*.json,/alarm/**,/screen/**,/**/*.jsp,/**/*.js,/**/*.gif,/**/*.png,/**/*.ico,/**/*.html,/**/*.map,/machineactivate/**,/device/**,/sensor/**,/mobile/**,/page/**
+noFilters=/**/*.css,/**/*.json,/alarm/**,/screen/**,/**/*.jsp,/**/*.js,/**/*.gif,/**/*.png,/**/*.ico,/**/*.html,/**/*.map,/machineactivate/**,/device/**,/sensor/**,/mobile/**,/page/**,/swagger*/**,/v2/**,/webjars/**
specialOrgIds=-1,5
\ No newline at end of file
--
Gitblit v1.8.0