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.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.LimitNumberFactory;
|
|
import com.fgkj.dao.BaseDAO;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.IDatabaseName;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.BattPower_off;
|
import com.fgkj.dto.Batt_Maint_Dealarm;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.User_inf;
|
import com.mysql.fabric.xmlrpc.base.Array;
|
|
public class BattPower_offImpl implements BaseDAO,CallBack{
|
|
|
public boolean add(Object obj) {
|
|
return false;
|
}
|
|
public boolean update(Object obj) {
|
|
return false;
|
}
|
|
public boolean del(Object obj) {
|
|
return false;
|
}
|
|
public List searchAll() {
|
|
return null;
|
}
|
//机房断电统计查询
|
/*
|
* 时间段选择放在：binf的battproductdate
|
* 状态：binf的num中
|
public List serchByCondition(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 tb_battpower_off.dev_id,poweroff_starttime,poweroff_stoptime,timelong,power_state,note" +
|
",db_battinf.tb_battinf.StationName,db_battinf.tb_battinf.StationName1 " +
|
" FROM web_site.tb_battpower_off,db_battinf.tb_battinf " +
|
" WHERE tb_battpower_off.dev_id=db_battinf.tb_battinf.FBSDeviceId " +
|
" AND db_battinf.tb_battinf.StationName LIKE ? " +
|
" AND db_battinf.tb_battinf.StationName1 LIKE ? " +
|
" AND poweroff_starttime>=? " +
|
" AND poweroff_starttime<=? ";
|
//实时还是历史
|
String stateSqlT=" AND power_state=? ";
|
String stateSqlF=" AND power_state!=? ";
|
if(binf.getNum()==100){
|
sql+=stateSqlF;
|
}else{
|
sql+=stateSqlT;
|
}
|
//分页
|
String limitSql=" limit ?,?";
|
sql+=limitSql;
|
List<BattPower_off> list = DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName1()+"%",binf.getBattProductDate(),binf.getBattProductDate1()
|
,binf.getNum(),(page.getPageCurr() - 1)*page.getPageSize(),
|
page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattPower_off bpoff=new BattPower_off();
|
bpoff.setDev_id(rs.getInt("dev_id"));
|
bpoff.setPoweroff_starttime(rs.getTimestamp("poweroff_starttime"));
|
bpoff.setPoweroff_stoptime(rs.getTimestamp("poweroff_stoptime"));
|
bpoff.setPower_state(rs.getInt("power_state"));
|
bpoff.setTimelong(rs.getInt("timelong"));
|
bpoff.setStationName(rs.getString("stationName"));
|
bpoff.setStationName1(rs.getString("stationName1"));
|
bpoff.setNote(rs.getString("note"));
|
list.add(bpoff);
|
}
|
} 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).setNum(number);
|
}
|
return list;
|
}*/
|
//机房断电统计查询
|
/*
|
* 时间段选择放在：binf的battproductdate
|
* 状态：binf的num中*/
|
public List serchByCondition(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=" SELECT SQL_CALC_FOUND_ROWS DISTINCT tb_battpower_off.dev_id,poweroff_starttime,poweroff_stoptime,timelong,power_state,note" +
|
",db_battinf.tb_battinf.StationName,db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName2,db_battinf.tb_battinf.StationName5 " +
|
" FROM web_site.tb_battpower_off,db_battinf.tb_battinf " +
|
" WHERE tb_battpower_off.dev_id=db_battinf.tb_battinf.FBSDeviceId " +
|
" AND db_battinf.tb_battinf.StationName LIKE ? AND db_battinf.tb_battinf.StationName2 LIKE ? AND db_battinf.tb_battinf.StationName5 LIKE ? " +
|
" AND poweroff_starttime>=? " +
|
" AND poweroff_starttime<=? ";
|
//实时还是历史
|
String stateSqlT=" AND power_state=? ";
|
String stateSqlF=" AND power_state!=? ";
|
if(binf.getNum()==100){
|
sql+=stateSqlF;
|
}else{
|
sql+=stateSqlT;
|
}
|
//判断是否为管理的机房
|
String stationSql=" and db_battinf.tb_battinf.StationId in(select distinct db_battinf.tb_battinf.stationId " +
|
" from db_battinf.tb_battinf " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.stationid=db_battinf.tb_battinf.stationid " +
|
" left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" left outer join db_user.tb_user_inf on tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" where db_user.tb_user_inf.uid=? and db_battinf.tb_battinf.stationname1 like ? ) ";
|
sql+=stationSql;
|
//分页
|
String limitSql=" limit ?,?";
|
sql+=limitSql;
|
List<BattPower_off> list = DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+binf.getStationName()+"%","%"+binf.getStationName2()+"%","%"+binf.getStationName5()+"%",binf.getBattProductDate(),binf.getBattProductDate1()
|
,binf.getNum(),uinf.getUId(),"%"+binf.getStationName1()+"%",(page.getPageCurr() - 1)*page.getPageSize(),
|
page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattPower_off bpoff=new BattPower_off();
|
bpoff.setDev_id(rs.getInt("dev_id"));
|
bpoff.setPoweroff_starttime(rs.getTimestamp("poweroff_starttime"));
|
bpoff.setPoweroff_stoptime(rs.getTimestamp("poweroff_stoptime"));
|
bpoff.setPower_state(rs.getInt("power_state"));
|
bpoff.setTimelong(rs.getInt("timelong"));
|
bpoff.setStationName(rs.getString("stationName"));
|
bpoff.setStationName1(rs.getString("stationName1"));
|
bpoff.setStationName2(rs.getString("stationName2"));
|
bpoff.setStationName5(rs.getString("stationName5"));
|
bpoff.setNote(rs.getString("note"));
|
list.add(bpoff);
|
}
|
} 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).setNum(number);
|
}
|
return list;
|
}
|
//机房断电统计(首页上的饼状图)
|
/*以电池组分组*/
|
/*public int serchPowerOff(){
|
String sql="";
|
String baseSql=" SELECT COUNT(DISTINCT dev_id) AS number FROM web_site.tb_battpower_off " +
|
" WHERE dev_id in( " +
|
" SELECT DISTINCT fbsdeviceid FROM db_battinf.tb_battinf ) ";
|
//电池断电状态：实时
|
String stateSql=" AND power_state=0 limit 1";
|
sql=baseSql+stateSql;
|
List<BattPower_off> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
BattPower_off bpoff=new BattPower_off();
|
bpoff.setNum(rs.getInt("number"));
|
list.add(bpoff);
|
}
|
} 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 serchPowerOff(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="";
|
String baseSql=" SELECT COUNT(DISTINCT dev_id) AS number FROM web_site.tb_battpower_off " +
|
" WHERE dev_id in( " +
|
" select distinct db_battinf.tb_battinf.fbsdeviceid " +
|
" 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=? ) ";
|
//电池断电状态：实时
|
String stateSql=" AND power_state=0 limit 1";
|
sql=baseSql+stateSql;
|
List<BattPower_off> 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()){
|
BattPower_off bpoff=new BattPower_off();
|
bpoff.setNum(rs.getInt("number"));
|
list.add(bpoff);
|
}
|
} 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 List serchByInfo(Object obj) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
public List getResults(ResultSet rs) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
public static void main(String[] args) throws ParseException {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Date date1 = sdf.parse("2000-01-01");
|
Date date2 = sdf.parse("2020-01-01");
|
|
BattInf binf=new BattInf();
|
binf.setStationName("");
|
binf.setStationName1("");
|
binf.setNum(0);
|
binf.setBattProductDate(ActionUtil.getSimpDate(date1));
|
binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
|
|
User_inf uinf=new User_inf();
|
uinf.setUId(1002);
|
|
Page p = new Page();
|
p.setPageCurr(1);
|
p.setPageSize(50);
|
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
bmd.setPage(p);
|
bmd.setBinf(binf);
|
bmd.setUinf(uinf);
|
|
BattPower_offImpl bimp=new BattPower_offImpl();
|
//List<BattPower_off> list=bimp.serchByCondition(bmd);
|
//int number=bimp.serchPowerOff(uinf);
|
//System.out.println(number);
|
}
|
}
|
