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

function mnltopst($date, $time){

    $convert = new DateTime($date.' '.$time, new DateTimeZone('Asia/Manila'));
    
    
    $convert->setTimezone(new DateTimeZone('America/Los_Angeles'));
    
    $datetime = $convert->format('m/d/Y h:i A');
    
    
    return ($datetime);
    
    
}
function psttomnl($date, $time){
    
  $convert = new DateTime($date.' '.$time, new DateTimeZone('America/Los_Angeles'));
  
  
  $convert->setTimezone(new DateTimeZone('Asia/Manila'));
  
  $datetime = $convert->format('m/d/Y h:i A');
  
  
  return ($datetime);
  
  
  
  
}







$row = 1;
if (($handle = fopen("tempbulkleave.csv", "r")) !== FALSE) {
    $i=0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if($i != 0){
            $pto_sql = $conn->query("SELECT * FROM pto WHERE empid='".$data[5]."' AND name='Paid Time Off'");
            $pto_row = $pto_sql->fetch_assoc();
    
            $sql = "SELECT * FROM `leave` ORDER BY id DESC LIMIT 1";
            $result = mysqli_query($conn, $sql);
            $row = mysqli_fetch_assoc($result);
            $curyear = date('y');
            $rowcount=mysqli_num_rows($result);
            $row['idl'];
            $rowcount = substr($row['idl'], 9);
            $count = $rowcount + 1;
            $strlength = strlen($count);
            if($strlength >= 1 && $strlength <= 4){
                $ATVID = str_pad($count, 5, '0', STR_PAD_LEFT);
             
              
           }
           else{
             $ATVID  = $count;
           }
            $idl = 'ATVPTO-'.$curyear.$ATVID;

            $filed = '2021-04-20';
            $leavetype = $data[6];
            $datefrom = $data[1];
            $dateto = $data[2];
            $duration = $data[3];
            $notes = $data[4];
            $status = 0;
            $empid=$data[5];
            $ptoid = 0;
            $leavename = $data[7];
    
            $available = $pto_row['availablepto'] - $data[3];
            $used = $data[3] + $pto_row['usedpto'];

           /*  $available = $pto_row['availablepto'] + $data[3];
            $used = $data[3] - $pto_row['usedpto']; */
            $ptoid = $pto_row['id'];

            echo $idl.'<br>';

            echo $datefrom.'-'.$dateto.'<br>';

            echo $pto_row['availablepto'].'<br>';
    
            if($datefrom == $dateto){
                $exist_leave = "SELECT * FROM schedule JOIN `leave` ON schedule.idl = `leave`.`idl` WHERE schedule.date = '$datefrom' AND `status` IN ('0','1','2','3') AND schedule.empid = '$empid'";
    
                $checksched = "SELECT * FROM schedule WHERE empid='$empid' AND date='$datefrom'";
    
            }else{
                $daterange = [];
        
                $period = new DatePeriod(
                    new DateTime($datefrom),
                    new DateInterval('P1D'),
                    new DateTime($dateto)
               );
    
               foreach ($period as $key => $value) {
                    $daterange[] =  $value->format('Y-m-d');     

                }
                array_push($daterange, $dateto);
          
                $dr = implode("','", $daterange);
    
                $exist_leave = "SELECT * FROM schedule JOIN `leave` ON schedule.idl = `leave`.`idl` WHERE schedule.date IN ('$dr') AND `status` IN ('0','1','2','3') AND schedule.empid = '$empid'";
    
                $checksched = "SELECT * FROM schedule WHERE empid='$empid' AND date IN ('$dr') ";
    
    
    
            }
            echo $exist_leave.'<br>'.$checksched.'<br>';
    
            $existdate = $conn->query($exist_leave);
            $checkschedexist = $conn->query($checksched);
            if($existdate->num_rows > 0){
    
                echo $error = 'date already have leave filed<br>';
    
            }else if($checkschedexist->num_rows == 0){
    
               echo $error = 'Schedule are not yet added  <br>';
    
            }else{
    
                            
                $insertleave = "INSERT INTO `leave`(`idl`, `type`, `datefiled`, `datefrom`, `dateto`, `duration`, `description`, `status`, `empid`, `reason`, `ptoid`, `name`) VALUES ('$idl','$leavetype','$filed','$datefrom','$dateto','$duration','$notes','0','$empid','','$ptoid','$leavename')";
                $updatepto = "UPDATE pto SET availablepto = '$available', usedpto='$used' WHERE id='$ptoid'";
    
                $conn->query($insertleave);
                echo $conn->error.'<br>';
                $conn->query($updatepto);
                echo $conn->error.'<br>';

    
                echo $insertleave.'<br>';
                echo $updatepto.'<br>';
    
               
                while( $sched_row = $checkschedexist->fetch_assoc()){
    
                    $pstdate = date('Y-m-d', strtotime(mnltopst($sched_row['date'], $sched_row['starttime'])));
                     
                    $schedupdate = "UPDATE schedule SET idl='$idl', leaveduration='$duration', pstdate='$pstdate' WHERE empid='$empid' AND date='".$sched_row['date']."'";
    
                    echo $schedupdate.'<br>';
    
                    $conn->query($schedupdate);
                    echo $conn->error.'<br>';

    
    
    
    
                }
                
    
               
    
    
            }
    
           
    
    

        }

        echo '<br><br>';

        


            $i++;  

    }
    fclose($handle);
}


?>