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
| CREATE PROCEDURE maProc()
BEGIN
DECLARE Date_creation DATE;
DECLARE Date_resolution DATE;
DECLARE x INT;
DECLARE done INT DEFAULT 0;
DECLARE BUG CURSOR FOR SELECT id FROM mantis_bug_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
CREATE TABLE MaTable (ID_Anomalie INT(10), ID_DATE DATE);
OPEN BUG;
REPEAT
FETCH BUG INTO x;
IF NOT done THEN
SELECT STR_TO_DATE(FROM_UNIXTIME(date_submitted, '%d/%m/%Y'), '%d/%m/%Y') INTO Date_creation
FROM mantis_bug_table
WHERE id = x;
SELECT STR_TO_DATE(FROM_UNIXTIME(min(date_modified), '%d/%m/%Y'), '%d/%m/%Y') INTO Date_resolution
FROM mantis_bug_history_table
WHERE bug_id = x AND field_name='status' AND new_value='80'
GROUP BY bug_id;
/* Si l'anomalie n'a pas été résolue, elle est donc encore ouverte aujourd'hui */
IF (Date_resolution IS NULL) THEN
SELECT STR_TO_DATE(DATE_FORMAT(CURDATE(), '%d/%m/%Y'), '%d/%m/%Y') INTO Date_resolution FROM DUAL;
END IF;
CALL maProc2(x, Date_creation, Date_resolution);
END IF;
UNTIL done END REPEAT;
CLOSE BUG;
END//
CREATE PROCEDURE maProc2(ID INT, Date_creation DATE, Date_resolution DATE)
BEGIN
DECLARE x DATE;
DECLARE done2 INT DEFAULT 0;
DECLARE JOUR CURSOR FOR SELECT DISTINCT STR_TO_DATE(ID_DATE, '%d/%m/%Y') FROM CALENDRIER WHERE STR_TO_DATE(ID_DATE, '%d/%m/%Y') >= Date_creation AND STR_TO_DATE(ID_DATE, '%d/%m/%Y') <= Date_resolution;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
OPEN JOUR;
REPEAT
FETCH JOUR INTO x;
IF NOT done2 THEN
INSERT INTO MaTable VALUES (ID, x);
END IF;
UNTIL done2 END REPEAT;
CLOSE JOUR;
END// |
Partager