<?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'];
				
				$customer=$_POST['customer'];
				
$msql2=mysqli_query($conn,"select * from customer_master where id='".$customer."'");
        
		while($rw2=mysqli_fetch_array($msql2))
		{
			
		$cusname=$rw2['cname'];
		$cusname_tamil=$rw2['cname_tamil'];
		$cusarea=$rw2['area'];
		$cusarea_tamil=$rw2['area_tamil'];
			
		}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Tray 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>
 
 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="overall_report_customer.php">
    
    <div class="form-group">
      <label for="">From Date:</label><br>
      <input type="date" class="form-control input-lg " id="fromDate" name="fromDate" autocomplete="off" required>
    </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" required>
    </div>

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

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="customer" required>
        <option value="">Select the Customer </option>
        <?php
        while($row = mysqli_fetch_array($sql_customer)){
        ?>
        <option value="<?php echo $row['customer_id'] ?>" > <?php echo $row['cname']; ?> </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>
    </div>
  </form>
  
</div>


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



<div class="container col-sm-12">
<div id="a">
<h3 align="center">Overall Report(<?php echo $tripfromDate;?>&nbsp;TO&nbsp;<?php echo $triptoDate;?>)</h3>
<h3 align="center">Customer Name:<?php echo $cusname;?>(<?php echo $cusname_tamil;?>)-<?php echo $cusarea;?>(<?php echo $cusarea_tamil;?>)</h3>
<p>&nbsp;</p>

 <h3 align="center">Sales Details</h3>
<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
				
                
                <th>Product</th>
                <th>Rate</th>
				<th>Qty</th>
				
				
                <th>Net Amount</th>
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        
        <?php
				
				
				$sql = "SELECT sh.id,sh.invoice_no,sh.area,sh.area_tamil, sh.sale_date,sh.customer_id, sh.vehicleno as vehicle,sh.ename as ename, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname,cm.cname_tamil,sd.productID,sd.sale_prate prate,sd.sale_eqty qt,pm.productname pname,sd.salenetamt FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join saledetails as sd on sh.id=sd.saleheader_id inner join product_master as pm on pm.id=sd.productID where sh.flag='1' and sh.status=''and sh.customer_id='".$customer."'  and sh.sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";
				
	 
	 
				?>
        
        <tbody>
            <?php
			$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 1".$sql);
			$total_price = 0;
			$total_qty = 0;
			while($row=mysqli_fetch_array($query))
{
	$deliver_date=$row["sale_date"];
$date=date_create($deliver_date);
$sale_date2=date_format($date,"d-m-Y");
				
				?>
			<tr>
            <td><?php echo $row["invoice_no"];?></td>
            <td><?php echo $sale_date2;?></td>
			
            
            <td><?php echo $row["pname"];?></td>
            <td><?php echo $row["prate"];?></td>
			<td align="right"><?php echo number_format((float)$row["qt"], 2, '.', '');?></td>
			
			
            <td align="right"><?php echo number_format(round($row["salenetamt"]), 2, '.', '');?></td>
			</tr>
            <?php
			
			$total_price  += $row["salenetamt"];
			$total_qty  += $row["qt"];
}


?>
       <tr>
           
           <td colspan="4" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$total_qty, 2, '.', '');?></strong></td>
           
			
            <td align="right"><strong><?php echo number_format(round($total_price), 2, '.', '');?></strong></td>
			</tr>             
           
        </tbody>
       
    </table>
    
    
    
    
    <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Product</th>
                <th>UOM</th>
                <th>Total&nbsp;Qty</th>
               
            </tr>
        </thead>
        
        <?php
				
				
				$sql_product = "SELECT shp.id, shp.sale_date,sdp.productID,sum(sdp.sale_eqty) qt,pm.productname pname,pm.qty_type FROM saleheader as shp LEFT JOIN customer_master as cm on cm.id = shp.customer_id inner join saledetails as sdp on shp.id=sdp.saleheader_id inner join product_master as pm on pm.id=sdp.productID where shp.flag='1' and shp.status=''and  shp.customer_id='".$customer."'  and shp.sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' group by  sdp.productID ASC ";
				
	 
	 
				?>
        
        <tbody>
            <?php
			$query_product=mysqli_query($conn, $sql_product) or die("brandgriddata.php: get brand Data step 2".$sql_product);
			
			$total_product_qty = 0;
			while($row_product=mysqli_fetch_array($query_product))
{
	
				
				?>
			<tr>
            
			
            
            <td><?php echo $row_product["pname"];?></td>
            <td><?php echo $row_product["qty_type"];?></td>
           
			<td align="right"><?php echo number_format((float)$row_product["qt"], 2, '.', '');?></td>
			
			
			</tr>
            <?php
			
			
			$total_product_qty  += $row_product["qt"];
}


?>
       <tr>
           
           <td colspan="2" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$total_product_qty, 2, '.', '');?></strong></td>
           
			
            
			</tr>             
           
        </tbody>
       
    </table>
    
    <!--Sales Return-->
    
     <h3 align="center">Sales Return</h3>
    
    <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
				
                
                <th>Product</th>
                <th>Rate</th>
				<th>Qty</th>
				
				
                <th>Net Amount</th>
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        
        <?php
				
				
				$sql_return = "SELECT sh.id,sh.invoice_no,sh.area,sh.area_tamil, sh.sale_date,sh.customer_id, sh.vehicleno as vehicle,sh.ename as ename, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname,cm.cname_tamil,sd.productID,sd.sale_prate prate,sd.sale_eqty qt,pm.productname pname,sd.salenetamt FROM sale_return_header as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join sale_return_details as sd on sh.id=sd.saleheader_id inner join product_master as pm on pm.id=sd.productID where sh.flag='1' and sh.status=''and sh.customer_id='".$customer."'  and sh.sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";
				
	 
	 
				?>
        
        <tbody>
            <?php
			$query_return=mysqli_query($conn, $sql_return) or die("brandgriddata.php: get brand Data step 1".$sql);
			$total_price_return = 0;
			$total_qty_return = 0;
			while($row_return=mysqli_fetch_array($query_return))
{
	$return_date=$row_return["sale_date"];
$date_return=date_create($return_date);
$sale_date2_return=date_format($date_return,"d-m-Y");
				
				?>
			<tr>
            <td><?php echo $row_return["invoice_no"];?></td>
            <td><?php echo $sale_date2_return;?></td>
			
            
            <td><?php echo $row_return["pname"];?></td>
            <td><?php echo $row_return["prate"];?></td>
			<td align="right"><?php echo number_format((float)$row_return["qt"], 2, '.', '');?></td>
			
			
            <td align="right"><?php echo number_format(round($row_return["salenetamt"]), 2, '.', '');?></td>
			</tr>
            <?php
			
			$total_price_return  += $row_return["salenetamt"];
			$total_qty_return  += $row_return["qt"];
}


?>
       <tr>
           
           <td colspan="4" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$total_qty_return, 2, '.', '');?></strong></td>
           
			
            <td align="right"><strong><?php echo number_format(round($total_price_return), 2, '.', '');?></strong></td>
			</tr>             
           
        </tbody>
       
    </table>
    
    
    <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Product</th>
                <th>UOM</th>
                <th>Total&nbsp;Qty</th>
               
            </tr>
        </thead>
        
        <?php
				
				
				$sql_product_return = "SELECT shp.id, shp.sale_date,sdp.productID,sum(sdp.sale_eqty) qt,pm.productname pname,pm.qty_type FROM sale_return_header as shp LEFT JOIN customer_master as cm on cm.id = shp.customer_id inner join sale_return_details as sdp on shp.id=sdp.saleheader_id inner join product_master as pm on pm.id=sdp.productID where shp.flag='1' and shp.status=''and  shp.customer_id='".$customer."'  and shp.sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' group by  sdp.productID ASC ";
				
	 
	 
				?>
        
        <tbody>
            <?php
			$query_product_return=mysqli_query($conn, $sql_product_return) or die("brandgriddata.php: get brand Data step 2".$sql_product);
			
			$total_product_qty_return = 0;
			while($row_product_return=mysqli_fetch_array($query_product_return))
{
	
				
				?>
			<tr>
            
			
            
            <td><?php echo $row_product_return["pname"];?></td>
            <td><?php echo $row_product_return["qty_type"];?></td>
           
			<td align="right"><?php echo number_format((float)$row_product_return["qt"], 2, '.', '');?></td>
			
			
			</tr>
            <?php
			
			
			$total_product_qty_return  += $row_product_return["qt"];
}


?>
       <tr>
           
           <td colspan="2" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$total_product_qty_return, 2, '.', '');?></strong></td>
           
			
            
			</tr>             
           
        </tbody>
       
    </table>
    
    <!-- Collection-->
    
    <h3 align="center">Collection</h3>
    
    <table id="stockCollectionDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
              
               
                <th>Collected Amount</th>
                <th>Discount Amount</th>
                 <th>Cash Amount</th>
                  <th>Bank Amount</th>
                 <th>Payment Mode</th>
                
                 <th>Remark</th>
               
            </tr>
        </thead>
        <tbody>
        
        <?php
		
		$sql_collection = "SELECT cc.handover,cc.transactionno,cc.paymentreference,cc.id,cc.cashno,cc.payment_through,cc.cash_amount,cc.bank_amount, cc.collectiondate, cc.customerID, cc.pending,cc.remark, cc.paymenttype, cc.received_amount,cc.discount, cm.cname FROM customer_payment_header as cc LEFT JOIN customer_master as cm on cm.id = cc.customerID where  cc.status='' and cc.collectiondate BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and cc.customerID ='".$customer."'";
		
		$query_collection=mysqli_query($conn, $sql_collection) or die("php: get purchase Data4");
		
		$tcollection=0;
		$tdiscount=0;
		
		$tcash=0;
		$tbank=0;
		
		while( $row_collection=mysqli_fetch_array($query_collection) ) {
		$date = new DateTime($row_collection["collectiondate"]); 
		?>
            
          <tr>
          <td><?php echo  $row_collection["cashno"];?></td>

    
   
	
    <td><?php echo  $date->format('d-m-Y');?></td> 
   
    
	<td align="right"><?php echo  number_format(round($row_collection["received_amount"]),2,'.','');?></td>
	<td align="right"><?php echo  number_format(round($row_collection["discount"]),2,'.','');?></td>
    
    <td align="right"><?php echo  number_format(round($row_collection["cash_amount"]),2,'.','');?></td>
    <td align="right"><?php echo  number_format(round($row_collection["bank_amount"]),2,'.','');?></td>
	
	
    <td><?php echo  $row_collection["paymenttype"];?></td>
	
	 <td><?php echo  $row_collection["remark"];?></td>
          
          </tr>          
           <?php
		   $tcollection+=$row_collection["received_amount"];
		   $tdiscount+=$row_collection["discount"];
		   
		   $tcash+=$row_collection["cash_amount"];
		$tbank+=$row_collection["bank_amount"];
		   }
		   ?>
           <tr>
           <td colspan="2" align="right"><strong>Total Amount</strong></td>
           <td align="right"><strong><?php echo number_format($tcollection,2,'.','');?></strong> </td>
          <td align="right"><strong><?php echo number_format($tdiscount,2,'.','');?></strong> </td>
           <td align="right"><strong><?php echo number_format($tcash,2,'.','');?></strong> </td>
            <td align="right"><strong><?php echo number_format($tbank,2,'.','');?></strong> </td>
           
           </tr>
        </tbody>
       
    </table>
    
    
    <!--Tray Collection-->
    
    
    
    <!--Pending Tray-->
    
    <h3 align="center">Tray Details</h3>
    <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                 <th>Sale Invoice No</th>
                <th>Date</th>
				
                <th>Customer</th>
                <th>Area</th>
               
                <!--<th>Vehicle.No</th>
                 
                <th>Employee Name</th>-->
                <th>Product</th>
                <th>Collected Tray</th>
				<th>Out Tray</th>
				
				
               
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        
        <?php
				
				
				$sql = "SELECT sh.id,sh.invoice_no,sh.area,sh.area_tamil,sh.sales_inv, sh.sale_date,sh.customer_id,  sh.create_date,sh.id,cm.cname,cm.cname_tamil,sh.tray_name pname,sh.pending_tray,sh.collected_tray_eqty,tray_out_qty FROM tray_details as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id where sh.flag='1' and sh.status='' ";
				
				if($tripfromDate !=''  && $triptoDate !='' && $customer ==''  && $ename =='' && $vno=='' && $product=='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename =='' && $vno=='' && $product=='') {
	  	$sql.=" and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename !='' && $vno=='' && $product=='' ) {
		$sql.=" and sh.ename='".$ename."'  and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";
	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer ==''  && $ename !='' && $vno=='' && $product=='') {
		$sql.=" and sh.ename='".$ename."'   and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

   	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $ename =='' && $vno=='' && $product=='')  {
	 	$sql.="  and sh.customer_id='".$customer."'   ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $ename !='' && $vno=='' && $product=='' )  {
		$sql.="  and sh.ename='".$ename."'   ORDER BY sh.id,sh.sale_date  ASC ";

	}
	
	else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $ename !='' && $vno=='' && $product=='')  {
		$sql.="  and sh.ename='".$ename."'  and sh.customer_id='".$customer."'   ORDER BY sh.id,sh.sale_date  ASC  ";

	}
	
	else if($tripfromDate !=''  && $triptoDate !='' && $customer ==''  && $ename =='' && $vno!='' && $product=='')  {
		$sql.="  and sh.vehicleno='".$vno."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	}
	
	else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename =='' && $vno!='' && $product=='')  {
		$sql.=" and sh.vehicleno='".$vno."'  and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	}
	
	else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename !='' && $vno!='' && $product=='')  {
		$sql.=" and sh.ename='".$ename."' and sh.vehicleno='".$vno."'  and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	}
	
	else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $ename =='' && $vno!='' && $product=='')  {
		$sql.=" and  sh.vehicleno='".$vno."'  ORDER BY sh.id,sh.sale_date  ASC ";

	}
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer ==''  && $ename =='' && $vno=='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	  else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename =='' && $vno=='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' and sh.customer_id='".$customer."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $ename =='' && $vno!='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' and sh.customer_id='".$customer."' and sh.vehicleno='".$vno."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $ename =='' && $vno!='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."' and sh.customer_id='".$customer."' and sh.vehicleno='".$vno."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $ename =='' && $vno=='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."' and sh.customer_id='".$customer."'  ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $ename =='' && $vno!='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."'  and sh.vehicleno='".$vno."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $ename =='' && $vno=='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."'  ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
				?>
        
        <tbody>
            <?php
			$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 1".$sql);
			$out_qty = 0;
			$in_qty = 0;
			while($row=mysqli_fetch_array($query))
{
	$deliver_date=$row["sale_date"];
$date=date_create($deliver_date);
$sale_date2=date_format($date,"d-m-Y");
				
				?>
			<tr>
            <td><?php echo $row["invoice_no"];?></td>
            <td><?php echo $row["sales_inv"];?></td>
            
            <td><?php echo $sale_date2;?></td>
			
            <td><?php echo $row["cname"];?>(<?php echo $row["cname_tamil"];?>)</td>
            <td><?php echo $row["area"];?>(<?php echo $row["area_tamil"];?>)</td>
            
            <!--<td><?php echo $row["vehicle"];?></td>
           
            <td><?php echo $row["ename"];?></td>-->
            <td><?php echo $row["pname"];?></td>
          
			<td align="right"><?php echo number_format((float)$row["collected_tray_eqty"], 2, '.', '');?></td>
			
			
            <td align="right"><?php echo number_format(round($row["tray_out_qty"]), 2, '.', '');?></td>
			</tr>
            <?php
			
			$in_qty  += $row["collected_tray_eqty"];
			$out_qty  += $row["tray_out_qty"];
}


?>
       <tr>
           
           <td colspan="6" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$in_qty, 2, '.', '');?></strong></td>
           
			
            <td align="right"><strong><?php echo number_format(round($out_qty), 2, '.', '');?></strong></td>
			</tr>             
           
        </tbody>
       
    </table>
        
        <!--Summ Report-->
        <table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%" align="right">
        <?php
		
		
        $msql1=mysqli_query($conn,"select sum(received_amount) as collamount,sum(discount) as discountamount from customer_payment_header where collectiondate<'".$tripfromDate."' and customerID='".$customer."' and status=''");
		$msql=mysqli_query($conn,"select sum(tamount) as salesamount from saleheader where sale_date<'".$fromdate."' and customer_id='".$customerName."' and status=''");
		
		
        
		while($rw=mysqli_fetch_array($msql))
		{
			
		$samount=$rw['salesamount'];	
		}
        
		while($rw1=mysqli_fetch_array($msql1))
		{
			
		$collection=$rw1['collamount'];	
		$discountamount=$rw1['discountamount'];	
		}
		
		$sql_sales_return=mysqli_query($conn,"SELECT sum(tamount) as return_balance FROM sale_return_header WHERE sale_date<'".$fromdate."' and  customer_id='$customer' and status=''");
		$row_sales_return=mysqli_fetch_array($sql_sales_return,MYSQLI_ASSOC);
		
		
		
		$sql_master_balance=mysqli_query($conn,"SELECT opening_balance as master_balance FROM customer_master WHERE   id='$customer' ");
		$row_master_balance=mysqli_fetch_array($sql_master_balance,MYSQLI_ASSOC);
		
		
			$openingbalance=($samount+$row_master_balance['master_balance']) - ($collection+$discountamount+$row_sales_return['return_balance']);
			
			
			$sql_sales=mysqli_query($conn,"SELECT sum(tamount) as salenet FROM saleheader WHERE sale_date between '".$tripfromDate."' AND '".$triptoDate."' and  customer_id='$customer' and status=''");
			
		$row_sales=mysqli_fetch_array($sql_sales,MYSQLI_ASSOC);
			
			$snet=$row_sales['salenet'];
			
			$sql_sales_rtn=mysqli_query($conn,"SELECT sum(tamount) as return_salenet FROM sale_return_header WHERE sale_date between '".$tripfromDate."' AND '".$triptoDate."' and  customer_id='$customer' and status=''");
			
		$row_sales_rtn=mysqli_fetch_array($sql_sales_rtn,MYSQLI_ASSOC);
			
			$rtn_net=$row_sales_rtn['return_salenet'];
			
			
			$sql_collection=mysqli_query($conn,"SELECT sum(received_amount) as colnet,sum(discount) as coldis,sum(cash_amount) as cash,sum(bank_amount) as bank FROM customer_payment_header WHERE collectiondate between '".$tripfromDate."' AND '".$triptoDate."' and  customerID='$customer' and status=''");
			
		$row_collection=mysqli_fetch_array($sql_collection,MYSQLI_ASSOC);
			
			$cnet=$row_collection['colnet'];
			$dnet=$row_collection['coldis'];
			
			$cash=$row_collection['cash'];
			$bank=$row_collection['bank'];
			
			$overpend=$snet-($cnet+$dnet);
			$allpendamt=($openingbalance+$overpend)-$rtn_net;
			
			?>
        <tr>
        <td>Opening Balance</td>
        <td><?php echo number_format($openingbalance,2,'.','');?></td>
        </tr>
        <tr>
        <td>Sales Amount</td>
        <td><?php echo number_format($snet,2,'.','');?></td>
       </tr>
        
        <tr>
        <td>Sales Return Amount</td>
        <td><?php echo number_format($rtn_net,2,'.','');?></td>
        </tr>
        
        <tr>
        <td>Collection Amount</td>
        <td><?php echo number_format($cnet,2,'.','');?>&nbsp;(Cash:<?php echo number_format($cash,2,'.','');?>,&nbsp;Bank:<?php echo number_format($bank,2,'.','');?>)</td>
       </tr>
        
        <tr>
        <td>Discount Amount</td>
        <td><?php echo number_format($dnet,2,'.','');?></td>
       </tr>
        
        <tr>
        <td>Given Tray</td>
        <td><?php echo number_format($out_qty,2,'.','');?></td>
       </tr>
        <tr>
        <td>Collected Tray </td>
        <td><?php echo number_format($in_qty,2,'.','');?></td>
        </tr>
        
        <tr>
        <td>Remaining Tray </td>
        <td><?php echo number_format(($out_qty-$in_qty),2,'.','');?></td>
        </tr>
        
        <tr>
        <td>Overall Pending</td>
        <td><?php echo number_format($allpendamt,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>