More Info
Signature Device
Web Interface
Iowa Spring Training System
Technologies UsedWeb interface was developed with php and the use of ADODB. The signature capture program was developed with C# which took advantage of the manufactures .NET API's. The web service was also developed with C# in order maneuver around the Pix Firewall. All data was stored in one Oracle DB with 8 tables and many constraints. Custom PLQSL scripts were used to provide functionality that SQL could not.
More Info
Iowa Spring was a client that came to Alliance Technologies in need of a reliable, more productive
solution for their training system. Before this project was developed, Iowa Spring was using huge binders
crammed with signatures for required training. This posed a problem every time they needed to find a signature
or find out if an employee has completed training, not to mention the rough times when auditors came.
First step was to decide how to get the signature in a digital format. After a lot of research I found a
cheap but suitable signature device from Topaz Systems
just like the one pictured on the right.
After I drew a few happy faces I started developing the signature device interface. Luckily I did not
have to get into device level programming because Topaz Systems provided a dll that I could use
to call a few hooks After an hour of working with it I had signatures on my screen.
Since I have never worked with .NET connecting to Oracle I worked with Andy Brudtkuhl and we created a web
service as a go between from the signature device to the Oracle database. Ok, I can’t say we… this was all
Andy’s creation. I was just a back seat driver during this part.
The web service transfers all of the training classes and employee information in one xml file.
This xml file is then queried locally by the signature device application instead of hitting the database on every query.
It was also the only way logically as the Pix Firewall blocks all traffic on all ports to the database servers unless you
are coming from an inside computer. Hence, the web service was a major part in this project and we couldn't go without it.
Along with the signature device is a barcode scanner. All employees barcodes are stored in the database so it will be
easy to find an employee - simply scan their employee ID card with a barcode scanner and the their name is looked up
from the xml file and displayed in the upper right hand corner. To my surprise, there was no programming involved with
the barcode scanner. After playing with it for a while I discovered that many barcode scanners simply read the bars,
come up with numbers and letters, then output text just like a keyboard does - Simple!
After scanning the
barcode, highlighting a training class and setting the date, the employee can sign their signature and click submit button
which inserts everything into the database and copies the image to the web server for later viewing.
I was planning on storing the image directly in the database since they are only 3kb images however, we had 2 days of
problems trying to get Oracle to take a "blob" field from .NET and output it properly to php. So, we threw in the towel
and came up with another resolution instead of wasting more time - just copy them to the servers file system with ftp
and use the ftp functions in php to call those images later. Why didn't I think of this before wasting 2 days?
This was the second half of this project. I used ADODB to connect to the Oracle database which is easier said then done. I created all of the add, edit, view and delete screens for employees, training, and positions. This is the primary area that all data is entered into the system.
Of course it has been password protected with htaccess. When adding new records it is necessary to enter dates in many places such as employee hire date or the date training is due for employees in certain positions.
Now we all know that many users don't have "Fat Fingers"... they just can't read what they typed and I can not be there to coach ever user through the system. To get around this popular short coming I decided to use a java script calendar as means of changing the date field. The calendar does not pop up in a new window; it just becomes another layer on the current page. Once a signature has been submitted, it is immediately available on the web interface via an employee report that shows all training taken for the specified employee within the specified date range.
This is where the ftp functions I mentioned earlier come into play.
When view signature is clicked php calls an ftp script, logs into another server
(same one that the web service is on) and pulls the image it needs. When the images were saved from the signature device, they were saved
in a directory structure based of the data that was selected. Now by using the submission date, training code, and the employee's id grabbed
from the database the image can be retrieved and you are 100% sure it is the one you need.
It's a nice little function that's easy to use and could be used in a nightly cron job for nightly transfers if needed. You are free
to take and customize it if you wish:
//Filename I want to save the image as on the local side
$localfile "/var/www/html/Path/To/Web/images/localimage.jpg";
//The file location and actual name of the file at the remote end
$remotefile = "/data/on/ftp/server/remoteimage.jpg";
//Go fetch the image
if ($connect = ftp_connect('216.239.39.99')) //host name or IP
{
if (ftp_login($connect, 'username', 'password'))
{
if (@ftp_get($connect, $local_file, $server_file, FTP_BINARY))
{
print("<img src=\"/Path/To/Web/images/".$localfile."\" />");
}
//You could expand on this to have 2 errors
else
{
print('File does not exsists or access is denied.');
exit();
}
}
else
{
print('Cannot log in');
exit();
}
ftp_close($connect);
}
else
{
//Something else happened that we did not account for
print('Cannot connect');
exit();
}
So now that everything is working, people can insert signatures, pull up signatures later, look at employee information etc.
That's great! However, I needed something to schedule all of this training or else there would be mass confusion on what is taken by who and when.
This is where the notifications come in. As one of the main core factors in this project, I wanted something solid for notifications
that could easily access the database, report results on the web interface, email efficiently, and run nightly. We're already using php,
lets use it again. In case you did not know, php has a CML that will run on Linux. The best way I found to get these notifications into a
cron job is to make cron run the following:
apache /var/Path/To/ShellScript/notifications.sh
This tells the cron job that apache will be running this process which in turn will run your code through the php parser. Except if we just call
our php file using php /var/myfile.php we could run into all kinds of issues. First of all, when making cron jobs it is always nice to
use the full path to all of your commands to prevent breakage so use /usr/bin/php if that's your path. We don't want php to print anything out on
the screen either as this will go to /var/log/messages every time you run it so use the quite mode by using -q. Errors however, will be forced
to print out. The finished product should look like something below:
#!/bin/bash
/usr/bin/php -q /Path/To/daily_notify.php
/usr/bin/php -q /Path/To/biweek_notify.php
/usr/bin/php -q /Path/To/overdue_notify.php
The web interface can run the same scripts that the shell script runs in case someone did not get the email or the email server was down. In any case
you can always view what training is happening within the next 2 weeks and what training is overdue. Iowa Spring's goal is to catch all training before
it becomes overdue. Therefore all supervisors get an email everyday (one for each employee) annoying them to get it down. The notifications will not
stop until HR voids it or it has been signed for.
