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

	include 'config.php';

	$requestData= $_REQUEST;
	$columns = array(
		0=>'id', 
		1=>'title',
		2=>'exam_id',
		3=>'state_id',
		4=>'expiry_date',
		5=>'added_by',
		6=>'status',
		7=>'add_date',
		8=>'state_name',
		9=>'exam_name',
		10=>'username',
		11=>'display_order',
		12=>'notif_type'
	);

	$query="SELECT noti.id, noti.title, noti.exam_id, noti.state_id, noti.expiry_date, noti.added_by, noti.status, noti.add_date, state.state_name, ex.exam_name, admin.username, noti.display_order, noti.notif_type FROM sp_notification as noti LEFT JOIN sp_states as state on state.state_id=noti.state_id LEFT JOIN sp_exam as ex on ex.exam_id=noti.exam_id LEFT JOIN sp_admin_login as admin on admin.id=noti.added_by WHERE noti.title !='' ";
	$getData=$db->prepare($query);
	$getData->execute();
	$totalData = $getData->rowCount();
	$totalFiltered = $totalData;

	$searchTerms = "";
	if( !empty($requestData['search']['value']) ) { 
		$searchTerms.=" AND ( ex.exam_name LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR noti.title LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR noti.add_date LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR admin.username LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR state.state_name LIKE '".$requestData['search']['value']."%') ";
	}

	$query  = " SELECT noti.id, noti.title, noti.exam_id, noti.state_id, noti.expiry_date, noti.added_by, noti.status, noti.add_date, state.state_name, ex.exam_name, admin.username, noti.display_order, noti.notif_type FROM sp_notification as noti LEFT JOIN sp_states as state on state.state_id=noti.state_id LEFT JOIN sp_exam as ex on ex.exam_id=noti.exam_id LEFT JOIN sp_admin_login as admin on admin.id=noti.added_by where noti.title!='' $searchTerms order by noti.display_order desc";
	$getData=$db->prepare($query);
	$getData->execute();
	$totalFiltered = $getData->rowCount();

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

	$getData=$db->prepare($query);
	$getData->execute();

	$curDate = date('Y-m-d');
	$data = array();
	while($row=$getData->fetch()){

		if($row['status']=='1'){
			$show = "<span style='color:green'>Active</span>";
		} else {
			$show = "<span style='color:red'>Deactive</span>";
		}

		if($row['expiry_date'] >= $curDate ){
			$titles = "<span style='color:green'>".$row['title']."</span>";
		} else {
			$titles = "<span style='color:red'>".$row['title']."</span>";
		}

		$nestedData=array(); 
		$nestedData["id"] =$row['id'];
		$nestedData["title"] =$titles;
		$nestedData["exam_id"] =$row['exam_id'];
		$nestedData["exam_name"] =$row['exam_name'];
		$nestedData["state_name"] =$row['state_name'];
		$nestedData["date"] =$row['add_date'];
		$nestedData["expiry_date"] =$row['expiry_date'];
		$nestedData["display_order"] =$row['display_order'];
		$nestedData["notif_type"] =$row['notif_type'];
		$nestedData["username"] =$row['username'];
		$nestedData["status"] = $show;

		$data[] = $nestedData;
	}

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