JCZWKfSK

1id | someDatas | deadline1 | deadline2 | deadline3 | deadline4 | deadline5
2---+-----------+-----------+-----------+-----------+-----------+-----------
3   |           |           |           |           |           |
4	
5id | name
6---+-----
7   |
8	
9id | id_demand | someOthersDatas | id_status
10---+-----------+-----------------+----------
11   |           |                 |
12	
13SELECT dem.*, st.`name` as 'statusName'
14FROM `status` st
15INNER JOIN `events` eve
16    ON eve.id_status = st.id
17INNER JOIN `demand` dem
18    ON eve.id_demand = dem.id
19WHERE st.`name` IN ('A', 'B', 'C', 'D')
20    AND eve.id IN
21    (
22        SELECT MAX(even.id) ev
23        FROM `demand` de
24        INNER JOIN `events` even
25            ON even.id_demand = de.id
26        GROUP BY de.id
27    );
28	
29DROP TEMPORARY TABLE IF EXISTS pendingDemands;
30CREATE TEMPORARY TABLE IF NOT EXISTS pendingDemands
31SELECT /* the query shown above */
32	
33delimiter //
34
35CREATE FUNCTION `easter_f`(X INTEGER) RETURNS date
36BEGIN
37    DECLARE K,M,S,A,D,R,OG,SZ,OE,OS INT;
38    DECLARE EASTERDATE DATE;
39    SET K = X DIV 100;
40    SET M = 15 + (3*K + 3) DIV 4 - (8*K + 13) DIV 25;
41    SET S = 2 - (3*K + 3) DIV 4;
42    SET A = X MOD 19;
43    SET D = (19*A + M) MOD 30;
44    SET R = (D + A DIV 11) DIV 29;
45    SET OG = 21 + D - R ;
46    SET SZ = 7 - (X + X DIV 4 + S) MOD 7;
47    SET OE = 7 - (OG - SZ) MOD 7;
48    SET OS = OG + OE;
49    SET EASTERDATE = date_add(concat(X, '-03-01'), INTERVAL OS-1 DAY);
50RETURN EASTERDATE;
51END//
52	
53DROP TEMPORARY TABLE IF EXISTS days_off;
54CREATE TEMPORARY TABLE IF NOT EXISTS days_off
55(
56    date_off VARCHAR(5)
57);
58
59INSERT INTO days_off VALUES('01-01'),
60                           ('05-01'),
61                           ('05-08'),
62                           ('07-14'),
63                           ('08-15'),
64                           ('11-01'),
65                           ('11-11'),
66                           ('12-25');
67	
68delimiter //
69DROP FUNCTION IF EXISTS `isDayOff`;
70CREATE FUNCTION `isDayOff`(d DATETIME) RETURNS BOOL
71BEGIN
72    SET @monthDay = (SELECT DATE_FORMAT(d, '%m-%d'));
73    SET @easter = (SELECT easter_f(YEAR(d)));
74    SET @dayWeek = (SELECT DAYOFWEEK(d));
75    SET @result = if (@dayWeek = 1
76                        OR @dayWeek = 7
77                        OR (SELECT COUNT(*) FROM days_off WHERE date_off = @monthDay) > 0
78                        OR DATE_ADD(@easter, INTERVAL 1 DAY) = d
79                        OR DATE_ADD(@easter, INTERVAL 40 DAY) = d
80                        OR DATE_ADD(@easter, INTERVAL 50 DAY) = d,
81                    TRUE,
82                    FALSE);
83    RETURN (@result);
84END//
85	
86DELIMITER //
87DROP FUNCTION IF EXISTS `get_next_valid_date`;
88CREATE FUNCTION `get_next_valid_date`(MyDate DATETIME) RETURNS DATETIME
89BEGIN
90    REPEAT
91        SET MyDate = (DATE_ADD(MyDate, INTERVAL 1 DAY));
92        SET @someCondition = (select isDayOff(MyDate));
93    UNTIL (@someCondition = 0) END REPEAT;
94    RETURN MyDate;
95END//