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.BaseDAO;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.LimitNumberFactory;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.IDatabaseName;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.Batttestdata_inf;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.Task_Batt_Test;
|
import com.fgkj.dto.User_inf;
|
import com.fgkj.dto.User_task_change;
|
|
public class User_task_changeImpl implements BaseDAO,CallBack{
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
User_task_change uchange=new User_task_change();
|
uchange.setNum(rs.getInt("num"));
|
uchange.setUsr_id(rs.getInt("usr_id"));
|
uchange.setMaster_id(rs.getInt("master_id"));
|
uchange.setAppoint_uid(rs.getInt("appoint_uid"));
|
uchange.setCopy_uids(rs.getString("copy_uids"));
|
uchange.setChange_type_id(rs.getInt("change_type_id"));
|
uchange.setTask_type_id(rs.getInt("task_type_id"));
|
uchange.setTask_rc_num(rs.getInt("task_rc_num"));
|
uchange.setBattgroupId(rs.getInt("battgroupId"));
|
uchange.setChange_reason(rs.getString("change_reason"));
|
uchange.setOld_executor_ids(rs.getString("old_executor_ids"));
|
uchange.setOld_task_exe_time(rs.getTimestamp("old_task_exe_time"));
|
uchange.setNew_executor_ids(rs.getString("new_executor_ids"));
|
uchange.setNew_task_exe_time(rs.getTimestamp("new_task_exe_time"));
|
uchange.setChange_ask_time(rs.getTimestamp("change_ask_time"));
|
uchange.setChange_ask_time_limit(rs.getTimestamp("change_ask_time_limit"));
|
uchange.setTask_change_approve_time(rs.getTimestamp("task_change_approve_time"));
|
uchange.setTask_change_approve_res(rs.getInt("task_change_approve_res"));
|
uchange.setRemark(rs.getString("remark"));
|
|
list.add(uchange);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
//4.1作业变更申请
|
public boolean add(Object obj) {
|
User_task_change uchange=(User_task_change) obj;
|
String sql="insert into db_user.tb_user_task_change(usr_id,master_id,appoint_uid,copy_uids,change_type_id,task_type_id,task_rc_num" +
|
",battgroupid,change_reason,old_executor_ids,old_task_exe_time,new_executor_ids,new_task_exe_time,change_ask_time" +
|
",change_ask_time_limit,task_change_approve_time,task_change_approve_res,remark) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uchange.getUsr_id(),uchange.getMaster_id(),uchange.getAppoint_uid()
|
,uchange.getCopy_uids(),uchange.getChange_type_id(),uchange.getTask_type_id(),uchange.getTask_rc_num(),uchange.getBattgroupId(),uchange.getChange_reason()
|
,uchange.getOld_executor_ids(),uchange.getOld_task_exe_time(),uchange.getNew_executor_ids(),uchange.getNew_task_exe_time(),uchange.getChange_ask_time()
|
,uchange.getChange_ask_time_limit(),uchange.getTask_change_approve_time(),uchange.getTask_change_approve_res(),uchange.getRemark()});
|
return bl;
|
}
|
//4.5作业变更查询(审批)
|
public boolean update(Object obj) {
|
User_task_change uchange=(User_task_change) obj;
|
String sql="update db_user.tb_user_task_change set task_change_approve_res=?,remark=?,task_change_approve_time=? where num=? ";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uchange.getTask_change_approve_res(),uchange.getRemark(),uchange.getTask_change_approve_time(),uchange.getNum()});
|
return bl;
|
}
|
//4.5作业变更查询(删除记录)
|
public boolean del(Object obj) {
|
User_task_change uchange=(User_task_change)obj;
|
String sql="delete from db_user.tb_user_task_change where num=?";
|
boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uchange.getNum()});
|
return bl;
|
}
|
|
public List searchAll() {
|
|
return null;
|
}
|
//4.5作业变更查询
|
public List serchByCondition1(Object obj) {
|
Task_Batt_Test tbt=(Task_Batt_Test) obj;
|
BattInf binf=tbt.getBinf();
|
User_task_change uchange=tbt.getUchange();
|
String sql="";
|
String baseSql="select distinct(tb_user_task_change.battgroupid),battgroupname,stationname,tb_user_task_change.num,usr_id,master_id,appoint_uid,copy_uids,change_type_id,task_type_id,task_rc_num" +
|
",change_reason,old_executor_ids,old_task_exe_time,new_executor_ids,new_task_exe_time,change_ask_time,change_ask_time_limit,task_change_approve_time" +
|
",task_change_approve_res,remark " +
|
"from db_user.tb_user_task_change,db_battinf.tb_battinf " +
|
"where tb_user_task_change.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
"and (change_ask_time)>=(?) and (change_ask_time)<=(?) ";
|
|
//用于维护区
|
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 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 task_typeSqlT=" and task_type_id!=? ";//全部
|
String task_typeSqlF=" and task_type_id=? ";
|
if(uchange.getTask_type_id()==100){
|
baseSql+=task_typeSqlT;
|
// System.out.println("Task_type_id: "+uchange.getTask_type_id());
|
}else{
|
baseSql+=task_typeSqlF;
|
}
|
//用于作业变更申请人
|
String approveSqlT=" and usr_id!=? ";//全部
|
String approveSqlF=" and usr_id=? ";
|
if(uchange.getUsr_id()==0){
|
baseSql+=approveSqlT;
|
// System.out.println("usr_id: "+uchange.getUsr_id());
|
}else{
|
baseSql+=approveSqlF;
|
}
|
//用于作业变更类型
|
String change_typeSqlT=" and change_type_id!=? ";//全部
|
String change_typeSqlF=" and change_type_id=? ";
|
if(uchange.getChange_type_id()==100){
|
baseSql+=change_typeSqlT;
|
// System.out.println("Change_type_id: "+uchange.getChange_type_id());
|
}else{
|
baseSql+=change_typeSqlF;
|
}
|
//用于变更审批状态
|
String change_approveSqlT=" and task_change_approve_res!=? ";//全部
|
String change_approveSqlF=" and task_change_approve_res=? ";
|
String change_approveSqlD=" and task_change_approve_res in (0,?) ";//未批准
|
if(uchange.getTask_change_approve_res()==100){
|
baseSql+=change_approveSqlT;
|
// System.out.println("Task_change_approve_res: "+uchange.getTask_change_approve_res());
|
}else{
|
if(uchange.getTask_change_approve_res()==2){
|
baseSql+=change_approveSqlF;
|
}
|
else{
|
baseSql+=change_approveSqlD;
|
}
|
}
|
//按照battgroupid排序
|
String endSql=" order by tb_user_task_change.num ";
|
sql=baseSql+endSql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn()
|
,new Object[]{
|
uchange.getChange_ask_time(),
|
uchange.getChange_ask_time1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
uchange.getTask_type_id(),
|
uchange.getUsr_id(),
|
uchange.getChange_type_id(),
|
uchange.getTask_change_approve_res()
|
}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
User_task_change uchange=new User_task_change();
|
uchange.setNum(rs.getInt("num"));
|
uchange.setUsr_id(rs.getInt("usr_id"));
|
uchange.setMaster_id(rs.getInt("master_id"));
|
uchange.setAppoint_uid(rs.getInt("appoint_uid"));
|
uchange.setCopy_uids(rs.getString("copy_uids"));
|
uchange.setChange_type_id(rs.getInt("change_type_id"));
|
uchange.setTask_type_id(rs.getInt("task_type_id"));
|
uchange.setTask_rc_num(rs.getInt("task_rc_num"));
|
uchange.setBattgroupId(rs.getInt("battgroupId"));
|
uchange.setChange_reason(rs.getString("change_reason"));
|
uchange.setOld_executor_ids(rs.getString("old_executor_ids"));
|
uchange.setOld_task_exe_time(rs.getTimestamp("old_task_exe_time"));
|
uchange.setNew_executor_ids(rs.getString("new_executor_ids"));
|
uchange.setNew_task_exe_time(rs.getTimestamp("new_task_exe_time"));
|
uchange.setChange_ask_time(rs.getTimestamp("change_ask_time"));
|
uchange.setChange_ask_time_limit(rs.getTimestamp("change_ask_time_limit"));
|
uchange.setTask_change_approve_time(rs.getTimestamp("task_change_approve_time"));
|
uchange.setTask_change_approve_res(rs.getInt("task_change_approve_res"));
|
uchange.setRemark(rs.getString("remark"));
|
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setStationName(rs.getString("stationName"));
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
Page page=new Page();
|
tbt.setUchange(uchange);
|
tbt.setBinf(binf);
|
tbt.setPage(page);
|
list.add(tbt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//System.out.println(list.size());
|
List<Task_Batt_Test> listd=new ArrayList();
|
int index=0;
|
for(int i=(tbt.getPage().getPageCurr()-1)*tbt.getPage().getPageSize();i<tbt.getPage().getPageSize()*tbt.getPage().getPageCurr()&&i<list.size();i++){
|
listd.add((Task_Batt_Test) list.get(i));
|
//system.out.println(list.get(i));
|
((Task_Batt_Test)listd.get(index++)).getPage().setPageAll(list.size());
|
}
|
return listd;
|
}
|
//4.5作业变更查询
|
@SuppressWarnings("unchecked")
|
public List serchByCondition(Object obj) {
|
Task_Batt_Test tbt=(Task_Batt_Test) obj;
|
BattInf binf=tbt.getBinf();
|
User_task_change uchange=tbt.getUchange();
|
String sql="";
|
String numberSql=" SELECT FOUND_ROWS() number";
|
Connection conn=DBUtil.getConn();
|
String baseSql="select SQL_CALC_FOUND_ROWS distinct(tb_user_task_change.battgroupid),battgroupname,stationname,tb_user_task_change.num,usr_id,master_id,appoint_uid,copy_uids,change_type_id,task_type_id,task_rc_num" +
|
",change_reason,old_executor_ids,old_task_exe_time,new_executor_ids,new_task_exe_time,change_ask_time,change_ask_time_limit,task_change_approve_time" +
|
",task_change_approve_res,remark " +
|
"from db_user.tb_user_task_change,db_battinf.tb_battinf " +
|
"where tb_user_task_change.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
"and (change_ask_time)>=(?) and (change_ask_time)<=(?) ";
|
|
//用于维护区
|
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 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 task_typeSqlT=" and task_type_id!=? ";//全部
|
String task_typeSqlF=" and task_type_id=? ";
|
if(uchange.getTask_type_id()==100){
|
baseSql+=task_typeSqlT;
|
// System.out.println("Task_type_id: "+uchange.getTask_type_id());
|
}else{
|
baseSql+=task_typeSqlF;
|
}
|
//用于作业变更申请人
|
String approveSqlT=" and usr_id!=? ";//全部
|
String approveSqlF=" and usr_id=? ";
|
if(uchange.getUsr_id()==0){
|
baseSql+=approveSqlT;
|
// System.out.println("usr_id: "+uchange.getUsr_id());
|
}else{
|
baseSql+=approveSqlF;
|
}
|
//用于作业变更类型
|
String change_typeSqlT=" and change_type_id!=? ";//全部
|
String change_typeSqlF=" and change_type_id=? ";
|
if(uchange.getChange_type_id()==100){
|
baseSql+=change_typeSqlT;
|
// System.out.println("Change_type_id: "+uchange.getChange_type_id());
|
}else{
|
baseSql+=change_typeSqlF;
|
}
|
//用于变更审批状态
|
String change_approveSqlT=" and task_change_approve_res!=? ";//全部
|
String change_approveSqlF=" and task_change_approve_res=? ";
|
String change_approveSqlD=" and task_change_approve_res in (0,?) ";//未批准
|
if(uchange.getTask_change_approve_res()==100){
|
baseSql+=change_approveSqlT;
|
// System.out.println("Task_change_approve_res: "+uchange.getTask_change_approve_res());
|
}else{
|
if(uchange.getTask_change_approve_res()==2){
|
baseSql+=change_approveSqlF;
|
}
|
else{
|
baseSql+=change_approveSqlD;
|
}
|
}
|
//按照battgroupid排序
|
String endSql=" order by tb_user_task_change.battgroupid ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
List<Task_Batt_Test> list=DAOHelper.executeQueryLimit(sql, conn
|
,new Object[]{
|
uchange.getChange_ask_time(),
|
uchange.getChange_ask_time1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
|
binf.getBattGroupId(),
|
"%"+binf.getBattGroupName1()+"%",
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
uchange.getTask_type_id(),
|
uchange.getUsr_id(),
|
uchange.getChange_type_id(),
|
uchange.getTask_change_approve_res(),
|
(tbt.getPage().getPageCurr() - 1)* tbt.getPage().getPageSize(),
|
tbt.getPage().getPageSize()
|
}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
User_task_change uchange=new User_task_change();
|
uchange.setNum(rs.getInt("num"));
|
uchange.setUsr_id(rs.getInt("usr_id"));
|
uchange.setMaster_id(rs.getInt("master_id"));
|
uchange.setAppoint_uid(rs.getInt("appoint_uid"));
|
uchange.setCopy_uids(rs.getString("copy_uids"));
|
uchange.setChange_type_id(rs.getInt("change_type_id"));
|
uchange.setTask_type_id(rs.getInt("task_type_id"));
|
uchange.setTask_rc_num(rs.getInt("task_rc_num"));
|
uchange.setBattgroupId(rs.getInt("battgroupId"));
|
uchange.setChange_reason(rs.getString("change_reason"));
|
uchange.setOld_executor_ids(rs.getString("old_executor_ids"));
|
uchange.setOld_task_exe_time(rs.getTimestamp("old_task_exe_time"));
|
uchange.setNew_executor_ids(rs.getString("new_executor_ids"));
|
uchange.setNew_task_exe_time(rs.getTimestamp("new_task_exe_time"));
|
uchange.setChange_ask_time(rs.getTimestamp("change_ask_time"));
|
uchange.setChange_ask_time_limit(rs.getTimestamp("change_ask_time_limit"));
|
uchange.setTask_change_approve_time(rs.getTimestamp("task_change_approve_time"));
|
uchange.setTask_change_approve_res(rs.getInt("task_change_approve_res"));
|
uchange.setRemark(rs.getString("remark"));
|
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
binf.setStationName(rs.getString("stationName"));
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
Page page=new Page();
|
tbt.setUchange(uchange);
|
tbt.setBinf(binf);
|
tbt.setPage(page);
|
list.add(tbt);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//System.out.println(list.size());
|
/*List<Task_Batt_Test> listd=new ArrayList();
|
int index=0;
|
for(int i=(tbt.getPage().getPageCurr()-1)*tbt.getPage().getPageSize();i<tbt.getPage().getPageSize()*tbt.getPage().getPageCurr()&&i<list.size();i++){
|
listd.add((Task_Batt_Test) list.get(i));
|
//system.out.println(list.get(i));
|
((Task_Batt_Test)listd.get(index++)).getPage().setPageAll(list.size());
|
}*/
|
//去掉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;
|
}
|
public List serchByInfo(Object obj) {
|
// 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.setBattGroupId(0);
|
binf.setBattGroupName1("");
|
binf.setBattProducer("");
|
binf.setMonCapStd(0f);
|
binf.setMonVolStd(0f);
|
|
User_task_change uchange=new User_task_change();
|
uchange.setTask_type_id(100); //作业类型 100:全部
|
uchange.setChange_type_id(100); //作业变更类型
|
uchange.setUsr_id(0); //作业变更申请人
|
uchange.setTask_change_approve_res(0); //变更审批状态
|
uchange.setChange_ask_time(ActionUtil.getSimpDate(date1));
|
uchange.setChange_ask_time1(ActionUtil.getSimpDate(date2));
|
|
Page page=new Page();
|
page.setPageCurr(1);
|
page.setPageSize(10);
|
|
Task_Batt_Test tbt=new Task_Batt_Test();
|
tbt.setBinf(binf);
|
tbt.setUchange(uchange);
|
tbt.setPage(page);
|
|
User_task_changeImpl uimpl=new User_task_changeImpl();
|
List<Task_Batt_Test> list=uimpl.serchByCondition(tbt);
|
for (Task_Batt_Test t : list) {
|
System.out.println(t.getUchange());
|
}
|
System.out.println(list.size());
|
}
|
}
|
