Name (please print)______________________________________ SSN_________________________

Signature_______________________________________________disc-nt userid _________________

1. (50 points) Suppose Captain Webb's Explorer Fashion (the e-store) has an inventory master table named inv with:

itemnum integer;
color integer;
size integer;
price numeric(18,2);
quantity-on-hand integer;
order-trigger integer;
order-amount integer;
itemcost numeric(18,2);

itemnum, color and size form the primary key. Create an *.asp report that shows all items that require reordering. Reordering for a particular item, color and size is indicated whenever the quantity-on-hand is less than order-trigger. Report in an HTML table: item, color, size, quantity-on-hand, order-trigger, order-amount and total order cost (calculated by multiplying itemcost by order-amount). Also show the total order cost for each itemnum (i.e, all colors and sizes of the same itemnum). Report only those items were reordering is indicated.

2. (50 points) The Captain Webb Explorer Fashion client program sends a single string that contains the a user's order (this string is named uord). The uord string is composed of line items of 8 bytes each. Each line item begins with a two-byte numeric itemnum, then a two-byte numeric color code, then a two-byte numeric size code, and then a two-byte numeric order quantity (i.e., how many of this itemnum, color and size). The client software prevents line items with quantity greater than 99 units. The order string will contain at least one line item. The number of line items can be found by dividing the length of the user string uord by 8 (i.e., the length of the string is 8 bytes per line line item times the number of line items). Write the *.asp to process the uord string from a POST:

1. Provide a six column report to the user:
   1. itemnum
   2. color
   3. size
   4. quantity ordered
   5. quantity shipped
   6. quantity backordered
2. For each line item:
   1. Write each line item's detail back to the user (itemnum, color, size, quantity from uord)
   2. Check each line item against the inv table discussed in (1) above.
      If the line item's order quantity exceeds the "quantity-on-hand":
      1. report to the user in the "quantity shipped" column that you are shipping "quantity-on-hand" (i.e., everything in inventory)
      2. report to the user in the "quantity backordered" column the unfulfilled order amount
      3. reduce the "quantity-on-hand" in the inv table to zero.
      otherwise
      1. report to the user in the "quantity shipped" column that you are shipping their requested order quantity
      2. report to the user in the "quantity backordered" column a zero
      3. reduce the "quantity-on-hand" in the inv table by the order quantity
3. Show the total number of units backordered for all line items