<?php
header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');
include 'config.php';
$requestData = $_REQUEST;

$columns = array(
	0 => 'id',
	1 => 'email_id',
	2 => 'reg_date',
	3 => 'status',
    4 => 'reason',
    5 => 'action'
);
$totalData = "0";
$getDataQuery  = "SELECT id, email_id, reg_date, status, reason FROM sp_subscription where id!=''";

$getData=$db->prepare($getDataQuery);
$getData->execute();
$totalFiltered = $getData->rowCount();

$searchTerms = "";
if (!empty($requestData['search']['value'])) {
	$searchTerms .= "where email_id LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR reg_date LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR status LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR reason LIKE '%" . $requestData['search']['value'] . "%' ";
}

$getDataQuery  = "SELECT id, email_id, reg_date, status, reason FROM sp_subscription   $searchTerms";

$getData=$db->prepare($getDataQuery);
$getData->execute();
$totalFiltered = $getData->rowCount();

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

$getData=$db->prepare($getDataQuery);
$getData->execute();
$data = array();

while ($querydata = $getData->fetch()) {

		$nestedData = array();
		$nestedData["id"] = $querydata['id'];
		$nestedData["email_id"] = $querydata['email_id'];
		$nestedData["reg_date"] = $querydata['reg_date'];
		$nestedData["status"] = $querydata['status'];
        $nestedData["reason"] = $querydata['reason'];
        $nested["action"] = $querydata['id'];
		$data[] = $nestedData;
	}

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