1. Problem 1 Execute
   Problem 1 Code

2. Problem 2 Execute
   Problem 2 Code

3. Rebuild database tables for Problem 1 Execute
   Code for database tables rebuild

Assume, there is an ice cream manufacturing company that has four production plants, that all make the company's seven ice cream flavors. From these four facilities, delivery trucks pick up ice cream products for delivery to local stores for sale. There are two tables:
   a table named icinv that holds the ice cream product inventory for all plants;
   a table named pickup_trans that hold the trucker pickup transactions.
The table structures are shown below.

Table name: icinv	Table name: pickup_trans
plant_id int NOT NULL, [plant id: 1, 2, 3 or 4] prod_id int NOT NULL, [ice cream product id: 1 → 7] cur_inv int NOT NULL, [current inventory in gallons for the prod_id currently in plant_id location]	trans_id int IDENTITY (1,1), pick_up_year int, pick_up_month int, pick_up_day int, truck_id int, plant_id int, p1 int; [gallons of ice cream product 1 picked up] p2 int; [gallons of ice cream product 2 picked up] p3 int; [gallons of ice cream product 3 picked up] p4 int; [gallons of ice cream product 4 picked up] p5 int; [gallons of ice cream product 5 picked up] p6 int; [gallons of ice cream product 6 picked up] p7 int; [gallons of ice cream product 7 picked up] (PRIMARY KEY (trans_id))
The tables are in a database named "gl001". For a recordset, the credential string is: rs.open some_sql_string,"DSN=gl001;UID=gl001;PWD=11111" For a connection, the credential string is: cn.open "gl001","gl001","11111"

1. (50 points) Write the 2nd pass for an inventory UPDATE program (named "inv_trans.asp"). Specifically:

1. The first pass provides the truck driver a form to record product pickups at one of the 4 ice cream plants. [DO NOT WRITE THIS PASS!]
   1. <form name="f1" action="http://www.good_ice_cream.com/inv_trans.asp" method="post">
   2. Truck ID: <input type="text" name="tid" size="4">
   3. Plant: <select name="pid"><option value="1">1<option value="2">2<option value="3">3<option value="4">4</select>
   4. Year:<input type="text" name="yr" size="4"> Month <input type="text" name="mon" size="2"> Day <input type="text" name="day" size="2">
   5. Seven textboxes to record the number of gallons of each of the seven ice cream products picked up:
      Product 1 <input type="text" name="p1" size="4">
      Product 2 <input type="text" name="p2" size="4">
      ...
      Product 7 <input type="text" name="p7" size="4">
      
   6. a submit button
2. the second pass receives the data from pass1 and then under transaction control:
   1. Subtracts the amount of each of the seven ice cream products (p1 → p7) picked up from their corresponding inventory values (i.e., updates cur_inv for the specific prod_id and plant_id in the icinv table). This will require seven updates to the icinv table.
   2. DO NOT test for existence of the icinv's table row before the update.
   3. If the seven updates work correctly, report "OK" to the trucker and commit the transactions
   4. else report "Transaction Failed" to the trucker and rollback the transactions.

2. (50 points) In the program above, there would be normally be an additional step (not discussed in #1 above or required that you write). This would involve adding the pickup transaction as a row to the table named pickup_trans (see description above) which records the cumulative transaction history of all pickups. Thus, all the details of the pickup transactions are available from this table. Assume in a query program, the second pass receives three values from an HTML form with element names: pm (for pickup_month), py (for pickup_year) and pi (for plant_id). Assume the three values are available in pass2 as: Request.form("pm"); Request.Form("py"); and Request.Form("pi"). Write this query program's second pass to create a report that contains:
   (a) Plant ID, Month and Year on the first line (i.e., the data sent in pass 1)
   (b) A seven-row by two-column table, where each row contains:
         (a) the ice cream product name (for values see below)
         (b) the total gallons of this product picked up from the plant, in the month and year specified.
             (Note: there will be many pickup transactions that contribute to the 7 totals)
Assume the following arrays are available in the second pass:
      pname = Array("Chocolate","Vanilla","Strawberry","Neopolitan","Rocky Road","Peach","Mint")
      ptotal = Array(0,0,0,0,0,0,0) (use this to accumulate the pickup amounts for each ice cream product)

Write only the code for the second pass. Do not write MAIN.