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

	include 'config.php';
	global $wpdb;
	$POST_TABLE = "naukri_wp_posts";
	$EXAM_CATEGORY = "sp_exam_category";
    $EXAM_TABLE = "sp_exam";
	$STATE_TABLE = "sp_states";
	$EXAM_TYPE = "sp_exam_type";
	

	$requestData = $_REQUEST;
	$columns = array(
		0 => 'exam_id',
		1 => 'exam_name',
		2 => 'post_name',
		3 => 'url_slug',
		4 => 'off_web_link',
	    5 => 'name',
	    6 => 'state_name',
	    7 => 'type',
		8 => 'status',
	);

	$getDataQuery  = "select e.exam_id, e.exam_name, e.post_name, e.url_slug, e.off_web_link, ec.name, e.status ,st.state_name ,et.type from $EXAM_TABLE as e left join $STATE_TABLE as st on st.state_id=e.state_id left join $EXAM_TYPE as et on et.type_id=e.type_id left join $EXAM_CATEGORY as ec on ec.cat_id=e.cat_id WHERE e.exam_id !='' ";
	$getData=$db->prepare($getDataQuery);
	$getData->execute();
	$totalData = $getData->rowCount();
	$totalFiltered = $totalData;

	$searchTerms = "";
	if (!empty($requestData['search']['value'])) {
		$searchTerms .= " AND ( e.exam_id LIKE '" . $requestData['search']['value'] . "%' ";
		$searchTerms .= " OR e.exam_name LIKE '%" . $requestData['search']['value'] . "%' ";
		$searchTerms .= " OR e.post_name LIKE '%" . $requestData['search']['value'] . "%' ";
		$searchTerms .= " OR e.url_slug LIKE '%" . $requestData['search']['value'] . "%' ";
		$searchTerms .= " OR ec.name LIKE '%" . $requestData['search']['value'] . "%' ) ";
	}

	$getDataQuery  = "select e.exam_id, e.exam_name, e.post_name, e.url_slug, e.off_web_link, ec.name, e.status ,st.state_name ,et.type from $EXAM_TABLE as e left join $STATE_TABLE as st on st.state_id=e.state_id left join $EXAM_TYPE as et on et.type_id=e.type_id left join $EXAM_CATEGORY as ec on ec.cat_id=e.cat_id WHERE e.exam_id !='' $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()) {

		$exam_slug = $querydata['url_slug'];
		$checkExist_page = $db->prepare("SELECT count(ID) as countId FROM $POST_TABLE WHERE post_name = '$exam_slug' AND post_status='publish'");
		
		$checkExist_page->execute();
		$checkPage = $checkExist_page->fetch(PDO::FETCH_ASSOC);

		$nestedData = array();
		
		$nestedData["exam_id"] = $querydata['exam_id'];
		$nestedData["exam_name"] = $querydata['exam_name'];
		$nestedData["post_name"] = $querydata['post_name'];
		$nestedData["url_slug"] = $querydata['url_slug'];
		$nestedData["off_web_link"] = $querydata['off_web_link'];
		$nestedData["name"] = $querydata['name'];
	    $nestedData["state_name"] = $querydata['state_name'];
	    $nestedData["type"] = $querydata['type'];
	    $nestedData["status"] = $querydata['status'];
		$nestedData["countId"] = $checkPage['countId'];
		$data[] = $nestedData;
	}

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