<?php
require_once '../../Server/connection.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

## Custom Field value
$searchByClientInv = $_POST['searchByClientInv'];
$searchByClientName = $_POST['searchByClientName'];
$searchByCurrencyName = $_POST['searchByCurrencyName'];
$searchByYear = $_POST['searchByYear'];
$searchByMonth1 = $_POST['searchByMonth'];
$searchByDate = $_POST['searchByDate'];
$searchByDate11 = date('Y-m-d', strtotime($searchByDate));
//$searchByStaffCurrent = 1;
## Search 




//if($searchByClientFileStatus=='Pending Files'){
	//$searchByClientFStatus = 0;
//}else{
	//$searchByClientFStatus = 1;
//}

$searchQuery = " ";
if($searchByClientInv != ''){
    $searchQuery .= " and (invoices.inv_no like '%".$searchByClientInv."%') ";
}




if($searchByClientName != ''){
    $searchQuery .= " and (collected_payments.client_id='".$searchByClientName."') ";
}

if($searchByCurrencyName != ''){
    $searchQuery .= " and (collected_payments.currency_id='".$searchByCurrencyName."') ";
}

if($searchByYear != ''){
    $searchQuery .= " and (collected_payments.year='".$searchByYear."') ";
}


if($searchByMonth1 != ''){
	
	
	if($searchByMonth1==1){
	 $searchByMonth = 'JAN';
	 $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";
	  }else if($searchByMonth1==2){
	 $searchByMonth = 'FEB';	
     $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	 
	  }else if($searchByMonth1==3){
	$searchByMonth = 'MARCH';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==4){
	$searchByMonth = 'APR';	
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==5){
	$searchByMonth = 'MAY';	
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==6){
	$searchByMonth = 'JUNE';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==7){
	$searchByMonth = 'JULY';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==8){
	$searchByMonth = 'AUG';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==9){
	$searchByMonth = 'SEPT';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==10){
	$searchByMonth = 'OCT';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else if($searchByMonth1==11){
	$searchByMonth = 'NOV';
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }else{
	$searchByMonth = 'DEC';	
    $searchQuery .= " and (collected_payments.month='".$searchByMonth."') ";	
	  }
	
	
}


if($searchByDate != ''){
    $searchQuery .= " and (collected_payments.date='".$searchByDate11."') ";
}




if($searchValue != ''){ 
    $searchQuery .= " and (invoices.inv_no like '%".$searchValue."%')";
}

//if($searchByStaffCurrent != ''){
    //$searchQuery .= " and (status_id='".$searchByStaffCurrent."') ";
//}



## Total number of records without filtering
$sel = mysqli_query($conn,"select count(*) as allcount from collected_payments,invoices where collected_payments.inv_id=invoices.inv_id");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of records with filtering
$sel = mysqli_query($conn,"select count(*) as allcount from collected_payments,invoices WHERE collected_payments.inv_id=invoices.inv_id and 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Total number of records with filtering
$sel7 = mysqli_query($conn,"select sum(amount_paid) as total from collected_payments,invoices WHERE collected_payments.inv_id=invoices.inv_id and 1 ".$searchQuery);
$records7 = mysqli_fetch_assoc($sel);
$totalAmountwithFilter7 = $records7['total'];

## Fetch records
$empQuery = "select * from collected_payments,invoices WHERE collected_payments.inv_id=invoices.inv_id and 1 ".$searchQuery." order by collected_payments.payment_id desc limit ".$row.",".$rowperpage;
//$empQuery = "select * from collected_payments,invoices WHERE collected_payments.inv_id=invoices.inv_id and 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
$x = 1;
while ($row = mysqli_fetch_assoc($empRecords)){
	
	$categoriesId = $row['payment_id'];
	$clientId = $row['client_id'];
	$payment_desc = $row['payment_desc'];
	$pNo = $row['cheque_no'];
	$currencyId = $row['currency_id'];
	$invId = $row['inv_id'];
	$amount_paid = number_format($row['amount_paid'],2);
	$coldate = $row['date'];
	$collectionDate = date('d-m-Y', strtotime($coldate));
	$payment_mode_id = $row['payment_mode_id'];
	$space = " ";
	
	$sql8120 = "SELECT * FROM currency where currency_id='$currencyId'";
			$result8120 = $conn->query($sql8120);
				while($data8120 = mysqli_fetch_array($result8120))
							{
							$currencyName = $data8120['currencyName']; 
							}
							
	$tt = $amount_paid.$space.$currencyName;
	
	$sql812 = "SELECT * FROM clients where client_id='$clientId'";
			$result812 = $conn->query($sql812);
				while($data812 = mysqli_fetch_array($result812))
							{
							$clientfName = $data812['client_fname'].$space.$data812['client_lname']; 
							}
							
							
	$sql813 = "SELECT * FROM payment_mode where payment_mode_id='$payment_mode_id'";
			$result813 = $conn->query($sql813);
				while($data813 = mysqli_fetch_array($result813))
							{
							$pmodeName = $data813['name']; 
							}
	
	
	$sql8132 = "SELECT * FROM invoices where inv_id='$invId'";
			$result8132 = $conn->query($sql8132);
				while($data8132 = mysqli_fetch_array($result8132))
							{
							$invNumber = $data8132['inv_no']; 
							}
	
	
	$button = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="editTripPayment.php?i='.$categoriesId.'" id="editOrderModalBtn"> <center><i class="glyphicon glyphicon-edit"></i></center></a>
	</div>';
	
	
	$button2 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="pdf_tripreceipt.php?i='.$categoriesId.'" id="editOrderModalBtn"> <center><i class="glyphicon glyphicon-eye-open"></i></center></a>
	</div>';
	
	
	//$button1 = '<!-- Single button -->
	//<div class="btn-group">
	    //<a type="button" class="btn btn-primary" href="uploaded_Docs.php?i='.$categoriesId.'" id="editOrderModalBtn"><center><i class="glyphicon glyphicon-eye-open"></i></center></a>
	//</div>';
	
	
	$button4 = '<!-- Single button -->
	<div class="btn-group">
	<a type="button" class="btn btn-primary" data-toggle="modal" data-target="#removeCategoriesModal" id="removeCategoriesModalBtn" onclick="removeCategories('.$categoriesId.')"> <i class="glyphicon glyphicon-trash"></i></a>
	
	</div>';
	
	
	
    $data[] = array(
    		"payment_id"=>$x,
			"AmountPaid"=>$tt,
			"Paid By"=>$clientfName,
			"invNo"=>$invNumber,
			"collectionDate"=>$collectionDate,
			"paymentMode"=>$pmodeName,
			"paymentNo"=>$pNo,
			"paymentDesc"=>$payment_desc,
			"edit"=>$button,
			"rec"=>$button2,
			"delete"=>$button4
    	);
		$x++;
		
	
		
		
		
}




## Response
$response = array(
    "draw" => intval($draw),
	//"iTotalRecords" => $totalAmountwithFilter7,
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
	//"iTotalAmount" => $totalAmountwithFilter7,
    "aaData" => $data
);

echo json_encode($response);
