Home arrow PHP arrow Page 9 - Time Is Money (part 2)

...And The Little Brush Strokes - PHP

In this concluding article, explore the scripts which add andremove timesheet entries to the system, and get a crash course instatistics by using these entries to generate useful resource allocationand usage reports.

TABLE OF CONTENTS:
  1. Time Is Money (part 2)
  2. Getting Creative
  3. Split Personality
  4. In...
  5. ...And Out
  6. The Number Game
  7. Exercising Restraint
  8. The Big Picture...
  9. ...And The Little Brush Strokes
  10. When Things Go Wrong
  11. Happy Endings
By: The Disenchanted Developer, (c) Melonfire
Rating: starstarstarstarstar / 4
November 16, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: