Login

Time Is Money (part 2)

In this concluding article, explore the scripts which add and
remove timesheet entries to the system, and get a crash course in
statistics by using these entries to generate useful resource allocation
and usage reports.
In the first
part of this article, I explained the need and rationale for a timesheet
application to log and analyze employee work hours, and put together a set of
functional requirements for this application. I then constructed a database
schema to address my requirements, designed a simple user interface, and wrote a
few of the basic scripts required by such a system.

The job’s nowhere
near complete, though – I have yet to write the code which accepts and validates
user input, displays timesheet entries, and analyzes the raw data to create a
useful report. I plan to address all these items, and a few more, over the next
few pages. So keep reading.{mospagebreak title=Getting Creative} If you look at
the main menu, you’ll see that the third menu item allows the user to view
entries for a specified date. When you analyze this a little, though, you’ll see
that this menu item has broader implications – it must allow the user
to:

view a list of timesheet entries for the specified date;

add
new entries for the specified date;

delete existing entries from the
list.

In order to perform these functions, the script – I’ve called it
“view.php” – must necessarily receive a valid datestamp (created from the
variables $d, $m and $y). These three variables are generated from the drop-down
boxes in “menu.php” – you may remember this from last time’s article (if not,
I’d strongly suggest you review the source code for “menu.php” and then come
back here). Once “view.php” receives these values, it generates a datestamp,
checks to ensure that the date is a valid one, and then queries the “log” table
for all relevant data.

So that takes care of displaying existing entries.
But what about adding new ones, or deleting existing ones?

Well, after
much thought and a couple of conversations with our resident interface designer,
I’ve come up with a clever way to combine both these functions into “view.php”.
Here’s a rough sketch of what I have in mind:

Essentially, I plan to split the page into two main
sections. The left side of the page will contain a list of entries retrieved
from the database, with a checkbox next to each; the user can select an entry
for deletion by checking the corresponding box. The right side of the page will
hold a form, which can be used to add new entries to the timesheet. Entries
added from the form on the right side will immediately appear in the list on the
left side.

This sounds complicated, but it’s actually not all that
difficult to implement. The important thing here is to take the pieces one at a
time and deal with them separately. And so, the first thing I need to do is set
up a page template.

The script begins with the usual checks and includes, and
generates an HTML page containing a (1r, 3c) table. The first and last of these
cells will be used for the existing and new timesheet data
respectively.

Note the checkdate() function at the top of the script –
I’m using this to validate the date value received by “view.php”. This check is
necessary to catch incorrect date values – for example, 30 February or 31 April.
If the date selected by the user is invalid, the script will redirect to the
generic error handler, which should display a message indicating the
error.{mospagebreak title=Split Personality} Let’s now look at the code which
connects to the database and retrieves a list of entries, given the user and
date.

1.
The first order of business is to open a connection to the database and retrieve
a list of entries for this user and this date; I’ve used a join so that I can
display descriptive project and task names rather than meaningless IDs.

4. With all that out of the way, I’m iterating through the
resultset to display each entry, complete with project name, task name and hours
worked. Since I want to allow the user to delete selected entries, a form
checkbox is added next to each entry as well.

Each iteration also adds
the current hours value to the $totalHours value, which is displayed in a
separate row once the resultset has been completely processed.

When the user hits the delete button and submits the form,
the selected entries will be passed to the form processor “delete.php” as an
array – this array is named $lid (you can see it attached to each checkbox
above) and contains the unique record identifier for each selected entry. The
“delete.php” processor will use this information to identify which records are
to be deleted from the “log” table.

5. Finally, I’m including the three
date variables $d, $m and $y in the form as hidden values. My intent here is to
pass these values to the “delete.php” script when the form is submitted;
“delete.php”, in turn, will use them to redirect the browser back to the correct
“view.php” instance.

This is much simpler to read and understand than the previous
listing. Essentially, I’m setting up drop-down boxes for the various project,
task and hour values (in increments of 0.5), and inviting the user to add an
entry to the timesheet by simply selecting appropriate values from each. I’m
also including the three hidden date variables in the form, for the same reasons
previously stated.

Here’s what this half of the page looks
like:

And here’s what the complete product
looks like:

In case you’re still confused,
don’t give up just yet – the next couple of scripts should make things clearer.
{mospagebreak title=In…} The two halves of “view.php” correspond to two
different form processors, “delete.php” and “add.php” respectively. Let’s look
at “add.php” first.

The data received from the form in “view.php” – project, task
and hour values – is used by this script to formulate an INSERT query and insert
the entry into the “log” table. The $SESSION_UID variable is used to identify
which user this entry belongs to.

Once the data has been inserted, the
browser is redirected back to the page it came from, using the datestamp values
passed to it from “view.php”.{mospagebreak title=…And Out} The opposite of
“add.php” is “delete.php”, which is designed to accept a list of record
identifiers and delete the corresponding records from the “log” table. An array
of these identifiers is passed to the script via the $lid array – take a look:

Here too, once the deletion has been accomplished, the user
is taken back to the originating instance of “view.php”{mospagebreak title=The Number Game} Yes, I know that was a little complicated, but you have to admit it
works like a charm. One of the major battles is now behind us, with one still
ahead – report generation.

Before I start writing code, I’d like to spend
a little time analyzing the problem ahead. First, there are innumerable types of
reports possible with the data available to us. Numbers can be massaged and
interpreted in different ways to produce different conclusions (as Disraeli
famously said, “there are lies, damned lies and statistics”) and so it’s
essential (especially since I’m running out of time) to identify which types of
reports are most useful, and concentrate my efforts on those.

In order to
get a better feel for the problem, I decide to turn to the Boss for help. After
all, he runs a company too – surely he’ll have some idea of the kind of data
that would be most helpful in making business decisions on resource allocation
and usage. And he does…

“Well, lemme see,” he drawls, spinning in his
swivel chair to look out of the window. “The most important thing, in my
opinion, would be to see the amount of time spent on the different tasks within
a project. I’d find it very useful to know, for example, how much time you spend
on code design and development, versus the amount of time the System Test people
take to test your code. If I can break up a project into tasks and attach a
dollar value to the hours spent on each task, I can then calculate the total
time and money spent on the project, compare it with the revenue earned, and
find out if we’re actually making any money!” (this last accompanied with a fist
slamming down on the table.)

“Next, ” he continues, calming down a
little, “I want to see a breakdown of hours spent on a project by user, so that
I know who’s pulling the weight and who’s slacking off. I notice that you, for
example, spend an inordinate amount of time at lunch – I’d be very curious to
see how those hours are logged.”

So he’s been keeping tabs on me, huh?
Gotta wonder how this guy has time to manage a company, given the amount of time
he spends keeping track of employee lunch hours…

But the Boss is just
warming up.

“And, since I’ve also happened to notice that this company’s
output drops dramatically every time I have to go out of town, I’d very much
like the ability to see a big-picture overview for a specific period of time –
this should show me the time spent on *all* our active customers, broken down by
user and task. And you know something – I bet that if I compare that data for
the weeks I’m in the office and the weeks I’m out of town, I’d be able to draw
some interesting conclusions,” smirking away like he’s just thought of something
funny.

Right. I’ve just about had enough of this guy. So I do the only
thing that occurs to me – I stand up, walk around the desk, tip his swivel chair
backwards and, as he flaps around helplessly on the floor, turn on my heel, and
stride through the door with my long black coat flowing behind me like a
cape…{mospagebreak title=Exercising Restraint} No, I didn’t really do that – I
still need a paycheck. But every dog has his day, and mine will come
soon…

Anyway, disturbing though that conversation was, I still managed
to get some useful data from it. I can now state with certainty that the system
should generate the following three types of reports:

a big-picture
overview of the time spent by users on all active projects for a specific period
of time,

a big-picture overview of the time spent on different tasks over
a specific period of time;

a focused report of time spent by different
users on the different components of a project for a specific
period.

With this in mind, let’s quickly review the code for the report
generation menu item in “menu.php”

This has all the data I need to build any of the three report
types – a starting date (created from the date variables $sd, $sm and $sy), an
ending date (created from the date variables $ed, $em and $ey) and a project
identifier (which may be 0 for “all projects”). When this form is submitted, it
sends these variables to the script “report.php”, which performs the actual
report generation.

If you look at “report.php”, you’ll see that it’s
basically one gigantic “if” loop – the first part generates big-picture type
reports, while the second part generates more specific reports.

Let’s write some code for the general report first – once
that’s done, writing code to build a report for a specific project should be a
piece of cake.{mospagebreak title=The Big Picture…} My general report, such as
it is, is going to look something like this:

Since I’m going to be using a table to display the
report, it helps to think of this in terms of rows and columns. The first row
contains a list of users; every subsequent row represents a new project, with
the numbers in the columns representing the total hours worked by that user on
that project. The last column display the row totals (total time spent by all
users on a specific project), while the last row contains column totals (total
time spent on all projects by a specific user).

Putting together the
first row should be simple enough – simply query the database for a list of
users and print them.

For each project (row) in this list, I need to look up the
total hours worked by each user (column) and print these numbers as cells. As I
obtain each cell total, I need to add that number to the appropriate row and
column total variable, for use at the end of every row and column respectively.

Now, on this same page, I need to include the second
type of report, this one offering a breakdown of projects versus tasks. The code
is identical to what you’ve just seen – simply replace all references to users
with corresponding references to tasks.

Suitable for printing or framing, eh?{mospagebreak title=…And The Little Brush Strokes} That takes care of the first half of the
“if” loop – the “gimme-a-picture-of-all-projects” report. But while the
Customer’s HRD minions will be deliriously happy with this information, the
Billing guys have yet to be satisfied. My next (and final) task, therefore, is
to wrap things up by filling in the second half of that “if” loop and generating
a focused report for a specific project across a specific time
period.

Given what I’ve just accomplished, this should be a snap,
especially if I treat it as a subset of the general report above. Here’s what I
anticipate it will look like:

Since this is a report for a specific project, I can assume
that the variable $pid will have a value other than 0. That said, the procedure
is almost identical to that used in the general report, except that in this
case, the users are represented by columns and the tasks by rows. As always,
I’ll generate the top row first, querying the table for a list of users and
displaying them. Next, I get a list of tasks and, for each task-user combination
for the given project, calculate the total hours worked.

Two arrays,
$columnTotals and $rowTotals, indexed by user ID and task ID respectively, hold
the total hours worked on both axes, and are used to build the last column and
row of the table.

Once this report has been generated, I’d also like to
print two summary reports, one listing the total hours worked by each user on
the project, and the other listing the total time spent on each task within the
project. These summary reports are essentially the row and column totals, which
I’m displaying again to make the data easier to analyze – both the Boss and I
concur that these summaries will probably be the most valuable bits of the
report, as they provide a bird’s-eye view of resource allocation across tasks
and users in a project.

Here’s the code to generate these two summary
tables (remember, these are just the column and row totals which have already
been calculated above):

{mospagebreak title=When Things Go Wrong} The last script –
another extremely simple one – is the error handler, “error.php”. If you look at
the source code, you’ll notice many links to this script, each one passing it a
cryptic error code via the $ec variable. Very simply, “error.php” intercepts the
variable and converts it to a human-readable error message, which is then
displayed to the user.

Simple and elegant – not to mention flexible. Found a
new error? No problem – assign it an error code and let “error.php”
know.{mospagebreak title=Happy Endings} And that just about concludes this case
study. Throughout this development effort, I have made widespread use of PHP’s
session management capabilities, date and string functions, HTTP header
functions, and database access capabilities. If you are new to PHP, I hope that
the effort has been instructive, and that it has helped you gain a greater
understanding of these powerful open-source tools.

If you’d like to learn
more about some of the issues, techniques and functions described throughout the
course of this article, here are a few links:

Despite
my protestations to the contrary (and my fear that I’m going to have to cut
short my overly-long lunch hour once the Boss gets his hands on it), I believe
that a tool such as the one described over the preceding pages offers tremendous
benefits to any organization in its efforts to streamline business processes and
allocate resources more efficiently. By obtaining and storing information in
electronic format, it reduces paperwork and simplifies resource accounting; by
imposing a structure on user information, it makes it easier and quicker to
locate, present and analyze raw data; and by using a database, it ensures that
data does not get corrupted.

It should be noted also that this is an
initial release of the application, and I expect it to evolve further, with new
features being added and old features being upgraded. It’s always a good idea to
review both design and code as the application evolves – I plan to do this a
little further down the road, and to make changes to both the database schema
and the scripts themselves. This process should take place in conjunction with
the development plan for new features, so that the addition of new features does
not add to overhead and cause performance degradation.

That said, let me
also add that when I delivered the finished product to the Customer at his
uptown office (a couple hours ahead of deadline), he was thrilled to bits, and
the severe HRD minions looked delirious with delight. In fact, the Customer was
so impressed that, in the five minutes I spent alone in his office,
he:

offered me one of his foul-smelling cigars (which I politely
declined);

offered me a job (which I also politely declined);

and
offered us a long-term software development contract (which I was happy to
accept on behalf of the Boss);

Needless to say, the Boss was equally
thrilled with the outcome and indicated that I would shortly be receiving a
bonus and some vacation time for my efforts…naturally, after executing the
Customer’s next project. In fact, the Boss said, he was thinking of making me
point man for the Customer’s entire development contract, a statement which made
my blood freeze and my stomach rumble in fear of the impending
ulcers…

What, you were expecting a happy ending? Get real!

Note:
All examples in this article have been tested on Linux/i586 with Apache 1.3.12,
mySQL 3.23 and PHP 4.06. Examples are illustrative only, and are not meant for a
production environment. YMMV!