<?php 

require_once("config/configDB.php"); 
require "userPermission.php";
//require_once("config/constant.php");
error_reporting(0);

$fromdate=$_POST['fromDate'];
$todate=$_POST['toDate'];
$ecode=$_POST['ecode'];


?>
<!DOCTYPE html>
<html>
<head>
    <title>Emp Salary 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' ?>


    <form action="payroll_statement.php" method="post">
    

 
<div class=" col-sm-12" >


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

<div class=" col-sm-2">
    <label>From Date</label>
     <input type="date" class="form-control input-lg" id="fromDate" name="fromDate" autocomplete="off">
  
</div>
<div class=" col-sm-2">
    <label>To Date</label>
     <input type="date" class="form-control input-lg" id="toDate" name="toDate" autocomplete="off">
    
    
</div>


<div class=" col-sm-2">


    <label class="control-label">Employee Name </label>
	   <select  id="ecode" name="ecode" class="form-control input-lg  select-trip-drivername selectpicker"  data-live-search="true">
        <option value="">Select</option>
         <?php
		
		$sql1 = mysqli_query($conn,"SELECT * FROM employee_master WHERE isdelete=1");
		while($row1=mysqli_fetch_array($sql1))
		{
		?>
        <option value="<?php echo $row1['ecode'];?>"><?php echo $row1['ename'];?></option>
        <?php
		}
		?>	    
        </select>
</div>
<br>

</div>

<div>
&nbsp;</div>

<div class="container " align="center" >
    
    

    <button type="submit" class="btn btn-primary btn-md datewise-submit-sale">Submit</button>&nbsp;
    
   <button type="button" class="btn btn-primary" onClick="tableToExcel('a', 'W3C Example Table')">Download</button> 
</div>

    </form>


   

<br>

<div class="container col-sm-12">
<div id="a">
<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
            
            <th>S.No</th>
            <th>Date</th>
            <th>Emp.Name</th>
             <th>Category</th>
                <th>Mode</th>
                <th>Amount</th>
               
                 
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        <?php
		
		$sql_ename=mysqli_query($conn,"select * from employee_master where ecode='$ecode'");
while($row_emp=mysqli_fetch_array($sql_ename))
{
$ename=$row_emp['ename'];
$eid=$row_emp['id'];
}
		
		$sql_empadvance=mysqli_query($conn,"select sum(advance_amount) as advance from employee_advance where employee_id='$eid' and status='' and advance_date<='$fromdate'");
while($row_empadvance=mysqli_fetch_array($sql_empadvance))
{
$advance=number_format($row_empadvance['advance'],2,'.','');

}

$sql_empadvance_collection=mysqli_query($conn,"select sum(advance_collection_amount) as collection from employee_advance_collection where employee_id='$eid' and status='' and advance_date<='$fromdate'");
while($row_empadvance_collection=mysqli_fetch_array($sql_empadvance_collection))
{
$collection=number_format($row_empadvance_collection['collection'],2,'.','');

}

$balance=$advance-$collection;


$sql11 = "SELECT lm.ename as ename,sum(lad.shifttype) as shifttype,sum(lad.extraworkinghrs) as extraworkinghrs,  lad.enameid,sum(lad.advancesalary) as advancesalary FROM employee_attendance_dtl as lad LEFT JOIN employee_master as lm ON lm.id=lad.enameid where lad.isdelete=1  and lad.enameid='$eid' and lad.invoicedate BETWEEN '".$from."' AND '".$to."' GROUP BY lad.enameid ";
$query_gettotalshift      = mysqli_query($conn, $sql11);
$data_step11   = mysqli_fetch_array($query_gettotalshift,MYSQLI_ASSOC); 

$query_master_data     = mysqli_query($conn,"SELECT ename,id,extrahoursamt ,shiftworkamt FROM employee_master WHERE id=".$eid." ");
$data_step12    = mysqli_fetch_array($query_master_data,MYSQLI_ASSOC);

$shiftworkamt   = $data_step12['shiftworkamt'];
$extrahoursamt  = $data_step12['extrahoursamt'];



$sql_pending_salary=mysqli_query($conn,"select sum(salary_amount) as salaryamt,sum(paidamount)as paidamt from employee_payment where ecode='$ecode' and given_date<='$fromdate' and status=''");
while($row_pending_salary=mysqli_fetch_array($sql_pending_salary))
{

$pending_salary=$row_pending_salary['salaryamt']-$row_pending_salary['paidamt'];

}
			
			 
			
			
			$msql3=mysqli_query($conn,"select adv.advance_date as date,adv.employeename as empname,('Advance') as type,adv.paytype as mode,adv.advance_amount as amount from employee_advance as adv where adv.employee_id ='".$eid."' && adv.advance_date BETWEEN '".$fromdate."' and '".$todate."' and adv.status=''
	UNION ALL		
			
			select col.advance_date as date,col.employeename as empname,('Collection') as type,col.paytype as mode,col.advance_collection_amount as amount from employee_advance_collection as col where col.employee_id ='".$eid."' && col.advance_date BETWEEN '".$fromdate."' and '".$todate."' and col.status=''
			
			
			
			 ");
			
		
			
			//select sal.given_date as date,sal.employee_name as empname,('Salary Paid') as type,sal.paytype as mode,sal.paidamount as amount from employee_payment as sal where sal.ecode ='".$ecode."' && sal.given_date BETWEEN '".$fromdate."' and '".$todate."' and sal.status=''


        
		$Advance=0;
		$tcollection=0;
		$tsalary=0;
		$count=0;
		while($rw3=mysqli_fetch_array($msql3))
		{ 
		
					$date=date_create($rw3['date']);
$dt=date_format($date,"d-m-Y");
			
		if($rw3['type'] == 'Advance'){
		
		$Advance=$Advance + $rw3['amount'];
		
		}
		
		if($rw3['type'] == 'Salary Paid'){
		
		$tsalary=$tsalary + $rw3['amount'];
		
		}
		
		if($rw3['type'] == 'Collection'){
		
		$tcollection=$tcollection + $rw3['amount'];
		
		}
			
			
        ?>
        <tbody>
           <tr>
           <td><?php echo ++$count;?></td>
           <td><?php echo $dt;?></td>
           <td><?php echo $rw3['empname'];?></td>
		 
           <td><?php echo $rw3['type'];?></td>
            <td><?php echo $rw3['mode'];?></td>
          
           <td><?php echo number_format(round($rw3['amount']),2,'.','');?></td>
           
           </tr> 
                    
           
        </tbody>
        <?php
		}
		
		?>
        
        <tr>
           
           <td colspan="5" align="right">Previous.Adv Balance</td>
           <td><?php echo number_format(round($balance),2,'.','');?></td>
           
           </tr> 
           <tr>
           
           <td colspan="5" align="right">Current Advance</td>
           <td><?php echo number_format(round($Advance),2,'.','');?></td>
           
           </tr> 
           
           <tr>
           
           <td colspan="5" align="right">Total Advance</td>
           <td><?php echo number_format(round($Advance+$balance),2,'.','');?></td>
           
           </tr> 
           
           <tr>
           
           <td colspan="5" align="right">Total Collection</td>
           <td><?php echo number_format(round($tcollection),2,'.','');?></td>
           
           </tr>
           
            <tr>
           
           <td colspan="5" align="right">Total Adv.Pending</td>
           <td><?php echo number_format(round(($balance+$Advance)-$tcollection),2,'.','');?></td>
           
           </tr> 
          <!-- <tr>
           
           <td colspan="5" align="right">Salary Recived</td>
           <td><?php echo number_format(round($tsalary),2,'.','');?></td>
           
           </tr> -->
       
    </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>