Page 3 of 3

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 5:56 am
by sifar786
Ok used DISTINCT but it shows the same duplicate records, one below the other

Code: Select all

SELECT DISTINCT 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 >= '2008/8/1'
)
AND (
ela.enddate <= '2008/8/31'
)
AND (
em.departmentid = '1'
)
ORDER BY em.code ASC , ela.startdate ASC ;
 

So your code will again display duplicate employees data..

Though i need to thank you ( :D ) cos thats a brilliant idea...didnt know i could use a mysql_query to loop within result subsets.Will have to work on that now and then later on figure out a way of displaying dates ranges one after the other in the table..

Right now, the initial query shows the same data even after using DISTINCT. Why is that so?

regards,

P.S: See Snapshot.

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 5:59 am
by it2051229
make sure that you are only selecting the employee id column and nothing more.. or you can do a "group by"

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 6:23 am
by sifar786
Tried both...only shows i think, the lowest (i.e. first occuring) index value dates....

see Snapshot01: (using both DISTINCT and GROUP BY)
see Snapshot02: (using only GROUP BY)

"DISTINCT on em.code" alone does not show unique employees...

Any ideas?

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 6:31 am
by it2051229
i tried this one and it worked

Code: Select all

select DISTINCT em.code 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 =".$deptId.") order by em.code ASC, ela.startdate ASC
[/cod]

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 6:42 am
by sifar786
Oh! You mean only take unqiue employees who qualify the start and end dates?

ok did that and showing 2 empcodes.. now what i understand is, using these empcodes do a search in $Result (original query) and locate corresponding StartDate & EndDate & Status Codes in an Array for each employeecode, and then dump them into single rows a table?

I think i will have to write multiple "for loops" for every daterange (from startdate to enddate) to display colored cells for each employee, right?

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 6:53 am
by it2051229

Code: Select all

 
$empCodeIdQuery = mysql_query(select DISTINCT em.employeeid 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 =".$deptId.") order by em.code ASC, ela.startdate ASC) or die(mysql_error());
 
if(mysql_num_rows($empCodeIdQuery) > 0)
{
   for($i=0; $i < mysql_num_rows($empCodeIdQuery); $i++)
   {
         $employeeId = mysql_result($empCodeIdQuery,$i,"em.employeeid");
         
         $dateQuery = mysql_query("SELECT the dates of this employee WHERE employeeid = '$employeeid'");
 
         for($j=0; $j < mysql_num_rows($dateQuery); $j++)
         {
                 // display all the dates.
         }
   }
}
 

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 7:02 am
by sifar786
Thanks :)

Will keep you updated as to where i have reached...

Regards,

Re: PHP leave tracking webpage

Posted: Mon Aug 25, 2008 3:55 pm
by sifar786
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....
                // &nbsp;&nbsp;&nbsp;&nbsp; is for padding TD cells for proper alignment...
 
                unset($padding);
                if ($sd['day']==$dt1['day'] && ($j==0))
                {
                    $padding=0;
                    echo str_repeat("<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>",$padding);
                    $padstart=$ed['day']+1;
                }
                elseif($sd['day']!=$dt1['day'] && ($j==0))
                {
                    $padding=abs($sd['day']-$dt1['day']);
                    echo str_repeat("<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>",$padding);
                    $padstart=$ed['day']+1;
                }
                elseif($sd['day']!=$dt1['day'] && ($j>=1))
                {
                    $padding=abs($sd['day']-$padstart);
                    echo str_repeat("<TD>&nbsp;&nbsp;&nbsp;&nbsp;</TD>",$padding);
                    $padstart=$ed['day']+1;
                }
                echo str_repeat("<TD bgcolor=$Colo bordercolor=$Colo>&nbsp;&nbsp;&nbsp;&nbsp;</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 :D

Let me know if you see any refinements which i may have missed out!

Best Regards,