CREATE FUNCTION dbo.IsWareGroupInHotelList(@AWareGroupNumber INT, @AHotelNumberList VARCHAR (100), @Operator TINYINT)
RETURNS BIT AS
--CREATED AV 2007 0430


/*
  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 @AllHotelNumbersRepresentedInDepartments bit
  DECLARE @AtLeastOneHotelRepresentedInDepartments bit
  DECLARE @NoDepartmentRepresentedInHotelList bit
  DECLARE @CurrentHotelRepresentedInDepartments bit
  DECLARE @Idx INT
  DECLARE @Return BIT

  SET @Return = 0

  SET @Idx = 1

  IF @Operator = 0 --AND
  BEGIN

    SET @AllHotelNumbersRepresentedInDepartments = 1 -- initialize
    WHILE (@AHotelNumberList <> '') AND (@AllHotelNumbersRepresentedInDepartments = 1) 
    BEGIN
      SET @CurrentHotelRepresentedInDepartments = 1 -- initialize
      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))  

      IF NOT EXISTS(SELECT WareGroupNumber FROM WareGroupsDepartmentsXTab WITH (NOLOCK) INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXTab.FK_Department
      WHERE WareGroupsDepartmentsXTab.WareGroupNumber = @AWareGroupNumber AND WareGroupsDepartmentsXTab.Type = 1 AND Departments.HotelNumber = @CurrentHotelNumber)
        SET @CurrentHotelRepresentedInDepartments = 0

      IF @CurrentHotelRepresentedInDepartments  = 0
        SET @AllHotelNumbersRepresentedInDepartments = 0
     
    END

    SET @Return = @AllHotelNumbersRepresentedInDepartments

  END
  ELSE IF @Operator = 1 --OR
  BEGIN
    SET @AtLeastOneHotelRepresentedInDepartments = 0 -- initialize
    WHILE (@AHotelNumberList <> '') AND (@AtLeastOneHotelRepresentedInDepartments = 0) 
    BEGIN
      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))  

      IF EXISTS(SELECT WareGroupNumber FROM WareGroupsDepartmentsXTab WITH (NOLOCK) INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXTab.FK_Department
      WHERE WareGroupsDepartmentsXTab.WareGroupNumber = @AWareGroupNumber AND WareGroupsDepartmentsXTab.Type = 1 AND Departments.HotelNumber = @CurrentHotelNumber)
        SET @AtLeastOneHotelRepresentedInDepartments = 1

    END

    SET @Return = @AtLeastOneHotelRepresentedInDepartments
  END

  ELSE IF @Operator = 3 --NOT
  BEGIN
    SET @NoDepartmentRepresentedInHotelList = 1

    WHILE (@AHotelNumberList <> '') AND (@NoDepartmentRepresentedInHotelList = 1) 
    BEGIN
      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))  

      IF  EXISTS(SELECT WareGroupNumber FROM WareGroupsDepartmentsXTab WITH (NOLOCK) INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXTab.FK_Department
      WHERE WareGroupsDepartmentsXTab.WareGroupNumber = @AWareGroupNumber AND WareGroupsDepartmentsXTab.Type = 1 AND Departments.HotelNumber = @CurrentHotelNumber)
        SET @NoDepartmentRepresentedInHotelList = 0

     
    END

    SET @Return = @NoDepartmentRepresentedInHotelList

  END 

  RETURN @Return
END

