
CREATE PROCEDURE [dbo].[OnlineHolidayTypes] @ACustomerLogin BIT = 0
  , @CustomerNumber INT = 0
  , @ARoomTypeResourceNumber SMALLINT = 0  
  AS
SET NOCOUNT ON
/*
    Created by Troels Buch Jun 2005
    
    Changes: 	
	 LJE20120224: Missing 'pt.RoomTypeResourceNumber' column added for population of #AvailCur - when @ACustomerLogin=1.
	 TB20100923: Added Check for product expire -AND CONVERT(DATETIME, ProductDate)<=@DateNow AND ProductEndDate>=@DateNow 	
     TB20100604: Added ProductSubtypeNumber
     TB20090707: Added SortNumber
     TB20081202: Changed varchar50 to varchar100
     TB20081105: Added @ARoomTypeResourceNumber - ONLY 4.2 +
     TB20070403: Added check for dayproduct
     TB20070228 - Added @ACustomerLogin
     TB20050816: Added ProducttypeDescription
     TB20050627:  Added ProductTypeNumber to Select

    NOTES: 	- 
*/

-- NEW

--BEGIN TRAN
DECLARE
@Productnumber  INT,
@ProductTypeNumber INT,
@ProductName VARCHAR (50) 
DECLARE @RowNumber  INT
DECLARE @HolidayType  VARCHAR (50)
DECLARE @Description  VARCHAR (100)
DECLARE @productTypeDescription VARCHAR (100)
DECLARE @stayMinDays INT 
DECLARE @stayMaxDays INT
DECLARE @DateNow DATETIME
SET @DateNow = CONVERT(DATETIME, GETDATE())

  CREATE TABLE #HolidayTypesTmp (		
		[Productnumber] [INT] NULL,
		[ProductTypeNumber] [INT] NULL,
		[ProductName] [VARCHAR] (50) NULL,
		[RowNumber] [INT]  NULL,
		[HolidayType] [VARCHAR] (50) NULL,
		[Description] [VARCHAR] (100) NULL,
		[ProducttypeDescription] [VARCHAR] (100) NULL,
		[StayMinDays] [INT] NULL,
		[StayMaxDays] [INT] NULL,
		[SortNumber] [INT] NULL	
		)

/*SELECT  DISTINCT  pr.producttypenumber,  [Text] AS HolidayType, 
(SELECT Count(*) AS RowNumber FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  WHERE AllowFilterpicassoOnline = 1 ) AS RowNumber , Productnumber, pr.Description, productname
FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
WHERE AllowFilterpicassoOnline = 1*/

IF @CustomerNumber = 0
BEGIN

IF @ACustomerLogin = 0
BEGIN
   DECLARE #AvailCur CURSOR LOCAL FAST_FORWARD FOR

SELECT  DISTINCT  pr.producttypenumber
  , [Text] AS HolidayType
  , (SELECT Count(*) AS RowNumber FROM Products pr WITH (NOLOCK) 
	INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	WHERE AllowFilterpicassoOnline = 1 AND dayproduct = 0) AS RowNumber 
  , Productnumber
  , pr.Description
  , productname
  , pt.Description AS productTypeDescription
  , pr.MiniMumDays
  , pr.MaximumDays
  , pt.RoomTypeResourceNumber
   FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
   WHERE AllowFilterpicassoOnline = 1 AND IsNULL(pr.customerrate, 0) = 0 AND dayproduct = 0 AND IsNULL(RoomTypeResourceNumber, 0) = @ARoomTypeResourceNumber
   AND CONVERT(DATETIME, ProductDate)<=@DateNow AND ProductEndDate>=@DateNow 
   AND IsNull(pr.ProductSubtypeNumber, 0) = 0

/*
   SELECT  DISTINCT  pr.producttypenumber,  [Text] AS HolidayType, 
   (SELECT Count(*) AS RowNumber FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  WHERE AllowFilterpicassoOnline = 1 AND dayproduct = 0) AS RowNumber , Productnumber, pr.Description, productname,  pt.Description AS productTypeDescription,
   pr.MiniMumDays, pr.MaximumDays
   FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
   WHERE AllowFilterpicassoOnline = 1 AND IsNULL(pr.customerrate, 0) = 0 AND dayproduct = 0
*/
END
ELSE
BEGIN
   DECLARE #AvailCur CURSOR LOCAL FAST_FORWARD FOR

   SELECT  DISTINCT  pr.producttypenumber
   , [Text] AS HolidayType
   , (SELECT Count(*) AS RowNumber FROM Products pr WITH (NOLOCK) 
     INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	 WHERE AllowFilterpicassoOnline = 1 AND dayproduct = 0) AS RowNumber 
   , Productnumber
   , pr.Description
   , productname
   , pt.Description AS productTypeDescription
   , pr.MiniMumDays
   , pr.MaximumDays
   , pt.RoomTypeResourceNumber
   FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
   WHERE AllowFilterpicassoOnline = 1 AND IsNULL(pr.customerrate, 0) = 1 AND dayproduct = 0
   AND CONVERT(DATETIME, ProductDate)<=@DateNow AND ProductEndDate>=@DateNow 
   AND IsNull(pr.ProductSubtypeNumber, 0) = 0
END

END
ELSE
BEGIN
   DECLARE #AvailCur CURSOR LOCAL FAST_FORWARD FOR

SELECT  DISTINCT  Products.producttypenumber,  [Text] AS HolidayType, 
   (SELECT Count(*) AS RowNumber FROM Products WITH (NOLOCK) 
   INNER JOIN ProductTypes WITH (NOLOCK) ON Products.ProductTypeNumber = ProductTypes.ProductTypeNumber
   WHERE AllowFilterpicassoOnline = 1 AND dayproduct = 0) AS RowNumber , Productnumber, Products.Description, productname,  ProductTypes.Description AS productTypeDescription,
   Products.MiniMumDays, Products.MaximumDays, ProductTypes.RoomTypeResourceNumber
   FROM Products Products WITH (NOLOCK) 
INNER JOIN ProductTypes ProductTypes WITH (NOLOCK) ON Products.ProductTypeNumber = ProductTypes.ProductTypeNumber 
INNER JOIN CustomerFieldsPerHotel 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 Customer WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
   WHERE /*AllowFilterpicassoOnline = 1 AND*/ dayproduct = 0 AND IsNULL(RoomTypeResourceNumber, 0) = @ARoomTypeResourceNumber AND CUSTOMER.customernumber = @CustomerNumber
END
/*
SELECT    DISTINCT  [Text] , 
(SELECT Count(*) AS RowNumber FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  WHERE AllowFilterpicassoOnline = 1 ) AS RowNumber 
FROM Products pr (NOLOCK) INNER JOIN ProductTypes pt (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
WHERE AllowFilterpicassoOnline = 1
*/
DECLARE @Number INT
--DECLARE @FirstRun  BIT
--SET @FirstRun = 1
--DECLARE @TmpMinDays INT
--DECLARE @TmpMaxDays INT
--SET @TmpMinDays = 0
--SET @TmpMaxDays = 0

OPEN #AvailCur
----
FETCH NEXT FROM #AvailCur
INTO @ProductTypeNumber, @HolidayType, @RowNumber, @Productnumber, @Description, @ProductName, @productTypeDescription, @stayMinDays, @stayMaxDays, @ARoomTypeResourceNumber
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  --print @ProductTypeNumber
    SELECT @Number = COUNT(*) FROM #HolidayTypesTmp WITH (NOLOCK) WHERE ProductTypeNumber = @ProductTypeNumber
  --print @Number       
    IF @Number < 1
     INSERT INTO #HolidayTypesTmp(ProductTypeNumber, HolidayType, RowNumber, Productnumber, [Description], ProductName, ProducttypeDescription, StayMinDays, StayMaxDays ) VALUES (@ProductTypeNumber, @HolidayType, @RowNumber, @Productnumber, @Description, @ProductName, @productTypeDescription, @stayMinDays, @stayMaxDays)
    
    FETCH NEXT FROM #AvailCur
    INTO @ProductTypeNumber, @HolidayType, @RowNumber, @Productnumber, @Description, @ProductName, @productTypeDescription, @stayMinDays, @stayMaxDays, @ARoomTypeResourceNumber
 END
----
CLOSE #AvailCur
DEALLOCATE #AvailCur

/*
DECLARE @StayMinDaysProductumber INT
DECLARE @StayMaxDaysProductnumber  INT

SELECT TOP 1 @StayMinDays = StayMinDays  FROM  #HolidayTypesTmp (NOLOCK)  ORDER BY  StayMinDays
SELECT TOP 1 @StayMaxDays = StayMaxDays  FROM  #HolidayTypesTmp (NOLOCK)  ORDER BY  StayMaxDays DESC

		[Productnumber] [INT] NULL,
		[ProductTypeNumber] [INT] NULL,
		[ProductName] [VARCHAR] (50) NULL,
		[RowNumber] [INT]  NULL,
		[HolidayType] [VARCHAR] (50) NULL,
		[Description] [VARCHAR] (100) NULL,
		[ProducttypeDescription] [VARCHAR] (50) NULL,
		[StayMinDays] [INT] NULL,
		[StayMaxDays] [INT] NULL
*/
--SELECT * FROM #HolidayTypesTmp (NOLOCK) WHERE ProductTypeNumber = 1

DECLARE @StMin INT
DECLARE @StMax INT
DECLARE @NumTmp INT

IF @CustomerNumber = 0
BEGIN
print 'TB @CustomerNumber = 0'
DECLARE #HolidayTypesTmpCur CURSOR LOCAL FAST_FORWARD FOR
SELECT Productnumber, ProductTypeNumber, ProductName, RowNumber, HolidayType, [Description], ProducttypeDescription, stayMinDays, stayMaxDays  FROM #HolidayTypesTmp WITH (NOLOCK) 

OPEN #HolidayTypesTmpCur
FETCH NEXT FROM #HolidayTypesTmpCur
INTO @Productnumber, @ProductTypeNumber, @ProductName, @RowNumber, @HolidayType, @Description, @ProducttypeDescription, @stayMinDays, @stayMaxDays

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	-- Minimum days stay
	SELECT @NumTmp = COUNT( DISTINCT(MinimumDays))
	FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	WHERE AllowFilterpicassoOnline = 1 AND pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0

	IF @NumTmp = 1
	BEGIN
	
	   SELECT TOP 1 @StMin = MinimumDays
	   FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	   WHERE AllowFilterpicassoOnline = 1 AND pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 ORDER BY MinimumDays

	   UPDATE #HolidayTypesTmp SET  StayMinDays = @StMin WHERE ProductTypeNumber = @ProductTypeNumber
	END
	ELSE
	BEGIN
	   UPDATE #HolidayTypesTmp SET  StayMinDays = 0 WHERE ProductTypeNumber = @ProductTypeNumber   
	END

	-- Maximum days stay
	SELECT @NumTmp = COUNT( DISTINCT(MaximumDays))
	FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	WHERE AllowFilterpicassoOnline = 1 AND pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0

	IF @NumTmp = 1
	BEGIN
	   SELECT TOP 1 @StMax = MaximumDays
	   FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	   WHERE AllowFilterpicassoOnline = 1 AND pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 ORDER BY MaximumDays DESC

	   UPDATE #HolidayTypesTmp SET  StayMaxDays = @StMax WHERE  ProductTypeNumber = @ProductTypeNumber
	END
	ELSE
	BEGIN
	    UPDATE #HolidayTypesTmp SET  StayMaxDays = 0 WHERE  ProductTypeNumber = @ProductTypeNumber
	END

	-- Update SortNumber
	UPDATE #HolidayTypesTmp SET  SortNumber = (SELECT SortNumber FROM Producttypes WITH (nolock) WHERE productTypenumber = @ProductTypeNumber)  WHERE  ProductTypeNumber = @ProductTypeNumber

	print 'TB'
	FETCH NEXT FROM #HolidayTypesTmpCur
             INTO @Productnumber, @ProductTypeNumber, @ProductName, @RowNumber, @HolidayType, @Description, @ProducttypeDescription, @stayMinDays, @stayMaxDays
    END

END
ELSE
BEGIN -- IF @CustomerNumber > 0
--
print 'TB @CustomerNumber > 0'
DECLARE #HolidayTypesTmpCur CURSOR LOCAL FAST_FORWARD FOR
SELECT Productnumber, ProductTypeNumber, ProductName, RowNumber, HolidayType, [Description], ProducttypeDescription, stayMinDays, stayMaxDays  FROM #HolidayTypesTmp WITH (NOLOCK) 

OPEN #HolidayTypesTmpCur
FETCH NEXT FROM #HolidayTypesTmpCur
INTO @Productnumber, @ProductTypeNumber, @ProductName, @RowNumber, @HolidayType, @Description, @ProducttypeDescription, @stayMinDays, @stayMaxDays

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	-- Minimum days stay
	SELECT @NumTmp = COUNT( DISTINCT(MinimumDays))
	FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
	OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
	INNER JOIN Customer WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
	WHERE pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 AND CUSTOMER.CustomerNumber = @CustomerNumber

	IF @NumTmp = 1
	BEGIN
	
	  SELECT TOP 1 @StMin = MinimumDays
                FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber
	  INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
	  INNER JOIN Customer WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
	  WHERE pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 AND CUSTOMER.CustomerNumber = @CustomerNumber ORDER BY MinimumDays

	   UPDATE #HolidayTypesTmp SET  StayMinDays = @StMin WHERE ProductTypeNumber = @ProductTypeNumber
	END
	ELSE
	BEGIN
	   UPDATE #HolidayTypesTmp SET  StayMinDays = 0 WHERE ProductTypeNumber = @ProductTypeNumber   
	END

	-- Maximum days stay
	SELECT @NumTmp = COUNT( DISTINCT(MaximumDays))
	FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
	  OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
	  INNER JOIN Customer WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
	WHERE pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 AND CUSTOMER.CustomerNumber = @CustomerNumber

	IF @NumTmp = 1
	BEGIN
	    SELECT TOP 1 @StMax = MaximumDays
	    FROM Products pr WITH (NOLOCK) INNER JOIN ProductTypes pt WITH (NOLOCK) ON pr.ProductTypeNumber = pt.ProductTypeNumber  
	    INNER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber6
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber1
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber2
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber3
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber4
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber5
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber7
	    OR Pr.ProductNumber = CustomerFieldsPerHotel.OvernightProductNumber8
	    INNER JOIN Customer WITH (NOLOCK) ON CUSTOMER.CustomerNumber = CustomerFieldsPerHotel.CustomerNumber 
	    WHERE pr.ProductTypeNumber=@ProductTypeNumber AND dayproduct = 0 AND CUSTOMER.CustomerNumber = @CustomerNumber ORDER BY MaximumDays DESC

	   UPDATE #HolidayTypesTmp SET  StayMaxDays = @StMax WHERE  ProductTypeNumber = @ProductTypeNumber
	END
	ELSE
	BEGIN
	    UPDATE #HolidayTypesTmp SET  StayMaxDays = 0 WHERE  ProductTypeNumber = @ProductTypeNumber
	END
	
	FETCH NEXT FROM #HolidayTypesTmpCur
             INTO @Productnumber, @ProductTypeNumber, @ProductName, @RowNumber, @HolidayType, @Description, @ProducttypeDescription, @stayMinDays, @stayMaxDays
    END


--
END

CLOSE #HolidayTypesTmpCur
DEALLOCATE #HolidayTypesTmpCur
   

SELECT * FROM #HolidayTypesTmp WITH (NOLOCK) ORDER BY SortNumber
DROP TABLE #HolidayTypesTmp
GO

