| | |
|---|
| | | //package com.whyc.service; |
|---|
| | | // |
|---|
| | | //import com.whyc.dto.DevA200AlarmDto; |
|---|
| | | //import com.whyc.mapper.CallBack; |
|---|
| | | //import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear; |
|---|
| | | //import com.whyc.pojo.db_lithium_testdata.BattLithiumTestData; |
|---|
| | | //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 SubTablePageInfoService { |
|---|
| | | // @Autowired |
|---|
| | | // private MybatisSqlExecuteService sqlExecuteService; |
|---|
| | | // //获取设备某次记录详细的单体放电过程 |
|---|
| | | // public List<BattLithiumTestData> getTdataById(Integer devId, Integer testRecordCount) { |
|---|
| | | // String sql="select * from db_batt_testdata.tb_batttestdata_" +devId |
|---|
| | | // +" where need_test=1 and test_record_count="+testRecordCount+" "; |
|---|
| | | // sql+=" ORDER BY record_num asc "; |
|---|
| | | // List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | // @Override |
|---|
| | | // public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | // List<BattLithiumTestData> list=new ArrayList<>(); |
|---|
| | | // while (rs.next()){ |
|---|
| | | // BattLithiumTestData tdata=new BattLithiumTestData(); |
|---|
| | | // tdata.setNum(rs.getInt("num")); |
|---|
| | | // tdata.setDevId(rs.getInt("dev_id")); |
|---|
| | | // tdata.setBattIdx(rs.getInt("batt_idx")); |
|---|
| | | // tdata.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | // tdata.setTestType(rs.getInt("test_type")); |
|---|
| | | // tdata.setRecordNum(rs.getInt("record_num")); |
|---|
| | | // tdata.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | // tdata.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | // tdata.setTestTimelong(rs.getInt("test_timelong")); |
|---|
| | | // tdata.setGroupVol(rs.getDouble("group_vol")); |
|---|
| | | // tdata.setTestCurr(rs.getDouble("test_curr")); |
|---|
| | | // tdata.setTestCap(rs.getDouble("test_cap")); |
|---|
| | | // tdata.setMonNum(rs.getInt("mon_num")); |
|---|
| | | // tdata.setMonVol(rs.getDouble("mon_vol")); |
|---|
| | | // tdata.setMonTmp(rs.getDouble("mon_tmp")); |
|---|
| | | // tdata.setMonCurr(rs.getDouble("mon_curr")); |
|---|
| | | // tdata.setMonCap(rs.getDouble("mon_cap")); |
|---|
| | | // tdata.setMonWh(rs.getDouble("mon_wh")); |
|---|
| | | // tdata.setMonState(rs.getString("mon_state")); |
|---|
| | | // tdata.setMonFault(rs.getString("mon_fault")); |
|---|
| | | // tdata.setNeedTest(rs.getInt("need_test")); |
|---|
| | | // list.add(tdata); |
|---|
| | | // } |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | // }); |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | // |
|---|
| | | // //获取设备某次记录详细的单体放电过程 |
|---|
| | | // public List<BattLithiumTestData> getTdataByIdWithListA200(Integer devId, Integer testRecordCount) { |
|---|
| | | // String sql="select * from db_lithium_testdata.tb_batttestdata_" +devId |
|---|
| | | // +" where need_test=1 and test_record_count="+testRecordCount+" "; |
|---|
| | | // sql+=" ORDER BY record_num asc "; |
|---|
| | | // List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | // @Override |
|---|
| | | // public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | // List list=new ArrayList<>(); |
|---|
| | | // List<Float> monVols=null; |
|---|
| | | // List<Integer> monNums=null; |
|---|
| | | // BattLithiumTestData tdata=null; |
|---|
| | | // int num=0; |
|---|
| | | // try { |
|---|
| | | // while (rs.next()){ |
|---|
| | | // int monNum=rs.getInt("mon_num"); |
|---|
| | | // float monvol=rs.getFloat("mon_vol"); |
|---|
| | | // int recordNum=rs.getInt("record_num"); |
|---|
| | | // if(num!=recordNum){ |
|---|
| | | // if(num!=0){ |
|---|
| | | // tdata.setMonVols(monVols); |
|---|
| | | // tdata.setMonNums(monNums); |
|---|
| | | // list.add(tdata); |
|---|
| | | // } |
|---|
| | | // tdata=new BattLithiumTestData(); |
|---|
| | | // monVols=new ArrayList<>(); |
|---|
| | | // monNums=new ArrayList<>(); |
|---|
| | | // tdata.setDevId(rs.getInt("dev_id")); |
|---|
| | | // tdata.setBattIdx(rs.getInt("batt_idx")); |
|---|
| | | // tdata.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | // tdata.setTestType(rs.getInt("test_type")); |
|---|
| | | // tdata.setRecordNum(recordNum); |
|---|
| | | // tdata.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | // tdata.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | // tdata.setTestTimelong(rs.getInt("test_timelong")); |
|---|
| | | // tdata.setGroupVol(rs.getDouble("group_vol")); |
|---|
| | | // tdata.setTestCurr(rs.getDouble("test_curr")); |
|---|
| | | // tdata.setTestCap(rs.getDouble("test_cap")); |
|---|
| | | // tdata.setNeedTest(rs.getInt("need_test")); |
|---|
| | | // tdata.setMaxTemp(rs.getFloat("max_temp")); |
|---|
| | | // tdata.setMinTemp(rs.getFloat("min_temp")); |
|---|
| | | // num=recordNum; |
|---|
| | | // } |
|---|
| | | // monVols.add(monvol); |
|---|
| | | // monNums.add(monNum); |
|---|
| | | // } |
|---|
| | | // tdata.setMonVols(monVols); |
|---|
| | | // tdata.setMonNums(monNums); |
|---|
| | | // list.add(tdata); |
|---|
| | | // } catch (SQLException e) { |
|---|
| | | // e.printStackTrace(); |
|---|
| | | // } |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | // }); |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | // //查询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; |
|---|
| | | // } |
|---|
| | | // |
|---|
| | | // |
|---|
| | | // public List<BattLithiumTestData> getTdataByIdWithListActm(Integer devId, Integer testRecordCount) { |
|---|
| | | // String sql="select * from db_batt_testdata.tb_batttestdata_" +devId |
|---|
| | | // +" where need_test=1 and test_record_count="+testRecordCount+" "; |
|---|
| | | // sql+=" ORDER BY record_num asc "; |
|---|
| | | // List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | // @Override |
|---|
| | | // public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | // List list=new ArrayList<>(); |
|---|
| | | // List<Float> monVols=null; |
|---|
| | | // List<Float> monCurrs=null; |
|---|
| | | // List<Float> monCaps=null; |
|---|
| | | // List<Float> monWhs=null; |
|---|
| | | // List<Integer> monNums=null; |
|---|
| | | // BattLithiumTestData tdata=null; |
|---|
| | | // int num=0; |
|---|
| | | // try { |
|---|
| | | // while (rs.next()){ |
|---|
| | | // int monNum=rs.getInt("mon_num"); |
|---|
| | | // float monvol=rs.getFloat("mon_vol"); |
|---|
| | | // float monCurr=rs.getFloat("mon_curr"); |
|---|
| | | // float monCap=rs.getFloat("mon_cap"); |
|---|
| | | // float monWh=rs.getFloat("mon_wh"); |
|---|
| | | // int recordNum=rs.getInt("record_num"); |
|---|
| | | // |
|---|
| | | // if(num!=recordNum){ |
|---|
| | | // if(num!=0){ |
|---|
| | | // tdata.setMonVols(monVols); |
|---|
| | | // tdata.setMonNums(monNums); |
|---|
| | | // tdata.setMonCurrs(monCurrs); |
|---|
| | | // tdata.setMonCaps(monCaps); |
|---|
| | | // tdata.setMonWhs(monWhs); |
|---|
| | | // list.add(tdata); |
|---|
| | | // } |
|---|
| | | // tdata=new BattLithiumTestData(); |
|---|
| | | // monVols=new ArrayList<>(); |
|---|
| | | // monNums=new ArrayList<>(); |
|---|
| | | // monCurrs=new ArrayList<>(); |
|---|
| | | // monCaps=new ArrayList<>(); |
|---|
| | | // monWhs=new ArrayList<>(); |
|---|
| | | // tdata.setDevId(rs.getInt("dev_id")); |
|---|
| | | // tdata.setBattIdx(rs.getInt("batt_idx")); |
|---|
| | | // tdata.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | // tdata.setTestType(rs.getInt("test_type")); |
|---|
| | | // tdata.setRecordNum(recordNum); |
|---|
| | | // tdata.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | // tdata.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | // tdata.setTestTimelong(rs.getInt("test_timelong")); |
|---|
| | | // tdata.setGroupVol(rs.getDouble("group_vol")); |
|---|
| | | // tdata.setTestCurr(rs.getDouble("test_curr")); |
|---|
| | | // tdata.setTestCap(rs.getDouble("test_cap")); |
|---|
| | | // tdata.setNeedTest(rs.getInt("need_test")); |
|---|
| | | // tdata.setMonTmp(rs.getDouble("mon_tmp")); |
|---|
| | | // num=recordNum; |
|---|
| | | // } |
|---|
| | | // monVols.add(monvol); |
|---|
| | | // monNums.add(monNum); |
|---|
| | | // monCurrs.add(monCurr); |
|---|
| | | // monCaps.add(monCap); |
|---|
| | | // monWhs.add(monWh); |
|---|
| | | // } |
|---|
| | | // |
|---|
| | | // tdata.setMonVols(monVols); |
|---|
| | | // tdata.setMonNums(monNums); |
|---|
| | | // tdata.setMonCurrs(monCurrs); |
|---|
| | | // tdata.setMonCaps(monCaps); |
|---|
| | | // tdata.setMonWhs(monWhs); |
|---|
| | | // list.add(tdata); |
|---|
| | | // } catch (SQLException e) { |
|---|
| | | // e.printStackTrace(); |
|---|
| | | // } |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | // }); |
|---|
| | | // return list; |
|---|
| | | // } |
|---|
| | | //} |
|---|
| | | 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.AnalysisAlm.ResAnalysis; |
|---|
| | | import com.whyc.dto.Real.*; |
|---|
| | | import com.whyc.dto.Statistic.QuarterPwr7Res; |
|---|
| | | import com.whyc.mapper.CallBack; |
|---|
| | | import com.whyc.pojo.db_alarm.BattalarmDataHistory; |
|---|
| | | import com.whyc.pojo.db_alarm.DevalarmDataHistory; |
|---|
| | | import com.whyc.pojo.db_batt_testdata.BatttestdataId; |
|---|
| | | import com.whyc.pojo.db_data_history.BattRealdataId; |
|---|
| | | import com.whyc.pojo.db_data_history.PwrdevHistorydataId; |
|---|
| | | import com.whyc.pojo.db_pwrdev_alarm.PwrdevAlarmHistory; |
|---|
| | | import com.whyc.util.ActionUtil; |
|---|
| | | import com.whyc.util.ThreadLocalUtil; |
|---|
| | | import org.springframework.beans.factory.annotation.Autowired; |
|---|
| | | import org.springframework.stereotype.Service; |
|---|
| | | |
|---|
| | | import java.lang.reflect.Field; |
|---|
| | | import java.sql.ResultSet; |
|---|
| | | import java.sql.SQLException; |
|---|
| | | import java.util.*; |
|---|
| | | import java.util.stream.Collectors; |
|---|
| | | |
|---|
| | | @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.setAlmId(rs.getInt("alm_id")); |
|---|
| | | 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 maxData,min("+columnName+") as minData,avg("+columnName+") as avgData"+" 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.setMaxData(rs.getFloat("maxData")); |
|---|
| | | data.setMinData(rs.getFloat("minData")); |
|---|
| | | data.setAvgData(rs.getFloat("avgData")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | if(list!=null&&list.size()>0){ |
|---|
| | | return list.get(0); |
|---|
| | | }else { |
|---|
| | | return null; |
|---|
| | | } |
|---|
| | | } |
|---|
| | | //获取内阻数据 |
|---|
| | | public List<QuarterDto> getBattResInfData(Integer battgroupId,Integer testRecordCount) { |
|---|
| | | String sql="select distinct mon_num,test_starttime,mon_res, from db_batt_testdata.tb_battresdata_"+battgroupId |
|---|
| | | +" where test_record_count="+testRecordCount+" order by mon_num 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.setMonNum(rs.getInt("mon_num")); |
|---|
| | | data.setNumValue(rs.getFloat("mon_res")); |
|---|
| | | data.setRecordTime(rs.getTimestamp("test_starttime")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //找这次放电的指定一笔数据 |
|---|
| | | public List<BatttestdataId> getLastDataByBattgroupId(Integer battgroupId, Integer testRecordCount, Integer recordNum) { |
|---|
| | | String sql="select distinct * from db_batt_testdata.tb_batttestdata_"+battgroupId |
|---|
| | | +" where test_record_count="+testRecordCount+" and record_num="+recordNum+ " order by mon_num asc"; |
|---|
| | | List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<BatttestdataId> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | BatttestdataId data=new BatttestdataId(); |
|---|
| | | data.setBattgroupId(rs.getInt("battgroup_id")); |
|---|
| | | data.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | data.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | 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.setMonRes(rs.getFloat("mon_res")); |
|---|
| | | data.setMonTmp(rs.getFloat("mon_tmp")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //系统概览获取半小时核容设备信息(组端信息取一个单体信息即可) |
|---|
| | | public List<BattHisRealDto> getHalfHourBattDevData(String tableName,Integer granularity,String halfHourAgoTime) { |
|---|
| | | String sql="select distinct mon_num,record_time,group_vol,online_vol,group_curr,group_tmp,load_curr " + |
|---|
| | | "from (select a.*, (@i:= @i+1) as number " + |
|---|
| | | " from (select * from "+tableName+" "+ |
|---|
| | | " where record_time>='"+halfHourAgoTime+"' and mon_num=1) a, " + |
|---|
| | | " (select @i:=0) b) c "+ |
|---|
| | | " where c.number%"+granularity+"=0 or c.number=1 "; |
|---|
| | | sql+=" order by record_time asc"; |
|---|
| | | List<BattHisRealDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<BattHisRealDto> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | BattHisRealDto data=new BattHisRealDto(); |
|---|
| | | data.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | data.setGroupVol(rs.getFloat("group_vol")); |
|---|
| | | data.setOnlineVol(rs.getFloat("online_vol")); |
|---|
| | | data.setGroupCurr(rs.getFloat("group_curr")); |
|---|
| | | data.setGroupTmp(rs.getFloat("group_tmp")); |
|---|
| | | data.setLoadCurr(rs.getFloat("load_curr")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //系统概览获取半小时交流输入统计 |
|---|
| | | public List<PwrHisRealAcInDto> getHalfHourPwrHisAcinData(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) { |
|---|
| | | String sql=" select distinct record_datetime,acin1_vola,acin1_volb,acin1_volc,acin2_vola,acin2_volb,acin2_volc " + |
|---|
| | | " ,acin1_curra,acin1_currb,acin1_currc,acin2_curra,acin2_currb,acin2_currc " + |
|---|
| | | " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" + |
|---|
| | | " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+ |
|---|
| | | "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum; |
|---|
| | | sql+=" order by record_datetime asc"; |
|---|
| | | List<PwrHisRealAcInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<PwrHisRealAcInDto> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | PwrHisRealAcInDto data=new PwrHisRealAcInDto(); |
|---|
| | | data.setRecordDatetime(rs.getTimestamp("record_datetime")); |
|---|
| | | data.setAcin1Vola(rs.getFloat("acin1_vola")); |
|---|
| | | data.setAcin1Volb(rs.getFloat("acin1_volb")); |
|---|
| | | data.setAcin1Volc(rs.getFloat("acin1_volc")); |
|---|
| | | data.setAcin2Vola(rs.getFloat("acin2_vola")); |
|---|
| | | data.setAcin2Volb(rs.getFloat("acin2_volb")); |
|---|
| | | data.setAcin2Volc(rs.getFloat("acin2_volc")); |
|---|
| | | data.setAcin1Curra(rs.getFloat("acin1_curra")); |
|---|
| | | data.setAcin1Currb(rs.getFloat("acin1_currb")); |
|---|
| | | data.setAcin1Currc(rs.getFloat("acin1_currc")); |
|---|
| | | data.setAcin2Curra(rs.getFloat("acin2_curra")); |
|---|
| | | data.setAcin2Currb(rs.getFloat("acin2_currb")); |
|---|
| | | data.setAcin2Currc(rs.getFloat("acin2_currc")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | |
|---|
| | | public List<PwrHisRealDcoutInDto> getHalfHourPwrHisDcoutData(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) { |
|---|
| | | String sql="select distinct * " + |
|---|
| | | " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" + |
|---|
| | | " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+ |
|---|
| | | "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum; |
|---|
| | | sql+=" order by record_datetime asc"; |
|---|
| | | List<PwrHisRealDcoutInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<PwrHisRealDcoutInDto> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | PwrHisRealDcoutInDto data=new PwrHisRealDcoutInDto(); |
|---|
| | | data.setRecordDatetime(rs.getTimestamp("record_datetime")); |
|---|
| | | data.setMOutputvol(rs.getFloat("m_outputvol")); |
|---|
| | | data.setM1Outcurr(rs.getFloat("m1_outcurr")); |
|---|
| | | data.setM2Outcurr(rs.getFloat("m2_outcurr")); |
|---|
| | | data.setM3Outcurr(rs.getFloat("m3_outcurr")); |
|---|
| | | data.setM4Outcurr(rs.getFloat("m4_outcurr")); |
|---|
| | | data.setM5Outcurr(rs.getFloat("m5_outcurr")); |
|---|
| | | data.setM6Outcurr(rs.getFloat("m6_outcurr")); |
|---|
| | | data.setM7Outcurr(rs.getFloat("m7_outcurr")); |
|---|
| | | data.setM8Outcurr(rs.getFloat("m8_outcurr")); |
|---|
| | | data.setM9Outcurr(rs.getFloat("m9_outcurr")); |
|---|
| | | data.setM10Outcurr(rs.getFloat("m10_outcurr")); |
|---|
| | | data.setM11Outcurr(rs.getFloat("m11_outcurr")); |
|---|
| | | data.setM12Outcurr(rs.getFloat("m12_outcurr")); |
|---|
| | | data.setM13Outcurr(rs.getFloat("m13_outcurr")); |
|---|
| | | data.setM14Outcurr(rs.getFloat("m14_outcurr")); |
|---|
| | | data.setM15Outcurr(rs.getFloat("m15_outcurr")); |
|---|
| | | data.setM16Outcurr(rs.getFloat("m16_outcurr")); |
|---|
| | | data.setM1OutVol(rs.getFloat("m1_out_vol")); |
|---|
| | | data.setM2OutVol(rs.getFloat("m2_out_vol")); |
|---|
| | | data.setM3OutVol(rs.getFloat("m3_out_vol")); |
|---|
| | | data.setM4OutVol(rs.getFloat("m4_out_vol")); |
|---|
| | | data.setM5OutVol(rs.getFloat("m5_out_vol")); |
|---|
| | | data.setM6OutVol(rs.getFloat("m6_out_vol")); |
|---|
| | | data.setM7OutVol(rs.getFloat("m7_out_vol")); |
|---|
| | | data.setM8OutVol(rs.getFloat("m8_out_vol")); |
|---|
| | | data.setM9OutVol(rs.getFloat("m9_out_vol")); |
|---|
| | | data.setM10OutVol(rs.getFloat("m10_out_vol")); |
|---|
| | | data.setM11OutVol(rs.getFloat("m11_out_vol")); |
|---|
| | | data.setM12OutVol(rs.getFloat("m12_out_vol")); |
|---|
| | | data.setM13OutVol(rs.getFloat("m13_out_vol")); |
|---|
| | | data.setM14OutVol(rs.getFloat("m14_out_vol")); |
|---|
| | | data.setM15OutVol(rs.getFloat("m15_out_vol")); |
|---|
| | | data.setM16OutVol(rs.getFloat("m16_out_vol")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //电源历史实时 |
|---|
| | | public List<PwrHisRealDcoutInDto> getPwrRealDataHis(String tableName,Integer granularity,Date recordDatetime,Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) { |
|---|
| | | String sql="select distinct * " + |
|---|
| | | " from "+tableName+" where record_datetime>='"+ActionUtil.sdf.format(recordDatetime)+"'" + |
|---|
| | | " and record_datetime<='"+ActionUtil.sdf.format(recordDatetime1)+ |
|---|
| | | "' and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum; |
|---|
| | | sql+=" order by record_datetime asc"; |
|---|
| | | List<PwrHisRealDcoutInDto> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<PwrHisRealDcoutInDto> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | PwrHisRealDcoutInDto data=new PwrHisRealDcoutInDto(); |
|---|
| | | data.setRecordDatetime(rs.getTimestamp("record_datetime")); |
|---|
| | | data.setMOutputvol(rs.getFloat("m_outputvol")); |
|---|
| | | data.setM1Outcurr(rs.getFloat("m1_outcurr")); |
|---|
| | | data.setM2Outcurr(rs.getFloat("m2_outcurr")); |
|---|
| | | data.setM3Outcurr(rs.getFloat("m3_outcurr")); |
|---|
| | | data.setM4Outcurr(rs.getFloat("m4_outcurr")); |
|---|
| | | data.setM5Outcurr(rs.getFloat("m5_outcurr")); |
|---|
| | | data.setM6Outcurr(rs.getFloat("m6_outcurr")); |
|---|
| | | data.setM7Outcurr(rs.getFloat("m7_outcurr")); |
|---|
| | | data.setM8Outcurr(rs.getFloat("m8_outcurr")); |
|---|
| | | data.setM9Outcurr(rs.getFloat("m9_outcurr")); |
|---|
| | | data.setM10Outcurr(rs.getFloat("m10_outcurr")); |
|---|
| | | data.setM11Outcurr(rs.getFloat("m11_outcurr")); |
|---|
| | | data.setM12Outcurr(rs.getFloat("m12_outcurr")); |
|---|
| | | data.setM13Outcurr(rs.getFloat("m13_outcurr")); |
|---|
| | | data.setM14Outcurr(rs.getFloat("m14_outcurr")); |
|---|
| | | data.setM15Outcurr(rs.getFloat("m15_outcurr")); |
|---|
| | | data.setM16Outcurr(rs.getFloat("m16_outcurr")); |
|---|
| | | data.setM1OutVol(rs.getFloat("m1_out_vol")); |
|---|
| | | data.setM2OutVol(rs.getFloat("m2_out_vol")); |
|---|
| | | data.setM3OutVol(rs.getFloat("m3_out_vol")); |
|---|
| | | data.setM4OutVol(rs.getFloat("m4_out_vol")); |
|---|
| | | data.setM5OutVol(rs.getFloat("m5_out_vol")); |
|---|
| | | data.setM6OutVol(rs.getFloat("m6_out_vol")); |
|---|
| | | data.setM7OutVol(rs.getFloat("m7_out_vol")); |
|---|
| | | data.setM8OutVol(rs.getFloat("m8_out_vol")); |
|---|
| | | data.setM9OutVol(rs.getFloat("m9_out_vol")); |
|---|
| | | data.setM10OutVol(rs.getFloat("m10_out_vol")); |
|---|
| | | data.setM11OutVol(rs.getFloat("m11_out_vol")); |
|---|
| | | data.setM12OutVol(rs.getFloat("m12_out_vol")); |
|---|
| | | data.setM13OutVol(rs.getFloat("m13_out_vol")); |
|---|
| | | data.setM14OutVol(rs.getFloat("m14_out_vol")); |
|---|
| | | data.setM15OutVol(rs.getFloat("m15_out_vol")); |
|---|
| | | data.setM16OutVol(rs.getFloat("m16_out_vol")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //历史测试记录具体某一次放电数据详情 |
|---|
| | | public List<BatttestdataId> getTinfDataWithTestRecordCount(String tableName, Integer testRecordCount, Integer recordNum, Integer granularity) { |
|---|
| | | String sql="select distinct * from "+tableName+" "+ |
|---|
| | | " where test_record_count="+testRecordCount+" " + |
|---|
| | | " and record_num%"+granularity+"=0 or record_num=1 or record_num="+recordNum; |
|---|
| | | sql+=" order by record_time asc"; |
|---|
| | | List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<BatttestdataId> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | BatttestdataId data=new BatttestdataId(); |
|---|
| | | data.setNum(rs.getInt("num")); |
|---|
| | | data.setBattgroupId(rs.getInt("battgroup_id")); |
|---|
| | | data.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | data.setTestType(rs.getInt("test_type")); |
|---|
| | | data.setRecordNum(rs.getInt("record_num")); |
|---|
| | | data.setDataNew(rs.getInt("data_new")); |
|---|
| | | data.setDataAvailable(rs.getInt("data_available")); |
|---|
| | | data.setTestTimelong(rs.getInt("test_timelong")); |
|---|
| | | 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")); |
|---|
| | | data.setMonRes(rs.getFloat("mon_res")); |
|---|
| | | data.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | data.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | data.setGroupVol(rs.getFloat("group_vol")); |
|---|
| | | data.setOnlineVol(rs.getFloat("online_vol")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //判断电池组历史实时表是否存在 |
|---|
| | | public int judgeTable_realdata( String table){ |
|---|
| | | String sql="select count(*) as tableNum " + |
|---|
| | | " from INFORMATION_SCHEMA.TABLES " + |
|---|
| | | " where TABLE_SCHEMA = 'db_data_history' " + |
|---|
| | | " and TABLE_NAME = 'tb_batt_realdata_"+table+"'"; |
|---|
| | | 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("tableNum")); |
|---|
| | | } catch (SQLException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } |
|---|
| | | return temp; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | int tableNum =0; |
|---|
| | | if(list!=null){ |
|---|
| | | tableNum= (int) list.get(0); |
|---|
| | | } |
|---|
| | | return tableNum; |
|---|
| | | } |
|---|
| | | //判断电源历史实时表是否存在 |
|---|
| | | public int judgeTable_pwrhis( String table){ |
|---|
| | | String sql="select count(*) as tableNum " + |
|---|
| | | " from INFORMATION_SCHEMA.TABLES " + |
|---|
| | | " where TABLE_SCHEMA = 'db_data_history' " + |
|---|
| | | " and TABLE_NAME = 'tb_pwrdev_historydata_"+table+"'"; |
|---|
| | | 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("tableNum")); |
|---|
| | | } catch (SQLException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } |
|---|
| | | return temp; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | int tableNum =0; |
|---|
| | | if(list!=null){ |
|---|
| | | tableNum= (int) list.get(0); |
|---|
| | | } |
|---|
| | | return tableNum; |
|---|
| | | } |
|---|
| | | //电池数据历史实时处理 |
|---|
| | | public List<RealDateDTO> getBattRealDataHis(BattRealdataId realdata, int granularity,Integer maxRecordNum,Integer minRecordNum) { |
|---|
| | | String sql=" select record_time, group_vol, online_vol,group_curr,load_curr, mon_vol, mon_tmp, mon_res, mon_num,record_num " + |
|---|
| | | " from db_data_history.tb_batt_realdata_"+realdata.getTableName()+" " + |
|---|
| | | " where record_time >= '"+ThreadLocalUtil.format(realdata.getRecordTime(),1)+"' " + |
|---|
| | | " and record_time <= '"+ThreadLocalUtil.format(realdata.getRecordTime1(),1)+"' "+ |
|---|
| | | " and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+maxRecordNum+" or record_num="+minRecordNum ; |
|---|
| | | List<RealDateDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<RealDateDTO> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | RealDateDTO ph=new RealDateDTO(); |
|---|
| | | ph.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | ph.setGroupVol(rs.getFloat("group_vol")); |
|---|
| | | ph.setOnlineVol(rs.getFloat("online_vol")); |
|---|
| | | ph.setGroupCurr(rs.getFloat("group_curr")); |
|---|
| | | ph.setMonVol(rs.getFloat("mon_vol")); |
|---|
| | | ph.setMonTmp(rs.getFloat("mon_tmp")); |
|---|
| | | ph.setMonRes(rs.getFloat("mon_res")); |
|---|
| | | ph.setMonNum(rs.getInt("mon_num")); |
|---|
| | | ph.setRecordNum(rs.getInt("record_num")); |
|---|
| | | list.add(ph); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | |
|---|
| | | //系统概览获取前N笔核容设备信息 |
|---|
| | | public List<RealDateDTO> getBattDevData100(String tableName, Integer granularity, Date recordDatetime, Date recordDatetime1,Integer maxRecordNum,Integer minRecordNum) { |
|---|
| | | String sql=" select record_time, group_vol, online_vol,group_curr, load_curr,record_num " + |
|---|
| | | " from "+tableName+" " + |
|---|
| | | " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " + |
|---|
| | | " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' "+ |
|---|
| | | " and (record_num-"+minRecordNum+")%"+granularity+"=0 or record_num="+minRecordNum+" or record_num="+maxRecordNum ; |
|---|
| | | List<RealDateDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<RealDateDTO> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | RealDateDTO ph=new RealDateDTO(); |
|---|
| | | ph.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | ph.setGroupVol(rs.getFloat("group_vol")); |
|---|
| | | ph.setOnlineVol(rs.getFloat("online_vol")); |
|---|
| | | ph.setGroupCurr(rs.getFloat("group_curr")); |
|---|
| | | ph.setLoadCurr(rs.getFloat("load_curr")); |
|---|
| | | ph.setRecordNum(rs.getInt("record_num")); |
|---|
| | | list.add(ph); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //电池数据历史实时处理(预警分析) |
|---|
| | | public List<ResAnalysis> getBattHisRealInAlm(BattRealdataId realdata, Map<String,Object> battMap) { |
|---|
| | | String result = String.join(",", battMap.keySet()); |
|---|
| | | String sql=" select record_time,"+result+" " + |
|---|
| | | " from db_data_history.tb_batt_realdata_"+realdata.getTableName()+" " + |
|---|
| | | " where record_time >= '"+ThreadLocalUtil.format(realdata.getRecordTime(),1)+"' " + |
|---|
| | | " and record_time <= '"+ThreadLocalUtil.format(realdata.getRecordTime1(),1)+"' " + |
|---|
| | | " order by record_time asc,mon_num asc"; |
|---|
| | | List<ResAnalysis> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<ResAnalysis> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | ResAnalysis ph=new ResAnalysis(); |
|---|
| | | ph.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | int i=1; |
|---|
| | | for (String key : battMap.keySet()) { |
|---|
| | | Integer dataType = (Integer) battMap.get(key); |
|---|
| | | try { |
|---|
| | | String propertyType = "dataType"+(i); |
|---|
| | | Field fieldType = ResAnalysis.class.getDeclaredField(propertyType); |
|---|
| | | fieldType.setAccessible(true); |
|---|
| | | fieldType.set(ph,dataType); |
|---|
| | | |
|---|
| | | Float value = rs.getFloat(key); |
|---|
| | | String propertyName = "dataName"+(i); |
|---|
| | | Field fieldName = ResAnalysis.class.getDeclaredField(propertyName); |
|---|
| | | fieldName.setAccessible(true); // 如果字段是私有的，需要设置为可访问 |
|---|
| | | fieldName.set(ph,key); |
|---|
| | | |
|---|
| | | String propertyValue = "dataValue"+(i); |
|---|
| | | Field fieldValue = ResAnalysis.class.getDeclaredField(propertyValue); |
|---|
| | | fieldValue.setAccessible(true); |
|---|
| | | fieldValue.set(ph,value); |
|---|
| | | } catch (NoSuchFieldException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } catch (IllegalAccessException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } |
|---|
| | | i++; |
|---|
| | | } |
|---|
| | | list.add(ph); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | |
|---|
| | | //电源数据历史实时处理(预警分析) |
|---|
| | | public List<ResAnalysis> getPwrHisRealInAlm(PwrdevHistorydataId pwrHis, Map<String,Object> pwrMap) { |
|---|
| | | String result = String.join(",", pwrMap.keySet()); |
|---|
| | | String sql=" select record_datetime,"+result+" " + |
|---|
| | | " from db_data_history.tb_pwrdev_historydata_"+pwrHis.getTableName()+" " + |
|---|
| | | " where record_datetime >= '"+ThreadLocalUtil.format(pwrHis.getRecordDatetime(),1)+"' " + |
|---|
| | | " and record_datetime <= '"+ThreadLocalUtil.format(pwrHis.getRecordDatetime1(),1)+"' " + |
|---|
| | | " order by record_datetime asc "; |
|---|
| | | List<ResAnalysis> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<ResAnalysis> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | ResAnalysis ph=new ResAnalysis(); |
|---|
| | | ph.setRecordTime(rs.getTimestamp("record_datetime")); |
|---|
| | | int i=1; |
|---|
| | | for (String key : pwrMap.keySet()) { |
|---|
| | | Integer dataType = (Integer) pwrMap.get(key); |
|---|
| | | try { |
|---|
| | | String propertyType = "dataType"+(i); |
|---|
| | | Field fieldType = ResAnalysis.class.getDeclaredField(propertyType); |
|---|
| | | fieldType.setAccessible(true); |
|---|
| | | fieldType.set(ph,dataType); |
|---|
| | | |
|---|
| | | Float value = rs.getFloat(key); |
|---|
| | | String propertyName = "dataName"+(i); |
|---|
| | | Field fieldName = ResAnalysis.class.getDeclaredField(propertyName); |
|---|
| | | fieldName.setAccessible(true); // 如果字段是私有的，需要设置为可访问 |
|---|
| | | fieldName.set(ph,key); |
|---|
| | | |
|---|
| | | String propertyValue = "dataValue"+(i); |
|---|
| | | Field fieldValue = ResAnalysis.class.getDeclaredField(propertyValue); |
|---|
| | | fieldValue.setAccessible(true); |
|---|
| | | fieldValue.set(ph,value); |
|---|
| | | } catch (NoSuchFieldException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } catch (IllegalAccessException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } |
|---|
| | | i++; |
|---|
| | | } |
|---|
| | | list.add(ph); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //获取电池组指定时间段内最大最小recordNum确保数据的完整 |
|---|
| | | public List getBattMaxAndMinRecordNum(String tableName, Date recordDatetime, Date recordDatetime1) { |
|---|
| | | String sql="select max(record_num) as maxRecordNum,min(record_num) as minRecordNum " + |
|---|
| | | " from " + tableName+" " + |
|---|
| | | " where record_time >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " + |
|---|
| | | " and record_time <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' " + |
|---|
| | | " limit 1"; |
|---|
| | | List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List list = new ArrayList<>(); |
|---|
| | | while (rs.next()) { |
|---|
| | | list.add(rs.getInt("maxRecordNum")); |
|---|
| | | list.add(rs.getInt("minRecordNum")); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //获取电源指定时间段内最大最小recordNum确保数据的完整 |
|---|
| | | public List getPwrMaxAndMinRecordNum(String tableName, Date recordDatetime, Date recordDatetime1) { |
|---|
| | | String sql="select max(record_num) as maxRecordNum,min(record_num) as minRecordNum " + |
|---|
| | | " from " + tableName+" " + |
|---|
| | | " where record_datetime >= '"+ThreadLocalUtil.format(recordDatetime,1)+"' " + |
|---|
| | | " and record_datetime <= '"+ThreadLocalUtil.format(recordDatetime1,1)+"' " + |
|---|
| | | " limit 1"; |
|---|
| | | List list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List list = new ArrayList<>(); |
|---|
| | | while (rs.next()) { |
|---|
| | | list.add(rs.getInt("maxRecordNum")); |
|---|
| | | list.add(rs.getInt("minRecordNum")); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //1.2.7优良电源统计上一季度的电源某一属性和参数阈值超过的次数 |
|---|
| | | public List<PwrdevHistorydataId> getPwrQuarter7(String tableName,List<String> propertyNameList) { |
|---|
| | | String result = propertyNameList.stream().collect(Collectors.joining(",")); |
|---|
| | | String sql=" select distinct record_datetime,"+result+" " + |
|---|
| | | " from "+tableName+" " ; |
|---|
| | | sql+=" order by record_datetime asc"; |
|---|
| | | List<PwrdevHistorydataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<PwrdevHistorydataId> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | PwrdevHistorydataId data=new PwrdevHistorydataId(); |
|---|
| | | data.setRecordDatetime(rs.getTimestamp("record_datetime")); |
|---|
| | | for (int i=0;i<propertyNameList.size();i++){ |
|---|
| | | try { |
|---|
| | | Float value = rs.getFloat(propertyNameList.get(i)); |
|---|
| | | String propertyName = ActionUtil.toCamelCase(propertyNameList.get(i)); |
|---|
| | | Field fieldValue = PwrdevHistorydataId.class.getDeclaredField(propertyName); |
|---|
| | | fieldValue.setAccessible(true); |
|---|
| | | fieldValue.set(data,value); |
|---|
| | | } catch (NoSuchFieldException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } catch (IllegalAccessException e) { |
|---|
| | | e.printStackTrace(); |
|---|
| | | } |
|---|
| | | } |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | //获取具体的放电数据 |
|---|
| | | public List<BatttestdataId> exportTinfDataByTestRecordCount(Integer battgroupId, Integer testRecordCount, String tableName) { |
|---|
| | | String sql="select distinct * from "+tableName+" "+ |
|---|
| | | " where test_record_count="+testRecordCount; |
|---|
| | | sql+=" order by record_time asc"; |
|---|
| | | List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() { |
|---|
| | | @Override |
|---|
| | | public List getResults(ResultSet rs) throws SQLException { |
|---|
| | | List<BatttestdataId> list=new ArrayList<>(); |
|---|
| | | while (rs.next()){ |
|---|
| | | BatttestdataId data=new BatttestdataId(); |
|---|
| | | data.setNum(rs.getInt("num")); |
|---|
| | | data.setBattgroupId(rs.getInt("battgroup_id")); |
|---|
| | | data.setTestRecordCount(rs.getInt("test_record_count")); |
|---|
| | | data.setTestType(rs.getInt("test_type")); |
|---|
| | | data.setRecordNum(rs.getInt("record_num")); |
|---|
| | | data.setDataNew(rs.getInt("data_new")); |
|---|
| | | data.setDataAvailable(rs.getInt("data_available")); |
|---|
| | | data.setTestTimelong(rs.getInt("test_timelong")); |
|---|
| | | 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")); |
|---|
| | | data.setMonRes(rs.getFloat("mon_res")); |
|---|
| | | data.setTestStarttime(rs.getTimestamp("test_starttime")); |
|---|
| | | data.setRecordTime(rs.getTimestamp("record_time")); |
|---|
| | | data.setGroupVol(rs.getFloat("group_vol")); |
|---|
| | | data.setOnlineVol(rs.getFloat("online_vol")); |
|---|
| | | list.add(data); |
|---|
| | | } |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | }); |
|---|
| | | return list; |
|---|
| | | } |
|---|
| | | |
|---|
| | | } |
|---|
