<?php 

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




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

$fdate=date_create($fromdate);
$tdate=date_create($todate);

//echo date_format($fdate,"d-m-Y");

$sql_empname=mysqli_query($conn,"select * from employee_master where id='$customerName'");

while($row_emp=mysqli_fetch_array($sql_empname))
{

$empname=$row_emp['ename'];
}


?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">

    <title>Stock 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" action="advance_statement.php" method="post">
    
    <div class="form-group">
      <label for="email">From Date:</label>
      <input type="date" class="form-control" id="fromDate" name="fromDate" autocomplete="off" required>
    </div>
    <div class="form-group">
      <label for="pwd">To Date</label>
      <input type="date" class="form-control" id="toDate" name="toDate" autocomplete="off" required>
    </div>

      <div class="form-group">
      <label for="pwd">Please Select the Employee </label>
      <select class="form-control  supplierStock" name="customerName" required>
      <option value="">Select The Employee</option>
         <?php
		$emp=mysqli_query($conn,"select ename,id from employee_master  group by ename");
        while($rowe = mysqli_fetch_array($emp)){
        ?>
        <option value="<?php echo $rowe['id'] ?>" > <?php echo $rowe['ename']; ?> </option>
        <?php
        }
        ?>
        </select>
    </div>
   
    
 
  
</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> 


    </form>
</div>

   

<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%">

<caption>
<p style="color:#000000;font-weight:bold;">EMP.Name:<?Php echo $empname;?></p>
<p style="color:#000000;font-weight:bold;">Report&nbsp;Date:(<?php echo date_format($fdate,"d-m-Y");?>&nbsp;-&nbsp;<?php echo date_format($tdate,"d-m-Y");?>)

</caption>

        <thead>
            <tr>
            
            <th>S.No</th>
            <th>Date</th>
            <th>Category</th>
           
            <th>Mode</th>
                <th>Paid Advance</th>
                
                <th>Collection</th>
                
               
                 
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        <?php
		$msql=mysqli_query($conn,"select sum(advance_amount) as opening_advance from employee_advance where advance_date<'".$fromdate."' and employee_id='".$customerName."' and status=''");
		
		
        
		while($rw=mysqli_fetch_array($msql))
		{
			
		$opening_advance=$rw['opening_advance'];	
		}
			
		
        $msql1=mysqli_query($conn,"select sum(advance_collection_amount) as opening_collamount from employee_advance_collection where advance_date<'".$fromdate."' and employee_id='".$customerName."' and status=''");
		
        
		while($rw1=mysqli_fetch_array($msql1))
		{
			
		$opening_collamount=$rw1['opening_collamount'];	
		
		}
			
			
			
						
			$openingbalance=($opening_advance) - ($opening_collamount);
			
			 
			
			
			$msql3=mysqli_query($conn,"select adv.advance_date as date,adv.employee_id as eid,adv.employeename as ename,('Advance') as category,adv.transaction_mode as mode,adv.advance_amount as advamt,('-') as collection from employee_advance as adv  where adv.employee_id ='".$customerName."' && adv.advance_date BETWEEN '".$fromdate."' and '".$todate."' and adv.status=''
			 


UNION ALL

select advc.advance_date as date,advc.employee_id as eid,advc.employeename as ename,('Collection') as category,advc.transaction_mode as mode,('-') as advamt,advc.advance_collection_amount as collection from employee_advance_collection as advc  where advc.employee_id ='".$customerName."' && advc.advance_date BETWEEN '".$fromdate."' and '".$todate."' and advc.status='' order by date ");





        
		$tadvance=0;
		$tcollection=0;
		
		$count=0;
		while($rw3=mysqli_fetch_array($msql3))
		{ 
		
			if($rw3['category'] == 'Collection'){
				
				$tcollection=$tcollection + $rw3['collection'];
				
				
				}
				
				
				else
				{
					$tadvance=$tadvance + $rw3['advamt'];
					//$sale_id = $rw3['saleid'];
					
					
					}
					
						
			
			
			$pending=($tadvance + $openingbalance) - ($tcollection);
			
			$date=date_create($rw3['date']);

        ?>
        <tbody>
           <tr>
           <td><?php echo ++$count;?></td>
           <td><?php echo date_format($date,"d-m-Y");?></td>
          
		 
           <td><?php echo $rw3['category'];?></td>
           <td><?php echo $rw3['mode'];?></td>
          
           <td align="right"><?php echo number_format(round($rw3['advamt']),2,'.','');?></td>
           <td align="right"><?php echo number_format(round($rw3['collection']),2,'.','');?></td>
           
           </tr> 
                    
           
        </tbody>
        <?php
		
		}
		?>
        
            <tr>
           
           <td colspan="4" align="right"><strong>மொத்த தொகை</strong></td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($tadvance),2,'.','');?></td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($tcollection),2,'.','');?></td>
           </tr>
           
           <tr>
           <td>&nbsp;</td>
           </tr>
        
        <tr>
           
           <td colspan="5" align="right" style="font-weight:bold;">முன் பாக்கி</td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($openingbalance),2,'.','');?></td>
           
           </tr> 
           <tr>
           
           <td colspan="5" align="right" style="font-weight:bold;">மொத்த முன்பணம்</td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($tadvance),2,'.','');?></td>
           
           </tr>
           
          
           
           <tr>
           
           <td colspan="5" align="right" style="font-weight:bold;">பெறப்பட்ட தொகை</td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($tcollection),2,'.','');?></td>
           
          
           
            <tr>
           
           <td colspan="5" align="right" style="font-weight:bold;">மொத்த பாக்கி</td>
           <td style="font-weight:bold;" align="right"><?php echo number_format(round($pending),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>