Viewing Account Activity 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 account activity function in the Island Hopper News application fits together and how it works.

Account Activity Overview

The Account Activity function provides a way for Island Hopper Publishing employees to view invoices and payments for a customer. Employees need to retrieve a customer record from the database before viewing account activity.

Island Hopper Publishing employees view account activity by starting the Island Hopper Publishing Microsoft® Windows-based client and selecting the Account Activity function.

Account Activity Components

Viewing account activity uses the bus_CustomerC and bus_InvoiceC business components and the db_CustomerC, db_CustomerPasswordC, db_InvoiceC and db_PaymentC data access 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_InvoiceC AddHeader, AddDetail, GetByID, 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_InvoiceC AddDetail, AddHeader, GetByCustID, GetByID, GetDetailByID
db_PaymentC AddPayment, GetByCustID

Account Activity 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 Account Activity to view the Account Activity form (frmInvoiceC), where they can retrieve a customer record from the database, view invoice details, and see a list of payments made by the customer.

The following figure shows the Account Activity form.

If employees double-click a particular invoice, they see the Invoice Details form, shown in the following figure.

Account Activity Process

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

Each of these parts is discussed in this white paper.

Retrieving the Customer Record

After an employee accesses the Account Activity 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. If an employee knows the invoice number, he or she can type it into the Invoice ID field and press Enter; however, the form will display the Customer Search form, and the employee will need to retrieve the customer by last name. 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 Account Activity 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 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 frmInvoiceC.cmdRetrieveEmail_Click or frmInvoiceC.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, Email, First Name, and Last Name fields with the information from the rsCustomer recordset.

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 frmInvoiceC, 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 it 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 frmInvoiceC, 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.

Populating the List of Invoices

After retrieving the customer from the database, the cmdRetrieveEmail or cmdRetrieveID procedures call the FillFields procedure to fill the customer information text fields with information about the customer. After filling the customer information text fields, the FillFields procedure calls the FillInvoiceList procedure to populate the list of invoices and payments (a grid control). Processing for this stage consists of the following steps:

  1. Retrieve the invoices for this customer from the database

  2. Retrieve the payments for this customer from the database

  3. Populate the grid control that contains the list of invoices and payments

These steps are described later in this document.

Retrieving invoices

In this step, the FillInvoiceList procedure uses the bus_InvoiceC component to retrieve a list of the invoices for this customer from the database. The following steps summarize the process.

  1. The FillInvoiceList procedure in frmInvoiceC.frm creates an instance of the business component, bus_InvoiceC, and calls the GetByCustID method of the bus_InvoiceC object.

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

  2. The GetByCustID method of the bus_InvoiceC object creates an instance of the data access component, db_InvoiceC, and calls the GetByCustID method of the db_InvoiceC object.

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

  3. The GetByCustID method of the db_InvoiceC object builds a SQL SELECT statement to extract the invoices for this customer from the Invoices table in the database and executes the command to the database using the Recordset Open method.

  4. When the command finishes executing, GetByCustID 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 containing the invoices to the GetByCustID method of the bus_InvoiceC object.

  5. The GetByCustID method of the bus_InvoiceC object also 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 containing the invoices to the FillInvoiceList procedure of frmInvoiceC.frm.

Retrieving payments

In this step, the FillInvoiceList procedure uses the db_PaymentC component to retrieve a list of the payments made by this customer from the database. The following steps summarize the process.

  1. The FillInvoiceList procedure in frmInvoiceC.frm creates an instance of the data access component, db_PaymentC, and calls the GetByCustID method of the db_PaymentC object.

  2. The GetByCustID method of the db_PaymentC object creates and opens an ADO Connection object and then builds the SQL SELECT statement to extract the payments for this customer from the database.

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

  4. When the command finishes executing, it returns the recordset to the FillInvoiceList procedure in frmInvoiceC.frm.

Populate the grid control

In this step, the grid control that contains the list of invoices and payments is populated. As long as there is at least one invoice or payment for the customer, the grid control will be populated. The FillInvoiceList procedure contains code to handle adding payment information if there are no more invoices or vice versa.

The FillInvoiceList procedure in frmInvoiceC.frm first checks both returned recordsets—the one containing invoices and the one containing payments—to make sure that at least one invoice or payment is found and then starts a loop to begin populating the grid.

The loop moves through the recordsets containing the invoices and payments until it reaches the end of each recordset. It writes each invoice and payment to a separate row in the grid. Invoices and payments are written by date, with invoices first.

Two procedures, AddPaymentRow and AddInvoiceRow, do the work of adding a row and populating it with the appropriate information.

The FillInvoiceList procedure in frmInvoiceC.frm includes code that handles adding payments when there are no more invoices and vice versa. The following steps summarize this process.

  1. Check to see if the recordset containing invoices is empty. If so, call AddPaymentRow to write a payment row and increment the counter.

  2. If the recordset containing invoices is not empty, check to see if the recordset containing payments is empty. If so, call AddInvoiceRow to write an invoice row and increment the counter.

  3. If both recordsets contain records, the FillInvoiceList procedure calls the Cdate function to compare the invoice date and the payment date of the current record in both recordsets. If the invoice date is greater than or equal to the payment date, call AddInvoiceRow to write an invoice row and increment the counter.

  4. If the payment date is greater than the invoice date, call AddPaymentRow to write a payment row and increment the counter.

When the loop reaches the end of both recordsets, the FillInvoiceList procedure sets some display properties on the grid control, gives it focus, cleans up the recordsets, and exits.

Showing Details About a Selected Invoice

After the grid control has been populated, employees can double-click any invoice to view details about it. Available details include the invoice number and date, as well as the IDs and descriptions of the ads on that invoice.

Double-clicking a particular invoice triggers the flxInvoiceList_dblClick procedure in frmInvoiceC.frm. This procedure does the following:

  1. Ensures the employee clicked on an invoice (details are not available for payments).

  2. Moves through the recordset containing the invoices until it finds the record that corresponds to the invoice the employee clicked.

  3. Shows the Invoice Details form, frmInvoiceDetailC.

Showing the Invoice Details form triggers the Form_Load and Form_Activate procedures in frmInvoiceDetailsC. The Form_Load procedure creates an instance of the data access component, db_InvoiceC, which is used to get invoice details from the database.

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

The Form_Activate procedure calls the RetrieveDetails procedure (also in frmInvoiceDetailsC) to get the details for the invoice using the db_InvoiceC object.

The RetrieveDetails procedure contains the following steps:

  1. Clear the grid on the Invoice Details form.

  2. Get the invoice information from the database.

  3. Get the invoice details from the database.

  4. Populate the grid control that contains the invoice details.

These steps are described later in this document.

Clearing the grid

This step consists of resetting several properties on the grid control that contains the invoice details.

Getting the invoice information

In this step, the RetrieveDetails procedure in frmInvoiceDetailC calls the GetByID method of the db_InvoiceC object to retrieve the information about the selected invoice from the database. The GetByID method of the db_InvoiceC object does the following:

  1. Builds a SQL SELECT statement to extract all the information about the selected invoice from the Invoices table in the database.

  2. Creates a new ADO Recordset object and executes the SQL statement to the database using the Recordset Open method.

  3. When the command finishes executing, 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 containing the invoice information to the RetrieveDetails procedure in frmInvoiceDetailC.

Getting the invoice details

In this step, the RetrieveDetails procedure in frmInvoiceDetailC calls the GetDetailByID method of the db_InvoiceC object to retrieve details—that is, the IDs and descriptions of the ads—for the selected invoice. The GetDetailByID method does the following:

  1. Builds a SQL SELECT statement to extract all the details for the invoice from the InvoiceDetails table in the database.

  2. Creates a new ADO Recordset object and executes the SQL statement to the database using the Recordset Open method.

  3. When the command finishes executing, 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 containing the invoice details information to the RetrieveDetails procedure in frmInvoiceDetailC.

Populate the grid control

In this step, the RetrieveDetails procedure in frmInvoiceDetailC loops through the recordsets containing the invoice and invoice details information and writes each ad ID and description to a separate row in the grid control. When the code reaches the end of the recordsets, it exits the loop, disables the Next or Previous buttons if necessary, and exits the procedure.

Note   If the invoice for which details are being displayed is the first or last invoice in the list on frmInvoiceC, the RetrieveDetails procedure in frmInvoiceDetailC disables the Previous or Next buttons, respectively, on the Invoice Details form. Employees can use these buttons to move through the list of invoices, displaying details on each one.

For More Information