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

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

//saleheader (invoice_no, sale_date, customer_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 => 'id',
	1 => 'invoice_no', 
	2 => 'sale_date',
	3 => 'customer_id',
    4 => 'itemcount',
	5 => 'tamount'
	
    
);
//where sale_date BETWEEN '' AND value2
if($cID!='' && $startDate !='' && $endDate !='' ) {
	
	$startDate 	= new DateTime($startDate);	
	$endDate 	= new DateTime($endDate);	
	$startDate 	=$startDate->format('Y-m-d');	
	$endDate 	=$endDate->format('Y-m-d');
	
	$sql = "SELECT id FROM saleheader where customer_id='".$cID."' AND sale_date BETWEEN '".$startDate."' AND '".$endDate."' ";
	
	$query=mysqli_query($conn, $sql) or die("get  Data");
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  		
	

	$sql = "SELECT sh.invoice_no, sh.sale_date, sh.customer_id,  sh.po_num,sh.saleGSTNO, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id  WHERE customer_id='".$cID."' AND sale_date BETWEEN '".$startDate."' AND '".$endDate."'";
	

} else if($startDate !='' && $endDate !='') {

	$startDate 	= new DateTime($startDate);	$endDate 	= new DateTime($endDate);	$startDate 	=$startDate->format('Y-m-d');	$endDate 	=$endDate->format('Y-m-d');
	
	$sql = "SELECT id FROM saleheader where sale_date BETWEEN '".$startDate."' AND '".$endDate."' ";
	$query=mysqli_query($conn, $sql) or die("get  Data");
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  
	
	$sql = "SELECT sh.invoice_no, sh.sale_date, sh.customer_id, sh.po_num,sh.saleGSTNO, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id  WHERE  sale_date BETWEEN '".$startDate."' AND '".$endDate."'";


} else {
	


	$sql = "SELECT id FROM saleheader ";
	$query=mysqli_query($conn, $sql) or die("get  Data");
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  

	$sql = "SELECT sh.invoice_no, sh.sale_date, sh.customer_id, sh.dc_num, sh.po_num,sh.saleGSTNO, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id ";

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

	$sql.=" AND (  sh.id LIKE '".$requestData['search']['value']."%'  ";    
	$sql.=" OR  sh.invoice_no LIKE '".$requestData['search']['value']."%' ";    
	$sql.=" OR sh.sale_date LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR cm.cname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR sh.itemcount LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR sh.tamount LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR sh.saleGSTNO LIKE '".$requestData['search']['value']."%' ) ";
    
}
$query=mysqli_query($conn, $sql) or die("php: get Data");

$totalFiltered = mysqli_num_rows($query); 

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

//die("php: get sale Data".$sql);
$query=mysqli_query($conn, $sql) or die("php: get sale Data");
	
//(invoice_no, sale_date, customer_id, dc_num, po_num,saleGSTNO, itemcount, tamount, create_date, flag)
$data = array();
while( $row=mysqli_fetch_array($query) ) { 
	$nestedData=array(); 

	$nestedData[] = $row["invoice_no"];
    //$nestedData[] = $row["collectiondate"];
   
	$date = new DateTime($row["sale_date"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["cname"];
    $nestedData[] = $row["itemcount"];
    
	
	$tempvalue = number_format((float)$row["tamount"], 2, '.', '');
	$nestedData[] = str_replace('.00', '', number_format($tempvalue, 2, '.', ''));
    
	
    
    $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);  

?>
