<?php
header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');	

include 'config.php';

$requestData = $_REQUEST;
$columns = array(
	0 => 'id',
	1 => 'exam_id',
	2 => 'type_id',
	3 => 'type_name',
	4 => 'gen',
    5 => 'obc',
    6 => 'sc',
    7 => 'st',
	8 => 'ebc',
	9 => 'wbc',
	10 => 'total_vac',
	11 => 'age_limit',
	12 => 'eligibility',
	13 => 'status',
	14 => 'date_added',
	15 => 'edit'
);

$getDataQuery  = "SELECT e.id, e.exam_id, e.type_id AS vtype, e.type_name, e.gen, e.obc, e.sc, e.st, e.ebc, e.wbc, e.total_vac, e.age_limit, e.eligibility, e.status, e.date_added,et.type,ex.exam_name FROM sp_vacancy_detail as e left join sp_exam_type as et on et.type_id=e.type_id left join sp_exam as ex on e.exam_id=ex.exam_id WHERE e.exam_id !=0 ";

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

$searchTerms = "";
if (!empty($requestData['search']['value'])) {
	$searchTerms .= " AND ( e.id LIKE '" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR e.type_name LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR e.total_vac LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR et.type LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR ex.exam_name LIKE '%" . $requestData['search']['value'] . "%' ";
	$searchTerms .= " OR e.age_limit LIKE '" . $requestData['search']['value'] . "%') ";
}

$getDataQuery  = "SELECT e.id, e.exam_id, e.type_id AS vtype, e.type_name, e.gen, e.obc, e.sc, e.st, e.ebc, e.wbc, e.total_vac, e.age_limit, e.eligibility, e.status, e.date_added,et.type,ex.exam_name FROM sp_vacancy_detail as e left join sp_exam_type as et on et.type_id=e.type_id left join sp_exam as ex on e.exam_id=ex.exam_id WHERE e.exam_id !=0 $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["examname"] = $querydata['exam_name'];
	$nestedData["typename"] = $querydata['vtype'];
	$nestedData["type_name"] = $querydata['type_name'];
	$nestedData["gen"] = $querydata['gen'];
	$nestedData["obc"] = $querydata['obc'];
    $nestedData["sc"] = $querydata['sc'];
    $nestedData["st"] = $querydata['st'];
    $nestedData["ebc"] = $querydata['ebc'];
    $nestedData["wbc"] = $querydata['wbc'];
    $nestedData["total_vac"] = $querydata['total_vac'];
    $nestedData["age_limit"] = $querydata['age_limit'];
    $nestedData["eligibility"] = $querydata['eligibility'];
    $nestedData["status"] = $querydata['status'];
    $nestedData["date_added"] = $querydata['date_added'];
	$nestedData["edit"] = $querydata['id'];
	$data[] = $nestedData;
}

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

echo json_encode($json_data);
?>