CREATE FUNCTION dbo.IsPKDepartmentInHotelList(@ADepartmentString VARCHAR (1000), @AHotelNumberList VARCHAR (100), @Operator TINYINT)
RETURNS BIT AS
--CREATED AV 2007 0330

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

@Operater = 0 : AND
@Operator = 1: OR
@Operator = 2 : NOT
*/
BEGIN

  DECLARE @HotelNumber INT
  DECLARE @CurrentPKDepartment INT
  DECLARE @CurrentHotelNumber int
  DECLARE @AllHotelNumbersRepresentedInDepartmentString bit
  DECLARE @CurrentHotelRepresentedInDepartmentString bit
  DECLARE @Idx INT
  DECLARE @Return BIT

  SET @Return = 0

  SET @Idx = 1

  IF @Operator = 0 --AND
  BEGIN

    SET @AllHotelNumbersRepresentedInDepartmentString = 1 -- initialize
    WHILE (@AHotelNumberList <> '') AND (@AllHotelNumbersRepresentedInDepartmentString = 1) 
    BEGIN
      SET @CurrentHotelRepresentedInDepartmentString = 0 -- initialize
--      EXEC ResolveNextToken @AHotelNumberList , ',', @CurrentHotelNumber,@AhotelNumberList
      SET @CurrentHotelNumber = DBO.GetNextToken (@AhotelNumberList,',') --We cant not call stored procedure from function so we have to eat the ahotelnumberlist string ourself
      IF @CurrentHotelNumber < 10
        SET  @AHotelNumberList = SUBSTRING (@AHotelNumberList,3,Len(@AhotelNumberList))
      ELSE
        SET  @AHotelNumberList = SUBSTRING (@AHotelNumberList,4,Len(@AhotelNumberList))  
      
      WHILE @Idx < = LEN(@ADepartmentString) 
      BEGIN
        SET @CurrentPKDepartment  = CONVERT(INT,SUBSTRING(@ADepartmentString, @Idx, 1))
        SELECT @HotelNumber = HotelNumber FROM Departments WITH (NOLOCK) WHERE PK_Department = @CurrentPKDepartment

        IF @HotelNumber = @CurrentHotelNumber
        BEGIN
          SET @CurrentHotelRepresentedInDepartmentString = 1
          BREAK
        END

        SET @Idx = @Idx + 1
      END
      IF @CurrentHotelRepresentedInDepartmentString  = 0
        SET @AllHotelNumbersRepresentedInDepartmentString = 0
      SET @Idx = 1
    END

    SET @Return = @AllHotelNumbersRepresentedInDepartmentString 

  END
  ELSE IF @Operator = 1 --OR
  BEGIN
    SET @AHotelNumberList = ','  +@AHotelNumberList + ','
    WHILE @Idx < = LEN(@ADepartmentString) 
    BEGIN
      SET @CurrentPKDepartment  = CONVERT(INT,SUBSTRING( @ADepartmentString, @Idx, 1))
      SELECT @HotelNumber = HotelNumber FROM Departments WITH (NOLOCK) WHERE PK_Department = @CurrentPKDepartment

      IF CHARINDEX (',' + CONVERT(VARCHAR (3),@HotelNumber) + ',', @AHotelNumberList) > 0
      BEGIN
        SET @Return = 1
        BREAK       
      END

      SET @Idx = @Idx + 1
    END
  END

  ELSE IF @Operator = 3 --NOT
  BEGIN
    SET @AHotelNumberList = ','  +@AHotelNumberList + ','
    SET @Return = 1
    WHILE @Idx < = LEN(@ADepartmentString) 
    BEGIN
      SET @CurrentPKDepartment  = CONVERT(INT, SUBSTRING( @ADepartmentString, @Idx, 1))
      SELECT @HotelNumber = HotelNumber FROM Departments WITH (NOLOCK) WHERE PK_Department = @CurrentPKDepartment

      IF CHARINDEX (',' + CONVERT(VARCHAR (3),@HotelNumber) + ',', @AHotelNumberList) > 0
      BEGIN
        SET @Return = 0
        BREAK       
      END

      SET @Idx = @Idx + 1
    END
  END 

  RETURN @Return
END

