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

public class Display8900 extends HttpServlet {
    
  public static Connection conn;
  public static Statement stmt;
    
  public void doGet (HttpServletRequest request,
                     HttpServletResponse response)
      throws ServletException, IOException {

    doPost(request,response);
  }

  public void doPost (HttpServletRequest request,
                      HttpServletResponse response)
      throws ServletException, IOException {

	response.setContentType("text/html");
	PrintWriter out = response.getWriter();

	try {
	  Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch(ClassNotFoundException e){
	    out.println("Error loading the Driver:"+e.getMessage()); 
        return;
	  }
	
	Connection conn = null;
	
	try {
	  conn = DriverManager.getConnection (
         Utilities8900.CONNECTSTRING,Utilities8900.ID,Utilities8900.PASSWORD); 
	} catch (SQLException e1) {
	    out.println("Error connecting to Oracle:"+e1.getMessage()); 
        return;
	  }
    if (conn == null) {
      out.println("Null Connection");
      return;
    }
	
	Statement stmt = null;
	try {
	  stmt = conn.createStatement ();
	} catch (SQLException e) {
	    out.println("createStatement " + e.getMessage());
        try {conn.close();} catch (SQLException e2) {};
        return;
	  }
	
	ResultSet rset = null;
	
    out.println(Utilities8900.PageHeader(Utilities8900.HEADER));
    String when[] = new String[50];
    String who[] = new String[50];
    int count = 0;

	String query = "select name from faculty where name not in " +
        "  (select who from schedule where who is not null) order by name";
	try {
	    rset = stmt.executeQuery(query);
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
      }

    String remainingFaculty[] = new String[50];
    count = 0;
	try {
	  if (rset.next()) {
        do {
          remainingFaculty[count++] = rset.getString(1); 
	    } while (rset.next()); 
      }
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
	  }

	query = "select to_char(when,'DD-MON-YYYY'),who " +
            "from schedule order by when";
	try {
	    rset = stmt.executeQuery(query);
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
      }

    int sCount = 0;
	try {
	  if (rset.next()) {
        do {
          when[sCount] = rset.getString(1); 
          who[sCount++] = rset.getString(2); 
	    } while (rset.next()); 
      }
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
	  }

    // Print the Page
    out.println("<center>");
    out.println("<nobr><h1>" + Utilities8900.HEADER + "</h1></nobr>");
    out.println("<h4>" + Utilities8900.WHENWHERE + "</h4>");
    out.println("<BR><BR>");       
    out.println("<form action=\"" + Utilities8900.URLPREFIX +
                "Process8900\" method=\"POST\">");
    out.println("<table border=1 bgcolor=\"#FFFFCC\" " +
                "cellpadding=2 cellspacing=4>");
    out.println("<tr>");
    out.println("<th>");
    out.println("Date");
    out.println("</th>");              
    out.println("<th>");
    out.println("Faculty");
    out.println("</th>");              
    out.println("</tr>");

    for (int i=0; i<sCount; i++) {
      out.println("<tr>");
      out.println("<td>");
      out.println(when[i]);
      out.println("</td>");
      out.println("<td>");
      if (who[i] == null) {
          String dd = when[i];
          int ii=dd.indexOf("-");
          int jj=dd.lastIndexOf("-");
          when[i] = dd.substring(0,ii)+
                    dd.substring(ii+1,jj)+
                    dd.substring(jj+1);       
        out.println("<select name=\""+when[i]+"\" size=1>");
        out.println("<option>Available");
        for (int j = 0; j < count; j++) {
          out.println("<option>" + remainingFaculty[j]);
        }
        out.println("</select>");
      }
      else
        out.println(who[i]);
      out.println("</td>");
      out.println("</tr>");
    }
    out.println("</table>");
    out.println("<BR><BR>");
    out.println("<INPUT TYPE=submit value=\"Add My Name to the Schedule\">");
    out.println("</form>");
    out.println("<center>");
    out.println("</body>");
    out.println("</html>");
	
	try {
      stmt.close();
      conn.close();	
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
	  }
	
	out.close();
  }
    
    
  public String getServletInfo() {
    return "This Servlet processes 8900 Form";
  }
    
    
}