package com.whyc.service;
|
|
import com.whyc.dto.LockAlmHisDto;
|
import com.whyc.dto.LockHisDto;
|
import com.whyc.mapper.CallBack;
|
import com.whyc.pojo.plus_lock_alarm.LockAlarmHis;
|
import com.whyc.pojo.plus_lock_his.LockHis;
|
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(LockHisDto dto) {
|
String sql="SELECT count(*) as number FROM plus_lock_his."+ dto.getRecordYear()+" history " +
|
" where history.lock_id="+ dto.getLockId() ;
|
if(dto.getStationName1()!=null){
|
sql+=" and tb_station_inf.station_name1 like '%"+dto.getStationName1()+"%' ";
|
}
|
if(dto.getStationName2()!=null){
|
sql+=" and tb_station_inf.station_name2 like '%"+dto.getStationName2()+"%' ";
|
}
|
if(dto.getStationName3()!=null){
|
sql+=" and tb_station_inf.station_name3 like '%"+dto.getStationName3()+"%' ";
|
}
|
if(dto.getStationName4()!=null){
|
sql+=" and tb_station_inf.station_name4 like '%"+dto.getStationName4()+"%' ";
|
}
|
if(dto.getLockName()!=null){
|
sql+=" and tb_lock_inf.lock_name like '%"+dto.getLockName()+"%' ";
|
}
|
if(dto.getStartTime()!=null){
|
sql+=" and record_time >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
|
}
|
if(dto.getEndTime()!=null){
|
sql+=" and record_time <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
|
}
|
if(dto.getUid()>100){
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" and tb_baojigroup_usr.uid= " +dto.getUid()+
|
" )";
|
}else{
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" )";
|
}
|
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(LockHisDto dto) {
|
String sql="SELECT * FROM plus_lock_his."+ dto.getRecordYear()+" history " +
|
" where history.lock_id="+ dto.getLockId() ;
|
if(dto.getStationName1()!=null){
|
sql+=" and tb_station_inf.station_name1 like '%"+dto.getStationName1()+"%' ";
|
}
|
if(dto.getStationName2()!=null){
|
sql+=" and tb_station_inf.station_name2 like '%"+dto.getStationName2()+"%' ";
|
}
|
if(dto.getStationName3()!=null){
|
sql+=" and tb_station_inf.station_name3 like '%"+dto.getStationName3()+"%' ";
|
}
|
if(dto.getStationName4()!=null){
|
sql+=" and tb_station_inf.station_name4 like '%"+dto.getStationName4()+"%' ";
|
}
|
if(dto.getLockName()!=null){
|
sql+=" and tb_lock_inf.lock_name like '%"+dto.getLockName()+"%' ";
|
}
|
if(dto.getStartTime()!=null){
|
sql+=" and record_time >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
|
}
|
if(dto.getEndTime()!=null){
|
sql+=" and record_time <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
|
}
|
if(dto.getUid()>100){
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" and tb_baojigroup_usr.uid= " +dto.getUid()+
|
" )";
|
}else{
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" )";
|
}
|
sql+=" ORDER BY record_time asc limit "+ dto.getLimitStart()+","+ dto.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 plus_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(LockAlmHisDto dto) {
|
String sql="SELECT count(*) as number FROM plus_lock_alarm."+ dto.getRecordYear()+" history" +
|
",plus_inf.tb_lock_inf,plus_inf.tb_station_inf " +
|
" where history.lock_id=tb_lock_inf.lock_id " +
|
" and tb_lock_inf.station_id=tb_station_inf.station_id ";
|
if(dto.getStationName1()!=null){
|
sql+=" and tb_station_inf.station_name1 like '%"+dto.getStationName1()+"%' ";
|
}
|
if(dto.getStationName2()!=null){
|
sql+=" and tb_station_inf.station_name2 like '%"+dto.getStationName2()+"%' ";
|
}
|
if(dto.getStationName3()!=null){
|
sql+=" and tb_station_inf.station_name3 like '%"+dto.getStationName3()+"%' ";
|
}
|
if(dto.getStationName4()!=null){
|
sql+=" and tb_station_inf.station_name4 like '%"+dto.getStationName4()+"%' ";
|
}
|
if(dto.getLockName()!=null){
|
sql+=" and tb_lock_inf.lock_name like '%"+dto.getLockName()+"%' ";
|
}
|
if(dto.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
|
}
|
if(dto.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
|
}
|
if(dto.getAlmIdList()!=null&&dto.getAlmIdList().size()>0){
|
List almIdList=dto.getAlmIdList();
|
sql+=" and history.alm_id in (";
|
for (int i=0;i<almIdList.size();i++) {
|
if(i==almIdList.size()-1){
|
sql=sql+almIdList.get(i)+") ";
|
}else{
|
sql=sql+almIdList.get(i)+",";
|
}
|
}
|
}
|
if(dto.getUid()>100){
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" and tb_baojigroup_usr.uid= " +dto.getUid()+
|
" )";
|
}else{
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" )";
|
}
|
|
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(LockAlmHisDto dto) {
|
String sql="SELECT history.* FROM plus_lock_alarm."+ dto.getRecordYear()+" history" +
|
",plus_inf.tb_lock_inf,plus_inf.tb_station_inf " +
|
" where history.lock_id=tb_lock_inf.lock_id " +
|
" and tb_lock_inf.station_id=tb_station_inf.station_id ";
|
if(dto.getStationName1()!=null){
|
sql+=" and tb_station_inf.station_name1 like '%"+dto.getStationName1()+"%' ";
|
}
|
if(dto.getStationName2()!=null){
|
sql+=" and tb_station_inf.station_name2 like '%"+dto.getStationName2()+"%' ";
|
}
|
if(dto.getStationName3()!=null){
|
sql+=" and tb_station_inf.station_name3 like '%"+dto.getStationName3()+"%' ";
|
}
|
if(dto.getStationName4()!=null){
|
sql+=" and tb_station_inf.station_name4 like '%"+dto.getStationName4()+"%' ";
|
}
|
if(dto.getLockName()!=null){
|
sql+=" and tb_lock_inf.lock_name like '%"+dto.getLockName()+"%' ";
|
}
|
if(dto.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
|
}
|
if(dto.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
|
}
|
if(dto.getAlmIdList()!=null&&dto.getAlmIdList().size()>0){
|
List almIdList=dto.getAlmIdList();
|
sql+=" and history.alm_id in (";
|
for (int i=0;i<almIdList.size();i++) {
|
if(i==almIdList.size()-1){
|
sql=sql+almIdList.get(i)+") ";
|
}else{
|
sql=sql+almIdList.get(i)+",";
|
}
|
}
|
}
|
if(dto.getUid()>100){
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" and tb_baojigroup_usr.uid= " +dto.getUid()+
|
" )";
|
}else{
|
sql+=" and tb_lock_inf.lock_id in(" +
|
"select distinct lock_id from plus_user.tb_baojigroup_lock,plus_user.tb_baojigroup_usr " +
|
" where tb_baojigroup_lock.baoji_id=tb_baojigroup_usr.baoji_id " +
|
" )";
|
}
|
sql+=" ORDER BY history.alm_start_time asc limit "+ dto.getLimitStart()+","+ dto.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;
|
}
|
|
|
}
|
