<?php
require_once("config/configDB.php"); 

// storing  request (ie, get/post) global array to a variable  
$requestData= $_REQUEST;

//saleheader (invoice_no, purchasedate, supplier_id, dc_num, po_num,saleGSTNO, itemcount, tamount, create_date, flag)
$userpermissionPrint 	= $_POST['userpermissionPrint'];
$cID 		= $_POST['customerID'];
$startDate 	= $_POST['startDate'];
$endDate 	= $_POST['endDate'];

$columns = array( 
// datatable column index  => database column name
	0 => 'sh.id', 
	1 => 'purchase_date',
	2 => 'supplier_id',
    3 => 'gstin',
	4 => 'purchasenetamt',	
	5 => 'gst5',	
		6 => 'gst5',	
		7 => 'gst6',
			8 => 'gst6',
				9 => 'gst9',
					10 => 'gst9',
					11 => 'gst14',
					12 => 'gst14',
					
	15=> 'purchasenetamt'
);
//where purchasedate BETWEEN '' AND value2
 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,sd.purchase_prate,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  ";
	$query=mysqli_query($conn, $sql) or die("get  Data".$sql);
$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  
} 

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,sd.purchase_prate,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);
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  

	

}
if( !empty($requestData['search']['value']) ) {   

	$sql.=" AND ( sh.id LIKE '".$requestData['search']['value']."%' ";    
	$sql.=" OR sh.purchase_date LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR cm.sname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cm.gstin LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR totalamount LIKE '".$requestData['search']['value']."%' ";
	
    $sql.=" OR id LIKE '".$requestData['search']['value']."%' )";
}//die("php: get Data".$sql);
$query=mysqli_query($conn, $sql) or die("php2: get Data".$sql);
$totalFiltered = mysqli_num_rows($query); 

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";


$query=mysqli_query($conn, $sql) or die("php1: get sale Data".$sql);
	
//(invoice_no, purchasedate, supplier_id, dc_num, po_num,saleGSTNO, itemcount, tamount, create_date, flag)
$data = array();
//echo $sql;die("---");
while( $row=mysqli_fetch_array($query) ) { 
$state=$row["supplier_state"];
	$nestedData=array(); 
	
	if($state=='TamilNadu')
	{
	$nestedData[] = $row["invoice_no"];
    $date = new DateTime($row["purchase_date"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["sname"];
    $nestedData[] = $row["gstin"];
	$nestedData[] = $row["productname"];
	$nestedData[] = $row["hsn_code"];
	$nestedData[] = $row["rate"];
	
	$nestedData[] = $row["gst"];
	$nestedData[] = $row["eqty"];
   
	$nestedData[] = $row["gst5"];	
	$nestedData[] = $row["gst5"];	
	$nestedData[] = $row["gst12"];	
	$nestedData[] = $row["gst12"];	
	$nestedData[] = $row["gst18"];
		$nestedData[] = $row["gst18"];	
		$nestedData[] = $row["gst28"];
		$nestedData[] = $row["gst28"];	

	 $nestedData[] = $row["totalamount"];	
	//$nestedData[] = str_replace('.00', '', number_format($tempvalue, 2, '.', ''));
	}
	else
	{
	
	$nestedData[] = $row["invoice_no"];
    $date = new DateTime($row["purchase_date"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["sname"];
    $nestedData[] = $row["gstin"];
	$nestedData[] = $row["productname"];
	$nestedData[] = $row["hsn_code"];
	$nestedData[] = $row["rate"];
	
	$nestedData[] = $row["gst"];
	$nestedData[] = $row["eqty"];
   
	$nestedData[] = '0.00';	
	$nestedData[] = '0.00';	
	$nestedData[] = '0.00';	
	$nestedData[] = '0.00';	
	$nestedData[] = '0.00';
		$nestedData[] = '0.00';
		$nestedData[] = '0.00';
		$nestedData[] = '0.00';
	
	 $nestedData[] = $row["totalamount"];	
	
	}
	
	
	
    
    //$nestedData[] = $row["saleGSTNO"];
	//$nestedData[] = "<a href='productMasterEdit.php?id=".$row['id']."' id='productMaster_".$row['id']."' class='btn btn-primary'>Edit</a>";
	$edit  = "";
	if($userpermissionPrint==1) {
		//$edit .="<a class='btn btn-primary'>Print</a>";
	}

	//$nestedData[] = $edit."&nbsp";

    $data[] = $nestedData;
}

$json_data = array(
			"draw"            => intval( $requestData['draw'] ),   
			"recordsTotal"    => intval( $totalData ),  
			"recordsFiltered" => intval( $totalFiltered ), 
			"data"            => $data   
			);

echo json_encode($json_data);  

?>
