/*
 * Code by George Kichukov for SAS 11/24/2002
 * This class is used to execute user modifications on the database
 * depending on the parameters passed to it
 */

package admin;

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

import utilities.*;

public class Query extends HttpServlet
{
	private String errorTarget = "../admin/index.jsp";

        public void init(ServletConfig config) throws ServletException
        {
                super.init(config);
        }

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

                // If it is a get request forward to doPost()
                doPost(request, response);
        }


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

               //name of the user
		String name="";
	
                PrintWriter out = response.getWriter();
                response.setContentType("text/html");

                //first thing that every servlet or jsp should do is check if user is logged in
                HttpSession session = request.getSession(false);
		
		//if not logged in redirect to login
 		if(session == null)
 		{
         		HTMLUtils.printRedirectPage(out, "You must login to the system prior to viewing this page.", errorTarget);
         		return;
 		}
 		
 		//else if logged in take the name from the session
 		else
 		{
 			name=(String)session.getAttribute("user");
 			
 			//if the name is null
 			if(name == null)
 			{
         			HTMLUtils.printRedirectPage(out, "You must login to the system prior to viewing this page.", errorTarget);
         			return;
 			}
 		
 		}

               HTMLUtils.printAdminHeader(out);
		
                //display welcome message to the user
                out.println("<p align=left>Welcome " + name + "</p>");  	

           
		//get the parameters
                String table=request.getParameter("table");
                String oper=request.getParameter("oper");
                String id=request.getParameter("id");
                String mID=request.getParameter("mID");
		
		//display operation summary
		out.println ("Operation: " + oper + " " + table + "<br>");
		
		//Retrieve all the HTML fields from the input stream
	        Enumeration keys;
	        String column;
	        String value;
	        String key1;
		keys = request.getParameterNames();
                String primaryKey = "";
               
                //case 1: MAJOR_REQ or MINOR_REQ tables
                if((table.equals("MAJOR_REQ")) || (table.equals("MINOR_REQ")))
                {
                	primaryKey="ID";
                	
                	//CASE 1 a) Coming from Req to delete a Requirement
                	//paramaters received: table and id
                	if(oper.equals("Delete"))
			{         
                                String sql="delete from " + table + " where " + primaryKey + "=" + id;
                                //out.println(sql);

	                      	Retriever.executeSQL(sql);
				out.println("Requirment Deleted Successfully!");	

		               		               	
			} //end if Delete
			
			//CASE 2 b) Coming from ModifyReq to Modify Requirement
                	//paramaters received: table and id and the requirement string + requirement credits
                	if(oper.equals("Modify"))
			{         
                                String sql="select * from " + table + " where " + primaryKey + "=" + id;
                                //out.println(sql);
                                
				try
		               	{

		                      	ResultSet rs = Retriever.executeSQL(sql);
					rs.next();
					
					try
					{
						String dataIn= RequirementParser.toCourseDatabaseString(request.getParameter("CourseList"));
						rs.updateString("CourseList", dataIn );
						rs.updateString("Credits", request.getParameter("Credits"));
						rs.updateRow();
						
						out.println("Requirement Modified Successfully!");
					}
					catch (RequirementParserException e)
					{
						out.println(e.getMessage());
					}

		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Modify
			
			//CASE 2 c) Coming from ModifyReq to Add Requirement
                	//paramaters received: table and id and the requirement string + requirement credits
                	// + the mID
                	if(oper.equals("Add"))
			{         
                                String sql="select * from " + table;
                                //out.println(sql);
                                
                                //major or minor ID
                                String fieldMID="M" + (table.substring(1,table.length()-4)).toLowerCase() + "ID";
				try
		               	{

		                      	ResultSet rs = Retriever.executeSQL(sql);
					rs.moveToInsertRow(); // moves cursor to the insert row
					
					rs.updateString("Credits", request.getParameter("Credits"));
					
					try
					{
						String dataIn= RequirementParser.toCourseDatabaseString(request.getParameter("CourseList"));
						rs.updateString("CourseList", dataIn );
					
						rs.updateString(fieldMID,mID);
						
						rs.insertRow();
						
						out.println("Requirement Added Successfully!");

					}
					catch (RequirementParserException e)
					{
						out.println(e.getMessage());
					}

					
					
					
		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Add
			
                	
                }
                
                
                //CASE 2: any other *_REQ 
                else if((table.substring(table.length()-3, table.length())).equals("REQ"))
                {
                	primaryKey="ID";
                	
                	//CASE 2 a) Coming from Req to delete a Requirement
                	//paramaters received: table and id
                	if(oper.equals("Delete"))
			{         
                                String sql="delete from " + table + " where " + primaryKey + "=" + id;
                                //out.println(sql);
                                
	                      	Retriever.executeSQL(sql);
				out.println("Requirment Deleted Successfully!");	

			} //end if Delete
			
			//CASE 2 b) Coming from ModifyReq to Modify Requirement
                	//paramaters received: table and id and the requirement string + requirement credits
                	if(oper.equals("Modify"))
			{         
                                String sql="select * from " + table + " where " + primaryKey + "=" + id;
                                //out.println(sql);
                                
				try
		               	{

		                      	ResultSet rs = Retriever.executeSQL(sql);
					rs.next();
					
					try
					{
						String dataIn= RequirementParser.toCourseDatabaseString(request.getParameter("CourseList"));
						rs.updateString("CourseList", dataIn );
						rs.updateString("CreditsRequired", request.getParameter("Credits"));
						rs.updateRow();
						
						out.println("Requirement Modified Successfully!");
					}
					catch (RequirementParserException e)
					{
						out.println(e.getMessage());
					}
					
					
		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Modify
			
			//CASE 2 c) Coming from ModifyReq to Add Requirement
                	//paramaters received: table and id and the requirement string + requirement credits
                	if(oper.equals("Add"))
			{         
                                String sql="select * from " + table;
                                //out.println(sql);
                                
				try
		               	{

		                      	ResultSet rs = Retriever.executeSQL(sql);
					rs.moveToInsertRow(); // moves cursor to the insert row
					
					
					try
					{
						String dataIn= RequirementParser.toCourseDatabaseString(request.getParameter("CourseList"));
						rs.updateString("CourseList", dataIn );
						rs.updateString("CreditsRequired", request.getParameter("Credits"));
						rs.insertRow();
						
						out.println("Requirement Added Successfully!");
					}
					catch (RequirementParserException e)
					{
						out.println(e.getMessage());
					}
					

		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Add
			
                	
                } //end else if *_REQ
                	
                
                //CASE 3: any other table
                else
                {
			//from SCHOOLS to SchoolID
                        primaryKey=table.substring(0,1) + (table.substring(1,table.length()-1)).toLowerCase() + "ID"; 
                    
			
			//CASE 3 a) 
			//paramters received: table and id to be deleted 		      
			if(oper.equals("Delete"))
			{
				
		                String sql="delete from " + table + " where " + primaryKey + "=" + id;
		                //out.println(sql);
		                      	
		                Retriever.executeSQL(sql);
					
				out.println("Record Deleted Successfully!");
		               	
			} //end if Delete
			
			//CASE 3 b)
			//paramters received: table and id to be modified 
			//all fields in the table		      
			if(oper.equals("Modify"))
			{
				
				//this is used to make sure that the primary key is not modified
				id=request.getParameter("pk" + primaryKey);
				
				String sql="select * from " + table + " where " + primaryKey + "=" + id;
		                //out.println(sql);
		                
				try
		               	{	    	
		                      	ResultSet rs = Retriever.executeSQL(sql); 
					

					//get enumeration of all paramters passed
					keys = request.getParameterNames(); 
					
					//loop through all parameters
					while (keys.hasMoreElements()) 
					{ 
					        key1 = (String) keys.nextElement(); 
					        value = request.getParameter(key1); 
					       
					        //make sure it is not the oper or table or the primary key
					        if( (key1.equals("oper")) || (key1.equals("table")) || ((key1.substring(0,2)).equals("pk")) ) 
					        {
					        	//do nothing
					        }
					        
					        else
					        {
					      		//out.println(sql + " " + key1 + " v:" + value);
					   		rs.updateString(key1,value); 
					        }
					       
					} 
					
					rs.updateRow();
					rs.close();
						
					out.println("Record Modified Successfully!");

		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Modify
			
			
			//CASE 3 c)
			//paramters received: table to which record is to be addes 
			//all fields in the table		      
			if(oper.equals("Add"))
			{
				
				
				String sql="select * from " + table;
		                //out.println(sql);
		                
				try
		               	{	    	
		                      	ResultSet rs = Retriever.executeSQL(sql); 
					rs.moveToInsertRow(); // moves cursor to the insert row

					//get enumeration of all paramters passed
					keys = request.getParameterNames(); 
					
					//loop through all parameters
					while (keys.hasMoreElements()) 
					{ 
					        key1 = (String) keys.nextElement(); 
					        value = request.getParameter(key1); 
					       
					        //make sure it is not the oper or table 
					        if( (key1.equals("oper")) || (key1.equals("table")) ) 
					        {
					        	//do nothing
					        }
					        
					        else
					        {
					      		//out.println(sql + " " + key1 + " v:" + value);
					      		rs.updateObject(key1,value); 		
						}     
					             
					} 
					rs.insertRow();
					rs.close();
						
					out.println("Record Added Successfully!");

		                } //end try
		
		                catch(SQLException e) {out.println("SQLException " + e.getMessage());}
		               	
			} //end if Modify
		             
		} //end else any other table
		
		HTMLUtils.printAdminFooter(out);
		
        } //end method doPost
 
} //end Query servlet