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
| CREATE PROCEDURE BIBPROCSQL.proc_SuiviTempsCODIR (IN iAnnee_EXC integer, IN iMois_Select integer)
DYNAMIC RESULT SETS 2
LANGUAGE SQL
MODIFIES SQL DATA
P1: BEGIN
-- Déclaration des variables
DECLARE iCompteur INT DEFAULT 0;
DECLARE iNbLignes BIGINT DEFAULT 0;
-- Déclaration des variables Ressources
DECLARE dTempsTotal_Lor DOUBLE DEFAULT 0;
DECLARE dTempsTotal_Lor_Full DOUBLE DEFAULT 0;
DECLARE dTempsTotal_Par DOUBLE DEFAULT 0;
DECLARE dTempsTotal_Par_Full DOUBLE DEFAULT 0;
DECLARE dTempsTotal_INRS DOUBLE DEFAULT 0;
DECLARE dTempsTotal_INRS_Full DOUBLE DEFAULT 0;
DECLARE dTemps DOUBLE DEFAULT 0;
DECLARE sEntite varchar(10);
DECLARE sActivite varchar(2);
--------------------------------------
------ Déclaration des curseurs ------
--------------------------------------
DECLARE c1 CURSOR FOR
SELECT SUBSTRING(A.OBGCOD, 1, 2), REPLACE(REPLACE(B.DCTENT,'N','Lorraine'),'P','Paris'), SUM(B.DCTCHEU)
FROM SCODSPEFIC.XXOCOM1 A, INRSSPEFIC.DCTHISTOD B WHERE B.DCTEXC = iAnnee_EXC AND SUBSTRING(A.OBGCOD, 1, 2) = '66'
AND B.DCTMOI <= iMois_Select AND A.COCOMP = B.DCTNCA
GROUP BY SUBSTRING(A.OBGCOD, 1, 2), B.DCTENT ORDER BY 1,2;
-- Curseur pour récupérer les données de la table RESSOURCES_CODIR
DECLARE c3 CURSOR WITH RETURN FOR
SELECT A.ENTITE, A.ACTIVITE, A.TEMPS, (DECIMAL(A.POURCENTAGE,4,2) || ' %') AS POURCENTAGE
FROM BIBPROCSQL.RESSOURCES_CODIR A ORDER BY A.ENTITE Asc;
--------------------------------------------------
------ Recherche des Ressources (en heures) ------
--------------------------------------------------
-- 1) Lorraine
SELECT SUM(B.DCTCHEU) into dTempsTotal_Lor FROM SCODSPEFIC.XXOCOM1 A, INRSSPEFIC.DCTHISTOD B
WHERE B.DCTEXC = iAnnee_EXC AND SUBSTRING(A.OBGCOD, 1, 2) = '66' AND A.COCOMP = B.DCTNCA AND B.DCTENT = 'N';
SELECT SUM(B.DCTCHEU) into dTempsTotal_Lor_Full FROM SCODSPEFIC.XXOCOM1 A, INRSSPEFIC.DCTHISTOD B
WHERE B.DCTEXC = iAnnee_EXC AND SUBSTRING(A.OBGCOD, 1, 1) = '6' AND A.COCOMP = B.DCTNCA AND B.DCTENT = 'N';
-- 2) Paris
SELECT SUM(B.DCTCHEU) into dTempsTotal_Par FROM SCODSPEFIC.XXOCOM1 A, INRSSPEFIC.DCTHISTOD B
WHERE B.DCTEXC = iAnnee_EXC AND SUBSTRING(A.OBGCOD, 1, 2) = '66' AND A.COCOMP = B.DCTNCA AND B.DCTENT = 'P';
SELECT SUM(B.DCTCHEU) into dTempsTotal_Par_Full FROM SCODSPEFIC.XXOCOM1 A, INRSSPEFIC.DCTHISTOD B
WHERE B.DCTEXC = iAnnee_EXC AND SUBSTRING(A.OBGCOD, 1, 1) = '6' AND A.COCOMP = B.DCTNCA AND B.DCTENT = 'P';
-- 3) INRS
SET dTempsTotal_INRS = dTempsTotal_Lor + dTempsTotal_Par;
SET dTempsTotal_INRS_Full = dTempsTotal_Lor_Full + dTempsTotal_Par_Full;
-- On supprime au préalable les enregistrements dans la table RESSOURCES_CODIR
SELECT count(*) into iNbLignes FROM BIBPROCSQL.RESSOURCES_CODIR A where SUBSTRING(A.ACTIVITE, 1, 1) = '6';
IF iNbLignes > 0 THEN
DELETE FROM BIBPROCSQL.RESSOURCES_CODIR A where SUBSTRING(A.ACTIVITE, 1, 1) = '6';
COMMIT;
END IF;
-- Ouverture du curseur c1
OPEN c1;
SET iCompteur = 0;
-- boucle de traitement
WHILE iCompteur < 2 DO
FETCH c1 INTO sActivite, sEntite, dTemps;
INSERT INTO BIBPROCSQL.RESSOURCES_CODIR (ACTIVITE,ENTITE,TEMPS,POURCENTAGE)
VALUES (sActivite,sEntite,ROUND(dTemps,2),(dTemps/dTempsTotal_INRS*100));
SET iCompteur = iCompteur + 1;
END WHILE;
--Fermeture du curseur c1
CLOSE c1;
-- On commit
COMMIT;
-- Ouverture du curseur c3
OPEN c3;
END P1 |
