CREATE PROCEDURE [dbo].[OnlineCheckAvailability] @ANumberOfRooms SMALLINT, @AAdults SMALLINT,
  @AChildren1 SMALLINT, @AChildren2 SMALLINT, @AStartDate DATETIME, @AEndDate DATETIME,
  @ProductTypeNumber INT , @ErrorLevel INTEGER OUTPUT, @ErrorFilterNumber INTEGER OUTPUT,
  @ErrorDateStamp DATETIME OUTPUT, @ACustomerNumber INTEGER = 0, @AOnlineRoomTypeNumber INTEGER = 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)='' AS
SET NOCOUNT ON


SET FMTONLY OFF
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:
 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
*/

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

--KS:
DECLARE @TotalNumberOfVacantRooms SMALLINT 
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, @SystemDate)  -- SUNDAY is 0!
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)



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 HotelNumber IN (SELECT DISTINCT Number From #HotelNumbers) -- TB20051208
  --SELECT @YieldNumber=OnlineYieldFilter, @DayColor=DayColor FROM RoomBookingStatusYield (NOLOCK) WHERE DateStamp=@CurrentDateStr AND HotelNumber=0

  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 SysObjects WITH (NOLOCK) where Name='YieldOnlineRoomMinimumPrice'
      
      IF @TableExists = 1
      BEGIN
        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

--REVERTED          SET @MinPricePerDay = @MinPricePerDay * DATEDIFF(DAY, @AStartDate, @AEndDate) -- JP 20070620
        END
      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
	print '@ACustomerRateNumber'
	print @ACustomerRateNumber
	
    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 ON Customer.CustomerNumber = CustomerLinks.CustomerNumber
      WHERE CustomerLinkNumber  = @CustomerNumberTmp
    END

    --****** 20070207
    IF @ACustomerRateNumber = 0
    BEGIN
      -- Don´t use CustomerRate - Customer Not logged in
      -- @ValidProductCount=COUNT (*)
      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) )
    --print @SystemDate
    --print DATEADD(dd, /*IsNull(proe.ProductOnlineClosedtoArrivaldays,0)*/14, @SystemDate)
    --print @AStartDate
    --print IsNull(proe.ProductOnlineClosedtoArrivaldays,0)
    --print @ValidProductCount
    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 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

        --print 'ValidProductCount'
        --print @ValidProductCount
    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
        
        --print 'ValidProductCount'
        --print @ValidProductCount
    END 
    --print 'ValCount'
    --print @ValidProductCount

    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 AND @ValidProductCountWeekdays > 0 -- 20050927
      SET @ErrorLevel = 16*/
    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

--print 'TB33'
--print  @ErrorLevel

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 @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 Upper (SearchKey)=Upper('PicassoOnlineCheckAvailAllTypes') -- Changed OnlineCheckAvailAllTypes to PicassoOnlineCheckAvailAllTypes
  
  DECLARE @OFSDescreaseVacantRooms BIT
  SELECT @OFSDescreaseVacantRooms=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('OOSDecreaseVacantRooms') 

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


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

  --print 'AvailCheckAllTypes'
  --print @AvailCheckAllTypes

  /* Temp table used for vacant rooms per day per roomtype */
  CREATE TABLE #VacantRooms (
	  [DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
	  [RoomTypeNumber] [int] NOT NULL ,
	  [OnlineRoomTypeNumber] [int] NULL,
	  [OnlinePriority] [tinyint] NOT NULL,
	  [VacantRooms] [smallint] NULL,
	  [MaxRooms] [smallint] NULL) --KS
  ON [PRIMARY]

  DECLARE #OnlineCheckAvailability_InitVacantRooms CURSOR FAST_FORWARD FOR

    SELECT A.RoomTypeNumber, A.OnlineRoomTypeNumber, (CASE WHEN OnlinePriority IS NULL THEN 127 ELSE OnlinePriority END) AS OnlinePriority , (SELECT COUNT (*) FROM Rooms WITH (NOLOCK) WHERE RoomTypeNumber=A.RoomTypeNumber) AS NumberOfRooms 
    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 MaxPax >= (@AAdults+@AChildren1+@AChildren2)  -- JP 20040915
    --AND MaxAdultPax >= @AAdults AND MaxChildren1Pax >= @AChildren1 AND MaxChildren2Pax >= @AChildren2
    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, @NumberOfRoomsOnType

  IF @@FETCH_STATUS = -1
    SET @ErrorLevel = 14

  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
	  /*if @CurrentDateStr = '20051028'
	  begin
		  print @CurrentDate
		  print @RoomTypeNumber
	  end*/

      --KS:
      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 IN (SELECT DISTINCT Number From #HotelNumbers)
        and Y.OnlineRoomTypeNumber = @OnlineRoomTypeNumber
        
      --KS:
      DECLARE @TempVacantRooms smallint
      SET @TempVacantRooms = @NumberOfRoomsOnType

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

      --KS:
      INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @TempVacantRooms, @YieldMaxRooms)

      --KS slettet start:
      --IF @AvailCheckAllTypes = 0
      --  INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @NumberOfRoomsOnType-@ANumberOfRooms)  -- NOTE! Subtracts the number of wanted booked rooms here!
      --ELSE --IF @AvailCheckAllTypes = 1
      --  INSERT INTO #VacantRooms VALUES (@CurrentDateStr, @RoomTypeNumber, @OnlineRoomTypeNumber,  @OnlinePriority, @NumberOfRoomsOnType)  -- NOTE! DOES NOT subtract the number of wanted booked rooms here because later we group and only needs it subtracted once per day and not per roomtype!
      --KS slettet slut:

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

    FETCH NEXT FROM #OnlineCheckAvailability_InitVacantRooms
    INTO @RoomTypeNumber, @OnlineRoomTypeNumber, @OnlinePriority, @NumberOfRoomsOnType
  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 ,
--	[SegmentNumber] [int] NOT NULL ,
--	[PointOfSaleNumber] [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 * FROM #VacantRooms -- vac =48
--EXEC GenerateAvailability @AStartDate, @AEndDate, 0, @AHotelNumber, @SystemDate, 0     -- OPTIMIZE: enddate can be one less!


  INSERT INTO #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2 
--EXEC GenerateAvailabilityTime @AStartDate, @AEndDate, @AHotelNumber --TEST
--EXEC GenerateAvailability @AStartDate, @AEndDate, 0, @AHotelNumber, @SystemDate, 1     -- OPTIMIZE: enddate can be one less!
  EXEC GenerateAvailability @AStartDate, @AEndDate, 0, @AHotelNumber, @SystemDate, 0     -- OPTIMIZE: enddate can be one less!

  DECLARE #OnlineCheckAvailability_AvailCur CURSOR FAST_FORWARD FOR

    SELECT DateStamp, RoomTypeNumber, SoldRooms, 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, @TotalRoomsAllotment, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    -- print @datestamp+' - '+convert(char,@roomtypenumber) + ' - '+ convert(char, @soldrooms)
    -- Subtract soldrooms, outoforderrooms and allotment from available rooms
    IF @OFSDescreaseVacantRooms = 0
		  SET @NumberOfOutForService = 0

    --KS:
    DECLARE @UnavailableRooms smallint 
    SET @UnavailableRooms = IsNull(@SoldRooms, 0) + IsNull(@NumberOfOutOfOrder, 0) + IsNull(@NumberOfOutForService, 0) + IsNull(@TotalRoomsAllotment, 0)+ IsNull(@NumberOfNotSaleable, 0)
		
    IF @ACheckavailability = 1 -- LJE20120501
      --KS:
      UPDATE #VacantRooms SET VacantRooms = VacantRooms - @UnavailableRooms
       WHERE DateStamp = @DateStamp 
         AND RoomTypeNumber = @RoomTypeNumber -- TB20050421 Check if not is null

      --KS:
    --UPDATE #VacantRooms SET VacantRooms=VacantRooms-IsNull(@SoldRooms, 0)-IsNull(@NumberOfOutOfOrder, 0)-IsNull(@NumberOfOutForService, 0)-IsNull(@TotalRoomsAllotment, 0) WHERE DateStamp=@DateStamp AND RoomTypeNumber=@RoomTypeNumber -- TB20050421 Check if not is null




    /*if @DateStamp='20051028' and @roomtypenumber=3
    begin
    print  'lala'
    print IsNull(@SoldRooms, 0)
    print IsNull(@NumberOfOutOfOrder, 0)
    print IsNull(@TotalRoomsAllotment, 0)
    print ''

    end*/
    --UPDATE #VacantRooms SET VacantRooms=VacantRooms-@SoldRooms-@NumberOfOutOfOrder-@TotalRoomsAllotment WHERE DateStamp=@DateStamp AND RoomTypeNumber=@RoomTypeNumber

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

  CLOSE #OnlineCheckAvailability_AvailCur
  DEALLOCATE #OnlineCheckAvailability_AvailCur

  DROP TABLE #ONLINECHECKAVAIL_ROOMBOOKINGSTATUS2

--SELECT * FROM #VacantRooms -- vac = 11
--WHERE VacantRooms>=0
--ORDER BY DateStamp, OnlineRoomTypeNumber, OnlinePriority  -- efter! testing

--find alle de rumtyper hvor der ikke er hul i antal dage og min og max availrooms er opfyldt også!
--SELECT RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority /*, (SELECT Type FROM RoomTypes(NOLOCK) WHERE RoomTypeNumber = A.RoomTypeNumber)*/ FROM #VacantRooms /*A*/ (NOLOCK) WHERE (VacantRooms >= @MinRoomsAvail) AND (VacantRooms <= @MaxRoomsAvail OR @MaxRoomsAvail = 0) GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority HAVING COUNT(RoomTypeNumber) = @NumberOfDays ORDER BY OnlinePriority, OnlineRoomTypeNumber  -- Order by OnlineRoomTypeNumber is not needed at all, it just looks better when testing

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

-- 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
      BEGIN
        DELETE FROM #VacantRooms WHERE RoomTypeNumber NOT IN (SELECT RoomTypeNumber FROM #VacantRooms /*A*/ WITH (NOLOCK) WHERE (VacantRooms+@ANumberOfRooms >= @MinRoomsAvail) AND (VacantRooms+@ANumberOfRooms <= @MaxRoomsAvail OR @MaxRoomsAvail = 0) GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority HAVING COUNT(RoomTypeNumber) = @NumberOfDays)
        -- + @ANumberOfRooms because we are checking before they are subtracted
    END
  END
  ELSE
  BEGIN  
    DELETE FROM #VacantRooms WHERE DateStamp IN (
    SELECT DateStamp FROM #VacantRooms WITH (NOLOCK)
    GROUP BY DateStamp
    HAVING (SUM(VacantRooms)-@ANumberOfRooms < @MinRoomsAvail) 
        OR (SUM(VacantRooms)-@ANumberOfRooms > @MaxRoomsAvail AND @MaxRoomsAvail > 0)  -- before
    )

    --KS:
    --UPDATE #VacantRooms SET VacantRooms=VacantRooms - @ANumberOfRooms

    --KS:
    --DELETE FROM #VacantRooms WHERE VacantRooms < 0

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

    /* 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) 
    GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority 
    HAVING COUNT(RoomTypeNumber) = @NumberOfDays)
  END

--select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after
 
--TB 20070815 - Check MaxPaxTotal pax from OnlineRoomtypes
  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 WITH (NOLOCK) WHERE OnlineRoomTypeNumber IN
   (SELECT OnlineRoomTypeNumber FROM onlineroomtypes WITH (NOLOCK)
    WHERE MaxPaxTotal < @TotPax
    OR maxadultpax < @AAdults 
    OR MaxChildren1Pax < @AChildren1
    OR MaxChildren2Pax < @AChildren2)
*/
-- New PAX Check - 20120126
    DELETE FROM #VacantRooms WHERE OnlineRoomTypeNumber IN
   (SELECT OnlineRoomTypeNumber FROM onlineroomtypes (NOLOCK)
    WHERE MaxPaxTotal < @TotPax
    OR maxadultpax < @AAdults 
    OR MaxChildren1Pax < @AChildren1
    OR MaxChildren2Pax < @AChildren2)
    
    -- TBU20140625 - Check for MinPax on OnlineRoomtypes
    DELETE FROM #VacantRooms WHERE OnlineRoomTypeNumber IN
    (SELECT OnlineRoomTypeNumber FROM onlineroomtypes (NOLOCK)
    WHERE ( IsNull(MinPax, 0) > @TotPax))
    
/*
    SELECT * FROM #VacantRooms WITH (NOLOCK)
    WHERE OnlineRoomTypeNumber in 
   (SELECT DISTINCT OnlineRoomTypeNumber FROM #VacantRooms WITH (NOLOCK)
    --WHERE VacantRooms < 1 and MaxRooms > 0 )
    WHERE MaxRooms = 0 or VacantRooms < 1 and MaxRooms > 0 or MaxRooms < 0 and @ANumberOfRooms > (MaxRooms * -1) + VacantRooms)
*/
    --KS:
    DELETE FROM #VacantRooms
    WHERE OnlineRoomTypeNumber in 
   (SELECT DISTINCT OnlineRoomTypeNumber FROM #VacantRooms WITH (NOLOCK)
    WHERE MaxRooms = 0 or VacantRooms < 1 and MaxRooms > 0 or MaxRooms < 0 and @ANumberOfRooms > (MaxRooms * -1) + VacantRooms)

	      /*AND (MaxRooms=0 OR @ANumberOfRooms>MaxRooms and MaxRooms > 0*/ --KS
         /* or @ANumberOfRooms > (MaxRooms * -1) and MaxRooms < 0 )*/ --KS


/*
print 'før'
SELECT DISTINCT OnlineRoomTypeNumber FROM #VacantRooms WITH (NOLOCK)
print 'efter'
*/
    --KS:
/*
    SELECT @TotalNumberOfVacantRooms = SUM(VacantRooms) FROM #VacantRooms 
    WHERE OnlineRoomTypeNumber in (SELECT DISTINCT OnlineRoomTypeNumber FROM onlineroomtypes (NOLOCK))
*/


    SELECT @TotalNumberOfVacantRooms = SUM(VacantRooms) FROM #VacantRooms 
    WHERE OnlineRoomTypeNumber in (SELECT DISTINCT OnlineRoomTypeNumber FROM #VacantRooms WITH (NOLOCK))
/*
print '@TotalNumberOfVacantRooms=' + str(@TotalNumberOfVacantRooms)

    select * FROM #VacantRooms with (nolock)
    WHERE MaxRooms < 0 and @ANumberOfRooms > @TotalNumberOfVacantRooms

*/
  
    --KS:
    DELETE FROM #VacantRooms 
    WHERE MaxRooms < 0 and @ANumberOfRooms > @TotalNumberOfVacantRooms

    --KS:
    UPDATE #VacantRooms SET VacantRooms=VacantRooms - @ANumberOfRooms --- Hvorfor egentlig??

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

  /*DELETE FROM #VacantRooms WHERE OnlineRoomTypeNumber IN (Select onlineroomtypes.onlineroomtypenumber
    From OnlineRoomtypes WITH (Nolock) 
    inner join YieldonlineRoomtypesxtab WITH (Nolock) on onlineroomtypes.onlineroomtypenumber =YieldonlineRoomtypesxtab.onlineroomtypenumber  
    where YieldonlineRoomtypesxtab.yieldnumber 
    in (SELECT distinct OnlineYieldFilter FROM RoomBookingStatusYield WITH (NOLOCK) 
    WHERE DateStamp <= @AEndDate AND DateStamp >= @AStartDate AND HotelNumber=@AHotelNumber) 
    AND MaxPaxTotal < @TotPax AND (MaxAdultPax < @AAdults OR MaxChildren1Pax < @AChildren1 OR MaxChildren2Pax < @AChildren2) )*/

    IF @@rowcount > 0
    BEGIN
      DECLARE @FoundNum INT
      SELECT @FoundNum=COUNT(*) FROM #VacantRooms WITH (NOLOCK)

      IF @FoundNum = 0
        SET @ErrorLevel = 17
    END
  END

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

  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- SELECT (SELECT MIN(RoomTypeNumber) FROM RoomTypes (NOLOCK) WHERE OnlineRoomTypeNumber = A.OnlineRoomTypeNumber AND OnlinePriority = MIN(A.OnlinePriority)) AS RoomTypeNumber, OnlineRoomTypeNumber, MIN(OnlinePriority) AS OnlinePriority
-- FROM #VacantRooms A (NOLOCK) WHERE VacantRooms>=0 GROUP BY OnlineRoomTypeNumber
  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  /* 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,
	[UseZeroPrice] [Bit] NULL
	)
  ON [PRIMARY]

-- tb test - slettes
/*SELECT DISTINCT Products.ProductNumber, Description FROM Products (NOLOCK)
      INNER JOIN ProductPrice (NOLOCK) ON Products.ProductNumber=ProductPrice.ProductNumber AND
      ((MarkAsDeleted=0 OR MarkAsDeleted IS NULL) AND AllowFilterPicassoOnline=1 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
       AND IsNull(MiniMumDays, 0)<=@NumberOfDays -- Added 20050419
       AND IsNull(MaximumDays, 0)>=@NumberOfDays )*/
--

  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)

--    SELECT DISTINCT (SELECT MIN(RoomTypeNumber) FROM RoomTypes (NOLOCK) WHERE OnlineRoomTypeNumber = A.OnlineRoomTypeNumber AND OnlinePriority = MIN(A.OnlinePriority)) AS RoomTypeNumber
--    FROM #VacantRooms A (NOLOCK) WHERE VacantRooms>=0 GROUP BY OnlineRoomTypeNumber  --TEST!

--***** 20070207
--** 20120112
--**
 
    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 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 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 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 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 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 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
        
        --print 'ValidProductCount'
        --print @ValidProductCount
    END


 --OLD
     /* DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR
      -- JP 20050622 only select the products for which we have prices for our roomtypes
      SELECT DISTINCT Products.ProductNumber, Description FROM Products (NOLOCK)
      INNER JOIN ProductPrice (NOLOCK) ON Products.ProductNumber=ProductPrice.ProductNumber AND
      ((MarkAsDeleted=0 OR 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 (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber*/

--***** 

    OPEN #OnlineCheckAvailability_ProductsCur

    DECLARE #OnlineCheckAvailability_RoomTypesCur SCROLL CURSOR FOR

--  SELECT DISTINCT (SELECT MIN(RoomTypeNumber) FROM RoomTypes (NOLOCK) WHERE OnlineRoomTypeNumber = A.OnlineRoomTypeNumber AND OnlinePriority = MIN(A.OnlinePriority)) AS RoomTypeNumber
--  FROM #VacantRooms A (NOLOCK) WHERE VacantRooms>=0 GROUP BY OnlineRoomTypeNumber
    SELECT DISTINCT RoomTypeNumber FROM #VacantRooms

    OPEN #OnlineCheckAvailability_RoomTypesCur

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

    WHILE (@@FETCH_STATUS <> -1) AND (@ErrorLevel = 00)
    BEGIN
      FETCH FIRST FROM #OnlineCheckAvailability_RoomTypesCur -- reuse cursor and read from top
      INTO @RoomTypeNumber

      WHILE (@@FETCH_STATUS <> -1) --AND (@ErrorLevel = 00)
      BEGIN
      --SELECT * FROM YieldOnlineRoomTypesXTab (NOLOCK) WHERE @ANumberOfRooms<=MaxRooms AND YieldNumber=@YieldNumber AND OnlineRoomTypeNumber IN (SELECT OnlineRoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber=@RoomTypeNumber)
      --print '@YieldNumber'     
      --print @ANumberOfRooms
      --@AStartDate DATETIME, @AEndDate
      
        -- KS ændret fra
        --SELECT @Found=COUNT(*) FROM RoomBookingStatusYield WITH (NOLOCK) 
    		--INNER JOIN YieldOnlineRoomTypesXTab ON RoomBookingStatusYield.OnlineYieldFilter = YieldOnlineRoomTypesXTab.YieldNumber 
		    --WHERE CONVERT(DATETIME, DateStamp, 112)  >= CONVERT(DATETIME, @AStartDate, 112) 
		    --AND CONVERT(DATETIME, DateStamp, 112)  < CONVERT(DATETIME, @AEndDate, 112)  
		    --AND HotelNumber = @AHotelNumber AND (MaxRooms = 0 OR @ANumberOfRooms > MaxRooms ) AND OnlineRoomTypeNumber IN 
		    --(SELECT OnlineRoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = @RoomTypeNumber)
		    ----COUNT(*) FROM YieldOnlineRoomTypesXTab (NOLOCK) WHERE @ANumberOfRooms<=MaxRooms AND YieldNumber=@YieldNumber AND OnlineRoomTypeNumber IN 
		    ----(SELECT OnlineRoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = @RoomTypeNumber)
		    -- KS ændret til

        SELECT @Found=COUNT(*) FROM RoomBookingStatusYield R WITH (NOLOCK) 
	      INNER JOIN YieldOnlineRoomTypesXTab Y ON R.OnlineYieldFilter = Y.YieldNumber 
	      inner join #VacantRooms V on V.OnlineRoomTypeNumber = Y.OnlineRoomTypeNumber
	      WHERE CONVERT(DATETIME, R.DateStamp, 112) >= CONVERT(DATETIME, @AStartDate, 112) 
	      AND CONVERT(DATETIME, R.DateStamp, 112) < CONVERT(DATETIME, @AEndDate, 112)  
	      AND (V.MaxRooms = 0 
	       OR V.MaxRooms > 0 and @ANumberOfRooms > V.MaxRooms 
	       OR V.MaxRooms > 0 and V.VacantRooms < 0 
         OR V.MaxRooms < 0 and V.VacantRooms < 0 and @ANumberOfRooms > (-1 * (V.MaxRooms + V.VacantRooms))
         OR V.MaxRooms < 0 and V.VacantRooms > -1 and @ANumberOfRooms > (-1 * V.MaxRooms + V.VacantRooms))
	      AND Y.OnlineRoomTypeNumber IN (SELECT OnlineRoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = @RoomTypeNumber)
		    -- KS ændret slut

        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, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
          ELSE
          BEGIN
            EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @ANumberOfRooms, @AAdults, 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, @NamelistSetting int, @OnePersonPaysForAll Bit
	        DECLARE @DateNow Datetime, @UseZeroPrice BIT
	        DECLARE @DateStr Char(8), @ChepPriceTotal MONEY--, @RoomPricePerDay MONEY
	
	
	        --SET @Start = '20100324'--CONVERT(Datetime, '20100324')
	        --SET @End = '20100328'--CONVERT(Datetime, '20100328')
	        SET @DateNow = @AStartDate
	        SET @ChepPriceTotal = -1
	        --SET @UseZeroPrice = 1
        	
	        WHILE (@DateNow < @AEndDate)
	        BEGIN
		        --@AStartDate DATETIME, @AEndDate
	           --print '@ProductNumber'
	           --print @ProductNumber
		        --[dbo].[ProductPricesDay] @OldProductNumber int,  @ArrivalDate datetime, @DepartureDate datetime, @PriceDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, @AdultPax int, @Child1Pax int, @Child2Pax int, @Addon money OUTPUT, @BillTextProduct VARCHAR(50) OUTPUT, @BillTextAddOn VARCHAR(50) OUTPUT, @CalcPriceDay money OUTPUT, @PacketNumber int OUTPUT, @EnvironmentFee smallmoney OUTPUT, @FixedPrice Bit OUTPUT, @VATProduct Bit OUTPUT,  @SpecifiedDepartureAddOn Bit OUTPUT, @NamelistSetting int = -1, @OnePersonPaysForAll Bit = 0, @EnvironmentFeeType Float = 0 OUTPUT, @AIsDayProduct BIT = 0 AS
	          exec [dbo].[ProductPricesDay] @ProductNumber, @AStartDate, @AEndDate, @DateNow, @RoomTypeNumber, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting = -1, @OnePersonPaysForAll = 0, @EnvironmentFeeType = 0 , @AIsDayProduct = 0 
	           --print CONVERT(VARCHAR(50), @CalcPriceDay)	
	          IF @ChepPriceTotal = -1
	          BEGIN 
		          SET @ChepPriceTotal = @CalcPriceDay+@Addon
	          END
	          ELSE
	          BEGIN
		          IF @CalcPriceDay < @ChepPriceTotal
		          BEGIN
			          SET @ChepPriceTotal = @CalcPriceDay+@Addon
		          END
	          END
	           --print'--'
        	
	          SET @DateNow = DATEADD(DAY, 1, @DateNow)
	        END

/*Print 'END'
print @RoomTypeNumber
print @ProductNumber
Print CONVERT(VARCHAR(50),@ChepPriceTotal)*/

/*
        --Check conditions
        IF @CalcTotalPrice <@MinStayPrice
          SET @ErrorLevel = 10
        ELSE IF @CalcArrivalPrice < @MinPricePerDay
          SET @ErrorLevel = 11

        IF @ErrorLevel = 00
*/--print '@CalcTotalPrice'
          INSERT INTO #ProductPrices VALUES (@RoomTypeNumber, @ProductNumber, @Description, @CalcArrivalPrice, @CalcTotalPrice, @ProductName, @CalcPriceDay, @ChepPriceTotal, 1)
        END
        ELSE
          SET @SpecialErrorLevel = 12
		
		--  Check Zero Price from product
		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
      END

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

    CLOSE #OnlineCheckAvailability_RoomTypesCur
    DEALLOCATE #OnlineCheckAvailability_RoomTypesCur

    CLOSE #OnlineCheckAvailability_ProductsCur
    DEALLOCATE #OnlineCheckAvailability_ProductsCur
  END

--select * from  #ProductPrices (nolock)
-- Remove prices where roomprice > 0
  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
	--print 'BookWithZeroPriceperday'
	--print @BookWithZeroPriceperday
	
  IF @ErrorLevel = 00 AND @BookWithZeroPriceperday = 0
  BEGIN
  	--print 'remove zero price'
  	IF @UseAllowZeroPriceOnProduct = 1
  	BEGIN
		DELETE FROM  #ProductPrices WHERE RoomPriceMinTotal = 0 AND UseZeroPrice = 0 --RoomPricePerDay
	END
	ELSE
	BEGIN
		DELETE FROM  #ProductPrices WHERE RoomPricePerDay = 0
		-- 20151106 - Check RoomPriceFirstDay
		DELETE FROM  #ProductPrices WHERE RoomPriceFirstDay = 0
	END
  END
--select * from  #ProductPrices (nolock)

  -- Return prices per 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

--***  HolidayTypes 20091109
	--select * from  #ProductPrices (nolock)
	--SELECT Productnumber FROM Products pr (NOLOCK) Inner JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber WHERE AllowFilterpicassoOnline = 1 AND pt.ProductTypeNumber = @ProductTypeNumber
      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
          BEGIN
	          SET @ErrorLevel = 15
		        print '15ff'
          END
        END
      END
    --select * from  #ProductPrices (nolock)
--SELECT Productnumber FROM Products pr (NOLOCK) Inner JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  WHERE AllowFilterpicassoOnline = 1 AND pt.ProductTypeNumber = @ProductTypeNumber
--***
--print 'dayweek'
--print @DayOfWeek

    --select * from  #ProductPrices (nolock)
-- See if #ProductPrices (nolock) is empty already
      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
	    --print 'day1'
	       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)) )
	       --SET @ArrivalDay = 'ArrivalSunday'
	    END
	    IF @DayOfWeek = 2
	    BEGIN
	    --print 'day2'
	       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)) )
	       --SET @ArrivalDay = 'ArrivalMonday'
	    END
	    IF @DayOfWeek = 3
	    BEGIN
	    --print 'day3'
	       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)) )
	       --SET @ArrivalDay = 'ArrivalTuesday'
	    END
	    IF @DayOfWeek = 4
	    BEGIN
	    --print 'day4'
	       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)) )
	       --SET @ArrivalDay = 'ArrivalWednesday'
	    END
	    IF @DayOfWeek = 5
	    BEGIN
	    --print 'day5'
	       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
	    --print 'day6'
	       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
	    --print 'day7'
	       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)) )
	       --SET @ArrivalDay = 'ArrivalSaturday'
	    END
	
			-- Check for summeryproduct - IF @AvailabilitySummeryproductMode=2, don´t do anything
			print 'avl'
			print @AvailabilitySummeryproductMode
			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)
				print 'No sumprod'			
			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)
				print 'Only sumprod'						
			END
			
			--Check for children
			DECLARE @ProductHasChildren INT
			SELECT @ProductHasChildren = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductExtended' AND COLUMN_NAME = 'NoChildren'
			print @ProductHasChildren
			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 FROM #ProductPrices

       OPEN #OnlineCheckAvailability_ProductsCur
       FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur
       INTO @RoomTypeNumber, @ProductNumber

       WHILE (@@FETCH_STATUS <> -1)
       BEGIN
         EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT

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

         FETCH NEXT FROM #OnlineCheckAvailability_ProductsCur
         INTO @RoomTypeNumber, @ProductNumber
       END

       CLOSE #OnlineCheckAvailability_ProductsCur
       DEALLOCATE #OnlineCheckAvailability_ProductsCur
      END

	--select * from  #ProductPrices (nolock)

--	IF @@rowcount > 0
--           BEGIN
                print 'inde'
	    SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
	    
	    IF @Found = 0
	    BEGIN
	      SET @ErrorLevel = 16
	      print '16fejl'
	    END	        
	  END
--            END


--SELECT * from #ProductPrices with (nolock)
DECLARE @UsedynamicPaxText BIT
DECLARE @Usedynamicstring VARCHAR(50)
DECLARE @TotPaxDynamicText INT
SET @TotPaxDynamicText = @AAdults + @AChildren1 +  @AChildren2
SET @UsedynamicPaxText=0
SELECT @Usedynamicstring = KeyValue FROM HotelSetup(NOLOCK) WHERE SearchKey = 'onlineusedynamicpaxtext'
IF @Usedynamicstring <> ''
	SET @UsedynamicPaxText=0

      IF @ErrorLevel = 00
      BEGIN
        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
        IF @Found = 0 and @SpecialErrorLevel = 12 -- if no prices found and there has been problems with exceeding maxrooms on some types then return error
          SET @ErrorLevel = @SpecialErrorLevel

        ELSE
--          SELECT @Found=COUNT(*) FROM #ProductPrices (NOLOCK)
          IF @Found > 0
  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--            SELECT #ProductPrices.*, OnlineRoomTypeNumber, OnlinePriority FROM #ProductPrices (NOLOCK) INNER JOIN RoomTypes (NOLOCK) ON #ProductPrices.RoomTypeNumber=RoomTypes.RoomTypeNumber ORDER BY [Description]

            SELECT --#ProductPrices.*
              #ProductPrices.RoomTypeNumber
              ,#ProductPrices.ProductNumber
              ,ISNULL(#ProductPrices.[Description],'') AS [Description]
              ,#ProductPrices.RoomPriceFirstDay
              ,#ProductPrices.RoomPrice
              ,ISNULL(#ProductPrices.ProductName,'') AS ProductName
			  ,#ProductPrices.RoomPricePerDay
              ,#ProductPrices.RoomPriceMinTotal
			,  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  
			 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
             WHERE dbo.DayColorMatches (@AHotelNumber, #ProductPrices.ProductNumber, @AStartDate, @VacantRoomColorCount)=1 -- @VacantRooms = 0 TODO implement support for auto coloring
            ORDER BY [Description]
  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          ELSE
            SET @ErrorLevel = 13
      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
	)
	SELECT * FROM #DUMMY_RESULT
	DROP TABLE #DUMMY_RESULT
  END


