/*
 * WorkingFlightsServlets.java
 *
 * Created on 25 February 2007, 15:03
 */
package test;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

/**
 *
 * @author lonsdalp
 * @version
 */
public class WorkingFlightsServlets extends HttpServlet {
    
    
    boolean OriginExists=false;
    boolean DestinationExists=false;
    boolean deptExists=false;
    boolean arrivalTimeExists=false;
    String[] stringy = new String[0];
    /** Handles the HTTP GET method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
//creating html form
        out.println("<P>");
// in the following line, we use the escape character \ before the " because the " is actually part of the HTML code, not the Java code
// using the escape character tells the compiler to treat the following " as a literal string character, not part of the code
        out.print("<form action= \"WorkingFlightsServlet\" ");
        out.println("method=POST>");
        out.println("<head>");
        out.println("<title>Maybe</title>");
        out.println("</head>");
        out.println("<body bgcolor=#CCFFFF>");
        out.println("<h1> MAYBE CRAZY </h1>");
        out.println("<table>"+"<tr>");
        out.println("<td><b>Enter Origin:</b></td>");
        out.println("<td><input type=text size=20 name=Origin></td>");
        out.println("</tr>"+"<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Destination:</td>");
        out.println("<td><input type=text size=20 name=Destination></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Departure Time:</td>");
        out.println("<td><input type=text size=20 name=DeptTime></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Arrival Time:</td>");
        out.println("<td><input type=text size=20 name=ArriTime></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Maximum Distance:</td>");
        out.println("<td><input type=text size=20 name=MaxDist></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("<tr>");
        out.println("<td><b>Enter Maimum Changes:</td>");
        out.println("<td><input type=text size=20 name=MaxCha></td>");
        out.println("</tr>");
        out.println("<br>");
        out.println("</table>");
        out.println("<br>");
        out.println("<b>Group by <BR>");
        out.println("<br>");
        
        //Get the value from the cookie and set the correct radio button
        Cookie cook1= getCookieValue(request,"groupby");
        if(cook1.getValue()!=null){
            if(cook1.getValue()=="Origin"){
                out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Origin>checked=checked Origin<BR>");
                out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Destination>Destination<BR>");
            }if(cook1.getValue()=="Destination"){
                out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Origin> Origin<BR>");
                out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Destination>checked=checked Destination<BR>");
            }
        }else{
            out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Origin> Origin<BR>");
            out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Destination>Destination<BR>");
        }
        
        out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Origin>checked=checked Origin<BR>");
        out.println("<br>");
        out.println("<INPUT TYPE=RADIO NAME=groupby VALUE=Destination>Destination<BR>");
        out.println("<br>");
        out.println("<b><input type=submit>");
        out.println("</body>");
        out.println("</form>");
    }
    
    /** Handles the HTTP POST method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        try {
//        float dept=-1;
//        float arrivalTime;
            Class.forName("org.postgresql.Driver");
            Connection con;
            con = DriverManager.getConnection("jdbc:postgresql://dbteach/ug74evd", "ug74evd", "rotofroh");
            Statement stamt = con.createStatement();
            ResultSet rs = stamt.executeQuery("SELECT * FROM FLIGHTS WHERE ORIGIN='TTT';");
            rs= DatabaseLink(request, response, out, rs, stamt);
            String format;
            ResultSetMetaData metadata = rs.getMetaData();
            int colcount = metadata.getColumnCount();
            out.println("<table BORDER=3 bgcolor=#CCFFFF>");
            out.println("<tr>");
            for (int i = 1; i <= colcount; i++) {
                format = metadata.getColumnDisplaySize(i) + "s ";
                //   out.println("<tr>");
                // out.println("<td>"+ format+"</td>");
                out.println("<td>"+metadata.getColumnLabel(i)+"</td>");
                // out.println("</tr>");
                
            }
            out.println("</tr>");
            while(rs.next()) {
                out.println("<tr>");
                for (int i = 1; i <= colcount; i++) {
                    format = metadata.getColumnDisplaySize(i) + "s ";
                    // out.println(format);
                    out.println("<td>"+rs.getString(i)+"</td>");
                    // out.println("</tr>");
                }
                out.println("</tr>");
                
            }

            rs.close();
            setCookies(response);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        
        
        // rs.next();
        //need to print out result set neatly!
        out.println();
        
        
        
    }
    
    
    public ResultSet DatabaseLink(HttpServletRequest request, HttpServletResponse response, PrintWriter out, ResultSet rs, Statement stamt){
        float dept=-1;
        float arrivalTime;
        String Origin = request.getParameter("Origin");
        out.println(Origin);
        String Destination = request.getParameter("Destination");
        out.println(Destination);
        String dept1 = request.getParameter("depttime");
        if(dept1!=null&& dept1.length()>0){
            dept = Float.parseFloat(dept1);
            deptExists=true;
        }
        String arrivalTime1 =request.getParameter("arrivalTime");
        if(arrivalTime1!=null && arrivalTime1.length()>0){
            arrivalTime = Float.parseFloat(arrivalTime1);
            //String cookieNamearrivalTime1 = arrivalTime1;
            arrivalTimeExists=true;
        }
        if(Origin!=null&& Origin.length()>1){
            OriginExists=true;
            //String cookieNameOrigin = Origin;
        }
        if(Destination!=null&& Destination.length()>1){
            //String cookieNameDestination = Destination;
            DestinationExists=true;
        }
        
        try {
            if(request.getParameter("groupby")!=null){
                stringy= request.getParameterValues("groupby");
            }
//            Class.forName("org.postgresql.Driver");
//            Connection con;
//            con = DriverManager.getConnection("jdbc:postgresql://dbteach/ug74evd", "ug74evd", "rotofroh");
//            Statement stamt = con.createStatement();
//            ResultSet rs = stamt.executeQuery("SELECT * FROM FLIGHTS WHERE ORIGIN='TTT';");
            //if groupby is set to destination
            if(stringy[0].equals("Destination")){
                out.println("groupby destination");
                if(!OriginExists){
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){
                                out.println("Please enter a value");
                            }else{//only arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE  arrival_time ="+arrivalTime1+" ORDER by destination;");
                            }
                        }else{//if dept_time does exist but destinaion and origin don't
                            if(!arrivalTimeExists){//only dept_time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+"\'ORDER by destination;");
                            }else{//dept_time and arrival time exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and arrival_time =\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }
                    }else{if(!deptExists){//if destination exists
                        if(!arrivalTimeExists){
                            out.println("we have a destination");
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE destination =\'"+Destination+"\'ORDER by destination;");
                        }else{//if destination and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE arrival_time =\'"+arrivalTime1+ "\' and destination =\'"+Destination+"\'ORDER by destination;");
                        }
                    }else{//if destination and dept time exist
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\'ORDER by destination;");
                        }else{//if destination, dept time and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                        }
                    }
                    }
                }else{//origin exists
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){//only origin exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'ORDER by destination;");
                                out.println("query done");
                            }else{//arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }else{
                            if(!arrivalTimeExists){// dept time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }else{// dept time, arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and arrival_time=\'"+arrivalTime1+"\' and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }
                        }
                    }else{
                        if(!deptExists){
                            if(!arrivalTimeExists){//destination and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\'ORDER by destination;");
                            }else{//destination, origin and arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and destination=\'"+Destination+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by destination;");
                            }
                        }else{
                            if(!arrivalTimeExists){
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and dept_time=\'"+dept1+"\'ORDER by destination;");
                            }
                            //departure time, destination,origina and arrival time exists
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'and dept_time=\'"+dept1+"\'ORDER by destination;");
                        }
                    }
                }
            }
            if(stringy[0].toString().equals("Origin")){
                out.println("groupby origin");
                if(!OriginExists){
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){
                                out.println("Please enter a value");
                            }else{//only arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE  arrival_time ="+arrivalTime1+" ORDER by Origin;");
                                
                            }
                        }else{//if dept_time does exist but destinaion and origin don't
                            if(!arrivalTimeExists){//only dept_time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+"\' ORDER by Origin;");
                            }else{//dept_time and arrival time exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and arrival_time =\'"+arrivalTime1+"\' ORDER by Origin;");
                            }
                        }
                    }else{if(!deptExists){//if destination exists
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE destination =\'"+Destination+"\' ORDER by Origin;");
                        }else{//if destination and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE arrival_time =\'"+arrivalTime1+ "\' and destination =\'"+Destination+"\' ORDER by Origin;");
                        }
                        
                        
                    }else{//if destination and dept time exist
                        if(!arrivalTimeExists){
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' ORDER by Origin;");
                        }else{//if destination, dept time and arrival time exist
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE dept_time =\'"+dept1+ "\' and destination =\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\' ORDER by Origin;");
                        }
                    }
                    }
                    
                }else{//origin exists
                    if(!DestinationExists){
                        if(!deptExists){
                            if(!arrivalTimeExists){//only origin exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' ORDER by Origin;");
                                out.println("query done");
                            }else{//arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and arrival_time=\'"+arrivalTime1+"\' ORDER by Origin;");
                            }
                            
                        }else{
                            if(!arrivalTimeExists){// dept time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and dept_time=\'"+dept1+"\' ORDER by Origin;");
                            }else{// dept time, arrival time and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and arrival_time=\'"+arrivalTime1+"\' and dept_time=\'"+dept1+"\'ORDER by Origin;");
                            }
                        }
                        
                    }else{
                        if(!deptExists){
                            if(!arrivalTimeExists){//destination and origin exist
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' ORDER by Origin;");
                            }else{//destination, origin and arrival time exists
                                rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\'and destination=\'"+Destination+"\'and arrival_time=\'"+arrivalTime1+"\'ORDER by Origin;");
                            }
                            
                        }else{
                            //departure time, destination,origina and arrival time exists
                            rs= stamt.executeQuery("SELECT * FROM FLIGHTS WHERE Origin=\'"+Origin+"\' and destination=\'"+Destination+"\' and arrival_time=\'"+arrivalTime1+"\'and dept_time=\'"+dept1+"\' ORDER by Origin;");
                        }
                        
                    }
                }
            }
        }catch(SQLException ex){
            ex.getMessage();
        }
        return rs;
    }
    
    /**
     * Takes in the request so that we can get the cookies from it.
     * We then loop through all the cookies in the request to find the cookie we need
     * we extract the value and return it to the calling method.
     * If no cookie is found, we return null, we could instead throw an Exception
     * @param request
     * @param key - the name of the value we want to return
     * @return
     */
    public Cookie getCookieValue(HttpServletRequest request, String key){
        Cookie[] cookies = request.getCookies();
        if ((cookies != null) && (cookies.length > 0)) {
            // we can iterate through each Cookie and get its Name and Value
            for (int i = 0; i < cookies.length; i++) {
                if(cookies[i].getName().equals(key)){
                	return cookies[i];
                }
            }
        }
        return null;
    }
    
    public void setCookies(HttpServletResponse response){
        String cookieName = "groupby";
        // stringy[0] contains the value of the selected groupby radio button.
        String cookieValue1 = stringy[0];
        
        if (cookieName != null && cookieValue1 != null) {
            Cookie cookie = new Cookie(cookieName, cookieValue1);
            cookie.setMaxAge(300);
            response.addCookie(cookie);
        }
    }
}