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.List;
|
|
import com.fgkj.dao.BaseDAO;
|
import com.fgkj.dao.BattTestData;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.LimitNumberFactory;
|
import com.fgkj.dao.UinfDaoFactory;
|
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.Page;
|
import com.fgkj.dto.User_inf;
|
|
public class Batt_rtstateDAOImpl implements BaseDAO{
|
private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
|
|
public boolean add(Object obj) {
|
return false;
|
}
|
|
public boolean update(Object obj) {
|
return false;
|
}
|
|
public boolean del(Object obj) {
|
return false;
|
}
|
|
public List searchAll() {
|
return null;
|
}
|
//实时监测
|
public List serchByCondition(Object obj) {
|
Batt_rtstate br=(Batt_rtstate)obj;
|
String sql="select num,BattGroupId,batt_count,rec_datetime,group_vol,online_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" +
|
" from db_ram_db.tb_batt_rtstate where BattGroupId=?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{br.getBattGroupId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_rtstate b=new Batt_rtstate();
|
b.setNum(rs.getInt("num"));
|
b.setBattGroupId(rs.getInt("BattGroupId"));
|
b.setBatt_count(rs.getInt("batt_count"));
|
b.setRec_datetime(rs.getTimestamp("rec_datetime"));
|
b.setOnline_vol(rs.getFloat("online_vol"));
|
b.setGroup_vol(rs.getFloat("group_vol"));
|
b.setGroup_curr(rs.getFloat("group_curr"));
|
b.setBatt_state(rs.getInt("batt_state"));
|
b.setBatt_test_type(rs.getInt("batt_test_type"));
|
b.setBatt_test_starttime(rs.getTimestamp("batt_test_starttime"));
|
b.setBatt_test_recordtime(rs.getTimestamp("batt_test_recordtime"));
|
b.setBatt_test_tlong(rs.getInt("batt_test_tlong"));
|
b.setBatt_test_cap(rs.getFloat("batt_test_cap"));
|
b.setBatt_real_cap(rs.getFloat("batt_real_cap"));
|
b.setBatt_rest_cap(rs.getFloat("batt_rest_cap"));
|
b.setBatt_rest_power1_time(rs.getInt("batt_rest_power1_time"));
|
b.setBatt_rest_power2_time(rs.getInt("batt_rest_power2_time"));
|
//System.out.println(b);
|
list.add(b);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
//实时监测<-------跨域----------->
|
public List serchByCondition_ky(Object obj) {
|
Batt_rtstate br=(Batt_rtstate)obj;
|
String sql="select num,BattGroupId,batt_count,rec_datetime,group_vol,online_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" +
|
" from db_ram_db.tb_batt_rtstate where BattGroupId=?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{br.getBattGroupId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_rtstate b=new Batt_rtstate();
|
b.setNum(rs.getInt("num"));
|
b.setBattGroupId(rs.getInt("BattGroupId"));
|
b.setBatt_count(rs.getInt("batt_count"));
|
b.setRec_datetime(rs.getTimestamp("rec_datetime"));
|
b.setOnline_vol(rs.getFloat("online_vol"));
|
b.setGroup_vol(rs.getFloat("group_vol"));
|
b.setGroup_curr(rs.getFloat("group_curr"));
|
b.setBatt_state(rs.getInt("batt_state"));
|
b.setBatt_test_type(rs.getInt("batt_test_type"));
|
b.setBatt_test_starttime(rs.getTimestamp("batt_test_starttime"));
|
b.setBatt_test_recordtime(rs.getTimestamp("batt_test_recordtime"));
|
b.setBatt_test_tlong(rs.getInt("batt_test_tlong"));
|
b.setBatt_test_cap(rs.getFloat("batt_test_cap"));
|
b.setBatt_real_cap(rs.getFloat("batt_real_cap"));
|
b.setBatt_rest_cap(rs.getFloat("batt_rest_cap"));
|
b.setBatt_rest_power1_time(rs.getInt("batt_rest_power1_time"));
|
b.setBatt_rest_power2_time(rs.getInt("batt_rest_power2_time"));
|
//System.out.println(b);
|
list.add(b);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
//测试数据——实时查询中左下角的充放电统计(旧)
|
public List serchDisOrChargr(){
|
String sql=" SELECT db_ram_db.tb_batt_rtstate.battgroupid,batt_test_type,stationid " +
|
" from db_ram_db.tb_batt_rtstate,db_battinf.tb_battinf " +
|
" WHERE batt_test_type IN(2,3) " +
|
" and db_ram_db.tb_batt_rtstate.battgroupid=db_battinf.tb_battinf.BattGroupId " +
|
" ORDER BY db_ram_db.tb_batt_rtstate.battgroupid";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<Batt_rtstate> list=new ArrayList();
|
int charge=0;
|
int dischar=0;
|
try {
|
while(rs.next()){
|
Batt_rtstate bstate=new Batt_rtstate();
|
bstate.setBattGroupId(rs.getInt("battGroupId"));
|
bstate.setBatt_test_type(rs.getInt("batt_test_type"));
|
bstate.setNote(rs.getString("stationid"));
|
if(rs.getInt("batt_test_type")==3){
|
dischar++;
|
}else if(rs.getInt("batt_test_type")==2){
|
charge++;
|
}
|
list.add(bstate);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).setNum(charge);
|
list.get(list.size()-1).setBatt_count(dischar);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//测试数据——实时查询中左下角的充放电统计(用户管理的机房)
|
public List serchDisOrChargrNew(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql=" SELECT distinct db_ram_db.tb_batt_rtstate.battgroupid,batt_test_type,stationid,fbsdeviceid,stationName,stationName1,stationName2,stationName3,stationName5 " +
|
" from db_ram_db.tb_batt_rtstate,db_battinf.tb_battinf " +
|
" where batt_test_type IN(2,3) " +
|
" and db_ram_db.tb_batt_rtstate.battgroupid=db_battinf.tb_battinf.BattGroupId " +
|
" and db_ram_db.tb_batt_rtstate.BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? ) " +
|
" ORDER BY db_ram_db.tb_batt_rtstate.battgroupid";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<Batt_rtstate> list=new ArrayList();
|
int charge=0;
|
int dischar=0;
|
try {
|
while(rs.next()){
|
Batt_rtstate bstate=new Batt_rtstate();
|
bstate.setBattGroupId(rs.getInt("battGroupId"));
|
bstate.setBatt_test_type(rs.getInt("batt_test_type"));
|
bstate.setNote(rs.getString("stationid"));
|
bstate.setBatt_test_tlong(rs.getInt("fbsdeviceid"));
|
bstate.setStationName(rs.getString("stationName"));
|
bstate.setStationName1(rs.getString("stationName1"));
|
bstate.setStationName2(rs.getString("stationName2"));
|
bstate.setStationName3(rs.getString("stationName3"));
|
bstate.setStationName5(rs.getString("stationName5"));
|
if(rs.getInt("batt_test_type")==3){
|
dischar++;
|
}else if(rs.getInt("batt_test_type")==2){
|
charge++;
|
}
|
list.add(bstate);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).setNum(charge);
|
list.get(list.size()-1).setBatt_count(dischar);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//根据电池组id查询该电池组(电池续航能力)
|
public List serchByInfo(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql="select tb_batt_rtstate.BattGroupId,batt_count,tb_batt_rtstate.rec_datetime,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" +
|
",max(binary tb_batt_rtdata.mon_vol) as maxmonvol,min(binary tb_batt_rtdata.mon_vol) as minmonvol" +
|
",BINARY(curr1+curr2+curr3) AS curr " +
|
",db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.moncapstd,db_battinf.tb_battinf.monvolstd " +
|
"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 tb_batt_rtstate.BattGroupId=? " +
|
"ORDER BY mon_num asc ";
|
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()){
|
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_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.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("MonVolStd"));
|
|
state.setBstate(bstate);
|
state.setBdata(bdata);
|
state.setBinf(binf);
|
list.add(state);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//查询单体编号实时数据(电池单体实时信息)
|
public List serchMons(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select tb_batt_rtstate.BattGroupId,batt_count,tb_batt_rtstate.rec_datetime,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 " +
|
",db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.moncapstd,db_battinf.tb_battinf.monvolstd " +
|
"FROM db_ram_db.tb_batt_rtstate,db_ram_db.tb_batt_rtdata,db_battinf.tb_battinf " +
|
"where tb_batt_rtstate.BattGroupId = tb_batt_rtdata.BattGroupId " +
|
"and tb_batt_rtstate.BattGroupId = db_battinf.tb_battinf.BattGroupId " +
|
"and tb_batt_rtdata.BattGroupId=? and tb_batt_rtdata.mon_num=? " +
|
"ORDER BY mon_num asc ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId(),binf.getMonNum()}, 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_vol(rs.getFloat("group_vol"));
|
bstate.setGroup_curr(rs.getFloat("group_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.setBattGroupId(rs.getInt("tb_batt_rtdata.BattGroupId"));
|
bdata.setRec_datetime(rs.getDate("tb_batt_rtdata.rec_datetime"));
|
bdata.setMon_num(rs.getInt("mon_num"));
|
bdata.setMon_vol(rs.getFloat("mon_vol"));
|
bdata.setMon_tmp(rs.getFloat("mon_tmp"));
|
bdata.setMon_res(rs.getFloat("mon_res"));
|
bdata.setMon_ser(rs.getFloat("mon_ser"));
|
bdata.setMon_conn_res(rs.getFloat("mon_conn_res"));
|
bdata.setMon_cap(rs.getFloat("mon_cap"));
|
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monvolstd"));
|
state.setBstate(bstate);
|
state.setBdata(bdata);
|
state.setBinf(binf);
|
list.add(state);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}
|
//查询电池组的实时状态（电池状态查询<放电，充电>）
|
/*
|
* 电池状态放在：binf的num中*/
|
/*public List serchBattState(Object obj){
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Page page=bmd.getPage();
|
String sql="";
|
String numberSql=" SELECT FOUND_ROWS() number";
|
Connection conn=DBUtil.getConn(IDatabaseName.DB_RAM_DB);
|
String baseSql="SELECT SQL_CALC_FOUND_ROWS DISTINCT db_battinf.tb_battinf.BattGroupId, rec_datetime,batt_state" +
|
",db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName,db_battinf.tb_battinf.BattGroupName,db_battinf.tb_battinf.deviceName " +
|
" FROM tb_batt_rtstate,db_battinf.tb_battinf " +
|
" WHERE tb_batt_rtstate.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" AND db_battinf.tb_battinf.StationName LIKE ? " +
|
" AND db_battinf.tb_battinf.StationName1 LIKE ? ";
|
//选取电池组
|
String idSqlT=" AND db_battinf.tb_battinf.BattGroupId=? ";
|
String idSqlF=" AND db_battinf.tb_battinf.BattGroupId!=? ";
|
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlF;
|
}else{
|
baseSql+=idSqlT;
|
}
|
//电池状态
|
String stateSqlT=" AND batt_state=? ";
|
String stateSqlF=" AND batt_state!=? ";
|
if(binf.getNum()==100){
|
baseSql+=stateSqlF;
|
}else{
|
baseSql+=stateSqlT;
|
}
|
|
//按照电池组升序排序
|
String endSql=" ORDER BY db_battinf.tb_battinf.BattGroupId ASC ";
|
//分页
|
String limitSql=" limit ?,? ";
|
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
List<BattInf> list = DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%",binf.getBattGroupId(),binf.getNum()
|
,(page.getPageCurr() - 1)*page.getPageSize(),
|
page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
//电池组状态
|
binf.setNum(rs.getInt("batt_state"));
|
binf.setStationName9(BattTestData.battState(rs.getInt("batt_state")));
|
binf.setBattProductDate(rs.getTimestamp("rec_datetime"));
|
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
|
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).setMonNum(number);
|
}
|
return list;
|
}*/
|
//首页上饼状图电池状态比例
|
/*public List serchBattStateRate(){
|
String sql=" SELECT DISTINCT tb_batt_rtstate.BattGroupId,batt_state, " +
|
" COUNT(tb_batt_rtstate.BattGroupId) as stateNum " +
|
" FROM db_ram_db.tb_batt_rtstate " +
|
" GROUP BY batt_state ORDER BY batt_state";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_rtstate bstate=new Batt_rtstate();
|
bstate.setBatt_state(rs.getInt("batt_state"));
|
bstate.setNum(rs.getInt("stateNum"));
|
list.add(bstate);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}*/
|
public List serchBattStateRate(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql=" SELECT DISTINCT tb_batt_rtstate.BattGroupId,batt_state, " +
|
" COUNT(tb_batt_rtstate.BattGroupId) as stateNum " +
|
" FROM db_ram_db.tb_batt_rtstate " +
|
" GROUP BY tb_batt_rtstate.BattGroupId,batt_state " +
|
" having db_ram_db.tb_batt_rtstate.BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )" +
|
" ORDER BY batt_state";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int test_plan0=0;//状态为无
|
int test_plan1=0;//状态为浮充
|
int test_plan2=0;//状态为充电
|
int test_plan3=0;//状态为放电
|
int test_plan4=0;//状态为均充
|
try {
|
while(rs.next()){
|
if(rs.getInt("batt_state")==BattTestData.test_plan0){
|
test_plan0+=rs.getInt("stateNum");
|
}else if(rs.getInt("batt_state")==BattTestData.test_plan1){
|
test_plan1+=rs.getInt("stateNum");
|
}else if(rs.getInt("batt_state")==BattTestData.test_plan2){
|
test_plan2+=rs.getInt("stateNum");
|
}else if(rs.getInt("batt_state")==BattTestData.test_plan3){
|
test_plan3+=rs.getInt("stateNum");
|
}else if(rs.getInt("batt_state")==BattTestData.test_plan4){
|
test_plan4+=rs.getInt("stateNum");
|
}
|
if(rs.isLast()){
|
Batt_rtstate bstate=new Batt_rtstate();
|
bstate.setBatt_state(BattTestData.test_plan0);
|
bstate.setNum(test_plan0);
|
list.add(bstate);
|
Batt_rtstate bstate1=new Batt_rtstate();
|
bstate1.setBatt_state(BattTestData.test_plan1);
|
bstate1.setNum(test_plan1);
|
list.add(bstate1);
|
Batt_rtstate bstate2=new Batt_rtstate();
|
bstate2.setBatt_state(BattTestData.test_plan2);
|
bstate2.setNum(test_plan2);
|
list.add(bstate2);
|
Batt_rtstate bstate3=new Batt_rtstate();
|
bstate3.setBatt_state(BattTestData.test_plan3);
|
bstate3.setNum(test_plan3);
|
list.add(bstate3);
|
Batt_rtstate bstate4=new Batt_rtstate();
|
bstate4.setBatt_state(BattTestData.test_plan4);
|
bstate4.setNum(test_plan4);
|
list.add(bstate4);
|
}
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}
|
//电池组实时情况+实时电池续航能力查询
|
/*public List serchBattLife(Object obj) {
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Page page=bmd.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" +
|
",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 " +
|
"WHERE ";
|
//电池组的选择
|
String idSqlT=" tb_batt_rtstate.BattGroupId=? ";
|
String idSqlF=" tb_batt_rtstate.BattGroupId!=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlF;
|
}else{
|
baseSql+=idSqlT;
|
}
|
//机房维护区选择
|
String stationSql=" AND db_battinf.tb_battinf.stationname like ? AND db_battinf.tb_battinf.stationname1 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 limitSql=" limit ?,?";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
List<Batt_State> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{binf.getBattGroupId(),"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%"
|
,binf.getNum(),(page.getPageCurr() - 1)*page.getPageSize(),
|
page.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("group_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.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 serchBattLife(Object obj) {
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
User_inf uinf=bmd.getUinf();
|
Page page=bmd.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" +
|
",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 " +
|
"WHERE ";
|
//电池组的选择
|
String idSqlT=" tb_batt_rtstate.BattGroupId=? ";
|
String idSqlF=" tb_batt_rtstate.BattGroupId!=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlF;
|
}else{
|
baseSql+=idSqlT;
|
}
|
//用于用户管理的电池组的筛选
|
String userSql=" and tb_batt_rtstate.BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )";
|
baseSql+=userSql;
|
//机房维护区选择
|
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;
|
//电池状态
|
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 limitSql=" limit ?,?";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
List<Batt_State> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{binf.getBattGroupId(),uinf.getUId(),"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%"
|
,binf.getNum(),(page.getPageCurr() - 1)*page.getPageSize(),
|
page.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("group_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.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);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).getBinf().setMonNum(number);
|
}
|
return list;
|
}
|
|
//查询出当前存在内存中最大的电池组id(很重要**********)
|
public int searchMaxBattgroupId(){
|
String sql=" select max(battgroupId) as id from db_ram_db.tb_batt_rtstate limit 1";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
int battgroupid=1010000;
|
if(rs.getString("id")!=null){
|
battgroupid=rs.getInt("id");
|
}
|
list.add(battgroupid);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int battgroupid=0;
|
if(list!=null&&list.size()>0){
|
battgroupid=(Integer) list.get(list.size()-1);
|
}
|
return battgroupid;
|
}
|
public static void main(String[] args) {
|
User_inf uinf=new User_inf();
|
uinf.setUId(1001);
|
Batt_rtstateDAOImpl bimpl=new Batt_rtstateDAOImpl();
|
/*List<Batt_rtstate> list=bimpl.serchDisOrChargrNew(uinf);
|
for (Batt_rtstate b : list) {
|
System.out.println(b);
|
}*/
|
Batt_rtstate br=new Batt_rtstate();
|
br.setBattGroupId(1002101);
|
/*List<Batt_rtstate> list=bimpl.serchByCondition_ky(br);
|
for (Batt_rtstate b : list) {
|
System.out.println(b);
|
}*/
|
int battgroupid=bimpl.searchMaxBattgroupId();
|
System.out.println(battgroupid);
|
}
|
}
|
