package com.whyc.service;
|
|
import com.whyc.dto.DevA200AlarmDto;
|
import com.whyc.mapper.CallBack;
|
import com.whyc.pojo.db_alarm.BattAlarmHistory;
|
import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear;
|
import com.whyc.pojo.db_dis_batt.BattTestInfData;
|
import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
|
import com.whyc.util.ActionUtil;
|
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 SubTableService {
|
@Autowired
|
private MybatisSqlExecuteService sqlExecuteService;
|
//根据充放电记录查询单体放电历史详情
|
public List<BattTestInfData> getTDataHis(int binfId, int testRecordCount) {
|
String sql="select * from db_dis_batt.batt_test_inf_"+binfId+" " +
|
" where binf_id="+binfId+" " +
|
" and test_record_count="+testRecordCount+
|
" order by test_starttime asc ";
|
List<BattTestInfData> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
BattTestInfData data=new BattTestInfData();
|
data.setNum(rs.getInt("num"));
|
data.setBinfId(rs.getInt("binf_id"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setTestType(rs.getInt("test_type"));
|
data.setRecordNum(rs.getInt("record_num"));
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
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.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//电池告警历史实时计算每张表查询总数
|
public int getBattHisCount(BattAlarmHistory battAlarmHistory) {
|
String sql="SELECT count(*) as number FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history " +
|
" where 1=1" ;
|
if (battAlarmHistory.getAlmLevel()!=null){
|
sql+=" and history.alm_level="+ battAlarmHistory.getAlmLevel();
|
}
|
if(battAlarmHistory.getAlmStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(battAlarmHistory.getAlmStartTime(),1)+"' ";
|
}
|
if(battAlarmHistory.getAlmEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(battAlarmHistory.getAlmEndTime(),1)+"' ";
|
}
|
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<BattAlarmHistory> getBattHisList(BattAlarmHistory battAlarmHistory) {
|
String sql="SELECT history.*,power_inf.power_name,power_inf.binf_name as battGroupName FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history,db_batt.power_inf where history.binf_id=power_inf.binf_id ";
|
if(battAlarmHistory.getAlmLevel()!=null){
|
sql+=" and history.alm_level="+ battAlarmHistory.getAlmLevel();
|
}
|
if(battAlarmHistory.getAlmStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(battAlarmHistory.getAlmStartTime(),1)+"' ";
|
}
|
if(battAlarmHistory.getAlmEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(battAlarmHistory.getAlmEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY alm_start_time desc limit "+ battAlarmHistory.getLimitStart()+","+ battAlarmHistory.getLimitEnd()+" ";
|
List<BattAlarmHistory> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
BattAlarmHistory data=new BattAlarmHistory();
|
data.setNum(rs.getInt("num"));
|
data.setBattGroupId(rs.getInt("binf_id"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setMonNum(rs.getInt("mon_num"));
|
data.setAlmId(rs.getInt("alm_id"));
|
data.setAlmLevel(rs.getInt("alm_level"));
|
data.setAlmValue(rs.getInt("alm_value"));
|
data.setPowerName(rs.getString("power_name"));
|
data.setBattGroupName(rs.getString("battGroupName"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//电源告警历史实时计算每张表查询总数
|
public int getPowerHisCount(PowerAlarmHistory powerAlarmHistory) {
|
String sql="SELECT count(*) as number FROM db_power_alarm."+ powerAlarmHistory.getRecordYear()+" history " +
|
" where 1=1";
|
if(powerAlarmHistory.getAlmLevel()!=null){
|
sql+= " and history.alm_level="+ powerAlarmHistory.getAlmLevel();
|
}
|
if(powerAlarmHistory.getAlmStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(powerAlarmHistory.getAlmStartTime(),1)+"' ";
|
}
|
if(powerAlarmHistory.getAlmEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(powerAlarmHistory.getAlmEndTime(),1)+"' ";
|
}
|
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<PowerAlarmHistory> getPowerHisList(PowerAlarmHistory powerAlarmHistory) {
|
String sql="SELECT history.*,power_inf.power_name FROM db_power_alarm."+ powerAlarmHistory.getRecordYear()+" history,db_batt.power_inf where history.power_id = power_inf.power_id ";
|
if(powerAlarmHistory.getAlmLevel()!=null){
|
sql+= " and history.alm_level="+ powerAlarmHistory.getAlmLevel();
|
}
|
if(powerAlarmHistory.getAlmStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(powerAlarmHistory.getAlmStartTime(),1)+"' ";
|
}
|
if(powerAlarmHistory.getAlmEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(powerAlarmHistory.getAlmEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY alm_start_time desc limit "+ powerAlarmHistory.getLimitStart()+","+ powerAlarmHistory.getLimitEnd()+" ";
|
List<PowerAlarmHistory> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
PowerAlarmHistory data=new PowerAlarmHistory();
|
data.setNum(rs.getInt("num"));
|
data.setPowerId(rs.getInt("power_id"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setAlmId(rs.getInt("alm_id"));
|
data.setAlmLevel(rs.getInt("alm_level"));
|
data.setAlmValue(rs.getInt("alm_value"));
|
data.setPowerName(rs.getString("power_name"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//取该单体最后一笔放电记录
|
public BattTestInfData getMonNumData(int binfId, Integer testRecordCount, Integer recordNum, int monNum) {
|
String sql="select * from db_dis_batt.batt_test_inf_"+binfId+" " +
|
" where binf_id="+binfId+" " +
|
" and test_record_count="+testRecordCount+
|
" and record_num="+recordNum+
|
" and mon_num="+monNum;
|
List<BattTestInfData> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
BattTestInfData data=new BattTestInfData();
|
data.setNum(rs.getInt("num"));
|
data.setBinfId(rs.getInt("binf_id"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setTestType(rs.getInt("test_type"));
|
data.setRecordNum(rs.getInt("record_num"));
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
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.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list.stream().findFirst().orElse((BattTestInfData) ActionUtil.objeNull);
|
}
|
|
//查询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;
|
}
|
|
}
|
