<?php 
error_reporting(0);
require_once("config/configDB.php"); 
require "userPermission.php";
require "financial_year_calculation.php";

$sql_customer = mysqli_query($conn,"SELECT sh.id as SLID,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_customer = '';

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

$sql_product = '';

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

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

?>
<!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">
	
 <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="purchasetaxDashboard.php" method="post">
    
    <div class="form-group">
      <label for="email">From Date:</label>
      <input type="date" class="form-control" id="fromDate" name="startDate">
    </div>
    <div class="form-group">
      <label for="pwd">To Date</label>
      <input type="date" class="form-control" id="toDate" name="endDate">
    </div>

    <button type="submit" class="btn btn-primary btn-md datewise-submit-saleoverall">Submit</button>

    <button type="button" class="btn btn-info btn-md datewise-submit-sale" onClick="tableToExcel('a', 'W3C Example Table')">Excel</button>
     
  </form>
  
</div>


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



<div class="container col-sm-12">
<div id="a">
<table id="salestockDashboardOverAll" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
                <th>Supplier</th>
                <th>GSTIN</th> 
                <th>Product</th>
                <th>HSN</th>
                <th>Rate</th>
               
                <th>GST(%)</th>
                <th>Qty</th>  
                
                <th>CGST(2.5%)</th>
                <th>SGST(2.5%)</th>
                <th>CGST(6%)</th>
                <th>SGST(6%)</th>
                <th>CGST(9%)</th>
                <th>SGST(9%)</th>
                <th>CGST(14%)</th>
                <th>SGST(14%)</th>
                <th>IGST</th>             
                <th>Amount</th>
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        <tbody>
            <?php
			$startDate 	= $_POST['startDate'];
$endDate 	= $_POST['endDate'];
			 if($startDate !='' && $endDate !='') {
	 
	$startDate 	= new DateTime($_POST['startDate']);
	$endDate 	= new DateTime($_POST['endDate']);
	$startDate 	=$startDate->format('Y-m-d');
	$endDate 	=$endDate->format('Y-m-d');

	
	$sql = "SELECT sd.purchaseheader_id ,sh.id,sd.purchase_gst,sum(if(sd.purchase_gst=18,sd.purchase_cgst,0)) as gst18,sum(if(sd.purchase_gst=5,sd.purchase_cgst,0)) as gst5,sum(if(sd.purchase_gst=12,sd.purchase_cgst,0)) as gst12,sum(if(sd.purchase_gst=28,sd.purchase_cgst,0)) as gst28,sum(sd.purchasenetamt) as totalamount,sh.invoice_no as invoice_no , sh.purchase_date, sh.supplier_id, sh.tamount, sh.create_date,sh.id,cm.sname,cm.gstin,pm.productname,sd.purchase_eqty as eqty,sd.purchase_prate as rate,pm.hsn_code,cm.supplier_state FROM purchasedetails as sd JOIN purchaseheader as sh on sh.id =sd.purchaseheader_id LEFT JOIN supplier_master as cm on cm.id = sh.supplier_id inner join product_master as pm on sd.productID=pm.id where (sh.purchase_date)>=('".$startDate."') AND (sh.purchase_date)<=('".$endDate."') and sh.status=''   group by sd.id  ";
			}
			else
			{
			$sql = "SELECT sd.purchaseheader_id ,sh.id,sd.purchase_gst,sum(if(sd.purchase_gst=18,sd.purchase_cgst,0)) as gst18,sum(if(sd.purchase_gst=5,sd.purchase_cgst,0)) as gst5,sum(if(sd.purchase_gst=12,sd.purchase_cgst,0)) as gst12,sum(if(sd.purchase_gst=28,sd.purchase_cgst,0)) as gst28,sum(sd.purchasenetamt) as totalamount,sh.invoice_no as invoice_no , sh.purchase_date, sh.supplier_id, sh.tamount, sh.create_date,sh.id,cm.sname,cm.gstin,pm.productname,sd.purchase_eqty as eqty,sd.purchase_prate as rate,pm.hsn_code,cm.supplier_state FROM purchasedetails as sd JOIN purchaseheader as sh on sh.id =sd.purchaseheader_id LEFT JOIN supplier_master as cm on cm.id = sh.supplier_id inner join product_master as pm on sd.productID=pm.id where  sh.status=''  group by sd.id";
			}
			$query=mysqli_query($conn, $sql) or die("get  Data".$sql);
			  while( $row=mysqli_fetch_array($query) ) { 
$state=$row["supplier_state"];
if($state=='TamilNadu')
	{
			?>
             
	 <tr>
    	<td><?php echo $row["invoice_no"]?></td>
    
    <td><?php $date = new DateTime($row["purchase_date"]);echo $date->format('d-m-Y')?></td> 
    <td><?php echo $row["sname"];?></td>
    <td><?php echo $row["gstin"];?></td>
	<td><?php echo $row["productname"];?></td>
	<td><?php echo $row["hsn_code"];?></td>
	<td><?php echo $row["rate"];?></td>
	
	<td><?php echo $row["purchase_gst"];?></td>
	<td><?php echo $row["eqty"]?></td>
   
	<td><?php echo $row["gst5"];?></td>	
	<td><?php echo $row["gst5"];?></td>	
	<td><?php echo $row["gst12"];?></td>	
	<td><?php echo $row["gst12"];?></td>	
	<td><?php echo $row["gst18"];?></td>
		<td><?php echo $row["gst18"];?></td>	
		<td><?php echo $row["gst28"];?></td>
		<td><?php echo $row["gst28"];?></td>	
<td>0.00</td>
	 <td><?php echo $row["totalamount"];?></td>	
     </tr>
     <?php
	}
	else
	{   
	?>
    <tr>
    	<td><?php echo $row["invoice_no"]?></td>
    
    <td><?php $date = new DateTime($row["purchase_date"]);echo $date->format('d-m-Y')?></td> 
    <td><?php echo $row["sname"];?></td>
    <td><?php echo $row["gstin"];?></td>
	<td><?php echo $row["productname"];?></td>
	<td><?php echo $row["hsn_code"];?></td>
	<td><?php echo $row["rate"];?></td>
	
	<td><?php echo $row["purchase_gst"];?></td>
	<td><?php echo $row["eqty"]?></td>
   
	<td>0.00</td>	
	<td>0.00</td>	
	<td>0.00</td>	
	<td>0.00</td>	
	<td>0.00</td>	
		<td>0.00</td>	
		<td>0.00</td>	
		<td>0.00</td>	
<td><?php echo $row["gst5"]+$row["gst5"]+$row["gst12"]+$row["gst12"]+$row["gst18"]+$row["gst18"]+$row["gst28"]+$row["gst28"];?></td>
	 <td><?php echo $row["totalamount"];?></td>	
     </tr>
	
	<?php
	}  }
     ?>      
        </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>
	<!--<script type="text/javascript" language="javascript" src="js/purchasetax.js"></script>-->
    <script>


$(document).ready(function() {

    //$('.sale-stockreport-pdf').hide();
    $('.sale-stockOverreport-excle').hide();
    //sale-stockreport-excel
    
         
            } );

           /* $("#fromDate").datepicker({
        minDate: '-1Y-6M',
        maxDate: '+1Y+6M',
        dateFormat: 'dd-mm-yy',
        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: 'dd-mm-yy',
        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>