1115 lines
20 KiB
PHP
1115 lines
20 KiB
PHP
<?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();
|
||
}
|
||
|