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_endurance;
|
import com.fgkj.dto.Batt_rtdata;
|
import com.fgkj.dto.Batt_rtstate;
|
import com.fgkj.dto.Batttestdata_inf;
|
import com.fgkj.dto.Page;
|
|
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 db_batt_testdata.tb_batttestdata_inf.num,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"
|
+ ",MonCapStd,MonvolStd " +
|
" from db_batt_testdata.tb_batttestdata_inf,db_battinf.tb_battinf where "
|
+ " db_battinf.tb_battinf.BattGroupId=db_batt_testdata.tb_batttestdata_inf.BattGroupId and db_batt_testdata.tb_batttestdata_inf.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"));
|
b.setMoncapstd(rs.getFloat("MonCapStd"));
|
b.setMonvolstd(rs.getFloat("monvolstd"));
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
//历史数据查询<-------跨域----------->
|
public List serchByCondition_ky(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.stationname2,db_battinf.tb_battinf.stationname5,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 ? and db_battinf.tb_battinf.stationname5 like ? ";
|
baseSql+=stationSql;
|
//1U/2U设备筛选
|
String monvolSqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//全部
|
String monvolSqlF=" and db_battinf.tb_battinf.monvolstd=? ";
|
if(binf.getMonVolStd()==0) {
|
baseSql+=monvolSqlT;
|
}else {
|
baseSql+=monvolSqlF;
|
}
|
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()+"%","%"+binf.getStationName5()+"%",binf.getMonVolStd()
|
}, 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.setStationName2(rs.getString("stationName2"));
|
binf.setStationName5(rs.getString("stationName5"));
|
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 serchBattLife(Object obj){
|
Batt_endurance bendurance=(Batt_endurance) obj;
|
Page page=bendurance.getPage();
|
Connection conn=DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="select SQL_CALC_FOUND_ROWS distinct tb_batt_endurance.num,tb_batt_endurance.deviceid,tb_batt_endurance.stationid,real_cap,tb_batt_endurance.moncapstd,real_curr,endurance_theory_timelong,endurance_actual_timelong,endurance_actual_timelong_max"
|
+ ",endurance_actual_timelong_min,is_out_stand,batts_moncapstd,batts_teststarttime,real_cap_group1,real_cap_group2,real_cap_group3,real_cap_group4,groupcount " +
|
",db_battinf.tb_battinf.StationName,db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName2,db_battinf.tb_battinf.StationName5 " +
|
" from web_site.tb_batt_endurance,db_battinf.tb_battinf " +
|
" where tb_batt_endurance.stationid=db_battinf.tb_battinf.stationid "+
|
" and tb_batt_endurance.stationid in (" +
|
" select distinct db_battinf.tb_battinf.stationid " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? and db_battinf.tb_battinf.station_install=1 ) " ;
|
//机房筛选
|
String stationSql=" and db_battinf.tb_battinf.StationName like ? and db_battinf.tb_battinf.StationName1 like ? and db_battinf.tb_battinf.StationName2 like ? and db_battinf.tb_battinf.StationName5 like ? ";
|
sql+=stationSql;
|
//1U/2U设备筛选
|
String monvolSqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//全部
|
String monvolSqlF=" and db_battinf.tb_battinf.monvolstd=? ";
|
if(bendurance.getMonvolstd()==0) {
|
sql+=monvolSqlT;
|
}else {
|
sql+=monvolSqlF;
|
}
|
//排序时间
|
String orderSql=" order by endurance_actual_timelong,tb_batt_endurance.deviceid asc ";
|
sql+=orderSql;
|
//分页
|
String limitSql=" limit ?,? ";
|
sql+=limitSql;
|
|
|
//System.out.println(sql);
|
List<Batt_endurance> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{bendurance.getNum(),"%"+bendurance.getStationName()+"%","%"+bendurance.getStationName1()+"%","%"+bendurance.getStationName2()+"%","%"+bendurance.getStationName5()+"%",bendurance.getMonvolstd()
|
,(page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_endurance b=new Batt_endurance();
|
b.setNum(rs.getInt("num"));
|
b.setDeviceId(rs.getInt("deviceId"));
|
b.setStationid(rs.getString("stationid"));
|
b.setReal_cap(rs.getFloat("real_cap"));
|
b.setMoncapstd(rs.getFloat("moncapstd"));
|
b.setReal_curr(rs.getFloat("real_curr"));
|
b.setBatts_moncapstd(rs.getString("batts_moncapstd"));
|
b.setBatts_teststarttime(rs.getString("batts_teststarttime"));
|
b.setReal_cap_group1(rs.getFloat("real_cap_group1"));
|
b.setReal_cap_group2(rs.getFloat("real_cap_group2"));
|
b.setReal_cap_group3(rs.getFloat("real_cap_group3"));
|
b.setReal_cap_group4(rs.getFloat("real_cap_group4"));
|
b.setGroupcount(rs.getInt("groupcount"));
|
b.setEndurance_theory_timelong(rs.getFloat("endurance_theory_timelong"));
|
b.setEndurance_actual_timelong(rs.getFloat("endurance_actual_timelong"));
|
b.setEndurance_actual_timelong_max(rs.getFloat("endurance_actual_timelong_max"));
|
b.setEndurance_actual_timelong_min(rs.getFloat("endurance_actual_timelong_min"));
|
b.setIs_out_stand(rs.getInt("is_out_stand"));
|
b.setStationName(rs.getString("stationname"));
|
b.setStationName1(rs.getString("stationname1"));
|
b.setStationName2(rs.getString("stationname2"));
|
b.setStationName5(rs.getString("stationname5"));
|
Page p=new Page();
|
b.setPage(p);
|
list.add(b);
|
}
|
} 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).getPage().setPageAll(number);
|
}
|
return list;
|
}
|
|
//机房历史放电数据续航能力查询(实时)
|
/*public List serchBattLifeNow(Object obj){
|
BattInf binf=(BattInf) obj;
|
Page p=binf.getPage();
|
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.stationname2,db_battinf.tb_battinf.stationname5,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 ? AND db_battinf.tb_battinf.stationname5 like ? and db_battinf.tb_battinf.station_install=1";
|
baseSql+=stationSql;
|
//1U/2U设备筛选
|
String monvolSqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//全部
|
String monvolSqlF=" and db_battinf.tb_battinf.monvolstd=? ";
|
if(binf.getMonVolStd()==0) {
|
baseSql+=monvolSqlT;
|
}else {
|
baseSql+=monvolSqlF;
|
}
|
//电池状态
|
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,db_battinf.tb_battinf.BattGroupId asc ";
|
//分页
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+orderSql+limitSql;
|
//System.out.println(sql);
|
List<Batt_State> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%",binf.getMonVolStd()
|
,(p.getPageCurr()-1)*p.getPageSize(),p.getPageSize()}, 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.setStationName2(rs.getString("stationName2"));
|
binf.setStationName5(rs.getString("stationName5"));
|
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 serchBattLifeNow(Object obj){
|
BattInf binf=(BattInf) obj;
|
Page p=binf.getPage();
|
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" +
|
",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.stationname2,db_battinf.tb_battinf.stationname5,db_battinf.tb_battinf.BattGroupName,db_battinf.tb_battinf.deviceName " +
|
"FROM db_ram_db.tb_batt_rtstate,db_battinf.tb_battinf " +
|
"where tb_batt_rtstate.BattGroupId = db_battinf.tb_battinf.BattGroupId "
|
+ " and db_battinf.tb_battinf.BattGroupId in (" +
|
" select distinct db_battinf.tb_battinf.BattGroupId " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? and db_battinf.tb_battinf.station_install=1 ) " ;
|
//机房维护区选择
|
String stationSql=" and db_battinf.tb_battinf.stationname like ? AND db_battinf.tb_battinf.stationname1 like ? AND db_battinf.tb_battinf.stationname2 like ? AND db_battinf.tb_battinf.stationname5 like ? ";
|
baseSql+=stationSql;
|
//1U/2U设备筛选
|
String monvolSqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//全部
|
String monvolSqlF=" and db_battinf.tb_battinf.monvolstd=? ";
|
if(binf.getMonVolStd()==0) {
|
baseSql+=monvolSqlT;
|
}else {
|
baseSql+=monvolSqlF;
|
}
|
|
//排序
|
String orderSql=" order by db_battinf.tb_battinf.stationid asc,db_battinf.tb_battinf.BattGroupId asc ";
|
//分页
|
String limitSql=" limit ?,? ";
|
sql=baseSql+orderSql+limitSql;
|
//System.out.println(sql);
|
List<Batt_State> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{binf.getNum(),"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%",binf.getMonVolStd()
|
,(p.getPageCurr()-1)*p.getPageSize(),p.getPageSize()}, 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.setGroup_curr(rs.getFloat("group_curr"));
|
bstate.setOnline_vol(rs.getFloat("online_vol"));
|
bstate.setGroup_vol(rs.getFloat("group_vol"));
|
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"));
|
|
|
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.setStationName2(rs.getString("stationName2"));
|
binf.setStationName5(rs.getString("stationName5"));
|
binf.setStationName9(BattTestData.battState(rs.getInt("batt_state")));
|
|
state.setBstate(bstate);
|
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<Batttestdata_inf> 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<Batttestdata_inf> list=new ArrayList<Batttestdata_inf>();
|
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;
|
}
|
|
//查询最近一次监测放电的电池组放电记录(10分钟)
|
public List Monitoring_Discharge(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select distinct db_batt_testdata.tb_batttestdata_inf.BattGroupId,GROUP_CONCAT(test_record_count ORDER BY test_record_count asc) as all_test_record_count "
|
+ " from db_batt_testdata.tb_batttestdata_inf,db_battinf.tb_battinf "
|
+ " where test_type=3 and test_starttype!=3 "
|
+ " and db_batt_testdata.tb_batttestdata_inf.BattGroupId=db_battinf.tb_battinf.BattGroupId "
|
+ " and test_timelong>=600 "
|
+ " and stationname1 like ? and stationname2 like ? and stationname5 like ? and stationid like ? "
|
+ " group by db_batt_testdata.tb_batttestdata_inf.BattGroupId ";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%","%"+binf.getStationId()+"%"}, new CallBack() {
|
|
@Override
|
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.setNote(rs.getString("all_test_record_count"));
|
list.add(batt);
|
}
|
} 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("2000-09-07");
|
Date date2= sdf.parse("2020-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.setNum(1002);
|
binf.setStationName("");
|
binf.setStationName1("广西省");
|
binf.setStationName2("钦州");
|
binf.setStationName5("");
|
binf.setStationId("");
|
binf.setMonVolStd(0f);
|
Page page=new Page();
|
page.setPageCurr(1);
|
page.setPageSize(10);
|
binf.setPage(page);
|
binf.setBattProductDate(ActionUtil.getSimpDate(date1));
|
binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
|
Batt_Maint_Dealarm bmd=new Batt_Maint_Dealarm();
|
bmd.setTdata(tdata);
|
bmd.setBinf(binf);
|
/*List<Batt_Maint_Dealarm> list=bimpl.serchByInfo(bmd);
|
for (Batt_Maint_Dealarm b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
*/
|
/*Batttestdata_inf tinf=new Batttestdata_inf();
|
tinf.setBattGroupId(1002095);
|
tinf.setNum(910000577);
|
List<Batttestdata_inf> list=bimpl.serchByCondition(tinf);
|
for (Batttestdata_inf b : list) {
|
System.out.println(b);
|
}*/
|
List<Batttestdata_inf> list=bimpl.Monitoring_Discharge(binf);
|
for (Batttestdata_inf b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
/*Batt_endurance ben=new Batt_endurance();
|
ben.setNum(1002);
|
ben.setStationName("");
|
ben.setStationName1("");
|
ben.setStationName2("");
|
ben.setStationName5("");
|
ben.setEndurance_actual_timelong(0);
|
ben.setMonvolstd(0);
|
ben.setIs_out_stand(100);
|
Page page=new Page();
|
page.setPageCurr(1);
|
page.setPageSize(10);
|
ben.setPage(page);
|
List<Batt_endurance> list=bimpl.serchBattLife(ben);
|
System.out.println(list.size());
|
for (Batt_endurance b : list) {
|
System.out.println(b);
|
}*/
|
/*List<Batt_State> list=bimpl.serchBattLifeNow(binf);
|
System.out.println(list.size());
|
for (Batt_State b : list) {
|
System.out.println(b);
|
}*/
|
}
|
|
public List getResults(ResultSet rs) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
|
}
|
