PHP leave tracking webpage

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

sifar786
Forum Commoner
Posts: 27
Joined: Thu Aug 21, 2008 11:50 pm

Re: PHP leave tracking webpage

Post 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.
Attachments
db_snapshot.PNG
db_snapshot.PNG (81.19 KiB) Viewed 507 times
Last edited by sifar786 on Mon Aug 25, 2008 6:01 am, edited 1 time in total.
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: PHP leave tracking webpage

Post by it2051229 »

make sure that you are only selecting the employee id column and nothing more.. or you can do a "group by"
sifar786
Forum Commoner
Posts: 27
Joined: Thu Aug 21, 2008 11:50 pm

Re: PHP leave tracking webpage

Post 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?
Attachments
db_snapshot02.PNG
db_snapshot02.PNG (46.18 KiB) Viewed 495 times
db_snapshot01.PNG
db_snapshot01.PNG (46.43 KiB) Viewed 495 times
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: PHP leave tracking webpage

Post 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]
sifar786
Forum Commoner
Posts: 27
Joined: Thu Aug 21, 2008 11:50 pm

Re: PHP leave tracking webpage

Post 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?
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: PHP leave tracking webpage

Post 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.
         }
   }
}
 
sifar786
Forum Commoner
Posts: 27
Joined: Thu Aug 21, 2008 11:50 pm

Re: PHP leave tracking webpage

Post by sifar786 »

Thanks :)

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

Regards,
sifar786
Forum Commoner
Posts: 27
Joined: Thu Aug 21, 2008 11:50 pm

Re: PHP leave tracking webpage

Post 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,
Attachments
timemate.zip
Heres the complete source code with the database....
(10.39 KiB) Downloaded 61 times
Post Reply