<?php
	require_once '../../autoload.php';
	require( '../GetDataFromDB.php' );

	RightsCheck ("VIEW_EXAMLIST");

	$toProcess = true;
	
	$draw			= $_GET ['draw'];
	$start			= $_GET ['start'];
	$length			= $_GET ['length'];
	
	$columns 		= $_GET ['columns'];
	$order			= $_GET ['order'];
	$orderCol		= $order [0]['column'];
	$orderDir		= $order [0]['dir'];
	$orderColName	= $columns [$orderCol]['data'];
	$orderby		= " ORDER BY ".$orderColName." ".$orderDir;
        
	$limit = '';

	if ( isset($_GET['start']) && $_GET['length'] != -1 ) 
	{
		$limit = " OFFSET ".intval($_GET['start'])." LIMIT ".intval($_GET['length']);
	}

	$doc_id 		= ( isset($_GET['doc_id']) )? $_GET['doc_id'] : -1;
	$fullname 	= ( isset($_GET['fullname']) )? $_GET['fullname'] : -1;
	

  
  	/*
   $psql = "with r0 as (select * from staff_job where now()>begdate and now()<enddate and removed=false ),
r1 as ( select * from r0 left join staff_role on r0.staff_role_id=staff_role.id where staff_role.removed=false ),
r2 as (select * from r1,staff_member where r1.staff_id=staff_member.id),
r3 as (select staff_id, firstname,lastname, UPPER(lastname) || ' ' || firstname as fullname, r2.name, inst_name as institution, dep_name as department from r2 left join v_fullinfo_department on v_fullinfo_department.dep_id=r2.dep_id where instordepremoved=false)
select staff_member_id, document_id, fullname, name as job, institution, department, last_opened, action_at, TO_CHAR(action_at, 'dd/mm/yyyy HH24:MI') as action_at_fmt from r3 left join action_status_link on r3.staff_id=action_status_link.staff_member_id where document_id=".$doc_id." and fullname ilike '%".$fullname."%'";
	*/

	$psql = "with 
			r0 as ( select * from staff_job where now()>begdate and now()<enddate and removed=false ),
			r1 as ( select * from r0 left join staff_role on r0.staff_role_id=staff_role.id where staff_role.removed=false ),
			r2 as (select * from r1,staff_member where r1.staff_id=staff_member.id),
			r3 as (select staff_id, firstname,lastname, UPPER(lastname) || ' ' || firstname as fullname, r2.name, inst_name as institution, dep_name as department from r2 left join v_fullinfo_department on v_fullinfo_department.dep_id=r2.dep_id where instordepremoved=false)
			select 
				staff_member_id, 
				document_id, 
				fullname, 
				name as job, 
				institution, 
				department, 
				last_opened, 
				action_at, 
				TO_CHAR(action_at, 'dd/mm/yyyy HH24:MI') as action_at_fmt,
				action_id
			from 
				r3 
			left join 
				action_status_link 
			on 
				r3.staff_id=action_status_link.staff_member_id 
			where 
				document_id=".$doc_id." and fullname ilike '%".$fullname."%'";
	
	$recordsTotal 		= 1000;
	$recordsFiltered	= 0;
	
	$data				= Array ();
	
	if ( $toProcess == true )
		$data = GetDataFromDB ( $psql, $recordsFiltered, $limit, $orderby );
	
	$arr = array (
			"draw"            => intval( $draw ),
			"recordsTotal"    => intval( $recordsTotal ),
			"recordsFiltered" => intval( $recordsFiltered ),
			"data"            => $data,
			"sql"			  => $psql.$orderby.$limit
	);

	$result = json_encode ( $arr );

	echo $result;
	
 
?>