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
103
104
105
106
107
108
109
110
| CREATE FUNCTION glpi.`calc_duree`(date_debut DATETIME, date_fin DATETIME, projet BOOLEAN, idgroup INTEGER(11)) RETURNS time
BEGIN
DECLARE borneDebut,borneFin,heureDebut,heureFin,duree TIME;
DECLARE jourDebut,jourFin DATE;
DECLARE dateDebut,dateFin DATETIME;
DECLARE nbJours INTEGER;
DECLARE dureeJournee INTEGER;
set heureDebut = TIME(date_debut);
set heureFin = TIME(date_fin);
set jourDebut = DATE(date_debut);
set jourFin = DATE(date_fin);
set dateDebut = date_debut;
set dateFin = date_fin;
set duree = '00:00:00';
set nbJours = 0;
select TIME(param_temps) into borneDebut from rra_paramcalendar
where param ='DEBUT_JOURNEE' and id_group = idGroup;
select TIME(param_temps) into borneFin from rra_paramcalendar
where param ='FIN_JOURNEE' and id_group = idGroup;
select param_duree into dureeJournee from rra_paramcalendar
where param ='DUREE_JOURNEE' and id_group = idGroup;
# IF (not projet) then
IF (heureDebut < borneDebut) then
# demande faite avant debut journee, ramenee a debut journe
set heureDebut = borneDebut;
if (jourDebut = jourFin) and (heureFin < borneDebut) then
# demande faire avant debut journee et traitee avant debut journee = 1 sec
set heureFin = ADDTIME(borneDebut,'00:00:01');
end if;
elseif (heureDebut > borneFin) then
# demande faite apres fin journee, ramenee a debut journee du jour d'apres
set heureDebut = borneDebut;
if (jourDebut = jourFin and heureFin > borneFin) then
# demande traitee dans une meme journee
# demande faire apres fin journee et traitee avant 00:00 = 1 sec
set heureFin = ADDTIME(borneDebut,'00:00:01') ;
set jourFin = jourFin + INTERVAL 1 DAY;
elseif (heureFin < borneDebut and jourDebut = jourFin - INTERVAL 1 DAY) then
# demande faite apres fin journee et traitee avant debut journee d'apres = 1 sec
set heureFin = ADDTIME(borneDebut,'00:00:01') ;
end if;
set jourDebut = jourDebut + INTERVAL 1 DAY;
end if;
set dateDebut = ADDTIME(jourDebut + interval 0 second,heureDebut);
set dateFin = ADDTIME(jourFin+ interval 0 second,heureFin);
set duree = TIMEDIFF(dateFin,dateDebut) ;
else
# mode projet, les nuits et WE sont enleves de la duree quand projet sur plusieurs jours
if (jourDebut = jourFin) then
# cas d'une demande traitee sur 1journee
if ((heureDebut < borneDebut and heureFin < borneDebut) or
(heureDebut > borneFin)) then
# cas des demandes faites en dehors des plages de travail
set duree = '00:00:01';
ELSE
if (heureDebut < borneDebut) then
set heureDebut = borneDebut;
end if;
if (heureFin > borneFin) then
set heureFin = borneFin;
end if;
set dateDebut = ADDTIME(jourDebut + interval 0 second,heureDebut);
set dateFin = ADDTIME(jourFin + interval 0 second,heureFin);
set duree = TIMEDIFF(dateFin,dateDebut) ;
end if;
else
# cas des demandes sur plusieurs jours. gestion des bornes
if (heureDebut < borneDebut) then
set heureDebut = borneDebut;
ELSEIF (heureDebut > borneFin) then
set heureDebut = borneDebut;
set jourDebut = jourDebut + INTERVAL 1 DAY;
end if;
if (heureFin > borneFin) then
set heureFin = borneFin;
elseif (heureFin < borneDebut) then
set heureFin = borneFin;
set jourFin = jourFin - INTERVAL 1 DAY;
end if;
# calcul du nmbre de jours reels
select count(*) into nbJours
from ( select @rownum:=@rownum+1 as rnum
from (select @rownum:=0) r ,glpi_users
where @rownum <= dateDiff(jourFin,jourDebut)) t
where DAYOFWEEK( jourDebut + interval (rnum-1) day) not in ( 1, 7 )
and not exists (select 1
from rra_paramcalendar
where jourDebut + interval (rnum-1) day = param_temps
and param = 'JOUR_FERIE'); |
