package com.jspdev.ch17;
import com.jspdev.util.*;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.util.*;
public class EmployeeBean
{
	private   Connection conn ;
	public EmployeeBean()throws Exception
	{
		this.conn=DatabaseConn.getConnection();
	}
	public void addEmployee(Employee employee)throws Exception
	{
	     this.executeHelper("insert into employee values('"+employee.getId()+"','"+employee.getName()+"',"+employee.getSalary()+",'"+employee.getDepartment()+"',"+employee.getAge()+")");
	}
	public Collection getEmployees()throws Exception
	{
		return this.getEmployeeHelper("select * from Employee");
	 }
	  
	 public Collection getNoDeptEmployees()throws Exception
	{
		return this.getEmployeeHelper("select * from Employee where  department='temp'");
	 }
	 
	 public Collection getDepartmentDemployees(String deptId)throws Exception
	 {
	 	return this.getEmployeeHelper("select * from employee where department='"+deptId+"'");
	 }
	 public void deleteEmployeeFromDept(String employId,String deptId)throws Exception
	 {
	 	try
	 	{
	 		this.executeHelper("update employee set department='temp' where id='"+employId+"'");
	 	}
	 	catch(Exception e)
	 	{
	 		e.printStackTrace();
	 		throw e;
	 	}
	 }
	 public void resetEmploy(String employId,String deptId)throws Exception
	 {
	 	try
	 	{
			this.executeHelper("update employee set department='"+deptId+"' where id='"+employId+"'");
	 	}
	 	catch(Exception e)
	 	{
	 		e.printStackTrace();
	 		throw e;
	 	}
	 	
	 }
	 
	  private Collection getEmployeeHelper(String sql)throws Exception
	  {
	  	 Statement stmt=conn.createStatement();
	     ResultSet rst=stmt.executeQuery(sql);
	     Collection ret=new ArrayList();
	     while(rst.next())
	     {
	     	Employee temp=new Employee();
	     	temp.setId(rst.getString("id"));
	     	temp.setName(rst.getString("name"));
	     	temp.setAge(rst.getInt("age"));
	     	temp.setSalary(rst.getFloat("salary"));
	     	temp.setDepartment(rst.getString("department"));
	        ret.add(temp);
	     }
	     return ret;
	  }
	  
	  private void executeHelper(String sql)throws Exception
	  {
	  	 Statement stmt=conn.createStatement();
		 stmt.executeUpdate(sql);
	  }
}
