<?php 

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

if( !isset($_SESSION['user']) ) {
 header("Location: ../index.php");
 exit;
}
                     
$datefrom = date('m/d/Y');
$dateto = date('m/d/Y');

$indifrom = date('m/d/Y');
$indito = date('m/d/Y');

if(isset($_POST['teamfilter'])){
  $teams = $_POST['team'];
$datefrom = $_POST['from'];
$dateto = $_POST['to'];

}


if(isset($_POST['indifilter'])){
 $emp = $_POST['employee'];
$indifrom = $_POST['indifrom'];
$indito = $_POST['indito'];

}

?>
<!DOCTYPE html>
<html lang="en">

  <head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Super Admin | Dashboard</title>

    <!-- Bootstrap core CSS-->
    <link href="../vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom fonts for this template-->
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">

    <!-- Page level plugin CSS-->
    <link href="../vendor/datatables/dataTables.bootstrap4.css" rel="stylesheet">

    <!-- Custom styles for this template-->
        <link href="../css/sb-admin.css" rel="stylesheet">
    <link href="../css/style.css" rel="stylesheet">
    <link rel="icon" href="../images/logo.jpg" type="image/gif" sizes="16x16">
 


  </head>

  <body id="page-top" onload=display_ct();>

    <?php include('uppernav.php')?>



      <!-- Sidebar -->
      
      <?php 
       $report = "active";
       $reportutil = "active";
      include('sidebar.php');
      ?>
      <div id="content-wrapper">

        <div class="container-fluid">

          <!-- Breadcrumbs-->
          <ol class="breadcrumb">
    
            <li class="breadcrumb-item active">Utilization</li>
          </ol>
        <ul class="nav nav-tabs" id="myTab" role="tablist">
            <li class="nav-item">
                <a class="nav-link active" id="home-tab" data-toggle="tab" href="#home" role="tab" aria-controls="home" aria-selected="true">Team</a>
            </li>
            <li class="nav-item">
                <a class="nav-link" id="profile-tab" data-toggle="tab" href="#profile" role="tab" aria-controls="profile" aria-selected="false">Individual</a>
            </li>

        </ul>
            <div class="tab-content" id="myTabContent">
                <div class="tab-pane fade show active p-2" id="home" role="tabpanel" aria-labelledby="home-tab">
                    <div class="row">

                        <div class="col-lg-12 col-sm-12 col-md-12 form-inline">
                            <form method="POST">
                                <button type="submit" class="btn btn-primary" name="teamfilter"> <i class="fa fa-filter" > </i> Filter</button>
                                <select name="team" id="team" class="form-control" required>
                                <?php 
                                $sqlteam = "SELECT team FROM empdetails GROUP BY team";
                                $teamres =  mysqli_query($conn, $sqlteam);
                                while($teamrow = mysqli_fetch_assoc($teamres)){

                                    if($teams == $teamrow['team']){
                                        echo '<option value="'.$teamrow['team'].'" selected>'.$teamrow['team'].'</option>';
                                    }else{
                                        echo '<option value="'.$teamrow['team'].'">'.$teamrow['team'].'</option>';
                                    }
                                    
                                }
                                
                                ?>
                                </select>

                                <input type="date" name="from" class="form-control" value="<?php echo $datefrom?>" required> - <input type="date" name= "to" class="form-control" value="<?php echo $dateto?>" required>

                                
                            </form>
                        </div>
                    
                    
                    </div>
                    <div class="col-lg-12 col-sm-12 col-md-12 p-2">

                      <div class="tableFixHead">
                          <?php 
                          

                          

                        //$sql = "SELECT * FROM tbl_utilization  JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE team = '$teams' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' GROUP BY activity";

                   
                       $sql = "SELECT * FROM tbl_activities WHERE team='$teams' AND activity != 'Login' AND activity != 'Log Off'";

                          
                          ?>
                          <table class="table table-bordered table-fixed table-sm"  id="teamutil"> 
                              <thead class="thead-dark text-center">
                            

                              <?php 
                              
                              
                             echo'<tr><th  colspan="4">'.$teams.'</th></tr>';
                              ?>
                              <tr class="thead-dark">

                                  <th>Activity</th>
                                  <th>Type</th>
                                  <th>Total Minutes</th>
                                  <th>Average</th>

                              
                              </tr>
                              </thead>
                              <tbody>

                              <?php
                                  $result = mysqli_query($conn, $sql);

                                  if(mysqli_num_rows($result) > 0){

                                    while($row = mysqli_fetch_assoc($result)){
                                      $activity = $row['activity'];

                                     $actsql = "SELECT activity FROM tbl_utilization WHERE activity = '$activity' AND  STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' GROUP BY date ";
                                     $actres = mysqli_query($conn, $actsql);
                                     $actno = mysqli_num_rows($actres);


                                      
                                     $sum = "SELECT SUM(elapsedtime) AS totaltime FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND team = '$teams' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' ";
                                    $sumres = mysqli_query($conn, $sum);
                                    $sumrow = mysqli_fetch_assoc($sumres);
                                    

                                    $avg = "SELECT AVG(elapsedtime) AS average FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND team = '$teams' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' ";
                                    $avgres = mysqli_query($conn, $avg);
                                    $avgrow = mysqli_fetch_assoc($avgres);

                                   // echo "TOTAL TIME : ".$sumrow['totaltime']."/ No of Activities: ".$actno."<br>";

                                    


            


                    

                                      echo'<tr><td>'.$row['activity'].'</td> 
                                      <td>'.$row['type'].'</td> ';

                                      if($sumrow['totaltime'] == ''){

                                        echo '
                                      
                                        <td> 0 </td>
                                        
                                        <td> 0 </td>';
                                        


                                      }else{

                                        $avg = $sumrow['totaltime'] / $actno;

                                                                              
                                      echo '<td>'.$sumrow['totaltime'].'</td>';
                                     echo' <td>'.number_format($avg, 2).'</td> ';


                                      }


                                    

                                    
                      



                                  }

                                  }else{

                                    echo'<tr><td colspan="4"> No data found</td></tr> ';
                                  }

                                  ?>
                              </tbody>
                          </table>

    

              
                      </div>


                    </div>

                    <div id="printteam" hidden>

                      <div style="margin:0 auto; width:50%" class="text-center"> 

            

                        <h1> <img src="../images/logo1.jpg" style=" padding:10px 20px;" >

                          ATV International ROHQ </h1>





                        </div>

                        <div class="col-12">


                        <hr class="new1">
                      </div>



                          <table class="table table-bordered table-fixed table-sm" > 
                              <thead class="thead-dark text-center">
                            

                              <?php 
                              
                              
                             echo'<tr><th  colspan="4">'.$teams.'</th></tr>';
                              ?>
                              <tr class="thead-dark">

                                  <th>Activity</th>
                                  <th>Type</th>
                                  <th>Total Minutes</th>
                                  <th>Average</th>

                              
                              </tr>
                              </thead>
                              <tbody>

                              <?php
                                  $result = mysqli_query($conn, $sql);

                                  if(mysqli_num_rows($result) > 0){

                                    while($row = mysqli_fetch_assoc($result)){
                                      $activity = $row['activity'];


                                      $actsql = "SELECT activity FROM tbl_utilization WHERE activity = '$activity' AND  STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' GROUP BY date ";
                                      $actres = mysqli_query($conn, $actsql);
                                      $actno = mysqli_num_rows($actres);
 


                                      
                                      $sum = "SELECT SUM(elapsedtime) AS totaltime FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND team = '$teams' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' ";
                                    $sumres = mysqli_query($conn, $sum);
                                    $sumrow = mysqli_fetch_assoc($sumres);

                                    $avg = "SELECT AVG(elapsedtime) AS average FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND team = '$teams' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' ";
                                    $avgres = mysqli_query($conn, $avg);
                                    $avgrow = mysqli_fetch_assoc($avgres);

                                      echo'<tr><td>'.$row['activity'].'</td> 
                                      <td>'.$row['type'].'</td> ';


                                      if($sumrow['totaltime'] == ''){

                                        echo '
                                      
                                        <td> 0 </td>
                                        
                                        <td> 0 </td>';
                                        


                                      }else{

                                        $avg = $sumrow['totaltime'] / $actno;

                                                                              
                                      echo '<td>'.$sumrow['totaltime'].'</td>';
                                     echo' <td>'.number_format($avg, 2).'</td> ';


                                      }


                                    

                                    
                      



                                  }

                                  }else{

                                    echo'<tr><td colspan="4"> No data found</td></tr> ';
                                  }

                                  ?>
                              </tbody>
                          </table>


                    </div>


                 


                    <span class="pull-right">
          
              
                      <button type="button" class="btn btn-danger" onclick="printContent('printteam')"> <i class="fas fa-file-pdf"></i> PDF</button>
                      <button onclick="exportTableToExcel('teamutil', 'teamutilization')" class="btn btn-success"> <i class="fa fa-table"></i> Excel</button>
                    </span>



                </div>
                <div class="tab-pane fade" id="profile" role="tabpanel" aria-labelledby="profile-tab">
                  <div class="row p-2">
                    <div class="col-lg-12 col-sm-12 col-md-12 form-inline">
                      <form method="POST">
                          <button type="submit" class="btn btn-primary" name="indifilter"> <i class="fa fa-filter" > </i> Filter</button>
                          <select name="employee" id="employee" class="form-control" required>
                          <?php 
                          $sqlemp = "SELECT empid, firstname, surname FROM empinfo ";
                          $empres =  mysqli_query($conn, $sqlemp);
                          while($emprow = mysqli_fetch_assoc($empres)){

                              if($emp == $emprow['empid']){
                                  echo '<option value="'.$emprow['empid'].'" selected>'.$emprow['empid'].' '.$emprow['firstname'].' '.$emprow['surname'].'</option>';
                              }else{
                                  echo '<option value="'.$emprow['empid'].'">'.$emprow['empid'].' '.$emprow['firstname'].' '.$emprow['surname'].'</option>';
                              }
                              
                          }
                          
                          ?>
                          </select>

                          <input type="date" name="indifrom" class="form-control" value="<?php echo $indifrom?>" required> - <input type="date" name= "indito" class="form-control" value="<?php echo $indito?>" required>

                          
                      </form>
                    </div>

                    <div class="col-lg-12 col-sm-12 col-md-12 p-2">

                      <div class="tableFixHead">
                          <?php 


                            $sqlempdetails = "SELECT team,firstname,surname FROM empdetails JOIN empinfo ON empdetails.detailid = empinfo.empid  WHERE detailid = '$emp'";
                            $empdetailres =mysqli_query($conn, $sqlempdetails);

                            $empdetailrow = mysqli_fetch_assoc($empdetailres);
                            $empteam = $empdetailrow['team'];
                             $empname = $empdetailrow['firstname'].' '.$empdetailrow['surname'];
                          

                          

                          $indisql = "SELECT * FROM tbl_activities WHERE team='$empteam' AND activity != 'Login' AND activity != 'Log Off'";

              
                          
                          ?>
                          <table class="table table-bordered table-fixed table-sm" id="indiutil"> 
                              <thead class="thead-dark text-center">
                            

                              <?php 
                              
                              
                                echo'<tr><th  colspan="4">'.$emp.' '.$empname.'</th></tr>';
                              ?>
                              <tr class="thead-dark">

                                  <th>Activity</th>
                                  <th>Type</th>
                                  <th>Total Minutes</th>
                                  <th>Average</th>

                              
                              </tr>
                              </thead>
                              <tbody>

                              <?php
                                  $indiresult = mysqli_query($conn, $indisql);

                                  if(mysqli_num_rows($indiresult) > 0){

                                    while($indirow = mysqli_fetch_assoc($indiresult)){
                                      $activity = $indirow['activity'];

                                      $actsql = "SELECT activity FROM tbl_utilization WHERE activity = '$activity' AND  tbl_utilization.empid = '$emp' AND STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$indifrom' AND '$indito' GROUP BY tbl_utilization.date ";
                                      $actres = mysqli_query($conn, $actsql);
                                      $actno = mysqli_num_rows($actres);


                                      
                                      $sum = "SELECT SUM(elapsedtime) AS totaltime FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND tbl_utilization.empid = '$emp' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$indifrom' AND '$indito' ";
                                      $sumres = mysqli_query($conn, $sum);
                                      $sumrow = mysqli_fetch_assoc($sumres);

                                      $avg = "SELECT AVG(elapsedtime) AS average FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND tbl_utilization.empid= '$emp' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$indifrom' AND '$indito' ";
                                      $avgres = mysqli_query($conn, $avg);
                                      $avgrow = mysqli_fetch_assoc($avgres);

                                      echo'<tr><td>'.$indirow['activity'].'</td> 
                                      <td>'.$indirow['type'].'</td> ';

                                      if($sumrow['totaltime'] == ''){

                                        echo '
                                      
                                        <td> 0 </td>
                                        
                                        <td> 0 </td>';
                                        


                                      }else{

                                        $avg = $sumrow['totaltime'] / $actno;

                                                                              
                                      echo '<td>'.$sumrow['totaltime'].'</td>';
                                     echo' <td>'.number_format($avg, 2).'</td> ';


                                      }

                                    

                                    




                                  }

                                  }else{

                                    echo'<tr><td colspan="4" > No data found</td></tr> ';
                                  }

                                  ?>
                              </tbody>
                          </table>


                      </div>


                    </div>
                    
                  </div>

                    <div id="printindi" hidden >
                              <div style="margin:0 auto; width:50%"> 

                                            

                              <img src="../images/logo1.jpg"style="float:left; padding:10px 20px;" >
                              &nbsp &nbsp &nbsp &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
                              <h1> ATV International ROHQ </h1>






                              </div>

                              <div class="col-12">


                              <hr class="new1">
                              </div>

                              <table class="table table-bordered table-fixed table-sm" > 
                                <thead class="thead-dark text-center">
                              

                                <?php 
                                
                                
                                  echo'<tr><th  colspan="4">'.$emp.' '.$empname.'</th></tr>';
                                ?>
                                <tr class="thead-dark">

                                    <th>Activity</th>
                                    <th>Type</th>
                                    <th>Total Minutes</th>
                                    <th>Average</th>

                                
                                </tr>
                                </thead>
                                <tbody>

                                <?php
                                    $indiresult = mysqli_query($conn, $indisql);

                                    if(mysqli_num_rows($indiresult) > 0){

                                      while($indirow = mysqli_fetch_assoc($indiresult)){
                                        $activity = $indirow['activity'];


                                        $actsql = "SELECT activity FROM tbl_utilization WHERE activity = '$activity' AND  STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$datefrom' AND '$dateto' GROUP BY date ";
                                        $actres = mysqli_query($conn, $actsql);
                                        $actno = mysqli_num_rows($actres);
   


                                        
                                    $sum = "SELECT SUM(elapsedtime) AS totaltime FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND tbl_utilization.empid = '$emp' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$indifrom' AND '$indito' ";
                                      $sumres = mysqli_query($conn, $sum);
                                      $sumrow = mysqli_fetch_assoc($sumres);

                                    $avg = "SELECT AVG(elapsedtime) AS average FROM tbl_utilization   JOIN empdetails ON  empdetails.detailid = tbl_utilization.empid WHERE activity ='$activity' AND tbl_utilization.empid= '$emp' AND   STR_TO_DATE(tbl_utilization.date, '%m/%d/%Y')  BETWEEN '$indifrom' AND '$indito' ";
                                      $avgres = mysqli_query($conn, $avg);
                                      $avgrow = mysqli_fetch_assoc($avgres);

                                        echo'<tr><td>'.$indirow['activity'].'</td> 
                                        <td>'.$indirow['type'].'</td> ';


                                        
                                      if($sumrow['totaltime'] == ''){

                                        echo '
                                      
                                        <td> 0 </td>
                                        
                                        <td> 0 </td>';
                                        


                                      }else{

                                        $avg = $sumrow['totaltime'] / $actno;

                                                                              
                                      echo '<td>'.$sumrow['totaltime'].'</td>';
                                     echo' <td>'.number_format($avg, 2).'</td> ';


                                      }


                                      

                                      




                                    }

                                    }else{

                                      echo'<tr><td colspan="4" > No data found</td></tr> ';
                                    }

                                    ?>
                                </tbody>
                            </table>


                    </div>

                  

                  <span class="pull-right">
          
              
                    <button type="button" class="btn btn-danger" onclick="printContent('printindi')"> <i class="fas fa-file-pdf"></i> PDF</button>
                    <button onclick="exportTableToExcel('indiutil', 'individualutil')" class="btn btn-success"> <i class="fa fa-table"></i> Excel</button>
                  </span>
                
                </div>

    
            </div>
  
          


          

          <!-- Icon Cards-->
      
        <!-- /.container-fluid -->

        <!-- Sticky Footer -->


      </div>
      <!-- /.content-wrapper -->

    </div>
    <!-- /#wrapper -->

    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
      <i class="fas fa-angle-up"></i>
    </a>

    <!-- Logout Modal-->
    <div class="modal fade" id="logoutModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
      <div class="modal-dialog" role="document">
        <div class="modal-content">
          <div class="modal-header">
            <h5 class="modal-title" id="exampleModalLabel">Ready to Leave?</h5>
            <button class="close" type="button" data-dismiss="modal" aria-label="Close">
              <span aria-hidden="true">×</span>
            </button>
          </div>
          <div class="modal-body">Select "Logout" below if you are ready to end your current session.</div>
          <div class="modal-footer">
            <button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
            <a class="btn btn-primary" href="login.html">Logout</a>
          </div>
        </div>
      </div>
    </div>

    <!-- Bootstrap core JavaScript-->
    <script src="../vendor/jquery/jquery.min.js"></script>
    <script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script>

    <!-- Core plugin JavaScript-->
    <script src="../vendor/jquery-easing/jquery.easing.min.js"></script>

    <!-- Page level plugin JavaScript-->
    <script src="../vendor/chart.js/Chart.min.js"></script>
    <script src="../vendor/datatables/jquery.dataTables.js"></script>
    <script src="../vendor/datatables/dataTables.bootstrap4.js"></script>

    <!-- Custom scripts for all pages-->
    <script src="../js/sb-admin.min.js"></script>

    <!-- Demo scripts for this page-->
    <script src="../js/demo/datatables-demo.js"></script>
    <script src="../js/demo/chart-area-demo.js"></script>

          <script language="javascript" type="text/javascript">
            $(document).ready(function(){
            $("form").on("submit", function(){
                $("#pageloader").fadeIn();
            });//submit

             $('#preloader').fadeOut();
            });//document ready



        </script>

        <script>
            $('a[data-toggle="tab"]').click(function (e) {
            e.preventDefault();
            $(this).tab('show');
            });

            $('a[data-toggle="tab"]').on("shown.bs.tab", function (e) {
                var id = $(e.target).attr("href");
                localStorage.setItem('selectedTab', id)
            });

            var selectedTab = localStorage.getItem('selectedTab');
            if (selectedTab != null) {
                $('a[data-toggle="tab"][href="' + selectedTab + '"]').tab('show');
            }



          </script>
          <script>

            function printContent(el){

            var css = '@page { size: auto}',
              head = document.head || document.getElementsByTagName('head')[0],
              style = document.createElement('style');

            style.type = 'text/css';
            style.media = 'print';

            if (style.styleSheet){
            style.styleSheet.cssText = css;
            } else {
            style.appendChild(document.createTextNode(css));
            }

            head.appendChild(style);



            var restorepage = document.body.innerHTML;
            var printcontent = document.getElementById(el).innerHTML;
            document.body.innerHTML = printcontent;
            window.print();
            document.body.innerHTML = restorepage;
            }

            function doCsv(){
            var table = document.getElementById("mytable").innerHTML;
            var data = table.replace(/<thead>/g, '')
            .replace(/<\/thead>/g, '')
            .replace(/<tbody>/g, '')
            .replace(/<\/tbody>/g, '')
            .replace(/<tr>/g, '')
            .replace(/<\/tr>/g, '\r\n')
            .replace(/<th>/g, '')
            .replace(/<\/th>/g, ';')
            .replace(/<td>/g, '')
            .replace(/<\/td>/g, '')
            .replace(/\t/g, '')
            .replace(/\n/g, '');

            var mylink = document.createElement('a');
            mylink.download = "csvname.csv";
            mylink.href = "data:application/csv," + escape(data);
            mylink.click();


            }

            function exportTableToExcel(tableID, filename = ''){
              var downloadLink;
              var dataType = 'application/vnd.ms-excel';
              var tableSelect = document.getElementById(tableID);
              var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
              
              // Specify file name
              filename = filename?filename+'.xls':'excel_data.xls';
              
              // Create download link element
              downloadLink = document.createElement("a");
              
              document.body.appendChild(downloadLink);
              
              if(navigator.msSaveOrOpenBlob){
                  var blob = new Blob(['\ufeff', tableHTML], {
                      type: dataType
                  });
                  navigator.msSaveOrOpenBlob( blob, filename);
              }else{
                  // Create a link to the file
                  downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
              
                  // Setting the file name
                  downloadLink.download = filename;
                  
                  //triggering the function
                  downloadLink.click();
              }
            }

            </script>
</body>

</html>
