Its Done!
Code: Select all
<?php
$connection = mysql_connect("localhost","root","") or die ("Couldn't connect to server");
$db=mysql_select_db("timemate", $connection) or die ("Couldn't select database");
$deptid = $_POST['deptid'];
?>
<HTML>
<HEAD>
<TITLE>Leave Gantt!</TITLE>
<link rel="stylesheet" type="text/css" media="all" href="datechooser.css">
<link rel="stylesheet" type="text/css" media="all" href="table.css">
<script type="text/javascript" src="datechooser.js"></script>
<script type="text/javascript">
<!-- //
// DateChooser Code for selecting StartDate and EndDate.
events.add(window, 'load', WindowLoad);
function WindowLoad()
{
var ndExample1 = document.getElementById('datechooserex1');
var ndExample2 = document.getElementById('datechooserex2');
ndExample1.DateChooser = new DateChooser();
ndExample2.DateChooser = new DateChooser();
// Check if the browser has fully loaded the DateChooser object, and supports it.
if (!ndExample1.DateChooser.display || !ndExample2.DateChooser.display)
{
return false;
}
ndExample1.DateChooser.setCloseTime(200);
ndExample1.DateChooser.setXOffset(10);
ndExample1.DateChooser.setYOffset(-10);
ndExample1.DateChooser.setUpdateFunction(FunctionEx1);
document.getElementById('datelinkex1').onclick = ndExample1.DateChooser.display;
ndExample2.DateChooser.setCloseTime(200);
ndExample2.DateChooser.setXOffset(10);
ndExample2.DateChooser.setYOffset(-10);
ndExample2.DateChooser.setUpdateFunction(FunctionEx2);
document.getElementById('datelinkex2').onclick = ndExample2.DateChooser.display;
}
function FunctionEx1(objDate)
{
// objDate is a plain old Date object, with the getPHPDate() property added on.
document.getElementById('dateinputex1').value= objDate.getPHPDate('Y-n-j');
return true;
}
function FunctionEx2(objDate)
{
// objDate is a plain old Date object, with the getPHPDate() property added on.
document.getElementById('dateinputex2').value= objDate.getPHPDate('Y-n-j');
return true;
}
// -->
</script>
</HEAD>
<BODY>
<H1>Leave Tracker</H1>
<form name="MyForm" method="POST">
<select id="deptid" name="deptid">
<?
/* Select Department in dropdown list.*/
$depQy="select departmentid, name from department";
$res=mysql_query($depQy) or die("Query failed: ".mysql_error());
while ($row=mysql_fetch_array($res))
{
if($deptid==$row['departmentid'])
$ins="SELECTED";
else
unset($ins);
echo "<option $ins value='".$row['departmentid']."'>".$row['name']."</option>";
}
?>
</select>
<a id="datechooserex1">
<a id="datelinkex1" href="#">StartDate</a>
<input id="dateinputex1" name="dateinputex1" type="text" value="" class="datechooser dc-dateformat='Y-n-j'">
</a>
<a id="datechooserex2">
<a id="datelinkex2" href="#">EndDate</a>
<input id="dateinputex2" name="dateinputex2" type="text" value="" class="datechooser dc-dateformat='Y-n-j'">
</a>
<input type="submit" NAME="SUBMIT" value="Submit">
</form>
<?php
// ******************** Main Code starts here *********************
if($_POST)
{
$date1=$_POST['dateinputex1'];
$date2=$_POST['dateinputex2'];
$unique_empcode_query="select distinct em.code,em.firstname,em.lastname from empmaster as em inner join empleaveapplication as ela on (em.employeeid = ela.employeeid) where (ela.startdate >= '".$date1."') and (ela.enddate <= '".$date2."') and (em.departmentid = ".$_POST['deptid'].") order by em.code ASC , ela.startdate ASC";
$unique_empcode_result=mysql_query($unique_empcode_query) or die("Query failed:".mysql_error());
// Create Table........
echo "<TABLE BORDER='1' CELLSPACING='0'>";
echo "<TH bgcolor='#6698FF' align='center'>Code</TH>";
echo "<TH bgcolor='#6698FF' align='center'>FirstName</TH>";
echo "<TH bgcolor='#6698FF' align='center'>LastName</TH>";
// Create Header date numbers......
$dt1=date_parse($date1);
$dt2=date_parse($date2);
for ($d=$dt1['day'];$d<($dt2['day']+1);$d++)
{
$d = str_pad($d, 2, 0, STR_PAD_LEFT);
echo "<th bgcolor='#6698FF' align='center'>".$d."</th>";
}
// Loop thru EmployeeID.....
for($i=0; $i < mysql_num_rows($unique_empcode_result); $i++)
{
unset($employeeid);
unset($empfirst);
unset($emplast);
$employeeid = mysql_result($unique_empcode_result,$i,"em.code");
$empfirst = mysql_result($unique_empcode_result,$i,"em.firstname");
$emplast = mysql_result($unique_empcode_result,$i,"em.lastname");
echo "<TR><TD align='center'>".$employeeid."</TD><TD align='center'>".$empfirst."</TD><TD align='center'>".$emplast."</TD>";
$emp_date_query = "select em.firstname, ela.startdate, ela.enddate, ela.status FROM empmaster as em INNER JOIN empleaveapplication as ela ON (em.employeeid=ela.employeeid and em.code= '".$employeeid."') where (ela.startdate >='".$date1."') AND (ela.enddate <= '".$date2."') AND (em.departmentid =".$_POST['deptid'].") order by em.code ASC, ela.startdate ASC";
$emp_date_result = mysql_query($emp_date_query) or die("Query failed:".mysql_error());
// Loop thru Dateranges for an employeeID.......
for($j=0; $j<mysql_num_rows($emp_date_result); $j++)
{
unset($dateme1);
unset($dateme2);
unset($status);
$dateme1=mysql_result($emp_date_result,$j,"ela.startdate");
$dateme2=mysql_result($emp_date_result,$j,"ela.enddate");
$status=mysql_result($emp_date_result,$j,"ela.status");
$sd=date_parse($dateme1);
$ed=date_parse($dateme2);
unset($diff);
$diff=($ed['day']-$sd['day'])+1;
unset($Colo);
// Choose color for TD cell as per status code...
if($status=='0') //No Leave
{
$Colo='White';
}
elseif($status=='1') // Leave Approved
{
$Colo='LightGreen';
}
elseif($status=='2') // Leave Pending
{
$Colo='Yellow';
}
elseif($status=='3') // Leave Rejected
{
$Colo='Red';
}
// Create TD cells starting from startdate till enddate, on same row, one after another....
// is for padding TD cells for proper alignment...
unset($padding);
if ($sd['day']==$dt1['day'] && ($j==0))
{
$padding=0;
echo str_repeat("<TD> </TD>",$padding);
$padstart=$ed['day']+1;
}
elseif($sd['day']!=$dt1['day'] && ($j==0))
{
$padding=abs($sd['day']-$dt1['day']);
echo str_repeat("<TD> </TD>",$padding);
$padstart=$ed['day']+1;
}
elseif($sd['day']!=$dt1['day'] && ($j>=1))
{
$padding=abs($sd['day']-$padstart);
echo str_repeat("<TD> </TD>",$padding);
$padstart=$ed['day']+1;
}
echo str_repeat("<TD bgcolor=$Colo bordercolor=$Colo> </TD>",$diff);
// echo "<tr><td>".$padding."</td></tr>";
}
echo "</TR>";
// Create a gap between each row.....
echo "<TR><TD></TD></TR>";
}
echo "</TABLE>";
}
mysql_close($connection);
?>
</BODY>
</HTML>
Thanks a bunch it2051229 for going out of the way and helping me
Let me know if you see any refinements which i may have missed out!
Best Regards,