package com.fgkj.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
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.dao.UinfDaoFactory;
import com.fgkj.db.DBUtil;
import com.fgkj.db.IDatabaseName;
import com.fgkj.dto.BattInf;
import com.fgkj.dto.Batt_User_Permit;
import com.fgkj.dto.Page;
import com.fgkj.dto.User_battgroup_baojigroup;
import com.fgkj.dto.User_battgroup_baojigroup_battgroup;
import com.fgkj.dto.User_inf;
import com.fgkj.dto.User_task_batt_template;

public class User_infImpl implements BaseDAO, CallBack {

	public List getResults(ResultSet rs) {
		List list = new ArrayList();
		try {
			while (rs.next()) {
				User_inf userinf = new User_inf();

				userinf.setUId(rs.getInt("uId"));

				userinf.setUSnId(rs.getString("uSnId"));

				userinf.setUName(rs.getString("uName"));

				userinf.setUShenFenId(rs.getString("uShenFenId"));

				userinf.setUEmployeeId(rs.getString("uEmployeeId"));

				userinf.setUTelephone(rs.getString("uTelephone"));

				userinf.setUMobilephone(rs.getString("uMobilephone"));

				userinf.setUEmail(rs.getString("uEmail"));

				userinf.setUAddr(rs.getString("uAddr"));

				userinf.setUBirthDay((Date) rs.getObject("uBirthDay"));

				userinf.setUAccessionDay((Date) rs.getObject("uAccessionDay"));

				userinf.setUSex(rs.getString("uSex"));

				userinf.setUDepartment(rs.getString("uDepartment"));

				userinf.setUProTitle(rs.getString("uProTitle"));

				userinf.setUAuthority(rs.getString("uAuthority"));

				userinf.setUJobGroup(rs.getString("uJobGroup"));

				userinf.setUDuties(rs.getString("uDuties"));

				userinf.setUTasks(rs.getString("uTasks"));

				userinf.setUBaojiusr(Integer.parseInt(rs.getString("uBaojiUsr")));

				userinf.setUNote(rs.getString("uNote"));
				
				//userinf.setUpassword(rs.getString("upassword"));

				list.add(userinf);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	//5.1用户管理(添加)
	public boolean add(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql="insert into db_user.tb_user_inf(uSnId,uPassword,uName,uShenFenId,uEmployeeId,uTelephone,uMobilephone,uEmail,uAddr,uBirthday,uAccessionDay," +
				"uSex,uDepartment,uProTitle,uAuthority,uJobGroup,uDuties,uTasks,uBaojiusr,uNote) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uinf.getUSnId(),uinf.getUpassword(),uinf.getUName(),uinf.getUShenFenId(),
			uinf.getUEmployeeId(),uinf.getUTelephone(),uinf.getUMobilephone(),uinf.getUEmail(),uinf.getUAddr(),uinf.getUBirthDay(),uinf.getUAccessionDay(),
			uinf.getUSex(),uinf.getUDepartment(),uinf.getUProTitle(),uinf.getUAuthority(),uinf.getUJobGroup(),uinf.getUDuties(),uinf.getUTasks(),uinf.getUBaojiusr(),
			uinf.getUNote()});
		return bl;
	}
	//5.1用户管理(添加时判断用户名是否重复)
	public int addJudge(Object obj){
		User_inf uinf=(User_inf) obj;
		String sql=" select count(uid) as nums from db_user.tb_user_inf where uname=? limit 1";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUName()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						int nums=rs.getInt("nums");
						list.add(nums);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		int flag=1;
		if(list!=null&&list.size()>0){
			int nums=(Integer) list.get(list.size()-1);
			//System.out.println(nums);
			if(nums>0){
				flag=0;
			}else{
				flag=1;
			}
		}
		return flag;
	}

	//5.1用户管理(编辑)
	public boolean update(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql="update db_user.tb_user_inf set uName=?,uShenFenId=?,uEmployeeId=?,uTelephone=?,uMobilephone=?,uEmail=?,uAddr=?,uBirthday=?,uAccessionDay=?," +
				"uSex=?,uDepartment=?,uProTitle=?,uAuthority=?,uJobGroup=?,uDuties=?,uTasks=?,uBaojiusr=?,uNote=? where uId=?";
		Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uinf.getUName(),uinf.getUShenFenId(),
			uinf.getUEmployeeId(),uinf.getUTelephone(),uinf.getUMobilephone(),uinf.getUEmail(),uinf.getUAddr(),uinf.getUBirthDay(),uinf.getUAccessionDay(),
			uinf.getUSex(),uinf.getUDepartment(),uinf.getUProTitle(),uinf.getUAuthority(),uinf.getUJobGroup(),uinf.getUDuties(),uinf.getUTasks(),uinf.getUBaojiusr(),
			uinf.getUNote(),uinf.getUId()});
		return bl;
	}

	//5.1用户管理(删除)
	public boolean del(Object obj) {
		User_inf uinf=(User_inf) obj;
		boolean bl = false;
		String sql="delete from db_user.tb_user_inf where uId=? ";
		bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uinf.getUId()});
		return bl;
	}

	//首页会根据uname查询用户信息
	public List serchByCondition(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql = "select uid,uname,uSnId,upassword,unote from db_user.tb_user_inf where uname=?";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUName()},
						new CallBack() {
							
							public List getResults(ResultSet rs) {
								List list=new ArrayList();
								try {
									while(rs.next()){
										User_inf uinf=new User_inf();
										uinf.setUId(rs.getInt("uId"));
										uinf.setUName(rs.getString("uName"));
										uinf.setUSnId(rs.getString("uSnId"));
										uinf.setUNote(rs.getString("uNote"));
										uinf.setUpassword(rs.getString("upassword"));
										list.add(uinf);
									}
								} catch (SQLException e) {
									e.printStackTrace();
								}															
								return list;
							}
						});
		return list;
	}
	//查询当前库中存在的name名字的排序(模糊)
	public List serchinfoUname() {
		String sql = "select uid,uname from db_user.tb_user_inf where uname like '%name%' order by uname";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(),null,
						new CallBack() {
							
							public List getResults(ResultSet rs) {
								List list=new ArrayList();
								try {
									while(rs.next()){
										User_inf uinf=new User_inf();
										uinf.setUId(rs.getInt("uId"));
										uinf.setUName(rs.getString("uName"));
										list.add(uinf);
									}
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}															
								return list;
							}
						});
		return list;
	}
	//5.3根据uid查包机组
	public List serchByInfo(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql = "select distinct(baoji_group_name) " +
				"from db_user.tb_user_battgroup_baojigroup,db_user.tb_user_battgroup_baojigroup_usr  " +
				"where tb_user_battgroup_baojigroup_usr.baoji_group_id=tb_user_battgroup_baojigroup.baoji_group_id " +
				"and tb_user_battgroup_baojigroup_usr.uId=?";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()},
						new CallBack() {
							
							public List getResults(ResultSet rs) {
								List list=new ArrayList();
									try {
										while(rs.next()){
											User_battgroup_baojigroup u=new User_battgroup_baojigroup();
											u.setBaoji_group_name(rs.getString("baoji_group_name"));
											list.add(u);
										}
									} catch (SQLException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
									}
									return list;
							}
							
						});
		return list;
	}
	//根据battgroupid查对应的包机人
	public List searchByBattGroupId(Object obj){
		User_battgroup_baojigroup_battgroup ugroup=(User_battgroup_baojigroup_battgroup) obj;
		String sql="select distinct(db_user.tb_user_inf.uid),db_user.tb_user_inf.uName,db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId " +
				"from db_user.tb_user_inf ,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr " +
				"where 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_battgroup_baojigroup_usr.uId=db_user.tb_user_inf.uid " +
				"and db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=? and db_user.tb_user_inf.uBaojiUsr=1 ";
		
		
		//System.out.println(sql);
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{ugroup.getBattGroupId()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						User_inf uinf=new User_inf();
						uinf.setUSnId(Integer.toString((rs.getInt("battgroupid"))));
						uinf.setUId(rs.getInt("uId"));
						uinf.setUName(rs.getString("uName"));
						list.add(uinf);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}

    //根据uId查用户姓名
	public List serchUname(Object obj) {
		User_inf uinf=(User_inf) obj;
		String sql = "select uName from db_user.tb_user_inf where uId=?";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()},
						new CallBack() {
							
							public List getResults(ResultSet rs) {
								List list=new ArrayList();
									try {
										while(rs.next()){
											User_inf uinf=new User_inf();						
											uinf.setUName(rs.getString("uName"));
											list.add(uinf);
										}
									} catch (SQLException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
									}
									return list;
							}
							
						});
		return list;
	}	
	
	//根据uId查用户姓名
	public String changeUidToUname(Object obj) {
		int uid=(Integer) obj;
		String sql = "select distinct uName from db_user.tb_user_inf where uId=? limit 1";
		List list = DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uid},new CallBack() {
						public List getResults(ResultSet rs) {
							List list=new ArrayList();
								try {
									while(rs.next()){
										String uname="";
										if(rs.getString("uname")!=null){
											uname=rs.getString("uname");
											list.add(uname);
										}
									}
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								return list;
						}
						
					});
		String uname="";
		if(list!=null&&list.size()>0){
			uname=(String) list.get(list.size()-1);
		}
		return uname;
	}	
	//查询所有uid在字符串objs中的所有用户名,并且用，连接多个用户名
	public List searchUnams(Object objs){		
		String sql = "SELECT GROUP_CONCAT(DISTINCT(uname))as unames from db_user.tb_user_inf where locate(uid,?);";
		return DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{objs.toString()}, new CallBack() {
			public List getResults(ResultSet rs) {
				List list = new ArrayList();
				try {
					while(rs.next()){
						String uname = rs.getString("unames");
						if(uname != null && uname.length() >0){
							list.add(rs.getString("unames"));
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
		});
	}
		
	//5.1查询所有员工信息
	public List searchAll(Object obj) {
		Batt_User_Permit bup=(Batt_User_Permit) obj;
		//System.out.println(bup);
		String sql = "select distinct(tb_user_inf.uid),uSnId,uName,uShenFenId,uEmployeeId,uTelephone,uMobilephone,uEmail,uAddr,uBirthday,uAccessionDay,uSex,uDepartment,uProTitle,uAuthority,uJobGroup,uDuties,uTasks,uBaojiusr,uNote" +
				",permit_group_name " +
				"from db_user.tb_user_inf " +
				"left outer join db_user.tb_user_permitgroup_data on db_user.tb_user_inf.uid=db_user.tb_user_permitgroup_data.uid " +
				"left outer join db_user.tb_user_permitgroup on db_user.tb_user_permitgroup.permit_group_id=db_user.tb_user_permitgroup_data.permit_group_id " +
				"where  tb_user_inf.uid!=1002 " +
				"order by uid";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(), null,
						new CallBack() {
					public List getResults(ResultSet rs) {
						List list = new ArrayList();
						int id=0;
						String permit="";
						try {
							while (rs.next()) {
							
								Batt_User_Permit bup=new Batt_User_Permit();
								
								User_inf userinf = new User_inf();
								userinf.setUId(rs.getInt("uId"));
								if(id==rs.getInt("uId")){
									continue;
								}else{
									id=rs.getInt("uId");
								}
								userinf.setUSnId(rs.getString("uSnId"));
								userinf.setUName(rs.getString("uName"));
								userinf.setUShenFenId(rs.getString("uShenFenId"));
								userinf.setUEmployeeId(rs.getString("uEmployeeId"));
								userinf.setUTelephone(rs.getString("uTelephone"));
								userinf.setUMobilephone(rs.getString("uMobilephone"));
								userinf.setUEmail(rs.getString("uEmail"));
								userinf.setUAddr(rs.getString("uAddr"));
								userinf.setUBirthDay((Date) rs.getObject("uBirthDay"));
								userinf.setUAccessionDay((Date) rs.getObject("uAccessionDay"));
								userinf.setUSex(rs.getString("uSex"));
								userinf.setUDepartment(rs.getString("uDepartment"));
								userinf.setUProTitle(rs.getString("uProTitle"));
								userinf.setUAuthority(rs.getString("uAuthority"));
								userinf.setUJobGroup(rs.getString("uJobGroup"));
								userinf.setUDuties(rs.getString("uDuties"));
								userinf.setUTasks(rs.getString("uTasks"));
								userinf.setUBaojiusr(Integer.parseInt(rs.getString("uBaojiUsr")));
								userinf.setUNote(rs.getString("uNote"));		
								
								Page page=new Page();
								bup.setUinf(userinf);
								bup.setPage(page);
								
								list.add(bup);
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
						return list;
					}
						});
		List<Batt_User_Permit> listd=new ArrayList();
		/*for(int i=0;i<list.size();i++){
			System.out.println(((Batt_Maint_Dealarm)list.get(i)).getBinf().getBattGroupId());
		}*/
		int index=0;
		
		//System.out.println(list);
		for(int i=(bup.getPage().getPageCurr()-1)*bup.getPage().getPageSize();i<bup.getPage().getPageSize()*bup.getPage().getPageCurr()&&i<list.size();i++){
			
			listd.add((Batt_User_Permit) list.get(i));
			//System.out.println(list.get(i));
			((Batt_User_Permit)listd.get(index++)).getPage().setPageAll(list.size());
		}
		return listd;
	}

	//5.1查询所有员工信息
	public List searchAll() {
		String sql = "select distinct(tb_user_inf.uid),uSnId,uName,uShenFenId,uEmployeeId,uTelephone,uMobilephone,uEmail,uAddr,uBirthday,uAccessionDay,uSex,uDepartment,uProTitle,uAuthority,uJobGroup,uDuties,uTasks,uBaojiusr,uNote" +
				",permit_group_name " +
				"from db_user.tb_user_inf " +
				"left outer join db_user.tb_user_permitgroup_data on db_user.tb_user_inf.uid=tb_user_permitgroup_data.uid " +
				"left outer join db_user.tb_user_permitgroup on db_user.tb_user_permitgroup.permit_group_id=tb_user_permitgroup_data.permit_group_id " +
				"where  tb_user_inf.uid!=1002 " +
				"order by uid";
		List list = DAOHelper
				.executeQuery(sql, DBUtil.getConn(), null,
						new CallBack() {
					public List getResults(ResultSet rs) {
						List list = new ArrayList();
						int id=0;
						String permit="";
						try {
							while (rs.next()) {
							
								
								
								User_inf userinf = new User_inf();
								userinf.setUId(rs.getInt("uId"));
								if(id==rs.getInt("uId")){
									continue;
								}else{
									id=rs.getInt("uId");
								}
								userinf.setUSnId(rs.getString("uSnId"));
								userinf.setUName(rs.getString("uName"));
								userinf.setUShenFenId(rs.getString("uShenFenId"));
								userinf.setUEmployeeId(rs.getString("uEmployeeId"));
								userinf.setUTelephone(rs.getString("uTelephone"));
								userinf.setUMobilephone(rs.getString("uMobilephone"));
								userinf.setUEmail(rs.getString("uEmail"));
								userinf.setUAddr(rs.getString("uAddr"));
								userinf.setUBirthDay(rs.getTimestamp("uBirthDay"));
								userinf.setUAccessionDay(rs.getTimestamp("uAccessionDay"));
								userinf.setUSex(rs.getString("uSex"));
								userinf.setUDepartment(rs.getString("uDepartment"));
								userinf.setUProTitle(rs.getString("uProTitle"));
								userinf.setUAuthority(rs.getString("uAuthority"));
								userinf.setUJobGroup(rs.getString("uJobGroup"));
								userinf.setUDuties(rs.getString("uDuties"));
								userinf.setUTasks(rs.getString("uTasks"));
								userinf.setUBaojiusr(Integer.parseInt(rs.getString("uBaojiUsr")));
								userinf.setUNote(rs.getString("uNote"));
								
								list.add(userinf);
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
						return list;
					}
						});
		return list;
	}
	//4.1查询维护区对应的维护人员个数(由模板创建作业时)
	public List serchDepNum(Object obj){
		User_task_batt_template utem=(User_task_batt_template) obj;
		String sql="select distinct(uid) from db_user.tb_user_inf where db_user.tb_user_inf.ubaojiusr=1 and db_user.tb_user_inf.udepartment=? ";
		List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{utem.getQuyu_name()}, new CallBack() {
			
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						User_inf uinf=new User_inf();
						uinf.setUId(rs.getInt("uId"));
						list.add(uinf);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		return list;
	}
     
	//修改用户密码
	public boolean updatePassword(Object obj){
		User_inf uinf=(User_inf) obj;
		String sql="update db_user.tb_user_inf set usnid=? , upassword=? where uid=?";
	    Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{uinf.getUSnId(),uinf.getUpassword(),uinf.getUId()});
	    return bl;
	}
	//重置用户密码
	public boolean resetPassword(Object obj){
		User_inf uinf=(User_inf) obj;
		String sql="update db_user.tb_user_inf set usnid=?,upassword=? where uid=?";
	    Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, new Object[]{UinfDaoFactory.MR_PWD,(String)ActionUtil.EncryptionMD5(UinfDaoFactory.MR_PWD),uinf.getUId()});
	    return bl;
	}
	
	//根据员工信息和员工编号去匹配合适的员工
	public List searchByNameOrId(Object obj){
		Batt_User_Permit bup=(Batt_User_Permit) obj;
		User_inf uinf=bup.getUinf();
		Page p=bup.getPage();
		Connection conn=DBUtil.getConn();
		String numberSql=" SELECT FOUND_ROWS() number";
		String sql="select SQL_CALC_FOUND_ROWS distinct(uid),uSnId,uName,uShenFenId,uEmployeeId,uTelephone,uMobilephone,uEmail,uAddr,uBirthday,uAccessionDay,uSex,uDepartment,uProTitle,uAuthority,uJobGroup,uDuties,uTasks,uBaojiusr,uNote" +
				" from db_user.tb_user_inf " +
				" where  uName like ? or uEmployeeId like ? " +
				" order by uid " +
				" limit ?,?";
		//System.out.println(sql);
		List<User_inf> list=DAOHelper.executeQueryLimit(sql, conn, new Object[]{"%"+uinf.getUName()+"%","%"+uinf.getUEmployeeId()+"%",(p.getPageCurr()-1)*p.getPageSize(),p.getPageSize()}, new CallBack() {
			public List getResults(ResultSet rs) {
				List list=new ArrayList();
				try {
					while(rs.next()){
						User_inf userinf = new User_inf();
						userinf.setUId(rs.getInt("uId"));
						userinf.setUSnId(rs.getString("uSnId"));
						userinf.setUName(rs.getString("uName"));
						userinf.setUShenFenId(rs.getString("uShenFenId"));
						userinf.setUEmployeeId(rs.getString("uEmployeeId"));
						userinf.setUTelephone(rs.getString("uTelephone"));
						userinf.setUMobilephone(rs.getString("uMobilephone"));
						userinf.setUEmail(rs.getString("uEmail"));
						userinf.setUAddr(rs.getString("uAddr"));
						userinf.setUBirthDay(rs.getTimestamp("uBirthDay"));
						userinf.setUAccessionDay(rs.getTimestamp("uAccessionDay"));
						userinf.setUSex(rs.getString("uSex"));
						userinf.setUDepartment(rs.getString("uDepartment"));
						userinf.setUProTitle(rs.getString("uProTitle"));
						userinf.setUAuthority(rs.getString("uAuthority"));
						userinf.setUJobGroup(rs.getString("uJobGroup"));
						userinf.setUDuties(rs.getString("uDuties"));
						userinf.setUTasks(rs.getString("uTasks"));
						userinf.setUBaojiusr(Integer.parseInt(rs.getString("uBaojiUsr")));
						userinf.setUNote(rs.getString("uNote"));
						list.add(userinf);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			}
		});
		//去掉limit条件后的总数
		int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
		if(list!=null&&list.size()>0){
			list.get(list.size()-1).setUNote(String.valueOf(number));
		}
		return list;
	}
	
	public static void main(String[] args) {
		User_infImpl ui= new User_infImpl();
		List<User_inf> list=ui.searchAll(); 
		for (User_inf u : list) {
			System.out.println(u);
		}
		User_inf u=new User_inf();
		
		u.setUId(1003);
//		List<User_inf> list=ui.serchByCondition(u); 
//		for (User_inf uu : list) {
//			System.out.println(uu);
//		}
		
		Batt_User_Permit bup=new Batt_User_Permit();
		Page page=new Page();
		page.setPageSize(2);
		page.setPageCurr(1);
		bup.setPage(page);
//		List<Batt_User_Permit> list=ui.searchAll(bup);
//		for (Batt_User_Permit object : list) {
//			System.out.println(object);
//		}
		
		ui.searchUnams("01001,01002,01003");
	}
	
	
}