Our client employs staff who work on various projects (jobs) for their clients. They need to be able to record time and expenses against each job. The system will run on their corporate server, and will operate as an intranet application ??" staff will access the system using Internet Explorer or other web browser. The development has already been started and an initial prototype exists, but we have taken on another project, so this one now needs completing. The platform is ASP.NET, XMLdocument, XSL and MS SQLServer 2000.
Job and Time Recording System Author: Richard Borrie, Amarsys Ltd Date: 25th March 2008 1 INTRODUCTION Our client employs staff who work on various projects (jobs) for their clients. They need to be able to record time and expenses against each job. The system will run on their corporate server, and will operate as an intranet application ??" staff will access the system using Internet Explorer or other web browser. There will be two levels of user in the system ??" administrators and standard users. The former will have access to additional maintenance functions. The development has already been started and an initial prototype exists, but we have taken on another project, so this one now needs completing. 2 PAGES Since the system will exist as an intranet application, it is perhaps best to describe the functionality in terms of pages. We still have some further items to agree with the client, but we believe this specification is about 80% complete. 2.1 LOGIN There will be a simple login process based on email address and a basic password. Since the system is only intended to operate within the corporate network, security is not a big issue. User validation should use Forms Authentication or similar, so that the user’s existing Windows Server account can be used for automatic login. Alternatively, an email-address login would be acceptable. Users will be validated against a staff table in the database. Users will be categorized as “standard?? or “administrator??. Administrators will have access to some additional menu items. All subsequent pages in the site must check that the user is logged in, and if not redirect the user back to the login page. Pages which are for administrator access only must additionally check that the logged-in user is an Administrator. 2.2 JOBS LISTING After login the main “home page?? will be a listing of jobs. A job is a project or task which staff can book time and expenses to. This can be searched, sorted, and selected in various ways. Jobs which are nearing their fee ceiling will be highlighted. The job listing will include hyperlinks to the individual job record and the client record of that job. There will also be a facility to create a new job. 2.3 JOB DETAILS This page will allow all users to maintain the details of individual jobs, including data such as the client, lead consultant, job status and so on. There will be a separate sub-page where job budgets can be recorded ??" there could be more than one budget (i.e. additional budgets may be agreed later in the life of the job). The job budget will include records for the agreed charge scales for different staff categories (Associate, Graduate, Student etc.) for the job. All job budgets would include a default “warning?? threshold set to a default (say 80%) percentage of the overall budget. The system would therefore be able to categorise jobs as “in progress??, “at threshold?? or “over budget??. The budget listing will include cumulative fees (hours x charge scales) and cumulative expenses. This page will also include the facility to create a new job. It will also be possible to delete jobs … but only those that do not have time already booked against them. A “job sheet?? page will show the current status of the job. This is essentially a single page on-screen report showing dates in the rows (earliest to latest) and staff names in the columns. For each person who has worked on the job there will be two columns, one showing the hours on that date and one showing the cumulative hours. A separate block at the bottom of the page will show the expenses booked. This job sheet page reproduces an existing printed report. We intend to create a database stored procedure which will do most of the work to create this report. Further sub-pages will show: • Time records booked against the job (listing + option to delete a record) • Expense records booked against the job (listing + option to delete a record) • Contacts identified against this job (listing + option to delete and add a record) 2.4 CLIENT LISTING This page will list all clients, with various search and sort options. 2.5 CLIENT DETAILS This page will allow users to maintain details of individual clients, and create new client records. A secondary tab page will show all current and archived jobs for this client, with the facility to drill-down into the job (see Job Details above). A further tab will show contacts linked to this client (see below). A further tab will list all invoices (hyperlinked to the relevant job). 2.6 BOOK TIME This is the most important page in the system. It will allow a user to book time against a job. The page will have several sections: 1) The user selection drop-down (to select the current staff record) 2) Table of previous time booked 3) Timesheet input form 4) Timesheet listing section These sections are now described in more detail. The top of the page will include a drop-down list of staff (1) ??" so that anyone can book time on behalf of someone else. However it will default to the logged-in user. The input page will be in two sections. The first section (2) will be a table listing the current and previous week (i.e. 10 columns of 2 x 5 days) across the top, with the list of all current jobs in rows. The intersection will show the total time booked by the selected person (which will default to the logged-in person). This will show the daily and weekly total time booked at a glance. We think that the data for this table can be provided through a database stored procedure. The second section (3) is where the time can be booked. It will consist of a simple data-entry form as follows: • A drop-down list of all current jobs which the user can select (time can only be booked against open jobs). • A drop-down list of task durations in 15 minute increments. • A drop-down list of dates stretching back over the previous 2 weeks or so. • A text box for a free-text comment. The idea is to minimize data-entry errors by using drop-down selection lists rather than free text where possible. The final section (4) will list the timesheet records entered today, with an option to delete a record. Non-administrator users can only edit their timesheet records on the day they have been entered. We would probably create an internal client called “Internal Jobs?? to allow time to be booked on non-client jobs such as holidays or sickness. The system could optionally create an email alert to the Administrator when someone books time (or expenses) on to a job which would cause the job to reach its budget threshold. Note that there is no facility on this page to delete timesheet records older than today ??" although an Administrator can do so elsewhere. 2.7 BOOK EXPENSES This page will be similar to the time-entry, with the section (2) containing the table of expenses booked over the last 2 weeks, and the section (3) allowing input of new expenses. The input form (3) will include a drop-down list of the various expense types ??" again, the intention is that users will only be able to enter valid data. The system will remember the last job from the timesheet entry and show this by default as the one to book expenses against. However each expense line will allow the user to select from all the available current jobs, so it will be possible to book, say, a car park ticket against one job and then a hotel bill against another job on the same form. A flag will indicate if the expense is chargeable to the client or not. (All expenses relate to a job but not all expenses are chargeable). The system will work out the gross, tax, and nett amounts. Mileage rates will be held in the database (i.e. the charge per mile). 2.8 TIMESHEET LISTING Time records would be stored in a database table and viewable in the timesheet listing page. This page would show the following for each timesheet record: • Job • Client • Staff • Time and cost (at booked rate) The timesheet listing would be searchable by a number of criteria, including job (to view all time booked to a job) and staff (to show all time booked by a member of staff). It would also be possible to select date ranges. This data is clearly important for productivity and client purposes, and would therefore be available for export to Excel spreadsheet format for further analysis. There will be a delete option alongside each record. However, only Administrators will be able to see this option. 2.9 EXPENSE LISTING This would be a similar listing to the timesheet listing, showing expense items and searchable by staff and job. There will be a delete option alongside each record. However, only Administrators will be able to see this option. 2.10 CONTACTS The system would maintain a list of contacts and hold basic contact details, with the usual facilities for searching and selecting records. 2.11 CONTACT DETAILS This would be a simple record of contacts relating to the job, including telephone, email and so on. Again the idea is to improve visibility ??" any member of staff would be able to view the contact details and therefore be productive if the lead consultant is unavailable. Contacts could include client contacts or other contacts relating to a job (case officer at the local authority etc.). Therefore contacts can be optionally linked to one or several jobs, and/or one or several clients. Clients that are linked to jobs would appear in the job details, and contacts linked to clients would appear in the client details. 2.12 STAFF LISTING This page will only be accessible to users who are Administrators. It will list all staff in the system, with various search and sort options. By default it will only list current staff. Hyperlinks will take the user to the staff details page. 2.13 STAFF DETAILS The Administrator will be able to edit the details of the individual staff record. Further tabs will allow the Administrator to view time records and expense records of this staff record. 2.14 REPORTING There will not be many formal “reports?? in the system. The idea is to keep it interactive and proactive, rather than producing paper reports. As noted above the main reports in the system will be the on-screen list of jobs which will highlight jobs at various delivery and budgetary stages, and the list of timesheet records. This data can be extracted into Excel for further analysis. 3 TIMESCALES AND IMPLEMENTATION 3.1 PROJECT TIMESCALES We have agreed a prototyping methodology with our client, therefore we will need to install semi-working prototypes early in the life of the project so that they can see how the system is being built. The project must be completed by Friday 25th April 2008, since system testing on the client premises will start the following week. We will refer back to you for any errors found during system testing during May. After that we will take-over all support of the system so your responsibility will then finish. Therefore the deliverable stages are: 1) Initial basic prototype running on our server - jobs list and job details pages etc. (11th ??" 18th April) 2) Further working prototypes running on our server with additional functionality ??" to be agreed with the coder 3) Working system ready for client testing (2nd May 2008). 4) Final handover (May 2008) We don’t require any documentation. 3.2 ROLES AND JOINT WORKING As already noted, the application development has already been started and we have an initial prototype which now needs to be completed. We will provide the database design and construction, system testing, general website design, and page framework. You will build the individual programs. In essence we will create the back-end and you will create the front-end. Essential: • You will be given access to our test database server (MS SQLServer 2000), and you will therefore be able to develop programs on your system whilst accessing the database on our server. It is essential that you work against our test database. • All programs must be developed using our page framework. Our page framework uses the .Net 2 XMLdocument and XSLT. It allows us to separate the programming from the user interface. Programs manipulate an XMLdocument object, and then output the final page via XSL transformation. We will provide some example programs so that you can see if you would be happy to work this way … if you like the “purity?? of XML then you probably will, if you prefer traditional ASP.NET inline HTML and script then you probably won’t. • You should have a static IP address. Desirable: • Ideally you should use our SubVersion version control system (e.g. TortoiseSVN). • The preferred development language is VB but we will consider C#. We use Visual Web Developer Express. • The main skills you will need are ASP.NET, MS SQLServer and basic XSLT. • We will create database stored procedures for all main database update processes, but if you are interested in doing this please let us know. At the start of the project we will provide: • The existing prototype ASP.NET application, including directory tree, CSS etc. • Prototype programs showing you how to list staff records and then edit and update an individual staff record ??" using our XML page framework. Most of the development process is simply a matter of extending this to handle other areas of the application (jobs, clients etc.). • Access to MS SQLServer 2000 database with test data, and table structures which will be about 80% complete. We will also deal with the client, and install the system on their server and test it. As already mentioned, we have agreed a prototyping methodology with the client so we will install the initial prototype quite soon after the project commences. So all you will need to do is familiarize yourself with our database and page framework, and then code the pages listed above. All materials must be kept confidential. 4 RELATED DOCUMENTATION We have included or can supply on request the following documentation for this project: • Summary data model diagram • Example page layout diagrams for many of the main pages NB. We believe that we have captured the main elements of our client’s requirement, and we think the main database tables, workflow processes, and page layouts are about 80% complete. This additional documentation illustrates the project requirements, but will need further interpretation. It is likely that additional fields will be required in each table (which will then need to be maintained on the relevant input form or listed on the relevant listing page) so you should allow for this in your bid. However we do not expect that any additional pages will be needed (although some additional sub-pages may be required).