package com.fgkj.dao.impl;
|
|
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.BaseDAO;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.IDatabaseName;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.Batt_Devdischarge;
|
import com.fgkj.dto.Batt_electricity;
|
|
|
public class Batt_electricityImpl implements BaseDAO,CallBack{
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_electricity bele=new Batt_electricity();
|
bele.setNum(rs.getInt("num"));
|
bele.setDev_id(rs.getInt("dev_id"));
|
bele.setDev_name(rs.getString("dev_name"));
|
bele.setDev_recordtime(rs.getTimestamp("dev_recordtime"));
|
bele.setDev_electricity_CM(rs.getFloat("dev_electricity_CM"));
|
bele.setDev_electricity_CT(rs.getFloat("dev_electricity_CT"));
|
bele.setDev_electricity_CU(rs.getFloat("dev_electricity_CU"));
|
bele.setNote(rs.getString("note"));
|
list.add(bele);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
public boolean add(Object obj) {
|
Batt_electricity bele=(Batt_electricity) obj;
|
String sql="insert into web_site.tb_batt_electricity(dev_id,dev_name,dev_recordtime,dev_electricity_CM,dev_electricity_CT,dev_electricity_CU,note) values(?,?,?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{
|
bele.getDev_id(),bele.getDev_name(),bele.getDev_recordtime(),
|
bele.getDev_electricity_CM(),bele.getDev_electricity_CT(),bele.getDev_electricity_CU(),
|
bele.getNote()});
|
return bl;
|
}
|
|
public boolean update(Object obj) {
|
Batt_electricity bele=(Batt_electricity) obj;
|
String sql="update web_site.tb_batt_electricity set dev_id=?,dev_name=?,dev_recordtime=?,dev_electricity_CM=?,dev_electricity_CT=?,dev_electricity_CU=?,note=? where num=? ";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{
|
bele.getDev_id(),bele.getDev_name(),bele.getDev_recordtime(),
|
bele.getDev_electricity_CM(),bele.getDev_electricity_CT(),bele.getDev_electricity_CU(),
|
bele.getNote(),bele.getNum()});
|
return bl;
|
}
|
|
public boolean del(Object obj) {
|
Batt_electricity bele=(Batt_electricity) obj;
|
String sql="delete from web_site.tb_batt_electricity where num=? ";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{bele.getNum()});
|
return bl;
|
}
|
|
public List searchAll() {
|
String sql="select num,dev_id,dev_name,dev_recordtime,dev_electricity_CM,dev_electricity_CT,dev_electricity_CU,note from web_site.tb_batt_devdischarge ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new Batt_DevdischargeImpl());
|
return list;
|
}
|
|
|
//9.1机房主控中用电量的统计的折线图
|
public List serchByInfo(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql=" select dev_id,dev_name,dev_recordtime,dev_electricity_cm,dev_electricity_ct,dev_electricity_cu " +
|
" from web_site.tb_batt_electricity,(select DISTINCT(FBSdeviceid) from db_battinf.tb_battinf where stationid=? ) as DISTINCTbattinf " +
|
" where dev_id=DISTINCTbattinf.FBSdeviceid " +
|
" and dev_recordtime>=? and dev_recordtime<=? " +
|
" order by dev_recordtime ";
|
//System.out.println(binf.getStationId()+"=="+binf.getBattProductDate()+"==="+binf.getBattProductDate1());
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationId(),binf.getBattProductDate(),binf.getBattProductDate1()},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
//System.out.println("TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT");
|
Batt_electricity bele=new Batt_electricity();
|
bele.setDev_id(rs.getInt("dev_id"));
|
bele.setDev_name(rs.getString("dev_name"));
|
bele.setDev_recordtime(rs.getTimestamp("dev_recordtime"));
|
bele.setDev_electricity_CM(rs.getFloat("dev_electricity_CM"));
|
bele.setDev_electricity_CT(rs.getFloat("dev_electricity_CT"));
|
bele.setDev_electricity_CU(rs.getFloat("dev_electricity_CU"));
|
list.add(bele);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public static void main(String[] args) throws ParseException {
|
Batt_electricityImpl bimpl=new Batt_electricityImpl();
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Date date1 = sdf.parse("2000-01-01");
|
Date date2 = sdf.parse("2017-12-01");
|
BattInf binf=new BattInf();
|
/*binf.setStationName("湖北省-鄂州市-东方世纪城-设备1");
|
binf.setStationName1("");*/
|
binf.setStationId("42070450");
|
binf.setBattProducer("");
|
binf.setSignalName("1");//统计方式
|
binf.setMonCount(1);
|
binf.setBattProductDate(ActionUtil.getSimpDate(date1));
|
binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
|
List<Batt_electricity> list=bimpl.serchByCondition(binf);
|
for (Batt_electricity b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
|
}
|
//10.1根据设备id连battinf和batt_devdischarge表
|
/*
|
* 记录时间放在battinf的battproducer
|
* 统计方式放在SignalName*/
|
public List serchByCondition(Object obj) {
|
|
final BattInf binf=(BattInf) obj;
|
String sql="";
|
String basesql="select num,dev_id,dev_name,dev_recordtime,dev_electricity_CM,dev_electricity_CT,dev_electricity_CU,note ";
|
String conditionSql=" from web_site.tb_batt_electricity " +
|
",(select DISTINCT(FBSdeviceid) from db_battinf.tb_battinf where stationid=? and stationname1 like ? and stationname2 like ? ) as DISTINCTbattinf " +
|
"where DISTINCTbattinf.FBSdeviceid=tb_batt_electricity.dev_id " +
|
"and to_days(tb_batt_electricity.dev_recordtime)>=to_days(?) and to_days(tb_batt_electricity.dev_recordtime)<=to_days(?) ";
|
basesql+=conditionSql;//合连表条件
|
String endSql=" ORDER BY dev_id ,dev_recordtime ";
|
sql=basesql+endSql;//合成最后的sql
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getStationId(),"%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%",binf.getBattProductDate(),binf.getBattProductDate1()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_electricity bele=new Batt_electricity();
|
bele.setNum(rs.getInt("num"));
|
bele.setDev_id(rs.getInt("dev_id"));
|
bele.setDev_name(rs.getString("dev_name"));
|
bele.setDev_recordtime(rs.getTimestamp("dev_recordtime"));
|
bele.setDev_electricity_CM(rs.getFloat("dev_electricity_CM"));
|
bele.setDev_electricity_CT(rs.getFloat("dev_electricity_CT"));
|
bele.setDev_electricity_CU(rs.getFloat("dev_electricity_CU"));
|
bele.setNote(binf.getSignalName());
|
list.add(bele);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
});
|
return list;
|
}
|
|
}
|
