show total number of leave from table

I've two tables called work_details and holy_days.

Holy_days table contains a row for each holiday in each year. work_details contains a row for each day's work report for each employee.

I want to get the leave report (yearly & monthly) of all employees.

I want to show how many days of leave they took per year and also per month (with dates)

Calculate leave days except saturday, sunday and the dates in holy_days table. Am using the following code, but this is not working properly..anybody plz help.

if ($request->isPost()) {
    $sdate1 = $request->get('txt_date1','post');
    $d1=explode('/', $sdate1);
    $sdate= $d1['2'].'-'.$d1['0'].'-'.$d1['1'];
    $edate1 = $request->get('txt_date2','post');
    $d2=explode('/', $edate1);
    $edate= $d2['2'].'-'.$d2['0'].'-'.$d2['1'];
$count=dates_between($emp_id,$sdate,$edate);

echo $count;

  }


function dates_between($emp_id,$start_date, $end_date = false)
 {
     if ( !$end_date )
     {
         $end_date = date("Y-m-d");
     }

     $test_date = $start_date;
     $day_incrementer = 1;
     $count_leaves=0;
     $flag=0;
     echo "SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".$emp_id."' and date between '".$start_date."' and '".$end_date."'";
 $work_res = mysql_query("SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".$emp_id."' and date between '".$start_date."' and '".$end_date."'");

 do
     {
             while($row=mysql_fetch_array($work_res))
                {
                 while((date("Y-m-d",$test_date)<$row['date'])&&($flag=0))

                 {

                     if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6))
                     {
                         echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                           $count_leaves++;
                     }
                        $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


                 }
                 $flag=1;


                 while((date("Y-m-d",$test_date)!=$row['date']))
                    {
                   if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6))
                   {
                      echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                       $count_leaves++;
                   }
                       $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


                    }
                     $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);



                 }
                if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6)&&($test_date <= $end_date))
                    {
                         echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                         $count_leaves++;
                         $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


                    }


}
while ( $test_date <= $end_date);

return($count_leaves);
 }

Answers


Natasha . Check this out! You ll surely surprise :)

function dates_between($emp_id,$start_date, $end_date = false)
 {
    $qsdate=$start_date;
    $qedate=$end_date;
    //echo $start_date.$end_date;
     if ( !$end_date )
     {
         $end_date = date("Y-m-d");
     }

     $start_date = is_int($start_date) ? $start_date : strtotime($start_date);
     $end_date = is_int($end_date) ? $end_date : strtotime($end_date);

     $test_date = $start_date;
     $day_incrementer = 1;
     $count_leaves=0;
     $flag=0;

 $work_res = mysql_query("SELECT DISTINCT date FROM `work_details` WHERE  employee_id='".$emp_id."' and date between '".$qsdate."' and '".$qedate."'");


             while($row=mysql_fetch_array($work_res))
                {
                 while((date("Y-m-d",$test_date)<$row['date'])&&($flag=0))

                 {

                    if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6))

                     {
                         echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                           $count_leaves++;
                     }
                        $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


                 }
                 $flag=1;


                 while((date("Y-m-d",$test_date)!=$row['date']))
                    {
                 if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6))

                   {
                      echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                       $count_leaves++;
                   }
                       $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


                    }
                     $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);



                 }


                 while ( date("Y-m-d", $test_date) <= date("Y-m-d", $end_date))
     {
          if(!(date('N', strtotime(date("Y-m-d", $test_date))) >= 6))

                    {
                         echo  "<tr><td align=center class=fontclass style=color:FF0000>".date("Y-m-d", $test_date)."</td></tr>";
                       $count_leaves++;



                    }
                    $test_date = $test_date + ($day_incrementer * 60 * 60 * 24);


     }


return($count_leaves);
 }

SELECT DISTINCT date 
FROM `work_details` 
WHERE  employee_id='".$emp_id."' 
and date between '".$start_date."' and '".$end_date."'
and date NOT IN(SELECT date from Holy_Days)

Assuming there is a date column in the Holy_Days table.


Need Your Help

How to load a different .phtml file if a specific condition is met in magento?

php html magento zend-framework magento-1.9

I am currently using a .phtml file for a specific page which works well in all modern browsers in magento. But it is not properly rendered in IE6, IE7.

Java: how to get a File from an escaped URL?

java file url escaping

I'm receiving an URL that locates a local file (the fact that I receive an URL is not in my control). The URL is escaped validly as defined in RFC2396. How can I transform this to a Java File obj...