@Fikzy
Find below my php code , sorry that it's a bit long. Also find attached a sample picture of how the report looks like. From the picture you can see that ADN for BG start and end date is Sept 13, 2007 to Oct 13 2007, thus week 42 (Oct 14 - Oct 20) should ideally be 0 (more explanation in the code below). Hope you can help me out.
Thanks
<?php
/**
* Graph
*
* This page display tool forecast report for clients
*
* @package TTS
* @author Ashley Akinpelu
*/
session_start();
header("Cache-control: private");
if (!isset($_SESSION['initiated']))
{
session_regenerate_id();
$_SESSION['initiated']=true;
}
/** mysql functions */
require_once 'mysqlfunctions.php';
//getting database link
$dblink=dbconnect();
/** Site wide functions */
include "functions.php";
/** include standard header file and defines common <head> properties */
$heading="Tool Tracking System | Reports | All Tools";
$topic="Report - All Tools (Client Listing)";
include "header.php";
$admin=$_SESSION['Admin'];
$name=$_SESSION['Name'];
/** File that checks for legitimate login */
include "security.php";
$table="forecast";
$begin=$_SESSION['ccst'];//begin date
$by=substr($begin, 0, 4);//begin year
$bm=substr($begin, 5, 2);//begin month
$bd=substr($begin, 8, 2);//begin day
$end=$_SESSION['ccfh'];//end date
$lbegin=$_SESSION['cclst'];//label begin date
$lend=$_SESSION['cclfh'];//lable end date
$beginw=$_GET['bwk'];//begin week
$endw=$_GET['ewk'];//end week
//writing select query to get row count for date range
$ctsql="SELECT count(*) as ToT FROM $table WHERE start>='$begin' AND end<='$end'";
//executing the select query
$ctres=mysql_query($ctsql,$dblink);
$ctrow=mysql_fetch_array($ctres);
//if row count is zero, no data
if ($ctrow[ToT]==0)
{
echo "<script>alert('Null data was returned from the database for the date range selected. Please select a new date range.')</script>";
echo "<script>window.document.location='report.php'</script>";
}
else
{
$wks=array();
$wkval=array();
//put the week numbers for the date range in an array
for ($week=$beginw; $week<=$endw; $week++)
{
$wks[]=($week);
}
$cwks=count($wks);
if($cwks>10)
{
echo "<script>alert('Please select a new date range that do not span over more than 2 months.')</script>";
echo "<script>window.document.location='report.php'</script>";
}
else
{
for ($i=0; $i<$cwks; $i++)
{
$x=6;
//Timestamp of begin and end dates
$timestamp=mktime(1,1,1,$bm,$bd,$by);
//retrieve week for the begin and end dates
$beginDay=date("w",$timestamp);
//get first day of the begin weeks
$timestampbegin[$i]=$timestamp-(60*60*24)*$beginDay;
$timestampbeginSat[$i]=$timestampbegin[$i]+($x*24 * 60 * 60);
if ($i>0 and $i<$cwks)
{
$y=($i*$x)+$i;
$timestampbegin[$i]=$timestampbegin[$i]+($y*24 * 60 * 60);
$timestampbeginSat[$i]=$timestampbegin[$i]+($x*24 * 60 * 60);
}
$bw[$i]=date("Y-m-d",$timestampbegin[$i]);
$ew[$i]=date("Y-m-d",$timestampbeginSat[$i]);
}
//writing select query to get client that have records for the selected date range
$query="SELECT client_name, count(comp_name) as NumTtl FROM $table WHERE start>='$begin' AND end<='$end'
GROUP BY client_name ORDER BY client_name";
//executing the select query
$result=mysql_query($query,$dblink);
$topic1="$name";
?>
<link href="css/tts.css" rel="stylesheet" type="text/css">
<script type="text/javascript" language="javascript" src="javascript/js.js"></script>
</head>
<body>
<div id="container">
<div id="wcome">
<?php
echo $topic1;
?>
</div>
<div id="header">
<?php
echo $topic;
?>
</div>
<div id="date">
<?php
echo date("F j, Y");
?>
</div>
<div id="center">
<form action="<?php echo $_server['php_self'];?>" method="post" name="aform">
<div class="article">
<?php
echo "<u><b>Date Range:</b> $lbegin - $lend</u><br><br>";
while($row=mysql_fetch_array($result))
{
$clnt=$row[0];
$ent=$row[1];
if ($ent==1)
{
$entf=$ent.' entry found:';
}
if ($ent>1)
{
$entf=$ent.' entries found:';
}
echo "<div class=\"entryH\">$clnt</div>";
echo "<div class=\"entry\">$entf</div>";
//for each cleint name returned get some data from the database
$sql="SELECT comp_name, tool_size, hole_size, func, start, end FROM $table WHERE client_name='$clnt' AND start>='$begin'
AND end<='$end'";
$number=0;
//executing the select query
$res=mysql_query($sql, $dblink) or die("query failed : " . mysql_error());
echo "<table class=\"tab\">
<tr class=\"colHead\">
<td>Name</td>
<td>Tool Size</td>
<td>Hole Size</td>
<td>Avail</td>";
//create td for the numbers of weeks in the date range
for ($i=0; $i<$cwks; $i++)
{
echo "<td>Week $wks[$i]</td>";
}
echo "</tr>";
while($nrow=mysql_fetch_array($res))
{
$compn=$nrow[0];
$tsize=$nrow[1];
$hsize=$nrow[2];
$func=$nrow[3];
$st=$nrow[4];
$ed=$nrow[5];
$number++;
$status=(1 & $number) ? 'Odd' : 'Even';
for ($i=0; $i<$cwks; $i++)
{
/*
this is the problem query below*/
//query to get the number of tools that falls within the date range for each comp_name of each client. if a week falls outside the selected time range it should return null
$wkquery="SELECT number from $table WHERE (('$st'>='$bw[$i]' OR '$st'<='$ew[$i]') AND ('$ed'>='$bw[$i]'
OR '$ed'<='$ew[$i]')) AND comp_name='$compn' AND tool_size='$tsize' AND hole_size='$hsize' AND func='$func'
AND start='$st' AND end= '$ed'";
$wkres=mysql_query($wkquery, $dblink) or die("query failed : " . mysql_error());
$wkrow=mysql_fetch_array($wkres);
if($wkrow[0]==null)
{
$numb[$i]=0;
}
else
{
$numb[$i]=$wkrow[0];
$diff=$func-$numb[$i];
if($diff<0 and $numb[$i]>0)
{
$bg="#FF0000";
}
}
}
if ($status=='Odd')
{
echo "<tr class=\"odd\">";
}
else
{
echo "<tr class=\"even\">";
}
echo " <td >$compn</td>
<td >$tsize</td>
<td >$hsize</td>
<td>$func</td>";
for ($i=0; $i<$cwks; $i++)
{
if($numb[$i]==0)
{
echo "<td>$numb[$i]</td>";
}
else
{
echo "<td bgcolor=\"$bg\">$numb[$i]</td>";
}
}
echo "</tr>";
}
echo "</table>";
}
echo "</table>";
}
}
?>
</div>
</form>
</div>
<div id="menu">
<?php
if($admin=="Y")
{
menuA();
}
else if($admin=="N")
{
menuO();
}
?>
</div>
<div id="footer">
Designed by <b>Ashley Akinpelu</b>.<br>Email: <a href="mailto:g99ma@mun.ca" class="footer">
g99ma@mun.ca</a>
</div>
</div>
</body>
</html>
![Nairaland [Nigerian Forum] Home](http://www.nairaland.com/Themes/default/images/english/home.gif)
Author
pic.jpg (75.7 KB, 1280x800 )
