Page #55 (Chapter 7 - Controlling Program Flow)

Chapter 7 - Controlling Program Flow

Visual Basic Developers Guide to ASP and IIS
A. Russell Jones
  Copyright 1999 SYBEX Inc.

Creating the AccountInfo Project
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"
    Set R = conn.Execute(SQL, , adCmdText)
    If R("CustomerCount").value = 0 Then
        Set rAccounts = New Recordset
        SQL = "SELECT AccountNumber FROM Accounts"
        rAccounts.Open SQL, conn, adOpenStatic, _
            adLockReadOnly, adCmdText
        i = 1
        While Not rAccounts.EOF
            aSignon = "Test" & CStr(i)
            aPassword = LCase(aSignon)
            SQL = "INSERT INTO Customers "
            SQL = SQL & "(LastName, FirstName, "
            SQL = SQL & "Signon, Password) "
            SQL = SQL & "VALUES ('" & CStr(i) & "',"
            SQL = SQL & " 'Customer', '" & _
                aSignon & "', '" & aPassword & "')"
            conn.Execute SQL
            i = i + 1
            rAccounts.MoveNext
        Wend
        rAccounts.Close
    End If
   
    ' create some account owners, if needed
    Set R = conn.Execute("SELECT count(*) AS " & _
        "CustomerAccountsCount FROM CustomerAccounts", _
        , adCmdText)
    If R("CustomerAccountsCount").value < 50 Then
        ' get the account numbers
        Set rAccounts = New Recordset
        SQL = "SELECT AccountNumber "
        SQL = SQL & "FROM Accounts"
        rAccounts.Open, conn, adOpenStatic, _
            adLockReadOnly, adCmdText
        ' and a list of customers
        Set rCustomers = New Recordset
        SQL = "SELECT CustomerID FROM Customers"
        rCustomers.Open SQL, conn, adOpenStatic, _
            adLockReadOnly, adCmdText
        While (Not rCustomers.EOF And Not rAccounts.EOF)
            ' match a customer to an account
            SQL = "INSERT INTO CustomerAccounts "
            SQL = SQL & "(CustomerID, AccountNumber) "
            SQL =  SQL & "VALUES & "(" & _
                CStr(rCustomers!CustomerID.value) & _
                ", '" & rAccounts!AccountNumber.value _
                & "')"
            conn.Execute SQL
            ' create some transaction records
            ' for each account.
            Call createTransactionRecords _
                (rAccounts!AccountNumber.value, _
                rCustomers!CustomerID.value)
            ' some customers should have
            ' more than one account
            randValue = getRandomInt(100, 1)
            If randValue <= 50 Then
                rCustomers.MoveNext
                accountOwnerCount = 0
            Else
                ' don't move the customers recordset
                accountOwnerCount = accountOwnerCount + 1
            End If
            ' nobody gets more than two accounts
            If accountOwnerCount = 2 Then
                accountOwnerCount = 0
                rCustomers.MoveNext
            End If
            rAccounts.MoveNext
        Wend
        rCustomers.Close
        rAccounts.Close
    End If
    R.Close
    Set R = Nothing
    Set rAccounts = Nothing
    Set rCustomers = Nothing
    ' delete any customers who don't have accounts
    SQL = "DELETE FROM Customers WHERE "
    SQL = SQL & "Customers.CustomerID NOT IN "
    SQL = SQL & "(SELECT CustomerAccounts.CustomerID "
    SQL = SQL & "FROM CustomerAccounts INNER JOIN "
    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.
Private Function getRandomInt _
    (upperbound As Integer, _
    lowerbound As Integer) As Integer
    getRandomInt = Int((upperbound - lowerbound + 1) _
        * Rnd + lowerbound)
End Function
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.



Visual Basic Developer[ap]s Guide to ASP and IIS
Visual Basic Developer[ap]s Guide to ASP and IIS
ISBN: 782125573
EAN: N/A
Year: 2005
Pages: 98

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net