From e196f42e9a977c0b2f8452dce875b110fb76e92f Mon Sep 17 00:00:00 2001
From: whyclxw <810412026@qq.com>
Date: 星期六, 28 六月 2025 16:40:41 +0800
Subject: [PATCH] 实时数据统计曲线-核容设备初稿
---
src/main/java/com/whyc/service/SubTablePageInfoService.java | 764 ++++++++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 712 insertions(+), 52 deletions(-)
diff --git a/src/main/java/com/whyc/service/SubTablePageInfoService.java b/src/main/java/com/whyc/service/SubTablePageInfoService.java
index 1eff6d6..21adb88 100644
--- a/src/main/java/com/whyc/service/SubTablePageInfoService.java
+++ b/src/main/java/com/whyc/service/SubTablePageInfoService.java
@@ -3,7 +3,15 @@
import com.whyc.dto.AlmHis.BattAlmPar;
import com.whyc.dto.AlmHis.DevAlmPar;
import com.whyc.dto.AlmHis.PwrAlmPar;
+import com.whyc.dto.AnalysisAlm.ResAnalysis;
import com.whyc.dto.Real.*;
+import com.whyc.dto.RealDataStatic.MonDataDto;
+import com.whyc.dto.RealDataStatic.ResRealDataAc;
+import com.whyc.dto.RealDataStatic.ResRealDataHr;
+import com.whyc.dto.RealDataStatic.ResRealDataMon;
+import com.whyc.dto.Statistic.ComPareChangeCurve;
+import com.whyc.dto.Statistic.ComPareChart;
+import com.whyc.dto.Statistic.QuarterPwr7Res;
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_alarm.BattalarmDataHistory;
import com.whyc.pojo.db_alarm.DevalarmDataHistory;
@@ -11,13 +19,16 @@
import com.whyc.pojo.db_data_history.BattRealdataId;
import com.whyc.pojo.db_data_history.PwrdevHistorydataId;
import com.whyc.pojo.db_pwrdev_alarm.PwrdevAlarmHistory;
+import com.whyc.util.ActionUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
+import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
+import java.util.stream.Collectors;
@Service
public class SubTablePageInfoService {
@@ -513,7 +524,7 @@
}
//鑾峰彇鍐呴樆鏁版嵁
public List<QuarterDto> getBattResInfData(Integer battgroupId,Integer testRecordCount) {
- String sql="select distinct mon_num,test_starttime,mon_res, from db_batt_testdata.tb_battresdata_"+battgroupId
+ String sql="select distinct mon_num,test_starttime,mon_res from db_batt_testdata.tb_battresdata_"+battgroupId
+" where test_record_count="+testRecordCount+" order by mon_num asc";
List<QuarterDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
@@ -530,6 +541,50 @@
}
});
return list;
+ }
+ //鍙栧唴闃绘祴璇曠殑鏈�澶у唴闃诲拰鍗曚綋缂栧彿
+ public List<QuarterDto> getMaxResInInfData(Integer battgroupId, Integer testRecordCount) {
+ String sql="select distinct mon_num,test_starttime,mon_res from db_batt_testdata.tb_battresdata_"+battgroupId
+ +" where test_record_count="+testRecordCount+" order by mon_num asc";
+ List<QuarterDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<QuarterDto> list=new ArrayList<>();
+ while (rs.next()){
+ QuarterDto data=new QuarterDto();
+ data.setMonNum(rs.getInt("mon_num"));
+ data.setNumValue(rs.getFloat("mon_res"));
+ data.setRecordTime(rs.getTimestamp("test_starttime"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鍙栧唴闃绘祴璇曟寚瀹氬崟浣撶殑鍗曚綋鐢靛帇
+ public ComPareChangeCurve getMonResChangeByBattgroupId(Integer battgroupId, Integer testRecordCount, Integer monNum) {
+ String sql="select distinct battgroup_id,mon_num,test_starttime,mon_res from db_batt_testdata.tb_battresdata_"+battgroupId
+ +" where test_record_count="+testRecordCount+" and monNum="+monNum;
+ List<ComPareChangeCurve> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ComPareChangeCurve> list=new ArrayList<>();
+ while (rs.next()){
+ ComPareChangeCurve data=new ComPareChangeCurve();
+ data.setMonNum(rs.getInt("mon_num"));
+ data.setCurveValue(rs.getFloat("mon_res"));
+ data.setStartTime(rs.getTimestamp("test_starttime"));
+ data.setBattgroupId(rs.getInt("battgroup_id"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ if(list!=null&&list.size()>0){
+ return list.get(0);
+ }
+ return null;
}
//鎵捐繖娆℃斁鐢电殑鎸囧畾涓�绗旀暟鎹�
public List<BatttestdataId> getLastDataByBattgroupId(Integer battgroupId, Integer testRecordCount, Integer recordNum) {
@@ -559,43 +614,14 @@
});
return list;
}
- //绯荤粺姒傝鑾峰彇鍗婂皬鏃舵牳瀹硅澶囦俊鎭�(缁勭淇℃伅鍙栦竴涓崟浣撲俊鎭嵆鍙�)
- public List<BattHisRealDto> getHalfHourBattDevData(String tableName,Integer granularity) {
- String sql="select distinct mon_num,record_time,group_vol,online_vol,group_curr,group_tmp " +
- "from (select a.*, (@i:= @i+1) as number " +
- " from (select * from "+tableName+" "+
- " where record_time>date_sub(now(),interval 0.5 hour) and mon_num=1) a, " +
- " (select @i:=0) b) c "+
- " where c.number%"+granularity+"=0 or c.number=1 ";
- sql+=" order by record_time asc";
- List<BattHisRealDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
- @Override
- public List getResults(ResultSet rs) throws SQLException {
- List<BattHisRealDto> list=new ArrayList<>();
- while (rs.next()){
- BattHisRealDto data=new BattHisRealDto();
- data.setRecordTime(rs.getTimestamp("record_time"));
- data.setGroupVol(rs.getFloat("group_vol"));
- data.setOnlineVol(rs.getFloat("online_vol"));
- data.setGroupCurr(rs.getFloat("group_curr"));
- data.setGroupTmp(rs.getFloat("group_tmp"));
- list.add(data);
- }
- return list;
- }
- });
- return list;
- }
//绯荤粺姒傝鑾峰彇鍗婂皬鏃朵氦娴佽緭鍏ョ粺璁�
- public List<PwrHisRealAcInDto> getHalfHourPwrHisAcinData(String tableName,Integer granularity) {
- String sql="select distinct record_datetime,acin1_vola,acin1_volb,acin1_volc,acin2_vola,acin2_volb,acin2_volc " +
- ",acin1_curra,acin1_currb,acin1_currc,acin2_curra,acin2_currb,acin2_currc " +
- "from (select a.*, (@i:= @i+1) as number " +
- " from (select * from "+tableName+" "+
- " where record_datetime>date_sub(now(),interval 0.5 hour)) a, " +
- " (select @i:=0) b) c "+
- " where c.number%"+granularity+"=0 or c.number=1 ";
- sql+=" order by record_datetime asc";
+ public List<PwrHisRealAcInDto> getHalfHourPwrHisAcinData(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) {
+ String sql=" select distinct record_datetime,acin1_vola,acin1_volb,acin1_volc,acin2_vola,acin2_volb,acin2_volc " +
+ " ,acin1_curra,acin1_currb,acin1_currc,acin2_curra,acin2_currb,acin2_currc " +
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+
+ "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum;
+ sql+=" order by record_datetime asc";
List<PwrHisRealAcInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
public List getResults(ResultSet rs) throws SQLException {
@@ -622,14 +648,175 @@
});
return list;
}
-
- public List<PwrHisRealDcoutInDto> getHalfHourPwrHisDcoutData(String tableName,Integer granularity) {
+ //绯荤粺姒傝鑾峰彇鍗婂皬鏃朵氦娴佽緭鍑虹粺璁�
+ public List<PwrHisRealDcoutInDto> getHalfHourPwrHisDcoutData(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) {
String sql="select distinct * " +
- "from (select a.*, (@i:= @i+1) as number " +
- " from (select * from "+tableName+" "+
- " where record_datetime>date_sub(now(),interval 0.5 hour)) a, " +
- " (select @i:=0) b) c "+
- " where c.number%"+granularity+"=0 or c.number=1 ";
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+
+ "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum;
+ sql+=" order by record_datetime asc";
+ List<PwrHisRealDcoutInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<PwrHisRealDcoutInDto> list=new ArrayList<>();
+ while (rs.next()){
+ PwrHisRealDcoutInDto data=new PwrHisRealDcoutInDto();
+ data.setRecordDatetime(rs.getTimestamp("record_datetime"));
+ data.setMOutputvol(rs.getFloat("m_outputvol"));
+ data.setM1Outcurr(rs.getFloat("m1_outcurr"));
+ data.setM2Outcurr(rs.getFloat("m2_outcurr"));
+ data.setM3Outcurr(rs.getFloat("m3_outcurr"));
+ data.setM4Outcurr(rs.getFloat("m4_outcurr"));
+ data.setM5Outcurr(rs.getFloat("m5_outcurr"));
+ data.setM6Outcurr(rs.getFloat("m6_outcurr"));
+ data.setM7Outcurr(rs.getFloat("m7_outcurr"));
+ data.setM8Outcurr(rs.getFloat("m8_outcurr"));
+ data.setM9Outcurr(rs.getFloat("m9_outcurr"));
+ data.setM10Outcurr(rs.getFloat("m10_outcurr"));
+ data.setM11Outcurr(rs.getFloat("m11_outcurr"));
+ data.setM12Outcurr(rs.getFloat("m12_outcurr"));
+ data.setM13Outcurr(rs.getFloat("m13_outcurr"));
+ data.setM14Outcurr(rs.getFloat("m14_outcurr"));
+ data.setM15Outcurr(rs.getFloat("m15_outcurr"));
+ data.setM16Outcurr(rs.getFloat("m16_outcurr"));
+ data.setM1OutVol(rs.getFloat("m1_out_vol"));
+ data.setM2OutVol(rs.getFloat("m2_out_vol"));
+ data.setM3OutVol(rs.getFloat("m3_out_vol"));
+ data.setM4OutVol(rs.getFloat("m4_out_vol"));
+ data.setM5OutVol(rs.getFloat("m5_out_vol"));
+ data.setM6OutVol(rs.getFloat("m6_out_vol"));
+ data.setM7OutVol(rs.getFloat("m7_out_vol"));
+ data.setM8OutVol(rs.getFloat("m8_out_vol"));
+ data.setM9OutVol(rs.getFloat("m9_out_vol"));
+ data.setM10OutVol(rs.getFloat("m10_out_vol"));
+ data.setM11OutVol(rs.getFloat("m11_out_vol"));
+ data.setM12OutVol(rs.getFloat("m12_out_vol"));
+ data.setM13OutVol(rs.getFloat("m13_out_vol"));
+ data.setM14OutVol(rs.getFloat("m14_out_vol"));
+ data.setM15OutVol(rs.getFloat("m15_out_vol"));
+ data.setM16OutVol(rs.getFloat("m16_out_vol"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鏌ヨ鐢垫簮瀛楁鐨勫紓鍔ㄧ殑璁板綍
+ public List<Object> getPwrChangesRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, String value, float upperLimit, float lowerLimit) {
+ String sql=" select distinct record_datetime,record_num "+
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+"'" +
+ " and "+value+">"+upperLimit+" or "+value+"<"+lowerLimit+" ";
+ sql+=" order by record_num asc";
+ List<Object> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List list=new ArrayList<>();
+ List numList=new ArrayList<>();
+ List timeList=new ArrayList<>();
+ while (rs.next()){
+ numList.add(rs.getInt("record_num"));
+ timeList.add(rs.getString("record_datetime"));
+ }
+ list.add(numList);
+ list.add(timeList);
+ return list;
+ }
+ });
+ return list;
+ }
+ //鏌ヨ鐢垫簮瀛楁鐨勫紓鍔�
+ public List<ResRealDataAc> getPwrPropertyChangesByRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, List<Integer> allChangeNums, Map<String, List<String>> propertyInfo) {
+ String propertyStr = propertyInfo.values().stream()
+ .flatMap(List::stream)
+ .collect(Collectors.joining(","));
+ String sql=" select distinct record_datetime,record_num, "+propertyStr+
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+"'";
+ if (allChangeNums.size() > 0) {
+ //鍘婚櫎閲嶅椤�
+ List<Integer> uniqueList = allChangeNums.stream()
+ .distinct()
+ .collect(Collectors.toList());
+ //鎷兼帴鐢熸垚sql
+ String changeNumsStr = uniqueList.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ sql += " and record_num in (" + changeNumsStr + ")";
+ }
+ sql += " order by record_datetime asc";
+ List<ResRealDataAc> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataAc> list=new ArrayList<>();
+ while (rs.next()){
+ ResRealDataAc data=new ResRealDataAc();
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setRecordDatetime(rs.getTimestamp("record_datetime"));
+ Map<String,Map<String,Float>>dataMap=new HashMap<>();
+ for (Map.Entry<String, List<String>> entry : propertyInfo.entrySet()) {
+ String key = entry.getKey();
+ List<String> valueList = entry.getValue();
+ Map<String,Float> propertyMap=new HashMap<>();
+ for (String value : valueList) {
+ Float propertyValue = rs.getFloat(value);
+ propertyMap.put(value,propertyValue);
+ }
+ dataMap.put(key,propertyMap);
+ }
+ data.setPropertyInfo(dataMap);
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //瀹炴椂鏁版嵁鏁版嵁鏇茬嚎缁熻-浜ゆ祦鍗曞厓
+ public List<ResRealDataAc> getSticRealAcData(String tableName, Integer granularity, Date recordDatetime, Date recordDatetime1
+ , Integer maxRecordNum, Integer minRecordNum, Map<String, List<String>> propertyInfo) {
+ String propertyStr = propertyInfo.values().stream()
+ .flatMap(List::stream)
+ .collect(Collectors.joining(","));
+ String sql=" select distinct record_datetime,record_num,"+propertyStr+" "+
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+
+ "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum;
+ sql+=" order by record_datetime asc";
+ List<ResRealDataAc> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataAc> list=new ArrayList<>();
+ while (rs.next()){
+ ResRealDataAc data=new ResRealDataAc();
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setRecordDatetime(rs.getTimestamp("record_datetime"));
+ Map<String,Map<String,Float>>dataMap=new HashMap<>();
+ for (Map.Entry<String, List<String>> entry : propertyInfo.entrySet()) {
+ String key = entry.getKey();
+ List<String> valueList = entry.getValue();
+ Map<String,Float> propertyMap=new HashMap<>();
+ for (String value : valueList) {
+ Float propertyValue = rs.getFloat(value);
+ propertyMap.put(value,propertyValue);
+ }
+ dataMap.put(key,propertyMap);
+ }
+ data.setPropertyInfo(dataMap);
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鐢垫簮鍘嗗彶瀹炴椂
+ public List<PwrHisRealDcoutInDto> getPwrRealDataHis(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) {
+ String sql="select distinct * " +
+ " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+
+ "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum;
sql+=" order by record_datetime asc";
List<PwrHisRealDcoutInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
@@ -715,7 +902,7 @@
});
return list;
}
- //鍒ゆ柇鍘嗗彶瀹炴椂琛ㄦ槸鍚﹀瓨鍦�
+ //鍒ゆ柇鐢垫睜缁勫巻鍙插疄鏃惰〃鏄惁瀛樺湪
public int judgeTable_realdata( String table){
String sql="select count(*) as tableNum " +
" from INFORMATION_SCHEMA.TABLES " +
@@ -740,10 +927,34 @@
}
return tableNum;
}
-
+ //鍒ゆ柇鐢垫簮鍘嗗彶瀹炴椂琛ㄦ槸鍚﹀瓨鍦�
+ public int judgeTable_pwrhis( String table){
+ String sql="select count(*) as tableNum " +
+ " from INFORMATION_SCHEMA.TABLES " +
+ " where TABLE_SCHEMA = 'db_data_history' " +
+ " and TABLE_NAME = 'tb_pwrdev_historydata_"+table+"'";
+ List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ LinkedList<Object> temp = new LinkedList<>();
+ try {
+ while (rs.next())
+ temp.add(rs.getInt("tableNum"));
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return temp;
+ }
+ });
+ int tableNum =0;
+ if(list!=null){
+ tableNum= (int) list.get(0);
+ }
+ return tableNum;
+ }
//鐢垫睜鏁版嵁鍘嗗彶瀹炴椂澶勭悊
public List<RealDateDTO> getBattRealDataHis(BattRealdataId realdata, int granularity,Integer maxRecordNum,Integer minRecordNum) {
- String sql=" select record_time, group_vol, online_vol,group_curr, mon_vol, mon_tmp, mon_res, mon_num,record_num " +
+ String sql=" select distinct record_time, group_vol, online_vol,group_curr,load_curr, mon_vol, mon_tmp, mon_res, mon_num,record_num " +
" from db_data_history.tb_batt_realdata_"+realdata.getTableName()+" " +
" where record_time >= '"+ThreadLocalUtil.format(realdata.getRecordTime(),1)+"' " +
" and record_time <= '"+ThreadLocalUtil.format(realdata.getRecordTime1(),1)+"' "+
@@ -770,12 +981,371 @@
});
return list;
}
- //鑾峰彇鎸囧畾鏃堕棿娈靛唴鏈�澶ф渶灏弐ecordNum纭繚鏁版嵁鐨勫畬鏁�
- public List getMaxAndMinRecordNum(BattRealdataId realdata) {
+ //瀹炴椂鏁版嵁缁熻鏇茬嚎--鏍稿璁惧
+ public List<ResRealDataHr> getSticRealHrData(String tableName, Integer granularity, Date recordDatetime, Date recordDatetime1, Integer maxRecordNum, Integer minRecordNum) {
+ String sql=" select distinct record_time,record_num,online_vol,group_vol,group_curr,group_tmp,hjq_tmp,dev_conresist,dev_condvoldp "+
+ " from "+tableName+" " +
+ " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " +
+ " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' "+
+ " and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+maxRecordNum+" or record_num="+minRecordNum ;
+ sql+=" order by record_num asc";
+ List<ResRealDataHr> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataHr> list=new ArrayList<>();
+ while (rs.next()){
+ ResRealDataHr data=new ResRealDataHr();
+ data.setRecordTime(rs.getTimestamp("record_time"));
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setOnlineVol(rs.getFloat("online_vol"));
+ data.setGroupVol(rs.getFloat("group_vol"));
+ data.setGroupCurr(rs.getFloat("group_curr"));
+ data.setGroupTmp(rs.getFloat("group_tmp"));
+ data.setHjqTmp(rs.getFloat("hjq_tmp"));
+ data.setDevConresist(rs.getFloat("dev_conresist"));
+ data.setDevCondvoldp(rs.getFloat("dev_condvoldp"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鏌ヨ鏍稿璁惧瀛楁鐨勫紓鍔�
+ public List<ResRealDataHr> getHrPropertyChangesByRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, List<Integer> allChangeNums) {
+ String sql=" select distinct record_time,record_num,online_vol,group_vol,group_curr,group_tmp,hjq_tmp,dev_conresist,dev_condvoldp "+
+ " from "+tableName+" where record_time>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_time<='"+ActionUtil.sdf.format(recordDatetime1)+"'";
+ if (allChangeNums.size() > 0) {
+ //鍘婚櫎閲嶅椤�
+ List<Integer> uniqueList = allChangeNums.stream()
+ .distinct()
+ .collect(Collectors.toList());
+ //鎷兼帴鐢熸垚sql
+ String changeNumsStr = uniqueList.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ sql += " and record_num in (" + changeNumsStr + ")";
+ }
+ sql += " order by record_num asc ";
+ List<ResRealDataHr> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataHr> list=new ArrayList<>();
+ while (rs.next()){
+ ResRealDataHr data=new ResRealDataHr();
+ data.setRecordTime(rs.getTimestamp("record_time"));
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setOnlineVol(rs.getFloat("online_vol"));
+ data.setGroupVol(rs.getFloat("group_vol"));
+ data.setGroupCurr(rs.getFloat("group_curr"));
+ data.setGroupTmp(rs.getFloat("group_tmp"));
+ data.setHjqTmp(rs.getFloat("hjq_tmp"));
+ data.setDevConresist(rs.getFloat("dev_conresist"));
+ data.setDevCondvoldp(rs.getFloat("dev_condvoldp"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //瀹炴椂鏁版嵁缁熻鏇茬嚎--钃勭數姹犵粍
+ public List<ResRealDataMon> getSticRealMonData(String tableName, Integer granularity, Date recordDatetime, Date recordDatetime1, Integer maxRecordNum, Integer minRecordNum) {
+ String sql=" select distinct battgroup_id,record_time,record_num,mon_num,mon_vol,mon_tmp,mon_res,mon_jh_curr "+
+ " from "+tableName+" " +
+ " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " +
+ " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' "+
+ " and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+maxRecordNum+" or record_num="+minRecordNum ;
+ sql+=" order by record_num,mon_num asc ";
+ List<ResRealDataMon> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataMon> result = new ArrayList<>();
+ Map<String, ResRealDataMon> map = new HashMap<>();
+ while (rs.next()) {
+ Integer battgroupId = rs.getInt("battgroup_id");
+ Date recordTime = rs.getTimestamp("record_time");
+ Integer recordNum = rs.getInt("record_num");
+ // 鏋勫缓鍞竴閿紙battgroupId + recordNum锛�
+ String key = battgroupId + "_" + recordNum;
+ ResRealDataMon data = map.get(key);
+ if (data == null) {
+ data = new ResRealDataMon();
+ data.setRecordTime(recordTime);
+ data.setRecordNum(recordNum);
+ data.setMonList(new ArrayList<>());
+ map.put(key, data);
+ result.add(data);
+ }
+ // 灏嗗崟浣撴暟鎹皝瑁呭埌 monList 涓�
+ MonDataDto monData = new MonDataDto();
+ monData.setMonNum(rs.getInt("mon_num"));
+ monData.setMonVol(rs.getFloat("mon_vol"));
+ monData.setMonTmp(rs.getFloat("mon_tmp"));
+ monData.setMonRes(rs.getFloat("mon_res"));
+ monData.setMonJhCurr(rs.getFloat("mon_jh_curr"));
+ data.getMonList().add(monData);
+ }
+ return result;
+ }
+ });
+ return list;
+ }
+
+ //鏌ヨ鏍稿璁惧/鍗曚綋瀛楁鐨勫紓鍔ㄧ殑璁板綍
+ public List<Object> getBattChangesRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, String value, float upperLimit, float lowerLimit) {
+ String sql=" select distinct record_time,record_num "+
+ " from "+tableName+" where record_time>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_time<='"+ActionUtil.sdf.format(recordDatetime1)+"'" +
+ " and "+value+">"+upperLimit+" or "+value+"<"+lowerLimit+" ";
+ sql+=" order by record_num asc";
+ List<Object> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List list=new ArrayList<>();
+ List numList=new ArrayList<>();
+ List timeList=new ArrayList<>();
+ while (rs.next()){
+ numList.add(rs.getInt("record_num"));
+ timeList.add(rs.getString("record_time"));
+ }
+ list.add(numList);
+ list.add(timeList);
+ return list;
+ }
+ });
+ return list;
+ }
+ //鏌ヨ鐢垫睜缁勫崟浣撳瓧娈电殑寮傚姩
+ public List<ResRealDataMon> getMonPropertyChangesByRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, List<Integer> allChangeNums) {
+ String sql=" select distinct battgroup_id,record_time,record_num,mon_num,mon_vol,mon_tmp,mon_res,mon_jh_curr "+
+ " from "+tableName+" where record_time>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_time<='"+ActionUtil.sdf.format(recordDatetime1)+"'";
+ if (allChangeNums.size() > 0) {
+ //鍘婚櫎閲嶅椤�
+ List<Integer> uniqueList = allChangeNums.stream()
+ .distinct()
+ .collect(Collectors.toList());
+ //鎷兼帴鐢熸垚sql
+ String changeNumsStr = uniqueList.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ sql += " and record_num in (" + changeNumsStr + ")";
+ }
+ sql += " order by record_num,mon_num asc ";
+ List<ResRealDataMon> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataMon> result = new ArrayList<>();
+ Map<String, ResRealDataMon> map = new HashMap<>();
+ while (rs.next()) {
+ Integer battgroupId = rs.getInt("battgroup_id");
+ Date recordTime = rs.getTimestamp("record_time");
+ Integer recordNum = rs.getInt("record_num");
+ // 鏋勫缓鍞竴閿紙battgroupId + recordNum锛�
+ String key = battgroupId + "_" + recordNum;
+ ResRealDataMon data = map.get(key);
+ if (data == null) {
+ data = new ResRealDataMon();
+ data.setRecordTime(recordTime);
+ data.setRecordNum(recordNum);
+ data.setMonList(new ArrayList<>());
+ map.put(key, data);
+ result.add(data);
+ }
+ // 灏嗗崟浣撴暟鎹皝瑁呭埌 monList 涓�
+ MonDataDto monData = new MonDataDto();
+ monData.setMonNum(rs.getInt("mon_num"));
+ monData.setMonVol(rs.getFloat("mon_vol"));
+ monData.setMonTmp(rs.getFloat("mon_tmp"));
+ monData.setMonRes(rs.getFloat("mon_res"));
+ monData.setMonJhCurr(rs.getFloat("mon_jh_curr"));
+ data.getMonList().add(monData);
+ }
+ return result;
+ }
+ });
+ return list;
+ }
+ //鏌ヨ鐢垫睜缁勫瓧娈电殑寮傚姩
+ public List<ResRealDataAc> getBattPropertyChangesByRecordNum(String tableName, Date recordDatetime, Date recordDatetime1, List<Integer> allChangeNums, Map<String, List<String>> propertyInfo) {
+ String propertyStr = propertyInfo.values().stream()
+ .flatMap(List::stream)
+ .collect(Collectors.joining(","));
+ String sql=" select distinct record_time,record_num, "+propertyStr+
+ " from "+tableName+" where record_time>='"+ActionUtil.sdf.format(recordDatetime)+"'" +
+ " and record_time<='"+ActionUtil.sdf.format(recordDatetime1)+"'";
+ if (allChangeNums.size() > 0) {
+ //鍘婚櫎閲嶅椤�
+ List<Integer> uniqueList = allChangeNums.stream()
+ .distinct()
+ .collect(Collectors.toList());
+ //鎷兼帴鐢熸垚sql
+ String changeNumsStr = uniqueList.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ sql += " and record_num in (" + changeNumsStr + ")";
+ }
+ sql += " order by record_time asc";
+ List<ResRealDataAc> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResRealDataAc> list=new ArrayList<>();
+ while (rs.next()){
+ ResRealDataAc data=new ResRealDataAc();
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setRecordDatetime(rs.getTimestamp("record_time"));
+ Map<String,Map<String,Float>>dataMap=new HashMap<>();
+ for (Map.Entry<String, List<String>> entry : propertyInfo.entrySet()) {
+ String key = entry.getKey();
+ List<String> valueList = entry.getValue();
+ Map<String,Float> propertyMap=new HashMap<>();
+ for (String value : valueList) {
+ Float propertyValue = rs.getFloat(value);
+ propertyMap.put(value,propertyValue);
+ }
+ dataMap.put(key,propertyMap);
+ }
+ data.setPropertyInfo(dataMap);
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+
+
+ //绯荤粺姒傝鑾峰彇鍓峃绗旀牳瀹硅澶囦俊鎭�
+ public List<RealDateDTO> getBattDevData100(String tableName, Integer granularity, Date recordDatetime, Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) {
+ String sql=" select record_time, group_vol, online_vol,group_curr, load_curr,record_num " +
+ " from "+tableName+" " +
+ " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " +
+ " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' "+
+ " and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum ;
+ List<RealDateDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<RealDateDTO> list=new ArrayList<>();
+ while (rs.next()){
+ RealDateDTO ph=new RealDateDTO();
+ ph.setRecordTime(rs.getTimestamp("record_time"));
+ ph.setGroupVol(rs.getFloat("group_vol"));
+ ph.setOnlineVol(rs.getFloat("online_vol"));
+ ph.setGroupCurr(rs.getFloat("group_curr"));
+ ph.setLoadCurr(rs.getFloat("load_curr"));
+ ph.setRecordNum(rs.getInt("record_num"));
+ list.add(ph);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鐢垫睜鏁版嵁鍘嗗彶瀹炴椂澶勭悊(棰勮鍒嗘瀽)
+ public List<ResAnalysis> getBattHisRealInAlm(BattRealdataId realdata, Map<String,Object> battMap) {
+ String result = String.join(",", battMap.keySet());
+ String sql=" select record_time,"+result+" " +
+ " from db_data_history.tb_batt_realdata_"+realdata.getTableName()+" " +
+ " where record_time >= '"+ThreadLocalUtil.format(realdata.getRecordTime(),1)+"' " +
+ " and record_time <= '"+ThreadLocalUtil.format(realdata.getRecordTime1(),1)+"' " +
+ " order by record_time asc,mon_num asc";
+ List<ResAnalysis> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResAnalysis> list=new ArrayList<>();
+ while (rs.next()){
+ ResAnalysis ph=new ResAnalysis();
+ ph.setRecordTime(rs.getTimestamp("record_time"));
+ int i=1;
+ for (String key : battMap.keySet()) {
+ Integer dataType = (Integer) battMap.get(key);
+ try {
+ String propertyType = "dataType"+(i);
+ Field fieldType = ResAnalysis.class.getDeclaredField(propertyType);
+ fieldType.setAccessible(true);
+ fieldType.set(ph,dataType);
+
+ Float value = rs.getFloat(key);
+ String propertyName = "dataName"+(i);
+ Field fieldName = ResAnalysis.class.getDeclaredField(propertyName);
+ fieldName.setAccessible(true); // 濡傛灉瀛楁鏄鏈夌殑锛岄渶瑕佽缃负鍙闂�
+ fieldName.set(ph,key);
+
+ String propertyValue = "dataValue"+(i);
+ Field fieldValue = ResAnalysis.class.getDeclaredField(propertyValue);
+ fieldValue.setAccessible(true);
+ fieldValue.set(ph,value);
+ } catch (NoSuchFieldException e) {
+ e.printStackTrace();
+ } catch (IllegalAccessException e) {
+ e.printStackTrace();
+ }
+ i++;
+ }
+ list.add(ph);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+
+ //鐢垫簮鏁版嵁鍘嗗彶瀹炴椂澶勭悊(棰勮鍒嗘瀽)
+ public List<ResAnalysis> getPwrHisRealInAlm(PwrdevHistorydataId pwrHis, Map<String,Object> pwrMap) {
+ String result = String.join(",", pwrMap.keySet());
+ String sql=" select record_datetime,"+result+" " +
+ " from db_data_history.tb_pwrdev_historydata_"+pwrHis.getTableName()+" " +
+ " where record_datetime >= '"+ThreadLocalUtil.format(pwrHis.getRecordDatetime(),1)+"' " +
+ " and record_datetime <= '"+ThreadLocalUtil.format(pwrHis.getRecordDatetime1(),1)+"' " +
+ " order by record_datetime asc ";
+ List<ResAnalysis> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<ResAnalysis> list=new ArrayList<>();
+ while (rs.next()){
+ ResAnalysis ph=new ResAnalysis();
+ ph.setRecordTime(rs.getTimestamp("record_datetime"));
+ int i=1;
+ for (String key : pwrMap.keySet()) {
+ Integer dataType = (Integer) pwrMap.get(key);
+ try {
+ String propertyType = "dataType"+(i);
+ Field fieldType = ResAnalysis.class.getDeclaredField(propertyType);
+ fieldType.setAccessible(true);
+ fieldType.set(ph,dataType);
+
+ Float value = rs.getFloat(key);
+ String propertyName = "dataName"+(i);
+ Field fieldName = ResAnalysis.class.getDeclaredField(propertyName);
+ fieldName.setAccessible(true); // 濡傛灉瀛楁鏄鏈夌殑锛岄渶瑕佽缃负鍙闂�
+ fieldName.set(ph,key);
+
+ String propertyValue = "dataValue"+(i);
+ Field fieldValue = ResAnalysis.class.getDeclaredField(propertyValue);
+ fieldValue.setAccessible(true);
+ fieldValue.set(ph,value);
+ } catch (NoSuchFieldException e) {
+ e.printStackTrace();
+ } catch (IllegalAccessException e) {
+ e.printStackTrace();
+ }
+ i++;
+ }
+ list.add(ph);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鑾峰彇鐢垫睜缁勬寚瀹氭椂闂存鍐呮渶澶ф渶灏弐ecordNum纭繚鏁版嵁鐨勫畬鏁�
+ public List getBattMaxAndMinRecordNum(String tableName, Date recordDatetime, Date recordDatetime1) {
String sql="select max(record_num) as maxRecordNum,min(record_num) as minRecordNum " +
- " from db_data_history.tb_batt_realdata_" + realdata.getTableName()+" " +
- " where record_time >= '"+ThreadLocalUtil.format(realdata.getRecordTime(),1)+"' " +
- " and record_time <= '"+ThreadLocalUtil.format(realdata.getRecordTime1(),1)+"' " +
+ " from " + tableName+" " +
+ " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " +
+ " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' " +
" limit 1";
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@Override
@@ -790,4 +1360,94 @@
});
return list;
}
+ //鑾峰彇鐢垫簮鎸囧畾鏃堕棿娈靛唴鏈�澶ф渶灏弐ecordNum纭繚鏁版嵁鐨勫畬鏁�
+ public List getPwrMaxAndMinRecordNum(String tableName, Date recordDatetime, Date recordDatetime1) {
+ String sql="select max(record_num) as maxRecordNum,min(record_num) as minRecordNum " +
+ " from " + tableName+" " +
+ " where record_datetime >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " +
+ " and record_datetime <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' " +
+ " limit 1";
+ List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List list = new ArrayList<>();
+ while (rs.next()) {
+ list.add(rs.getInt("maxRecordNum"));
+ list.add(rs.getInt("minRecordNum"));
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //1.2.7浼樿壇鐢垫簮缁熻涓婁竴瀛ｅ害鐨勭數婧愭煇涓�灞炴�у拰鍙傛暟闃堝�艰秴杩囩殑娆℃暟
+ public List<PwrdevHistorydataId> getPwrQuarter7(String tableName,List<String> propertyNameList) {
+ String result = propertyNameList.stream().collect(Collectors.joining(","));
+ String sql=" select distinct record_datetime,"+result+" " +
+ " from "+tableName+" " ;
+ sql+=" order by record_datetime asc";
+ List<PwrdevHistorydataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<PwrdevHistorydataId> list=new ArrayList<>();
+ while (rs.next()){
+ PwrdevHistorydataId data=new PwrdevHistorydataId();
+ data.setRecordDatetime(rs.getTimestamp("record_datetime"));
+ for (int i=0;i<propertyNameList.size();i++){
+ try {
+ Float value = rs.getFloat(propertyNameList.get(i));
+ String propertyName = ActionUtil.toCamelCase(propertyNameList.get(i));
+ Field fieldValue = PwrdevHistorydataId.class.getDeclaredField(propertyName);
+ fieldValue.setAccessible(true);
+ fieldValue.set(data,value);
+ } catch (NoSuchFieldException e) {
+ e.printStackTrace();
+ } catch (IllegalAccessException e) {
+ e.printStackTrace();
+ }
+ }
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鑾峰彇鍏蜂綋鐨勬斁鐢垫暟鎹�
+ public List<BatttestdataId> exportTinfDataByTestRecordCount(Integer battgroupId, Integer testRecordCount, String tableName) {
+ String sql="select distinct * from "+tableName+" "+
+ " where test_record_count="+testRecordCount;
+ sql+=" order by record_time asc";
+ List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BatttestdataId> list=new ArrayList<>();
+ while (rs.next()){
+ BatttestdataId data=new BatttestdataId();
+ data.setNum(rs.getInt("num"));
+ data.setBattgroupId(rs.getInt("battgroup_id"));
+ data.setTestRecordCount(rs.getInt("test_record_count"));
+ data.setTestType(rs.getInt("test_type"));
+ data.setRecordNum(rs.getInt("record_num"));
+ data.setDataNew(rs.getInt("data_new"));
+ data.setDataAvailable(rs.getInt("data_available"));
+ data.setTestTimelong(rs.getInt("test_timelong"));
+ data.setTestCurr(rs.getFloat("test_curr"));
+ data.setTestCap(rs.getFloat("test_cap"));
+ data.setMonNum(rs.getInt("mon_num"));
+ data.setMonVol(rs.getFloat("mon_vol"));
+ data.setMonTmp(rs.getFloat("mon_tmp"));
+ data.setMonRes(rs.getFloat("mon_res"));
+ data.setTestStarttime(rs.getTimestamp("test_starttime"));
+ data.setRecordTime(rs.getTimestamp("record_time"));
+ data.setGroupVol(rs.getFloat("group_vol"));
+ data.setOnlineVol(rs.getFloat("online_vol"));
+ list.add(data);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+
}
--
Gitblit v1.9.1