
CREATE PROCEDURE dbo.HallBlocksAllTables @AStartDateTime datetime, @AEndDateTime datetime, @AHallNumber INT, @Result BIT OUTPUT  AS
--Created AV 2006 1013
/*
This code is for Kolding Fjord. They have a hall which consists of all the tables in the restaurant. So when they book the hall the system should check if any table is booked. If so then give a warning.
And if you book a table the system also checks if the hall is booked and gives a warning.
I know that this proc can be made with only one sql sentence. But it is easier and it gives a better overview with the below IF sentence
*/

DECLARE @Arr24HoursStarts CHAR(5)
SELECT @Arr24HoursStarts = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'

SET @Result = 0

IF @AHallNumber < 1
BEGIN
  -- IN THIS BLOCK WE CHECK IF ANY HALL, WHERE ConferenceRoom,WarnIfTableIsBooked = 1, IS BOOKED
  IF EXISTS(SELECT ReservationConferenceLine.ConferenceRoomNumber FROM ReservationConferenceLine(NOLOCK)
  INNER JOIN ConferenceRooms(NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
  INNER JOIN Reservations(NOLOCK) ON ReservationConferenceLine.ReferenceNumber= Reservations.ReferenceNumber AND Reservations.GuestNumber=0
  WHERE ConferenceRooms.WarnIfTableIsBooked = 1
  AND ISNULL(ReservationConferenceLine.TableBooking,0) =0 
  AND Reservations.Status < 6
  AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < @AEndDateTime
  AND 
  (
    (ReservationConferenceLine.DepartureTime < @Arr24HoursStarts
      AND DATEADD(DAY, -1, CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112)) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime)
    OR
    (ReservationConferenceLine.DepartureTime >= @Arr24HoursStarts
      AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime)
  ))

    SET @Result = 1
END
ELSE
BEGIN
  -- IN THIS BLOCK WE CHECK IF ANY TABLE IS BOOKED
  IF EXISTS(SELECT ReservationConferenceLine.ConferenceRoomNumber FROM ReservationConferenceLine(NOLOCK)
  INNER JOIN ConferenceRooms(NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
  INNER JOIN Reservations(NOLOCK) ON ReservationConferenceLine.ReferenceNumber= Reservations.ReferenceNumber AND Reservations.GuestNumber=0
  WHERE ReservationConferenceLine.TableBooking = 1
  AND Reservations.Status < 6
  AND ReservationConferenceLine.TableStatus < 5
  AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < @AEndDateTime
  AND 
  (
    (ReservationConferenceLine.DepartureTime < @Arr24HoursStarts 
      AND DATEADD(DAY, -1, CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112)) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime)
    OR
    (ReservationConferenceLine.DepartureTime >= @Arr24HoursStarts
      AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime)
  ))
   SET @Result = 1
END