<?php 
include('dbconnect.php');


function computeMonth($date1, $date2, $differenceFormat){

   // $differenceFormat = '%d';
    $datetime1 = date_create($date1);
    $datetime2 = date_create($date2);

    $interval = date_diff($datetime1, $datetime2);

   return $interval->format($differenceFormat);

}


function getAllLeaveDuration($conn, $empid){
    $cur_year = date('Y');
    $cut_off = $conn->query("SELECT * FROM pto_cut_off WHERE year='$cur_year' ");
    $cut_row = $cut_off->fetch_assoc();
    $query = $conn->query("SELECT SUM(duration) as 'total' FROM `leave` WHERE empid='".$empid."' AND type='Paid Time Off' AND status IN ('3','0','1','2') AND datefiled >= '".date('Y-m-d',strtotime($cut_row['d_from']))."'  AND datefiled <='".date('Y-m-d', strtotime($cut_row['d_to']))."' ");
    //echo "SELECT SUM(duration) as 'total' FROM `leave` WHERE empid='".$empid."' AND type='Paid Time Off' AND status IN ('3','0','1','2') AND datefiled >= '".date('Y-m-d',strtotime($cut_row['d_from']))."'  AND datefiled <='".date('Y-m-d', strtotime($cut_row['d_to']))."'";

    $total_row = $query->fetch_assoc();

    return $total_row['total'];

}
function updatePTO($empid,$conn){
    $pto = $conn->query("SELECT * FROM pto WHERE empid='".$empid."' AND name= 'Paid Time Off' ");
    $pto_row = $pto->fetch_assoc();

    $total_used_leave = getAllLeaveDuration($conn, $empid);


    $usedpto = $total_used_leave;
    $available_pto = (float)$pto_row['ptoearned'] - $usedpto;

    $conn->query("UPDATE pto SET availablepto='$available_pto', usedpto='$usedpto' WHERE empid='$empid' AND name='Paid Time Off' ");

    //echo "UPDATE pto SET availablepto='$available_pto', usedpto='$usedpto' WHERE empid='$empid' AND name='Paid Time Off'".'<br>';

    echo $conn->error;



}
//echo date('m-d-Y');

//echo computeMonth('2020-03-27','2019-11-27');

$query1 = $conn->query("SELECT * FROM empdetails WHERE status ='Active'");
while($row = $query1->fetch_assoc()){
$hired_year = date('Y', strtotime($row['hireddate']));
$cur_year = date('Y');
$cur_date = date('Y-m-d');

$multiplier = $conn->query("SELECT * FROM pto WHERE empid='".$row['empid']."' AND name= 'Paid Time Off' ");
$row_multiplier = $multiplier->fetch_assoc();

$days_duration = (int)computeMonth($cur_date, $row_multiplier['yearupdate'], '%a');




        //$cur_date = $row['status'] == 'Active - Forced Leave' ? $row['inactivedate'] : $cur_date;


        if($hired_year == $cur_year){
            $last_day_of_the_year = date('Y-m-d', strtotime('last day of december this year'));

            $month_dur = (int)computeMonth($cur_date, $row['hireddate'],'%m');
            $maxPTO = (int)computeMonth($last_day_of_the_year, $row['hireddate'],'%m') * (float)$row_multiplier['multiplier'];

            //echo $row['hireddate'].' '.computeMonth($last_day_of_the_year, $row['hireddate'],'%m') .' | '.$maxPTO.' | '.$row['empid'].'<br>';

          

        }else{
             $rep_hired_date = $cur_year.'-'.'01-'.date('d', strtotime($row['hireddate']));

            $hired_date = ($cur_year - 1).'-'.'12'.'-'.date('d', strtotime($row['hireddate']));

            $month_dur = (int)computeMonth($cur_date, $rep_hired_date,'%m')+1;
            $pto = $conn->query("SELECT * FROM pto WHERE empid='".$row['empid']."' AND name= 'Paid Time Off' ");
            $pto_row = $pto->fetch_assoc();
            $maxPTO = $pto_row['maxpto'];


            
        }
  
       
        $pto_earned = $month_dur * (float)$row_multiplier['multiplier'];


        if($pto_earned <= $maxPTO){

                    
            //$available_pto = $pto_earned - $row_multiplier['usedpto'];
            echo $row['empid'].' - '.$pto_earned.'<br>';
            
            $conn->query("UPDATE pto SET ptoearned='$pto_earned' WHERE empid='".$row['empid']."' AND name='Paid Time Off' ");
            $conn->error; 

        }

         updatePTO($row['empid'],$conn);


 

}

$conn->close();



?>