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 java.util.Locale;
|
|
import javax.persistence.Id;
|
import javax.sql.ConnectionEvent;
|
|
import sun.security.action.GetBooleanAction;
|
|
import com.fgkj.actions.ActionUtil;
|
import com.fgkj.dao.BaseDAO;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.FBS9100_ComBase;
|
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.BattMap_information;
|
import com.fgkj.dto.Batt_Maint_Dealarm;
|
import com.fgkj.dto.Batt_maint_inf;
|
import com.fgkj.dto.Batt_maintenance_inf;
|
import com.fgkj.dto.Batttestdata_inf;
|
import com.fgkj.dto.Batttestdatastop;
|
import com.fgkj.dto.Limit;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.RoleLimit;
|
import com.fgkj.dto.Roles;
|
import com.fgkj.dto.User_inf;
|
import com.fgkj.dto.User_log;
|
import com.fgkj.dto.User_task_batt_template;
|
import com.fgkj.dto.ram.Fbs9100_setparam;
|
import com.fgkj.services.User_logService;
|
|
public class BattInfImpl implements BaseDAO, CallBack {
|
//添加新的电池组(多个)
|
public boolean add(Object obj) {
|
List<BattInf> list=(List<BattInf>) obj;
|
ArrayList<String> sql_str=new ArrayList();
|
List<User_log> listU=new ArrayList<User_log>();//存放user_log
|
//获取当前最大的stationid和battgroupid
|
int maxs=42000000;//最大stationid
|
int maxb=1000001;//最大battgroupid
|
List maxList=(new BattInfImpl()).searchMaxId();
|
if(maxList!=null&&maxList.size()>0){
|
maxs=(Integer) maxList.get(0)+1;
|
maxb=(Integer) maxList.get(1)+1;
|
}
|
if(list!=null && list.size()>0){
|
for (int i = 0; i < list.size(); i++) {
|
BattInf binf=list.get(i);
|
//验证新添加的机房是否存在
|
List listS=(new BattInfImpl()).judgeBatt(binf);
|
if(listS!=null&&listS.size()>0){
|
binf.setStationId((String) listS.get(listS.size()-1));
|
}else{
|
binf.setStationId(String.valueOf(maxs));
|
}
|
binf.setBattGroupId(maxb);
|
String sql = "insert into db_battinf.tb_battinf(" +
|
"StationId,StationName,StationName1,StationName2,StationName3,StationName4,StationName5,StationIp,FBSDeviceId,FbsDeviceIp,FbsDeviceIp_YM,FbsDeviceIp_WG,"
|
+ "FBSDeviceName,GroupIndexInFBSDevice,battgroupId,BattGroupNum,BattGroupName,FloatVolLevel,OfflineVolLevel,BattFloatCurrent" +
|
",MonCount,MonCapStd,monVolStd,monResStd,MonSerStd,MonVolLowToAvg,BattProducer,BattModel,BattProductDate,BattInUseDate,Load_curr,DisCurrMax,station_phone "
|
+ ",Vol_grade,Manufacturers,Assetequipment,Deviceclasspath,Deviceoperationdepartment,PropertyRightUnit,PropertyAttribute,FactoryNumber,OperationTeam "
|
+ ",DeviceIdentityCode,ProjectType,BatteryApplicationType,SingleRatedVoltage,RemoteMonitor,BatttIndependentPlaced,JunChargeVoltageValue " +
|
") values('"+binf.getStationId()+"','"+binf.getStationName()+"','"+binf.getStationName1()+"','"+binf.getStationName2()+"','"+binf.getStationName3()+"','"+binf.getStationName4()+
|
"','"+binf.getStationName5()+"','"+binf.getStationIp()+"',"+binf.getFBSDeviceId()+",'"+binf.getFbsDeviceIp()+"','"+binf.getFbsDeviceIp_YM()+"','"+binf.getFbsDeviceIp_WG()+"','"+binf.getFBSDeviceName()+"',"+binf.getGroupIndexInFBSDevice()+
|
","+binf.getBattGroupId()+","+binf.getBattGroupNum()+",'"+binf.getBattGroupName()+"',"+binf.getFloatVolLevel()+","+binf.getOfflineVolLevel()+","+binf.getBattFloatCurrent()+
|
","+binf.getMonCount()+","+binf.getMonCapStd()+","+binf.getMonVolStd()+","+binf.getMonResStd()+","+binf.getMonSerStd()+","+binf.getMonVolLowToAvg()+
|
",'"+binf.getBattProducer()+"','"+binf.getBattModel()+"','"+DAOHelper.sdf.format(binf.getBattProductDate())+"','"+DAOHelper.sdf.format(binf.getBattInUseDate())+"',"+binf.getLoad_curr()+","+binf.getDisCurrMax()+",'"+binf.getStation_phone()+
|
"',"+binf.getVol_grade()+",'"+binf.getManufacturers()+"','"+binf.getAssetequipment()+"','"+binf.getDeviceclasspath()+"','"+binf.getDeviceoperationdepartment()+"','"+binf.getPropertyRightUnit()+"','"+binf.getPropertyAttribute()+
|
"','"+binf.getFactoryNumber()+"','"+binf.getOperationTeam()+"','"+binf.getDeviceIdentityCode()+"','"+binf.getProjectType()+"','"+binf.getBatteryApplicationType()+"',"+binf.getSingleRatedVoltage()+",'"+binf.getRemoteMonitor()+
|
"','"+binf.getBatttIndependentPlaced()+"',"+binf.getJunChargeVoltageValue()+")";
|
|
if(binf.getStationId().equals(maxs)){
|
maxs++;
|
}
|
maxb++;
|
sql_str.add(sql);
|
|
String baoji_sql_main="REPLACE into db_user.tb_user_battgroup_baojigroup_battgroup(baoji_group_id,StationId,BattGroupId) values('100011','"+binf.getStationId()+"',"+binf.getBattGroupId()+")";
|
sql_str.add(baoji_sql_main);
|
|
// System.out.println(baoji_sql_main);
|
String baoji_sql="REPLACE into db_user.tb_user_battgroup_baojigroup_battgroup(baoji_group_id,StationId,BattGroupId) " +
|
" SELECT baoji_group_id,'"+binf.getStationId()+"',"+binf.getBattGroupId()+" from db_user.tb_user_battgroup_baojigroup_usr where uid="+((User_inf)ActionUtil.getUser()).getUId();
|
sql_str.add(baoji_sql);
|
{
|
String msg="添加"+binf.getStationName()+"机房"+binf.getBattGroupId()+"电池组的信息";
|
User_log ulog=UinfDaoFactory.CreateULog(UinfDaoFactory.Increase, msg);
|
listU.add(ulog);
|
}
|
}
|
}
|
boolean bl=DAOHelper.makeManualCommit(DBUtil.getConn(), sql_str);
|
(new User_logService()).addPro(listU);//将用户的操作记录下来
|
return bl;
|
|
}
|
//修改电池组信息(多个)
|
public boolean update(Object obj) {
|
List<BattInf> list=(List<BattInf>) obj;
|
ArrayList sql_str=new ArrayList();
|
List<User_log> listU=new ArrayList<User_log>();//存放user_log
|
if(list!=null&&list.size()>0){
|
for (int i = 0; i < list.size(); i++) {
|
BattInf binf=list.get(i);
|
//验证新添加的机房是否存在
|
List listS=(new BattInfImpl()).judgeBatt(binf);
|
if(listS!=null&&listS.size()>0){
|
binf.setStationId((String) listS.get(listS.size()-1));
|
}else{
|
//获取当前最大的stationid
|
List maxList=(new BattInfImpl()).searchMaxId();
|
if(maxList!=null&&maxList.size()>0){
|
int maxs=(Integer) maxList.get(0)+1;
|
binf.setStationId(String.valueOf(maxs));
|
}
|
}
|
String sql = "update db_battinf.tb_battinf set StationId='"+binf.getStationId()+"',StationName='"+binf.getStationName()+"',StationName1='"+binf.getStationName1()+"',StationName2='"+binf.getStationName2()+"',StationName3='"+binf.getStationName3()+"',StationName4='"+binf.getStationName4()+"',StationName5='"+binf.getStationName5()+"',StationIp='"+binf.getStationIp()+"',FBSDeviceId="+binf.getFBSDeviceId()+",FbsDeviceIp='"+binf.getFbsDeviceIp()+"',"
|
+ "FBSDeviceName='"+binf.getFBSDeviceName()+"',GroupIndexInFBSDevice="+binf.getGroupIndexInFBSDevice()+",BattGroupNum="+binf.getBattGroupNum()+",BattGroupName='"+binf.getBattGroupName()+"',FloatVolLevel="+binf.getFloatVolLevel()+",OfflineVolLevel="+binf.getOfflineVolLevel()+",BattFloatCurrent="+binf.getBattFloatCurrent()+"," +
|
"MonCount="+binf.getMonCount()+",MonCapStd="+binf.getMonCapStd()+",monVolStd="+binf.getMonVolStd()+",monResStd="+binf.getMonResStd()+",MonSerStd="+binf.getMonSerStd()+",MonVolLowToAvg="+binf.getMonVolLowToAvg()+",BattProducer='"+binf.getBattProducer()+"',BattModel='"+binf.getBattModel()+"',BattProductDate='"+DAOHelper.sdf.format(binf.getBattProductDate())+"',BattInUseDate='"+DAOHelper.sdf.format(binf.getBattInUseDate())+"', "
|
+" Load_curr="+binf.getLoad_curr()+",DisCurrMax="+binf.getDisCurrMax()+",station_phone='"+binf.getStation_phone()+"' "
|
+ ",Vol_grade="+binf.getVol_grade()+",Manufacturers='"+binf.getManufacturers()+"',Assetequipment='"+binf.getAssetequipment()+"',Deviceclasspath='"+binf.getDeviceclasspath()+"',Deviceoperationdepartment='"+binf.getDeviceoperationdepartment()+"',PropertyRightUnit='"+binf.getPropertyRightUnit()+"',PropertyAttribute='"+binf.getPropertyAttribute()+"',FactoryNumber='"+binf.getFactoryNumber()+"',OperationTeam='"+binf.getOperationTeam()+"' "
|
+ ",DeviceIdentityCode='"+binf.getDeviceIdentityCode()+"',ProjectType='"+binf.getProjectType()+"',BatteryApplicationType='"+binf.getBatteryApplicationType()+"',SingleRatedVoltage="+binf.getSingleRatedVoltage()+",RemoteMonitor='"+binf.getRemoteMonitor()+"',BatttIndependentPlaced='"+binf.getBatttIndependentPlaced()+"',JunChargeVoltageValue="+binf.getJunChargeVoltageValue()+" "
|
+" where battgroupId="+binf.getBattGroupId();
|
sql_str.add(sql);
|
{
|
String msg="修改"+binf.getStationName()+"机房"+binf.getBattGroupId()+"电池组的信息";
|
User_log ulog=UinfDaoFactory.CreateULog(UinfDaoFactory.Alter, msg);
|
listU.add(ulog);
|
}
|
}
|
}
|
(new User_logService()).addPro(listU);//将用户的操作记录下来
|
Boolean bl=DAOHelper.makeManualCommit(DBUtil.getConn(), sql_str);
|
return bl;
|
}
|
//修改电池信息配置(ip地址,掩码和网关)
|
public boolean updateIp(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql = "update db_battinf.tb_battinf set FbsDeviceIp='"+binf.getFbsDeviceIp()+"',FbsDeviceIp_YM='"+binf.getFbsDeviceIp_YM()+"',FbsDeviceIp_WG='"+binf.getFbsDeviceIp_WG()+"' where FbsDeviceId="+binf.getFBSDeviceId();
|
boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, null);
|
return bl;
|
}
|
|
//删除电池组(多个)
|
public boolean del(Object obj) {
|
List<BattInf> list=(List<BattInf>) obj;
|
ArrayList sql_str=new ArrayList();
|
List<User_log> listU=new ArrayList<User_log>();//存放user_log
|
if(list!=null&&list.size()>0){
|
for (int i = 0; i < list.size(); i++) {
|
BattInf binf=list.get(i);
|
String sql = "delete from db_battinf.tb_battinf where battgroupid="+binf.getBattGroupId();
|
sql_str.add(sql);
|
{
|
String msg="删除"+binf.getStationName()+"机房"+binf.getBattGroupId()+"电池组的信息";
|
User_log ulog=UinfDaoFactory.CreateULog(UinfDaoFactory.Delete, msg);
|
listU.add(ulog);
|
}
|
}
|
}
|
(new User_logService()).addPro(listU);//将用户的操作记录下来
|
Boolean bl=DAOHelper.makeManualCommit(DBUtil.getConn(), sql_str);
|
return bl;
|
}
|
//根据维护区和机房名称查询电池组信息
|
public List searchInform(Object obj){
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Page page=bmd.getPage();
|
Connection conn= DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="select SQL_CALC_FOUND_ROWS distinct(StationId),StationName1,StationName2,StationName3,StationName4,StationName5,StationIp,FBSDeviceId,FbsDeviceIp_YM,FbsDeviceIp_WG,FbsDeviceIp,"
|
+ "FBSDeviceName,GroupIndexInFBSDevice,battgroupId,BattGroupNum,BattGroupName,FloatVolLevel,OfflineVolLevel,BattFloatCurrent " +
|
",MonCount,MonCapStd,monVolStd,monResStd,MonSerStd,MonVolLowToAvg,BattProducer,BattModel,BattProductDate,BattInUseDate,Load_curr,DisCurrMax,station_phone "
|
+ ",Vol_grade,Manufacturers,Assetequipment,Deviceclasspath,Deviceoperationdepartment,PropertyRightUnit,PropertyAttribute,FactoryNumber,OperationTeam "
|
+ ",DeviceIdentityCode,ProjectType,BatteryApplicationType,SingleRatedVoltage,RemoteMonitor,BatttIndependentPlaced,JunChargeVoltageValue " +
|
" From db_battinf.tb_battinf " +
|
" Where StationName like ? and StationName1 like ? " +
|
" order by StationId " +
|
" limit ?,?";
|
List<BattInf> list=DAOHelper.executeQueryLimit(sql,conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%",(page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
int id=0;
|
|
try {
|
while (rs.next()) {
|
BattInf binf = new BattInf();
|
binf.setStationId(rs.getString("stationId"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName2(rs.getString("stationName2"));
|
binf.setStationName3(rs.getString("stationName3"));
|
binf.setStationName4(rs.getString("stationName4"));
|
binf.setStationName5(rs.getString("StationName5"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
|
binf.setFbsDeviceIp(rs.getString("fbsDeviceIp"));
|
binf.setFbsDeviceIp_YM(rs.getString("fbsDeviceIp_YM"));
|
binf.setFbsDeviceIp_WG(rs.getString("fbsDeviceIp_WG"));
|
binf.setFBSDeviceName(rs.getString("fBSDeviceName"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setFloatVolLevel(rs.getFloat("floatVolLevel"));
|
binf.setOfflineVolLevel(rs.getFloat("offlineVolLevel"));
|
binf.setBattFloatCurrent(rs.getFloat("battFloatCurrent"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setMonResStd(rs.getFloat("monResStd"));
|
binf.setMonSerStd(rs.getFloat("monSerStd"));
|
binf.setMonVolLowToAvg(rs.getFloat("monVolLowToAvg"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattModel(rs.getString("battModel"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setLoad_curr(rs.getFloat("load_curr"));
|
binf.setDisCurrMax(rs.getFloat("disCurrMax"));
|
binf.setStation_phone(rs.getString("station_phone"));
|
//紫晶
|
binf.setVol_grade(rs.getFloat("vol_grade"));
|
binf.setManufacturers(rs.getString("Manufacturers"));
|
binf.setAssetequipment(rs.getString("Assetequipment"));
|
binf.setDeviceclasspath(rs.getString("Deviceclasspath"));
|
binf.setDeviceoperationdepartment(rs.getString("Deviceoperationdepartment"));
|
binf.setPropertyRightUnit(rs.getString("PropertyRightUnit"));
|
binf.setPropertyAttribute(rs.getString("PropertyAttribute"));
|
binf.setFactoryNumber(rs.getString("FactoryNumber"));
|
binf.setOperationTeam(rs.getString("OperationTeam"));
|
binf.setDeviceIdentityCode(rs.getString("DeviceIdentityCode"));
|
binf.setProjectType(rs.getString("ProjectType"));
|
binf.setBatteryApplicationType(rs.getString("BatteryApplicationType"));
|
binf.setSingleRatedVoltage(rs.getFloat("SingleRatedVoltage"));
|
binf.setRemoteMonitor(rs.getString("RemoteMonitor"));
|
binf.setBatttIndependentPlaced(rs.getString("BatttIndependentPlaced"));
|
binf.setJunChargeVoltageValue(rs.getFloat("JunChargeVoltageValue"));
|
list.add(binf);
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).setNum(number);
|
}
|
return list;
|
}
|
//添加电池组时查询出最大的机房id和电池组id
|
public List searchMaxId(){
|
String sql="select max(stationid) as maxs,max(battgroupid) as maxb from db_battinf.tb_battinf 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 maxs=42000000;
|
int maxb=1000001;
|
if(rs.getString("maxs") != null){
|
maxs=Integer.parseInt(rs.getString("maxs"));
|
}
|
if(rs.getInt("maxb")!=0){
|
maxb=rs.getInt("maxb");
|
}
|
list.add(maxs);
|
list.add(maxb);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//判断新增的电池组是否存在
|
public List judgeBatt(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select distinct stationid from db_battinf.tb_battinf where stationname=? limit 1";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationName()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
String stationid=rs.getString("stationid");
|
list.add(stationid);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//0.4根据城市名称查电池组
|
public List search(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select distinct(battgroupid) from db_battinf.tb_battinf where stationname like ? ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName()+"%"}, new CallBack() {
|
List list = new ArrayList<BattInf>();
|
int id=0;
|
public List getResults(ResultSet rs) {
|
try {
|
while(rs.next()){
|
BattInf binf =new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}
|
//查询出当前存在内存中最大的设备的id(很重要**********)
|
public int searchMaxdevId_binf(){
|
String sql="select max(FbsDeviceId) as id from db_battinf.tb_battinf 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 dev_id=0;
|
if(rs.getString("id")!=null){}
|
dev_id=rs.getInt("id");
|
list.add(dev_id);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int dev_id=0;
|
if(list!=null&&list.size()>0){
|
dev_id=(Integer) list.get(list.size()-1);
|
}
|
return dev_id;
|
}
|
//2.2查所有的电池组
|
public List searchAll() {
|
// system.out.println("开始查询");
|
String sql = "select battgroupid from db_battinf.tb_battinf ";
|
// system.out.println(user.getUname()+"&&&");
|
List list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null,
|
new CallBack() {
|
List list = new ArrayList<BattInf>();
|
int id=0;
|
public List getResults(ResultSet rs) {
|
try {
|
while(rs.next()){
|
BattInf binf =new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//5.3添加选取电池组或者机房或者维护区
|
public List serchBatt(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql="select distinct(battgroupid),stationid,stationname,stationname1,battgroupname from db_battinf.tb_battinf " +
|
"where ";
|
//用于维护区
|
String station1SqlT=" stationname1!=? ";//全部
|
String station1SqlF=" 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 battSqlT=" and battgroupid!=? ";
|
String battSqlF=" and battgroupid=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=battSqlT;
|
}else{
|
baseSql+=battSqlF;
|
}
|
String endSql=" order by stationid ";
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationName1(),binf.getStationName(),binf.getBattGroupId()}, 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.setStationId(rs.getString("StationId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//查询电池组左侧导航菜单（实时监测，历史数据查询）
|
public List findMenu() {
|
String sql = "select DISTINCT(StationName1),StationName,StationName2,StationName3,StationName4,StationName5,StationName6,StationName7,StationName8,StationName9,StationId,StationIP,BattGroupName,BattGroupName1,BattGroupName2,MonCount,battgroupId,MonCapStd,MonVolStd,MonResStd,MonSerStd,BattProducer,BattInUseDate " +
|
" from db_battinf.tb_battinf " +
|
" ORDER BY StationName1,battgroupId";
|
return DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<List> list = new ArrayList<List>();
|
String conStr="-";//连接机房名称的符号
|
String station="";//用于判断是不是同一机房
|
List listS = new ArrayList();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
String stationName="";
|
if(!rs.getString("StationName1").trim().equals("")){
|
stationName+=rs.getString("StationName1");
|
}
|
if(!rs.getString("StationName2").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName2");
|
}
|
if(!rs.getString("StationName3").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName3");
|
}
|
if(!rs.getString("StationName4").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName4");
|
}
|
if(!rs.getString("StationName5").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName5");
|
}
|
if(!rs.getString("StationName6").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName6");
|
}
|
if(!rs.getString("StationName7").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName7");
|
}
|
if(!rs.getString("StationName8").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName8");
|
}
|
if(!rs.getString("StationName9").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName9");
|
}
|
batt.setStationName(stationName);
|
batt.setStationId(rs.getString("StationId"));
|
batt.setStationIp(rs.getString("StationIP"));
|
batt.setBattGroupName(rs.getString("BattGroupName").trim());
|
batt.setBattGroupName1(rs.getString("BattGroupName1").trim());
|
batt.setBattGroupName2(rs.getString("BattGroupName2").trim());
|
batt.setMonCount(rs.getInt("MonCount"));
|
batt.setBattGroupId(rs.getInt("battgroupId"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setMonResStd(rs.getFloat("MonResStd"));
|
batt.setMonSerStd(rs.getFloat("MonSerStd"));
|
batt.setBattProducer(rs.getString("BattProducer"));
|
batt.setBattInUseDate(rs.getDate("BattInUseDate"));
|
if(!station.equals(stationName)){
|
if(!station.equals("")){
|
list.add(listS);
|
}
|
listS=new ArrayList();
|
station=stationName;
|
}
|
listS.add(batt);
|
}
|
list.add(listS);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
|
//----------根据StationName1（维护区） 查不重复BattGroupName（蓄电池组）
|
public List serchByStationName1(Object obj) {
|
BattInf bif = (BattInf) obj;
|
//System.out.println(bif.getStationName1());
|
String sql = null;
|
List list = new ArrayList();
|
if (bif==null || bif.getStationName1() == null) {
|
sql = "select DISTINCT(StationName),battgroupname,moncount,BattGroupId from db_battinf.tb_battinf ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null,
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
// batt.setStationName(rs.getString("StationName"));
|
batt.setBattGroupName(rs
|
.getString("battGroupName"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setBattGroupId(rs.getInt("BattGroupId"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
} else {
|
sql = "select DISTINCT(StationName),battgroupname,moncount,battgroupid from db_battinf.tb_battinf where StationName1 like ? ORDER BY StationName1,battgroupid";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(),
|
new Object[] { "%"+bif.getStationName1()+"%" }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
// batt.setStationName(rs.getString("StationName"));
|
batt.setBattGroupName(rs
|
.getString("battGroupName"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setBattGroupId(rs.getInt("battGroupId"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
}
|
return list;
|
|
}
|
|
// ----------根据StationName（站点） 查不重复的BattGroupName（蓄电池组）
|
public List serchByBattGroupName(Object obj) {
|
BattInf bif = (BattInf) obj;
|
String sql = null;
|
List list = new ArrayList();
|
if(bif.getStationName()!= null && bif.getStationName1()!= null){
|
if (bif.getStationName().equals("")) {
|
if (!bif.getStationName1().equals("")) {
|
sql = "select DISTINCT(StationName),FBSDeviceId,StationId,battgroupname,moncount,BattGroupId,StationName,battproducer,moncapstd,monvolstd,battproductdate,battinusedate,GroupIndexInFBSDevice from db_battinf.tb_battinf where stationname1=? ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(),
|
new Object[] { bif.getStationName1() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
//batt.setStationName(rs.getString("StationName"));
|
batt.setBattGroupName(rs.getString("battGroupName"));
|
batt.setStationName(rs.getString("StationName"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setFBSDeviceId(rs.getInt("FBSDeviceId"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setBattGroupId(rs.getInt("BattGroupId"));
|
batt.setBattProducer(rs.getString("battProducer"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setBattProductDate(rs.getDate("battProductDate"));
|
batt.setBattInUseDate(rs.getDate("battInUseDate"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
} else {
|
sql = "select DISTINCT(StationName),FBSDeviceId,StationId,battgroupname,moncount,BattGroupId,StationName,battproducer,moncapstd,monvolstd,battproductdate,battinusedate,GroupIndexInFBSDevice from db_battinf.tb_battinf ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null,
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
// batt.setStationName(rs.getString("StationName"));
|
batt.setBattGroupName(rs
|
.getString("battGroupName"));
|
batt.setStationName(rs.getString("StationName"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setFBSDeviceId(rs.getInt("FBSDeviceId"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setBattGroupId(rs.getInt("BattGroupId"));
|
batt.setBattProducer(rs.getString("battProducer"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setBattProductDate(rs.getDate("battProductDate"));
|
batt.setBattInUseDate(rs.getDate("battInUseDate"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
} else {
|
if (bif.getStationName1().equals("")) {
|
sql = "select DISTINCT(battgroupname),FBSDeviceId,StationId,moncount,BattGroupId,BattGroupId,StationName,battproducer,moncapstd,monvolstd,battproductdate,battinusedate,GroupIndexInFBSDevice from db_battinf.tb_battinf where StationName=? ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(),
|
new Object[] { bif.getStationName() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setBattGroupName(rs
|
.getString("battGroupName"));
|
batt.setStationName(rs.getString("StationName"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setBattGroupId(rs.getInt("BattGroupId"));
|
batt.setFBSDeviceId(rs.getInt("FBSDeviceId"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setBattProducer(rs.getString("battProducer"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setBattProductDate(rs.getDate("battProductDate"));
|
batt.setBattInUseDate(rs.getDate("battInUseDate"));
|
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
} else {
|
sql = "select DISTINCT(battgroupname),FBSDeviceId,StationId,moncount,BattGroupId,StationName,battproducer,moncapstd,monvolstd,battproductdate,battinusedate,GroupIndexInFBSDevice from db_battinf.tb_battinf where StationName=? and stationname1=? ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), new Object[] {
|
bif.getStationName(), bif.getStationName1() },
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setBattGroupName(rs
|
.getString("battGroupName"));
|
batt.setStationName(rs.getString("StationName"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setBattGroupId(rs.getInt("BattGroupId"));
|
batt.setFBSDeviceId(rs.getInt("FBSDeviceId"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setBattProducer(rs.getString("battProducer"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setBattProductDate(rs.getDate("battProductDate"));
|
batt.setBattInUseDate(rs.getDate("battInUseDate"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
}
|
}
|
return list;
|
}
|
|
|
// ----------根据StationName1（维护区） 查不重复的StationName（站点）
|
public List serchByStationName(Object obj) {
|
BattInf binf = (BattInf) obj;
|
String sql="";
|
String baseSql = "select DISTINCT(StationName),stationid,fbsdeviceid " +
|
" from db_battinf.tb_battinf " +
|
" where ";
|
//用于维护区
|
String station1SqlT=" stationname1!=? ";//全部
|
String station1SqlF=" stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
String endSql=" ORDER BY StationName1,battgroupId";
|
sql=baseSql+endSql;
|
List list = DAOHelper.executeQuery(sql,DBUtil.getConn(), new Object[] { binf.getStationName1() },new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationId(rs.getString("stationid"));
|
batt.setFBSDeviceId(rs.getInt("fbsdeviceid"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
// ----------根据StationName1（维护区） 查不重复的StationName（站点）不包含91000000一期设备
|
public List serchByStationNameNot91(Object obj) {
|
BattInf binf = (BattInf) obj;
|
String sql="";
|
String baseSql="select DISTINCT(StationName),StationName1,stationid,fbsdeviceid,FBSDeviceName " +
|
" from db_battinf.tb_battinf " +
|
" where ";
|
//用于维护区
|
String station1SqlT=" stationname1!=? and fbsdeviceid>="+FBS9100_ComBase.EQUIPMENTCLASSIFICATION_TWO+" ";//全部
|
String station1SqlF=" stationname1=? and fbsdeviceid>="+FBS9100_ComBase.EQUIPMENTCLASSIFICATION_TWO+" ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
String endSql=" ORDER BY StationName1,battgroupId";
|
sql=baseSql+endSql;
|
List list = DAOHelper.executeQuery(sql,DBUtil.getConn(), new Object[] { binf.getStationName1() },new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
batt.setStationId(rs.getString("stationid"));
|
batt.setFBSDeviceId(rs.getInt("fbsdeviceid"));
|
batt.setFBSDeviceName(rs.getString("fBSDeviceName"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
// ----------查不重复的StationName1（维护区）
|
public List serchByStation() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(StationName1) from db_battinf.tb_battinf ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName1(rs
|
.getString("stationName1"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
// ----------查不重复的StationName1（维护区）不包含91000000一期设备
|
public List serchByStationNot91() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(StationName1) from db_battinf.tb_battinf where fbsdeviceId>="+FBS9100_ComBase.EQUIPMENTCLASSIFICATION_TWO+" ORDER BY StationName1,battgroupId";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName1(rs
|
.getString("stationName1"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//-----------查询所有的省份
|
public List serchAllStationName1(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql=" select DISTINCT(StationName1) from db_battinf.tb_battinf "
|
+ " where StationId in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" ) "
|
+ " ORDER BY CONVERT( StationName1 USING gbk ) COLLATE gbk_chinese_ci ASC ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
String StationName1=rs.getString("StationName1");
|
list.add(StationName1);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//-----------查询所有的市
|
public List serchAllStationName2(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select DISTINCT(StationName2) from db_battinf.tb_battinf "
|
+ " where StationName1 like ? and StationId in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" ) "
|
+ " ORDER BY CONVERT( StationName2 USING gbk ) COLLATE gbk_chinese_ci ASC ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%",binf.getNum()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
String StationName2=rs.getString("StationName2");
|
list.add(StationName2);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//-----------查询所有的区县
|
public List serchAllStationName5(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select DISTINCT(StationName5) from db_battinf.tb_battinf "
|
+ " where StationName1 like ? and StationName2 like ? "
|
+ " ORDER BY CONVERT( StationName5 USING gbk ) COLLATE gbk_chinese_ci ASC ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
String StationName5=rs.getString("StationName5");
|
list.add(StationName5);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//-----------查询所有的机房
|
public List serchAllStationName(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select DISTINCT StationName,StationName1,StationName2,StationName3,StationName5,stationid,fbsdeviceid,FBSDeviceName,station_install " +
|
" FROM db_battinf.tb_battinf " +
|
" where StationName1 like ? and StationName2 like ? and StationName5 like ? " +
|
" ORDER BY CONVERT( StationName USING gbk ) COLLATE gbk_chinese_ci ASC ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%"},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
batt.setStationName2(rs.getString("stationName2"));
|
batt.setStationName3(rs.getString("stationName3"));
|
batt.setStationName5(rs.getString("stationName5"));
|
batt.setStationId(rs.getString("stationId"));
|
batt.setFBSDeviceName(rs.getString("fBSDeviceName"));
|
batt.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
batt.setStation_install(rs.getInt("station_install"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
} );
|
return list;
|
}
|
|
//-----------查询电池组信息
|
public List serchAllBattinf(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select DISTINCT StationName,StationName1,StationName2,StationName3,StationName5,StationId,stationid_ex,StationIP,FBSDeviceId,BattGroupName,BattGroupName1,BattGroupName2,MonCount,battgroupId,MonCapStd,MonVolStd,MonResStd,MonSerStd,BattProducer,BattInUseDate,GroupIndexInFBSDevice,Load_curr,DisCurrMax " +
|
" from db_battinf.tb_battinf where StationName1 like ? and StationName2 like ? and StationName5 like ? "
|
+ " and Stationid like ? "
|
+ " ORDER BY battgroupId asc ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%","%"+binf.getStationId()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
batt.setStationName2(rs.getString("stationName2"));
|
batt.setStationName3(rs.getString("stationName3"));
|
batt.setStationName5(rs.getString("stationName5"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setStationId_ex(rs.getString("stationId_ex"));
|
batt.setStationIp(rs.getString("StationIP"));
|
batt.setBattGroupName(rs.getString("BattGroupName").trim());
|
batt.setBattGroupName1(rs.getString("BattGroupName1").trim());
|
batt.setBattGroupName2(rs.getString("BattGroupName2").trim());
|
batt.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
batt.setMonCount(rs.getInt("MonCount"));
|
batt.setBattGroupId(rs.getInt("battgroupId"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setMonResStd(rs.getFloat("MonResStd"));
|
batt.setMonSerStd(rs.getFloat("MonSerStd"));
|
batt.setBattProducer(rs.getString("BattProducer"));
|
batt.setBattInUseDate(rs.getDate("BattInUseDate"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setLoad_curr(rs.getFloat("load_curr"));
|
batt.setDisCurrMax(rs.getFloat("disCurrMax"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
// 查电池品牌
|
public List serchByBattProducer() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(BattProducer) from db_battinf.tb_battinf ORDER BY BattProducer";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setBattProducer(rs
|
.getString("battProducer"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
|
// 查单体电压
|
public List serchByMonVolStd() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(MonVolStd) from db_battinf.tb_battinf ";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
|
// 查电池容量
|
public List serchByMonCapStd() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(MonCapStd) from db_battinf.tb_battinf ORDER BY MonCapStd";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setMonCapStd(rs.getFloat("MonCapStd"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
|
// 查系统类型
|
public List serchByBattGroupName1() {
|
String sql = null;
|
List list = new ArrayList();
|
sql = "select DISTINCT(BattGroupName1) from db_battinf.tb_battinf";
|
list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setBattGroupName1(rs
|
.getString("BattGroupName1"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//查设备总数
|
/*public int serchByDeviceId(){
|
String sql=" SELECT COUNT(DISTINCT fbsdeviceid) number FROM db_battinf.tb_battinf limit 1";
|
List<BattInf> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setNum(rs.getInt("number"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
});
|
int number=0;//存放总数
|
if(list!=null&&list.size()>0){
|
number=list.get(list.size()-1).getNum();
|
}
|
return number;
|
}*/
|
public int serchByDeviceId(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql=" SELECT COUNT(DISTINCT fbsdeviceid) number FROM db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" 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=? " +
|
" limit 1";
|
List<BattInf> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setNum(rs.getInt("number"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
});
|
int number=0;//存放总数
|
if(list!=null&&list.size()>0){
|
number=list.get(list.size()-1).getNum();
|
}
|
return number;
|
}
|
//-----------根据蓄电池组查不重复的MonCount(monNum)（单体编号）
|
public List serchByMonNum(Object obj){
|
BattInf bif = (BattInf) obj;
|
// system.out.println(bif.getStationName1());
|
String sql = "select battGroupName,moncount,signalname from db_battinf.tb_battinf where battgroupid=? order by signalname";
|
List list = DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{bif.getBattGroupId()}, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
// batt.setStationName(rs.getString("StationName"));
|
batt.setBattGroupName(rs.getString("battGroupName"));
|
batt.setMonCount(rs.getInt("monCount"));
|
batt.setMonNum(Integer.parseInt(rs.getString("signalname").split("#")[0]));
|
//System.out.println(batt);
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public List serchByBattgroupId(Object obj){
|
BattInf binf=(BattInf)obj;
|
String sql="select * from db_battinf.tb_battinf where battgroupid=?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId()},new CallBack() {
|
|
public List getResults(ResultSet rs) { List list = new ArrayList();
|
try {
|
while (rs.next()) { BattInf batt = new BattInf();
|
|
batt.setNum(Integer.parseInt(rs.getString("num")));
|
|
batt.setStationId((rs.getString("StationId")));
|
|
batt.setStationName(rs.getString("StationName"));
|
|
batt.setStationName1(rs.getString("StationName1"));
|
|
batt.setStationName2(rs.getString("StationName2"));
|
|
batt.setStationName3(rs.getString("StationName3"));
|
|
batt.setStationName4(rs.getString("StationName4"));
|
|
batt.setStationName5(rs.getString("StationName5"));
|
|
batt.setStationName6(rs.getString("StationName6"));
|
|
batt.setStationName7(rs.getString("StationName7"));
|
|
batt.setStationName8(rs.getString("StationName8"));
|
|
batt.setStationName9(rs.getString("StationName9"));
|
|
batt.setStationIp(rs.getString("StationIp"));
|
|
batt.setFBSDeviceId(Integer.parseInt(rs.getString("FBSDeviceId")));
|
|
batt.setFbsDeviceIp(rs.getString("FbsDeviceIp"));
|
|
batt.setFBSDeviceName(rs.getString("FBSDeviceName"));
|
|
batt.setGroupIndexInFBSDevice(Integer.parseInt(rs.getString(
|
"GroupIndexInFBSDevice")));
|
|
batt.setBattModel(rs.getString("BattModel"));
|
|
batt.setBattGroupId(Integer.parseInt(rs.getString("BattGroupId")));
|
|
batt.setBattGroupNum(Integer.parseInt(rs.getString("BattGroupNum")));
|
|
batt.setBattGroupName(rs.getString("BattGroupName"));
|
|
batt.setBattGroupName1(rs.getString("BattGroupName1"));
|
|
batt.setBattGroupName2(rs.getString("BattGroupName2"));
|
|
batt.setBattGroupName3(rs.getString("BattGroupName3"));
|
|
batt.setBattGroupName4(rs.getString("BattGroupName4"));
|
|
batt.setBattGroupName5(rs.getString("BattGroupName5"));
|
|
batt.setBattGroupName6(rs.getString("BattGroupName6"));
|
|
batt.setFloatVolLevel(Float.parseFloat(rs.getString("FloatVolLevel")));
|
|
batt.setOfflineVolLevel(Float.parseFloat(rs.getString("OfflineVolLevel"))) ;
|
|
batt.setBattFloatCurrent(Float.parseFloat(rs.getString("BattFloatCurrent")));
|
|
batt.setDeviceId(rs.getString("DeviceId"));
|
|
batt.setDeviceName(rs.getString("DeviceName"));
|
|
batt.setMonCount(Integer.parseInt(rs.getString("MonCount")));
|
|
batt.setMonCapStd(Float.parseFloat(rs.getString("MonCapStd")));
|
|
batt.setMonResStd(Float.parseFloat(rs.getString("MonResStd")));
|
|
batt.setMonSerStd(Float.parseFloat(rs.getString("MonSerStd")));
|
|
batt.setMonTmpStd(Float.parseFloat(rs.getString("MonTmpStd")));
|
|
batt.setMonVolStd(Float.parseFloat(rs.getString("MonVolStd")));
|
|
batt.setMonVolLowToAvg(Float.parseFloat(rs.getString("MonVolLowToAvg")));
|
|
batt.setMonNum(Integer.parseInt(rs.getString("MonNum")));
|
|
batt.setBattProducer(rs.getString("BattProducer"));
|
|
batt.setBattProductDate((Date) rs.getObject("BattProductDate"));
|
|
batt.setBattInUseDate((Date) rs.getObject("BattInUseDate"));
|
|
batt.setSignalId(rs.getString("SignalId"));
|
|
batt.setCInterFaceId(Integer.parseInt(rs.getString("CInterFaceId")));
|
|
batt.setSignalName(rs.getString("SignalName"));
|
int BattGuarantDayCount=rs.getInt("BattGuarantDayCount")-ActionUtil.daysBetween(rs.getDate("BattInUseDate"), new Date());//剩余保修天数
|
batt.setBattGuarantDayCount(BattGuarantDayCount);
|
|
list.add(batt); } } catch (SQLException e) { e.printStackTrace(); }
|
return list; }
|
|
});
|
}
|
|
//3.1/3.2/0.1/6.2/6.4.3/6.4.4根据battgroupid查机房名称和电池组名称/查capstdolstd/moncount/serstd
|
public List serchByCondition(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql="select distinct(stationname),battgroupname,moncapstd,monserstd,monvolstd,moncount from db_battinf.tb_battinf where battgroupid=? ";
|
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()){
|
BattInf binf=new BattInf();
|
binf.setStationName(rs.getString("stationName"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonSerStd(rs.getFloat("monSerStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//1.1根据选择的信息查满足条件的battgroupid
|
/*public List serchByConditionNew(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
List list = null;
|
BattInf binf = bmd.getBinf();
|
Batt_maint_inf mainf = bmd.getMainf();
|
Batttestdata_inf tdata = bmd.getTdata();
|
User_inf uinf=bmd.getUinf();
|
String sql="";
|
String baseSql="select distinct(db_battinf.tb_battinf.BattgroupId),db_battinf.tb_battinf.stationid,StationName1,StationName,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate "
|
+ ",maint_done_time"
|
+ ",test_record_count,test_starttime,test_type,test_curr,test_timelong,max_monvol,min_monvol,test_cap,data_new "
|
+",db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid,db_user.tb_user_inf.uId,db_user.tb_user_inf.uName "
|
+ "from db_battinf.tb_battinf "
|
+ "left outer join db_battinf.tb_batt_maint_inf on db_battinf.tb_battinf.BattgroupId=db_battinf.tb_batt_maint_inf.BattGroupId "
|
+ "left outer join db_batt_testdata.tb_batttestdata_inf on db_battinf.tb_battinf.BattgroupId=db_batt_testdata.tb_batttestdata_inf.BattGroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid=db_battinf.tb_battinf.BattgroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id " +
|
"left outer join db_user.tb_user_inf on db_user.tb_user_inf.uId=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
"where to_days(battproductdate)>=to_days(?) and to_days(battproductdate)<=to_days(?) and to_days(battinusedate)>=to_days(?) and to_days(battinusedate)<=to_days(?)";
|
//用于维护区
|
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 producerSqlT=" and battproducer!=? ";//全部
|
String producerSqlF=" and battproducer=? ";
|
if(binf.getBattProducer().equals("")){
|
baseSql+=producerSqlT;
|
}else{
|
baseSql+=producerSqlF;
|
}
|
//用于电池测试记录条件:
|
//放电/充电单个/有在线监测记录
|
String testSqlH=" and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type=? and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) ";
|
//内阻/电导
|
String testSqlF=" and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_battresdata_inf.BattGroupId ) from db_batt_testdata.tb_battresdata_inf where db_batt_testdata.tb_battresdata_inf.test_type!=? and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) ";
|
//充电或者放电都行
|
String testSqlT=" and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) ";
|
//无记录
|
String testSqlN=" and db_battinf.tb_battinf.BattGroupId not in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) " +
|
" and db_battinf.tb_battinf.BattGroupId not in(select distinct(db_batt_testdata.tb_battresdata_inf.BattGroupId ) from db_batt_testdata.tb_battresdata_inf where db_batt_testdata.tb_battresdata_inf.test_type in (?,5) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) " ;
|
//全部
|
String testSqlA=" and db_battinf.tb_battinf.BattGroupId!=(select count(distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId )) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?) and to_days(test_starttime)<=to_days(record_time)) " ;
|
if(tdata.getTest_type()==2){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==3){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==9){//有在线监测记录
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==5000){
|
baseSql+=testSqlF;
|
}else if(tdata.getTest_type()==3000){//充电或放电
|
baseSql+=testSqlT;
|
}else if(tdata.getTest_type()==4000){//无记录
|
baseSql+=testSqlN;
|
}else if(tdata.getTest_type()==0){
|
baseSql+=testSqlA;
|
}
|
//选取蓄电池组条件
|
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 nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
//包机人
|
String userSqlF=" and db_user.tb_user_inf.uname=? and db_user.tb_user_inf.ubaojiusr=1 ";
|
if(uinf.getUName().equals("0")){
|
baseSql=baseSql;
|
}else{
|
baseSql+=userSqlF;
|
}
|
//用于测试类型
|
//全部
|
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 maintSqlT=" and db_battinf.tb_battinf.BattGroupId in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
//无维护记录
|
String maintSqlF=" and db_battinf.tb_battinf.BattGroupId not in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
//全部
|
String maintSqlA=" and db_battinf.tb_battinf.BattGroupId!=(select count(distinct(db_battinf.tb_batt_maint_inf.battgroupid )) from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
|
if(mainf.getRemark().equals("0")){
|
baseSql+=maintSqlF;
|
}else if(mainf.getRemark().equals("1")){
|
baseSql+=maintSqlT;
|
}else if(mainf.getRemark().equals("100")){
|
baseSql+=maintSqlA;
|
}
|
String endSql=" order by StationName1 asc,db_battinf.tb_battinf.BattgroupId asc,test_starttime asc ";
|
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
if(tdata.getTest_type()==4000){
|
if(uinf.getUName().equals("0")){
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}else{
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
uinf.getUName(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}
|
}else{
|
if(uinf.getUName().equals("0")){
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}else{
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
uinf.getUName(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}
|
}
|
|
// System.out.println(list.size());
|
List<Batt_Maint_Dealarm> listd=new ArrayList();
|
for(int i=0;i<list.size();i++){
|
System.out.println(((Batt_Maint_Dealarm)list.get(i)).getBinf().getBattGroupId());
|
}
|
int index=0;
|
//System.out.println(list.size());
|
for(int i=(bmd.getPage().getPageCurr()-1)*bmd.getPage().getPageSize();i<bmd.getPage().getPageSize()*bmd.getPage().getPageCurr()&&i<list.size();i++){
|
listd.add((Batt_Maint_Dealarm) list.get(i));
|
//system.out.println(list.get(i));
|
((Batt_Maint_Dealarm)listd.get(index++)).getPage().setPageAll(list.size());
|
}
|
return listd;
|
}*/
|
//1.1根据选择的信息查满足条件的battgroupid
|
public List serchByConditionNew(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf = bmd.getBinf();
|
User_inf uinf=bmd.getUinf();
|
Page page=bmd.getPage();
|
Connection conn=DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="";
|
String baseSql="select SQL_CALC_FOUND_ROWS distinct(db_battinf.tb_battinf.BattgroupId),db_battinf.tb_battinf.stationid,StationName1,StationName,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate "
|
+ " from db_battinf.tb_battinf "
|
+ " where ";
|
//选取蓄电池组条件
|
String idSqlT=" db_battinf.tb_battinf.BattgroupId!=? ";
|
String idSqlF=" db_battinf.tb_battinf.BattgroupId=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//用户管理的机房
|
String userSql=" and db_battinf.tb_battinf.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=? " +
|
" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ?)";
|
baseSql+=userSql;
|
String endSql=" order by StationName1 asc,db_battinf.tb_battinf.BattgroupId asc ";
|
sql=baseSql+endSql;
|
String limitSql=" limit ?,? ";
|
sql+=limitSql;
|
//System.out.println(sql);
|
List<BattInf> list = DAOHelper.executeQueryLimit(sql,conn,new Object[] {binf.getBattGroupId(),uinf.getUId(),"%"+binf.getStationName1()+"%","%"+binf.getStationName()+"%",(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.setStationId(rs.getString("stationId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
list.add(binf);
|
}
|
} 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).setSize(number);
|
}
|
return list;
|
}
|
//1.4电池组后评估(分页查询电池组信息)
|
/*public List serchGroupAssess(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf = bmd.getBinf();
|
Page page =bmd.getPage();
|
User_inf uinf=bmd.getUinf();
|
Connection conn=DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="";
|
String baseSql=" select SQL_CALC_FOUND_ROWS distinct(db_battinf.tb_battinf.BattgroupId),db_battinf.tb_battinf.stationid,StationName1,StationName,stationip,BattGroupName,BattGroupName1,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid=db_battinf.tb_battinf.BattgroupId " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on db_user.tb_user_inf.uId=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where battproductdate>=? and battproductdate<=? and battinusedate>=? and battinusedate<=?";
|
//用于维护区
|
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 producerSqlT=" and battproducer!=? ";//全部
|
String producerSqlF=" and battproducer=? ";
|
if(binf.getBattProducer().equals("")){
|
baseSql+=producerSqlT;
|
}else{
|
baseSql+=producerSqlF;
|
}
|
//选取蓄电池组条件
|
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 nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
//用于包机人条件
|
String userSqlT="and db_battinf.tb_battinf.battgroupid!=? ";//全部
|
String userSqlF="and db_user.tb_user_inf.uId=? and db_user.tb_user_inf.ubaojiusr=1 ";
|
if(uinf.getUId()==0){
|
baseSql+=userSqlT;
|
}else{
|
baseSql+=userSqlF;
|
}
|
//排序
|
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.getBattProductDate(),binf.getBattProductDate1(),binf.getBattInUseDate(),binf.getBattInUseDate1()
|
,binf.getStationName1(),binf.getStationName(),binf.getBattProducer(),binf.getBattGroupId()
|
,"%"+binf.getBattGroupName1()+"%",binf.getMonVolStd(),binf.getMonCapStd(),uinf.getUId()
|
,(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.setStationId(rs.getString("stationId"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
|
list.add(binf);
|
}
|
} 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).setSize(number);
|
}
|
|
return list;
|
}*/
|
//1.4电池组后评估(分页查询电池组信息)
|
public List serchGroupAssess(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf = bmd.getBinf();
|
Page page =bmd.getPage();
|
User_inf uinf=bmd.getUinf();
|
Connection conn=DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="";
|
String baseSql=" select SQL_CALC_FOUND_ROWS distinct(db_battinf.tb_battinf.BattgroupId),db_battinf.tb_battinf.stationid,StationName1,StationName,stationip,BattGroupName,BattGroupName1,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate " +
|
" from db_battinf.tb_battinf " +
|
" where ";
|
//选取蓄电池组条件
|
String idSqlT=" db_battinf.tb_battinf.battgroupid!=? ";
|
String idSqlF=" db_battinf.tb_battinf.battgroupid=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//用户管理的机房
|
String userSql=" and db_battinf.tb_battinf.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=? " +
|
" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ?)";
|
baseSql+=userSql;
|
//排序
|
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.getBattGroupId(),uinf.getUId(),"%"+binf.getStationName1()+"%","%"+binf.getStationName()+"%",
|
(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.setStationId(rs.getString("stationId"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
|
list.add(binf);
|
}
|
} 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).setSize(number);
|
}
|
|
return list;
|
}
|
|
//1.2电池组统计分析查询
|
/*public List serchByTestType(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf = bmd.getBinf();
|
Page page =bmd.getPage();
|
Batttestdata_inf tinf=bmd.getTdata();
|
Connection conn=DBUtil.getConn();
|
String sql="";
|
String baseSql=" select distinct(db_battinf.tb_battinf.BattgroupId),StationName1,StationName,stationip,BattGroupName,BattGroupName1,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate " +
|
" ,db_batt_testdata.tb_batttestdata_inf.test_record_count,test_starttime " +
|
"from db_battinf.tb_battinf,db_batt_testdata.tb_batttestdata_inf " +
|
" where db_battinf.tb_battinf.battgroupid=db_batt_testdata.tb_batttestdata_inf.battgroupid " +
|
" and battproductdate>=? and battproductdate<=? and battinusedate>=? and battinusedate<=? " +
|
" and record_time>=? and record_time<=? ";
|
//用于维护区
|
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 producerSqlT=" and battproducer!=? ";//全部
|
String producerSqlF=" and battproducer=? ";
|
if(binf.getBattProducer().equals("")){
|
baseSql+=producerSqlT;
|
}else{
|
baseSql+=producerSqlF;
|
}
|
//选取蓄电池组条件
|
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 nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
//充电放电选择
|
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 endSql=" order by db_battinf.tb_battinf.battgroupid asc,test_starttime asc ";
|
//最终结果
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
List<BattInf> list=DAOHelper.executeQuery(sql,DBUtil.getConn(), new Object[]{
|
binf.getBattProductDate(),binf.getBattProductDate1(),binf.getBattInUseDate(),binf.getBattInUseDate1()
|
,tinf.getRecord_time(),tinf.getRecord_time1()
|
,binf.getStationName1(),binf.getStationName(),binf.getBattProducer(),binf.getBattGroupId()
|
,"%"+binf.getBattGroupName1()+"%",binf.getMonVolStd(),binf.getMonCapStd()
|
}, 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.setStationName1(rs.getString("stationName1"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setNum(rs.getInt("test_record_count"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
return list;
|
}*/
|
//1.2电池组统计分析查询
|
public List serchByTestType(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf = bmd.getBinf();
|
User_inf uinf=bmd.getUinf();
|
Page page =bmd.getPage();
|
Batttestdata_inf tinf=bmd.getTdata();
|
String sql="";
|
String baseSql=" select distinct(db_battinf.tb_battinf.BattgroupId),StationName1,StationName,stationip,BattGroupName,BattGroupName1,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate " +
|
" ,db_batt_testdata.tb_batttestdata_inf.test_record_count,test_starttime " +
|
"from db_battinf.tb_battinf,db_batt_testdata.tb_batttestdata_inf " +
|
" where db_battinf.tb_battinf.battgroupid=db_batt_testdata.tb_batttestdata_inf.battgroupid " +
|
" and record_time>=? and record_time<=? ";
|
//选取蓄电池组条件
|
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 userSql=" and db_battinf.tb_battinf.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=? " +
|
" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ?)";
|
baseSql+=userSql;
|
//充电放电选择
|
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 endSql=" order by db_battinf.tb_battinf.battgroupid asc,test_starttime asc ";
|
//最终结果
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
List<BattInf> list=DAOHelper.executeQuery(sql,DBUtil.getConn(), new Object[]{
|
tinf.getRecord_time(),tinf.getRecord_time1()
|
,binf.getBattGroupId(),uinf.getUId(),"%"+binf.getStationName1()+"%","%"+binf.getStationName()+"%"
|
}, 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.setStationName1(rs.getString("stationName1"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setNum(rs.getInt("test_record_count"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//4.1作业管理——创建模板查询
|
public List serchBattgroup(Object obj){
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
List list = null;
|
BattInf binf = bmd.getBinf();
|
Batt_maint_inf mainf = bmd.getMainf();
|
Batttestdata_inf tdata = bmd.getTdata();
|
User_inf uinf=bmd.getUinf();
|
String sql="";
|
String baseSql="select distinct(db_battinf.tb_battinf.BattgroupId),StationName1,StationName,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate "
|
+ ",maint_done_time"
|
+ ",test_record_count,test_starttime,test_type,test_curr,test_timelong,max_monvol,min_monvol,test_cap,data_new "
|
+",db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid,db_user.tb_user_inf.uId,db_user.tb_user_inf.uName "
|
+ "from db_battinf.tb_battinf "
|
+ "left outer join db_battinf.tb_batt_maint_inf on db_battinf.tb_battinf.BattgroupId=db_battinf.tb_batt_maint_inf.BattGroupId "
|
+ "left outer join db_batt_testdata.tb_batttestdata_inf on db_battinf.tb_battinf.BattgroupId=db_batt_testdata.tb_batttestdata_inf.BattGroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid=db_battinf.tb_battinf.BattgroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id " +
|
"left outer join db_user.tb_user_inf on db_user.tb_user_inf.uId=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
"where stationname1 like ? and stationname like ? and battproducer like ? and to_days(battproductdate)>=to_days(?) and to_days(battproductdate)<=to_days(?) and to_days(battinusedate)>=to_days(?) and to_days(battinusedate)<=to_days(?)";
|
|
|
//用于电池测试记录条件:
|
//放电/充电单个/有在线监测记录
|
String testSqlH="and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type=? and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?)) ";
|
//内阻/电导
|
String testSqlF="and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_battresdata_inf.BattGroupId ) from db_batt_testdata.tb_battresdata_inf where db_batt_testdata.tb_battresdata_inf.test_type=? and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?)) ";
|
//充电或者放电都行
|
String testSqlT="and db_battinf.tb_battinf.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?)) ";
|
//无记录
|
String testSqlN="and db_battinf.tb_battinf.BattGroupId not in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?)) " ;
|
//全部
|
String testSqlA="and db_battinf.tb_battinf.BattGroupId!=(select count(distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId )) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and to_days(record_time)>=to_days(?) and to_days(record_time)<=to_days(?)) " ;
|
if(tdata.getTest_type()==2){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==3){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==9){//有在线监测记录
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==5){
|
baseSql+=testSqlF;
|
}else if(tdata.getTest_type()==3000){//充电或放电
|
baseSql+=testSqlT;
|
}else if(tdata.getTest_type()==4000){//无记录
|
baseSql+=testSqlN;
|
}else if(tdata.getTest_type()==0){
|
baseSql+=testSqlA;
|
}
|
//System.out.println("#############333333");
|
//选取蓄电池组条件
|
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;
|
}
|
//System.out.println("#############444444");
|
|
//选取系统类型
|
String nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
//包机人
|
String userSqlF=" and db_user.tb_user_inf.uname=? and db_user.tb_user_inf.ubaojiusr=1 ";
|
if(uinf.getUName().equals("0")){
|
baseSql=baseSql;
|
}else{
|
baseSql+=userSqlF;
|
}
|
//用于测试类型
|
//全部
|
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 maintSqlT=" and db_battinf.tb_battinf.BattGroupId in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
//无维护记录
|
String maintSqlF=" and db_battinf.tb_battinf.BattGroupId not in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
//全部
|
String maintSqlA=" and db_battinf.tb_battinf.BattGroupId!=(select count(distinct(db_battinf.tb_batt_maint_inf.battgroupid )) from db_battinf.tb_batt_maint_inf where to_days(maint_done_time)>=to_days(?) and to_days(maint_done_time)<=to_days(?)) " ;
|
|
if(mainf.getRemark().equals("0")){
|
baseSql+=maintSqlF;
|
}else if(mainf.getRemark().equals("1")){
|
baseSql+=maintSqlT;
|
}else if(mainf.getRemark().equals("100")){
|
baseSql+=maintSqlA;
|
}
|
String endSql=" order by StationName1 asc,db_battinf.tb_battinf.BattgroupId asc,test_starttime asc ";
|
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
if(uinf.getUName().equals("0")){
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
"%" + binf.getStationName1() + "%",
|
"%" + binf.getStationName() + "%",
|
"%" + binf.getBattProducer() +"%",
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}else{
|
list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
"%" + binf.getStationName1() + "%",
|
"%" + binf.getStationName() + "%",
|
"%" + binf.getBattProducer() +"%",
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
uinf.getUName(),
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1()
|
},
|
new BattInfImpl());
|
}
|
return list;
|
}
|
|
//8.1放电计划管理中查询(旧版本)
|
public List serchBattgroupinfo1(Object obj){
|
BattInf binf = (BattInf) obj;
|
String sql="";
|
String baseSql="select distinct(db_battinf.tb_battinf.BattgroupId),StationName1,StationName,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate,groupIndexInFBSDevice "
|
+ "from db_battinf.tb_battinf "
|
+"where stationname1 like ? and stationname like ? and battproducer like ? and to_days(battproductdate)>=to_days(?) and to_days(battproductdate)<=to_days(?) and to_days(battinusedate)>=to_days(?) and to_days(battinusedate)<=to_days(?)";
|
|
//选取蓄电池组条件
|
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 nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
String endSql=" order by StationName1 asc,db_battinf.tb_battinf.BattgroupId asc ";
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
List<BattInf> list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {
|
"%" + binf.getStationName1() + "%",
|
"%" + binf.getStationName() + "%",
|
"%" + binf.getBattProducer() +"%",
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
},
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int id=0;
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;//去除重复项
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
return list;
|
}
|
|
//8.1放电计划管理中默认进图查询
|
/*public List serchBattgroupinfo(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select DISTINCT stationid,BattgroupId,StationName,StationName1,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate,groupIndexInFBSDevice " +
|
" FROM db_battinf.tb_battinf " +
|
" where StationId in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" )";
|
List<BattInf> list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {uinf.getUId()},new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int id=0;
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;//去除重复项
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
return list;
|
}*/
|
public List serchBattgroupinfo(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select distinct db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.stationid,StationName,StationName1,stationip,BattGroupName,BattGroupName1,battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate,groupIndexInFBSDevice " +
|
" 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=? ";
|
List<BattInf> list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] {uinf.getUId()},new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int id=0;
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;//去除重复项
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
return list;
|
}
|
|
//新版添加放电计划查询<排除设置过放电计划的电池组>
|
public List serchBattPlan(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select distinct db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.stationid,FBSDeviceId,StationName,StationName1,stationip,BattGroupName,BattGroupName1,db_battinf.tb_battinf.battgroupnum,moncount,moncapstd,monvolstd,battproducer,battproductdate,battinusedate,groupIndexInFBSDevice,load_curr "
|
+ " ,tb_fbs9100_setparam.HourRate,DisCurr,DisCap,DisTime,groupVol_Low,monomerVol_Low,monomerLowCount,tb_fbs9100_setparam.battGroupNum,onlineVolLowAction,dCVolHighLimit,chargeCurrSet " +
|
" from db_battinf.tb_battinf,db_ram_db.tb_fbs9100_setparam " +
|
" where StationName1 like ? and StationName2 like ? and StationName5 like ? and stationid like ? "
|
+ " and db_battinf.tb_battinf.FBSDeviceId=db_ram_db.tb_fbs9100_setparam.dev_id "
|
+ " and db_battinf.tb_battinf.StationId in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id "
|
+ " and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" ) "
|
+ " and db_battinf.tb_battinf.battgroupid not in (select distinct battgroupid from web_site.tb_battdischarge_plan where (discharge_state=0 or discharge_state=1 or discharge_state=3 ) and discharge_cleartype=1 ) "
|
+ " order by db_battinf.tb_battinf.battgroupid asc ";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%","%"+binf.getStationId()+"%",binf.getNum()}, 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.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
binf.setLoad_curr(rs.getFloat("load_curr"));
|
binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
|
Fbs9100_setparam fsetparam=new Fbs9100_setparam();
|
|
fsetparam.setHourRate(rs.getInt("HourRate"));
|
fsetparam.setDisCurr(rs.getFloat("DisCurr"));
|
fsetparam.setDisCap(rs.getFloat("DisCap"));
|
fsetparam.setDisTime(rs.getInt("DisTime"));
|
fsetparam.setGroupVol_Low(rs.getFloat("groupVol_Low"));
|
fsetparam.setMonomerVol_Low(rs.getFloat("monomerVol_Low"));
|
fsetparam.setMonomerLowCount(rs.getInt("monomerLowCount"));
|
fsetparam.setBattGroupNum(rs.getInt("tb_fbs9100_setparam.battGroupNum"));
|
fsetparam.setOnlineLowAction(rs.getInt("onlineVolLowAction"));
|
fsetparam.setDCVolHighLimit(rs.getFloat("dCVolHighLimit"));
|
fsetparam.setChargeCurrSet(rs.getFloat("chargeCurrSet"));
|
binf.setFparam(fsetparam);
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
|
|
//4.1根据维护区查询所有的电池组和对应的包机人(从模板创建)
|
public List serchByTemplate(Object obj){
|
User_task_batt_template utem=(User_task_batt_template) obj;
|
String sql="select distinct(tb_battinf.battgroupid) from db_battinf.tb_battinf " +
|
"where " ;
|
//用于维护区
|
String station1SqlT=" stationname1!=? ";//全部
|
String station1SqlF=" stationname1=? ";
|
if(utem.getQuyu_name().equals("")){
|
sql+=station1SqlT;
|
}else{
|
sql+=station1SqlF;
|
}
|
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{utem.getQuyu_name()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int id=0;
|
String uids="";
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//4.1根据维护区查询该维护区中还未加入到各类作业的电池组数(从模板创建)
|
public List serchBattNotInTask(Object obj){
|
User_task_batt_template utem=(User_task_batt_template) obj;
|
String sql="select distinct(tb_battinf.battgroupid),stationname1,battinusedate,db_user.tb_user_inf.uId from db_battinf.tb_battinf " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid=db_battinf.tb_battinf.BattgroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id " +
|
"left outer join db_user.tb_user_inf on db_user.tb_user_inf.uId=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
"where tb_battinf.battgroupid not in( ";
|
//用于ucheck和utest
|
String ucheckSql=" select distinct(db_user.tb_user_task_batt_check.battgroupid) from db_user.tb_user_task_batt_check,db_user.tb_user_task,db_battinf.tb_battinf " +
|
"where db_user.tb_user_task_batt_check.task_id=db_user.tb_user_task.task_id and task_type=? " +
|
"and db_user.tb_user_task_batt_check.battgroupid=tb_battinf.battgroupid and stationname1=? ) ";
|
String utestSql=" select distinct(db_user.tb_user_task_batt_test.battgroupid) from db_user.tb_user_task_batt_test ,db_user.tb_user_task,db_battinf.tb_battinf " +
|
"where db_user.tb_user_task_batt_test.task_id=db_user.tb_user_task.task_id and task_type=? " +
|
"and db_user.tb_user_task_batt_test.battgroupid=tb_battinf.battgroupid and stationname1=? ) ";
|
if(utem.getTask_type()==2){
|
sql+=ucheckSql;
|
}else{
|
sql+=utestSql;
|
}
|
|
//用于维护区
|
String station1SqlT=" and stationname1!=? ";//全部
|
String station1SqlF=" and stationname1=? ";
|
if(utem.getQuyu_name().equals("")){
|
sql+=station1SqlT;
|
}else{
|
sql+=station1SqlF;
|
}
|
//作业模板中选中执行人方式时设置执行人
|
String userSqlT=" and db_user.tb_user_inf.ubaojiusr=1 ";
|
String userSqlF=" and db_user.tb_user_inf.ubaojiusr!=100 ";
|
if(utem.getTask_exe_usr_selmode()==0){
|
sql+=userSqlT;
|
}else{
|
sql+=userSqlF;
|
}
|
//排序方式
|
String endSql="order by battgroupid ";
|
sql+=endSql;
|
//System.out.println("sql: "+sql);
|
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{utem.getTask_type(),utem.getQuyu_name(),utem.getQuyu_name()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<BattInf> list=new ArrayList();
|
int id=0;
|
String uids="";
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
if(id==rs.getInt("battGroupId")){
|
uids+=list.get(list.size()-1).getStationIp();
|
uids+=",";
|
uids+=Integer.toString(rs.getInt("uid"));
|
list.get(list.size()-1).setStationIp(uids);
|
uids="";
|
continue;
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setStationIp(Integer.toString(rs.getInt("uid")));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
if(list!=null&&list.size()>0){
|
for (int i = 0; i <list.size(); i++) {
|
//如果执行人模式是自动选择(包机人优先)，则将包机人变为执行人
|
if(utem.getTask_exe_usr_selmode()==0){
|
((BattInf) list.get(i)).setSignalName(((BattInf) list.get(i)).getStationIp());//执行人
|
}
|
((BattInf) list.get(i)).setBattProductDate1(utem.getTask_time());//执行日期
|
}
|
}
|
return list;
|
}
|
//模糊查询
|
public List serchByInfo(Object obj) {
|
BattInf binf = (BattInf) obj;
|
String sql = "select distinct(BattgroupId),StationName,StationName1,BattGroupName1,BattGroupName2 from db_battinf.tb_battinf where StationName like ? or StationName1 like ? or BattGroupName1 like ? or BattGroupName2 like ?";
|
List list = DAOHelper.executeQuery(
|
sql,
|
DBUtil.getConn(),
|
new Object[] { "%" + binf.getStationName() + "%",
|
"%" + binf.getStationName1() + "%",
|
|
"%" + binf.getBattGroupName2() + "%" }, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("StationName"));
|
batt.setStationName1(rs
|
.getString("StationName1"));
|
batt.setBattGroupName1(rs
|
.getString("BattGroupName1"));
|
batt.setBattGroupName2(rs
|
.getString("BattGroupName2"));
|
batt.setBattGroupId(rs.getInt("BattgroupId"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
});
|
return list;
|
}
|
//根据电池组id查询电池的基本信息
|
public List searchBattBybattgroupid(Object obj){
|
BattInf binf = (BattInf) obj;
|
//System.out.println(binf);
|
String sql="select DISTINCT(StationName),BattGroupId,BattGroupName,BattProducer,MonVolStd,MonCapStd,MonCount,BattProductDate,BattInUseDate from db_battinf.tb_battinf where BattGroupId=?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId()},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf b=new BattInf();
|
//System.out.println(rs.getString("StationName"));
|
b.setStationName(rs.getString("StationName"));
|
b.setBattGroupId(rs.getInt("BattGroupId"));
|
b.setBattGroupName(rs.getString("BattGroupName"));
|
b.setBattProducer(rs.getString("BattProducer"));
|
b.setMonVolStd(rs.getFloat("MonVolStd"));
|
b.setMonCapStd(rs.getFloat("MonCapStd"));
|
b.setMonCount(rs.getInt("MonCount"));
|
b.setBattProductDate(rs.getDate("BattProductDate"));
|
b.setBattInUseDate(rs.getDate("BattInUseDate"));
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
} );
|
}
|
//6.3会根据电池组名称查询电池id
|
|
public int serchByName(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select distinct(battgroupid) from db_battinf.tb_battinf where CONCAT_WS('-', StationName,BattGroupName)=? order by battgroupid";
|
List<BattInf> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupName()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int battgroupid=0;
|
if(list!=null&&list.size()>0){
|
battgroupid=(int)list.get(0).getBattGroupId();
|
}
|
return battgroupid;
|
}
|
|
//6.4数据统计报表查询(分地域查地域所在总电池数)
|
public List serchBylevel(int level) {
|
String sql="";
|
String baseSql="select stationname1,stationname2,stationname3,stationname4,moncount,count(distinct(db_battinf.tb_battinf.battgroupid)) as number " +
|
"from db_battinf.tb_battinf ";
|
|
String levelSqlO=" group by stationname1 ";//分组层次1
|
String levelSqlT=" group by stationname2,stationname1 ";//分组层次2
|
String levelSqlH=" group by stationname3,stationname2,stationname1 ";//分组层次3
|
String levelSqlF=" group by stationname4,stationname3,stationname2,stationname1 ";//分组层次4
|
if(level==1){
|
sql=baseSql+levelSqlO;
|
}else if(level==2){
|
sql=baseSql+levelSqlT;
|
}else if(level==3){
|
sql=baseSql+levelSqlH;
|
}else if(level==4){
|
sql=baseSql+levelSqlF;
|
}
|
String endSql=" order by db_battinf.tb_battinf.battgroupid ";
|
sql+=endSql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName2(rs.getString("stationName2"));
|
binf.setStationName3(rs.getString("stationName3"));
|
binf.setStationName4(rs.getString("stationName4"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonNum(rs.getInt("number"));//分组总数
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//6.4.7按地域和标称电压分组统计蓄电池组服役超期的数量
|
/*
|
* 区域层次放在stationid中*/
|
public List serchBeyondTime(Object obj){
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql="select stationname1,stationname2,stationname3 ,stationname4,monvolstd,count(distinct(battgroupid)) as sumNumber " +
|
"from db_battinf.tb_battinf where battinusedate<? ";
|
//分组限制条件(区域)
|
|
String levelSqlO=" group by stationname1 ";//分组层次1
|
String levelSqlT=" group by stationname2,stationname1 ";//分组层次2
|
String levelSqlH=" group by stationname3,stationname2,stationname1 ";//分组层次3
|
String levelSqlF=" group by stationname4,stationname3,stationname2,stationname1 ";//分组层次4
|
if(binf.getStationId().equals("1")){
|
baseSql=baseSql+levelSqlO;
|
}else if(binf.getStationId().equals("2")){
|
baseSql=baseSql+levelSqlT;
|
}else if(binf.getStationId().equals("3")){
|
baseSql=baseSql+levelSqlH;
|
}else if(binf.getStationId().equals("4")){
|
baseSql=baseSql+levelSqlF;
|
}
|
//分组限制条件(电压)
|
String volSql=" ,monvolstd ";
|
baseSql+=volSql;
|
//排序方式(按照区域排序)
|
String endSql1=" order by stationname1,monvolstd ";
|
String endSql2=" order by stationname2,stationname1,monvolstd ";
|
String endSql3=" order by stationname3,stationname2,stationname1,monvolstd ";
|
String endSql4=" order by stationname4,stationname3,stationname2,stationname1,monvolstd ";
|
if(binf.getStationId().equals("1")){
|
sql=baseSql+endSql1;
|
}else if(binf.getStationId().equals("2")){
|
sql=baseSql+endSql2;
|
}else if(binf.getStationId().equals("3")){
|
sql=baseSql+endSql3;
|
}else if(binf.getStationId().equals("4")){
|
sql=baseSql+endSql4;
|
}
|
System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattInUseDate()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<BattInf> list=new ArrayList<BattInf>();
|
int id=0;//标志符号
|
try {
|
while(rs.next()){
|
BattInf b=new BattInf();
|
b.setStationName1(rs.getString("stationName1"));
|
b.setStationName2(rs.getString("stationName2"));
|
b.setStationName3(rs.getString("stationName3"));
|
b.setStationName4(rs.getString("stationName4"));
|
b.setMonVolStd(rs.getFloat("monVolStd"));
|
b.setNum(rs.getInt("sumNumber"));
|
|
if(list.size()>0){
|
if(binf.getStationId().equals("1")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("2")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("3")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("4")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())
|
&&rs.getString("stationName4").equals(list.get(list.size()-1).getStationName4())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//6.4.8按地域和标称电压分组统计蓄电池组的数量(蓄电池组数量统计)
|
/*
|
* 区域层次放在stationid中*/
|
public List serchByMonVolStdGroup(Object obj){
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql="select stationname1,stationname2,stationname3 ,stationname4,monvolstd,count(distinct(battgroupid)) as sumNumber " +
|
"from db_battinf.tb_battinf where to_days(battinusedate)>to_days(?) and to_days(battinusedate)<to_days(?) ";
|
//分组限制条件(区域)
|
|
String levelSqlO=" group by stationname1 ";//分组层次1
|
String levelSqlT=" group by stationname2,stationname1 ";//分组层次2
|
String levelSqlH=" group by stationname3,stationname2,stationname1 ";//分组层次3
|
String levelSqlF=" group by stationname4,stationname3,stationname2,stationname1 ";//分组层次4
|
if(binf.getStationId().equals("1")){
|
baseSql=baseSql+levelSqlO;
|
}else if(binf.getStationId().equals("2")){
|
baseSql=baseSql+levelSqlT;
|
}else if(binf.getStationId().equals("3")){
|
|
baseSql=baseSql+levelSqlH;
|
}else if(binf.getStationId().equals("4")){
|
baseSql=baseSql+levelSqlF;
|
}
|
//分组限制条件(电压)
|
String volSql=" ,monvolstd ";
|
baseSql+=volSql;
|
//排序方式(按照区域排序)
|
String endSql1=" order by stationname1,monvolstd ";
|
String endSql2=" order by stationname2,stationname1,monvolstd ";
|
String endSql3=" order by stationname3,stationname2,stationname1,monvolstd ";
|
String endSql4=" order by stationname4,stationname3,stationname2,stationname1,monvolstd ";
|
if(binf.getStationId().equals("1")){
|
sql=baseSql+endSql1;
|
}else if(binf.getStationId().equals("2")){
|
sql=baseSql+endSql2;
|
}else if(binf.getStationId().equals("3")){
|
sql=baseSql+endSql3;
|
}else if(binf.getStationId().equals("4")){
|
sql=baseSql+endSql4;
|
}
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattInUseDate(),binf.getBattInUseDate1()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<BattInf> list=new ArrayList<BattInf>();
|
int id=0;//标志符号
|
try {
|
while(rs.next()){
|
BattInf b=new BattInf();
|
b.setStationName1(rs.getString("stationName1"));
|
b.setStationName2(rs.getString("stationName2"));
|
b.setStationName3(rs.getString("stationName3"));
|
b.setStationName4(rs.getString("stationName4"));
|
b.setMonVolStd(rs.getFloat("monVolStd"));
|
b.setNum(rs.getInt("sumNumber"));
|
|
if(list.size()>0){
|
if(binf.getStationId().equals("1")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("2")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("3")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("4")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())
|
&&rs.getString("stationName4").equals(list.get(list.size()-1).getStationName4())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//6.4.9按地域和标称电压分组统计蓄电池组品牌的数量(蓄电池供应商信息统计)
|
/*
|
* 区域层次放在stationid中*/
|
public List serchByBattProducerGroup(Object obj){
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql="select stationname1,stationname2 ,stationname3,stationname4,battproducer,count(distinct(battgroupid)) as sumNumber " +
|
"from db_battinf.tb_battinf where to_days(battinusedate)>to_days(?) and to_days(battinusedate)<to_days(?) ";
|
//分组限制条件(区域)
|
String groupSql1=" group by stationname1 ";
|
String groupSql2=" group by stationname2,stationname1 ";
|
String groupSql3=" group by stationname3,stationname2,stationname1 ";
|
String groupSql4=" group by stationname4,stationname3,stationname2,stationname1 ";
|
if(binf.getStationId().equals("1")){
|
baseSql+=groupSql1;
|
}else if(binf.getStationId().equals("2")){
|
baseSql+=groupSql2;
|
}else if(binf.getStationId().equals("3")){
|
baseSql+=groupSql3;
|
}else if(binf.getStationId().equals("4")){
|
baseSql+=groupSql4;
|
}
|
//分组限制条件(电压)
|
String volSql=" ,battproducer ";
|
baseSql+=volSql;
|
//排序方式(按照区域排序)
|
String endSql1=" order by stationname1,battproducer ";
|
String endSql2=" order by stationname2,stationname1,battproducer ";
|
String endSql3=" order by stationname3,stationname2,stationname1,battproducer ";
|
String endSql4=" order by stationname4,stationname3,stationname2,stationname1,battproducer ";
|
if(binf.getStationId().equals("1")){
|
sql=baseSql+endSql1;
|
}else if(binf.getStationId().equals("2")){
|
sql=baseSql+endSql2;
|
}else if(binf.getStationId().equals("3")){
|
sql=baseSql+endSql3;
|
}else if(binf.getStationId().equals("4")){
|
sql=baseSql+endSql4;
|
}
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattInUseDate(),binf.getBattInUseDate1()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<BattInf> list=new ArrayList<BattInf>();
|
int id=0;//标志符号
|
try {
|
while(rs.next()){
|
BattInf b=new BattInf();
|
b.setStationName1(rs.getString("stationName1"));
|
b.setStationName2(rs.getString("stationName2"));
|
b.setStationName3(rs.getString("stationName3"));
|
b.setStationName4(rs.getString("stationName4"));
|
b.setBattProducer(rs.getString("battproducer"));
|
b.setNum(rs.getInt("sumNumber"));
|
|
if(list.size()>0){
|
if(binf.getStationId().equals("1")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("2")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("3")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("4")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())
|
&&rs.getString("stationName4").equals(list.get(list.size()-1).getStationName4())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
});
|
return list;
|
}
|
|
//1 根据机房id查询电池组id排序最小值
|
public List serchByIdLow(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select distinct stationName,stationName1,stationName2,stationName3,stationName4,stationName5 from db_battinf.tb_battinf where stationid=? order by battgroupid asc limit 1 ";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf binf=new BattInf();
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName2(rs.getString("stationName2"));
|
binf.setStationName3(rs.getString("stationName3"));
|
binf.setStationName4(rs.getString("stationName4"));
|
binf.setStationName5(rs.getString("stationName5"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
//6.4.10按地域和标称电压分组统计蓄电池组使用时间(蓄电池投产年限统计)
|
/*
|
* 区域层次放在stationid中*/
|
public List serchByBattInUseDateGroup(Object obj){
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql="select stationname1,stationname2 ,stationname3,stationname4,battinusedate,count(distinct(battgroupid)) as sumNumber " +
|
"from db_battinf.tb_battinf where to_days(battinusedate)>to_days(?) and to_days(battinusedate)<to_days(?) ";
|
//分组限制条件(区域)
|
String groupSql1=" group by stationname1 ";
|
String groupSql2=" group by stationname2,stationname1 ";
|
String groupSql3=" group by stationname3,stationname2,stationname1 ";
|
String groupSql4=" group by stationname4,stationname3,stationname2,stationname1 ";
|
if(binf.getStationId().equals("1")){
|
baseSql+=groupSql1;
|
}else if(binf.getStationId().equals("2")){
|
baseSql+=groupSql2;
|
}else if(binf.getStationId().equals("3")){
|
baseSql+=groupSql3;
|
}else if(binf.getStationId().equals("4")){
|
baseSql+=groupSql4;
|
}
|
//分组限制条件(电压)
|
String volSql=" ,battinusedate ";
|
baseSql+=volSql;
|
//排序方式(按照区域排序)
|
String endSql1=" order by stationname1,battinusedate ";
|
String endSql2=" order by stationname2,stationname1,battinusedate ";
|
String endSql3=" order by stationname3,stationname2,stationname1,battinusedate ";
|
String endSql4=" order by stationname4,stationname3,stationname2,stationname1,battinusedate ";
|
if(binf.getStationId().equals("1")){
|
sql=baseSql+endSql1;
|
}else if(binf.getStationId().equals("2")){
|
sql=baseSql+endSql2;
|
}else if(binf.getStationId().equals("3")){
|
sql=baseSql+endSql3;
|
}else if(binf.getStationId().equals("4")){
|
sql=baseSql+endSql4;
|
}
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattInUseDate(),binf.getBattInUseDate1()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List<BattInf> list=new ArrayList<BattInf>();
|
int id=0;//标志符号
|
try {
|
while(rs.next()){
|
BattInf b=new BattInf();
|
b.setStationName1(rs.getString("stationName1"));
|
b.setStationName2(rs.getString("stationName2"));
|
b.setStationName3(rs.getString("stationName3"));
|
b.setStationName4(rs.getString("stationName4"));
|
b.setBattInUseDate(rs.getTimestamp("battInUseDate"));
|
b.setNum(rs.getInt("sumNumber"));
|
|
if(list.size()>0){
|
if(binf.getStationId().equals("1")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("2")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("3")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}else if(binf.getStationId().equals("4")){
|
if(rs.getString("stationName1").equals(list.get(list.size()-1).getStationName1())
|
&&rs.getString("stationName2").equals(list.get(list.size()-1).getStationName2())
|
&&rs.getString("stationName3").equals(list.get(list.size()-1).getStationName3())
|
&&rs.getString("stationName4").equals(list.get(list.size()-1).getStationName4())){
|
b.setBattGroupNum(list.get(list.size()-1).getBattGroupNum());
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
}
|
}else{
|
b.setBattGroupNum(id);
|
id+=1;
|
}
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
});
|
return list;
|
}
|
|
//9.1根据stationid查询机房电池组数和每组电池组中单体的个数(机房信息内容画图)
|
public List serchMonNum(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql=" select distinct db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.battgroupname ,db_battinf.tb_battinf.moncount,db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.moncapstd," +
|
" db_battinf.tb_battinf.battproductdate,db_battinf.tb_battinf.battinusedate " +
|
" from db_battinf.tb_battinf " +
|
" where db_battinf.tb_battinf.stationid=? " ;
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, 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.setBattGroupName(rs.getString("battGroupName"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setStationId(rs.getString("stationId"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//9.1根据电池组stationid查询电池组的信息(电池寿命管理)
|
public List serchByStationid(Object obj){
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql=" select distinct db_battinf.tb_battinf.battgroupid,db_battinf.tb_battinf.battgroupname ,db_battinf.tb_battinf.moncount,db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.moncapstd," +
|
" db_battinf.tb_battinf.battproductdate,db_battinf.tb_battinf.battinusedate,db_battinf.tb_battinf.BattProducer,db_battinf.tb_battinf.BattModel,db_battinf.tb_battinf.MonVolStd " +
|
" from db_battinf.tb_battinf " +
|
" where db_battinf.tb_battinf.stationid=? " ;
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, 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.setBattGroupName(rs.getString("battGroupName"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setBattProducer(rs.getString("BattProducer"));
|
binf.setBattModel(rs.getString("battModel"));
|
binf.setMonVolStd(rs.getFloat("MonVolStd"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setStationId(rs.getString("stationId"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
/**
|
* 根据机房id查询电池组
|
* @param obj
|
* @return
|
*/
|
public List searchBattByStationId(Object obj){
|
BattInf binf = (BattInf)obj;
|
String sql = "SELECT db_battinf.tb_battinf.BattGroupId,db_battinf.tb_battinf.StationId,db_battinf.tb_battinf.BattGroupName,db_battinf.tb_battinf.MonCapStd,db_battinf.tb_battinf.MonVolStd" +
|
",BattGuarantDayCount,BattInUseDate " +
|
" from db_battinf.tb_battinf where StationId = ?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationId()}, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List<BattInf> list = new ArrayList<BattInf>();
|
try {
|
while(rs.next()){
|
BattInf b = new BattInf();
|
b.setStationId(rs.getString("StationId"));
|
b.setBattGroupId(rs.getInt("BattGroupId"));
|
b.setBattGroupName(rs.getString("BattGroupName"));
|
b.setMonCapStd(rs.getFloat("MonCapStd"));
|
b.setMonVolStd(rs.getFloat("MonVolStd"));
|
int BattGuarantDayCount=rs.getInt("BattGuarantDayCount")-ActionUtil.daysBetween(rs.getDate("BattInUseDate"), new Date());//剩余保修天数
|
b.setBattGuarantDayCount(BattGuarantDayCount);
|
list.add(b);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
//根据battgroupid查询机房id
|
public int serchDev_id(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="SELECT DISTINCT db_battinf.tb_battinf.FBSDeviceId as dev_id from db_battinf.tb_battinf where battgroupid=? ";
|
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()){
|
int dev_id=rs.getInt("dev_id");
|
list.add(dev_id);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int dev_id=0;
|
if(list!=null&&list.size()>0){
|
dev_id=(Integer) list.get(list.size()-1);
|
}
|
return dev_id;
|
}
|
//查询总的单体数（电池健康率）
|
/*public int serchMonAll(){
|
String sql="select sum(moncount) as moncounts from db_battinf.tb_battinf 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 moncounts=rs.getInt("moncounts");
|
list.add(moncounts);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int moncounts=0;//单体总个数
|
if(list!=null&&list.size()>0){
|
moncounts=(Integer) list.get(list.size()-1);
|
}
|
return moncounts;
|
|
}*/
|
public int serchMonAll(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select sum(moncount) as moncounts " +
|
" 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=?" +
|
" limit 1";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
int moncounts=rs.getInt("moncounts");
|
list.add(moncounts);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int moncounts=0;//单体总个数
|
if(list!=null&&list.size()>0){
|
moncounts=(Integer) list.get(list.size()-1);
|
}
|
return moncounts;
|
|
}
|
//测试
|
public List searchStationId(Object obj){
|
BattInf binf = (BattInf)obj;
|
String sql = "SELECT * " +
|
" from db_battinf.tb_battinf where StationId = ?";
|
return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationId()},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.setStationId(rs.getString("stationId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName2(rs.getString("stationName2"));
|
|
binf.setStationName3(rs.getString("stationName3"));
|
|
binf.setStationName4(rs.getString("stationName4"));
|
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
|
binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
binf.setFbsDeviceIp(rs.getString("fbsDeviceIp"));
|
binf.setFBSDeviceName(rs.getString("fBSDeviceName"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
|
}
|
|
//查询所有的机房
|
public List serchAllStation(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select DISTINCT stationid,StationName,StationName1,StationName2,StationName3,StationName4,StationName5,StationName6,StationName7,StationName8,StationName9,fBSDeviceId " +
|
" FROM db_battinf.tb_battinf " +
|
" where StationId in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" )";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
String conStr="-";//连接机房名称的符号
|
try {
|
while(rs.next()){
|
BattInf batt = new BattInf();
|
String stationName="";
|
if(!rs.getString("StationName1").trim().equals("")){
|
stationName+=rs.getString("StationName1");
|
}
|
if(!rs.getString("StationName2").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName2");
|
}
|
if(!rs.getString("StationName3").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName3");
|
}
|
if(!rs.getString("StationName4").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName4");
|
}
|
if(!rs.getString("StationName5").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName5");
|
}
|
if(!rs.getString("StationName6").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName6");
|
}
|
if(!rs.getString("StationName7").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName7");
|
}
|
if(!rs.getString("StationName8").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName8");
|
}
|
if(!rs.getString("StationName9").trim().equals("")){
|
stationName+=conStr;
|
stationName+=rs.getString("StationName9");
|
}
|
batt.setStationName(stationName);
|
batt.setStationId(rs.getString("StationId"));
|
batt.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
} );
|
return list;
|
}
|
//根据机房id查询机房下的电池组信息
|
public List serchBattByStation(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql="select DISTINCT StationName,StationName1,StationName2,StationName3,StationName4,StationName5,StationId,StationIP,FBSDeviceId,BattGroupName,BattGroupName1,BattGroupName2,MonCount,battgroupId,MonCapStd,MonVolStd,MonResStd,MonSerStd,BattProducer,BattInUseDate,GroupIndexInFBSDevice,Load_curr,DisCurrMax " +
|
"from db_battinf.tb_battinf where stationid=? " +
|
"ORDER BY StationName1,battgroupId ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binf.getStationId()},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
String conStr="-";//连接机房名称的符号
|
try {
|
while (rs.next()) {
|
BattInf batt = new BattInf();
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
batt.setStationName2(rs.getString("stationName2"));
|
batt.setStationName3(rs.getString("stationName3"));
|
batt.setStationName4(rs.getString("stationName4"));
|
batt.setStationName5(rs.getString("stationName5"));
|
batt.setStationId(rs.getString("StationId"));
|
batt.setStationIp(rs.getString("StationIP"));
|
batt.setBattGroupName(rs.getString("BattGroupName").trim());
|
batt.setBattGroupName1(rs.getString("BattGroupName1").trim());
|
batt.setBattGroupName2(rs.getString("BattGroupName2").trim());
|
batt.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
batt.setMonCount(rs.getInt("MonCount"));
|
batt.setBattGroupId(rs.getInt("battgroupId"));
|
batt.setMonCapStd(rs.getFloat("monCapStd"));
|
batt.setMonVolStd(rs.getFloat("monVolStd"));
|
batt.setMonResStd(rs.getFloat("MonResStd"));
|
batt.setMonSerStd(rs.getFloat("MonSerStd"));
|
batt.setBattProducer(rs.getString("BattProducer"));
|
batt.setBattInUseDate(rs.getDate("BattInUseDate"));
|
batt.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
batt.setLoad_curr(rs.getFloat("load_curr"));
|
batt.setDisCurrMax(rs.getFloat("disCurrMax"));
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//搜索机房或电池组
|
public List serchStationOrBattgroup(Object obj){
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
//机房
|
String baseSqlT="select DISTINCT stationid,StationName,StationName1 " +
|
"FROM db_battinf.tb_battinf " +
|
"where ";
|
//电池组
|
String baseSqlF="select DISTINCT stationid,battgroupid,battgroupname,StationName,StationName1 " +
|
"FROM db_battinf.tb_battinf " +
|
"where ";
|
String conditionSqlT=" stationname like ? ";//机房
|
String conditionSqlF=" battgroupname like ? ";//电池组
|
if(binf.getNum()==1){
|
sql=baseSqlT+conditionSqlT;
|
}else{
|
sql=baseSqlF+conditionSqlF;
|
}
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName9()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattInf batt=new BattInf();
|
if(binf.getNum()==1){
|
batt.setStationId(rs.getString("stationId"));
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
}else{
|
batt.setStationId(rs.getString("stationId"));
|
batt.setBattGroupId(rs.getInt("battGroupId"));
|
batt.setBattGroupName(rs.getString("battGroupName"));
|
batt.setStationName(rs.getString("stationName"));
|
batt.setStationName1(rs.getString("stationName1"));
|
}
|
|
list.add(batt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}
|
//查询所有的电池组数
|
public int serchAllBatt(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select count(distinct battgroupid) as nums from db_battinf.tb_battinf " +
|
" where battgroupid in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" ) " +
|
" and stationid in(" +
|
" select distinct db_user.tb_user_battgroup_baojigroup_battgroup.stationid from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +
|
" ) " +
|
" limit 1";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId(),uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
int nums=rs.getInt("nums");
|
list.add(nums);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int nums=0;//总数
|
if(list!=null&&list.size()>0){
|
nums=(Integer) list.get(list.size()-1);
|
}
|
return nums;
|
}
|
|
//根据维护区和机房名称查询电池组信息(电池组信息配置)<-------跨域----------->
|
public List searchInform_ky(){
|
String sql="select distinct(StationId),StationId_ex,StationName,StationName1,StationName2,StationName3,StationName4,StationName5,StationIp,FBSDeviceId,FbsDeviceIp_YM,FbsDeviceIp_WG,FbsDeviceIp,"
|
+ "FBSDeviceName,GroupIndexInFBSDevice,battgroupId,BattGroupNum,BattGroupName,FloatVolLevel,OfflineVolLevel,BattFloatCurrent" +
|
",MonCount,MonCapStd,monVolStd,monResStd,MonSerStd,MonVolLowToAvg,BattProducer,BattModel,BattProductDate,BattInUseDate,battGuarantDayCount,SignalId,CInterFaceId,Load_curr,install_user,DisCurrMax,SignalName,Station_install,station_phone " +
|
" From db_battinf.tb_battinf " +
|
" order by StationId ";
|
List<BattInf> list=DAOHelper.executeQueryLimit(sql,DBUtil.getConn(), null,new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
int id=0;
|
|
try {
|
while (rs.next()) {
|
BattInf binf = new BattInf();
|
binf.setStationId(rs.getString("stationId"));
|
binf.setStationId_ex(rs.getString("stationId_ex"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationName2(rs.getString("stationName2"));
|
binf.setStationName3(rs.getString("stationName3"));
|
binf.setStationName4(rs.getString("stationName4"));
|
binf.setStationName5(rs.getString("stationName5"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
|
binf.setFbsDeviceIp(rs.getString("fbsDeviceIp"));
|
|
binf.setFbsDeviceIp_YM(rs.getString("fbsDeviceIp_YM"));
|
|
binf.setFbsDeviceIp_WG(rs.getString("fbsDeviceIp_WG"));
|
|
binf.setFBSDeviceName(rs.getString("fBSDeviceName"));
|
binf.setGroupIndexInFBSDevice(rs.getInt("groupIndexInFBSDevice"));
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setFloatVolLevel(rs.getFloat("floatVolLevel"));
|
binf.setOfflineVolLevel(rs.getFloat("offlineVolLevel"));
|
binf.setBattFloatCurrent(rs.getFloat("battFloatCurrent"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setMonResStd(rs.getFloat("monResStd"));
|
binf.setMonSerStd(rs.getFloat("monSerStd"));
|
binf.setMonVolLowToAvg(rs.getFloat("monVolLowToAvg"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattModel(rs.getString("battModel"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
binf.setBattGuarantDayCount(rs.getInt("battGuarantDayCount"));
|
binf.setSignalId(rs.getString("signalId"));
|
binf.setCInterFaceId(rs.getInt("cInterFaceId"));
|
binf.setStation_install(rs.getInt("station_install"));
|
binf.setLoad_curr(rs.getFloat("load_curr"));
|
binf.setInstall_user(rs.getString("install_user"));
|
binf.setDisCurrMax(rs.getFloat("disCurrMax"));
|
binf.setStation_phone(rs.getString("station_phone"));
|
list.add(binf);
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
public static void main(String[] args) throws ParseException {
|
//new BattInfImpl().searchAll();
|
|
BattInfImpl biml = new BattInfImpl();
|
BattInf binf = new BattInf();
|
binf.setNum(1002);
|
binf.setBattGroupId(0);
|
binf.setStationName("内蒙古");
|
binf.setStationName1("");
|
binf.setStationName2("");
|
binf.setStationName5("");
|
binf.setStationId("");
|
|
User_inf uinf=new User_inf();
|
uinf.setUId(1002);
|
|
Page p=new Page();
|
p.setPageCurr(1);
|
p.setPageSize(50);
|
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Date date1 = sdf.parse("2000-01-01");
|
Date date2 = sdf.parse("2020-01-01");
|
Batttestdata_inf tdata = new Batttestdata_inf();
|
tdata.setTest_type(3);
|
tdata.setTest_starttype(3);
|
tdata.setRecord_time(ActionUtil.getSimpDate(date1));
|
tdata.setRecord_time1(ActionUtil.getSimpDate(date2));
|
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
bmd.setBinf(binf);
|
bmd.setPage(p);
|
bmd.setTdata(tdata);
|
bmd.setUinf(uinf);
|
//List<BattInf> list = biml.serchByConditionNew(bmd);
|
//List<BattInf> list=biml.serchByTestType(bmd);
|
/*List<BattInf> list=biml.serchGroupAssess(bmd);
|
for (BattInf b : list) {
|
System.out.println(b);
|
}*/
|
List<BattInf> list=biml.serchBattPlan(binf);
|
for (BattInf b : list) {
|
System.out.println(b.getBattGroupId()+" "+b.getFBSDeviceId()+" "+b.getFparam());
|
}
|
System.out.println(list.size());
|
}
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList<BattInf>();
|
int id=0;
|
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
BattInf binf = new BattInf();
|
Batt_maint_inf mainf = new Batt_maint_inf();
|
Batttestdata_inf tdata = new Batttestdata_inf();
|
Page p=new Page();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
if(id==rs.getInt("battGroupId")){
|
continue;
|
}else{
|
id=rs.getInt("battGroupId");
|
}
|
binf.setStationId(rs.getString("stationId"));
|
|
binf.setStationName(rs.getString("stationName"));
|
binf.setStationName1(rs.getString("stationName1"));
|
binf.setStationIp(rs.getString("stationIp"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setBattGroupName1(rs.getString("battGroupName1"));
|
binf.setBattGroupNum(rs.getInt("battGroupNum"));
|
binf.setMonCount(rs.getInt("monCount"));
|
binf.setMonCapStd(rs.getFloat("monCapStd"));
|
binf.setMonVolStd(rs.getFloat("monVolStd"));
|
binf.setBattProducer(rs.getString("battProducer"));
|
binf.setBattProductDate(rs.getDate("battProductDate"));
|
binf.setBattInUseDate(rs.getDate("battInUseDate"));
|
|
//minf.setFault_type(rs.getString("fault_type"));
|
if (rs.getString("maint_done_time") != null)
|
mainf.setMaint_done_time(DAOHelper.sdf.parse(rs
|
.getString("maint_done_time")));
|
|
tdata.setBattGroupId(rs.getInt("battGroupId"));
|
tdata.setTest_record_count(rs.getInt("test_record_count"));
|
|
if (rs.getString("test_starttime") != null)
|
tdata.setTest_starttime(DAOHelper.sdf.parse(rs
|
.getString("test_starttime")));
|
|
tdata.setTest_type(rs.getInt("test_type"));
|
tdata.setTest_curr(rs.getFloat("test_curr"));
|
tdata.setTest_timelong(rs.getInt("test_timelong"));
|
tdata.setMax_monvol(rs.getFloat("max_monvol"));
|
tdata.setMin_monvol(rs.getFloat("min_monvol"));
|
tdata.setTest_cap(rs.getFloat("test_cap"));
|
tdata.setData_new(rs.getInt("data_new"));
|
|
User_inf uinf=new User_inf();
|
uinf.setUId(rs.getInt("uId"));
|
uinf.setUSnId(Integer.toString(rs.getInt("battgroupid")));
|
uinf.setUName(rs.getString("uName"));
|
|
|
bmd.setBinf(binf);
|
bmd.setMainf(mainf);
|
bmd.setTdata(tdata);
|
bmd.setPage(p);
|
bmd.setUinf(uinf);
|
list.add(bmd);
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
}
|
