· 6 years ago · Jun 11, 2019, 01:18 PM
1CREATE PROCEDURE MSP_ARCHIVE_FILES(my_archive_note text, my_date_fin TEXT)
2DELIMITER //
3BEGIN
4-- RécupÚre le script d'archivage des piÚces jointes + met une note avec le nouveau nom des fichiers et leurs emplacements
5-- Exemple utilisation pour archiver les piÚces jointes des mantis avant le 01/01/2013
6-- CALL MSP_ARCHIVE_FILES("File move to ADSoftware's archive", "2014-01-01")
7DECLARE my_bug_id INTEGER;
8DECLARE my_bug_Note TEXT;
9DECLARE my_date TEXT DEFAULT REPLACE(date(now()), '-', '/');
10DECLARE done INTEGER DEFAULT FALSE;
11
12DECLARE curFiles CURSOR FOR
13
14SELECT mantis_bug_file_table.bug_id, concat(my_archive_note, " - ", my_date, '
15', GROUP_CONCAT(concat('- ''' , filename , ''' TO ''O:\\MantisAttachment\\_archives\\', '#', bug_id, '_', RIGHT(diskfile, INSTR(REVERSE(diskfile), '/') - 1), RIGHT(filename, INSTR(REVERSE(filename), '.')), '''') SEPARATOR '
16')) as bug_Note
17FROM mantis_bug_file_table
18inner join mantis_bug_table
19 on mantis_bug_file_table.bug_id = mantis_bug_table.id
20where mantis_bug_table.status > 80 -- Mantis fermés
21and mantis_bug_file_table.description = '' -- on n'a pas déjà mis un commentaire
22-- and mantis_bug_table.id = 2526
23and mantis_bug_table.date_submitted < unix_timestamp(my_date_fin)
24GROUP by mantis_bug_file_table.bug_id;
25
26 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
27
28 OPEN curFiles;
29
30 read_loop: LOOP
31 FETCH curFiles INTO my_bug_id, my_bug_Note;
32 IF done THEN
33 LEAVE read_loop;
34 END IF;
35
36 update mantis_bug_file_table
37 set description = concat(my_archive_note, " - ", my_date)
38 , filename = concat('// ADS_Archive // ', filename)
39 where mantis_bug_file_table.bug_id = my_bug_id;
40
41 CALL USP_BUGNOTE_ADD(my_bug_id, my_bug_Note, 1);
42 END LOOP;
43
44 CLOSE curFiles;
45
46 -- on retourne un jeu de résultat
47 SELECT concat('ren ', RIGHT(diskfile, INSTR(REVERSE(diskfile), '/') - 1), ' #', bug_id, '_', RIGHT(diskfile, INSTR(REVERSE(diskfile), '/') - 1), RIGHT(filename, INSTR(REVERSE(filename), '.'))), mantis_bug_file_table.*
48 FROM mantis_bug_file_table
49 where description like concat('%', my_date, '%');
50
51END//
52DROP TRIGGER IF EXISTS TG_ADD_BUG_TRACKER;
53
54delimiter //
55CREATE TRIGGER TG_ADD_BUG_TRACKER
56AFTER UPDATE ON mantis_bug_table
57FOR EACH ROW
58/*
59 #9773 : On ajoute 'ataiarui' comme suiveur de tous les mantis ATN, dÚs la modification
60*/
61BEGIN
62 CREATE TEMPORARY TABLE IF NOT EXISTS TempTableSurveillance AS
63 (
64 SELECT mantis_user_table.ID as user_ID, mantis_project_table.ID as PROJECT_ID
65 FROM (SELECT 'ataiarui' as username
66 , 'AIRPACK_ATN' as project
67 UNION
68 SELECT 'XXX' as username
69 , 'AIRPACK_XXX' as project) tmpList
70 INNER JOIN mantis_user_table
71 ON cast(mantis_user_table.username AS CHAR(50)) = tmpList.username
72 AND mantis_user_table.enabled = 1
73 INNER JOIN mantis_project_table
74 ON cast(mantis_project_table.name AS CHAR(50)) = tmpList.project
75 AND mantis_project_table.enabled = 1
76 );
77
78 INSERT INTO mantis_bug_monitor_table (user_id, bug_id)
79 select TempTableSurveillance.user_id, mantis_bug_table.ID
80 from mantis_bug_table
81 INNER JOIN TempTableSurveillance
82 ON TempTableSurveillance.project_id = mantis_bug_table.project_id
83 LEFT JOIN mantis_bug_monitor_table
84 ON mantis_bug_monitor_table.bug_id = mantis_bug_table.id
85 AND mantis_bug_monitor_table.user_id = TempTableSurveillance.user_id
86 where mantis_bug_table.id = NEW.ID
87 AND mantis_bug_table.status < 80 -- Résolu livré
88 AND mantis_bug_table.view_state = 10 -- public
89 AND mantis_bug_monitor_table.user_id IS NULL;
90
91END;//
92delimiter ;-- Si _close = 1 on ferme les mantis qui ont le tag To_Close
93-- Si _close = 0 on affiche combien de mantis ont le tag To_Close
94DELIMITER //
95
96drop FUNCTION if exists UFN_MANTIS_PRIORITY //
97
98CREATE FUNCTION UFN_MANTIS_PRIORITY(_Mantis_id INT)
99RETURNS varchar(255) DETERMINISTIC
100BEGIN
101declare textresult varchar(255);
102
103 SELECT CASE mantis_bug_table.severity WHEN 10 THEN 'Aucune'
104 WHEN 20 THEN 'Basse'
105 WHEN 30 THEN 'Normale'
106 WHEN 40 THEN 'Elevée'
107 WHEN 50 THEN 'Urgente'
108 WHEN 60 THEN 'Immédiate'
109 END INTO textresult
110 FROM mantis_bug_table
111 WHERE mantis_bug_table.ID = _Mantis_id;
112
113 RETURN textresult;
114
115END //
116
117delimiter ;-- Si _close = 1 on ferme les mantis qui ont le tag To_Close
118-- Si _close = 0 on affiche combien de mantis ont le tag To_Close
119DELIMITER //
120
121drop FUNCTION if exists UFN_MANTIS_SEVERITY //
122
123CREATE FUNCTION UFN_MANTIS_SEVERITY(_Mantis_id INT)
124RETURNS varchar(255) DETERMINISTIC
125BEGIN
126declare textresult varchar(255);
127
128 SELECT CASE mantis_bug_table.severity WHEN 10 then 'Integration'
129 WHEN 15 THEN 'Regularisation'
130 WHEN 20 THEN 'Amélioration'
131 WHEN 40 THEN 'Cosmétique'
132 WHEN 60 THEN 'Anomalie'
133 WHEN 70 THEN 'Installation'
134 END INTO textresult
135 FROM mantis_bug_table
136 WHERE mantis_bug_table.ID = _Mantis_id;
137
138 RETURN textresult;
139
140END //
141
142delimiter ;-- Si _close = 1 on ferme les mantis qui ont le tag To_Close
143-- Si _close = 0 on affiche combien de mantis ont le tag To_Close
144DELIMITER //
145
146drop FUNCTION if exists UFN_MANTIS_STATUS //
147
148CREATE FUNCTION UFN_MANTIS_STATUS(_Mantis_id INT)
149RETURNS varchar(255) DETERMINISTIC
150BEGIN
151declare textresult varchar(255);
152
153 SELECT CASE mantis_bug_table.status WHEN 10 then 'new'
154 WHEN 20 THEN 'Commentaire'
155 WHEN 30 THEN 'Résolu en attente'
156 WHEN 40 THEN 'Specifications'
157 WHEN 50 THEN 'Affecté'
158 WHEN 52 THEN 'A tester'
159 WHEN 54 THEN 'A livrer'
160 WHEN 80 THEN 'Livré'
161 WHEN 90 THEN 'Fermé'
162 END INTO textresult
163 FROM mantis_bug_table
164 WHERE mantis_bug_table.ID = _Mantis_id;
165
166 RETURN textresult;
167
168END //
169
170delimiter ;DELIMITER //
171
172drop procedure if exists USP_ADD_CATEGORIE//
173
174CREATE PROCEDURE USP_ADD_CATEGORIE (IN _NAME VARCHAR(50))
175BEGIN
176
177INSERT INTO mantis_category_table VALUES (null, 63, 0, _NAME, 0);
178
179END //
180
181delimiter ;-- Si _close = 1 on ferme les mantis qui ont le tag To_Close
182-- Si _close = 0 on affiche combien de mantis ont le tag To_Close
183DELIMITER //
184
185drop procedure if exists USP_BUGNOTE_ADD //
186
187CREATE PROCEDURE USP_BUGNOTE_ADD(IN _Mantis_id INT, IN _Bug_Note text, _Is_Private bit)
188
189BEGIN
190
191 IF EXISTS(SELECT * FROM mantis_bug_table WHERE ID = _Mantis_id) THEN
192 INSERT INTO mantis_bugnote_text_table(note)
193 SELECT _Bug_Note;
194
195 INSERT INTO mantis_bugnote_table(bug_id, reporter_id, bugnote_text_id, view_state, note_type, note_attr, time_tracking, last_modified, date_submitted)
196 SELECT _Mantis_id, 1, LAST_INSERT_ID(), CASE WHEN _Is_Private THEN 50 ELSE 10 END, 0, '', 0, UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW());
197 END IF;
198END //
199
200delimiter ;DELIMITER //
201
202drop procedure if exists USP_CLOSE_MANTIS_ROW//
203
204CREATE PROCEDURE USP_CLOSE_MANTIS_ROW(IN _ID INT)
205BEGIN
206
207UPDATE mantis_bug_table
208set status = 90
209where id = _ID;
210
211
212END //
213
214delimiter ;DELIMITER //
215
216drop procedure if exists USP_CREATE_MANTIS//
217
218CREATE PROCEDURE USP_CREATE_MANTIS(IN _reporter_id INT, IN _project_id INT, IN _date INT, IN _id_mantis INT, IN _errors TEXT)
219BEGIN
220
221DECLARE bug_id INT;
222DECLARE resume VARCHAR(128);
223DECLARE _description VARCHAR(200);
224
225SELECT summary into resume from mantis_bug_table where id = _id_mantis;
226SELECT description into _description
227from mantis_bug_table
228INNER JOIN mantis_bug_text_table
229ON mantis_bug_table.bug_text_id = mantis_bug_text_table.id
230where mantis_bug_table.id = _id_mantis;
231
232IF _id_mantis = 0 THEN
233set resume = "IT_RESULT";
234set _description = "IT_RESULT";
235END IF;
236
237INSERT INTO mantis_bug_table VALUES (null, _project_id, _reporter_id, _reporter_id, 0, 30, 60, 10, 10, 10, 10, 10, 0, "", "", "", "", "", "", 0, 50, resume, 0, 0, "", 1, _date, 1, _date);
238SELECT LAST_INSERT_ID() INTO bug_id;
239INSERT INTO mantis_bug_text_table VALUES (null, _description, "", _errors);
240UPDATE mantis_bug_table
241SET bug_text_id = LAST_INSERT_ID()
242WHERE id = bug_id;
243IF _id_mantis > 0 THEN
244INSERT INTO mantis_bug_relationship_table VALUES (null, bug_id, _id_mantis, 1);
245END IF;
246
247SELECT bug_id;
248
249END //
250
251delimiter ;DELIMITER //
252drop procedure if exists USP_DASHBOARD_N3//
253CREATE PROCEDURE USP_DASHBOARD_N3(IN _Date_Week DATETIME)
254BEGIN
255
256CREATE TEMPORARY TABLE IF NOT EXISTS ListeSemaine AS
257(
258SELECT DISTINCT tt.id as bug_id
259 ,YEAR(from_unixtime(ads_date.date_submitted, '%Y-%m-%d')) as Year
260 , WEEK(from_unixtime(ads_date.date_submitted, '%Y-%m-%d'), 1) as week
261 , YEAR(from_unixtime(ads_date.date_submitted, '%Y-%m-%d')) * 100 + WEEK(from_unixtime(ads_date.date_submitted, '%Y-%m-%d'), 1) + 1 as fullNextWeek
262FROM ads_date
263CROSS JOIN (SELECT ID FROM mantis_bug_table WHERE mantis_bug_table.last_updated > UNIX_TIMESTAMP(_Date_Week)) tt
264 on 1 = 1
265WHERE ads_date.date_submitted BETWEEN UNIX_TIMESTAMP(_Date_Week) AND UNIX_TIMESTAMP(NOW())
266);
267
268CREATE TEMPORARY TABLE IF NOT EXISTS ListeTousCeuxPassesN3 AS
269(
270 SELECT BUG_ID
271 , date_modified as date_New_N3
272 FROM mantis_bug_history_table
273 WHERE mantis_bug_history_table.FIELD_NAME = 'Support N3'
274 AND mantis_bug_history_table.NEW_VALUE = 'Oui'
275);
276CREATE TEMPORARY TABLE IF NOT EXISTS TmpN3 AS
277(
278 SELECT *
279 FROM ListeTousCeuxPassesN3
280);
281
282INSERT INTO ListeTousCeuxPassesN3
283SELECT mantis_bug_table.id
284 , mantis_bug_table.date_submitted
285FROM mantis_custom_field_string_table
286INNER JOIN mantis_bug_table
287 ON mantis_bug_table.id = mantis_custom_field_string_table.bug_id
288LEFT JOIN TmpN3
289 ON TmpN3.Bug_Id = mantis_custom_field_string_table.bug_id
290WHERE mantis_custom_field_string_table.field_id = 18
291AND mantis_custom_field_string_table.value = 'Oui'
292AND TmpN3.bug_id is null;
293
294CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugN3 AS
295(
296SELECT BUG_ID, date_New_N3, DateCloture, DateSortieN3
297FROM (
298 SELECT *
299 , (
300 SELECT NEW_VALUE
301 FROM mantis_bug_history_table
302 WHERE mantis_bug_history_table.BUG_ID = ListN3NEW.BUG_ID
303 AND mantis_bug_history_table.date_modified <= ListN3NEW.date_New_N3
304 AND FIELD_NAME = 'status'
305 ORDER BY mantis_bug_history_table.date_modified DESC, mantis_bug_history_table.id DESC
306 LIMIT 1
307 ) as statusBefore
308 , (
309 SELECT date_modified
310 FROM mantis_bug_history_table
311 WHERE mantis_bug_history_table.BUG_ID = ListN3NEW.BUG_ID
312 AND mantis_bug_history_table.date_modified >= ListN3NEW.date_New_N3
313 AND FIELD_NAME = 'status'
314 AND old_value < 54 and new_value >= 54
315 ORDER BY mantis_bug_history_table.date_modified, mantis_bug_history_table.id
316 LIMIT 1
317 ) as DateCloture
318 , (
319 SELECT date_modified
320 FROM mantis_bug_history_table
321 WHERE mantis_bug_history_table.BUG_ID = ListN3NEW.BUG_ID
322 AND mantis_bug_history_table.date_modified BETWEEN ListN3NEW.date_New_N3 AND UNIX_TIMESTAMP(NOW())
323 AND FIELD_NAME = 'Support N3'
324 AND NEW_VALUE = 'Non'
325 ORDER BY mantis_bug_history_table.date_modified DESC, mantis_bug_history_table.id DESC
326 LIMIT 1
327 ) as DateSortieN3
328 FROM (
329 SELECT ListeTousCeuxPassesN3.BUG_ID, MIN(ListeTousCeuxPassesN3.date_New_N3) AS date_New_N3
330 FROM ListeTousCeuxPassesN3
331 GROUP BY ListeTousCeuxPassesN3.BUG_ID) ListN3NEW
332 ) listN3
333WHERE IFNULL(listN3.statusBefore, 0) < 80
334AND DateSortieN3 IS NULL
335);
336
337update ListeBugN3
338Set DateCloture = DateSortieN3
339where DateCloture > DateSortieN3;
340
341update ListeBugN3, mantis_bug_table
342Set DateCloture = NULL
343WHERE mantis_bug_table.id = ListeBugN3.bug_id
344AND mantis_bug_table.status < 54;
345
346CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugN3New AS
347(
348 SELECT YEAR(from_unixtime(date_New_N3, '%Y-%m-%d')) as Year
349 , WEEK(from_unixtime(date_New_N3, '%Y-%m-%d'), 1) as week
350 , YEAR(from_unixtime(date_New_N3, '%Y-%m-%d')) * 100 + WEEK(from_unixtime(date_New_N3, '%Y-%m-%d'), 1) as Fullweek
351 , COUNT(*) as N3New
352 FROM ListeBugN3
353 GROUP BY YEAR(from_unixtime(date_New_N3, '%Y-%m-%d'))
354 , WEEK(from_unixtime(date_New_N3, '%Y-%m-%d'), 1)
355);
356CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugN3Cloture AS
357(
358 SELECT YEAR(from_unixtime(DateCloture, '%Y-%m-%d')) as Year
359 , WEEK(from_unixtime(DateCloture, '%Y-%m-%d'), 1) as week
360 , YEAR(from_unixtime(DateCloture, '%Y-%m-%d')) * 100 + WEEK(from_unixtime(DateCloture, '%Y-%m-%d'), 1) as Fullweek
361 , COUNT(*) as N3Cloture
362 FROM ListeBugN3
363 GROUP BY YEAR(from_unixtime(DateCloture, '%Y-%m-%d'))
364 , WEEK(from_unixtime(DateCloture, '%Y-%m-%d'), 1)
365);
366
367CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugN3Restant AS
368(
369 SELECT fullNextWeek
370 , ((SELECT sum(ListeBugN3New.N3New) FROM ListeBugN3New WHERE ListeBugN3New.Fullweek < tmpSemaine.fullNextWeek ) - (SELECT sum(ListeBugN3Cloture.N3Cloture) FROM ListeBugN3Cloture WHERE ListeBugN3Cloture.Fullweek < tmpSemaine.fullNextWeek)) as N3Restant
371 FROM (SELECT distinct fullNextWeek FROM ListeSemaine) tmpSemaine
372);
373
374
375CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugStatus AS
376(
377SELECT ListeSemaine.bug_id
378 , ListeSemaine.Year
379 , ListeSemaine.week
380 , (SELECT NEW_VALUE
381 FROM mantis_bug_history_table
382 WHERE mantis_bug_history_table.BUG_ID = ListeSemaine.bug_id
383 AND YEAR(from_unixtime(mantis_bug_history_table.date_modified, '%Y-%m-%d')) * 100 + WEEK(from_unixtime(mantis_bug_history_table.date_modified, '%Y-%m-%d'), 1) < ListeSemaine.fullNextWeek
384 AND FIELD_NAME = 'HANDLER_ID'
385 ORDER BY mantis_bug_history_table.date_modified DESC, mantis_bug_history_table.id DESC
386 LIMIT 1
387 ) as handler_id
388 , (SELECT NEW_VALUE
389 FROM mantis_bug_history_table
390 WHERE mantis_bug_history_table.BUG_ID = ListeSemaine.bug_id
391 AND YEAR(from_unixtime(mantis_bug_history_table.date_modified, '%Y-%m-%d')) * 100 + WEEK(from_unixtime(mantis_bug_history_table.date_modified, '%Y-%m-%d'), 1) < ListeSemaine.fullNextWeek
392 AND FIELD_NAME = 'status'
393 ORDER BY mantis_bug_history_table.date_modified DESC, mantis_bug_history_table.id DESC
394 LIMIT 1
395 ) as status
396FROM ListeSemaine
397);
398SELECT tmpSupport.Year
399 , tmpSupport.Week
400 , ListeBugN3New.N3New
401 , ListeBugN3Cloture.N3Cloture
402 , ListeBugN3Restant.N3Restant
403 , tmpSupport.Assigne as SupportAssigne
404 , tmpSupport.Commentaire as SupportCommentaire
405FROM (SELECT
406 ListeBugStatus.Year
407 , ListeBugStatus.week
408 , count(if(ListeBugStatus.status = 20, 1, NULL)) as Commentaire
409 , count(if(ListeBugStatus.status = 20, NULL, 1)) as Assigne
410 , count(*) as total
411 FROM ListeBugStatus
412 INNER JOIN mantis_bug_table
413 ON mantis_bug_table.ID = ListeBugStatus.Bug_ID
414 WHERE ListeBugStatus.handler_id in (9)
415 AND ListeBugStatus.status < 54
416 GROUP BY ListeBugStatus.Year
417 , ListeBugStatus.week
418 order by ListeBugStatus.Year
419 , ListeBugStatus.week DESC
420 ) tmpSupport
421LEFT JOIN ListeBugN3Restant
422 on ListeBugN3Restant.fullNextWeek = tmpSupport.Year * 100 + tmpSupport.Week + 1
423LEFT JOIN ListeBugN3New
424 ON ListeBugN3New.Year = tmpSupport.Year
425 AND ListeBugN3New.week = tmpSupport.week
426LEFT JOIN ListeBugN3Cloture
427 ON ListeBugN3Cloture.Year = tmpSupport.Year
428 AND ListeBugN3Cloture.week = tmpSupport.week;
429
430drop table ListeSemaine;
431drop table ListeTousCeuxPassesN3;
432drop table TmpN3;
433drop table ListeBugN3;
434drop table ListeBugN3New;
435drop table ListeBugN3Cloture;
436drop table ListeBugN3Restant;
437drop table ListeBugStatus;
438END //
439
440delimiter ;DELIMITER //
441
442drop procedure if exists USP_DATETRACKING//
443
444CREATE PROCEDURE USP_DATETRACKING(IN _Date_Start DATETIME)
445BEGIN
446
447SELECT mantis_project_table.name as Project,
448 mantis_user_table.username,
449 mantis_category_table.name as Category,
450 tblBugTot.nb as nb_distinct_mantis,
451 year(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d')) as year_modified,
452 weekofyear(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d')) as week_modified,
453 count(distinct mantis_bug_table.ID) as nb_distinct_mantis_Week,
454 SUM(mantis_bugnote_table.time_tracking) as Time_Total,
455 SUM(1) as nb_note_total,
456 SUM(case when mantis_bugnote_table.time_tracking > 0 THEN 1 ELSE 0 END) as nb_note_with_Time_Traking
457FROM `mantis_bugnote_table`
458INNER JOIN mantis_bug_table
459 ON mantis_bug_table.ID = mantis_bugnote_table.bug_ID
460INNER JOIN mantis_project_table
461 ON mantis_project_table.ID = mantis_bug_table.project_id
462INNER JOIN mantis_category_table
463 ON mantis_category_table.ID = mantis_bug_table.category_id
464INNER JOIN mantis_user_table
465 ON mantis_user_table.ID = mantis_bugnote_table.REPORTER_ID
466 AND mantis_user_table.EMAIL LIKE '%@adsoftware.fr'
467INNER JOIN (SELECT mantis_bugnote_table.REPORTER_ID, count(distinct mantis_bug_table.id) as nb
468 FROM `mantis_bugnote_table`
469 INNER JOIN mantis_bug_table
470 ON mantis_bug_table.ID = mantis_bugnote_table.bug_ID
471 where mantis_bugnote_table.date_submitted >= UNIX_TIMESTAMP(_Date_Start)
472 GROUP BY mantis_bugnote_table.REPORTER_ID) tblBugTot
473 ON tblBugTot.REPORTER_ID = mantis_bugnote_table.REPORTER_ID
474
475where mantis_bugnote_table.date_submitted >= UNIX_TIMESTAMP(_Date_Start)
476GROUP BY mantis_project_table.name, mantis_user_table.username, tblBugTot.nb, mantis_category_table.name, year(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d')), weekofyear(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d'))
477ORDER BY mantis_project_table.name, mantis_user_table.username, mantis_category_table.name, year(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d')), weekofyear(from_unixtime(mantis_bugnote_table.date_submitted, '%Y/%m/%d'));
478
479END //
480delimiter ;DELIMITER //
481
482drop procedure if exists USP_DISABLE_PROJECT//
483
484CREATE PROCEDURE USP_DISABLE_PROJECT (IN _NAME VARCHAR(30))
485BEGIN
486
487UPDATE mantis_project_table
488SET enabled = 0
489where name = _NAME;
490
491
492END //
493
494delimiter ;DELIMITER //
495
496drop procedure if exists USP_EMPTY_VERSION//
497
498CREATE PROCEDURE USP_EMPTY_VERSION()
499BEGIN
500
501SELECT mantis_bug_table.ID, mantis_project_table.name as project, mantis_user_table.username, fixed_in_version, mantis_config_table.user_id,
502 from_unixtime(mantis_bug_table.date_submitted,'%Y/%m/%d') as DateSubmitted, mantis_bug_table.summary, CASE mantis_bug_table.status WHEN 10 then 'new'
503WHEN 20 THEN 'Commentaire'
504WHEN 30 THEN 'Résolu en attente'
505WHEN 40 THEN 'Specifications'
506WHEN 50 THEN 'Affecté'
507WHEN 51 THEN 'Ouvert'
508WHEN 52 THEN 'A tester'
509WHEN 54 THEN 'A livrer'
510WHEN 80 THEN 'Livré'
511WHEN 90 THEN 'Fermé'
512END as state, from_unixtime(mantis_bug_table.last_updated,'%Y/%m/%d') as LastUpdated
513FROM mantis_bug_table
514INNER JOIN mantis_project_table
515ON mantis_project_table.ID = mantis_bug_table.project_id
516INNER JOIN mantis_user_table
517ON mantis_user_table.ID = mantis_bug_table.REPORTER_ID
518INNER JOIN mantis_config_table
519ON mantis_bug_table.profile_id = mantis_config_table.user_id
520WHERE target_version = "" and fixed_in_version <> "" and mantis_config_table.config_id = "bug_resolved_status_threshold" and mantis_bug_table.status < mantis_config_table.value
521ORDER BY mantis_bug_table.ID;
522
523END //
524
525delimiter ;DELIMITER //
526
527drop procedure if exists USP_GET_BUG_SUPPORT//
528
529CREATE PROCEDURE USP_GET_BUG_SUPPORT (IN _Date_Week DATETIME)
530BEGIN
531
532 SELECT FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%Y-%m-%d') As Date_New_Support, FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%H:%i') As Hour_New_Support, UVW_MANTIS_TEXT.*
533 , FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%Y-%m-%d') As Dat_Resolved, FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%H:%i') As Date_Resolved
534 , datediff(FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%Y-%m-%d %H:%i'), FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%Y-%m-%d %H:%i')) AS Resolve_Day
535 , mantis_project_table.name, mantis_category_table.name, mantis_bug_table.summary
536 FROM mantis_bug_table
537 INNER JOIN UVW_MANTIS_TEXT
538 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
539 INNER JOIN mantis_project_table
540 ON mantis_project_table.ID = mantis_bug_table.project_id
541 AND (mantis_project_table.name LIKE 'AIRFACT\_%'
542 OR mantis_project_table.name LIKE 'AIRPACK\_%')
543 INNER JOIN mantis_user_table
544 ON mantis_user_table.ID = mantis_bug_table.REPORTER_ID
545 INNER JOIN mantis_category_table
546 ON mantis_category_table.ID = mantis_bug_table.category_id
547 INNER JOIN (SELECT mantis_bug_history_table.BUG_ID, MIN(mantis_bug_history_table.date_modified) AS date_submitted
548 FROM mantis_bug_history_table
549 WHERE FIELD_NAME = 'HANDLER_ID'
550 AND (NEW_VALUE = 9 OR OLd_VALUE = 9)
551 GROUP BY mantis_bug_history_table.BUG_ID) tmpListTempSupport
552 ON tmpListTempSupport.bug_id = mantis_bug_table.id
553 LEFT JOIN (SELECT mantis_bug_history_table.BUG_ID, MIN(mantis_bug_history_table.date_modified) AS date_Resolved
554 FROM mantis_bug_history_table
555 WHERE FIELD_NAME = 'STATUS'
556 AND (NEW_VALUE >= 80 AND OLd_VALUE < 80)
557 GROUP BY mantis_bug_history_table.BUG_ID) tmpFirstResolved
558 ON tmpFirstResolved.bug_id = mantis_bug_table.id
559 WHERE tmpListTempSupport.date_submitted > UNIX_TIMESTAMP(_Date_Week)
560 ORDER BY 1, 2;
561
562END //
563
564delimiter ;DELIMITER //
565
566drop procedure if exists USP_GET_MANTIS_BY_VERSION//
567
568CREATE PROCEDURE USP_GET_MANTIS_BY_VERSION (IN _VersionFrom VARCHAR(20), IN _VersionTo VARCHAR(20))
569BEGIN
570
571 DROP TABLE IF EXISTS Nouveaute;
572 DROP TABLE IF EXISTS Nouveaute_LANG;
573 DROP TABLE IF EXISTS Nouveaute_LANG_SPLIT;
574 DROP TABLE IF EXISTS Nouveaute_LANG_EN;
575
576 CREATE TEMPORARY TABLE IF NOT EXISTS Nouveaute AS
577 (
578 SELECT mantis_bug_table.id, REPLACE(REPLACE(REPLACE(note, '<B>', ''), '</B>', ''), '[UK]', '[EN]') AS note, mantis_bugnote_table.bugnote_text_id
579 FROM mantis_bug_table
580 INNER JOIN mantis_bugnote_table
581 ON mantis_bugnote_table.bug_id = mantis_bug_table.id
582 INNER JOIN mantis_bugnote_text_table
583 ON mantis_bugnote_text_table.id = mantis_bugnote_table.bugnote_text_id
584 WHERE mantis_bugnote_text_table.note LIKE '%[TITRE]%'
585 AND mantis_bug_table.fixed_in_version >= _VersionFrom
586 AND mantis_bug_table.fixed_in_version <= _VersionTo
587 );
588
589 UPDATE Nouveaute
590 Set note = REPLACE(REPLACE(note, '] ', ']'), '] ', ']');
591
592 -- **************************************************************************
593 -- On récupÚre les textes traduits des nouveautés
594 -- **************************************************************************
595 CREATE TEMPORARY TABLE Nouveaute_LANG AS
596 (
597 SELECT id
598 , 'FR' as LANG
599 , substring(note, locate('[TITRE]', note, locate('[FR]', note)), IFNULL(NULLIF(locate('[EN]', note), 0) - locate('[TITRE]', note, locate('[FR]', note)), CHAR_LENGTH(note))) as NOTE
600 FROM Nouveaute
601 WHERE locate('[FR]', note) > 0
602 ) ;
603 INSERT INTO Nouveaute_LANG
604 SELECT id
605 , 'EN'
606 , substring(note, locate('[TITRE]', note, locate('[EN]', note)), CHAR_LENGTH(note)) as NOTE
607 FROM Nouveaute
608 WHERE locate('[EN]', note) > 0;
609
610 CREATE TEMPORARY TABLE Nouveaute_LANG_SPLIT AS
611 (
612 SELECT id
613 , LANG
614 , trim(TRAILING '\n\r' FROM substring(note, locate('[TITRE]', note) + 8 + 1, IFNULL(NULLIF(locate('[', note, locate('[TITRE]', note) + 8), 0) - (locate('[TITRE]', note) + 8 + 2), CHAR_LENGTH(note)))) as TITRE
615 , trim(TRAILING '\n\r' FROM substring(note, locate('[DESCRIPTION]', note) + 14 + 1, IFNULL(NULLIF(locate('[', note, locate('[DESCRIPTION]', note) + 14), 0) - (locate('[DESCRIPTION]', note) + 14 + 2), CHAR_LENGTH(note)))) as DESCRIPTION
616 , trim(TRAILING '\n\r' FROM substring(note, locate('[DROITS]', note) + 9 + 1, IFNULL(NULLIF(locate('[', note, locate('[DROITS]', note) + 9), 0) - (locate('[DROITS]', note) + 9 + 2), CHAR_LENGTH(note)))) as DROIT
617 FROM Nouveaute_LANG
618 );
619 CREATE TEMPORARY TABLE Nouveaute_LANG_EN AS
620 (
621 SELECT *
622 FROM Nouveaute_LANG_SPLIT
623 WHERE LANG = 'EN'
624 );
625
626 -- **************************************************************************
627 -- On retourne le résultat
628 -- **************************************************************************
629 SELECT
630 mantis_bug_table.id,
631 summary,
632 UVW_MANTIS_TEXT.severity AS Severity,
633 mantis_category_table.name AS category_name,
634 mantis_user_table.username,
635 mantis_bug_text_table.description AS Description,
636 UVW_MANTIS_TEXT.status AS state,
637 fixed_in_version,
638 mantis_project_table.name AS project_name,
639 UVW_MANTIS_TEXT.view_state AS view_state,
640 GROUP_CONCAT(DISTINCT mantis_tag_table.name
641 ORDER BY mantis_tag_table.name ASC
642 SEPARATOR '; ') AS tag_name,
643 Nouveaute.note,
644 Nouveaute.bugnote_text_id,
645 IFNULL(Nouveaute_FR.TITRE, '') as TITRE_FR,
646 IFNULL(Nouveaute_FR.DESCRIPTION, '') as DESCRIPTION_FR,
647 IFNULL(Nouveaute_FR.DROIT, '') as DROIT_FR,
648 IFNULL(Nouveaute_EN.TITRE, '') as TITRE_EN,
649 IFNULL(Nouveaute_EN.DESCRIPTION, '') as DESCRIPTION_EN,
650 IFNULL(Nouveaute_EN.DROIT, '') as DROIT_EN
651
652 FROM mantis_bug_table
653 INNER JOIN UVW_MANTIS_TEXT
654 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
655 INNER JOIN mantis_category_table
656 ON mantis_category_table.id = mantis_bug_table.category_id
657 INNER JOIN mantis_project_table
658 ON mantis_project_table.id = mantis_bug_table.project_id
659 INNER JOIN mantis_bug_text_table
660 ON mantis_bug_text_table.id = mantis_bug_table.bug_text_id
661 INNER JOIN mantis_user_table
662 ON mantis_user_table.id = mantis_bug_table.reporter_id
663 LEFT JOIN mantis_bug_tag_table
664 ON mantis_bug_tag_table.bug_id = mantis_bug_table.id
665 LEFT JOIN mantis_tag_table
666 ON mantis_tag_table.id = mantis_bug_tag_table.tag_id
667 LEFT JOIN Nouveaute
668 ON Nouveaute.id = mantis_bug_table.id
669 LEFT JOIN Nouveaute_LANG_SPLIT as Nouveaute_FR
670 ON Nouveaute_FR.id = mantis_bug_table.id
671 AND Nouveaute_FR.LANG = 'FR'
672 LEFT JOIN Nouveaute_LANG_EN as Nouveaute_EN
673 ON Nouveaute_EN.id = mantis_bug_table.id
674 AND Nouveaute_EN.LANG = 'EN'
675 LEFT JOIN mantis_bug_tag_table tmp_bug_tag
676 inner join mantis_tag_table tmp_tag
677 ON tmp_bug_tag.tag_id = tmp_tag.id
678 AND tmp_tag.name = 'toclose'
679 ON mantis_bug_table.id = tmp_bug_tag.bug_id
680 WHERE fixed_in_version >= _VersionFrom
681 AND fixed_in_version <= _VersionTo
682 AND tmp_bug_tag.bug_id IS NULL -- on ne veut pas ceux qui sont avec la balise ToClose
683 GROUP BY mantis_bug_table.id
684 ORDER BY mantis_project_table.name ASC , fixed_in_version DESC , id DESC;
685
686END //
687
688delimiter ;DELIMITER //
689
690drop procedure if exists USP_GET_MANTIS_SUPPORT_BY_DATE//
691
692CREATE PROCEDURE USP_GET_MANTIS_SUPPORT_BY_DATE (IN _DateFrom DATETIME, IN _DateTo DATETIME)
693BEGIN
694
695 SELECT FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%Y-%m-%d') As Date_New_Support, FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%H:%i') As Hour_New_Support, UVW_MANTIS_TEXT.*
696 , FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%Y-%m-%d') As Date_Resolved, FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%H:%i') As Hour_Resolved
697 , datediff(FROM_UNIXTIME(tmpFirstResolved.date_Resolved, '%Y-%m-%d %H:%i'), FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%Y-%m-%d %H:%i')) AS Resolve_Day
698 , mantis_project_table.name, mantis_category_table.name, mantis_bug_table.summary
699 FROM mantis_bug_table
700 INNER JOIN UVW_MANTIS_TEXT
701 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
702 INNER JOIN mantis_project_table
703 ON mantis_project_table.ID = mantis_bug_table.project_id
704 AND (mantis_project_table.name LIKE 'AIRFACT\_%'
705 OR mantis_project_table.name LIKE 'AIRPACK\_%')
706 INNER JOIN mantis_user_table
707 ON mantis_user_table.ID = mantis_bug_table.REPORTER_ID
708 INNER JOIN mantis_category_table
709 ON mantis_category_table.ID = mantis_bug_table.category_id
710 INNER JOIN (SELECT mantis_bug_history_table.BUG_ID, MIN(mantis_bug_history_table.date_modified) AS date_submitted
711 FROM mantis_bug_history_table
712 WHERE FIELD_NAME = 'HANDLER_ID'
713 AND (NEW_VALUE = 9 OR OLd_VALUE = 9)
714 GROUP BY mantis_bug_history_table.BUG_ID) tmpListTempSupport
715 ON tmpListTempSupport.bug_id = mantis_bug_table.id
716 LEFT JOIN (SELECT mantis_bug_history_table.BUG_ID, MIN(mantis_bug_history_table.date_modified) AS date_Resolved
717 FROM mantis_bug_history_table
718 WHERE FIELD_NAME = 'STATUS'
719 AND (NEW_VALUE >= 80 AND OLd_VALUE < 80)
720 GROUP BY mantis_bug_history_table.BUG_ID) tmpFirstResolved
721 ON tmpFirstResolved.bug_id = mantis_bug_table.id
722 WHERE tmpListTempSupport.date_submitted between UNIX_TIMESTAMP(_DateFrom) AND UNIX_TIMESTAMP(_DateTo)
723 ORDER BY 1, 2;
724
725
726END //
727
728delimiter ;DELIMITER //
729
730drop procedure if exists USP_GET_NB_BUG_SUPPORT//
731
732CREATE PROCEDURE USP_GET_NB_BUG_SUPPORT (IN _Date_Week DATETIME)
733BEGIN
734
735IF (SELECT MAX(Date_submitted) from ADS_STAT_bug_support) < CURDATE() THEN
736CALL USP_NB_BUG_SUPPORT( DATE_ADD((SELECT MAX(Date_submitted) from ADS_STAT_bug_support), INTERVAL 2 DAY));
737END IF;
738SELECT *
739FROM ADS_STAT_bug_support
740WHERE Date_submitted >= _Date_Week;
741
742END //
743
744delimiter ;SELECT FROM_UNIXTIME(ifnull(mantis_bug_history_table.date_modified, mantis_bug_table.Date_submitted), '%Y-%m-%d') As Date_Order_Last
745 , DATE_ADD(FROM_UNIXTIME(ifnull(mantis_bug_history_table.date_modified, mantis_bug_table.Date_submitted), '%Y-%m-%d'), INTERVAL 31 DAY) as Date_Relance
746 , mantis_custom_field_string_table.value as Invoice_status
747 , UVW_MANTIS_TEXT.*
748 , mantis_project_table.name as Project
749 , mantis_category_table.name as Category
750 , CASE WHEN mantis_bug_table.due_date = 1 THEN '' ELSE FROM_UNIXTIME(mantis_bug_table.due_date, '%Y-%m-%d') END as Date_Due_Mantis
751 , mantis_bug_table.summary
752 FROM mantis_bug_table
753 INNER JOIN mantis_custom_field_string_table
754 on mantis_custom_field_string_table.BUG_ID = mantis_bug_table.id
755 INNER JOIN mantis_custom_field_table
756 ON mantis_custom_field_string_table.field_id = mantis_custom_field_table.id
757 AND mantis_custom_field_table.NAME = 'Order status'
758 AND mantis_custom_field_string_table.value in ('Sent', 'Accepted')
759 INNER JOIN UVW_MANTIS_TEXT
760 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
761 LEFT JOIN (
762 SELECT mantis_bug_history_table.BUG_ID, MAX(mantis_bug_history_table.id) AS ID
763 FROM mantis_bug_history_table
764 WHERE FIELD_NAME = 'Order status'
765 GROUP BY mantis_bug_history_table.BUG_ID) tmpList
766 ON tmpList.BUG_ID = mantis_bug_table.id
767 LEFT JOIN mantis_bug_history_table
768 ON mantis_bug_history_table.BUG_ID = mantis_bug_table.id
769 AND mantis_bug_history_table.ID = tmpList.id
770 AND mantis_bug_history_table.new_value in ('Sent', 'Accepted')
771 INNER JOIN mantis_project_table
772 ON mantis_project_table.ID = mantis_bug_table.project_id
773 INNER JOIN mantis_category_table
774 ON mantis_category_table.ID = mantis_bug_table.category_id
775
776 WHERE mantis_bug_table.status < 80 -- livré
777 ORDER BY ifnull(mantis_bug_history_table.date_modified, mantis_bug_table.Date_submitted);
778
779SELECT FROM_UNIXTIME(ifnull(mantis_bug_table.last_updated, mantis_bug_table.Date_submitted) , '%Y-%m-%d') As Date_Comment_Last
780 , DATE_ADD(FROM_UNIXTIME(ifnull(mantis_bug_table.last_updated, mantis_bug_table.Date_submitted) , '%Y-%m-%d'), INTERVAL 31 DAY) as Date_Relance
781 , UVW_MANTIS_TEXT.*
782 , mantis_project_table.name as Project
783 , mantis_category_table.name as Category
784 , CASE WHEN mantis_bug_table.due_date = 1 THEN '' ELSE FROM_UNIXTIME(mantis_bug_table.due_date, '%Y-%m-%d') END as Date_Due_Mantis
785 , mantis_bug_table.summary
786 FROM mantis_bug_table
787 INNER JOIN UVW_MANTIS_TEXT
788 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
789
790 INNER JOIN mantis_project_table
791 ON mantis_project_table.ID = mantis_bug_table.project_id
792 INNER JOIN mantis_category_table
793 ON mantis_category_table.ID = mantis_bug_table.category_id
794
795 WHERE mantis_bug_table.status = 20 -- Commentaire
796 ORDER BY ifnull(mantis_bug_table.last_updated, mantis_bug_table.Date_submitted);
797
798 DELIMITER //
799
800drop procedure if exists USP_GET_PROJECT_NAME_BY_ID//
801
802CREATE PROCEDURE USP_GET_PROJECT_NAME_BY_ID (IN _ID INT)
803BEGIN
804
805SELECT name
806FROM mantis_project_table
807WHERE id = _ID;
808
809END //
810
811delimiter ;DELIMITER //
812
813drop procedure if exists USP_GET_TIME_MANTIS//
814
815CREATE PROCEDURE USP_GET_TIME_MANTIS (IN _DateStart DATETIME)
816BEGIN
817
818CREATE TEMPORARY TABLE IF NOT EXISTS MaxHISTO AS
819(
820SELECT mantis_bug_table.id as bug_id, max(mantis_bug_history_table.id) as max_hist_id
821FROM mantis_bug_table
822INNER JOIN mantis_bug_history_table
823ON mantis_bug_history_table.bug_id = mantis_bug_table.id
824WHERE field_name = "status" and old_value < 54 and new_value >= 54 and severity = 60 and mantis_bug_history_table.date_modified >= UNIX_TIMESTAMP(_DateStart)
825GROUP BY mantis_bug_table.id
826);
827
828CREATE TEMPORARY TABLE IF NOT EXISTS MaxTime AS
829(
830SELECT MaxHISTO.bug_id, MaxHISTO.max_hist_id, IFNULL(SUM(mantis_bugnote_table.time_tracking),0) as Time_Total
831FROM MaxHISTO
832 LEFT JOIN mantis_bugnote_table
833 ON MaxHISTO.bug_id = mantis_bugnote_table.bug_id
834GROUP BY MaxHISTO.bug_id, MaxHISTO.max_hist_id
835);
836
837
838SELECT year(from_unixtime(mantis_bug_history_table.date_modified, '%Y/%m/%d')) as Year, MONTH(from_unixtime(mantis_bug_history_table.date_modified, '%Y/%m/%d')) as Month
839, AVG(TIMESTAMPDIFF(DAY, from_unixtime(mantis_bug_table.date_submitted,'%Y-%m-%d'), from_unixtime(mantis_bug_history_table.date_modified,'%Y-%m-%d'))) as day_difference
840, SUM(MaxTime.Time_Total) as Time_Total
841, AVG(MaxTime.Time_Total) as Moyenne_time
842, count(distinct MaxTime.bug_id) as nbr_bug
843FROM MaxTime
844inner join mantis_bug_table
845 on mantis_bug_table.id = MaxTime.bug_id
846INNER JOIN mantis_bug_history_table
847 ON mantis_bug_history_table.bug_id = mantis_bug_table.id
848 and mantis_bug_history_table.id = MaxTime.max_hist_id
849GROUP BY Year, Month;
850
851drop table MaxTime;
852drop table MaxHISTO;
853
854END //
855
856delimiter ;DELIMITER //
857
858drop procedure if exists USP_GET_USERS//
859
860CREATE PROCEDURE USP_GET_USERS()
861BEGIN
862
863 SELECT mantis_user_table.id
864 , CONCAT(IFNULL(mantis_user_table.realname, ''), CASE WHEN mantis_user_table.email LIKE '%@adsoftware.fr' THEN '' ELSE CONCAT(' [', IFNULL(mantis_user_table.username, ''), ']') END) AS username
865 , mantis_user_table.email
866 , mantis_project_table.name
867 , CASE mantis_project_user_list_table.access_level
868 WHEN 10
869 THEN 'viewer'
870 WHEN 25
871 THEN 'reporter'
872 WHEN 40
873 THEN 'updater'
874 WHEN 55
875 THEN 'developer'
876 WHEN 70
877 THEN 'manager'
878 WHEN 90
879 THEN 'administrator'
880 END AS access_level
881 FROM mantis_user_table
882 INNER JOIN mantis_project_user_list_table
883 ON mantis_project_user_list_table.user_id = mantis_user_table.id
884 INNER JOIN mantis_project_table
885 ON mantis_project_table.id = mantis_project_user_list_table.project_id
886 WHERE mantis_user_table.enabled = 1
887 UNION
888 SELECT mantis_user_table.id
889 , CONCAT(IFNULL(mantis_user_table.realname, ''), CASE WHEN mantis_user_table.email LIKE '%@adsoftware.fr' THEN '' ELSE CONCAT(' [', IFNULL(mantis_user_table.username, ''), ']') END) AS username
890 , mantis_user_table.email
891 , '__ NON AFFECTE __ ' as name
892 , CASE mantis_user_table.access_level
893 WHEN 10
894 THEN 'viewer'
895 WHEN 25
896 THEN 'reporter'
897 WHEN 40
898 THEN 'updater'
899 WHEN 55
900 THEN 'developer'
901 WHEN 70
902 THEN 'manager'
903 WHEN 90
904 THEN 'administrator'
905 END AS access_level
906 FROM mantis_user_table
907 LEFT JOIN mantis_project_user_list_table
908 ON mantis_project_user_list_table.user_id = mantis_user_table.id
909 WHERE mantis_user_table.enabled = 1
910 AND mantis_project_user_list_table.user_id IS NULL
911 ;
912
913END //
914
915delimiter ;DELIMITER //
916
917drop procedure if exists USP_MANTIS_CUSTOMER_PROJECT_NAME_UPDATE//
918
919CREATE PROCEDURE USP_MANTIS_CUSTOMER_PROJECT_NAME_UPDATE (IN _ID INT, IN _NAME VARCHAR(50))
920BEGIN
921
922 IF _ID IS NULL THEN
923 SELECT mantis_project_table.id INTO _ID
924 FROM mantis_project_table
925 where mantis_project_table.name = _NAME;
926
927 IF _ID IS NULL THEN
928 INSERT INTO mantis_project_table VALUES (null, _NAME, 10, 1, 50, 10, "", "", 1, 1);
929 INSERT INTO mantis_project_hierarchy_table VALUES (LAST_INSERT_ID(), 63, 1);
930 insert into mantis_project_user_list_table (project_id, user_id, access_level)
931 select LAST_INSERT_ID(), user_id, access_level from mantis_project_user_list_table where project_id = 69;
932 INSERT INTO mantis_custom_field_project_table (field_id, project_id, sequence)
933 SELECT field_id, LAST_INSERT_ID(), sequence FROM mantis_custom_field_project_table Where project_id = 69;
934 SELECT LAST_INSERT_ID() as ID;
935 ELSE
936 SELECT _ID as ID;
937 END IF;
938 ELSE
939 IF _NAME <> "" THEN
940 SIGNAL SQLSTATE '45000'
941 SET MESSAGE_TEXT = 'Mantis project name cannot be updated, please open MantisBT to do this.';
942 -- UPDATE mantis_project_table SET name = _NAME WHERE id = _ID;
943 SELECT _ID as ID;
944 ELSE
945 SELECT NULL as ID;
946 END IF;
947 END IF;
948
949END //
950
951delimiter ;DELIMITER //
952
953drop procedure if exists USP_MANTIS_OPEN//
954
955CREATE PROCEDURE USP_MANTIS_OPEN(IN _project VARCHAR(20))
956BEGIN
957
958SELECT mantis_bug_table.id, CASE mantis_bug_table.status
959WHEN 10 then 'new'
960WHEN 20 THEN 'Commentaire'
961WHEN 30 THEN 'Résolu en attente'
962WHEN 40 THEN 'Specifications'
963WHEN 50 THEN 'Affecté'
964WHEN 51 THEN 'Ouvert'
965WHEN 52 THEN 'A tester'
966WHEN 54 THEN 'A livrer'
967WHEN 80 THEN 'Livré'
968WHEN 90 THEN 'Fermé'
969END as state, mantis_project_table.name as project_name, mantis_project_table.id as project_id, mantis_category_table.name as category_name, from_unixtime(mantis_bug_table.date_submitted,'%Y/%m/%d') as DateSubmitted,
970mantis_user_table.username
971FROM mantis_bug_table
972INNER JOIN mantis_project_table
973ON mantis_project_table.id = mantis_bug_table.project_id
974INNER JOIN mantis_category_table
975ON mantis_category_table.id = mantis_bug_table.category_id
976INNER JOIN mantis_user_table
977ON mantis_user_table.id = mantis_bug_table.reporter_id
978INNER JOIN mantis_config_table
979ON mantis_bug_table.profile_id = mantis_config_table.user_id
980WHERE mantis_config_table.config_id = "bug_resolved_status_threshold" and mantis_bug_table.status < mantis_config_table.value and mantis_project_table.name = _project
981ORDER BY mantis_bug_table.id DESC;
982
983END //
984
985delimiter ;DELIMITER //
986
987drop procedure if exists USP_NB_BUG//
988
989CREATE PROCEDURE USP_NB_BUG(IN _Date_Start DATETIME)
990BEGIN
991 DROP TABLE IF EXISTS TmpToADD;
992 DROP TABLE IF EXISTS TmpResult;
993 DROP TABLE IF EXISTS TmpProject;
994 DROP TABLE IF EXISTS TmpProjectSeverity;
995 DROP TABLE IF EXISTS TmpProjectPriority;
996
997
998 CREATE TEMPORARY TABLE IF NOT EXISTS TmpResult AS
999 (
1000 SELECT mantis_project_table.name
1001 , year(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d')) as year_modified
1002 , week(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d'),5 ) as week_modified
1003 , count(*) as nombreNew
1004 , UVW_MANTIS_TEXT.Severity as severity
1005 , UVW_MANTIS_TEXT.priority as priority
1006 FROM mantis_bug_table
1007 INNER JOIN UVW_MANTIS_TEXT
1008 ON UVW_MANTIS_TEXT.id = mantis_bug_table.id
1009 INNER JOIN mantis_user_table
1010 ON mantis_user_table.ID = mantis_bug_table.REPORTER_ID
1011 INNER JOIN mantis_project_table
1012 ON mantis_project_table.ID = mantis_bug_table.project_id
1013 WHERE mantis_bug_table.date_submitted >= UNIX_TIMESTAMP(_Date_Start)
1014 AND (mantis_project_table.name like 'AIRFACT\_%' or mantis_project_table.name like 'AIRPACK\_%')
1015 GROUP BY mantis_project_table.name, year(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d')), week(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d'),5 )
1016-- ORDER BY mantis_project_table.name, year(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d')), week(from_unixtime(mantis_bug_table.date_submitted, '%Y/%m/%d'),5 )
1017 );
1018
1019 -- Pour les besoins du graph DevExpress, on rajoute une ligne pour chaque projet dans Severity et Priority
1020 CREATE TEMPORARY TABLE IF NOT EXISTS TmpProject AS
1021 (
1022 SELECT name, max(year_modified) as year_modified, max(week_modified) as week_modified
1023 FROM TmpResult
1024 GROUP BY TmpResult.name
1025 );
1026
1027 CREATE TEMPORARY TABLE IF NOT EXISTS TmpProjectSeverity AS
1028 (
1029 SELECT DISTINCT TmpProject.name, tmp.severity, TmpProject.year_modified, TmpProject.week_modified
1030 FROM (SELECT DISTINCT TmpResult.severity FROM TmpResult) tmp
1031 CROSS JOIN TmpProject
1032 );
1033
1034 CREATE TEMPORARY TABLE IF NOT EXISTS TmpProjectPriority AS
1035 (
1036 SELECT DISTINCT TmpProject.name, tmp.priority, TmpProject.year_modified, TmpProject.week_modified
1037 FROM (SELECT DISTINCT TmpResult.priority FROM TmpResult) tmp
1038 CROSS JOIN TmpProject
1039 );
1040
1041 CREATE TEMPORARY TABLE IF NOT EXISTS TmpToADD AS
1042 (
1043 SELECT TmpProjectSeverity.name, TmpProjectSeverity.severity AS ToAdd, TmpProjectSeverity.year_modified, TmpProjectSeverity.week_modified
1044 FROM TmpProjectSeverity
1045 LEFT JOIN TmpResult
1046 ON TmpResult.name = TmpProjectSeverity.name
1047 AND TmpResult.severity = TmpProjectSeverity.severity
1048 WHERE TmpResult.name IS NULL
1049 );
1050
1051 INSERT TmpResult (name, severity, year_modified, week_modified, nombreNew)
1052 SELECT *, 0
1053 FROM TmpToADD;
1054
1055 DELETE FROM TmpToADD;
1056
1057 INSERT INTO TmpToADD
1058 SELECT TmpProjectPriority.name, TmpProjectPriority.priority, TmpProjectPriority.year_modified, TmpProjectPriority.week_modified
1059 FROM TmpProjectPriority
1060 LEFT JOIN TmpResult
1061 ON TmpResult.name = TmpProjectPriority.name
1062 AND TmpResult.priority = TmpProjectPriority.priority
1063 WHERE TmpResult.name IS NULL;
1064
1065 INSERT INTO TmpResult (name, priority, year_modified, week_modified, nombreNew)
1066 SELECT *, 0
1067 FROM TmpToADD;
1068
1069 SELECT *
1070 FROM TmpResult
1071 ORDER BY name;
1072END //
1073
1074delimiter ;DELIMITER //
1075
1076drop procedure if exists USP_NB_BUG_SUPPORT//
1077
1078CREATE PROCEDURE USP_NB_BUG_SUPPORT (IN _Date_Week DATETIME)
1079BEGIN
1080
1081CREATE TEMPORARY TABLE IF NOT EXISTS ListeSemaine AS
1082(
1083 SELECT DISTINCT tt.id as bug_id
1084 , from_unixtime(ads_date.date_submitted, '%Y-%m-%d') as DateSubmitted
1085 FROM ads_date
1086 CROSS JOIN (SELECT distinct mantis_bug_table.ID , mantis_bug_history_table.NEW_VALUE
1087 FROM mantis_bug_table
1088 INNER JOIN mantis_bug_history_table
1089 ON mantis_bug_history_table.bug_id = mantis_bug_table.id
1090 AND mantis_bug_history_table.FIELD_NAME = 'HANDLER_ID'
1091 AND mantis_bug_history_table.NEW_VALUE = 9
1092 WHERE mantis_bug_table.last_updated > UNIX_TIMESTAMP(_Date_Week)
1093 OR mantis_bug_table.status < 80) tt
1094 ON 1 = 1
1095 WHERE ads_date.date_submitted BETWEEN UNIX_TIMESTAMP(_Date_Week) AND UNIX_TIMESTAMP(NOW())
1096);
1097
1098CREATE TEMPORARY TABLE IF NOT EXISTS ListeBugStatus AS
1099(
1100 SELECT ListeSemaine.bug_id
1101 , ListeSemaine.DateSubmitted
1102 , 0 as handler_id
1103 , (SELECT NEW_VALUE
1104 FROM mantis_bug_history_table
1105 WHERE mantis_bug_history_table.BUG_ID = ListeSemaine.bug_id
1106 AND from_unixtime(mantis_bug_history_table.date_modified, '%Y-%m-%d') < ListeSemaine.DateSubmitted
1107 AND FIELD_NAME = 'status'
1108 ORDER BY mantis_bug_history_table.date_modified DESC, mantis_bug_history_table.id DESC
1109 LIMIT 1
1110 ) as status
1111 FROM ListeSemaine
1112);
1113
1114UPDATE ListeBugStatus
1115SET handler_id = (SELECT NEW_VALUE
1116 FROM mantis_bug_history_table
1117 WHERE mantis_bug_history_table.BUG_ID = ListeBugStatus.bug_id
1118 AND FROM_UNIXTIME(mantis_bug_history_table.date_modified,
1119 '%Y-%m-%d') < ListeBugStatus.DateSubmitted
1120 AND FIELD_NAME = 'HANDLER_ID'
1121 AND (NEW_VALUE = 9 OR OLd_VALUE = 9)
1122 ORDER BY mantis_bug_history_table.date_modified DESC , mantis_bug_history_table.id DESC
1123 LIMIT 1)
1124WHERE ListeBugStatus.status < 54;
1125
1126INSERT INTO ADS_STAT_bug_support (SupportAssigne, SupportCommentaire, Date_submitted)
1127SELECT
1128 count(if(ListeBugStatus.status = 20, NULL, 1)) as SupportAssigne
1129 , count(if(ListeBugStatus.status = 20, 1, NULL)) as SupportCommentaire
1130 , DATE_ADD(ListeBugStatus.DateSubmitted, INTERVAL -1 DAY)
1131 FROM ListeBugStatus
1132 WHERE ListeBugStatus.handler_id = 9
1133 AND ListeBugStatus.status < 54
1134 GROUP BY ListeBugStatus.DateSubmitted
1135 order by ListeBugStatus.DateSubmitted;
1136
1137
1138-- on met à jour le nombre de nouveau bug par jour, posté sur un projet client et qui a été une fois dans la boîte du support
1139UPDATE ADS_STAT_bug_support
1140SET
1141 NBNewBug = (SELECT COUNT(*)
1142 FROM mantis_bug_table
1143 INNER JOIN mantis_project_table
1144 ON mantis_project_table.ID = mantis_bug_table.project_id
1145 AND (mantis_project_table.name LIKE 'AIRFACT\_%'
1146 OR mantis_project_table.name LIKE 'AIRPACK\_%')
1147 INNER JOIN mantis_user_table
1148 ON mantis_user_table.ID = mantis_bug_table.REPORTER_ID
1149 INNER JOIN (SELECT mantis_bug_history_table.BUG_ID, MIN(mantis_bug_history_table.date_modified) AS date_submitted
1150 FROM mantis_bug_history_table
1151 WHERE FIELD_NAME = 'HANDLER_ID'
1152 AND (NEW_VALUE = 9 OR OLd_VALUE = 9)
1153 GROUP BY mantis_bug_history_table.BUG_ID) tmpListTempSupport
1154 ON tmpListTempSupport.bug_id = mantis_bug_table.id
1155 WHERE FROM_UNIXTIME(tmpListTempSupport.date_submitted, '%Y-%m-%d') = ADS_STAT_bug_support.Date_submitted)
1156WHERE ADS_STAT_bug_support.NBNewBug IS NULL;
1157
1158drop table ListeSemaine;
1159drop table ListeBugStatus;
1160
1161END //
1162
1163delimiter ;DELIMITER //
1164
1165drop procedure if exists USP_POSTED_NOTE//
1166
1167CREATE PROCEDURE USP_POSTED_NOTE(IN _Date_Start DATETIME)
1168BEGIN
1169
1170SELECT mantis_project_table.name,
1171 mantis_user_table.username,
1172 SUM(mantis_bugnote_table.time_tracking) as Time_Total,
1173 SUM(1) as nb_note_total,
1174 SUM(case when mantis_bugnote_table.time_tracking > 0 THEN 1 ELSE 0 END) as nb_note_with_Time_Traking
1175FROM `mantis_bugnote_table`
1176INNER JOIN mantis_bug_table
1177ON mantis_bug_table.ID = mantis_bugnote_table.bug_ID
1178INNER JOIN mantis_project_table
1179 ON mantis_project_table.ID = mantis_bug_table.project_id
1180INNER JOIN mantis_user_table
1181 ON mantis_user_table.ID = mantis_bugnote_table.REPORTER_ID
1182 AND mantis_user_table.EMAIL LIKE '%@adsoftware.fr'
1183where mantis_bugnote_table.last_modified >= UNIX_TIMESTAMP(_Date_Start)
1184GROUP BY mantis_project_table.name, mantis_user_table.username;
1185
1186END //
1187
1188delimiter ;DELIMITER //
1189
1190drop procedure if exists USP_RESEARCH_FULLTEXT; //
1191
1192CREATE PROCEDURE USP_RESEARCH_FULLTEXT(IN _text VARCHAR(50))
1193BEGIN
1194
1195CREATE TEMPORARY TABLE IF NOT EXISTS Tmp_TAG ENGINE=MyISAM AS
1196(
1197 select mantis_bug_table.id, GROUP_CONCAT(DISTINCT mantis_tag_table.name ORDER BY mantis_tag_table.name ASC SEPARATOR '; ') as tag_liste, mantis_tag_table.name as tag_name
1198 FROM mantis_bug_table
1199 INNER JOIN mantis_bug_tag_table
1200 ON mantis_bug_tag_table.bug_id = mantis_bug_table.id
1201 INNER JOIN mantis_tag_table
1202 ON mantis_tag_table.id = mantis_bug_tag_table.tag_id
1203 GROUP BY mantis_bug_table.id
1204);
1205
1206CREATE TEMPORARY TABLE IF NOT EXISTS Tmp AS
1207(
1208 SELECT mantis_bug_table.ID as id_mantis, mantis_bugnote_text_table.id as id_note
1209 , MATCH (mantis_bug_table.summary, mantis_bug_text_table.description, mantis_bugnote_text_table.note, mantis_project_table.name) AGAINST (_text IN BOOLEAN MODE) as cpt
1210 , MATCH (mantis_bugnote_text_table.note) AGAINST (_text IN BOOLEAN MODE) as cpt_note
1211 , Tmp_TAG.tag_liste
1212 FROM mantis_bug_table
1213 INNER JOIN mantis_project_table
1214 ON mantis_project_table.id = mantis_bug_table.project_id
1215 LEFT JOIN mantis_bugnote_table
1216 ON mantis_bugnote_table.bug_id = mantis_bug_table.id
1217 LEFT JOIN mantis_bugnote_text_table
1218 ON mantis_bugnote_text_table.id = mantis_bugnote_table.bugnote_text_id
1219 INNER JOIN mantis_bug_text_table
1220 ON mantis_bug_table.ID = mantis_bug_text_table.id
1221 LEFT JOIN Tmp_TAG
1222 ON Tmp_TAG.id = mantis_bug_table.id
1223 WHERE ifnull(_text, '') = ''
1224 or MATCH (mantis_bug_table.summary, mantis_bug_text_table.description, mantis_bugnote_text_table.note, Tmp_TAG.tag_name, mantis_bug_table.id, mantis_project_table.name) AGAINST (_text IN BOOLEAN MODE)
1225 ORDER BY cpt DESC, mantis_bug_table.ID DESC, mantis_bugnote_text_table.id ASC
1226);
1227
1228CREATE TEMPORARy TABLE IF NOT EXISTS TmpDelete AS
1229(
1230 SELECT id_mantis FROM Tmp GROUP BY id_mantis Having SUM(cpt_note) > 0
1231);
1232
1233
1234DELETE FROM Tmp
1235WHERE id_mantis IN (SELECT id_mantis FROM TmpDelete) and cpt_note = 0;
1236
1237
1238CREATE TEMPORARY TABLE IF NOT EXISTS Tmp2 AS
1239(
1240 SELECT id_mantis, id_note, MAX(id_note) as id_note_max
1241 FROM Tmp
1242 WHERE id_note IS NOT NULL
1243 GROUP BY id_mantis
1244 HAVING SUM(cpt_note) = 0
1245);
1246
1247DELETE Tmp FROM Tmp INNER JOIN Tmp2 ON Tmp2.id_mantis = Tmp.id_mantis where Tmp.id_note <> Tmp2.id_note_max;
1248
1249
1250
1251SELECT DISTINCT Tmp.id_mantis as ID, Tmp.id_note, Tmp.cpt, Tmp.cpt_note, mantis_project_table.name as projectName, from_unixtime(mantis_bugnote_table.date_submitted,'%Y/%m/%d') as date_note
1252 , mantis_category_table.name as categoryName
1253 , mantis_user_table.username
1254 , from_unixtime(mantis_bug_table.date_submitted,'%Y/%m/%d') as DateSubmitted
1255 , CASE mantis_bug_table.severity
1256 WHEN 10 then 'Integration'
1257 WHEN 15 THEN 'Regularisation'
1258 WHEN 20 THEN 'Amélioration'
1259 WHEN 40 THEN 'Cosmétique'
1260 WHEN 60 THEN 'Anomalie'
1261 WHEN 70 THEN 'Installation'
1262 END as Severity
1263 , mantis_bug_table.summary
1264 , mantis_bug_text_table.description
1265 , mantis_bugnote_text_table.note, CASE mantis_bug_table.status
1266 WHEN 10 then 'new'
1267 WHEN 20 THEN 'Commentaire'
1268 WHEN 30 THEN 'Résolu en attente'
1269 WHEN 40 THEN 'Specifications'
1270 WHEN 50 THEN 'Affecté'
1271 WHEN 51 THEN 'Ouvert'
1272 WHEN 52 THEN 'A tester'
1273 WHEN 54 THEN 'A livrer'
1274 WHEN 80 THEN 'Livré'
1275 WHEN 90 THEN 'Fermé'
1276 END as state, MATCH (mantis_bug_table.summary) AGAINST (_text IN BOOLEAN MODE) as cpt_summary, MATCH (mantis_bug_text_table.description) AGAINST (_text IN BOOLEAN MODE) as cpt_description
1277 , Tmp.tag_liste as tag_name
1278FROM Tmp
1279LEFT JOIN mantis_bugnote_table
1280ON mantis_bugnote_table.id = Tmp.id_note
1281LEFT JOIN mantis_bugnote_text_table
1282ON mantis_bugnote_text_table.id = Tmp.id_note
1283INNER JOIN mantis_bug_table
1284ON Tmp.id_mantis = mantis_bug_table.id
1285INNER JOIN mantis_bug_text_table
1286ON mantis_bug_table.ID = mantis_bug_text_table.id
1287INNER JOIN mantis_user_table
1288ON mantis_user_table.id = mantis_bug_table.reporter_id
1289INNER JOIN mantis_category_table
1290ON mantis_category_table.id = mantis_bug_table.category_id
1291INNER JOIN mantis_project_table
1292ON mantis_project_table.id = mantis_bug_table.project_id;
1293
1294drop table Tmp;
1295drop table TmpDelete;
1296drop table Tmp2;
1297drop table Tmp_TAG;
1298
1299END //
1300
1301delimiter ;DELIMITER //
1302
1303drop procedure if exists USP_SET_USER_PROJECT//
1304
1305CREATE PROCEDURE USP_SET_USER_PROJECT(IN _USER_ID INT, IN _PROJECT_ID INT, IN _REPLACE_EXISTING BIT)
1306USP_SET_USER_PROJECT:BEGIN
1307/***********************************************************
1308--
1309select * from mantis_user_table where realname like 'alan%'
1310call USP_SET_USER_PROJECT(357, 'AIRPACK_ATN', 0)
1311
1312-- Si on passe _PROJECT_ID Ã vide et _REPLACE_EXISTING Ã 1, on supprime tous les droits
1313-- Sinon, on donne systématiquement le droit viewer sur PUBLIC et Reporter sur le projet demandé
1314***********************************************************/
1315 IF NULLIF(_USER_ID, 0) IS NULL THEN
1316 LEAVE USP_SET_USER_PROJECT;
1317 END IF;
1318
1319 IF _REPLACE_EXISTING = 1 THEN
1320 DELETE FROM mantis_project_user_list_table
1321 WHERE mantis_project_user_list_table.user_id = _USER_ID;
1322 END IF;
1323
1324 -- On donne le droit sur Public
1325 CREATE TEMPORARY TABLE IF NOT EXISTS ListProjetDroit AS
1326 (
1327 SELECT _USER_ID as user_id
1328 , mantis_project_table.ID as project_id
1329 , CASE WHEN name = 'AIRPACK Public' THEN 10 ELSE 25 END as access_level
1330 FROM mantis_project_table
1331 WHERE ((mantis_project_table.name = 'AIRPACK Public')
1332 OR (mantis_project_table.ID = _PROJECT_ID))
1333 AND _PROJECT_ID > 0
1334 );
1335 /*
1336 , CASE mantis_project_user_list_table.access_level
1337 WHEN 10
1338 THEN 'viewer'
1339 WHEN 25
1340 THEN 'reporter'
1341 WHEN 40
1342 THEN 'updater'
1343 WHEN 55
1344 THEN 'developer'
1345 WHEN 70
1346 THEN 'manager'
1347 WHEN 90
1348 THEN 'administrator'
1349 END AS access_level
1350 */
1351 INSERT INTO mantis_project_user_list_table (project_id, user_id, access_level)
1352 SELECT ListProjetDroit.project_id, ListProjetDroit.user_id, ListProjetDroit.access_level
1353 FROM ListProjetDroit
1354 LEFT JOIN mantis_project_user_list_table
1355 ON mantis_project_user_list_table.user_id = ListProjetDroit.user_id
1356 AND mantis_project_user_list_table.project_id = ListProjetDroit.project_id
1357 WHERE mantis_project_user_list_table.project_id IS NULL;
1358
1359
1360 DROP TABLE ListProjetDroit;
1361
1362END //
1363
1364delimiter ;DELIMITER //
1365
1366drop procedure if exists USP_STAT_MANTIS//
1367
1368CREATE PROCEDURE USP_STAT_MANTIS (IN _DateStart DATETIME)
1369BEGIN
1370
1371 SELECT UVW_MANTIS_TEXT.*
1372 , mantis_project_table.name as Project
1373 , mantis_category_table.name as Category
1374 , mantis_bug_table.summary
1375 , STR_TO_DATE(FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.creation, '%d/%m/%Y %H:%i'), '%d/%m/%Y %H:%i') as creation
1376 , STR_TO_DATE(FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.affected, '%d/%m/%Y'), '%d/%m/%Y') as affected
1377 , STR_TO_DATE(FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.delivered, '%d/%m/%Y'), '%d/%m/%Y') as delivered
1378 , STR_TO_DATE(FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.closed, '%d/%m/%Y'), '%d/%m/%Y') as closed
1379 , datediff(FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.delivered, '%Y-%m-%d %H:%i'), FROM_UNIXTIME(UVW_MANTIS_STATUS_CHANGE_DATE.creation, '%Y-%m-%d %H:%i')) AS Resolve_Day
1380 FROM UVW_MANTIS_STATUS_CHANGE_DATE
1381 INNER JOIN mantis_bug_table
1382 ON mantis_bug_table.id = UVW_MANTIS_STATUS_CHANGE_DATE.ID
1383 INNER JOIN UVW_MANTIS_TEXT
1384 ON UVW_MANTIS_TEXT.ID = UVW_MANTIS_STATUS_CHANGE_DATE.ID
1385 INNER JOIN mantis_project_table
1386 ON mantis_project_table.ID = mantis_bug_table.project_id
1387 INNER JOIN mantis_category_table
1388 ON mantis_category_table.ID = mantis_bug_table.category_id
1389
1390 WHERE unix_timestamp(_DateStart) < UVW_MANTIS_STATUS_CHANGE_DATE.creation
1391 OR unix_timestamp(_DateStart) < UVW_MANTIS_STATUS_CHANGE_DATE.delivered;
1392END //
1393
1394delimiter ;-- Si _close = 1 on ferme les mantis qui ont le tag To_Close
1395-- Si _close = 0 on affiche combien de mantis ont le tag To_Close
1396DELIMITER //
1397
1398drop procedure if exists USP_TO_CLOSE //
1399
1400CREATE PROCEDURE USP_TO_CLOSE(IN _Mantis_id INT, IN _IS_Just_Select BIT)
1401
1402BEGIN
1403
1404CREATE TEMPORARY TABLE IF NOT EXISTS temptable AS
1405(
1406 SELECT distinct mantis_bug_table.id
1407 FROM mantis_bug_table
1408 LEFT JOIN mantis_bug_tag_table
1409 inner join mantis_tag_table
1410 ON mantis_bug_tag_table.tag_id = mantis_tag_table.id
1411 AND mantis_tag_table.name = 'toclose'
1412 ON mantis_bug_table.id = mantis_bug_tag_table.bug_id
1413 WHERE (mantis_bug_tag_table.bug_id IS NOT NULL
1414 OR mantis_bug_table.id = _Mantis_id)
1415 AND mantis_bug_table.status <> 90
1416);
1417
1418SELECT mantis_bug_table.ID
1419 , mantis_project_table.name as projet
1420 , mantis_category_table.name as category
1421 , UVW_MANTIS_TEXT.status as status
1422 , mantis_bug_table.summary
1423FROM temptable
1424inner join mantis_bug_table
1425 ON mantis_bug_table.ID = temptable.ID
1426inner join mantis_project_table
1427 on mantis_bug_table.project_id = mantis_project_table.id
1428inner join mantis_category_table
1429 ON mantis_bug_table.category_id = mantis_category_table.id
1430inner join UVW_MANTIS_TEXT
1431 ON UVW_MANTIS_TEXT.ID = mantis_bug_table.ID;
1432
1433IF _IS_Just_Select = 0 THEN
1434
1435 -- ----------------------------------------------------------------------------------------
1436 -- Si on a un Mantis à fermer, on ajoute la balise pour plus tard
1437 -- ----------------------------------------------------------------------------------------
1438 if _Mantis_id > 0 THEN
1439 INSERT INTO mantis_bug_tag_table (bug_id, tag_id, user_id, date_attached)
1440 SELECT _Mantis_id, mantis_tag_table.id, 1, UNIX_TIMESTAMP(NOW())
1441 FROM mantis_tag_table
1442 LEFT JOIN mantis_bug_tag_table
1443 ON mantis_bug_tag_table.tag_id = mantis_tag_table.id
1444 AND mantis_bug_tag_table.bug_id = _Mantis_id
1445 WHERE mantis_tag_table.name = 'toclose'
1446 AND mantis_bug_tag_table.bug_id IS NULL;
1447 END IF;
1448
1449 -- ----------------------------------------------------------------------------------------
1450 -- On ferme le mantis avec 'ToClose'
1451 -- ----------------------------------------------------------------------------------------
1452 INSERT INTO mantis_bug_history_table (user_id, bug_id, field_name, old_value, new_value, type, date_modified)
1453 SELECT DISTINCT 1, temptable.ID, 'status', mantis_bug_table.status, 90, 0, UNIX_TIMESTAMP(NOW())
1454 FROM temptable
1455 INNER JOIN mantis_bug_table
1456 ON mantis_bug_table.ID = temptable.ID;
1457
1458 UPDATE mantis_bug_table, temptable
1459 SET status = 90
1460 WHERE mantis_bug_table.id = temptable.id;
1461
1462 DELETE mantis_bug_history_table
1463 FROM mantis_bug_history_table
1464 INNER JOIN mantis_bug_table
1465 ON mantis_bug_table.ID = mantis_bug_history_table.BUG_ID
1466 WHERE type IN (25 , 26)
1467 AND old_value IN ('ToClose')
1468 AND status = 90;
1469
1470 -- ----------------------------------------------------------------------------------------
1471 -- On ferme tous les mantis Resolu-livré sans mise-à jour dans les 6 derniers mois
1472 -- En commentaire depuis plus d'un an
1473 -- ----------------------------------------------------------------------------------------
1474 CREATE TEMPORARY TABLE IF NOT EXISTS TmpListeBugStatus AS
1475 (
1476 SELECT ID, status
1477 FROM mantis_bug_table
1478 WHERE (last_updated < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 6 MONTH))
1479 and status = 80)
1480 OR
1481 (last_updated < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH))
1482 and status = 20)
1483 );
1484
1485 INSERT INTO TmpListeBugStatus
1486 SELECT mantis_bug_table.ID, mantis_bug_table.status
1487 FROM mantis_bug_tag_table, mantis_bug_table
1488 WHERE mantis_bug_table.id = mantis_bug_tag_table.bug_id
1489 AND mantis_bug_tag_table.tag_id = (
1490 SELECT mantis_tag_table.id FROM mantis_tag_table WHERE mantis_tag_table.name = 'toclose')
1491 and status <> 90;
1492
1493
1494 INSERT INTO mantis_bug_history_table (user_id, bug_id, field_name, old_value, new_value, type, date_modified)
1495 SELECT DISTINCT 1, ID, 'status', status, 90, 0, UNIX_TIMESTAMP(NOW())
1496 FROM TmpListeBugStatus;
1497
1498 update mantis_bug_table, TmpListeBugStatus
1499 set mantis_bug_table.status = 90
1500 WHERE TmpListeBugStatus.ID = mantis_bug_table.ID;
1501END IF;
1502
1503DROP TABLE temptable;
1504
1505END //
1506
1507delimiter ;DELIMITER //
1508
1509drop procedure if exists USP_UPDATE_TARGET_VERSION_ALL//
1510
1511CREATE PROCEDURE USP_UPDATE_TARGET_VERSION_ALL()
1512BEGIN
1513
1514UPDATE mantis_bug_table
1515SET target_version = fixed_in_version
1516WHERE target_version = "" and fixed_in_version <> "";
1517
1518END //
1519
1520delimiter ;DELIMITER //
1521
1522drop procedure if exists USP_UPDATE_TARGET_VERSION_ROW//
1523
1524CREATE PROCEDURE USP_UPDATE_TARGET_VERSION_ROW(IN _id INT)
1525BEGIN
1526
1527UPDATE mantis_bug_table
1528SET target_version = fixed_in_version
1529WHERE ID = _id;
1530
1531END //
1532
1533delimiter ;drop view if exists UVW_MANTIS_STATUS_CHANGE_DATE ;
1534
1535CREATE view UVW_MANTIS_STATUS_CHANGE_DATE
1536AS
1537 SELECT mantis_bug_table.id
1538 , mantis_bug_table.date_submitted as creation
1539 , min(case when NEW_VALUE > 10 and NEW_VALUE <= 50 THEN date_modified ELSE NULL END) as affected
1540 , min(case when NEW_VALUE = 52 THEN date_modified ELSE NULL END) as totest
1541 , min(case when NEW_VALUE = 52 THEN date_modified ELSE NULL END) as todeliver
1542 , min(case when NEW_VALUE >= 80 THEN date_modified ELSE NULL END) as delivered
1543 , max(case when NEW_VALUE = 90 THEN date_modified ELSE NULL END) as closed
1544
1545 FROM mantis_bug_table
1546 LEFT JOIN mantis_bug_history_table
1547 ON mantis_bug_history_table.BUG_ID = mantis_bug_table.ID
1548 AND mantis_bug_history_table.FIELD_NAME = 'status'
1549 AND mantis_bug_history_table.NEW_VALUE > 10
1550
1551 GROUP BY mantis_bug_table.id;
1552
1553drop view if exists UVW_MANTIS_TEXT ;
1554
1555CREATE view UVW_MANTIS_TEXT
1556AS
1557 SELECT mantis_bug_table.ID
1558 , CASE mantis_bug_table.status
1559 WHEN 10 then 'new'
1560 WHEN 20 THEN 'Commentaire'
1561 WHEN 30 THEN 'Résolu en attente'
1562 WHEN 40 THEN 'Specifications'
1563 WHEN 50 THEN 'Affecté'
1564 WHEN 51 THEN 'Ouvert'
1565 WHEN 52 THEN 'A tester'
1566 WHEN 54 THEN 'A livrer'
1567 WHEN 80 THEN 'Livré'
1568 WHEN 90 THEN 'Fermé'
1569 END AS status
1570 , CASE mantis_bug_table.priority
1571 WHEN 10 THEN 'Aucune'
1572 WHEN 20 THEN 'Basse'
1573 WHEN 30 THEN 'Normale'
1574 WHEN 40 THEN 'Elevée'
1575 WHEN 50 THEN 'Urgente'
1576 WHEN 60 THEN 'Immédiate'
1577 END AS priority
1578 , CASE mantis_bug_table.severity
1579 WHEN 10 THEN 'Integration'
1580 WHEN 15 THEN 'Regularisation'
1581 WHEN 20 THEN 'Amélioration'
1582 WHEN 40 THEN 'Cosmétique'
1583 WHEN 60 THEN 'Anomalie'
1584 WHEN 70 THEN 'Installation'
1585 WHEN 80 THEN 'Formation'
1586 END AS severity
1587 , CASE mantis_bug_table.view_state
1588 WHEN 10 THEN 'Public'
1589 WHEN 50 THEN 'Privé'
1590 END AS view_state
1591 FROM mantis_bug_table;