CREATE PROCEDURE [dbo].[GetClimbingSchedulePaxInfo] 
  @AHallNumber INT
  ,@AHallLineID INT
  ,@AStartDateTime datetime
  ,@ATimeIntervalInMinutes INT
  ,@AAvailablePax INT OUTPUT
  ,@AAvailableStartingPax INT OUTPUT
AS

-- Procedure returns the number of pax on a hall in a given time interval
-- Initial use is for f.ex FunkyMonkyPark where we need to check pax in a 30 minute interval and also on a 3 hour interval

-- Calls: GetSchedulerExpressData

-- 20160119/SG: Understøtter sammenlagte halls
-- 20160113/SG: Error when ooo lasted more than one day
-- 20140610/SG: Added WITH (NOLOCK) in ooo-section
-- 20140429/SG: Added Out Of Order
-- 20140407/RN: Initial version.

BEGIN
  DECLARE @ArrivalDate CHAR(8)
  DECLARE @ArrivalTime CHAR(5)
  DECLARE @DepartureTime CHAR(5)
  DECLARE @DepartureDate CHAR(8)
  DECLARE @NumberOfPax INT
  DECLARE @NumberOfStartingPax INT
  
  DECLARE @HallTypeNumber INT
  DECLARE @Found INT
  DECLARE @TotalNumberOfPax INT
  DECLARE @MaxStartingPax INT
  DECLARE @HallOpeningDatetime DATETIME
  DECLARE @HallClosingDatetime DATETIME
  
  SET @ArrivalDate = CONVERT(CHAR(8), @AStartDateTime, 112)
  SET @ArrivalTime = CONVERT(CHAR(5), @AStartDateTime, 108)
  SET @DepartureDate = CONVERT(CHAR(8), @AStartDateTime, 112)
  SET @DepartureTime = CONVERT(CHAR(5), DATEADD(minute, (@ATimeIntervalInMinutes - 1) ,@AStartDateTime), 108)
  
  SELECT @HallTypeNumber = ConferenceRoomTypeNumber FROM ConferenceRooms (NOLOCK) WHERE ConferenceRoomNumber = @AHallNumber
    
  
  EXEC GetSchedulerExpressDataWithPAX2 @AStartDateTime
    , @HallTypeNumber
	, @Found OUTPUT
	, @TotalNumberOfPax OUTPUT
	, @HallOpeningDatetime OUTPUT
	, @HallClosingDatetime OUTPUT
	, @MaxStartingPax OUTPUT

  IF @MaxStartingPax = 0
    SET @MaxStartingPax = 30
  
  SELECT @NumberOfPax = SUM(ReservationConferenceLine.Adults + ReservationConferenceLine.Children1 + ReservationConferenceLine.Children2)
  FROM ReservationConferenceLine (NOLOCK)
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
    AND Reservations.GuestNumber = ReservationConferenceLine.GuestNo 

  INNER JOIN CONFERENCEROOMSXTAB (NOLOCK) 
  ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms(NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber

  WHERE (1=1)
    AND RESERVATIONCONFERENCELINE.ReservationConfLineNumber <> @AHallLineID
    AND Reservations.Status <= 5
    --AND ReservationConferenceLine.ConferenceRoomNumber = @AHallNumber
	AND ConferenceRoomsXtab.CollectsConferenceRoomNumber = @AHallNumber
    AND RESERVATIONCONFERENCELINE.ArrivalDate = @ArrivalDate
    AND RESERVATIONCONFERENCELINE.ArrivalTime <= @DepartureTime
    AND RESERVATIONCONFERENCELINE.DepartureTime >= @ArrivalTime
      
  SELECT @NumberOfStartingPax = SUM(ReservationConferenceLine.Adults + ReservationConferenceLine.Children1 + ReservationConferenceLine.Children2)
  FROM ReservationConferenceLine (NOLOCK)
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
    AND Reservations.GuestNumber = ReservationConferenceLine.GuestNo 

  INNER JOIN CONFERENCEROOMSXTAB (NOLOCK) 
  ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms(NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber

  WHERE (1=1)
    AND RESERVATIONCONFERENCELINE.ReservationConfLineNumber <> @AHallLineID
    AND Reservations.Status <= 5
    --AND ReservationConferenceLine.ConferenceRoomNumber = @AHallNumber
    AND ConferenceRoomsXtab.CollectsConferenceRoomNumber = @AHallNumber
    AND RESERVATIONCONFERENCELINE.ArrivalDate = @ArrivalDate
    AND RESERVATIONCONFERENCELINE.DepartureDate = @DepartureDate
    AND RESERVATIONCONFERENCELINE.ArrivalTime >= @ArrivalTime
    AND RESERVATIONCONFERENCELINE.ArrivalTime <= @DepartureTime
  
  SET @AAvailablePax = @TotalNumberOfPax - @NumberOfPax
  SET @AAvailableStartingPax = @MaxStartingPax - @NumberOfStartingPax

  IF ISNULL(@NumberOfPax, 0) = 0
    SET @AAvailablePax = @TotalNumberOfPax
  
  IF ISNULL(@NumberOfStartingPax, 0) = 0
    SET @AAvailableStartingPax = @MaxStartingPax

  -- Out Of Order 
  DECLARE @OooCount int;   
  SELECT @OooCount = COUNT(*)
                 FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
                 WHERE ReferenceNumber = 0 --Ooo
          AND ReservationConferenceLine.ConferenceRoomNumber = @AHallNumber
          AND (@ArrivalDate between RESERVATIONCONFERENCELINE.ArrivalDate AND RESERVATIONCONFERENCELINE.DepartureDate
		       OR @DepartureDate between RESERVATIONCONFERENCELINE.ArrivalDate AND RESERVATIONCONFERENCELINE.DepartureDate)
          AND (@ArrivalTime BETWEEN RESERVATIONCONFERENCELINE.ArrivalTime AND RESERVATIONCONFERENCELINE.DepartureTime
               OR @DepartureTime BETWEEN RESERVATIONCONFERENCELINE.ArrivalTime AND RESERVATIONCONFERENCELINE.DepartureTime)
  IF @OooCount > 0
  BEGIN
    Set @AAvailablePax = 0;
    Set @AAvailableStartingPax = 0;
  END

/*    
  PRINT 'ArrivalDate: ' + @ArrivalDate
  PRINT 'ArrivalTime: ' + @ArrivalTime
  PRINT 'DepartureTime: ' + @DepartureTime
  PRINT 'MaxStartingPax: ' + CAST(@MaxStartingPax AS CHAR(5))
  PRINT 'MaxPax: ' + CAST(@TotalNumberOfPax AS CHAR(5))
  PRINT 'NumberOfPax: ' + CAST(@NumberOfPax AS CHAR(5))
  PRINT 'AvailablePax: ' + CAST(@AAvailablePax AS CHAR(5))   
  PRINT 'Avail.Start.Pax: ' + CAST(@AAvailableStartingPax AS CHAR(5)) 
*/
END
