Database Enhanced Timesheet Project

High Level Design

 

Revision History

version

author

date

description

1.0

Nick Bilogorskiy

2001-03-27

Initiated document

 

Summary

This document is intended to be a reference point to the Dynamic Timesheet project as a whole. It includes information about installing the timesheet, describes the high-level design of the project, and mentiones the dependencies, troubleshooting, and maintenance issues.

 

 Table of Contents

 

Table of Contents.. 2

1.         System Overview.. 3

2.         Assumptions and Dependencies.. 4

3.         Installation.. 5

4.         Maintenance.. 6

5.         Appendix A. 7

 

1.    System Overview

 

The dynamic timesheet is essentially an addon/update to the existing timecard tracking system. It was implemented in order to simplify the tasks of filling timecards in and to automate as much of the process as possible.

 

Basically the timesheet is comprised of 3 layers:

·        Excel spreadsheet itself with 4 combo box controls on the spreadsheet (front end)

·        ActiveX DLL project, containing classes that connect to the database and manipulate the controls on the spreadsheet. (middleware)

·        Access Database containing all the WBS codes in related tables. (back end)

 

The spreadsheet calls upon the COM object to do all the work for it. There is very minimal code inside the spreadsheet itself. All it does is instantiates an instance of CTimecodeWorksheet class and passes it the Excel objects. (see Appendix A). That class then makes use of CTimecodeData to query the database and handle the task of mapping the UI values (like “Chilko” and “Tofino”) to their codes, and back. The database is a relational Access database (see Appendix A), and the code uses ActiveX Data Object Recordsets to connect to it. The connection to the database is read-only.


Most of the code is in TimeCodeAutomation.dll on \\vault2\Timecards. However, there is some code in the worksheet as well, so to create new worksheets the user must copy the entire sheet (and not just individual cells).


When the user selects a cell or modifies the spreadsheet by typing codes in a cell or selecting entries in the combo boxes, dynamic SQL queries are generated and run against the database. The component is responsible for:

1.      Mapping the given UI value (e.g. release name “Chilko”) to its WBS code

2.      Populating the other combo boxes with relevant information (e.g. Chilko’s Features, Phases and tasks)

3.      Validating codes


If the active row contains an invalid value (no match in the database) each cell that is invalid and the corresponding dropdown box is cleared.

 

Sample SQL query:

SELECT DISTINCT TaskCodes.TaskUI, TaskCodes.TaskCode

FROM TaskCodes INNER JOIN (Tasks INNER JOIN (PhaseCodes
INNER JOIN  Phases ON ((Phases.PhaseCodeID=PhaseCodes.ID)))

ON (Tasks.PhaseID=Phases.ID)) ON (TaskCodes.ID=Tasks.TaskCodeID)

WHERE PhaseCodes.PhaseUI= “Requirements”

2.    Assumptions and Dependencies

 

·        Users need to properly install the new timesheet
(see section 3 – Installation)

·        Dependencies:
(users also need the following packages installed on their computer)

 

Name

DLL

Package

Visual Basic for Applications

Msvbvm60.dll

Office

OLE Automation

stdole2.tlb

Office

Microsoft Excel 9.0 Object Library

Excel9.olb

Office

Microsoft Office 9.0 Object Library

MSO9.dll

Office

Microsoft ActiveX Data Objects 2.5 Library

msado25.tlb

MDAC

Microsoft ADO Recordset 2.5 Library

msador15.dll

MDAC

 

3.          Installation

 

To switch to the new timesheet one needs to follow these steps:

 

1.      Go to \\vault2\Timecards (or the corresponding drive letter, if it is already mapped)

2.      Run the “TimecardSetup.bat” file (it registers the dll located  in the same folder)

3.      Copy the “Timecard template.xls” and paste it with your own name
(e.g. “Robin Smith.xls”)

4.      Open the newly created spreadsheet (“Robin Smith.xls”) and fill it out.

 

 

Note:   

1.         If you get an error message: “Class not found” or “ActiveX component    could not be created”, that means that the setup probably failed and the DLL isn’t registered. You will need to register it on your computer manually prior to running the Excel timecard spreadsheet.

 

2.         Since the timesheet uses the COM object located on the network drive, it takes a little while to instantiate and initialize (2-10 seconds). The initializations starts not when you open the worksheet but with the first click (selection) you make.

 

Alternative:       copy to local drive & register. User is then responsible for re-copying DLL when updates are available.

4.          Maintenance

 

Since this is a 3-tier system, there are 3 different components here to maintain.

 

1.      UI: Excel Spreadsheet.
Simply edit the spreadsheet and save to the network location.

 

2.      Middle Tier: ActiveX DLL – COM.
This is a VB component. All project and class source files are stored in SourceSafe, inside $/Technical Documentation/Department Documentation/Product Quality/Project Administration/TimeCodeAutomation. To update the component these files must be checked out from sourcesafe, updated and recompiled. The new DLL file must then be placed on the network (\\vault2\Timecards) replacing the old one. Then the source files must be checked in. The DLL does not need to be re-registered as long as the CLSID doesn’t change (i.e. the component project must be set to Binary compatibility).

 

3.      Back end: Access Database
The database is most likely to be updated often. It contains WBS codes in tables with 1-to-many relationships. These codes are likely to change as new projects take place. The database also needs to be updated for all new releases with their respective features, phases, etc. It’s located on \\vault2\Timecards as well (TimeCodes.mdb).

There are 2 ways to update the database:

a.       Use the Forms already in the Database

                                                               i.      Open the database in Access 2000

                                                             ii.      Go to Forms and start up the one you need.

b.      Edit the tables directly:

                                                               i.      Releases, FeatureCodes, PhaseCodes, and TaskCodes store the respective names and codes, while Features, Phases, and Tasks are structure tables (which releases have which features, etc.)

 

                          

 

5.          Appendix A

 

Figure 1: Database schema

 

 

The database is normalized, so that the repetition of values is minimal and maintenance is easier. The following tables contain UI values and codes. Releases

They have no duplicates rows.


The rest of the database includes these tables:

·        Features

These ones contain of long list of numbers, in essense mapping out the respective features to their releases, phases to features, and tasks to phases. For instance, there is a “General” phase with 2 tasks in it, and a number of different features use it. There don’t make copies of it, instead the all use the same one, so that if changes are needed to General phase (1 more task added), we only change it one one place: Tasks table.


Similarly, when a release name is changed from Chilko to Chico for instance, you only need to change in in Releases table. The changes will propagate though the database.

 

 

 

 

 

 

Figure 2: OLE Diagram

TimeCodeAutomation is a VB ActiveX DLL component with Binary Compatibility setting and it consists of these files:

§         CTimecodeData.cls

§         CTimecodeWorksheet.cls

§         ModGlobal.bas

§         TimecodeAutomation.vbp (project file)

They are all available in Sourcesafe at $/Technical Documentation/Department Documentation/Product Quality/Project Administration/TimeCodeAutomation.