From 1fac32e9cc64d7bff76b6647d0007dd3711bcd69 Mon Sep 17 00:00:00 2001
From: zmc <zmc_li@foxmail.com>
Date: 星期一, 07 八月 2023 17:58:51 +0800
Subject: [PATCH] SpringBoot后台
---
src/main/java/com/job/zsc/ServletInitializer.java | 12 +
src/main/java/com/job/zsc/service/impl/FugitiveDustServiceImpl.java | 54 ++++++
src/main/java/com/job/zsc/pojo/AnalysisDustData.java | 26 ++
src/main/java/com/job/zsc/mapper/AnalysisDataMapper.java | 2
src/main/java/com/job/zsc/service/FugitiveDustService.java | 18 ++
src/main/java/com/job/zsc/service/Vue3FumeService.java | 5
pom.xml | 42 ++--
src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml | 42 ++++
src/main/java/com/job/zsc/service/impl/Vue3FumeServiceImpl.java | 12 +
src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml | 105 ++++++++---
src/main/java/com/job/zsc/controller/Vue3FumeController.java | 19 +
src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java | 7
src/main/java/com/job/zsc/utils/UUIDGenerator.kt | 2
src/main/java/com/job/zsc/pojo/DustSiteData.java | 21 ++
src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml | 44 ++++
src/main/java/com/job/zsc/controller/FugitiveDustController.java | 54 ++++++
src/main/java/com/job/zsc/mapper/FugitiveDustMapper.java | 23 ++
src/main/resources/application.yml | 7
src/main/java/com/job/zsc/service/impl/AnalysisDataServiceImpl.java | 2
19 files changed, 438 insertions(+), 59 deletions(-)
diff --git a/pom.xml b/pom.xml
index 95c445e..ebc952c 100644
--- a/pom.xml
+++ b/pom.xml
@@ -5,7 +5,7 @@
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
- <version>3.0.5</version>
+ <version>2.5.15</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
@@ -13,6 +13,7 @@
<artifactId>web_01</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>web_01</name>
+ <packaging>war</packaging>
<description>web_01</description>
<properties>
@@ -26,14 +27,19 @@
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
- <version>3.0.0</version>
+ <version>2.3.1</version>
</dependency>
+<!-- <dependency>-->
+<!-- <groupId>com.mysql</groupId>-->
+<!-- <artifactId>mysql-connector-j</artifactId>-->
+<!-- <scope>runtime</scope>-->
+<!-- </dependency>-->
<dependency>
- <groupId>com.mysql</groupId>
- <artifactId>mysql-connector-j</artifactId>
- <scope>runtime</scope>
+ <groupId>mysql</groupId>
+ <artifactId>mysql-connector-java</artifactId>
+ <version>8.0.33</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
@@ -81,19 +87,19 @@
<build>
<plugins>
- <plugin>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-maven-plugin</artifactId>
- <version>3.0.5</version>
- <configuration>
- <excludes>
- <exclude>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- </exclude>
- </excludes>
- </configuration>
- </plugin>
+<!-- <plugin>-->
+<!-- <groupId>org.springframework.boot</groupId>-->
+<!-- <artifactId>spring-boot-maven-plugin</artifactId>-->
+<!-- <version>3.0.5</version>-->
+<!-- <configuration>-->
+<!-- <excludes>-->
+<!-- <exclude>-->
+<!-- <groupId>org.projectlombok</groupId>-->
+<!-- <artifactId>lombok</artifactId>-->
+<!-- </exclude>-->
+<!-- </excludes>-->
+<!-- </configuration>-->
+<!-- </plugin>-->
</plugins>
</build>
diff --git a/src/main/java/com/job/zsc/ServletInitializer.java b/src/main/java/com/job/zsc/ServletInitializer.java
new file mode 100644
index 0000000..0ee891d
--- /dev/null
+++ b/src/main/java/com/job/zsc/ServletInitializer.java
@@ -0,0 +1,12 @@
+package com.job.zsc;
+
+import org.springframework.boot.builder.SpringApplicationBuilder;
+import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
+
+public class ServletInitializer extends SpringBootServletInitializer {
+
+ @Override
+ protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
+ return builder.sources(QianduanApplication.class);
+ }
+}
diff --git a/src/main/java/com/job/zsc/controller/FugitiveDustController.java b/src/main/java/com/job/zsc/controller/FugitiveDustController.java
new file mode 100644
index 0000000..7225986
--- /dev/null
+++ b/src/main/java/com/job/zsc/controller/FugitiveDustController.java
@@ -0,0 +1,54 @@
+package com.job.zsc.controller;
+
+
+import com.job.zsc.pojo.AnalysisDustData;
+import com.job.zsc.pojo.DustSiteData;
+import com.job.zsc.pojo.PageBean;
+import com.job.zsc.pojo.Result;
+import com.job.zsc.service.FugitiveDustService;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.web.bind.annotation.*;
+
+import java.util.List;
+
+
+@Slf4j
+@RequestMapping("/dust")
+@CrossOrigin
+@RestController
+public class FugitiveDustController {
+ @Autowired
+ private FugitiveDustService fugitiveDustService;
+
+
+
+ //鎵皹 鐩戞祴鐐瑰巻鍙叉暟鎹� 鍚庣鍒嗛〉
+ @GetMapping("/history1")
+ public Result conditonQueryAbnormalData3(@RequestParam(defaultValue = "1") Integer page,
+ @RequestParam(defaultValue = "20")Integer pageSize,
+ String siteName,String mnCode,String beginTime, String endTime,String[] scenarioType ){
+ PageBean pageBean = fugitiveDustService.page(page,pageSize,siteName,mnCode, beginTime,endTime,scenarioType);
+ return Result.success(pageBean);
+ }
+
+// 杩斿洖鐩戞祴鐐规暟鎹腑鐨勪笉鍚岀殑鍦烘櫙
+ @GetMapping("/scenario")
+ public Result conditonQueryHistoryData(){
+ List<DustSiteData> list = fugitiveDustService.scenarioData();
+ return Result.success(list);
+ }
+// 杩斿洖鎵�鏈夌珯鐐瑰悕瀛�
+ @GetMapping("/sitename")
+ public Result allSiteName(){
+ List<DustSiteData> list = fugitiveDustService.allSiteName();
+ return Result.success(list);
+ }
+
+// 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂闂存鏌ヨ鍧囧�煎拰
+ @GetMapping("/analysisdata")
+ public Result analysisdata( String siteName,String beginTime, String endTime){
+ List<AnalysisDustData> list = fugitiveDustService.analysisdata(siteName,beginTime,endTime);
+ return Result.success(list);
+ }
+}
diff --git a/src/main/java/com/job/zsc/controller/Vue3FumeController.java b/src/main/java/com/job/zsc/controller/Vue3FumeController.java
index 496aade..0f489c4 100644
--- a/src/main/java/com/job/zsc/controller/Vue3FumeController.java
+++ b/src/main/java/com/job/zsc/controller/Vue3FumeController.java
@@ -134,11 +134,20 @@
return Result.success(list);
}
-// @GetMapping("/shopnum")
-// public Result shopNum(){
-// String num = vue3FumeService.shopNum();
-// return Result.success(num);
-// }
+// 杩斿洖鍙緵鐖彇鐨勫簵閾哄悕绉� 锛堣繖浜涘簵閾哄悕绉版湁鐨勬槸缂╁啓鐨� 瀵瑰簲涓巔ython缂栫爜 涓嶈兘鐢∕ySQL鍏朵粬琛ㄧ殑搴楅摵鍚嶄唬鏇匡級
+ @GetMapping("/shopnamepy")
+ public Result shopnamePythonUse(){
+ List<FumeHistoryData> list = vue3FumeService.shopnamePythonUse();
+ return Result.success(list);
+ }
+
+
+ // 杩斿洖鍘嗗彶鏁版嵁琛ㄧ殑璺濅粖鐨勬渶鏂版椂闂�
+ @GetMapping("/lastesttime")
+ public Result lastestTimeFromHistoryData(){
+ List<FumeHistoryData> list = vue3FumeService.lastestTimeFromHistoryData();
+ return Result.success(list);
+ }
}
diff --git a/src/main/java/com/job/zsc/mapper/AnalysisDataMapper.java b/src/main/java/com/job/zsc/mapper/AnalysisDataMapper.java
index 96c081b..2005f0c 100644
--- a/src/main/java/com/job/zsc/mapper/AnalysisDataMapper.java
+++ b/src/main/java/com/job/zsc/mapper/AnalysisDataMapper.java
@@ -18,7 +18,7 @@
List<AnalysisData> find(String shopname,String value,String begin, String end);
- @Insert("insert into from_web_analysis_data values(#{fumeDevId},#{fumeDate},#{fumeDayMin},#{fumeDayMax},#{fumeDayAverage},#{purifierOpenRate},#{fumeMinuteExceedingNum},#{fumeOverStandardRate},#{fumeDataEfficiency},#{dailyOnlineRate},#{noonOnlineRate},#{nightOnlineRate},#{keyOnlineRate},#{noonValidRate},#{nightValidRate},#{keyValidRate},#{noonOpeningCount},#{nightOpeningCount},#{keyOpeningRate},#{noonExceedingNum},#{nightExceedingNum},#{keyExceedingRate})")
+ @Insert("insert into fm_web_analysis_data values(#{fumeDevId},#{fumeDate},#{fumeDayMin},#{fumeDayMax},#{fumeDayAverage},#{purifierOpenRate},#{fumeMinuteExceedingNum},#{fumeOverStandardRate},#{fumeDataEfficiency},#{dailyOnlineRate},#{noonOnlineRate},#{nightOnlineRate},#{keyOnlineRate},#{noonValidRate},#{nightValidRate},#{keyValidRate},#{noonOpeningCount},#{nightOpeningCount},#{keyOpeningRate},#{noonExceedingNum},#{nightExceedingNum},#{keyExceedingRate})")
void write(AnalysisData analysisData);
Integer search(String shopname,String value, String begin, String end);
diff --git a/src/main/java/com/job/zsc/mapper/FugitiveDustMapper.java b/src/main/java/com/job/zsc/mapper/FugitiveDustMapper.java
new file mode 100644
index 0000000..e9eb6ed
--- /dev/null
+++ b/src/main/java/com/job/zsc/mapper/FugitiveDustMapper.java
@@ -0,0 +1,23 @@
+package com.job.zsc.mapper;
+
+import com.job.zsc.pojo.AnalysisDustData;
+import com.job.zsc.pojo.DustSiteData;
+import org.apache.ibatis.annotations.Mapper;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.annotations.Select;
+
+import java.util.List;
+
+@Mapper
+public interface FugitiveDustMapper {
+
+ List<DustSiteData> conditonQueryDustHistoryData(String siteName, String mnCode,String beginTime, String endTime,@Param("scenarioType") String[] scenarioType) ;
+
+ @Select("select distinct Type_Name from monitor_site")
+ List<DustSiteData> scenarioData();
+
+ @Select("select DISTINCT name from ja_t_dust_site_info ")
+ List<DustSiteData> allSiteName();
+
+ List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime);
+}
diff --git a/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java b/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
index 20cbfdc..74826e9 100644
--- a/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
+++ b/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
@@ -66,6 +66,13 @@
List<FumeAbnormalData> earlyAndLastTime();
+ @Select("select max(MV_Data_Time) as MV_Data_Time from fd_t_minutevalue")
+ List<FumeHistoryData> lastestTimeFromHistoryData();
+
+ @Select("select * from shop_names order by MV_ID")
+ List<FumeHistoryData> shopnamePythonUse();
+
+
// 杩斿洖璁惧淇℃伅琛ㄧ殑鏉℃暟
// @Select("SELECT count(*) from ea_t_device_info")
// String shopNum();
diff --git a/src/main/java/com/job/zsc/pojo/AnalysisDustData.java b/src/main/java/com/job/zsc/pojo/AnalysisDustData.java
new file mode 100644
index 0000000..6392325
--- /dev/null
+++ b/src/main/java/com/job/zsc/pojo/AnalysisDustData.java
@@ -0,0 +1,26 @@
+package com.job.zsc.pojo;
+
+import com.fasterxml.jackson.annotation.JsonFormat;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+import java.time.LocalDate;
+
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+public class AnalysisDustData {
+
+ private String mnCode;
+
+ @JsonFormat(pattern = "yyyy-MM-dd")
+ private LocalDate lst;
+
+ private Double dayAvg;
+ private String dayOnline;
+ private String dayValid;
+ private String dayExceeding;
+
+
+}
diff --git a/src/main/java/com/job/zsc/pojo/DustSiteData.java b/src/main/java/com/job/zsc/pojo/DustSiteData.java
new file mode 100644
index 0000000..1cac514
--- /dev/null
+++ b/src/main/java/com/job/zsc/pojo/DustSiteData.java
@@ -0,0 +1,21 @@
+package com.job.zsc.pojo;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+// 鐩戞祴鐐�
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+public class DustSiteData {
+ private String name;
+ private String address;
+ private String sName;
+ private String mnCode;
+ private String typeName;
+ private String dustValue;
+ private String lst;
+ private String quality;
+ private String groupName;
+}
diff --git a/src/main/java/com/job/zsc/service/FugitiveDustService.java b/src/main/java/com/job/zsc/service/FugitiveDustService.java
new file mode 100644
index 0000000..0c38476
--- /dev/null
+++ b/src/main/java/com/job/zsc/service/FugitiveDustService.java
@@ -0,0 +1,18 @@
+package com.job.zsc.service;
+
+import com.job.zsc.pojo.AnalysisDustData;
+import com.job.zsc.pojo.DustSiteData;
+import com.job.zsc.pojo.PageBean;
+
+import java.util.List;
+
+public interface FugitiveDustService {
+
+ PageBean page(Integer page, Integer pageSize, String siteName, String mnCode, String beginTime, String endTime,String[] scenarioType);
+
+ List<DustSiteData> scenarioData();
+
+ List<DustSiteData> allSiteName();
+
+ List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime);
+}
diff --git a/src/main/java/com/job/zsc/service/Vue3FumeService.java b/src/main/java/com/job/zsc/service/Vue3FumeService.java
index 3cef51e..cc19868 100644
--- a/src/main/java/com/job/zsc/service/Vue3FumeService.java
+++ b/src/main/java/com/job/zsc/service/Vue3FumeService.java
@@ -41,5 +41,10 @@
List<FumeAbnormalData> earlyAndLastTime();
+ List<FumeHistoryData> lastestTimeFromHistoryData();
+
+ List<FumeHistoryData> shopnamePythonUse();
+
+
// String shopNum();
}
diff --git a/src/main/java/com/job/zsc/service/impl/AnalysisDataServiceImpl.java b/src/main/java/com/job/zsc/service/impl/AnalysisDataServiceImpl.java
index 284f0e7..7a1e744 100644
--- a/src/main/java/com/job/zsc/service/impl/AnalysisDataServiceImpl.java
+++ b/src/main/java/com/job/zsc/service/impl/AnalysisDataServiceImpl.java
@@ -3,11 +3,13 @@
import com.job.zsc.mapper.AnalysisDataMapper;
import com.job.zsc.pojo.AnalysisData;
import com.job.zsc.service.AnalysisDataService;
+import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
+@Slf4j
@Service
public class AnalysisDataServiceImpl implements AnalysisDataService {
@Autowired
diff --git a/src/main/java/com/job/zsc/service/impl/FugitiveDustServiceImpl.java b/src/main/java/com/job/zsc/service/impl/FugitiveDustServiceImpl.java
new file mode 100644
index 0000000..1fd0a83
--- /dev/null
+++ b/src/main/java/com/job/zsc/service/impl/FugitiveDustServiceImpl.java
@@ -0,0 +1,54 @@
+package com.job.zsc.service.impl;
+
+import com.github.pagehelper.Page;
+import com.github.pagehelper.PageHelper;
+import com.job.zsc.mapper.FugitiveDustMapper;
+import com.job.zsc.pojo.AnalysisDustData;
+import com.job.zsc.pojo.DustSiteData;
+import com.job.zsc.pojo.PageBean;
+import com.job.zsc.service.FugitiveDustService;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.util.List;
+
+@Slf4j
+@Service
+public class FugitiveDustServiceImpl implements FugitiveDustService {
+
+ @Autowired
+ private FugitiveDustMapper fugitiveDustMapper;
+
+ @Override
+ public PageBean page(Integer page, Integer pageSize, String siteName, String mnCode, String beginTime, String endTime,String[] scenarioType) {
+ //璁剧疆鍒嗛〉鍙傛暟
+ PageHelper.startPage(page,pageSize);
+ //鎵ц鏌ヨ
+ List<DustSiteData> infoList=fugitiveDustMapper.conditonQueryDustHistoryData(siteName,mnCode,beginTime,endTime,scenarioType);
+ //鑾峰彇鏌ヨ缁撴灉
+ Page<DustSiteData> p=(Page<DustSiteData>)infoList;
+
+ //灏佽PageBean瀵硅薄*/
+ PageBean pageBean=new PageBean(p.getTotal(),p.getResult());
+ return pageBean;
+ }
+
+ @Override
+ public List<DustSiteData> scenarioData() {
+ List<DustSiteData> list = fugitiveDustMapper.scenarioData();
+ return list;
+ }
+
+ @Override
+ public List<DustSiteData> allSiteName() {
+ List<DustSiteData> list = fugitiveDustMapper.allSiteName();
+ return list;
+ }
+
+ @Override
+ public List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime) {
+ List<AnalysisDustData> list = fugitiveDustMapper.analysisdata(siteName,beginTime,endTime);
+ return list;
+ }
+}
diff --git a/src/main/java/com/job/zsc/service/impl/Vue3FumeServiceImpl.java b/src/main/java/com/job/zsc/service/impl/Vue3FumeServiceImpl.java
index da03a14..76ab380 100644
--- a/src/main/java/com/job/zsc/service/impl/Vue3FumeServiceImpl.java
+++ b/src/main/java/com/job/zsc/service/impl/Vue3FumeServiceImpl.java
@@ -116,6 +116,18 @@
return list;
}
+ @Override
+ public List<FumeHistoryData> lastestTimeFromHistoryData() {
+ List<FumeHistoryData> list = vue3FumeMapper.lastestTimeFromHistoryData();
+ return list;
+ }
+
+ @Override
+ public List<FumeHistoryData> shopnamePythonUse() {
+ List<FumeHistoryData> list = vue3FumeMapper.shopnamePythonUse();
+ return list;
+ }
+
// @Override
// public String shopNum() {
// String num = vue3FumeMapper.shopNum();
diff --git a/src/main/java/com/job/zsc/utils/UUIDGenerator.kt b/src/main/java/com/job/zsc/utils/UUIDGenerator.kt
index 18bcdd4..64b5fdb 100644
--- a/src/main/java/com/job/zsc/utils/UUIDGenerator.kt
+++ b/src/main/java/com/job/zsc/utils/UUIDGenerator.kt
@@ -1,4 +1,4 @@
-package cn.flightfeather.supervision.infrastructure.utils
+package com.job.zsc.utils
import org.apache.commons.logging.LogFactory
import java.net.InetAddress
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index ce8572e..a2eb7ce 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -1,6 +1,10 @@
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
+# url: jdbc:mysql://localhost:3306/fume
+# username: fume
+# password: fume_feiyu2023
+
url: jdbc:mysql://localhost:3306/qianduan_sql
username: root
password: 1234
@@ -9,4 +13,5 @@
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
map-underscore-to-camel-case: true
-
+server:
+ port: 8081
diff --git a/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml b/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
index 08973d1..27e68c5 100644
--- a/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
@@ -6,7 +6,7 @@
<select id="findByDevId" resultType="com.job.zsc.pojo.AnalysisData">
- select * from analysis_data
+ select * from fm_web_analysis_data
<where>
<if test="devId != null and devId != '' ">
fume_dev_id=#{devId}
@@ -18,51 +18,102 @@
</where>
</select>
+<!--淇℃伅鍜屾暟鎹湭鍒嗗紑鐨勬暟鎹〃鍒嗘瀽 ed_data-->
+<!-- <select id="find" resultType="com.job.zsc.pojo.AnalysisData">-->
+<!-- SELECT-->
+<!-- equipment_number as fume_dev_id,-->
+<!-- DATE(reporting_time) as fume_date,-->
+<!-- MIN(smoke_pop_density) as fume_day_min,-->
+
+<!-- MAX(smoke_pop_density) as fume_day_max,-->
+
+<!-- AVG(smoke_pop_density) as fume_day_average,-->
+
+<!-- CONCAT(ROUND(SUM(CASE WHEN smoke_pop_density > 0 and purifier > 0 THEN 1 ELSE 0 END)/144 * 100, 2), '%') as purifier_open_rate,-->
+<!-- SUM(CASE WHEN smoke_push_density >= 1 OR smoke_pop_density >= 1 THEN 1 ELSE 0 END) AS fume_minute_exceeding_num,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN smoke_push_density >= 1 OR smoke_pop_density >= 1 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_over_standard_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN smoke_push_density > 0 OR smoke_pop_density > 0 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_data_efficiency,-->
+
+<!-- CONCAT(ROUND(COUNT(*)/144 * 100, 2), '%') as daily_online_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_online_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as night_online_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_online_rate,-->
+
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_valid_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/24* 100, 2), '%') as night_valid_rate,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN ((TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59')) AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/48* 100, 2), '%') as key_valid_rate,-->
+
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as noon_opening_count,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as night_opening_count,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as 'key_opening_rate',-->
+
+<!-- SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '14:00:00') and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END) as noon_exceeding_num,-->
+<!-- SUM(CASE WHEN (TIME(attribution_time) BETWEEN '17:00:00' and '21:00:00') and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END) as night_exceeding_num,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN ((TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59'))and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_exceeding_rate-->
+<!-- FROM-->
+<!-- ed_data-->
+<!-- <where>-->
+<!-- <if test="shopname != null and shopname != '' ">-->
+<!-- shop_name like concat('%',#{shopname},'%')-->
+<!-- </if>-->
+<!-- <if test="value != null and value != '' ">-->
+<!-- or shop_name like concat('%',#{value},'%')-->
+<!-- </if>-->
+<!-- <if test="begin != null and end != null">-->
+<!-- and reporting_time between #{begin} and #{end}-->
+<!-- </if>-->
+<!-- </where>-->
+<!-- GROUP BY equipment_number, DATE(reporting_time)-->
+<!-- limit 0,500-->
+<!-- </select>-->
+<!--鏂拌〃-->
<select id="find" resultType="com.job.zsc.pojo.AnalysisData">
SELECT
- equipment_number as fume_dev_id,
- DATE(reporting_time) as fume_date,
- MIN(smoke_pop_density) as fume_day_min,
+ a.MV_Stat_Code as fume_dev_id,
- MAX(smoke_pop_density) as fume_day_max,
+ DATE(a.MV_Data_Time) as fume_date,
+ MIN(a.MV_Fume_Concentration2) as fume_day_min,
- AVG(smoke_pop_density) as fume_day_average,
+ MAX(a.MV_Fume_Concentration2) as fume_day_max,
- CONCAT(ROUND(SUM(CASE WHEN smoke_pop_density > 0 and purifier > 0 THEN 1 ELSE 0 END)/144 * 100, 2), '%') as purifier_open_rate,
- SUM(CASE WHEN smoke_push_density >= 1 OR smoke_pop_density >= 1 THEN 1 ELSE 0 END) AS fume_minute_exceeding_num,
- CONCAT(ROUND(SUM(CASE WHEN smoke_push_density >= 1 OR smoke_pop_density >= 1 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_over_standard_rate,
- CONCAT(ROUND(SUM(CASE WHEN smoke_push_density > 0 OR smoke_pop_density > 0 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_data_efficiency,
+ AVG(a.MV_Fume_Concentration2) as fume_day_average,
+
+ CONCAT(ROUND(SUM(CASE WHEN a.MV_Fume_Concentration2 > 0 and a.MV_Purifier_Electricity > 0 THEN 1 ELSE 0 END)/144 * 100, 2), '%') as purifier_open_rate,
+ SUM(CASE WHEN a.MV_Fume_Concentration >= 1 OR a.MV_Fume_Concentration2 >= 1 THEN 1 ELSE 0 END) AS fume_minute_exceeding_num,
+ CONCAT(ROUND(SUM(CASE WHEN a.MV_Fume_Concentration >= 1 OR a.MV_Fume_Concentration2 >= 1 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_over_standard_rate,
+ CONCAT(ROUND(SUM(CASE WHEN a.MV_Fume_Concentration > 0 OR a.MV_Fume_Concentration2 > 0 THEN 1 ELSE 0 END)/COUNT(*) * 100, 2), '%') as fume_data_efficiency,
CONCAT(ROUND(COUNT(*)/144 * 100, 2), '%') as daily_online_rate,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_online_rate,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as night_online_rate,
- CONCAT(ROUND(SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_online_rate,
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_online_rate,
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END)/24 * 100, 2), '%') as night_online_rate,
+ CONCAT(ROUND(SUM(CASE WHEN (TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59') or (TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_online_rate,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_valid_rate,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/24* 100, 2), '%') as night_valid_rate,
- CONCAT(ROUND(SUM(CASE WHEN ((TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59')) AND (smoke_push_density > 0 OR smoke_pop_density > 0) THEN 1 ELSE 0 END)/48* 100, 2), '%') as key_valid_rate,
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59' AND (a.MV_Fume_Concentration > 0 OR a.MV_Fume_Concentration2 > 0) THEN 1 ELSE 0 END)/24 * 100, 2), '%') as noon_valid_rate,
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59' AND (a.MV_Fume_Concentration > 0 OR a.MV_Fume_Concentration2 > 0) THEN 1 ELSE 0 END)/24* 100, 2), '%') as night_valid_rate,
+ CONCAT(ROUND(SUM(CASE WHEN ((TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59') or (TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59')) AND (a.MV_Fume_Concentration > 0 OR a.MV_Fume_Concentration2 > 0) THEN 1 ELSE 0 END)/48* 100, 2), '%') as key_valid_rate,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as noon_opening_count,
- CONCAT(ROUND(SUM(CASE WHEN TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as night_opening_count,
- CONCAT(ROUND(SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as 'key_opening_rate',
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as noon_opening_count,
+ CONCAT(ROUND(SUM(CASE WHEN TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59' THEN 1 ELSE 0 END) /24 * 100, 2), '%') as night_opening_count,
+ CONCAT(ROUND(SUM(CASE WHEN (TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59') or (TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59') THEN 1 ELSE 0 END)/48 * 100, 2), '%') as 'key_opening_rate',
- SUM(CASE WHEN (TIME(attribution_time) BETWEEN '10:00:00' and '14:00:00') and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END) as noon_exceeding_num,
- SUM(CASE WHEN (TIME(attribution_time) BETWEEN '17:00:00' and '21:00:00') and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END) as night_exceeding_num,
- CONCAT(ROUND(SUM(CASE WHEN ((TIME(attribution_time) BETWEEN '10:00:00' and '13:59:59') or (TIME(attribution_time) BETWEEN '17:00:00' and '20:59:59'))and (smoke_push_density >= 1 OR smoke_pop_density >= 1) THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_exceeding_rate
+ SUM(CASE WHEN (TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '14:00:00') and (a.MV_Fume_Concentration >= 1 OR a.MV_Fume_Concentration2 >= 1) THEN 1 ELSE 0 END) as noon_exceeding_num,
+ SUM(CASE WHEN (TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '21:00:00') and (a.MV_Fume_Concentration >= 1 OR a.MV_Fume_Concentration2 >= 1) THEN 1 ELSE 0 END) as night_exceeding_num,
+ CONCAT(ROUND(SUM(CASE WHEN ((TIME(a.MV_Data_Time) BETWEEN '10:00:00' and '13:59:59') or (TIME(a.MV_Data_Time) BETWEEN '17:00:00' and '20:59:59'))and (a.MV_Fume_Concentration >= 1 OR a.MV_Fume_Concentration2 >= 1) THEN 1 ELSE 0 END)/48 * 100, 2), '%') as key_exceeding_rate
FROM
- ed_data
+ fd_t_minutevalue as a
+ JOIN ea_t_device_info AS b ON a.MV_Stat_Code = b.DI_Code
<where>
<if test="shopname != null and shopname != '' ">
- shop_name like concat('%',#{shopname},'%')
+ b.DI_Name like concat('%',#{shopname},'%')
</if>
<if test="value != null and value != '' ">
- or shop_name like concat('%',#{value},'%')
+ or b.DI_Name like concat('%',#{value},'%')
</if>
<if test="begin != null and end != null">
- and reporting_time between #{begin} and #{end}
+ and a.MV_Data_Time between #{begin} and #{end}
</if>
</where>
- GROUP BY equipment_number, DATE(reporting_time)
+ GROUP BY a.MV_Stat_Code, DATE(a.MV_Data_Time)
limit 0,500
</select>
diff --git a/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml b/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml
new file mode 100644
index 0000000..1234589
--- /dev/null
+++ b/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml
@@ -0,0 +1,44 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper
+ PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
+<mapper namespace="com.job.zsc.mapper.FugitiveDustMapper">
+
+ <select id="conditonQueryDustHistoryData" resultType="com.job.zsc.pojo.DustSiteData">
+ select *
+ from monitor_site
+ <where>
+ <if test="siteName !=null and siteName != ''">
+ Name like concat('%',#{siteName},'%')
+ </if>
+ <if test="mnCode !=null and mnCode != ''">
+ and MN_Code = #{mnCode}
+ </if>
+ <if test="beginTime != null and endTime != null">
+ and Lst between #{beginTime} and #{endTime}
+ </if>
+
+ <if test="scenarioType != null and scenarioType != '' ">
+ and Type_Name in
+ <foreach item="item" collection="scenarioType" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </if>
+ </where>
+<!-- limit 0,1000-->
+ </select>
+
+<!--鍒嗘瀽鏁版嵁-->
+ <select id="analysisdata" resultType="com.job.zsc.pojo.AnalysisDustData">
+ select a.* from avg_dust_value as a join ja_t_dust_site_info as b on a.mn_code = b. mn_code
+ <where>
+ <if test="siteName !=null and siteName != ''">
+ b.name = #{siteName}
+ </if>
+
+ <if test="beginTime != null and endTime != null">
+ and a.Lst between #{beginTime} and #{endTime}
+ </if>
+ </where>
+ </select>
+</mapper>
\ No newline at end of file
diff --git a/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml b/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
index d6b9270..2e8cb91 100644
--- a/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
@@ -26,23 +26,53 @@
</select>
<!-- 鍘嗗彶娌圭儫鏌ヨ-->
+<!-- <select id="conditionQueryHistory" resultType="com.job.zsc.pojo.FumeHistoryData">-->
+<!-- select a1.MV_ID,a1.MV_Stat_Code,b.DI_Name,a1.MV_Data_Time,a1.MV_Fume_Concentration2,a1.MV_Fan_Electricity,a1.MV_Purifier_Electricity-->
+<!-- from fd_t_minutevalue as a1 , ea_t_device_info as b-->
+<!-- <where>-->
+<!-- <!– 鏉′欢涓虹┖鏃舵煡璇㈠叏閮�–>-->
+<!-- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">-->
+<!-- a1.MV_Stat_Code = b.DI_Code-->
+<!-- </if>-->
+<!-- <if test="devId != null and devId != '' ">-->
+<!-- and a1.MV_Stat_Code = #{devId} and b.DI_Code = #{devId}-->
+<!-- </if>-->
+
+<!-- <if test="beginTime != null and endTime != null">-->
+<!-- and a1.MV_Data_Time between #{beginTime} and #{endTime}-->
+<!-- </if>-->
+<!-- </where>-->
+<!-- order by MV_Data_Time asc-->
+<!-- </select>-->
+ <!-- 鍘嗗彶娌圭儫鏌ヨ-->
<select id="conditionQueryHistory" resultType="com.job.zsc.pojo.FumeHistoryData">
- select a1.MV_ID,a1.MV_Stat_Code,b.DI_Name,a1.MV_Data_Time,a1.MV_Fume_Concentration2,a1.MV_Fan_Electricity,a1.MV_Purifier_Electricity
- from fd_t_minutevalue as a1 , ea_t_device_info as b
+ SELECT d.DI_Name, c.*
+ FROM (
+ SELECT a.*
+ FROM fd_t_minutevalue as a
+ LEFT JOIN fd_t_minutevalue as b ON a.MV_Data_Time = b.MV_Data_Time AND a.MV_Isduplication < b.MV_Isduplication
+ WHERE b.MV_ID IS NULL AND a.MV_Stat_Code = #{devId})
+ as c,ea_t_device_info as d
+
+<!-- CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS-->
+<!-- SELECT a.*-->
+<!-- FROM fd_t_minutevalue AS a-->
+<!-- LEFT JOIN fd_t_minutevalue AS b ON a.MV_Data_Time = b.MV_Data_Time AND a.MV_Isduplication < b.MV_Isduplication WHERE b.MV_ID IS NULL AND a.MV_Stat_Code = 'GLHB00000000016004'-->
+
<where>
<!-- 鏉′欢涓虹┖鏃舵煡璇㈠叏閮�-->
<if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- a1.MV_Stat_Code = b.DI_Code
+ c.MV_Stat_Code = d.DI_Code
</if>
<if test="devId != null and devId != '' ">
- and a1.MV_Stat_Code = #{devId} and b.DI_Code = #{devId}
+ and c.MV_Stat_Code = #{devId} and d.DI_Code = #{devId}
</if>
<if test="beginTime != null and endTime != null">
- and a1.MV_Data_Time between #{beginTime} and #{endTime}
+ AND c.MV_Data_Time between #{beginTime} AND #{endTime}
</if>
</where>
- order by MV_Data_Time asc
+ ORDER BY c.MV_Data_Time ASC;
</select>
<!-- 鏉′欢鏌ヨ寮傚父琛�-->
--
Gitblit v1.9.3