Imagine that you're a Web applications programmer for The Great Bank of America. Your task is to create a home-banking application so that people can use their browsers to view the balance or history of their checking and savings accounts. Your boss gives you a beautiful flowchart created by the mainframe programming group some years ago. The flowchart shows the screens in the application and the steps a customer must go through to view his or her account balance (see Figure 7.1).
Let's write the application. If you haven't worked with databases and ADO, you may want to skip ahead and read Chapter 10, "Retrieving and Storing Data in Databases." You need a sign-on screen—you can reuse parts of the SecuredSite project in Chapter 5, "Securing an IIS Application," for that. You'll also need a database. I'll provide the database tables and relational structure. You can use any database to which you can connect with ADO. If you don't want to build the database, you can download the Microsoft Access database (AccountInfo.mdb) used in this project from the Sybex Web site.
Note
To download code, navigate to http://www.sybex.com. Click Catalog and search for this book's title. Click the Downloads button and accept the licensing agreement. Accepting the agreement grants you access to the downloads page for the book.
Create a new IIS application. Name the project AccountInfo and save it in a directory called AccountInfo. IIS applications use your project directory as the default Web site; therefore, you should always create a new directory when you save the project. That way, you can reuse filenames without worrying about overwriting your earlier work.
Figure 7.2 shows the tables, field names, and table relationships in the MS Access version of the AccountInfo project database.
Figure 7.3 shows the field information for each table in the SQL Server version of the database. You can download the AccountInfo.sql file from the Sybex Web site to obtain the DDL commands that will create the database on SQL Server.
The Accounts table contains account information. The Customers table holds information about the people who own or use the accounts. Each customer and each account has a unique ID. A single customer may have multiple accounts, and each account may have one or more customers who are authorized to use the account. The CustomerAccounts table maps customers to accounts. Any activity in any account is called a transaction.
For this project, you can pretend that some other application logs every activity to the Transactions table. Because this is a report application, you're going to read only the information that already exists in the tables; therefore, you're going to have to populate the tables with data before the application will work. The WebClass_Start event in the Reports WebClass (see Listing 7.1) populates the Accounts, Customers, and CustomerAccounts tables if necessary. The Start event is a good place to put the code because it executes only the first time the WebClassManager creates a WebClass.
As written, the code creates 50 checking accounts and 50 customers who each own one or two accounts. If you'd prefer to type in your own sample data or change the data provided, feel free—just remember to comment out, delete, or modify the WebClass_Start code.
Listing 7.1: Code to Populate the AccountInfo Database Tables (AccountInfoWebClass.dsr)
Private Sub WebClass_Start()
Dim anAccNum As String
Dim i As Integer
Dim SQL As String
Dim cm As Command
Dim initBalance As Long
Dim rAccounts As Recordset
Dim rCustomers As Recordset
Dim firstname As String
Dim lastname As String
Dim accountType As String
Dim R As Recordset
Set cm = New Command
Set cm.ActiveConnection = conn
Dim aPassword As String
Dim aSignon As String
Dim randValue As Integer
Dim accountOwnerCount As Integer
' create some accounts
SQL = "SELECT count(*) as AccountCount FROM Accounts"
Set R = conn.Execute(SQL, , adCmdText)
If R("AccountCount").value < 50 Then
For i = 1 To 50
initBalance = Int((5000 * Rnd) + 1)
anAccNum = getNewAccountNumber()
randValue = getRandomInt(100, 1)
If randValue <= 50 Then
accountType = "Checking"
Else
accountType = "Savings"
End If
SQL = "INSERT INTO Accounts "
SQL = SQL & "(AccountNumber, AccountType, "
SQL = SQL & "Balance, CreatedOn, "
SQL = SQL & "LastActivity, StartingBalance) "
SQL = SQL & "VALUES ('" & anAccNum & "', '" _
& accountType
SQL = SQL & "', " & CStr(initBalance) & _
", '" & _
Format$(Now(), "general date") & "', '" & _
Format$(Now(), "general date") & "', " & _
CStr(initBalance) & ")"
cm.CommandText = SQL
cm.Execute , , adCmdText
Next
End If
' create customers if needed
SQL = "SELECT count(*) AS CustomerCount FROM Customers"
SQL = SQL & "Customers ON Customers.CustomerID = "
SQL = SQL & "CustomerAccounts.CustomerID)"
conn.Execute SQL
Response.Redirect "Signon.asp"
End Sub
Note
When you run this project, you can sign on by typing test and a number from 1 to 25. Use the same text as your password, for example, (Signon=Test25, Password=Test25 or Signon=test11, Password=test11). Sign-ons and passwords are not case sensitive in this application. If the sign-on you select is not recognized, sign on with a lower number.
Most of the code to populate the database is fairly straightforward, but you will notice that it contains several subroutine calls, some of which may be useful to you in future programs. When I first wrote the code to populate the database, I created 50 customers and 50 accounts, one per customer. But that didn't seem realistic, so I added the code to loop through the Accounts table and the Customers table, randomly assigning two accounts to some users. I also needed to randomly create either savings or checking accounts. That need led to a function called getRandomInt. You provide the maximum and minimum values for the random integer, and the function returns an integer between those bounds. I wrote an almost identical function to get random Long values.
I had more problems with initializing a set of transactions. Transactions are deposits, withdrawals, and inquiries by a customer for a specific account. Transactions occur sequentially, by date. I didn't want the transactions to occur on regular intervals, so I needed a way to generate random dates but still have the overall sequence of dates move from the past toward the present. To add the transactions, I created a separate subroutine called createTransactionRecords. The Start event code calls this subroutine once for each account.
Private Sub createTransactionRecords(AccountNumber _
As String, CustomerID As Long)
Dim accountBalance
Dim R As Recordset
Dim SQL As String
Dim tranType As String
Dim tranDate As Date
Dim tranAmount As Currency
Dim curBalance As Currency
Dim i As Integer
Dim tranCount As Integer
Dim maxDays As Integer
Dim dayOffset As Integer
Dim lastDayOffset As Integer
Dim tmpBalance As Currency
maxDays = 365 * 20
' get the current balance
curBalance = getCurrentBalance(AccountNumber)
' insert a random number of transaction records
tranCount = getRandomInt(10, 3)
For i = 1 To tranCount
tranType = getRandomTransactionType()
If i = 1 Then
dayOffset = getRandomInt(maxDays, 1000)
Else
dayOffset = getRandomInt(lastDayOffset - 1, 1)
End If
lastDayOffset = dayOffset
tranDate = getDateOffsetBy(dayOffset)
If tranType = "Inquiry" Then
tranAmount = 0
Else
tranAmount = Cur(getRandomLong( _
CLng(curBalance) * 100, 350) \ 100)
End If
tmpBalance = curBalance
If tranType = "WithDrawal" Then
curBalance = curBalance - tranAmount
ElseIf tranType = "Deposit" Then
curBalance = curBalance + tranAmount
End If
' write the transaction
SQL = "INSERT INTO Transactions "
SQL = SQL & "(AccountNumber, CustomerID, "
SQL = SQL & "TransactionType, TransactionDate, "
SQL = SQL & "Amount, Balance) "
SQL = SQL & "VALUES ('" & AccountNumber & "'," _
& CustomerID & ", '" & tranType & "', '" _
& tranDate & "', " & tranAmount & ", " _
& curBalance & ")"
conn.Execute SQL
Next
End Sub
The createTransactionRecords subroutine creates between 3 and 10 transactions for each account.
tranCount = getRandomInt(10, 3)
I needed a function not only to get random dates but to force them to be sequential in time. I did this by using two variables, dayOffset and lastDayOffset, to hold an integer value representing the offset, in days, from the current date. To ensure that the first value would not be too close to the current date, I forced the first date to be at least 1,000 days earlier than the current date. Subsequent dates had to be at least one day later than the first date.
If i = 1 Then
dayOffset = getRandomInt(maxDays, 1000)
Else
dayOffset = getRandomInt(lastDayOffset - 1, 1)
End If
lastDayOffset = dayOffset
tranDate = getDateOffsetBy(dayOffset)
Finally, to get the date itself, the getDateOffsetBy function calculates a date offset from today by the value of the semi-random dateOffset variable. The getDateOffsetBy function returns the formatted date.
Private Function getDateOffsetBy(days As Integer) As Date
getDateOffsetBy = CDate(Format$(DateAdd("d", _
CDbl(days) * -1, Now), "short date") & " " & _
getRandomTime())
End Function
The getDateOffsetBy function calls a function called getRandomTime to get the time portion of the random date/time value:
Private Function getRandomTime() As String
Dim hour As Integer
Dim minute As Integer
Dim second As Integer
Dim AmPm As String
hour = getRandomInt(24, 1)
minute = getRandomInt(59, 0)
second = getRandomInt(59, 0)
If hour > 12 Then
AmPm = "PM"
hour = hour - 12
ElseIf hour < 12 Then
AmPm = "AM"
Else
AmPm = "PM"
End If
getRandomTime = Format$(hour, "00") & ":" & _
Format$(minute, "00") & ":" & Format$ _
(second, "00") & " " & AmPm
End Function
Finally, the createTransactionRecords function executes a SQL insert command string to insert the record into the Transactions table. The process inserts transaction records in a loop, then returns so the WebClass_Start code can continue matching accounts to customers.
Take another quick look at Listing 7.1. You want the routine to add records to the database only if the database is empty; therefore, each part of the routine checks the count of records in a table before it begins adding records. The account insertion loop counts account records, the customer insertion loop counts customer records, etc. If you step through the code with the debugger, you'll see that the insertion code executes only if the records are missing.
If you want to start over or change the code to populate the database, you'll need to delete the records first, so that the check condition will fail. In Access, I've created a table relationship with cascading deletes so that records in the CustomerAccounts and Transactions tables are deleted whenever you delete an associated "parent" record in either the Accounts or Customers table.