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 |
