Project 2 Requirements

Electronic Resource Web Site -- Phase II

Through your experience working on project 2, you will have the opportunity to see how a Web-enabled system works. You have already done a great job of starting the general look and feel of the site in terms of how people (the application clients) will interact with your site. My responsibility is to add new requirements to make your work challenging (but not tedious) as you gain a perspective of how back-end server services connect to the client. I have come up with the following list of requirements for Phase II of your project:

1. A New Customer Form (that will create a new Customer record in the database) for Customers to use to indentify themselves to the organization.
2. A Search Form (that will anyone to search the database) for the Owner to use to check on inventory coverage.
3. A Moderation Form (that will enable and document an upload review) for the Moderator
4. A New Upload Form (that will create a new Upload record in the database) for the Supplier
5. Integration of the New Supplier Form and Moderator Event Report pages I provide you as examples.
6. A Download Report similar to what you delivered for Project 1 (showing details about files that have been downloaded by Customers)
7. A Supplier Activity Report (that will show the summary activity of Suppliers regarding total upload file counts, total size uploaded, and percent of total file inventory size).
8. A Resource Inventory Report by Category showing the resources by category available (however you define category).
9. A Top Customers Report showing the customers who have been most active and the metrics by which you define 'Top' (# of files, total bytes downloaded, etc.).
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.

New Customer Form --> CUSTOMERS - Set Up Account
Search Form -->       MODERATOR - Search Available Manuals
Moderation Form -->   MODERATOR - Review Latest Addition
New Upload Form -->   SUPPLIERS - Upload Servicing Manual
New Supplier Form --> SUPPLIERS - Set up Account

All file reports are available from the VIEW REPORTS link at the bottom of the home page.

In order to fulfill these requirements, you will need to understand three concepts:

1. How the Common Gateway Interface (CGI) enables communication from a client back to the Web site server
2. How the Structured Query Language (SQL) enables data management and manipulation in a relational database
3. How a dyanmic Web page service is enabled via a programming or scripting language (in our case Java in Java Server Page (JSP) technology)

I believe you will find the CGI process straightforward. I believe the tutorial will get you well on your way with SQL. My biggest concern is in you being able to focus on the JSP technology in a top-down manner so you don't get overwhelmed with the details. I will provide an example of working form and report that you can include in your Web site. I will include a style sheet you can manipulate in order to get that form and report to look like the rest of your site. The integration of these two Java Server Pages is requirement 5 from the list above.

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=Name>

because I have documented in the SQL Starter Notes for Project 2 document that the Customer table attribute for name is named Name. Creating your forms for requirements 1, 2, 3, and 4 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://www.oworld.org/498/bdc/addsupplier.jsp" method=GET>

Turning in Your Form Processing Java Code

I will provide you with your own functional addsupplier.jsp page that you can use as a model of how to prepare a JSP that processes a form. They will process the Add Supplier Form that I have provided for you to incorporate into your Web site. Your JSPs can be very similar if you are not comfortable trying out more complicated processing (the idea is for you to learn as much as you can through self-discovery with trial and error).

For each of these form processing JSPs, you will use form processing code that can process the form and add a new record to the appropriate table in the database. This can be easy once you get the hang of it. Here is an example that should make the process clear (be sure to remind yourself of what the complete JSP looks like as you consider the SQL code integration). If I had asked you to process a New Supplier Form, you would have wanted to create the following executeUpdate command:

stmt.executeUpdate("INSERT INTO Supplier VALUES (" + request.getParameter("Name") + "','" + 
                                                     request.getParameter("Password") + "'," + 
                                                     request.getParameter("Member") + ",'" + 
                                                     request.getParameter("Initials") + "')");

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 application (apache in our case) to a database and execute SQL commands through a database interface. We just happen to be using Java as our CGI processing 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 a Java Server Page request object (which is created automatially by the Tomcat service running on the server). So, all you have to do is use the request.getParameter() method 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 within the form data you can put together using the + operator (the + operator in Java is called a concatenation operator when you are using specialized text objects called Strings). A String is passed on literally as written when you put double quotation marks around text.

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 new file submitter filled out a New Supplier Form on the Web. The form includes INPUT controls with the following NAME attributes and control contents:

NAME	CONTENT
Name	Viviana Perez
Password	eid8bW9
Member	1
Initials	bdc

The Postgres serial type can generate a new SupplierID based on incrementing a value by one from the last SupplierID value generated in the table. The form INPUT control names match with the parameter in the getParameter() routine above 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() routines in the executeUpdate() above. You will see the Java actually sends:

INSERT INTO Supplier VALUES ('Viviana Perez','eid8bW9',1,'bdc')

to the database for processing against our filedist database. Since the command is a valid command, the record is immediately added to the database. You should write at least four similar stmt.executeUpdate() commands for your four required project 2 forms and properly encode them into Java code.

Each of these forms should be readily available from your project Web site (and the proper processing JSP files should be uploaded according to your URL specification).

If you do this correctly, your form will be processed by your SQL function as intended. If you do it wrong, you may need some help determining what went wrong. Send me an e-mail message with the name of your form and JSP and I can help you debug the error. Most of the time, it will be a silly typing error. If it is something more significant, we should meet and discuss the overall process.

Turning in Your Report Generating Java Code

You will want to code the Java carefully, but the steps should be clear enough that you feel comfortable making simple changes to the example Moderator Event Report I provide you. Basically, you should write your SQL statements first and make sure they work properly against our class database (you can do that via our class on-line SQL Select Tool). Then, you should transfer those working queries into a JSP executeQuery statement like:

       rs = stmt.executeQuery("SELECT Show_ID, Entertainer_FK AS eName, Venue_FK AS vName, Date, Start_time, Ticket_Price FROM Show WHERE Owner='498'");

       while(rs.next()) {        
            String show_id = rs.getString("Show_ID");
            String entertainer_id = rs.getString("eName");	
            String venue = rs.getString("vname");
            String date =rs.getString("Date");
            String time =rs.getString("Start_time");
            String price =rs.getString("Ticket_price");

Then, you can use those variables within HTML or XHTML tags to generate attractive report output. For example:

%>
	<TR>
	<TD><%= show_id %></TD>
	<TD><%= entertainer_id %></TD> 
	<TD><%= venue %></TD>
	<TD><%= date %></TD>
	<TD><%= time %></TD>
	<TD>$<%= price %></TD>
	</TR>
<%
        }

Each of your reports should be nicely integrated with your Web site from Project 1.

getUniqueID() Method

Note that you could also maintain your table unique primary keys via Java methods yourself. I leave you an example of how I do that here:

   /*
     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;
   }