Steve Kirk
Microsoft Developer Network
May 1998
Summary: Describes the Point of Sale application. (10 printed pages) Knowledge of Visual Basic, basic database design, and the Duwamish Order Entry application will be helpful. Discusses:
In this article I'll describe the Point of Sale (POS) application used in Phase 1 of the Duwamish Books sample.
The Point of Sale application automates retail checkout and inventory tracking functions for a small bookstore. It also handles orders to be shipped and orders for items that are not in inventory. It borrows interfaces from the Catalog application (see "Searching the Inventory: Duwamish Catalog Application, Phase 1") and from the Order Entry application.
Because the Point of Sale application is architecturally similar to the Order Entry application that I describe in "Building an Order Entry and Tracking Application: Duwamish Books Order Entry, Phase 1," I'll focus here on what is unique to this application and make only brief reference to what is the same.
The POS application gets the employee alias from the Duwamish launcher.
The POS application creates sale and sale detail entities that represent the exchange of merchandise for money. If the sale involves shipping or a special order, the POS application generates a sale order. The application also generates inventory transaction entities for sales where the customer takes the merchandise away. A sale order represents a sale that requires shipping or other processing. An order represents the intention of a business transaction, which is connected, through the Point of Sale and Shipping and Receiving applications, to inventory transactions that represent the actual flow of merchandise items through the store.
A sale represents an exchange of merchandise for money with a customer. Table 1 lists the attributes of a sale entity.
Table 1. Sale Entity
| Attributes | Description |
| PKId | Uniquely identifies the sale. |
| CustomerID | Identifies the customer. |
| OrderId | Identifies a sale order (when special order or shipping is involved). |
| EmployeeID | Identifies the employee who entered the sale. |
| SaleDate | Indicates the date/time when the sale was inserted into the database. |
| SubTotal | The sum of all detail items in the sale. |
| Tax | The computed tax for the total sale. |
| PaymentType | Can be cash, check, or credit card. |
| CreditCardNo | For credit card sales. |
| ExpirationDate | For credit card sales. |
| NameOnCard | For credit card sales. |
| Ship True | Indicates that the sale is to be shipped. |
A sale order represents an order that requires more processing than a simple "customer buys and takes book" sale. This type of sale may be shipped or held for the customer and may require items to be ordered. Sale orders are generated by the Point of Sale application when sale quantity exceeds quantity on hand or when the ship option is selected.
A sale order uses the attributes in Table 2 of the order entity.
Table 2. Sale Order Attributes
| Attributes | Description |
| PKId | Uniquely identifies the order. |
| CustomerID | Identifies the customer. |
| EmployeeID | Identifies the employee who entered the order. |
| OrderDate | Indicates the date/time when the order was inserted into the database. |
| PickupDate | Indicates the date/time when the order was picked up by the customer. |
| Status | Indicates where the order is in its lifetime. |
| IsSales | True indicates that the order is a sales order. |
A sale order has the following states during its lifetime:
The POS application uses its own main form for a user interface. It borrows the common search form and item detail form (described in "Searching the Inventory: Duwamish Catalog Application, Phase 1") for catalog services and uses the common actors form and actor form (described in "Building an Order Entry and Tracking Application") for customer services and shipping address entry. The application also uses the common data access code in data_api.bas wherever possible and it uses data objects defined by the common class modules to represent customer, sale, sale detail, item, and other data entities.
The POS interface is designed to quickly enter a sale without requiring use of a pointing device. Toolbar, menu, and form navigation use keyboard shortcuts in addition to tab and arrow key control and the application actively controls interface focus. The interface also provides a variety of lookup methods for customers and merchandise. Table 3 describes the user interface functions provided by the application.
Table 3. POS User Interface Functions
| Control | Description |
| Customer Name text control | Accepts user input of customer alias and returns best match from database.
Displays customer alias and customer name when a customer is selected. |
| Toolbar Customer button | Selects a customer using the services of the common actors form. Provides customers Add/Edit functions. |
| ID or Title text control | Accepts user input of an item ID and returns best match from database.
or Accepts a partial title and returns best match or fills titles list if multiple items match input. Displays item ID and title when an item is selected. |
| Titles list | Displays list of titles returned from title search. |
| Toolbar Catalog | Provides full catalog search function with integrated order builder using services of common search and ItemDetail forms. |
| Quantity text control | Accepts user input of quantity for selected item. |
| Ship check box
or Sale menu Ship option |
Marks order to be shipped. Automatically selected when item quantity exceeds inventory quantity. |
| Sale Details list view | Selects sale detail.
Displays details of current sale. |
| Previous sales list view | Displays previous sale details for customer. |
| Toolbar Submit button
or File menu Submit Sale option |
Submits sale to database. |
|
Toolbar Ship To button |
Provides override of customer address for shipping. Uses services of common actor form. |
| Toolbar Remove button | Removes selected detail item from sale. |
| Toolbar Clear button
or File menu Clear Sale option |
Clears sale data. |
| Toolbar Exit button
or File menu Exit option |
Exits POS application. |
The POS application is used to build and submit data that represent bookstore sales. The following code samples show parts of the sale building process, and how the sale is applied to the database.
A sale is created by selecting a customer and adding items to the sale. Although these processes don't have to occur in this order, I'll start by selecting a customer. The customer name control accepts input of a customer alias or nickname and attempts to locate the customer using this short personal identifier. The following code segments show how a customer object is populated by the best alias match when the customer name control (txtCustomerName) has focus and the user presses ENTER:
Private Sub txtCustomerName_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case VBRUN.vbKeyReturn
' Populate the customer object with best match from database
If POSCommon.GetIntendedCustomer(m_oCustomer, txtCustomerName) Then
The following procedure builds a query, uses the common data application programming interface (API) to retrieve data from the database, and populates the customer object:
Function GetIntendedCustomer(ByRef oCustomer As cCustomer, _
ByVal txtCustomer As String) As Boolean
' Populate oCustomer with best match for txtCustomer
Dim oRecordset As ADODB.Recordset
' Build query
sQry = scSEL_LIKE_CUSTOMER_P & txtCustomer & scSEL_LIKE_CUSTOMER_S
' Retrieve recordset
If DataAccessAPI.GetRecordset(DUWAMISHDSN, sQry, oRecordset) Then
' Set customer object to a new object
Set oCustomer = Nothing
Set oCustomer = New cCustomer
' Populate customer object
With oCustomer
. PKId = oRecordset!PKId
If Not IsNull(oRecordset!NickName) Then .NickName = oRecordset!NickName
If Not IsNull(oRecordset!LastName) Then .LastName = oRecordset!LastName
The toolbar Customer button gets a customer object using the services of the common actors form, which is documented in "Building an Order Entry and Tracking Application."
The order can be filled either by entering items in the ID or Title control (txtTitleISBN) or through the toolbar Catalog button. In "Building an Order Entry and Tracking Application," I documented use of the common search form to build an order, so in this article I'll focus on the other methods of adding items. When txtTitleISDN has control and the user presses ENTER, the following code first determines if the entered text is an item ID and looks for matching titles if it determines the text to be a partial title:
If POSCommon.GetIntendedItem(oItem, txtTitleISBN) Then
Else
' Fill an items collection for partial title matches
If POSCommon.GetIntendedItems(oItems, txtTitleISBN) Then
' Hide text box and fill title list with items
txtTitleISBN.Visible = False
cmbTitle.Clear
For Each oItem In oItems
cmbTitle.AddItem oItem.Title
cmbTitle.ItemData(cmbTitle.NewIndex) = oItem.PKId
Next
Here is the procedure that fills the collection:
Public Function GetIntendedItems(ByRef oItems As cItems, _
ByVal txtItem As String) As Boolean
' Init oItems
Set oItems = Nothing
Set oItems = New cItems
' Build querysQry = scSEL_LIKE_ITEMS_P & txtItem & scSEL_LIKE_ITEMS_S
If DataAccessAPI.GetRecordset(DUWAMISHDSN, sQry, oRecordset) Then
If oRecordset.RecordCount Then
GetIntendedItems = True
Do While Not oRecordset.EOF
Set oItem = New cItem
With oItem
.PKId = oRecordset!PKId
If Not IsNull(oRecordset!ItemTypeId) Then
.ItemTypeId = oRecordset!ItemTypeId
End If
If Not IsNull(oRecordset!AuthorId) Then
.AuthorId = oRecordset!AuthorId
End If
End With
' Prepare a key value for the item
sKey = FormatPKIdAsKey(oItem.PKId)
' Add the item to the item collection
oItems.Add oItem, sKey
' Release object variable
Set oItem = Nothing
' Move to next record
oRecordset.MoveNext
Loop
End If
Set oRecordset = Nothing
End If
..Although I used the common data access API in data_api.bas wherever possible in this application, the parent/child relationship between sale and detail records and the necessity of grouping several data changes as a single transaction require special data access code. The following code segments show how the sale data objects are inserted into the database after processing by the business rule code:
'Get connection and begin transaction
Set oConn = New ADODB.Connection
oConn.Open sConnect
oConn.BeginTrans
The first piece of the transaction is to insert a record into the sales table. A keyset cursor using optimistic locking allows the record to be read after insertion to retrieve the automatically generated PKId, which will be used to relate sale details to the sale entity:
' Insert sale
Set oRset = New ADODB.Recordset
Set oRset.ActiveConnection = oConn
oRset.CursorType = adOpenKeyset
oRset.LockType = adLockOptimistic
oRset.Open scTABLE_SALES, , , , adCmdTable
' Add sale record
oRset.AddNew
' Copy data from sale object to sale record
With oSale
oRset!CustomerId = .CustomerId
If .OrderId Then
oRset!OrderId = oSale.OrderId
End If
oRset!EmployeeId = .EmployeeId
End With
' Update sale record
oRset.Update
' Record PKId of sale record for use with sale details
oSale.PKId = oRset!PKId
oRset.Close
Set oRset = Nothing
By using the same cursor and lock types for the sale details, the PKId's can be preserved for use in inventory transaction entities:
' Insert sale details
Set oRset = New ADODB.Recordset
Set oRset.ActiveConnection = oConn
oRset.CursorType = adOpenKeyset
oRset.LockType = adLockOptimistic
oRset.Open scTABLE_SALE_DETAILS, , , , adCmdTable
For Each oSaleDet In oSaleDetails
oRset.AddNew
oRset!SaleId = oSale.PKId
With oSaleDet
oRset!ItemId = .ItemId
oRset!UnitPrice =.UnitPrice
oRset!Quantity = .Quantity
End With
ORset.Update
' Record PKId of dale detail record in sale detail object
oSaleDet.PKId = oRset!PKId
Next
oRset.Close
Set oRset = Nothing
The following code adjusts inventory in cases where the customer is taking the merchandise. The inventory transaction records can be inserted as a batch because no post update reads are required. Updates to the InStock quantity require a read update sequence so each record is locked, read, and updated individually:
If Not oSale.Ship Then
Set oRset = New ADODB.Recordset
Set oRset.ActiveConnection = oConn
oRset.CursorType = adOpenKeyset
oRset.LockType = adLockBatchOptimistic
oRset.Open scTABLE_INVENTORY_TRACK, , , , adCmdTable
' Add an inventory tracking entity for each inventory transaction
For Each oSaleDet In oSaleDetails
oRset.AddNew
oRset!ItemId = oSaleDet.ItemId
oRset!EmployeeId = oSale.EmployeeId
oRset!TransactionId = oSaleDet.PKId
oRset!IsSale = True
oRset!Quantity = -1 * oSaleDet.Quantity
oRset!TransactionDate = tDate
oRset.Update
Next
oRset.UpdateBatch
oRset.Close
Set oRset = Nothing
' Adjust the InStock field for each detail
For Each oSaleDet In oSaleDetails
sQry = scSELECT_ITEM_INSTOCK & oSaleDet.ItemId
Set oRset = New ADODB.Recordset
Set oRset.ActiveConnection = oConn
oRset.CursorType = adOpenKeyset
oRset.LockType = adLockOptimistic
oRset.Open sQry, , , , adCmdText
If Not oRset.EOF Then
oRset!InStock = oRset!InStock - oSaleDet.Quantity
oRset.Update
End If
oRset.Close
Set oRset = Nothing
Next
End IfThe following section commits all of the pieces of the transaction as one, and cleans up:
oConn.CommitTrans
oConn.Close
Set oConn = Nothing
InsertSale = True
Exit Function
An error at any point causes the whole transaction to fail and the calling procedure to be notified:
ErrorHandler:
oConn.RollbackTrans
oConn.Close
Set oConn = Nothing
InsertSale = False
Err.Raise lErrNo, OBJNAME, sErrDesc
The POS application is the most transaction-intensive of the Phase 1 applications. As I detailed above, each sale transaction in which the customer immediately receives the merchandise causes records to be inserted into three tables and one table to be updated. Each sale where the merchandise is shipped later causes records to be inserted into four tables with inventory updates handled later in the Shipping application. The probability for update contention is not great when inventory updates are being made by only a single cash register and by shipping/receiving, but it increases when more cash registers are added. The POS application recovers from a collision by prompting the user to press Submit to try again. But as the probability for collisions increases, the way they are avoided and the way they are resolved become critical design issues. The remaining phases of the Duwamish Books sample will present solutions to this problem as we detail a migration path from desktop to enterprise scale.