Hack 78 Manage Your Inventory with IPN

Indicate whether the products on your web site are in stock using up-to-date inventory data maintained by some add-ons to your IPN processing script .

Merchants who sell tangible goods typically don't have an unlimited supply of any item. When you sell out of something, you might no longer want it to appear on your web site: you can't sell what you don't have. Managing inventory counts for each order and updating your web pages accordingly can be a time-consuming and tedious process, but it can be mostly automated with PayPal's IPN system.

This hack consists of a database table, tblProducts , that holds our inventory count, an IPN processing page that manages the count, a web page that displays an out-of-stock message when appropriate, and an email notification to alert you when the inventory count for a particular item is running low (or has been depleted).

7.18.1 Updating the Inventory Count

Create a database table, tblProducts , that contains fields for the product's unique item number, item_number , and the initial inventory count, count_inventory , as shown in Table 7-2.

Table 7-2. A database table to manage your store inventory

item_number	item_name	count_inventory
6001	Vitamins	6
6002	Sulfuric Acid	5612
6004	Calculator
7001	Imitation Gruel	77

When a payment is made, PayPal will post the transaction details to your IPN processing page. Included in these details is the unique item number, for which you'll need to query your database for the in-stock inventory. Finally, decrement the value by the number of products purchased:

 Dim item_number Dim count_inventory_new item_number = Request.Form("item_number") quantity = Request.Form("quantity") 'Retrieve the current inventory count from the database 'Connect to database and create recordset connStore = "DRIVER={Microsoft Access Driver (*.mdb)};                 DBQ="C:/InetPub/wwwroot/database/dbPayPal.mdb") set rsInventoryCount = Server.CreateObject("ADODB.Recordset") rsInventoryCount.ActiveConnection = connStore rsInventoryCount.Source = "SELECT count_inventory FROM tblProducts                  WHERE item_number = " & item_number rsInventoryCount.Open( ) count_inventory_new = rsInventoryCount("count_inventory") - quantity 'Store the reduced inventory count in the database set cInsPayment = Server.CreateObject("ADODB.Command") cInsPayment.ActiveConnection = "DRIVER={Microsoft Access Driver                  (*.mdb)};DBQ="C:/InetPub/wwwroot/database/dbPayPal.mdb") cInsPayment.CommandText = "UPDATE tblProducts SET count_inventory =                  " & count_inventory_new & " WHERE item_number = " & item_number & "" cInsPayment.CommandType = 1 cInsPayment.CommandTimeout = 0 cInsPayment.Prepared = true cInsPayment.Execute( ) 

This code only handles the inventory count; see [Hack #65] for the complete code necessary to implement IPN.

7.18.2 Creating the Selling Page

An inventory count will not do much good if the web store allows people to purchase items that are no longer available. You can remove the Buy Now button for an out-of-stock item with a simple conditional statement on a dynamic page.

Start by placing the current inventory count into the rsInventoryCount variable, using a SQL statement something like this:

 SELECT count_inventory FROM tblProducts WHERE item_number = 'Wid-001' 

See the "Database Coding and Platform Choices" section of the Preface for the additional information needed to put this SQL statement to work with this and the other hacks in this book.

Next, compare that value to zero, and display the button only if the item is available:

 <% If rsInventoryCount("count_inventory") > 0 Then  'We have it in stock, display PayPal purchase button %> <form action="https://www.paypal.com/cgi-bin/webscr" method="post"> <input type="hidden" name="cmd" value="_xclick"> <input type="hidden" name="business" value="[e-mail]"> <input type="hidden" name="item_name" value="<%=rsProduct("item_name")%>"> <input type="hidden" name="item_number" value="<%=rsProduct("item_number")%>"> <input type="hidden" name="amount" value="<%=rsProduct("item_price")%>"> <input type="hidden" name="no_note" value="1"> <input type="hidden" name="currency_code" value="USD"> <input type="image" src="https://www.paypal.com/en_US/i/btn/x-click-but23.gif"                  border="0" name="submit"> </form> <% Else  'We do not have any left, show OoS message %> %> We're sorry, this item is out of stock. <% End If %> 

You might not want to use a value of zero as your threshold, especially if it is a high-volume item. Real-world values might be different than the electronic inventory count, due to defective merchandise from your supplier or offline transactions. Try setting the number to, say, three instead. Or, display a message to your customers that inventories are low and they should contact you directly to assure quick fulfillment.

7.18.3 Alerting Yourself if Inventory Is Low

Finally, set up a script to email yourself or let your staff know when inventory is low or has become depleted. Insert this code into your IPN processing script:

 If count_inventory_new <   5   Then  'Low count, send email  Dim InvCDO  Set InvCDO = Server.CreateObject("CDONTS.NewMail")  InvCDO.From = "   [e-mail]   "  InvCDO.To ="   [e-mail]   "  InvCDO.Subject = "Order More Inventory"  InvCDO.Body = "We need to order more of item # " & item_number  InvCDO.Send( )  Set InvCDO = Nothing End If 

If, immediately after a purchase, you have fewer than five of the item left in your inventory, you'll get an email that contains a warning, along with the product's item_number .

There will be some lag time between the instant your customer hits the Buy Now or Checkout button and the time that that transaction is complete. Since this means it might be possible for two customers to be in the process of purchasing a single remaining item, you'll want to keep the threshold sufficiently high (five, in this case) sufficiently high so that this doesn't happen.