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

$sql_customer = mysqli_query($conn,"SELECT sh.customer_id,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 order by cm.cname asc");

$row_customer = '';




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


?>
<!DOCTYPE html>
<html>
<head>
    <title>Area 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>

<script>
 
 function printDiv() {
    var divToPrint = document.getElementById('a');
    var htmlToPrint = '' +
        '<style type="text/css">' +
        'table th, table td {' +
        'border:1px solid #000;' +
        'padding;0.5em;' +
        '}' +
        '</style>';
    htmlToPrint += divToPrint.outerHTML;
    newWin = window.open("");
    newWin.document.write(htmlToPrint);
    newWin.print();
    newWin.close();
}
 
 
 </script>
</head>
<body>
    <?php require 'menu.php' ?>


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


    
    
    <div class="form-group">
    <label for="">Area </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="area" required>
        <option value="">Select the Area </option>
       <?php
	   $sqlproduct = mysqli_query($conn,"SELECT area, area_tamil FROM saleheader WHERE status='' group by area order by area ASC ");



while($prow = mysqli_fetch_array($sqlproduct)){
	   
	   ?>
        <option value="<?php echo $prow['area'];?>" > <?php echo $prow['area'];?>/ <?php echo $prow['area_tamil'];?> </option>
       <?php
	   }
	   ?>
        </select>
    </div>
    
    
    
     <div class="form-group"><br>

    <button type="submit" class="btn btn-primary btn-md datewise-submit-sale">Submit</button>
<button type="button" class="btn-success btn-lg btn-md datewise-submit-sale" onClick="printDiv();">Print</button>
    <!--<button type="button" class="btn btn-info btn-md datewise-submit-sale" onClick="tableToExcel('a', 'W3C Example Table')">Excel</button>-->
    </div>
  </form>
  
</div>


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



<div class="container col-sm-12">
<div id="a">

        <?php
        
        if($tripfromDate !='' && $triptoDate !='' && $area !='') {
$qry_area=mysqli_query($conn,"select areaname,area_tamil from area_master where areaname='$area' ");
//echo "select areaname,area_tamil from area_master where area='$area' ";
}
while($area_row=mysqli_fetch_array($qry_area))
{
$area=$area_row['areaname'];
$area_tamil=$area_row['area_tamil'];
}

?>

<h3>Date:<?php 

 $date=date_create($tripfromDate);
 $date2=date_create($triptoDate); 

echo "(".date_format($date,"d-m-Y")." TO ".date_format($date2,"d-m-Y").")";?>

</h3>
<h3><?php echo $area;?>&nbsp;-&nbsp;<?php echo $area_tamil;?></h3>

<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
            
            <th style="text-align:center">வ.எண்</th>
            <th style="text-align:center">பெயர்</th>
            <th style="text-align:center">முன் பாக்கி</th>
          
            <th style="text-align:center">விற்பனை</th>
                <th style="text-align:center">Sales Returun</th>
                
                <th style="text-align:center">பெறப்பட்ட வரவு</th>
                <th style="text-align:center">தள்ளுபடி</th>
                
                <th style="text-align:center">மொத்த பாக்கி</th>
               
            </tr>
        </thead>
		
<?Php

 if($tripfromDate !='' && $triptoDate !='' && $area !='') {
$supplierqry=mysqli_query($conn,"select area,area_tamil,id,cname,opening_balance,cname_tamil From customer_master where area='$area' order by cname ");
}
$sno=0;
$total_opening=0;
$total_sales=0;
$total_sales_return=0;
$total_collection=0;
$total_discount=0;

while($serrow=mysqli_fetch_array($supplierqry))
{

$master_opening=$serrow['opening_balance'];
$customerName=$serrow['id'];

$sales_opening=mysqli_query($conn,"select sum(tamount) as sales_opening from saleheader where sale_date<'".$tripfromDate."' and customer_id='".$customerName."' and status=''");
$row_sales_opening=mysqli_fetch_array($sales_opening,MYSQLI_ASSOC);

$sales_opening_amt=$row_sales_opening['sales_opening'];	


$sales_return_opening=mysqli_query($conn,"select sum(tamount) as sales_return_opening from sale_return_header where sale_date<'".$tripfromDate."' and customer_id='".$customerName."' and status=''");
$row_sales_return_opening=mysqli_fetch_array($sales_return_opening,MYSQLI_ASSOC);
$sales_return_opening_amt=$row_sales_return_opening['sales_return_opening'];	


$collection_opening=mysqli_query($conn,"select sum(received_amount) as opening_collamount,sum(discount) as opening_discountamount from customer_payment_header where collectiondate<'".$tripfromDate."' and customerID='".$customerName."' and status=''");
$row_collection_opening=mysqli_fetch_array($collection_opening,MYSQLI_ASSOC);

$collection_opning=$row_collection_opening['opening_collamount'];
$opening_discount=$row_collection_opening['opening_discountamount'];

$openingbalance=($sales_opening_amt+$master_opening) - ($collection_opning+$opening_discount+$sales_return_opening_amt);



$sales=mysqli_query($conn,"select sum(tamount) as sales_amount from saleheader where sale_date between '".$tripfromDate."' and '".$triptoDate."' and customer_id='".$customerName."' and status=''");
$row_sales=mysqli_fetch_array($sales,MYSQLI_ASSOC);

$sales_amount=$row_sales['sales_amount'];

$sales_return=mysqli_query($conn,"select sum(tamount) as sales_return from sale_return_header where sale_date between '".$tripfromDate."' and '".$triptoDate."' and customer_id='".$customerName."' and status=''");
$row_sales_return=mysqli_fetch_array($sales_return,MYSQLI_ASSOC);
$sales_return_amount=$row_sales_return['sales_return'];	


$collection=mysqli_query($conn,"select sum(received_amount) as collamount,sum(discount) as discountamount from customer_payment_header where collectiondate between '".$tripfromDate."' and '".$triptoDate."' and customerID='".$customerName."' and status=''");
$row_collection=mysqli_fetch_array($collection,MYSQLI_ASSOC);

$collection_amount=$row_collection['collamount'];
$discount_amount=$row_collection['discountamount'];

	$total_pending=($openingbalance+$sales_amount)-($sales_return_amount+$collection_amount+$discount_amount);

?>

 
        <tbody>
        <tr>
        <td><?php echo ++$sno;?></td>
        <td><?php echo $serrow['cname'];?></td>
        <td align="right"><?php echo number_format($openingbalance,2,'.','');?></td>
        <td align="right"><?php echo number_format($sales_amount,2,'.','');?></td>
        <td align="right"><?php echo number_format($sales_return_amount,2,'.','');?></td>
        <td align="right"><?php echo number_format($collection_amount,2,'.','');?></td>
        <td align="right"><?php echo number_format($discount_amount,2,'.','');?></td>
         <td align="right"><?php echo number_format($total_pending,2,'.','');?></td>
        </tr>
        
        </tbody>
        <?php
		
		$total_opening+=$openingbalance;
$total_sales+=$sales_amount;
$total_sales_return+=$sales_return_amount;
$total_collection+=$collection_amount;
$total_discount+=$discount_amount;

}
      ?> 
      <tr>
      <td colspan="2" align="right"><strong>Total</strong></td>
      <td align="right"><strong><?php echo number_format($total_opening,2,'.','');?></strong></td>
      <td align="right"><strong><?php echo number_format($total_sales,2,'.','');?></strong></td>
      <td align="right"><strong><?php echo number_format($total_sales_return,2,'.','');?></strong></td>
      <td align="right"><strong><?php echo number_format($total_collection,2,'.','');?></strong></td>
      <td align="right"><strong><?php echo number_format($total_discount,2,'.','');?></strong></td>
      <td align="right"><strong><?php echo number_format((($total_opening+$total_sales)-($total_sales_return+$total_collection+$total_discount)),2,'.','');?></strong></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>