<?php



require_once("config/configDB.php"); 


$startDate=$_GET['startDate'];
$endDate=$_GET['endDate'];
//echo $create_date;startDate=01-10-2018&endDate=07-10-2018

//echo $startDate.'--'.$endDate;die('-------');
$queryString = $_SERVER['QUERY_STRING'];



if($startDate !='' && $endDate !='') {
	 
	$startDate 	= new DateTime($startDate);
	$endDate 	= new DateTime($endDate);
	$startDate 	=$startDate->format('Y-m-d');
	$endDate 	=$endDate->format('Y-m-d');
	
	$re = mysqli_query($conn,"SELECT sd.purchaseheader_id ,sh.id,sd.gst,sum(if(sd.gst=18,sd.cgst,0)) as gst18,sum(if(sd.gst=5,sd.cgst,0)) as gst5,sum(if(sd.gst=12,sd.cgst,0)) as gst12,sum(if(sd.gst=28,sd.cgst,0)) as gst28,sum(netamt) as totalamount,sh.invoice_no as invoice_no , sh.purchasedate, sh.supplier_id, sh.tamount, sh.create_date,sh.id,cm.sname,cm.gstin,pm.productname,sd.eqty,sd.rate,sd.discount,sd.totalgst,sh.tds,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.purchasedate)>=('".$startDate."') AND (sh.purchasedate)<=('".$endDate."') and sh.status=''  group by sd.id   ");
	
}else {

$re = mysqli_query($conn,"SELECT sd.purchaseheader_id ,sh.id,sd.gst,sum(if(sd.gst=18,sd.cgst,0)) as gst18,sum(if(sd.gst=5,sd.cgst,0)) as gst5,sum(if(sd.gst=12,sd.cgst,0)) as gst12,sum(if(sd.gst=28,sd.cgst,0)) as gst28,sum(netamt) as totalamount,sh.invoice_no as invoice_no , sh.purchasedate, sh.supplier_id, sh.tamount, sh.create_date,sh.id,cm.sname,cm.gstin,pm.productname,sd.eqty,sd.rate,sd.discount,sd.totalgst,sh.tds,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");
}
	//echo "-------------";
$res = array(); 
	while($row = mysqli_fetch_array($re))
	{
	$state=$row["supplier_state"];
	
	if($state=='Tamil Nadu')
	{

			array_push($res, array(
		 "Invoice No"=>($row['invoice_no']),
		 "Date"=>($row["purchasedate"]),
		 "Supplier"=>($row['sname']),
		 "GSTIN"=>($row['gstin']),
		 "product"=>($row['productname']),
		 "HSN Code"=>($row['hsn_code']),
		 "Rate"=>($row["rate"]),
		 "Discount"=>($row["discount"]),
		 "GST(%)"=>($row["gst"]),
		 "Qty"=>($row["eqty"]),
		  
		 "CGST(2.5%)"=>($row["gst5"]),
		 "SGST(2.5%)"=>($row["gst5"]),
		 "CGST(6%)"=>($row["gst12"]),
		 "SGST(6%)"=>($row["gst12"]),
		 "SGST(9%)"=>($row["gst18"]),
		 "CGST(9%)"=>($row["gst18"]),
		 "SGST(14%)"=>($row["gst18"]),
		 "CGST(14%)"=>($row["gst18"]),
		  "IGST"=>"0.00",
		 "NetAmount"=>($row["totalamount"])) );
				
		}
		
		else
		{
		
		array_push($res, array(
		 "Invoice No"=>($row['invoice_no']),
		 "Date"=>($row["purchasedate"]),
		 "Supplier"=>($row['sname']),
		 "GSTIN"=>($row['gstin']),
		 "product"=>($row['productname']),
		 "HSN Code"=>($row['hsn_code']),
		 "Rate"=>($row["rate"]),
		 "Discount"=>($row["discount"]),
		 "GST(%)"=>($row["gst"]),
		 "Qty"=>($row["eqty"]),
		  
		 "CGST(2.5%)"=>"0.00",
		 "SGST(2.5%)"=>"0.00",
		 "CGST(6%)"=>"0.00",
		 "SGST(6%)"=>"0.00",
		 "SGST(9%)"=>"0.00",
		 "CGST(9%)"=>"0.00",
		 "SGST(14%)"=>"0.00",
		 "CGST(14%)"=>"0.00",
		  "IGST"=>($row["totalgst"]),
		 "NetAmount"=>($row["totalamount"])) );
		
		}
		
	}
	
	//print_r($res);
	
	$filename = "Salereport".$startDate."-".$endDate.".xls";		 
            header("Content-Type: application/vnd.ms-excel");
			header("Content-Disposition: attachment; filename=\"$filename\"");
			ExportFile($res);
			
			
	function ExportFile($records) {
	$heading = false;
		if(!empty($records))
		  foreach($records as $row) {
			if(!$heading) {
			  // display field/column names as a first row
			  echo implode("\t", array_keys($row)) . "\n";
			  $heading = true;
			}
			echo implode("\t", array_values($row)) . "\n";
		  }
		exit;
}