Tracking Payments in the Island Hopper News Sample

Julie MacAller
Microsoft Corporation

January 1999

Introduction

The Island Hopper News application is an automated classified ads system created by a fictitious company, Island Hopper Publishing, as a test project to evaluate converting the current paper-based weekly newspaper into an online newspaper. The design team consists of the Island Hopper News editor, the Classified Ads and Accounting department heads, and two developers.

This paper describes how the code for the tracking payments function in the Island Hopper News application fits together and how it works.

Tracking Payments Overview

The Tracking Payments function provides a way for Island Hopper Publishing employees to enter customer payments. Employees need to retrieve a customer record from the database before entering payment information. Employees also need to know the invoice ID before entering the payment; the invoice ID ensures the payment is associated with the correct invoice. Customers can pay by check or credit card.

Island Hopper Publishing employees track payments by starting the Island Hopper Publishing Microsoft® Windows-based client and selecting the Payments function.

Tracking Payments Components

Tracking payments uses the bus_CustomerC and bus_PaymentC business components, the db_CustomerC, db_CustomerPasswordC, and db_PaymentC data access components, and the util_TakeANumber and util_TakeANumberUpdate utility components.

These components expose the following methods:

Component Methods
bus_CustomerC Add, GetByEmail, IbusCustomerChange_Add, IbusCustomerChange_Delete, IbusCustomerChange_Update, IbusCustomerLookup_GetByEmail, IbusCustomerLookup_GetByID, IbusCustomerLookup_ListByLastName, Validate
bus_PaymentC AddPayment, GetByCustID
db_CustomerC IcustomerChange_Add, IcustomerChange_Delete, IcustomerChange_Update, ICustomerLookup_GetByEmail, ICustomerLookup_GetByID, ICustomerLookup_ListByLastName, UpdateBalance
db_CustomerPasswordC IcustomerPasswordChange_Add, IcustomerPasswordChange_Delete, IcustomerPasswordChange_Update, ICustomerPasswordLookup_GetByID
db_PaymentC AddPayment, GetByCustID
util_TakeANumber GetANumber
util_TakeANumberUpdate Update

Tracking Payments User Interface

Employees start the Island Hopper Publishing Windows-based client by clicking the Start menu, pointing at Programs, pointing at Island Hopper, and selecting Island Hopper C. The application's main screen is displayed. Employees then click Payments to view the Payments form (frmInputPaymentC), where they can retrieve a customer record from the database, enter payment information for that customer, and add the new payment to the database. When the new payment is successfully added, a confirmation message displays for the employee; the message includes the unique ID of the payment. The form then clears, so the employee can enter another payment.

Tracking Payments Process

From a developer's point of view, there are three main parts to the tracking payments process:

Each of these parts is discussed in this white paper.

Retrieving the Customer Record

After an employee accesses the Payments screen, he or she needs to retrieve a customer record from the database. Employees can retrieve records using the customer ID or e-mail address; they can also search for a customer by last name and select the appropriate customer record from the returned list. Each retrieval method is described later in this document.

Retrieving by ID or e-mail address

All customer maintenance operations require the retrieval of a customer record from the database. Even the Add operation attempts to retrieve a customer record containing the customer information in the form to make sure it doesn't already exist. Although retrieving by customer ID and retrieving by e-mail address are nearly identical operations, retrieving by last name is a little different, so it is described separately.

The retrieval operation starts when the customer clicks the appropriate button on the Payments form (cmdRetrieveID, cmdRetrieveEmail, or cmdSearchCustomer).

When a customer requests the retrieval of a customer record by ID or e-mail address, the following process occurs in the code:

  1. If retrieving by customer ID, make sure the Customer ID field contains a number to use in the search.

  2. Create an instance of the business component, bus_CustomerC.

  3. Call the appropriate retrieval method (GetByID or GetByEmail) on the IbusCustomerLookup interface to bus_CustomerC and pass in the customer ID or e-mail address, as well as two Microsoft® ActiveX Data Objects (ADO) Recordset variables (rsCustomer and rsPassword) to hold the retrieved customer and password information.

  4. Call the Microsoft® Transaction Server (MTS) CreateInstance method on the MTS ObjectContext object to create the data access component, db_CustomerC.

  5. Call the appropriate retrieval method (GetByID or GetByEmail) on the ICustomerLookup interface to db_CustomerC, passing in the customer ID or e-mail address.

  6. Create a new ADO Recordset object, and call the global RunSp function to run a stored procedure, passing the name of the stored procedure and any other parameters. For more information about RunSp and the use of stored procedures, see the section on "Stored Procedures" in the Island Hopper Code Organization white paper.

  7. When the stored procedure finishes running and passes back a recordset with the requested information, call the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of the transaction.

  8. Return the recordset to the appropriate retrieval method on the IbusCustomerLookup interface to the bus_CustomerC object.

  9. Call the MTS CreateInstance method on the MTS ObjectContext object to create the db_CustomerPasswordC component.

  10. Call the GetByID method on the ICustomerPasswordLookup interface to the db_CustomerPasswordC component, passing in the customer ID.

  11. Create a SQL SELECT command to retrieve a record from the CustomerPasswords table where the customer ID matches the supplied customer ID.

  12. Use the Open method on the ADO Recordset object to execute the SQL command.

  13. When the database command completes and returns a recordset, call the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of the transaction.

  14. Return the recordset back to the appropriate retrieval method of IbusCustomerLookup.

  15. Call the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of the transaction.

  16. Return the recordsets back to frmInputPaymentC.cmdRetrieveEmail_Click or frmInputPaymentC.cmdRetrieveID_Click, which checks the rsCustomer recordset to see whether it is empty.

    If the rsCustomer recordset is empty, the retrieval method displays a message that no customer record was found and calls the cmdCancel_Click procedure to clear the form fields and prepare the form for the next operation.

    If the rsCustomer recordset contains a record, the retrieval method fills the Customer ID, First Name, and Last Name fields with the information from the rsCustomer recordset, enables the Save button, locks the fields containing the customer information, and disables the Retrieve and Search buttons.

Retrieving by last name

When an employee requests the retrieval of a customer record by last name, the following process occurs:

  1. Extract any text from the Last Name field on frmInputPaymentC, and put it in the Last Name field on frmCustomerSearchC.

  2. Show frmCustomerSearchC.

At this point, the employee types in the last name, if it isn't already present, and clicks the Search button, which calls the cmdSearch_Click procedure.

  1. The cmdSearch_Click procedure calls the ListByLastName method on the IbusCustomerLookup interface to the bus_CustomerC object, passing in the last name.

  2. The bus_CustomerC object calls the MTS CreateInstance method on the ObjectContext object to create an instance of db_CustomerC.

  3. The bus_CustomerC object calls the ListByLastName method on the ICustomerLookup interface to the db_CustomerC object, passing in the last name.

  4. The ListByLastName method of the db_CustomerC object calls the RunSp function to run the Customer_ListByLastName stored procedure.

  5. When the stored procedure finishes running and returns a Recordset with the requested information, the ListByLastName method of the db_CustomerC object calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of the transaction, and then it returns the recordset to the ListByLastName method of the bus_CustomerC object.

  6. The ListByLastName method of the bus_CustomerC object calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of the transaction and then returns the recordset to cmdSearch_Click on frmCustomerSearchC.

  7. The cmdSearch_Click procedure checks the rsCustomer Recordset to see whether it is empty.

    If the rsCustomer Recordset is empty, the cmdSearch_Click procedure displays a message that no customer was found.

    If the rsCustomer Recordset is not empty, the cmdSearch_Click procedure loops through the Recordset to fill the lstCustomers listbox control with name and e-mail address for each retrieved customer and saves the customer ID numbers in an array for easy retrieval when the employee selects a customer record to work with.

Employees can now click the customer they want and then click Retrieve or double-click on the customer they want (which has the same effect). Clicking Retrieve calls cmdRetrieve_Click, which calls lstCustomers_DblClick; double-clicking a customer also calls lstCustomers_DblClick.

  1. Call the AcceptCustomerID method on frmInputPaymentC, which takes the customer ID from the array created by frmCustomerSearch, converts it to data type String, and places it into the Customer ID field

  2. Call cmdRetrieveID_Click, which retrieves the rest of the customer information and the password.

Now the selected customer record is retrieved from the database using the get by customer ID retrieval method.

Validating Payment Information

After employees retrieve a customer record, they can fill in information about the payment they are entering, including the payment date and amount, the type of payment (check or credit card), the check or credit card number, the card expiration date, and the ID of the invoice associated with the payment. Then employees click the Save button (cmdSave) to add the payment to the database. Before adding the payment, however, the payment information is validated by calling the ValidateFields procedure.

All validation code takes place on the client. No business or data access components are required. Validating the payment information consists of the following steps:

  1. Check the payment method

  2. Check the payment date field

  3. Check the card expiration date field

  4. Check the invoice ID field

Checking the payment method

This step determines whether the payment is from a check or a credit card. The validation code checks to see whether the card expiration date field is empty and the payment method is "Check." If the payment method is "Check," it's OK for the card expiration field to be blank. If the payment method is not a check, and the card expiration date field is empty, an error message displays to prompt the employee to enter a date.

Checking the payment date field

This step determines whether the value in the payment date field is a valid date. It uses the IsDate function to evaluate the value. If the date is not valid, an error message displays to prompt the employee to enter a valid date.

Checking the card expiration date field

This step determines two things about the card expiration date field: whether it is a valid date and whether the card has expired. First, the code checks the validity of the date using the IsDate function. If the date is valid, the code checks whether the date is less than the current date, indicating a card that has expired.

If the date is not valid, or if the card has expired, an error message displays for the employee.

Checking the invoice ID field

This step uses the IsNumeric function to check whether the invoice ID is a number. No further checking is done.

Updating the Database

After validating the payment information, the payment is added to the database. If the payment is added successfully, a confirmation message displays for the employee. The AddPayment method of the bus_PaymentC component does the work for this stage. Processing for this stage consists of the following steps:

  1. Get a unique ID for the new payment

  2. Update the database with the new payment

  3. Update the customer's balance

Get a unique ID for the payment

This step gets a unique ID for the new payment. This ID is passed as a parameter later in the AddPayment method of the db_PaymentC component, when the payment is actually inserted into the database. One of the Island Hopper News business rules is that each payment must have a unique ID. Obtaining that unique ID is part of business processing, not part of data access. That's why the payment ID is obtained now and passed to the db_PaymentC object, which does the work of inserting the payment into the database. Getting the ID includes the following steps.

  1. The AddPayment method of the bus_PaymentC component creates an instance of the utility component, TakeANumber, and calls the GetANumber method on the object.

    The TakeANumber component is marked as "requires a transaction," so it enlists in the current transaction.

  2. The GetANumber method of the TakeANumber object gets the next available unique ID from the database.

    The GetANumber method queries the database for the next available number for a payment. For more information on how this procedure works—and why it's used at all—see the Assigning Blocks of Unique IDs in the Island Hopper News Sample white paper.

  3. The GetANumber method calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of—but not commit—the transaction, and then it returns the new payment ID number to the AddPayment method of the bus_PaymentC object.

Update the database

This step updates the database with the new payment. Adding the new payment to the database includes the following steps.

  1. The AddPayment method of the bus_PaymentC object creates an instance of the data access component, db_PaymentC, and calls the AddPayment method on the db_PaymentC object.

    The db_PaymentC component is marked as "requires a transaction," so it enlists in the current transaction.

  2. The AddPayment method of the db_PaymentC object creates and opens an ADO Connection object and then builds the SQL INSERT statement for adding the payment to the database.

  3. Next, the AddPayment method of the db_PaymentC object executes the SQL statement to the database using the Connection.Execute method.

  4. When the command finishes executing, the AddPayment method calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of—but not commit—the transaction. Then AddPayment passes control to the AddPayment method of the bus_PaymentC object.

Update the customer balance

This step updates the customer balance showing that the payment was made and credited properly. Processing consists of the following steps:

  1. The AddPayment method of the bus_PaymentC object creates an instance of the data access component, db_CustomerC, and calls the UpdateBalance method on the db_CustomerC object.

    The db_CustomerC component is marked as "requires a transaction," so it enlists in the current transaction.

  2. The UpdateBalance method of the db_CustomerC object builds a SQL UPDATE statement to update the customer's record in the Customers table with the new balance.

  3. Next, the UpdateBalance method creates a new ADO Connection object, opens the connection, and executes the SQL statement to the database using the Connection.Execute method.

  4. When the command finishes executing, the UpdateBalance method calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to signal approval of—but not commit—the transaction. Then UpdateBalance passes control to the AddPayment method of the bus_PaymentC object.

  5. The AddPayment method of the bus_PaymentC object also calls the MTS SetComplete method on the MTS ObjectContext object to release any held resources and to commit the transaction. Then AddPayment returns the payment ID to the cmdSave_Click procedure of frmInputPaymentC.frm.

  6. The cmdSave_Click procedure displays a confirmation message with the new payment ID number and prepares the Payments form for a new payment.

For More Information