MALWART STORES 24 byte QR CODE 012345678901234567890123

Malwart Stores uses a qr-code to identify inventory delivered to its many retail stores. Each box of product(s) contains a unique 24 digit qr-code. This qr-code is used as the key to a box_data table where the packing, shipping and delivery information about a box of product(s) is maintained. There is also a box-contents table that holds the detailed information on each product AND number of units of the product contained in a specific box (i.e., product_code and quantity). Thus, for each row in the box_data table (i.e., a box) there are one or more rows in the box_contents table that details what is in the box. Further, there is a store_inventory table for each store that holds the quantity_on_hand of each product in its inventory. The data structures for these tables are shown below.

box_data box_contents store_inventory box_qr_code char(24) NOT NULL, pack_location char(6), pack_date_time varchar(24) load_location char(6), load_date_time varchar(24) store_id char(6), delivery_date_time varchar(24), delivery_completed char(1), truck_code char(12), route_code char(12), PRIMARY KEY (box_qr_code) product_code char(30) NOT NULL, box_qr_code char(24) NOT NULL, quantity int NO PRIMARY KEY store_id char(6) NOT NULL, product_code char(30) NOT NULL, quantity_on_hand int, PRIMARY KEY (store_id)

1. (60 points) Assume that when the drivers arrive at a Malwart store, they use a "cellphone qr-code reader app" to record each box as it is unloaded. For each box, the "qr-code reader app" wirelessly transmits (AJAX) the following string to an asp program over the internet:

1. Using the box_qr_code (the value of "qrc" from the querystring), read the store_id from the box_data table

2. Using the box_qr_code, read ALL rows in the box_contents table that contain this box_qr_code (i.e, get the product_code and quantity values for each product in the box)

3. Add the quantity amounts in (2. above) to the corresponding quantity_on_hand fields of the store_inventory table (use the store_id from (1. above) and the product_code from (2. above) to determine which row in the store_inventory table to update

4. If the posting in (3. above) works correctly: (a) record the current date and time (from "dt" in the querystring) in the delivery_date_time field of the box_data table; (b) set the value of the delivery_completed field to "Y" in box_data table; (c) send "OK" to the user. If the posting fails: (a) set the value of the delivery_completed field to "N" in box_data table; (b) Send "FAILED" to the user.

2. (40 points) When the truck driver is ready to leave the Malwart store, he will use a cell phone app that reads a qr-code printed on the top page of his BOL (BOL = 'bill of lading' -- a paper document that records the details of mechandise delivered to the store). This cellphone app sends (AJAX) the following string over the internet:

• Using the truck_code value, store_id value, and route_code value from the querystring, create a small HTML table that contains:
  • Row 1, values of: truck_code; store_id; and route_code, each in a column
  • Row 2, values of:
    • in Col 1 the string, "Boxes Loaded = number of boxes (rows) in the box_data table using the values shown in Row 1 (i.e., truck_code, store_id, route_code).
    • in Col 2 the string, "Boxes Delivered = number of boxes in [Row 2 , Col 1] where delivery_completed="Y" in the box_data table.
    • in Col 3 the string, "Number of Missing Boxes = [Row 2 , Col 1] minus [Row 2 , Col 2]
• Then if [Row 2 , Col 3] above shows more than zero boxes are missing then following the table:
  • Display the string: "Here are the missing boxes:"
  • Show each missing box's box_qr_code on a line by itself