CalTrans District 12

Traffic Management Center

Activity Log Project

The Problems:

CalTrans District 12 was using an Access 97 database to record and store information relating to hiway indicents on Orange County roadways. When this program was not working or was running too slow, they would use Excel or write the information down on pieces of paper. The Access 97 database could not be installed on newer operating systems (Access 97 required Windows 98) and so needed to be updated or replaced.

The database was password protected and the employee that wrote the original database was no longer working for CalTrans and didn't remember the password. This meant that an update to the existing database would not be feasible. The program would have to be duplicated from what could be seen from the user perspective. In addidtion, there were multiple groups within CalTrans that each used their own database and Access Interface.

With budget problems facing most state agencies, coming up with the funds to improve the data collection of the District 12 Traffic Management Center (TMC) would prove to be a big challenge. The project was put on hold for most of 2008. UCI was brought in to do a performance evaluation of the TMC and would ultimately hire me to allow them to collect the data they needed to conduct their evaluation.


The Action Plan:

Analize the existing database and interface to determine the needs of the end users within the TMC. Design a new database and interface using current network application technonolgy that has the same look and feel as the old Access 97 interface to make transition to the new software smoother for the end users.

Duplicate the existing functionalty of the current program and test to be sure that everything is working the same as the original program had instended. Then add more functionalty to improve the program including the ability to make changes to the program without requiring new programming.

Once the existing program has been replaced and upgraded, use it as a template to write a new program for other division within the TMC. The new program will have the same look and feel as the old one, but will be customized for the needs of the other division. Also look into integrating the XML data feed from the CHP which shares the TMC with CalTrans.


The Challenges:

#1. The formatting of the existing data was not compatible with todays database technology.

Challenge: The date/time of each table row was stored as text in the following format... "6/1/2009 1:15 PM". This format could not be queried by a date or time range within an SQL query.

Solution: Although the new program converts the date/time into that same format for display purposes, the underlying data was split into two fields, one for the date and one for the time in the following format... "2009-06-01","13:15". This format would allow for date/time range searches to be made within the SQL query.


#2. The data used by the TMC changes, grows, shrinks and morphs constantly and the interface needs to change with the data without additional programming.

Challenge: CalTrans is constantly changing. The data recorded will change in the upcoming months and years. CalTrans employees need to be able to add and remove lists and items in the lists on a continuing basis.

Solution: A dynamic menuing system was created that allowed for adding and changing of the menus by use of an administration program. Moving into the future, CalTrans can make any modifications to the system that are required without additional programming.


#3. Any reports run on old non-existant fields need to still display the correct data.

Challenge: The old database used many relational tables. The main data table constited of mostly links to other tables that contained the actual data. So items could not be removed from the linked tables without losing the information in past reports.

Solution: The new database structure still uses tables for lists, but instead of loading the main table with a referance to the data, the actual data is stored in the table. This way, when items are removed from the lists, the information is not lost for reports that are run after the data is removed.


Updates:


CalTrans District 12 - Activity Log Enhancements - 2009 & 2010

Improve the existing Activity Log Report Builder

The purpose of this task was to improve the Activity Logs Reporting capability. To that end reports we're added to all three of the TMC's activity log programs. The TMT Responders Log had three reports added. First was a delay calculation report, the second was an employee report and the third was a EA summary report. All three reports included the ability to run the report for any date range. Also the date range subroutine that is used by all reports was improved to be more accurate and error check malformed or invalid dates.

The Operations Activity Log (back row) had two reports added and several reports improved including the report builder itself. The report builder was improved to display all activity log columns including the Performance Measures, Route/Direction/Location, Lanes Blocked and Post Miles. Both the Daily Report and the Incident History Report were improved to include data captured from the Communications Radio Activity Log and the CHP iCAD public XML feed. The TMC Operations Activity Report was improved to better capture activity log data and the final report was made so the the entries could be edited if needed. A new CHP iCAD Activity Report was added, and access to the new Closure Sheets Report from the Communications Radio Log was added for the back row.

The main focus for this phase of the project was on the Communications Radio Activity Log reports (front row). As this log was only just completed at the end of last phase, only two reports had been started. All of the reports from the back row were copied to the front row and modified to include the new IMMS field. The report builder was improved to display all activity log columns including the Performance Measures, Route/Direction/Location, Lanes Blocked and Post Miles. The Daily Report and the Incident History Report were improved to include data captured from the Operations Activity Log and the CHP iCAD public XML feed. The Spilled Substance report was improved and the ability to email the report as an attachment was added.

The majority of the work was done on the Closure Sheets Report. The Route and Direction fields were split and both were given drop down lists instead of data entry boxes. Location and Description were split into separate entries and a new Lanes entry box was added. Error checking was added to insure proper formatting of the ID field that corresponds to the CAD field in the Activity Log. After the work was complete, it was reclassified as an Activity Log Routine and removed from the reports window and a button was added inside the Communications Radio Activity Log to access it. This was required because the information from the Closure Sheets was now getting inserted directly into the Communications Radio Activity Log when the 10-97 field gets entered saving duplication of effort. A new copy of the report was added back to the reports window without the ability to modify the entries.

The level of effort for this task was estimated at 200 hours. As of the end of this phase only 121 hours had been used. Since not all that time was used, more work is being requested for the next phase of this project to use the remaining 79 hours.


Miscellaneous support for the Activity Log deployment

From time to time bugs and display errors are discovered and reported by the users of the Activity Log Programs. Once they are reported they are researched and programming fixes are implemented. The following items were the major fixes or changes in this phase of the contract:

1.   Fixed a bug that would cause the javascript to break when illegal characters were used in CAD numbers.
2.   Fixed the bug that was preventing the transactions with no CAD number from showing up in the Find Record Search.
3.   Set the default display when you pick a date to show records with no CAD number.
4.   Fixed a bug that caused the menus to display poorly when the screen resolution was set below 1024x768.
5.   Fixed a display problem that caused older CAD numbers to show before newer ones.
6.   Resolved an issue causing the time to not input properly under some circumstances.
7.   Communications Radio Log Internet Explorer Layout Fix.
8.   Fixed a display problem with low resolution settings on Communications Radio Log.
9.   Fixed a problem with duplicate menu items in Communications Radio Log.
10. Diagnosed and fixed CHP iCAD XML data that stopped working on 3/9/2010.
11. Numerous small bug fixes, display fixes, maintenance issues and program changes.

The level of effort for this task was estimated at 100 hours. As of the end of this phase only 63 hours had been used. This leaves another 37 hours available for the next phase of this project.


Enhancements to the Activity Log application

The purpose of this task was to add improvements to the existing Activity Log Program. As Operators use the program, they discover things that can be improved or discover items that might have been missed in initial development. One of the first items was to change the layout of the Communications Radio Activity Log. The columns were moved. The buttons were moved to a more central location. New buttons were added for closing CAD numbers and IMMS numbers. More room was made for the memo field. The bottom selection box was widened and more information was displayed.

Other changes include mouse over menu items being added and the Operators given the ability to edit the mouse over menu items. The contact information program was updated. A button was added to allow users to change their password. The (Contacted) check box column for the log was removed. The edit record function was updated to open an incident if a CAD was added while editing a record. Error checking was added to manually entered date stamps on CAD numbers. New search for IMMS number capability added to Incident History Search. Users can now select the number of records that appear in the lower search box. As well as other small miscellaneous changes and updates.

The level of effort for this task was estimated at 100 hours. As of the end of this phase 95 hours has been used. Little if any development is left on this project. The only item still outstanding to date is to record when the Operations Activity Log turns on a CMS that is associated with a CAD from the Closure Sheets and to record that entry into the Closure Sheets when it's input into the Operations Activity Log.