CREATE PROCEDURE [dbo].[GetPaxOnProductAvailability] @AStartDatetime datetime, @AEndDatetime datetime, @AHotelNumbers VARCHAR(50) AS
BEGIN
    -- 20100507/RN: Added OnlinelowAvailability use.
    -- 20100416/RN: Added ProductTypeNumber, ProductSubtypeNumber and PensionTypeNumber

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- SET NOCOUNT ON;
	
	DECLARE @FromDate DATETIME
	DECLARE @ToDate DATETIME
	DECLARE @NumberOfDays INT
	DECLARE @WeekNumber INT
	DECLARE @PicassoFirstWeekDay INT
	
	DECLARE @ProductNumber INT
	DECLARE @ProductName VARCHAR(50)
	DECLARE @ProductDescription VARCHAR(128)
	DECLARE @OnlinelowAvailability INT
	DECLARE @ProductTypeNumber INT
	DECLARE @ProductSubtypeNumber INT
	DECLARE @PensionTypeNumber INT
	DECLARE @AvailablePax INT
	DECLARE @UsedRooms INT
	DECLARE @MaxPax INT
	DECLARE @MaxRooms INT
	
	CREATE TABLE #Temptable (
		ProductNumber INT,
		ProductName VARCHAR(50),
		ProductDescription VARCHAR(128),
		ArrivalDate DATETIME,
		DepartureDate DATETIME,
		AvailablePax INT,
		UsedRooms INT,
		MaxPax INT,
		MaxRooms INT,
		WeekNumber INT,
		ProductTypeNumber INT,
		ProductSubTypeNumber INT,
		PensionTypeNumber INT,
		OnlinelowAvailability INT
	)
	
	IF EXISTS(SELECT KeyValue FROM HOTELSETUP WHERE SEARCHKEY = 'FirstWeekDay')
	  SELECT @PicassoFirstWeekDay = KeyValue FROM HOTELSETUP WHERE SEARCHKEY = 'FirstWeekDay'
	ELSE
	  SELECT @PicassoFirstWeekDay = 7

	-- Get the products we should count availability for:
	DECLARE #ProductCursor CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT DISTINCT Products.ProductNumber, ProductName, Products.Description, ProductTypeNumber, ProductSubtypeNumber, PensionTypeNumber, OnlinelowAvailability FROM Products
    INNER JOIN ProductPrice ON Products.ProductNumber = ProductPrice.ProductNumber
	WHERE ISNULL(MarkAsDeleted,0) = 0
      AND (ISNULL(UseMaxPax,0) = 1 OR ISNULL(UseMaxRooms,0) = 1)
      AND ProductPrice.DateStamp BETWEEN CONVERT(CHAR(8),@AStartDatetime,112) AND CONVERT(CHAR(8),@AEndDatetime,112)
    
    OPEN #ProductCursor
    
    FETCH NEXT FROM #ProductCursor
    INTO @ProductNumber, @ProductName, @ProductDescription, @ProductTypeNumber, @ProductSubtypeNumber, @PensionTypeNumber, @OnlinelowAvailability
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
	  -- For each day in the period we should get the availability by
	  -- calling GetUsedPaxOnProduct.
	  -- Afterwards we need to calculate the weeknumber from the date
	  -- and assign the free pax to the weeknumber if it is less than
	  -- the current assigned free pax.
	  
  	  SELECT @NumberOfDays = DATEDIFF(day,@AStartDatetime,DATEADD(dd,-1,@AEndDatetime))
	  SET @FromDate = @AStartDatetime
	  SET @ToDate = @FromDate + 1
	  
	  IF @PicassoFirstWeekDay = 7
	    SET @WeekNumber = dbo.GetISOWeekNumberFromDate(@FromDate + 1)
	  ELSE
	    SET @WeekNumber = dbo.GetISOWeekNumberFromDate(@FromDate)
	  
	  WHILE (@NumberOfDays >= 0)
	  BEGIN
		EXEC GetUsedPaxOnProduct @ProductNumber, @FromDate, @ToDate, @AHotelNumbers, -1, @AvailablePax OUTPUT, @UsedRooms OUTPUT, @MaxPax OUTPUT, @MaxRooms OUTPUT
		
		INSERT INTO #Temptable (
 		  ProductNumber,
		  ProductName,
		  ProductDescription,
		  ArrivalDate,
		  DepartureDate,
		  AvailablePax,
		  UsedRooms,
		  MaxPax,
		  MaxRooms,
		  WeekNumber,
          ProductTypeNumber,
          ProductSubTypeNumber,
          PensionTypeNumber,
          OnlinelowAvailability
		) VALUES (
		  @ProductNumber,
		  @ProductName,
		  @ProductDescription,
		  @FromDate,
		  @ToDate,
		  @AvailablePax,
		  @UsedRooms,
		  @MaxPax,
		  @MaxRooms,
		  @WeekNumber,
          @ProductTypeNumber,
          @ProductSubTypeNumber,
          @PensionTypeNumber,
          @OnlinelowAvailability
		)
		
        SET @FromDate = @FromDate + 1
        SET @ToDate = @FromDate + 1
        SET @NumberOfDays = (@NumberOfDays -1)
        
        IF @PicassoFirstWeekDay = 7
	      SET @WeekNumber = dbo.GetISOWeekNumberFromDate(@FromDate + 1)
	    ELSE
	      SET @WeekNumber = dbo.GetISOWeekNumberFromDate(@FromDate)
	  END
	  
      FETCH NEXT FROM #ProductCursor
      INTO @ProductNumber, @ProductName, @ProductDescription, @ProductTypeNumber, @ProductSubtypeNumber, @PensionTypeNumber, @OnlinelowAvailability
    END  
    
    CLOSE #ProductCursor
    DEALLOCATE #ProductCursor 

	SELECT * FROM #Temptable WITH (NOLOCK) ORDER BY ArrivalDate, DepartureDate, WeekNumber, ProductNumber
END
