CREATE PROCEDURE [dbo].[OnlineCheckDominoAvailability] @ANumberOfRooms SMALLINT, @AAdults SMALLINT,
  @AChildren1 SMALLINT, @AChildren2 SMALLINT, @AStartDate DATETIME, @AEndDate DATETIME,
  @ProductTypeNumber INT, @ErrorLevel INT OUTPUT, @ErrorFilterNumber INT OUTPUT,
  @ErrorDateStamp DATETIME OUTPUT, @ACustomerNumber INT = 0, @AOnlineRoomTypeNumber INT = 0,
  @AHotelNumber VARCHAR(100) = '', @ACustomerRateNumber INT = 0, @AResource TINYINT = 0, @ACheckavailability BIT = 1, 
  @ACountryNumber INT = 1, @AvailabilitySummeryproductMode INT = 2, @IsOwnerBooking BIT = 0, @CampaignCodeText VARCHAR(50) = '', 
  @AIncludeAllPaxPrices BIT = 0, @AAllotmentNumber INT = 0 AS
SET NOCOUNT ON

DECLARE @dt DATETIME
SET @dt = GETDATE()

IF @ANumberOfRooms is null and @AAdults is null 
BEGIN
  SELECT
  cast(null AS INT) AS RoomTypeNumber,
  cast(null AS INT) AS ProductNumber,
  cast(null AS VARCHAR(128)) AS Description,
  cast(null AS MONEY) AS RoomPriceFirstDay,
  cast(null AS MONEY) AS RoomPrice,
  cast(null AS VARCHAR(255)) AS ProductName,
  cast(null AS MONEY) AS RoomPricePerDay,
  cast(null AS MONEY) AS RoomPriceMinTotal,
  cast(null AS INT) AS OnlineRoomTypeNumber,
  cast(null AS TINYINT) AS OnlinePriority,
  cast(null AS VARCHAR(50)) AS RomTypeName
END
/*
Created by Jacob Pedersen January 2004

Changes:
 AHE20220112: MinRoomsAvail added to temp table #VacantRooms when @AIncludeAllPaxPrices=1
 MLJ 2022-01-10  Bug Fix: Wrong availability when MaxRooms=0
 MLJ 2022-01-04: Bug fix: - Correction to total overbooking check
 MLJ 2021-12-20: Bug fixes: 
                   - Overbooking with MaxRooms<0 not implemented correct.
				   - Total overbooking on all room types did not calculate correctly when stay was more than one day
 AHE20211006: If totally overbooked, return ErrorLevel20
 PHJ20211004: MinRoomsAvail [smallint] NULL added to temp table #VacantRooms
 AHE20210819: @TotalNumberOfVacantRooms changed from SMALLINT to INT
 AHE20201215: Check for ClosedtoArrival on product
 KJ 20191011: Bug: Fix for "Showing records even if no vacancy in whole period".
 KJ 20190903: Added Bonuspoint fields
 AHE20190319: '@AGetPackage = 0' added to ProductPricesDay
 AHE20181116: @VacantRoomHotelNumber inserted in line 628
 KJ 20181018: Added show prices for all pax
 TBU20180220: Moved INNER JOIN ProductExtended proe for customerlogin
 TBU20180214: Added IsNull(bunkbed, 0)
 TBU20170918: Added check on @CalcTotalPrice>0
 TBU20170905: Added onlineuseroombookingstatus
 TBU20170824: Changed products for Customerlogin
 TBU20170816: Added @CustomerNumberForHotel
 TBU20170607: Removed print
 TBU20170607: Added Check on Vacantrooms
 TBU20170606: Changed name to OnlineCheckDominoAvailability
 TBU20170531: Changed to used with bunkedbed + roomtype names per pax 
 TBU20160921: Added @ACustomerRateNumber = 5
 TBU20160909: Added: Errorlevel 18 : No rooms availabel. MaxPax is greter than MaxPaxTotal
 TBU20160609: Changed ORDER BY HotelNumber, ProductNumber, RoomTypeNumber, [Description]
 TBU20160601: Added OnlineRoomBookingStatus
 TBU/KC20160524: Added #AVAILCUR
 TBU20160511: Added delete from #HotelNumbers
 TBU20160322: Added @IgnoreRoomtypeResources
 TBU20160222: Added @UseCourseRoomBooking
 TBU20161106: Added Check RoomPriceFirstDay
 TBU20150903: Added pax=0 at OnlineRoomtypeTranslated
 TBU20150708: Changed problem with @YieldNumber
 TB20150217: Added @NumberOfNotSaleable
 TB20140708: Added ProductOnlineClosedtoArrivaldays 8.0+  -  pro.. Error
 TB20140707: Added ProductOnlineClosedtoArrivaldays 8.0+
 KJ 20140702: Bug: Fix for "Field added in GenerateAvailability". Case #CR074080
 KJ/TB 20140625: Only show products from selected hotel. 
 TB20140625: Added Check for MinPax on OnlineRoomtypes - ONLY 8.0+
 TB20140230: Added @CampaignCodeText + @ACustomerRateNumber = 4
 TB20140109: Changed Check Zero Price from product. Use RoomPriceMinTotal = 0 AND UseZeroPrice = 0 --RoomPricePerDay
 TB20140109: Changed Check Zero Price from product. Added WITH (Nolock)
 TB20140107: Added Check Zero Price from product
 TB20130404: Changed @VacantRoomColorCount - Only count arrivaldate
 KS20130212: Overbooking is now possible, if "MaxRooms" is set to a negative number.
 LJE20121221: When Description, ProductName is NULL - show "".
 TB20121123: Added @VacantRoomColorCount
LJE20120501: When "@ACheckavailability=0" - do not update #VacantRooms table. Oure problem with H1, H2, H3, H4, H5 roomtypes.
 TB20120418: Removed @AvailabilityOnlySummeryproduct. Added @IsOwnerBooking. SET @MinStayPrice=0
 KC20120411: FIxed missing parameter in line 559	
 KC20120403: Added NumberofOutForService to RoomBookingStatus2 	
 TB20120210: Added @AvailabilitySummeryproductMode
 TB20120206: Added @AvailabilityOnlySummeryproduct AND check for children AND @BookWithZeroPriceperday - 
 TB20120126: New PAX Check
 TB20120111: Remove prices where roomprice > 0
 TB20110623: Changed Check on maxrooms with more filters: SELECT @Found=COUNT(*) FROM RoomBookingStatusYield
 TB20110620: Changed Check on Maxpax.
 TB20110315: Added check for maxrooms per roomtype - SELECT @Found=COUNT(*) FROM YieldOnlineRoomTypesXTab...
 TB20100325: Added Check for Min. Price All days + @Addon
 TB20100324: Added Check for Min. Price All days
 TB20100309: Added check for PAX
 TB20091109: Added check for Producttypes
 TB20090323: Added @ACountryNumber
 TB20090312: Added ProductName
 TB20090211: Added @ACheckavailability - If true there will be no check for overbooking.
 TB20080930: Added @AResource
 TB20080807: Added all OvernightProductNumber to  @ACustomerRateNumber = 3
 TB20080806: Removed allowfilterpicassoonline in case of @ACustomerRateNumber = 3
 AV 20080304 DominoEnabled variable added
 TB20080222: Added ISNULL in the end. At SELECT #ProductPrices
 TB20071120: Added NOLOCK at @ACustomerRateNumber = 3
 TB20071002 -  Find Compant/Agency if Contact Person is logged in
 TB20070830: Added Check for Count on #ProductPrices. before check for errlvl 16
 JP20070829 EXT: MinRoomPricePerDay: When no children minimum prices are defined the prices are calculated without children and later recalculated with children. So check is done without children but result price includes children
 --REVERTED   JP 20070620: EXT: multiply with period
 TB20070815:  Added Check on MaxPaxTotal from OnlineRoomTypes
 JP 20070619: EXT: added load of @MinPricePerDay from YieldOnlineRoomMinimumPrice if available
 JP20070503: Changed last parameter to GenerateAvailability to 0
 TB20070207: Added @ACustomerRateNumber-  Find product for a customer that is logged in - ONLY 3.38
 JP+NLB20060728: Added       AND dbo.DayColorMatches (@AHotelNumber, Products.ProductNumber, @AStartDate, 0)=1 -- @VacantRooms = 0 TODO implement support for auto coloring
 TB20060717:  Added Check on DayProduct = 0
 TB20050504:  Don´t count filter on departureday
 

NOTES: 	- @ValidProductCount is important as it is required that products match whole reservation period. This will be a problem if a product goes to 31/12 and another takes over 1/1. Then you cannot make reservations from dec to jan but that could be fixed with +product in later version

	- @OnlineRoomTypeNumber is not used since we always return all possibilites so user can select the best option for him
	
	ONLY USE FOR 3.38
@AvailabilitySummeryproductMode: 
	0= Avialability without summeryproduct
	1= Avialability with summeryproduct
	2= Avialability with both normal and summeryproduct
*/
SET @ErrorLevel = 00
DECLARE @SpecialErrorLevel INT
SET @SpecialErrorLevel = 00  -- Used for errors which does not abort immediately, for example 12

/*
  Error Levels:
  00 : No errors
  01 : Max days exceeded
  02 : Min days requirement not met
  03 : Not whole week(s)
  04 : DayColor not correct
  05 : Arrival not possible on this day
  06 : Arrival too close to today
  07 : No valid products in period
  08 : No yield filter specified for the day
  09 : Not correct number of days
  10 : Minimum stay price not reached
  11 : Minimum price per day not reached
  12 : Maximum number of rooms possible in roomtype exceeded
  13 : The number of rooms is not available in the period
  14 : Physical roomtypes have not been assigned to the online roomtypes. OR pax is greater than maxpax on all OnlineTypes
  15 : No Rooms available for this holidayType  
  16 : Returned when no products match
  17 : No rooms availabel. Pax is greter than MaxPaxTotal from OnlineRoomTypes
  18 : No rooms availabel. MaxPax is greter than MaxPaxTotal
  19 : No rooms availabel. StartDay is too close to now
  20 : No rooms availabel. All rooms are booked
*/

--DECLARE @DominoEnabled BIT
--SELECT @DominoEnabled=CONVERT(SMALLINT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CentralBookingType'
--SET @DominoEnabled = ISNULL(@DominoEnabled, 0)

DECLARE @NumberOfDays INT
SET @NumberOfDays=CONVERT(INT, @AEndDate) - CONVERT(INT, @AStartDate)

DECLARE @YieldNumber INT
DECLARE @DayColor INT

DECLARE @DoRecalcPrices BIT
DECLARE @MaxDays SMALLINT
DECLARE @MinDays SMALLINT
DECLARE @TimePeriod SMALLINT
DECLARE @MinRoomsAvail SMALLINT
DECLARE @MaxRoomsAvail SMALLINT
DECLARE @WholeWeeks BIT
DECLARE @MatchDayColor SMALLINT
DECLARE @ArrivalMonday BIT
DECLARE @ArrivalTuesday BIT
DECLARE @ArrivalWednesday BIT
DECLARE @ArrivalThursday BIT
DECLARE @ArrivalFriday BIT
DECLARE @ArrivalSaturday BIT
DECLARE @ArrivalSunday BIT
DECLARE @DaysBeforeArrival SMALLINT
DECLARE @MinStayPrice MONEY
DECLARE @MinPricePerDay MONEY

DECLARE @ValidProductCount INT

DECLARE @UseProductTypes INT

DECLARE @UseMappingRoomtypes BIT
DECLARE @UseOnlineRoomBookingStatus BIT
DECLARE @HotelCustomerNumber INT
DECLARE @NamelistSetting int
DECLARE @OnePersonPaysForAll Bit

SELECT @OnePersonPaysForAll=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='OnePersonPaysForAll'
SET @OnePersonPaysForAll=ISNULL(@OnePersonPaysForAll, 0)

DECLARE @TotalNumberOfVacantRooms INT --QQAH20210819 SMALLINT changed to INT
SET @TotalNumberOfVacantRooms = 0

SET @DoRecalcPrices = 0
SELECT @UseProductTypes = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'PicassoOnlineUseProductTypes'

DECLARE @FormatedEndDate CHAR (8)
SET @FormatedEndDate = CONVERT (CHAR (8), @AEndDate, 112)
DECLARE @CurrentDate DATETIME
SET @CurrentDate=@AStartDate
DECLARE @CurrentDateStr CHAR (8)
SET @CurrentDateStr = CONVERT (CHAR (8), @CurrentDate, 112)
DECLARE @SystemDate DATETIME
SELECT @SystemDate=CONVERT(DATETIME, SystemDate, 112) FROM System WITH (NOLOCK) WHERE Number=1
DECLARE @DayOfWeek TINYINT
SELECT @DayOfWeek=DatePart(WEEKDAY, @AStartDate)  -- SUNDAY is 1!

CREATE TABLE #HotelNumbers
(
  listpos int NOT NULL,
  number  int NOT NULL
)

IF @AHotelNumber <> ''
	INSERT INTO #HotelNumbers (listpos, number)
	SELECT ListPos, Number FROM DBO.iter_intlist_to_table(@AHotelNumber)

DECLARE @LHotelNumber INT

SELECT @LHotelNumber = number From #HotelNumbers WHERE number >=0 ORDER BY number DESC
SELECT @HotelCustomerNumber = CustomerNumber from Customer (NOLOCK) where customertypenumber=7 and hotelnumber = @LHotelNumber
SELECT @UseMappingRoomtypes = dbo.GetHotelSetupKeyValue('OnlineRoomTypeTextPerPAX', @HotelCustomerNumber)

WHILE (@ErrorLevel = 00) AND (@CurrentDateStr < @FormatedEndDate)
BEGIN
  SET @YieldNumber = -1
  SET @DayColor = -1
	
  SELECT @YieldNumber=OnlineYieldFilter, @DayColor=DayColor FROM RoomBookingStatusYield WITH (NOLOCK) WHERE DateStamp=@CurrentDateStr AND OnlineYieldFilter > 0 AND HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers) -- TB20051208

 
	delete from #HotelNumbers where number NOT IN (
	SELECT HotelNumber FROM RoomBookingStatusYield WITH (NOLOCK) 
	WHERE DateStamp=@CurrentDateStr AND OnlineYieldFilter > 0 AND HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers)
	)

  SET @ErrorFilterNumber = @YieldNumber
  SET @ErrorDateStamp = @CurrentDateStr

  IF @YieldNumber > 0
  BEGIN
    -- Select filter properties
    SELECT @MaxDays=MaxDays, @MinDays=MinDays, @TimePeriod=TimePeriod, @MinRoomsAvail=MinRoomsAvail, @MaxRoomsAvail=MaxRoomsAvail, @WholeWeeks=WholeWeeks, @MatchDayColor=MatchDayColor, @DaysBeforeArrival=DaysBeforeArrival,
           @ArrivalMonday=ArrivalMonday, @ArrivalTuesday=ArrivalTuesday, @ArrivalWednesday=ArrivalWednesday, @ArrivalThursday=ArrivalThursday, @ArrivalFriday=ArrivalFriday, @ArrivalSaturday=ArrivalSaturday, @ArrivalSunday=ArrivalSunday,
           @MinStayPrice=MinStayPrice, @MinPricePerDay=ISNULL(MinPricePerDay, 0)
    FROM Yield WITH (NOLOCK) WHERE YieldNumber=@YieldNumber AND FilterTypeNumber=1 -- 1 = DELPHI: TYieldFilter = yfPicassoOnline

    -- JP 20070619
    IF ISNULL(@MinPricePerDay, 0) = 0
    BEGIN
      DECLARE @TableExists INT
      
      SELECT @TableExists = COUNT(*) FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        
      IF @TableExists = 1
      BEGIN
        SET @MinPricePerDay = 0

        IF @AAdults = 1
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult1, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 2
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult2, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 3
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult3, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 4
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult4, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 5
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult5, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 6
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult6, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 7
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult7, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 8
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult8, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AAdults = 9
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Adult9, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber

        IF @AChildren1 = 1
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child11, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 2
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child12, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 3
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child13, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 4
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child14, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 5
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child15, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 6
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child16, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 7
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child17, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 8
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child18, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren1 = 9
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child19, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber

        IF @AChildren2 = 1
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child21, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 2
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child22, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 3
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child23, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 4
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child24, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 5
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child25, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 6
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child26, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 7
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child27, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 8
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child28, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
        ELSE IF @AChildren2 = 9
          SELECT @MinPricePerDay = @MinPricePerDay + ISNULL(Child29, 0) * @ANumberOfRooms FROM YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
      END
    END

    -- TB20071002 -  Find Compant/Agency if Contact Person is logged in
    DECLARE @CustomerNumberTmp INT
    DECLARE @CustomerTypeNumber INT
    SET @CustomerNumberTmp = @ACustomerNumber
    
    -- Check Campaigncode
    IF @CampaignCodeText <> ''
		SET @ACustomerRateNumber = 4
	
    SELECT @CustomerTypeNumber=CustomerTypeNumber FROM Customer WITH (NOLOCK) WHERE Customernumber = @ACustomerNumber

    IF @CustomerTypeNumber = 8
    BEGIN 
      SELECT @ACustomerNumber = Customer.CustomerNumber FROM Customer WITH (NOLOCK) 
      INNER JOIN CustomerLinks WITH (NOLOCK) ON Customer.CustomerNumber = CustomerLinks.CustomerNumber
      WHERE CustomerLinkNumber  = @CustomerNumberTmp
    END

    IF @ACustomerRateNumber = 0
    BEGIN
      SELECT @ValidProductCount=COUNT (*) FROM Products pro WITH (NOLOCK) 
		  INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
		  WHERE AllowFilterPicassoOnline=1 AND pro.DayProduct = 0      
        AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
        AND ProductEndDate>=@FormatedEndDate 
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
        AND IsNull(MaximumDays, 0)>=@NumberOfDays
        AND IsNull(CustomerRate, 0) = 0  
        AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )
    END
    ELSE IF @ACustomerRateNumber = 1
    BEGIN
      -- General products and CustomerRate products
      SELECT @ValidProductCount=COUNT (*) FROM Products pro WITH (NOLOCK) 
		  INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
		  WHERE AllowFilterPicassoOnline=1 AND pro.DayProduct = 0
        AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
        AND ProductEndDate>=@FormatedEndDate 
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
        AND IsNull(MaximumDays, 0)>=@NumberOfDays
        AND IsNull(CustomerRate, 0) IN (0,1) 
        AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )
    END
    ELSE IF @ACustomerRateNumber = 2
    BEGIN
      -- Only CustomerRate products
      SELECT @ValidProductCount=COUNT (*) FROM Products pro WITH (NOLOCK) 
		  INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
	    WHERE AllowFilterPicassoOnline=1 AND pro.DayProduct = 0
        AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
        AND ProductEndDate>=@FormatedEndDate 
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
        AND IsNull(MaximumDays, 0)>=@NumberOfDays
        AND IsNull(CustomerRate, 0) = 1
        AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )
    END
    ELSE IF @ACustomerRateNumber = 3
    BEGIN
      -- product from inernet on customer (1 product)
      SELECT @ValidProductCount=COUNT (*) -- Customer.Customernumber, Products.ProductNumber, Products.productname, Products.CustomerRate, Products.Description
        FROM CUSTOMER WITH (NOLOCK)  INNER JOIN
        CustomerFieldsPerHotel WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber INNER JOIN
        Products WITH (NOLOCK)  ON Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
        WHERE     (CUSTOMER.customernumber = @ACustomerNumber) AND (CustomerFieldsPerHotel.HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers))
        /*AND AllowFilterPicassoOnline=1*/ AND Products.DayProduct = 0
        AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
        AND ProductEndDate>=@FormatedEndDate 
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
        AND IsNull(MaximumDays, 0)>=@NumberOfDays
    END   
    ELSE IF @ACustomerRateNumber = 4
    BEGIN
      -- CampaignCodeProducts
      SELECT @ValidProductCount=COUNT (*) FROM campaigncodesproducts WITH (NOLOCK)
	    INNER JOIN CampaignCodes WITH (NOLOCK) ON CampaignCodes.PK_CampaignCode = campaigncodesproducts.pk_Campaigncode
	    INNER JOIN Products WITH (NOLOCK) ON products.ProductNumber = campaigncodesproducts.FK_productNumber
	    INNER JOIN CampaignCodesHotel WITH (NOLOCK) ON CampaignCodesHotel.PK_CampaignCode = CampaignCodes.PK_CampaignCode 
      WHERE CampaignCodes.Text = @CampaignCodeText
      AND campaigncodesproducts.enabled = 1 
      AND CampaignCodesHotel.EnableAssociatedProducts = 1
      AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
      AND ProductEndDate>=@FormatedEndDate 
      AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
      AND IsNull(MaximumDays, 0)>=@NumberOfDays
    END 
	  ELSE IF @ACustomerRateNumber = 5
    BEGIN
      -- All online products and Marketing products
      SELECT @ValidProductCount=COUNT (*) FROM Products pro WITH (NOLOCK) 
		  INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
		  WHERE AllowFilterPicassoOnline=1 AND pro.DayProduct = 0      
        AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
        AND ProductEndDate>=@FormatedEndDate 
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
        AND IsNull(MaximumDays, 0)>=@NumberOfDays
        AND IsNull(CustomerRate, 0) = 0  
        AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )
    END 

    IF IsNull(@MinStayPrice, 0) = 0
      SET @MinStayPrice = 0
    IF IsNull(@MinPricePerDay, 0) = 0
      SET @MinPricePerDay = 0
    IF IsNull(@MaxDays, 0) = 0
      SET @MaxDays = 0
    IF IsNull(@MinDays, 0) = 0
      SET @MinDays = 0
    IF IsNull(@MaxRoomsAvail, 0) = 0
      SET @MaxRoomsAvail = 0
    IF IsNull(@MinRoomsAvail, 0) = 0
      SET @MinRoomsAvail = 0
    IF IsNull(@WholeWeeks, 0) = 0
      SET @WholeWeeks = 0
    IF IsNull(@MatchDayColor, 0) = 0
      SET @MatchDayColor = 0
    IF IsNull(@DaysBeforeArrival, 0) = 0
      SET @DaysBeforeArrival = 0
    IF IsNull(@ArrivalMonday, 0) = 0
      SET @ArrivalMonday = 0
    IF IsNull(@ArrivalTuesday, 0) = 0
      SET @ArrivalTuesday = 0
    IF IsNull(@ArrivalWednesday, 0) = 0
      SET @ArrivalWednesday = 0
    IF IsNull(@ArrivalThursday, 0) = 0
      SET @ArrivalThursday = 0
    IF IsNull(@ArrivalFriday, 0) = 0
      SET @ArrivalFriday = 0
    IF IsNull(@ArrivalSaturday, 0) = 0
      SET @ArrivalSaturday = 0
    IF IsNull(@ArrivalSunday, 0) = 0
      SET @ArrivalSunday = 0
    IF IsNull(@TimePeriod, 0) = 0
      SET @TimePeriod = 0

    /* ---- Check conditions ---- */
    IF (@TimePeriod = 0) AND (@NumberOfDays > @MaxDays)
      SET @ErrorLevel = 01
    ELSE IF (@TimePeriod = 0) AND (@NumberOfDays < @MinDays)
      SET @ErrorLevel = 02
    ELSE IF (@WholeWeeks=1) AND (@NumberOfDays % 7 > 0)  -- % = MODulo
      SET @ErrorLevel = 03     
    ELSE IF (@MatchDayColor > 0) AND ((@MatchDayColor=1 /*dcGreen*/) AND (@DayColor<>32768)) OR ((@MatchDayColor=2 /*dcYellow*/) AND (@DayColor<>65535)) OR ((@MatchDayColor=3 /*dcRed*/) AND (@DayColor<>255)) OR ((@MatchDayColor=4 /*dcBlue*/) AND (@DayColor<>16711680))  --   TDayColor = (dcNone, dcGreen, dcYellow, dcRed, dcBlue);
      SET @ErrorLevel = 04
    ELSE IF (@ArrivalMonday=1 OR @ArrivalTuesday=1 OR @ArrivalWednesday=1 OR @ArrivalThursday=1 OR @ArrivalFriday=1 OR @ArrivalSaturday=1 OR @ArrivalSunday=1) AND
      (@DayOfWeek=2 AND @ArrivalMonday=0) OR (@DayOfWeek=3 AND @ArrivalTuesday=0) OR (@DayOfWeek=4 AND @ArrivalWednesday=0) OR (@DayOfWeek=5 AND @ArrivalThursday=0) OR (@DayOfWeek=6 AND @ArrivalFriday=0) OR (@DayOfWeek=7 AND @ArrivalSaturday=0) OR (@DayOfWeek=1 AND @ArrivalSunday=0)
      SET @ErrorLevel = 05
    ELSE IF (@DaysBeforeArrival > 0) AND (DateAdd(DAY, -@DaysBeforeArrival, @AStartDate) < @SystemDate)
      SET @ErrorLevel = 06    
    ELSE IF @ValidProductCount < 1
      SET @ErrorLevel = 07
    ELSE IF (@TimePeriod > 0) AND (@NumberOfDays <> @TimePeriod)
      SET @ErrorLevel = 09     
    END
  ELSE	  
	SET @ErrorLevel = 08
	
  
  SET @CurrentDate = @CurrentDate +1
  SET @CurrentDateStr = CONVERT (CHAR (8), @CurrentDate, 112)
END

IF @ErrorLevel = 00  -- IF NO ERRORS
BEGIN
  SET @ErrorFilterNumber = 0
  SET @ErrorDateStamp = 0

  DECLARE @DateStamp CHAR (8)
  DECLARE @RoomTypeNumber INT
  DECLARE @OnlineRoomTypeNumber INT
  DECLARE @OnlinePriority TINYINT
  DECLARE @NumberOfRoomsOnType SMALLINT
  DECLARE @HotelNumber INT
  DECLARE @SoldRooms SMALLINT
  DECLARE @Adults SMALLINT
  DECLARE @VacantRoomHotelNumber SMALLINT

  DECLARE @Children1 SMALLINT
  DECLARE @Children2 SMALLINT
  DECLARE @NumberOfConfirmed SMALLINT
  DECLARE @NumberOfProvisionals SMALLINT
  DECLARE @NumberOfGuaranteed SMALLINT
  DECLARE @NumberOfTentative SMALLINT
  DECLARE @NumberOfWaitinglists SMALLINT
  DECLARE @NumberOfDayrooms SMALLINT
  DECLARE @FreeRoomsAllotment SMALLINT
  DECLARE @TotalRoomsAllotment SMALLINT
  DECLARE @NumberOfRoomsSoldToday SMALLINT
  DECLARE @NumberOfOutOfOrder SMALLINT
  DECLARE @NumberOfOutForService SMALLINT
  DECLARE @NumberOfOutOfHouseDayRooms SMALLINT
  DECLARE @NumberOfNotSaleable SMALLINT,
  @SoldPrice MONEY

  DECLARE @AvailCheckAllTypes BIT
  SELECT @AvailCheckAllTypes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineCheckAvailAllTypes' -- Changed OnlineCheckAvailAllTypes to PicassoOnlineCheckAvailAllTypes
  
  DECLARE @OFSDescreaseVacantRooms BIT
  SELECT @OFSDescreaseVacantRooms=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OOSDecreaseVacantRooms' 

  DECLARE @IgnoreRoomtypeResources BIT
  SELECT @IgnoreRoomtypeResources= IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='onlineignoreroomtyperesource' 

  SET @IgnoreRoomtypeResources = ISNULL( @IgnoreRoomtypeResources, 0)
 
  IF IsNull (@AvailCheckAllTypes, 0) = 0
    SET @AvailCheckAllTypes = 0

  /* Temp table used for vacant rooms per day per roomtype */
  CREATE TABLE #VacantRooms 
  (
	  [ID] INT IDENTITY(1,1) NOT NULL,
	  [DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
	  [RoomTypeNumber] [int] NOT NULL ,
	  [OnlineRoomTypeNumber] [int] NULL,
	  [OnlinePriority] [tinyint] NOT NULL,
	  [VacantRooms] [smallint] NULL,
	  [HotelNumber] [smallint] NULL,
	  [MinRoomsAvail] [smallint] NULL,
	  [MaxRooms] [smallint] NULL,
    Adults INT NULL,
    IsBunkBed BIT NULL
	  PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  )) ON [PRIMARY]

  DECLARE @IsBunkBed1 BIT


  DECLARE #OnlineCheckAvailability_InitVacantRooms CURSOR FAST_FORWARD FOR
    SELECT A.RoomTypeNumber, A.OnlineRoomTypeNumber, (CASE WHEN OnlinePriority IS NULL THEN 127 ELSE OnlinePriority END) AS OnlinePriority, A.Hotelnumber, (SELECT COUNT (*) FROM Rooms WITH (NOLOCK) WHERE RoomTypeNumber=A.RoomTypeNumber) AS NumberOfRooms,
    IsNull(A.ParentRoomTypeNumber, 0)
    FROM RoomTypes A WITH (NOLOCK)	
    INNER JOIN OnlineRoomTypes ORT WITH (NOLOCK) on ORT.Roomtypenumber = A.Roomtypenumber
    WHERE A.OnlineRoomTypeNumber > 0
    --AND ((@DominoEnabled = 0)  OR (@DominoEnabled = 1 
	AND IsNull(HotelNumber, 0) IN (SELECT DISTINCT Number From #HotelNumbers) -- TB20050803 - Used for chainhotel - TB20051227
    AND (IsNULL(resourcetypenumber, 0) = @AResource OR @IgnoreRoomtypeResources=1)
    ORDER BY A.OnlineRoomTypeNumber,OnlinePriority
  OPEN #OnlineCheckAvailability_InitVacantRooms

  FETCH NEXT FROM #OnlineCheckAvailability_InitVacantRooms INTO @RoomTypeNumber, @OnlineRoomTypeNumber, @OnlinePriority, @VacantRoomHotelNumber, @NumberOfRoomsOnType, @IsBunkBed1

  IF @@FETCH_STATUS = -1
    SET @ErrorLevel = 14
	
  DECLARE @TotalVacant INT = 1  --Check for overBooked
	
  WHILE (@ErrorLevel = 0) AND (@@FETCH_STATUS <> -1)
  BEGIN	
    -- for hver dato i perioden skal indsættes post med numberofrooms i vacantrooms
    SET @CurrentDate = @AStartDate    
    SET @CurrentDateStr = CONVERT (CHAR (8), @CurrentDate, 112)
    
    WHILE @CurrentDateStr < @FormatedEndDate
    BEGIN
	  -- Test for total overbooked QQAH20211006
	  EXEC GetVacantRooms @CurrentDate, @CurrentDate , '', '', 1, 0, @ASumResult = 1, @NotOverBooked = @TotalVacant output 

	  IF @TotalVacant > 0  --Not total overbooked
	  BEGIN
        DECLARE @YieldMaxRooms SMALLINT

        SELECT @YieldMaxRooms = Y.MaxRooms
        FROM YieldOnlineRoomTypesXTab Y WITH (NOLOCK) 
        INNER JOIN RoomBookingStatusYield R WITH (NOLOCK) ON Y.YieldNumber = R.OnlineYieldFilter
        WHERE R.DateStamp = @CurrentDateStr
          and R.HotelNumber = @VacantRoomHotelNumber  --QQAH 20181116
          --and R.HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers)
          and Y.OnlineRoomTypeNumber = @OnlineRoomTypeNumber
        
        DECLARE @TempVacantRooms smallint
        SET @TempVacantRooms = @NumberOfRoomsOnType

        IF @AvailCheckAllTypes = 0
          SET @TempVacantRooms = @NumberOfRoomsOnType - @ANumberOfRooms

        INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, @AAdults, @IsBunkBed1)

        IF @IsBunkBed1 = 0 AND @AIncludeAllPaxPrices = 1
        BEGIN
          IF @AAdults <> 1
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 1, @IsBunkBed1)
          IF @AAdults <> 2
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 2, @IsBunkBed1)
          IF @AAdults <> 3
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 3, @IsBunkBed1)
          IF @AAdults <> 4
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 4, @IsBunkBed1)
          IF @AAdults <> 5
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 5, @IsBunkBed1)
          IF @AAdults <> 6
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 6, @IsBunkBed1)
          IF @AAdults <> 7
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 7, @IsBunkBed1)
          IF @AAdults <> 8
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 8, @IsBunkBed1)
          IF @AAdults <> 9
            INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @VacantRoomHotelNumber, @MinRoomsAvail, @YieldMaxRooms, 9, @IsBunkBed1)
        END
	  END
      ELSE
	  BEGIN
		SET @ErrorLevel = 20   -- Total overbooked QQAH20211006
	  END

      SET @CurrentDate = @CurrentDate +1
      SET @CurrentDateStr = CONVERT(CHAR(8), @CurrentDate, 112)
    END

    FETCH NEXT FROM #OnlineCheckAvailability_InitVacantRooms  INTO @RoomTypeNumber, @OnlineRoomTypeNumber, @OnlinePriority, @VacantRoomHotelNumber, @NumberOfRoomsOnType, @IsBunkBed1
  END
  

  CLOSE #OnlineCheckAvailability_InitVacantRooms
  DEALLOCATE #OnlineCheckAvailability_InitVacantRooms

	--select * from #VacantRooms (nolock)
  IF @ErrorLevel > 00
    RETURN

  /* Copy from GenerateAvailability SP */
  CREATE TABLE #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2 (
		[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
		[RoomTypeNumber] [int] NOT NULL ,
		[HotelNumber] [int] NOT NULL ,
		[ReservationGroup] [int] NOT NULL,
		[SoldRooms] [smallint] NULL ,
		[Adults] [smallint] NULL ,
		[Children1] [smallint] NULL ,
		[Children2] [smallint] NULL ,
		[NumberOfConfirmed] [smallint] NULL ,
		[NumberOfProvisionals] [smallint] NULL ,
		[NumberOfGuaranteed] [smallint] NULL ,
		[NumberOfTentative] [smallint] NULL ,
		[NumberOfWaitinglists] [smallint] NULL ,
		[NumberOfDayrooms] [smallint] NULL ,
		[FreeRoomsAllotment] [smallint] NULL ,
		[TotalRoomsAllotment] [smallint] NULL ,
		[PaxAllotment] [smallint] NULL,
		[NumberOfRoomsSoldToday] [smallint] NULL,
		[NumberOfOutOfOrder] [smallint] NULL,
		[NumberOfOutForService] [smallint] NULL,
		[NumberOfNotSaleable] [smallint] NULL ,
		[NummberOfOccupiedGroup] [smallint] NULL,
		[SoldPrice] [MONEY] NULL
		)

  SELECT @UseOnlineRoomBookingStatus = dbo.GetHotelSetupKeyValue('onlineuseroombookingstatus', @HotelCustomerNumber)

  IF @UseOnlineRoomBookingStatus = 1
  BEGIN
    INSERT INTO #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2
	  EXEC OnlineRoomBookingStatus @AStartDate, @AEndDate 
  END
  ELSE
  BEGIN
     INSERT INTO #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2
     EXEC GenerateAvailability @AStartDate, @AEndDate, 0, @AHotelNumber, @SystemDate, 0, 0,'', '', 2, 0, 1, 0, 0, @AAllotmentNumber     -- OPTIMIZE: enddate can be one less!
  END

  DECLARE #OnlineCheckAvailability_AvailCur CURSOR FAST_FORWARD FOR
    SELECT DateStamp, RoomTypeNumber, SoldRooms, FreeRoomsAllotment, TotalRoomsAllotment, NumberOfOutOfOrder, NumberOfOutForService, NumberOfNotSaleable 
    FROM #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2 WITH (NOLOCK) 
    WHERE RoomTypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE OnlineRoomTypeNumber > 0)  -- DONT CARE ABOUT ONLINEROOMTYPE SINCE WE WANT TO RETURN ALL POSSIBILITIES SO USER HAVE OPTIONS
  OPEN #OnlineCheckAvailability_AvailCur

  FETCH NEXT FROM #OnlineCheckAvailability_AvailCur INTO @DateStamp, @RoomTypeNumber, @SoldRooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF @OFSDescreaseVacantRooms = 0
		  SET @NumberOfOutForService = 0
	IF @AAllotmentNumber = 0
	BEGIN
		DECLARE @UnavailableRooms smallint 
		SET @UnavailableRooms = IsNull(@SoldRooms, 0) + IsNull(@NumberOfOutOfOrder, 0) + IsNull(@NumberOfOutForService, 0) + IsNull(@TotalRoomsAllotment, 0)+ IsNull(@NumberOfNotSaleable, 0)
		
		IF @ACheckavailability = 1 -- LJE20120501
		  UPDATE #VacantRooms SET VacantRooms = VacantRooms - @UnavailableRooms
		   WHERE DateStamp = @DateStamp 
			 AND RoomTypeNumber = @RoomTypeNumber -- TB20050421 Check if not is null
	END
	ELSE
	BEGIN
		UPDATE #VacantRooms SET VacantRooms = @FreeRoomsAllotment
		WHERE DateStamp = @DateStamp 
			AND RoomTypeNumber = @RoomTypeNumber	
	END

    FETCH NEXT FROM #OnlineCheckAvailability_AvailCur INTO @DateStamp, @RoomTypeNumber, @SoldRooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable
  END

  CLOSE #OnlineCheckAvailability_AvailCur
  DEALLOCATE #OnlineCheckAvailability_AvailCur

  
  DROP TABLE #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2

  -- set VacantRooms for DaycolorMatch
  DECLARE @VacantRoomColorCount INT
  SELECT @VacantRoomColorCount=SUM(VacantRooms)  from #VacantRooms (nolock)
  WHERE DateStamp = CONVERT(DATETIME, @AStartDate, 112)

  IF @AvailCheckAllTypes = 0
  BEGIN
    IF @ACheckavailability = 1
      DELETE FROM #VacantRooms WHERE RoomTypeNumber NOT IN 
      (
        SELECT RoomTypeNumber FROM #VacantRooms WITH (NOLOCK) 
        WHERE VacantRooms+@ANumberOfRooms >= @MinRoomsAvail
        AND (VacantRooms+@ANumberOfRooms <= @MaxRoomsAvail OR @MaxRoomsAvail = 0) 
        GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority HAVING COUNT(RoomTypeNumber) = @NumberOfDays
      )
  END
  ELSE
  BEGIN  
    DECLARE @TEmpHotelNumber INt, @TempDateStamp CHAR(8)
	
    DECLARE #AVAILCUR CURSOR FOR
      SELECT DateStamp, HotelNumber 
      FROM #VacantRooms WITH (NOLOCK)
      GROUP BY DateStamp, HotelNumber
      HAVING (SUM(VacantRooms-CASE WHEN MaxRooms < 0 THEN MaxRooms ELSE 0 END)-@ANumberOfRooms < @MinRoomsAvail) 
          OR (SUM(VacantRooms)-@ANumberOfRooms > @MaxRoomsAvail AND @MaxRoomsAvail > 0)  -- before
    OPEN #AVAILCUR

	  FETCH NEXT FROM #AVAILCUR INTO @TempDateStamp, @TempHotelNumber

	  WHILE @@FETCH_STATUS <> -1
	  BEGIN
		  DELETE FROM #VacantRooms 
		  WHERE Datestamp = @TempDateStamp and HotelNumber = @TempHotelNumber

		  FETCH NEXT FROM #AVAILCUR INTO @TempDateStamp, @TempHotelNumber
	  END

	  CLOSE #AVAILCUR
	  DEALLOCATE #AVAILCUR

    /* We need those rooms where we can make a booking for all */
    DELETE FROM #VacantRooms 
    WHERE RoomTypeNumber NOT IN 
    (
      SELECT RoomTypeNumber 
      FROM #VacantRooms WITH (NOLOCK) 
      WHERE (ISNULL(@AIncludeAllPaxPrices, 0) = 0 OR (#VacantRooms.Adults = @AAdults))
      GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority 
      HAVING COUNT(RoomTypeNumber) = @NumberOfDays
    )
  END

 -- select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after
 -- multi hotelNumber fail after here

  DECLARE @UseCalendar BIT
  SELECT @UseCalendar = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'OnlineShowCalender'
  
  IF @UseCalendar <> 0
  BEGIN
    DECLARE @TotPax INT
    SET @TotPax = @AAdults + @AChildren1 + @AChildren2

	--select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after

 	  IF @UseMappingRoomtypes = 1 AND ISNULL(@AIncludeAllPaxPrices, 0) = 0
	  BEGIN
		  DELETE FROM #VacantRooms WHERE RoomTypeNumber IN
		  (SELECT RoomtypeNumber FROM Roomtypes (NOLOCK)
		  WHERE IsNull(MaxPax, 0) < @TotPax AND IsNull(ParentRoomTypeNumber, 0)=0)
	  END

  	DECLARE @MaxpaxCount INT
    SELECT @MaxpaxCount=COUNT(*) FROM #VacantRooms WITH (NOLOCK)    
	
	  --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after

    IF ISNULL(@AIncludeAllPaxPrices, 0) = 0
    BEGIN
      DELETE FROM #VacantRooms WHERE OnlineRoomTypeNumber IN
      (SELECT OnlineRoomTypeNumber FROM onlineroomtypes (NOLOCK)
      WHERE (MaxPaxTotal < @TotPax AND IsNull(bunkbed, 0)=0)
      OR (maxadultpax < @AAdults  AND IsNull(bunkbed, 0)=0)
      OR MaxChildren1Pax < @AChildren1
      OR MaxChildren2Pax < @AChildren2)
    END
    
	  --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after

    -- TBU20140625 - Check for MinPax on OnlineRoomtypes
    IF ISNULL(@AIncludeAllPaxPrices, 0) = 0
    BEGIN
      DELETE FROM #VacantRooms WHERE OnlineRoomTypeNumber IN
      (SELECT OnlineRoomTypeNumber FROM onlineroomtypes (NOLOCK)
      WHERE ( IsNull(MinPax, 0) > @TotPax))
    END
    
	  IF @MaxpaxCount > 0
	  BEGIN
		  SELECT @MaxpaxCount=COUNT(*) FROM #VacantRooms WITH (NOLOCK)  
		  IF @MaxpaxCount = 0
			  SET @ErrorLevel = 18
  	END

	DELETE FROM #VacantRooms
	WHERE (MaxRooms < 0 AND VacantRooms < MaxRooms ) 
	OR  
	( MaxRooms > 0 AND VacantRooms < 1  )
	OR
	( MaxRooms = 0)

	IF EXISTS(SELECT * FROM #VacantRooms GROUP BY DateStamp HAVING(SUM(VacantRooms) < @ANumberOfRooms))
	BEGIN
		DELETE FROM #VacantRooms 
		SET @ErrorLevel = 17
	END

	DELETE FROM #VacantRooms
	WHERE RoomTypeNumber IN ( 
	    SELECT RoomTypeNumber FROM #VacantRooms
		GROUP BY RoomTypeNumber
		HAVING COUNT(*) < @NumberOfDays
	)
  END

  -- multi hotelNumber fail before here
--select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after MaxPaxTotal

-- TBU20151027 - Mapping onlineroomtypes to roomtypes - Find all onlineroomtypes with more roomtypes
  IF @UseMappingRoomtypes = 1
  BEGIN
    DECLARE @OnlRoomtypeNumber INT

  --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber

  -- TBU20151027 - Mapping onlineroomtypes to roomtypes - Now delete from #VacantRooms
    DECLARE #OnlineRoomtypes_Cursor2 CURSOR FAST_FORWARD FOR
      select distinct onlineroomtypenumber 
      from #VacantRooms (NOLOCK)
	    where onlineroomtypenumber IN (
	      select onlineroomtypenumber from #VacantRooms (NOLOCK)		
		    GROUP BY onlineroomtypenumber, datestamp--, onlinepriority
		    HAVING COUNT(onlineroomtypenumber) >1 --AND sum(onlinepriority)=10
	    )
	    ORDER BY onlineroomtypenumber
	  OPEN #OnlineRoomtypes_Cursor2

    FETCH NEXT FROM #OnlineRoomtypes_Cursor2 INTO @OnlRoomtypeNumber

	  WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	    DELETE FROM #VacantRooms 
      WHERE onlinepriority NOT IN
      ( 
	      select distinct top 1 onlinepriority from #VacantRooms (nolock)
	      where onlineroomtypenumber=@OnlRoomtypeNumber	
	      ORDER BY onlinepriority
      )
	    AND onlineroomtypenumber = @OnlRoomtypeNumber
      
      FETCH NEXT FROM #OnlineRoomtypes_Cursor2  INTO @OnlRoomtypeNumber
    END

    CLOSE #OnlineRoomtypes_Cursor2
    DEALLOCATE #OnlineRoomtypes_Cursor2
  END
	--Mapping onlineroomtypes end

  --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber
  --select * from #VacantRooms where #VacantRooms.RoomTypeNumber = (select distinct v2.RoomTypeNumber from #VacantRooms v2 where v2.RoomTypeNumber = #VacantRooms.RoomTypeNumber and v2.[VacantRooms] <= v2.[MinRoomsAvail])
  DELETE FROM #VacantRooms
  WHERE RoomTypeNumber IN ( 
    SELECT RoomTypeNumber FROM #VacantRooms
	WHERE [VacantRooms]-CASE WHEN MaxRooms < 0 THEN MaxRooms ELSE 0 END <= [MinRoomsAvail]
  )

  /* Temp table used for product prices per product per roomtype */
  CREATE TABLE #ProductPrices (
	[RoomTypeNumber] [int] NOT NULL ,
	[ProductNumber] [int] NOT NULL,
	[Description] [VARCHAR] (255) NULL,
	[RoomPriceFirstDay] [Money] NULL,
	[RoomPrice] [money] NULL,
	[ProductName] [VARCHAR] (255) NULL,
	[RoomPricePerDay] [Money] NULL,
	[RoomPriceMinTotal] [Money] NULL,
	[HotelNumber] [int] NULL,
	[UseZeroPrice] [Bit] NULL,
	[NoHostelDiscount] [Bit] NULL,
  Adults INT NULL
	)
  ON [PRIMARY]
  
  IF @ErrorLevel = 00
  BEGIN
    -- Select possible products in period
    DECLARE @Found INT
    DECLARE @ProductNumber INT
    DECLARE @Description VARCHAR(255)
    DECLARE @CalcTotalPrice REAL
    DECLARE @CalcArrivalPrice REAL
    DECLARE @ProductName VARCHAR(255)
  	DECLARE @NoHostelDiscount BIT
	  DECLARE @IsBunkedBed BIT
	  DECLARE @AdultTemp INT
	  DECLARE @NumberOfRoomsTemp INT

    IF @ACustomerRateNumber = 0
    BEGIN
      -- Don´t use CustomerRate - Customer Not logged in
      DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
      SELECT DISTINCT pro.ProductNumber, Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM Products pro WITH (NOLOCK)
      INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
      INNER JOIN ProductPrice WITH (NOLOCK) ON pro.ProductNumber = ProductPrice.ProductNumber 
      AND      ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) 
      AND        AllowFilterPicassoOnline=1 
      AND        pro.DayProduct = 0 
      AND        CONVERT(DATETIME, ProductDate) <= @AStartDate 
      AND        ProductEndDate >= @FormatedEndDate
      AND IsNull(MiniMumDays, 0) <= @NumberOfDays -- Added 20050419
      AND IsNull(MaximumDays, 0) >= @NumberOfDays
      AND IsNull(CustomerRate, 0) = 0)     
      AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )  
      INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber
    END
    ELSE IF @ACustomerRateNumber = 1
    BEGIN
     -- General products and CustomerRate products
      DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
      SELECT DISTINCT pro.ProductNumber, Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM Products pro WITH (NOLOCK)
      INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
      INNER JOIN ProductPrice WITH (NOLOCK) ON pro.ProductNumber=ProductPrice.ProductNumber AND
      ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) AND AllowFilterPicassoOnline=1 AND pro.DayProduct = 0 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
       AND IsNull(MiniMumDays, 0)<=@NumberOfDays -- Added 20050419
       AND IsNull(MaximumDays, 0)>=@NumberOfDays  
       AND IsNull(CustomerRate, 0) IN (0,1) )
       AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )  
       INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber
    END
    ELSE IF @ACustomerRateNumber = 2
    BEGIN
      -- Only CustomerRate products
      DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
        SELECT DISTINCT pro.ProductNumber, Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM Products pro WITH (NOLOCK)
	      INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
        INNER JOIN ProductPrice WITH (NOLOCK) ON pro.ProductNumber=ProductPrice.ProductNumber AND
        ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) AND AllowFilterPicassoOnline=1 AND pro.DayProduct = 0 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
        AND IsNull(MiniMumDays, 0)<=@NumberOfDays -- Added 20050419
        AND IsNull(MaximumDays, 0)>=@NumberOfDays 
        AND IsNull(CustomerRate, 0) = 1 )
        AND ( ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @SystemDate) )  
        INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber
    END
    ELSE IF @ACustomerRateNumber = 3
    BEGIN	
    -- product from inernet on customer (1 product)
     DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
       SELECT DISTINCT Products.ProductNumber, Products.Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM CUSTOMER WITH (NOLOCK)   
       INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
       AND (CUSTOMER.customernumber = @ACustomerNumber) 
       AND (CustomerFieldsPerHotel.HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers))
       INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6	   
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
       OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
	     INNER JOIN ProductExtended proe WITH (NOLOCK) ON Products.ProductNumber = proe.FK_ProductNumber
       INNER JOIN ProductPrice WITH (NOLOCK) ON Products.ProductNumber=ProductPrice.ProductNumber AND
       ((Products.MarkAsDeleted=0 OR Products.MarkAsDeleted IS NULL) /*AND AllowFilterPicassoOnline=1*/ AND Products.DayProduct = 0 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
       AND IsNull(MiniMumDays, 0)<=@NumberOfDays -- Added 20050419
       AND IsNull(MaximumDays, 0)>=@NumberOfDays) 
       INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber
    END
    ELSE IF @ACustomerRateNumber = 4
    BEGIN
      -- CampaignCodeProducts
      DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
      SELECT DISTINCT Products.ProductNumber, Description, ProductName, '0' AS NoHostelDiscount, NameListSetting FROM Products WITH (NOLOCK)
      INNER JOIN ProductPrice WITH (NOLOCK) ON Products.ProductNumber = ProductPrice.ProductNumber 
      INNER JOIN campaigncodesproducts WITH (NOLOCK) ON products.ProductNumber = campaigncodesproducts.FK_productNumber
      INNER JOIN CampaignCodes WITH (NOLOCK) ON CampaignCodes.PK_CampaignCode = campaigncodesproducts.pk_Campaigncode 
      INNER JOIN CampaignCodesHotel WITH (NOLOCK) ON CampaignCodesHotel.PK_CampaignCode = CampaignCodes.PK_CampaignCode      
      AND      ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) 
      AND        CONVERT(DATETIME, ProductDate) <= @AStartDate
      AND        ProductEndDate >= @FormatedEndDate
      AND IsNull(MiniMumDays, 0) <= @NumberOfDays
      AND IsNull(MaximumDays, 0) >= @NumberOfDays)     
      INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber
      WHERE CampaignCodes.Text = @CampaignCodeText
      AND campaigncodesproducts.enabled = 1 
      AND CampaignCodesHotel.EnableAssociatedProducts = 1
    END
    ELSE IF @ACustomerRateNumber = 5
    BEGIN
      -- All online products and Marketing products
      DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
      SELECT DISTINCT pro.ProductNumber, Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM Products pro WITH (NOLOCK)
      INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
      INNER JOIN ProductPrice WITH (NOLOCK) ON pro.ProductNumber = ProductPrice.ProductNumber 
      AND      ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) 
      AND        AllowFilterPicassoOnline=1 
      AND        pro.DayProduct = 0 
      AND        CONVERT(DATETIME, ProductDate) <= @AStartDate 
      AND        ProductEndDate >= @FormatedEndDate
      AND IsNull(MiniMumDays, 0) <= @NumberOfDays -- Added 20050419
      AND IsNull(MaximumDays, 0) >= @NumberOfDays
      AND IsNull(CustomerRate, 0) = 0)     
      AND (ISNULL(proe.ProductOnlineClosedtoArrivaldays,0) = 0 OR @AStartDate >= DATEADD(dd, IsNull(proe.ProductOnlineClosedtoArrivaldays,0), '20160921'))
      UNION
      SELECT DISTINCT Products.ProductNumber, Products.Description, ProductName, proe.NoHostelDiscount, NameListSetting FROM CUSTOMER WITH (NOLOCK)   
      INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
      AND (CUSTOMER.customernumber = @ACustomerNumber) 
      AND (CustomerFieldsPerHotel.HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers))
      INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
	    INNER JOIN ProductExtended proe WITH (NOLOCK) ON Products.ProductNumber = proe.FK_ProductNumber
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
        OR Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
      INNER JOIN ProductPrice WITH (NOLOCK) ON Products.ProductNumber=ProductPrice.ProductNumber AND
       ((Products.MarkAsDeleted=0 OR Products.MarkAsDeleted IS NULL) /*AND AllowFilterPicassoOnline=1*/ AND Products.DayProduct = 0 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
      AND IsNull(MiniMumDays, 0)<=@NumberOfDays -- Added 20050419
      AND IsNull(MaximumDays, 0)>=@NumberOfDays)
    END

    OPEN #OnlineCheckAvailability_ProductsCur
	
    DECLARE #OnlineCheckAvailability_RoomTypesCur SCROLL CURSOR FOR
      SELECT DISTINCT RoomTypeNumber, Adults, IsBunkBed
      FROM #VacantRooms
    OPEN #OnlineCheckAvailability_RoomTypesCur

    FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur INTO @ProductNumber, @Description, @ProductName, @NoHostelDiscount, @NameListSetting

    WHILE (@@FETCH_STATUS <> -1) AND (@ErrorLevel = 00)
    BEGIN
      FETCH FIRST FROM #OnlineCheckAvailability_RoomTypesCur INTO @RoomTypeNumber, @AdultTemp, @IsBunkedBed

      WHILE (@@FETCH_STATUS <> -1) --AND (@ErrorLevel = 00)
      BEGIN
        SET @Found = 0 -- Always 0 in new availability
	
		    SET @NumberOfRoomsTemp = @ANumberOfRooms
		    IF @IsBunkedBed = 1
		    BEGIN
			    SET @AdultTemp = @ANumberOfRooms
			    SET @NumberOfRoomsTemp = @AAdults
		    END
			
        IF @Found = 0
        BEGIN
          SELECT @Found = SUM(ISNULL(Child11, 0)) + SUM(ISNULL(Child21, 0)) FROM dbo.YieldOnlineRoomMinimumPrice WITH (NOLOCK) WHERE FK_Yield=@YieldNumber
          
          IF (@Found > 0) and (@@rowcount > 0)
            EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @NumberOfRoomsTemp, @AdultTemp, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
          ELSE
          BEGIN
            EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @NumberOfRoomsTemp, @AdultTemp, 0, 0, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT -- JP 20070829 EXT: DO NOT USE Children if not setup on Yield Filter
            SET @DoRecalcPrices = 1
          END

	        -- TB20100324 MinPriceTotal
	        DECLARE @Addon money, @BillTextProduct VARCHAR(50), @BillTextAddOn VARCHAR(50), @CalcPriceDay money, @PacketNumber int, @EnvironmentFee smallmoney, @FixedPrice bit, @VATProduct bit, @EnvironmentFeeType Float, @AIsDayProduct Bit, @SpecifiedDepartureAddOn Bit
	        DECLARE @DateNow Datetime, @UseZeroPrice BIT
	        DECLARE @DateStr Char(8), @ChepPriceTotal MONEY--, @RoomPricePerDay MONEY

	        SET @DateNow = @AStartDate
	        SET @ChepPriceTotal = -1
        	
	        WHILE (@DateNow < @AEndDate AND (@CalcTotalPrice>0))
	        BEGIN
	          exec [dbo].[ProductPricesDay] @ProductNumber, @AStartDate, @AEndDate, @DateNow, @RoomTypeNumber, @NumberOfRoomsTemp, @AdultTemp, @AChildren1, @AChildren2, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting, @OnePersonPaysForAll, @EnvironmentFeeType = 0 , @AIsDayProduct = 0, @AGetPackage = 0  
	          IF @ChepPriceTotal = -1
		          SET @ChepPriceTotal = @CalcPriceDay+@Addon
	          ELSE
	          BEGIN
		          IF @CalcPriceDay < @ChepPriceTotal
			          SET @ChepPriceTotal = @CalcPriceDay+@Addon
	          END
	          SET @DateNow = DATEADD(DAY, 1, @DateNow)
	        END

    			declare @HotelNumberprice INT

			    SELECT @HotelNumberprice=hotelNumber From Roomtypes WITH (nolock) where roomtypeNumber=@RoomTypeNumber

          INSERT INTO #ProductPrices VALUES (@RoomTypeNumber, @ProductNumber, @Description, @CalcArrivalPrice, @CalcTotalPrice, @ProductName, @CalcPriceDay, @ChepPriceTotal, @HotelNumberprice,  1, @NoHostelDiscount, @AdultTemp)
        END
        ELSE
          SET @SpecialErrorLevel = 12
		
    		SELECT @UseZeroPrice=AllowZeroPrice FROM Products pro WITH (NOLOCK) 
    		INNER JOIN ProductExtended proExt WITH (NOLOCK) ON pro.ProductNumber = proext.FK_ProductNumber
    		WHERE pro.ProductNumber = @ProductNumber
		
        UPDATE #ProductPrices SET UseZeroPrice=@UseZeroPrice
        WHERE ProductNumber=@ProductNumber

        FETCH NEXT FROM #OnlineCheckAvailability_RoomTypesCur  INTO @RoomTypeNumber, @AdultTemp, @IsBunkedBed
      END

      FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur INTO @ProductNumber, @Description, @ProductName, @NoHostelDiscount, @NameListSetting
    END

    CLOSE #OnlineCheckAvailability_RoomTypesCur
    DEALLOCATE #OnlineCheckAvailability_RoomTypesCur

    CLOSE #OnlineCheckAvailability_ProductsCur
    DEALLOCATE #OnlineCheckAvailability_ProductsCur
  END

  DECLARE @BookWithZeroPriceperday BIT 
  DECLARE @UseAllowZeroPriceOnProduct INT  
  
  IF EXISTS (SELECT keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE searchkey ='onlinebookwithzeropriceperdayfromproduct')
    SELECT @UseAllowZeroPriceOnProduct=keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE searchkey ='onlinebookwithzeropriceperdayfromproduct'
  ELSE
    SET @UseAllowZeroPriceOnProduct = 0
  
  IF EXISTS (SELECT keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE searchkey ='onlinebookwithzeropriceperday')
    SELECT @BookWithZeroPriceperday=keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE searchkey ='onlinebookwithzeropriceperday'
  ELSE
    SET @BookWithZeroPriceperday = 1
  
  IF @IsOwnerBooking = 1
  BEGIN
	  SET @BookWithZeroPriceperday = 1
	  SET @MinStayPrice = 0
	  SET @MinPricePerDay = 0
  END
	
  IF @ErrorLevel = 00 AND @BookWithZeroPriceperday = 0
  BEGIN
  	IF @UseAllowZeroPriceOnProduct = 1
  	BEGIN
  		DELETE FROM #ProductPrices WHERE RoomPriceMinTotal = 0 AND UseZeroPrice = 0 --RoomPricePerDay
  	END
  	ELSE
  	BEGIN
	  	DELETE FROM #ProductPrices WHERE RoomPricePerDay = 0
  		DELETE FROM #ProductPrices WHERE RoomPriceFirstDay = 0
	  END
  END
--select * from  #ProductPrices (nolock)

  -- Return prices per roomtype and per product...
  IF @ErrorLevel = 00
  BEGIN
    DELETE FROM #ProductPrices WHERE RoomPrice < @MinStayPrice
  
    IF @@rowcount > 0
    BEGIN
      SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
      IF @Found = 0
        SET @ErrorLevel = 10
    END

    IF @ErrorLevel = 00
    BEGIN
      DELETE FROM #ProductPrices WHERE RoomPriceMinTotal < @MinPricePerDay
      IF @@rowcount > 0
      BEGIN
        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
        IF @Found = 0
          SET @ErrorLevel = 11
      END

      IF @ErrorLevel = 00
      BEGIN
        IF IsNull(@UseProductTypes, 0) = 1 AND IsNull(@ProductTypeNumber, 0) > 0	
          DELETE FROM #ProductPrices WHERE #ProductPrices.Productnumber NOT IN (SELECT Productnumber FROM Products pr WITH (NOLOCK) Inner JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber WHERE AllowFilterpicassoOnline = 1 AND pt.ProductTypeNumber = @ProductTypeNumber)
	      IF @@rowcount > 0
      	BEGIN
	        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
	        IF @Found = 0
	          SET @ErrorLevel = 15
        END
      END

      SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)

-- ERRORLEVEL 16
      IF @ErrorLevel = 00 AND @Found > 0-- Check Error level 16 - Remove all products that don´t match arive date
      BEGIN  
  	    IF @DayOfWeek = 1 --Søndag
	      BEGIN
  	       DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	         AND NOT (ArrivalSunday=1 
	         OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
	      END
	      IF @DayOfWeek = 2
	      BEGIN
          DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	        AND  NOT (ArrivalMonday=1 
	        OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND  ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
  	    END
	      IF @DayOfWeek = 3
	      BEGIN
          DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	        AND NOT (ArrivalTuesday=1
	        OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
  	    END
	      IF @DayOfWeek = 4
	      BEGIN
	        DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	        AND NOT  (ArrivalWednesday=1
	        OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
  	    END
	      IF @DayOfWeek = 5
	      BEGIN
	         DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
  	       AND NOT  (ArrivalThursday=1
	         OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )		
	         --SET @ArrivalDay = 'ArrivalThursday'
	      END
	      IF @DayOfWeek = 6
  	    BEGIN
	         DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	         AND NOT  (ArrivalFriday=1
	         OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
  	       --SET @ArrivalDay = 'ArrivalFriday'
  	    END
	      IF @DayOfWeek = 7
	      BEGIN
	        DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (SELECT productnumber  FROM  Products  WITH (NOLOCK) WHERE AllowFilterPicassoOnline = 1
	        AND NOT  (ArrivalSaturday=1
	        OR (ArrivalSunday=0 AND ArrivalMonday=0 AND ArrivalTuesday=0 AND ArrivalWednesday=0 AND ArrivalThursday=0 AND ArrivalFriday=0 AND ArrivalSaturday=0)) )
  	    END
	
			  -- Check for summeryproduct - IF @AvailabilitySummeryproductMode=2, don´t do anything
        IF @AvailabilitySummeryproductMode = 0 -- Without summeryproduct
			  BEGIN
				  DELETE FROM #ProductPrices WHERE ProductNumber IN (
				  SELECT Productnumber FROM Products p WITH (NOLOCK) INNER JOIN ProductExtended pe WITH (NOLOCK) ON p.ProductNumber = pe.FK_ProductNumber 
				  WHERE pe.SumAdultChild1 = 1)
			  END
        IF @AvailabilitySummeryproductMode = 1 -- Only summeryproduct
			  BEGIN
				  DELETE FROM #ProductPrices WHERE ProductNumber IN (
				  SELECT Productnumber FROM Products p WITH (NOLOCK) INNER JOIN ProductExtended pe WITH (NOLOCK) ON p.ProductNumber = pe.FK_ProductNumber 
				  WHERE pe.SumAdultChild1 = 0)
			  END
			
			  --Check for children
			  DECLARE @ProductHasChildren INT
			  SELECT @ProductHasChildren = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductExtended' AND COLUMN_NAME = 'NoChildren'
			  IF @ProductHasChildren = 1 AND ( @AChildren1 > 0 OR @AChildren2 > 0)
			  BEGIN
			    --print 'Yes inde'	
				  DELETE FROM #ProductPrices WHERE ProductNumber IN (
				  SELECT Productnumber FROM Products p WITH (NOLOCK) INNER JOIN ProductExtended pe WITH (NOLOCK) ON p.ProductNumber = pe.FK_ProductNumber 
				  WHERE IsNull(pe.NoChildren, 0) = 1)
			  END

        IF @DoRecalcPrices = 1 -- JP 20070829 EXT: Since we calculated without children we now have to recalc with children and insert the correct values
        BEGIN
          DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
            SELECT RoomTypeNumber, ProductNumber, Adults
            FROM #ProductPrices
          OPEN #OnlineCheckAvailability_ProductsCur

          FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur INTO @RoomTypeNumber, @ProductNumber, @AdultTemp
	   
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
      		  SET @NumberOfRoomsTemp = @ANumberOfRooms
		        select @IsBunkedBed = IsNull(ParentRoomTypeNumber, 0) from roomtypes (nolock) where roomtypenumber = @RoomTypeNumber
		        IF @IsBunkedBed = 1
		        BEGIN
			        SET @AdultTemp = @ANumberOfRooms
			        SET @NumberOfRoomsTemp = @AAdults
		        END

           EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @NumberOfRoomsTemp, @AdultTemp, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT

           UPDATE #ProductPrices SET RoomPriceFirstDay=@CalcArrivalPrice, RoomPrice=@CalcTotalPrice 
           WHERE RoomTypeNumber=@RoomTypeNumber 
           AND ProductNumber=@ProductNumber
           AND Adults = @AdultTemp

           FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur INTO @RoomTypeNumber, @ProductNumber, @AdultTemp
          END
	  
          CLOSE #OnlineCheckAvailability_ProductsCur
          DEALLOCATE #OnlineCheckAvailability_ProductsCur
        END

	      SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
	    
	      IF @Found = 0
	        SET @ErrorLevel = 16
	    END

      DECLARE @UsedynamicPaxText BIT
      DECLARE @Usedynamicstring VARCHAR(50)
      DECLARE @TotPaxDynamicText INT
      SET @TotPaxDynamicText = @AAdults + @AChildren1 +  @AChildren2

      IF @ANumberOfRooms > 1 AND @TotPaxDynamicText = 1 AND @UseMappingRoomtypes = 1
        SET @TotPaxDynamicText = @ANumberOfRooms

      SET @UsedynamicPaxText=0
      SELECT @Usedynamicstring = KeyValue FROM HotelSetup(NOLOCK) WHERE SearchKey = 'onlineusedynamicpaxtext'
      IF @Usedynamicstring <> ''
        SET @UsedynamicPaxText=0

      IF @ErrorLevel = 00 --QQAH20201215 Check for ClosedtoArrival on product
      BEGIN
        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
		IF @Found > 0
		BEGIN
  		  DELETE FROM #ProductPrices WHERE #ProductPrices.productnumber IN (
		    SELECT productnumber FROM Products pro WITH (NOLOCK) 
            INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
            WHERE AllowFilterPicassoOnline=1 AND pro.DayProduct = 0
            AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
            AND ProductEndDate>=@FormatedEndDate 
            AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
            AND IsNull(MaximumDays, 0)>=@NumberOfDays
		    --AND (DateAdd(DAY, -IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @AStartDate) < Convert (date, GETDATE()) ) )
		    AND (DateAdd(DAY, -IsNull(proe.ProductOnlineClosedtoArrivaldays,0), @AStartDate) < @SystemDate) )

          SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
		  IF @Found = 0
		    SET @SpecialErrorLevel = 19
        END
	  END

      IF @ErrorLevel = 00
      BEGIN
        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)

        DECLARE @GetVATFrom SMALLINT
        SELECT @GetVATFrom = CONVERT(INT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BookkeepingReport'

        CREATE TABLE #RoomTypeWare
        (
          RoomTypeNumber INT,
          WareNumber INT
        )

        IF @GetVATFrom = 2
        BEGIN
          INSERT INTO #RoomTypeWare
          SELECT rt.RoomTypeNumber, w.WareNumber 
          FROM 
          (
            SELECT RoomTypeNumber, row_number() OVER (ORDER BY SortNumber, Text) AS row_num
            FROM RoomTypes WITH (NOLOCK)
          ) rt    
          INNER JOIN
          (
            SELECT Ware.WareNumber, row_number() OVER (ORDER BY Ware.SortNumber) AS row_num
            FROM Ware WITH (NOLOCK) 
            INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
            WHERE WareGroups.Payment = 2 -- system rooms
            AND Ware.MarkAsDeleted IS NULL
          ) w
          ON rt.row_num = w.row_num
        END
        ELSE
        BEGIN
          INSERT INTO #RoomTypeWare
          SELECT RoomTypeNumber, -1 AS WareNumber
          FROM RoomTypes WITH (NOLOCK)
        END

        IF @Found = 0 and @SpecialErrorLevel IN (12, 19) -- if no prices found and there has been problems with exceeding maxrooms on some types then return error
          SET @ErrorLevel = @SpecialErrorLevel
        ELSE IF @Found > 0
          SELECT --#ProductPrices.*
          #ProductPrices.RoomTypeNumber
          ,#ProductPrices.ProductNumber
          ,ISNULL(#ProductPrices.[Description],'') AS [Description]
          ,#ProductPrices.RoomPriceFirstDay
          ,#ProductPrices.RoomPrice
          ,ISNULL(#ProductPrices.ProductName,'') AS ProductName
			    ,#ProductPrices.RoomPricePerDay
          ,#ProductPrices.RoomPriceMinTotal
			    ,IsNull(#ProductPrices.NoHostelDiscount, 0) AS NoHostelDiscount
			    ,#ProductPrices.HotelNumber
			    ,  RT.OnlineRoomTypeNumber
			    , ISNULL(OnlinePriority,0) AS OnlinePriority			 
			    , (select [Text] 
			    FROM OnlineRoomTypesTranslated WITH (NOLOCK) 
			    WHERE OnlineRoomTypeNumber = RT.OnlineRoomTypeNumber AND CountryNumber = @ACountryNumber AND pax=0 ) AS RomTypeName,			  
			    (select IsNull([Text], '') 
			    FROM OnlineRoomTypesTranslated WITH (NOLOCK) 
			    WHERE OnlineRoomTypeNumber = RT.OnlineRoomTypeNumber AND CountryNumber = @ACountryNumber AND pax=@TotPaxDynamicText) AS RomTypeNamePax,
          #ProductPrices.Adults,
          bpfw.BonusPointEarn, bpfw.BonusPointEarnValue, bpfw.BonusPointSpend, bpfw.BonusPointSpendValue 
			    FROM #ProductPrices WITH (NOLOCK) 
          INNER JOIN RoomTypes RT WITH (NOLOCK) ON #ProductPrices.RoomTypeNumber=RT.RoomTypeNumber 
			    INNER JOIN ProductHotelXTAB WITH (NOLOCK) ON ProductHotelXTAB.ProductNumber = #ProductPrices.ProductNumber
			    INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ProductHotelXTAB.HotelCustomerNumber
			    AND Customer.HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers)
			    AND Customer.CustomerTypeNumber = 7
			    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
          INNER JOIN #RoomTypeWare ON #RoomTypeWare.RoomTypeNumber = #ProductPrices.RoomTypeNumber
          OUTER APPLY dbo.BonusPointForWare(#RoomTypeWare.WareNumber, #ProductPrices.RoomPrice, 1) bpfw
          WHERE dbo.DayColorMatches (@AHotelNumber, #ProductPrices.ProductNumber, @AStartDate, @VacantRoomColorCount)=1 -- @VacantRooms = 0 TODO implement support for auto coloring
          ORDER BY HotelNumber, ProductNumber, #ProductPrices.RoomTypeNumber, [Description]
        ELSE
          SET @ErrorLevel = 13

        DROP TABLE #RoomTypeWare
      END
    END
  END
  ELSE
  BEGIN
    SET @ErrorFilterNumber = 0
    SET @ErrorDateStamp = 0
  END

  DROP TABLE #ProductPrices
  DROP TABLE #VacantRooms
END

IF @ErrorLevel > 0
BEGIN
	CREATE TABLE #DUMMY_RESULT (
	[RoomTypeNumber] [int] NULL,
	[ProductNumber] [int] NULL,
	[Description] [varchar] (128) NULL ,
	[RoomPriceFirstDay] [MONEY] NULL,
	[RoomPrice] [MONEY] NULL,
	[ProductName] [varchar] (255) NULL ,
	[RoomPricePerDay] [MONEY] NULL,
	[RoomPriceMinTotal] [MONEY] NULL,
	[OnlineRoomTypeNumber] [int] NULL,
	[OnlinePriority] [tinyint] NULL,
	[RomTypeName] [varchar] (50) NULL,
  [Adults] [INT] NULL
	)
	SELECT * FROM #DUMMY_RESULT
	DROP TABLE #DUMMY_RESULT
END
