From b56c0758e2158261d973e90b581d1ed78c5dfc79 Mon Sep 17 00:00:00 2001
From: whyclxw <810412026@qq.com>
Date: 星期三, 11 九月 2024 15:38:54 +0800
Subject: [PATCH] 告警实时socket和历史分库分表历史查询
---
src/main/java/com/whyc/service/SubTablePageInfoService.java | 102 +++++++++++++++++++++++++-------------------------
1 files changed, 51 insertions(+), 51 deletions(-)
diff --git a/src/main/java/com/whyc/service/SubTablePageInfoService.java b/src/main/java/com/whyc/service/SubTablePageInfoService.java
index ec20038..081ed68 100644
--- a/src/main/java/com/whyc/service/SubTablePageInfoService.java
+++ b/src/main/java/com/whyc/service/SubTablePageInfoService.java
@@ -1,7 +1,11 @@
package com.whyc.service;
+import com.whyc.dto.DalmDto;
import com.whyc.mapper.CallBack;
+import com.whyc.pojo.db_alarm.DevalarmData;
+import com.whyc.pojo.db_alarm.DevalarmDataYear;
import com.whyc.pojo.db_batt_testdata.BatttestdataId;
+import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -57,23 +61,28 @@
}
- /*//鏌ヨDevAlm鍘嗗彶鍛婅鏁伴噺
- public int getCountForDevAlm(DevAlarmHisDto alm) {
- String sql="select count(distinct num) as number from db_ckpwrdev_alarm." +alm.getRecordYear()
+ //鏌ヨDevAlm鍘嗗彶鍛婅鏁伴噺
+ public int getCountForDevAlm(DalmDto dto) {
+ String sql="select count(distinct num) as number from db_alarm." +dto.getTableName()
+" where 1=1 ";
- if(alm.getAlmStartTime()!=null){
- sql+=" and alm_starttime >='"+ DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmStartTime())+"' ";
+ if(dto.getStartTime()!=null){
+ sql+=" and alm_starttime >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
}
- if(alm.getAlmEndTime()!=null){
- sql+=" and alm_endtime <='"+DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmEndTime())+"' ";
+ if(dto.getEndTime()!=null){
+ sql+=" and alm_endtime <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
}
- if(alm.getDevType()!=0){
- sql+=" and dev_type="+alm.getDevType();
+ if(dto.getDevType()!=0){
+ sql+=" and FLOOR(dev_id/100000000)="+dto.getDevType();
}
- if(alm.getAlmLevel()!=0){
- sql+=" and alm_level="+alm.getAlmLevel();
+ if(dto.getAlmId()!=0){
+ sql+=" and alm_id="+dto.getAlmId();
}
+ sql+=" and dev_id in (" +
+ " SELECT distinct dev_id from db_user.tb_battgroup_baojigroup,db_user.tb_battgroup_usr" +
+ " where tb_battgroup_baojigroup.baoji_group_id=tb_battgroup_usr.baoji_group_id" +
+ " and uid="+dto.getUid()+
+ ")";
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
public List getResults(ResultSet rs) throws SQLException {
@@ -94,60 +103,51 @@
return num;
}
//鏌ヨdevalm鍘嗗彶鍛婅
- public List getListDevAlm(DevAlarmHisDto alm){
- String sql="select * from db_ckpwrdev_alarm." +alm.getRecordYear()
+ public List getListDevAlm(DalmDto dto){
+ String sql="select * from db_alarm." +dto.getTableName()
+" where 1=1 ";
- if(alm.getAlmStartTime()!=null){
- sql+=" and alm_starttime >='"+ DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmStartTime())+"' ";
+
+ if(dto.getStartTime()!=null){
+ sql+=" and alm_starttime >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
}
- if(alm.getAlmEndTime()!=null){
- sql+=" and alm_endtime <='"+DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmEndTime())+"' ";
+ if(dto.getEndTime()!=null){
+ sql+=" and alm_endtime <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
}
- if(alm.getDevType()!=0){
- sql+=" and dev_type="+alm.getDevType();
+ if(dto.getDevType()!=0){
+ sql+=" and FLOOR(dev_id/100000000)="+dto.getDevType();
}
- if(alm.getAlmLevel()!=0){
- sql+=" and alm_level="+alm.getAlmLevel();
+ if(dto.getAlmId()!=0){
+ sql+=" and alm_id="+dto.getAlmId();
}
- sql+=" ORDER BY alm_starttime desc limit "+alm.getLimitStart()+","+alm.getLimitEnd()+" ";
+ sql+=" and dev_id in (" +
+ " SELECT distinct dev_id from db_user.tb_battgroup_baojigroup,db_user.tb_battgroup_usr" +
+ " where tb_battgroup_baojigroup.baoji_group_id=tb_battgroup_usr.baoji_group_id" +
+ " and uid="+dto.getUid()+
+ ")";
+ sql+=" ORDER BY alm_starttime asc limit "+dto.getLimitStart()+","+dto.getLimitEnd()+" ";
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
public List getResults(ResultSet rs) throws SQLException {
- List<CKPowerDevAlarmHistory> list=new ArrayList<>();
+ List<DevalarmDataYear> list=new ArrayList<>();
while (rs.next()){
- CKPowerDevAlarmHistory ph=new CKPowerDevAlarmHistory();
- ph.setNum(rs.getLong("num"));
- ph.setPowerDeviceId(rs.getInt("power_device_id"));
- ph.setAlmId(rs.getInt("alm_id"));
- ph.setAlmName(rs.getString("alm_name"));
- ph.setDevType(rs.getInt("dev_type"));
- ph.setAlmLevel(rs.getInt("alm_level"));
- ph.setAlmStartTime(rs.getTimestamp("alm_starttime"));
- ph.setAlmEndTime(rs.getTimestamp("alm_endtime"));
- ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
- ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
- ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
- list.add(ph);
+ DevalarmDataYear dataYear=new DevalarmDataYear();
+ dataYear.setNum(rs.getInt("num"));
+ dataYear.setDevId(rs.getInt("dev_id"));
+ dataYear.setAlmSignalId(rs.getInt("alm_signal_id"));
+ dataYear.setAlmStarttime(rs.getTimestamp("alm_starttime"));
+ dataYear.setAlmValue(rs.getFloat("alm_value"));
+ dataYear.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
+ dataYear.setConfirmedUid(rs.getInt("confirmed_uid"));
+ dataYear.setConfirmedTime(rs.getTimestamp("confirmed_time"));
+ dataYear.setAlmEndtime(rs.getTimestamp("alm_endtime"));
+ dataYear.setAlmClearedType(rs.getInt("alm_cleared_type"));
+ list.add(dataYear);
}
return list;
}
});
return list;
}
- //鏌ヨ鎵�鏈夌殑鍘嗗彶鏃堕棿琛�
- public List getDevAlmHisList() {
- String sql="SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'tb_ckpowerdev_alarm_history_%'";
- List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
- @Override
- public List getResults(ResultSet rs) throws SQLException {
- List<String> list=new ArrayList<>();
- while (rs.next()){
- list.add(rs.getString("table_name"));
- }
- return list;
- }
- });
- return list;
- }*/
+
}
--
Gitblit v1.9.1