// Copyright MageLang Institute; Version $Id: //depot/main/src/edu/modules/Servlets/magercises/FormPostingAndProcessing/solution/CourseDB.java#3 $
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.Enumeration;
import java.util.Hashtable;

public class CourseDB extends HttpServlet 
{ 
	static String PostURL = "http://chumley.magelang.com:8080/servlet/CourseDB";
	static String HTTPURL = "http://chumley.magelang.com:8080/";
	static String UpdateTemplateFileName = "d:/website/tools/servlets/UpdateTemplate.html";
	static String AddTemplateFileName    = "d:/website/tools/servlets/AddTemplate.html";
  
	String      URL                  = "jdbc:odbc:CourseData";
  String      username             = "";
  String      password             = "";
  Connection  con                  = null;
  Statement   stmt                 = null;
	String      UpdateTemplateString = null;
	String      AddTemplateString    = null;
	
  public void init( ServletConfig conf ) throws ServletException
	{
		super.init(conf);
		try 
		{
		  connect(URL, username, password);
		  UpdateTemplateString = getTemplateFile(UpdateTemplateFileName);
//		  AddTemplateString    = getTemplateFile(AddTemplateFileName);
		}
		catch (Exception e) 
		{
		  throw( new UnavailableException( this, "Failed connecting to Course DB" ) );
		}
  }
		
  protected void connect( String URL, String username, String password )
	   throws SQLException, ServletException
  {
		loadDriver();
		con  = DriverManager.getConnection (URL, username,	password);
		stmt = con.createStatement();
	  }
	
	  private void loadDriver() throws ServletException 
		{ 
			try 
			{
		  	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			} 
			catch (Exception e) 
			{
		  	throw new ServletException("Failed to load JDBC/ODBC driver.");
			}
  }

  // Form processing ends up here via a POST from a form.
  public void doPost(HttpServletRequest req, HttpServletResponse res)
       throws ServletException, IOException
  {
    ServletOutputStream out = res.getOutputStream();

    // set content type and other response header fields first
    res.setContentType("text/html");
    
    String cmd = req.getParameterValues("CMD")[0];

		try 
		{
			if ( cmd.equals("ADD") ) 
			{
				openHTML(out, "Added Course");
				addRecord(req, out);
			}
			else 
			{
				String[] ids = req.getParameterValues("courseID");
				if ( ids==null ) 
				{
	    		out.println("<i><b>You did not select a course.</b></i><br>");					
				}
				else if ( cmd.equals("UPDATE") ) 
				{
					openHTML(out, cmd+" RECORD");
	  			String id = ids[0];
	  			showRecordUsingTemplate(out, id, UpdateTemplateString);
				}
				else if ( cmd.equals("COMMIT CHANGES") ) 
				{
					openHTML(out, cmd);
	  			String id = ids[0];
	  			updateRecord(req, out, id);
				}
				else if ( cmd.equals("SHOW") ) 
				{
					try 
					{
						openHTML(out, "");
						showRecord(out, ids[0]);
	    		}
	    		catch (SQLException sql) 
					{
	    			out.println("<i><b>This course has been deleted</b></i><br>");
	    			out.println("<i><b>Do a refresh/reload on main database entry point to refresh list of courses</b></i>");
	    		}				
				}
				else if ( cmd.equals("DELETE") ) 
				{
					openHTML(out, "Deleted Course");
	    		try 
					{
						showRecord(out, ids[0]);
	    			stmt = con.createStatement();
						stmt.executeUpdate("DELETE FROM Courses WHERE ID="+ids[0]+";");
	    		}
	    		catch (SQLException sql) 
					{
	    			out.println("<i><b>This course has already been deleted</b></i><br>");
	    			out.println("<i><b>Do a refresh/reload on main database entry point to refresh list of courses</b></i>");
	    		}
				}
			}
		}
		catch (SQLException e) 
		{
		  out.println("SQL error: '"+e.getMessage()+"' during command "+cmd);
		}
		catch (Exception a) 
		{
		  out.println("general error: '"+a.getMessage()+"' during command "+cmd);
		}
		closeHTML(out);
  }

  // Get produces a list of all courses: the start up page
  public void doGet(HttpServletRequest req, HttpServletResponse res)
       throws ServletException, IOException
  {
    ServletOutputStream out = res.getOutputStream();

    // set content type and other response header fields first
    res.setContentType("text/html");

    // then write the data of the response
    out.println("<HEAD><TITLE>Course Database</TITLE></HEAD><BODY>");
    out.println("<h1>Welcome to MageLang's Course Database</h1>");

		try 
		{
		  // get complete list of records
		  stmt = con.createStatement();
		  ResultSet result = stmt.executeQuery("SELECT ID, Company, Course, CourseStartDate FROM Courses;");
		  out.println("<form action=\""+PostURL+"\" method=POST>");
		  out.println("<p>Select a course:<br>");
		  out.println("<select NAME=\"courseID\" SIZE=10>");
		  while(result.next()) 
			{
				String ID = result.getString("ID");
				String company = result.getString("Company");
				String course = result.getString("Course");
				String date = result.getString("CourseStartDate");
				out.println("<option VALUE="+ID+">["+company+", "+course+", "+date+"]</option>");
		  }
		  out.println("</select>");
		  out.println("</p><BR>");
			out.println("<input type=submit name=CMD value=UPDATE>&nbsp;&nbsp;");
			out.println("<input type=submit name=CMD value=SHOW>&nbsp;&nbsp;");
			out.println("<input type=submit name=CMD value=DELETE "+ "onClick=\"return confirm('Are you sure you want to delete this class?')\">&nbsp;&nbsp;");
		  out.println("</form>");
		  out.println("<p>");
		  out.println("You may also <a href=\""+HTTPURL+"CourseDB/AddCourse.html\">ADD A RECORD</a>.");
		}
		catch (SQLException e) 
		{
		  throw new UnavailableException(this, "cannot gen list of courses");
		}
    out.println("</BODY></HTML>");
    out.close();
  }

	public void showQueryRecord(ServletOutputStream out, String query)
		throws ServletException, SQLException, IOException
	{
    stmt = con.createStatement();
    ResultSet result = stmt.executeQuery(query);
    String[] fields = getFieldNames(result);
    result.next();

    showRecord(out, result, fields);
	}

	public void showRecord(ServletOutputStream out, String id)
		throws ServletException, SQLException, IOException
	{		
    showQueryRecord(out, "SELECT * FROM Courses WHERE ID = "+id+";");
  }
        
	public void showRecord(ServletOutputStream out, ResultSet result, String[] fields)
		throws ServletException, SQLException, IOException
	{
   	// dump a table of all the contents
		out.println("<table border=0>");
		for (int f=0; f<fields.length; f++) 
		{
			String fs = result.getString(fields[f]);
			if ( fields[f].equals("ID") || fields[f].equals("userlock") ) 
			{
				continue;
			}
			out.println("<tr>");
			out.println("	<td valign=top width=175><b>"+fields[f]+"</b> </td>");
			out.println("	<td width=400>"+toHTMLStringWithLineBreaks(fs));
			out.println("	</td>");
			out.println("</tr>");
		}
		out.println("</table>");
	}
	
	public void addRecord(HttpServletRequest req, ServletOutputStream out)
			throws SQLException, IOException, ServletException
	{
		StringBuffer ins = new StringBuffer("INSERT into Courses ");

		// Create list of fields to update
		Hashtable pairs = getValueDictionary(req);
		ins.append( getSQLFieldTupleAssignment(pairs) );
 		ins.append(";");
		
	 	stmt = con.createStatement();
	 	stmt.executeUpdate(ins.toString());
    showQueryRecord(out,
    	"SELECT * FROM Courses WHERE Company='"+req.getParameterValues("Company")[0]+
    			"' AND Course='"+req.getParameterValues("Course")[0]+
    			"' AND CourseStartDate='"+req.getParameterValues("CourseStartDate")[0]+
    			"';");
	}
	
	public void showRecordUsingTemplate(ServletOutputStream out, String id, String template)
		throws SQLException, IOException
	{
	 	stmt = con.createStatement();
    ResultSet result = stmt.executeQuery("SELECT * FROM Courses WHERE ID = "+id+";");
    String[] names = getFieldNames(result);
    result.next();
    String[] values = getFieldValues(result, names);
		String u = getFilledInTemplate(template, names, values);
		out.println(u);
	}

	public void updateRecord(HttpServletRequest req, ServletOutputStream out, String id)
		throws SQLException, IOException
	{
		StringBuffer ins = new StringBuffer("UPDATE Courses SET ");
			
		// Create list of fields to update
		Hashtable pairs = getValueDictionary(req);
		ins.append( getSQLFieldAssignments(pairs) );
 		ins.append(" WHERE ID="+id+";");
 		out.println("SQL Update: "+ins.toString());
		
	 	stmt = con.createStatement();
	 	stmt.executeUpdate(ins.toString());
	 	out.println("UPDATED AS FOLLOWS:");
    //showQueryRecord(out, "SELECT * FROM Courses WHERE ID="+id+";");
	}

	public void openHTML(ServletOutputStream out, String title) throws IOException 
	{
    out.println("<HTML><HEAD><TITLE>"+title+"</TITLE></HEAD><BODY>");
    out.println("<h2>"+title+"</h2>");
	}
	
	public void closeHTML(ServletOutputStream out) throws IOException 
	{
	  out.println("</BODY></HTML>");
    out.close();
	}
	
  public String getServletInfo() 
	{
    return "MageLang Course Database Servlet";
  }
	
	public String getSQLFieldTupleAssignment(Hashtable pairs) 
	{
		StringBuffer ins = new StringBuffer("(");
			
		// Create list of fields to update
		boolean firstOne = true;
		Enumeration values = pairs.keys();
  
		defineFields:
  	while(values.hasMoreElements()) 
		{
      String name = (String)values.nextElement();
  		if ( !firstOne ) 
			{
  			ins.append(',');
  		}
  		firstOne = false;
      ins.append(name);
    }
 		ins.append(") VALUES (");
		
		// Get list of values to insert
		firstOne = true;
		values = pairs.keys();
 
		setFields:
  	while(values.hasMoreElements()) 
		{
      String name = (String)values.nextElement();
  		if ( !firstOne ) 
			{
  			ins.append(',');
  		}
  		firstOne = false;
      String value = (String)pairs.get(name);
      ins.append('\'');
      if ( value==null || value.length()==0 ) 
			{
				ins.append(' ');
      }
      else 
			{
      	ins.append(value);
      }
      ins.append('\'');
    }
 		ins.append(")");
 		return ins.toString();
	}
	
	public String getSQLFieldAssignments(Hashtable pairs) 
	{
		StringBuffer ins = new StringBuffer(500);
			
		// Create list of fields to update
		boolean firstOne = true;
		Enumeration values = pairs.keys();
  
		setFields:
  	while(values.hasMoreElements()) 
		{
      String name = (String)values.nextElement();
      if ( name.equals("courseID") ) continue setFields;
      String value = (String)pairs.get(name);
  		if ( !firstOne ) 
			{
  			ins.append(',');
  		}
  		firstOne = false;
      ins.append(name);
      ins.append("=");
      ins.append('\'');
      if ( value==null || value.length()==0 ) 
			{
				ins.append(' ');
      }
      else 
			{
      	ins.append(value);
      }
      ins.append('\'');
    }
 		return ins.toString();
	}
	
	public Hashtable getValueDictionary(HttpServletRequest req) 
	{
		Hashtable pairs = new Hashtable(51);
		
		Enumeration values = req.getParameterNames();
  
		defineFields:
  	while(values.hasMoreElements()) 
		{
      String name = (String)values.nextElement();
      if ( name.equals("CMD") ) break defineFields;
      String value = req.getParameterValues(name)[0];
      pairs.put(name, value);
    }
    return pairs;
	}
				
	public String[] getFieldNames(ResultSet result) throws SQLException 
	{
		ResultSetMetaData meta = result.getMetaData();
		int columns = meta.getColumnCount();
		String[] fields = new String[columns];
    for (int i=1;i<=columns;i++) 
		{
			fields[i-1] = meta.getColumnLabel(i);
    }
		return fields;
	}

	public String[] getFieldValues(ResultSet result, String[] names) throws SQLException 
	{
		String[] values = new String[names.length];
    for (int i=0;i<names.length;i++) 
		{
			values[i] = result.getString(names[i]);
    }
		return values;
	}

	/** Print out the string substituting <br> for newline. */
  public String toHTMLStringWithLineBreaks(String s) throws ServletException 
	{
    if ( s==null ) 
		{
    	return "<no info>";
    }
    StringBuffer buf = new StringBuffer(100);
  	int i=0;
    while ( i<s.length() ) 
		{
			if ( s.charAt(i)=='\r' ) 
			{
			  i++;
			}
			if ( s.charAt(i)=='\n' ) 
			{
			  buf.append("<br>");
			}
			else 
			{
			  buf.append(s.charAt(i));
			}
			i++;
    }
		return buf.toString();
  }

  public String getTemplateFile(String filename)
		throws IOException, ServletException
	{
    FileReader f = new FileReader (filename);
    BufferedReader bf = new BufferedReader(f);
    StringBuffer buf = new StringBuffer(2000);
    String line;
    while ((line = bf.readLine()) != null) 
		{
      buf.append( toHTMLStringWithLineBreaks(line) + System.getProperty("line.separator") );
    }
    bf.close();
    f.close();
    return buf.toString();
  }
  
  /*
  public void defineTemplateFile(String filename)
		throws IOException, ServletException
	{
    FileReader f = new FileReader (filename);
    BufferedReader bf = new BufferedReader(f);
    StringBuffer buf = new StringBuffer(2000);
    String line;
    while ((line = bf.readLine()) != null) 
		{
      buf.append( toHTMLStringWithLineBreaks(line) + System.getProperty("line.separator") );
    }
    bf.close();
    f.close();
    UpdateTemplateString = buf.toString();
  }
  */
  
	public String getFilledInTemplate(String template,
		String[] names,
		String[] values) throws SQLException
	{
		String form = template;
		for (int f=0; f<names.length; f++) 
		{
			form = stringReplace(form, "#"+names[f]+"#", values[f]);
		}
		return form;
	}
	
	public static String stringReplace(String template, String a, String b)
	{
		int j;
		String s=template;
		j = template.indexOf(a, 0);
		while ( j!=-1 ) 
		{
			s = s.substring(0,j) + b + s.substring(j+a.length());
			j = s.indexOf(a, j);
		}
		if ( s==null ) 
		{
			return new String(template);
		}
		else 
		{
			return s;
		}
	}
        
	public void destroy() 
	{
		try 
		{
		  con.close();
		}
		catch (Exception e) 
		{
		  log("can't drop DB");
		}
  }

}