Project Definition

 

-Facilities Energy Management System is a database system that is upgraded upon the old Utility Bill Management System, which was written in VB6.

 

-The immediate goal of the system is to manage utility bills- organize and logically keep track of Wisconsin Public Service bills that contain volumes of over a 10-15 year period. The E/R Diagram and simplified E/R Diagram in the View Column can help you undestand the structure and model of the database.

 

-The main goal of the system is to generate reports that provide information to support the College decision making. With good analysis, the college can try to improve efficiency and save thousands of dollars each year. The WorkFlow Chart provided in the View Column can show you the process of using the system.

 

Project Requirements

 

1) Sucessfully convert the old system in VB6 to a working system using C# and running on .NET environment.

2) Refine the program by cleanning up messy codes and unused modules (the old system was started, built and finished by different people). Test and make sure the program is robust!

3) Analyzing the program, enhancing/adding features, and/or remodelling the database based on needs/requests.

4) Document the project and the system (the old system was never documented).

 

Approaches to the Project

 

There are many approaches to this project based on different requirements.

Requirement #1: Conversion

-First Approach: Automatically convert the source code of the program from VB6 to VB.NET using built-in conversion tool of Visual Studio 2005.NET. Then using an open-source conversion tool online- http://www.developerfusion.com/tools/convert/vb-to-csharp/-, convert the code from VB.NET to C#.

      +Trial Time: 2 weeks (week 7 and 8 in Gantt Chart)

      +Result: Approach abandonned! The code converted from VB6 to VB.NET was far from working. There are lines of code that were not converted and a small amount of codes were lost in the process. The program's codes were mixed between VB6 and VB.NET. It got worse when these lines of junk code were converted to C#. Besides the same problems, the code was now a mixed of VB6, VB.NET, and C#. After almost two weeks, I figured out that the time it would take to fix these hundred bugs and the confusion caused by three different classes of syntax made this approach unworthy.

 

-Second Approach: the approach that I'm currently using is the mix of these two approaches: the syntax-focused approach which converts the system from VB6 to C# line by line based on my understanding of the syntax of the 2 languages; and the function-focused approach which requires me to understand the purpose of each function and to try to independently write the function in C#, with reference from Dr. Blahnik's written functions.

 

Requirement #2: Refinement

-First Approach (macro-approach): To study the old system carefully and then get rid of unused functions. I started out doing this but then realized that even in used functions, there are redundant codes that tries to accomplish the same goal. The reasons for these are: different people write/modify different functions that call on each other; and repeating tasks from between called and event-handling functions. To eliminate those codes, I move to the next approach.

-Second Approach (micro-approach): To get rid of the unnecessary codes as I convert them. While converting, I will make sure to simplify the code from functions that call on each other.

 

Requirement #3a: Enhancing the Database

-The needs for enhancing the database arose during my system conversion. As I try to convert and refine the system, I have encountered many difficulties mainly because of the way the database was modeled:

First of all, the database is not normalized- it does not solve the problems associated with relationship between meter number and account number and building (it allows account number to exist independently from buildings and meter number, it allows a meter number to be associated with many account numbers and vice versa; it allows an account number to associate with different meter numbers from different buildings, etc.)

Secondly, the database keeps track of data from old account numbers, and the only way of accessing those data is through the aliases in the new account number. Also, the old application does not have any function that creates these aliases, so when the account numbers change again, the system administrator has to go into the database to create those aliases.

In addition, there are many other flaws with the database. Some of the fields (dates) were given wrong data type (binary type) that causes trouble while writing data and inefficient use of memory. The account alias field that should allows zero-length string (because not all accounts are associated with older accounts) does not. An entity does not have primary keys while another entity has more primary keys than it needs; this allows duplicate data and bad data.

 

-My solution: I have fixed the minor flaws from the data type and entities of the database. I have also searched out and corrected the bad data. However, the scope of my project is not to redesign the database, so I do not fix the first two major problems. Instead, I made sure that my queries search and bring out the correct data, and that my application enforces the data integrity by allowing the user to write or edit only the correct data. This, however, is only temporary fixes. Problems will come back the next time the account numbers get changed. This is why I recommend a future CSCI330 project to inherit and remodel the database, and the application to fit the database.

 

Requirement #3b: Enhancing the System

- The needs and requests for enhancing the system arose during the testing phas, mainly from my client, Dr. Blahnik. Other suggestions came from my computer science professors: Dr. McVey and Dr. Pankratz. The enhancements focus mostly on user-interface.

- Here are the list of the main enhancements I have made on the system:

1. The ability to print activities log.
2. The ability to change printer preferences and to see the preview of the reports.
3. Corrections of the reports (the reports coming from the old system have some flaws such as duplication of data and wrong calculation of changes)
4. Significant improvements of the setup form that lets the user to create and edit buildings, meter numbers, and account numbers. I basically re-designed the form because the previous form is very confusing, has some useless features, and allows the user to create bad data.

- One of the functions of the old system is the search function. Because the old search function was useless, it never got used. And because the user has not identified a need for it, I omit that function from my new system. However, I suggest that if the database is to be remodeled, which will make clear distinction between old accounts and new accounts and solve other problems of relations between account numbers and meter numbers, the search function can become useful, as the user can assess to more summarizing data than the current standard reports.

 

Requirement 4: Documentation

- Because Dr. Blahnik will inherit my system at the end of this semester, and someone else will inherit and maintain the system when Dr. Blahnik is retired, it is essential for the project to be well-documented.

- Solution: I will make sure that the code is well-documented and I will write a clear description of the current database, and what needs to be enhanced.