I m trying to create a Leave tracking webpage which will display a sort of HTML Gantt Table.
On selecting/entering a StartDate & EndDate for Gantt table, it will query an MSSQL database and display unique employees with their corresponding row TD cells colored according to their various Leave Date ranges & according to their AppliedLeave status (0=Pending (Yellow), 1=Approved(Green), 2=Rejected(Red)).
EmpCode | EmpName | 01 | 02 | 03 | 05 |....
0001 | sahil | 0 | 0 | | |....
0002 | mae | | | 1 | |....
0003 | joe | 2 | | 1 | 1 |....
How to achieve this?
'********************* CODE HERE ************************
Code: Select all
<?php
$connection = mssql_connect("localhost","root","") or die ("Couldn't connect to server");
$db = mssql_select_db("leavetracker", $connection) or die ("Couldn't select database");
$deptid = $_POST['DeptID'];
/*$date1=$POST['dateinputex1'];
$date2=$POST['dateinputex2'];*/
?>
<HTML>
<HEAD>
<TITLE>
Leave Gantt
</TITLE>
<link rel="stylesheet" type="text/css" media="all" href="datechooser.css">
<script type="text/javascript" src="datechooser.js"></script>
<script type="text/javascript">
<!-- //
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('n/d/Y');
return true;
}
function FunctionEx2(objDate)
{
// objDate is a plain old Date object, with the getPHPDate() property added on.
document.getElementById('dateinputex2').value= objDate.getPHPDate('n/d/Y');
return true;
}
// -->
</script>
</HEAD>
<BODY>
<H1>Leave Tracking Gantt</H1>
<form name="MyForm" method="POST">
<select id="DeptID" name="DeptID">
<?
$depQy="select DepartmentID,Name FROM Department";
$res=mssql_query($depQy) or die("Query failed: ".mssql_error());
while ($row=mssql_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="">
</a>
<a id="datechooserex2">
<a id="datelinkex2" href="#">EndDate</a>
<input id="dateinputex2" name="dateinputex2" type="text" value="">
</a>
<input type="submit" NAME="SUBMIT" value="Submit">
</form>
<?php
if($_POST)
{
$date1=$_POST['dateinputex1'];
$date2=$_POST['dateinputex2'];
$query = "SELECT em.Code, em.FirstName, em.LastName, ela.StartDate, ela.EndDate, ela.status 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'].")";
$result = mssql_query($query) or die("Query failed: ".mssql_error());
echo "<TABLE BORDER='1'>";
echo "<TR bgcolor=gray>";
echo "<TH>EmpCode</TH><TH>FirstName</TH><TH>LastName</TH><TH>StartDate</TH><TH>EndDate</TH><TH>Status</TH>";
for ($i=0; $i<=date('d',$date2);$i++)
{
echo "<TH>".date('Y/m/d',mktime(0,0,0,date('m',$date1),date('d',$date1)+$i,date('Y',$date1)))."</TH>";
}
echo "</TR>";
while ($row = mssql_fetch_array($result))
{
echo "<TR bgcolor=#DBC7DA>";
echo "<TD>".$row['Code'];
echo "</TD><TD>".$row['FirstName'];
echo "</TD><TD>".$row['LastName'];
echo "</TD><TD>".$row['StartDate'];
echo "</TD><TD>".$row['EndDate'];
echo "</TD><TD>".$row['Status'];
echo "</TD>";
echo "</TR>";
}
echo "</TABLE>";
}
mssql_close($connection);
?>
</BODY>
</HTML>