- Les ajouts
L'array contient $currentCertificat qui contient la liste des certificats déjà présents, chacun ayant comme $key son id, si un certificat est déjà présent dans cet array, la requête ne sera pas traité
L'array $listeCertificats contient la liste des certificats actuellement associés au à la spécialité, il permet de déterminer les suppression
*/
foreach ($_POST['certificat'] as $certificatKey => $certificatValue) {
if ($certificatKey != '0') {
// On remplit $sqlAffectationData['add']
if ($certificatKey == 'new') {
foreach ($certificatValue as $certificatId) {
if (count(checkCertificat($certificatId, array())) == 0) {
$sqlAffectationData['add'][$certificatId] = $certificatId;
}
}
} else {
if (isset($listeCertificats)) {
if (is_numeric($certificatKey) && isset($listeCertificats[$certificatKey]) && $listeCertificats[$certificatKey] != $certificatValue && count(checkCertificat($certificatId, array())) == 0) {
$sqlAffectationData['edit'][$certificatKey] = array('id' => $certificatKey, 'service' => $certificatValue);
}
if (isset($listeCertificats[$certificatKey])) {
unset($listeCertificats[$certificatKey]);
}
}
}
}
}
// On prend en charge les suppressions
if (isset($listeCertificats)) {
foreach ($listeCertificats as $affectionId => $affectationValue) {
$sqlAffectationData['delete'][$affectionId] = $affectionId;
}
}
/**
* getCertificatInfo - Retourne les informations relatives au certificat
*
* @category stageFunction
* @param int $id Identifiant du certificat
* @return array Array contenant les informations relatives au certificat
*
* @Author Ali Bellamine
*
* Contenu de l'array retourné :<br>
* ['id'] => (int) Identifiant du certificat<br>
* ['nom'] => (string) Nom du certificat<br>
* ['promotion']['id'] => (int) Id de la promotion associée au certificat<br>
* ['promotion']['nom'] => (string) Nom de la promotion associée au certificat<br>
* ['services'][id du service]['id'] => (int) Id des services associés au certificat<br>
* ['etudiants'][id de l'étudiant]['id'] => (int) Id des étudiants associés au certificat<br>
* ['nb']['services'] => (int) Nombre de services associés au certificat<br>
* ['nb']['etudiants'] => (int) Nombre d'étudiants associés au certificat
*
*/
function getCertificatInfo($id)
{
/*
Initialisation des variables
*/
global $db;
// Permet l'accès à la BDD
$erreur = array();
$specialite = array();
/*
On vérifie l'existance du certificat
*/
$erreur = checkCertificat($id, $erreur);
if (count($erreur) == 0) {
// Récupérations des données
$sql = 'SELECT c.id id, c.nom nom, p.nom promo, p.id promotionId, (SELECT count(*) FROM servicecertificat WHERE idCertificat = c.id LIMIT 1) nbServices
FROM certificat c
INNER JOIN promotion p ON p.id = c.promotion WHERE c.id = ? LIMIT 1';
$res = $db->prepare($sql);
$res->execute(array($id));
$certificat = array();
// On construit l'array contenant les données
if ($res_f = $res->fetch()) {
$certificat['id'] = $res_f['id'];
$certificat['nom'] = $res_f['nom'];
$certificat['promotion']['nom'] = $res_f['promo'];
$certificat['promotion']['id'] = $res_f['promotionId'];
$certificat['nb']['services'] = $res_f['nbServices'];
}
// Liste des services enregistrés dans le certificat
$sql = 'SELECT s.id id
FROM servicecertificat sc
INNER JOIN service s ON s.id = sc.idService
INNER JOIN hopital h ON h.id = s.hopital
WHERE sc.idCertificat = ?
ORDER BY h.nom ASC, s.nom ASC';
$res = $db->prepare($sql);
$res->execute(array($id));
while ($res_f = $res->fetch()) {
$certificat['services'][$res_f['id']]['id'] = $res_f['id'];
}
// Liste des étudiants enregistrés dans le certificat
$certificat['etudiants'] = array();
$sql = 'SELECT u.id id
FROM servicecertificat sc
INNER JOIN affectationexterne ae ON ae.service = sc.idService
INNER JOIN user u ON u.id = ae.userId
WHERE sc.idCertificat = :certificat AND ae.dateDebut <= :now AND ae.dateFin >= :now
ORDER BY u.nom ASC, u.prenom ASC';
$res = $db->prepare($sql);
$res->execute(array('certificat' => $id, 'now' => TimestampToDatetime(time())));
while ($res_f = $res->fetch()) {
$certificat['etudiants'][$res_f['id']]['id'] = $res_f['id'];
}
$certificat['nb']['etudiants'] = count($certificat['etudiants']);
return $certificat;
} else {
return false;
}
}
if ($action == 'list') {
if (isset($_GET['order'])) {
$order = $_GET['order'];
} else {
$order = 'nom';
}
if (isset($_GET['desc'])) {
$desc = true;
} else {
$desc = false;
}
$listeCertificats = getCertificatList($order, $desc);
} else {
if ($action == 'view' || $action == 'delete' || $action == 'edit') {
// On récupère les données sur la spécialité
if (count(checkCertificat($_GET['id'], array())) == 0) {
$certificatData = getCertificatInfo($_GET['id']);
} else {
header('Location: ' . ROOT . CURRENT_FILE . '?page=' . $_GET['page']);
}
}
}
/**
2. Traitement des formulaires
**/
if (isset($_POST) && count($_POST)) {
/*
Préparation des données : on crée un array contenant toutes les données, ce dernier sera ensuite parcouru pour créer la requête SQL qui sera préparée
*/
if ($action == 'edit' || $action == 'delete') {
$sqlData['id'] = $certificatData['id'];
On crée le $whereSQL
*/
$whereSQL = '';
if (isset($_POST['filtres'])) {
if (isset($_POST['filtres']['promotion'])) {
foreach ($_POST['filtres']['promotion'] as $promotionId => $promotionValue) {
if (count(checkPromotion($promotionId, array())) == 0) {
$whereSQL .= ' AND (SELECT count(*) FROM user INNER JOIN affectationexterne ON user.id = affectationexterne.userId INNER JOIN service ON service.id = affectationexterne.service WHERE user.promotion = ' . $promotionId . ' AND service.chef = u.id AND affectationexterne.dateDebut <= "' . TimestampToDatetime(time()) . '" AND affectationexterne.dateFin >= "' . TimestampToDatetime(time()) . '" LIMIT 1) = 1 ';
}
}
}
if (isset($_POST['filtres']['certificat']) && count($_POST['filtres']['certificat']) > 0) {
$whereSQL .= ' AND (';
$addOR = FALSE;
foreach ($_POST['filtres']['certificat'] as $certificatId => $certificatValue) {
if (count(checkCertificat($certificatId, array())) == 0) {
if ($addOR) {
$whereSQL .= ' OR ';
} else {
$addOR = TRUE;
}
$whereSQL .= ' (SELECT count(*) FROM service INNER JOIN servicecertificat ON servicecertificat.idService = service.id WHERE service.chef = u.id AND servicecertificat.idCertificat = ' . $certificatId . ' LIMIT 1) = 1';
}
}
$whereSQL .= ')';
}
}
$orderSQL = ' ORDER BY u.nom, u.prenom';
}
}
/*
} else {
$whereSqlFilter .= ' AND ';
}
if ($whereSqlContent == '') {
$whereSqlContent .= 'WHERE ';
} else {
$whereSqlContent .= ' AND ';
}
$whereSqlFilter .= ' e.finStage > :dateMin AND e.debutStage < :dateMax ';
$whereSqlContent .= ' e.finStage > :dateMin AND e.debutStage < :dateMax ';
}
/*
Certificat
*/
if (isset($_GET['FILTER']['certificat']) && is_numeric($_GET['FILTER']['certificat'])) {
$erreur = checkCertificat($_GET['FILTER']['certificat'], $erreur);
if (count($erreur) == 0) {
$preparedValue['certificat'] = $_GET['FILTER']['certificat'];
if ($whereSqlFilter == '') {
$whereSqlFilter .= 'WHERE ';
} else {
$whereSqlFilter .= ' AND ';
}
if ($whereSqlContent == '') {
$whereSqlContent .= 'WHERE ';
} else {
$whereSqlContent .= ' AND ';
}
$whereSqlFilter .= ' (SELECT count(*) FROM servicecertificat WHERE idService = e.service AND idCertificat =:certificat) != 0 ';
$whereSqlContent .= ' (SELECT count(*) FROM servicecertificat WHERE idService = e.service AND idCertificat = :certificat) != 0 ';
}
