<?php 

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

if( !isset($_SESSION['user']) ) {
 header("Location: ../index.php");
 exit;
}
$date = date('04/27/2019');


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);
      
      
      
      
      }

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

        $team = phps($_POST['team']);
         $date = phps($_POST['date']);
          $date = date('m/d/Y', strtotime($date));
          $dateval = date('Y-m-d', 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 = '$date' 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 ='$date' ";
          $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 = '$date' 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 = '$date' 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 ='$date' 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 = '$date' 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 = '$date' 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 ='$date' ";
     $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 = '$date' GROUP BY tbl_utilization.empid ";    
   
   
    }


?>
<?php

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) { 
        $emp = $row['empid'];

        $login = "SELECT * FROM `tbl_login` WHERE empid= '$emp' AND date = '$date'";
        $loginres = $conn->query($login);
        $loginrow = $loginres->fetch_assoc();

        
        $logoff = "SELECT * FROM `tbl_login` WHERE empid= '$emp' AND date = '$date'";
        $logoffres = $conn->query($logoff);
        $logoffrow = $logoffres->fetch_assoc();




        $curact= "SELECT * FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' ORDER BY id DESC  LIMIT 1";
        $actres = $conn->query($curact);
        $currentact = $actres->fetch_assoc();



        $prodsql = "SELECT SUM(elapsedtime) AS 'prod'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND type='Prod'";
        $prodresult = $conn->query($prodsql);
        $prodrow = $prodresult->fetch_assoc();

    
        

        $adminsql = "SELECT SUM(elapsedtime) AS 'admin'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND type='Admin'";
        $adminresult = $conn->query($adminsql);
        $adminrow = $adminresult->fetch_assoc();

        $nonprod = "SELECT SUM(elapsedtime) AS 'nonprod'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND type='Non-Prod'";
        $nonprodresult = $conn->query($nonprod);
        $nonprodrow = $nonprodresult->fetch_assoc();
        

        $break = "SELECT SUM(elapsedtime) AS 'break'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND type='Break'";
        $breakres = $conn->query($break);
        $breakrow = $breakres->fetch_assoc();

        $break1 = "SELECT SUM(elapsedtime) AS 'break1'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND activity='1st Break'";
        $break1res = $conn->query($break1);
        $break1row = $break1res->fetch_assoc();

        $break2 = "SELECT SUM(elapsedtime) AS 'break2'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND activity='2nd Break'";
        $break2res = $conn->query($break2);
        $break2row = $break2res->fetch_assoc();

        
        $lunchbreak = "SELECT SUM(elapsedtime) AS 'lunch'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND activity='Lunch Break'";
        $lunchbreakres = $conn->query($lunchbreak);
        $lunchbreakrow = $lunchbreakres->fetch_assoc();


        $toilet = "SELECT SUM(elapsedtime) AS 'toilet'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' AND activity='Toilet Break'";
        $toiletres = $conn->query($toilet);
        $toiletrow = $toiletres->fetch_assoc();

        $total = "SELECT SUM(elapsedtime) AS 'totaltime'  FROM `tbl_utilization` WHERE empid= '$emp' AND date = '$date' ";
        $totalres = $conn->query($total);
        $totalrow = $totalres->fetch_assoc();

        if($prodrow['prod'] == '' || $prodrow['prod'] == '0'){

            $prodpercent = '0';
        

        }else{

            $prodpercent = ($prodrow['prod'] / $totalrow['totaltime']) * 100;

        }

        if($adminrow['admin']  == '' || $adminrow['admin']  == '0'){

            $adminpercent = '0';

        }else{

            $adminpercent = ($adminrow['admin'] / $totalrow['totaltime']) * 100;

        }

        if($nonprodrow['nonprod'] == '' || $nonprodrow['nonprod'] == '0'){

            $nonprodpercent = '0';

        }else{

            $nonprodpercent = ($nonprodrow['nonprod'] / $totalrow['totaltime']) * 100;

        }
        if($breakrow['break'] == '' || $breakrow['break'] == '0'){
            $breakpercent = '0';
            
        

        }
        else{
            $breakpercent = ($breakrow['break'] / $totalrow['totaltime']) * 100;
        }





        
            
            



            if($break1row['break1'] > 15){

                $break1percent =  $break1row['break1'] - 15;
            
            }else{
                $break1percent = 0;
            }

            if($break2row['break2'] > 15){

                $break2percent =  $break2row['break2'] - 15;
                
            }else{
                $break2percent = 0;
            }

            if($lunchbreakrow['lunch']  > 60 ){
                $lunchpercent = $lunchbreakrow['lunch'] - 60;
                

            }else{
                $lunchpercent = 0;
            }
            if($toiletrow['toilet'] > 10){
                $toiletpercent = $toiletrow['toilet'] - 10;
            }else{
                $toiletpercent = 0;
            }



        


        






        

        ?>

    
        <div class="col-lg-12 col-sm-12 col-md-12" style="font-size:13px">
            <div class="row">
            <div class="col-sm-12" >
                <div class="card p-1">
                    <div class="card-body p-1">
                        <a href="viewutil.php?empid=<?php echo $emp?>&date=<?php echo $date?>&team=<?php echo  $row['team']?>" class="btn btn-info btn-sm float-right">View</a>
                        <h5 class="card-title"><?php echo $row['firstname'].' '.$row['surname'] ?></h5> 
                        <h6 class="card-title"><?php echo $row['team']?> | <?php echo $row['empid'] ?></h6>
                

                        <div class="row">

                        <div class="col-lg-4"> <h6>Details</h6></div>
                        <div class="col-lg-4"> <h6>Summary</h6></div>
                        <div class="col-lg-4"> <h6>Breaks</h6></div>

                        <div class="col-lg-4">

                            DATE: <?php echo $date?> <br>
                            LOGIN : <?php echo date('h:i A', strtotime($loginrow['timein']))?> <br>
                            LOGOUT: <?php echo $logoffrow['timeout']?> <br>
            
                            TOTAL TIME: <?php echo $totalrow['totaltime']?><br>
                            CURRENT : <?php echo $currentact['activity']?> 

                        
                            
                        </div>
                        <div class="col-lg-4">
                            
                
                    
                            <table class= "table table-sm table-borderless" style="width:100%; font-size:13px;">
                            <tr>
                                <th> </th>
                                <th>Mins</th> 
                                <th>Percent</th>
                            </tr>
                            <tr>
                                <td>PROD : </td>
                                <td><?php 
                                if($prodrow['prod'] == ''){
                                    echo '0';
                                }
                                else{

                                    echo $prodrow['prod'];

                                }
                                ?> </td> 
                                <td><?php echo round($prodpercent, 0)?> %  </td>
                            </tr>
                            <tr>
                                <td>ADMIN:</td>
                                <td><?php 
                                if($adminrow['admin'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $adminrow['admin'];
                                }
                            
                                
                                ?></td> 
                                <td><?php echo round($adminpercent, 0)?> %  </td>
                            </tr>

                            <tr>
                                <td>NON-PROD:</td>
                                <td><?php 
                                if($nonprodrow['nonprod'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $nonprodrow['nonprod'];
                                }
                                
                                
                                ?> </td> 
                                <td><?php echo round($nonprod, 0)?> %  </td>
                            </tr>

                            <tr>
                                <td>BREAK:</td>
                                <td><?php 
                                if($breakrow['break'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $breakrow['break'];
                                }
                                
                                
                                ?> </td>
                                <td><?php echo round($breakpercent, 0)?> %  </td>
                            </tr>

                            
                            </table>

            

                            
                
                
                        </div>

                        <div class="col-lg-4" >

                        <table class= "table table-sm table-borderless" style="width:100%; font-size:13px;">
                            <tr>
                                <th> </th>
                                <th>Mins</th> 
                                <th>OB</th>
                            </tr>
                            <tr>
                                <td>1ST RBEAK: </td>
                                <td><?php 
                                if($break1row['break1'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $break1row['break1'];
                                }
                                
                                
                                ?> </td>
                                <td><?php echo round($break1percent, 0)?>   </td>
                            </tr>
                            <tr>
                                <td>2ND BREAK: </td>
                                <td><?php 
                                if($break2row['break2'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $break2row['break2'];
                                }
                                
                                
                                ?> </td>
                                <td><?php echo round($break2percent, 0)?>  </td>
                            </tr>

                            <tr>
                                <td>LUNCH BREAK:</td>
                                <td><?php 
                                if($lunchbreakrow['lunch'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $lunchbreakrow['lunch'];
                                }
                                
                                
                                ?> </td>
                                <td><?php echo round($lunchpercent, 0)?>  </td>
                            </tr>

                            <tr>
                                <td>TOILET BREAK:</td>
                                <td><?php 
                                if($toiletrow['toilet'] == ''){
                                    echo '0'; 
                            

                                }else{
                                    echo $toiletrow['toilet'];
                                }
                                
                                
                                ?> </td>
                                <td><?php echo round($toiletpercent, 0)?>  </td>
                            </tr>
                        </table>

                            



                            

                            
                        </div>

            
                        </div>
                        
                
                    </div>
                </div>
                </div>



                
            </div>







        </div>


    <?php

        }
    } else {
        echo "No utilization found";
    }


    mysqli_close($conn);

    ?>

            