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
| DELIMITER |
CREATE PROCEDURE JourFerie()
BEGIN
DECLARE an INT;
DECLARE G INT;
DECLARE C INT;
DECLARE C_4 INT;
DECLARE E INT;
DECLARE H INT;
DECLARE K INT;
DECLARE P INT;
DECLARE Q INT;
DECLARE I INT;
DECLARE B INT;
DECLARE J1 INT;
DECLARE J2 INT;
DECLARE R INT;
DECLARE DimPaque DATETIME;
DECLARE LunPaque DATETIME;
DECLARE JeuAscension DATETIME;
DECLARE LunPentecote DATETIME;
DECLARE NouvelAn DATETIME;
DECLARE FeteTravail DATETIME;
DECLARE Armistice3945 DATETIME;
DECLARE Assomption DATETIME;
DECLARE Armistice1418 DATETIME;
DECLARE FeteNationale DATETIME;
DECLARE ToussaINT DATETIME;
DECLARE Noel DATETIME;
SET an = YEAR(CURDATE());
SET G = an % 19;
SET C = an / 100;
SET C_4 = C / 4;
SET E = (8 * C + 13)/25;
SET H = (19 * G + C - C_4 - E + 15) % 30;
SET K = H / 28;
SET P = 29 / (H + 1);
SET Q = (21 - G)/11;
SET I = (K * P * Q - 1) * K + H;
SET B = (an / 4) + an;
SET J1 = (B + I + 2 + C_4) - C;
SET J2 = J1 % 7;
SET R = 28 + I - J2;
IF(R > 31) THEN
SET DimPaque = CAST(CONCAT(CAST(an AS CHAR) , '-04-' , cast((R-31) AS CHAR)) AS DATETIME);
ELSE
SET DimPaque = CAST(CONCAT(CAST(an AS CHAR) , '-03-' , cast(R AS CHAR)) AS DATETIME);
END IF;
-- Jours fériés mobiles
SET LunPaque = ADDDATE(DimPaque,INTERVAL 1 DAY);
SET JeuAscension = ADDDATE(DimPaque,INTERVAL 39 DAY);
SET LunPentecote = ADDDATE(DimPaque,INTERVAL 50 DAY);
-- Jours fériés fixes
SET NouvelAn = cast(CONCAT(cast(an AS CHAR),'-01-01 00:00:00') AS DATETIME);
SET FeteTravail = cast(CONCAT(cast(an AS CHAR),'-05-01 00:00:00') AS DATETIME);
SET Armistice3945 = cast(CONCAT(cast(an AS CHAR),'-05-08 00:00:00') AS DATETIME);
SET Assomption = cast(CONCAT(cast(an AS CHAR),'-08-15 00:00:00') AS DATETIME);
SET Armistice1418 = cast(CONCAT(cast(an AS CHAR),'-11-11 00:00:00') AS DATETIME);
SET FeteNationale = cast(CONCAT(cast(an AS CHAR),'-07-14 00:00:00') AS DATETIME);
SET ToussaINT = cast(CONCAT(cast(an AS CHAR),'-11-01 00:00:00') AS DATETIME);
SET Noel = cast(CONCAT(cast(an AS CHAR),'-12-25 00:00:00') AS DATETIME);
INSERT INTO JoursFeries (JourDate, JoURLabel, JourChome)
SELECT DimPaque, 'Dimanche de Pâques', 0
UNION
SELECT LunPaque, 'Lundi de Pâques', 0
UNION
SELECT JeuAscension, 'Jeudi de l''Ascension', 1
UNION
SELECT LunPentecote, 'Lundi de Pentecôte', 1
UNION
SELECT NouvelAn, 'Nouvel an', 1
UNION
SELECT FeteTravail, 'Fête du travail', 1
UNION
SELECT Armistice3945, 'Armistice 39-45', 1
UNION
SELECT Assomption, 'Assomption', 1
UNION
SELECT FeteNationale, 'Fête Nationale', 1
UNION
SELECT ToussaINT, 'Toussaint', 1
UNION
SELECT Armistice1418, 'Armistice 14-18', 1
UNION
SELECT Noel, 'Noël', 0;
END|
DELIMITER ; |
