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

public class Process8900 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;
	
	String query = "select to_char(when,'DD-MON-YYYY') from schedule "+
                   "where who is null";
	try {
	    rset = stmt.executeQuery(query);
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
      }


    out.println(Utilities8900.PageHeader(Utilities8900.HEADER));
    String when[] = new String[50];
    int count = 0;
	try {
	  if (rset.next()) {
        do {
          String dd = rset.getString(1); 
          int ii=dd.indexOf("-");
          int jj=dd.lastIndexOf("-");
          when[count++] = dd.substring(0,ii)+
                          dd.substring(ii+1,jj)+
                          dd.substring(jj+1);
	    } while (rset.next()); 
      }
	} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
        return;
	  }

    String who[] = new String[50];
    int numNames=0;
    int position=0;
    for (int i=0; i<count; i++) {
      String fname[] = request.getParameterValues(when[i]);	
      who[i] = fname[0];
      if (!(who[i].equals("Available"))) {
         numNames++;
         position = i;
      }
    }

    if (numNames == 1) {
      query = "update schedule set who='"+who[position]+
              "' where when='" + when[position]+"' and who is null";
	  try {
	    int nRows = stmt.executeUpdate(query);
        if (nRows == 0) {
          out.println("<h3>Error (Someone stole your date)!<h3>");
          out.println("<BR><BR>");
    out.println("<form action=\"" + Utilities8900.URLPREFIX +
                "Display8900\" method=\"POST\">");
    out.println("<INPUT TYPE=submit value=\"Back to Start Page\">");
          out.println("</form>");
          out.println("</body>");
          out.println("</html>");
	  try { stmt.close(); conn.close();} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
            return;
	  }
	  out.close();
          return; 
        }
        else {
          out.println("<h3>Your name has been added to the schedule."+
                      " Thanks!</h3>");
          out.println("<BR><BR>");
        }
	  } catch (SQLException e) {
	      out.println("executeQuery " + e.getMessage());
              return;
        }
    }
    else {
      out.println("<h3>Error(too few or too many names)!");
      out.println("<BR><BR>");
    out.println("<form action=\"" + Utilities8900.URLPREFIX +
                "Display8900\" method=\"POST\">");
    out.println("<INPUT TYPE=submit value=\"Back to Start Page\">");
          out.println("</form>");
          out.println("</body>");
          out.println("</html>");
	  try { stmt.close(); conn.close();} catch (SQLException e) {
	    out.println("executeQuery " + e.getMessage());
            return;
	  }
	  out.close();
          return; 
    }

	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("Available"); 
        //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";
  }
    
    
}