From fe928b238dc59f38785ff5aabf4088d1b899c4b7 Mon Sep 17 00:00:00 2001
From: xufenglei <xufenglei>
Date: Sun, 30 Sep 2018 08:47:49 +0800
Subject: [PATCH] 接口 优化
---
src/main/resources/mapper/QualityDailyMapper.xml | 29 ++++++++++++++
src/main/resources/mapper/HangzhouAqiMapper.xml | 15 +++++++
src/main/java/com/moral/service/HangzhouAqiService.java | 2
src/main/java/com/moral/controller/ScreenController.java | 2
src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java | 40 +++++++++++++++-----
src/main/java/com/moral/mapper/HangzhouAqiMapper.java | 3 +
6 files changed, 79 insertions(+), 12 deletions(-)
diff --git a/src/main/java/com/moral/controller/ScreenController.java b/src/main/java/com/moral/controller/ScreenController.java
index 2894cdc..f0c0f43 100644
--- a/src/main/java/com/moral/controller/ScreenController.java
+++ b/src/main/java/com/moral/controller/ScreenController.java
@@ -705,7 +705,7 @@
@ApiImplicitParams(value = {
@ApiImplicitParam(name = "orgid",defaultValue = "5",value = "������id", required = true, paramType = "query", dataType = "Integer"),
})
- public ResultBean<List<Map>> getAqi24HoursNC(@RequestParam("orgid") Integer organizationId) {
+ public ResultBean<List<Map<String,Object>>> getAqi24HoursNC(@RequestParam("orgid") Integer organizationId) {
return new ResultBean<>(hangzhouAqiService.queryAqi24Hours(organizationId));
}
@GetMapping("mpt-dev-summary")
diff --git a/src/main/java/com/moral/mapper/HangzhouAqiMapper.java b/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
index e4e263c..d10a233 100644
--- a/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
+++ b/src/main/java/com/moral/mapper/HangzhouAqiMapper.java
@@ -11,4 +11,7 @@
public interface HangzhouAqiMapper extends BaseMapper<HangzhouAqi> {
public List<Map> selectAqisByCodeAndTimePeriod(@Param("code")String code,@Param("timePeriod") TimePeriod timePeriod);
+
+ List<Map<String, Object>> getAqisByOrganizationId(Map<String, Object> parameters);
+
}
\ No newline at end of file
diff --git a/src/main/java/com/moral/service/HangzhouAqiService.java b/src/main/java/com/moral/service/HangzhouAqiService.java
index 2138803..145b54e 100644
--- a/src/main/java/com/moral/service/HangzhouAqiService.java
+++ b/src/main/java/com/moral/service/HangzhouAqiService.java
@@ -10,5 +10,5 @@
* @Description: ������api������������
*/
public interface HangzhouAqiService {
- public List<Map> queryAqi24Hours(@NotNull Integer orgId);
+ public List<Map<String, Object>> queryAqi24Hours(@NotNull Integer orgId);
}
diff --git a/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java b/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
index 7e01d91..4ae4086 100644
--- a/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
+++ b/src/main/java/com/moral/service/impl/HangzhouAqiServiceImpl.java
@@ -1,18 +1,23 @@
package com.moral.service.impl;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import javax.annotation.Resource;
+
+import org.springframework.stereotype.Service;
+import org.springframework.util.ObjectUtils;
+
import com.moral.common.exception.BusinessException;
-import com.moral.common.util.ReportTimeFormat;
import com.moral.entity.Organization;
import com.moral.entity.charts.TimePeriod;
-import com.moral.entity.charts.TimeUnits;
import com.moral.mapper.HangzhouAqiMapper;
import com.moral.mapper.OrganizationMapper;
import com.moral.service.HangzhouAqiService;
-import org.apache.commons.collections.CollectionUtils;
-import org.springframework.stereotype.Service;
-
-import javax.annotation.Resource;
-import java.util.*;
/**
* @Auther: fengxiang
@@ -34,9 +39,11 @@
* @param orgId
* @return ���������������24���������������������������������������0
*/
- @Override
- public List<Map> queryAqi24Hours(Integer orgId) {
- List<Map> aqi24HoursValues = new ArrayList<>(24);
+ @SuppressWarnings("serial")
+ @Override
+ public List<Map<String, Object>> queryAqi24Hours(Integer orgId) {
+ List<Map<String, Object>> aqi24HoursValues = new ArrayList<>(24);
+/*
Date now = ReportTimeFormat.getFormatDate(ReportTimeFormat.HOUR_FORMAT);
Date start = ReportTimeFormat.dateCalc(now, TimeUnits.HOUR,-24);
Date end = ReportTimeFormat.dateCalc(now, TimeUnits.MILLISECOND,-1);;
@@ -66,6 +73,19 @@
aqiItem.put("aqi",aqiValue);
aqi24HoursValues.add(aqiItem);
}
+*/
+ Organization organization = organizationMapper.selectByPrimaryKey(orgId);
+
+ Map<String, Object> parameters = new HashMap<String, Object>(){{
+ put("end", LocalDateTime.now());
+ put("cityCode", organization.getAreaCode());
+ }};
+ aqi24HoursValues = hangzhouAqiMapper.getAqisByOrganizationId(parameters);
+ if (ObjectUtils.isEmpty(aqi24HoursValues)) {
+ parameters.put("cityCode", organization.getCityCode());
+ aqi24HoursValues = hangzhouAqiMapper.getAqisByOrganizationId(parameters);
+ }
+ Collections.reverse(aqi24HoursValues);
return aqi24HoursValues;
}
private Integer getCode(Integer orgId,boolean isGetCityCode)
diff --git a/src/main/resources/mapper/HangzhouAqiMapper.xml b/src/main/resources/mapper/HangzhouAqiMapper.xml
index 75d5c97..bd3e260 100644
--- a/src/main/resources/mapper/HangzhouAqiMapper.xml
+++ b/src/main/resources/mapper/HangzhouAqiMapper.xml
@@ -32,4 +32,19 @@
)
order by time asc
</select>
+
+ <select id="getAqisByOrganizationId" resultType="map">
+ SELECT
+ h.aqi_json ->> '$.AQI' * 1 aqi,
+ DATE_FORMAT( h.time, '%H' ) * 1 time
+ FROM
+ hangzhou_aqi h
+ <where>
+ h.city_code = #{cityCode}
+ AND h.time <![CDATA[<]]> #{end}
+ </where>
+ ORDER BY
+ h.time DESC
+ LIMIT 24
+ </select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/mapper/QualityDailyMapper.xml b/src/main/resources/mapper/QualityDailyMapper.xml
index 0e17f92..263964f 100644
--- a/src/main/resources/mapper/QualityDailyMapper.xml
+++ b/src/main/resources/mapper/QualityDailyMapper.xml
@@ -1,6 +1,7 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.QualityDailyMapper">
+<!--
<select id="getQualityCountDayByRegion" resultType="map">
SELECT
COUNT( * ) count
@@ -13,4 +14,32 @@
AND quality < 3
</where>
</select>
+ -->
+
+ <select id="getQualityCountDayByRegion" resultType="map">
+ SELECT COUNT(*) count
+ FROM (
+ SELECT
+ MAX(CASE aqi_json ->> '$.quality'
+ WHEN "���" THEN 1
+ WHEN "���" THEN 2
+ WHEN "������������" THEN 3
+ WHEN "������������" THEN 4
+ WHEN "������������" THEN 5
+ WHEN "������������" THEN 6
+ ELSE 0 END
+ ) AS quality
+ FROM
+ `hangzhou_aqi`
+ <where>
+ time >= #{start}
+ AND time < #{end}
+ AND city_code = #{cityCode}
+ </where>
+ GROUP BY DATE_FORMAT( time, '%Y-%m-%d' )
+ ) t
+ <where>
+ t.quality < 3
+ </where>
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0