CREATE PROCEDURE [dbo].[GetUsedPaxOnProduct] 
	-- Add the parameters for the stored procedure here
	@ProductNumber int, @ArrivalDate datetime, @DepartureDate datetime, @HotelNumbers VARCHAR(50), @RoomLineNumber int, @AvailablePax int OUTPUT, @UsedRooms int OUTPUT, @MaxPax int OUTPUT, @MaxRooms int OUTPUT
AS

-- 20101214/KJ/RN: Rewrite of procedure because of namelist count problem....
-- 20091214/RN: SET @UsedCount = 0 moved outside WHILE loop of course
-- 20091214/RN: Now adding 1 day to @FromDate in while loop and not to @ArrivalDate, Ooops !
-- 20091214/RN: Added multiply with NumberOfRooms.
-- 20091208/RN: Now returns available pax and disregarding @RoomLineNumber in the query so RoomGrid in Reception works properly
-- 20091208/RN: Subtracts a day from departuredate, we should not count pax/rooms on departuredate.
-- 20091207/RN: Added use of cursor
-- 20091123/RN: Initial version

BEGIN
  DECLARE @FromDate VARCHAR(8)
  DECLARE @ToDate VARCHAR(8)
  DECLARE @RoomCount int
  DECLARE @UsedCount int
  DECLARE @tmpAdults int

  DECLARE @Adults int
  DECLARE @Children1 int
  DECLARE @Children2 int
  DECLARE @NumberOfDays int
  DECLARE @DoUsePax int

  SELECT 
    @MaxPax = MaxPax, @MaxRooms = MaxRooms, @DoUsePax = UseMaxPax
  FROM Products 
  WHERE ProductNumber = @ProductNumber

  SET @AvailablePax = 0

  IF @DoUsePax = 1
  BEGIN

  SELECT @NumberOfDays = DATEDIFF(day,@ArrivalDate,DATEADD(dd,-1,@DepartureDate))

  SET @AvailablePax = 9999
  SET @FromDate = CONVERT(CHAR(8),@ArrivalDate, 112)
  SET @ToDate = @FromDate
  
  SET @UsedCount = 0
  SET @RoomCount = 0
  SET @UsedRooms = 0

  WHILE (@NumberOfDays >= 0)
  BEGIN
    SET @Adults = 0

    DECLARE db_cur CURSOR FOR
      SELECT 
        CASE WHEN Reservations.GuestNumber = 0 THEN 
          SUM(ISNULL(ReservationRoomLine.NumberOfRooms, 1) * (ISNULL(ReservationRoomLine.Adults,0) + ISNULL(ReservationRoomLine.Children1,0)+ ISNULL(ReservationRoomLine.Children2,0))) 
        ELSE 
          SUM(ISNULL(Reservations.Adults,0) + ISNULL(Reservations.Children1,0) + ISNULL(Reservations.Children2,0)) 
      END
    FROM RESERVATIONS (NOLOCK) 
    LEFT OUTER JOIN ReservationRoomLineXTab (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND  ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber 
    LEFT OUTER JOIN ReservationRoomline (NOLOCK) ON ReservationRoomline.LINENUMBER  = RESERVATIONROOMLINEXTAB.LINENUMBER 
    LEFT OUTER JOIN RoomTypes (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE
    (
      (
        Reservations.GuestNumber = 0
        AND (ReservationRoomLine.DepartureDate > @FromDate AND ReservationRoomLine.ArrivalDate <= @ToDate) 
      )
      OR 
      (
        Reservations.GuestNumber > 0
        AND (Reservations.DepartureDate > @FromDate AND Reservations.ArrivalDate <= @ToDate) 
      )
    )
    AND Reservations.ReferenceNumber > 0
    AND Reservations.Status < 7
    AND ISNULL(RoomTypes.HotelNumber, -1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumbers))
    AND
    (
      ( 
        Reservations.GuestNumber = 0
        AND (ReservationRoomLine.ProductNumber = @ProductNumber OR ReservationRoomLine.ProductNumber2 = @ProductNumber OR ReservationRoomLine.ProductNumber3 = @ProductNumber OR ReservationRoomLine.ProductNumber4 = @ProductNumber)
      )
      OR
      (
        Reservations.GuestNumber > 0
        AND (Reservations.ProductNumber = @ProductNumber OR Reservations.ProductNumber2 = @ProductNumber OR Reservations.ProductNumber3 = @ProductNumber OR Reservations.ProductNumber4 = @ProductNumber)
      )
    )
      AND ReservationRoomLineXTab.LineNumber <> @RoomLineNumber
      GROUP BY Reservations.GuestNumber
    OPEN db_cur
    
    FETCH NEXT FROM db_cur INTO @tmpAdults
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @Adults = @Adults + @tmpAdults
      FETCH NEXT FROM db_cur INTO @tmpAdults
    END
    CLOSE db_cur
    DEALLOCATE db_cur
    
    IF @UsedCount < @Adults
      SET @UsedCount = @Adults

    SET @FromDate = @FromDate + 1
    SET @ToDate = @FromDate
    SET @NumberOfDays = (@NumberOfDays -1)

  END -- WHILE @NumberOfDays...

  SET @AvailablePax = @MaxPax - @UsedCount
  END
END