| JacKDynne |
8th Apr 04 01:39 PM |
Here's my problem:
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.
Heres the questions: :D
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... :blink:
Any input is appreciated *TIA* :)
The code for the report is included below and here is a screenshot of the generated report:
Code:
<?
// 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>
|
