<?php 

require_once("config/configDB.php"); 
require "userPermission.php";
//require_once("config/constant.php");
error_reporting(0);
$sql_supplier = mysqli_query($conn,"SELECT sh.supplier_id,cm.sname FROM purchaseheader as sh  join supplier_master as cm on cm.id=sh.supplier_id WHERE cm.isdelete='1' GROUP BY sh.supplier_id ");

$row_supplier = '';

$sqlproduct = mysqli_query($conn,"SELECT id, productname FROM supplierproduct_master WHERE flag='1' ");

$sql_product = '';

while($prow = mysqli_fetch_array($sqlproduct)){

    $sql_product =$sql_product."<option value='".$prow['id']."'>".$prow['productname']."</option>";
}

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


?>
<!DOCTYPE html>
<html>
<head>
    <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="supplier_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">
    </div>
    <div class="form-group">
      <label for="pwd">To Date</label>
      <input type="date" class="form-control" id="toDate" name="toDate" autocomplete="off">
    </div>

      <div class="form-group">
      <label for="pwd">Please Select the supplier </label>
      <select class="form-control  supplierStock" name="supplierName">
        <option value=""> Select the supplier </option>
        <?php
        while($row = mysqli_fetch_array($sql_supplier)){
        ?>
        <option value="<?php echo $row['supplier_id'] ?>" > <?php echo $row['sname']; ?> </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%">
        <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>Supplier Name</th>
                
                <th>Category</th>
                <th>Mode</th>
                <th>Amount</th>
               
                 
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        <?php
		$msql=mysqli_query($conn,"select sum(tamount) as purchaseamount from purchaseheader where purchase_date<'".$fromdate."' and supplier_id='".$supplierName."' and status=''");
        
		while($rw=mysqli_fetch_array($msql))
		{
			
		$samount=$rw['purchaseamount'];	
		}
			
		
        $msql1=mysqli_query($conn,"select sum(amount) as collamount from supplier_payment_header where collectiondate<'".$fromdate."' and supplierID='".$supplierName."' and status=''");
        
		while($rw1=mysqli_fetch_array($msql1))
		{
			
		$collection=$rw1['collamount'];	
		}
			
			
			
			
			$openingbalance=$samount - $collection;
			
			 
			
			
			$msql3=mysqli_query($conn,"select purchaseheader.supplier_id as cid,purchaseheader.invoice_no as invno,purchaseheader.purchase_date as date,purchaseheader.tamount as amt,('Purchase') as type,('-') as mode from purchaseheader where purchaseheader.supplier_id ='".$supplierName."' && purchaseheader.purchase_date BETWEEN '".$fromdate."' and '".$todate."' and purchaseheader.status='' 
UNION ALL
select supplier_payment_header.supplierID as cid,supplier_payment_header.cashno as invno,supplier_payment_header.collectiondate as date,supplier_payment_header.received_amount as amt,('Paid') as type,supplier_payment_header.paymentreference as mode from supplier_payment_header where  supplier_payment_header.supplierID='".$supplierName."' && supplier_payment_header.collectiondate BETWEEN '".$fromdate."' and '".$todate."' and  supplier_payment_header.status='' order by date");


        
		$tsales=0;
		$tcollection=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'];
				$sale_id = $rw3['saleid'];
				$sale_inv = mysqli_query($conn,"select purchaseheader.invoice_no as invno from purchaseheader where id = '$sale_id'");
				$sale_id_noo = mysqli_fetch_assoc($sale_inv);
				$sale_id_no  = $sale_id_noo['invno'];
				}else{
					$tsales=$tsales + $rw3['amt'];
					$sale_id = $rw3['saleid'];
					$pro_inv = mysqli_query($conn,"select sd.productId,sd.sale_eqty as qty,pm.productname as proname from          purchasedetails as sd inner join supplierproduct_master as pm on pm.id = sd.productID where sd.purchaseheader_id ='$sale_id'");
				$pro_id_noo = mysqli_fetch_assoc($pro_inv);
				$pro_name  = $pro_id_noo['proname'];
				$pro_qty  = $pro_id_noo['qty'];
					}
			
			$msql4=mysqli_query($conn,"select * from    supplier_master where id='".$supplierName."'  ");
			
				while($rw4=mysqli_fetch_array($msql4))
		
			{
			$pending=($tsales + $openingbalance) - $tcollection;
			
			
        ?>
        <tbody>
           <tr>
           <td><?php echo ++$count;?></td>
           <td><?php echo $rw3['date'];?></td>
           <td><?php echo $sale_id_no;?></td>
		  <!-- <td><?php echo $rw3['address'];?></td>
           <td><?php echo $pro_name;?></td>
           <td><?php echo $pro_qty;?></td>-->
           <td><?php echo $rw3['invno'];?></td>
           <td><?php echo $rw4['sname'];?></td>
           
           <td><?php echo $rw3['type'];?></td>
           <td><?php echo $rw3['mode'];?></td>
           
           <td><?php echo number_format(round($rw3['amt']),2,'.','');?></td>
           
           </tr> 
                    
           
        </tbody>
        <?php
		}
		}
		?>
        
        <tr>
           
           <td colspan="7" align="right">Opening Balance</td>
           <td><?php echo number_format(round($openingbalance),2,'.','');?></td>
           
           </tr> 
           <tr>
           
           <td colspan="7" align="right">Total Purchase</td>
           <td><?php echo number_format(round($tsales),2,'.','');?></td>
           
           </tr> 
           
           <tr>
           
           <td colspan="7" align="right">Total Paid</td>
           <td><?php echo number_format(round($tcollection),2,'.','');?></td>
           
           </tr>
           
            <tr>
           
           <td colspan="7" align="right">Total Pending</td>
           <td><?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>

    <!--<script>


$(document).ready(function() {

    

            $("#fromDate").datepicker({
        minDate: '-1Y-6M',
        maxDate: '+1Y+6M',
        dateFormat: 'yy-mm-dd',
        onSelect: function (dateStr) {
            var min = $(this).datepicker('getDate'); // Get selected date
            $("#toDate").datepicker('option', 'minDate', min || '0'); // Set other min, default to today
        }
    });

    //toDate

    $("#toDate").datepicker({
        minDate: '1',
        maxDate: '+1Y+6M',
        dateFormat: 'yy-mm-dd',
        onSelect: function (dateStr) {
            var max = $(this).datepicker('getDate'); // Get selected date

            $('#datepicker').datepicker('option', 'maxDate', max || '+1Y+6M'); // Set other max, default to +18 months

            
        }
    });

            
});

</script>-->
</body>
</html>