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
| DELIMITER |
Create procedure reservation(IN debut datetime, IN fin datetime, IN type varchar)
begin
set @salles=(SELECT id, id_typesalle
FROM salle
WHERE statut = 1
and id_typesalle = type
and nbplaces >= nbpersonnes
and id not in ( select salle.id
from reservation
join salle on reservation.id_salle=salle.id
where statut = 1
and id_typesalle = type
and ( debut between datedeb and datefin and fin between datedebut and datefin )
or ( debut < datedebut and fin > datefin )
or ( debut < datedebut and fin between datedebut and datefin )
or ( debut between datedebut and datefin and fin > datefin))
limit 1);
if ((@salles is not null) and (debut<fin)) then
insert into reservations (detail, datedebut, datefin, nbpersonnes, id_membres, id_salle)
values (detail, debut, fin, nbpersonnes, id_membres, @salles);
SELECT 1;
ELSE
SELECT 0;
end if;
if (@salles is null ) then
signal sqlstate '45000' set message_text = 'Reservation impossible pour les critères choisis';
end if;
end
| |
Partager