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

	function GetInstDepFromExtID ( $ext_id, $type )
	{
		$results = array ();

		$pdo = getPDO ();
	
		if ( $pdo )
		{
			error_log ( "ext_id : " .$ext_id."   type : ".$type );

			$sql = "select STRING_AGG(tag_id::text, ',') tags_list from tag_link where tag_link.ext_id={$ext_id} and tag_link.type='{$type}'";
			$s0 = $pdo->prepare ( $sql );

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

				$tags_list = $d0[0]['tags_list'];

				error_log ( "tags_list : " .$tags_list );
		
				$sql = "with req1 as (select distinct institution.id as inst_id, institution.name, tag.id, '' as department, ('{'||(string_agg(department.id::text,',') over (partition by institution.id))||'}')::int[] <@ array[{$_SESSION['deplistid']}] as alldepininst from institution,tag,tag_link,department where tag_link.type = 'institution'	and tag_link.ext_id = institution.id and tag.id = tag_link.tag_id and tag.removed = false and department.inst_id = institution.id and department.removed = false order by institution.name),
				req2 as (select department.inst_id,institution.name,tag.id,department.id as dep_id,department.name from institution,department,tag,tag_link where department.id in({$_SESSION['deplistid']}) and institution.id =department.inst_id and tag_link.type = 'department' and tag_link.ext_id = department.id and tag.id = tag_link.tag_id and tag.removed = false and institution.removed = false and department.removed = false order by institution.name, tag.name)
				select req1.id as tag_id,department.inst_id, department.id as dep_id, department.name from department,req1 where department.removed = false and department.inst_id=req1.inst_id and req1.id in ({$tags_list}) union select req2.id as tag_id,department.inst_id, department.id as dep_id, department.name from department,req2 where department.removed = false and department.id=req2.dep_id and req2.id in ({$tags_list})";
		
				$s1 = $pdo->prepare ( $sql );

				if ( $s1->execute () )
				{
					$d1 = $s1->fetchAll ( PDO::FETCH_ASSOC );

					for ( $i=0; $i<count($d1); $i++ )
					{
						array_push ( $results, array ( "tag_id" => $d1[$i]['tag_id'], "inst_id" => $d1[$i]['inst_id'], "dep_id" => $d1[$i]['dep_id'] ) );
					}
				}
			}
		}

		error_log ( "Nb elems : " .count ($results) );

		return $results;
	}

	error_log ( "##################################################" );
	error_log ( "### get_instdep_from_extid ###" );

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

	$ext_id = $_POST['ext_id'];
	$type 	= $_POST['type'];
	
	$result['status'] = 'OK';

	$results = GetInstDepFromExtID ( $ext_id, $type );
	$deps =array ();
	for ( $i=0;$i<count($results); $i++ )
		array_push ( $deps, $results[$i]['dep_id'] );
	$dep_list = implode ( ",", $deps );
	error_log ( "dep_list : " .$dep_list );

	if ( strlen($dep_list) > 0 )
	{
		$pdo = getPDO ();
	
		if ( $pdo )
		{
			$placeholders = array_map(function ($index) {
    				return ':param_' . ($index + 1);
					}, array_keys($deps));
			
			$sql = "with req0 as (select institution.id as inst_id, staff_job.dep_id as dep_id, staff_member.id as staff_id, staff_member.lastname, staff_member.firstname, institution.name as institution, department.name as department from staff_member,staff_job,department,institution where staff_member.active=true and staff_member.removed=false and department.removed=false and institution.removed=false and staff_job.removed=false and staff_member.id=staff_job.staff_id and staff_job.dep_id =department.id and department.inst_id=institution.id and current_timestamp >= staff_job.begdate and current_timestamp<=staff_job.enddate and department.id in (".implode(',', $placeholders)."))
			select distinct (inst_id) as inst_id,institution from req0";

			$s0 = $pdo->prepare ( $sql );
			foreach ( $deps as $index => $value )
				$s0->bindParam ( ':param_'.($index+1), $deps[$index], PDO::PARAM_INT );
			
			if ( $s0->execute () )
			{
				$d0 = $s0->fetchAll ( PDO::FETCH_ASSOC );

				for ( $i=0; $i<count ($d0); $i++ )
				{
					error_log ( "Inst [".$i."] : " .$d0 [$i]['inst_id']. " / " .$d0[$i]['institution'] );

					$r0 = array (		"text" => $d0[$i]['institution'],
										// "ident"	=> $inst_id,
										"expanded" => true,
										"checked" => true,
										"nodes" => array() );

					
					// Departments
					$sql = "with req0 as (select institution.id as inst_id, staff_job.dep_id as dep_id, staff_member.id as staff_id, staff_member.lastname, staff_member.firstname, institution.name as institution, department.name as department from staff_member,staff_job,department,institution where staff_member.active=true and staff_member.removed=false and department.removed=false and institution.removed=false and staff_job.removed=false and staff_member.id=staff_job.staff_id and staff_job.dep_id =department.id and department.inst_id=institution.id and current_timestamp >= staff_job.begdate and current_timestamp<=staff_job.enddate and department.id in (".implode(',', $placeholders)."))
					select distinct (dep_id) as dep_id,department from req0 where inst_id=:inst_id";

					$s1 = $pdo->prepare ( $sql );
					foreach ( $deps as $index => $value )
						$s1->bindParam ( 'param_'.($index+1), $deps[$index], PDO::PARAM_INT );
					$s1->bindParam ( ":inst_id", $d0[$i]['inst_id'] );

					if ( $s1->execute () )
					{
						$d1 = $s1->fetchAll ( PDO::FETCH_ASSOC );

						for ( $j=0; $j<count ($d1); $j++ )
						{
							error_log ( "Dep [".$j."] : " .$d1 [$j]['dep_id']. " / " .$d1[$j]['department'] );

							$r1 = array (		"text" => $d1[$j]['department'],
										// "ident"	=> $inst_id,
										"expanded" => true,
										"checked" => true,
										"nodes" => array() );



							// Staff member
							$sql = "with req0 as (select institution.id as inst_id, staff_job.dep_id as dep_id, staff_member.id as staff_id, staff_member.lastname, staff_member.firstname, institution.name as institution, department.name as department from staff_member,staff_job,department,institution where staff_member.active=true and staff_member.removed=false and department.removed=false and institution.removed=false and staff_job.removed=false and staff_member.id=staff_job.staff_id and staff_job.dep_id =department.id and department.inst_id=institution.id and current_timestamp >= staff_job.begdate and current_timestamp<=staff_job.enddate and department.id in (".implode(',', $placeholders)."))
							select distinct (staff_id) as staff_id,UPPER(lastname) as lastname, firstname from req0 where dep_id=:dep_id";

							$s2 = $pdo->prepare ( $sql );
							foreach ( $deps as $index => $value )
								$s2->bindParam ( 'param_'.($index+1), $deps[$index], PDO::PARAM_INT );
							$s2->bindParam ( ":dep_id", $d1[$j]['dep_id'] );

							if ( $s2->execute () )
							{
								$d2 = $s2->fetchAll ( PDO::FETCH_ASSOC );

								for ( $k=0; $k<count ($d2); $k++ )
								{
									error_log ( "Staff [".$k."] : " .$d2 [$k]['staff_id']. " / " .$d2[$j]['lastname'] );
									$fullname = $d2[$k]['lastname'] . " " . $d2[$k]['firstname'];
									$r2 = array (		"text" =>  $fullname,
										// "ident"	=> ($d1[$j]['dep_id']."_".$d2[$k]['staff_id']),
										"ident"	=> $d2[$k]['staff_id'],
										"expanded" => true,
										"checked" => true );

									array_push ( $r1['nodes'], $r2 );
								}
							}

							array_push ( $r0['nodes'], $r1 );
						}
					}



					array_push ( $result['results'], $r0 );
				}
			}
		}
	}
	
	header('Content-type: application/json');
	
	echo json_encode ( $result );
?>