import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.util.*;

public class FetchEmployeeServlet extends HttpServlet{
	
	private final static String driver="com.intersys.jdbc.CacheDriver";
	private final static String url="jdbc:Cache://127.0.0.1:1972/USER";
	private final static String user="_SYSTEM";
	private final static String password="sys";
	private final static String sql="select cod, fname, lname, image, departament, email from people where cod = ?";
	
	private Connection connection=null;
	private PreparedStatement statement=null;
	private ServletContext context;
	
	public void init(ServletConfig config)throws ServletException{
		super.init(config);
		context=config.getServletContext();
		try{
			Class.forName(driver);
			connection=DriverManager.getConnection(url,user,password);
			statement=connection.prepareStatement(sql);
		}catch(ClassNotFoundException e){
			System.err.println("Unable to load database driver");
			throw new ServletException("Unable to load database driver");
		}catch(SQLException e){
			System.err.println("Unable to connect to database");
			throw new ServletException("Unable to connect to database");
		}
		System.out.println("%%conectou");
	}
	
	public void service(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException{
		String jsp;
		String cmd=req.getParameter("cmd");
		String codString=req.getParameter("cod");
		int cod;
		try{
			cod=Integer.parseInt(codString);
		}catch(NumberFormatException e){
			cod=0;
		}
		System.out.println("%%cod:"+cod);
		if ("get".equals(cmd)){
			System.out.println("%%get True");
			EmployeeBean bean=fetchEmployee(cod);
			req.setAttribute("employee",bean);
			jsp="employee.jsp";
		}else{
			System.out.println("%%get False");
			Vector list=(Vector)fetchAll();
			req.setAttribute("list",list);
			jsp="/list.jsp";
		}
		
		RequestDispatcher dispatcher;
		dispatcher=context.getRequestDispatcher(jsp);
		dispatcher.forward(req,res);
	}
	
	public EmployeeBean makeBean(ResultSet results)throws SQLException{
		EmployeeBean bean= new EmployeeBean();
		bean.setCod(results.getInt("cod"));
		bean.setFirstName(results.getString("fname"));
		bean.setLastName(results.getString("lname"));
		bean.setEmail(results.getString("email"));
		bean.setDepartament(results.getString("departament"));
		bean.setImage(results.getString("image"));
//		EmployeeBean bean= new EmployeeBean(1);
//		bean.setFirstName("Primeiro Nome");
//		bean.setLastName("Ultimo Nome");
//		bean.setEmail("fulano@brasil.com.br");
//		bean.setDepartament("DGH");
//		bean.setImage("image");

		System.out.println("\ncod:"+bean.getCod());
		System.out.println("\nfname:"+bean.getFirstName());
		System.out.println("\nlname:"+bean.getLastName());
		System.out.println("\nemail:"+bean.getEmail());
		System.out.println("\ndepartament:"+bean.getDepartament());
		System.out.println("\nimage:"+bean.getImage());
		
		return bean;
	}
	
	public EmployeeBean fetchEmployee(int cod){
		try{
			ResultSet results;
			synchronized (statement){
				statement.clearParameters();
				statement.setInt(1,cod);
				results=statement.executeQuery();
			}
			EmployeeBean bean=null;
			if (results.next()){
				bean=makeBean(results);
			}
			if (results!=null)
				results.close();
			return bean;
		}catch(SQLException se){
			return null;
		}
	}
	
	public Vector fetchAll(){
		try{
			Vector todos=new Vector();
			ResultSet results;
			Statement st=connection.createStatement();
			results=st.executeQuery("select cod, fname, lname, image, departament, email from people");
			while (results.next())
				System.out.println("antes makeBean");
				todos.add(makeBean(results));
				System.out.println("depois makeBean");
			return todos;
		}catch(SQLException se){
			return null;
		}
	}
	
	public void destroy(){
		try{
			if (connection!=null)
				connection.close();
		}catch(SQLException e){
		}
	}
}