package com.whyc.service;
|
|
import com.whyc.dto.AlmHis.BattAlmPar;
|
import com.whyc.dto.AlmHis.DevAlmPar;
|
import com.whyc.dto.AlmHis.PwrAlmPar;
|
import com.whyc.dto.Real.*;
|
import com.whyc.mapper.CallBack;
|
import com.whyc.pojo.db_alarm.BattalarmDataHistory;
|
import com.whyc.pojo.db_alarm.DevalarmDataHistory;
|
import com.whyc.pojo.db_batt_testdata.BatttestdataId;
|
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.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_batt_inf.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.provice='"+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<BattalarmDataHistory> 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_batt_inf.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.provice='"+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;
|
}
|
});*/
|
List<BattalarmDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
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"));
|
data.setBattgroupId(rs.getInt("battgroup_id"));
|
data.setStationName(rs.getString("station_name"));
|
data.setProvice(rs.getString("provice"));
|
data.setCity(rs.getString("city"));
|
data.setCountry(rs.getString("country"));
|
data.setBattgroupName(rs.getString("battgroup_name"));
|
list.add(data);
|
}
|
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.provice='"+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.*, " +
|
" 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.provice='"+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.setAlmId(rs.getInt("alm_id"));
|
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.provice='"+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 alarm.alm_level="+param.getAlmLevel()+" ";
|
}
|
List<Integer> almIds=param.getAlmIds();
|
if(almIds!=null&&almIds.size()>0){
|
sql+=" and alarm.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.provice='"+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 alarm.alm_level="+param.getAlmLevel()+" ";
|
}
|
List<Integer> almIds=param.getAlmIds();
|
if(almIds!=null&&almIds.size()>0){
|
sql+=" and alarm.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.setAlmId(rs.getInt("alm_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;
|
}
|
//获取电池组某一年月的数据
|
public List<QuarterDto> getBattRealHis(String tableName, String columnName) {
|
String sql="select distinct mon_num,record_time,"+columnName+" from "+tableName+" order by record_time 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.setRecordTime(rs.getTimestamp("record_time"));
|
data.setNumValue(rs.getFloat(columnName));
|
data.setMonNum(rs.getInt("mon_num"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//获取表中最大，最小，平均数据值
|
public CompareDto getBattCompareHis(String tableName, String columnName) {
|
String sql="select max("+columnName+") as maxData,min("+columnName+") as minData,avg("+columnName+") as avgData"+" from "+tableName;
|
List<CompareDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<CompareDto> list=new ArrayList<>();
|
while (rs.next()){
|
CompareDto data=new CompareDto();
|
data.setMaxData(rs.getFloat("maxData"));
|
data.setMinData(rs.getFloat("minData"));
|
data.setAvgData(rs.getFloat("avgData"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
if(list!=null&&list.size()>0){
|
return list.get(0);
|
}else {
|
return null;
|
}
|
}
|
//获取内阻数据
|
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
|
+" 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 List<BatttestdataId> getLastDataByBattgroupId(Integer battgroupId, Integer testRecordCount, Integer recordNum) {
|
String sql="select distinct * from db_batt_testdata.tb_batttestdata_"+battgroupId
|
+" where test_record_count="+testRecordCount+" and record_num="+recordNum+ " order by mon_num 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.setBattgroupId(rs.getInt("battgroup_id"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setOnlineVol(rs.getFloat("online_vol"));
|
data.setGroupVol(rs.getFloat("group_vol"));
|
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.setMonRes(rs.getFloat("mon_res"));
|
data.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
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";
|
List<PwrHisRealAcInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrHisRealAcInDto> list=new ArrayList<>();
|
while (rs.next()){
|
PwrHisRealAcInDto data=new PwrHisRealAcInDto();
|
data.setRecordDatetime(rs.getTimestamp("record_datetime"));
|
data.setAcin1Vola(rs.getFloat("acin1_vola"));
|
data.setAcin1Volb(rs.getFloat("acin1_volb"));
|
data.setAcin1Volc(rs.getFloat("acin1_volc"));
|
data.setAcin2Vola(rs.getFloat("acin2_vola"));
|
data.setAcin2Volb(rs.getFloat("acin2_volb"));
|
data.setAcin2Volc(rs.getFloat("acin2_volc"));
|
data.setAcin1Curra(rs.getFloat("acin1_curra"));
|
data.setAcin1Currb(rs.getFloat("acin1_currb"));
|
data.setAcin1Currc(rs.getFloat("acin1_currc"));
|
data.setAcin2Curra(rs.getFloat("acin2_curra"));
|
data.setAcin2Currb(rs.getFloat("acin2_currb"));
|
data.setAcin2Currc(rs.getFloat("acin2_currc"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public List<PwrHisRealDcoutInDto> getHalfHourPwrHisDcoutData(String tableName,Integer granularity) {
|
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 ";
|
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_outvol"));
|
data.setM2OutVol(rs.getFloat("m2_outvol"));
|
data.setM3OutVol(rs.getFloat("m3_outvol"));
|
data.setM4OutVol(rs.getFloat("m4_outvol"));
|
data.setM5OutVol(rs.getFloat("m5_outvol"));
|
data.setM6OutVol(rs.getFloat("m6_outvol"));
|
data.setM7OutVol(rs.getFloat("m7_outvol"));
|
data.setM8OutVol(rs.getFloat("m8_outvol"));
|
data.setM9OutVol(rs.getFloat("m9_outvol"));
|
data.setM10OutVol(rs.getFloat("m10_outvol"));
|
data.setM11OutVol(rs.getFloat("m11_outvol"));
|
data.setM12OutVol(rs.getFloat("m12_outvol"));
|
data.setM13OutVol(rs.getFloat("m13_outvol"));
|
data.setM14OutVol(rs.getFloat("m14_outvol"));
|
data.setM15OutVol(rs.getFloat("m15_outvol"));
|
data.setM16OutVol(rs.getFloat("m16_outvol"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//历史测试记录具体某一次放电数据详情
|
public List<BatttestdataId> getTinfDataWithTestRecordCount(String tableName, Integer testRecordCount, Integer recordNum, Integer granularity) {
|
String sql="select distinct * from "+tableName+" "+
|
" where test_record_count="+testRecordCount+" " +
|
" and record_num%"+granularity+"=0 or record_num=1 or record_num="+recordNum;
|
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;
|
}
|
//判断历史实时表是否存在
|
public int judgeTable_realdata( String table){
|
String sql="select count(*) as tableNum " +
|
" from INFORMATION_SCHEMA.TABLES " +
|
" where TABLE_SCHEMA = 'db_data_history' " +
|
" and TABLE_NAME = 'tb_batt_realdata_"+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 " +
|
" 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)+"' "+
|
" and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+maxRecordNum+" or record_num="+minRecordNum ;
|
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.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
ph.setMonRes(rs.getFloat("mon_res"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//获取指定时间段内最大最小recordNum确保数据的完整
|
public List getMaxAndMinRecordNum(BattRealdataId realdata) {
|
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)+"' " +
|
" 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;
|
}
|
}
|
