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
| create or replace trigger trigger_creation_facture
after insert or update on reservation
for each row
declare
IDFACTURE number;
Exist_deja number;
REMISE number;
FACTURETOTALE number;
DATEFACTURE date;
DATEDEPART date;
MOISDEPART varchar2(10);
TARIFADULTE number;
TARIFENFANT number;
NBADULTE number;
NBENFANT number;
FACTUREAVION number;
DUREE number;
TARIFCHS number;
TARIFCHD number;
TARIFLITSUP number;
NBCHS number;
NBCHD number;
NBLITS number;
CATEGORIEHOTEL number;
FACTUREHOTEL number;
TARIFCIRCUIT number;
NOM varchar2(20);
PRENOM varchar2(20);
ADRESSE varchar2(20);
TELEPHONE number;
BEGIN
FACTUREAVION:=0;
FACTUREHOTEL:=0;
FACTURETOTALE:=0;
REMISE:=0;
Exist_deja:=0;
IDFACTURE:= :NEW.ID_RESERVATION;
Select id_facturation into Exist_deja
from facturation
where id_facturation=IDFACTURE;
If(Exist_deja<>0)then
Delete from facturation where id_facturation=Exist_deja;
end if;
DATEFACTURE:=CURRENT_DATE();
select nom,prenom,adresse,telephone into NOM,PRENOM,ADRESSE,TELEPHONE from client c,reservation r
where r.id_reservation=IDFACTURE and r.id_client=c.ID_CLIENT;
select datevol into DATEDEPART from vol v,reservation r
where r.id_reservation=IDFACTURE AND r.id_vol=v.ID_VOL;
MOISDEPART:= TRUNC(DATEDEPART,'MM');
If(MOISDEPART<>'7' and MOISDEPART<>'8') then
REMISE:=10;
End if;
Select tarifadulte, tarifenfant into TARIFADULTE,TARIFENFANT from vol v,reservation r
where r.id_reservation=IDFACTURE and r.id_vol=v.ID_VOL;
Select nbadulte, nbenfant, duree, nbchambresimple, nbchambredouble, nblitsup
into NBADULTE, NBENFANT,DUREE,NBCHS,NBCHD,NBLITS from RESERVATION r
where r.id_reservation=IDFACTURE;
Select
prixchsimple, prixchdouble, prixlitsup into TARIFCHS,TARIFCHD,TARIFLITSUP from reservation r, circuit c,hotel h
where r.ID_RESERVATION=IDFACTURE and r.id_circuit=c.ID_CIRCUIT and c.id_etape1=h.id_etape;
Select prixcircuit into TARIFCIRCUIT from circuit c, reservation r where r.ID_RESERVATION=IDFACTURE and r.id_circuit=c.ID_CIRCUIT;
FACTUREAVION:=NBADULTE*TARIFADULTE+NBENFANT*TARIFENFANT;
FACTUREHOTEL:=DUREE*(TARIFCHS*NBCHS+TARIFCHD*NBCHD+TARIFLITSUP*NBLITS);
FACTURETOTALE := (FACTUREAVION+FACTUREHOTEL+TARIFCIRCUIT)*(1-REMISE/100);
insert into FACTURATION (
id_facturation, datefacturation , favion, fhotel, facturetotale ,remise , nbadultef , nbenfantf , tarifadultevol, tarifenfantvol , nbchdoublef , nbchsimplef ,nblitsupf , tarifchdoublef , tarifchsimplef ,tariflitsupf ,tarifcircuitf,
nomclient, prenomclient, adresseclientf, telephoneclientf, id_reservation)
values
(IDFACTURE, DATEFACTURE, FACTUREAVION, FACTUREHOTEL, FACTURETOTALE, REMISE,
NBADULTE,NBENFANT ,TARIFADULTE , TARIFENFANT,NBCHD ,NBCHS ,NBLITS , TARIFCHD, TARIFCHS, TARIFLITSUP, TARIFCIRCUIT , NOM ,PRENOM ,ADRESSE ,TELEPHONE, IDFACTURE) ;
UPDATE reservation SET id_facturation=IDFACTURE;
END; |
