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.CompareDto;
|
import com.whyc.dto.Real.QuarterDto;
|
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_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.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 maxValue1,min("+columnName+") as minValue,avg("+columnName+") as avgValue"+" 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.setMaxValue(rs.getFloat("maxValue1"));
|
data.setMinValue(rs.getFloat("minValue"));
|
data.setAvgValue(rs.getFloat("avgValue"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
if(list!=null&&list.size()>0){
|
return list.get(0);
|
}else {
|
return null;
|
}
|
}
|
}
|
