/*
 * Code by George Kichukov for SAS 11/03/2002
 * Updated 11/24/02
 	- add check login
 	- add diferent cases
 * This class is used to Input data for any of the tables that don't end with _REQ
 * It gets all paramters (table, operation, specific record ID) and displays
 * appropriate input fields
 * If operation is ADD the input fields are empty but there is a hidden field 
   with the table name
 * if the operation is Modify input fields are prepopulated
 * if the operations is View it is dipslayed in the same way as Modify but there is no Submit Button
 * if operation is delete - forward to Query and pass the parameters
 *
 * Updated 12/08/02 - added formatting
 */

package admin;

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

import utilities.*;

public class Input 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");

                
            
                //if operation is delete
                //pass all parameters over to Query
                if(oper.equals("Delete"))
		{                                
	                String target = "/servlet/admin.Query?table=" + table + "&oper=" + oper + "&id=" + id;
	
	        	try
	                {
	                	// Forward the request to the target named
	                        ServletContext context = getServletContext();
	                        RequestDispatcher dispatcher = context.getRequestDispatcher(target);
	                        dispatcher.forward(request, response);
	                }
	
	                catch(ServletException e){System.err.println("ServeltException" + e.getMessage());}
	                catch(IOException e){System.err.println("IOException" + e.getMessage());}
	         }
	         
	         //if the operation is Modify/View prepopulate the input fields with 
	         //the data for the selected ID from the databse
	         else if ( (oper.equals("Modify")) || (oper.equals("View")))
	         {

	                try
	                {
	                        ResultSet rs=null;
	                        
	                        String fieldID ="";
	                        
	                        //get the primary key name from the table name e.g. CourseID from COURSES
	                        fieldID = table.substring(0,1) + (table.substring(1,table.length()-1)).toLowerCase() + "ID";

				//select * columns from the specific record	                        	                        
	                        String sql = "select * from " + table + " where " + fieldID + "=" + id;
	                        rs = Retriever.executeSQL(sql);
	                        rs.next();
	
	                       //get all column names and information from the table
	                        ResultSetMetaData rsmd = rs.getMetaData();
	                        int numberOfColumns = rsmd.getColumnCount();
	
	                        //send all paramters to query
	                        out.println("<form method=post action=admin.Query>");
	                        out.println("<input type=hidden name=table value=" + table + ">");
	                        out.println("<input type=hidden name=oper value=" + oper + ">");
	
				
				//set up the table
	                         out.println("<TABLE cellpadding=0 cellspacing=0 border=0 width=750>");
	                         out.println("<tr><td width=250>&nbsp;</td><td width=500>&nbsp;</td></tr>");      	
	
	
	                        //loop through all columns
	                        for(int i=1; i<numberOfColumns+1; i++)
	                        {
	                                        String columnName=rsmd.getColumnName(i);
	
	                                        //if it is the primary key
	                                        if (columnName.equals(fieldID))
	                                        {
	                                                //get the primary key
	                                                int pk = rs.getInt(fieldID);
	
	
	                                                 //display it as a hidden field
	                                                out.println("<input type=hidden name=" + "pk" + columnName + " value= " + rs.getInt(columnName) + ">");
	
	                                        }
	
	                                         //if it ends with ID e.g. MajorID, MinorID => it is a foreign key
	                                         //display the information from the referenced table
	                                         //as a drop down box
	                                        else if ((columnName.substring(columnName.length()-2, columnName.length())).equals("ID"))
	                                        {
	                                                //first remove the ID at the and and make it to uppercase and add 'S'
	                                                String joinTable=(columnName.substring(0,columnName.length()-2));
	                                                
	                                                //output the columnName
	                                                out.println("<tr><td>" + joinTable + "</td>");
	
	                                                String joinName=joinTable + "Name";
	                                                joinTable = joinTable.toUpperCase() + "S";
	                                                String joinID=columnName;
	                                                int thisID=rs.getInt(columnName);
	
	                                                String joinSQL = "select " + joinID + ", " + joinName + " from " + joinTable;
	
	                                                ResultSet rsIDs = Retriever.executeSQL(joinSQL);
	
	                                                //display it as a drop down box
	                                                String box="";
	                                                out.println ("<td><select name=" + columnName + ">");
	
	                                                while(rsIDs.next())
	                                                {
	                                                        box = box + "<option value=" + rsIDs.getInt(1);
	
	                                                        //if that was the one in the table have it selected by default
	                                                        if(rsIDs.getInt(1)==thisID)
	                                                                box = box + " selected ";
	
	                                                        box = box +">" + rsIDs.getString(2)+ "</option>";
	                                                }
	
	                                                out.println(box + "</select></td></tr>");
	                                        }
	
	                                        //if it is NotAvailableLst
	                                        else if (columnName.equals("NotAvailableList"))
	                                        {
	                                               String value= rs.getString(columnName);
	                                               String majors="";
	                                               
	                                               if(value.equals("none"))
	                                               {
	                                               		majors="none";
	                                               }
	                                               
	                                               else
	                                               {
		                                               //first parse the string
		                                               String [] majorIDs=parseString(rs.getString(columnName));
		
		                                               // 1. parse the majors list
		                                               majors=Retriever.getMajorCodesFromIDs(majorIDs);
	                                               }
	                                               
	                                 
	                                               		
	                                               //output the columnName
	                                                out.println("<tr><td>" + columnName + "</td>");
							
							//output the valuse
	                                                out.println("<td>" + majors);
	
	                                                // if this is not view display the link to change these courses
	                                                if(!oper.equals("View"))
	                                                {
	                                                	out.println("&nbsp;<a href='admin.ModifyMajorList?id=" + rs.getInt(fieldID) + "&oper=" + oper + "&table=" + table + "&mID=" + id + "'>Modify</a>");
	                                                }
	                                                
	                                                //close the cell and the row
	                                                out.println("</td></tr>");
	                                        }
	
	                                        //if it is a list
	                                        else if ((columnName.substring(columnName.length()-4, columnName.length())).equals("List"))
	                                        {
	
	                                                out.println(columnName);
	                                                
	                                                 String value= rs.getString(columnName);
	                                                 String majors="";
	                                                 String req="";
	                                               
	                                                 if(value.equals("none"))
	                                                 {
	                                               		req="none";
	                                                 }
	                                               
	                                                 else
	                                                 {
	                                                
	                                                	// 1. parse the course list
	                                                	req=RequirementParser.toCourseDisplayString(rs.getString(columnName));       
	                                                 }
	                                                 
	                                               		
	                                                //output the columnName
	                                                out.println("<tr><td>" + columnName + "</td>");
							
							//output the valuse
	                                                out.println("<td>" + req);
	
	
	                                                // if this is not view display the link to change these courses
	                                                if(!oper.equals("View"))
	                                                {
	                                                	out.println("&nbsp;<a href='admin.ModifyReq?id=" + rs.getInt(fieldID) + "&oper=" + oper + "&table=" + table + "&mID=" + id + "&field=" + columnName +"'>Modify</a>");
	                                                }
	                                                
	                                                //close the cell and the row
	                                                out.println("</td></tr>");
	                                                
	                                        }
	
	                                        //esle display it in a textbox
	                                        else
	                                        {
	                                               //output the columnName
	                                                out.println("<tr><td>" + columnName + "</td>");
							
							//output the valuse
	                                                out.println("<td><input type=textfield size=100 name=" + columnName + " value='" + rs.getString(columnName) + "'>");
	                                                //close the cell and the row
	                                                out.println("</td></tr>");
	                                        }
	
	                           
	                        }
				
				//close the table
				out.println("</table>");
				
				// if this is not view display the Submit Button
	                        if(!oper.equals("View"))
	                        {
	                        	out.println("<input type=submit value=submit></form>");
	                        }
	                        rs.close();
	                
	                }//end try
	
	                catch(SQLException e){out.println("SQLException " + e.getMessage());}
	         
	         }//end else if Modify
	         
	         //it is an Add Operation
	         //display empty fields
	         else
	         {
	         	try
	                {
	                        ResultSet rs=null;
	                        
	                        String fieldID ="";
	                        
	                        //get the primary key name from the table name e.g. CourseID from COURSES
	                        fieldID = table.substring(0,1) + (table.substring(1,table.length()-1)).toLowerCase() + "ID";

				//select * columns from the table                        	                        
	                        String sql = "select * from " + table;
	                        rs = Retriever.executeSQL(sql);
	                        
	                        //out.println(sql);
	                        rs.next();
	                        
	                       //get all column names and information from the table
	                        ResultSetMetaData rsmd = rs.getMetaData();
	                        int numberOfColumns = rsmd.getColumnCount();
	
	                        //send all paramters to query
	                        out.println("<form method=post action=admin.Query>");
	                        out.println("<input type=hidden name=table value=" + table + ">");
	                        out.println("<input type=hidden name=oper value=" + oper + ">");
	
				
				//set up the table
	                         out.println("<TABLE cellpadding=0 cellspacing=0 border=0 width=750>");
	                         out.println("<tr><td width=250>&nbsp;</td><td width=500>&nbsp;</td></tr>");      	
	
	
	                        //loop through all columns
	                        for(int i=1; i<numberOfColumns+1; i++)
	                        {
	                                        String columnName=rsmd.getColumnName(i);
						out.println("<tr>");
	
	                                        //if it is the primary key
	                                        if (columnName.equals(fieldID))
	                                        {
	                                                //MySQL will add it automatically
	                                                //since it is autonumber
	                                                
	                                                //get the biggest autonumber
	                                               
	                                                /* we don't need that for now
	                                                
	                                                ResultSet rs2=Retriever.executeSQL("select MAX(" + fieldID + ") from " + table);
	                                                rs2.next();
	                                                id = (new Integer(rs2.getInt(1)+1)).toString();
	                                                rs2.close();
	                                                */
	                                                
	                                        }
	
	                                         //if it ends with ID e.g. MajorID, MinorID => it is a foreign key
	                                         //display the information from the referenced table
	                                         //as a drop down box
	                                        else if ((columnName.substring(columnName.length()-2, columnName.length())).equals("ID"))
	                                        {
	                                                //first remove the ID at the and and make it to uppercase and add 'S'
	                                                String joinTable=(columnName.substring(0,columnName.length()-2));
	                                                
	                                                
	                                                 //output the columnName
	                                                out.println("<td>" + joinTable + "</td>");
	
	                                                String joinName=joinTable + "Name";
	                                                joinTable = joinTable.toUpperCase() + "S";
	                                                String joinID=columnName;
	                                                int thisID=rs.getInt(columnName);
	
	                                                String joinSQL = "select " + joinID + ", " + joinName + " from " + joinTable;
	
	                                                ResultSet rsIDs = Retriever.executeSQL(joinSQL);
	
	                                                //display it as a drop down box
	                                                String box="";
	                                                out.println ("<td><select name=" + columnName + ">");
	
	                                                while(rsIDs.next())
	                                                {
	                                                        box = box + "<option value=" + rsIDs.getInt(1);
	                                                        box = box +">" + rsIDs.getString(2)+ "</option>";
	                                                }
	
	                                                out.println(box + "</select></td>");
	                                        }
	
	                                        //if it is NotAvailableLst
	                                        else if (columnName.equals("NotAvailableList"))
	                                        {
	                                               //output the columnName
	                                               out.println("<td>" + columnName + "</td>");
	                                               out.println("<td>Please add this minor first and then come back to modify the Not Available List");
	                                               out.println("<input type=hidden name=" + columnName + " value=none></td>");
	                                               //out.println("&nbsp;<a href='admin.ModifyMajorList?id=" + rs.getInt(fieldID) + "&oper=" + oper + "&table=" + table + "&ID=" + id + "'>Modify</a>"); 
	                                        }
	
	                                        //if it is a list
	                                        else if ((columnName.substring(columnName.length()-4, columnName.length())).equals("List"))
	                                        {
	                                                out.println("<td>" + columnName + "</td>");
	                                                out.println("<td>Please add this course first and then come back to modify this list");
	                                                out.println("<input type=hidden name=" + columnName + " value=none></td>");
	                                                //out.println("&nbsp;<a href='admin.ModifyReq?id=" + rs.getInt(fieldID) + "&oper=" + oper + "&table=" + table + "&ID=" + id + "'>Modify</a>");     
	                                        }
	
	                                        //esle display it in a textbox
	                                        else
	                                        {
	                                                out.println("<td>" + columnName + "</td>");
	                                                out.println("<td><input type=textfield name=" + columnName +  "></td>");
	                                        }
	
	                                 out.println("</tr>");
	                        }
				
				//close the table
				out.println("</table>");
				
				// display the Submit Button
	                        out.println("<input type=submit value=submit></form>");
	                        
	                        
	                        rs.close();
	                
	                }//end try
	
	                catch(SQLException e){out.println("SQLException " + e.getMessage());}
	         
	         }//end else for Add	
		 	      
        }

        //this method is used to return an array of MajorIDs from the star delimited List
        public String [] parseString(String in)
        {

                java.util.StringTokenizer st = new java.util.StringTokenizer(in, "*");
                String [] result = new String[st.countTokens()];
                int i=0;

                while (st.hasMoreTokens())
                {
                        result[i++]=st.nextToken();

                }

                return result;
        }

}