修改了数据排名清单的sql语句

zmc

2023-10-12 48959b0b908dd57bf9dd991b75756ac2698f2b4d

修改了数据排名清单的sql语句

 src/main/java/com/job/zsc/QianduanApplication.java

@@ -1,13 +1,25 @@
package com.job.zsc;

import com.job.zsc.scheduledtasks.TaskController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class QianduanApplication {
    @Autowired
    private TaskController taskController;

    @Bean
    public ApplicationRunner runner() {
         return args -> taskController.run();
    }
    public static void main(String[] args) {
        SpringApplication.run(QianduanApplication.class, args);

    }

}

 src/main/java/com/job/zsc/dustexceptionanalysis/ExceptionAnalysis.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,36 @@
package com.job.zsc.dustexceptionanalysis;

import com.job.zsc.pojo.DustExceptionSetting;
import com.job.zsc.pojo.DustSiteData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import java.util.ArrayList;
import java.util.List;

@Controller
public class ExceptionAnalysis {


    public static List<DustSiteData> isDustValueExceeding(List<DustSiteData> siteDataList) {
        List<DustSiteData> result = new ArrayList<>();
        DustExceptionSetting dustExceptionSetting = new DustExceptionSetting();
        /*扬尘超标判断*/
//        for (DustSiteData item : siteDataList) {
//            if (item.getDustValue() >= dustExceptionSetting.getExceedingStandard()  ) {
//                result.add(item);
//            }
//        }
        return result;
    }

    public static int main(List<DustSiteData> siteDataList) {
        List<DustSiteData> r1 = isDustValueExceeding(siteDataList);

//        if (!r1.isEmpty()) {
//            Repository repository = new Repository();
//            repository.valueExceedingWriteExceptionTable(r1);
//        }
        return r1.size();
    }
}

 src/main/java/com/job/zsc/dustexceptionanalysis/analysis/Exceeding.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,38 @@
package com.job.zsc.dustexceptionanalysis.analysis;

import com.job.zsc.pojo.DustExceptionSetting;
import com.job.zsc.pojo.DustSiteData;

import java.util.ArrayList;
import java.util.List;

public class Exceeding {
    public static List<DustSiteData> isDustValueExceeding(List<DustSiteData> siteDataList) {
        List<DustSiteData> result = new ArrayList<>();

        DustExceptionSetting dustExceptionSetting = new DustExceptionSetting();


        /*扬尘超标判断*/
//        for (DustSiteData item : siteDataList) {
//            if (item.getDustValue() >= dustExceptionSetting.getExceedingStandard()  ) {
//                result.add(item);
//            }
//        }
        return result;
    }


    public static int main(List<DustSiteData> siteDataList) {
        List<DustSiteData> exceedingData = isDustValueExceeding(siteDataList);

//        if (!r1.isEmpty()) {
//            Repository repository = new Repository();
//            repository.valueExceedingWriteExceptionTable(r1);
//        }

        return exceedingData.size();
    }


}

 src/main/java/com/job/zsc/dustexceptionanalysis/database/DustRepository.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,21 @@
package com.job.zsc.dustexceptionanalysis.database;

import com.job.zsc.pojo.DustExceptionSetting;
import com.job.zsc.pojo.DustSiteData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DustRepository {



    /*读取指定时间段的扬尘数据*/
    @Select("select * from ja_t_dust_site_data_info where lst between #{beginTime} and #{endTime}")
    public List<DustSiteData> getDustData(String beginTime, String endTime);

    @Select("select * from dust_exception_setting order by update_time desc limit 1")
    public List<DustExceptionSetting> readExceptionSetting();
}

 src/main/java/com/job/zsc/dustexceptionanalysis/dataprocess/DataProcess.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,14 @@
package com.job.zsc.dustexceptionanalysis.dataprocess;

import com.job.zsc.pojo.DustExceptionSetting;

import java.util.List;

public class DataProcess {
    public void giveValues(List<DustExceptionSetting> data){
        DustExceptionSetting dustExceptionSetting = new DustExceptionSetting();



    }
}

 src/main/java/com/job/zsc/mapper/dustexception/OnlineAnalysisMapper.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,15 @@
package com.job.zsc.mapper.dustexception;


import com.job.zsc.pojo.DustSiteData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface OnlineAnalysisMapper {
    /*读取指定时间段的数据*/
    @Select("select * from ja_t_dust_site_data_info where lst between #{beginTime} and #{endTime}")
    public List<DustSiteData> getDustData(String beginTime,String endTime);
}

 src/main/java/com/job/zsc/mapper/dustexception/onlineAnalysisMapper.java

ÎļþÒÑɾ³ý

 src/main/java/com/job/zsc/pojo/DustExceptionSetting.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,49 @@
package com.job.zsc.pojo;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

@Data
@AllArgsConstructor
//@NoArgsConstructor
public class DustExceptionSetting {
    private static int id;
    private static String user;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private static LocalDateTime updateTime;  //开始时间

    private static String version;

    private static int missDataMinutes;
    private static float dataLow;
    private static int longTimeNoChange;
    private static int mutationNum;
    private static float mutationRate;
    private static float nearExceedLowValue;
    private static float nearExceedHighValue;
    private static int nearExceedNum;
    private static int dayExceedBorderlineLowNum;
    private static int dayExceedBorderlineHighNum;
    private static int changeTrendGroup;
    private static int changeTrendInterval;
    private static int changeTrendRate;
    private static int exceedingStandard;

    private static DustExceptionSetting instance;


    public static DustExceptionSetting getInstance() {

        if (instance == null) {
            instance = new DustExceptionSetting();
        }
        return instance;
    }


}

 src/main/java/com/job/zsc/pojo/DustSiteData.java

@@ -1,8 +1,11 @@
package com.job.zsc.pojo;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

// ç›‘测点
@Data
@@ -17,9 +20,13 @@

    private String mnCode;
    private String typeName;
    private String dustValue;
    private String noiseValue;
    private String lst;
    private double dustValue;
    private double noiseValue;

//    private String lst;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime lst;

    private String quality;
    private String groupName;
}

 src/main/java/com/job/zsc/scheduledtasks/BaseTimingTask.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,27 @@
package cn.flightfeather.supervision.timingtask

import java.time.LocalDateTime
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors

/**
 * å®šæ—¶ä»»åŠ¡åŸºç±»
 */
abstract class BaseTimingTask {

    // è®°å½•ä¸Šæ¬¡ä»»åŠ¡æ‰§è¡Œçš„时间点，单位：毫秒
    private var lastTime: LocalDateTime = LocalDateTime.MIN

    // ä»»åŠ¡æ‰§è¡Œå‘¨æœŸï¼Œå•ä½ï¼šåˆ†é’Ÿ
    abstract val period: Long

    open fun execute(localtime:LocalDateTime) {
        val now = LocalDateTime.now()
        if (now.minusSeconds(period * 60 - 5) >= lastTime) {
            lastTime = now
            doTask(localtime)
        }
    }

    abstract fun doTask(localtime:LocalDateTime)
}

 src/main/java/com/job/zsc/scheduledtasks/ScheduledTaskController.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,13 @@
package com.job.zsc.scheduledtasks;

import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;

public class ScheduledTaskController {

    /*线程池*/
    private ScheduledExecutorService scheduledExecutorService = Executors.newSingleThreadScheduledExecutor();
    public void run(){
    
    }
}

 src/main/java/com/job/zsc/scheduledtasks/TaskController.kt

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,105 @@
package com.job.zsc.scheduledtasks

import cn.flightfeather.supervision.timingtask.BaseTimingTask
import org.slf4j.LoggerFactory
import org.springframework.stereotype.Component
import java.time.LocalDateTime
import java.time.LocalTime
import java.util.*
import java.util.concurrent.Executors
import java.util.concurrent.ScheduledExecutorService
import java.util.concurrent.TimeUnit

/**
 * æ•°æ®èŽ·å–控制器
 */
@Component
class TaskController(
        /*实例名 ç±»å*/
//    fetchVOC: TaskFetchVOC,
//    pushFume: TaskPushFume,
//    ledgerCopy: TaskLedgerCopy,
//    ledgerRemind: TaskLedgerRemind,
//    taskJinAnLampEnterBaseInfo: TaskJinAnLampEnterBaseInfo,
//    taskJinAnLampDeviceData: TaskJinAnLampDeviceData,
//    taskJinAnConstructionSiteInfo: TaskJinAnConstructionSiteInfo,
//    taskJinAnHourlyDustData: TaskJinAnHourlyDustData
) {

    companion object {
        private const val FETCH_PERIOD_MIN = 1L * 60
        private const val MAINTAIN_PERIOD_MIN = 5L * 60
        val LOGGER = LoggerFactory.getLogger(TaskController::class.java)
    }

    private val timeTask = mutableListOf<BaseTimingTask>()
    private var schedule = Executors.newScheduledThreadPool(1)
    private var maintainSchedule = Executors.newScheduledThreadPool(1)
    private var taskTime: Date = Date()
    private var maintainTime: Date = Date()

    init {
        LOGGER.info("添加定时任务")
        timeTask.clear()

       /*做的定时任务加进来*/


        LOGGER.info("添加定时任务完成，任务总计${timeTask.size}个")
    }

    fun run() {
        fetchTask(true)
        maintainTask(true)
    }

    private fun fetchTask(isFirst: Boolean = false) {
        val cal = Calendar.getInstance()
        val time = LocalTime.now()
        val sec = time.second
        val delay = 60L - sec
//        var localtime = LocalDateTime.of(2021, 4, 16, 9, 0)
        if (!isFirst) {
            schedule = closeThread(schedule)
        }
        schedule.scheduleAtFixedRate({
            taskTime = Date()
            if (Date().time - maintainTime.time > (MAINTAIN_PERIOD_MIN + 1) * 60 * 1000) {
                maintainTask()
            }

            val localtime = LocalDateTime.now()
//            LOGGER.info("定时任务执行，定时任务数：${timeTask.size}")
            timeTask.forEach {
//                LOGGER.info("定时任务：${it.javaClass.name}")
                it.execute(localtime)
            }
//            localtime = localtime.plusMinutes(1)
        }, delay, FETCH_PERIOD_MIN, TimeUnit.SECONDS)
    }

    private fun maintainTask(isFirst: Boolean = false) {
        if (!isFirst) {
            maintainSchedule = closeThread(maintainSchedule)
        }
        maintainSchedule.scheduleAtFixedRate({
            maintainTime = Date()
            if (Date().time - taskTime.time > (FETCH_PERIOD_MIN + 1) * 60 * 1000) {
                fetchTask()
            }
        }, 0, MAINTAIN_PERIOD_MIN, TimeUnit.SECONDS)
    }

    private fun closeThread(s: ScheduledExecutorService): ScheduledExecutorService {
        try {
            s.shutdown()
            if (s.awaitTermination(10, TimeUnit.SECONDS)) {
                s.shutdownNow()
            }
        } catch (e: InterruptedException) {
            e.printStackTrace()
            s.shutdownNow()
        }
        return Executors.newScheduledThreadPool(1)
    }
}

 src/main/java/com/job/zsc/service/dustexception/impl/onlineAnalysisServiceImpl.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,7 @@
package com.job.zsc.service.dustexception.impl;

import com.job.zsc.service.dustexception.onlineAnalysisService;

public class onlineAnalysisServiceImpl implements onlineAnalysisService {
        /*逻辑处理*/
}

 src/main/resources/application.yml

@@ -1,13 +1,13 @@
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/fume
#     username: fume
#     password: fume_feiyu2023

#     url: jdbc:mysql://localhost:3306/qianduan_sql
#     username: root
#     password: 1234
     url: jdbc:mysql://localhost:3306/qianduan_sql
     username: root
     password: 1234

mybatis:
  configuration:

 src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml

@@ -73,29 +73,33 @@
    <select id="analysisByTime" resultType="com.job.zsc.pojo.AnalysisDustData">
        select  c.name,d.*
        from ja_t_dust_site_info as c  join
        (select
        a.mn_code as mn_code,
        DATE(a.lst) as lst,
        ROUND(AVG(a.dust_value),3)  as day_avg,
        min(a.dust_value) as min,
        max(a.dust_value) as max,
        (
        select
        mn_code as mn_code,
        DATE(lst) as lst,
        ROUND(AVG(dust_value),3)  as day_avg,
        min(dust_value) as min,
        max(dust_value) as max,
        CONCAT(ROUND(COUNT(*)/96*100, 2), '%')  as day_online,
        CONCAT(ROUND(SUM(CASE WHEN a.dust_value >0 THEN 1 ELSE 0 END)/96 *100, 2), '%') as day_valid,
        CONCAT(ROUND(SUM(CASE WHEN a.dust_value >= 1  THEN 1 ELSE 0 END)/96*100,2),'%') as  day_exceeding
        from ja_t_dust_site_data_info as a join ja_t_dust_site_info as b on a.mn_code = b.mn_code
        CONCAT(ROUND(SUM(CASE WHEN dust_value >0 THEN 1 ELSE 0 END)/96 *100, 2), '%') as day_valid,
        CONCAT(ROUND(SUM(CASE WHEN dust_value >= 1  THEN 1 ELSE 0 END)/96*100,2),'%') as  day_exceeding
        from ja_t_dust_site_data_info
        <where>
            <if test="siteName !=null and siteName != ''">
                b.name = #{siteName}
            </if>
<!--            <if test="siteName !=null and siteName != ''">-->
<!--                b.name = #{siteName}-->
<!--            </if>-->

            <if test="beginTime != null and endTime != null">
                and  a.lst between #{beginTime} and #{endTime}
                lst between #{beginTime} and #{endTime}
            </if>

        </where>
        GROUP BY a.mn_code,DATE(a.lst)
        GROUP BY mn_code,DATE(lst)
        order by lst asc ) as d  on c.mn_code = d.mn_code
    </select>



    <!--条件查询异常数据  åŠ ä¸Šäº†å®¡è®¡ä¿¡æ¯-->
    <select id="getExceptionData" resultType="com.job.zsc.pojo.DustExceptionData">
        select *

 src/test/java/com/job/zsc/QianduanApplicationTests.java

@@ -1,12 +1,19 @@
package com.job.zsc;

import com.job.zsc.dustexceptionanalysis.analysis.Exceeding;
import com.job.zsc.dustexceptionanalysis.database.DustRepository;
import com.job.zsc.mapper.dustexception.OnlineAnalysisMapper;
import com.job.zsc.pojo.DustExceptionSetting;
import com.job.zsc.pojo.DustSiteData;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.SQLOutput;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.*;

@SpringBootTest
class QianduanApplicationTests {

@@ -28,5 +35,31 @@
            //return df2.format(date1);

    }
    @Autowired
    private DustRepository dustRepository;
    @Test
    void readSiteData(){
//        DustExceptionSetting dustExceptionSetting = DustExceptionSetting.getInstance();
//        è¯»å–异常分析判定的配置
        List<DustExceptionSetting> list= dustRepository.readExceptionSetting();
        System.out.println("11111");

        System.out.println(list.size());
        System.out.println(list.get(0));


//        List<DustSiteData> dustData = dustRepository.getDustData("2023-10-01 00:00:00","2023-10-01 01:00:00");
////      System.out.println(dustData.get(0).getMnCode());
//        int a  = Exceeding.main(dustData);
//        System.out.println(a);


    }


//    @Test
//    void exceeding(){
//
//    }

}