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
| create function QGPL/ferie(inpDate date) returns char(15)
language sql
deterministic
Disallow Parallel
no external action
Returns NULL on NULL Input
Begin
Declare A dec(5, 0);
Declare B dec(5, 0);
Declare C dec(5, 0);
Declare P dec(5, 0);
Declare E dec(5, 0);
Declare F dec(5, 0);
Declare G dec(5, 0);
Declare H dec(5, 0);
Declare I dec(5, 0);
Declare K dec(5, 0);
Declare R dec(5, 0);
Declare N dec(5, 0);
Declare Jour dec(2, 0);
Declare Mois dec(2, 0);
Declare An dec(4, 0);
Declare D dec(5, 0);
Declare M dec(5, 0);
Declare Paques date;
Declare Delta_Paques dec(5, 0);
Set Jour = Day(inpDate);
Set Mois = Month(inpDate);
Set An = Year(inpDate);
-- Calcul du dimanche de Pâques
Set A = MOD(An, 19);
Set B = INT(An / 100);
Set C = MOD(An, 100);
Set P = INT(B / 4);
Set E = MOD(B, 4);
Set F = INT((B+8) / 25);
Set G = INT((B-F+1) / 3);
Set H = MOD((19*A)+(B-P-G+15), 30);
Set I = INT(C / 4);
Set K = MOD(C, 4);
Set R = MOD(32+(2*E)+(2*I)-H-K, 7);
Set N = INT((A+11*H+22*R) / 451);
-- M=mois de Pâques
-- D=Jour de Pâques
Set M = INT((H+R-7*N+114) / 31);
Set D = MOD(H+R-7*N+114, 31)+1;
-- On fabrique la date de Dimanche de Pâques
Set Paques = date(right('0' concat trim(char(D)), 2) concat '.'
concat right('0' concat trim(char(M)), 2) concat '.'
concat trim(char(AN)));
-- On Calcule le delta entre la date envoyée et Paques
Set Delta_Paques = DAYS(InpDate) - DAYS(Paques);
-- Renvoie du jour férié
CASE
when (jour=01 and mois=01)
Then Return 'Jour de l''an';
when (jour=01 and mois=05)
Then Return 'Fête du Travail';
when (jour=08 and mois=05)
Then Return 'Armistice 39-45';
when (jour=14 and mois=07)
Then Return 'Fête Nationale';
when (jour=15 and mois=08)
Then Return 'Assomption';
when (jour=01 and mois=11)
Then Return 'Toussaint';
when (jour=11 and mois=11)
Then Return 'Armistice 14-18';
when (jour=25 and mois=12)
Then Return 'Noël';
when (Delta_Paques=1)
Then Return 'Lundi de Pâques';
when (Delta_Paques=39)
Then Return 'Ascension';
when (Delta_Paques=50)
Then Return 'Pentecôte';
else Return '';
END CASE;
END |
Partager