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());
	}
}