package com.fgkj.dao.impl;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.fgkj.actions.ActionUtil;
|
import com.fgkj.dao.AlarmDaoFactory;
|
import com.fgkj.dao.BaseDAO;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.LimitNumberFactory;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.IDatabaseName;
|
import com.fgkj.dto.BadBatt_mon;
|
import com.fgkj.dto.BattDischarge_plan;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.BattMap_information;
|
import com.fgkj.dto.Batt_Maint_Dealarm;
|
import com.fgkj.dto.Batt_devalarm_data;
|
import com.fgkj.dto.Batt_endurance;
|
import com.fgkj.dto.Batt_replace;
|
import com.fgkj.dto.Battalarm_data;
|
import com.fgkj.dto.Bts_station_event;
|
import com.fgkj.dto.Deverror_record;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.User_inf;
|
import com.fgkj.dto.ram.Bts_station_state;
|
import com.fgkj.dto.ram.Fbs9100_state;
|
|
public class BattMap_informationImpl implements BaseDAO,CallBack{
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setStationName1(rs.getString("stationName1"));
|
binformation.setStationName2(rs.getString("stationName2"));
|
binformation.setStationName3(rs.getString("stationName3"));
|
binformation.setStationName5(rs.getString("stationName5"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
binformation.setMonvolstd(rs.getFloat("monvolstd")); //表明设备是2V还是12V
|
binformation.setBattmodel(rs.getString("battmodel"));
|
binformation.setBattproducer(rs.getString("battproducer"));
|
binformation.setLoad_curr(rs.getFloat("load_curr"));
|
binformation.setOther(rs.getString("other"));
|
binformation.setBattGroupNum(rs.getInt("battGroupNum"));
|
binformation.setStation_install(rs.getInt("station_install"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
//9.1录入机房信息
|
public boolean add(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="insert into web_site.tb_battmap_information(StationId,StationName,address,longitude,latitude) values(?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getStationId(),binformation.getStationName(),
|
binformation.getAddress(),binformation.getLongitude(),binformation.getLatitude()});
|
return bl;
|
}
|
//9.1录入机房信息(手机端)
|
public boolean replace(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql=" replace into web_site.tb_battmap_information(StationId,StationName,address,longitude,latitude) values(?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getStationId(),binformation.getStationName(),
|
binformation.getAddress(),binformation.getLongitude(),binformation.getLatitude()});
|
return bl;
|
}
|
public boolean update(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="update web_site.tb_battmap_information set StationId=?,StationName=?,address=?,longitude=?,latitude=?,information=? where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getStationId(),binformation.getStationName(),
|
binformation.getAddress(),binformation.getLongitude(),binformation.getLatitude(),binformation.getInformation(),binformation.getNum()});
|
return bl;
|
}
|
|
public boolean del(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
//System.out.println(binformation);
|
String sql="delete from web_site.tb_battmap_information where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{binformation.getNum()});
|
return bl;
|
}
|
|
//9.1百度地图定位查询电池组信息
|
public List searchAll() {
|
String sql="select num,StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//9.1查询未添加物理信息的机房
|
public List serchNotInBattMap() {
|
String sql="select distinct tb_battinf.stationid,tb_battinf.stationname from db_battinf.tb_battinf " +
|
"where tb_battinf.stationid not in(select web_site.tb_battmap_information.stationid from web_site.tb_battmap_information )";
|
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.setStationId(rs.getString("StationId"));
|
binf.setStationName(rs.getString("stationName"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//9.1查询未添加物理信息的机房(用户管理的)
|
public List serchNotInBattMapByUid(Object obj) {
|
User_inf uinf=(User_inf) obj;
|
String sql="select distinct db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_battinf.tb_battinf.fBSDeviceId " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? " +
|
" and db_battinf.tb_battinf.stationid not in(select web_site.tb_battmap_information.stationid from web_site.tb_battmap_information )";
|
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()){
|
BattInf binf=new BattInf();
|
binf.setStationId(rs.getString("StationId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
list.add(binf);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//9.1查询已经添加物理信息的机房(用户管理的,l查询全部打开页面)
|
public List serchInBattMapByUid(Object obj) {
|
User_inf uinf=(User_inf) obj;
|
String sql="select distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information"
|
+ ",db_battinf.tb_battinf.FBSDeviceId,db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName2,db_battinf.tb_battinf.StationName3,db_battinf.tb_battinf.StationName5 " +
|
" from web_site.tb_battmap_information,db_battinf.tb_battinf " +
|
" where tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
|
" and tb_battmap_information.StationId in (" +
|
" select distinct db_battinf.tb_battinf.stationid " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? ) ";
|
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()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
binformation.setMonvolstd(rs.getFloat("monvolstd")); //表明设备是2V还是12V
|
binformation.setStationName1(rs.getString("stationName1"));
|
binformation.setStationName2(rs.getString("stationName2"));
|
binformation.setStationName3(rs.getString("stationName3"));
|
binformation.setStationName5(rs.getString("stationName5"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
} );
|
return list;
|
}
|
//9.1查询已经添加物理信息的机房(用户管理的，分页刷新)
|
public List serchInBattMapByUid2(Object obj) {
|
Page page=(Page) obj;
|
int uid=page.getPageAll();
|
Connection conn=DBUtil.getConn();
|
String numberSql="SELECT FOUND_ROWS() number";//存放总数
|
String sql="select SQL_CALC_FOUND_ROWS distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information,db_battinf.tb_battinf.FBSDeviceId,db_battinf.tb_battinf.station_install,db_battinf.tb_battinf.monvolstd " +
|
" from web_site.tb_battmap_information,db_battinf.tb_battinf " +
|
" where tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
|
" and tb_battmap_information.StationId in (" +
|
" select distinct db_battinf.tb_battinf.stationid " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? and tb_battinf.station_install=true) " +
|
" order by tb_battmap_information.StationId "+
|
" limit ?,? " ;
|
List<BattMap_information> list=DAOHelper.executeQueryLimit(sql, conn,new Object[]{uid,(page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
binformation.setInformation(rs.getString("station_install"));
|
binformation.setMonvolstd(rs.getFloat("monvolstd")); //表明设备是2V还是12V
|
Page p=new Page();
|
binformation.setPage(p);
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
} );
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
//System.out.println("number: "+number);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).getPage().setPageAll(number);
|
}
|
return list;
|
}
|
//9.1查询已经添加物理信息的机房(用户管理的，3.根据具体id查询告警信息等)
|
public List serchInBattMapByUid3(Object obj) {
|
BattMap_information binfor=(BattMap_information) obj;
|
String sql="select distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information,db_battinf.tb_battinf.FBSDeviceId,tb_battinf.stationName1,tb_battinf.stationName2,tb_battinf.stationName3,tb_battinf.stationName5 " +
|
" from web_site.tb_battmap_information,db_battinf.tb_battinf " +
|
" where tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
|
" and tb_battmap_information.StationId in (" +
|
" select distinct db_battinf.tb_battinf.stationid " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? ) " +
|
" and tb_battmap_information.StationId=? ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binfor.getNum(),binfor.getStationId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setStationName1(rs.getString("stationName1"));
|
binformation.setStationName2(rs.getString("stationName2"));
|
binformation.setStationName3(rs.getString("stationName3"));
|
binformation.setStationName5(rs.getString("stationName5"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
binformation.setFBSDeviceId(rs.getInt("fBSDeviceId"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
} );
|
return list;
|
}
|
//9.1查询机房所在的所有省份
|
public List serchStationName1(){
|
String sql=" SELECT DISTINCT substring_index(substring_index(address,'-',2),'-',1) as stationname1 FROM web_site.tb_BattMap_information " ;
|
//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()){
|
String stationname1=rs.getString("stationname1");
|
list.add(stationname1);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//9.1根据省份查询机房所在的所有城市
|
public List serchStationName2(Object obj){
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql=" SELECT DISTINCT substring_index(substring_index(address,'-',2),'-',-1) as stationname2 FROM web_site.tb_BattMap_information" +
|
" where address like ? " ;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, 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;
|
}
|
|
//9.1根据省份和城市查询机房所在的所有机房
|
public List serchStationName(Object obj){
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql=" SELECT DISTINCT StationName,longitude,latitude FROM web_site.tb_BattMap_information " +
|
"WHERE address like ? ";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//9.1地图上根据维护区查询机房经纬度
|
public List serchByInfo(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="select num, StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
|
" where 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()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//地图上根据机房名称查询经纬度
|
public List serchTudeByStationName(Object obj){
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="select num, StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
|
" where StationName like ?";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationName()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//9.1百度地图定位根据省份查询所有该区域的机房
|
public List serchByCondition(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="select num,StationId,StationName,address,longitude,latitude,information from web_site.tb_battmap_information " +
|
" where address like ? ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binformation.getAddress()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattMap_information binformation=new BattMap_information();
|
binformation.setNum(rs.getInt("num"));
|
binformation.setStationId(rs.getString("stationId"));
|
binformation.setStationName(rs.getString("stationName"));
|
binformation.setAddress(rs.getString("address"));
|
binformation.setLongitude(rs.getDouble("longitude"));
|
binformation.setLatitude(rs.getDouble("latitude"));
|
binformation.setInformation(rs.getString("information"));
|
list.add(binformation);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//9.1首页上查询已经添加物理信息的机房(用户管理的,根据12项查询条件筛选)
|
public List serchBattInMapFilter(Object obj,int condition) {
|
String sql="";
|
String baseSql="select distinct tb_battmap_information.num,tb_battmap_information.StationId,tb_battmap_information.StationName,address,longitude,latitude,information"
|
+ ",db_battinf.tb_battinf.FBSDeviceId,db_battinf.tb_battinf.monvolstd,db_battinf.tb_battinf.BattGroupNum,db_battinf.tb_battinf.battmodel,db_battinf.tb_battinf.battproducer,db_battinf.tb_battinf.load_curr"
|
+ ",db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName2,db_battinf.tb_battinf.StationName3,db_battinf.tb_battinf.StationName5,db_battinf.tb_battinf.station_install " ;
|
|
String fromsql=" from web_site.tb_battmap_information,db_battinf.tb_battinf,"
|
+ "(select distinct db_battinf.tb_battinf.stationid " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? ) as baoji ";
|
//基础条件
|
String condition_sql=" where tb_battmap_information.StationId=db_battinf.tb_battinf.StationId " +
|
" and tb_battmap_information.StationId=baoji.stationid ";
|
//机房筛选条件
|
String stationSql=" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname2 like ? and db_battinf.tb_battinf.stationname5 like ? ";
|
//12种条件+续航时间的筛选+设备温度告警
|
String one_sql=",db_battinf.tb_battinf.monvolstd as other ";
|
String vol_sqlF=" and db_battinf.tb_battinf.monvolstd=? ";//按照2V/12V筛选:1
|
String vol_sqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//按照2V/12V筛选:1
|
|
String two_sql=",db_battinf.tb_battinf.BattGroupNum as other ";
|
String group_sqlF=" and db_battinf.tb_battinf.BattGroupNum=? ";//按照机房下电池组数筛选:2
|
String group_sqlT=" and db_battinf.tb_battinf.BattGroupNum!=? ";//按照机房下电池组数筛选:2
|
|
String three_sql=",db_battinf.tb_battinf.load_curr as other ";
|
String realCurr_sql=" and db_battinf.tb_battinf.load_curr>=? and db_battinf.tb_battinf.load_curr<=? ";//按照实际负载电流:3
|
|
String four_sql=",realcap.caps as other ";
|
String realcap=",(select distinct stationid,ROUND(sum(real_cap_group1+real_cap_group2),3) as caps from web_site.tb_batt_endurance "
|
+ " group by stationid having caps>=? and caps<=?) as realcap ";
|
String realcap_sql=" and tb_battmap_information.StationId=realcap.stationid ";//实际容量范围筛选：4
|
|
String five_sql=",badbatt.number as other ";
|
String badbatt=",(select DISTINCT stationid,sum(batt_bad_num.mon_nums) as number,sum(db_battinf.tb_battinf.moncount) as counts"
|
+ " from db_battinf.tb_battinf,"
|
+ " (select DISTINCT battgroupid,COUNT(mon_num) as mon_nums from web_site.tb_badbatt_mon GROUP BY battgroupid) as batt_bad_num "
|
+ " where db_battinf.tb_battinf.BattGroupId=batt_bad_num.battgroupid "
|
+ " GROUP BY stationid) as badbatt ";
|
String badbatt_sql=" and tb_battmap_information.StationId=badbatt.stationid and badbatt.number>=(?*counts*0.01) ";//落后单体数范围筛选：5
|
|
String six_sql=",alarm.alm_id as other ";
|
String alarm=",(select distinct stationid,alm_id from db_battinf.tb_battinf,db_alarm.tb_battalarm_data where db_battinf.tb_battinf.BattGroupId=db_alarm.tb_battalarm_data.battgroupid) "
|
+ " as alarm ";
|
String alarm_sqlF=" and tb_battmap_information.StationId=alarm.stationid and alarm.alm_id=? ";//电池实时告警类型：6
|
String alarm_sqlT=" and tb_battmap_information.StationId=alarm.stationid and alarm.alm_id!=? ";//电池实时告警类型：6
|
|
String seven_sql=",db_alarm.tb_devalarm_data.alm_type as other ";
|
String dev=",db_alarm.tb_devalarm_data ";
|
String dev_sqlF=" and db_battinf.tb_battinf.FBSDeviceId=db_alarm.tb_devalarm_data.dev_id and db_alarm.tb_devalarm_data.alm_type=? ";//设备实时告警类型：7
|
String dev_sqlT=" and db_battinf.tb_battinf.FBSDeviceId=db_alarm.tb_devalarm_data.dev_id and db_alarm.tb_devalarm_data.alm_type!=? ";//设备实时告警类型：7
|
|
String eight_sql=",db_battinf.tb_deverror_record.error_state as other ";
|
String deverror=",db_battinf.tb_deverror_record ";
|
String deverror_sqlF=" and tb_battmap_information.StationId=db_battinf.tb_deverror_record.StationId and db_battinf.tb_deverror_record.error_state=? ";//机房故障记录筛选：8
|
String deverror_sqlT=" and tb_battmap_information.StationId=db_battinf.tb_deverror_record.StationId and db_battinf.tb_deverror_record.error_state!=? ";//机房故障记录筛选：8
|
|
String nine_sql=",db_ram_db.tb_fbs9100_state.dev_version as other ";
|
String version=",db_ram_db.tb_fbs9100_state ";
|
String version_sql=" and db_battinf.tb_battinf.FBSDeviceId=db_ram_db.tb_fbs9100_state.dev_id and db_ram_db.tb_fbs9100_state.dev_version like ? ";//设备版本号:9
|
|
String ten_sql=",event.nums as other ";
|
String state=",(select distinct station_id,count(station_id) as nums from db_alarm.tb_bts_station_event "
|
+ " where db_alarm.tb_bts_station_event.station_event_type=? and db_alarm.tb_bts_station_event.station_event_trig=2 "
|
+ " and db_alarm.tb_bts_station_event.record_datetime>=? and db_alarm.tb_bts_station_event.record_datetime<=? "
|
+ " group by station_id HAVING nums >?) as event ";
|
String power_off_sql=" and tb_battmap_information.StationId=event.station_id ";//停电记录：10
|
String diaozhan_sql=" and tb_battmap_information.StationId=event.station_id ";//掉站记录:11
|
|
String twelve_sql=",web_site.tb_batt_replace.replaced_moncount as other ";
|
String repalce=",web_site.tb_batt_replace ";
|
String repalce_sql=" and tb_battmap_information.StationId=web_site.tb_batt_replace.StationId and replaced_time>=? and replaced_time<=? and replaced_moncount>=? ";//更换记录：12
|
|
String thirteen_sql=",web_site.tb_batt_endurance.endurance_actual_timelong as other ";
|
String endurance_time=",web_site.tb_batt_endurance ";
|
String endurance_time_sql=" and tb_battmap_information.StationId=web_site.tb_batt_endurance.stationid and endurance_actual_timelong>=? and endurance_actual_timelong<=? ";
|
|
String fourteen_sql=",db_ram_db.tb_fbs9100_state.dev_temp as other ";
|
String temp=",db_ram_db.tb_fbs9100_state ";
|
String temp_sql=" and db_battinf.tb_battinf.FBSDeviceId=db_ram_db.tb_fbs9100_state.dev_id and db_ram_db.tb_fbs9100_state.dev_temp>=? and db_ram_db.tb_fbs9100_state.dev_temp<=? ";//设备温度：14
|
|
//排序
|
String endSql=" order by tb_battmap_information.StationId asc";
|
List list=new ArrayList();
|
switch (condition) {
|
case 1:
|
BattInf binf=(BattInf) obj;
|
if(binf.getMonVolStd()==0){
|
sql=baseSql+one_sql+fromsql+condition_sql+stationSql+vol_sqlT;
|
}else{
|
sql=baseSql+one_sql+fromsql+condition_sql+stationSql+vol_sqlF;
|
}
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binf.getNum(),"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%",binf.getMonVolStd()}, new BattMap_informationImpl());
|
break;
|
case 2:
|
BattInf binf2=(BattInf) obj;
|
if(binf2.getBattGroupNum()==0){
|
sql=baseSql+two_sql+fromsql+condition_sql+stationSql+group_sqlT;
|
}else{
|
sql=baseSql+two_sql+fromsql+condition_sql+stationSql+group_sqlF;
|
}
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binf2.getNum(),"%"+binf2.getStationName1()+"%","%"+binf2.getStationName2()+"%","%"+binf2.getStationName5()+"%",binf2.getBattGroupNum()}, new BattMap_informationImpl());
|
break;
|
case 3:
|
BattInf binf3=(BattInf) obj;
|
sql=baseSql+three_sql+fromsql+condition_sql+stationSql+realCurr_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{binf3.getNum(),"%"+binf3.getStationName1()+"%","%"+binf3.getStationName2()+"%","%"+binf3.getStationName5()+"%",binf3.getLoad_curr(),binf3.getLoad_curr1()}, new BattMap_informationImpl());
|
break;
|
case 4:
|
Batt_endurance endurance=(Batt_endurance) obj;
|
sql=baseSql+four_sql+fromsql+realcap+condition_sql+stationSql+realcap_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{endurance.getNum(),"%"+endurance.getStationName1()+"%","%"+endurance.getStationName2()+"%","%"+endurance.getStationName5()+"%",endurance.getReal_cap_min(),endurance.getReal_cap_max()}, new BattMap_informationImpl());
|
break;
|
case 5:
|
BadBatt_mon bad=(BadBatt_mon) obj;
|
sql=baseSql+five_sql+fromsql+badbatt+condition_sql+stationSql+badbatt_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{bad.getNum(),"%"+bad.getStationName1()+"%","%"+bad.getStationName2()+"%","%"+bad.getStationName5()+"%",bad.getNumber()}, new BattMap_informationImpl());
|
break;
|
case 6:
|
Battalarm_data adata=(Battalarm_data) obj;
|
if(adata.getAlm_id()==0){
|
sql=baseSql+six_sql+fromsql+alarm+condition_sql+stationSql+alarm_sqlT;
|
}else{
|
sql=baseSql+six_sql+fromsql+alarm+condition_sql+stationSql+alarm_sqlF;
|
}
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{adata.getNum(),"%"+adata.getStationName1()+"%","%"+adata.getStationName2()+"%","%"+adata.getStationName5()+"%",adata.getAlm_id()}, new BattMap_informationImpl());
|
break;
|
case 7:
|
Batt_devalarm_data ddata=(Batt_devalarm_data) obj;
|
if(ddata.getAlm_type()==0){
|
sql=baseSql+seven_sql+fromsql+dev+condition_sql+stationSql+dev_sqlT;
|
}else{
|
sql=baseSql+seven_sql+fromsql+dev+condition_sql+stationSql+dev_sqlF;
|
}
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{ddata.getNum(),"%"+ddata.getStationName1()+"%","%"+ddata.getStationName2()+"%","%"+ddata.getStationName5()+"%",ddata.getAlm_type()}, new BattMap_informationImpl());
|
break;
|
case 8:
|
Deverror_record error=(Deverror_record) obj;
|
if(error.getError_state()==-1){
|
sql=baseSql+eight_sql+fromsql+deverror+condition_sql+stationSql+deverror_sqlT;
|
}else{
|
sql=baseSql+eight_sql+fromsql+deverror+condition_sql+stationSql+deverror_sqlF;
|
}
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{error.getNum(),"%"+error.getStationName1()+"%","%"+error.getStationName2()+"%","%"+error.getStationName5()+"%",error.getError_state()}, new BattMap_informationImpl());
|
break;
|
case 9:
|
Fbs9100_state fbs=(Fbs9100_state) obj;
|
sql=baseSql+nine_sql+fromsql+version+condition_sql+stationSql+version_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{fbs.getNum(),"%"+fbs.getStationName1()+"%","%"+fbs.getStationName2()+"%","%"+fbs.getStationName5()+"%","%"+fbs.getDev_version()+"%"}, new BattMap_informationImpl());
|
break;
|
case 10:
|
Bts_station_event bts_event=(Bts_station_event) obj;
|
sql=baseSql+ten_sql+fromsql+state+condition_sql+stationSql+power_off_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{bts_event.getNum(),"%"+bts_event.getStationName1()+"%","%"+bts_event.getStationName2()+"%","%"+bts_event.getStationName5()+"%",bts_event.getStation_event_type(),bts_event.getRecord_datetime(),bts_event.getRecord_datetime_end(),bts_event.getStation_event_type_poff()}, new BattMap_informationImpl());
|
break;
|
case 11:
|
Bts_station_event bts_event1=(Bts_station_event) obj;
|
sql=baseSql+ten_sql+fromsql+state+condition_sql+stationSql+diaozhan_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{bts_event1.getNum(),"%"+bts_event1.getStationName1()+"%","%"+bts_event1.getStationName2()+"%","%"+bts_event1.getStationName5()+"%",bts_event1.getStation_event_type(),bts_event1.getRecord_datetime(),bts_event1.getRecord_datetime_end(),bts_event1.getStation_event_type_diaozhan()}, new BattMap_informationImpl());
|
break;
|
case 12:
|
Batt_replace replace=(Batt_replace) obj;
|
sql=baseSql+twelve_sql+fromsql+repalce+condition_sql+stationSql+repalce_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{replace.getNum(),"%"+replace.getStationname1()+"%","%"+replace.getStationname2()+"%","%"+replace.getStationname5()+"%",replace.getReplaced_time(),replace.getReplaced_time1(),replace.getReplaced_moncount()}, new BattMap_informationImpl());
|
break;
|
case 13:
|
Batt_endurance endurance1=(Batt_endurance) obj;
|
sql=baseSql+thirteen_sql+fromsql+endurance_time+condition_sql+stationSql+endurance_time_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{endurance1.getNum(),"%"+endurance1.getStationName1()+"%","%"+endurance1.getStationName2()+"%","%"+endurance1.getStationName5()+"%",endurance1.getEndurance_actual_timelong_min(),endurance1.getEndurance_actual_timelong_max(),}, new BattMap_informationImpl());
|
break;
|
case 14:
|
Fbs9100_state fbs1=(Fbs9100_state) obj;
|
sql=baseSql+fourteen_sql+fromsql+temp+condition_sql+stationSql+temp_sql;
|
sql+=endSql;
|
list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{fbs1.getNum(),"%"+fbs1.getStationName1()+"%","%"+fbs1.getStationName2()+"%","%"+fbs1.getStationName5()+"%",fbs1.getDev_temp(),fbs1.getDev_temp1()}, new BattMap_informationImpl());
|
break;
|
default:
|
break;
|
}
|
//System.out.println(condition+":"+sql);
|
return list;
|
}
|
public static void main(String[] args) throws ParseException {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
Date date1 = sdf.parse("2019-01-10 00:00:00");
|
Date date2 = sdf.parse("2019-03-13 00:00:00");
|
BattMap_informationImpl bimpl=new BattMap_informationImpl();
|
Batt_Maint_Dealarm bmd=new Batt_Maint_Dealarm();
|
Battalarm_data adata=new Battalarm_data();
|
adata.setAlm_cleared_type(0); //告警 0:选中 100:没有
|
adata.setAlm_id(1); //存在落后单体 1：选中 100:没有
|
BattMap_information binformation=new BattMap_information();
|
BattDischarge_plan bplan=new BattDischarge_plan();
|
bplan.setDischarge_reason(3); //存在延时：3 100 没有
|
bmd.setAdata(adata);
|
bmd.setBinformation(binformation);
|
bmd.setBplan(bplan);
|
User_inf uinf=new User_inf();
|
uinf.setUId(1002);
|
BattInf binf=new BattInf();
|
binf.setStationName1("");
|
binf.setStationName2("");
|
binf.setStationName5("");
|
binf.setNum(1002);
|
binf.setMonVolStd(12f);
|
binf.setBattGroupNum(1);
|
binf.setLoad_curr(20f);
|
binf.setLoad_curr1(50f);
|
|
BadBatt_mon bad=new BadBatt_mon();
|
bad.setNum(1002);
|
bad.setNumber(20);
|
bad.setNumber1(80);
|
Battalarm_data a=new Battalarm_data();
|
a.setNum(1002);
|
a.setAlm_id(119005);
|
|
Batt_devalarm_data d=new Batt_devalarm_data();
|
d.setNum(1002);
|
d.setAlm_type(119020);
|
|
Deverror_record e=new Deverror_record();
|
e.setNum(1002);
|
e.setError_state(8);
|
|
Fbs9100_state f=new Fbs9100_state();
|
f.setNum(1002);
|
f.setStationName1("");
|
f.setStationName2("");
|
f.setStationName5("");
|
f.setDev_version("");//D:8,V12.53.37
|
f.setDev_temp(30f);
|
f.setDev_temp1(40f);
|
|
Bts_station_event se=new Bts_station_event();
|
se.setNum(1002);
|
se.setStation_event_type(1);
|
se.setRecord_datetime(ActionUtil.getSimpDate(date1));
|
se.setRecord_datetime_end(ActionUtil.getSimpDate(date2));
|
se.setStation_event_type_poff(0);
|
se.setStation_event_type_diaozhan(0);
|
|
Batt_endurance bd=new Batt_endurance();
|
bd.setNum(1002);
|
bd.setStationName1("");
|
bd.setStationName2("");
|
bd.setStationName5("");
|
bd.setReal_cap_min(100f);
|
bd.setReal_cap_max(200f);
|
bd.setEndurance_actual_timelong_min(60);
|
bd.setEndurance_actual_timelong_max(120);
|
|
Batt_replace r=new Batt_replace();
|
r.setNum(1002);
|
r.setReplaced_time(ActionUtil.getSimpDate(date1));
|
r.setReplaced_time1(ActionUtil.getSimpDate(date2));
|
r.setReplaced_moncount(5);
|
|
|
//List<Batt_Maint_Dealarm>list=bimpl.serchByCondition(bmd);
|
List<BattMap_information> list=bimpl.serchBattInMapFilter(f,14);
|
for (BattMap_information b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
}
|
}
|
