1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
| BEGIN
DECLARE Fin DATE;
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE p_trig VARCHAR(3);
DECLARE p_current_trig VARCHAR(3) DEFAULT '';
DECLARE p_start DATE;
DECLARE p_end DATE;
DECLARE p_nom VARCHAR(12);
DECLARE joursConges INT DEFAULT 0;
DECLARE joursRepos INT DEFAULT 0;
DECLARE joursMaladie INT DEFAULT 0;
DECLARE dateCourante DATE;
DECLARE jour INT;
DECLARE curseur_events CURSOR FOR SELECT trig, start_date, end_date, nom FROM eventsFiltered ORDER BY groupe, hierarchie, nom, start_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
IF Periode = 'mois' THEN
SET Fin = DATE_ADD(Debut, INTERVAL 1 MONTH);
END IF;
IF Periode = 'trimestre' THEN
SET Fin = DATE_ADD(Debut, INTERVAL 3 MONTH);
END IF;
IF Periode = 'semestre' THEN
SET Fin = DATE_ADD(Debut, INTERVAL 6 MONTH);
END IF;
IF Periode = 'annee' THEN
SET Fin = DATE_ADD(Debut, INTERVAL 1 YEAR);
END IF;
SET Fin = DATE_SUB(Fin, INTERVAL 1 DAY);
DROP TEMPORARY TABLE IF EXISTS conge_repos_maladie;
CREATE TEMPORARY TABLE conge_repos_maladie (
trig VARCHAR(3), conges INT, repos INT, maladie INT
);
DROP TEMPORARY TABLE IF EXISTS eventsFiltered;
CREATE TEMPORARY TABLE eventsFiltered AS
SELECT e.pk, e.trig, e.groupe, e.hierarchie,
(CASE WHEN e.start_date < Debut THEN Debut ELSE e.start_date END) AS start_date,
(CASE WHEN e.end_date > Fin THEN Fin ELSE e.end_date END) AS end_date,
(CASE WHEN e.event_type = 2 OR e.event_type = 3 THEN 'Congés' ELSE et.nom END) AS nom,
(CASE WHEN e.end_date > Fin THEN DATEDIFF(Fin, e.start_date) + 1 ELSE e.duration END) AS duration
FROM eventsByNav e LEFT JOIN events_type et ON e.event_type = et.pk WHERE (e.start_date BETWEEN Debut AND Fin OR e.end_date BETWEEN Debut AND Fin) AND event_type IN (1,2,3,4) AND e.groupe IN (1,4);
SELECT trig INTO p_current_trig FROM eventsFiltered ORDER BY groupe, hierarchie LIMIT 1; -- récupération premier navigant
OPEN curseur_events;
loop_events: LOOP
FETCH curseur_events INTO p_trig, p_start, p_end, p_nom;
IF done THEN -- sauvegarde dernier enregistrement et sortie de boucle
INSERT INTO conge_repos_maladie (trig, conges, repos, maladie) VALUES (p_current_trig, joursConges, joursRepos, joursMaladie);
LEAVE loop_events;
END IF;
IF p_trig != p_current_trig THEN -- changement de navigant
INSERT INTO conge_repos_maladie (trig, conges, repos, maladie) VALUES (p_current_trig, joursConges, joursRepos, joursMaladie);
SET joursConges = 0;
SET joursRepos = 0;
SET joursMaladie = 0;
SET p_current_trig = p_trig;
SET dateCourante = p_start;
boucle1: WHILE dateCourante <= p_end DO -- balayage de la période pour supprimer les WE
SET jour = DAYOFWEEK(dateCourante);
IF NOT (jour = 1 OR jour = 7) THEN
CASE
WHEN p_nom = 'Congés' THEN
SET joursConges = joursConges + 1;
WHEN p_nom = 'Récupération' THEN
SET joursRepos = joursRepos + 1;
WHEN p_nom = 'Maladie' THEN
SET joursMaladie = joursMaladie + 1;
END CASE;
END IF;
SET dateCourante = ADDDATE(dateCourante, 1);
END WHILE boucle1;
ELSE -- toujours le même navigant
SET dateCourante = p_start;
boucle2: WHILE dateCourante <= p_end DO
SET jour = DAYOFWEEK(dateCourante);
IF NOT (jour = 1 OR jour = 7) THEN
CASE
WHEN p_nom = 'Congés' THEN
SET joursConges = joursConges + 1;
WHEN p_nom = 'Récupération' THEN
SET joursRepos = joursRepos + 1;
WHEN p_nom = 'Maladie' THEN
SET joursMaladie = joursMaladie + 1;
END CASE;
END IF;
SET dateCourante = ADDDATE(dateCourante, 1);
END WHILE boucle2;
END IF;
END LOOP loop_events;
CLOSE curseur_events;
SELECT * FROM conge_repos_maladie;
END |
Partager