package com.fgkj.dao.impl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.fgkj.actions.ActionUtil; import com.fgkj.dao.BaseDAO; import com.fgkj.dao.BattCapFactory; import com.fgkj.dao.BattTestData; import com.fgkj.dao.CallBack; import com.fgkj.dao.DAOHelper; import com.fgkj.dao.LimitNumberFactory; import com.fgkj.db.DBUtil; import com.fgkj.db.IDatabaseName; import com.fgkj.dto.BattInf; import com.fgkj.dto.Batt_Maint_Dealarm; import com.fgkj.dto.Batt_State; import com.fgkj.dto.Batt_rtdata; import com.fgkj.dto.Batt_rtstate; import com.fgkj.dto.Batttestdata_inf; public class Batttestdata_infDAOImpl implements BaseDAO,CallBack{ private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //向batttestdata_inf表中添加数据 public boolean add(Object obj) { Batttestdata_inf b=(Batttestdata_inf)obj; String sql="insert into db_batt_testdata.tb_batttestdata_inf(BattGroupId,test_record_count,test_record_count_ex,test_type,record_time_interval,data_new,data_available,record_num,test_starttime,test_starttime_ex,test_starttype,record_time,test_timelong,test_stoptype,group_vol,test_curr,test_cap,max_monnum,max_monvol,min_monnum,min_monvol,mon_num,mon_vol,upload_usr_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; return DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{ b.getBattGroupId(), b.getTest_record_count(), b.getTest_record_count_ex(), b.getTest_type(), b.getRecord_time_interval(), b.getData_new(), b.getData_available(), b.getRecord_num(), b.getTest_starttime(), b.getTest_starttime_ex(), b.getTest_starttype(), b.getRecord_time(), b.getTest_timelong(), b.getTest_stoptype(), b.getGroup_vol(), b.getTest_curr(), b.getTest_cap(), b.getMax_monnum(), b.getMax_monvol(), b.getMin_monnum(), b.getMin_monvol(), b.getMon_num(), b.getMon_vol(), b.getUpload_usr_id() }); } //1.1测试battgroupid在batttestdata_inf中是否存在 public List judge(Object obj){ Batttestdata_inf bti=(Batttestdata_inf)obj; String sql="select table_name from `INFORMATION_SCHEMA`.`TABLES` " + "where table_name ='tb_batttestdatastop_"+bti.getBattGroupId()+"'and TABLE_SCHEMA='db_batt_testdata'"; return DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ String name=rs.getString("table_name"); list.add(name); //System.out.println(binf); } } catch (SQLException e) { e.printStackTrace(); } return list; } }); } public boolean update(Object obj) { return false; } //6.1电池充放电删除操作 public boolean del(Object obj) { Batttestdata_inf tdata=(Batttestdata_inf)obj; String sql="delete from db_batt_testdata.tb_batttestdata_inf where num=?"; return DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{tdata.getNum()}); } //电池充/放电数据管理删除 public String delPro(Object obj) { Batttestdata_inf tdata=(Batttestdata_inf)obj; String sql="delete from db_batt_testdata.tb_batttestdata_inf where num="+tdata.getNum(); return sql; } //查询所有的充放电信息 public List searchAll() { String sql="select num,BattGroupId,test_record_count,test_record_count_ex,test_type,record_time_interval,data_new,data_available,record_num,test_starttime,test_starttime_ex,test_starttype,record_time,test_timelong,test_stoptype,group_vol,test_curr,test_cap,max_monnum,max_monvol,min_monnum,min_monvol,mon_num,mon_vol,upload_usr_id from db_batt_testdata.tb_batttestdata_inf " + "where db_batt_testdata.tb_batttestdata_inf.data_available=1 "; return DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf b=new Batttestdata_inf(); b.setNum(rs.getInt("num")); b.setBattGroupId(rs.getInt("BattGroupId")); b.setTest_record_count(rs.getInt("test_record_count")); b.setTest_record_count_ex(rs.getInt("test_record_count_ex")); b.setTest_type(rs.getInt("test_type")); b.setRecord_time_interval(rs.getInt("record_time_interval")); b.setData_new(rs.getInt("data_new")); b.setData_available(rs.getInt("data_available")); b.setRecord_num(rs.getInt("record_num")); b.setTest_starttime(rs.getTimestamp("test_starttime")); b.setTest_starttime_ex(rs.getTimestamp("test_starttime_ex")); b.setTest_starttype(rs.getInt("test_starttype")); b.setRecord_time(rs.getTimestamp("record_time")); b.setTest_timelong(rs.getInt("test_timelong")); b.setTest_stoptype(rs.getInt("test_stoptype")); b.setGroup_vol(rs.getFloat("group_vol")); b.setTest_curr(rs.getFloat("test_curr")); b.setTest_cap(rs.getFloat("test_cap")); b.setMax_monnum(rs.getInt("max_monnum")); b.setMax_monvol(rs.getFloat("max_monvol")); b.setMin_monnum(rs.getInt("min_monnum")); b.setMin_monvol(rs.getFloat("min_monvol")); b.setMon_num(rs.getInt("mon_num")); b.setMon_vol(rs.getFloat("mon_vol")); b.setUpload_usr_id(rs.getInt("upload_usr_id")); list.add(b); //System.out.println(b); } } catch (SQLException e) { e.printStackTrace(); } return list; } }); } //历史数据查询 public List serchByCondition(Object obj) { final Batttestdata_inf bti=(Batttestdata_inf)obj; String sql="select num,BattGroupId,test_record_count,test_record_count_ex,test_type,record_time_interval,data_new,data_available,record_num,test_starttime,test_starttime_ex,test_starttype,record_time,test_timelong,test_stoptype,group_vol,test_curr,test_cap,max_monnum,max_monvol,min_monnum,min_monvol,mon_num,mon_vol,upload_usr_id " + " from db_batt_testdata.tb_batttestdata_inf where BattGroupId=? " + " and db_batt_testdata.tb_batttestdata_inf.data_available=1 " + " order by test_type asc,test_starttime desc "; return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{bti.getBattGroupId()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf b=new Batttestdata_inf(); b.setNum(rs.getInt("num")); b.setBattGroupId(rs.getInt("BattGroupId")); b.setTest_record_count(rs.getInt("test_record_count")); b.setTest_record_count_ex(rs.getInt("test_record_count_ex")); b.setTest_type(rs.getInt("test_type")); b.setRecord_time_interval(rs.getInt("record_time_interval")); b.setData_new(rs.getInt("data_new")); b.setData_available(rs.getInt("data_available")); b.setRecord_num(rs.getInt("record_num")); b.setTest_starttime(rs.getTimestamp("test_starttime")); b.setTest_starttime_ex(rs.getTimestamp("test_starttime_ex")); b.setTest_starttype(rs.getInt("test_starttype")); b.setRecord_time(rs.getTimestamp("record_time")); b.setTest_timelong(rs.getInt("test_timelong")); b.setTest_stoptype(rs.getInt("test_stoptype")); //放电终止原因 if(bti.getNum()/100000==6185){ b.setTest_stoptype_reason(BattTestData.getStopType_6185(b.getTest_stoptype())); }else if(bti.getNum()/100000==4016){ b.setTest_stoptype_reason(BattTestData.getStopType_4016(b.getTest_stoptype())); }else{ b.setTest_stoptype_reason(BattTestData.getStopType(b.getTest_starttype(), b.getTest_stoptype())); } b.setGroup_vol(rs.getFloat("group_vol")); b.setTest_curr(rs.getFloat("test_curr")); b.setTest_cap(rs.getFloat("test_cap")); b.setMax_monnum(rs.getInt("max_monnum")); b.setMax_monvol(rs.getFloat("max_monvol")); b.setMin_monnum(rs.getInt("min_monnum")); b.setMin_monvol(rs.getFloat("min_monvol")); b.setMon_num(rs.getInt("mon_num")); b.setMon_vol(rs.getFloat("mon_vol")); b.setUpload_usr_id(rs.getInt("upload_usr_id")); list.add(b); } } catch (SQLException e) { e.printStackTrace(); } return list; } }); } //6.2根据battgroupi和test_starttime查test_record_count public List serchTest_record_count(Object obj) { Batttestdata_inf tdata=(Batttestdata_inf)obj; String sql="select test_record_count " + "from db_batt_testdata.tb_batttestdata_inf where BattGroupId=? and (test_starttime)=(?) " + "and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{tdata.getBattGroupId(),tdata.getTest_starttime()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf tdata=new Batttestdata_inf(); tdata.setTest_record_count(rs.getInt("test_record_count")); list.add(tdata); //System.out.println(tdata); } } catch (SQLException e) { e.printStackTrace(); } return list; } }); } //0.7电测放电测试比例 public List serchByInfo(Object obj) { Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj; Batttestdata_inf tdata=bmd.getTdata(); BattInf binf=bmd.getBinf(); String sql=""; String baseSql="select distinct(tb_battinf.battgroupid),battgroupname,stationname,stationname1,battinusedate " + "from db_battinf.tb_battinf " + "where stationname1 like ? "; //测试年限 String timeSqlT=" and tb_battinf.battgroupid not in (select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId) from db_batt_testdata.tb_batttestdata_inf where to_days(test_starttime)>=to_days(?) and to_days(test_starttime)<=to_days(?) and to_days(test_starttime)<=to_days(record_time) and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; String timeSqlF=" and tb_battinf.battgroupid not in (select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId) from db_batt_testdata.tb_batttestdata_inf where to_days(test_starttime)>=to_days(?) and to_days(test_starttime)<=to_days(?) and to_days(test_starttime)<=to_days(record_time) and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; if(tdata.getNum()==100){ baseSql+=timeSqlT; }else{ baseSql+=timeSqlF; } //用于测试类型 //全部 String start_typeA=" ) "; //拉闸放电 String start_typeF=" and test_starttype<5 and test_type not in(2,9) and test_starttype!=2) "; //核对性放电 String start_typeT=" and test_starttype>=5 or (test_type not in(2,9) and test_starttype=2)) "; if(tdata.getTest_starttype()==0){ baseSql+=start_typeA; }else if(tdata.getTest_starttype()==1){ baseSql+=start_typeF; }else if(tdata.getTest_starttype()==2){ baseSql+=start_typeT; } String endSql=" order by tb_battinf.StationName1,tb_battinf.battgroupId"; sql=baseSql+endSql; //System.out.println(sql); List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%",tdata.getTest_starttime(),tdata.getTest_starttime_ex()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); int id=0; try { while(rs.next()){ Batt_Maint_Dealarm bmd=new Batt_Maint_Dealarm(); BattInf binf=new BattInf(); if(id==rs.getInt("battGroupId")){ continue; }else{ id=rs.getInt("battGroupId"); } binf.setBattGroupId(rs.getInt("battGroupId")); binf.setBattGroupName(rs.getString("battGroupName")); binf.setStationName(rs.getString("stationName")); binf.setStationName1(rs.getString("stationName1")); binf.setBattInUseDate(rs.getDate("battInUseDate")); Batttestdata_inf tdata=new Batttestdata_inf(); tdata.setBattGroupId(rs.getInt("battGroupId")); bmd.setBinf(binf); bmd.setTdata(tdata); list.add(bmd); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //0.10电池健康率 public List serchGood(){ String sql="select distinct(battgroupid),test_record_count,test_cap from db_batt_testdata.tb_batttestdata_inf where data_available=1 order by battgroupid "; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf tdata=new Batttestdata_inf(); tdata.setBattGroupId(rs.getInt("battGroupId")); tdata.setTest_record_count(rs.getInt("test_record_count")); tdata.setTest_cap(rs.getFloat("test_cap")); list.add(tdata); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //7.1电池放电落后单体筛选，并将电容告警的电池组加到告警表中 /*开始放电的时间判断的时间段，放在test_starttime和test_starttime_ex中*/ public List serchBadBatt(Object obj){ Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj; Batttestdata_inf tdata=bmd.getTdata(); BattInf binf=bmd.getBinf(); String sql=""; String baseSql="select DISTINCT(tb_batttestdata_inf.BattGroupId),tb_batttestdata_inf.test_starttime,tb_batttestdata_inf.record_time,tb_batttestdata_inf.test_cap," + "tb_batttestdata_inf.max_monvol,tb_batttestdata_inf.min_monvol,tb_batttestdata_inf.test_record_count,tb_batttestdata_inf.test_curr, " + "db_battinf.tb_battinf.stationname1,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.battproducer,db_battinf.tb_battinf.battinusedate,db_battinf.tb_battinf.MonCapStd,db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.monCount,db_battinf.tb_battinf.battgroupname " + "FROM db_batt_testdata.tb_batttestdata_inf " + "LEFT OUTER JOIN db_battinf.tb_battinf ON db_batt_testdata.tb_batttestdata_inf.BattGroupId=db_battinf.tb_battinf.BattGroupId " + "where test_type=3 " + "and battinusedate>=? and battinusedate<=? " + "and tb_batttestdata_inf.test_starttime>=? and tb_batttestdata_inf.test_starttime<=? " + "and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; //用于维护区 String station1SqlT=" and stationname1!=? ";//全部 String station1SqlF=" and stationname1=? "; if(binf.getStationName1().equals("")){ baseSql+=station1SqlT; }else{ baseSql+=station1SqlF; } //用于机房站点 String stationSqlT=" and stationname!=? ";//全部 String stationSqlF=" and stationname=? "; if(binf.getStationName().equals("")){ baseSql+=stationSqlT; }else{ baseSql+=stationSqlF; } //选取蓄电池组条件 String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? "; String idSqlF=" and db_battinf.tb_battinf.battgroupid=? "; if(binf.getBattGroupId()==0){ baseSql+=idSqlT; }else{ baseSql+=idSqlF; } //用于电池类型 String producerSqlT=" and battproducer!=? ";//全部 String producerSqlF=" and battproducer=? "; if(binf.getBattProducer().equals("")){ baseSql+=producerSqlT; }else{ baseSql+=producerSqlF; } String endSql=" ORDER BY db_batt_testdata.tb_batttestdata_inf.battgroupid asc,db_batt_testdata.tb_batttestdata_inf.test_starttime desc "; sql=baseSql+endSql; //System.out.println(sql); List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{ binf.getBattInUseDate(), binf.getBattInUseDate1(), tdata.getTest_starttime(), tdata.getTest_starttime_ex(), binf.getStationName1(), binf.getStationName(), binf.getBattGroupId(), binf.getBattProducer() }, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batt_Maint_Dealarm bmd =new Batt_Maint_Dealarm(); Batttestdata_inf tdata=new Batttestdata_inf(); BattInf binf=new BattInf(); binf.setStationName1(rs.getString("stationName1")); binf.setStationName(rs.getString("stationName")); binf.setBattProducer(rs.getString("battProducer")); if(rs.getString("battInUseDate")!=null) binf.setBattInUseDate(rs.getDate("battInUseDate")); binf.setMonVolStd(rs.getFloat("monVolStd")); binf.setMonCapStd(rs.getFloat("monCapStd")); binf.setMonCount(rs.getInt("monCount")); binf.setBattGroupName(rs.getString("battGroupName")); tdata.setBattGroupId(rs.getInt("battGroupId")); if(rs.getString("test_starttime")!=null) tdata.setTest_starttime(rs.getTimestamp("test_starttime")); if(rs.getString("record_time")!=null) tdata.setRecord_time(rs.getTimestamp("record_time")); tdata.setTest_cap(rs.getFloat("test_cap")); tdata.setMax_monvol(rs.getFloat("max_monvol")); tdata.setMin_monvol(rs.getFloat("min_monvol")); tdata.setTest_record_count(rs.getInt("test_record_count")); tdata.setTest_curr(rs.getFloat("test_curr")); bmd.setBinf(binf); bmd.setTdata(tdata); list.add(bmd); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //机房历史放电数据续航能力查询(历史) public List serchBattLife(Object obj){ BattInf binf=(BattInf) obj; String sql=""; String baseSql=" SELECT DISTINCT(tb_batttestdata_inf.BattGroupId),tb_batttestdata_inf.test_starttime,tb_batttestdata_inf.record_time,tb_batttestdata_inf.test_cap," + " tb_batttestdata_inf.max_monvol,tb_batttestdata_inf.min_monvol,tb_batttestdata_inf.test_record_count,tb_batttestdata_inf.test_curr " + ",db_battinf.tb_battinf.load_curr AS curr " + ",db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.stationname1,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.MonCapStd,db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.battgroupname " + " FROM db_batt_testdata.tb_batttestdata_inf " + " LEFT OUTER JOIN db_battinf.tb_battinf ON db_batt_testdata.tb_batttestdata_inf.BattGroupId=db_battinf.tb_battinf.BattGroupId " + " WHERE tb_batttestdata_inf.test_starttime>=? and tb_batttestdata_inf.test_starttime<=? " + " and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; //机房维护区选择 String stationSql=" AND db_battinf.tb_battinf.stationname like ? and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname2 like ? "; baseSql+=stationSql; String endSql=" ORDER BY db_battinf.tb_battinf.stationid asc,tb_batttestdata_inf.BattGroupId asc,db_batt_testdata.tb_batttestdata_inf.test_starttime desc "; sql=baseSql+endSql; //System.out.println(sql); List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{ binf.getBattProductDate(),binf.getBattProductDate1(), "%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%" }, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batt_Maint_Dealarm bmd =new Batt_Maint_Dealarm(); Batttestdata_inf tdata=new Batttestdata_inf(); BattInf binf=new BattInf(); binf.setStationId(rs.getString("stationId")); binf.setStationName1(rs.getString("stationName1")); binf.setStationName(rs.getString("stationName")); binf.setMonVolStd(rs.getFloat("monVolStd")); binf.setMonCapStd(rs.getFloat("monCapStd")); binf.setBattGroupName(rs.getString("battGroupName")); tdata.setBattGroupId(rs.getInt("battGroupId")); if(rs.getString("test_starttime")!=null) tdata.setTest_starttime(rs.getTimestamp("test_starttime")); if(rs.getString("record_time")!=null) tdata.setRecord_time(rs.getTimestamp("record_time")); tdata.setTest_cap(rs.getFloat("test_cap")); tdata.setMax_monvol(rs.getFloat("max_monvol")); tdata.setMin_monvol(rs.getFloat("min_monvol")); tdata.setTest_record_count(rs.getInt("test_record_count")); tdata.setTest_curr(rs.getFloat("curr"));//三大运营商电流 bmd.setBinf(binf); bmd.setTdata(tdata); list.add(bmd); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //机房历史放电数据续航能力查询(实时) public List serchBattLifeNow(Object obj){ BattInf binf=(BattInf) obj; String numberSql=" SELECT FOUND_ROWS() number"; Connection conn=DBUtil.getConn(); String sql=""; String baseSql="SELECT SQL_CALC_FOUND_ROWS tb_batt_rtstate.BattGroupId,batt_count,tb_batt_rtstate.rec_datetime,online_vol,group_vol,group_curr,batt_state," + "batt_test_type,batt_test_starttime,batt_test_recordtime,batt_test_tlong," + "batt_test_cap,batt_real_cap,batt_rest_cap,batt_rest_power1_time,batt_rest_power2_time" + ",tb_batt_rtdata.BattGroupId,tb_batt_rtdata.rec_datetime,mon_num,mon_vol,mon_tmp," + "mon_res,mon_ser,mon_conn_res,mon_cap " + ",BINARY(curr1+curr2+curr3) AS curr " + ",db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.moncapstd,db_battinf.tb_battinf.monvolstd " + ",db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationname1,db_battinf.tb_battinf.BattGroupName,db_battinf.tb_battinf.deviceName " + ",MAX(binary tb_batt_rtdata.mon_vol) as maxmonvol,MIN(binary tb_batt_rtdata.mon_vol) as minmonvol " + "FROM db_ram_db.tb_batt_rtstate " + "LEFT OUTER JOIN db_ram_db.tb_batt_rtdata ON tb_batt_rtstate.BattGroupId = tb_batt_rtdata.BattGroupId " + "LEFT OUTER JOIN db_battinf.tb_battinf ON tb_batt_rtstate.BattGroupId = db_battinf.tb_battinf.BattGroupId " + "LEFT OUTER JOIN db_ram_db.cmcc_power_data ON db_battinf.tb_battinf.fbsdeviceid=cmcc_power_data.dev_id " + "where "; //机房维护区选择 String stationSql=" db_battinf.tb_battinf.stationname like ? AND db_battinf.tb_battinf.stationname1 like ? AND db_battinf.tb_battinf.stationname2 like ? "; baseSql+=stationSql; //电池状态 String stateSqlT=" AND batt_state=? "; String stateSqlF=" AND batt_state!=? "; if(binf.getNum()==100){ baseSql+=stateSqlF; }else{ baseSql+=stateSqlT; } //分组 String endSql=" GROUP BY tb_batt_rtstate.BattGroupId "; //排序 String orderSql=" order by db_battinf.tb_battinf.stationid asc"; sql=baseSql+endSql+orderSql; //System.out.println(sql); List list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%" ,binf.getNum()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batt_State state=new Batt_State(); Batt_rtstate bstate=new Batt_rtstate(); bstate.setBattGroupId(rs.getInt("tb_batt_rtstate.BattGroupId")); bstate.setBatt_count(rs.getInt("batt_count")); bstate.setRec_datetime(rs.getTimestamp("tb_batt_rtstate.rec_datetime")); bstate.setOnline_vol(rs.getFloat("online_vol")); bstate.setGroup_vol(rs.getFloat("group_vol")); bstate.setGroup_curr(rs.getFloat("curr"));//三大运营商电流和 bstate.setBatt_state(rs.getInt("batt_state")); bstate.setBatt_test_type(rs.getInt("batt_test_type")); bstate.setBatt_test_starttime(rs.getTimestamp("batt_test_starttime")); bstate.setBatt_test_recordtime(rs.getTimestamp("batt_test_recordtime")); bstate.setBatt_test_tlong(rs.getInt("batt_test_tlong")); bstate.setBatt_test_cap(rs.getFloat("batt_test_cap")); bstate.setBatt_real_cap(rs.getFloat("batt_real_cap")); bstate.setBatt_rest_cap(rs.getFloat("batt_rest_cap")); bstate.setBatt_rest_power1_time(rs.getInt("batt_rest_power1_time")); bstate.setBatt_rest_power2_time(rs.getInt("batt_rest_power2_time")); Batt_rtdata bdata=new Batt_rtdata(); bdata.setMon_vol(rs.getFloat("maxmonvol")); bdata.setMon_tmp(rs.getFloat("minmonvol")); BattInf binf=new BattInf(); binf.setBattGroupId(rs.getInt("battGroupId")); binf.setStationId(rs.getString("stationid")); binf.setMonCapStd(rs.getFloat("monCapStd")); binf.setMonVolStd(rs.getFloat("MonVolStd")); binf.setBattGroupName(rs.getString("battGroupName")); binf.setDeviceName(rs.getString("deviceName")); binf.setStationName(rs.getString("stationName")); binf.setStationName1(rs.getString("stationName1")); binf.setStationName9(BattTestData.battState(rs.getInt("batt_state"))); state.setBstate(bstate); state.setBdata(bdata); state.setBinf(binf); list.add(state); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); //去掉limit条件后的总数 int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql); //System.out.println("number: "+number); if(list!=null&&list.size()>0){ list.get(list.size()-1).getBinf().setMonNum(number); } return list; } //充电测试数据 public List serchByTestType2(Object obj) { Batttestdata_inf binf=(Batttestdata_inf) obj; String sql="select battgroupid,test_starttime,test_curr,max_monvol,min_monvol,test_cap from db_batt_testdata.tb_batttestdata_inf where test_type=2 and battgroupid=? " + "and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and db_batt_testdata.tb_batttestdata_inf.data_available=1"; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId(),binf.getRecord_time(),binf.getRecord_time1()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf batt=new Batttestdata_inf(); batt.setBattGroupId(rs.getInt("battGroupId")); batt.setTest_starttime(rs.getTimestamp("test_starttime")); batt.setTest_curr(rs.getFloat("test_curr")); batt.setMax_monvol(rs.getFloat("max_monvol")); batt.setTest_cap(rs.getFloat("test_cap")); batt.setMin_monvol(rs.getFloat("min_monvol")); list.add(batt); } } catch (SQLException e) { e.printStackTrace(); } return list; } }); return list; } //放电测试数据 public List serchByTestType3(Object obj) { Batttestdata_inf binf=(Batttestdata_inf) obj; String sql="select battgroupid,test_starttime,test_curr,max_monvol,min_monvol,test_cap from db_batt_testdata.tb_batttestdata_inf where test_type=3 and battgroupid=? " + "and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId(),binf.getRecord_time(),binf.getRecord_time1()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf batt=new Batttestdata_inf(); batt.setBattGroupId(rs.getInt("battGroupId")); batt.setTest_starttime(rs.getTimestamp("test_starttime")); batt.setTest_curr(rs.getFloat("test_curr")); batt.setMax_monvol(rs.getFloat("max_monvol")); batt.setTest_cap(rs.getFloat("test_cap")); batt.setMin_monvol(rs.getFloat("min_monvol")); list.add(batt); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //新增放电测试次数 public List serchByDataNew(Object obj) { Batttestdata_inf binf=(Batttestdata_inf) obj; String sql="select battgroupid,test_record_count from db_batt_testdata.tb_batttestdata_inf where test_type=3 and data_new=1 " + "and battgroupid=? and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf batt=new Batttestdata_inf(); batt.setBattGroupId(rs.getInt("battGroupId")); batt.setTest_record_count(rs.getInt("test_record_count")); /*batt.setTest_starttime(DAOHelper.sdf.parse(rs.getString("test_starttime"))); batt.setTest_type(rs.getInt("test_type")); batt.setTest_curr(rs.getFloat("test_curr")); batt.setTest_timelong(rs.getInt("test_timelong")); batt.setMax_monvol(rs.getFloat("max_monvol")); batt.setTest_cap(rs.getFloat("test_cap")); batt.setMin_monvol(rs.getFloat("min_monvol"));*/ list.add(batt); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //6.4.2根据条件查询符合条件的测试完成的电池组(蓄电池放电测试完成率) /* * mon_num中存放层次 * test_starttypeHex 中存放层次对应namem名字*/ public List serchComplete(Object obj){ Batttestdata_inf tdata=(Batttestdata_inf) obj; String sql=""; String baseSql="select distinct(tb_batttestdata_inf.battgroupid) from db_batt_testdata.tb_batttestdata_inf,db_battinf.tb_battinf " + "where tb_batttestdata_inf.BattGroupId=db_battinf.tb_battinf.BattGroupId " + "and (test_starttime)>=(?) and (test_starttime)<=(?) " + "and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; //上传方式 String usrSqlT="and upload_usr_id!=? " ; String usrSqlU="and upload_usr_id!=0 and upload_usr_id!=? " ; String usrSqlF="and upload_usr_id=? " ; if(tdata.getUpload_usr_id()==100){//全部 baseSql+=usrSqlT; }else{ if(tdata.getUpload_usr_id()==0){ baseSql+=usrSqlF; }else{ baseSql+=usrSqlU; } } String levelSql=" and db_battinf.tb_battinf.stationname4 like ? and db_battinf.tb_battinf.stationname3 like ? and db_battinf.tb_battinf.stationname2 like ? and db_battinf.tb_battinf.stationname1 like ? ";//分组层次 sql=baseSql+levelSql; //System.out.println(sql); List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{tdata.getTest_starttime(),tdata.getTest_starttime_ex(),tdata.getUpload_usr_id(),"%"+tdata.getStationname4()+"%","%"+tdata.getStationname3()+"%","%"+tdata.getStationname2()+"%","%"+tdata.getStationname1()+"%"}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf tdata=new Batttestdata_inf(); tdata.setBattGroupId(rs.getInt("battGroupId")); list.add(tdata); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //6.4.4根据条件查询符合条件的测试完成的电池组(蓄电池容量预警) /* * data_available 中存放层次 * test_starttypeHex 中存放层次对应name名字*/ public List serchCap(Object obj){ Batttestdata_inf tdata=(Batttestdata_inf) obj; String sql=""; String baseSql="select distinct(tb_batttestdata_inf.battgroupid),avg(test_cap) AvgCap,group_concat(test_cap) " + "from db_batt_testdata.tb_batttestdata_inf where " + "(test_starttime)>=(?) and (test_starttime)<=(?) " + "and db_batt_testdata.tb_batttestdata_inf.data_available=1 "; //上传方式 String usrSqlT="and upload_usr_id!=? " ; String usrSqlU="and upload_usr_id!=0 and upload_usr_id!=? " ; String usrSqlF="and upload_usr_id=? " ; if(tdata.getUpload_usr_id()==100){//全部 baseSql+=usrSqlT; }else{ if(tdata.getUpload_usr_id()==0){ baseSql+=usrSqlF; }else{ baseSql+=usrSqlU; } } String levelSql=" and tb_batttestdata_inf.battgroupid in (select distinct(db_battinf.tb_battinf.BattGroupId) from db_battinf.tb_battinf where db_battinf.tb_battinf.stationname4 like ? and db_battinf.tb_battinf.stationname3 like ? and db_battinf.tb_battinf.stationname2 like ? and db_battinf.tb_battinf.stationname1 like ?) ";//分组层次1 sql=baseSql+levelSql; String endSql=" group by(tb_batttestdata_inf.battgroupid) "; sql=sql+=endSql; //System.out.println(sql); List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{tdata.getTest_starttime(),tdata.getTest_starttime_ex(),tdata.getUpload_usr_id(),"%"+tdata.getStationname4()+"%","%"+tdata.getStationname3()+"%","%"+tdata.getStationname2()+"%","%"+tdata.getStationname1()+"%"}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); int id=0; try { while(rs.next()){ Batttestdata_inf tdata=new Batttestdata_inf(); tdata.setBattGroupId(rs.getInt("battGroupId")); tdata.setTest_cap(rs.getFloat("AvgCap")); list.add(tdata); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //1.4电池组性能评估(根据电池组id查询所有的放电记录求出放电总次数，最高历史容量,最低历史容量，平均容量，最新测试容量) public List serchDischargeTest(Object obj){ final Batttestdata_inf tinf=(Batttestdata_inf) obj; String sql=""; String baseSql="select battgroupid,test_curr,max_monvol,min_monvol,test_cap " + " from db_batt_testdata.tb_batttestdata_inf " + " where battgroupid=? " + " and record_time>=? and record_time<=? " + " and db_batt_testdata.tb_batttestdata_inf.data_available=1" ; //充电放电选择 String test_typeA="";//全部 String test_typeR=" and test_type=2 ";//充电 //用于放电类型 //全部 String start_typeA=" and test_type=3 "; //停电放电 String start_typeF=" and (test_starttype<5 and test_type not in(2,9) and test_starttype!=2 AND test_starttype!=3) "; //假负载放电2 String start_typeT=" and (test_starttype=2 and test_type not in(2) ) "; //节能放电3 String start_typeH=" and (test_starttype=3 and test_type not in(2) ) "; if(tinf.getTest_type()==0){ baseSql+=test_typeA; }else if(tinf.getTest_type()==2){ baseSql+=test_typeR; }else if(tinf.getTest_type()==3){ if(tinf.getTest_starttype()==0){ baseSql+=start_typeA; }else if(tinf.getTest_starttype()==1){ baseSql+=start_typeF; }else if(tinf.getTest_starttype()==2){ baseSql+=start_typeT; }else if(tinf.getTest_starttype()==3){ baseSql+=start_typeH; } } //排序 String orderSql=" order by test_starttime desc "; sql=baseSql+orderSql; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{tinf.getBattGroupId(),tinf.getRecord_time(),tinf.getRecord_time1()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); int sum=0;//总测试次数 float cap=0f;//实际容量 float allCap=0f;//总容量 float maxCap=0f;//最高容量 float minCap=10000f;//最低容量 float avgCap=0f;//平均容量 float lastCap=0f;//最近测试容量 try { while(rs.next()){ Batttestdata_inf batt=new Batttestdata_inf(); batt.setBattGroupId(rs.getInt("battGroupId")); batt.setTest_curr(rs.getFloat("test_curr")); batt.setMax_monvol(rs.getFloat("max_monvol")); batt.setTest_cap(rs.getFloat("test_cap")); batt.setMin_monvol(rs.getFloat("min_monvol")); int hourRate=BattCapFactory.GetHourRate(tinf.getTest_cap(), batt.getTest_curr()); cap=(float) BattCapFactory.GetMonomerCap(tinf.getTest_cap(), hourRate,batt.getTest_cap(), batt.getMax_monvol(), batt.getMin_monvol(), tinf.getGroup_vol(), BattCapFactory.CapType_Real); if(sum==0){ lastCap=cap; } if(maxCap<=cap){ maxCap=cap;//最大 } if(minCap>=cap){ minCap=cap;//最小 } allCap+=cap; sum++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(sum!=0){ avgCap=allCap/sum;//平均容量 }else{ avgCap=0; minCap=0; } list.add(tinf.getBattGroupId()); list.add(sum); list.add(maxCap); list.add(avgCap); list.add(minCap); list.add(lastCap); return list; } }); return list; } //1.2电池组统计分析查询 public List serchByTestType(Object obj){ final Batttestdata_inf tinf=(Batttestdata_inf) obj; String sql=""; String baseSql="select battgroupid,test_curr,max_monvol,test_starttime,test_timelong,min_monvol,test_cap,min_monnum " + " from db_batt_testdata.tb_batttestdata_inf " + " where battgroupid=? and test_record_count=? " + " and record_time>=? and record_time<=? " + " and db_batt_testdata.tb_batttestdata_inf.data_available=1 " ; //充电放电选择 String test_typeA="";//全部 String test_typeR=" and test_type=2 ";//充电 //用于放电类型 //全部 String start_typeA=" and test_type=3 "; //停电放电 String start_typeF=" and (test_starttype<5 and test_type not in(2,9) and test_starttype!=2 AND test_starttype!=3) "; //假负载放电2 String start_typeT=" and (test_starttype=2 and test_type not in(2) ) "; //节能放电3 String start_typeH=" and (test_starttype=3 and test_type not in(2) ) "; if(tinf.getTest_type()==0){ baseSql+=test_typeA; }else if(tinf.getTest_type()==2){ baseSql+=test_typeR; }else if(tinf.getTest_type()==3){ if(tinf.getTest_starttype()==0){ baseSql+=start_typeA; }else if(tinf.getTest_starttype()==1){ baseSql+=start_typeF; }else if(tinf.getTest_starttype()==2){ baseSql+=start_typeT; }else if(tinf.getTest_starttype()==3){ baseSql+=start_typeH; } } //排序 String orderSql=" order by test_starttime desc "; sql=baseSql+orderSql; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{tinf.getBattGroupId(),tinf.getTest_record_count(),tinf.getRecord_time(),tinf.getRecord_time1()}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); float cap=0f;//实际容量 float restcap=0f;//剩余容量 try { while(rs.next()){ Batttestdata_inf batt=new Batttestdata_inf(); batt.setBattGroupId(rs.getInt("battGroupId")); batt.setTest_curr(rs.getFloat("test_curr")); batt.setMax_monvol(rs.getFloat("max_monvol")); batt.setTest_cap(rs.getFloat("test_cap")); batt.setMin_monvol(rs.getFloat("min_monvol")); batt.setTest_starttime(rs.getTimestamp("test_starttime")); int hourRate=BattCapFactory.GetHourRate(tinf.getTest_cap(), batt.getTest_curr()); cap=(float) BattCapFactory.GetMonomerCap(tinf.getTest_cap(), hourRate,batt.getTest_cap(), batt.getMax_monvol(), batt.getMin_monvol(), tinf.getGroup_vol(), BattCapFactory.CapType_Real); restcap=(float) BattCapFactory.GetMonomerCap(tinf.getTest_cap(), hourRate,batt.getTest_cap(), batt.getMax_monvol(), batt.getMin_monvol(), tinf.getGroup_vol(), BattCapFactory.CapType_Rest); batt.setTest_timelong(rs.getInt("test_timelong")); batt.setMin_monnum(rs.getInt("min_monnum")); batt.setGroup_vol(cap);//实际容量 batt.setMon_vol(restcap);//剩余容量 list.add(batt); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } //电池配组用到(筛选出有效的最近的一次节能放电技术) public List Batt_selecteffectiveBatt(Object obj,float param){ BattInf binf=(BattInf) obj; String sql="SELECT distinct battGroupId,test_starttime,record_time,test_cap,max_monvol,min_monvol,test_record_count,test_curr FROM db_batt_testdata.tb_batttestdata_inf " + " WHERE BattGroupId =? " + " AND data_available=1 AND data_new = 1 AND " + " test_type = 3 AND test_starttype = 3 AND " //判断是节能放电 + " abs(test_cap) >?*? " //判断测试数据容量是否有效 + " ORDER BY test_record_count DESC LIMIT 1 "; List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId(),binf.getMonCapStd(),param}, new CallBack() { public List getResults(ResultSet rs) { List list=new ArrayList(); try { while(rs.next()){ Batttestdata_inf tinf=new Batttestdata_inf(); tinf.setBattGroupId(rs.getInt("battGroupId")); if(rs.getString("test_starttime")!=null) tinf.setTest_starttime(rs.getTimestamp("test_starttime")); if(rs.getString("record_time")!=null) tinf.setRecord_time(rs.getTimestamp("record_time")); tinf.setTest_cap(rs.getFloat("test_cap")); tinf.setMax_monvol(rs.getFloat("max_monvol")); tinf.setMin_monvol(rs.getFloat("min_monvol")); tinf.setTest_record_count(rs.getInt("test_record_count")); tinf.setTest_curr(rs.getFloat("test_curr")); list.add(tinf); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } }); return list; } public static void main(String[] args) throws ParseException { Batttestdata_infDAOImpl bimpl=new Batttestdata_infDAOImpl(); Batttestdata_inf tdata=new Batttestdata_inf(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); Date date1= sdf.parse("2015-09-07"); Date date2= sdf.parse("2016-09-07"); tdata.setNum(100); tdata.setTest_starttype(2); tdata.setTest_starttime(ActionUtil.getSimpDate(date1)); tdata.setTest_starttime_ex(ActionUtil.getSimpDate(date2)); BattInf binf=new BattInf(); binf.setStationName1("二枢纽"); Batt_Maint_Dealarm bmd=new Batt_Maint_Dealarm(); bmd.setTdata(tdata); bmd.setBinf(binf); List list=bimpl.serchByInfo(bmd); for (Batt_Maint_Dealarm b : list) { System.out.println(b); } System.out.println(list.size()); } public List getResults(ResultSet rs) { // TODO Auto-generated method stub return null; } }