Project 2 Requirements

This document should communicate completely my expectations for your Project 2 deliverables. If not, please ask questions via e-mail or in class.

Product Manufacturing Web Site Enhancements (Phase II)

Through project 2, you will have the opportunity to see a Web-enabled job lot manufacturing system in partial action. You have already done a great job of starting the interface for the product and raw materials to the manufacture database. I ask you to be more creative to maximize your self-learning during the project 2 work phase. I hope you will continue to work on this project after you submit it, so I understand if you run out of time. My hope for you is that you get the most out of this for yourself.

My responsibility is to add new requirements to make your work challenging (but not tedious). With your help in class on May 12th, I have come up with the following list of requirements for Phase II:

  1. A New Customer Form (that will create a new Customer record in the database)
  2. A New Product Form (that will create a new Product record in the database)
  3. A New Raw Material Form (that will create a new Raw Material record in the database)
  4. A New ProductSale Form (that will create a new ProductSale record in the database)
  5. A New Production Form (that will create a new Production record in the database)
  6. A New MaterialPurchase Form (that will create a new MaterialPurchase record in the database)
  7. A Raw Materials Inventory Report similar to what you delivered for Project 1
  8. A Customer Buying History Report showing a listing of all purchases a customer has made descending by date.
  9. A Quality Index by Supplier Report showing a list of suppliers and the average quality of shipments received.
  10. A Product Profit Report showing the margin made on sales of one product.
  11. An Innovative Report of your own choosing that shows something interesting and useful for the customer, inventory specialist, or organizational manager.
Examples of all the above requirements are here (though they should be made better where appropriate):

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

because I have documented the name in the Manufacture Data Definition SQL Commands document that the Customer table field for name is named Name. Creating your forms for requirements 1, 2, 3, 4, 5, and 6 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

An example of how you should turn in your HTML code is my Add Raw Material Form. Study the source code by viewing its source in your browser. Note the name attributes are EXACTLY the same as the Material table stipulates (specifies). Note the FORM element has a ACTION attribute:

<FORM ACTION="http://www.cev.washington.edu/498/addmaterial.jsp" method=GET>

The ACTION attribute points to the URL where the FORM can be processed by a Java Server Page (JSP). You will be writing the main JSP 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 addmaterial.html form, you see:

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

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

Turning in Your Form Processing Java Code

For each HTML form you create, you will need to submit servlet processing code that can process the form and add a new record to the appropriate table in the database. This should be easy once you get the hang of it. If I had asked you to process a New Customer Form, you would have wanted to submit the following addCustomer_xxx.jsp (where xxx would be your assigned initials): 
<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="javax.naming.*" import="javax.sql.*" %>

<HTML>
<HEAD>
<TITLE>Successful Add to Add Show Table</TITLE>
<META content="text/html; charset=unicode" http-equiv=Content-Type>
</HEAD>
<body>

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/manufacture");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();
    
    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="Successful Add of New Material";
    String new_id="-1";
    
    try {
            rs = stmt.executeQuery("SELECT MAX(Material_ID) AS MAX_ID FROM Material");
            
            if (rs.next()) {
                new_id = "" + (rs.getInt(1)+1);
            } else {
                new_id = "0";
            }
            rs.close();
            
            //create new set here
            stmt.executeUpdate("INSERT INTO Material VALUES (" + new_id + "," + 
                  request.getParameter("OnHand") + "," + request.getParameter("LeadTime") + "," + 
                  request.getParameter("Unit") + ",'brc')");
                      
    } catch (Exception exp) {
        error = exp.toString();
    }

    stmt.close();
    conn.close();
%>

<!-- Put your visual HTML body content here -->

<H3><%= error %></H3>
</BODY>
</HTML>
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() method that is available from the JSP request object (which is created automatially by the Java Server Page 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 INSERT 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 purchasing employee filled out an Add New Material Form on the Web. The form included INPUT controls with the following NAME attributes and control contents:
NAMECONTENT
OnHand4
LeadTime14
Unit0
Ownerbrc
The SELECT MAX query gets the last Material_ID entered in the table. You add one to that and pass it on through the database connection (conn) via a statement (stmt). The form INPUT control names match with each parameter in the getParameter() methods 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 Material VALUES (47,4,14,4,'brc')

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 other 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:

FORMFile Name
New Customeraddcustomer_xxx.jsp
New Productaddproduct_xxx.jsp
New Productionaddproduction_xxx.jsp
New ProductSaleaddproductsale_xxx.jsp
New MaterialPurchaseaddmaterialpurchase_xxx.jsp
whereby the xxx would be replaced with your initials for this class.

For your HTML, upload the following HTML file names to the subdiretory with your initials (and use the respective URL in your forms):

FORMFile NameURL
New Customeraddcustomer.htmlhttp://www.cev.washington.edu/498a/xxx/addcustomer_xxx.jsp
New Feedbackaddproduct.htmlhttp://www.cev.washington.edu/498a/xxx/addproduct_xxx.jsp
New Transportaddproduction.htmlhttp://www.cev.washington.edu/498a/xxx/addproduction_xxx.jsp
New Tripaddproductsale.htmlhttp://www.cev.washington.edu/498a/xxx/addproductsale_xxx.jsp
New Locationaddmaterialpurchase.htmlhttp://www.cev.washington.edu/498a/xxx/addmaterialpurchase_xxx.jsp

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 e-mail message explaining what is wrong and how to correct it. 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 7th).

Turning in Your Report Generating Java Code

For your reports, the Java servlet will do all the work and return your HTML as a mix of database contents within your HTML tags. You will want to code the Java closely (though you can just follow my example that follows without having to understand a lot of Java) and you will upload your report generating code in the following file names (and create anchor (A) tags on your Web pages that point to the respective report generating URLs):

REPORTFile NameURL
Product Profitprodprof_xxx.jsphttp://www.cev.washington.edu/498a/xxx/prodprof_xxx.jsp
Customer Buying Historycustbuy_xxx.jsphttp://www.cev.washington.edu/498a/xxx/custbuy_xxx.jsp
Quality Index by Supplierqualsupp_xxx.jsphttp://www.cev.washington.edu/498a/xxx/qualsupp_xxx.jsp
Raw Materials Inventorymatinv_xxx.jsphttp://www.cev.washington.edu/498a/xxx/matinv_xxx.jsp
Your Specifiedother_xxx.jsphttp://www.cev.washington.edu/498a/xxx/other_xxx.jsp
whereby the xxx would be replaced with your initials for this class.

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 JSP to run. For example, for my Product Profit Report, I would create an A element like the following:

<A href=http://www.cev.washington.edu/498/prodprof_brc.jsp>

You can put all of your report hyperlinks on your 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 Server Page engine on the class Web server will handle the request as the JSP named prodprof_brc.jsp.

Your text files should look something like the following example from last year: 

<%@ page contentType="text/html;charset=ISO-8859-1" language="java" import="javax.naming.*" import="javax.sql.*" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>WOW - Entertainment Venue Planning</title>

<%
    // Obtain our environment naming context 
    Context initContext = new InitialContext(); 

    // Look up our data source 
    DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/entertain");

    // Allocate and use a connection from the pool  
    java.sql.Connection conn = ds.getConnection();

    // create and execute the query
    java.sql.Statement stmt = conn.createStatement();
    java.sql.ResultSet rs = null;

    String error="";
%> 

</head>
<BODY BGCOLOR="#B1AD9C" LEFTMARGIN="0" TOPMARGIN="0" MARGINWIDTH="0" MARGINHEIGHT="0">
<CENTER>
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0">
 <TR>
  <TD WIDTH="25"><IMG SRC="../498a/atl/images/navarrow.gif" title="arrow"></td>
  <TD WIDTH="150"><img src="../498a/atl/images/navigation.jpg" title="Navigation"></td>
  <TD WIDTH="575" ROWSPAN="7" valign="top" align="left">
	<DIV CLASS="white_border">
	<DIV CLASS="white_border_text">
	
<font size="+1">All Shows Report</font>
<p>
<center>
	<table border="0" cellpadding="3" cellspacing="0">
	 <tr>
	  <td colspan="6" align="center" bgcolor="5B0110"><font color="#eeeeee"><b>Reports for All Shows</b></font></td>
	 </tr>
	 <tr bgcolor="#eeeeee">
	  <td width="80">Show ID</td>
	  <td width="120">Entertainer</td>
	  <td width="120">Venue</td>
 	  <td width="80">Date</td>
 	  <td width="80">Time</td>
 	  <td width="80">Price</td>
	 </tr>
<%
    try {
        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");

	    int time_digit = Integer.parseInt(time);

        if((time_digit<12 && time_digit>0) || (time_digit==24)) time = time_digit + ":00am"; 
   	else if(time_digit>=12 && time_digit<=23) time = (time_digit-12) + ":00pm";

%>
	<TR>
	<TD><%= show_id %></TD>
	<TD><%= entertainer_id %></TD> 
	<TD><%= venue %></TD>
	<TD><%= date %></TD>
	<TD><%= time %></TD>
	<TD>$<%= price %></TD>
	</TR>
<%
        }
    } catch (Exception exp) {
        error = exp.toString();
    }

    rs.close();
    stmt.close();
    conn.close();
%>
    </TABLE>
    <H3><%= error %></H3>
    
    </center>
    <br>
	</DIV>
 	</DIV></TD>
 </TR>
</TABLE>
</CENTER>
</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.