package dal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class Employee
{
	//添加員工信息
	public static boolean add(int d_id,String name,String sex,int age,String tel,String address)
	{
		boolean b=false;
		try
		{
			String sql="insert into Employee(D_id,E_name,E_sex,E_age,E_tel,E_address) values(?,?,?,?,?,?)";	
			PreparedStatement ps=SQLHelper.getConn().prepareStatement(sql);
			ps.setInt(1,d_id);
			ps.setString(2,name);
			ps.setString(3,sex);
			ps.setInt(4,age);
			ps.setString(5,tel);
			ps.setString(6,address);
			b=SQLHelper.executeUpdate(ps);
		}
		catch(SQLException e)
		{
			System.out.println (e);
		}
		return b;
	}
	
	//刪除員工信息
	public static boolean delete(int id)
	{
		boolean b=false;
		try
		{
			String sql="delete from Employee where E_id=?";
			PreparedStatement ps=SQLHelper.getConn().prepareStatement(sql);
			ps.setInt(1,id);
			b=SQLHelper.executeUpdate(ps);
		}
		catch(SQLException e)
		{
			System.out.println (e);
		}
		return b;
	}
	
	//更新員工信息
	public static boolean update(int id,int d_id,String name,String sex,int age,String tel,String address)
	{
		boolean b=false;
		try
		{
			String sql="update Employee set D_id=?,E_name=?,E_sex=?,E_age=?,E_tel=?,E_address=? where E_id=?";
			PreparedStatement ps=SQLHelper.getConn().prepareStatement(sql);
			ps.setInt(1,d_id);
			ps.setString(2,name);
			ps.setString(3,sex);
			ps.setInt(4,age);
			ps.setString(5,tel);
			ps.setString(6,address);
			ps.setInt(7,id);
			b=SQLHelper.executeUpdate(ps);
		}
		catch(SQLException e)
		{
			System.out.println (e);
		}
		return b;
	}
	
	//查詢員工信息
	public static ResultSet list(String strWhere)
	{
		ResultSet rs=null;
		try
		{
			String sql="select * from Employee as a inner join Department as b on a.D_id=b.D_id";
			if(strWhere!="")
			{
				sql+=strWhere;
			}
			PreparedStatement ps=SQLHelper.getConn().prepareStatement(sql);
			rs=SQLHelper.executeQuery(ps);
		}
		catch(SQLException e)
		{
			System.out.println (e);
		}
		return rs;
	}
}