CREATE PROCEDURE dbo.HotelmanagerReservations @rundate VARCHAR(8), @HotelNumberStr VARCHAR(100), @includedaymeeting BIT = 0, 
  @InclVat bit = 1, @InclPackage bit = 1, @AReportOwnerReservation INT, @AInclNUL BIT, @AInclEmpty BIT, @AAdditionalTO MONEY = 0, 
  @AWorkHour INT = 0, @APerRoomBed BIT = 0, @AIncludeProvision BIT, @AIncludeAllotment BIT AS
SET NOCOUNT ON

/*
  Remarks:
    @AReportOwnerReservation: (0: rorExcluded, 1: rorOnly, 2: rorIncluded)  

  Changes:
    KJ 20220506: Allotment added req #4059
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    KJ 20171218: Ext: Added Group data by rooms/beds
    KJ 20170629: Bug: Fix for "Wrong number of stays". Case #CR127717
    KJ 20151021: Bug: Fix for "Wrong type of input params"
    KJ 20151012: Ext: Added Turnover per hour
    KJ 20130430: Ext: Added incl NUL and Empty products. Req #1530
    KJ 20120829: Ext: Removed VATFactor - not neede anymore
    KJ 20120712: Bug: MISTableView rewrite
    KJ 20120709: Bug: MISTableView rewrite
    KJ 20120430: Ext: Added owner reservations.
    KJ 20120216: Ext: Use MISTableView to select data.
    KJ 20110801; Ext: Added Adults, Children1 and Children2. Req# 624
    KJ 20110615: Bug: Fix for "Excl vat and excl package amount is wrong". Case# CR024613
    KJ 20110226: Ext: Added VATFactor. Req# 826
    KJ 20091207: Bug: Fix for "Rev/par not correct when out of order rooms"
    KJ 20090406: Ext: Added use of new sp -> HotelManagerNumberOfRooms
    KJ 20080922: Bug: Fix for "Divide by zero"
    KJ 20080723: Ext: Added support for date in the future
    KJ 20080107: Bug Fix for "Ambigeus fieldname 'Type'"
    KJ 20070309: Added early departures
    KJ 20070227: Number of Out of order not correct.
    KJ 20061124: Added Rev/Par
    KJ 20061124: Rewrite to Domino
    KJ 20060905: Wrong NumberOfDayRooms
    KJ 20060905: TodayNumberOfRooms wrong
    KJ 20060816: Also show noshows for guestnumber > 0
    KJ 20060814: Only Include OutOfOrder to belęg procent if selected
    KJ 20060814: Do not include Dayrooms in Belęg Procent. 
    KJ 20060707: Using DBO.DaysInMonth to get number of days in month
    KJ 20050816: Number of DayRooms wrong
    KJ 20050721: Added number of Upgrade rooms
    KJ 20050720: Added output of Dayrooms
*/

declare @valuetype varchar(10) 
set @valuetype = '1'
if @includedaymeeting = 1
  set @valuetype = @valuetype + ',3'

DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @SystemDate CHAR(8)

IF @rundate = '' 
  SELECT @rundate = CONVERT(VARCHAR(8), CONVERT(DATETIME, SystemDate, 112) - 1, 112) FROM system WITH (NOLOCK)

SELECT @SystemDate = CONVERT(CHAR(8), CONVERT(DATETIME, systemdate, 112), 112) FROM system WITH (NOLOCK)

SET @Year = SUBSTRING(@rundate, 1, 4)
SET @month = SUBSTRING(@rundate, 5, 2)

DECLARE @dayofmonth INT 
DECLARE @dayofyear INT 
DECLARE @Remainingdayofmonth INT 
DECLARE @Remainingdayofyear INT 

SET @dayofmonth = CONVERT(INT, SUBSTRING(@rundate, 7, 2))
SET @dayofyear = DATEPART(dayofyear, CONVERT(DATETIME, @rundate, 112))

SET @AIncludeProvision = ISNULL(@AIncludeProvision, 0)

DECLARE @totalroom INT
DECLARE @totalroomRooms INT
DECLARE @totalroomBeds INT

SELECT @TotalRoom = COUNT(Number) 
FROM Rooms WITH (NOLOCK) 
WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))

SELECT @totalroomRooms = COUNT(Number) 
FROM Rooms WITH (NOLOCK) 
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
  AND RoomTypes.ParentRoomTypeNumber IS NULL
WHERE Rooms.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))

SELECT @totalroomBeds = COUNT(Number) 
FROM Rooms WITH (NOLOCK) 
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
  AND NOT RoomTypes.ParentRoomTypeNumber IS NULL
WHERE Rooms.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))

DECLARE @TodayPrice MONEY
DECLARE @TodayPriceExPackage MONEY
DECLARE @TodayPriceExPackageExVat MONEY
DECLARE @TodayPriceInclPackageExVat MONEY

DECLARE @TodayPriceRooms MONEY
DECLARE @TodayPriceExPackageRooms MONEY
DECLARE @TodayPriceExPackageExVatRooms MONEY
DECLARE @TodayPriceInclPackageExVatRooms MONEY

DECLARE @TodayPriceBeds MONEY
DECLARE @TodayPriceExPackageBeds MONEY
DECLARE @TodayPriceExPackageExVatBeds MONEY
DECLARE @TodayPriceInclPackageExVatBeds MONEY

DECLARE @MonthPrice MONEY
DECLARE @MonthPriceExPackage MONEY
DECLARE @MonthPriceExPackageExVat MONEY
DECLARE @MonthPriceInclPackageExVat MONEY

DECLARE @MonthPriceRooms MONEY
DECLARE @MonthPriceExPackageRooms MONEY
DECLARE @MonthPriceExPackageExVatRooms MONEY
DECLARE @MonthPriceInclPackageExVatRooms MONEY

DECLARE @MonthPriceBeds MONEY
DECLARE @MonthPriceExPackageBeds MONEY
DECLARE @MonthPriceExPackageExVatBeds MONEY
DECLARE @MonthPriceInclPackageExVatBeds MONEY

DECLARE @RemainingMonthPrice MONEY
DECLARE @RemainingMonthPriceExPackage MONEY
DECLARE @RemainingMonthPriceExPackageExVat MONEY
DECLARE @RemainingMonthPriceInclPackageExVat MONEY

DECLARE @RemainingMonthPriceBooked MONEY
DECLARE @RemainingMonthPriceExPackageBooked MONEY
DECLARE @RemainingMonthPriceExPackageExVatBooked MONEY
DECLARE @RemainingMonthPriceInclPackageExVatBooked MONEY

DECLARE @YearPrice MONEY
DECLARE @YearPriceExPackage MONEY
DECLARE @YearPriceExPackageExVat MONEY
DECLARE @YearPriceInclPackageExVat MONEY

DECLARE @YearPriceRooms MONEY
DECLARE @YearPriceExPackageRooms MONEY
DECLARE @YearPriceExPackageExVatRooms MONEY
DECLARE @YearPriceInclPackageExVatRooms MONEY

DECLARE @YearPriceBeds MONEY
DECLARE @YearPriceExPackageBeds MONEY
DECLARE @YearPriceExPackageExVatBeds MONEY
DECLARE @YearPriceInclPackageExVatBeds MONEY

DECLARE @RemainingYearPrice MONEY
DECLARE @RemainingYearPriceExPackage MONEY
DECLARE @RemainingYearPriceExPackageExVat MONEY
DECLARE @RemainingYearPriceInclPackageExVat MONEY

DECLARE @RemainingYearPriceBooked MONEY
DECLARE @RemainingYearPriceExPackageBooked MONEY
DECLARE @RemainingYearPriceExPackageExVatBooked MONEY
DECLARE @RemainingYearPriceInclPackageExVatBooked MONEY

--------------------------------------------------------------------------------------
-------------------  TODAY PART START  -----------------------------------------------
--------------------------------------------------------------------------------------
--------------------  TODAY WALKIN - START  ------------------------------------------
declare @todaynumberofromswalkin int
declare @todaynumberofromswalkinRooms int
declare @todaynumberofromswalkinBeds int

EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 2, 1, 0, @todaynumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @todaynumberofromswalkinRooms OUTPUT, @todaynumberofromswalkinBeds OUTPUT

--------------------  TODAY WALKIN - END  -----------------------------------------
--------------------  TODAY Early departures - START  -----------------------------
DECLARE @TodayEarlyDeparture INT
DECLARE @TodayEarlyDepartureRooms INT
DECLARE @TodayEarlyDepartureBeds INT

EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 6, 1, 0, @TodayEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @TodayEarlyDepartureRooms OUTPUT, @TodayEarlyDepartureBeds OUTPUT

--------------------  TODAY Early departures - END  -------------------------------
--------------------  TODAY INHOUSE - START  -----------------------------------------
declare @todaynumberofrooms int
DECLARE @TodayAdult INT
DECLARE @TodayChildren1 INT
DECLARE @TodayChildren2 INT
DECLARE @TodayPax INT

IF @rundate <= @SystemDate
  SELECT @todaynumberofrooms = sum(actualnumberofrooms), 
    @TodayAdult = SUM(ISNULL(ActualAdult, 0)),
    @TodayChildren1 = SUM(ActualChildren1),
    @TodayChildren2 = SUM(ActualChildren2),
    @TodayPax = sum(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2)
  from mistableView WITH (nolock) 
  where datestamp = @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
ELSE 
  SELECT @todaynumberofrooms = sum(Bookednumberofrooms), 
    @TodayAdult = SUM(BookedAdult),
    @TodayChildren1 = SUM(BookedChildren1),
    @TodayChildren2 = SUM(BookedChildren2),
    @TodayPax = sum(BookedAdult+BookedChildren1+BookedChildren2) 
  from mistableView WITH (nolock) 
  where datestamp = @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  DECLARE @TodayNumberOfRoomsRooms INT
  DECLARE @TodayNumberOfRoomsBeds INT
  DECLARE @TodayAdultRooms INT
  DECLARE @TodayAdultBeds INT
  DECLARE @TodayChildren1Rooms INT
  DECLARE @TodayChildren1Beds INT
  DECLARE @TodayChildren2Rooms INT
  DECLARE @TodayChildren2Beds INT
  DECLARE @TodayPaxRooms INT
  DECLARE @TodayPaxBeds INT

  SELECT sum(actualnumberofrooms) AS NumberOfRooms, 
    SUM(ISNULL(ActualAdult, 0)) AS NumberOfAdult,
    SUM(ActualChildren1) AS NumberOfChildren1,
    SUM(ActualChildren2) AS NumberOfChildren2,
    sum(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2) AS NumberOfPax,
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTable
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp = @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT @TodayNumberOfRoomsRooms = NumberOfRooms,
    @TodayAdultRooms = NumberOfAdult,
    @TodayChildren1Rooms = NumberOfChildren1,
    @TodayChildren2Rooms = NumberOfChildren2,
    @TodayPaxRooms = NumberOfPax
  FROM #TmpTable
  WHERE RoomBedType = 0

  SELECT @TodayNumberOfRoomsBeds = NumberOfRooms,
    @TodayAdultBeds = NumberOfAdult,
    @TodayChildren1Beds = NumberOfChildren1,
    @TodayChildren2Beds = NumberOfChildren2,
    @TodayPaxBeds = NumberOfPax
  FROM #TmpTable
  WHERE RoomBedType = 1

  DROP TABLE #TmpTable
END

--------------------  TODAY INHOUSE - END  -----------------------------------------
--------------------  TODAY ARRIVALS - START  -----------------------------------------
declare @todaynumberofroomsarrivals int
declare @todaynumberofroomsarrivalsRooms int
declare @todaynumberofroomsarrivalsBeds int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 1, 1, 0, @todaynumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @todaynumberofroomsarrivalsRooms OUTPUT, @todaynumberofroomsarrivalsBeds OUTPUT

--------------------  TODAY ARRIVALS - END  -----------------------------------------
--------------------  TODAY DAYROOM - START  -----------------------------------------
declare @todaynumberofdayrooms int
declare @todaynumberofdayroomsRooms int
declare @todaynumberofdayroomsBeds int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 4, 1, 0, @todaynumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @todaynumberofdayroomsRooms OUTPUT, @todaynumberofdayroomsBeds OUTPUT

--------------------  TODAY DAYROOM - END  -----------------------------------------
--------------------  TODAY OUT OF ORDER - START  ------------------------------------
/*
declare @todayoutoforder  int
declare @todayoutoforderRooms int
declare @todayoutoforderBeds int

SELECT @todayoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate < @rundate THEN @rundate ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate > @rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr='' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@rundate

IF @APerRoomBed = 1
BEGIN
  SELECT @todayoutoforderRooms = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate < @rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate > @rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr='' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@rundate
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

  SELECT @todayoutoforderBeds = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate < @rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate > @rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr='' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@rundate
	  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
		AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
END
*/
--------------------  TODAY OUT OF ORDER - END  ------------------------------------
--------------------  TODAY NOSHOW - START  ------------------------------------
declare @todaynoshow  int
declare @todaynoshowRooms int
declare @todaynoshowBeds int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 3, 1, 0, @todaynoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @todaynoshowRooms OUTPUT, @todaynoshowBeds OUTPUT

--------------------  TODAY NOSHOW - END  ------------------------------------
--------------------  TODAY Upgrade rooms - START  -----------------------------------------
declare @TodayNumberOfRoomsUpgrade int
declare @TodayNumberOfRoomsUpgradeRooms int
declare @TodayNumberOfRoomsUpgradeBeds int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 5, 1, 0, @TodayNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @TodayNumberOfRoomsUpgradeRooms OUTPUT, @TodayNumberOfRoomsUpgradeBeds OUTPUT

--------------------  TODAY Upgrade rooms - END  -----------------------------------------
--------------------  TODAY Rev/Par - START  ---------------------------------------------
SELECT @TodayPrice = sum(ActualPriceInclPackage), 
@TodayPriceExPackage = sum(ActualPriceExPackage), 
@TodayPriceExPackageExVat = sum(ActualPriceExPackageExVat), 
@TodayPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp = @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  SELECT
    sum(ActualPriceInclPackage) AS ActualPriceInclPackage, 
    sum(ActualPriceExPackage) AS ActualPriceExPackage, 
    sum(ActualPriceExPackageExVat) AS ActualPriceExPackageExVat, 
    sum(ActualPriceInclPackageExVat) AS ActualPriceInclPackageExVat,
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTableRevPar
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp = @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT 
    @TodayPriceRooms = ActualPriceInclPackage, 
    @TodayPriceExPackageRooms = ActualPriceExPackage, 
    @TodayPriceExPackageExVatRooms = ActualPriceExPackageExVat, 
    @TodayPriceInclPackageExVatRooms = ActualPriceInclPackageExVat
  FROM #TmpTableRevPar
  WHERE RoomBedType = 0

  SELECT 
    @TodayPriceBeds = ActualPriceInclPackage, 
    @TodayPriceExPackageBeds = ActualPriceExPackage, 
    @TodayPriceExPackageExVatBeds = ActualPriceExPackageExVat, 
    @TodayPriceInclPackageExVatBeds = ActualPriceInclPackageExVat
  FROM #TmpTableRevPar
  WHERE RoomBedType = 1

  DROP TABLE #TmpTableRevPar
END
--------------------  TODAY Rev/Par - END  ---------------------------------------------

--------------------------------------------------------------------------------------
-------------------  TODAY PART END  -------------------------------------------------
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  MONTH PART START  -----------------------------------------------
--------------------------------------------------------------------------------------
--------------------  MONTH WALKIN - START  -----------------------------------------
declare @monthnumberofromswalkin int
declare @monthnumberofromswalkinRooms int
declare @monthnumberofromswalkinBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 2, 1, 0, @monthnumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @monthnumberofromswalkinRooms OUTPUT, @monthnumberofromswalkinBeds OUTPUT

--------------------  MONTH WALKIN - END  -----------------------------------------
--------------------  MONTH Early departures - START  -----------------------------

DECLARE @MonthEarlyDeparture INT
DECLARE @MonthEarlyDepartureRooms INT
DECLARE @MonthEarlyDepartureBeds INT
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 6, 1, 0, @MonthEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @MonthEarlyDepartureRooms OUTPUT, @MonthEarlyDepartureBeds OUTPUT

--------------------  MONTH Early departures - END  -------------------------------
--------------------  MONTH INHOUSE - START  --------------------------------------
declare @monthnumberofrooms int
DECLARE @MonthAdult INT
DECLARE @MonthChildren1 INT
DECLARE @MonthChildren2 INT
DECLARE @MonthPax INT

declare @monthnumberofroomsBooked INT

select @monthnumberofrooms = sum(ActualNumberOfRooms),
  @MonthAdult = SUM(ISNULL(ActualAdult, 0)),
  @MonthChildren1 = SUM(ActualChildren1),
  @MonthChildren2 = SUM(ActualChildren2),
  @MonthPax = SUM(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2)
from mistableView WITH (nolock) 
where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  DECLARE @monthnumberofroomsRooms INT
  DECLARE @monthnumberofroomsBeds INT
  DECLARE @monthnumberofroomsBookedRooms INT
  DECLARE @monthnumberofroomsBookedBeds INT
  DECLARE @MonthAdultRooms INT
  DECLARE @MonthAdultBeds INT
  DECLARE @MonthChildren1Rooms INT
  DECLARE @MonthChildren1Beds INT
  DECLARE @MonthChildren2Rooms INT
  DECLARE @MonthChildren2Beds INT
  DECLARE @MonthPaxRooms INT
  DECLARE @MonthPaxBeds INT

  SELECT sum(actualnumberofrooms) AS NumberOfRooms, 
    SUM(ISNULL(ActualAdult, 0)) AS NumberOfAdult,
    SUM(ActualChildren1) AS NumberOfChildren1,
    SUM(ActualChildren2) AS NumberOfChildren2,
    SUM(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2)  AS NumberOfPax, 
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTable1
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT @monthnumberofroomsRooms = NumberOfRooms,
    @MonthAdultRooms = NumberOfAdult,
    @MonthChildren1Rooms = NumberOfChildren1,
    @MonthChildren2Rooms = NumberOfChildren2,
    @MonthPaxRooms = NumberOfPax
  FROM #TmpTable1
  WHERE RoomBedType = 0

  SELECT @monthnumberofroomsBeds = NumberOfRooms,
    @MonthAdultBeds = NumberOfAdult,
    @MonthChildren1Beds = NumberOfChildren1,
    @MonthChildren2Beds = NumberOfChildren2,
    @MonthPaxBeds = NumberOfPax
  FROM #TmpTable1
  WHERE RoomBedType = 1

  DROP TABLE #TmpTable1
END

IF @rundate >= @SystemDate
BEGIN
  select @monthnumberofroomsBooked = sum(BookedNumberOfRooms) 
  from mistableView WITH (nolock) 
  where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    
  SET @monthnumberofrooms = ISNULL(@monthnumberofrooms, 0) + ISNULL(@monthnumberofroomsBooked, 0)

  IF @APerRoomBed = 1
  BEGIN
    select sum(BookedNumberOfRooms) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTable2
    from mistableView WITH (nolock) 
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
    where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
    AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
    AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
    
    SELECT @monthnumberofroomsBookedRooms = NumberOfRooms
    FROM #TmpTable2
    WHERE RoomBedType = 0

    SELECT @monthnumberofroomsBookedBeds = NumberOfRooms
    FROM #TmpTable2
    WHERE RoomBedType = 1

    DROP TABLE #TmpTable2

    SET @monthnumberofroomsRooms = ISNULL(@monthnumberofroomsRooms, 0) + ISNULL(@monthnumberofroomsBookedRooms, 0)
    SET @monthnumberofroomsBeds = ISNULL(@monthnumberofroomsBeds, 0) + ISNULL(@monthnumberofroomsBookedBeds, 0)
  END
END

--------------------  MONTH INHOUSE - END  -----------------------------------------
--------------------  MONTH ARRIVALS - START  --------------------------------------
declare @monthnumberofroomsarrivals int
declare @monthnumberofroomsarrivalsRooms int
declare @monthnumberofroomsarrivalsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 1, 1, 0, @monthnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @monthnumberofroomsarrivalsRooms OUTPUT, @monthnumberofroomsarrivalsBeds OUTPUT

--------------------  MONTH ARRIVALS - END  -----------------------------------------
--------------------  MONTH DAYROOM - START  ----------------------------------------
declare @monthnumberofdayrooms  int
declare @monthnumberofdayroomsRooms int
declare @monthnumberofdayroomsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 4, 1, 0, @monthnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @monthnumberofdayroomsRooms OUTPUT, @monthnumberofdayroomsBeds OUTPUT

--------------------  MONTH DAYROOM - END  ---------------------------------------
--------------------  MONTH OUT OF ORDER - START  ------------------------------
/*
declare @monthoutoforder  int
declare @monthoutoforderRooms int
declare @monthoutoforderBeds int

SELECT @monthoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + @Month + '01' THEN @Year + @Month + '01' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + @Month + '01'

IF @APerRoomBed = 1
BEGIN
  SELECT @monthoutoforderRooms = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + @Month + '01' THEN @Year + @Month + '01' ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + @Month + '01'
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

  SELECT @monthoutoforderBeds = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + @Month + '01' THEN @Year + @Month + '01' ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + @Month + '01'
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
END
*/
--------------------  MONTH OUT OF ORDER - END  --------------------------------
--------------------  MONTH NOSHOW - START  ------------------------------------
declare @monthnoshow int
declare @monthnoshowRooms int
declare @monthnoshowBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 3, 1, 0, @monthnoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @monthnoshowRooms OUTPUT, @monthnoshowBeds OUTPUT

--------------------  MONTH NOSHOW - END  ------------------------------------
--------------------  MONTH upgrade rooms - START  ---------------------------
declare @MonthNumberOfRoomsUpgrade int
declare @MonthNumberOfRoomsUpgradeRooms int
declare @MonthNumberOfRoomsUpgradeBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 5, 1, 0, @MonthNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @MonthNumberOfRoomsUpgradeRooms OUTPUT, @MonthNumberOfRoomsUpgradeBeds OUTPUT

--------------------  MONTH Upgrade rooms - END  -----------------------------------
--------------------  MONTH Rev/Par - START  ---------------------------------------------

SELECT @MonthPrice = sum(ActualPriceInclPackage), @MonthPriceExPackage = sum(ActualPriceExPackage), @MonthPriceExPackageExVat = sum(ActualPriceExPackageExVat), @MonthPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  SELECT
    sum(ActualPriceInclPackage) AS ActualPriceInclPackage, 
    sum(ActualPriceExPackage) AS ActualPriceExPackage, 
    sum(ActualPriceExPackageExVat) AS ActualPriceExPackageExVat, 
    sum(ActualPriceInclPackageExVat) AS ActualPriceInclPackageExVat,
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTableRevParMonth
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + @month + '%' and datestamp <= @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT 
    @MonthPriceRooms = ActualPriceInclPackage, 
    @MonthPriceExPackageRooms = ActualPriceExPackage, 
    @MonthPriceExPackageExVatRooms = ActualPriceExPackageExVat, 
    @MonthPriceInclPackageExVatRooms = ActualPriceInclPackageExVat
  FROM #TmpTableRevParMonth
  WHERE RoomBedType = 0

  SELECT 
    @MonthPriceBeds = ActualPriceInclPackage, 
    @MonthPriceExPackageBeds = ActualPriceExPackage, 
    @MonthPriceExPackageExVatBeds = ActualPriceExPackageExVat, 
    @MonthPriceInclPackageExVatBeds = ActualPriceInclPackageExVat
  FROM #TmpTableRevParMonth
  WHERE RoomBedType = 1

  DROP TABLE #TmpTableRevParMonth
END

------------------------------------------------------------------------------------
-------------------  MONTH PART END  -----------------------------------------------
------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  Remaining MONTH PART START  -------------------------------------
--------------------------------------------------------------------------------------
--------------------  Remaining MONTH INHOUSE - START  --------------------------------------
declare @Remainingmonthnumberofrooms int
declare @RemainingmonthnumberofroomsBooked INT
DECLARE @RemainingmonthPax INT
DECLARE @RemainingMonthAdults INT
DECLARE @RemainingMonthChildren1 INT
DECLARE @RemainingMonthChildren2 INT
DECLARE @RemainingmonthPaxBooked INT
DECLARE @RemainingMonthAdultsBooked INT
DECLARE @RemainingMonthChildren1Booked INT
DECLARE @RemainingMonthChildren2Booked INT

select @Remainingmonthnumberofrooms = SUM(ActualNumberOfRooms)+SUM(BookedNumberOfRooms), 
  @RemainingmonthPax = SUM(ActualAdult+ActualChildren1+ActualChildren2)+SUM(BookedAdult+BookedChildren1+BookedChildren2),
  @RemainingMonthAdults = SUM(ActualAdult)+SUM(BookedAdult),
  @RemainingMonthChildren1 = SUM(ActualChildren1)+SUM(BookedChildren1),
  @RemainingMonthChildren2 = SUM(ActualChildren2)+SUM(BookedChildren2),
  @RemainingMonthPrice = SUM(ActualPriceInclPackage)+SUM(BookedPriceInclPackage),
  @RemainingMonthPriceExPackage = SUM(ActualPriceExPackage)+SUM(BookedPriceExPackage), 
  @RemainingMonthPriceExPackageExVat = SUM(ActualPriceExPackageExVat)+SUM(BookedPriceExPackageExVat), 
  @RemainingMonthPriceInclPackageExVat = SUM(ActualPriceInclPackageExVat)+SUM(BookedPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp like @year + @month + '%' 
and datestamp > @rundate 
and valuetype in (select value from dbo.SplitNumberList(@valuetype)) 
AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  DECLARE @RemainingmonthnumberofroomsRooms INT
  DECLARE @RemainingmonthPaxRooms INT
  DECLARE @RemainingMonthAdultsRooms INT
  DECLARE @RemainingMonthChildren1Rooms INT
  DECLARE @RemainingMonthChildren2Rooms INT

  DECLARE @RemainingmonthnumberofroomsBookedRooms INT
  DECLARE @RemainingmonthPaxBookedRooms INT
  DECLARE @RemainingMonthAdultsBookedRooms INT
  DECLARE @RemainingMonthChildren1BookedRooms INT
  DECLARE @RemainingMonthChildren2BookedRooms INT

  DECLARE @RemainingMonthPriceRooms MONEY
  DECLARE @RemainingMonthPriceExPackageRooms MONEY
  DECLARE @RemainingMonthPriceExPackageExVatRooms MONEY
  DECLARE @RemainingMonthPriceInclPackageExVatRooms MONEY

  DECLARE @RemainingMonthPriceBookedRooms MONEY
  DECLARE @RemainingMonthPriceExPackageBookedRooms MONEY
  DECLARE @RemainingMonthPriceExPackageExVatBookedRooms MONEY
  DECLARE @RemainingMonthPriceInclPackageExVatBookedRooms MONEY

  DECLARE @RemainingmonthnumberofroomsBeds INT
  DECLARE @RemainingmonthPaxBeds INT
  DECLARE @RemainingMonthAdultsBeds INT
  DECLARE @RemainingMonthChildren1Beds INT
  DECLARE @RemainingMonthChildren2Beds INT

  DECLARE @RemainingmonthnumberofroomsBookedBeds INT
  DECLARE @RemainingmonthPaxBookedBeds INT
  DECLARE @RemainingMonthAdultsBookedBeds INT
  DECLARE @RemainingMonthChildren1BookedBeds INT
  DECLARE @RemainingMonthChildren2BookedBeds INT

  DECLARE @RemainingMonthPriceBeds MONEY
  DECLARE @RemainingMonthPriceExPackageBeds MONEY
  DECLARE @RemainingMonthPriceExPackageExVatBeds MONEY
  DECLARE @RemainingMonthPriceInclPackageExVatBeds MONEY

  DECLARE @RemainingMonthPriceBookedBeds MONEY
  DECLARE @RemainingMonthPriceExPackageBookedBeds MONEY
  DECLARE @RemainingMonthPriceExPackageExVatBookedBeds MONEY
  DECLARE @RemainingMonthPriceInclPackageExVatBookedBeds MONEY

  select SUM(BookedNumberOfRooms)+SUM(ActualNumberOfRooms) AS Remainingmonthnumberofrooms,
    SUM(BookedAdult+BookedChildren1+BookedChildren2)+SUM(ActualAdult+actualchildren1+actualchildren2) AS RemainingmonthPax,
    SUM(BookedAdult)+sum(ActualAdult) AS RemainingMonthAdults,
    SUM(BookedChildren1)+SUM(ActualChildren1) AS RemainingMonthChildren1,
    SUM(BookedChildren2)+SUM(ActualChildren2) AS RemainingMonthChildren2,
    SUM(BookedPriceInclPackage)+SUM(ActualPriceInclPackage) AS RemainingMonthPrice, 
    SUM(BookedPriceExPackage)+SUM(ActualPriceExPackage) AS RemainingMonthPriceExPackage, 
    SUM(BookedPriceExPackageExVat)+SUM(ActualPriceExPackageExVat) AS RemainingMonthPriceExPackageExVat, 
    SUM(BookedPriceInclPackageExVat)+SUM(ActualPriceInclPackageExVat) AS RemainingMonthPriceInclPackageExVat,
	CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #tmpTableRemainingMonth
  from mistableView WITH (nolock) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + @month + '%' and datestamp > @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT @RemainingmonthnumberofroomsRooms = Remainingmonthnumberofrooms,
    @RemainingmonthPaxRooms = RemainingmonthPax,
    @RemainingMonthAdultsRooms = RemainingMonthAdults,
    @RemainingMonthChildren1Rooms = RemainingMonthChildren1,
    @RemainingMonthChildren2Rooms = RemainingMonthChildren2,
    @RemainingMonthPriceRooms = RemainingMonthPrice, 
    @RemainingMonthPriceExPackageRooms = RemainingMonthPriceExPackage, 
    @RemainingMonthPriceExPackageExVatRooms = RemainingMonthPriceExPackageExVat, 
    @RemainingMonthPriceInclPackageExVatRooms = RemainingMonthPriceInclPackageExVat
  FROM #tmpTableRemainingMonth
  WHERE RoomBedType = 0

  SELECT @RemainingmonthnumberofroomsBeds = Remainingmonthnumberofrooms,
    @RemainingmonthPaxBeds = RemainingmonthPax,
    @RemainingMonthAdultsBeds = RemainingMonthAdults,
    @RemainingMonthChildren1Beds = RemainingMonthChildren1,
    @RemainingMonthChildren2Beds = RemainingMonthChildren2,
    @RemainingMonthPriceBeds = RemainingMonthPrice, 
    @RemainingMonthPriceExPackageBeds = RemainingMonthPriceExPackage, 
    @RemainingMonthPriceExPackageExVatBeds = RemainingMonthPriceExPackageExVat, 
    @RemainingMonthPriceInclPackageExVatBeds = RemainingMonthPriceInclPackageExVat
  FROM #tmpTableRemainingMonth
  WHERE RoomBedType = 1

  DROP TABLE #tmpTableRemainingMonth
END

-------------------- Remaining MONTH INHOUSE - END  -----------------------------------------
-------------------- Remaining MONTH ARRIVALS - START  --------------------------------------
declare @Remainingmonthnumberofroomsarrivals int
declare @RemainingmonthnumberofroomsarrivalsRooms int
declare @RemainingmonthnumberofroomsarrivalsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 1, 1, 1, @Remainingmonthnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @RemainingmonthnumberofroomsarrivalsRooms OUTPUT, @RemainingmonthnumberofroomsarrivalsBeds OUTPUT

-------------------- Remaining MONTH ARRIVALS - END  -----------------------------------------
-------------------- Remaining MONTH DAYROOM - START  ----------------------------------------
declare @Remainingmonthnumberofdayrooms int
declare @RemainingmonthnumberofdayroomsRooms int
declare @RemainingmonthnumberofdayroomsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 4, 1, 1, @Remainingmonthnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @RemainingmonthnumberofdayroomsRooms OUTPUT, @RemainingmonthnumberofdayroomsBeds OUTPUT

-------------------- Remaining MONTH DAYROOM - END  ---------------------------------------
-------------------- Remaining MONTH OUT OF ORDER - START  ------------------------------
/*
declare @Remainingmonthoutoforder int
declare @RemainingmonthoutoforderRooms int
declare @RemainingmonthoutoforderBeds int

SELECT @Remainingmonthoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>EOMONTH(@rundate) THEN EOMONTH(@rundate) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 
  AND ArrivalDate<=EOMONTH(@rundate) 
  AND DepartureDate>=@rundate

IF @APerRoomBed = 1
BEGIN
  SELECT @RemainingmonthoutoforderRooms = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>EOMONTH(@rundate) THEN EOMONTH(@rundate) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 
    AND ArrivalDate<=EOMONTH(@rundate) 
    AND DepartureDate>=@rundate
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

  SELECT @RemainingmonthoutoforderBeds = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>EOMONTH(@rundate) THEN EOMONTH(@rundate) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 
    AND ArrivalDate<=EOMONTH(@rundate) 
    AND DepartureDate>=@rundate
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
END
*/
-------------------- Remaining MONTH OUT OF ORDER - END  --------------------------------
-------------------- Remaining MONTH upgrade rooms - START  ---------------------------
/*
  --- does not seams to be used....


declare @RemainingMonthNumberOfRoomsUpgrade int
declare @RemainingMonthNumberOfRoomsUpgradeRooms int
declare @RemainingMonthNumberOfRoomsUpgradeBeds int

SELECT DISTINCT @RemainingMonthNumberOfRoomsUpgrade = count(*)
FROM RESERVATIONROOMLINE WITH (NOLOCK) 
INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
WHERE (1=1) 
AND (((
((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
 ) 
AND ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
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))

IF @APerRoomBed = 1
BEGIN
  SELECT DISTINCT count(*) AS NumberOfRooms, CAST(CASE WHEN RoomTypes1.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTableRemRoomsUpg
  FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
  INNER JOIN RoomTypes RoomTypes1 WITH (NOLOCK) ON RoomTypes1.RoomTypeNumber = RESERVATIONROOMLINE.BookedTypeNumber
  WHERE (1=1) 
  AND (((
  ((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
  OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
  AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
  ) 
  AND ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
  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))
  GROUP BY CASE WHEN RoomTypes1.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
    
  SELECT @RemainingMonthNumberOfRoomsUpgradeRooms = NumberOfRooms
  FROM #TmpTableRemRoomsUpg
  WHERE RoomBedType = 0

  SELECT @RemainingMonthNumberOfRoomsUpgradeBeds = NumberOfRooms
  FROM #TmpTableRemRoomsUpg
  WHERE RoomBedType = 1

  DROP TABLE #TmpTableRemRoomsUpg
END
*/
-------------------- Remaining MONTH Upgrade rooms - END  -----------------------------------
------------------------------------------------------------------------------------
------------------- Remaining MONTH PART END  -----------------------------------------------
------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
-------------------  YEAR PART START  -----------------------------------------------
-------------------------------------------------------------------------------------
--------------------  YEAR WALKIN - START  ------------------------------------------
declare @yearnumberofromswalkin int
declare @yearnumberofromswalkinRooms int
declare @yearnumberofromswalkinBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 2, 1, 0, @yearnumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @yearnumberofromswalkinRooms OUTPUT, @yearnumberofromswalkinBeds OUTPUT

--------------------  YEAR WALKIN - END  -----------------------------------------
--------------------  YEAR Early departures - START  -----------------------------

DECLARE @YearEarlyDeparture INT
DECLARE @YearEarlyDepartureRooms INT
DECLARE @YearEarlyDepartureBeds INT
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 6, 1, 0, @YearEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @YearEarlyDepartureRooms OUTPUT, @YearEarlyDepartureBeds OUTPUT

--------------------  YEAR Early departures - END  -------------------------------
--------------------  YEAR INHOUSE - START  --------------------------------------
declare @yearnumberofrooms  int
DECLARE @YearAdult INT
DECLARE @YearChildren1 INT
DECLARE @YearChildren2 INT
DECLARE @YearPax INT

select @yearnumberofrooms = sum(ActualNumberOfRooms),
  @YearAdult = SUM(ISNULL(ActualAdult, 0)),
  @YearChildren1 = SUM(ActualChildren1),
  @YearChildren2 = SUM(ActualChildren2),
  @YearPax = SUM(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2)
from mistableView WITH (nolock) 
where datestamp like @year + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  DECLARE @yearnumberofroomsRooms int
  DECLARE @yearnumberofroomsBeds int
  DECLARE @yearnumberofroomsBookedRooms INT
  DECLARE @yearnumberofroomsBookedBeds INT
  DECLARE @yearAdultRooms INT
  DECLARE @yearAdultBeds INT
  DECLARE @yearChildren1Rooms INT
  DECLARE @yearChildren1Beds INT
  DECLARE @yearChildren2Rooms INT
  DECLARE @yearChildren2Beds INT
  DECLARE @yearPaxRooms INT
  DECLARE @yearPaxBeds INT

  SELECT sum(actualnumberofrooms) AS NumberOfRooms, 
    SUM(ISNULL(ActualAdult, 0)) AS NumberOfAdult,
    SUM(ActualChildren1) AS NumberOfChildren1,
    SUM(ActualChildren2) AS NumberOfChildren2,
    SUM(ISNULL(ActualAdult, 0)+ActualChildren1+ActualChildren2)  AS NumberOfPax, 
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTableYear
  from mistableView WITH (nolock) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + '%' and datestamp <= @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
    
  SELECT @yearnumberofroomsRooms = NumberOfRooms,
    @YearAdultRooms = NumberOfAdult,
    @YearChildren1Rooms = NumberOfChildren1,
    @YearChildren2Rooms = NumberOfChildren2,
    @YearPaxRooms = NumberOfPax
  FROM #TmpTableYear
  WHERE RoomBedType = 0

  SELECT @yearnumberofroomsBeds = NumberOfRooms,
    @YearAdultBeds = NumberOfAdult,
    @YearChildren1Beds = NumberOfChildren1,
    @YearChildren2Beds = NumberOfChildren2,
    @YearPaxBeds = NumberOfPax
  FROM #TmpTableYear
  WHERE RoomBedType = 1

  DROP TABLE #TmpTableYear
END

--------------------  YEAR INHOUSE - END  -----------------------------------------
--------------------  YEAR ARRIVALS - START  --------------------------------------
declare @yearnumberofroomsarrivals int
declare @yearnumberofroomsarrivalsRooms int
declare @yearnumberofroomsarrivalsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 1, 1, 0, @yearnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @yearnumberofroomsarrivalsRooms OUTPUT, @yearnumberofroomsarrivalsBeds OUTPUT

--------------------  YEAR ARRIVALS - END  -----------------------------------------
--------------------  YEAR DAYROOM - START  ----------------------------------------
declare @yearnumberofdayrooms int
declare @yearnumberofdayroomsRooms int
declare @yearnumberofdayroomsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 4, 1, 0, @yearnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @yearnumberofdayroomsRooms OUTPUT, @yearnumberofdayroomsBeds OUTPUT

--------------------  YEAR DAYROOM - END  ---------------------------------------
--------------------  YEAR OUT OF ORDER - START  ------------------------------
/*
declare @yearoutoforder int
declare @yearoutoforderRooms int
declare @yearoutoforderBeds int

SELECT @yearoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + '0101' THEN @Year + '0101' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + '0101'

IF @APerRoomBed = 1
BEGIN
  SELECT @yearoutoforderRooms = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + '0101' THEN @Year + '0101' ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + '0101'
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

  SELECT @yearoutoforderBeds = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + '0101' THEN @Year + '0101' ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + '0101'
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
END
*/

--------------------  YEAR OUT OF ORDER - END  --------------------------------
--------------------  YEAR NOSHOW - START  ------------------------------------
declare @yearnoshow int
declare @yearnoshowRooms int
declare @yearnoshowBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 3, 1, 0, @yearnoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @yearnoshowRooms OUTPUT, @yearnoshowBeds OUTPUT

--------------------  YEAR NOSHOW - END  ------------------------------------
--------------------  YEAR upgrade rooms - START  ---------------------------
declare @YearNumberOfRoomsUpgrade int
declare @YearNumberOfRoomsUpgradeRooms int
declare @YearNumberOfRoomsUpgradeBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 5, 1, 0, @YearNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @YearNumberOfRoomsUpgradeRooms OUTPUT, @YearNumberOfRoomsUpgradeBeds OUTPUT

-------------------- Year Upgrade rooms - END  ------------------------------------
--------------------  YEAR Rev/Par - START  ---------------------------------------
SELECT @YearPrice = sum(ActualPriceInclPackage), @YearPriceExPackage = sum(ActualPriceExPackage), @YearPriceExPackageExVat = sum(ActualPriceExPackageExVat), @YearPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp like @year + '%' and datestamp <= @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  SELECT
    sum(ActualPriceInclPackage) AS ActualPriceInclPackage, 
    sum(ActualPriceExPackage) AS ActualPriceExPackage, 
    sum(ActualPriceExPackageExVat) AS ActualPriceExPackageExVat, 
    sum(ActualPriceInclPackageExVat) AS ActualPriceInclPackageExVat,
    CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #TmpTableRevParYear
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + '%' and datestamp <= @rundate and valuetype = 1 and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT 
    @YearPriceRooms = ActualPriceInclPackage, 
    @YearPriceExPackageRooms = ActualPriceExPackage, 
    @YearPriceExPackageExVatRooms = ActualPriceExPackageExVat, 
    @YearPriceInclPackageExVatRooms = ActualPriceInclPackageExVat
  FROM #TmpTableRevParYear
  WHERE RoomBedType = 0

  SELECT 
    @YearPriceBeds = ActualPriceInclPackage, 
    @YearPriceExPackageBeds = ActualPriceExPackage, 
    @YearPriceExPackageExVatBeds = ActualPriceExPackageExVat, 
    @YearPriceInclPackageExVatBeds = ActualPriceInclPackageExVat
  FROM #TmpTableRevParYear
  WHERE RoomBedType = 1

  DROP TABLE #TmpTableRevParYear
END

-----------------------------------------------------------------------------------
-------------------  YEAR PART END  -----------------------------------------------
-----------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  Remaining YEAR PART START  -------------------------------------
--------------------------------------------------------------------------------------
--------------------  Remaining YEAR INHOUSE - START  --------------------------------------
declare @RemainingYearnumberofrooms  int
declare @RemainingYearnumberofroomsBooked INT
DECLARE @RemainingYearPax INT
DECLARE @RemainingYearAdults INT
DECLARE @RemainingYearChildren1 INT
DECLARE @RemainingYearChildren2 INT
DECLARE @RemainingYearPaxBooked INT
DECLARE @RemainingYearAdultsBooked INT
DECLARE @RemainingYearChildren1Booked INT
DECLARE @RemainingYearChildren2Booked INT

select @RemainingYearnumberofrooms = SUM(BookedNumberOfRooms)+SUM(ActualNumberOfRooms), 
  @RemainingYearPax = SUM(BookedAdult+BookedChildren1+BookedChildren2)+SUM(ActualAdult+ActualChildren1+ActualChildren2),
  @RemainingYearAdults = SUM(BookedAdult)+SUM(ActualAdult),
  @RemainingYearChildren1 = SUM(BookedChildren1)+SUM(ActualChildren1),
  @RemainingYearChildren2 = SUM(BookedChildren2)+SUM(ActualChildren2),
  @RemainingYearPrice = SUM(BookedPriceInclPackage)+SUM(ActualPriceInclPackage),
  @RemainingYearPriceExPackage = SUM(BookedPriceExPackage)+SUM(ActualPriceExPackage),
  @RemainingYearPriceExPackageExVat = SUM(BookedPriceExPackageExVat)+SUM(ActualPriceExPackageExVat),
  @RemainingYearPriceInclPackageExVat = SUM(BookedPriceInclPackageExVat)+SUM(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp like @year + '%' and datestamp > @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)

IF @APerRoomBed = 1
BEGIN
  DECLARE @RemainingYearnumberofroomsRooms INT
  DECLARE @RemainingYearPaxRooms INT
  DECLARE @RemainingYearAdultsRooms INT
  DECLARE @RemainingYearChildren1Rooms INT
  DECLARE @RemainingYearChildren2Rooms INT

  DECLARE @RemainingYearnumberofroomsBookedRooms INT
  DECLARE @RemainingYearPaxBookedRooms INT
  DECLARE @RemainingYearAdultsBookedRooms INT
  DECLARE @RemainingYearChildren1BookedRooms INT
  DECLARE @RemainingYearChildren2BookedRooms INT

  DECLARE @RemainingYearPriceRooms MONEY
  DECLARE @RemainingYearPriceExPackageRooms MONEY
  DECLARE @RemainingYearPriceExPackageExVatRooms MONEY
  DECLARE @RemainingYearPriceInclPackageExVatRooms MONEY

  DECLARE @RemainingYearPriceBookedRooms MONEY
  DECLARE @RemainingYearPriceExPackageBookedRooms MONEY
  DECLARE @RemainingYearPriceExPackageExVatBookedRooms MONEY
  DECLARE @RemainingYearPriceInclPackageExVatBookedRooms MONEY

  DECLARE @RemainingYearnumberofroomsBeds INT
  DECLARE @RemainingYearPaxBeds INT
  DECLARE @RemainingYearAdultsBeds INT
  DECLARE @RemainingYearChildren1Beds INT
  DECLARE @RemainingYearChildren2Beds INT

  DECLARE @RemainingYearnumberofroomsBookedBeds INT
  DECLARE @RemainingYearPaxBookedBeds INT
  DECLARE @RemainingYearAdultsBookedBeds INT
  DECLARE @RemainingYearChildren1BookedBeds INT
  DECLARE @RemainingYearChildren2BookedBeds INT

  DECLARE @RemainingYearPriceBeds MONEY
  DECLARE @RemainingYearPriceExPackageBeds MONEY
  DECLARE @RemainingYearPriceExPackageExVatBeds MONEY
  DECLARE @RemainingYearPriceInclPackageExVatBeds MONEY

  DECLARE @RemainingYearPriceBookedBeds MONEY
  DECLARE @RemainingYearPriceExPackageBookedBeds MONEY
  DECLARE @RemainingYearPriceExPackageExVatBookedBeds MONEY
  DECLARE @RemainingYearPriceInclPackageExVatBookedBeds MONEY

  select SUM(BookedNumberOfRooms)+SUM(ActualNumberOfRooms) AS RemainingYearnumberofrooms,
    SUM(BookedAdult+BookedChildren1+BookedChildren2)+SUM(ActualAdult+ActualChildren1+ActualChildren2) AS RemainingYearPax,
    SUM(BookedAdult)+SUM(ActualAdult) AS RemainingYearAdults,
    SUM(BookedChildren1)+SUM(ActualChildren1) AS RemainingYearChildren1,
    SUM(BookedChildren2)+SUM(ActualChildren2) AS RemainingYearChildren2,
    SUM(BookedPriceInclPackage)+SUM(ActualPriceInclPackage) AS RemainingYearPrice, 
    SUM(BookedPriceExPackage)+SUM(ActualPriceExPackage) AS RemainingYearPriceExPackage, 
    SUM(BookedPriceExPackageExVat)+SUM(ActualPriceExPackageExVat) AS RemainingYearPriceExPackageExVat, 
    SUM(BookedPriceInclPackageExVat)+SUM(ActualPriceInclPackageExVat) AS RemainingYearPriceInclPackageExVat,
	CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
  INTO #tmpTableRemainingYear
  from mistableView WITH (nolock) 
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = mistableView.RoomTypeNumber  
  where datestamp like @year + '%' and datestamp > @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) AND HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr))
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

  SELECT @RemainingYearnumberofroomsRooms = RemainingYearnumberofrooms,
    @RemainingYearPaxRooms = RemainingYearPax,
    @RemainingYearAdultsRooms = RemainingYearAdults,
    @RemainingYearChildren1Rooms = RemainingYearChildren1,
    @RemainingYearChildren2Rooms = RemainingYearChildren2,
    @RemainingYearPriceRooms = RemainingYearPrice, 
    @RemainingYearPriceExPackageRooms = RemainingYearPriceExPackage, 
    @RemainingYearPriceExPackageExVatRooms = RemainingYearPriceExPackageExVat, 
    @RemainingYearPriceInclPackageExVatRooms = RemainingYearPriceInclPackageExVat
  FROM #tmpTableRemainingYear
  WHERE RoomBedType = 0

  SELECT @RemainingYearnumberofroomsBeds = RemainingYearnumberofrooms,
    @RemainingYearPaxBeds = RemainingYearPax,
    @RemainingYearAdultsBeds = RemainingYearAdults,
    @RemainingYearChildren1Beds = RemainingYearChildren1,
    @RemainingYearChildren2Beds = RemainingYearChildren2,
    @RemainingYearPriceBeds = RemainingYearPrice, 
    @RemainingYearPriceExPackageBeds = RemainingYearPriceExPackage, 
    @RemainingYearPriceExPackageExVatBeds = RemainingYearPriceExPackageExVat, 
    @RemainingYearPriceInclPackageExVatBeds = RemainingYearPriceInclPackageExVat
  FROM #tmpTableRemainingYear
  WHERE RoomBedType = 1

  DROP TABLE #tmpTableRemainingYear
END

-------------------- Remaining Year INHOUSE - END  -----------------------------------------
-------------------- Remaining Year ARRIVALS - START  --------------------------------------
declare @RemainingYearnumberofroomsarrivals int
declare @RemainingYearnumberofroomsarrivalsRooms int
declare @RemainingYearnumberofroomsarrivalsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 1, 1, 1, @RemainingYearnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @RemainingYearnumberofroomsarrivalsRooms OUTPUT, @RemainingYearnumberofroomsarrivalsBeds OUTPUT

-------------------- Remaining Year ARRIVALS - END  -----------------------------------------
-------------------- Remaining Year DAYROOM - START  ----------------------------------------
declare @RemainingYearnumberofdayrooms int
declare @RemainingYearnumberofdayroomsRooms int
declare @RemainingYearnumberofdayroomsBeds int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 4, 1, 1, @RemainingYearnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty, @RemainingYearnumberofdayroomsRooms OUTPUT, @RemainingYearnumberofdayroomsBeds OUTPUT

-------------------- Remaining Year DAYROOM - END  ---------------------------------------
-------------------- Remaining Year OUT OF ORDER - START  ------------------------------
/*
declare @RemainingYearoutoforder int
declare @RemainingYearoutoforderRooms int
declare @RemainingYearoutoforderBeds int

SELECT @RemainingYearoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@Year + '1231' THEN @Year + '1231' ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 
  AND ArrivalDate<=@Year + '1231' 
  AND DepartureDate>=@rundate

IF @APerRoomBed = 1
BEGIN
  SELECT @RemainingYearoutoforderRooms = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@Year + '1231' THEN @Year + '1231' ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 
    AND ArrivalDate<=@Year + '1231' 
    AND DepartureDate>=@rundate
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

  SELECT @RemainingYearoutoforderBeds = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
    CONVERT(DATETIME, CASE WHEN ArrivalDate<@rundate THEN @rundate ELSE ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN DepartureDate>@Year + @Month + '31' THEN @Year + @Month + '31' ELSE DepartureDate END, 112)
    )), 0)
  FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
  INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
  INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0
    AND ArrivalDate<=@Year + '1231' 
    AND DepartureDate>=@rundate
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RM.RoomTypeNumber
    AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
END
*/

-------------------- Remaining Year OUT OF ORDER - END  --------------------------------
-------------------- Remaining Year upgrade rooms - START  ---------------------------
/*
  --- does not seams to be used....

declare @RemainingYearNumberOfRoomsUpgrade int
SELECT DISTINCT @RemainingYearNumberOfRoomsUpgrade = count(*)
FROM RESERVATIONROOMLINE WITH (NOLOCK) 
INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
WHERE (1=1) 
AND (((
((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
) 
AND ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
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))
*/
-------------------- Remaining Year Upgrade rooms - END  -----------------------------------
------------------------------------------------------------------------------------
------------------- Remaining Year PART END  -----------------------------------------------
------------------------------------------------------------------------------------

-- 1 = Rum Inhouse
-- 2 = Tidl. afrejse
-- 3 = Belęg. Procent

-- 6 = Reservationer Ankomster
-- 7 = Reservationer WalkIN
-- 8 = Reservationer Blivere
-- 9 = Reservationer NoShows
-- 10 = DayRooms
-- 11 = Rooms Upgraded

create table #temp_res 
(
  row_id int,
  today money,
  thismonth money,
  thisyear money, 
  budgetmonth money,
  budgetyear money
)

/*
SET @todayoutoforder = 0
SET @monthoutoforder = 0
SET @Yearoutoforder = 0
SET @Remainingmonthoutoforder = 0
SET @RemainingYearoutoforder = 0

SET @todayoutoforderRooms = 0
SET @todayoutoforderBeds = 0
SET @monthoutoforderRooms = 0
SET @monthoutoforderBeds = 0
SET @YearoutoforderRooms = 0
SET @YearoutoforderBeds = 0
SET @RemainingmonthoutoforderRooms = 0
SET @RemainingmonthoutoforderBeds = 0
SET @RemainingYearoutoforderRooms = 0
SET @RemainingYearoutoforderBeds = 0
*/

IF @inclVAT = 1 AND @inclPackage = 0
BEGIN
  SET @todayPrice = @TodayPriceExPackage
  SET @MonthPrice = @MonthPriceExPackage
  SET @RemainingMonthPrice = @RemainingMonthPriceExPackage
  SET @YearPrice = @YearPriceExPackage
  SET @RemainingYearPrice = @RemainingYearPriceExPackage

  SET @todayPriceRooms = @TodayPriceExPackageRooms
  SET @MonthPriceRooms = @MonthPriceExPackageRooms
  SET @RemainingMonthPriceRooms = @RemainingMonthPriceExPackageRooms
  SET @YearPriceRooms = @YearPriceExPackageRooms
  SET @RemainingYearPriceRooms = @RemainingYearPriceExPackageRooms

  SET @todayPriceBeds = @TodayPriceExPackageBeds
  SET @MonthPriceBeds = @MonthPriceExPackageBeds
  SET @RemainingMonthPriceBeds = @RemainingMonthPriceExPackageBeds
  SET @YearPriceBeds = @YearPriceExPackageBeds
  SET @RemainingYearPriceBeds = @RemainingYearPriceExPackageBeds
END
ELSE IF @inclVAT = 0 AND @inclPackage = 0
BEGIN
  SET @todayPrice = @TodayPriceExPackageExVat
  SET @MonthPrice = @MonthPriceExPackageExVat
  SET @RemainingMonthPrice = @RemainingMonthPriceExPackageExVat
  SET @YearPrice = @YearPriceExPackageExVat
  SET @RemainingYearPrice = @RemainingYearPriceExPackageExVat

  SET @todayPriceRooms = @TodayPriceExPackageExVatRooms
  SET @MonthPriceRooms = @MonthPriceExPackageExVatRooms
  SET @RemainingMonthPriceRooms = @RemainingMonthPriceExPackageExVatRooms
  SET @YearPriceRooms = @YearPriceExPackageExVatRooms
  SET @RemainingYearPriceRooms = @RemainingYearPriceExPackageExVatRooms

  SET @todayPriceBeds = @TodayPriceExPackageExVatBeds
  SET @MonthPriceBeds = @MonthPriceExPackageExVatBeds
  SET @RemainingMonthPriceBeds = @RemainingMonthPriceExPackageExVatBeds
  SET @YearPriceBeds = @YearPriceExPackageExVatBeds
  SET @RemainingYearPriceBeds = @RemainingYearPriceExPackageExVatBeds
END
ELSE IF @inclVAT = 0 AND @inclPackage = 1
BEGIN
  SET @todayPrice = @TodayPriceInclPackageExVat
  SET @MonthPrice = @MonthPriceInclPackageExVat
  SET @RemainingMonthPrice = @RemainingMonthPriceInclPackageExVat
  SET @YearPrice = @YearPriceInclPackageExVat
  SET @RemainingYearPrice = @RemainingYearPriceInclPackageExVat

  SET @todayPriceRooms = @TodayPriceInclPackageExVatRooms
  SET @MonthPriceRooms = @MonthPriceInclPackageExVatRooms
  SET @RemainingMonthPriceRooms = @RemainingMonthPriceInclPackageExVatRooms
  SET @YearPriceRooms = @YearPriceInclPackageExVatRooms
  SET @RemainingYearPriceRooms = @RemainingYearPriceInclPackageExVatRooms

  SET @todayPriceBeds = @TodayPriceInclPackageExVatBeds
  SET @MonthPriceBeds = @MonthPriceInclPackageExVatBeds
  SET @RemainingMonthPriceBeds = @RemainingMonthPriceInclPackageExVatBeds
  SET @YearPriceBeds = @YearPriceInclPackageExVatBeds
  SET @RemainingYearPriceBeds = @RemainingYearPriceInclPackageExVatBeds
END

DECLARE @TodayTurnoverperHour MONEY
IF ISNULL(@AWorkHour, 0) > 0
  SET @TodayTurnoverperHour = (ISNULL(@todayPrice, 0) + ISNULL(@AAdditionalTO, 0)) / @AWorkHour
ELSE
  SET @TodayTurnoverperHour = 0

DECLARE @Remainingmonth MONEY
DECLARE @RemainingmonthRooms MONEY
DECLARE @RemainingmonthBeds MONEY
DECLARE @RemainingYear MONEY
DECLARE @RemainingYearRooms MONEY
DECLARE @RemainingYearBeds MONEY

SET @Remainingmonth = (convert(money, (isnull(@Remainingmonthnumberofrooms, 0) + ISNULL(@Remainingmonthnumberofdayrooms, 0))))
IF ISNULL(@Remainingmonth, 0) = 0
  SET @Remainingmonth = 1
SET @RemainingmonthRooms = (convert(money, (isnull(@RemainingmonthnumberofroomsRooms, 0) + ISNULL(@RemainingmonthnumberofdayroomsRooms, 0))))
IF ISNULL(@RemainingmonthRooms, 0) = 0
  SET @RemainingmonthRooms = 1 
SET @RemainingmonthBeds = (convert(money, (isnull(@RemainingmonthnumberofroomsBeds, 0) + ISNULL(@RemainingmonthnumberofdayroomsBeds, 0))))
IF ISNULL(@RemainingmonthBeds, 0) = 0
  SET @RemainingmonthBeds = 1 

SET @RemainingYear = (convert(money, (isnull(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofdayrooms, 0))))
IF ISNULL(@RemainingYear, 0) = 0
  SET @RemainingYear = 1 
SET @RemainingYearRooms = (convert(money, (isnull(@RemainingYearnumberofroomsRooms, 0) + ISNULL(@RemainingYearnumberofdayroomsRooms, 0))))
IF ISNULL(@RemainingYearRooms, 0) = 0
  SET @RemainingYearRooms = 1 
SET @RemainingYearBeds = (convert(money, (isnull(@RemainingYearnumberofroomsBeds, 0) + ISNULL(@RemainingYearnumberofdayroomsBeds, 0))))
IF ISNULL(@RemainingYearBeds, 0) = 0
  SET @RemainingYearBeds = 1 

-- 1 = Rum Inhouse
select @todaynumberofrooms as todaynumberofrooms, @monthnumberofrooms as monthnumberofrooms, @yearnumberofrooms as yearnumberofrooms, @Remainingmonthnumberofrooms as monthbudgetnumberofrooms, @RemainingYearnumberofrooms as yearbudgetnumberofrooms, 
-- 2 = Tidl. afrejse
@TodayEarlyDeparture as todayearly, @MonthEarlyDeparture as monthearly, @YearEarlyDeparture as yearearly, --0 as monthbudgetearly, 0 as yearbudgetearly,

-- 3 = Belęg. Procent
CAST(0 AS FLOAT) as toDayOccPercent, 
CAST(0 AS FLOAT) as MonthOccPercent, 
CAST(0 AS FLOAT) as YearOccPercent, 
CAST(0 AS FLOAT) as RemainingMonthOccPercent, 
CAST(0 AS FLOAT) as RemainingYearOccPercent, 

-- 4 = Pax
@RemainingmonthPax as monthbudgetpax, 
@RemainingmonthAdults as monthbudgetAdults, 
@RemainingmonthChildren1 as monthbudgetChildren1, 
@RemainingmonthChildren2 as monthbudgetChildren2, 
@RemainingYearPax as yearbudgetpax,
@RemainingYearAdults as yearbudgetAdults,
@RemainingYearChildren1 as yearbudgetChildren1,
@RemainingYearChildren2 as yearbudgetChildren2,

-- 5 = Pax / Rum
convert(money, isnull(@RemainingmonthPax, 1)) / @Remainingmonth as monthbudgetpaxperroom, 
convert(money, isnull(@RemainingmonthAdults, 1)) / @Remainingmonth as monthbudgetAdultsperroom, 
convert(money, isnull(@RemainingmonthChildren1, 1)) / @Remainingmonth as monthbudgetChildren1perroom, 
convert(money, isnull(@RemainingmonthChildren2, 1)) / @Remainingmonth as monthbudgetChildren2perroom, 
convert(money, isnull(@RemainingYearPax, 1)) / @RemainingYear as yearbudgetpaxperroom, 
convert(money, isnull(@RemainingYearAdults, 1)) / @RemainingYear as yearbudgetAdultsperroom, 
convert(money, isnull(@RemainingYearChildren1, 1)) / @RemainingYear as yearbudgetChildren1perroom, 
convert(money, isnull(@RemainingYearChildren2, 1)) / @RemainingYear as yearbudgetChildren2perroom, 

-- 6 = Reservationer Ankomster
@todaynumberofroomsarrivals as todaynumberofroomsarrivals, @monthnumberofroomsarrivals as monthnumberofroomsarrivals, @yearnumberofroomsarrivals as yearnumberofroomsarrivals, @Remainingmonthnumberofroomsarrivals as monthbudgetnumberofroomsarrivals, @RemainingYearnumberofroomsarrivals as yearbudgetnumberofroomsarrivals, 

-- 7 = Reservationer WalkIN
@todaynumberofromswalkin as todaynumberofromswalkin, @monthnumberofromswalkin as monthnumberofromswalkin, @yearnumberofromswalkin as yearnumberofromswalkin, --0 as monthbudgetnumberofromswalkin, 0 as yearbudgetnumberofromswalkin, 

-- 8 = Reservationer Blivere
CASE WHEN @todaynumberofrooms-@todaynumberofroomsarrivals < 0 THEN 0 ELSE @todaynumberofrooms-@todaynumberofroomsarrivals END as todaystays,
CASE WHEN @monthnumberofrooms-@monthnumberofroomsarrivals < 0 THEN 0 ELSE @monthnumberofrooms-@monthnumberofroomsarrivals END as monthstays,
CASE WHEN @yearnumberofrooms-@yearnumberofroomsarrivals < 0 THEN 0 ELSE @yearnumberofrooms-@yearnumberofroomsarrivals END as yearstays,
CASE WHEN @Remainingmonthnumberofrooms-@Remainingmonthnumberofroomsarrivals < 0 THEN 0 ELSE @Remainingmonthnumberofrooms-@Remainingmonthnumberofroomsarrivals END as monthBudgetstays,
CASE WHEN @RemainingYearnumberofrooms-@RemainingYearnumberofroomsarrivals <0 THEN 0 ELSE @RemainingYearnumberofrooms-@RemainingYearnumberofroomsarrivals END as YearBudgetstays,

-- 9 = Reservationer NoShows
@todaynoshow as todaynoshow, @monthnoshow as monthnoshow, @yearnoshow as yearnoshow, --0 as monthbudgetnoshow, 0 as yearbudgetnoshow,

-- 10 = DayRooms
@todaynumberofdayrooms as todaynumberofdayrooms, @monthnumberofdayrooms as monthnumberofdayrooms, @yearnumberofdayrooms as yearnumberofdayrooms,
@RemainingMonthNumberOfDayrooms as monthBudgetnumberofdayrooms, @RemainingYearNumberOfDayrooms as YearBudgetnumberofdayrooms,

-- 11 = Rooms Upgraded
@TodayNumberOfRoomsUpgrade as TodayNumberOfRoomsUpgrade, @MonthNumberOfRoomsUpgrade as MonthNumberOfRoomsUpgrade, @YearNumberOfRoomsUpgrade as YearNumberOfRoomsUpgrade,
-- 12 = revPar  
@todayPrice as TodayRevPar, @monthPrice AS MonthRevPar, @yearPrice AS YearRevPar, 
@RemainingmonthPrice AS MonthBudgetRevPar, @RemainingYearPrice AS YearBudgetRevPar,
-- 13 = OOO
0 AS TodayOutOfOrder, 0 AS MonthOutOfOrder, 0 AS YearOutOfOrder, 0 AS RemainingMonthOutOfOrder, 0 AS RemainingYearOutOfOrder,

-- 14 = TurnoverperHour
@TodayTurnoverperHour AS TodayTurnoverperHour,

-- 15 = Pax
@TodayAdult AS TodayAdult, @TodayChildren1 AS TodayChildren1, @TodayChildren2 AS TodayChildren2, @TodayPax AS TodayPax,
@MonthAdult AS MonthAdult, @MonthChildren1 AS MonthChildren1, @MonthChildren2 AS MonthChildren2, @MonthPax AS MonthPax,
@YearAdult AS YearAdult, @YearChildren1 AS YearChildren1, @YearChildren2 AS YearChildren2, @YearPax AS YearPax,

-- Per Room/Bed
-- 1 = Rum Inhouse
@TodayNumberOfRoomsRooms as TodayNumberOfRoomsRooms, @TodayNumberOfRoomsBeds AS TodayNumberOfRoomsBeds,
@monthnumberofroomsRooms as monthnumberofroomsRooms, @monthnumberofroomsBeds AS monthnumberofroomsBeds,
@yearnumberofroomsRooms as yearnumberofroomsRooms, @yearnumberofroomsBeds as yearnumberofroomsBeds,
@RemainingmonthnumberofroomsRooms as monthbudgetnumberofroomsRooms, @RemainingmonthnumberofroomsBeds as monthbudgetnumberofroomsBeds,
@RemainingYearnumberofroomsRooms as yearbudgetnumberofroomsRooms, @RemainingYearnumberofroomsBeds as yearbudgetnumberofroomsBeds, 
-- 2 = Tidl. afrejse
@TodayEarlyDepartureRooms as todayearlyRooms, @TodayEarlyDepartureBeds as todayearlyBeds,
@MonthEarlyDepartureRooms as monthearlyRooms, @MonthEarlyDepartureBeds as monthearlyBeds,
@YearEarlyDepartureRooms as yearearlyRooms, @YearEarlyDepartureBeds as yearearlyBeds,
-- 3 = Belęg. Procent
CAST(0 AS FLOAT) as toDayOccPercentRooms, CAST(0 AS FLOAT) as toDayOccPercentBeds, 
CAST(0 AS FLOAT) as MonthOccPercentRooms, CAST(0 AS FLOAT) as MonthOccPercentBeds, 
CAST(0 AS FLOAT) as YearOccPercentRooms, CAST(0 AS FLOAT) as YearOccPercentBeds, 
CAST(0 AS FLOAT) as RemainingMonthOccPercentRooms, CAST(0 AS FLOAT) as RemainingMonthOccPercentBeds, 
CAST(0 AS FLOAT) as RemainingYearOccPercentRooms, CAST(0 AS FLOAT) as RemainingYearOccPercentBeds,
-- 4 = Pax
@RemainingmonthPaxRooms as monthbudgetpaxRooms, @RemainingmonthPaxBeds as monthbudgetpaxBeds, 
@RemainingmonthAdultsRooms as monthbudgetAdultsRooms, @RemainingmonthAdultsBeds as monthbudgetAdultsBeds, 
@RemainingmonthChildren1Rooms as monthbudgetChildren1Rooms, @RemainingmonthChildren1Beds as monthbudgetChildren1Beds, 
@RemainingmonthChildren2Rooms as monthbudgetChildren2Rooms, @RemainingmonthChildren2Beds as monthbudgetChildren2Beds, 
@RemainingYearPaxRooms as yearbudgetpaxRooms, @RemainingYearPaxBeds as yearbudgetpaxBeds,
@RemainingYearAdultsRooms as yearbudgetAdultsRooms, @RemainingYearAdultsBeds as yearbudgetAdultsBeds,
@RemainingYearChildren1Rooms as yearbudgetChildren1Rooms, @RemainingYearChildren1Beds as yearbudgetChildren1Beds,
@RemainingYearChildren2Rooms as yearbudgetChildren2Rooms, @RemainingYearChildren2Beds as yearbudgetChildren2Beds,
-- 5 = Pax / Rum
convert(money, isnull(@RemainingmonthPaxRooms, 1)) / @RemainingmonthRooms as monthbudgetpaxperroomRooms, 
convert(money, isnull(@RemainingmonthAdultsRooms, 1)) / @RemainingmonthRooms as monthbudgetAdultsperroomRooms, 
convert(money, isnull(@RemainingmonthChildren1Rooms, 1)) / @RemainingmonthRooms as monthbudgetChildren1perroomRooms, 
convert(money, isnull(@RemainingmonthChildren2Rooms, 1)) / @RemainingmonthRooms as monthbudgetChildren2perroomRooms, 
convert(money, isnull(@RemainingYearPaxRooms, 1)) / @RemainingYearRooms as yearbudgetpaxperroomRooms, 
convert(money, isnull(@RemainingYearAdultsRooms, 1)) / @RemainingYearRooms as yearbudgetAdultsperroomRooms, 
convert(money, isnull(@RemainingYearChildren1Rooms, 1)) / @RemainingYearRooms as yearbudgetChildren1perroomRooms, 
convert(money, isnull(@RemainingYearChildren2Rooms, 1)) / @RemainingYearRooms as yearbudgetChildren2perroomRooms, 

convert(money, isnull(@RemainingmonthPaxBeds, 1)) / @RemainingmonthBeds as monthbudgetpaxperroomBeds, 
convert(money, isnull(@RemainingmonthAdultsBeds, 1)) / @RemainingmonthBeds as monthbudgetAdultsperroomBeds, 
convert(money, isnull(@RemainingmonthChildren1Beds, 1)) / @RemainingmonthBeds as monthbudgetChildren1perroomBeds, 
convert(money, isnull(@RemainingmonthChildren2Beds, 1)) / @RemainingmonthBeds as monthbudgetChildren2perroomBeds, 
convert(money, isnull(@RemainingYearPaxBeds, 1)) / @RemainingYearBeds as yearbudgetpaxperroomBeds, 
convert(money, isnull(@RemainingYearAdultsBeds, 1)) / @RemainingYearBeds as yearbudgetAdultsperroomBeds, 
convert(money, isnull(@RemainingYearChildren1Beds, 1)) / @RemainingYearBeds as yearbudgetChildren1perroomBeds, 
convert(money, isnull(@RemainingYearChildren2Beds, 1)) / @RemainingYearBeds as yearbudgetChildren2perroomBeds, 
-- 6 = Reservationer Ankomster
@todaynumberofroomsarrivalsRooms as todaynumberofroomsarrivalsRooms, @todaynumberofroomsarrivalsBeds as todaynumberofroomsarrivalsBeds,
@monthnumberofroomsarrivalsRooms as monthnumberofroomsarrivalsRooms, @monthnumberofroomsarrivalsBeds as monthnumberofroomsarrivalsBeds,
@yearnumberofroomsarrivalsRooms as yearnumberofroomsarrivalsRooms, @yearnumberofroomsarrivalsBeds as yearnumberofroomsarrivalsBeds, 
@RemainingmonthnumberofroomsarrivalsRooms as monthbudgetnumberofroomsarrivalsRooms, @RemainingmonthnumberofroomsarrivalsBeds as monthbudgetnumberofroomsarrivalsBeds,
@RemainingYearnumberofroomsarrivalsRooms as yearbudgetnumberofroomsarrivalsRooms, @RemainingYearnumberofroomsarrivalsBeds as yearbudgetnumberofroomsarrivalsBeds, 
-- 7 = Walkin
@todaynumberofromswalkinRooms AS todaynumberofromswalkinRooms, @todaynumberofromswalkinBeds AS todaynumberofromswalkinBeds,
@monthnumberofromswalkinRooms as monthnumberofromswalkinRooms, @monthnumberofromswalkinBeds as monthnumberofromswalkinBeds,
@yearnumberofromswalkinRooms as yearnumberofromswalkinRooms, @yearnumberofromswalkinBeds as yearnumberofromswalkinBeds, 
-- 8 = Reservationer Blivere
CASE WHEN @todaynumberofroomsRooms-@todaynumberofroomsarrivalsRooms < 0 THEN 0 ELSE @todaynumberofroomsRooms-@todaynumberofroomsarrivalsRooms END as todaystaysRooms, CASE WHEN @todaynumberofroomsBeds-@todaynumberofroomsarrivalsBeds < 0 THEN 0 ELSE @todaynumberofroomsBeds-@todaynumberofroomsarrivalsBeds END as todaystaysBeds,
CASE WHEN @monthnumberofroomsRooms-@monthnumberofroomsarrivalsRooms < 0 THEN 0 ELSE @monthnumberofroomsRooms-@monthnumberofroomsarrivalsRooms END as monthstaysRooms, CASE WHEN @monthnumberofroomsBeds-@monthnumberofroomsarrivalsBeds < 0 THEN 0 ELSE @monthnumberofroomsBeds-@monthnumberofroomsarrivalsBeds END as monthstaysBeds,
CASE WHEN @yearnumberofroomsRooms-@yearnumberofroomsarrivalsRooms < 0 THEN 0 ELSE @yearnumberofroomsRooms-@yearnumberofroomsarrivalsRooms END as yearstaysRooms, CASE WHEN @yearnumberofroomsBeds-@yearnumberofroomsarrivalsBeds < 0 THEN 0 ELSE @yearnumberofroomsBeds-@yearnumberofroomsarrivalsBeds END as yearstaysBeds,
CASE WHEN @Remainingmonthnumberofroomsrooms-@Remainingmonthnumberofroomsarrivalsrooms < 0 THEN 0 ELSE @Remainingmonthnumberofroomsrooms-@Remainingmonthnumberofroomsarrivalsrooms END as monthBudgetstaysrooms, CASE WHEN @RemainingmonthnumberofroomsBeds-@RemainingmonthnumberofroomsarrivalsBeds < 0 THEN 0 ELSE @RemainingmonthnumberofroomsBeds-@RemainingmonthnumberofroomsarrivalsBeds END as monthBudgetstaysBeds,
CASE WHEN @RemainingYearnumberofroomsRooms-@RemainingYearnumberofroomsarrivalsRooms < 0 THEN 0 ELSE @RemainingYearnumberofroomsRooms-@RemainingYearnumberofroomsarrivalsRooms END as YearBudgetstaysRooms, CASE WHEN @RemainingYearnumberofroomsBeds-@RemainingYearnumberofroomsarrivalsBeds < 0 THEN 0 ELSE @RemainingYearnumberofroomsBeds-@RemainingYearnumberofroomsarrivalsBeds END as YearBudgetstaysBeds,
-- 9 = Reservationer NoShows
@todaynoshowRooms as todaynoshowRooms, @todaynoshowBeds as todaynoshowBeds, 
@monthnoshowRooms as monthnoshowRooms, @monthnoshowBeds as monthnoshowBeds,
@yearnoshowRooms as yearnoshowRooms, @yearnoshowBeds as yearnoshowBeds,
-- 10 = DayRooms 
@todaynumberofdayroomsRooms as todaynumberofdayroomsRooms, @todaynumberofdayroomsBeds as todaynumberofdayroomsBeds,
@monthnumberofdayroomsRooms as monthnumberofdayroomsRooms, @monthnumberofdayroomsBeds as monthnumberofdayroomsBeds,
@yearnumberofdayroomsRooms as yearnumberofdayroomsRooms, @yearnumberofdayroomsBeds as yearnumberofdayroomsBeds,
@RemainingMonthNumberOfDayroomsRooms as monthBudgetnumberofdayroomsRooms, @RemainingMonthNumberOfDayroomsBeds as monthBudgetnumberofdayroomsBeds, 
@RemainingYearNumberOfDayroomsRooms as YearBudgetnumberofdayroomsRooms, @RemainingYearNumberOfDayroomsBeds as YearBudgetnumberofdayroomsBeds,

-- 11 = Rooms Upgraded
@TodayNumberOfRoomsUpgradeRooms as TodayNumberOfRoomsUpgradeRooms, @TodayNumberOfRoomsUpgradeBeds as TodayNumberOfRoomsUpgradeBeds,
@MonthNumberOfRoomsUpgradeRooms as MonthNumberOfRoomsUpgradeRooms, @MonthNumberOfRoomsUpgradeBeds as MonthNumberOfRoomsUpgradeBeds,
@YearNumberOfRoomsUpgradeRooms as YearNumberOfRoomsUpgradeRooms, @YearNumberOfRoomsUpgradeBeds as YearNumberOfRoomsUpgradeBeds, 
-- 12 = revPar  
@todayPriceRooms as TodayRevParRooms, @todayPriceBeds as TodayRevParBeds,
@monthPriceRooms AS MonthRevParRooms, @monthPriceBeds AS MonthRevParBeds,
@yearPriceRooms AS YearRevParRooms, @yearPriceBeds AS YearRevParBeds,
@RemainingmonthPriceRooms AS MonthBudgetRevParRooms, @RemainingmonthPriceBeds AS MonthBudgetRevParBeds, 
@RemainingYearPriceRooms AS YearBudgetRevParRooms, @RemainingYearPriceBeds AS YearBudgetRevParBeds,
-- 13 = OOO
0 AS TodayOutOfOrderRooms, 0 AS TodayOutOfOrderBeds, 
0 AS MonthOutOfOrderRooms, 0 AS MonthOutOfOrderBeds, 
0 AS YearOutOfOrderRooms, 0 AS YearOutOfOrderBeds,
0 AS RemainingMonthOutOfOrderRooms, 0 AS RemainingMonthOutOfOrderBeds, 
0 AS RemainingYearOutOfOrderRooms, 0 AS RemainingYearOutOfOrderBeds,

-- 15 = Pax
@TodayAdultRooms AS TodayAdultRooms, @TodayAdultBeds AS TodayAdultBeds, 
@TodayChildren1Rooms AS TodayChildren1Rooms, @TodayChildren1Beds AS TodayChildren1Beds, 
@TodayChildren2Rooms AS TodayChildren2Rooms, @TodayChildren2Beds AS TodayChildren2Beds, 
@TodayPaxRooms AS TodayPaxRooms, @TodayPaxBeds AS TodayPaxBeds,
@MonthAdultRooms AS MonthAdultRooms, @MonthAdultBeds AS MonthAdultBeds, 
@MonthChildren1Rooms AS MonthChildren1Rooms, @MonthChildren1Beds AS MonthChildren1Beds, 
@MonthChildren2Rooms AS MonthChildren2Rooms, @MonthChildren2Beds AS MonthChildren2Beds, 
@MonthPaxRooms AS MonthPaxRooms, @MonthPaxBeds AS MonthPaxBeds,
@YearAdultRooms AS YearAdultRooms, @YearAdultBeds AS YearAdultBeds, 
@YearChildren1Rooms AS YearChildren1Rooms, @YearChildren1Beds AS YearChildren1Beds, 
@YearChildren2Rooms AS YearChildren2Rooms, @YearChildren2Beds AS YearChildren2Beds, 
@YearPaxRooms AS YearPaxRooms, @YearPaxBeds AS YearPaxBeds

DROP TABLE #temp_res