From 22e80b64405b03d39305134dacbe821b8d5a23ad Mon Sep 17 00:00:00 2001
From: whyclxw <810412026@qq.com>
Date: 星期二, 27 五月 2025 14:16:44 +0800
Subject: [PATCH] 电池，设备，电源历史告警
---
src/main/java/com/whyc/service/SubTablePageInfoService.java | 730 +++++++++++++++++++++++++++++++++----------------------
1 files changed, 440 insertions(+), 290 deletions(-)
diff --git a/src/main/java/com/whyc/service/SubTablePageInfoService.java b/src/main/java/com/whyc/service/SubTablePageInfoService.java
index 40fba41..46fb3dd 100644
--- a/src/main/java/com/whyc/service/SubTablePageInfoService.java
+++ b/src/main/java/com/whyc/service/SubTablePageInfoService.java
@@ -1,290 +1,440 @@
-//package com.whyc.service;
-//
-//import com.whyc.dto.DevA200AlarmDto;
-//import com.whyc.mapper.CallBack;
-//import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear;
-//import com.whyc.pojo.db_lithium_testdata.BattLithiumTestData;
-//import com.whyc.util.ThreadLocalUtil;
-//import org.springframework.beans.factory.annotation.Autowired;
-//import org.springframework.stereotype.Service;
-//
-//import java.sql.ResultSet;
-//import java.sql.SQLException;
-//import java.util.ArrayList;
-//import java.util.LinkedList;
-//import java.util.List;
-//
-//@Service
-//public class SubTablePageInfoService {
-// @Autowired
-// private MybatisSqlExecuteService sqlExecuteService;
-// //鑾峰彇璁惧鏌愭璁板綍璇︾粏鐨勫崟浣撴斁鐢佃繃绋�
-// public List<BattLithiumTestData> getTdataById(Integer devId, Integer testRecordCount) {
-// String sql="select * from db_batt_testdata.tb_batttestdata_" +devId
-// +" where need_test=1 and test_record_count="+testRecordCount+" ";
-// sql+=" ORDER BY record_num asc ";
-// List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
-// @Override
-// public List getResults(ResultSet rs) throws SQLException {
-// List<BattLithiumTestData> list=new ArrayList<>();
-// while (rs.next()){
-// BattLithiumTestData tdata=new BattLithiumTestData();
-// tdata.setNum(rs.getInt("num"));
-// tdata.setDevId(rs.getInt("dev_id"));
-// tdata.setBattIdx(rs.getInt("batt_idx"));
-// tdata.setTestRecordCount(rs.getInt("test_record_count"));
-// tdata.setTestType(rs.getInt("test_type"));
-// tdata.setRecordNum(rs.getInt("record_num"));
-// tdata.setTestStarttime(rs.getTimestamp("test_starttime"));
-// tdata.setRecordTime(rs.getTimestamp("record_time"));
-// tdata.setTestTimelong(rs.getInt("test_timelong"));
-// tdata.setGroupVol(rs.getDouble("group_vol"));
-// tdata.setTestCurr(rs.getDouble("test_curr"));
-// tdata.setTestCap(rs.getDouble("test_cap"));
-// tdata.setMonNum(rs.getInt("mon_num"));
-// tdata.setMonVol(rs.getDouble("mon_vol"));
-// tdata.setMonTmp(rs.getDouble("mon_tmp"));
-// tdata.setMonCurr(rs.getDouble("mon_curr"));
-// tdata.setMonCap(rs.getDouble("mon_cap"));
-// tdata.setMonWh(rs.getDouble("mon_wh"));
-// tdata.setMonState(rs.getString("mon_state"));
-// tdata.setMonFault(rs.getString("mon_fault"));
-// tdata.setNeedTest(rs.getInt("need_test"));
-// list.add(tdata);
-// }
-// return list;
-// }
-// });
-// return list;
-// }
-//
-// //鑾峰彇璁惧鏌愭璁板綍璇︾粏鐨勫崟浣撴斁鐢佃繃绋�
-// public List<BattLithiumTestData> getTdataByIdWithListA200(Integer devId, Integer testRecordCount) {
-// String sql="select * from db_lithium_testdata.tb_batttestdata_" +devId
-// +" where need_test=1 and test_record_count="+testRecordCount+" ";
-// sql+=" ORDER BY record_num asc ";
-// List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
-// @Override
-// public List getResults(ResultSet rs) throws SQLException {
-// List list=new ArrayList<>();
-// List<Float> monVols=null;
-// List<Integer> monNums=null;
-// BattLithiumTestData tdata=null;
-// int num=0;
-// try {
-// while (rs.next()){
-// int monNum=rs.getInt("mon_num");
-// float monvol=rs.getFloat("mon_vol");
-// int recordNum=rs.getInt("record_num");
-// if(num!=recordNum){
-// if(num!=0){
-// tdata.setMonVols(monVols);
-// tdata.setMonNums(monNums);
-// list.add(tdata);
-// }
-// tdata=new BattLithiumTestData();
-// monVols=new ArrayList<>();
-// monNums=new ArrayList<>();
-// tdata.setDevId(rs.getInt("dev_id"));
-// tdata.setBattIdx(rs.getInt("batt_idx"));
-// tdata.setTestRecordCount(rs.getInt("test_record_count"));
-// tdata.setTestType(rs.getInt("test_type"));
-// tdata.setRecordNum(recordNum);
-// tdata.setTestStarttime(rs.getTimestamp("test_starttime"));
-// tdata.setRecordTime(rs.getTimestamp("record_time"));
-// tdata.setTestTimelong(rs.getInt("test_timelong"));
-// tdata.setGroupVol(rs.getDouble("group_vol"));
-// tdata.setTestCurr(rs.getDouble("test_curr"));
-// tdata.setTestCap(rs.getDouble("test_cap"));
-// tdata.setNeedTest(rs.getInt("need_test"));
-// tdata.setMaxTemp(rs.getFloat("max_temp"));
-// tdata.setMinTemp(rs.getFloat("min_temp"));
-// num=recordNum;
-// }
-// monVols.add(monvol);
-// monNums.add(monNum);
-// }
-// tdata.setMonVols(monVols);
-// tdata.setMonNums(monNums);
-// list.add(tdata);
-// } catch (SQLException e) {
-// e.printStackTrace();
-// }
-// return list;
-// }
-// });
-// return list;
-// }
-// //鏌ヨDevAlm鍘嗗彶鍛婅鏁伴噺
-// public int getCountForDevAlm(DevA200AlarmDto dto) {
-// String sql="select count(distinct num) as number from db_alarm." +dto.getTableName()
-// +" where 1=1 ";
-//
-// if(dto.getStartTime()!=null){
-// sql+=" and alm_starttime >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
-// }
-// if(dto.getEndTime()!=null){
-// sql+=" and alm_endtime <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
-// }
-// if(dto.getDevType()!=null){
-// sql+=" and FLOOR(dev_id/100000000)="+dto.getDevType();
-// }
-// if(dto.getDevId()!=null){
-// sql+=" and dev_id="+dto.getDevId();
-// }
-// if(dto.getAlmId()!=null){
-// 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()+
-// ")";
-// sql+=" order by alm_starttime desc ";
-// 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("number"));
-// } catch (SQLException e) {
-// e.printStackTrace();
-// }
-// return temp;
-// }
-// });
-// int num =0;
-// if(list!=null){
-// num= (int) list.get(0);
-// }
-// return num;
-// }
-// //鏌ヨdevalm鍘嗗彶鍛婅
-// public List getListDevAlm(DevA200AlarmDto dto){
-// String sql="select * from db_alarm." +dto.getTableName()
-// +" where 1=1 ";
-//
-// if(dto.getStartTime()!=null){
-// sql+=" and alm_starttime >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
-// }
-// if(dto.getEndTime()!=null){
-// sql+=" and alm_endtime <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
-// }
-// if(dto.getDevType()!=null){
-// sql+=" and FLOOR(dev_id/100000000)="+dto.getDevType();
-// }
-// if(dto.getDevId()!=null){
-// sql+=" and dev_id="+dto.getDevId();
-// }
-// if(dto.getAlmId()!=null){
-// 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()+
-// ")";
-// sql+=" ORDER BY alm_starttime desc limit "+dto.getLimitStart()+","+dto.getLimitEnd()+" ";
-// List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
-// @Override
-// public List getResults(ResultSet rs) throws SQLException {
-// List<DevLithiumAlarmDataYear> list=new ArrayList<>();
-// while (rs.next()){
-// DevLithiumAlarmDataYear dataYear=new DevLithiumAlarmDataYear();
-// dataYear.setNum(rs.getInt("num"));
-// dataYear.setDevId(rs.getInt("dev_id"));
-// dataYear.setAlmId(rs.getInt("alm_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<BattLithiumTestData> getTdataByIdWithListActm(Integer devId, Integer testRecordCount) {
-// String sql="select * from db_batt_testdata.tb_batttestdata_" +devId
-// +" where need_test=1 and test_record_count="+testRecordCount+" ";
-// sql+=" ORDER BY record_num asc ";
-// List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
-// @Override
-// public List getResults(ResultSet rs) throws SQLException {
-// List list=new ArrayList<>();
-// List<Float> monVols=null;
-// List<Float> monCurrs=null;
-// List<Float> monCaps=null;
-// List<Float> monWhs=null;
-// List<Integer> monNums=null;
-// BattLithiumTestData tdata=null;
-// int num=0;
-// try {
-// while (rs.next()){
-// int monNum=rs.getInt("mon_num");
-// float monvol=rs.getFloat("mon_vol");
-// float monCurr=rs.getFloat("mon_curr");
-// float monCap=rs.getFloat("mon_cap");
-// float monWh=rs.getFloat("mon_wh");
-// int recordNum=rs.getInt("record_num");
-//
-// if(num!=recordNum){
-// if(num!=0){
-// tdata.setMonVols(monVols);
-// tdata.setMonNums(monNums);
-// tdata.setMonCurrs(monCurrs);
-// tdata.setMonCaps(monCaps);
-// tdata.setMonWhs(monWhs);
-// list.add(tdata);
-// }
-// tdata=new BattLithiumTestData();
-// monVols=new ArrayList<>();
-// monNums=new ArrayList<>();
-// monCurrs=new ArrayList<>();
-// monCaps=new ArrayList<>();
-// monWhs=new ArrayList<>();
-// tdata.setDevId(rs.getInt("dev_id"));
-// tdata.setBattIdx(rs.getInt("batt_idx"));
-// tdata.setTestRecordCount(rs.getInt("test_record_count"));
-// tdata.setTestType(rs.getInt("test_type"));
-// tdata.setRecordNum(recordNum);
-// tdata.setTestStarttime(rs.getTimestamp("test_starttime"));
-// tdata.setRecordTime(rs.getTimestamp("record_time"));
-// tdata.setTestTimelong(rs.getInt("test_timelong"));
-// tdata.setGroupVol(rs.getDouble("group_vol"));
-// tdata.setTestCurr(rs.getDouble("test_curr"));
-// tdata.setTestCap(rs.getDouble("test_cap"));
-// tdata.setNeedTest(rs.getInt("need_test"));
-// tdata.setMonTmp(rs.getDouble("mon_tmp"));
-// num=recordNum;
-// }
-// monVols.add(monvol);
-// monNums.add(monNum);
-// monCurrs.add(monCurr);
-// monCaps.add(monCap);
-// monWhs.add(monWh);
-// }
-//
-// tdata.setMonVols(monVols);
-// tdata.setMonNums(monNums);
-// tdata.setMonCurrs(monCurrs);
-// tdata.setMonCaps(monCaps);
-// tdata.setMonWhs(monWhs);
-// list.add(tdata);
-// } catch (SQLException e) {
-// e.printStackTrace();
-// }
-// return list;
-// }
-// });
-// return list;
-// }
-//}
+package com.whyc.service;
+
+import com.whyc.dto.AlmHis.BattAlarmRes;
+import com.whyc.dto.AlmHis.BattAlmPar;
+import com.whyc.dto.AlmHis.DevAlmPar;
+import com.whyc.dto.AlmHis.PwrAlmPar;
+import com.whyc.mapper.CallBack;
+import com.whyc.pojo.db_alarm.BattalarmDataHistory;
+import com.whyc.pojo.db_alarm.DevalarmDataHistory;
+import com.whyc.pojo.db_pwrdev_alarm.PwrdevAlarmHistory;
+import com.whyc.util.ThreadLocalUtil;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.*;
+
+@Service
+public class SubTablePageInfoService {
+ @Autowired
+ private MybatisSqlExecuteService sqlExecuteService;
+
+
+
+ //鑾峰彇鐢垫睜缁勫巻鍙插憡璀︽暟閲�
+ public int getBattAlmHisCount(BattAlmPar param){
+ String sql="select count(distinct history.num) as number " +
+ " from db_alarm."+param.getRecordYear()+" history,db_station.tb_station_inf,db_station.tb_batt_inf " +
+ " where history.battgroup_id=tb_battinf.battgroup_id " +
+ " and tb_batt_inf.station_id=tb_station_inf.station_id " +
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_batt_inf.station_id in(" +
+ " select distinct station_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ 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("number"));
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return temp;
+ }
+ });
+ int num =0;
+ if(list!=null){
+ num= (int) list.get(0);
+ }
+ return num;
+ }
+ //鑾峰彇鐢垫睜缁勫巻鍙插憡璀﹂泦鍚�
+ public List<BattAlarmRes> getBattAlmHisList(BattAlmPar param){
+ String sql="select history.battgroup_id,history.num,alm_signal_id,history.mon_num,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time,alm_severity " +
+ " ,tb_station_inf.station_name,tb_station_inf.provice,tb_station_inf.city,tb_station_inf.country,tb_batt_inf.battgroup_name " +
+ " from db_alarm."+param.getRecordYear()+" history,db_station.tb_station_inf,db_station.tb_batt_inf " +
+ " where history.battgroup_id=tb_battinf.battgroup_id " +
+ " and tb_batt_inf.station_id=tb_station_inf.station_id " +
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_batt_inf.station_id in(" +
+ " select distinct station_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ sql+=" order by history.alm_start_time desc,history.battgroup_id asc,history.mon_num asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
+
+ List<BattAlarmRes> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattAlarmRes> list=new ArrayList<>();
+ int id=0;
+ int i=0;
+ List dataList=new ArrayList();
+ BattAlarmRes ph=new BattAlarmRes();
+ while (rs.next()){
+ int battGroupId=rs.getInt("battgroup_id");
+ if(battGroupId!=id){
+ ph.setAdataHs(dataList);
+ dataList=new ArrayList();
+ }
+ BattalarmDataHistory data=new BattalarmDataHistory();
+ data.setNum(rs.getLong("num"));
+ data.setBattgroupId(rs.getInt("battgroup_id"));
+ data.setAlmSignalId(rs.getInt("alm_signal_id"));
+ data.setMonNum(rs.getInt("mon_num"));
+ data.setAlmValue(rs.getFloat("alm_value"));
+ data.setAlmLevel(rs.getInt("alm_level"));
+ data.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
+ data.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
+ data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
+ data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
+ data.setAlmSeverity(rs.getInt("alm_severity"));
+ dataList.add(data);
+ if(battGroupId!=id){
+ if(i!=0){
+ list.add(ph);
+ }
+ id=battGroupId;
+ ph=new BattAlarmRes();
+ ph.setBattgroupId(battGroupId);
+ ph.setStationName(rs.getString("station_name"));
+ ph.setProvice(rs.getString("provice"));
+ ph.setCity(rs.getString("city"));
+ ph.setCountry(rs.getString("country"));
+ ph.setBattGroupName(rs.getString("battgroup_name"));
+ }
+ if(rs.isLast()){
+ ph.setAdataHs(dataList);
+ list.add(ph);
+ }
+ i++;
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+
+ //鑾峰彇璁惧鍘嗗彶鍛婅鏁伴噺
+ public int getDevAlmHisCount(DevAlmPar param){
+ String sql="SELECT count(distinct history.num) as number FROM db_alarm."+param.getRecordYear()+" history,db_station.tb_batt_inf,db_station.tb_station_inf " +
+ " where history.dev_id=tb_batt_inf.dev_id "+
+ " and tb_batt_inf.station_id=tb_station_inf.station_id "+
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_batt_inf.station_id in(" +
+ " select distinct station_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ 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("number"));
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return temp;
+ }
+ });
+ int num =0;
+ if(list!=null){
+ num= (int) list.get(0);
+ }
+ return num;
+ }
+ //鑾峰彇璁惧鍘嗗彶鍛婅闆嗗悎
+ public List getDevAlmHisList(DevAlmPar param){
+ String sql="SELECT history.num,dev_id,dev_ip,alm_level,alm_start_time,alm_end_time,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_severity, " +
+ " tb_station_inf.station_name,tb_station_inf.provice,tb_station_inf.city,tb_station_inf.country,tb_batt_inf.dev_name " +
+ " FROM db_alarm."+param.getRecordYear()+" history,db_station.tb_batt_inf,db_station.tb_station_inf " +
+ " where history.dev_id=tb_batt_inf.dev_id "+
+ " and tb_batt_inf.station_id=tb_station_inf.station_id "+
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_batt_inf.station_id in(" +
+ " select distinct station_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ sql+=" order by history.alm_start_time desc,history.dev_id asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
+ List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<DevalarmDataHistory> list=new ArrayList<>();
+ while (rs.next()){
+ DevalarmDataHistory ph=new DevalarmDataHistory();
+ ph.setNum(rs.getLong("num"));
+ ph.setDevId(rs.getInt("dev_id"));
+ ph.setDevIp(rs.getString("dev_ip"));
+ ph.setAlmLevel(rs.getInt("alm_level"));
+ ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
+ ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
+ ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
+ ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
+ ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
+ ph.setDevName(rs.getString("dev_name"));
+ ph.setStationName(rs.getString("station_name"));
+ ph.setProvice(rs.getString("provice"));
+ ph.setCity(rs.getString("city"));
+ ph.setCountry(rs.getString("country"));
+ list.add(ph);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+ //鐢垫簮鍘嗗彶鍛婅鏁�
+ public int getPwrAlmHisCount(PwrAlmPar param){
+ String sql="select count(distinct alarm.num) as number from db_pwrdev_alarm."+param.getRecordYear()+" alarm" +
+ ",db_station.tb_power_inf,db_station.tb_station_inf " +
+ " where alarm.power_id = tb_power_inf.power_id " +
+ " and tb_power_inf.station_id=tb_station_inf.station_id "+
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_power_inf.power_id in(" +
+ " select distinct power_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ 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("number"));
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return temp;
+ }
+ });
+ int num =0;
+ if(list!=null){
+ num= (int) list.get(0);
+ }
+ return num;
+ }
+ //鐢垫簮鍘嗗彶鍛婅闆嗗悎
+ public List<PwrdevAlarmHistory> getPwrAlmHisList(PwrAlmPar param){
+ String sql="select alarm.*," +
+ " tb_station_inf.station_name,tb_station_inf.provice,tb_station_inf.city,tb_station_inf.country,tb_power_inf.power_name " +
+ " from db_pwrdev_alarm."+param.getRecordYear()+" alarm" +
+ ",db_station.tb_power_inf,db_station.tb_station_inf " +
+ " where alarm.power_id = tb_power_inf.power_id " +
+ " and tb_power_inf.station_id=tb_station_inf.station_id "+
+ " and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' " ;
+ if(param.getProvice()!=null){
+ sql+=" and tb_station_inf.province='"+param.getProvice()+"' ";
+ }
+ if(param.getCity()!=null){
+ sql+=" and tb_station_inf.city='"+param.getCity()+"' ";
+ }
+ if(param.getCountry()!=null){
+ sql+=" and tb_station_inf.country='"+param.getCountry()+"' ";
+ }
+ if(param.getStationName()!=null){
+ sql+=" and tb_station_inf.station_name like '%"+param.getStationName()+"%' ";
+ }
+ if(param.getAlmLevel()!=null){
+ sql+=" and history.alm_level="+param.getAlmLevel()+" ";
+ }
+ List<Integer> almIds=param.getAlmIds();
+ if(almIds!=null&&almIds.size()>0){
+ sql+=" and history.alm_id in ( ";
+ for (int i=0;i<almIds.size();i++) {
+ sql+=almIds.get(i);
+ if(i!=(almIds.size()-1)){
+ sql+=",";
+ }
+ }
+ sql+=")";
+ }
+ if(param.getUid()>100){
+ sql+=" and tb_power_inf.power_id in(" +
+ " select distinct power_id from db_user.tb_baojigroup_power,db_user.tb_baojigroup_usr " +
+ " where tb_baojigroup_power.baoji_group_id=tb_baojigroup_usr.baoji_group_id " +
+ " and tb_baojigroup_usr.uid="+param.getUid()+
+ " )";
+ }
+ sql+="order by alarm.alm_start_time desc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
+ List<PwrdevAlarmHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<PwrdevAlarmHistory> list=new ArrayList<>();
+ while (rs.next()){
+ PwrdevAlarmHistory ph=new PwrdevAlarmHistory();
+ ph.setNum(rs.getLong("num"));
+ ph.setPowerId(rs.getInt("power_id"));
+ ph.setPowerName(rs.getString("power_name"));
+ ph.setAlmLevel(rs.getInt("alm_level"));
+ ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
+ ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
+ ph.setAlmValue(rs.getFloat("alm_value"));
+ ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
+ ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
+ ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
+ ph.setUsrId(rs.getInt("usr_Id"));
+ ph.setAlmTrigger(rs.getInt("alm_trigger"));
+ ph.setAlmSeverity(rs.getInt("alm_severity"));
+ ph.setStationName(rs.getString("station_name"));
+ ph.setProvice(rs.getString("provice"));
+ ph.setCity(rs.getString("city"));
+ ph.setCountry(rs.getString("country"));
+ list.add(ph);
+ }
+ return list;
+ }
+ });
+ return list;
+ }
+
+}
--
Gitblit v1.9.1