<?php 
include('../dbconnect.php');
include('../phps.php');
ob_start();
session_start();

if( !isset($_SESSION['user']) ) {
 header("Location: ../index.php");
 exit;
}
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);
  
  
}
  
$date = date('Y-m-d');
$dateval = date('m/d/Y', strtotime($date));


      if(isset($_POST['team']) && isset($_POST['date'])){

        $team = phps($_POST['team']);
         $date = phps($_POST['date']);
         
          $dateval = date('m/d/Y', strtotime($date));

       
   
   
       if($team == 'All'){
          
   
   
   
           $emplist = "SELECT * FROM empinfo JOIN empdetails ON empinfo.empid = empdetails.detailid  AND status='Active'";
           $sql = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' GROUP BY tbl_utilization.empid ";
          $emplist1 = "SELECT *,tbl_login.date AS 'utildate' FROM tbl_login JOIN empdetails ON tbl_login.empid = empdetails.detailid JOIN empinfo ON  tbl_login.empid = empinfo.empid WHERE tbl_login.date ='$dateval' ";
          $sql1 = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' GROUP BY tbl_utilization.empid ";
   
   
   
       }
       else{
   
           $emplist = "SELECT * FROM empinfo JOIN empdetails ON empinfo.empid = empdetails.detailid WHERE empdetails.team = '$team'  AND status='Active'";
           $sql = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' AND team='$team' GROUP BY tbl_utilization.empid ";
   
          $emplist1 = "SELECT *,tbl_login.date AS 'utildate' FROM tbl_login JOIN empdetails ON tbl_login.empid = empdetails.detailid JOIN empinfo ON  tbl_login.empid = empinfo.empid WHERE tbl_login.date ='$dateval' AND team ='$team'  ";
          $sql1 = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' AND team='$team' GROUP BY tbl_utilization.empid";
   
                 
       }
   
   
   
    }else{
   
       $emplist = "SELECT * FROM empinfo JOIN empdetails ON empinfo.empid = empdetails.detailid  AND status='Active'";
       $sql = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' GROUP BY tbl_utilization.empid ";
     $emplist1 = "SELECT *,tbl_login.date AS 'utildate' FROM tbl_login JOIN empdetails ON tbl_login.empid = empdetails.detailid JOIN empinfo ON  tbl_login.empid = empinfo.empid WHERE tbl_login.date ='$dateval' ";
     $sql1 = "SELECT * FROM `tbl_utilization` JOIN empinfo ON tbl_utilization.empid = empinfo.empid  JOIN empdetails ON tbl_utilization.empid = empdetails.empid WHERE tbl_utilization.date = '$dateval' GROUP BY tbl_utilization.empid ";    
   
   
    }
    ?>

                        <?php 

                
                            $empres = mysqli_query($conn, $emplist);

                        ?>
                        <table class="table table-hover table-sm">
                            <thead class="thead-dark">
                                <th>Employee ID</th>
                                <th>Name</th>
                                <th>Schedule </th>
                                <th>Login </th>
                                <th>Late </th>
                            </thead>
                            <tbody id="tbodyatt">
                            <?php
   
                            while($emprow = mysqli_fetch_assoc($empres)){

                                    $employee = $emprow['empid'];
                                    $schedweek = date('W', strtotime($dateval));
                                    
                                                                //get schedule
                                                
                            $schedsql="SELECT * FROM schedule WHERE empid='$employee' AND date='$date' ";
                            $schedres = mysqli_query($conn, $schedsql);
                            $schedrow = mysqli_fetch_assoc($schedres);

                           

                            $lilosql ="SELECT * FROM tbl_login WHERE empid='$employee' AND date='$dateval'";
                            $lilores =  mysqli_query($conn, $lilosql);
                            $lilorow = mysqli_fetch_assoc($lilores);

                            $weeks = date('W', strtotime($date));
                            $yrs = date('Y', strtotime($date));
                            
                            $gethrs = "SELECT * FROM schedule WHERE empid='$employee' AND year='$yrs' AND week='$weeks' AND starttime !='OFF' AND starttime !='HOLIDAY OFF' LIMIT 1";
                            $gehrsres = mysqli_query($conn, $gethrs);
                            $gethrsrow = mysqli_fetch_assoc($gehrsres);
                            $hrs = $gethrsrow['starttime'];



                             $getidl  ="SELECT * FROM schedule WHERE empid='$employee' AND pstdate='$date'";
                            $getidlres = mysqli_query($conn, $getidl);

                            $getidlrow = mysqli_fetch_assoc($getidlres);
                               $idl = $getidlrow['idl'];



                   

                            $leavesql ="SELECT * FROM `leave` WHERE idl='$idl' AND status='3'";
                            $leaveres = mysqli_query($conn, $leavesql);
                            $leaverow = mysqli_fetch_assoc($leaveres);


          


                         
                       

                            echo'<tr> 
                            <td>'.$emprow['empid'].'</td>
                            <td>'.$emprow['firstname'].' '.$emprow['surname'].'</td>';


                            if(empty($lilorow['timein'])){

                                $login = '---';
                                $late = '0';

                                if($schedrow['pststarttime'] == 'OFF'){
                                    $pststarttime ='<span class="text-danger">OFF</span>';
                     
                           
                                 
                                }else if($schedrow['pststarttime'] == 'HOLIDAY OFF'){

                                    $pststarttime ='<span class="text-danger">HOLIDAY OFF</span>';
                  
                             

                                }else if(!empty($leaverow['name'])){

                                    $pststarttime = '<span class="text-danger">'.$leaverow['name'].'</span>';
              
                                    
                                }else if(empty($schedrow['starttime'])){
                                    $pststarttime ='---';
                        
                          


                                }else{

                                    $pststarttime = date('h:i A', strtotime($schedrow['pststarttime']));
                                    $pstendtime = date('h:i A', strtotime($schedrow['pstendtime']));
                                    $login = '<span class="text-danger">ABSENT</span>';


                                }

                            }else{
                                $login = date('h:i A', strtotime($lilorow['timein']));
                                $late = '0';

                                if(!empty($leaverow['name'])){

                                    $pststarttime = '<span class="text-danger">'.$leaverow['name'].'Halfday</span>';
                            
                                 
                                    
                                }else if($schedrow['pststarttime'] == 'OFF'){
                                    $pststarttime ='<span class="text-danger">OFF</span>';
                             



                                }else{

                                    
                                    $pststarttime = date('h:i A', strtotime($schedrow['pststarttime']));
                             

                                    $pststart = new DateTime($pststarttime);
                                    $timein = new DateTime($login);


                                    if($timein > $pststart){
                                        $late = $timein->diff($pststart);
                                        $late = $late->format('%H:%I');
                                    }else{
                                        $late ='0';
                                    }




                                }


                            }

                            echo '
                            <td>'.$pststarttime.'</td>
                        
                            <td>'.$login.'</td>
                            <td>'.$late.'</td>';


                            ///Late computation 

                            




                           
                              

                                
                            }
                            

/*                             if(mysqli_num_rows($loginres) == 0){

                                $mnltimein = 'ABSENT';
    
                               
    
    
                              }else{
                                $timein = date('h:i A', strtotime($loginrow['timein']));
                                $mnltimein = psttomnl($logindate, $timein);
                                $mnltimein = date('h:i A', strtotime($mnltimein));
    
                              }

                              
            


                                    echo'<tr> 
                                    <td>'.$emprow['empid'].'</td>
                                    <td>'.$emprow['firstname'].' '.$emprow['surname'].'</td>';
                                    <td>'.$starttime.' - '.$endtime.'</td>
                                    <td>'.$mnltimein.'</td>
                                    </tr>';
                                }
 */
                                
                                ?>
                               
                            </tbody>

                        </table>

             

     