Northern Lights Project

Electrical Co-op Customer Service Interface

The software from this project provides a Web-based customer service interface for an electrical co-op's account management and billing system. Co-op personnel extract data from the billing system and transfer it to the Web server on a monthly basis. The Web server imports the data into the Web site's database. Customers log in to the customer service interface on the co-op Web site, where they can review their account history and use a credit card to pay their bill on-line.

• Customer: Northern Lights, Inc.
• Web Site Design: Sandpoint.com
• Software Programming and Interface Design: Logical Expressions, Inc.

The project consists of two elements: an import system and a browser-based customer service interface.

Import System

The import system reads data from a comma separated value (CSV) file and loads it into the Web site's database. The process works as follows:

1. Once per month, NLI personnel use their billing system (called PCS) to generate a CSV extract file. The file contains customer information and the current month's billing information.
2. NLI transfers the CSV file via FTP to the Web server (their Web site is hosted by a third party).
3. The import program runs automatically on the Web server on the 28th of the month using the NT schedule service. It imports the waiting CSV file into the database that drives the Web site and logs statistics about the information that was imported.
4. NLI personnel can check the results of the import by examining the log file.

Features of the import system include:

• Automatic deletion of all billing information that is older than the history window of 13 months.
• Automatic database compress following completion of the import to minimize Access database bloat.
• Interactive mode with interface for setting file locations and running the import manually.
• Batch mode for running the import through the NT scheduler (this mode suppresses form display and gets file locations from an INI file).
• Log file with record count and database compression statistics.

The image below shows the import program's interface when run in interactive mode. The import program uses ADO to update the Web server's Access database, and DAO to perform the compress.

Use the import utility's interactive mode to set file locations and to run the import manually.

Customer Service Interface

The customer service interface lets NLI customers view their account information and make payments on-line.

Features of the customer service interface include:

• Administrative login for managing user accounts.
• Billing and Payment history pages with option to download as CSV file. These files open in Microsoft Excel when that program is present on the user's system.
• Secure (SSL) login and on-line payment.
• Current charges review page with payment link.
• Automatic payment processing, e-mail confirmation, and reporting of results through Authorize.net API.

The images below demonstrate elements of the project's Web interface. To see these pages, you must log on with a valid account through the site's customer service login page.

Review your electrical usage history with the Billing History page.

Review your current month's charges.

Pay your bill on-line with a credit card.

Technologies Employed

• Microsoft IIS 4.0
• Microsoft Access 97
• Microsoft Visual Basic
• Active Server Pages and VBScript
• Authorize.net COM Interface
• Active Data Objects