The license agreement-only method clearly opts for freedom over security. When you supply the user with software, it comes with a carefully crafted license agreement that lays out the terms of use for both the user and the software supplier. It generally gives the user certain rights as to installation, use, and distribution of the software.

When you write an application for use only within a specific organization or by a small group of users who you will have regular contact with, the license agreement-only method may be just what you need. In fact, I would bet that most Visual Basic applications are in this vein. Microsoft has announced over the years that the vast majority of Visual Basic programmers target their applications for use in a specific business organization, tied to a specific custom database. Such systems often require very little in the way of license enforcement, since the application is useless when carried outside the building where it was meant to reside.

Even if your software achieves widespread distribution, this licensing scheme may still be the way to go. Many open source applications, including a major operating system that rhymes with “Plinux,” use the Free Software Foundation’s GNU General Public License (http://www.fsf.org/licensing/licenses/gpl.html) as their primary licensing and distribution policy.

If you need a bit more control over the distribution, installation, and use of an application, you can impose a generated general license key—basically a password that allows the application to be installed or used. Such keys are often entered at the start of the installation process, with the user prompted for a specific key. Without the key, it’s goodbye installation.

The software vendor will need a way to generate a good set of unique installation keys. There are a couple of options:

A custom-generated license key is similar to a general generated key, but uses personal information supplied by the user as part of the generation process. Such a key is more interactive, and requires that the end-user specifically communicate with the software vendor (or an application on its web site) to complete the installation process.

During the purchase or installation process, the user makes specific information (such as the owner’s name and the date of purchase) available to the software vendor. The vendor then uses public-private key encryption (asymmetric cryptography) to either fully encrypt or digitally sign the relevant information. The encrypted signature is then returned to the end-user for installation. The installation process uses the public portion of the key pair to ensure that the signature is valid.

We will use this license key method in the Library Project, so I’ll have more to say about it a little later.

For paranoid software vendors, or for those who have a legitimate need to keep a tight rein on their installation base, there are solutions that involve regular access to hardware or services to confirm that previously installed software is legal and valid. One popular method uses a “dongle,” typically a USB port-based device that the software must have access to each time it runs. The software vendor supplies a dongle with the licensed software, and may encode it with date-based or use-based limits.

With the prevalence of the Internet, software vendors also have the option of real-time verification over the Web. Each time the program runs, it could access a known vendor site to engage in a usage verification process. Such a system allows for ongoing monitoring of the software by vendors who may have a business or governmental reason to limit use of the software.

For one of my customer projects, I must access a third-party web site on a monthly basis and download proprietary data for use with that vendor’s software. The vendor requires that I always access their web site from a specific machine with a specific IP address. It will refuse to supply the data if I attempt to connect from any other machine. If I have a real need to use a fresh IP address (if, for example, I change Internet service providers), I must submit paperwork to the vendor informing them of the new IP address. It seems pesky, and it is an irritation. But the data they supply is unique and valuable, and they feel they have a business need to protect that investment. Since my customer requires the data, I have no choice but to comply with the monthly verification procedures.

The highest level of security requires a blatant distrust of the user, although there may be good reason for this. For highly sensitive applications, the software vendor may make their product available to only a limited number of customers, and then only on a lease basis. As part of the lease agreement, the customer agrees to have a trained staff member of the software vendor on-site, running and maintaining the application for the customer. At the very least, the vendor will require that one of its employees be immediately available to the customer whenever the application is used.

In a world of off-the-shelf software applications, it seems unconscionable that such a system could exist. But in high-risk situations, security concerns are raised to such a level that neither party is willing to fully assume the risks of installing and using the application apart from the other.

Although I was tempted to use this system for the Library Project, I think we’ll stick with our original plan of employing a custom-generated license key.

The Library Project’s license file contains some basic ownership and rights information related to the user who purchased rights to the software. Here’s the XML content I’ve come up with:

<?xml version="1.0" encoding="utf-8"?>
<License>
  <Product>Library Project</Product>
  <LicenseDate>1/1/2000</LicenseDate>
  <ExpireDate>12/31/2999</ExpireDate>
  <CoveredVersion>1.*</CoveredVersion>
  <Licensee>John Q. Public</Licensee>
  <SerialNumber>LIB-123456789</SerialNumber>
</License>

That seems sufficient. The process that builds the digital signature also stores an encrypted signature within the XML content.

In the "Project" section of this chapter, we’ll build a new application that exists solely to generate license files for the Library application. It will have three primary components:

The "Project" section of this chapter will show you how to generate a generic license file. This XML file will be distributed and installed with the Library application using the setup program that we will build in Chapter 25. The file will be named LibraryLicense.lic (by default) and will always appear in the same directory as the Library.exe application file.

If I were developing a real application for paying customers, and I had a web site that supported a web service (which I’ll talk about in Chapter 23), here is one design for installing the license file that I might use:

Whenever the Library application runs, it reads in the XML license file and performs many checks to ensure that the license is valid for the current application installation. If the license is invalid for any reason, the application blocks access to the enhanced administrative features included in the Library system.

Since we’ll be adding a new external file that will be processed by the Library Project, we need to document its structure in the project’s Technical Resource Kit. Let’s add the following new section to that document.

<?xml version="1.0"?>
<License>
  <Product>Library Project</Product>
  <LicenseDate>1/1/2000</LicenseDate>
  <ExpireDate>12/31/2999</ExpireDate>
  <CoveredVersion>1.*</CoveredVersion>
  <Licensee>John Q. Public</Licensee>
  <SerialNumber>LIB-123456789</SerialNumber>
  <Signature>
    Digital signature appears here (not shown)
  </Signature>
</License>

<major>.<minor>.<build>.<revision>

Generating license files and digital signatures by hand using Notepad would be . . . well, let’s not even think about it. Instead, we’ll depend on a custom application to create the files and signatures for us. I’ve already developed that custom tool for you. You’ll find it in the installation directory for this book’s code, in the LibraryLicensing subdirectory.

This support application includes two main forms. The first (KeyLocationForm.vb, shown in Figure 22-6) locates or creates the public-private key files used in the digital signature process.

Figure 22-6. Support form for digital signatures

Most of the form’s code helps to locate and verify the folder that will contain the two key files (one private, one public). Some of the code in the ActGenerate_Click event handler creates the actual files.

Dim twoPartKey As RSA
Dim publicFile As String
Dim privateFile As String

...some code skipped here, then...

' ----- Generate the keys.
twoPartKey = New RSACryptoServiceProvider
twoPartKey = RSA.Create(  )

' ----- Save the public key.
My.Computer.FileSystem.WriteAllText(publicFile, _
   twoPartKey.ToXmlString(False), False)

' ----- Save the private key.
My.Computer.FileSystem.WriteAllText(privateFile, _
   twoPartKey.ToXmlString(True), False)

That’s really simple! The System.Security.Cryptography.RSA class and the related RSACryptoServiceProvider class do all the work. All you have to do is call the RSA.Create method, and then generate the relevant XML keys using the ToXmlString method, passing an argument of False for the public key and True for the private key. If you want to look at some sample keys, open the LicenseFiles subdirectory in this book’s source installation directory. You’ll find two files, one for the public key and one for the private key. I’d print one of them here, but it all just looks like random characters.

The other support form is MainForm.vb, which generates the actual end-user license file, and appears in Figure 22-7.

Figure 22-7. Support form for license file generation

As with the first form, most of this form’s code simply ensures that the public and private key files are intact, and that the user entered valid data before generation. The ActGenerate_Click event handler is where the real fun is. First, we need some XML content, which we build in the BuildXmlLicenseContent method. It creates the content element by element, using the methods we learned about in Chapter 13. For instance, here’s the part of the code that adds the serial number:

dataElement = result.CreateElement("SerialNumber")
dataElement.InnerText = Trim(SerialNumber.Text)
rootElement.AppendChild(dataElement)

Then comes the digital signature, via the SignXmlLicenseContent function, most of which appears here:

Private Function SignXmlLicenseContent( _
      ByVal sourceXML As XmlDocument) As Boolean
   ' ----- Add a digital signature to an XML document.
   Dim privateKeyFile As String
   Dim privateKey As RSA
   Dim signature As SignedXml
   Dim referenceMethod As Reference

   ' ----- Load in the private key.
   privateKeyFile = My.Computer.FileSystem.CombinePath( _
      KeyLocation.Text, PrivateKeyFilename)
   privateKey = RSA.Create(  )
   privateKey.FromXmlString( _
      My.Computer.FileSystem.ReadAllText(privateKeyFile))

   ' ----- Create the object that generates the signature.
   signature = New SignedXml(sourceXML)
   signature.SignedInfo.CanonicalizationMethod = _
      SignedXml.XmlDsigCanonicalizationUrl
   signature.SigningKey = privateKey

   ' ----- The signature will appear as a <reference>
   '       element in the XML.
   referenceMethod = New Reference("")
   referenceMethod.AddTransform(New _
      XmlDsigEnvelopedSignatureTransform(False))
   signature.AddReference(referenceMethod)

   ' ----- Add the signature to the XML content.
   signature.ComputeSignature(  )
   sourceXML.DocumentElement.AppendChild(signature.GetXml(  ))

   ' ----- Finished.
   Return True
End Function

Digital signing occurs via the SignedXml class (in the System.Security.Cryptography.Xml namespace). This class uses a few different signing methods; the one I chose (XmlDsigCanonicalizationUrl) is used for typical XML and ignores embedded comments.

This signature appears as tags and values in the XML output, added through the AppendChild statement near the end of the routine. Since we don’t want the signature itself to be considered when we later scan the XML file for valid content, the SignedXml class adds the signature as a <reference> tag. This occurs in code by adding a Reference object that is programmed for that purpose. It’s added through the signature.AddReference method call.

Once we have the signature in the XML content, we write it all out to a file specified by the user via the standard XmlDocument.Save method (in the ActGenerate_Click event handler).

licenseXML.Save(LicenseSaveLocation.FileName)

Here’s a sample XML license file that includes a digital signature. This is the one that I have included in the LicenseFiles directory in the book’s source installation directory (with some lines wrapped to fit this page).

<?xml version="1.0"?>
<License>
  <Product>Library Project</Product>
  <LicenseDate>1/1/2000</LicenseDate>
  <ExpireDate>12/31/2999</ExpireDate>
  <CoveredVersion>1.*</CoveredVersion>
  <Licensee>John Q. Public</Licensee>
  <SerialNumber>LIB-123456789</SerialNumber>
  <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
    <SignedInfo>
      <CanonicalizationMethod Algorithm=
        "http://www.w3.org/TR/2001/REC-xml-c14n-20010315" />
      <SignatureMethod Algorithm=
        "http://www.w3.org/2000/09/xmldsig#rsa-sha1" />
      <Reference URI="">
        <Transforms>
          <Transform Algorithm="http://www.w3.org/2000/09/
            xmldsig#enveloped-signature" />
        </Transforms>
        <DigestMethod Algorithm="http://www.w3.org/2000/09/
          xmldsig#sha1" />
        <DigestValue>Dn6JYIBI/qQudmvSiMvuOvnVBGU=
          </DigestValue>
      </Reference>
    </SignedInfo>
    <SignatureValue>NULghI4WbzDLroIcf2u9aoybfSjXPJRN5
       0UMrCPYa5bup+c7RJnqTM+SzP4jmfJWPPs7pOvDC/fbdNY
       VMaoyXW0jL3Lk8du3X4JXpW3xp9Nxq31y/Ld8E+RkoiPO6
       KRGDI+RRZ8MAQda8WS+L2fMyenRAjo+fR9KL3sQ/hOfQX8=
    </SignatureValue>
  </Signature>
</License>

The digital signature appears as the scrambled content within the <SignatureValue> tag. Now, if anyone tries to modify any of the license values, the license will no longer match the signature, and the entire license will become invalid.

Instead of using a digital signature, we could have just encrypted the entire licensing file with the private key, and later used the public key to decrypt it and examine its contents. But I like the digital signature better, since it allows anyone to open the license file and check the parameters of the license itself while still preventing any changes.

Let’s return to the Library application already in progress.

The program will adjust its behavior depending on whether it is licensed or not. But to make that determination, it needs to ensure that the contents of the licensing file are valid and haven’t been tampered with. To do this, it needs a way to unscramble the signature and compare it with the rest of the license to make sure it matches. We built the signature using the private key; we must unscramble it using the public key.

We could store the public key in its own file outside the program, but then it might get lost (just like my real keys). Instead, we’ll store the public key as an application resource, found externally in the source code’s Resources folder. I’ve already added the resource to your copy of the program, and named it LicensePublicKey. With this key embedded in the application, any regeneration of the public and private keys will require modification of this resource. In code, we refer to the XML content of the public key using its resource name:

My.Resources.LicensePublicKey

Some of the security features use classes found in the System.Security.Cryptography.Xml namespace. This is not one of the namespaces included by default in new Visual Basic applications, so we’ll have to add it ourselves. Open the project properties window and select the References tab. Just below the list of References, click the Add button, and then select System.Security from the .NET tab of the Add Reference window that appears.

Since we have the project properties window still open, click over to the Settings tab. Add a new String setting and use “LicenseFileLocation” for its name. We’ll use this setting to store the path to the license file. Save and close the project properties window.

Our general licensing needs throughout the application are pretty simple. We only need to know the current status of the licensing file, and have access to a few of the licensing values so that we can display a short message about the license. We may need to do this in various parts of the program, so let’s add some useful generic code to the General.vb module. Open that module now.

Right at the top of that file, the code already includes a reference to the System.Security.Cryptography namespace, since we include code that encrypts user passwords. But this doesn’t cover the standard or secure XML stuff. So add two new Imports statements as well.

Imports System.Xml
Imports System.Security.Cryptography.Xml

We’ll use an enumeration to indicate the status of the license. Add it now to the General module.

Public Enum LicenseStatus
   ValidLicense
   MissingLicenseFile
   CorruptLicenseFile
   InvalidSignature
   NotYetLicensed
   LicenseExpired
   VersionMismatch
End Enum

Let’s also add a simple structure that communicates the values extracted from the license file. Add this code to the General module.

Public Structure LicenseFileDetail
   Public Status As LicenseStatus
   Public Licensee As String
   Public LicenseDate As Date
   Public ExpireDate As Date
   Public CoveredVersion As String
   Public SerialNumber As String
End Structure

By default, the license file appears in the same directory as the application, using the name LibraryLicense.lic. Add a global constant to the General module that identifies this default name.

Public Const DefaultLicenseFile _
   As String = "LibraryLicense.lic"

All we need now is some code to fill in the LicenseFileDetail structure. Add the new ExamineLicense function to the General module.

Public Function ExamineLicense(  ) As LicenseFileDetail
   ' ----- Examine the application's license file, and
   '       report back what's inside.
   Dim result As New LicenseFileDetail
   Dim usePath As String
   Dim licenseContent As XmlDocument
   Dim publicKey As RSA
   Dim signedDocument As SignedXml
   Dim matchingNodes As XmlNodeList
   Dim versionParts(  ) As String
   Dim counter As Integer
   Dim comparePart As String

   ' ----- See if the license file exists.
   result.Status = LicenseStatus.MissingLicenseFile
   usePath = My.Settings.LicenseFileLocation
   If (usePath = "") Then usePath = _
      My.Computer.FileSystem.CombinePath( _
      My.Application.Info.DirectoryPath, DefaultLicenseFile)
   If (My.Computer.FileSystem.FileExists(usePath) = False) _
      Then Return result

   ' ----- Try to read in the file.
   result.Status = LicenseStatus.CorruptLicenseFile
   Try
      licenseContent = New XmlDocument(  )
      licenseContent.Load(usePath)
   Catch ex As Exception
      ' ----- Silent error.
      Return result
   End Try
   ' ----- Prepare the public key resource for use.
   publicKey = RSA.Create(  )
   publicKey.FromXmlString(My.Resources.LicensePublicKey)

   ' ----- Confirm the digital signature.
   Try
      signedDocument = New SignedXml(licenseContent)
      matchingNodes = licenseContent.GetElementsByTagName( _
         "Signature")
      signedDocument.LoadXml(CType(matchingNodes(0), _
         XmlElement))
   Catch ex As Exception
      ' ----- Still a corrupted document.
      Return result
   End Try
   If (signedDocument.CheckSignature(publicKey) = False) Then
      result.Status = LicenseStatus.InvalidSignature
      Return result
   End If

   ' ----- The license file is valid. Extract its members.
   Try
      ' ----- Get the licensee name.
      matchingNodes = licenseContent.GetElementsByTagName( _
         "Licensee")
      result.Licensee = matchingNodes(0).InnerText

      ' ----- Get the license date.
      matchingNodes = licenseContent.GetElementsByTagName( _
         "LicenseDate")
      result.LicenseDate = CDate(matchingNodes(0).InnerText)

      ' ----- Get the expiration date.
      matchingNodes = licenseContent.GetElementsByTagName( _
         "ExpireDate")
      result.ExpireDate = CDate(matchingNodes(0).InnerText)

      ' ----- Get the version number.
      matchingNodes = licenseContent.GetElementsByTagName( _
         "CoveredVersion")
      result.CoveredVersion = matchingNodes(0).InnerText

      ' ----- Get the serial number.
      matchingNodes = licenseContent.GetElementsByTagName( _
         "SerialNumber")
      result.SerialNumber = matchingNodes(0).InnerText
   Catch ex As Exception
      ' ----- Still a corrupted document.
      Return result
   End Try

   ' ----- Check for out-of-range dates.
   If (result.LicenseDate > Today) Then
      result.Status = LicenseStatus.NotYetLicensed
      Return result
   End If
   If (result.ExpireDate < Today) Then
      result.Status = LicenseStatus.LicenseExpired
      Return result
   End If

   ' ----- Check the version.
   versionParts = Split(result.CoveredVersion, ".")
   For counter = 0 To UBound(versionParts)
      If (IsNumeric(versionParts(counter)) = True) Then
         ' ----- The version format is
         '       major.minor.build.revision.
         Select Case counter
            Case 0 : comparePart = _
               CStr(My.Application.Info.Version.Major)
            Case 1 : comparePart = _
               CStr(My.Application.Info.Version.Minor)
            Case 2 : comparePart = _
               CStr(My.Application.Info.Version.Build)
            Case 3 : comparePart = _
               CStr(My.Application.Info.Version.Revision)
            Case Else
               ' ----- Corrupt version number.
               Return result
         End Select
         If (Val(comparePart) <> _
               Val(versionParts(counter))) Then
            result.Status = LicenseStatus.VersionMismatch
            Return result
         End If
      End If
   Next counter

   ' ----- Everything seems to be in order.
   result.Status = LicenseStatus.ValidLicense
   Return result
End Function

That’s a lot of code, but most of it just loads and extracts values from the XML license file. The signature-checking part is relatively short.

publicKey = RSA.Create(  )
publicKey.FromXmlString(My.Resources.LicensePublicKey)
signedDocument = New SignedXml(licenseContent)
matchingNodes = licenseContent.GetElementsByTagName( _
   "Signature")
signedDocument.LoadXml(CType(matchingNodes(0), XmlElement))
If (signedDocument.CheckSignature(publicKey) = False) Then
   ' ----- Invalid
End If

The SignedXml object—which we also used to generate the original license file—needs to know exactly which XML tag in its content represents the digital signature. You would think that having an element named <Signature> would be a big tip-off, but perhaps not. Anyway, once you’ve assigned that node using the SignedXml.LoadXml method, you call the CheckSignature method, passing it the public key. If it returns True, you’re good. I mean, not in a moral sense; the code doesn’t know anything about you. But the signature is good.

When we added the About form to the project a few hundred pages ago, we included a Label control named LabelLicensed. It currently always displays “Unlicensed,” but now we have the tools to display a proper license, if available. Open the source code for the About.vb form, and add the following code to the start of the AboutProgram_Load event handler.

' ----- Prepare the form.
Dim licenseDetails As LicenseFileDetail

' ----- Display the licensee.
licenseDetails = ExamineLicense(  )
If (licenseDetails.Status = LicenseStatus.ValidLicense) Then
   LabelLicensed.Text = _
      "Licensed to " & licenseDetails.Licensee & vbCrLf & _
      "Serial number " & licenseDetails.SerialNumber
End If

Figure 22-8 shows the About form in use with details displayed from the license file.

Figure 22-8. Displaying a valid license

Just for fun, I changed the version number in my license file from “1.*” to “2.*” without updating the digital signature. Sure enough, when I displayed the About form again, it displayed “Unlicensed,” since the check of the signature failed. How did I test the code this early? I copied the LibraryLicense.lic file from the book’s installed LicenseFiles subdirectory and placed that copy in the binDebug subdirectory of the project’s source code. Later on, you’ll be able to put the file anywhere you want and browse for it, but we’re getting ahead of ourselves.

At some point, a missing or invalid license should have a negative impact on the use of the application. When that happens, we should give the user a chance to correct the problem by locating a valid license file. We’ll do this through the new LocateLicense.vb form. I’ve already added the form to your project. It appears in Figure 22-9.

Figure 22-9. The gentle way to enforce a product license

This form starts up with a call to its public ChangeLicense function, which returns True if the user changes the license. Most of this form’s code manages the display, presenting detailed reasons why a license is valid or invalid using the results of the ExamineLicense function. If for any reason the license is invalid, a click on the Locate button lets the user browse for a better version.

Private Sub ActLocate_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles ActLocate.Click
   ' ----- Prompt the user for a new license file.
   If (LocateLicenseDialog.ShowDialog(  ) <> _
      Windows.Forms.DialogResult.OK) Then Return

   ' ----- Store the new path.
   My.Settings.LicenseFileLocation = _
      LocateLicenseDialog.FileName
   LocationModified = True

   ' ----- Update the display.
   DisplayLicenseStatus(  )
   LicensePath.Text = My.Settings.LicenseFileLocation
End Sub

The LocationModified form-level variable gets sent back to the caller as a trigger to refresh the status of the license.

For the Library Project in particular, I didn’t see a point in enforcing the license on startup, since it’s not the patrons’ fault that the library stole this important work of software. Instead, I delay the verification process until an administrator or librarian tries to access the enhanced features of the application. Then, if the license check fails, the user should be able to browse the disk for a valid license file.

I think the best place to add the license check is just after the administrator successfully supplies a password. If we checked before that point, it would give ordinary patrons the ability to browse the disk, which is probably a no-no, since anyone and her uncle can walk up and use a patron workstation. Open the source code for the ChangeUser.vb form, locate the ActOK_Click event handler, and locate the “Successful login” comment.

' ----- Successful login.
LoggedInUserID = CInt(dbInfo!ID)
LoggedInUserName = CStr(dbInfo!LoginID)
...

Just before this block of code, add the following license-checking code.

' ----- Don't allow the login if the program is unlicensed.
Do While (ExamineLicense(  ).Status <> _
      LicenseStatus.ValidLicense)
   ' ----- Ask the user what to do.
   If (MsgBox("This application is not properly licensed " & _
         "for administrative use. If you have access to " & _
         "a valid license file, you can verify it now. " & _
         "Would you like to locate a valid license file " & _
         "at this time?", MsgBoxStyle.YesNo Or _
         MsgBoxStyle.Question, ProgramTitle) <> _
         MsgBoxResult.Yes) Then
      dbInfo.Close(  )
      dbInfo = Nothing
      Return
   End If

   ' ----- Prompt for an updated license.
   Call LocateLicense.ChangeLicense(  )
   LocateLicense = Nothing
Loop

This code gives the user an unlimited number of chances to locate a valid license file. Once the license is validated, the code moves forward and enables administrative access.

The last major set of code to be added to the Library Project isn’t related to licensing, but it’s important nonetheless: the processing of fines for overdue items. We’ll add a common method that will perform the processing, and then call it where needed throughout the application.

Add the new DailyProcessByPatronCopy method to the General module.

Public Sub DailyProcessByPatronCopy( _
      ByVal patronCopyID As Integer, ByVal untilDate As Date)
   ' ----- This routine does the most basic work of
   '       processing overdue fines. All other daily
   '       processing routines eventually call this routine.
   Dim sqlText As String
   Dim dbInfo As SqlClient.SqlDataReader
   Dim daysToFine As Integer
   Dim lastProcess As Date
   Dim fineSoFar As Decimal

   On Error GoTo ErrorHandler

   ' ----- Get all of the basic values needed to process
   '       this entry.
   sqlText = "SELECT PC.DueDate, PC.ProcessDate, " & _
      "PC.Fine, CMT.DailyFine FROM PatronCopy AS PC " & _
      "INNER JOIN ItemCopy AS IC ON PC.ItemCopy = IC.ID " & _
      "INNER JOIN NamedItem AS NI ON IC.ItemID = NI.ID " & _
      "INNER JOIN CodeMediaType AS CMT ON " & _
      "NI.MediaType = CMT.ID " & _
      "WHERE PC.ID = " & patronCopyID & _
      " AND PC.DueDate <= " & DBDate(Today) & _
      " AND PC.Returned = 0 AND PC.Missing = 0 " & _
      "AND IC.Missing = 0"
   dbInfo = CreateReader(sqlText)
   If (dbInfo.Read = False) Then
      ' ----- Missing the patron copy record. Oh well.
      '       It was probably because this item was not
      '       yet overdue, or it was missing, or something
      '       valid like that where fines should not increase.
      dbInfo.Close(  )
      dbInfo = Nothing
      Return
   End If

   ' ----- If we have already processed this record for today,
   '       don't do it again.
   If (IsDBNull(dbInfo!ProcessDate) = False) Then
      If (CDate(dbInfo!ProcessDate) >= untilDate) Then
         dbInfo.Close(  )
         dbInfo = Nothing
         Return
      End If
      lastProcess = CDate(dbInfo!ProcessDate)
   Else
      lastProcess = CDate(dbInfo!DueDate)
   End If

   ' ----- Fines are due on this record. Figure out how much.
   daysToFine = CInt(DateDiff(DateInterval.Day, _
      CDate(dbInfo!DueDate), untilDate) - _
      DateDiff(DateInterval.Day, CDate(dbInfo!DueDate), _
      lastProcess) - FineGraceDays)
   If (daysToFine < 0) Then daysToFine = 0
   fineSoFar = 0@
   If (IsDBNull(dbInfo!Fine) = False) Then _
      fineSoFar = CDec(dbInfo!Fine)
   fineSoFar += CDec(dbInfo!DailyFine) * CDec(daysToFine)
   dbInfo.Close(  )
   dbInfo = Nothing

   ' ----- Update the record with the latest fine and
   '       processing information.
   sqlText = "UPDATE PatronCopy SET " & _
      "ProcessDate = " & DBDate(untilDate) & _
      ", Fine = " & Format(fineSoFar, "0.00") & _
      " WHERE ID = " & patronCopyID
   ExecuteSQL(sqlText)
   Return

ErrorHandler:
   GeneralError("DailyProcessByPatronCopy", Err.GetException(  ))
   Resume Next
End Sub

This code examines a PatronCopy record—the record that marks the checking out of a single item by a patron—to see whether it is overdue, and if so, what penalty needs to be added to the record. Each record includes a ProcessDate field. We don’t want to charge the patron twice on the same day for a single overdue item (no, we don’t), so we use the ProcessDate to confirm which days are uncharged.

There are a few places throughout the application where we want to call this processing routine without bothering the user. The first appears in the PatronRecord form, the form that displays the fines a patron still owes. Just before showing that list, we should refresh each item checked out by the patron to make sure we display the most up-to-date fine information. Open that form’s source code, locate the PatronRecord_Load event handler, and add the following code, just before the call to RefreshPatronFines(-1) that appears halfway through the routine.

' ----- Make sure that each item is up-to-date.
For counter = 0 To ItemsOut.Items.Count - 1
   newEntry = CType(ItemsOut.Items(counter), PatronDetailItem)
   DailyProcessByPatronCopy(newEntry.DetailID, Today)
Next counter

The overdue status for an item must also be refreshed just before it is checked in. Open the source code for the MainForm form and locate the ActDoCheckIn_Click event handler. About halfway through its code, you’ll find a comment that starts with “Handle missing items.” Just before that comment, insert the following code.

' ----- Bring the status of the item up-to-date.
DailyProcessByPatronCopy(patronCopyID, CheckInDate.Value)

Checkout needs to refresh the patron’s fines as well, just before letting the patron know whether there are, in fact, any fines due. Move to the MainForm.ActCheckOutPatron_Click event handler, and add the following declarations to the top of the routine.

Dim dbTable As Data.DataTable
Dim dbRow As Data.DataRow

In this same method, find a comment that starts with “Show the patron if there are any fines due.” As usual, it’s about halfway through the routine. Insert the following code just before that comment.

' ----- Bring the patron record up-to-date.
sqlText = "SELECT ID FROM PatronCopy WHERE Returned = 0 " & _
   "AND Missing = 0 AND DueDate < " & DBDate(Today) & _
   " AND (ProcessDate IS NULL OR ProcessDate < " & _
   DBDate(Today) & ") AND Patron = " & patronID
dbTable = CreateDataTable(sqlText)
For Each dbRow In dbTable.Rows
   DailyProcessByPatronCopy(CInt(dbRow!ID), Today)
Next dbRow
dbTable.Dispose(  )
dbTable = Nothing

In addition to automatic fine processing, the Library Project also allows an administrator or librarian to perform daily processing of all patron items at will. This occurs through the Daily Processing panel on the main form (see Figure 22-10).

Figure 22-10. Daily administrative processing

Currently, the panel doesn’t do much of anything, so let’s change that. The first task is to update the status label that appears at the top of the panel. Add a new method named RefreshProcessLocation to the MainForm form’s class.

I won’t show its code here, but it basically checks the CodeLocation.LastProcessing database field either for all locations, or for the user-selected location, and updates the status display accordingly.

The user selects a location for processing with the ProcessLocation drop-down list, but we haven’t yet added any code to populate that list. Find the TaskProcess method in the main form’s source code, and add these declarations to the top of its code.

Dim sqlText As String
Dim dbInfo As SqlClient.SqlDataReader

On Error GoTo ErrorHandler

Then add these statements to the end of the method.

' ----- Refresh the list of locations.
ProcessLocation.Items.Clear(  )
ProcessLocation.Items.Add(New ListItemData( _
   "<All Locations>", −1))
ProcessLocation.SelectedIndex = 0
sqlText = "SELECT ID, FullName FROM CodeLocation " & _
   "ORDER BY FullName"
dbInfo = CreateReader(sqlText)
Do While dbInfo.Read
   ProcessLocation.Items.Add(New ListItemData( _
      CStr(dbInfo!FullName), CInt(dbInfo!ID)))
Loop
dbInfo.Close(  )
dbInfo = Nothing
RefreshProcessLocation(  )
Return

ErrorHandler:
   GeneralError("MainForm.TaskProcess", Err.GetException(  ))
   Resume Next

Each time the user selects a different location from the list, we need to update the status display. Add the following code to the ProcessLocation_SelectedIndexChanged event handler.

' ----- Update the status based on the current location.
RefreshProcessLocation(  )

Daily processing occurs when the user clicks on the Process button. Add the following code to the ActDoProcess_Click event handler.

' ----- Process all of the checked-out books.
Dim sqlText As String
Dim dbTable As Data.DataTable
Dim dbRow As Data.DataRow
Dim locationID As Integer

On Error GoTo ErrorHandler
Me.Cursor = Cursors.WaitCursor

' ----- Get the list of all items that likely need processing.
sqlText = "SELECT PC.ID FROM PatronCopy AS PC " & _
   "INNER JOIN ItemCopy AS IC ON PC.ItemCopy = IC.ID "& _
   "WHERE PC.Returned = 0 AND PC.Missing = 0 " & _
   "AND IC.Missing = 0 AND PC.DueDate < " & DBDate(Today) & _
   " AND (PC.ProcessDate IS NULL OR PC.ProcessDate < " & _
   DBDate(Today) & ")"
If (ProcessLocation.SelectedIndex <> −1) Then
   locationID = CInt(CType(ProcessLocation.SelectedItem, _
      ListItemData))
   If (locationID <> −1) Then sqlText &= _
      " AND IC.Location = " & locationID
Else
   locationID = −1
End If
dbTable = CreateDataTable(sqlText)
For Each dbRow In dbTable.Rows
   DailyProcessByPatronCopy(CInt(dbRow!ID), Today)
Next dbRow
dbTable.Dispose(  )
dbTable = Nothing
Me.Cursor = Cursors.Default
MsgBox("Processing complete.", MsgBoxStyle.OkOnly Or _
   MsgBoxStyle.Information, ProgramTitle)
' ----- Update the processing date.
sqlText = "UPDATE CodeLocation SET LastProcessing = " & _
   DBDate(Today)
If (locationID <> −1) Then sqlText &= _
   " WHERE ID = " & locationID
ExecuteSQL(sqlText)

' ----- Update the status display.
ProcessStatus.Text = "      Processing is up to date."
ProcessStatus.ImageIndex = StatusImageGood
Return

ErrorHandler:
   GeneralError("MainForm.ActDoProcess_Click", Err.GetException(  ))
   Resume Next

To try out the code, run it, locate a valid license file, and test out the different administrative features.

This marks the end of primary coding for the Library Project. Congratulations! But there’s still plenty to do, as you can tell by the presence of four more chapters. Now would not be the time to close the book and call it a day. But it would be a good time to learn about ASP.NET, the topic of the next chapter.