/**
 * -----------------------------------------------------------------------------
 * 創建時間:2006年2月15日
 * 作    者:孫豐偉
 * 功	 能:員工管理,系統對員工的管理主要有(參看案例分析中的用例圖):
 * 			1.建立新員工,    method: addEmployee()
 * 			2.修改員工,	     method: updateEmployee()
 * 			3.刪除員工,      method: removeEmployeeById,要確保員工
 * 							 在其他表中不被引用
 * 			4.模糊查詢       method: getEmployees()
 * 			5.根據部門查詢員工,	method: getEmployeesByDepartment()
 * 			6.返回所有員工,  method: getAllEmployees()
 * 			7.查找最后一個員工號,method:getLastEmployeeId()
 * ------------------------------------------------------------------------------
 */
package cn.sunfengwei.employee.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Vector;

import cn.sunfengwei.employee.db.ConnectDB;


public class EmployeeDAO {

	private EmployeeDTO employee;
	private Connection con;
	private PreparedStatement pstmt;
	private Statement stmt;
	private ResultSet rs;
	public EmployeeDAO() {
		
	}
	
	
//	1.添加新員工
	public boolean addEmployee(EmployeeDTO employee)
	{
		boolean flag = false;
		con = ConnectDB.getConnection();
		String strQuery = "insert into employee values(?,?,?,?,?,?)";
		try {
			pstmt = con.prepareStatement(strQuery);
			pstmt.setInt(1, employee.getEID());
			pstmt.setString(2, employee.getName());
			pstmt.setString(3, employee.getSex());
			pstmt.setDate(4, employee.getBirthday());
			pstmt.setInt(5,employee.getDepartmentId());
			pstmt.setString(6, employee.getPhone());
			if (pstmt.executeUpdate() > 0)
				flag = true;
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			//e.printStackTrace();
		}
		return flag;
	}
	//2.修改員工信息
	public boolean updateEmployee(EmployeeDTO employee)
	{
		boolean flag = false;
		con = ConnectDB.getConnection();
		String strQuery ="update employee set name=?,sex=?,birthday=?,departmentId=?,phone=? where eid=?";
		try {
			pstmt = con.prepareStatement(strQuery);
			pstmt.setString(1, employee.getName());
			pstmt.setString(2, employee.getSex());
			pstmt.setDate(3, employee.getBirthday());
			pstmt.setInt(4,employee.getDepartmentId());
			pstmt.setString(5, employee.getPhone());
			pstmt.setInt(6, employee.getEID());
			if (pstmt.executeUpdate() > 0)
				flag = true;
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			System.out.println(e.getMessage());
		}
		return flag;
	}
//	3.移除員工
	public boolean removeEmployeeById(int EID)
	{
		boolean flag = false;
		con = ConnectDB.getConnection();
		String strQuery = "delete from employee where EID=?";
		try {
			pstmt = con.prepareStatement(strQuery);
			
			pstmt.setInt(1,EID);
			if (pstmt.executeUpdate() > 0)
				flag = true;
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
		}
		return flag;
	
	}

	//4. 此方法用來實現模糊查詢,程序中暫未實現
	public Collection getEmployees(EmployeeDTO Employee)
	{
		Vector departments=new Vector();
		
		return departments;
	}
	//5. 根據部門查找員工
public Vector getEmployeesByDepartment(int departmentId)
{
	//HashMap departments=new HashMap();
	Vector departments=new Vector();
	con = ConnectDB.getConnection();
	String strQuery = "select * from viewEmployee where 部門編號=?";
	
	try {
		pstmt = con.prepareStatement(strQuery);
		pstmt.setInt(1, departmentId);
		rs=pstmt.executeQuery();
		while(rs.next())
		{
		this.employee=new EmployeeDTO();
		this.employee.setEID(rs.getInt(1)); 
		this.employee.setName(rs.getString(2));
		this.employee.setSex(rs.getString(3));
		this.employee.setBirthday(rs.getDate(4));
		this.employee.setDepartmentId(rs.getInt(5));
		this.employee.setDepartmentName(rs.getString(6));
		this.employee.setPhone(rs.getString(7));
		//departments.put(new Integer(this.employee.getEID()),this.employee);
		departments.add(this.employee);
		}
		rs.close();
		pstmt.close();
		con.close();
	} catch (SQLException e) {
		// TODO 自動生成 catch 塊
		e.printStackTrace();
	}
	return departments;
}
//6. 返回所有的員工
public Vector getAllEmployees()
{
	//HashMap departments=new HashMap();
	Vector departments=new Vector();
	con = ConnectDB.getConnection();
	String strQuery = "select * from viewEmployee order by 部門編號";
	
	try {
		pstmt = con.prepareStatement(strQuery);
		rs=pstmt.executeQuery();
		while(rs.next())
		{
		this.employee=new EmployeeDTO();
		this.employee.setEID(rs.getInt(1)); 
		this.employee.setName(rs.getString(2));
		this.employee.setSex(rs.getString(3));
		this.employee.setBirthday(rs.getDate(4));
		this.employee.setDepartmentId(rs.getInt(5));
		this.employee.setDepartmentName(rs.getString(6));
		this.employee.setPhone(rs.getString(7));
		//departments.put(new Integer(this.employee.getEID()),this.employee);
		departments.add(this.employee);
		}
		rs.close();
		pstmt.close();
		con.close();
	} catch (SQLException e) {
		// TODO 自動生成 catch 塊
		e.printStackTrace();
	}
	return departments;
}
//8. 返回最大的員工號
public int getLastEmployeeId()
{
	int id=0;
	con = ConnectDB.getConnection();
	String strQuery = "select max(EID) from Employee";
	
	try {
		stmt = con.createStatement();
		rs=stmt.executeQuery(strQuery);
		if(rs.next())
		{
			id=rs.getInt(1);
		
		}
		else
		{
			id=0;
		}
		rs.close();
		stmt.close();
		con.close();
	} catch (SQLException e) {
		// TODO 自動生成 catch 塊
		e.printStackTrace();
	}
	return id;
}
}