<?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_Report.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 !='') {
$supplierqry=mysqli_query($conn,"
select a.area,a.area_tamil,a.cid From

(select sh.area,sh.area_tamil,sh.customer_id as cid from saleheader as sh where sh.status='' and sh.sale_date between '$tripfromDate' and  '$triptoDate' and  sh.area='$area'  GROUP BY sh.customer_id

union all
 
select cm.area,cm.area_tamil,cc.customerID as cid from customer_master cm inner join customer_payment_header as cc on cm.id=cc.customerID where cc.status='' and cc.collectiondate between '$tripfromDate' and  '$triptoDate' and  cm.area='$area'  GROUP BY cc.customerID)a GROUP BY a.area,a.cid

");
}



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

$area=$serrow['area'];
$area_tamil=$serrow['area_tamil'];

}
      ?>  
        
        
      <h3 align="center">Area Report</h3>  
       
<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>

 
	              <?php

//$sql_cus=mysqli_query($conn,"select shd.sale_date,shd.customer_id,cmh.cname,cmh.cname_tamil from saleheader as shd inner join customer_master as cmh on shd.customer_id=cmh.id where shd.status='' and shd.area='$area' and shd.sale_date between'".$tripfromDate."' AND  '".$triptoDate."'  group by shd.customer_id");

$sql_cus=mysqli_query($conn,"
select a.area,a.area_tamil,a.cid,a.cname,a.cname_tamil,a.cid From

(select sh.area,sh.area_tamil,sh.customer_id as cid,cm.cname,cm.cname_tamil from saleheader as sh inner join customer_master as cm on sh.customer_id=cm.id where sh.status='' and sh.sale_date between '$tripfromDate' and  '$triptoDate' and  sh.area='$area'  GROUP BY sh.customer_id

union all
 
select ccm.area,ccm.area_tamil,cc.customerID as cid,ccm.cname,ccm.cname_tamil from customer_master as ccm inner join customer_payment_header as cc   on ccm.id=cc.customerID where cc.status='' and cc.collectiondate between '$tripfromDate' and  '$triptoDate' and  ccm.area='$area'  GROUP BY cc.customerID)a group by a.cid 

");



$sum_sales=0;
$sum_collection=0;
$sum_discount=0;
$sum_opening=0;
$sum_pending=0;
while($row_cus=mysqli_fetch_array($sql_cus))
{
$cusname=$row_cus['cname'];
$cname_tamil=$row_cus['cname_tamil'];
$cusname_id=$row_cus['cid'];

//echo $cusname_id;

?>

      <h3><?php echo $cusname;?>(<?php echo $cname_tamil;?>)</h3> 
          
      
                <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <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>Invoice.No</th>
           <!-- <th>Delivery Address</th>
            <th>Product</th>
            <th>Qty</th>-->
            <th>Payment.No</th>
                <th>Customer Name</th>
                
                <th>Category</th>
                <th>Mode</th>
                 <th>Discount</th>
                <th>Amount</th>
               
                 
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
            <tbody>
           

      
      
      <?php
	  
	  
	  $msql=mysqli_query($conn,"select sum(tamount) as salesamount from saleheader where sale_date<'".$tripfromDate."' and customer_id='".$cusname_id."' and status=''");
		
		
        
		while($rw=mysqli_fetch_array($msql))
		{
			
		$samount=$rw['salesamount'];	
		}
			
		
        $msql1=mysqli_query($conn,"select sum(received_amount) as collamount,sum(discount) as discountamount from customer_payment_header where collectiondate<'".$tripfromDate."' and customerID='".$cusname_id."' and status=''");
		
        
		while($rw1=mysqli_fetch_array($msql1))
		{
			
		$collection=$rw1['collamount'];	
		$discountamount=$rw1['discountamount'];	
		}
			
			$sql_tray=mysqli_query($conn,"SELECT sum(tamount) as return_amount FROM sale_return_header WHERE sale_date<'".$tripfromDate."' and  customer_id='$cusname_id' and status=''");
			
		$row_tray=mysqli_fetch_array($sql_tray,MYSQLI_ASSOC);
		
		$sql_master_balance=mysqli_query($conn,"SELECT opening_balance as master_balance FROM customer_master WHERE   id='$cusname_id' ");
		$row_master_balance=mysqli_fetch_array($sql_master_balance,MYSQLI_ASSOC);
			
						
			$openingbalance=($samount+$row_master_balance['master_balance']) - ($collection+$discountamount+$row_tray['return_amount']);
	  
	  $msql3=mysqli_query($conn,"select saleheader.customer_id as cid,saleheader.invoice_no as invno,saleheader.sale_date as date,saleheader.tamount as amt,('Sales') as type,('-') as mode,('-') as discount from saleheader  where saleheader.customer_id ='".$cusname_id."' && saleheader.sale_date BETWEEN '".$tripfromDate."' and '".$triptoDate."' and saleheader.status=''
			 
UNION ALL


select srt.customer_id as cid,srt.invoice_no as invno,srt.sale_date as date,srt.tamount as amt,('Return') as type,('-') as mode,('-') as discount from sale_return_header as srt  where srt.customer_id ='".$cusname_id."' && srt.sale_date BETWEEN '".$tripfromDate."' and '".$triptoDate."' and srt.status='' 

UNION ALL

select customer_payment_header.customerID as cid,customer_payment_header.cashno as invno,customer_payment_header.collectiondate as date,customer_payment_header.received_amount as amt,('Paid') as type,customer_payment_header.paymenttype as mode,discount as discount from customer_payment_header where customer_payment_header.customerID='".$cusname_id."' && customer_payment_header.collectiondate BETWEEN '".$tripfromDate."' and '".$triptoDate."' and  customer_payment_header.status='' order by date ");


	  
	  
	$tsales=0;
		$tcollection=0;
		$treturn=0;
		$tdicount=0;
		$count=0;
		while($rw3=mysqli_fetch_array($msql3))
		{ 
		$sale_id_no =  $rw3['invno'];
			if($rw3['type'] == 'Paid'){
				$pro_name = '-';
				$pro_qty  = '-';
				$tcollection=$tcollection + $rw3['amt'];
				$tdicount=$tdicount + $rw3['discount'];
				//$sale_id = $rw3['saleid'];
				//$sale_inv = mysqli_query($conn,"select saleheader.invoice as invno from saleheader where id = '$sale_id'");
				//$sale_id_noo = mysqli_fetch_assoc($sale_inv);
				//sale_id_no  = $sale_id_noo['invno'];
				}
				elseif($rw3['type'] == 'Return')
				{
				$treturn=$treturn + $rw3['amt'];
				
				}
				
				else
				{
					$tsales=$tsales + $rw3['amt'];
					//$sale_id = $rw3['saleid'];
					
					
					}
					
						$sql_tray_return=mysqli_query($conn,"SELECT sum(tamount) as tray_return FROM tray_return_header WHERE sale_date between  '".$tripfromDate."' and '".$triptoDate."' and  customer_id='$cusname_id' and status=''");
						
		$row_tray_return=mysqli_fetch_array($sql_tray_return,MYSQLI_ASSOC);
		
		$tray_rtn=$row_tray_return['tray_return'];
			
			$msql4=mysqli_query($conn,"select * from    customer_master where id='".$cusname_id."'  ");
			
				while($rw4=mysqli_fetch_array($msql4))
		
			{
			$pending=($tsales + $openingbalance) - ($tdicount+$tcollection+$treturn);
			
			
        ?>
        <tbody>
           <tr>
           <td><?php echo ++$count;?></td>
           <td><?php echo $rw3['date'];?></td>
           <td><?php echo $sale_id_no;?></td>
		 
           <td><?php echo $rw3['invno'];?></td>
           <td><?php echo $rw4['cname'];?></td>
           
           <td><?php echo $rw3['type'];?></td>
           <td><?php echo $rw3['mode'];?></td>
           <td><?php echo number_format(round($rw3['discount']),2,'.','');?></td>
           <td><?php echo number_format(round($rw3['amt']),2,'.','');?></td>
           
           </tr> 
                    
           
        </tbody>
        <?php
		}
		}
		?>
        
        <tr>
           
           <td colspan="8" align="right">Opening Balance</td>
           <td><?php echo number_format(round($openingbalance),2,'.','');?></td>
           
           </tr> 
           <tr>
           
           <td colspan="8" align="right">Total Sales</td>
           <td><?php echo number_format(round($tsales),2,'.','');?></td>
           
           </tr>
           
           <tr>
           
           <td colspan="8" align="right">Return Amount</td>
           <td><?php echo number_format(round($treturn),2,'.','');?></td>
           
           </tr> 
           
           <tr>
           
           <td colspan="8" align="right">Colection</td>
           <td><?php echo number_format(round($tcollection),2,'.','');?></td>
           
           </tr>
           
           <td colspan="8" align="right">Discount</td>
           <td><?php echo number_format(round($tdicount),2,'.','');?></td>
           
         </tr>
    
    
           
            <tr>
           
           <td colspan="8" align="right">Total Pending</td>
           <td><?php echo number_format(round($pending),2,'.','');?></td>
           
           </tr> 
       
    </table>
    
 <?php
 
 $sum_sales+=$tsales;
$sum_collection+=$tcollection;
$sum_discount+=$tdicount;
$sum_opening+=$openingbalance;
$sum_pending+=$pending;
 
	}
	
?>


<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                
               <th>Overall Opening Balance</th>
		
                <th>Overall Sales</th>
                <th>Overall Collection</th>
                <th>Overall Discount</th>
				<th>Overall Pending</th>
                <th> Pending</th>
				
            </tr>
            </thead>
            <tbody>
            <tr>
            <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_opening,2,'.','');?></td>
            <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_sales,2,'.','');?></td>
            <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_collection,2,'.','');?></td>
            <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_discount,2,'.','');?></td>
            <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_pending,2,'.','');?></td>
             <td style="text-align:right;font-weight:bold;"><?php echo number_format($sum_sales-($sum_collection+$sum_discount),2,'.','');?></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>