CREATE FUNCTION dbo.PacketHotelNumber(@APacketNumber INTEGER, @AHotelNumberList VARCHAR (100), @Operator TINYINT)
/*
    Created by Anders Vindal 2005-07-27
    Last Changed RN 2012 0119 if there is no packetitems always return 1, CR038916
    Last Changed AV 2008 0515 new block inserted, bfEXact
    Last Changed AV 2007 0401 use the new departmentxtab
    Last Changed:AV 2005 1102 dont look on ware.cashierdepartment but on packetitemnew.departmentserviceoverwrite

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

    bfAND - Get the Packets which belong to ALL specified hotels
    bfOR - Get Packets which belong to ANY of the specified hotels
    bfNOT - Get Packets which do NOT belong to any of hotel specified hotels

*/

RETURNS BIT
AS
BEGIN
  DECLARE
  @Return TINYINT,
  @TempHotelNumberList VARCHAR (100),
  @CurrentHotelNumber INTEGER,
  @AllHotelNumbersRepresentedInPacket BIT,
  @HotelFound BIT,
  @PacketItemCount INT

  SET @Return = 0

  IF @Operator = 0  -- bfAND
  BEGIN

    SET @AllHotelNumbersRepresentedInPacket = 1 -- initialize
    WHILE (@AHotelNumberList <> '') AND (@AllHotelNumbersRepresentedInPacket = 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 NOT EXISTS(SELECT DISTINCT PacketItem_NewDepartmentXTab.PacketItemNumber FROM PacketItem_NewDepartmentXTab WITH (NOLOCK)  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department AND
                                Departments.HotelNumber = @CurrentHotelNumber INNER JOIN PacketItem_New WITH (NOLOCK) ON PacketItem_New.PacketItemNumber = PacketItem_NewDepartmentXTab.PacketItemNumber
                                 WHERE PacketItem_New.PacketNumber = @APacketNumber)

        SET @AllHotelNumbersRepresentedInPacket = 0

    END

    SET @Return = @AllHotelNumbersRepresentedInPacket 

  END
  ELSE IF @Operator = 1   OR @Operator = 3 -- bfOR or bfNOT
  BEGIN
    SET @HotelFound = 0
    WHILE (@AHotelNumberList <> '') AND (@HotelFound = 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 PacketItem_NewDepartmentXTab.PacketItemNumber FROM PacketItem_NewDepartmentXTab WITH (NOLOCK) INNER JOIN PacketItem_New WITH (NOLOCK) ON PacketItem_New.PacketItemNumber = PacketItem_NewDepartmentXTab.PacketItemNumber INNER JOIN
       Departments WITH (NOLOCK) ON PacketItem_NewDepartmentXTab.FK_Department = Departments.PK_Department AND Departments.HotelNumber = @CurrentHotelNumber  WHERE PacketItem_NewDepartmentXTab.Type = 1 AND PacketItem_New.PacketNumber = @APacketNumber)
         SET @HotelFound = 1
    END

    SET @Return = @HotelFound

  
  END
  ELSE IF @Operator = 7 -- Exact
  BEGIN

    SET @TempHotelNumberList = ''
    DECLARE #PacketCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT HotelNumber FROM dbo.Departments D WITH (NOLOCK)
    INNER JOIN dbo.PacketItem_NewDepartmentXTab PDX WITH (NOLOCK) ON PK_Department=FK_Department AND PDX.Type = 1
    INNER JOIN dbo.PacketItem_New WITH (NOLOCK) ON PacketItem_New.PacketItemNumber=PDX.PacketItemNumber
    WHERE PacketItem_New.PacketNumber = @APacketNumber
    ORDER BY  hotelNumber
    OPEN #PacketCur
    
    FETCH NEXT FROM #PacketCur INTO @CurrentHotelNumber

    WHILE (@@fetch_status = 0)
    BEGIN
      IF @TempHotelNumberList <> ''
        SET @TempHotelNumberList = @TempHotelNumberList + ','
      IF @CurrentHotelNumber > 9 
        SET @TempHotelNumberList = @TempHotelNumberList + CONVERT(VARCHAR(2), @CurrentHotelNumber)
      ELSE
        SET @TempHotelNumberList = @TempHotelNumberList + CONVERT(VARCHAR(1), @CurrentHotelNumber)
      FETCH NEXT FROM #PacketCur INTO @CurrentHotelNumber

    END

    CLOSE #PacketCur
    DEALLOCATE #PacketCur

   IF @TempHotelNumberList IS NULL SET @TempHotelNumberList = ''
    IF @TempHotelNumberList = @AHotelNumberList
      SET @Return = 1
    ELSE
      SET @Return = 0

   
  END
  
  /* 20120119/RN: CR038916*/
  IF @Return = 0
  BEGIN
    SELECT @PacketItemCount = COUNT(*) FROM PacketItem_NEW WHERE PacketNumber = @APacketNumber
    IF @PacketItemCount = 0
      SET @Return = 1
  END

  RETURN @Return
END



