<?php 

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

if( !isset($_SESSION['user']) ) {
 header("Location: ../index.php");
 exit;
}
$search = phps($_POST['search']);
$filter = phps($_POST['filter']);

$search= mysqli_real_escape_string($conn, $search);
$filter= mysqli_real_escape_string($conn, $filter);

?>
<!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 href="../css/style.css" rel="stylesheet">
    <link rel="icon" href="../images/logo.jpg" type="image/gif" sizes="16x16">
    
    <style>
.tableFixHead    { overflow-y: auto; height: 650px; }
.tableFixHead th { position: sticky; top: 0; }

/* Just common table stuff. Really. */
table  { border-collapse: collapse; width: 100%; }
th, td { padding: 8px 16px; }
th     { background:#eee; }


@media print {
.red {
  background-color: red;
}


}

hr.new1 {
  border-top: 2px solid #333;
}


    </style>






  </head>



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

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



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

        <div class="container-fluid">

          <!-- Breadcrumbs-->
          <ol class="breadcrumb">
            <li class="breadcrumb-item">
              <a href="#" > Employee Status</a>
            </li>

          </ol>
          <div class="row p-3" >
            <div class="col-sm-12 form-inline">
            <form method="POST">
                <button type="submit" class="btn btn-primary" name="searchb"><i class="fas fa-search"> </i> Search </button> &nbsp
                <input type="text" class="form-control" name="search" placeholder="Search here..." value="<?php echo $search?>">  &nbsp
                <button type="submit" class="btn btn-primary" name="filterb"><i class="fas fa-filter"> </i> Filter </button> &nbsp
                <select name="filter" class="form-control">
                <option value=''> All </option>

                <?php 
                                 
                    $filters = "SELECT team FROM empdetails GROUP BY team";
                    $filterres = mysqli_query($conn, $filters);
          
                    while($filterrow = mysqli_fetch_assoc($filterres)){
                      if($filter == $filterrow['team']){
                        echo '<option value="'.$filterrow['team'].'" selected = "selected">'.$filterrow['team'].'</option>';

                      }
                      else { 
                        echo '<option value="'.$filterrow['team'].'">'.$filterrow['team'].'</option>';

                      }
                   
                    }
                ?>
              
                </select>
              
            </form>

            </div>
            <div class="col-sm-12 form-group">
            </div>
          <div class="col-sm-12">
          <div class="tableFixHead">
            <table class="table table-bordered table-fixed" > 
                <thead class="thead-dark text-center">
                <tr class="thead-dark">
                    <th>Employee ID</th>
                    <th>Employee Name</th>
                    <th>Team</th>
                    <th>Position</th>
                    <th>Job Title</th>
                    <th>Status</th>
                    <th>Salary Code</th>
                    <?php
                            $monetary = array();

                        $compensql = "SELECT * FROM monetary";
                        $compenresult = mysqli_query($conn, $compensql);
                        while($comprow = mysqli_fetch_assoc($compenresult)){

                            echo'<th>'.$comprow['name'].'</th>';
                            $monetary[] =  $comprow['monetaryid'];


                        }


                    
                    
                    ?>
                     <th>TOTAL</th>
                </tr>
                </thead>
                <tbody>
                    <?php 


                    $sql = "SELECT * FROM empdetails JOIN empinfo ON empdetails.empid = empinfo.empid";

                  


                    if(isset($_POST['searchb'])){
                      
                        $sql = "SELECT * FROM empdetails LEFT JOIN empinfo ON empdetails.detailid = empinfo.empid WHERE CONCAT(empinfo.empid, empinfo.surname, empinfo.firstname,empdetails.team, empdetails.generalpos, empdetails.status, empdetails.hireddate, empdetails.inactivedate)  LIKE '%".$search."%'";

                    }
                    else if(isset($_POST['filterb'])){
                      if($filter == ''){
                        $sql = "SELECT * FROM empdetails JOIN empinfo ON empdetails.empid = empinfo.empid";
                      }
                      else{
                        $sql = "SELECT * FROM empdetails LEFT JOIN empinfo ON empdetails.detailid = empinfo.empid WHERE team='$filter'";
                      }
                      


                    }
                        $compensation = array();
                        $amount = array();
                    $result = mysqli_query($conn, $sql);
                    while($row = mysqli_fetch_assoc($result)){

                      
                        $empid = $row['empid'];
                        echo'<tr> <td>'.$row['empid'].'</td>
                        <td>'.$row['firstname'].' '.$row['surname'].'</td>
                        <td>'.$row['team'].'</td>
                        <td>'.$row['generalpos'].'</td>
                        <td>'.$row['jobtitle'].'</td>
                        <td>'.$row['status'].'</td>';

                      $salaryempsql ="SELECT * FROM salaryemp JOIN compensation ON salaryemp.salarycode = compensation.salarycode WHERE empid='$empid' AND effectivitydate <= CURDATE() ORDER BY effectivitydate DESC LIMIT 1";


                        $salaryempres = mysqli_query($conn,$salaryempsql);
                        $salaryemprow = mysqli_fetch_assoc($salaryempres);
                         $cursalarycode = $salaryemprow['salarycode'];
                         echo '<td>'.$salaryemprow['salarycode'].'</td>';

                        $compensql = "SELECT * FROM compensation WHERE salarycode='$cursalarycode'  ORDER BY monetaryid";
                        $compenresult = mysqli_query($conn, $compensql);

                        while($compenrow = mysqli_fetch_assoc($compenresult)) {

                            $compensation[] = $compenrow['monetaryid'];
                            $amount[] = $compenrow['amount'];
                                                




                            
                        

                        }


                        for($c=0 ; $c < count($monetary); $c++){

                            if( $compensation[$c] == $monetary[$c]){

                              $amounts = floatval(preg_replace('/[^\d.]/', '', $amount[$c]));


                             // var_dump((float)$amounts);

                               echo' <td>'.$amount[$c].'</td>';

                               $total = (float)$total;

                            
                             $total += (float)$amounts;
                               
                            }else{
                                echo' <td> --- </td>';

                            }
                        }
                          
                        echo'<td>'.number_format((float)$total, 2).'</td>';
                       
                        $total = '';
                        $compensation  = array(); 
                        $amount  = array(); 



                    }
                    ?>
                </tbody>
            </table>

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

          
          
          </div>
       
        </div>

          

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

        <div id="printarea"  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>


      

        
    
 
        <div class="col-12"> <h3> <?php echo $filter?> Employee List </h3> <p><?php echo $_SESSION['user']; ?> | <?php echo date('m/d/Y')?></p></div>  
     
        
          <table class="table table-bordered table-sm" id="mytable">
          <thead class="thead-dark text-center" class="red">
                <tr class="thead-dark">
                    <th>Employee ID</th>
                    <th>Employee Name</th>
                    <th>Team</th>
                    <th>Position</th>
                    <th>Job Title</th>
                    <th>Status</th>
                    <th>Salary Code</th>

                    <?php
                            $monetary = array();

                        $compensql = "SELECT * FROM monetary";
                        $compenresult = mysqli_query($conn, $compensql);
                        while($comprow = mysqli_fetch_assoc($compenresult)){

                            echo'<th>'.$comprow['name'].'</th>';
                            $monetary[] =  $comprow['monetaryid'];


                        }


                    
                    
                    ?>
                     <th>TOTAL</th>

                </tr>
                </thead>
                <tbody>
                    <?php 

                    $sql1 = $sql;




                    $result1 = mysqli_query($conn, $sql1);
                    while($row1 = mysqli_fetch_assoc($result1)){
                        $empid = $row1['empid'];
                        echo'<tr> 
                        <td>'.$row1['empid'].'</td>
                        <td>'.$row1['firstname'].' '.$row1['surname'].'</td>
                        <td>'.$row1['team'].'</td>
                        <td>'.$row1['generalpos'].'</td>
                        <td>'.$row1['jobtitle'].'</td>
                        <td>'.$row1['status'].'</td>';


                        $salaryempsql ="SELECT * FROM salaryemp JOIN compensation ON salaryemp.salarycode = compensation.salarycode WHERE empid='$empid' AND effectivitydate <= CURDATE() ORDER BY effectivitydate DESC LIMIT 1";


                        $salaryempres = mysqli_query($conn,$salaryempsql);
                        $salaryemprow = mysqli_fetch_assoc($salaryempres);
                         $cursalarycode = $salaryemprow['salarycode'];
                         echo '<td>'.$salaryemprow['salarycode'].'</td>';

                        $compensql = "SELECT * FROM compensation WHERE salarycode='$cursalarycode'  ORDER BY monetaryid";
                        $compenresult = mysqli_query($conn, $compensql);

                        while($compenrow = mysqli_fetch_assoc($compenresult)) {

                            $compensation[] = $compenrow['monetaryid'];
                            $amount[] = $compenrow['amount'];
                                                




                            
                        

                        }


                        for($c=0 ; $c < count($monetary); $c++){

                            if( $compensation[$c] == $monetary[$c]){

                              $amounts = floatval(preg_replace('/[^\d.]/', '', $amount[$c]));

                               echo' <td>'.$amount[$c].'</td>';

                               $total = (float)$total;
                           
                               
                              $total += $amounts;
                               
                            }else{
                                echo' <td> --- </td>';

                            }
                        }

                        echo'<td>'.number_format((float)$total, 2).'</td>';
                        $total = '';

                        $compensation  = array(); 
                        $amount  = array(); 






                    }
  
                    ?>
                </tbody>
          
          </table>
          
     

        </div>

        

        

        <!-- 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 src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/1.5.3/jspdf.min.js"></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>

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

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



        </script>
</body>

</html>
