<?php

include ('../inc/includes.php');
error_reporting(0);


function getIdResultatFicheEvaluationCount() {
	global $DB;

	$query = "SELECT * from id_resultat_fiche_evaluation_count";
	$result = $DB->query($query);

	if ($result->num_rows > 0) {

		$row = $result->fetch_assoc();
		return $row['value'];
	}
}


function getFicheEvaluation($id_fiche_evaluation){
		
	global $DB;	
	   
	$query = "SELECT * from fiche_evaluation WHERE id_fiche_evaluation = ?";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("i", $id_fiche_evaluation);
	
	$stmt->execute();	  
	$result = $stmt->get_result();
	
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$rows[] = $row;
		}
		return $rows[0];
	}
		   
	return null;	
}

function getResultatLignesFicheEvaluation($id_fiche_evaluation, $currentuserId){
		
	global $DB;	
	   
	$idResultatFicheEvaluationCount = getIdResultatFicheEvaluationCount();
	if($currentuserId != "")
		$users = getSubSubUsers($currentuserId);
	
	$myarray = [];

	$query = "SELECT
					resultat_ligne_fiche_evaluation.*,
					ligne_fiche_evaluation.*,
					glpi_users.registration_number as matricule,
					glpi_users.name as fullname,
					_fn.value AS fonction,
					_st.value AS structure,
					_sup.name as superiorname
				FROM
					`resultat_ligne_fiche_evaluation`
				JOIN glpi_users ON glpi_users.id = resultat_ligne_fiche_evaluation.id_candidat
				left join ligne_fiche_evaluation on ligne_fiche_evaluation.id_ligne_fiche_evaluation = resultat_ligne_fiche_evaluation.id_ligne_fiche_evaluation
				LEFT JOIN glpi_users _sup ON
					_sup.id = glpi_users.users_id_supervisor
				LEFT JOIN picklist _fn ON
					_fn.id = glpi_users.id_fonction
				LEFT JOIN picklist _st ON
					_st.id = glpi_users.id_structure
				WHERE
					resultat_ligne_fiche_evaluation.id_ligne_fiche_evaluation = 508 ";
	
	// for($i =0; $i < $idResultatFicheEvaluationCount; $i++) {
	// 	$query = "SELECT * from resultat_ligne_fiche_evaluation rlfe
	// 		LEFT JOIN ligne_fiche_evaluation lfe ON lfe.id_ligne_fiche_evaluation = rlfe.id_ligne_fiche_evaluation 
	// 		WHERE rlfe.id_resultat_fiche_evaluation = ? and lfe.id_fiche_evaluation=?";

	 	if($currentuserId != "") {
	 		$query = $query." and (resultat_ligne_fiche_evaluation.id_candidat = ?";

	 		foreach($users as $user) {
	 			$query = $query." or resultat_ligne_fiche_evaluation.id_candidat = ".$user['id'];
	 		}
	 		$query = $query.") ";
			$stmt = $DB->prepare($query);

	 		$stmt->bind_param("i",$currentuserId);
	 		$stmt = $DB->prepare($query);
	 	} else {
	 		// $query = $query." group by rlfe.id_resultat_fiche_evaluation";
	 		$stmt = $DB->prepare($query);

	 		// $stmt->bind_param("ii", $i,$id_fiche_evaluation);
	 	}
	 	//echo $query;
	
		$stmt->execute();	  
		$result = $stmt->get_result();
		  
		if ($result->num_rows > 0) {
			$rows = array();	 
			while($row = $result->fetch_assoc()) {
				$rows[] = $row;
			}
			$finalarray[] = $rows;
		}
	// }


	// $finalarray = [];
	// foreach($myarray as $anrow) {
	// 	$query = "SELECT * from resultat_ligne_fiche_evaluation rlfe
	// 		LEFT JOIN ligne_fiche_evaluation lfe ON lfe.id_ligne_fiche_evaluation = rlfe.id_ligne_fiche_evaluation 
	// 		WHERE rlfe.id_resultat_fiche_evaluation = ?";
	// 	$stmt = $DB->prepare($query);

	// 	$stmt->bind_param("i", $anrow[0]['id_resultat_fiche_evaluation']);
	
	// 	$stmt->execute();	  
	// 	$result = $stmt->get_result();
		
	// 	if ($result->num_rows > 0) {
	// 		$rows = array();	 
	// 		while($row = $result->fetch_assoc()) {
	// 			$rows[] = $row;
	// 		}
	// 		$finalarray[] = $rows;
	// 	}
	// }
		   
	return $finalarray;	
}


function getUserId($idUser){
	
	global $DB;	
	$subUsers = array();

	$query = "SELECT id , CONCAT(realname,' ', firstname) as fullname  from glpi_users where id=? ";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("i", $idUser);

	$stmt->execute();	  
    $result = $stmt->get_result();
	  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$subUsers[] = $row;
		}
		return $subUsers[0];		 
	}

	return $subUsers;
} 

function getFUser($idUser){
	
	global $DB;	
	$subUsers = array();
	
	$query = "SELECT u.`id`,registration_number as matricule  , CONCAT(realname,' ', firstname) as fullname,(select CONCAT(realname,' ', firstname) FROM `glpi_users` WHERE id = (SELECT users_id_supervisor FROM `glpi_users` WHERE id = ?) ) as superiorname, pl.`value` as fonction, pls.`value` as structure FROM `glpi_users` u 
		LEFT JOIN `picklist` pl on u.`id_fonction` = pl.`id`
        	LEFT JOIN `picklist` pls on u.`id_structure` = pls.`id`
       		 where u.`id` = ?";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("ii", $idUser, $idUser);

	$stmt->execute();	  
    $result = $stmt->get_result();
	  
	if ($result->num_rows > 0) {
		$rows = array();
		while($row = $result->fetch_assoc()) {
			$subUsers[] = $row;
		}
		return $subUsers[0];		 
	}

	return $subUsers;
} 


function getSubSubUsers($idUser){

	global $DB;	
	$subSubUsers = array();

	$users = getSubUsers($idUser);
	foreach($users as $user) {

		$query = "SELECT id , CONCAT(realname,' ', firstname) as fullname  from glpi_users where users_id_supervisor = ? ";
		$stmt = $DB->prepare($query);

		$stmt->bind_param("i" , $user['id']);

		$stmt->execute();	  
    	$result = $stmt->get_result();
	  
		if ($result->num_rows > 0) {
			$rows = array();	 
			while($row = $result->fetch_assoc()) {
				$rows[] = $row;
			}
			$subSubUsers = array_merge($subSubUsers,$rows);	 
		}
	}
	$all = array_merge($subSubUsers,$users);
	
	return $all;
}


function getSubUsers($idUser){
	
	global $DB;	
	$subUsers = array();

	$query = "SELECT id , CONCAT(realname,' ', firstname) as fullname  from glpi_users where users_id_supervisor = ? ";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("i" , $idUser);

	$stmt->execute();	  
    $result = $stmt->get_result();
	  
	if ($result->num_rows > 0) {
		$rows = array();	 
		while($row = $result->fetch_assoc()) {
			$subUsers[] = $row;
		}
		return $subUsers;		 
	}

	return $subUsers;
} 

function deleteLignesFicheEvaluation($id_fiche_evaluation) {
		
	global $DB;	
	
	$query = "DELETE FROM resultat_ligne_fiche_evaluation WHERE id_resultat_fiche_evaluation = ?";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("i" , $id_fiche_evaluation);

	$stmt->execute();	

	$query = "DELETE FROM resultat_ligne_fiche_eval_annuel WHERE id_resultat_fiche_evaluation = ?";
	$stmt = $DB->prepare($query);

	$stmt->bind_param("i" , $id_fiche_evaluation);

	$stmt->execute();	
	
	return null;	
}

function cache_write($filename,$content){
	
	return file_put_contents($filename,$content);

}

function cache_read($filename){

	return file_get_contents($filename);

}
 
function getHeaders(){
	global $DB;


	$query = "SELECT * from ligne_fiche_evaluation where id_fiche_evaluation = 24 and id_parent = 0 and ordre != 0";
	$result = $DB->query($query);

	if ($result->num_rows > 0) {
		$row = array();

		while ($data = $result->fetch_assoc()) {
			$row[] = $data;
		}
		
		return $row;
	}

}

function getEvalScore($id_resultat_fiche_evaluation,$id_ligne_fiche_evaluation){
	global $DB;

	$query = "SELECT eval FROM `resultat_ligne_fiche_eval_annuel` WHERE id_resultat_fiche_evaluation = ? and id_ligne_fiche_evaluation = ?";

	$stmt = $DB->prepare($query);

	$stmt->bind_param("ii" , $id_resultat_fiche_evaluation,$id_ligne_fiche_evaluation);

	$stmt->execute();	  
    $result = $stmt->get_result();


	if ($result->num_rows > 0) {
		
		return $result->fetch_assoc();
	}

}

function insertScore($user_id, $id_rfe, $score,$year){
	
	global $DB;

	$query = "INSERT INTO  resultat_eval_scores (`user_id`, `type_id`, `id_rfe`, `score`) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE `score` = ?;";

	$stmt = $DB->prepare($query);

	$stmt->bind_param("isiss" , $user_id,$year,$id_rfe,$score,$score);
	
	return $stmt->execute();
}

function getScore($user_id){
	global $DB;

	$query = "SELECT score FROM `resultat_eval_scores` WHERE user_id = ?";

	$stmt = $DB->prepare($query);

	$stmt->bind_param("i" , $user_id);

	$stmt->execute();	  
    $result = $stmt->get_result();

	return $result->fetch_assoc();
}