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.IDatabaseName;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.Task_Batt_Test;
|
import com.fgkj.dto.User_inf;
|
import com.fgkj.dto.User_task;
|
import com.fgkj.dto.User_task_batt_template;
|
import com.fgkj.dto.User_task_batt_test;
|
import com.fgkj.dto.User_task_user_list;
|
import com.fgkj.services.User_task_batt_testService;
|
import com.sun.org.apache.bcel.internal.generic.GETSTATIC;
|
|
public class User_task_batt_testImpl implements BaseDAO,CallBack{
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
|
User_task_batt_test utest=new User_task_batt_test();
|
utest.setNum(rs.getInt("num"));
|
utest.setTask_id(rs.getInt("task_id"));
|
utest.setTask_usr_ids(rs.getString("task_usr_ids"));
|
utest.setBattGroupId(rs.getInt("BattGroupId"));
|
utest.setTask_enabled(rs.getInt("task_enabled"));
|
utest.setTask_exe_date(rs.getTimestamp("task_exe_date"));
|
utest.setTask_close_date(rs.getTimestamp("task_close_date"));
|
utest.setTask_notice_starttime(rs.getTimestamp("task_notice_starttime"));
|
utest.setTask_notice_endtime(rs.getTimestamp("task_notice_endtime"));
|
utest.setTask_notice_count(rs.getInt("task_notice_count"));
|
utest.setTask_notice_latesttime(rs.getTimestamp("task_notice_latesttime"));
|
utest.setTask_notice_master(rs.getInt("task_notice_master"));
|
utest.setTest_complete(rs.getInt("test_complete"));
|
utest.setTest_completetime(rs.getTimestamp("test_completetime"));
|
utest.setTest_starttime(rs.getTimestamp("test_starttime"));
|
utest.setTest_record_count_id(rs.getInt("test_record_count_id"));
|
utest.setNote(rs.getString("note"));
|
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
|
tbt.setBinf(binf);
|
tbt.setUtest(utest);
|
list.add(tbt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
public boolean add(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="insert into db_user.tb_user_task_batt_test(task_id,task_usr_ids,BattGroupId,task_enabled,task_exe_date,task_close_date," +
|
"task_notice_starttime,task_notice_endtime,task_notice_count,task_notice_latesttime,task_notice_master,test_complete," +
|
"test_completetime,test_starttime,test_record_count_id,note) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{utest.getTask_id(),utest.getTask_usr_ids(),
|
utest.getBattGroupId(),utest.getTask_enabled(),utest.getTask_exe_date(),utest.getTask_close_date(),utest.getTask_notice_starttime(),
|
utest.getTask_notice_endtime(),utest.getTask_notice_count(),utest.getTask_notice_latesttime(),utest.getTask_notice_master(),
|
utest.getTest_complete(),utest.getTest_completetime(),utest.getTest_starttime(),utest.getTest_record_count_id(),utest.getNote()});
|
return bl;
|
}
|
|
//4.1添加作业并确定
|
public String addPro(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="insert into db_user.tb_user_task_batt_test(task_id,task_usr_ids,BattGroupId,task_enabled,task_exe_date,task_close_date," +
|
"task_notice_starttime,task_notice_endtime,task_notice_count,task_notice_latesttime,task_notice_master,test_complete," +
|
"test_completetime,test_starttime,test_record_count_id,note) values("+utest.getTask_id()+",'"+utest.getTask_usr_ids()+"',"+
|
utest.getBattGroupId()+","+utest.getTask_enabled()+",'"+DAOHelper.sdf.format(utest.getTask_exe_date())+"','"+DAOHelper.sdf.format(utest.getTask_close_date())+"','"+DAOHelper.sdf.format(utest.getTask_notice_starttime())+"','"+
|
DAOHelper.sdf.format(utest.getTask_notice_endtime())+"',"+utest.getTask_notice_count()+",'"+DAOHelper.sdf.format(utest.getTask_notice_latesttime())+"',"+utest.getTask_notice_master()+","+
|
utest.getTest_complete()+",'"+DAOHelper.sdf.format(utest.getTest_completetime())+"','"+DAOHelper.sdf.format(utest.getTest_starttime())+"',"+utest.getTest_record_count_id()+",'"+utest.getNote()+"')";
|
return sql;
|
}
|
|
public boolean update(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="update db_user.tb_user_task_batt_test set task_id=?,task_usr_ids=?,BattGroupId=?,task_enabled=?,task_exe_date=?,task_close_date=?," +
|
"task_notice_starttime=?,task_notice_endtime=?,task_notice_count=?,task_notice_latesttime=?,task_notice_master=?,test_complete=?," +
|
"test_completetime=?,test_starttime=?,test_record_count_id=?,note=? where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{utest.getTask_id(),utest.getTask_usr_ids(),
|
utest.getBattGroupId(),utest.getTask_enabled(),utest.getTask_exe_date(),utest.getTask_close_date(),utest.getTask_notice_starttime(),
|
utest.getTask_notice_endtime(),utest.getTask_notice_count(),utest.getTask_notice_latesttime(),utest.getTask_notice_master(),
|
utest.getTest_complete(),utest.getTest_completetime(),utest.getTest_starttime(),utest.getTest_record_count_id(),utest.getNote(),
|
utest.getNum()});
|
return bl;
|
}
|
|
//4.1作业管理(修改执行人)(暂待定)
|
public boolean updateUser(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="update db_user.tb_user_task_batt_test set task_usr_ids=? where task_id=? ";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{utest.getTask_usr_ids()
|
,utest.getTask_id()});
|
return bl;
|
}
|
//4.1作业管理(作业变更申请中的修改)
|
public boolean updateTask(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="update db_user.tb_user_task_batt_test set task_usr_ids=?,task_exe_date=? where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{utest.getTask_usr_ids()
|
,utest.getTask_exe_date(),utest.getNum()});
|
return bl;
|
}
|
//4.1作业管理(作业删除)
|
public boolean del(Object obj) {
|
User_task_batt_test utest=(User_task_batt_test) obj;
|
String sql="delete from db_user.tb_user_task_batt_test where num=?";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{utest.getNum()});
|
return bl;
|
}
|
|
public List searchAll() {
|
String sql="select * from db_user.tb_user_task_batt_test";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new User_task_batt_testImpl());
|
return list;
|
|
}
|
//4.1根据task_id查询user_task_batt_test信息
|
public List serchByCondition(Object obj) {
|
User_task utask=(User_task) obj;
|
//System.out.println(utask);
|
String sql="select distinct(task_id),tb_user_task_batt_test.num,task_usr_ids,tb_user_task_batt_test.BattGroupId,task_enabled,task_exe_date,task_close_date," +
|
"task_notice_starttime,task_notice_endtime,task_notice_count,task_notice_latesttime,task_notice_master,test_complete," +
|
"test_completetime,test_starttime,test_record_count_id,note," +
|
"stationname ,battgroupname " +
|
"from db_user.tb_user_task_batt_test,db_battinf.tb_battinf " +
|
"where tb_user_task_batt_test.battgroupid=db_battinf.tb_battinf.battgroupid and task_id=? " +
|
"order by task_id ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{utask.getTask_id()}, new User_task_batt_testImpl());
|
//System.out.println(list.size());
|
return list;
|
}
|
|
//4.6作业报表(根据电池组信息以及统计方式查询user_task_batt_test信息)
|
public List serchByInfo(Object obj) {
|
Task_Batt_Test tbt=(Task_Batt_Test) obj;
|
//System.out.println(tbt.getUinf());
|
User_inf uinf=tbt.getUinf();
|
final BattInf binf=tbt.getBinf();
|
final User_task_batt_test utest=tbt.getUtest();
|
String sql="";
|
String baseSql="select distinct(tb_user_task_batt_test.BattGroupId),task_id,task_usr_ids,task_enabled,task_exe_date,task_close_date,task_notice_starttime,task_notice_endtime,task_notice_count,task_notice_latesttime,task_notice_master,test_complete,test_completetime,test_starttime,test_record_count_id,note " +
|
"from db_user.tb_user_task_batt_test ,db_battinf.tb_battinf " +
|
"where tb_user_task_batt_test.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
"and (battproductdate)>=(?) and (battproductdate)<=(?) " +
|
"and (battinusedate)>=(?) and (battinusedate)<=(?) " +
|
"and (task_exe_date)>=(?) and (task_exe_date)<=(?) ";
|
//用于维护区
|
String station1SqlT=" and stationname1!=? ";//全部
|
String station1SqlF=" and stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
//用于机房站点
|
String stationSqlT=" and stationname!=? ";//全部
|
String stationSqlF=" and stationname=? ";
|
if(binf.getStationName().equals("")){
|
baseSql+=stationSqlT;
|
}else{
|
baseSql+=stationSqlF;
|
}
|
//用于电池类型
|
String producerSqlT=" and battproducer!=? ";//全部
|
String producerSqlF=" and battproducer=? ";
|
if(binf.getBattProducer().equals("")){
|
baseSql+=producerSqlT;
|
}else{
|
baseSql+=producerSqlF;
|
}
|
|
//用于包机人
|
String baojiSqlT=" and tb_user_task_batt_test.BattGroupId not in (select distinct(db_battinf.tb_battinf.BattgroupId) 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_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.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 baojiSqlF=" and tb_user_task_batt_test.BattGroupId in (select distinct(db_battinf.tb_battinf.BattgroupId) 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_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.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=? and db_user.tb_user_inf.ubaojiusr=1 ) ";
|
|
if(uinf.getUId()==0){
|
baseSql+=baojiSqlT;
|
}else{
|
baseSql+=baojiSqlF;
|
}
|
//选择执行人
|
String userSqlT=" and task_usr_ids!=? ";
|
String userSqlF=" and task_usr_ids=? ";
|
if(utest.getTask_usr_ids().equals("0")){
|
baseSql+=userSqlT;
|
}else{
|
baseSql+=userSqlF;
|
}
|
//作业完成状态
|
String compeleteSqlT=" and test_complete!=? ";//全部
|
String compeleteSqlF=" and test_complete=? ";//未完成/已完成
|
if(utest.getTest_complete()==100){
|
baseSql+=compeleteSqlT;
|
}else{
|
baseSql+=compeleteSqlF;
|
if(utest.getTest_complete()==2){
|
//及时完成的条件
|
String rightNowSql=" and (test_completetime)<=(task_close_date) ";
|
baseSql+=rightNowSql;
|
}
|
}
|
//选取蓄电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? ";
|
String idSqlF=" and db_battinf.tb_battinf.battgroupid=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//选取系统类型
|
String nameSqlT=" and BattGroupName1 like ? ";
|
String nameSqlF=" and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ? ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
//用于单体电压
|
String volSqlT=" and monVolStd!=? ";//全部
|
String volSqlF=" and monVolStd=?" ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT=" and monCapStd!=? ";//全部
|
String capSqlF=" and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
String endSql=" order by task_exe_date ";
|
sql=baseSql+endSql;
|
//System.out.println("utest: "+sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),
|
new Object[]{
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
utest.getTask_exe_date(),
|
utest.getTask_exe_date1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
uinf.getUId(),
|
utest.getTask_usr_ids(),
|
utest.getTest_complete(),
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
},new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
User_task_batt_test u=new User_task_batt_test();
|
u.setTask_id(rs.getInt("task_id"));
|
u.setTask_usr_ids(rs.getString("task_usr_ids"));
|
u.setBattGroupId(rs.getInt("BattGroupId"));
|
u.setTask_enabled(rs.getInt("task_enabled"));
|
u.setTask_exe_date(rs.getTimestamp("task_exe_date"));
|
u.setTask_close_date(rs.getTimestamp("task_close_date"));
|
u.setTask_notice_starttime(rs.getTimestamp("task_notice_starttime"));
|
u.setTask_notice_endtime(rs.getTimestamp("task_notice_endtime"));
|
u.setTask_notice_count(rs.getInt("task_notice_count"));
|
u.setTask_notice_latesttime(rs.getTimestamp("task_notice_latesttime"));
|
u.setTask_notice_master(rs.getInt("task_notice_master"));
|
u.setTest_complete(rs.getInt("test_complete"));
|
u.setTest_completetime(rs.getTimestamp("test_completetime"));
|
u.setTest_starttime(rs.getTimestamp("test_starttime"));
|
u.setTest_record_count_id(rs.getInt("test_record_count_id"));
|
u.setNote(utest.getNote());
|
|
BattInf b=new BattInf();
|
if(binf.getStationName1().equals("")){
|
b.setStationName1("全部");
|
}else{
|
b.setStationName1(binf.getStationName1());
|
}
|
if(binf.getStationName().equals("")){
|
b.setStationName("全部");
|
}else{
|
b.setStationName(binf.getStationName());
|
}
|
if(binf.getBattGroupId()==0){
|
b.setBattGroupName("全部");
|
}else{
|
b.setBattGroupName(binf.getBattGroupName());
|
}
|
if(binf.getSignalName().equals("")){
|
b.setSignalName("全部");
|
}else{
|
b.setSignalName(binf.getSignalName());
|
}
|
tbt.setBinf(b);
|
tbt.setUtest(u);
|
list.add(tbt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public static void main(String[] args) throws ParseException {
|
User_task_batt_testService us = new User_task_batt_testService();
|
/*User_task_batt_test u=new User_task_batt_test();
|
u.setNum(10578);
|
us.serchByCondition(u);
|
//us.searchAll();*/
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Date date1 = sdf.parse("2000-01-01");
|
Date date2 = sdf.parse("2016-11-30");
|
Date date3 = sdf.parse("2000-01-01");
|
Date date4 = sdf.parse("2016-11-30");
|
|
User_task_batt_test utest=new User_task_batt_test();
|
utest.setTask_usr_ids("0");
|
utest.setTest_complete(100);//已完成:1 未完成:0 全部:100
|
utest.setTask_exe_date(date3);
|
utest.setTask_exe_date1(date4);
|
utest.setNote("1");//1:按月份 2:按季度 3:按年份
|
|
BattInf binf = new BattInf();
|
binf.setStationName("");
|
binf.setStationName1("");
|
binf.setBattGroupName("");//电池组名
|
binf.setSignalName("");//执行人
|
binf.setBattGroupId(0);
|
binf.setBattGroupName1("");
|
binf.setBattProducer("");
|
binf.setMonCapStd(0f);
|
binf.setMonVolStd(0f);
|
binf.setBattProductDate(ActionUtil.getSimpDate(date1));
|
binf.setBattProductDate1(ActionUtil.getSimpDate(date2));
|
binf.setBattInUseDate(ActionUtil.getSimpDate(date1));
|
binf.setBattInUseDate1(ActionUtil.getSimpDate(date2));
|
|
User_inf uinf=new User_inf();
|
uinf.setUId(0);
|
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
tbt.setBinf(binf);
|
tbt.setUtest(utest);
|
tbt.setUinf(uinf);
|
us.serchByInfo(tbt);
|
}
|
|
}
|
