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
|
Set Term ^;
CREATE PROCEDURE MAJ_TTT_SEANCES(
DATE_S DATE,
IDX_SEANCE VARCHAR(5),
IDX_MOIS VARCHAR(5) )
AS DECLARE VARIABLE NB INTEGER;
etc... etc ..
DECLARE VARIABLE MOIS VARCHAR(12);
DECLARE VARIABLE AN CHAR(1);
BEGIN /* PROCEDURE */
DELETE FROM TTT_PROV;
/*=================== INSERTION DES TRAITEMENTS MOIS ET ANNEES =======================*/
FOR
SELECT DISTINCT
D1.IUPATM,
D.DATESEANCE ,
D1.IDXMEDIC,
D1.POSOPPRISE,
D1.NBPRISE,
null AS VALIDATION,
D1.LIEU,
D1.MOIS,
D1.AN
FROM
SEANCES D,
TTT_PRESCR D1
WHERE
((D1.MOIS LIKE :IDX_MOIS) OR (D1.AN = '1' ))
AND(D.DATESEANCE = :DATE_S)
AND (D1.IUPATM = D.IUPATM)
AND (D1.DATEDEB <= D.DATESEANCE)
AND ((D1.DATEFIN >= D.DATESEANCE) OR (D1.DATEFIN IS NULL))
AND ((D1.LIEU = 'H') OR (D1.LIEU = 'S'))
INTO
:IUPATM,
:DATESEANCE ,
:IDXMEDIC,
:POSOPPRISE,
:NBPRISE,
:VALIDATION,
:LIEU,
:MOIS,
:AN
DO /* FOR */
BEGIN
DATE_DEB_AN = CAST('01/01/'||EXTRACT(YEAR FROM :DATE_S) AS DATE);
DATE_DEB_MOIS = CAST(EXTRACT(MONTH FROM :DATE_S)||'/01/'||EXTRACT(YEAR FROM :DATE_S) AS DATE);
INTER_AN = (360/:NBPRISE)-1;
INTER_MOIS = (30/:NBPRISE)-1;
NB = :NBPRISE;
WHILE (NB > 0) DO
BEGIN /* WHILE */
IF (:AN IS NULL) THEN
BEGIN
IF (DATE_DEB_MOIS + INTER_MOIS*(NB-1) <= DATESEANCE ) THEN
BEGIN
INSERT INTO TTT_PROV (
IUPATM,
DATESEANCE,
IDXMEDIC,
POSOPPRISE,
NBPRISE,
VALIDATION,
LIEU)
VALUES(
:IUPATM,
(:DATE_DEB_MOIS + :INTER_MOIS*(:NB-1)),
:IDXMEDIC,
:POSOPPRISE,
:NBPRISE,
:VALIDATION,
:LIEU);
END
END
ELSE
BEGIN
IF (DATE_DEB_AN + INTER_AN*(NB-1) <= DATESEANCE ) THEN
BEGIN
INSERT INTO TTT_PROV (
IUPATM,
DATESEANCE,
IDXMEDIC,
POSOPPRISE,
NBPRISE,
VALIDATION,
LIEU)
VALUES(
:IUPATM,
(:DATE_DEB_AN + :INTER_AN*(:NB-1)),
:IDXMEDIC,
:POSOPPRISE,
:NBPRISE,
:VALIDATION,
:LIEU);
END
END
NB = NB-1;
END /* ENDWHILE */
END /* FOR */
etc ... etc.. |
Partager