<?php
//user_fetch.php
include('db_con.php');
$query = '';
$output = array();
$query .= "
SELECT r.pay_id,r.dated,a.acc_name name,r.description,u.user_name, r.amount FROM pay_voucher r, accounts a,user_detail u WHERE u.user_id=r.user_id and
r.v_id=a.acc_no AND r.paid_by='Cash' AND";
if(isset($_POST["search"]["value"]))
{
	$query .= '(pay_id LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR description LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR a.acc_name LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR r.dated LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR u.user_name LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR amount LIKE "%'.$_POST["search"]["value"].'%")';
	
	
}
if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY pay_id DESC ';
}
if($_POST["length"] != -1)
{
	$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
	
	$sub_array = array();
	$sub_array[] = $row['pay_id'];
	$sub_array[] = $row['dated'];
	$sub_array[] = $row['name'];
	
	$sub_array[] = $row['description'];
	$sub_array[] = $row['user_name'];
	$sub_array[] = $row['amount'];
	
	
$sub_array[] = '<a href="ap_cash_payment_edit.php?update=1&id='.$row["pay_id"].'"><span class="btn btn-warning"><i class="fa fa-edit"></i></span></a>';
	$sub_array[] = '<button type="button" name="delete" id="'.$row["pay_id"].'" class="btn btn-danger btn-xs delete" ><i class="fa fa-remove"></i></button>';
	$data[] = $sub_array;
}
$output = array(
	"draw"				=>	intval($_POST["draw"]),
	"payordsTotal"  	=>  $filtered_rows,
	"payordsFiltered" 	=> 	get_total_all_payords($connect),
	"data"    			=> 	$data
);
echo json_encode($output);
function get_total_all_payords($connect)
{
	$statement = $connect->prepare("SELECT * FROM pay_voucher");
	$statement->execute();
	return $statement->rowCount();
}
?>