CREATE PROCEDURE [dbo].[HotelManagerNumberOfRooms] @ARundate VARCHAR(8), @AYear VARCHAR(4) = '', @AMonth VARCHAR(2) = '', @AHotelNumberStr VARCHAR(100), @ARunType SMALLINT, 
  @ATotal BIT, @ARemaining BIT, @ATotalNumberOfRooms INT = 0 OUTPUT, @AReportOwnerReservation INT, @AInclNUL BIT, @AInclEmpty BIT, 
  @ATotalNumberOfRoomsRooms INT = 0 OUTPUT, @ATotalNumberOfRoomsBeds INT = 0 OUTPUT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen april 2009

  Remarks:
  @AReportOwnerReservation: 
    0: rorExcluded
    1: rorOnly
    2: rorIncluded
    
  @ARuntype:
    None = 0
    RoomsArriving = 1
    RoomsWalkin = 2
    RoomsNoShow = 3
    RoomsDay = 4
    RoomsUpgraded = 5
    RoomsEarlyDeparture = 6
    RoomsUpgradedDetailed = 7
    
  Changes:
    KJ 20240530: Bug: Fix for "Noshows always = 0". CAse #CR244824
    KJ 20231101: Bug: Fix for "Wrong number of rooms".
    KJ 20180208: Bug: Fix for "Month to date and year to date not showing same numbers". Case #CR141561
    KJ 20171218: Ext: Added Group data by rooms/beds
    KJ 20170629: Bug: Fix for "Wrong number of stays". Case #CR127717
    KJ 20170622: Bug: Fix for "Wrong number of arrival rooms". Case #CR127717
    KJ 20151021: Bug: Fix for "Wrong type of input params"
    KJ 20151012: Bug: Fix for wrong number of no shows. (sometimes followuptype is 5 and sometimes 12. Case #CR093301
    KJ 20130430: Ext: Added incl NUL and Empty products. Req #1530
    KJ 20120430: Ext: Added owner reservations.
    KJ 20100520: Bug: Fix for "Wrong number of dayrooms for month"
    KJ 20090407: Use allocated RoomType if any else use BookedTypeNumber from ReservationRoomLine
*/

SET @AHotelNumberStr = ISNULL(@AHotelNumberStr, '')
SET @AYear = ISNULL(@AYear, '')
SET @AMonth = ISNULL(@AMonth, '')

IF @ARunType = 1
BEGIN
  -- Arrivals
  IF @ATotal = 1
  BEGIN
    SELECT SUM(numberofrooms) AS TotalNumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableArrival
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE LineNumber IN (
    SELECT ReservationRoomLine.LineNumber
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON rrx.LineNumber = ReservationRoomLine.LineNumber
    INNER JOIN Reservations r WITH (NOLOCK) ON rrx.ReferenceNumber = r.ReferenceNumber 
      AND  rrx.GuestNumber = r.GuestNumber 
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (NOLOCK) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber     
    WHERE
    (
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      AND (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
          where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      AND ((@AHotelNumberStr = '') OR (reservationroomline.RoomNumber in (Select RoomNumber from Rooms WITH (nolock) where HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr)))))
      AND reservationroomline.Roomnumber is not null
    ) 
    or 
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      and (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
           INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationRoomLineXtab.ReferenceNumber 
           AND ((@AHotelNumberStr = '') OR (HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
           where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      and reservationroomline.RoomNumber is null
    )
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((Rooms.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND Rooms.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> 0)      
    AND r.Status <= 6
    )
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = TotalNumberOfRooms 
    FROM #TmpTableArrival
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = TotalNumberOfRooms 
    FROM #TmpTableArrival
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
  END
  ELSE
  BEGIN
    SELECT sum(numberofrooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber) AS BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    FROM reservationroomline WITH (nolock) 
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber)
    WHERE LineNumber IN (
    SELECT reservationroomline.LineNumber
    FROM reservationroomline WITH (nolock) 
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON rrx.LineNumber = ReservationRoomLine.LineNumber
    INNER JOIN Reservations r WITH (NOLOCK) ON rrx.ReferenceNumber = r.ReferenceNumber 
      AND  rrx.GuestNumber = r.GuestNumber 
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber    
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    WHERE
    (
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      AND (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
          where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      AND ((@AHotelNumberStr = '') OR (ReservationRoomLine.RoomNumber in (Select RoomNumber from Rooms WITH (nolock) where HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr)))))
      AND ReservationRoomLine.Roomnumber is not null
    ) 
    or 
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      and (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
           INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationRoomLineXtab.ReferenceNumber 
           AND ((@AHotelNumberStr = '') OR (HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
           where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      and ReservationRoomLine.RoomNumber is null
    )
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((Rooms.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND Rooms.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> 0)      
    AND r.Status <= 6
    )
    GROUP BY ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber), RoomTypes.Type
  END
END
ELSE IF @ARunType = 2
BEGIN
  -- Walkin
  IF @ATotal = 1
  BEGIN
    SELECT COUNT(ReservationMain.ReferenceNumber) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableWalkin
    FROM ReservationMain WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ReservationMain.ReferenceNumber IN 
    (
      SELECT (ReferenceNumber)
      FROM Followup WITH (NOLOCK) 
      INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
        AND GuestNumber = 0 
      WHERE SubType IN (1,4)
        AND 
        (
          ((@AYear + @AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) = @ARunDate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
        )
      GROUP BY CONVERT(VARCHAR(8), CreatedDate, 112), ReferenceNumber 
      HAVING COUNT(SubType) > 1
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber <> ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableWalkin
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableWalkin
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableWalkin
  END
  ELSE
  BEGIN
    SELECT COUNT(ReservationMain.ReferenceNumber) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    FROM ReservationMain WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
    WHERE ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ReservationMain.ReferenceNumber IN 
    (
      SELECT (ReferenceNumber)
      FROM Followup WITH (NOLOCK) 
      INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
        AND GuestNumber = 0 
      WHERE SubType IN (1,4)
        AND 
        (
          ((@AYear + @AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) = @ARunDate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
        )
      GROUP BY CONVERT(VARCHAR(8), CreatedDate, 112), ReferenceNumber 
      HAVING COUNT(SubType) > 1
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber), RoomTypes.Type
  END
END
ELSE IF @ARunType = 3
BEGIN
  -- Noshow
  IF @ATotal = 1
  BEGIN
    SELECT count(reservations.referencenumber) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableNoShow
    from followup WITH (nolock)
    inner join reservationfollowupxtab WITH (nolock) on reservationfollowupxtab.followupnumber = followup.followupnumber 
    inner join reservations WITH (nolock) on reservations.referencenumber = reservationfollowupxtab.referencenumber and reservations.guestnumber = reservationfollowupxtab.guestnumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    where Followup.Type IN (5, 12) 
    AND SubType = 3
    and state >= 3 
    and createdby in (
      select useridnumber 
      from personel WITH (nolock) 
      where usertypenumber = 4
    )
    and status = 8
    AND (
      ((@AYear + @AMonth = '') AND (convert(varchar(8), createddate, 112) like @Arundate)) OR -- Day
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
    )
    AND ((@AHotelNumberStr = '') OR (Followup.Hotelnumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableNoShow
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableNoShow
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableNoShow
  END
  ELSE
  BEGIN
    select count(reservations.referencenumber) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    from followup WITH (nolock)
    inner join reservationfollowupxtab WITH (nolock) on reservationfollowupxtab.followupnumber = followup.followupnumber 
    inner join reservations WITH (nolock) on reservations.referencenumber = reservationfollowupxtab.referencenumber and reservations.guestnumber = reservationfollowupxtab.guestnumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
    where Followup.Type IN (5, 12) 
    AND SubType = 3
    and state >= 3 
    and createdby in (
      select useridnumber 
      from personel WITH (nolock) 
      where usertypenumber = 4
    )
    and status = 8
    AND (
      ((@AYear + @AMonth = '') AND (convert(varchar(8), createddate, 112) like @Arundate)) OR -- Day
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
    )
    AND ((@AHotelNumberStr = '') OR (followup.Hotelnumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber), RoomTypes.Type
  END
END
ELSE IF @ARunType = 4
BEGIN
  -- Dayroom
  DECLARE @DayRoomLaterThanTime VARCHAR(5)
  SELECT @DayRoomLaterThanTime = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DayRoomsLaterThanTime'
  IF @DayRoomLaterThanTime = ''
    SET @DayRoomLaterThanTime = '13:00'

  IF @ATotal = 1
  BEGIN
    SELECT SUM(SpecNumberOfRooms) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableDayRoom
    FROM TGenerateAvailabilityView WITH (NOLOCK)
    INNER JOIN Reservations r WITH (NOLOCK) ON r.ReferenceNumber = TGenerateAvailabilityView.ReferenceNumber
      AND r.GuestNumber = TGenerateAvailabilityView.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = r.ReferenceNumber 
      AND rrlx.GuestNumber = r.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE TGenerateAvailabilityView.Referencenumber > 0
    AND ((@AHotelNumberStr = '') OR (TGenerateAvailabilityView.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ((
      (@ARemaining = 1) AND 
      (
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like  @Ayear + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))
      )
    ) OR
      ((@ARemaining = 0) AND 
      (
        ((@AYear + @AMonth = '') AND (SpecArrivalDate = @Arundate AND (SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate = @Arundate AND SpecDepartureTime > @DayRoomLaterThanTime))) OR
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecDepartureDate like @Ayear + @Amonth + '%' AND SpecDepartureDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecDepartureDate like @Ayear + '%') AND SpecDepartureDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))) -- Year
      )
    ))
    AND TGenerateAvailabilityView.Status < 7
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    AND r.Status <= 6
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableDayRoom
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableDayRoom
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableDayRoom
  END
  ELSE
  BEGIN
    SELECT SUM(SpecNumberOfRooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    FROM TGenerateAvailabilityView WITH (NOLOCK)
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = TGenerateAvailabilityView.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
    WHERE TGenerateAvailabilityView.Referencenumber > 0
    AND ((@AHotelNumberStr = '') OR (TGenerateAvailabilityView.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ((
      (@ARemaining = 1) AND 
      (
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like  @Ayear + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))
      )
    ) OR
      ((@ARemaining = 0) AND 
      (
        ((@AYear + @AMonth = '') AND (SpecArrivalDate = @Arundate AND (SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate = @Arundate AND SpecDepartureTime > @DayRoomLaterThanTime))) OR
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like @Ayear + '%') AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))) -- Year
      )
    ))
    AND TGenerateAvailabilityView.Status < 7
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber), RoomTypes.Type
  END
END
ELSE IF @ARunType = 5
BEGIN
  -- Upgraded
  IF @ATotal = 1
  BEGIN
    SELECT count(*) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableUpg
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
      AND AddressType = 1
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE (1=1) 
    AND 
    (
      -- DAY
      ((@AYear + @AMonth = '') AND ((((
      ((ReservationRoomLine.ArrivalDate = @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate = @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      OR
      -- Month
      ((@AYear <> '') AND (@AMonth <> '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate like @Ayear + @Amonth + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate like @Ayear + @Amonth + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      -- Year
      OR
      ((@AYear <> '') AND (@AMonth = '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate like @Ayear + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate like @Ayear + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND RESERVATIONADDRESS.LINK IS NOT NULL) AND rOwner.CustomerNumber = RESERVATIONADDRESS.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
	
    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableUpg
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableUpg
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
	
    DROP TABLE #TmpTableUpg      
  END
  ELSE
  BEGIN
    SELECT COUNT(*) AS cnt, ReservationRoomLine.BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
    INNER JOIN Reservations WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber 
      AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
      AND AddressType = 1
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE (1=1) 
    AND 
    (
      -- DAY
      ((@AYear + @AMonth = '') AND ((((
      ((ReservationRoomLine.ArrivalDate = @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate = @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      OR
      -- Month
      ((@AYear <> '') AND (@AMonth <> '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate LIKE @Ayear + @Amonth + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate LIKE @Ayear + @Amonth + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      -- Year
      OR
      ((@AYear <> '') AND (@AMonth = '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate LIKE @Ayear + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate LIKE @Ayear + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ReservationAddress.Link IS NOT NULL) AND rOwner.CustomerNumber = ReservationAddress.Link) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY ReservationRoomLine.BookedTypeNumber, RoomTypes.Type
  END
END
ELSE IF @ARunType = 6
BEGIN
  -- Early departure
  IF @ATotal = 1
  BEGIN
    SELECT COUNT(StatisticLeads.NumberOfRooms) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableEarly
    FROM StatisticLeads WITH (NOLOCK)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = StatisticLeads.ReferenceNumber
      AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    LEFT OUTER JOIN Reservations WITH (NOLOCK) ON StatisticLeads.ReferenceNumber = Reservations.ReferenceNumber 
      AND StatisticLeads.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE 1=1 AND
    (
      ((@AYear + @AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) = @ARunDate)) OR
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + @AMonth + '%')) OR
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + '%'))
    )
    AND Reservations.Status > 5 
    AND StatisticLeads.Type = 1
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
	
    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableEarly
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableEarly
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
	
    DROP TABLE #TmpTableEarly      
  END
  ELSE
  BEGIN
    SELECT COUNT(StatisticLeads.NumberOfRooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber, RoomTypes.Type AS RoomTypeName
    FROM StatisticLeads WITH (NOLOCK)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = StatisticLeads.ReferenceNumber
      AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    LEFT OUTER JOIN Reservations WITH (NOLOCK) ON StatisticLeads.ReferenceNumber = Reservations.ReferenceNumber 
      AND StatisticLeads.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
    WHERE 1=1 AND
    (
      ((@AYear + @AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) = @ARunDate)) OR
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + @AMonth + '%')) OR
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + '%'))
    )
    AND Reservations.Status > 5 
    AND StatisticLeads.Type = 1
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber), RoomTypes.Type
  END
END
ELSE IF @ARunType = 7
BEGIN
  -- Upgraded detailed
  IF @ATotal = 1
  BEGIN
    SELECT count(*) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableUpgDetailed
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber
      AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
      AND AddressType = 1
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE (1=1) 
    AND 
    (
      -- DAY
      ((@AYear + @AMonth = '') AND ((((
      ((ReservationRoomLine.ArrivalDate = @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate = @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      OR
      -- Month
      ((@AYear <> '') AND (@AMonth <> '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate like @Ayear + @Amonth + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate like @Ayear + @Amonth + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      -- Year
      OR
      ((@AYear <> '') AND (@AMonth = '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate like @Ayear + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate like @Ayear + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND RESERVATIONADDRESS.LINK IS NOT NULL) AND rOwner.CustomerNumber = RESERVATIONADDRESS.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
	
    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableUpgDetailed
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableUpgDetailed
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
	
    DROP TABLE #TmpTableUpgDetailed    
  END
  ELSE
  BEGIN
    SELECT COUNT(*) AS cnt, ReservationRoomLine.BookedTypeNumber, BookedRT.Type AS RoomTypeName, BookedRT.SortNumber AS RoomTypeSortNumber,
    Rooms.RoomTypeNumber AS AllocatedRoomTypeNumber, AllocatedRT.Type AS AllocatedRoomTypeName, AllocatedRT.SortNumber AS AllocatedRoomTypeSortNumber,
    CASE WHEN Reservations.GuestNumber = 0 THEN 
      ReservationRoomLine.Adults + ReservationRoomLine.Children1 + ReservationRoomLine.Children2 
    ELSE
      Reservations.Adults + Reservations.Children1 + Reservations.Children2
    END AS Pax
    FROM ReservationRoomLine WITH (NOLOCK) 
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
    INNER JOIN Reservations WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber 
      AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
      AND AddressType = 1
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    LEFT OUTER JOIN RoomTypes BookedRT ON BookedRT.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    LEFT OUTER JOIN RoomTypes AllocatedRT ON AllocatedRT.RoomTypeNumber = Rooms.RoomTypeNumber
    WHERE (1=1) 
    AND 
    (
      -- DAY
      ((@AYear + @AMonth = '') AND ((((
      ((ReservationRoomLine.ArrivalDate = @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate = @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      OR
      -- Month
      ((@AYear <> '') AND (@AMonth <> '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate LIKE @Ayear + @Amonth + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR  ((Reservations.ArrivalDate LIKE @Ayear + @Amonth + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
      -- Year
      OR
      ((@AYear <> '') AND (@AMonth = '') AND (
      (((
      ((ReservationRoomLine.ArrivalDate LIKE @Ayear + '%') AND (ReservationRoomLine.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
      OR ((Reservations.ArrivalDate LIKE @Ayear + '%') AND (Reservations.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
      AND (((ReservationRoomLine.DepartureDate <> ReservationRoomLine.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((Reservations.DepartureDate <> Reservations.ArrivalDate)) AND (Reservations.GuestNumber > 0))
      )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ReservationAddress.Link IS NOT NULL) AND rOwner.CustomerNumber = ReservationAddress.Link) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    AND Reservations.Status <= 6
    GROUP BY ReservationRoomLine.BookedTypeNumber, Rooms.RoomTypeNumber, Reservations.GuestNumber, ReservationRoomLine.Adults + ReservationRoomLine.Children1 + ReservationRoomLine.Children2, 
      Reservations.Adults + Reservations.Children1 + Reservations.Children2, BookedRT.Type, AllocatedRT.Type, BookedRT.SortNumber, AllocatedRT.SortNumber
  END
END