Untitled

<html>
    <head>
        <title>Submitting Reservation</title>
        <link rel="stylesheet" type="text/css" href="assets/home.css">
    </head>
    <body>
    <%@ page import="java.sql.*" %>
    <%@ page import="java.util.Date" %>
    <%@ page import="java.text.SimpleDateFormat" %>
    <%@include file="menu-bar.jsp" %>
<%
    //Set getConnection() parameters to connect to my database
    String url = "jdbc:mysql://localhost:3306/internetairline";
    String username = "root";
    String DBpassword = "test$cozaser199";
    Connection conn;
    PreparedStatement pstmtFlightSet, pstmtAvail, pstmtReserve;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, username, DBpassword);

        //Find out if reservation was round flight or one way
        boolean roundFlight;
        String prepare;
        if ( request.getParameter("roundFlight").equals("checked") )
            roundFlight = true;
        else
            roundFlight = false;

        //Get flight info from sent parameters and set value to Strings
        int dFlight = Integer.parseInt(request.getParameter("dFlight"));
        int rFlight = Integer.parseInt(request.getParameter("rFlight"));

        // Depending on round flight or one way, build String to represent flightSet to be used in SELECT available query
        String flightSet;
        if (roundFlight)
        {
            prepare = "SELECT fid FROM flight f1 WHERE f1.orig =" + dFlight + " AND f1.dest ="+ rFlight +" UNION SELECT fid FROM flight f2 WHERE f2.orig ="+ rFlight +" AND f2.dest ="+ dFlight +";
            pstmtFlightSet = conn.prepareStatement(prepare1);
        }
        else
        {
            prepare = "SELECT fid FROM flight f WHERE f.orig ="+ dFlight +" AND f.dest ="+ rFlight;
            pstmtFlightSet = conn.prepareStatement(prepare);
        }
        pstmtFlightSet = conn.prepareStatement("SELECT available FROM flight WHERE fid IN (" + prepare + ")");
        //Selecting available # of tickets to validate seat availability.
        ResultSet rsAvail = pstmtFlightSet.executeQuery();
        //Getting "available" field values and checking if negative.
        if (roundFlight)
        {
            //Move through 2 records and save "available" value
            rsAvail.next();
            int dAvail = Integer.parseInt(rsAvail.getString(1)); //Get departure available column field, set to int to be compared
            rsAvail.next();
            int rAvail = Integer.parseInt(rsAvail.getString(1)); //Get return available field

            //If negative, flight(s) cannot be reserved. Redirect and alert to home page.
            if (dAvail < 0 || rAvail < 0)
            {
%>              <script type="text/javascript">
                    alert("No availability on this flight. Please decrease the number of tickets you are reserving.")
                    window.location = 'home.jsp'
                </script>
<%          }
        }
        else    //one way flight
        {
            //Move to first and only record and save available value.
            rsAvail.next();
            int dAvail = Integer.parseInt(rsAvail.getString(1)); //Get departure available column field, set to int to be compared

            //If negative, flight(s) cannot be reserved. Redirect and alert to home page.
            if (dAvail < 0)
            {
%>              <script type="text/javascript">
                    alert("No availability on this flight. Please decrease the number of tickets you are reserving.")
                    window.location = 'home.jsp'
                    </script>
<%          }
        }
        //Set current date to be added in reservation insert
        Date dateCurrent = new Date();
        SimpleDateFormat fdate = new SimpleDateFormat("yyyy-MM-dd");
        String date = fdate.format(dateCurrent);

        //Getting all info necessary for inserting a flight reservation into table
        //rfilight only needed for round flight, so we must check if round flight
        if (roundFlight)
        {
            pstmtReserve = conn.prepareStatement("INSERT INTO reservation(cid, dfid, rfid, qty, cardnum, cardmonth, cardyear, order_date) VALUES (?,?,?,?,?,?,?,?)");
            pstmtReserve.clearParameters();
            pstmtReserve.setInt(1, Integer.parseInt(request.getParameter("cid")));
            pstmtReserve.setInt(2, dFlight);
            pstmtReserve.setInt(3, rFlight);
            pstmtReserve.setInt(4, Integer.parseInt(request.getParameter("numTickets")));
            pstmtReserve.setInt(5, Integer.parseInt(request.getParameter("cardNumber")));
            pstmtReserve.setInt(6, Integer.parseInt(request.getParameter("cardMonth")));
            pstmtReserve.setInt(7, Integer.parseInt(request.getParameter("cardYear")));
            pstmtReserve.setString(8, date);
        }
        //One way
        else
        {
            pstmtReserve = conn.prepareStatement("INSERT INTO reservation(cid, dfid, qty, cardnum, cardmonth, cardyear, order_date) VALUES (?,?,?,?,?,?,?)");
            pstmtReserve.clearParameters();
            pstmtReserve.setInt(1, Integer.parseInt(request.getParameter("cid")));
            pstmtReserve.setInt(2, dFlight);
            pstmtReserve.setInt(3, Integer.parseInt(request.getParameter("numTickets")));
            pstmtReserve.setInt(4, Integer.parseInt(request.getParameter("cardNumber")));
            pstmtReserve.setInt(5, Integer.parseInt(request.getParameter("cardMonth")));
            pstmtReserve.setInt(6, Integer.parseInt(request.getParameter("cardYear")));
            pstmtReserve.setString(7, date);
        }
        //Insert into reservation based on round or one way flight
        int numRows= pstmtReserve.executeUpdate();

%>      <script type="text/javascript">
            alert("Your flight has been reserved!")
            window.location = 'home.jsp'
        </script>
<%
    rsAvail.close();
    pstmtFlightSet.close();
    pstmtReserve.close();
    conn.close();
    }//end try, start catch block for any errors/exceptions
    catch(SQLException ex) {
        out.println("SQLException: " + ex.getMessage());
        out.println("SQLState: " + ex.getSQLState());
        out.println("VendorError: " + ex.getErrorCode());
        ex.printStackTrace();
    }
%>
    </body>
</html>