
CREATE PROCEDURE [dbo].[OnlineCheckAvailabilityTime] @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 INTEGER = 0, @ACustomerRateNumber INT = 0, @ACountryNumber INT = 0
,@AvailabilitySummeryproductMode INT = 2
,@IsOwnerBooking BIT=0 AS
SET NOCOUNT ON
/*
  Created by Jacob Pedersen January 2004

  Changes:
   LJE20120709: @IsOwnerBooking parameter added.
   KC20120403: Added Number of Out-for-service	
   TB20120214: Added @AvailabilitySummeryproductMode
   TB20110829: Added join to OnlineRoomTypesTranslated to final select
   TB20100607: Added parameter @ACountryNumber INT = 0
   TB+KJ 20081218: Added  @NumberOfDays+1 when comparing roomtypenumber and @NumberOfDays
   MFR20070618: Changed calculation of hours to handle different dates.
   TB20070516:  DELETE FROM #VacantRooms WHERE VacantRooms <= 0
   TB20070516: Changed SET @NumberOfDays
   TB20070515: Changed @ValidProductCount at @ACustomerRateNumber = 0
   TB20070507:  Added call to GenerateAvailabilityTime
   TB20070330: Added Check for booking per hours
   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
   NLB20060504: Use @AHotelNumber to find YieldFilter
   TB20060212:  Availability is Ordered by Description
   TB20051227: Added Check on null for hotelnumber
   TB20051209: Removed @AHotelNumber to find YieldFilter
   TB20051208: Use @AHotelNumber to find YieldFilter
  NLB20051202: Errorlevel 16 erstattet med ny kode.
  TB20051014: In Check for errorlevel 15: Delete from #ProductPrices has been removed. 
  TB20050927: Added ErrorLevel 16 -  Check Error level 16 - Remove all products that don´t match arive date
  TB20050926:  Added 1 as parameter in GenerateAvailability insteda of 0
  TB20050803: Added hotelnumber to insert into #VacantRooms
  TB20050628:  Added ErrorLevel 15 - HolidayTypes
  JP20050622: Changed: only select the products for which we have prices for our roomtypes
  JP20050622: Changed: Added @AHotelNumber parameter for availability. Backwards compatible
  TB20050421:  When Update Tabel #VacantRooms: Check if any values id not null  
  TB20050419: Changed #OnlineCheckAvailability_ProductsCur. Now it check for conditions on the product. Min/max days and arrivaldays
  TB20050414: Changed @ValidProductCount. Check if arrive day is ok and if min and max days is ok for product.
  TB20050225: Changed searchkey name from OnlineCheckAvailAllTypes to PicassoOnlineCheckAvailAllTypes
  JP20050224: hotelsetting for chosing if availability is checked per roomtype or all rooms. errorlevel 13 problem
  JP20041216: Added ErrorLevel 14
  JP20041210: Added missing field SoldPrice
  TB20040106: Added  isNull check for @TimePeriod
  JP20040920: fixed arrival on mon-sun
  JP20040920: fixed errorlevel 10 + 11 checks
  JP20040915: added check for MaxPax on RoomType so we don't try to fit more people than can be fitted.

  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

*/
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  
*/

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

print '@NumberOfDays 1'
print @NumberOfDays
print '@AEndDate'
print @AEndDate
print '@AStartDate'
print @AStartDate
print '---'

DECLARE @YieldNumber INT
DECLARE @DayColor INT

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
SELECT @UseProductTypes = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'PicassoOnlineUseProductTypes'

DECLARE @OFSDecreaseVacantRoom INT
SELECT @OFSDecreaseVacantRoom = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'OOSDecreaseVacantRooms'

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!

DECLARE @HotelNumberStr VARCHAR(100)
SET @HotelNumberStr = CAST(@AHotelNumber AS VARCHAR(100))
IF @HotelNumberStr = '-1'
	SET @HotelNumberStr = ''

WHILE (@ErrorLevel = 00) AND (@CurrentDateStr <= @FormatedEndDate)
BEGIN
   --print 'TBYield'
    --print @CurrentDateStr
   --print @AHotelNumber
   SELECT @YieldNumber=OnlineYieldFilter, @DayColor=DayColor FROM RoomBookingStatusYield WITH (NOLOCK) WHERE DateStamp=@CurrentDateStr AND HotelNumber=@AHotelNumber -- TB20051208
   --SELECT @YieldNumber=OnlineYieldFilter, @DayColor=DayColor FROM RoomBookingStatusYield WITH (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=MinPricePerDay
    FROM Yield WITH (NOLOCK) WHERE YieldNumber=@YieldNumber AND FilterTypeNumber=1 -- 1 = DELPHI: TYieldFilter = yfPicassoOnline

--** TB Test 20070328
SET @MinDays  = 0
--**

--****** 20070207
IF @ACustomerRateNumber = 0
BEGIN
 -- Don´t use CustomerRate - Customer Not logged in
  -- @ValidProductCount=COUNT (*)
declare @DiffDateParm DateTime
SET @DiffDateParm = CONVERT(DateTime, '00:00')
SELECT @ValidProductCount=COUNT (*) FROM Products WITH (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.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 (
         ( CONVERT(CHAR(5), @AStartDate, 108) >= CONVERT(CHAR(5), Products.StartTime , 108)    AND CONVERT(CHAR(5), @AEndDate, 108) <= CONVERT(CHAR(5), Products.EndTime , 108) )
         OR  (CONVERT(CHAR(5), Products.StartTime , 108) = '00:00'  AND CONVERT(CHAR(5), Products.EndTime , 108) = '00:00' ) 
        )
      AND DateDiff(MINUTE,@AStartDate, @AEndDate)/60.0>=DateDiff(Minute,@DiffDateParm,Convert(DateTime,CONVERT(char(5),Products.MinimumTime,108))) /60.0
      AND DateDiff(MINUTE,@AStartDate, @AEndDate)/60.0<= DateDiff(Minute,@DiffDateParm,Convert(DateTime,CONVERT(char(5),Products.MaximumTime,108))) /60.0
 
      


/*
  SELECT @ValidProductCount=COUNT (*)  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   -- TB20070328 * Here must be changed for use of time booking
       AND IsNull(CustomerRate, 0) = 0)
       AND (
         ( CONVERT(CHAR(5), @AStartDate, 108) >= CONVERT(CHAR(5), Products.StartTime , 108)    AND CONVERT(CHAR(5), @AEndDate, 108) <= CONVERT(CHAR(5), Products.EndTime , 108) )
         OR  (CONVERT(CHAR(5), Products.StartTime , 108) = '00:00'  AND CONVERT(CHAR(5), Products.EndTime , 108) = '00:00' ) 
        )
        AND CONVERT(CHAR(5), @AEndDate - @AStartDate , 108) >= CONVERT(CHAR(5), Products.MinimumTime, 108) AND CONVERT(CHAR(5), @AEndDate - @AStartDate , 108) <= CONVERT(CHAR(5), Products.MaximumTime, 108)
*/
/*
print '@NumberOfDays'
print @NumberOfDays
print'@AStartDate'
print @AStartDate
print '@AEndDate'
print @AEndDate
print '@FormatedEndDate'
print @FormatedEndDate*/



  --    AND Datediff(MINUTE,@AEndDate, @AStartDate)/60.0>=DateDiff(MINUTE,@DiffDateParm,Products.MinimumTime)/60.0
--     AND DateDiff(MINUTE,@AEndDate, @AStartDate)/60.0<=DateDiff(MINUTE,@DiffDateParm,Products.MaximumTime)/60.0


       --INNER JOIN #VacantRooms (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber

 /*SELECT @ValidProductCount=COUNT (*) FROM Products (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.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*/
END
ELSE IF @ACustomerRateNumber = 1
BEGIN
 -- General products and CustomerRate products
 SELECT @ValidProductCount=COUNT (*) FROM Products WITH (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.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) 
END
ELSE IF @ACustomerRateNumber = 2
BEGIN
  -- Only CustomerRate products
  SELECT @ValidProductCount=COUNT (*) FROM Products WITH (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.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
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 INNER JOIN
    CustomerFieldsPerHotel ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber INNER JOIN
    Products ON Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
    WHERE     (CUSTOMER.customernumber = @ACustomerNumber) AND (CustomerFieldsPerHotel.HotelNumber = @AHotelNumber)
    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

/* OLD
    --SELECT @ValidProductCount=COUNT (*) FROM Products (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND CONVERT(DATETIME, ProductDate)<=@AStartDate AND ProductEndDate>=@FormatedEndDate
   SELECT @ValidProductCount=COUNT (*) FROM Products (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.DayProduct = 0
    AND CONVERT(DATETIME, ProductDate)<=@AStartDate 
    AND ProductEndDate>=@FormatedEndDate 
    AND IsNull(MiniMumDays, 0)<=@NumberOfDays 
    AND IsNull(MaximumDays, 0)>=@NumberOfDays */

----****** 

    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,
  @SoldPrice MONEY

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

  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)
  ON [PRIMARY]


  DECLARE #OnlineCheckAvailability_InitVacantRooms CURSOR FAST_FORWARD FOR

    SELECT RoomTypeNumber, 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)
    WHERE OnlineRoomTypeNumber > 0
    AND IsNull(HotelNumber, 0) =  @AHotelNumber -- TB20050803 - Used for chainhotel - TB20051227
    AND MaxPax >= (@AAdults+@AChildren1+@AChildren2)  -- JP 20040915
    ORDER BY 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*/
      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!

      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 WITH (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 ,
		[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,
        NumberOfOccupied SMALLINT NULL,
		[SoldPrice] [MONEY] NULL
		)

--SELECT * FROM #VacantRooms -- vac =48

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

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

  DECLARE #OnlineCheckAvailability_AvailCur CURSOR FAST_FORWARD FOR

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

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

--TB20070516
DELETE FROM #VacantRooms WHERE VacantRooms <= 0

  IF @AvailCheckAllTypes = 0
  BEGIN
	--select * FROM #VacantRooms WHERE RoomTypeNumber NOT IN (SELECT RoomTypeNumber FROM #VacantRooms /*A*/ (NOLOCK) WHERE (VacantRooms+@ANumberOfRooms >= @MinRoomsAvail) AND (VacantRooms+@ANumberOfRooms <= @MaxRoomsAvail OR @MaxRoomsAvail = 0) GROUP BY RoomTypeNumber, OnlineRoomTypeNumber, OnlinePriority) -- HAVING COUNT(RoomTypeNumber) = @NumberOfDays)
    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
  ELSE
  BEGIN
  --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- before

    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
    )

    UPDATE #VacantRooms SET VacantRooms=VacantRooms - @ANumberOfRooms

    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+1)

  END

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

  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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)
  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

--    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!

IF @ACustomerRateNumber = 0
BEGIN
 -- Don´t use CustomerRate - Customer Not logged in

-- TB test

 /*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  
       AND IsNull(CustomerRate, 0) IN (0,1) )
   INNER JOIN #VacantRooms (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber


 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   -- TB20070328 * Here must be changed for use of time booking
       AND IsNull(CustomerRate, 0) = 0)
       AND (
         ( CONVERT(CHAR(5), @AStartDate, 108) >= CONVERT(CHAR(5), Products.StartTime , 108)    AND CONVERT(CHAR(5), @AEndDate, 108) <= CONVERT(CHAR(5), Products.EndTime , 108) )
         OR  (CONVERT(CHAR(5), Products.StartTime , 108) = '00:00'  AND CONVERT(CHAR(5), Products.EndTime , 108) = '00:00' ) 
        )
        AND CONVERT(CHAR(5), @AEndDate - @AStartDate , 108) >= CONVERT(CHAR(5), Products.MinimumTime, 108) AND CONVERT(CHAR(5), @AEndDate - @AStartDate , 108) <= CONVERT(CHAR(5), Products.MaximumTime, 108)
      INNER JOIN #VacantRooms (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber*/

-- tb end



   DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR

  SELECT DISTINCT Products.ProductNumber, Description FROM Products WITH (NOLOCK)
  INNER JOIN ProductPrice WITH (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   -- TB20070328 * Here must be changed for use of time booking
       AND IsNull(CustomerRate, 0) = 0)
       AND (
         ( CONVERT(CHAR(5), @AStartDate, 108) >= CONVERT(CHAR(5), Products.StartTime , 108)    AND CONVERT(CHAR(5), @AEndDate, 108) <= CONVERT(CHAR(5), Products.EndTime , 108) )
         OR  (CONVERT(CHAR(5), Products.StartTime , 108) = '00:00'  AND CONVERT(CHAR(5), Products.EndTime , 108) = '00:00' ) 
        )
      AND DateDiff(MINUTE,@AStartDate, @AEndDate)/60.0>=DateDiff(Minute,@DiffDateParm,Convert(DateTime,CONVERT(char(5),Products.MinimumTime,108))) /60.0
      AND DateDiff(MINUTE,@AStartDate, @AEndDate)/60.0<= DateDiff(Minute,@DiffDateParm,Convert(DateTime,CONVERT(char(5),Products.MaximumTime,108))) /60.0
       INNER JOIN #VacantRooms WITH (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber

 

      -- AND CONVERT(CHAR(5), @AStartDate, 108) >= CONVERT(CHAR(5), Products.StartTime , 108)
      -- AND CONVERT(CHAR(5), @AEndDate, 108) <= CONVERT(CHAR(5), Products.EndTime , 108)
       --AND (CONVERT(CHAR(5), Products.StartTime , 108) = '00:00'  AND CONVERT(CHAR(5), Products.EndTime , 108) = '00:00' ) 
 /*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
       AND IsNull(CustomerRate, 0) = 0)
       INNER JOIN #VacantRooms (NOLOCK) ON ProductPrice.RoomTypeNumber=#VacantRooms.RoomTypeNumber*/

/*print 'troe 1'
print @AStartDate
print @AEndDate
print @FormatedEndDate
print @NumberOfDays*/
END
ELSE IF @ACustomerRateNumber = 1
BEGIN

 -- General products and CustomerRate products
     DECLARE #OnlineCheckAvailability_ProductsCur CURSOR FAST_FORWARD FOR

     SELECT DISTINCT Products.ProductNumber, Description FROM Products WITH (NOLOCK)
      INNER JOIN ProductPrice WITH (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  
       AND IsNull(CustomerRate, 0) IN (0,1) )
   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 Products.ProductNumber, Description FROM Products WITH (NOLOCK)
     INNER JOIN ProductPrice WITH (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 
      AND IsNull(CustomerRate, 0) = 1 )
      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 FROM CUSTOMER WITH (NOLOCK)   
   INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
   AND (CUSTOMER.customernumber = @ACustomerNumber) 
   AND (CustomerFieldsPerHotel.HotelNumber = @AHotelNumber)
   INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
   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

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

 --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*/

--***** 

--rint 'F'
--print @ErrorLevel
 --select * from #VacantRooms (nolock) order by datestamp,roomtypenumber-- after

    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

    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 OnlineRoomTypeNumber FROM YieldOnlineRoomTypesXTab (NOLOCK) WHERE YieldNumber=@YieldNumber AND @ANumberOfRooms<=MaxRooms -- Test
        SELECT @Found=COUNT(*) FROM YieldOnlineRoomTypesXTab WITH (NOLOCK) WHERE @ANumberOfRooms<=MaxRooms AND OnlineRoomTypeNumber IN (SELECT OnlineRoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber=@RoomTypeNumber)

        IF @Found > 0
        BEGIN
          --print 'yeldxtab'
          EXEC ProductPrices @ProductNumber, @AStartDate, @AEndDate, @RoomTypeNumber, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
/*
        --Check conditions
        IF @CalcTotalPrice <@MinStayPrice
          SET @ErrorLevel = 10
        ELSE IF @CalcArrivalPrice < @MinPricePerDay
          SET @ErrorLevel = 11


        IF @ErrorLevel = 00
*/
            INSERT INTO #ProductPrices VALUES (@RoomTypeNumber, @ProductNumber, @Description, @CalcArrivalPrice, @CalcTotalPrice)
        END
        ELSE
          SET @SpecialErrorLevel = 12

        FETCH NEXT FROM #OnlineCheckAvailability_RoomTypesCur
        INTO @RoomTypeNumber
      END

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

    CLOSE #OnlineCheckAvailability_RoomTypesCur
    DEALLOCATE #OnlineCheckAvailability_RoomTypesCur

    CLOSE #OnlineCheckAvailability_ProductsCur
    DEALLOCATE #OnlineCheckAvailability_ProductsCur
  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 RoomPriceFirstDay < @MinPricePerDay
      IF @@rowcount > 0
      BEGIN
        SELECT @Found=COUNT(*) FROM #ProductPrices WITH (NOLOCK)
        IF @Found = 0
          SET @ErrorLevel = 11
      END

--***  HolidayTypes 20050627
	--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	
              --DELETE FROM #ProductPrices WHERE #ProductPrices.Productnumber NOT IN (SELECT Productnumber FROM Products pr (NOLOCK) Inner JOIN ProductTypes pt (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
--***

-- ERRORLEVEL 16 
   --select * from  #ProductPrices (nolock)
IF @ErrorLevel = 00 -- 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

	--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 (nolock)

--*****

      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.*, OnlineRoomTypeNumber, OnlinePriority FROM #ProductPrices WITH (NOLOCK) INNER JOIN RoomTypes WITH (NOLOCK) ON #ProductPrices.RoomTypeNumber=RoomTypes.RoomTypeNumber 
              AND dbo.DayColorMatches (@AHotelNumber, ProductNumber, @AStartDate, 0)=1 -- @VacantRooms = 0 TODO implement support for auto coloring
            ORDER BY [Description]*/
            SELECT #ProductPrices.*,  RT.OnlineRoomTypeNumber, ISNULL(OnlinePriority,0) AS OnlinePriority, (select [Text] FROM OnlineRoomTypesTranslated WITH (NOLOCK) WHERE OnlineRoomTypeNumber = RT.OnlineRoomTypeNumber AND CountryNumber = @ACountryNumber) AS RomTypeName  FROM #ProductPrices  WITH (NOLOCK) INNER JOIN RoomTypes RT  WITH (NOLOCK) ON #ProductPrices.RoomTypeNumber=RT.RoomTypeNumber   	
              AND dbo.DayColorMatches (@HotelNumberStr, ProductNumber, @AStartDate, 0)=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

GO

