<?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.saleheader_id ,sh.id, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin,pm.productname,sd.sale_eqty,sd.sale_prate,pm.hsn FROM saledetails as sd JOIN saleheader as sh on sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join product_master as pm on sd.productID=pm.id where (sh.sale_date)>=('".$startDate."') AND (sh.sale_date)<=('".$endDate."') and sh.status='' and sd.status=''    group by sd.saleheader_id,sh.id ");
	
}else {

$re = mysqli_query($conn,"SELECT sd.saleheader_id ,sh.id, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin,pm.productname,sd.sale_eqty,sd.sale_prate,pm.hsn FROM saledetails as sd JOIN saleheader as sh on sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join product_master as pm on sd.productID=pm.id where  sh.status='' and sd.status=''   group by sd.saleheader_id,sh.id");
}
	//echo "-------------";
$res = array(); 
	while($row = mysqli_fetch_array($re))
	{

			array_push($res, array(
		 "Invoice"=>($row['invoice_no']),
		 "Date"=>($row["sale_date"]),
		 "Customer"=>($row['cname']),
		 "GSTIN"=>($row['gstin']),
		 "product"=>($row['productname']),
		 "HSN Code"=>($row['hsn']),
		 "Rate"=>($row["sale_prate"]),
		 "Qty"=>($row["sale_eqty"]),
		  //"Amount"=>($row["sale_prate"]),
		 "Amount"=>number_format( ($row["sale_prate"]* $row["sale_eqty"]),2,'.',''),
		 //"Amount"=>($row["totalamount"]-($row["gst5"]+$row["gst12"]+$row["gst18"])),
		 "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"]),
		 "NetAmount"=>($row["tamount"])) );
				
		
		
	}
	
	//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;
}