package com.fgkj.dao.impl;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
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.dto.BattMap_information;
|
import com.fgkj.dto.Batt_endurance;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.User_inf;
|
import com.mysql.fabric.xmlrpc.base.Array;
|
|
public class Batt_enduranceImpl implements BaseDAO,CallBack{
|
|
public List getResults(ResultSet rs) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
public boolean add(Object obj) {
|
// TODO Auto-generated method stub
|
return false;
|
}
|
|
public boolean update(Object obj) {
|
// TODO Auto-generated method stub
|
return false;
|
}
|
|
public boolean del(Object obj) {
|
// TODO Auto-generated method stub
|
return false;
|
}
|
|
public List searchAll() {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
//续航时间的查询
|
public List serchByCondition(Object obj) {
|
Batt_endurance bendurance=(Batt_endurance) obj;
|
Page page=bendurance.getPage();
|
Connection conn=DBUtil.getConn();
|
String numberSql=" SELECT FOUND_ROWS() number";
|
String sql="select SQL_CALC_FOUND_ROWS distinct tb_batt_endurance.num,tb_batt_endurance.deviceid,tb_batt_endurance.stationid,real_cap,tb_batt_endurance.moncapstd,real_curr,endurance_theory_timelong,endurance_actual_timelong,endurance_actual_timelong_max,endurance_actual_timelong_min,is_out_stand,batts_moncapstd,batts_teststarttime " +
|
",db_battinf.tb_battinf.StationName,db_battinf.tb_battinf.StationName1,db_battinf.tb_battinf.StationName2,db_battinf.tb_battinf.StationName5 " +
|
" from web_site.tb_batt_endurance,db_battinf.tb_battinf " +
|
" where tb_batt_endurance.stationid=db_battinf.tb_battinf.stationid "+
|
" and tb_batt_endurance.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.station_install=1 ) " ;
|
//机房筛选
|
String stationSql=" and db_battinf.tb_battinf.StationName like ? and db_battinf.tb_battinf.StationName1 like ? and db_battinf.tb_battinf.StationName2 like ? and db_battinf.tb_battinf.StationName5 like ? ";
|
sql+=stationSql;
|
//1U/2U设备筛选
|
String monvolSqlT=" and db_battinf.tb_battinf.monvolstd!=? ";//全部
|
String monvolSqlF=" and db_battinf.tb_battinf.monvolstd=? ";
|
if(bendurance.getMonvolstd()==0) {
|
sql+=monvolSqlT;
|
}else {
|
sql+=monvolSqlF;
|
}
|
//实际续航时间
|
String timelongSqlT=" and endurance_actual_timelong<=? ";//小于某一时间
|
String timelongSqlF=" and endurance_actual_timelong>-1? ";//全部
|
if(bendurance.getEndurance_actual_timelong()==0){
|
sql+=timelongSqlF;
|
}else{
|
sql+=timelongSqlT;
|
}
|
//是否掉站
|
String is_out_standSqlT=" and is_out_stand!=? ";//全部
|
String is_out_standSqlF=" and is_out_stand=? ";
|
if(bendurance.getIs_out_stand()==100){
|
sql+=is_out_standSqlT;
|
}else{
|
sql+=is_out_standSqlF;
|
}
|
//排序时间
|
String orderSql=" order by endurance_actual_timelong,tb_batt_endurance.deviceid asc ";
|
sql+=orderSql;
|
//分页
|
String limitSql=" limit ?,? ";
|
sql+=limitSql;
|
|
|
//System.out.println(sql);
|
List<Batt_endurance> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{bendurance.getNum(),"%"+bendurance.getStationName()+"%","%"+bendurance.getStationName1()+"%","%"+bendurance.getStationName2()+"%","%"+bendurance.getStationName5()+"%",bendurance.getMonvolstd(),bendurance.getEndurance_actual_timelong(),bendurance.getIs_out_stand()
|
,(page.getPageCurr()-1)*page.getPageSize(),page.getPageSize()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_endurance b=new Batt_endurance();
|
b.setNum(rs.getInt("num"));
|
b.setDeviceId(rs.getInt("deviceId"));
|
b.setStationid(rs.getString("stationid"));
|
b.setReal_cap(rs.getFloat("real_cap"));
|
b.setMoncapstd(rs.getFloat("moncapstd"));
|
b.setReal_curr(rs.getFloat("real_curr"));
|
b.setBatts_moncapstd(rs.getString("batts_moncapstd"));
|
b.setBatts_teststarttime(rs.getString("batts_teststarttime"));
|
b.setEndurance_theory_timelong(rs.getFloat("endurance_theory_timelong"));
|
b.setEndurance_actual_timelong(rs.getFloat("endurance_actual_timelong"));
|
b.setEndurance_actual_timelong_max(rs.getFloat("endurance_actual_timelong_max"));
|
b.setEndurance_actual_timelong_min(rs.getFloat("endurance_actual_timelong_min"));
|
b.setIs_out_stand(rs.getInt("is_out_stand"));
|
b.setStationName(rs.getString("stationname"));
|
b.setStationName1(rs.getString("stationname1"));
|
b.setStationName2(rs.getString("stationname2"));
|
b.setStationName5(rs.getString("stationname5"));
|
Page p=new Page();
|
b.setPage(p);
|
list.add(b);
|
}
|
} 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;
|
}
|
//续航不足1小时，2小时等的饼状图
|
public List serchByState_xuhang(Object obj) {
|
User_inf uinf=(User_inf) obj;
|
String sql="select distinct tb_batt_endurance.deviceid,tb_batt_endurance.stationid,endurance_theory_timelong,endurance_actual_timelong " +
|
" from web_site.tb_batt_endurance " +
|
" where tb_batt_endurance.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.station_install=1) " ;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int num_hour=0;//续航一小时内的
|
int num_two=0;//续航一小时到两个小时的
|
int num_three=0;//续航两小时到三小时内的
|
int num_other=0;//续航三小时以上
|
try {
|
while(rs.next()){
|
float actual_timelong=rs.getFloat("endurance_actual_timelong");
|
if(actual_timelong<=60){
|
num_hour+=1;
|
}else if(actual_timelong<=120){
|
num_two+=1;
|
}else if(actual_timelong<=180){
|
num_three+=1;
|
}else{
|
num_other+=1;
|
}
|
}
|
list.add(num_hour);
|
list.add(num_two);
|
list.add(num_three);
|
list.add(num_other);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//导航上续航不足3小时的个数
|
public int serchxuhangNotInThree(Object obj) {
|
User_inf uinf=(User_inf) obj;
|
String sql="select count(distinct tb_batt_endurance.deviceid) as nums from web_site.tb_batt_endurance " +
|
" where endurance_actual_timelong<180 " +
|
" and tb_batt_endurance.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.station_install=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 nums=rs.getInt("nums");
|
list.add(nums);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int nums=0;//续航不足3小时数
|
if(list!=null&&list.size()>0){
|
nums=(Integer) list.get(list.size()-1);
|
}
|
return nums;
|
}
|
//根据机房id查询该机房续航不足的数
|
public int serchxuhangNotInThree_stationid(Object obj) {
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="select count(distinct tb_batt_endurance.deviceid) as nums from web_site.tb_batt_endurance " +
|
" where endurance_actual_timelong<180 and tb_batt_endurance.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()){
|
int nums=rs.getInt("nums");
|
list.add(nums);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int nums=0;//续航不足3小时数
|
if(list!=null&&list.size()>0){
|
nums=(Integer) list.get(list.size()-1);
|
}
|
return nums;
|
}
|
public List serchByInfo(Object obj) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
public static void main(String[] args) {
|
Batt_enduranceImpl bimpl=new Batt_enduranceImpl();
|
Batt_endurance ben=new Batt_endurance();
|
ben.setNum(1002);
|
ben.setStationName("");
|
ben.setStationName1("");
|
ben.setStationName2("");
|
ben.setStationName5("");
|
ben.setEndurance_actual_timelong(0);
|
ben.setMonvolstd(0);
|
ben.setIs_out_stand(100);
|
Page page=new Page();
|
page.setPageCurr(1);
|
page.setPageSize(10);
|
ben.setPage(page);
|
User_inf uinf=new User_inf();
|
uinf.setUId(1002);
|
List<Batt_endurance> list=bimpl.serchByCondition(ben);
|
for (Batt_endurance b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
|
|
/*List<Integer> list=bimpl.serchByState_xuhang(uinf);
|
for (int num : list) {
|
System.out.println(num);
|
}*/
|
//System.out.println(list.size());
|
/*int nums=bimpl.serchxuhangNotInThree(uinf);
|
System.out.println(nums);*/
|
/*BattMap_information binformation=new BattMap_information();
|
binformation.setStationId("42000530");
|
int nums=bimpl.serchxuhangNotInThree_stationid(binformation);
|
System.out.println(nums);*/
|
}
|
|
|
|
}
|
