I know you're expecting me to localize all of the forms in the Library Project into Greek, and it is a tempting idea. But in the interests of brevity (and my sanity), I'll leave that as an exercise for the reader. (Muffled laughter.)

What we will do in this chapter's project code is to enable the remaining patron-specific tracking and management features. Those features include the management of fines for naughty patrons who don't return their library books on time. We'll use the generic currency formatting features discussed in this chapter to make the application as globally accessible as possible.

Project Access

Load the "Chapter 18 (Before) Code" project, either through the New Project templates, or by accessing the project directly from the installation directory. To see the code in its final form, load "Chapter 18 (After) Code" instead.

Tracking Patron Payments

Let's create a class that exposes the important features of each set of payments applied to a specific checked-in item. Of course, they'll all be stored in the Library database. But keeping a summary of payments temporarily cached in memory simplifies some processing.

Add a new class item to the Library Project, giving it the name PaymentItem.vb. Define it using the following code.

Insert Snippet

Insert Chapter 18, Snippet Item 1.

Public Class PaymentItem
   ' ----- Used to track and print payment tickets.
   Public ItemTitle As String
   Public PatronCopyID As Integer
   Public FeesPaid As Decimal
   Public BalanceDue As Decimal
End Class

Each instance of this class identifies the collected fines and payments for a specific library item (ItemTitle) and for the patron who turned in the item late (PatronCopyID).

Calculating Patron Fines

We also need to know the total fines owed by a patron for all items, even when we're not showing the details. Add the CalculatePatronFines function to the General.vb module.

Insert Snippet

Insert Chapter 18, Snippet Item 2.

Public Function CalculatePatronFines(_
      ByVal patronID As Integer) As Decimal
   ' ----- Given a patron ID, calculate the fines due.
   Dim sqlText As String

   On Error GoTo ErrorHandler

   ' ----- Retrieve the fine records for the patron.
   sqlText = "SELECT SUM(Fine - Paid) FROM PatronCopy " &_
      "WHERE Patron = " & patronID
   Return DBGetDecimal(ExecuteSQLReturn(sqlText))

   GeneralError("CalculatePatronFines", Err.GetException())
   Return 0@
End Function

It's pretty basic code, actually, because the database does all of the work of adding up the values. I checked the database documentation and confirmed that Fine and Paid are required fields, and will never be NULL. This keeps the SQL code terse.

Patron Record Access

Before reviewing a patron's record, the user must identify the patron. This is done through a Patron Record Access form, sort of a login form for patrons. Each patron is assigned a password, which must be supplied before the patron can access his or her own record. Administrators can access a patron's record without providing the password.

I've already added the PatronAccess.vb form to your project; it appears in Figure.

The Patron Access form, PatronAccess.vb

This form's code is a lot like that found in the ChangeUser.vb form, a form that provides administrative access to the program, and that we added back in Chapter 11, "Security." The Patron Access form behaves a little differently for administrators and regular patrons.

  • Regular patrons must provide either their barcode, or supply their name (full last name, optional wildcards on the first name) and their password. If they use a partial name instead of a barcode, and a search of that name results in multiple matches, they will have to provide a more correct entry of their name. (If two patrons have the same name, they will have to depend on barcodes; but this program is for a small library, so name conflicts should be rare.)

  • Administrators enter the patron's name or barcode, but no password is needed. If there are multiple name matches, the form presents all matching names in a list, and the administrator can select the correct entry from the list. This gives an administrator full access to all patron records. It's obviously important for an administrator to log out when finished using a workstation that is available to patrons.

The PatronAccess form's SelectPatron method provides the interface to the form for both administrators and ordinary patrons. The function returns the ID of the selected patron, or 1 if the user didn't successfully access a patron record.

Patron Password Modification

Although administrators can change the password of each patron through the Patron.vb form, we don't want to give ordinary patrons access to that form and all of its raw, unadulterated power. But we still want the patrons to be able to change their own passwords, because it's the nice and secure thing to do. I've added the PatronPassword.vb form to your project to fulfill this purpose (see Figure).

The Patron Password form, PatronPassword.vb

The form is basically a dramatically reduced subset of the full Patron.vb form. Because it needs to deal with active patrons only, it doesn't have a lot of the Patron.vb code that differentiates between new and existing patron records. The focus of the Patron Password form is the update statement that sets the patron's password, in the SaveFormData method.

sqlText = "UPDATE Patron SET [Password] = " & _
   DBText(EncryptPassword("patron", _
   Trim(RecordPassword.Text))) & _
   " WHERE ID = " & ActiveID

The word "Password" is a reserved keyword in SQL Server, so we need to "escape" it with square brackets when referring to the field in SQL statements.

Collecting Patron Payments

In a perfect world, patrons would never let their books and other library items reach the overdue state. Of course, in a perfect world, libraries would let you keep books you like indefinitely. And give me a break with those incessant overdue notices. What's up with that?

But for those small libraries that insist on charging fines for overdue items, the Library Project includes features for assigning and tracking fines. In a later chapter, we'll add the code that automatically calculates the fines for overdue items. Right now, we'll implement the form that lets you document patron payments and other financial adjustments to items in the patron's record.

I've added the PatronPayment.vb form to the collection of project files, but it's not yet integrated into the project. Select the PatronPayment.vb file in the Solution Explorer, and then change its Build Action property (in the Properties panel) from "None" to "Compile." Figure shows the controls on this form.

The Patron Payment form, PatronPayment.vb

Fines that are automatically added to an overdue item appear in the PatronCopy.Fine database field. While that value is displayed on the Patron Payment form, it's not the primary focus of that form. Instead, the form exists to allow a librarian to enter charges and payments for a previously checked-out item, storing these updates in the PatronPayment database table. This table tracks four types of financial events for each item checked out by a patron.

  1. Additional fines imposed by a librarian or administrator. For example, a librarian may add the value of an item as a fine if it turns out that the patron has lost the item. Additional fine entries use the letter "F" in the PatronPayment.EntryType database field.

  2. Payments made by the patron for an overdue item. "P" is the entry type.

  3. A dismissal of some or all of the pending fines for an overdue item, indicated by a "D" entry type.

  4. If entry type is "R," the record indicates a refund paid to the patron by the library.

Each PatronPayment table record includes a transaction date, the amount of the transaction, optional comments, and the identity of the administrative user recording the entry. To make the code a little clearer, the letter codes in the database table are converted into enumeration values from the EventEntryType enumeration.

Private Enum EventEntryType
End Enum

The OverdueFines entry allows the PatronCopy.Fines value to be part of the displayed financial history on the form.

The librarian uses the fields in the "New Payment Event" section of the PatronPayment form to add charge and payment records. All previously added records appear in the EventHistory list, in the "Payment Event History" section of the form.

The calling form (added later in this chapter) needs to pass in the PatronCopy.ID value to identify the proper record. But the plan is to have payments added on this form flow back to the parent form. The two forms will share a set of PaymentItem objects, using the class we added a few sections earlier in this chapter. We'll store it in a local member variable as a generic set.

Private PaymentsOnly As Generic.List(Of PaymentItem)

The entry point into the form will be a public method named ManagePayments. Add that code now to the PatronPayment class.

Insert Snippet

Insert Chapter 18, Snippet Item 3.

Public Sub ManagePayments(ByVal patronCopyID As Integer, _
      ByVal sessionPayments As Generic.List(Of PaymentItem))
   ' ----- Manage the payments for an item.
   ActivePatronCopyID = patronCopyID
   PaymentsOnly = sessionPayments
End Sub

This method records the patron-copy ID number and the collection of payments for that checked-out item. Processing then moves on to the form's Load event handler. It's in this routine that we will add our localized financial management code. In the PatronPayment_Load routine, scan down about one-third of the way through the method to the code that loads in the "summary details" from the database. Just after the line:

RecordItem.Text = CStr(dbInfo!Title)

add in the statements that will globally format currency values for the Fines, Payments, and Balance summary labels that appear near the top of the form.

Insert Snippet

Insert Chapter 18, Snippet Item 4.

originalFine = CDec(dbInfo!Fine)
RecordFine.Text = Format(originalFine, "Currency")
RecordPayments.Text = Format(CDec(dbInfo!Paid), "Currency")
balanceDue = originalFine - CDec(dbInfo!Paid)
RecordBalance.Text = Format(balanceDue, "Currency")

The rest of the Load event handler's code loads existing records from the PatronPayment table, plus the original overdue fine, if any, from the PatronCopy.Fine database field.

Later, when the user clicks the Add button to add a new financial event to the patron-and-item-copy entry, the SaveEventData routineequivalent to the SaveFormData method in most of the other forms we've developed so farsaves the updated information in the database. This routine needs to save the new charge or payment in the PatronPayment table, plus update the charge and payment summary in the PatronCopy record. Add the code that writes out these records, just after the calculations for the fineAmount and paidAmount variables in the SaveEventData method.

Insert Snippet

Insert Chapter 18, Snippet Item 5.

' ----- Add the entry to the database.
sqlText = "INSERT INTO PatronPayment (PatronCopy, " & _
   "EntryDate, EntryType, Amount, Comment, UserID) " & _
   "OUTPUT INSERTED.ID VALUES (" & ActivePatronCopyID & _
   ", GETDATE(), " & DBText(entryCode) & ", " & _
   RecordAmount.Text & ", " & _
   DBText(Trim(RecordComment.Text)) & _
   ", " & LoggedInUserID & ")"
newID = CInt(ExecuteSQLReturn(sqlText))

sqlText = "UPDATE PatronCopy SET Fine = " & fineAmount & _
   ", Paid = " & paidAmount & " WHERE ID = " & _

I've wrapped up both database statements in a transaction to help ensure the integrity of the data. Once the database is up to date, it's time to update the screen. The on-screen list of charges and payments needs this new record. That list uses the local EventHistoryItem class, a variation of the application-wide ListItemData class that we usually use in ListBox controls. EventHistoryItem has fields that are specific to displaying financial information in the EventHistory list box. Add the code that builds an EventHistoryItem record and add it to the EventHistory list, immediately after the database update code we just added.

Insert Snippet

Insert Chapter 18, Snippet Item 6.

' ----- Add an item to the entry list.
historyItem = New EventHistoryItem
historyItem.PaymentID = newID
historyItem.EntryDate = Today
historyItem.PaymentAmount = CDec(RecordAmount.Text)
historyItem.Comments = Trim(RecordComment.Text)
historyItem.EntryType = entryType

This code block is followed by similar code that updates the PaymentsOnly list, the Generic.List(Of PaymentItem) that was passed in from the calling form. The code either updates the existing payment summary record, or adds a new record to the generic list.

' ----- Add a new payment.
scanPayment = New PaymentItem
scanPayment.PatronCopyID = ActivePatronCopyID
scanPayment.ItemTitle = RecordItem.Text
scanPayment.FeesPaid = paidAmount
scanPayment.BalanceDue = fineAmount - paidAmount

Before leaving this function, we need to refresh the three financial summary values near the top of the form, the ones we set when the form first loaded. Add this code just after the update to the PaymentOnly list.

Insert Snippet

Insert Chapter 18, Snippet Item 7.

' ----- Update the on-screen values.
RecordFine.Text = Format(fineAmount, "Currency")
RecordPayments.Text = Format(paidAmount, "Currency")
RecordBalance.Text = Format(fineAmount - paidAmount, _

The EventHistory list is a variable-line-height owner draw control, similar to one we designed in Chapter 17, "GDI+." Its MeasureItem event handler sets the height of each list item (comments appear on a second line when available), while its DrawItem event handler does the actual drawing of each data column and the comments.

Managing All Fines and Payments

The Patron Payment form lets a librarian enter individual fines and payments, but the program still needs a form to manage all fines and payments for a single patron, a form that calls up the Patron Payment form when needed. The new PatronRecord.vb form fulfills this need. I've added this form to your project, although you need to enable it. Select it in the Solution Explorer, and change its Build Action property (in the Properties panel) from "None" to "Compile." Figure shows the controls on this form.

The Patron Record form, PatronRecord.vb

This form is available to both administrators and patrons, although some of the fields are hidden from patron view.

The Password button leads to the Change Patron Password form we added earlier in this chapter. The Edit button, only available to administrators, provides access to the full Patron.vb form. The main section of the Patron Record form displays a list of all items the patron currently has checked out. It includes a Renew button that lets a patron extend the due date for a checked-out item. We'll add the code for that feature in a later chapter.

The form also displays a summary of all pending fines and payments. Figure shows the Fines tab and its fields.

The Fines panel on the Patron Record form

The Print Balance Ticket button generates a printed receipt of all fines and payments for the patron. We'll add its code in a later chapter.

Most of the code in this form exists to manage fines and payments. To add a charge or payment, the librarian selects an item from the Fines list, and then clicks the Fines and Payments button. This brings up the just-added Patron Payment form.

The two main lists on the Patron Record form will each forgo the standard ListItemData class, and use a more property-rich class to support the display needs of each list. We'll add this PatronDetailItem as a separate public class because (as we'll see in a later chapter) it will be used elsewhere in the Library Project. Create a new class named PatronDetailItem.vb, and use the following code for its content.

Insert Snippet

Insert Chapter 18, Snippet Item 8.

Private Class PatronDetailItem
   Public DetailID As Integer
   Public TitleText As String
   Public DueDate As Date
   Public FineAmount As Decimal
   Public PaidAmount As Decimal
   Public BalanceDue As Decimal
End Class

Now back to the PatronRecord form. As you can tell from looking at the form, the Fines list displays several columns of currency values. Let's add the code that correctly formats the currency according to the regional monetary settings. First, locate the RefreshPaymentFines method. This routine adds up all fines and payments, and displays the result through the BalanceDue Label control.

Near the top of this routine is a comment that states, "Clear the current list." Add the following code just after this comment.

Insert Snippet

Insert Chapter 18, Snippet Item 9.

totalBalance = 0@
BalanceDue.Text = Format(0@, "Currency")
Me.Cursor = Windows.Forms.Cursors.WaitCursor

We could have just set the BalanceDue field to "$0.00," but this would not be properly globalized. Using the Format function with "Currency" as the formatting rule still results in "$0.00" when used in America, but properly adjusts for other cultures as well.

The RefreshPaymentFines method does a whole bunch of calculations, and ends up with the remaining patron balance in the totalBalance local variable. Locate the comment that reads, "Show the total balance," and add the following code just after it.

Insert Snippet

Insert Chapter 18, Snippet Item 10.

BalanceDue.Text = Format(totalBalance, "Currency")

The Fines list, an owner draw ListBox implementation, also displays currency values. This is another list that forgoes the standard ListItemData class, using the local PatronDetailItem class instead for its item management. Locate the Fines_DrawItem event handler, and the "Extract the details from the list item" comment within that handler. Add the following code just after the comment.

Insert Snippet

Insert Chapter 18, Snippet Item 11.

itemDetail = CType(Fines.Items(e.Index), PatronDetailItem)
titleText = itemDetail.TitleText
fineText = Format(itemDetail.FineAmount, "Currency")
paidText = Format(itemDetail.PaidAmount, "Currency")
balanceText = Format(itemDetail.BalanceDue, "Currency")
If (itemDetail.BalanceDue = 0@) Then useNotice = useBrush

This block properly formats the each currency value. By default, all due amounts appear in red in the list. The last line in this code block resets the color to the neutral list item color if no balance is due.

Connecting Patron Features to the Main Form

That does it for the new patron-specific forms. Let's enable access to them through the main Library form. Wow! It's been awhile since I really looked at this form. I've forgotten what it looks like. Ah, yes. One of the main icons provides access to a patron's record (see Figure).

Accessing patron records from the Main form

All we need to do is add an event handler for the Patron button. Locate the ActAccessPatron_Click event handler in the form's source code. Then add the following code to that handler.

Insert Snippet

Insert Chapter 18, Snippet Item 12.

' ----- Look up the record of an active patron.
Dim patronID As Integer

' ----- Get the ID of the patron.
patronID = (New PatronAccess).SelectPatron()
If (patronID = -1) Then Return

' ----- Show the patron record.
Call (New PatronRecord).ViewPatronRecord(patronID, True)

This code makes direct calls to two of the forms we added in this chapter: PatronAccess and PatronRecord. It first prompts the user to select a patron record, and then displays its details through the Patron Record form.

Dueling Patron Management Forms

Let's make one more change regarding patron records. Way back in an earlier chapter, we included a Manage Patron Items button on the Patron.vb form. This button existed to provide access to the future PatronRecord.vb form, but it's pretty much been dead weight until now. But with the PatronRecord.vb form in place, we're ready to make patron management history.

Open the source code for the Patron.vb form, and locate the ActItems_Click event handler. Then add the following code to it.

Insert Snippet

Insert Chapter 18, Snippet Item 13.

Call (New PatronRecord).ViewPatronRecord(ActiveID, False)

This is all well and good, but you are probably thinking to yourself, "The Patron form now lets you open the Patron Record form. And that form has an Edit button that lets you once again open the Patron form. If you get a rogue librarian, there may be millions of patron management forms on the screen at once." And that's all true. So we had to add some code to prevent that from happening. The second "False" argument to PatronRecord.ViewPatronRecord is a flag that says, "Don't show the Edit button on the Patron Record form." Similar code exists in the Patron Record form that stops the recursion.

Private Sub ActEditPatron_Click...
   If ((New Patron).EditRecordLimited( _
      ActivePatronID) <> -1) Then...

The EditRecord Limited method hides the Manage Patron Items button on the Patron.vb form. Whichever form you start with, you can access the other form, but you won't be able to generate a new copy of the initial form.

There was a lot of new code in this chapter, but it was all very pedestrian. We could have made even more culturally sensitive changes. For example, the Due Date column in the list of checked-out items on the PatronRecord.vb form uses a hard-coded date format for its display.

dueDate = Format(itemDetail.DueDate, "MMM d, yyyy")

This could be changed to "Short Date" or another culture-neutral setting. Whichever method you choose really depends on your target audience. If you do need to develop applications for global markets, you might want to consider reading another great Addison-Wesley book, .NET Internationalization: The Developer's Guide to Building Global Windows and Web Applications, by Guy Smith-Ferrier. But wait, let's finish this book first! Chapter 19, "Printing," is next.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows