//dbservlet.java for Course/Semester Planner
//Original Author: Scott Grabus 1999-2000 (s397446@gettysburg.edu)
//Copyright 2000 Gettysburg College All Rights Reserved


 import java.net.*; 
 import java.io.*; 
 import java.util.*; 
 import java.lang.*;
 import java.awt.*;
 import java.awt.image.*;
 import java.awt.event.*;
 import java.applet.*;
 import java.sql.*;
 import javax.servlet.*;
 import javax.servlet.http.*;
 

 
 public class dbservlet extends HttpServlet { 
 
  
 
  public void doGet(HttpServletRequest request, HttpServletResponse response){ 
  	
  	try{
  	String id;
  	String class_id;
	String new_semester;
	
	Class.forName("oracle.jdbc.driver.OracleDriver");
  	Connection con = DriverManager.getConnection("jdbc:oracle:thin:@triton.cs.gettysburg.edu:1521:csci","user15","omicron");

	Statement stmt = con.createStatement();
		
	id = request.getParameter("stu_id");
	class_id = request.getParameter("class_id");
	new_semester = request.getParameter("new_semester");
	
    	
	ServletOutputStream out = response.getOutputStream();
	//BufferedReader in = new BufferedReader(new InputStreamReader(applet.openStream()));
	
	out.println("Student ID is: "+id);
	out.println("Class ID is: "+class_id);
	out.println("New Semester is: "+new_semester);
	
	
	//get the priorities from the table
	String getmatchingp = new String("select p.priority_code from grabus_priority_codes p,grabus_ie i where i.priority = p.priority_code and i.class_id ="+class_id);
//	ResultSet rs = stmt.executeQuery(getmatchingp);
//	rs.next();
	String selected = "U"; //rs.getString(1);
	String getprioritycodes = new String("select priority_code from grabus_priority_codes");
ResultSet rs = stmt.executeQuery(getprioritycodes);
	while(rs.next()){
	   out.println(rs.getString(1));
	}
	out.println("end");
	out.println(selected);
	out.println("prioritydone");
	
	
	//get the status from the table
	String getmatchings = new String("select s.status_code from grabus_status_codes s,grabus_ie i where i.status = s.status_code and i.class_id ="+class_id);
	//rs = stmt.executeQuery(getmatchings);
	//rs.next();
	selected = "H"; //rs.getString(1);	
	String getstatuscodes = new String("select status_code from grabus_status_codes");
	rs = stmt.executeQuery(getstatuscodes);
	while(rs.next()){
	   out.println(rs.getString(1));
	}
	out.println("end");
	out.println(selected);
	out.println("statusdone");
	
	
	
	
	for(int i=0; i<14; i++){
               	    String sql = "select class_id,icon_source_url,icon_name,descr,priority,status "+ 
						"from grabus_ie where panel_id = "+Integer.toString(i)+
						"and student_id="+id;
               	    rs = stmt.executeQuery(sql);
               	    
		       out.println(Integer.toString(i)); //the panel_id
				while(rs.next()){
				   out.println(rs.getInt("class_id"));//Double.toString(Math.random()));//
				   out.println(rs.getString("icon_source_url"));  //icon_source);
				   out.println(rs.getString("icon_name"));  //icon_name);
				   out.println(rs.getString("descr"));  //description);
				   out.println(rs.getString("priority"));  //priority);
				   out.println(rs.getString("status"));  //status);
				   
				}
				
			 out.println("endsemester");
			 //rs = stmt.executeQuery(sql); //execute next query
	}
	
	
	for(int i=1; i<13; i++){
		String sql = "select sem_label from grabus_semlabels where panel_id ="+ Integer.toString(i);
               	rs = stmt.executeQuery(sql);
               	while(rs.next()){
		   out.println(rs.getString(1));
		}
	}
	//out.println(in.readLine());
	//out.println("scott");
	
	
	}
	catch(Exception e){System.out.println("bad");System.out.println(e);}
		
	
       

  } //doGet method
} //end of class