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.BattTestData;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
|
import com.fgkj.dao.BaseDAO;
|
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_Devdischarge_all;
|
import com.fgkj.dto.Batttestdata;
|
import com.fgkj.dto.Page;
|
|
public class Batt_DevdischargeImpl implements BaseDAO,CallBack{
|
private String tb_batt_devdischarge_CM="web_site.tb_batt_devdischarge"+BattTestData.CM;
|
private String tb_batt_devdischarge_CT="web_site.tb_batt_devdischarge"+BattTestData.CT;
|
private String tb_batt_devdischarge_CU="web_site.tb_batt_devdischarge"+BattTestData.CU;
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_Devdischarge bdischarge=new Batt_Devdischarge();
|
bdischarge.setNum(rs.getInt("num"));
|
bdischarge.setDev_id(rs.getInt("dev_id"));
|
bdischarge.setDev_name(rs.getString("dev_name"));
|
bdischarge.setDev_recordtime(rs.getTimestamp("dev_recordtime"));
|
bdischarge.setDev_vol(rs.getFloat("dev_vol"));
|
bdischarge.setDev_curr(rs.getFloat("dev_curr"));
|
bdischarge.setDev_power(rs.getFloat("dev_power"));
|
|
bdischarge.setNote(rs.getString("note"));
|
list.add(bdischarge);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
public boolean add(Object obj) {
|
Batt_Devdischarge bdischarge=(Batt_Devdischarge) obj;
|
String sql="insert into web_site.tb_batt_devdischarge(dev_id,dev_name,dev_recordtime,dev_vol,dev_curr,dev_power,note) values(?,?,?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{
|
bdischarge.getDev_id(),bdischarge.getDev_name(),bdischarge.getDev_recordtime(),
|
bdischarge.getDev_vol(),bdischarge.getDev_curr(),bdischarge.getDev_power(),
|
bdischarge.getNote()});
|
return bl;
|
}
|
|
public boolean update(Object obj) {
|
Batt_Devdischarge bdischarge=(Batt_Devdischarge) obj;
|
String sql="update web_site.tb_batt_devdischarge set dev_id=?,dev_name=?,dev_recordtime=?,dev_vol=?,dev_curr=?,dev_power=?,note=? where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{bdischarge.getDev_id(),bdischarge.getDev_name(),bdischarge.getDev_recordtime(),
|
bdischarge.getDev_vol(),bdischarge.getDev_curr(),bdischarge.getDev_power(),
|
bdischarge.getNote()});
|
return bl;
|
}
|
|
public boolean del(Object obj) {
|
Batt_Devdischarge bdischarge=(Batt_Devdischarge) obj;
|
String sql="delete from web_site.tb_batt_devdischarge where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{bdischarge.getNum()});
|
return bl;
|
}
|
|
public List searchAll() {
|
String sql="select num,dev_id,dev_name,dev_recordtime,dev_vol,dev_curr,dev_power,note from web_site.tb_batt_devdischarge ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new Batt_DevdischargeImpl());
|
return list;
|
}
|
|
//10.1根据设备id连battinf和batt_devdischarge表
|
/*
|
* 记录时间放在battinf的battproducer
|
**/
|
public List serchByCondition(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql="";
|
String basesql="select "+
|
tb_batt_devdischarge_CM+".num,"+tb_batt_devdischarge_CM+".dev_id,"+tb_batt_devdischarge_CM+".dev_name,"+tb_batt_devdischarge_CM+".dev_recordtime,"+tb_batt_devdischarge_CM+".dev_vol,"+tb_batt_devdischarge_CM+".dev_curr,"+tb_batt_devdischarge_CM+".dev_power,"+tb_batt_devdischarge_CM+".note, " +
|
tb_batt_devdischarge_CT+".num,"+tb_batt_devdischarge_CT+".dev_id,"+tb_batt_devdischarge_CT+".dev_name,"+tb_batt_devdischarge_CT+".dev_recordtime,"+tb_batt_devdischarge_CT+".dev_vol,"+tb_batt_devdischarge_CT+".dev_curr,"+tb_batt_devdischarge_CT+".dev_power,"+tb_batt_devdischarge_CT+".note, " +
|
tb_batt_devdischarge_CU+".num,"+tb_batt_devdischarge_CU+".dev_id,"+tb_batt_devdischarge_CU+".dev_name,"+tb_batt_devdischarge_CU+".dev_recordtime,"+tb_batt_devdischarge_CU+".dev_vol,"+tb_batt_devdischarge_CU+".dev_curr,"+tb_batt_devdischarge_CU+".dev_power,"+tb_batt_devdischarge_CU+".note, " +
|
"DISTINCTbattinf.FBSDeviceName ";
|
//连表条件
|
String conditionSql=" from "+ tb_batt_devdischarge_CM +",(select DISTINCT(FBSdeviceid),FBSDeviceName from db_battinf.tb_battinf where stationid like ? and stationname1 like ? and stationname2 like ? ) as DISTINCTbattinf,"+tb_batt_devdischarge_CT+","+tb_batt_devdischarge_CU;
|
|
String whereSql=" where DISTINCTbattinf.FBSdeviceid=tb_batt_devdischarge_CM.dev_id and " +
|
"tb_batt_devdischarge_CT.dev_id=tb_batt_devdischarge_CM.dev_id and " +
|
"tb_batt_devdischarge_CU.dev_id=tb_batt_devdischarge_CM.dev_id and " +
|
"tb_batt_devdischarge_CT.record_num=web_site.tb_batt_devdischarge_CM.record_num and " +
|
"tb_batt_devdischarge_CU.record_num=web_site.tb_batt_devdischarge_CM.record_num and " +
|
tb_batt_devdischarge_CM+".dev_recordtime>=? and "+tb_batt_devdischarge_CM+".dev_recordtime<=(?) ";
|
|
int number=binf.getNum();//总数
|
int roteN=0;
|
int endN=BattTestData.RC_NUM_PARAM;//总笔数
|
if(number<=endN){
|
roteN=1;
|
}else{
|
if(number%endN==0){
|
roteN=number/endN;
|
}else{
|
roteN=number/endN+1;
|
}
|
}
|
//System.out.println(binf.getNum()+" "+roteN+" "+binf.getMonCount()+" "+binf.getMonNum());
|
String numSql=" and (tb_batt_devdischarge_CM.record_num%"+roteN+"=0 or tb_batt_devdischarge_CM.record_num=? or tb_batt_devdischarge_CM.record_num=?) ";
|
//排序条件
|
String endSql=" ORDER BY "+tb_batt_devdischarge_CM+".dev_id ,"+tb_batt_devdischarge_CM+".dev_recordtime ";
|
sql=basesql+conditionSql+whereSql+numSql+endSql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationId()+"%","%"+binf.getStationName1()+"%","%"+binf.getStationName2()+"%",binf.getBattProductDate(),binf.getBattProductDate1()
|
,binf.getMonNum(),binf.getMonCount() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Batt_Devdischarge bdischarge_CM=new Batt_Devdischarge();
|
bdischarge_CM.setNum(rs.getInt("tb_batt_devdischarge_CM.num"));
|
bdischarge_CM.setDev_id(rs.getInt("tb_batt_devdischarge_CM.dev_id"));
|
bdischarge_CM.setDev_name(rs.getString("DISTINCTbattinf.FBSDeviceName"));
|
bdischarge_CM.setDev_recordtime(rs.getTimestamp("tb_batt_devdischarge_CM.dev_recordtime"));
|
bdischarge_CM.setDev_vol(rs.getFloat("tb_batt_devdischarge_CM.dev_vol"));
|
bdischarge_CM.setDev_curr(rs.getFloat("tb_batt_devdischarge_CM.dev_curr"));
|
bdischarge_CM.setDev_power(rs.getFloat("tb_batt_devdischarge_CM.dev_power"));
|
bdischarge_CM.setNote(rs.getString("tb_batt_devdischarge_CM.note"));
|
|
Batt_Devdischarge bdischarge_CT=new Batt_Devdischarge();
|
bdischarge_CT.setNum(rs.getInt("tb_batt_devdischarge_CT.num"));
|
bdischarge_CT.setDev_id(rs.getInt("tb_batt_devdischarge_CT.dev_id"));
|
bdischarge_CT.setDev_name(rs.getString("DISTINCTbattinf.FBSDeviceName"));
|
bdischarge_CT.setDev_recordtime(rs.getTimestamp("tb_batt_devdischarge_CT.dev_recordtime"));
|
bdischarge_CT.setDev_vol(rs.getFloat("tb_batt_devdischarge_CT.dev_vol"));
|
bdischarge_CT.setDev_curr(rs.getFloat("tb_batt_devdischarge_CT.dev_curr"));
|
bdischarge_CT.setDev_power(rs.getFloat("tb_batt_devdischarge_CT.dev_power"));
|
bdischarge_CT.setNote(rs.getString("tb_batt_devdischarge_CT.note"));
|
|
Batt_Devdischarge bdischarge_CU=new Batt_Devdischarge();
|
bdischarge_CU.setNum(rs.getInt("tb_batt_devdischarge_CU.num"));
|
bdischarge_CU.setDev_id(rs.getInt("tb_batt_devdischarge_CU.dev_id"));
|
bdischarge_CU.setDev_name(rs.getString("DISTINCTbattinf.FBSDeviceName"));
|
bdischarge_CU.setDev_recordtime(rs.getTimestamp("tb_batt_devdischarge_CU.dev_recordtime"));
|
bdischarge_CU.setDev_vol(rs.getFloat("tb_batt_devdischarge_CU.dev_vol"));
|
bdischarge_CU.setDev_curr(rs.getFloat("tb_batt_devdischarge_CU.dev_curr"));
|
bdischarge_CU.setDev_power(rs.getFloat("tb_batt_devdischarge_CU.dev_power"));
|
bdischarge_CU.setNote(rs.getString("tb_batt_devdischarge_CU.note"));
|
|
Batt_Devdischarge_all bdall=new Batt_Devdischarge_all();
|
bdall.setBd_CM(bdischarge_CM);
|
bdall.setBd_CT(bdischarge_CT);
|
bdall.setBd_CU(bdischarge_CU);
|
list.add(bdall);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//查询tb_batt_devdischarge_CM最大最小num和总num
|
public List serchMaxAndMinNum(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" SELECT COUNT(tb_batt_devdischarge_CM.record_num) AS number,MAX(tb_batt_devdischarge_CM.record_num) AS maxnum,MIN(tb_batt_devdischarge_CM.record_num) AS minnum " +
|
"FROM "+tb_batt_devdischarge_CM +",(select DISTINCT(FBSdeviceid),FBSDeviceName from db_battinf.tb_battinf where stationid=? ) as DISTINCTbattinf "+
|
" WHERE DISTINCTbattinf.FBSdeviceid=tb_batt_devdischarge_CM.dev_id and " +
|
tb_batt_devdischarge_CM+".dev_recordtime>=? and "+tb_batt_devdischarge_CM+".dev_recordtime<=? " +
|
"LIMIT 1";
|
// System.out.println(sql);
|
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()){
|
Batt_Devdischarge bd=new Batt_Devdischarge();
|
bd.setDev_curr((float)rs.getInt("number"));
|
bd.setDev_power((float)rs.getInt("maxnum"));
|
bd.setDev_vol((float)rs.getInt("minnum"));
|
list.add(bd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public List serchByInfo(Object obj) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
public static void main(String[] args) throws ParseException {
|
Batt_DevdischargeImpl bimpl=new Batt_DevdischargeImpl();
|
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.setStationId("42070450");
|
binf.setStationName("湖北省-鄂州市-东方世纪城-设备1");
|
binf.setStationName1("");
|
binf.setBattProducer("");
|
binf.setMonCount(1);
|
binf.setBattProductDate(ActionUtil.getSimpDate(date1));
|
binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
|
List<Batt_Devdischarge_all> list=bimpl.serchByCondition(binf);
|
for (Batt_Devdischarge_all b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());
|
|
}
|
|
}
|
