Project 2 Requirements

• Travel Agency Web Site -- Phase II

Travel Agency Web Site Enhancements (Phase II)

1. A New Customer Form (that will create a new Customer record in the database) for the Customer
2. A Feedback Form (that will create a new Feedback record in the database) for the Customer
3. A New Trip Form (that will create a new Trip record in the database) for the Customer or Trip Manager
4. A New Transport Form (that will create a new Transport record in the database) for the Customer or Trip Manager
5. A New Location Form (that will create a new Location record in the database) for the Customer or Trip Manager
6. An enhanced Trip Report similar to what you delivered for Project 1 (showing all trips existing for a customer in the Trip table)
7. A Location Report (that will show the various travel locations where trips are already available).
8. A Lodging Capacity Report showing the percent capacity for each Lodging establishment for a specific date.
9. A Top Customers Report showing the customers who have booked the most dollars worth of travel services (by decending dollars). This report is for travel agency management.
10. An Innovative Report of your own choosing that shows something interesting and useful for the customer, trip manager, or travel agency management's use.

Of course, some of your reports will end up longer than the examples, but the examples show you the kind of data I expect for each report (your particular data choices can be different). Remember that you only have to code the header row and one row of the detail because you will put your format in a loop that the Java servlet will fill with your SQL Command (query) results.

To make our database work for everyone, I am requesting that you use the database field names as the NAME attribute values within your form control elements. For example, in your New Customer Form, you would have an INPUT element that looks like this:

<INPUT TYPE=TEXT NAME=First_Name>

because I have documented in the Travel Database Data Definiton SQL Commands document that the Customer table field for first name is named First_Name. Creating your forms for requirements 1, 2, 3, 4, and 5 above should be easy if you just create an input control for each field in the respective tables (of course you can add additional information in the forms that just won't be saved in the tables).

Turning in Your HTML Forms

<FORM ACTION="http://sambucus.cev.washington.edu/servlets/addcustomer" method=GET>

The ACTION attribute points to the URL where the FORM can be processed by a Java servlet. I am writing the basic servlets (though I'll make the source code available to you in a later email message) and you will be writing the main servlet processing for your form and reports. So, to make that work correctly, you will have to point to the right servlet for each form and report. You will see a table of proper servlet names to use later in this document. I will differentiate between student forms through an INPUT element that you should include immediately after each open FORM tag. In the case of my addcustomer.html form, you see:

<INPUT NAME=author TYPE=HIDDEN VALUE=bdc>

I set the VALUE attribute to my assigned initials, bdc. You should set all your hidden INPUT element VALUE attributes to your assigned initials (even HIDDEN control types pass the named variable to the servlet).

Turning in Your Form Processing Java Code

stmt.executeUpdate("INSERT INTO Lodging VALUES (" + 
                  getUniqueID(con, out, "Lodging_ID", "Lodging") + ",'" +
                  request.getParameter("Name") + "'," + request.getParameter("Type") + "," + 
                  request.getParameter("Capacity") + ",'" + request.getParameter("Owner") + "')");

Don't panic if this looks completely foreign to you. Just think it through with me here. Remember that a CGI script can connect a Web server to a database and execute SQL commands through a database interface. We just happen to be using Java as our CGI language to make the connection. So, by the time your code runs, the Java has already connected to the database, created a valid Statement object (referenced by the stmt for which our executeUpdate() routine will run. Now the CGI code must also be able to get the data from our form into the database. With Java, we can use the getParameter() routine that is available from the Servlet request object (which is created automatially by the Java servlet engine running on the server). So, all you have to do is use the request.getParameter() routine to process each value from your form and put its value into the SQL command that will add a new record to the table you wish to append. Any other text you need between the form data you can put together using the + operator (the + operator in Java is called a concatenation operator when you are using text values (called Strings).

Look closely at where the quotes (single and double) are placed in the update statement. The single quotes are passed to the database to properly identify text and date fields (remember text and dates need to be enclosed in single quotes). Now try doing some simple substitutions with me. Imagine a travel agent employee filled out a New Lodging Form on the Web. The form included INPUT controls with the following NAME attributes and control contents:

NAME	CONTENT
Name	Rambler Hotel
Type	0
Capacity	58
Owner	bdc

The getUniqueID() routine gets a new autogenerated Lodging_ID for the new Lodging table record. You just need to pass it the database connection (con), writer (out), attribute name ("Lodging_ID"), and table name ("Lodging") and the getUniqueID() will return the next sequentially available ID for that table. The form INPUT control names match with the parameter in the getParameter() routine so that the proper value will be placed into the INSERT INTO command in the right place. Take the values from the CONTENT column above and substitute them into the appropriate getParameter() routine in the executeUpdate() above. You will see the Java actually sends:

INSERT INTO Lodging VALUES (47,'Rambler Hotel',0,50,'bdc')

to the database. Since the command is a valid command, the record is gladly added to the database. You should write 5 similar stmt.executeUpdate() commands for your five forms and save them in 5 separate files. Then, upload your files to your directory on sambucus.cev.washington.edu with the following names:

FORM	File Name
New Customer	addcustomer.txt
New Feedback	addfeedback.txt
New Transport	addtransport.txt
New Trip	addtrip.txt
New Location	addlocation.txt

The text files will match your HTML file names closely. The only difference should be the file extension. For your HTML, upload the following HTML file names to the subdiretory with your initials (and use the respective URL in you forms):

FORM	File Name	Servlet URL
New Customer	addcustomer.html	http://sambucus.cev.washington.edu/servlet/addcustomer
New Feedback	addfeedback.html	http://sambucus.cev.washington.edu/servlet/addfeedback
New Transport	addtransport.html	http://sambucus.cev.washington.edu/servlet/addtransport
New Trip	addtrip.html	http://sambucus.cev.washington.edu/servlet/addtrip
New Location	addlocation.html	http://sambucus.cev.washington.edu/servlet/addlocation

If you do this correctly, your form will be processed by your SQL function as intended. If you do it wrong, I will send you an email message explaining what is wrong and how to correct it. I plan to do this once a day through June 12th, when I will have to accept whatever you haven't gotten working as your final (and you will have had to make a good effort by midnight on June 10th).

Turning in Your Report Generating Java Code

REPORT	File Name	Servlet URL
Alphabetical Locations	locations.txt	http://sambucus.cev.washington.edu/servlet/locations
Lodging Capacity	capacity.txt	http://sambucus.cev.washington.edu/servlet/capacity
Trip Detail	trips.txt	http://sambucus.cev.washington.edu/servlet/trips
Customer Revenue	revenue.txt	http://sambucus.cev.washington.edu/servlet/revenue
Your Specified	other.txt	http://sambucus.cev.washington.edu/servlet/other

Reports are generated similarly to the SELECT command results Java code we reviewed together in class (and which you can always review in the SQL Starter Kit document). To generate a report you create a hyperlink in your HTML document that points to a valid URL for the proper servlet to run. For example, for my Lodging Capacity Report, I would create an A element like the following:

<A href=http://sambucus.cev.washington.edu/servlets/capacity?author=bdc>

You can put all of your report hyperlinks on your travel agency home page or you can put them on other pages within your site if you prefer. But, I will need to find them easily when reviewing your site in order to grade them as best as you will want me to. When someone clicks on a hyperlink like the example, the Java Servlet engine on the sambucus server will pass the request on to the servlet named capacity. The author=bdc will be passed as a parameter the report can us with a request.getParameter() statement just as you saw in the forms processing case.

Your text files should look something like the following:

out.println("<HTML>" +
            "<HEAD><TITLE>SQL Result Set</TITLE></HEAD>" +
            "<BODY bgcolor=#ffff99>" +
            "<H3>" + Title + "</H3><TABLE>" +
            "<tr><th>Name</th><th>Type");

try {
   Class.forName("postgresql.Driver");
   Connection con = DriverManager.getConnection("jdbc:postgresql://sambucus.cev.washington.edu/travel","postgres","password");

   Statement st = con.createStatement();

   ResultSet rs = st.executeQuery("SELECT * FROM Transport WHERE Owner='bdc'");

   while(rs.next()) {
             // Show Transport Name 
             out.println("<tr>");
             out.println("<td>" + rs.getString(2) + "</td>");
             out.println("</tr>");
             // Show Transport Type 
             out.println("<tr>");
             out.println("<td>" + rs.getInt(3) + "</td>");
             out.println("</tr>");
             // Show Transport Capacity
             out.println("<tr>");
             out.println("<td>" + rs.getInt(4) + "</td>");
             out.println("</tr>");
   }
   out.println("</TABLE>");

   rs.close();
   st.close();
   con.close();

   out.println("</BODY></HTML>");

Note the Java basically mixes your HTML tags with the results from your SQL SELECT command. Spend some time thinking this through. Print the code out and use highlighters to mark the parts that are HTML and the parts that come from the Java Servlet technology. Compare mixing SQL results and HTML tags using the CGI with mixing XML data and HTML tags using XSLT.

getUniqueID() Method

   /*
     PrintWriter is the Java Servlet HTML writer object class for the Servlet Engine
     Connection is the Java JDBC database connection class
     String is the Java class for managing text content
   */
   public int getUniqueID(PrintWriter out, Connection con, String field, String table) {
   
      int max=0;

      try {
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT MAX(" + field + ") AS MAX_ID FROM " + table);
         if (rs.next()) {
            max = rs.getInt(1) + 1;
         } else {
            max = 0;
         }
         stmt.close();
      } catch (java.lang.Exception ex) {
         if (ex != null) {
            out.println("Exception: " + ex.getMessage() + " from command: ");
         } else {
            out.println("Null exception from command.<p>");
         }
      }
      return max;
   }