package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.UserBean;


public class UserDao {
/**
 * 
 * 
 * 
 * 员工
 * 
 * 
 * 
 * 
 * ***/	
	//用于员工增加
		public void Staffinsert(UserBean userbean) throws ClassNotFoundException,SQLException {
			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "insert into p_staff (t_user,t_name,t_pwd,t_email,t_dept,t_no,t_auz)"
					+ "values(?,?,?,?,?,?,?)";
			// 数据库添加语句
			stat = conn.prepareStatement(sql);
			stat.setString(1, userbean.getUser());
			stat.setString(2, userbean.getName());
			stat.setString(3, userbean.getPwd());
			stat.setString(4, userbean.getEmail());
			stat.setString(5, userbean.getDept());
			stat.setInt(6, userbean.getNo());
			stat.setInt(7, 0);
			stat.executeUpdate();
			}

		// 用于数据库中员工是否注册
		public boolean check(String user)
				throws ClassNotFoundException, SQLException {

			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "select * from p_staff where t_user = ? ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, user);
			
			ResultSet rs = stat.executeQuery();
			while (rs.next()) {
				 rs.close();
			      stat.close();  
			       conn.close();
				return true;
			} 
			rs.close();
		      stat.close();  
		       conn.close();
			
			return false;

		}
		// 用于数据库中员工修改密码信息判斷
		public boolean checkpwd(String user, String name, String email,
				String dept, String no) throws ClassNotFoundException, SQLException {
			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "select * from p_staff where t_user = ? and t_name = ? and t_email = ? and t_dept = ? and t_no = ? ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, user);
			stat.setString(2, name);
			stat.setString(3, email);
			stat.setString(4, dept);
			stat.setString(5, no);

			ResultSet rs = stat.executeQuery();
			while (rs.next()) {
				 rs.close();
			      stat.close();  
			       conn.close();
				return true;
			}
			 rs.close();
		      stat.close();  
		       conn.close();
		       return false;

		}
		//员工修改密码
		public void update(String user , String pwd) throws ClassNotFoundException, SQLException{
			
			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "UPDATE p_staff SET t_pwd = ? WHERE t_user = ? ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, pwd);
			stat.setString(2, user);
			stat.executeUpdate();
			
		      stat.close();  
		       conn.close();
		}
	   
	
	/**
 * 管理员
 */
		
// 用于数据库中管理员是否注册
		public boolean checkadmin(String user)
				throws ClassNotFoundException, SQLException {

			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "select * from p_admin where a_user = ?  ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, user);
		
			ResultSet rs = stat.executeQuery();
			while (rs.next()) {
				 rs.close();
			      stat.close();  
			       conn.close();
				return true;
			} 
			rs.close();
		      stat.close();  
		       conn.close();
			return false;

		}
	// 用于数据库中管理员修改密码信息判斷
		public boolean checkadminpwd(String user, String name, String email,
				String dept, String no) throws ClassNotFoundException, SQLException {
			Connection conn = null;
			PreparedStatement stat = null;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
					"root", "root");
			String sql = "select * from p_admin where a_user = ? and a_name = ? and a_email = ? and a_dept = ? and a_no = ? ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, user);
			stat.setString(2, name);
			stat.setString(3, email);
			stat.setString(4, dept);
			stat.setString(5, no);

			ResultSet rs = stat.executeQuery();
			while (rs.next()) {
				 rs.close();
			      stat.close();  
			       conn.close();
				return true;
			}
			 rs.close();
		      stat.close();  
		       conn.close();
			return false;

		}

		//管理员修改密码
	    public void updateadmin(String user , String pwd) throws ClassNotFoundException, SQLException{
				
				Connection conn = null;
				PreparedStatement stat = null;
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
						"root", "root");
				String sql = "UPDATE p_admin SET a_pwd = ? WHERE a_user = ? ";
				stat = conn.prepareStatement(sql);
				stat.setString(1, pwd);
				stat.setString(2, user);
				stat.executeUpdate();
				 
			      stat.close();  
			       conn.close();
			}
			
	
	  //用于管理增加
	  		public void Admininsert(UserBean userbean) throws ClassNotFoundException,SQLException {
	  			Connection conn = null;
	  			PreparedStatement stat = null;
	  			Class.forName("com.mysql.jdbc.Driver");
	  			conn = DriverManager.getConnection("jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
	  					"root", "root");
	  			String sql = "insert into p_admin (a_user,a_name,a_pwd,a_email,a_dept,a_no,a_auz)"
	  					+ "values(?,?,?,?,?,?,?)";
	  			// 数据库添加语句
	  			stat = conn.prepareStatement(sql);
	  			stat.setString(1, userbean.getUser());
	  			stat.setString(2, userbean.getName());
	  			stat.setString(3, userbean.getPwd());
	  			stat.setString(4, userbean.getEmail());
	  			stat.setString(5, userbean.getDept());
	  			stat.setInt(6, userbean.getNo());
	  			stat.setInt(7, 0);
	  			stat.executeUpdate();
	  			}	    
	    
	    
	    // 用于p_dept数据库中数据全部取出
	    public List<String> celectdeptall() throws ClassNotFoundException,
		SQLException {

	    	List<String> list = new ArrayList<String>();
	    	Connection conn = null;
	    	PreparedStatement stat = null;
	    	Class.forName("com.mysql.jdbc.Driver");
	    	conn = DriverManager
	    			.getConnection(
	    					"jdbc:mysql://localhost/person?useUnicode=true&characterEncoding=utf8",
	    					"root", "root");
	    	String sql = "select * from p_dept ";
	    	stat = conn.prepareStatement(sql);
	    	ResultSet rs = stat.executeQuery();
	    	while (rs.next()) {
	    		String user = new String();

	    		user = rs.getString("t_dept");
	    		list.add(user);
	    	}
	    	rs.close();
	    	stat.close();  
	    	 conn.close(); 
	    	return list;
	}


 
}