CREATE PROCEDURE dbo.OnlineGetProduct @ACustomerRateNumber SMALLINT, @ACustomerNumber INT, @AHotelNumberCSV VARCHAR(100), @ACampaignCodeText VARCHAR(50),
  @AStartDate DATETIME, @AEndDate DATETIME, @AProductNumberCSV VARCHAR(MAX) OUTPUT AS
SET NOCOUNT ON
/*
  Created by: Kent Johansen June 2016
  Description: Returns a list of products allowed online (Logik is copied from SP OnlineCheckAvailability)
*/

DECLARE @ProdNo INT

SET @AProductNumberCSV = ''

IF @ACustomerRateNumber = 0 OR @ACustomerRateNumber = 1 OR @ACustomerRateNumber = 2
BEGIN
  -- Donīt use CustomerRate - Customer Not logged in
  DECLARE #db_cur CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT DISTINCT pro.ProductNumber
    FROM Products pro WITH (NOLOCK)
    INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
    WHERE ISNULL(MarkAsDeleted, 0) = 0
    AND CONVERT(DATETIME, ProductDate) <= CONVERT(CHAR(8), @AStartDate, 112)
    AND ProductEndDate >= CONVERT(CHAR(8), @AEndDate, 112)
    AND ((@ACustomerRateNumber = 0 AND ISNULL(CustomerRate, 0) = 0) OR (@ACustomerRateNumber = 1 AND ISNULL(CustomerRate, 0) IN (0,1)) OR (@ACustomerRateNumber = 2 AND ISNULL(CustomerRate, 0) = 1))
END
ELSE IF @ACustomerRateNumber = 3
BEGIN
  -- product from inernet on customer (1 product)
  DECLARE #db_cur CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT DISTINCT Products.ProductNumber
	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 iter_intlist_to_table(@AHotelNumberCSV))
    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 ISNULL(Products.MarkAsDeleted, 0) = 0
    AND CONVERT(DATETIME, ProductDate) <= CONVERT(CHAR(8), @AStartDate, 112)
    AND ProductEndDate >= CONVERT(CHAR(8), @AEndDate, 112)
END
ELSE IF @ACustomerRateNumber = 4
BEGIN
  -- CampaignCodeProducts
  DECLARE #db_cur CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT DISTINCT Products.ProductNumber
	FROM Products WITH (NOLOCK)
    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      
    WHERE CampaignCodes.Text = @ACampaignCodeText
    AND CampaignCodesProducts.Enabled = 1 
    AND CampaignCodesHotel.EnableAssociatedProducts = 1
    AND ISNULL(Products.MarkAsDeleted, 0) = 0
    AND CONVERT(DATETIME, ProductDate) <= CONVERT(CHAR(8), @AStartDate, 112)
    AND ProductEndDate >= CONVERT(CHAR(8), @AEndDate, 112)
END

OPEN #db_cur

FETCH NEXT FROM #db_cur INTO @ProdNo

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @AProductNumberCSV <> ''
    SET @AProductNumberCSV = @AProductNumberCSV + ','
  SET @AProductNumberCSV = @AProductNumberCSV + CAST(@ProdNo AS VARCHAR)
  FETCH NEXT FROM #db_cur INTO @ProdNo
END

CLOSE #db_cur
DEALLOCATE #db_cur