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 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 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 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 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 list=bimp.serchByCondition(bmd); //int number=bimp.serchPowerOff(uinf); //System.out.println(number); } }