<?php

  function CanAttribue($profil) { 

    		
			if( $profil == "Directeur Approvisionnement"

			    || $profil == "Directeur Assurance Qualite" || $profil == "Directeur Commercial" 

				|| $profil == "Directeur MGX" || $profil == "Directeur Production" 

				|| $profil == "Directeur Qualite" || $profil == "Directeur Maintenance" 

				|| $profil == "Assistant" || $profil == "Directeur RH" || $profil =="RESPONSABLE"){

					return 1;

				}

		return 0;		

  }	

  
  function callWebService($matricule , $operation, $year = '2024'){

			 // $certificate_location = "/home/dsisophal/public_html/certificates/certificate.crt";


		 $url = "https://evaluation.sophal.net/controller/Webservices.php"; 

		 //$url = "https://5191-105-235-139-49.eu.ngrok.io//controller/Webservices.php"; 

		 $accessKey = '4Lak&L1JZ$u2X6ctDrH*ML@g#'; //CSRF ATTACK

					   
				$data = array(

					'accessKey' => $accessKey ,

					'matricule' => $matricule,

					'operation' => $operation,

					'year' => $year

				);


				$resource = curl_init();

				curl_setopt($resource, CURLOPT_URL, $url);

				curl_setopt($resource, CURLOPT_HTTPHEADER, array('Content-Type:multipart/form-data'));

				// curl_setopt($resource, CURLOPT_HTTPHEADER, array('Content-Type:multipart/form-data','ngrok-skip-browser-warning: 69420'));

				curl_setopt($resource, CURLOPT_POST, 1);

				curl_setopt($resource, CURLOPT_RETURNTRANSFER, 1);			

				curl_setopt($resource, CURLOPT_POSTFIELDS, $data);

			//	curl_setopt($resource, CURLOPT_CAINFO, $certificate_location);

			//	curl_setopt($resource, CURLOPT_CAPATH, $certificate_location);

    			curl_setopt($resource, CURLOPT_CONNECTTIMEOUT,1000); 

                curl_setopt($resource, CURLOPT_TIMEOUT, 1000); //timeout in seconds

                curl_setopt( $resource, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);

                curl_setopt($resource, CURLOPT_SSL_VERIFYHOST, true);


				$response = curl_exec($resource);  

				
				if(curl_errno($resource))

                {

                    echo 'Erreur Curl : ' . curl_error($resource);

                }

		      
				curl_close($resource);

							
				if ($response !== false) {

					
					return $response;

				}

				
		return false;	

		
	}

	
	function getMatricule(){

		
		global $DB;	

	   
		$query = "SELECT registration_number as matricule from glpi_users 

		where  glpi_users.id =? ";

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


		$stmt->bind_param("i" , $_SESSION["glpiID"]);

 
		$stmt->execute();	  

	   	$result = $stmt->get_result();

		  
		  if ($result->num_rows > 0) {

			  $res = $result->fetch_assoc();	

			   return $res['matricule'];

		   }

		   
		  return null;

		
	}

	
	function last_generated_Sequence($type){		

		global $DB;	

		   
			$query = "SELECT * FROM `sequence` where  type = ? ";

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


			$stmt->bind_param("s" , $type);

 
			$stmt->execute();	  

	   		$result = $stmt->get_result();		   

             if ($result->num_rows > 0) {

                   $res = array();			  

				   while($row = $result->fetch_assoc()) {

					  $res[] = $row;

					}

                    return 	$res[0]['last_seq'] ;				

			   }			   

		return null;	

	}

	
	function update_sequence($last_seq , $type){		

		global $DB;	

		$stmt = $DB->prepare("UPDATE `sequence` SET `last_seq`= ? WHERE type = ? ");

				 $stmt->bind_param("is", $last_seq  , $type);

				 $stmt->execute();	

	}

	
   function add_conge(array $req ,$tickets_id){

	   global $DB;

	   
	    if( isset($_SESSION['modele']) == 2 && !empty($_SESSION['modele'])){	

		
		   $mat      = getMatricule();	

		   $response = callWebService(strval($mat) , 'get_MonDossierRH'); 

		   
		           //supprimer UTF-8-BOM

					for ($i = 0; $i <= 31; ++$i) { 

						$response = str_replace(chr($i), "", $response); 

					}

					$response = str_replace(chr(127), "", $response);     

					if (0 === strpos(bin2hex($response), 'efbbbf')) {

					   $response = substr($response, 3);

					}

					//supprimer UTF-8-BOM

					
					$response = json_decode($response,true);

					
           if($response != false){

			   
              $last_generated_Sequence = last_generated_Sequence('conge annuel')+1 ;		   

			  $stmt = $DB->prepare("INSERT INTO `ticket_conge`( `tickets_id`, `num_reference`, `matricule`, `fullname`,

			  `fonction`, `structure`, `sous_structure`, `date_recrutement`,`exercice`, `nbr_jour` ,`date_debut`,`date_fin`, 

			  `date_reprise`,`designations`,`adresse`, `reliquat` ,`date_creation`) 

									VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,'Congé Annuel',?,?,NOW())");

									
				$date_debut   = Date('Y-m-d', strtotime($req['dc_date_depart']));

				$date_fin     = Date('y-m-d', strtotime($date_debut."+{$req['dc_nbr_jour']} days"));

				$date_reprise = Date('y-m-d', strtotime($date_fin.'+1 days'));

				$reliquat     = $req['max_jour']-$req['dc_nbr_jour'];

				
			 $stmt->bind_param("iisssssssisssss"                                                    ,

								  $tickets_id                                                      ,

                                  $last_generated_Sequence                                         ,								  

								  $response['employe']['MATRICULE']                                ,

								  $response['employe']['employe_name']                             ,								                              

								  $response['employe']['Libelle']                                  ,

								  $response['employe']['DEPARTEMENT']                              ,

								  $response['employe']['SERVICE']                                  ,

								  date('Y-m-d', strtotime($response['employe']['DateRecrutement'])),

								  $req['dc_exercice']                                              ,

								  $req['dc_nbr_jour']                                              ,

								  $date_debut                                                      ,

								  $date_fin                                                        ,

								  $date_reprise  								  				   ,

                                  $req['dc_adresse']                                               ,

                                  $reliquat								  

							  );					  								


			 $stmt->execute();	

             update_sequence($last_generated_Sequence ,'conge annuel');			 

		   }

	  }

   }

   
   function add_Group_Observateur($tickets_id , $groups_id ){

	   global $DB;

	   $type = 3;

	   $stmt = $DB->prepare("INSERT INTO `glpi_groups_tickets` (`groups_id`, `type`, `tickets_id`) VALUES (?,?,?)");

	   $stmt->bind_param("iii" ,$groups_id,$type,$tickets_id);

	   $stmt->execute();

	   
   }

				
	function add_Group_Attribue($tickets_id , $groups_id ){

	   global $DB;

	   $type = 2;

	   $stmt = $DB->prepare("INSERT INTO `glpi_groups_tickets` (`groups_id`, `type`, `tickets_id`) VALUES (?,?,?)");

	   $stmt->bind_param("iii" ,$groups_id,$type,$tickets_id);

	   $stmt->execute();	   

   }	


   function add_Validation($tickets_id , $id_validateur ,$id_demandeur_validation){

	   global $DB;

	   
	   $stmt = $DB->prepare("INSERT INTO `glpi_ticketvalidations` (`entities_id`, `tickets_id`, `users_id_validate`, 

	   `comment_submission`, `users_id`, `submission_date`, `status`, `timeline_position`) 

	   VALUES ('0', ?, ?, '', ?, NOW(), '2', '1')");

	   $stmt->bind_param("iii" ,$tickets_id , $id_validateur ,$id_demandeur_validation);

	   $stmt->execute();	   

   }  


    function add_User_Demandeur($tickets_id ,$id_demandeur){

	   global $DB;

	   $type = 1;

	   $stmt = $DB->prepare("INSERT INTO `glpi_tickets_users` (`users_id`, `type`, `tickets_id`, `alternative_email`) 

	                        VALUES (?, ?, ?, '')");

	   $stmt->bind_param("iii" , $id_demandeur, $type, $tickets_id);

	   $stmt->execute();	   

   }  


   function soumis_a_validation($tickets_id){

	   global $DB;

	   $type = 2;

	   	   
	   $stmt = $DB->prepare("UPDATE `glpi_tickets` SET `global_validation` = ? WHERE `id` = ? ");

	   $stmt->bind_param("ii" , $type, $tickets_id);

	   $stmt->execute();	   

   }   


   function add_Attestation_Travail($tickets_id){

	   	global $DB;

		
		   $mat      = getMatricule();	

		   $response = callWebService(strval($mat) , 'get_MonDossierRH'); 

		   
		           //supprimer UTF-8-BOM

					for ($i = 0; $i <= 31; ++$i) { 

						$response = str_replace(chr($i), "", $response); 

					}

					$response = str_replace(chr(127), "", $response);     

					if (0 === strpos(bin2hex($response), 'efbbbf')) {

					   $response = substr($response, 3);

					}

					//supprimer UTF-8-BOM

					
					$response = json_decode($response,true);

					
           if($response != false){	

              $last_generated_Sequence = last_generated_Sequence('attestation de travail')+1 ;		   

			  $stmt = $DB->prepare("INSERT INTO `ticket_document`( `tickets_id`, `num_reference`, `modele`, `matricule`, `fullname`, `date_naissance`, 

													  `lieu_naissance`, `fonction`, `structure`, `date_recrutement`, `date_creation`) 

									VALUES (?,?,'attestation de travail' ,?,?,?,?,?,?,?,NOW())");

			 $stmt->bind_param("iisssssss"                                                         ,

								  $tickets_id                                                      ,

                                  $last_generated_Sequence                                         ,								  

								  $response['employe']['MATRICULE']                                ,

								  $response['employe']['employe_name']                             ,

								  date('Y-m-d', strtotime($response['employe']['DateNaissance']))  ,

								  $response['employe']['LieuNaissance']                            ,

								  $response['employe']['Libelle']                                  ,

								  $response['employe']['DEPARTEMENT']                              ,

								  date('Y-m-d', strtotime($response['employe']['DateRecrutement']))							  

							  );					  								


			 $stmt->execute();	

             update_sequence($last_generated_Sequence ,'attestation de travail');			 

		   }

	  
   }	


    function add_ticket($id_categorie,$name,$table,$glpiID){

	   global $DB;

				   
	   $stmt = $DB->prepare("INSERT INTO `glpi_tickets` (`date`, `type`, `itilcategories_id`, `entities_id`, `status`, 

	                                                     `requesttypes_id`, `urgency`, `impact`, `locations_id`, 

	                                                      `priority`, `name`, `content`, `id`, `users_id_lastupdater`, `users_id_recipient`, 

														  `global_validation`, `date_creation`, `date_mod`) 

	       VALUES (NOW(), '2', ?, '0', '1', '1', '3', '3', '0', '3',?, ?, '0', ?, ?, '1', NOW(), NOW())");

	   $stmt->bind_param("issii" , $id_categorie,$name,$table,$glpiID,$glpiID );

	   $stmt->execute();	   

   } 


   function isValidConge($tickets_id){

	   global $DB;

				   
	   $stmt = $DB->prepare("SELECT status FROM `glpi_ticketvalidations` WHERE `tickets_id` = ? ");

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

	   $stmt->execute();

       $result = $stmt->get_result();

       $status = array();

	   
		while ($row = $result->fetch_assoc()) {

			 $status[] = $row;

		}		

		
		if( count($status) != 0){

			for($m=0 ; $m <count($status) ; $m++){

				if($status[$m]['status']  != 3) return false;

			}

		}else{

			return false;

		}

		return true;	   

   } 

   
   function add_User_Observateur($tickets_id ,$id_observateur){

	   global $DB;

	   $type = 3;

	   $stmt = $DB->prepare("INSERT INTO `glpi_tickets_users` (`users_id`, `type`, `tickets_id`, `alternative_email`) 

	                        VALUES (?, ?, ?, '')");

	   $stmt->bind_param("iii" , $id_observateur, $type, $tickets_id);

	   $stmt->execute();	   

   } 

   
   function getResponsable($idUser){

	
		global $DB;	

		
		$query = "SELECT users_id_supervisor 

						from glpi_users 

				where id = ? LIMIT 1";

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


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


		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$row = $result->fetch_assoc();		     		 

		}


		return  $row['users_id_supervisor'];

	}


	function getUserHierarchy($id){

		
		$list = array();

		$list[] = $id;

		while(getResponsable($id) != 0) {

			$id = getResponsable($id);

			$list[] = $id;	

			
		}

			return $list;

			
	}

	
	function add_validation_percent($tickets_id){

       global $DB;

		   	   
	   $stmt = $DB->prepare("UPDATE `glpi_tickets` SET `validation_percent` = 100 WHERE `id` = ? ");

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

	   $stmt->execute();	   

   } 

   
   function get_detail_ticket($id_ticket){

	
		global $DB;	

		
        $res = array();

		$query = "SELECT * 

						from glpi_tickets 

				where id = ?' LIMIT 1";

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


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


		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$res = $result->fetch_assoc();		     		 

		}


		return  $res;

	}

	
	function get_fullname_user($id_user){

	
		global $DB;	

		
		$query = "SELECT concat(realname , ' ' , firstname) as fullname FROM `glpi_users` 

				where id = ? LIMIT 1";

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


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


		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$res = array();

			$res = $result->fetch_assoc();	

            return  $res['fullname'];			

		}


		return  null;

	}


	function get_group_users($id_group){

	
		global $DB;	

		
		$query = "SELECT * FROM `glpi_groups_users` 

				where groups_id  = ?";

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


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


		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$res = array();

			while($res[] = $result->fetch_assoc()){}

            return  $res;			

		}


		return  null;

	}

	
	function atributeTo($tickets_id,$idUser){

		global $DB;


		$type = 2;

	   	$stmt = $DB->prepare("INSERT INTO `glpi_tickets_users` (`users_id`, `type`, `tickets_id`, `alternative_email`) 

	                        VALUES (?, ?, ?, '')");

	   	$stmt->bind_param("iii" , $idUser, $type, $tickets_id);

	   	$stmt->execute();

	}


	function changeStatusTo($tickets_id,$status){

		global $DB;

					   
		$stmt = $DB->prepare("UPDATE `glpi_tickets` SET `status` = ? WHERE `id` = ? ");

		$stmt->bind_param("ii" , $status, $tickets_id);

		$stmt->execute();	  

	}


	function getHSEmployees($all,$id,$dateDeb=null,$dateFin=null){


		global $DB;	

		if($all){

			$query = "SELECT t.id,(SELECT CONCAT(realname,' ',firstname) FROM `glpi_users` WHERE id = users_id_recipient) as name,

			content,

			CASE

    		 WHEN v.status = 2 THEN 'Demande de validation : En attente de validation'

             WHEN v.status = 3 THEN 'Demande de validation : Acceptee'

             WHEN v.status = 4 THEN 'Demande de validation : Refusee'

			END AS Validation FROM `glpi_tickets` t

            LEFT JOIN `glpi_ticketvalidations` v ON v.tickets_id = t.id

				WHERE itilcategories_id = ?";

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


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

		}else{

			$query = "SELECT t.id,(SELECT CONCAT(realname,' ',firstname) FROM `glpi_users` WHERE id = users_id_recipient) as name,

			content,

			CASE

    		 WHEN v.status = 2 THEN 'Demande de validation : En attente de validation'

             WHEN v.status = 3 THEN 'Demande de validation : Acceptee'

             WHEN v.status = 4 THEN 'Demande de validation : Refusee'

			END AS Validation FROM `glpi_tickets` t

            LEFT JOIN `glpi_ticketvalidations` v ON v.tickets_id = t.id

				WHERE t.status < 5 and itilcategories_id = ? AND date_creation BETWEEN ? AND ?";

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


			$stmt->bind_param("iss" , $id,$dateDeb,$dateFin);

		}

		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$res = array();

			while($res[] = $result->fetch_assoc()){}

            return  $res;			

		}


		return  null;

	}

	
	function getReclamations($all,$id,$dateDeb=null,$dateFin=null){

		global $DB;	
		if($all){
			$query = "
				SELECT 
					t.id, 
					CONCAT(u.realname, ' ', u.firstname) AS name, 
					t.content,
					CASE
					WHEN t.status = 1 THEN 'Nouveau'
					WHEN t.status = 2 THEN 'En cours'
					WHEN t.status = 3 THEN 'En cours'
					WHEN t.status = 4 THEN 'En attente'
					WHEN t.status = 5 THEN 'R�solu'
					WHEN t.status = 6 THEN 'Clos'
					END AS status,
					t.name as ticket_name,
					u.registration_number AS matricule,
					Date(t.date) as date
				FROM 
					glpi_tickets t
				INNER JOIN 
					glpi_users u 
				ON 
					t.users_id_recipient = u.id
				WHERE 
					t.itilcategories_id = ?
			";
			$stmt = $DB->prepare($query);
			$stmt->bind_param("i", $id);
		}else{
			$query = "
				SELECT 
					t.id, 
					CONCAT(u.realname, ' ', u.firstname) AS name, 
					t.content,
					CASE
					WHEN t.status = 1 THEN 'Nouveau'
					WHEN t.status = 2 THEN 'En cours'
					WHEN t.status = 3 THEN 'En cours'
					WHEN t.status = 4 THEN 'En attente'
					WHEN t.status = 5 THEN 'R�solu'
					WHEN t.status = 6 THEN 'Clos'
					END AS status,
					t.name as ticket_name,
					u.registration_number AS matricule,
					Date(t.date) as date
				FROM 
					glpi_tickets t
				INNER JOIN 
					glpi_users u 
				ON 
					t.users_id_recipient = u.id
				WHERE 
					t.itilcategories_id = ? AND date_creation BETWEEN ? AND ?
			";
			$stmt = $DB->prepare($query);

			$stmt->bind_param("iss" , $id,$dateDeb,$dateFin);
		}
		$stmt->execute();	  
		$result = $stmt->get_result();
		  
		if ($result->num_rows > 0) {
			$res = array();
			while($res[] = $result->fetch_assoc()){}
            return  $res;			
		}

		return  null;
	}

	function getUsers(){

		global $DB;	

		
		$query = "SELECT u.id, COALESCE(concat(u.realname , ' ' , u.firstname),'') as fullname,

					 COALESCE(u.registration_number,'') as matricule,

					 COALESCE((SELECT value FROM picklist WHERE id = u.id_fonction),'') as fonction, 

					 COALESCE((SELECT value FROM picklist WHERE id = u.id_structure),'') as structure, 

					 COALESCE(concat(u1.realname , ' ' , u1.firstname),'') as superiorname 

					 FROM `glpi_users` u 

					 JOIN `glpi_users` u1 ON u.users_id_supervisor = u1.id 

					 WHERE u.is_active = 1

					 AND u.realname IS NOT NULL 

					 AND u.registration_number IS NOT NULL

					 AND u.registration_number != '' 

					 AND Length(u.registration_number) = 4 UNION 

				  SELECT u.id, COALESCE(concat(u.realname , ' ' , u.firstname),'') as fullname,

					 COALESCE(u.registration_number,'') as matricule,

					 COALESCE((SELECT value FROM picklist WHERE id = u.id_fonction),'') as fonction, 

					 COALESCE((SELECT value FROM picklist WHERE id = u.id_structure),'') as structure, 

					 COALESCE(concat(u1.realname , ' ' , u1.firstname),'') as superiorname 

					 FROM `glpi_users` u 

					 JOIN `glpi_users` u1 ON u.users_id_supervisor = u1.id WHERE u.registration_number in ('2566')";

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


		$stmt->execute();	  

		$result = $stmt->get_result();

		  
		if ($result->num_rows > 0) {

			$res = array();

			while($row = $result->fetch_assoc()){

				$res[] = $row;

			}

            return  $res;			

		}


		return  [];

	}
	
	
		// closing hr tickets after 2 days
	function close_hr_tickets(){			
		global $DB;	
		$stmt = $DB->prepare("update glpi_tickets set status = 6 , closedate = now() where id in ( SELECT 
				id
				from glpi_tickets 
				where STATUS != 6 and itilcategories_id in (12,15,16,36,38,72) and (CASE 
						WHEN DAYOFWEEK(date_creation) IN (4) THEN  TIMESTAMPDIFF(HOUR,date_creation,CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 2 DAY)  + TIMESTAMPDIFF(HOUR, CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 4 DAY,now())
						WHEN DAYOFWEEK(date_creation) IN (5) THEN  TIMESTAMPDIFF(HOUR,date_creation,CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 1 DAY)  + TIMESTAMPDIFF(HOUR, CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 3 DAY,now())
						WHEN DAYOFWEEK(date_creation) IN (6) THEN TIMESTAMPDIFF(HOUR,CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 2 DAY, now())
						WHEN DAYOFWEEK(date_creation) IN (7) THEN TIMESTAMPDIFF(HOUR,CONCAT(DATE(date_creation), ' 00:00:00') + INTERVAL 1 DAY, now())
						ELSE TIMESTAMPDIFF(HOUR,date_creation, now())
					END) > 48)");
		return $stmt->execute();	
	}