Time Is Money (part 2) - ...And The Little Brush Strokes (
Page 9 of 11 )
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:

And here's
the code to accomplish it:
<!-- tasks vs. users table for a specific project -->
<table width=100% border="0" cellspacing="2" cellpadding="5">
<tr>
<td> </td>
<?
// get user list
// this resultset is useful during report generation, so make sure that it
is retained!
$query = "SELECT uid, uname FROM users";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
// print users in top row
while (list($uid, $uname) = mysql_fetch_row($result))
{
echo "<td valign=top align=center><font color=#D03468>$uname</font></td>";
}
?>
<td> </td>
</tr>
<?
// create variables to hold row and column totals (useful later)
$rowTotals = array();
$columnTotals = array();
// get list of tasks
$query2 = "SELECT tid, tname FROM tasks";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error
in query: $query2 . " . mysql_error());
// iterate through resultset
while (list($tid, $tname) = mysql_fetch_row($result2))
{
echo "<tr>";
echo "<td valign=top align=left><font color=#D03468>$tname</font></td>";
mysql_data_seek($result, 0);
// for each task-user combination
while (list($uid, $uname) = mysql_fetch_row($result))
{
// get intersection
$query3 = "SELECT SUM(hours) from log WHERE pid = '$pid' AND tid = '$tid'
AND uid = '$uid' AND date >= '$sdate' AND date <= '$edate'";
$result3 = mysql_db_query($database, $query3, $connection) or die ("Error
in query: $query3 . " . mysql_error());
list($sum) = mysql_fetch_row($result3);
// correction
if (!$sum) { $sum = 0; }
// keep track of totals
$rowTotals[$tid] = $rowTotals[$tid] + $sum;
$columnTotals[$uid] = $columnTotals[$uid] + $sum;
// print value
echo "<td valign=top align=center>" . sprintf("%1.01f", $sum) . "</td>";
}
// print row total
echo "<td valign=top align=center><b>" . sprintf("%1.01f",
$rowTotals[$tid]) . "</b></td>";
echo "</tr>";
}
?>
<tr>
<td> </td>
<?
// back to top of user list
mysql_data_seek($result, 0);
// print column totals
while (list($uid, $uname) = mysql_fetch_row($result))
{
$sumOfColumnTotals = $sumOfColumnTotals + $columnTotals[$uid];
echo "<td valign=top align=center><b>" . sprintf("%1.01f",
$columnTotals[$uid]) . "</b></td>";
}
// print grand total
echo "<td valign=top align=center><b>" . sprintf("%1.01f",
$sumOfColumnTotals) . "</b></td>";
?>
</tr>
</table>
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):
<!-- tasks summary - these are the row totals -->
<table border="0" cellspacing="2" cellpadding="5">
<tr>
<td valign=top><font color=#D03468>Task</font></td>
<td valign=top align=center><font color=#D03468>Hours</td>
</tr>
<?
// iterate through task list
mysql_data_seek($result2, 0);
// for each task, get corresponding row total and print
while (list($tid, $tname) = mysql_fetch_row($result2))
{
$sumOfRowTotals = $sumOfRowTotals + $rowTotals[$tid];
echo "<tr>";
echo "<td valign=top align=left>$tname</td>";
echo "<td valign=top align=center>" . sprintf("%1.01f", $rowTotals[$tid])
. "</td>";
echo "</tr>";
}
?>
<tr>
<td> </td>
<td valign="top" align="center"><hr><b><? echo sprintf("%1.01f",
$sumOfRowTotals); ?></b></td>
</tr>
</table>
<!-- user hours summary - these are the column totals -->
<table border="0" cellspacing="2" cellpadding="5">
<tr>
<td valign=top><font color=#D03468>User</font></td>
<td valign=top align=center><font color=#D03468>Hours</td>
</tr>
<?
// iterate through user list
mysql_data_seek($result, 0);
// print column totals
while (list($uid, $uname) = mysql_fetch_row($result))
{
echo "<tr>";
echo "<td valign=top align=left>$uname</td>";
echo "<td valign=top align=center>" . sprintf("%1.01f",
$columnTotals[$uid]) . "</td>";
echo "</tr>";
}
?>
<tr>
<td> </td>
<td valign="top" align="center"><hr><b><? echo sprintf("%1.01f",
$sumOfColumnTotals); ?></b></td>
</tr>
</table>
And here's the finished product looks like: