The purpose of this report
is to document my work at the Kentucky Higher Education Student Loan Corporation
between September, 2001 and December 2002. Each project is briefly described
using the following format:
|
Project Name |
Approximate Date |
Languages/Tools Used |
|
Summary |
||
Though there is a great deal
of overlap between projects, they are listed approximately in order
chronologically with the most recent project first.
|
“Watchdog” time tracking program |
Nov, 2002 |
Visual Basic |
|
This program is being
developed to produce graphical reports which show the daily agent activity on
Mosaix. Textual data is read in and processed, then
used to draw a chart and create an html report showing other pertinent
information. |
||
|
Spectrum Interface |
Oct, 2002 |
Visual Basic & Spectrum Wallboard SDK |
|
The purpose of this
program is to provide an interface between the Mosaix
system and the three Spectrum wallboards. Using a VT100 emulator, information
is retrieved from Mosaix. It is then processed and
formatted as desired. The next step of the program (not yet completed) would
be to send the formatted data to the wallboards via the PCs comm ports. |
||
|
“LVC” Loan Verification Certificate form generator |
Dec 2001+ |
Visual Basic & AS/400 query code |
|
After building a list of SSNs, this program downloads information from the AS/400.
This information is used to construct the html LVCs
for the borrowers indicated. The program can batch process a large number
borrowers, or display information for a single person at a time. This program has been
modified and extended several times. After a second format (Federal Direct
Loans) was implemented, procedures were added to export data to the
disclosure spreadsheet as well. Code was also added to insure that the LVC
output corresponded exactly to similar output produced by the on-line system
(which uses sub-cent truncation rather than rounding, and so has small errors
in many calculations). |
||
|
Disclosure Spreadsheet |
Oct, 2002 |
Excel & Embedded Visual Basic |
|
This project started with
an existing Excel spreadsheet that was being used to manually create
consolidation disclosure statements. Embedded code was added to automate this
process using data generated by either the LVC or ConsolFetch
programs. Over time, more and more functionality was added to increase the
usefulness of the spreadsheet and streamline the process. |
||
|
“ConsolFetch” consolidation data retrieval utility |
Sep, 2002 |
Visual Basic & AS/400 query code |
|
Given a load date, this
utility queries the KHEAA400 database for consolidation data. The data is
downloaded, reformatted, and written out to a series of files (one per
borrower). This data can then be read in by the disclosure spreadsheet
mentioned previously. The data produced by this utility is used in the exact
same manner as that exported by the LVC program and so the output format must
match up exactly with that produced by the former piece of software. |
||
|
“EMU” Email Modification Utility |
Aug, 2002 |
Visual Basic & HLLAPI |
|
The KHESLC website
collects borrower email addresses. The EMU program takes the compiled list of
addresses, processes them, and then sends the results up to the AS/400 using
a standard Client Access session and HLLAPI emulation code. This results in
the email address for the indicated borrowers being updated with the latest
information. |
||
|
Webmaker modifications |
July, 2002 + |
Visual Basic & ASP
scripting |
|
The Webmaker
process takes data from our database and makes it available for borrower
viewing on the web. The various phases of this project were all concerned
with adding functionality to the existing process. This involved modifying
the source code of the Webmaker program which
processed the AS/400 data, as well as the ASP web pages which were used to
display the data to the borrower. “Total Deductible Interest” information was
added into mix, as was information to help keep borrower information current.
Then the web pages and underlying scripts were modified to display the new
information and subsequent user interaction. |
||
|
“Outage Update Utility” EHLLAPI extension |
June, 2002 |
Visual Basic & HLLAPI |
|
When the AS/400 is
inaccessible, there is a web page that can be used to collect borrower
contact data. “EHLLAPI” code is used to move the offline data back up to the
AS/400 when it comes back online. The “Outage Update Utility” is an extension
of the EHLLAPI program that provides a more robust interface and more
built-in functionality (including a date range, comment field, and log file).
The HLL(High Level Language) API is used to simulate
a user manually entering data into an AS/400 Client Access session. |
||
|
“Grinder” SCCR file processing |
May, 2002 + |
Visual Basic & FTP |
|
The purpose of this
program is to take SCCR data reports from a number of different formats (and
media types), and translate them to a single standard output. This
standardized SCCR report can then be uploaded to the KHEA400 system, or
archived for later use. FTP scripts are used for both the download of initial
information, and the upload of the final report. The program was designed
in a modular fashion so that new formats could be added on with relative
ease. At the current time, formats from USAF/MHEAA, ASA and EdFund, are supported or under construction. This is an
ongoing project as new formats are added, and existing sources slowly convert
from hardcopy reports to electronic data. |
||
|
AGI 45-day letter |
Apr, 2002 |
Embedded Excel code &
HLLAPI |
|
A few buttons were added
to an existing spreadsheet to automate data entry and processing. The
embedded code would download data for a given list of borrowers and then fill
out the spreadsheet. It would also allow the user to print out the data for
one or more borrowers in a batch process. |
||
|
“Fetch” & “Mini Fetch” AS/400 Data Retrieval Programs |
Feb, 2002 |
Visual Basic & AS/400 query code |
|
These are simply VB
template projects that contain the basic functionality to interface with the
AS/400 using standard queries (in .DTF files). This functionality was so
common in KHESLC projects that it seemed worthwhile to set up a common
starting point for all such projects. Neither the full version, nor the
minimal one, was used as a stand-alone project; but source code from both was
used as the basis for several subsequent endeavors. |
||
|
PDF Test |
Jan, 2002 |
C (VC 6.0) & PDF API |
|
This was an experiment in
producing graphical .PDF files using C code. This functionality would have
been useful for a future project, but when that project was called off, the
experiment was also abandoned. |
||
|
“DLLSETUP” LVC installation program |
Dec, 2001 |
Install Shield |
|
This utility was used to
install certain DLLs on the user’s system. These DLLs were needed in order to
run the LVC program on older (pre Win2k) machines. Install Shield was used to
automate the installation process. |
||
|
“Shorten” |
Dec, 2001 |
C++ (VC 6.0) & Secret Agent API |
|
This project was a command
line utility that could decrypt a number of files using the “Secret Agent”
encryption package. The names of the decrypted files were then shortened to
an 8.3 format for later processing. |
||
|
“ComSub” Comment Substitution Program |
Nov, 2001 |
Visual Basic, HLLAPI &
Excel |
|
Written for David Carlson
(internal auditor), this program read data from an Excel file, processed it,
and then used Client Express and the HLLAPI interface to modify records on
the AS/400. Specifically, it was changing
a comment line in several thousand records from something like “NEEDS SAVE”
to “FULLY CURED”. The Excel file contained the borrower SSNs
and current comment lines. |
||
|
PGP Encryption & “Crypt” |
Oct, 2001 |
C++ (VC 6.0) & PGP API |
|
The PGP encryption package
(by Network Associates) was used to build a command-line utility that could
encrypt or decrypt files using a specified key. The program also logged all
activity. |
||