CREATE FUNCTION dbo.SubTypesMatch(@ARoomNumber INTEGER, @ASubTypeList VARCHAR (100), @Operator TINYINT)
RETURNS BIT AS
/*
    Created by Jacob Pedersen 2006-07-17

    Last Changed:
    N/A

    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 BIT,
  @SubTypeNumber INT

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

  IF @Operator = 0  -- bfAND
  BEGIN
    SET @ASubTypeList = REPLACE(@ASubTypeList, ',', ',,') -- ,,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 (',', @ASubTypeList) = 0) -- OR and only one subtype, then we can make some faster SQL
  BEGIN
    DECLARE SubTypeCur CURSOR FORWARD_ONLY FOR

    SELECT DISTINCT SubTypeNumber FROM RoomRoomSubTypeXTab WITH (NOLOCK) WHERE RoomNumber=@ARoomNumber AND SubTypeNumber=@ASubTypeList
  END
  ELSE
  BEGIN
    DECLARE SubTypeCur CURSOR FORWARD_ONLY FOR

    SELECT DISTINCT SubTypeNumber FROM RoomRoomSubTypeXTab WITH (NOLOCK) WHERE RoomNumber=@ARoomNumber
  END

  OPEN SubTypeCur
  FETCH NEXT FROM SubTypeCur INTO @SubTypeNumber

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

    FETCH NEXT FROM SubTypeCur INTO @SubTypeNumber
  END

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

  CLOSE SubTypeCur
  DEALLOCATE SubTypeCur

  RETURN @Return
END

