package com.whyc.service;
|
|
import com.whyc.mapper.CallBack;
|
import com.whyc.pojo.db_lock_alarm.LockAlarmHis;
|
import com.whyc.pojo.db_lock_his.LockHis;
|
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 int getLockHisCount(LockHis his) {
|
String sql="SELECT count(*) as number FROM db_lock_his."+ his.getRecordYear()+" history " +
|
" where history.lock_id="+ his.getLockId() ;
|
if(his.getStartTime()!=null){
|
sql+=" and record_time >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
|
}
|
if(his.getEndTime()!=null){
|
sql+=" and record_time <='"+ThreadLocalUtil.format(his.getEndTime(),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<LockHis> getLockHisList(LockHis his) {
|
String sql="SELECT * FROM db_lock_his."+ his.getRecordYear()+" history " +
|
" where history.lock_id="+ his.getLockId() ;
|
if(his.getStartTime()!=null){
|
sql+=" and record_time >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
|
}
|
if(his.getEndTime()!=null){
|
sql+=" and record_time <='"+ThreadLocalUtil.format(his.getEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY record_time asc limit "+ his.getLimitStart()+","+ his.getLimitEnd()+" ";
|
List<LockHis> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
LockHis data=new LockHis();
|
data.setNum(rs.getInt("num"));
|
data.setLockId(rs.getInt("lock_id"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setLockState(rs.getInt("lock_state"));
|
data.setUnlockType(rs.getInt("unlock_type"));
|
data.setUnlockId(rs.getString("unlock_id"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//锁的历史记录
|
public List<LockHis> getLockHisWithReal(LockHis his) {
|
String sql="SELECT * FROM db_lock_his."+ his.getRecordYear()+" history " +
|
" where history.lock_id="+ his.getLockId() ;
|
if(his.getStartTime()!=null){
|
sql+=" and record_time >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
|
}
|
if(his.getEndTime()!=null){
|
sql+=" and record_time <='"+ThreadLocalUtil.format(his.getEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY record_time asc ";
|
List<LockHis> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
LockHis data=new LockHis();
|
data.setNum(rs.getInt("num"));
|
data.setLockId(rs.getInt("lock_id"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setLockState(rs.getInt("lock_state"));
|
data.setUnlockType(rs.getInt("unlock_type"));
|
data.setUnlockId(rs.getString("unlock_id"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//锁的告警历史记录总数
|
public int getLockAlmHisCount(LockAlarmHis his) {
|
String sql="SELECT count(*) as number FROM db_lock_alarm."+ his.getRecordYear()+" history " +
|
" where 1=1 ";
|
if(his.getLids()!=null&&his.getLids().size()>0){
|
List<Integer> lids=his.getLids();
|
sql+=" and history.lock_id in (";
|
for (int i=0;i<lids.size();i++) {
|
if(i==lids.size()-1){
|
sql=sql+lids.get(i)+") ";
|
}else{
|
sql=sql+lids.get(i)+",";
|
}
|
}
|
}
|
if(his.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
|
}
|
if(his.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(his.getEndTime(),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<LockAlarmHis> getLockAlmHisList(LockAlarmHis his) {
|
String sql="SELECT * FROM db_lock_alarm."+ his.getRecordYear()+" history " +
|
" where 1=1 " ;
|
if(his.getLids()!=null&&his.getLids().size()>0){
|
List<Integer> lids=his.getLids();
|
sql+=" and history.lock_id in (";
|
for (int i=0;i<lids.size();i++) {
|
if(i==lids.size()-1){
|
sql=sql+lids.get(i)+") ";
|
}else{
|
sql=sql+lids.get(i)+",";
|
}
|
}
|
}
|
if(his.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
|
}
|
if(his.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(his.getEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY alm_start_time asc limit "+ his.getLimitStart()+","+ his.getLimitEnd()+" ";
|
List<LockAlarmHis> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
while (rs.next()){
|
LockAlarmHis data=new LockAlarmHis();
|
data.setNum(rs.getInt("num"));
|
data.setLockId(rs.getInt("lock_id"));
|
data.setAlmId(rs.getInt("alm_id"));
|
data.setAlmSource(rs.getInt("alm_source"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
}
|
