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

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

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

$columns  = array( 
		0 => 'id', 
		1 => 'collectiondate',
		2 => 'customerID',
		3 => 'pending',
		4 => 'paymenttype',
		5 => 'amount',
		6 => 'cashno'
	);
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 customer_payment_header where customerID ='".$cID."' AND collectiondate BETWEEN '".$startDate."' AND '".$endDate."'   and status='' ";
$query=mysqli_query($conn, $sql) or die("get  Data test1");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  

$sql = "SELECT cc.bank_name,cc.handover,cc.transactionno,cc.paymentreference,cc.id,cc.cashno,cc.payment_through, cc.collectiondate, cc.customerID, cc.pending,cc.remark, cc.paymenttype, cc.received_amount,cc.discount, cm.cname FROM customer_payment_header as cc LEFT JOIN customer_master as cm on cm.id = cc.customerID  WHERE cc.customerID ='".$cID."' AND cc.collectiondate BETWEEN '".$startDate."' AND '".$endDate."' and cc.status='' ";

}  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 customer_payment_header where collectiondate BETWEEN '".$startDate."' AND '".$endDate."' and status='' ";
$query=mysqli_query($conn, $sql) or die("get  Data test1");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  

$sql = "SELECT cc.bank_name,cc.handover,cc.transactionno,cc.paymentreference,cc.id,cc.cashno,cc.payment_through, cc.collectiondate, cc.customerID, cc.pending,cc.remark, cc.paymenttype, cc.received_amount,cc.discount, cm.cname FROM customer_payment_header as cc LEFT JOIN customer_master as cm on cm.id = cc.customerID  WHERE cc.collectiondate BETWEEN '".$startDate."' AND '".$endDate."' and cc.status=''";

} else {

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

$sql = "SELECT cc.handover,cc.transactionno,cc.paymentreference,cc.id,cc.cashno,cc.payment_through, cc.collectiondate, cc.customerID, cc.pending,cc.remark, cc.paymenttype, cc.received_amount,cc.discount, cm.cname FROM customer_payment_header as cc LEFT JOIN customer_master as cm on cm.id = cc.customerID where  cc.status=''";

}



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

	$sql.=" AND ( cc.id LIKE '".$requestData['search']['value']."%' ";    
	$sql.=" OR cc.collectiondate LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR cm.cname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cc.pending LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cc.paymenttype LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR cc.received_amount LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cc.cashno LIKE '".$requestData['search']['value']."%' )";
}
$query=mysqli_query($conn, $sql) or die("php: get Data2".$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("php: get purchase Data3");
	
//(invoice_no, purchase_date, supplier_id, dc_num, po_num,purchaseGSTNO, itemcount, tamount, create_date, flag)
$data = array();
while( $row=mysqli_fetch_array($query) ) { 
	$nestedData=array(); 

	$nestedData[] = $row["cashno"];

    //$nestedData[] = $row["collectiondate"];
   
	$date = new DateTime($row["collectiondate"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["cname"];
    //$nestedData[] = number_format(round($row["pending"]),2,'.','');
	$nestedData[] = number_format(round($row["received_amount"]),2,'.','');
	$nestedData[] = number_format(round($row["discount"]),2,'.','');
	//$nestedData[] = number_format(round($row["pending"] - $row["received_amount"]),2,'.','');;
	
    $nestedData[] = $row["paymenttype"];
	//$nestedData[] = $row["handover"];
	// $nestedData[] = $row["paymentreference"];
	// $nestedData[] = $row["transactionno"];
	 // $nestedData[] = $row["bank_name"];
	 $nestedData[] = $row["remark"];
	
	
    
    
	//$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);  

?>
