package com.whyc.service; import com.whyc.mapper.CallBack; import com.whyc.pojo.BattAlarmHis; import com.whyc.pojo.BattTestInfData; 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 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 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(BattAlarmHis battAlarmHis) { String sql="SELECT count(*) as number FROM db_alarm."+battAlarmHis.getRecordYear()+" history " + " where history.binf_id="+battAlarmHis.getBinfId() ; if(battAlarmHis.getAlmStartTime()!=null){ sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(battAlarmHis.getAlmStartTime(),1)+"' "; } if(battAlarmHis.getAlmEndTime()!=null){ sql+=" and alm_start_time <='"+ThreadLocalUtil.format(battAlarmHis.getAlmEndTime(),1)+"' "; } List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { @Override public List getResults(ResultSet rs) throws SQLException { LinkedList 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 getBattHisList(BattAlarmHis battAlarmHis) { String sql="SELECT * FROM db_alarm."+battAlarmHis.getRecordYear()+" history " + " where history.binf_id="+battAlarmHis.getBinfId() ; if(battAlarmHis.getAlmStartTime()!=null){ sql+=" and alm_start_time >='"+ ThreadLocalUtil.format(battAlarmHis.getAlmStartTime(),1)+"' "; } if(battAlarmHis.getAlmEndTime()!=null){ sql+=" and alm_start_time <='"+ThreadLocalUtil.format(battAlarmHis.getAlmEndTime(),1)+"' "; } sql+=" ORDER BY alm_start_time asc limit "+battAlarmHis.getLimitStart()+","+battAlarmHis.getLimitEnd()+" "; List list=sqlExecuteService.executeQuery_call(sql, new CallBack() { @Override public List getResults(ResultSet rs) throws SQLException { List list=new ArrayList(); while (rs.next()){ BattAlarmHis data=new BattAlarmHis(); data.setNum(rs.getInt("num")); data.setBinfId(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.getFloat("alm_value")); 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 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); } }