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
|
CREATE FUNCTION reglementSejour(idResa integer)
RETURNS numeric(l0,2)
AS $codeFonction$
DECLARE
numCli integer ;
montantDejaPaye numeric(l0,2) ;
BEGIN
SELECT idClient INTO numCli
FROM reservation
WHERE idReservation = idResa ;
SELECT SUM(montantReglement) INTO montantDejaPaye
FROM reglement
WHERE idReservation = idResa ;
IF (montantDejaPaye IS NULL ) THEN
montantDejaPaye = 0 ;
END IF ;
IF ((SELECT COUNT(*) FROM pro WHERE idClient = numCli)=l) THEN
RETURN(SELECT SUM(prix * duree) - montantDejaPaye
FROM contenir
JOIN chambre USING (numeroChambre)
JOIN concerner USING (numeroChambre,idReservation)
WHERE dateDebut >= CURRENT_DATE
AND idReservation = idResa
)
;
ELSE
RETURN (SELECT SUM(prix * duree)
FROM contenir
JOIN chambre USING (numeroChambre)
JOIN concerner USING (numeroChambre,idReservation)
WHERE idReservation = idResa
) ;
END IF ;
END ;
$codeFonction$ LANGUAGE PLPGSQL ; |
