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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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:

>>> More PHP Articles          >>> More By The Disenchanted Developer, (c) Melonfire