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

}