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 111 112 113 114 115 116 117 118 119 120 121 122
| 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
;
-------------------------------------------------------------
create procedure QGPL/Calendrier
language sql modifies sql data
Begin
Declare wcDate Date;
Declare wType char(1);
Declare wlFerie char(15);
Declare jour int;
-- Creation d'une table Calendrier
CREATE TABLE QGPL/CALEND ( CDATE DATE NOT NULL, TYPE CHARACTER (1) NOT NULL, LFERIE CHAR (15) NOT NULL, PRIMARY KEY (CDATE) );
SET wCDate = date('01.01.1990')
;
WHILE wCDate <= Date('31.12.2039') DO
Set wlFerie = Ferie(wcDate);
Set jour = dayofweek_iso(wCdate);
Case
When trim(wlFerie)<>'' Then Set wType = 'F';
When Jour= 6 Then Set wType = 'S';
When Jour = 7 Then Set wType = 'D';
Else Set wType = 'O';
End Case;
INSERT INTO QGPL/CALEND VALUES(wCDate, wType, wlFerie);
SET wCDate = wCDate + 1 Days;
END WHILE;
END |
Partager