<?php 
error_reporting(0);
require_once("config/configDB.php");
require_once("config/constant.php");

$sql_customer = mysqli_query($conn,"SELECT sh.employee_id,cm.ename FROM employee_advance as sh  join employee_master as cm on cm.id=sh.employee_id WHERE cm.isdelete='1' and sh.status='' GROUP BY sh.employee_id order by cm.ename asc");

$row_customer = '';




$tripfromDate=$_POST['fromDate'];
				$triptoDate=$_POST['toDate'];
			
				


?>
<!DOCTYPE html>
<html>
<head>
    <title>Emp.Paymen Report</title>
    <?php require 'header.php' ?>
    <link href = "https://code.jquery.com/ui/1.10.4/themes/ui-lightness/jquery-ui.css"
         rel = "stylesheet">
<script >
var tableToExcel = (function() {
  var uri = 'data:application/vnd.ms-excel;base64,'
    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
  return function(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()

</script>
</head>
<body>
    <?php require 'menu.php' ?>


    <div class="container text-center" id="saleStock">
  
  <form class="form-inline" method="post" action="salary_details.php">
    
    <div class="form-group">
      <label for="email">From Date:</label>
      <input type="date" class="form-control" id="fromDate" name="fromDate" autocomplete="off">
    </div>
    <div class="form-group">
      <label for="pwd">To Date</label>
      <input type="date" class="form-control" id="toDate" name="toDate" autocomplete="off">
    </div>


    
    

    <button type="submit" class="btn btn-primary btn-md datewise-submit-sale">Submit</button>

    <button type="button" class="btn btn-info btn-md datewise-submit-sale" onClick="tableToExcel('a', 'W3C Example Table')">Excel</button>
    
  </form>
  
</div>



    <div class="container col-sm-12">
    &nbsp;
    </div>



<div class="container col-sm-12">
<div id="a">
<table class="table table-responsive table-striped table-bordered dt-responsive nowrap" id="accLabourDetailsAdd" cellspacing="0">
          <thead>
          <caption>Attendance&nbsp;Salary</caption>
            <tr>
              <th>S.No</th>
              <th>Date</th>
              <th>Emp.Name</th>
              <th>Total&nbsp;Working&nbsp;Shift </th>
              <th>Extra Working Hours</th>
               <th>Salary/Shift</th> 
              <th>Salary/Extra Hour</th>
              <th>Shift&nbsp;Salary</th>
              <th>OT&nbsp;Salary</th>
              <th>Total&nbsp;Salary</th>
              <th>Advance.Amt</th>
               <th>Collected&nbsp;Advance</th>
               <th>Remaining&nbsp;Salary</th>
            </tr>
          </thead>
          <tbody>
          <?php
		  
		  if($tripfromDate !=''  && $triptoDate !=''   ) { 

$sql1 ="SELECT att.invoicedate,emp.ename,att.enameid,sum(att.shifttype) as shifttype,sum(att.extraworkinghrs) as extraworkinghrs,emp.shiftworkamt,emp.extrahoursamt FROM employee_attendance_dtl as att inner join employee_master as emp on att.enameid=emp.id  WHERE  invoicedate between '".$tripfromDate."' AND '".$triptoDate."'  and att.isdelete=1 group by  att.enameid  ";


}
//echo $sql1;

$Query_Adv  = mysqli_query($conn,$sql1); 

		  
            $trlength = 0;
			$att_sal=0;
            //lm.ename,lad.shifttype,lad.extraworkinghrs,lad.shiftworkamt,lad.extrahoursamt,lad.enameid
            while($drow = mysqli_fetch_array($Query_Adv))
            {
			
			$empid=$drow['enameid'];
			
			$totalshifsalary=$drow['shifttype']*$drow['shiftworkamt'];
			$totalotsalary=$drow['extraworkinghrs']*$drow['extrahoursamt'];
			
			$totalsalary=$totalshifsalary+$totalotsalary;
			
			
			
			$attdate=date_create($drow['invoicedate']);
$att_date=date_format($attdate,"d-m-Y");


$sql_advance="SELECT sum( advance_amount) as advance FROM employee_advance   WHERE status=''   and advance_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and employee_id='$empid'  ";
	$query_advance=mysqli_query($conn, $sql_advance) ;
while($row_advance=mysqli_fetch_array($query_advance))
{


$sql_collection="SELECT sum(advance_collection_amount) as adv_collection FROM employee_advance_collection  WHERE status=''    and advance_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and employee_id='$empid'  ";
$query_collection=mysqli_query($conn, $sql_collection) ;
while($row_colection=mysqli_fetch_array($query_collection))
{


$remaining_adv=$row_advance['advance']-$row_colection['adv_collection'];

$remaining_salary=$totalsalary-($remaining_adv);

              ?>
              <tr>
              <td><?php echo ++$trlength;?></td>
               <td><?php echo $att_date; ?></td>
              <td><?php echo $drow['ename']; ?></td>
              <td><?php echo $drow['shifttype']; ?></td>
              <td><?php echo $drow['extraworkinghrs']; ?></td>
              <td><?php echo $drow['shiftworkamt']; ?></td>
              <td><?php echo $drow['extrahoursamt']; ?></td>
              <td align="right"><strong><?php echo number_format($totalshifsalary,2,'.','');?></strong></td>
              
               <td align="right"><strong><?php echo number_format($totalotsalary,2,'.','');?></strong></td>
                <td align="right"><strong><?php echo number_format($totalsalary,2,'.','');?></strong></td>
              
               <td align="right"><strong><?php echo number_format($row_advance['advance'],2,'.','');?></strong></td>
               <td align="right"><strong><?php echo number_format($row_colection['adv_collection'],2,'.','');?></strong></td>
               <td align="right"><strong><?php echo number_format($remaining_salary,2,'.','');?></strong></td>
              </tr>
              
              
              
              <?php
                $att_sal+=$totalshifsalary;
            }
			}
			}
          ?>
         
          </tbody>
        </table>

</div>
</div>



    <?php require 'footer.php' ?>

    <script type="text/javascript">var base_url = "<?= $base_url ?>";</script>
  
    <script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js"></script>

    
</body>
</html>