I have a web based timesheet app running with php and mysql. When I run reports on specified date ranges, it pulls the entire user db (not just the department that runs the report) and puts it in a row across the top which is really wide (I have to scroll over from the bottom). What happens is that unless I copy the entire page and insert into an excel file the majority of the users are cut off when I print straight from the browser. Big problem 'cause my users are not literate enough to cut and paste as I did.
1. Is there any way to have the html print correctly (either by wrapping or some other means)?
2.What could I include in the report to only print users time from specific departments? (doesn't really fix the problem for big departments though)?
I am trying to flip the Users/Tasks sections so that the tasks are printed in the top row then the users are printed from top to bottom but no luck getting it right thus far...
The code for the report is included below and here is a screenshot of the generated report:
<?
// report.php - generate reports
// includes
include("config.php");
include("functions.php");
// assign global variables for PHP 4.1+ with register_globals disabled
$sm = $_REQUEST['sm'];
$sd = $_REQUEST['sd'];
$sy = $_REQUEST['sy'];
$em = $_REQUEST['em'];
$ed = $_REQUEST['ed'];
$ey = $_REQUEST['ey'];
$pid = $_REQUEST['pid'];
$pname = $_REQUEST['pname'];
// check for valid session and valid administrator
session_start();
if(!session_is_registered("SESSION_UID") || $_SESSION['SESSION_UPERMS'] > 2 )
{
header("Location: error.php?ec=1");
exit;
}
// check for valid dates
if (!checkdate($sm, $sd, $sy) || !checkdate($em, $ed, $ey))
{
header("Location: error.php?ec=2");
exit;
}
// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
// create start and end datestamp
$sdate = $sy . "-" . $sm . "-" . $sd;
$edate = $ey . "-" . $em . "-" . $ed;
?>
<html>
<head>
<basefont face="Verdana">
<style type="text/css">
TD {font-family: Verdana; font-size: smaller}
</style>
</head>
<body bgcolor="FFFF99">
<?
// display page header
$title = "<a style=color:white href=menu.php>Main Menu</a> > Generate Activity Reports";
include("header.inc.php");
?>
<?
// main "if" loop begins
// if report required for ALL projects
if ($pid == 0)
{
?>
<!-- main table -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<!-- title -->
<tr>
<td valign="top" align="left">
<b><font color="#3098C3">Activity report for all projects for the period <? echo fixDate($sdate); ?> to <? echo fixDate($edate); ?></font></b>
</td>
</tr>
<!-- spacer -->
<tr>
<td> </td>
</tr>
<!-- begin projects vs. users section -->
<!-- begin projects vs. users section -->
<tr>
<td valign="top" align="left">
<b><font color="#3098C3"> Projects/Users:</font></b>
</td>
</tr>
<tr>
<td valign="top" align="left">
<!-- projects vs. users table -->
<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 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 a variable to hold column totals
$columnTotals = array();
// get project list
$query2 = "SELECT pid, pname FROM projects";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error());
// do this for each project in project list...
while (list($pid, $pname) = mysql_fetch_row($result2))
{
echo "<tr>";
echo "<td valign=top><font color=#D03468>$pname</font></td>";
$rowTotal = 0;
$count = 0;
// go back to top of user list
// select a uid...
mysql_data_seek($result, 0);
while (list($uid, $uname) = mysql_fetch_row($result))
{
// calculate the sum of the intersection of user and project
$query3 = "SELECT SUM(hours) from log WHERE pid = '$pid' 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 is zero - explicitly assign it 0, so that it gets printed in report
if (!$sum) { $sum = 0; }
// keep track of the row total
$rowTotal = $rowTotal + $sum;
// and the column total
$columnTotals[$count] = $columnTotals[$count] + $sum;
$count++;
// print the number
echo "<td align=center>" . sprintf("%1.02f", $sum) . "</td>";
}
// end of the row, print the row total
echo "<td align=center><b>" . sprintf("%1.02f", $rowTotal) . "</b></td>";
}
echo "</tr>";
?>
<tr>
<?
// last row of table contains column totals
// ie. total hours by this user on all projects
echo "<td> </td>";
// the sum of the column totals gives a grand total
$sumOfColumnTotals = 0;
// print column totals
for ($x=0; $x<sizeof($columnTotals); $x++)
{
$sumOfColumnTotals = $sumOfColumnTotals + $columnTotals[$x];
echo "<td align=center><b>" . sprintf("%1.02f", $columnTotals[$x]) . "</b></td>";
}
// print grand total
echo "<td align=center><b>" . sprintf("%1.02f", $sumOfColumnTotals) . "</b></td>";
// clean up resultsets
mysql_free_result($result);
mysql_free_result($result2);
mysql_free_result($result3);
?>
</tr>
</table>
</td>
</tr>
<!-- end projects vs. users section -->
<!-- end projects vs. users section -->
<!-- spacer -->
<tr>
<td> </td>
</tr>
<!-- begin projects vs. tasks section -->
<!-- begin projects vs. tasks section -->
<tr>
<td valign="top" align="left">
<b><font color="#3098C3"> Projects/Tasks:</font></b>
</td>
</tr>
<tr>
<td>
<!-- projects versus tasks table -->
<table width=100% border="0" cellspacing="2" cellpadding="5">
<tr>
<td> </td>
<?
// get task list
// this resultset is useful further down
$query = "SELECT tid, tname FROM tasks";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
// print tasks in top row
while (list($tid, $tname) = mysql_fetch_row($result))
{
echo "<td valign=top align=center><font color=#D03468>$tname</font></td>";
}
?>
<td> </td>
</tr>
<?
// create variable to hold column totals
$columnTotals = array();
// get project list
$query2 = "SELECT pid, pname FROM projects";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error());
// for each project in list...
while (list($pid, $pname) = mysql_fetch_row($result2))
{
// go back to top of task list
mysql_data_seek($result, 0);
// print project name as first column
echo "<tr>";
echo "<td valign=top><font color=#D03468>$pname</font></td>";
$rowTotal = 0;
$count = 0;
// get intersection of this task and this project
while (list($tid, $tname) = mysql_fetch_row($result))
{
$query3 = "SELECT SUM(hours) from log WHERE pid = '$pid' AND tid = '$tid' 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 for zero values
if (!$sum) { $sum = 0; }
// keep track of totals for later use
$rowTotal = $rowTotal + $sum;
$columnTotals[$count] = $columnTotals[$count] + $sum;
$count++;
// print sum
echo "<td align=center>" . sprintf("%1.02f", $sum) . "</td>";
}
// print row total
echo "<td align=center><b>" . sprintf("%1.02f", $rowTotal) . "</b></td>";
}
echo "</tr>";
?>
<tr>
<?
echo "<td> </td>";
$sumOfColumnTotals = 0;
// print last row - column totals
for ($x=0; $x<sizeof($columnTotals); $x++)
{
$sumOfColumnTotals = $sumOfColumnTotals + $columnTotals[$x];
echo "<td align=center><b>" . sprintf("%1.02f", $columnTotals[$x]) . "</b></td>";
}
// print last cell - grand total aka sum of column totals
echo "<td align=center><b>" . sprintf("%1.02f", $sumOfColumnTotals) . "</b></td>";
// clean up resultsets
mysql_free_result($result);
mysql_free_result($result2);
mysql_free_result($result3);
?>
</tr>
</table>
</td>
</tr>
<!-- end projects vs. tasks section -->
<!-- end projects vs. tasks section -->
</table>
<?
}
// report required for a specific department
else
{
?>
<!-- main table -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<!-- title -->
<tr>
<td valign="top" align="left" colspan=2>
<?
// get name of selected project
$query = "SELECT pname FROM projects WHERE pid = '$pid'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query . " . mysql_error());
$row = mysql_fetch_row($result);
?>
<b><font color="#3098C3">Activity report for "<? echo $row[0]; ?>" for the period <? echo fixDate($sdate); ?> to <? echo fixDate($edate); ?></font></b>
</td>
</tr>
<!-- spacer -->
<tr>
<td colspan=2> </td>
</tr>
<!-- begin tasks vs. users section -->
<!-- begin tasks vs. users section -->
<tr>
<td colspan="2" valign="top" align="left">
<b><font color="#3098C3"> Tasks/Users:</font></b>
</td>
</tr>
<tr>
<td valign="top" align="left" colspan="2">
<!-- tasks vs. users table -->
<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.02f", $sum) . "</td>";
}
// print row total
echo "<td valign=top align=center><b>" . sprintf("%1.02f", $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.02f", $columnTotals[$uid]) . "</b></td>";
}
// print grand total
echo "<td valign=top align=center><b>" . sprintf("%1.02f", $sumOfColumnTotals) . "</b></td>";
?>
</tr>
</table>
</td>
</tr>
<!-- spacer -->
<tr>
<td colspan=2> </td>
</tr>
<tr>
<td colspan=2 valign="top" align="left">
<b><font color="#3098C3"> Tasks/Users Summary:</font></b>
</td>
</tr>
<tr>
<td valign="top" align="left">
<!-- 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.02f", $rowTotals[$tid]) . "</td>";
echo "</tr>";
}
?>
<tr>
<td> </td>
<td valign="top" align="center"><hr><b><? echo sprintf("%1.02f", $sumOfRowTotals); ?></b></td>
</tr>
</table>
</td>
<td valign="top" align="left">
<!-- 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.02f", $columnTotals[$uid]) . "</td>";
echo "</tr>";
}
?>
<tr>
<td> </td>
<td valign="top" align="center"><hr><b><? echo sprintf("%1.02f", $sumOfColumnTotals); ?></b></td>
</tr>
</table>
</td>
</tr>
</table>
<?
// clean up resultsets
mysql_free_result($result);
mysql_free_result($result2);
mysql_free_result($result3);
}
// main "if" loop done - now clean up and print page footer
mysql_close($connection);
?>
<? include("footer.inc.php"); ?>
</body>
</html>