<?php 

require_once("config/configDB.php"); 
require "userPermission.php";
require_once("config/constant.php");
error_reporting(0);

$sql_expense = mysqli_query($conn,"SELECT id, expense FROM expense_master where flag=1 ");
$row_expense = '';

$sql_employee = mysqli_query($conn,"SELECT id,ename FROM employee_master WHERE isdelete='1' order by ename ASC ");
$row_employee = '';






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

?>

<!DOCTYPE html>
<html>
<head>
    <?php 
      require 'meta.php'; 
      require 'header.php'; 
    ?>
<style>
	
	@font-face
	{
	font-family:Bamini;
	src:url(fonts/Bamini.TTF);
	
	}
	.tamil
	{
	font-family:Bamini;
	
	}
	
	
	</style>
    <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-fluid">
<form class="form-inline" method="post" action="product_stockreport_date.php">
    <center>
    <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"><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>
    </center>
  </form>

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

<center><h3 > Product Stock Report</h3></center>

<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
             <th>S.No</th>
              <!--<th>Product&nbsp;ID</th>-->
                <th>Product&nbsp;Name</th>
                 <th>Product&nbsp;Name</th>
                  <th>Qty&nbsp;Type</th>
                  <th>Opening&nbsp;Stock</th>
                  <th>Pur&nbsp;Qty</th>
                  <th>Sales&nbsp;Qty</th>
                  <th>Sales&nbsp;Return&nbsp;Qty</th>
                  
                <th>Available&nbsp;Qty</th>
                <th>Recent&nbsp;Rate</th>
                <th>Stock&nbsp;Value</th>
				
				
               
            </tr>
        </thead>
        
        <?php
				
				
				$sql = "SELECT id, productname,product_tamil,qty_type from product_master where isdelete=1 order by productname ASC ";
				
		
	
				?>
        
        <tbody>
            <?php
			$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 1".$sql);
			$sno=0;
			$qty=0;
			$amt=0;
			while($row=mysqli_fetch_array($query))
{
	$pid=$row["id"];
	
	$query_purchase_opening=mysqli_query($conn,"select sum(pd.purchase_eqty) as opening_pqty from purchasedetails pd inner join purchaseheader as ph on ph.id=pd.purchaseheader_id where pd.productID='".$pid."' and pd.status='' and ph.status='' and ph.purchase_date<'".$fromdate."'");
	
	//echo "select sum(pd.purchase_eqty) as opening_pqty from purchasedetails pd inner join purchaseheader as ph on ph.id=pd.purchaseheader_id where pd.productID='".$pid."' and pd.status='' and ph.status='' and ph.purchase_date<'".$fromdate."'";
	
	$row_purchase_opening=mysqli_fetch_array($query_purchase_opening,MYSQLI_ASSOC);
	
	$purchase_oprning=$row_purchase_opening['opening_pqty'];
	
	$query_sales_opening=mysqli_query($conn,"select sum(sd.sale_eqty) as opening_sqty from saledetails sd inner join saleheader as sh on sh.id=sd.saleheader_id where sd.productID='".$pid."' and sd.status='' and sh.status='' and sh.sale_date<'".$fromdate."'");
	$row_sales_opening=mysqli_fetch_array($query_sales_opening,MYSQLI_ASSOC);
	
	$sales_oprning=$row_sales_opening['opening_sqty'];
	
	$query_salesreturn_opening=mysqli_query($conn,"select sum(srd.sale_eqty) as srqty from sale_return_details srd inner join sale_return_header srh on srh.id=srd.saleheader_id where srd.productID='".$pid."' and srd.status='' and srh.status='' and srh.sale_date<'".$fromdate."'");
	$row_salesreturn_opening=mysqli_fetch_array($query_salesreturn_opening,MYSQLI_ASSOC);
	
	$salesreturn_oprning=$row_salesreturn_opening['srqty'];
	
	$sales_opening_stock=$sales_oprning-$salesreturn_oprning;
	
	$opening_qty=$purchase_oprning-$sales_opening_stock;
	


$query_purchase=mysqli_query($conn,"select sum(pd.purchase_eqty) as pqty from purchasedetails pd inner join purchaseheader as ph on ph.id=pd.purchaseheader_id where pd.productID='".$pid."' and pd.status='' and ph.status='' and ph.purchase_date between '".$fromdate."' and '".$todate."'");



while($row_pqty=mysqli_fetch_array($query_purchase))
{
$pqty=$row_pqty['pqty'];

}

$query_sales=mysqli_query($conn,"select sum(sd.sale_eqty) as sqty from saledetails sd inner join saleheader as sh on sh.id=sd.saleheader_id where sd.productID='".$pid."' and sd.status='' and sh.status='' and sh.sale_date between '".$fromdate."' and '".$todate."' ");
while($row_sqty=mysqli_fetch_array($query_sales))
{
$sqty=$row_sqty['sqty'];

}

$query_sales_return=mysqli_query($conn,"select sum(srd.sale_eqty) as rqty from sale_return_details srd inner join sale_return_header srh on srh.id=srd.saleheader_id where srd.productID='".$pid."' and srd.status='' and srh.status='' and srh.sale_date between '".$fromdate."' and '".$todate."'");
while($row_sqty_return=mysqli_fetch_array($query_sales_return))
{
$rqty=$row_sqty_return['rqty'];

}

$available_qty=($opening_qty+$pqty)-($sqty-$rqty);

$aqty=number_format($available_qty,2,'.','');

//echo "select Max(id) as maxid from purchasedetails  where productID='".$pid."' and status=''";

$sql_max=mysqli_query($conn,"select Max(id) as maxid from purchasedetails  where productID='".$pid."' and status=''");
while($row_max=mysqli_fetch_array($sql_max))
{
$maxid=$row_max['maxid'];

}
//echo "select Max(purchase_prate) as pprice from purchasedetails where where productID='".$maxid."' and status=''";
$sql_max_rate=mysqli_query($conn,"select Max(purchase_prate) as pprice from purchasedetails  where id='".$maxid."' and status=''");
while($row_max_price=mysqli_fetch_array($sql_max_rate))
{
$max_price=$row_max_price['pprice'];

}

if($opening_qty==0 && $pqty==0 && $sqty==0 && $rqty==0)
			{
				$stl= "style='display:none'";
			}
			else
			{
			$stl=" ";	
			}
				
				?>
			<tr <?php echo $stl;?>>
            <td><?php echo ++$sno;?></td>
           
			<!--<td><?php echo $row["id"];?></td>-->
            <td><?php echo $row["productname"];?></td>
            <td><?php echo $row["product_tamil"];?></td>
            <td><?php echo $row["qty_type"];?></td>
          
           <td align="right"><?php echo number_format($opening_qty,2,'.','');?></td>
           <td align="right"><?php echo number_format($pqty,2,'.','');?></td>
          <td align="right"><?php echo number_format($sqty,2,'.','');?></td>
          <td align="right"><?php echo number_format($rqty,2,'.','');?></td>
          
			
             <td align="right"><?php echo number_format($aqty, 2, '.', '');?></td>
              <td align="right"><?php echo number_format(round($max_price), 2, '.', '');?></td>
             <td align="right"><?php echo number_format(round($aqty*$max_price), 2, '.', '');?></td>
              
			
			
           
			</tr>
            <?php
			
		$qty+=$aqty;
			$amt+=$aqty*$max_price;	
			
}


?>
            <tr style="font-weight:bold;">
        <td colspan="8" align="right">Total Qty</td>
       
       <td align="right"><?php echo number_format($qty,2,'.','');?></td>
       <td >Total Value</td>
       <td align="right"><?php echo number_format($amt,2,'.','');?></td>
       
        </tr>   
                    
           
        </tbody>
       
    </table>
    </div>
</div>

</div>


    <?php require 'footer.php' ?>
   
   
</body>
</html>