Chapter 12. Ordering and Shipping at the Winestore

Example 12-1. order.3 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

   include 'include.inc';

   set_error_handler("errorHandler");

   // Re-establish the existing session
   session_start( );    

   // Check if a cart exists - this should never fail
   // unless the script is run directly
   if (!session_is_registered("order_no"))
   {
      session_register("message");
      $message = 
         "There are no items in your shopping cart!";

      // Redirect the browser back to the calling page
      header("Location: $HTTP_REFERER");
      exit;
   }      

   // Check if the user is logged in - this should 
   // never fail unless the script is run directly
   if (!session_is_registered("loginUsername"))
   {
      session_register("message");
      $message = 
         "You must login to finalize your purchase.";

      // Redirect the browser back to the calling page
      header("Location: $HTTP_REFERER");
      exit;
   }      
   
   // Open a connection to the DBMS
   if (!($connection = @ mysql_pconnect($hostName, 
                                       $username, 
                                       $password)))
      showerror( );                                     

   if (!mysql_select_db($databaseName, $connection))
      showerror( );

   // Several tables must be locked to finalize a purchase.
   // We avoid locking four other tables by
   // using another DBMS connection to produce the wine
   // information
   $query = "LOCK TABLES inventory WRITE, 
                         orders WRITE, 
                         items WRITE, 
                         users READ, 
                         customer READ";

   // LOCK the tables
   if (!(@ mysql_query ($query, $connection)))
      showerror( );   

   // 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 = $order_no";

   // Initialise an empty error message
   $message = "";

   if (!($result = @ mysql_query ($query, $connection)))
      showerror( );
      
   // Get the next wine in the cart         
   for ($winesInCart = 0; 
        $winesInCart < mysql_num_rows($result); 
        $winesInCart++)
   {
      $cartRow[$winesInCart] = @ mysql_fetch_array($result);

      // 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"];

      if (!($stockResult = @ mysql_query ($query,
                                          $connection)))
         showerror( );
                                       
      $on_hand = @ mysql_fetch_array($stockResult);

      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 (!session_is_registered("message"))
            session_register("message");

         if ($available == 0)
            $message .= "Sorry! We just sold out of " . 
               showWine($cartRow[$winesInCart]["wine_id"],
                        NULL) .
                        "\n<br>";
         else 
            $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 = $order_no
                   AND item_id = " .
                   $cartRow[$winesInCart]["item_id"];

         if (!(@ mysql_query ($query, $connection)))
            showerror( );
      }                                                           
   } // for $winesInCart < mysql_num_rows($result);

   // 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 think about purchasing the lesser
   // amount.
   
   if (empty($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.
      $custID = getCustomerID($loginUsername, NULL);

      $query = "SELECT max(order_id) 
                FROM orders 
                WHERE cust_id = $custID";

      if (($result = @ mysql_query ($query, $connection)))
         $row = mysql_fetch_array($result);         
      else
         showerror( );

      $newOrder_no = $row["max(order_id)"] + 1;
      
      // Now, change the cust_id and order_id of their cart!
      $query = "UPDATE orders 
                SET cust_id = $custID , " . 
                "order_id = " . $newOrder_no .
               " WHERE order_id = $order_no";

      if (!(@ mysql_query ($query, $connection)))
         showerror( );

      $query = "UPDATE items 
                SET cust_id = $custID , " . 
               "order_id = " . $newOrder_no .
               " WHERE order_id = $order_no";

      if (!(@ mysql_query ($query, $connection)))
         showerror( );

      // Officially empty the cart
      session_unregister("order_no");

      // Now we have to do 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 inventories for this wine, oldest first
         $query = "SELECT inventory_id, on_hand
                   FROM inventory
                   WHERE wine_id = " .
                   $currentRow["wine_id"] .
                   " ORDER BY date_added";

         if (!($result = @ mysql_query ($query,
                                        $connection)))
            showerror( );

         // While there are still bottles to be deducted
         while($currentRow["qty"] > 0)
         {
            // Get the next-oldest inventory
            $row = @ mysql_fetch_array($result);

            // 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
            if (!(@ mysql_query ($query, $connection)))
               showerror( );
         }
      }
   }
   else
      $message .=  "\n<br>The quantities in your cart " .
                   "have been updated\n.";

   // Last, UNLOCK the tables
   $query = "UNLOCK TABLES";
   if (!(@ mysql_query ($query, $connection)))
     showerror( );

   // Redirect to the email confirmation page if 
   // everything is ok
   // (supply the custID and orderID to the script)
   // otherwise go back to the cart page and show a message
   if (empty($message))
   {
      header("Location: example.shipping.1.php?" .
             "custID=$custID&orderID=$newOrder_no");
      exit;
   }
   else
      header("Location: example.cart.2.php");
?>