<?PHP 
set_time_limit(10000);
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);
  
  
  
  
}


function computeDurations($date1,$date2){

  
        $curdate = strtotime($date1);
        $datadate = strtotime($date2);
        $diff = $curdate - $datadate;
     
        $sec  = $diff;
       return $min = floor($diff/(60));

        $hrs = floor($diff/(60*60));
        $days = floor($diff/(60*60*24));
    

}

if(isset($_POST['teamfilter'])){
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="LILO.csv";');
    $csv =  fopen('php://output', 'w');

    $header =['Employee ID','Name','Team','Date','Login(Pacific)','Logout(Pacific)','Login(Manila)','Logout(Manila)','Late','First Break','Second Break','Toilet Break','Lunch Break','Prod','Non-Prod','Admin'];

    fputcsv($csv, $header);

    $option_group = strtok($_POST['team'],':');
    $team = substr($_POST['team'],strrpos($_POST['team'], ':' )+1);
    //echo $team;

    



    if($option_group == 'Team'){

        $sql=$conn->query('SELECT *, schedule.date AS "sched_date" FROM schedule JOIN empinfo ON schedule.empid = empinfo.empid  JOIN empdetails ON schedule.empid = empdetails.detailid WHERE empdetails.team="'.$team.'" AND status= "Active" AND schedule.date BETWEEN "'.$_POST['from'].'" AND "'.$_POST['to'].'" '  );

    }else{

        $sql=$conn->query('SELECT *, schedule.date AS "sched_date" FROM schedule JOIN empinfo ON schedule.empid = empinfo.empid  JOIN empdetails ON schedule.empid = empdetails.detailid WHERE empdetails.empid="'.$team.'" AND status= "Active" AND schedule.date BETWEEN "'.$_POST['from'].'" AND "'.$_POST['to'].'" '  );

    }

    while ( $row=$sql->fetch_assoc() ) {
      

        $lilo = $conn->query('SELECT * FROM tbl_login WHERE date="'.date('m/d/Y', strtotime($row['sched_date'])).'" AND empid="'.$row['empid'].'" ');
        $lilo_row = $lilo->fetch_assoc();

       if($lilo_row['timein'] == ''){
           if($row['idl'] != ''){
               $leave = $conn->query("SELECT * FROM `leave` WHERE idl='".$row['idl']."' AND status='3' ");
               if($leave->num_rows > 0){
                   $leave_row = $leave->fetch_assoc();
                   $timein = $leave_row['name'];
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';   
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];                            }
           }else if($row['pststarttime'] == 'OFF' ||$row['pststarttime'] == 'HOLIDAY OFF' ){
               $timein = $row['pststarttime'];
               $timeout= $row['pststarttime'];
               $mnl_timein = '--';
               $mnl_timeout= '--';
               $late ='--';
               $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
               $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               
           }else{
               if(date('Y-m-d') > $row['sched_date']){
                   $timein= 'ABSENT';
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               }else{
                   $timein= '--';
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               }
           }
       }else{
           if($row['idl'] != ''){
               //echo "SELECT * FROM `leave` WHERE idl='".$row['idl']."' AND status='3' ";
            $leave = $conn->query("SELECT * FROM `leave` WHERE idl='".$row['idl']."' AND status='3' AND duration='0.5' ");


            if($leave->num_rows > 0)
            {
                $leave_row = $leave->fetch_assoc();
                $timein =  $leave_row['name'].' Half Day '.date('h:i A', strtotime($lilo_row['timein']));
                $mnl_timein = $leave_row['name'].' Half Day '.date('h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timein'])), date('H:i',strtotime($lilo_row['timein'])))  ));

            }else{
                $timein =  date('h:i A', strtotime($lilo_row['timein']));
                $mnl_timein = date('h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timein'])), date('H:i',strtotime($lilo_row['timein'])))  ));

            }

                
            

           }else{

                $timein =  date('h:i A', strtotime($lilo_row['timein']));
                $mnl_timein = date('h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timein'])), date('H:i',strtotime($lilo_row['timein'])))  ));


           }

            
            

      
          
           $timeout =date('h:i A', strtotime($lilo_row['timeout']));
           $mnl_timeout = date( 'h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timeout'])), date('H:i',strtotime($lilo_row['timeout'])))  ));
           $late = 0;

           if(strtotime($lilo_row['timein']) > strtotime($row['pststarttime'])){
               $late = computeDurations(date('Y-m-d H:i', strtotime($lilo_row['timein'])),$row['sched_date'].' '.$row['pststarttime'] ) ;
           }

           $activity = array("1st Break","2nd Break", "Toilet Break","Lunch Break");
           $type= array("Prod","Non-Prod","Admin");

           foreach ($activity as $key => $activitys) {
               $util = $conn->query("SELECT SUM(elapsedtime) AS 'break' FROM tbl_utilization WHERE empid='".$row['empid']."' AND date= '".date('m/d/Y',strtotime($row['sched_date']))."' AND activity='$activitys' ");

            
               $util_row = $util->fetch_assoc();
               $break[$activitys] = $util_row['break'];

           }
            foreach ($type as $key => $types) {
                $util = $conn->query("SELECT SUM(elapsedtime) AS 'time' FROM tbl_utilization WHERE empid='".$row['empid']."' AND date= '".date('m/d/Y',strtotime($row['sched_date']))."' AND type='$types' ");

            
                $util_row = $util->fetch_assoc();
                $prod[$types] = $util_row['time'];

            }

               
           
       }

     

    $data = [$row['empid'],$row['firstname'].' '.$row['surname'],$row['team'],date('m/d/Y',strtotime($row['sched_date'])),$timein,  $timeout, $mnl_timein,$mnl_timeout , $late,($break['1st Break'] == '' ? '0' : $break['1st Break']), ($break['2nd Break']== '' ? '0' : $break['2nd Break']), ($break['Toilet Break']== '' ? '0' : $break['Toilet Break']), ($break['Lunch Break']== '' ? '0' : $break['Lunch Break']),($prod['Prod']== '' ? '0' : $prod['Prod']),  ($prod['Non-Prod']== '' ? '0' : $prod['Non-Prod']),($prod['Admin']== '' ? '0' : $prod['Admin'])  ];

        fputcsv($csv, $data);
    }

    fclose($csv);
    $conn->close();

}
function getAllTeam($conn){
 
    
$sql = $conn->query('SELECT * FROM  team WHERE name !="Management" ');
while ($row = $sql->fetch_assoc()) {
    $teams[] = $row['name'];
}


return $teams;
$conn->close();
}
function getAllEmployee($conn){
    $sql = $conn->query('SELECT firstname,surname,empinfo.empid FROM empinfo JOIN empdetails ON empinfo.empid = empdetails.detailid WHERE empdetails.team !="Management" ');
while ($row = $sql->fetch_assoc()) {
    $empid[$row['empid']] = $row['firstname'].' '.$row['surname'];
}

return $empid;
$conn->close();

}
//$conn->close();
?>