<?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'];
				
	$date = new DateTime($tripfromDate);
$predate= $date->modify("-1 days")->format('Y-m-d');	
				
	$date1=date_create($tripfromDate);			
				


?>
<!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="md_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"><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">

<table class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
        <caption><h3>Current Stock(<?php echo  date_format($date1,"d-m-Y");?>)</h3></caption>
        <th>S.No</th>
        <th>Description</th>
        <?php
		$sqla_product_head = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate'  group by pm.productname order by pm.productname ASC ");
		
		while($product_row_head = mysqli_fetch_array($sqla_product_head)){
		?>
        <th><?php echo $product_row_head['productname'];?></th>
        
        
        <?php
		}
		?>
        
        </thead>
        
        <tbody>
        
        
        <tr>
         <td>1</td>
        <td>Opening&nbsp;Qty</td>
         <?php
		 
		 $sqla_product_sales_hdr1 = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate'   group by pm.productname order by pm.productname ASC ");
		
		while($product_row2_sales_purchase = mysqli_fetch_array($sqla_product_sales_hdr1)){
		
		$pid_sales_pur=$product_row2_sales_purchase['productID'];
		 
		$sqla_product_purchase = mysqli_query($conn,"SELECT sum(pd.purchase_eqty) as purchase_qty FROM purchasedetails pd inner join product_master pm on pm.id=pd.productID inner join purchaseheader as ph on ph.id=pd.purchaseheader_id WHERE pd.status='' and ph.status='' and ph.purchase_date<='$tripfromDate'  and pd.productID='$pid_sales_pur' ");
		
		
		while($product_row_purchase = mysqli_fetch_array($sqla_product_purchase)){
		$pur_qty=$product_row_purchase['purchase_qty'];
		
		$sqla_product_return =mysqli_query($conn,"SELECT sum(rd.sale_eqty) as rtnqty FROM sale_return_details rd inner join product_master pm on pm.id=rd.productID inner join sale_return_header as rh on rh.id=rd.saleheader_id WHERE rd.status='' and rh.status='' and rh.sale_date<'$tripfromDate'  and rd.productID='$pid_sales_pur' ");
		
		$row_opening_return=mysqli_fetch_array($sqla_product_return,MYSQLI_ASSOC);
		$rtn_qty=$row_opening_return['rtnqty'];
		
		$sqla_product_Sales_opening = mysqli_query($conn,"SELECT sum(sd.sale_eqty) as qty FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date<'$tripfromDate'  and sd.productID='$pid_sales_pur' ");
		
		$row_opening_sales=mysqli_fetch_array($sqla_product_Sales_opening,MYSQLI_ASSOC);
		
		$opening_sal_qty=$row_opening_sales['qty'];
		
		$opening_stock=($pur_qty+$rtn_qty)-($opening_sal_qty);
		
		//$opening_stock=$rtn_qty;
		
		?>
        <td align="right"><?php echo number_format($opening_stock,2,'.','');?></td>
        
        
        <?php
		
		}
		}
		?>
        </tr>
        
        
        <tr>
         <td>2</td>
        <td>Sales&nbsp;Qty</td>
         <?php
		 
		 $sqla_product_sales_hdr = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate'   group by pm.productname order by pm.productname ASC ");
		
		while($product_row2_sales = mysqli_fetch_array($sqla_product_sales_hdr)){
		
		$pid_sales=$product_row2_sales['productID'];
		 
		$sqla_product_Sales = mysqli_query($conn,"SELECT sum(sd.sale_eqty) as qty FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate'  and sd.productID='$pid_sales' ");
		
		
		while($product_row_sales = mysqli_fetch_array($sqla_product_Sales)){
		$sqty=$product_row_sales['qty'];
		
		?>
        <td align="right"><?php echo number_format($sqty,2,'.','');?></td>
        
        
        <?php
		
		}
		}
		?>
        </tr>
        
        
        
        <tr>
         <td>3</td>
        <td>Stock&nbsp;Qty</td>
         <?php
		 
		 $sqla_product_sales_hdr1_stk = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate'   group by pm.productname order by pm.productname ASC ");
		
		while($product_row2_sales_purchase_stk = mysqli_fetch_array($sqla_product_sales_hdr1_stk)){
		
		$pid_sales_pur_stk=$product_row2_sales_purchase_stk['productID'];
		 
		$sqla_product_purchase_stk = mysqli_query($conn,"SELECT sum(pd.purchase_eqty) as purchase_qty FROM purchasedetails pd inner join product_master pm on pm.id=pd.productID inner join purchaseheader as ph on ph.id=pd.purchaseheader_id WHERE pd.status='' and ph.status='' and ph.purchase_date<='$tripfromDate'  and pd.productID='$pid_sales_pur_stk' ");
		
		
		while($product_row_purchase_stk = mysqli_fetch_array($sqla_product_purchase_stk)){
		$pur_qty_stk=$product_row_purchase_stk['purchase_qty'];
		
		$sqla_product_return_stk =mysqli_query($conn,"SELECT sum(rd.sale_eqty) as rtnqty FROM sale_return_details rd inner join product_master pm on pm.id=rd.productID inner join sale_return_header as rh on rh.id=rd.saleheader_id WHERE rd.status='' and rh.status='' and rh.sale_date<='$tripfromDate'  and rd.productID='$pid_sales_pur_stk' ");
		
		$row_opening_return_stk=mysqli_fetch_array($sqla_product_return_stk,MYSQLI_ASSOC);
		$rtn_qty_stk=$row_opening_return_stk['rtnqty'];
		
		$sqla_product_Sales_opening_stk = mysqli_query($conn,"SELECT sum(sd.sale_eqty) as qty FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date<='$tripfromDate'  and sd.productID='$pid_sales_pur_stk' ");
		
		$row_opening_sales_stk=mysqli_fetch_array($sqla_product_Sales_opening_stk,MYSQLI_ASSOC);
		
		$opening_sal_qty_stk=$row_opening_sales_stk['qty'];
		
		$opening_stock_stk=($pur_qty_stk+$rtn_qty_stk)-($opening_sal_qty_stk);
		
		//$opening_stock=$rtn_qty;
		
		
		
		?>
        <td align="right"><?php echo number_format($opening_stock_stk,2,'.','');?></td>
        
        
        <?php
		
		}
		}
		?>
        </tr>
        
        
        
        </tbody>
        
        </table>
        
        <h3>Sales Details</h3>

<?php
	   $sqlarea = mysqli_query($conn,"SELECT area, area_tamil FROM saleheader WHERE status='' and sale_date='$tripfromDate' group by area order by area ASC ");



while($prow = mysqli_fetch_array($sqlarea)){
	   $area=$prow['area'];
	   ?>
        <h3><?php echo $prow['area'];?></h3>
        
        <table class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
        <th>S.No</th>
        <th>Cus.Name</th>
        <?php
		$sqla_product = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate' and sh.area='$area' group by pm.productname order by pm.productname ASC ");
		
		while($product_row = mysqli_fetch_array($sqla_product)){
		?>
        <th><?php echo $product_row['productname'];?></th>
        
        
        <?php
		}
		?>
        
        </thead>
        <tbody>
        <?php
		 $sql_customer = mysqli_query($conn,"SELECT customer_id,cname FROM saleheader WHERE status='' and sale_date='$tripfromDate' and area='$area' and itemcount!=0 order by cname ASC ");

		$sno=0;
		
		while($row_customer = mysqli_fetch_array($sql_customer)){
		$cid=$row_customer['customer_id'];
		?>
        <tr>
         <td><?php echo ++$sno;?></td>
        <td><?php echo $row_customer['cname'];?></td>
         <?php
		 
		 $sqla_product2 = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate' and sh.area='$area' group by pm.productname order by pm.productname ASC ");
		
		while($product_row2 = mysqli_fetch_array($sqla_product2)){
		
		$pid=$product_row2['productID'];
		 
		$sqla_product_sum = mysqli_query($conn,"SELECT sum(sd.sale_eqty) as qty FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate' and sh.customer_id='$cid' and sd.productID='$pid' ");
		
		
		while($product_row_sum = mysqli_fetch_array($sqla_product_sum)){
		$pqty=$product_row_sum['qty'];
		
		?>
        <td align="right"><?php echo number_format($pqty,2,'.','');?></td>
        
        
        <?php
		
		}
		}
		?>
        </tr>
        
        
        
        
        <?php
		}
		?>
        
        <tr>
        <td>&nbsp;</td>
        <td align="right"><strong>Total</strong></td>
        
         <?php
		 
		 $sqla_product3 = mysqli_query($conn,"SELECT sd.productID, pm.productname FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate' and sh.area='$area' group by pm.productname order by pm.productname ASC ");
		$sum_qty=0;
		while($product_row_sum = mysqli_fetch_array($sqla_product3)){
		
		$pid_sum=$product_row_sum['productID'];
		$pn=$product_row_sum['productname'];
		 
		$sqla_product_net = mysqli_query($conn,"SELECT sum(sd.sale_eqty) as qty FROM saledetails sd inner join product_master pm on pm.id=sd.productID inner join saleheader as sh on sh.id=sd.saleheader_id WHERE sd.status='' and sh.status='' and sh.sale_date='$tripfromDate' and  sd.productID='$pid_sum' and sh.area='$area' ");
		
		
		while($product_row_net = mysqli_fetch_array($sqla_product_net)){
		$nqty=$product_row_net['qty'];
		
		?>
		 <td align="right"><strong><?php echo number_format($nqty,2,'.','');?></strong></td>
         
         <?php
		 }
		 }
		 ?>
         
         </tr>
        
        </tbody>
        
        
        </table>
        
        
       <?php
	   }
	   ?>

</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>



            $("#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>