19.3 Finalizing Orders
After entering valid credit card details
and optional delivery instructions, the next step in the ordering
process is to turn the shopping cart into an order
that's owned by a customer. This requires several
steps that include checking that sufficient inventory is available to
complete the order and deducting the wines sold from the inventory.
The order finalization requires locking of the database and is an
example of the moderately complex query processing that described in
Chapter 8. Locking prevents several undesirable
problems. For example, locking prevents the same inventory being
purchased by two users at the same time. It also prevents unlikely
problems such as the same user finalizing two separate orders at the
same time in two browser windows and confusing the application.
The script order/order-step3.php shown in Example 19-3, later in this section, performs the ordering
process. The script works as follows:
It tests that the user is logged in (using the
sessionAuthenticate( ) function discussed in
Chapter 20) and that the cart has contents.
These tests should never fail, as the Make
Purchase button is shown only when the user is viewing the
cart, is logged in, and the cart has contents. If either test fails,
an error message is registered, and the script redirects to the
cart/showcart.php script. The inventory, items, and
orders tables are locked for writing, and the
users and customer tables
are locked for reading. The inventory,
items, and orders tables
are all updated in the purchasing process, and they must be locked
because the inventory is first checked to ensure that sufficient
quantities of wine are available and then later updated. Without locking, it's possible for another user to
purchase the wine while this script is running, resulting in more
wine being sold than is in stock. This is an example of the dirty
read concurrency problem discussed in Chapter 8, and locking must be used to avoid the
problem. Each item in the cart is then processed, and the inventory is checked
to ensure that enough wine is available. If no wine is available (the
count( ) of the matching inventory rows is zero)
an error message is registered. Similarly, if less wine is available
than the user wants (the sum( ) of the
on_hand quantity of the matching rows is less than
the shopping cart qty) an error message is also
registered. On error, the script also updates the shopping cart so
that the quantity (qty) of wine in the cart
matches the quantity that is on_hand. In the case of an error, the script uses the function
showWine( ) to find the details of the wine. It
opens its own connection to the DBMS so that the
wine, wine_variety,
winery, and grape_variety
tables don't need to be locked for reading in the
order/order-step3.php script. The function is
part of winestore.inc and is discussed in Chapter 16. If the inventory checks succeed, the script proceeds to convert the
shopping cart to be a customer order. This process is
straightforward, and is encapsulated in the next three steps. The first step of converting a cart to an order is to determine the
customer's cust_id from the
$_SESSION["loginUsername"] variable using the
function getCust_id( ) that's
described in Chapter 16. The second step is to find the maximum order_id
for this customer so that the next order_id can be
assigned to the new order. The third and final step is to update the orders
and items rows by replacing the
cust_id of -1 with the
customer's cust_id (obtained in
Step 5) and the order_id with the next available
order_id (obtained in Step 6) for this customer. After the database has been updated, the cart is emptied using
unset($_SESSION["order_no"]); to
remove the session variable. Having completed the order and checked the inventory, the script
finishes the ordering process by reducing the inventory. This can
never fail, because all required tables are locked and
we've checked that sufficient quantities are
available. The process is similar to checking the cart as described in Step 3:
we iterate through each item and, for each one, we update the
inventory. The inventories are processed from oldest to newest.
Consider an example in which the user wants to purchase 24 bottles of
a wine. Suppose there are two inventories of this wine: the first has
13 bottles and was added in May 2000; the second has 25 bottles and
was added in September 2001. To satisfy the order, the oldest
inventory of 13 bottles is emptied and deleted, and the second
inventory is reduced by 11 bottles. With the process complete, the tables are unlocked. If there are no
errors, the script redirects to the
order/order-step4.php script to confirm the
order, and the cust_id and
order_id are passed as GET
method parameters. If there are errors, the user is returned to the
cart view page, cart/showcart.php.
Example 19-3. The order/order-step3.php script that finalizes the user's purchase
<?php
// This script finalizes a purchase
// It expects that a cart has contents and that the
// user is logged in
require_once "DB.php";
require_once "../includes/winestore.inc";
require_once "../includes/authenticate.inc";
set_error_handler("customHandler");
session_start( );
// Connect to a authenticated session
sessionAuthenticate(S_SHOWCART);
// Check that the cart isn't empty
if (!isset($_SESSION["order_no"]))
{
$_SESSION["message"] = "Your cart is empty!";
header("Location: " . S_SHOWCART);
exit;
}
$connection = DB::connect($dsn, true);
if (DB::isError($connection))
trigger_error($connection->getMessage( ), E_USER_ERROR);
// Several tables must be locked to finalize a purchase.
$query = "LOCK TABLES inventory WRITE,
orders WRITE,
items WRITE,
users READ,
customer READ";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// Process each wine in the cart and find out if there is sufficient
// stock available in the inventory
$query = "SELECT * FROM items
WHERE cust_id = -1
AND order_id = {$_SESSION["order_no"]}";
// initialize an empty error message
$_SESSION["message"] = "";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// Get the next wine in the cart
for ($winesInCart = 0;
$winesInCart < $result->numRows( );
$winesInCart++)
{
$cartRow[$winesInCart] = $result->fetchRow(DB_FETCHMODE_ASSOC);
// Is there enough of this wine on hand?
$query = "SELECT COUNT(on_hand), SUM(on_hand)
FROM inventory
WHERE wine_id = {$cartRow[$winesInCart]["wine_id"]}";
$stockResult = $connection->query($query);
if (DB::isError($stockResult))
trigger_error($stockResult->getMessage( ), E_USER_ERROR);
$on_hand = $stockResult->fetchRow(DB_FETCHMODE_ASSOC);
if ($on_hand["COUNT(on_hand)"] == 0)
$available = 0;
else
$available = $on_hand["SUM(on_hand)"];
// Is there more wine in the cart than is for sale?
if ($cartRow[$winesInCart]["qty"] > $available)
{
if ($available == 0)
$_SESSION["message"] = "Sorry! We just sold out of " .
showWine($cartRow[$winesInCart]["wine_id"], NULL) .
"\n<br>";
else
$_SESSION["message"] .=
"Sorry! We only have {$on_hand["SUM(on_hand)"]}
bottles left of " .
showWine($cartRow[$winesInCart]["wine_id"], NULL) .
"\n<br>";
// Update the user's quantity to match the available amount
$query = "UPDATE items
SET qty = {$available}
WHERE cust_id = -1
AND order_id = {$_SESSION["order_no"]}
AND item_id = {$cartRow[$winesInCart]["item_id"]}";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
}
} // for $winesInCart < $result->numRows( )
// We have now checked if there is enough wine available.
// If there is, we can proceed with the order. If not, we
// send the user back to the amended cart to consider whether
// to proceed with the order.
if (empty($_SESSION["message"]))
{
// Everything is ok - let's proceed then!
// First of all, find out the user's cust_id and
// the next available order_id for this customer.
$cust_id = getCust_id($_SESSION["loginUsername"], $connection);
$query = "SELECT max(order_id)
FROM orders
WHERE cust_id = {$cust_id}";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
$newOrder_no = $row["max(order_id)"] + 1;
// Now, change the cust_id and order_id of their cart!
$query = "UPDATE orders SET
cust_id = {$cust_id},
order_id = {$newOrder_no}
WHERE order_id = {$_SESSION["order_no"]}
AND cust_id = -1";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
$query = "UPDATE items SET
cust_id = {$cust_id},
order_id = {$newOrder_no}
WHERE order_id = {$_SESSION["order_no"]}
AND cust_id = -1";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// Empty the cart
unset($_SESSION["order_no"]);
// Now we have to update the inventory.
// We do this one cart item at a time.
// For all items, we know that there *is*
// sufficient inventory, since we've checked earlier
foreach($cartRow as $currentRow)
{
// Find the inventory rows for this wine, oldest first
$query = "SELECT inventory_id, on_hand
FROM inventory
WHERE wine_id = {$currentRow["wine_id"]}
ORDER BY date_added";
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// While there are still bottles to be deducted
while($currentRow["qty"] > 0)
{
// Get the next-oldest inventory
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
// Is there more wine in this inventory than the user wants?
if ($row["on_hand"] > $currentRow["qty"])
{
// Reduce the inventory by the amount the user ordered
$query = "UPDATE inventory SET
on_hand = on_hand - {$currentRow["qty"]}
WHERE wine_id = {$currentRow["wine_id"]}
AND inventory_id = {$row["inventory_id"]}";
// The user doesn't need any more of this wine
$currentRow["qty"] = 0;
}
else
{
// Remove the inventory - we sold the remainder to
// this user
$query = "DELETE FROM inventory
WHERE wine_id = {$currentRow["wine_id"]}
AND inventory_id = {$row["inventory_id"]}";
// This inventory reduces the customer's required
// amount by at least 1, but we need to process more
// inventory
$currentRow["qty"] -= $row["on_hand"];
}
// UPDATE or DELETE the inventory
$result = $connection->query($query);
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
}
}
}
else
$_SESSION["message"] .=
"\n<br>The quantities in your cart have been updated\n.";
// Last, UNLOCK the tables
$result = $connection->query("UNLOCK TABLES");
if (DB::isError($result))
trigger_error($result->getMessage( ), E_USER_ERROR);
// Redirect to the email confirmation page if everything is ok
// (supply the cust_id and order_id to the script)
// otherwise go back to the cart page and show a message
if (empty($_SESSION["message"]))
{
header("Location: " . S_ORDER_4 .
"?cust_id={$cust_id}&order_id={$newOrder_no}");
exit;
}
else
header("Location: " . S_SHOWCART);
?>
|
No comments:
Post a Comment