CREATE FUNCTION dbo.ProductHotelNumber(@AProductNumber INTEGER, @AHotelNumberList VARCHAR (100), @Operator TINYINT)
RETURNS BIT AS

/*
    Created by Jacob Pedersen 2005-07-05

    Last Changed:
    20110822 BA: CR026410/Everything runs smoothly outside this function. But inside it runs slow. 
                 Optimizing SELECT Statement by removing three UNION's. 
    20070622 AV: I'm stupid! I forgot to rewrite department stuff in this function
    20060412 JP: OPTIMIZE: Possibly faster in some cases when OR and only one hotel. Unknown if it has any effect

    TBooleanFunction = (bfAND, bfOR, bfXOR, bfNOT, bfNAND, bfNOR, bfXNOR);  // http://searchsmb.techtarget.com/sDefinition/0,,sid44_gci213512,00.html

    bfAND - Products which belong to ALL specified hotels
    bfOR - Products which belong to ANY of the specified hotels
    bfNOT - Products which does NOT belong to any of hotel specified hotels

*/
BEGIN
  DECLARE
  @Return TINYINT,
  @HotelNumber INTEGER

  SET @AHotelNumberList = ','+@AHotelNumberList+',' -- ,0,1,

  IF @Operator = 0  -- bfAND
  BEGIN
    SET @AHotelNumberList = REPLACE(@AHotelNumberList, ',', ',,') -- ,,0,,1,,    -> ,,1,,   ->   ,,
    SET @Return = 0  -- Assume failure
  END
  ELSE IF @Operator = 1  -- bfOR
    SET @Return = 0  -- Assume failure
  ELSE IF @Operator = 3  -- bfNOT
    SET @Return = 1  -- Assume success

/*
  IF (@Operator = 1) AND (CHARINDEX (',', @AHotelNumberList) = 0) -- OR and only one Hotel, then we can make some faster SQL
  BEGIN
    SET @HotelNumber = CONVERT(INT, @AHotelNumberList)

    DECLARE ProductCur CURSOR FORWARD_ONLY FOR

    -- Check RoomTypes
    SELECT DISTINCT TOP 1 @HotelNumber FROM ProductPrice (NOLOCK) WHERE RoomTypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes(NOLOCK) WHERE HotelNumber=@HotelNumber)
    UNION
    -- Check Packets
    SELECT DISTINCT TOP 1 @HotelNumber FROM PacketItem_New (NOLOCK)
    INNER JOIN Ware(NOLOCK) ON Ware.WareNumber=PacketItem_New.WareNumber AND PacketItem_New.PacketNumber IN (
    SELECT DISTINCT ArrivalPacketNumber FROM ProductPrice(NOLOCK) WHERE ProductNumber=@AProductNumber
    UNION
    SELECT DISTINCT StayPacketNumber FROM ProductPrice(NOLOCK) WHERE ProductNumber=@AProductNumber
    UNION
    SELECT DISTINCT DeparturePacketNumber FROM ProductPrice(NOLOCK) WHERE ProductNumber=@AProductNumber
    )
    INNER JOIN Departments (NOLOCK) ON Departments.HotelNumber=@HotelNumber
  END
  ELSE
*/
  BEGIN
    DECLARE ProductCur CURSOR FORWARD_ONLY FOR

    -- Check RoomTypes
    SELECT DISTINCT C.HotelNumber FROM Products P WITH (NOLOCK) 
	INNER JOIN ProductHotelXTAB PHX WITH (NOLOCK) ON P.ProductNumber=@AProductNumber AND P.ProductNumber=PHX.ProductNumber
	INNER JOIN Customer C ON PHX.HotelCustomerNumber = C.CustomerNumber
  END

  OPEN ProductCur
  FETCH NEXT FROM ProductCur INTO @HotelNumber

  WHILE (@@Fetch_Status <> -1) AND (@AHotelNumberList <> ',,')
  BEGIN
    IF @Operator = 0 -- bfAND -- all hotels must be present in specified list
      SET @AHotelNumberList = REPLACE(@AHotelNumberList, ',' + CONVERT(VARCHAR (3), @HotelNumber) + ',', '')  -- remove the hotels we find in the dataset from the specified hotel list
    ELSE IF (@Operator = 1) AND (CHARINDEX (',' + CONVERT(VARCHAR (3), @HotelNumber) + ',', @AHotelNumberList) > 0) -- bfOR -- if the hotel is in the list then success
    BEGIN
      SET @Return = 1
      BREAK -- WHILE
    END
    ELSE IF (@Operator = 3) AND (CHARINDEX (',' + CONVERT(VARCHAR (3), @HotelNumber) + ',', @AHotelNumberList) > 0) -- bfNOT -- if the hotel is in the list then failure
    BEGIN
      SET @Return = 0
      BREAK -- WHILE
    END

    FETCH NEXT FROM ProductCur INTO @HotelNumber
  END

  IF @Operator = 0 AND @AHotelNumberList = ',,'  -- bfAND
    SET @Return = 1

  CLOSE ProductCur
  DEALLOCATE ProductCur

  RETURN @Return
END

