<?php 

require_once("config/configDB.php");



?>
<!DOCTYPE html>
<html>
<head>
    <title>Pending Report</title>
    <?php require 'header.php' ?>
    <link href = "https://code.jquery.com/ui/1.10.4/themes/ui-lightness/jquery-ui.css"
         rel = "stylesheet">
         
         <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 col-sm-12">
<button type="button" class="btn btn-info btn-md datewise-submit-sale" onClick="tableToExcel('a', 'W3C Example Table')">Excel</button>
<div id="a">


<table id="stockCollectionDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">

<caption><center><h3>Sales Pending Amount </h3></center></caption>
        <thead>
            <tr>
                <th>S.no</th>
              
                <th>Customer</th>
                <th>Contact Number</th>
                <th>Opening Balance</th>
                <th>Sales Amount</th>
               
                <th>Collected Amount</th>
                 <th>Pending Amount</th>
                
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        <tbody>
        
        <?php
		
		$sqlj=mysqli_query($conn,"SELECT sh.customer_id,sum(sh.tamount) as tamount,cm.mobile,cm.cname FROM saleheader as sh  join customer_master as cm on cm.id=sh.customer_id  WHERE cm.isdelete='1' and sh.status='' GROUP BY sh.customer_id ");
		
		$count=0;
		$saleamount=0;
		$collection=0;
		$remaining=0;
		$opening=0;
		
		while($rowj=mysqli_fetch_array($sqlj))
		{
			$customer_id=$rowj['customer_id'];
			
			//echo "SELECT sum(amount) as camount FROM `collection_customer` WHERE customerID='$customer_id' and status=''";
			$sqlj1=mysqli_query($conn,"SELECT sum(amount) as camount FROM `collection_customer` WHERE customerID='$customer_id' and status=''");
			while($rowj1=mysqli_fetch_array($sqlj1))
		{
			
			
		$samount=number_format(round($rowj['tamount']),2,'.','');
			$camount=number_format(round($rowj1['camount']),2,'.','');
			
			
			
			$sql_master_balance=mysqli_query($conn,"SELECT opening_balance as master_balance FROM customer_master WHERE   id='$customer_id' ");
		$row_master_balance=mysqli_fetch_array($sql_master_balance,MYSQLI_ASSOC);
		
		$opening_balance=$row_master_balance['master_balance'];
			$pending=($opening_balance+$rowj['tamount']) - ($rowj1['camount']);
		?>
        
            <tr <?php //echo $stl;?>>
            <td><?php echo ++$count;?></td>
            <td><?php echo $rowj['cname'];?></td>
             <td><?php echo $rowj['mobile'];?></td>
              <td align="right"><?php echo number_format(round($opening_balance),2,'.','');?></td>
            <td align="right"><?php echo number_format(round($rowj['tamount']),2,'.','');?></td>
            <td align="right"><?php echo number_format(round($rowj1['camount']),2,'.','');?></td>
             <td align="right"><?php echo number_format(round($pending),2,'.','');?></td>
            
             </tr>
             
             <?php
			 $opening+=$opening_balance;
			 $saleamount +=$rowj['tamount'];
		$collection +=$rowj1['camount'];
		$remaining =($opening+$saleamount) - $collection ;
		
		}
		}
		?>
           <tr>
            
            
            <td colspan="3" align="right"><strong>Total Amount</strong></td>
            <td align="right"><strong><?php echo number_format(round($opening),2,'.','');?></strong></td>
            <td align="right"><strong><?php echo number_format(round($saleamount),2,'.','');?></strong></td>
            <td align="right"><strong><?php echo number_format(round($collection),2,'.','');?></strong></td>
             <td align="right"><strong><?php echo number_format(round($remaining),2,'.','');?></strong></td>
            
             </tr>
        </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>