CREATE FUNCTION [dbo].[AccountHotelNumber](@AAccountNumber INT, @AHotelNumberList VARCHAR (100), @Operator TINYINT) RETURNS BIT AS

/*
    Created by Kent Johansen March 2011
    Changes:
    
*/

BEGIN
  DECLARE @Return TINYINT
  DECLARE @TempHotelNumberList VARCHAR (100)
  DECLARE @CurrentHotelNumber INT
  DECLARE @AllHotelNumbersRepresentedInAccount BIT
  DECLARE @HotelFound BIT

  SET @Return = 0

  IF @Operator = 0  -- bfAND
  BEGIN
    SET @AllHotelNumbersRepresentedInAccount = 1 -- initialize
    WHILE (@AHotelNumberList <> '') AND (@AllHotelNumbersRepresentedInAccount = 1) 
    BEGIN
      SET @CurrentHotelNumber = DBO.GetNextToken(@AhotelNumberList, ',')
      IF @CurrentHotelNumber < 10
        SET @AHotelNumberList = SUBSTRING(@AHotelNumberList, 3, Len(@AhotelNumberList))
      ELSE
        SET @AHotelNumberList = SUBSTRING(@AHotelNumberList, 4, Len(@AhotelNumberList))  

      IF NOT EXISTS(
        SELECT DISTINCT adx.AccountNumber 
        FROM AccountDepartmentXtab adx WITH (NOLOCK)
        INNER JOIN Departments D WITH (NOLOCK) ON D.PK_Department = adx.FK_Department 
          AND D.HotelNumber = @CurrentHotelNumber 
        WHERE adx.AccountNumber = @AAccountNumber
      )
      SET @AllHotelNumbersRepresentedInAccount = 0
      
    END
    SET @Return = @AllHotelNumbersRepresentedInAccount 

  END
  ELSE IF @Operator = 1 -- bfOR
  BEGIN
    SET @HotelFound = 0
    
    WHILE (@AHotelNumberList <> '') AND (@HotelFound = 0) 
    BEGIN
      SET @CurrentHotelNumber = DBO.GetNextToken(@AhotelNumberList, ',')
      IF @CurrentHotelNumber < 10
        SET @AHotelNumberList = SUBSTRING (@AHotelNumberList, 3, Len(@AhotelNumberList))
      ELSE
        SET @AHotelNumberList = SUBSTRING (@AHotelNumberList, 4, Len(@AhotelNumberList))  

      IF EXISTS (
        SELECT DISTINCT adx.AccountNumber 
        FROM AccountDepartmentXtab adx WITH (NOLOCK)
        INNER JOIN Departments D WITH (NOLOCK) ON D.PK_Department = adx.FK_Department 
          AND D.HotelNumber = @CurrentHotelNumber 
        WHERE adx.AccountNumber = @AAccountNumber
      )
        SET @HotelFound = 1
    END

    SET @Return = @HotelFound
  END
  ELSE IF @Operator = 3 -- bfNOT
  BEGIN
    SET @HotelFound = 0
    
    WHILE (@AHotelNumberList <> '') AND (@HotelFound = 0) 
    BEGIN
      SET @CurrentHotelNumber = DBO.GetNextToken(@AhotelNumberList, ',')
      IF @CurrentHotelNumber < 10
        SET @AHotelNumberList = SUBSTRING (@AHotelNumberList, 3, Len(@AhotelNumberList))
      ELSE
        SET @AHotelNumberList = SUBSTRING (@AHotelNumberList, 4, Len(@AhotelNumberList))  

      IF EXISTS (
        SELECT DISTINCT adx.AccountNumber 
        FROM AccountDepartmentXtab adx WITH (NOLOCK)
        INNER JOIN Departments D WITH (NOLOCK) ON D.PK_Department = adx.FK_Department 
          AND D.HotelNumber = @CurrentHotelNumber 
        WHERE adx.AccountNumber = @AAccountNumber
      )
        SET @HotelFound = 1
    END

    SET @Return = ~@HotelFound
  END

  RETURN @Return
END