<?php
	require_once '../../autoload.php';

	// with t2 as (with t1 as (select * from staff_member sm left join staff_job sj on sm.id=sj.staff_id and sm.removed=false and sj.removed=false and sm.active=true) select * from t1 left join staff_role sr on t1.staff_role_id=sr.id and sr.removed=false) select staff_id, staff_role_id, lastname, firstname, name as role_name from t2 group by staff_id,staff_role_id, lastname, firstname,name order by staff_role_id

	$result = array (
			"status"	=> 'KO',
			"results"	=> array ()
	);

	$pdo = getPDO ();

	if ( $pdo )
	{
		$sql = "with t2 as 
					(
						with t1 as 
						(	
							select 
								* 
							from 
								staff_member sm 
							left join 
								staff_job sj 
							on 
								sm.id=sj.staff_id and sm.removed=false and sj.removed=false and sm.active=true
						) 
						select 
							* 
						from 
							t1 
						left join 
							staff_role sr 
						on 
							t1.staff_role_id=sr.id and sr.removed=false
					) 
					select 
						staff_id, 
						staff_role_id, 
						lastname, 
						firstname, 
						name as role_name 
					from 
						t2 
					group by 
						staff_id,staff_role_id, lastname, firstname,name 
					order by 
						staff_role_id";
		
		$s0 = $pdo->prepare ( $sql );

		if ( $s0->execute () )
		{
			$result ['status'] = 'OK';

			$d0 = $s0->fetchAll ( PDO::FETCH_ASSOC );

			for ( $i=0; $i<count ( $d0 ); $i++ )
				array_push ( $result['results'], array ( 	'staff_id' 			=> $d0[$i]['staff_id'],
															'staff_role_id' 	=> $d0[$i]['staff_role_id'],
															'lastname' 			=> $d0[$i]['lastname'],
															'firstname'			=> $d0[$i]['firstname'],
															'role_name'			=> $d0[$i]['role_name'] ) );
		}
	}
	
	header('Content-type: application/json');
	
	echo json_encode ( $result );
?>

