CREATE PROCEDURE dbo.OwnerGetNumberOfPax @AFromDate DATETIME, @AToDate DATETIME, @ARoomNumber INT, @APax INT OUTPUT AS
SET NOCOUNT ON
/*
  Changes:
    20110908: Ext: Added different ways to calculate the number of pax req #900+#1005 (otfpUnknown, otfpHouseOwner, otfpNotHouseOwner, otfpAll)
    20110530: Bug: Use departure date to determine if the res should be counted or not
    20110526: Bug: Fix for "Enddate counted in 2 periods"
    20101228: Bug: Fix for "Number of pax wrong"
*/

DECLARE @FromDateStr CHAR(8)
DECLARE @ToDateStr CHAR(8)
DECLARE @PaxCalcType INT
DECLARE @HotelCustomerNumber INT

SET @FromDateStr = CONVERT(CHAR(8), @AFromDate, 112)
SET @ToDateStr = CONVERT(CHAR(8), @AToDate, 112)

SELECT @HotelCustomerNumber = Customer.CustomerNumber 
FROM Customer WITH (NOLOCK)
INNER JOIN Rooms WITH (NOLOCK) ON Rooms.HotelNumber = Customer.HotelNumber
  AND CustomerTypeNumber = 7
  AND Rooms.RoomNumber = @ARoomNumber

SELECT @PaxCalcType = dbo.GetHotelSetupKeyValue ('OwnerTicketFeePaxCalculationType', @HotelCustomerNumber)

DECLARE @tmpCount INT
SET @APax = 0

IF ISNULL(@PaxCalcType, 0) = 0 OR @PaxCalcType = 2 -- otfpUnknown or otfpNotHouseOwner(default)
BEGIN
  DECLARE db_cur_pax CURSOR FOR
    SELECT 
    CASE WHEN Reservations.GuestNumber = 0 THEN
      SUM(ReservationRoomLine.Adults)+SUM(ReservationRoomLine.Children1)+SUM(ReservationRoomLine.Children2)
    ELSE 
      SUM(Reservations.Adults)+SUM(Reservations.Children1)+SUM(Reservations.Children2)
    END AS Pax
    FROM Reservations WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber 
      AND Rooms.RoomNumber = @ARoomNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber 
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber 
      AND AddressType = 1
      AND ISNULL(ReservationAddress.Link, -1) <> Rooms.CustomerNumber 
    WHERE (1=1) 
    AND ((Reservations.GuestNumber = 0 AND ReservationRoomLine.DepartureDate>=@FromDateStr AND ReservationRoomLine.DepartureDate < @ToDateStr)
    OR (Reservations.GuestNumber > 0 AND Reservations.DepartureDate>=@FromDateStr AND Reservations.DepartureDate < @ToDateStr))
    AND Reservations.Status <= 6 -- OutofHouse
    AND Reservations.Referencenumber > 0
    GROUP BY Reservations.GuestNumber
END
ELSE IF @PaxCalcType = 1 -- otfpHouseOwner
BEGIN
  DECLARE db_cur_pax CURSOR FOR
    SELECT 
    CASE WHEN Reservations.GuestNumber = 0 THEN
      SUM(ReservationRoomLine.Adults)+SUM(ReservationRoomLine.Children1)+SUM(ReservationRoomLine.Children2)
    ELSE 
      SUM(Reservations.Adults)+SUM(Reservations.Children1)+SUM(Reservations.Children2)
    END AS Pax
    FROM Reservations WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber 
      AND Rooms.RoomNumber = @ARoomNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber 
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber 
      AND AddressType = 1
      AND ISNULL(ReservationAddress.Link, -1) = Rooms.CustomerNumber 
    WHERE (1=1) 
    AND ((Reservations.GuestNumber = 0 AND ReservationRoomLine.DepartureDate>=@FromDateStr AND ReservationRoomLine.DepartureDate < @ToDateStr)
    OR (Reservations.GuestNumber > 0 AND Reservations.DepartureDate>=@FromDateStr AND Reservations.DepartureDate < @ToDateStr))
    AND Reservations.Status <= 6 -- OutofHouse
    AND Reservations.Referencenumber > 0
    GROUP BY Reservations.GuestNumber
END
ELSE IF @PaxCalcType = 3 -- otfpAll
BEGIN
  DECLARE db_cur_pax CURSOR FOR
    SELECT 
    CASE WHEN Reservations.GuestNumber = 0 THEN
      SUM(ReservationRoomLine.Adults)+SUM(ReservationRoomLine.Children1)+SUM(ReservationRoomLine.Children2)
    ELSE 
      SUM(Reservations.Adults)+SUM(Reservations.Children1)+SUM(Reservations.Children2)
    END AS Pax
    FROM Reservations WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber 
      AND Rooms.RoomNumber = @ARoomNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber 
      AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber 
      AND AddressType = 1
    WHERE (1=1) 
    AND ((Reservations.GuestNumber = 0 AND ReservationRoomLine.DepartureDate>=@FromDateStr AND ReservationRoomLine.DepartureDate < @ToDateStr)
    OR (Reservations.GuestNumber > 0 AND Reservations.DepartureDate>=@FromDateStr AND Reservations.DepartureDate < @ToDateStr))
    AND Reservations.Status <= 6 -- OutofHouse
    AND Reservations.Referencenumber > 0
    GROUP BY Reservations.GuestNumber
END
  
OPEN db_cur_pax
FETCH NEXT FROM db_cur_pax INTO @tmpCount
 
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @APax = @APax + ISNULL(@tmpCount, 0)
  FETCH NEXT FROM db_cur_pax INTO @tmpCount
END
 
CLOSE db_cur_pax
DEALLOCATE db_cur_pax