NAME______________________________________________________________________ Last 4 PSID____________

See the working answer page here (source code)

Assume, there is a prestige paper company that sells personalized stationery on its website. There is a table named porder that holds the order data entered by the website users AND a table named pstock that has the paper inventory information. The data structures for these tables are shown below:

porder

pstock

oid int IDENTITY (1,1), cust_first_name varchar(25), cust_last_name varchar(25), cust_address1 varchar(35), cust_address2 varchar(35), cust_city varchar(20), cust_state char(2), cust_zip char(5), cust_phone varchar(10),       [below are 5 fields that make up one order line] size_code_1 int, [value and option text are: 0="Pick a Size",1=4"x6", 2=5"x7", 3=8"x10",4=8.5"x11" monogram_text_1 varchar(25), monogram_font_1 int, [1="Arial", 2="Times New Roman", 3="Comic Sans MS"] color_1 varchar (25) (see discussion below) order_quantity_1 int,       [...six more groups of five fields like the one above       ...that end in the digits: 2,3,4,5,6 and 7] (PRIMARY KEY (oid))

size_code int, color varchar(25), quantity_on_hand int, reorder_trigger int, reorder_quantity int on_order_switch char(1), order_date char(8) PRIMARY KEY (size_code,color))

Write the SECOND pass ONLY for the user_order.asp program that allows a customer to order stationery. Specifically:

1. The first pass (DO NOT WRITE THIS) provides an HTML form with:
   
   1. eight textboxes for the eight customer data fields: cust_first_name; cust_last_name; cust_address1; cust_address2; cust_city; cust_state; cust_zip; cust_phone; cust_state; cust_zip; and cust_phone.
   2. seven order lines each with:
      1. a select box for size_code with 5 options: (option values and option text are shown above)
      2. a textbox for the monogram_text;
      3. a select box for the monogram_font:(option values and option text are shown above)
      4. a color select boxes with 148 options. The first select option is:
               <option value="0">Please choose a color below
         The next 147 options are the 147 CSS color names. Assume that the following array was used to generate the color options
         c[ 1]="AliceBlue";
         c[ 2]="AntiqueWhite";
         c[ 3]="Aqua";
         ...
         c[146]="Azure";
         c[147]="Beige";
         
      5. a textbox for the order_quantity
   3. a hidden textbox named "token" with value=2
   4. a submit button to send the data to user_order.asp.
   
   DO NOT WRITE PASS 1 OR MAIN. Pass 1 will be shown to you in class on the screen.
   
2. In pass two (WRITE THIS ONLY -- NOT PASS1 & NOT MAIN):
   1. Retrieve the size_code and color for each order line and the perform a check that order_quantity submitted by the user for each order line DOES NOT exceed the quantity_on_hand for the size_code/color in pstock. DO NOT check if the order line has no choices (for size or color). For each order line where the quantity is NOT available, DO NOT attempt to restore their order data, just return a line to the user with the message:
         The product you ordered with color=some color name and size=some size is NOT available in this quantity.
   2. If all quantities are available (for order lines with size and color choices), insert the data into the porder table. If the insert is successful, return the message:
         "Order Placed OK"
   otherwise return the message:
         "INSERT Failed"
   

NOTE 2: Assume that if any size_code_1 thru size_code_7 OR color_1 thru color_7 is zero, the user has NOT made an entry on that line and if the INSERT is performed a zero should be used for numeric quantities and an empty string used for any text values.