<?php 
include('../dbconnect.php');
ob_start();
session_start();
$empid = $_SESSION['user'];
if(isset($_POST['tbl'])){
    $conn->set_charset("utf8");
    $column = array('employee_id', 'firstname', 'stat', 'name','datefrom', 'duration', 'hr','tl','approver');

    $query = "
    SELECT *, `leave`.status AS 'stat', empinfo.empid AS 'employee_id' FROM `leave` JOIN empdetails ON `leave`.empid = empdetails.detailid JOIN empinfo ON empdetails.detailid = empinfo.empid

    ";

    if(isset($_POST['status']) || isset($_POST['emp']))
    {
        

        $leavestat = $_POST['status'];
         $employee = $_POST['emp'];


        if($leavestat  =='All' && $employee  == 'All'){
            $query .= " WHERE `leave`.status IN ('2','3','4.1','4.2','4.3') ";
    
   
       }else if($leavestat  =='All' && isset($employee)){
   
   
        


           if($employee == 'Team Leader'){
   
            $query .=  "  WHERE   `leave`.status IN ('0','1','4.2')";
            

            }else if($employee == 'HR'){

                $query .=  " WHERE     `leave`.status  IN ('1','2','4.2')";
                

            }else if($employee == 'Senior Manager'){

                $query .=  " WHERE  `leave`.status IN ('2','3','4.3')";

            }else{

                $query .=  " WHERE   `empinfo`.empid = '$employee' AND `leave`.status != '5' ";

            }

       
       
   
       }
   
       else if(isset($leavestat) &&  $employee  =='All' ){
   
           if($leavestat == '4.1'){
   
               $query .=  " WHERE   `leave`.status >= '4.1'  AND `leave`.status != '5' ";
       
   
   
           } else if($leavestat == '2'){
   
               $query .= " WHERE   `leave`.status = '$leavestat'  ";
       
   
           }else if($leavestat == '3'){
   
               $query .=  " WHERE   `leave`.status = '$leavestat'  ";
       
   
           }
   
   
   
   
   
   
           
   
   
   
       }
   
       else if(isset($leavestat) &&  isset($employee) ){
   
    
   
   
           
           if($leavestat == '4.1'){
   
               if($employee == 'Team Leader'){
   
                   $query .=  "  WHERE   `leave`.status ='4.1'";
                   
   
               }else if($employee == 'HR'){
   
                   $query .=  " WHERE     `leave`.status ='4.2'";
                   
   
               }else if($employee == 'Senior Manager'){
   
                   $query .=  " WHERE  `leave`.status ='4.3'";
   
               }
   
            
   
               else{
   
                   $query .=  "  WHERE  `leave`.status >= '4.1' AND `leave`.empid='$employee' AND `leave`.status != '5' ";
   
               }
   
               
       
   
   
           } else if($leavestat == '2'){
   
               if($employee == 'Team Leader'){
   
                   $query .=  " WHERE  `leave`.status ='0'";
                   
   
               }else if($employee == 'HR'){
   
                   $query .=  " WHERE  `leave`.status ='1'";
                   
   
               }else if($employee == 'Senior Manager'){
   
                   $query .=  " WHERE  `leave`.status ='2'";
   
               }else{
   
               $query .=  " WHERE  `leave`.status = '$leavestat'  AND  `leave`.empid='$employee' ";
               }
       
   
           }else if($leavestat == '3'){
   
               if($employee == 'Team Leader'){
   
                   $query .=  " WHERE  `leave`.status ='1'";
                   
   
               }else if($employee == 'HR'){
   
                   $query .=  " WHERE  `leave`.status ='2'";
                   
   
               }else if($employee == 'Senior Manager'){
   
                   $query .=  " WHERE  `leave`.status ='3'";
   
               }else{
   
               $query .=  " WHERE  `leave`.status = '$leavestat' AND `leave`.empid='$employee' ";
   
               }
       
   
           }
   
   
   
   
   
       }

    


    }else{

        $query .= " WHERE `leave`.status IN ('2','3','4.1','4.2','4.3')  ";

    }
    

    

    if(isset($_POST['order']))
    {
     $query .= ' ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
    }
    else
    {
     $query .= ' ORDER BY idl DESC, datefrom DESC ';
    }

    if($_POST["length"] != -1)
    {
     $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
    }
    
    $statement = $conn->query($query);
  
    $number_filter_row = $statement->num_rows;
    echo $conn->error;

    $statement = $conn->query($query.$query1);
    //echo  $query;

    $result = mysqli_fetch_all($statement, MYSQLI_ASSOC);

    foreach($result as $row)
    {
        $sub_array = array();

        
    

        if($row['stat'] == '3'){
      
            $status = 'Approved';
            }else if($row['stat'] == '5'){

            $status = 'Cancelled';
            }else if($row['stat'] == '4.3' || $row['stat'] == '4.2' || $row['stat'] == '4.1'){

            $status = 'Declined';
            }else{

       
            $status = 'Pending Approval';

            }

            
     

            if($row['stat'] == '0'){
            $status = 'Pending Approval';
            $tl ='Pending';
            $hr ='Pending';
            $approver ='Pending';
            }else if($row['stat'] == '1'){
            $status = 'Pending Approval';
            $tl ='Approved <br>'.$row['tl'] .'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
            $hr ='Pending';
            $approver ='Pending';

            }
            //.'<br>'.($row['approve3_date'] == '' ? '' : date('m/d/Y', strtotime($row['approver3_date'])));
            
            else if($row['stat'] == '2'){
            $status = 'Pending Approval';

            $tl ='Approved <br>'.$row['tl']  .'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
            $hr ='Aprroved <br>'.$row['hr']  .'<br>'.($row['approver2_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver2_date'])));
            $approver ='Pending';
            
            
            
            }

            
        else if($row['stat'] == '3'){
            $status = 'Approved';
   
            $tl ='Approved <br>'.$row['tl']  .'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
            $hr ='Approved <br>'.$row['hr']  .'<br>'.($row['approver2_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver2_date'])));
            $approver ='Approved <br>'.$row['approver']  .'<br>'.($row['approver3_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver3_date'])));
            }

            else if($row['stat'] == '5'){
            $status = 'Cancelled';
           $tl =  ' Cancelled ';
           $hr = ' Cancelled ';
           $approver =' Cancelled ';
            }
            else if($row['stat'] == '4.1'){
            $status = 'Declined';
   
            $tl ='Declined <br>'.$row['tl'].'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
            $hr ='Declined';
            $approver ='Declined';
            }

            else if($row['stat'] == '4.2'){
                $status = 'Declined';

                $tl ='Approved <br>'.$row['tl']  .'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
                $hr ='Declined <br>'.$row['hr']  .'<br>'.($row['approver2_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver2_date'])));
                $approver ='Declined';
     
             
            }

            else if($row['stat'] == '4.3'){
                $status = 'Declined';
        
         

                $tl ='Approved <br>'.$row['tl']  .'<br>'.($row['approver1_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver1_date'])));
                $hr ='Approved <br>'.$row['hr']  .'<br>'.($row['approver2_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver2_date'])));
                $approver ='Declined <br>'.$row['approver']  .'<br>'.($row['approver3_date'] == 0 ? '' : date('m/d/Y', strtotime($row['approver3_date'])));
            
            }
            $sub_array[]  = $row['employee_id'];
            $sub_array[]  = $row['firstname'].' '.$row['surname'];
            $sub_array[]  = $status;
            $sub_array[]  = $row['name'];
            $sub_array[]  = date('m/d/Y', strtotime($row['datefrom'])).' - '.date('m/d/Y', strtotime($row['dateto']));
            $sub_array[]  = $row['duration'];
            $sub_array[]  = $tl;
            $sub_array[]  = $hr;
            $sub_array[]  = $approver;
            $sub_array[]  = "
            <button type='button'   data-toggle='modal'  data-target='#view' class='btn btn-primary btn-sm viewButton'  id='".$row['idl']."' > <i class='fa fa-eye'></i></button> | 
            <button type='button' data-toggle='modal'  data-target='#approve' class='btn btn-success btn-sm approve'  id='".$row['idl']."' ".($row['stat'] == '4.3' || $row['stat'] == '4.1' || $row['stat'] == '4.2' || $row['stat'] == '3' ? "disabled" : "")."> <i class='fa fa-check'></i>   </button>  |
            <button type='button'  data-toggle='modal'  data-target='#disapprove'  class='btn btn-danger btn-sm disapprove'  id='".$row['idl']."' ".($row['stat'] == '4.1' || $row['stat'] == '4.2' || $row['stat'] == '4.3' ? "disabled" : "")."> <i class='fa fa-close'></i>   </button> | 
            <button type='button' class='btn btn-warning btn-sm text-white print'  id='".$row['idl']."'> <i class='fas fa-print'></i> </button>
            
            ";
           
            $data[] = $sub_array;




    }

    function count_all_data($conn,$qry)
    {
        
     $query = " SELECT *, `leave`.status AS 'stat', empinfo.empid AS 'employee_id' FROM `leave` JOIN empdetails ON `leave`.empid = empdetails.detailid JOIN empinfo ON empdetails.detailid = empinfo.empid  WHERE   `leave`.status IN ('2','3','4.1','4.2','4.3') ORDER BY datefrom ".$qry;
    
        $statement = mysqli_query($conn, $query);
     return mysqli_num_rows($statement);
    }
    
    $output = array(
     "draw"       =>  intval($_POST["draw"]),
     "recordsTotal"   =>  count_all_data($conn, $qry),
     "recordsFiltered"  =>  $number_filter_row,
     "data"       =>  $data
    );
    
    echo json_encode($output);


}
if(isset($_POST['view'])){

    
    $id= $_POST['id'];

    $sql = $conn->query("SELECT *, `leave`.status AS 'stat', empdetails.empid AS  `employee_id` FROM `leave` JOIN empdetails ON `leave`.empid = empdetails.detailid JOIN empinfo ON empdetails.detailid = empinfo.empid WHERE idl = '$id'");

    $row = $sql->fetch_assoc();


    

    $var['empid'] = $row['employee_id'];
    $var['name'] = $row['firstname'].' '.$row['surname'];
    $var['datefiled'] = date('m/d/Y', strtotime($row['datefiled']));
    $var['leavetype'] = $row['type'];
    $var['date'] = date('m/d/Y', strtotime($row['datefrom'])).' - '.date('m/d/Y', strtotime($row['dateto']));
    $var['duration'] = $row['duration'];
    $var['notes'] = $row['description'];

    echo json_encode($var);



}

if(isset($_POST['approve'])){

    $id= $_POST['id'];
    $date = date('Y-m-d H:i:s');

    $conn->query("UPDATE `leave` SET `status` = '3',approver='$empid', approver3_date='$date' WHERE `idl`='$id' ");



}
if(isset($_POST['disappprove'])){
    $id= $_POST['id'];
    $reason = $_POST['reason'];
    $date = date('Y-m-d H:i:s');

    $sql = $conn->query("SELECT *, `leave`.status AS 'stat', empdetails.empid AS  `employee_id` FROM `leave` JOIN empdetails ON `leave`.empid = empdetails.detailid JOIN empinfo ON empdetails.detailid = empinfo.empid WHERE idl = '$id'");
    $row = $sql->fetch_assoc();
    $emp = $row['employee_id'];

  

        $ptoid = $row['ptoid'];

        $pto = $conn->query("SELECT * FROM pto WHERE  empid='$emp' AND name='Paid Time Off' ");
        $ptorow = $pto->fetch_assoc();
    
        $availablepto = $ptorow['availablepto'];
        $usedpto = $ptorow['usedpto'];
      
       
      
        $available = $availablepto + $row['duration'];
        $used = $usedpto - $cancelduration; 
        $conn->query("UPDATE `pto` SET usedpto='$used', availablepto='$available' WHERE empid='$emp' AND name='Paid Time Off'");

    

   
         $conn->query("UPDATE `leave` SET status = '4.3', reason='$reason', approver='$empid',approver3_date='$date'  WHERE idl='$id'");


          echo "UPDATE `leave` SET status = '4.3', reason='$reason', approver='$empid'  WHERE idl='$id'";






}
if(isset($_POST['print'])){
    $id= $_POST['id'];
    $sql = $conn->query("SELECT *, `leave`.status AS 'stat', empdetails.empid AS  `employee_id` FROM `leave` JOIN empdetails ON `leave`.empid = empdetails.detailid JOIN empinfo ON empdetails.detailid = empinfo.empid WHERE idl = '$id'");
    $row = $sql->fetch_assoc();
    $var['fempid'] = $row['employee_id'];
    $var['fname'] = $row['firstname'].' '.$row['surname'];
    $var['fteam'] = $row['team'];
    $var['fleavedate'] = date('m/d/Y', strtotime($row['datefrom'])).' - '.date('m/d/Y', strtotime($row['dateto']));
    $var['fnoofapprovedleave'] = $row['duration'];
    $var['fdatefiled'] = date('m/d/Y', strtotime($row['datefiled']));
    $var['leavelist1'] = "<div class='col-6 p-1'>
    Emergency Leave
    </div>".($row['name'] == 'Emergency Leave'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>' )." <div class='col-6 p-1'>
    Sick Leave
    </div> ".($row['name'] == 'Sick Leave'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>' )."  <div class='col-6 p-1'>
    Vaction Leave
    </div> ".($row['name'] == 'Vacation Leave'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>' )." <div class='col-6 p-1'>
    LWOP - Unpaid Leave
    </div>  ".($row['name'] == 'LWOP - Unpaid Leave'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>')." <div class='col-6 p-1'>
    Magna Carta Leave
    </div>  ".($row['name'] == 'Magna Carta'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>')." <div class='col-6 p-1'>
    Bereavement Leave
    </div>  ".($row['name'] == 'Bereavement'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>')
    ;

    /* $leavelist = $conn->query("SELECT * FROM pto WHERE name IN('Magna Carta','Bereavement') GROUP BY name");

    while($leavelistrow = $leavelist->fetch_assoc()){

        $var['leavelist2'] .='<div class="col-6 p-1">'.$leavelistrow['name'].' Leave </div>';

        if($leavelistrow['name'] == $row['type'] ){

            $var['leavelist2'] .='

            <div class="col-2 p-1 border border-dark text-center">
                <i class="fa fa-check"> </i>
        
            </div>
        
            ';

        }else{

            $var['leavelist2'] .='

            <div class="col-2 p-1 border border-dark">
                                                    
            </div>
        
            ';

        }


        



    } */

    $leavelist2 = $conn->query("SELECT * FROM pto WHERE name != 'Paid Time Off' AND name !='Magna Carta' AND name != 'Bereavement' GROUP BY name ");

    while($leavelistrow2 = $leavelist2->fetch_assoc()){

        $var['leavelist3'] .='<div class="col-6 p-1">'.$leavelistrow2['name'].' Leave </div>';

        if($leavelistrow2['name'] == $row['type'] ){

            $var['leavelist3'] .='

            ✔
        
            ';

        }else{

            $var['leavelist3'] .='

            <div class="col-2 p-1 border border-dark">
                                                    
            </div>
        
            ';

        }



    }

    $var['leavelist3'] .= "  <div class='col-6 p-1'>
    Holiday Off
    </div> ".($row['name'] == 'Holiday Off'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>' );
    
    $var['leavelist3'] .= "  <div class='col-6 p-1'>
    Compensatory Time Off Leave
    </div> ".($row['name'] == 'Compensatory Time Off'? '<div class="col-2 p-1 border border-dark text-center">
    ✔

    </div>' : '<div class="col-2 p-1 border border-dark">
                                               
    </div>' );

    $var['leavelist3'] .= '<div class="col-12 p-1 ">
    Note/Comment/Reason: <u>'.$row['description'].'</u>
  
    </div>';
    $var['fapproved'] = ($row['stat'] == '3' ? ' ✔ ' : '');

    if($row['stat'] == '0' || $row['stat'] == '1' || $rorowwrequest['stat'] == '2'){
  
        $disapprove ='';

    }else if($row['stat'] == '3'){
        
        $disapprove ='';
    }
    
    else{
        $disapprove ='✔';
    
    }

    $var['fdisapproved'] =  $disapprove;
    $var['freason'] = $row['reason'];
    $var['fdatehire']  = date('m/d/Y', strtotime($row['hireddate']));



    $pto = $conn->query("SELECT * FROM `pto` WHERE empid='".$row['employee_id']."' AND name='Paid Time Off'");
    $ptorow = $pto->fetch_assoc();
    $var['fremainingpto']  = $ptorow['availablepto'];
    $var['fleaveid'] = $row['idl'];



    echo json_encode($var);


    
}
?>