[AccessD] File status logging classes

The Insurance Call Center software I developed for DIS uses a fair amount of
file manipulations.  For example I export data to text files for a couple of
different data sets to send off to the Insurer to update their system with
the file statuses.  These files start a claim at the insurer and then tell
the insurer to pay the claim, how much and when.  It is necessary to track
these file exports pretty closely since if they don't go the Claimant
doesn't get paid.

I inherited code that did this process from the old system that I replaced
and since it "mostly worked" I never rewrote it.  The Insurer is moving to a
new automated software that changes the way they do things so I am now
converting that old system from using a word doc to hold the changes to
using a fixed width field text file.  The file will now be FTPd to a server
instead of opened and read (manually keyed in) at the other end.

Automation, gotta love it.

One of the problems I encountered in the "old way" of doing things was that
a process would export the file, attach the file to an email and pray that
it made it out of the email server.  If it didn't then we would manually
monitor an email box where we sent the files (to ourselves) and if a file
didn't go, we had to go manually build an email, find the file and attach it
to the email and try again.

Of course this new way gives me the opportunity to do things better (we
hope!).  One of the things I need to do is track the status of the file as
it is exported and uploaded.  My idea is to simply have processes that run.
A file export process exports the file.  A FTP process uploads the file.  An
Email process attaches files to emails and tries to send them.  As processes
handle files, they all need to log a status for the file.  

Rather than have code in each process that knows about table names, how to
read / write the tables etc. I decided to build a set of classes that
handles logging and reporting file statuses.  There are two tables involved:

atblFile
FIL_ID	Autonumber PK
FIL_Spec	The fully pathed file/name/extension of the file being
processed
FIL_Dte	The date this record created
FIL_Time	The time this record created
FIL_Cmpltd	The date the processing successfully completed

atblFileStatus
FILST_ID		Autonumber PK
FILST_IDFIL		The FK of the FILE record
FILST_Status	The text status of the file
FILST_Dte		The date this record created
FILST_Time		The time this record created

I then built three classes:

clsFileSupervisor, a supervisor class to manipulate the file records.
clsFile, a file to store the data in a single record from atblFile
clsFileStatus, a class to store each status record for a given file record.

clsFileStatus has no processing at all, it just stores a status and has
properties to return the pieces of the status record.

clsFile is able to load the status records for the File PK, and instantiate
clsFileStatus for each status, storing the classes in a collection.  It is
also able to create a new status record in the status table for the file it
is processing.  It also has properties for the file itself as well as a
current status property and a property that can return the entire collection
of statuses.

clsFileSupervisor manages the process of building, reading and updating
files and their statuses.  It loads all the files that are not Cmpltd into
classes and stores these file classes in a collection.  It also can return a
specific file class instance or the entire collection of file classes.

Additionally, it monitors a message class.  The message class has been
discussed in various places in my previous ramblings, but is basically just
a small class with two methods that can be called.  

Send(varFrom As Variant, varTo As Variant, varSubj As Variant, varMsg As
Variant)
SendSimple(varMsg As Variant)

These two methods do nothing more than raise an event Message() and
MessageSimple() passing the parameters right back out.

Thus any process can grab a pointer to the message class and send messages.
This File Status Logger class can grab a pointer to the message class and
sink the messages.  

Private WithEvents mclsMsg As dclsMsg

Private Sub mclsMsg_Message(varFrom As Variant, varTo As Variant, _
                        varSubj As Variant, varMsg As Variant)
    '
    'messages sent using the more complex message() event will be scanned by
the
    'File Supervisor.  If the To is "FileSuper" then the message will be
processed here
    '
    If varTo = "FileSuper" Then
        'The Subject will be the File Spec
        'The Msg will be the status

		'Process the message, logging the filespec and status in the
log table
    End If
End Sub

Messages have a To: which must be set to "FileSuper" in order for the file
supervisor to handle the message.  Any message sent on this message channel
addressed To: FileSuper is assumed to have a Subject: of FileSpec (the
path/name/extension of the file being processed) and a Message: of the
status.

Any such messages are grabbed by the supervisor class.  If the filespec
already exists in the table (and is loaded in the collection) then the
existing clsFile logs the STATUS (the Message:) into atblFileStatus.  If the
FileSpec does not exist then a new File record (and class) is created and
then the STATUS is logged (and a class created).

The upshot of all this (and it's not THAT complicated), is that any process
can log a file status simply by sending a message on the message channel.
This eliminates having to build code in each class that manipulates a file
to handle this status logging.

The process could be implemented without the message class of course by
loading and getting a pointer to the file logger, calling methods of that
class directly.  I just like the indirection allowed by the message class.

In summary, I build processes that handle each step of manipulating a file,
building the file and archiving it to disk, uploading it to the FTP server
etc.  Each process logs it's status via this set of File Status classes.
Any process can also check the status of a file from this file supervisor to
see whether a previous process is complete, whether a process failed etc.
The "last process" class updates the Completed date in the File class to
indicate that the file has successfully finished the entire handling process
sequences, whatever that sequence may be.

This should allow much more robust failure recovery, where any given process
simply looks for files with a given current status and processes them,
logging the updated status as they finish.  Any failure allows the same
process to retry later and if successful, log the process success in the
file status log.  The process classes will be responsible for sending email
messages to myself and a "failure notify" list of any failed processes so
that failures can be investigated.  

My intention with these rather long winded messages is to provide examples
of how classes can be used to encapsulate processes and allow each such
process to be self contained.  Systems like this can be tested all by
themselves without any of the surrounding processes since with a specified
interface they don't depend on the other processes to be loaded and
functioning.    This allows testing as a "black box" that does what it is
supposed to do.  Poke it and see if it wiggles.  Build file records, add
statuses, read file properties, read file statuses and properties.  These
things should work precisely as designed regardless.  Once this black box
works, use it with the next black box to record the status of the file being
processed by that black box.

John W. Colby
The DIS Database Guy