CREATE FUNCTION dbo.GetArrPax (@arrline INT, @ref INT, @guest INT, @rline INT = 0)  
RETURNS INT AS  

/*
  Changes:
    AV 20051229 the function now supports all debtypes
*/

BEGIN 
IF (@rline = 0) and (@guest > 0) --udregn roomlinenumber
  SELECT @rline = linenumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber = @Ref and GuestNumber = @Guest

IF EXISTS(SELECT Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @Ref and GuestNumber = @Guest and Status in (7, 8))
  return 0

IF NOT EXISTS(SELECT ArrangementLineNumber FROM ArrangementRoomXTab WITH (NOLOCK) WHERE ArrangementLinenumber = @ArrLine)
BEGIN
  DECLARE @Pcs INT
  SELECT @Pcs=Pieces FROM ArrangementLines WITH (NOLOCK) WHERE ArrangementLinenumber=@ArrLine
  RETURN @Pcs
end

DECLARE @Deb INT SET @Deb = 0
DECLARE @DebStr VARCHAR(10) SET @DebStr = ''
DECLARE @AdultPlusLines INT SET @AdultPlusLines = 0
DECLARE @C1PlusLines INT SET @C1PlusLines = 0
DECLARE @C2PlusLines INT SET @C2PlusLines = 0
DECLARE @TArrn INT SET @TArrn = 0
DECLARE @PlusProductLineNumber INT SET @PlusProductLineNumber = 0

DECLARE Dep_Cur CURSOR FOR
SELECT  DISTINCT ArrangementLineNumber, PlusProductLineNumber, DebType FROM PacketItem_New WITH  (NOLOCK) INNER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementRoomxtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
WHERE ArrangementLineNumber = @Arrline AND LineNumber = @Rline
OPEN Dep_Cur
FETCH NEXT FROM Dep_Cur INTO @TArrn, @PlusProductLineNumber, @Deb
WHILE @@fetch_status = 0
BEGIN
  IF CHARINDEX(CONVERT(VARCHAR(1), @Deb), @DebStr) = 0 
    SET @DebStr = @DebStr + CONVERT(VARCHAR(1), @Deb)

  IF (@deb = 1) OR (@Deb = 2) or  (@deb = 3) 
    SET @AdultPlusLines = @AdultPlusLines + 1
  IF (@deb = 4)  OR (@Deb = 5) or (@deb = 6) 
    SET @c1pluslines = @c1pluslines + 1
  IF (@deb = 7) OR  (@Deb = 8)   OR  (@deb = 9) 
    SET @c2pluslines = @c2pluslines + 1

  FETCH NEXT FROM Dep_Cur into @TArrn, @PlusProductLineNumber, @Deb
END
DEALLOCATE Dep_Cur

IF @Guest = 0
BEGIN
  DECLARE @adult int set @adult = 0
  DECLARE @c1 int set @c1 = 0
  DECLARE @c2 int set @c2 = 0
  DECLARE @pax int set @pax = 0
  SELECT @adult = adults, @c1 = children1, @c2 = children2 FROM reservationroomline WITH (NOLOCK) INNER JOIN ReservationRoomLineXtab WITH (NOLOCK) ON ReservationRoomLineXTab.Linenumber = reservationroomline.linenumber where referencenumber = @ref and guestnumber = 0 and reservationroomline.linenumber = @rline
  if (charindex('2', @debstr) > 0)  OR charindex('3', @debstr) > 0 
    SET @pax = @pax + (@adult * @adultpluslines)
  if (charindex('5', @debstr) > 0) OR (charindex('6', @debstr) > 0) 
    SET @pax = @pax + (@c1 * @c1pluslines)
  if (charindex('8', @debstr) > 0) OR (charindex('9', @debstr) > 0) 
    SET @pax = @pax + (@c2 * @c2pluslines)

  if (charindex('1', @debstr) > 0) 
    SET @pax = @Pax +@adultpluslines
  if (charindex('4', @debstr) > 0) 
    SET @pax = @Pax +@c1pluslines
  if (charindex('7', @debstr) > 0) 
    SET @pax = @Pax +@c2pluslines
END
ELSE

IF @guest > 0
BEGIN
  set @adult = 0
  set @c1 = 0
  set @c2 = 0
  if (charindex('3', @debstr) > 0)  or (charindex('2', @debstr) > 0)
    select @adult = (adults * @adultpluslines) from reservations WITH (nolock) where referencenumber = @ref and guestnumber = @guest
  if (charindex('5', @debstr) > 0) or  (charindex('6', @debstr) > 0)
    select @c1 = (children1 * @c1pluslines) from reservations WITH (nolock) where referencenumber = @ref and guestnumber = @guest
  if charindex('8', @debstr) > 0 or (charindex('9', @debstr) > 0)
    select @c2 = (children2 * @c2pluslines) from reservations WITH (nolock) where referencenumber = @ref and guestnumber = @guest
  set @pax = @adult + @c1 + @c2

  if (charindex('1', @debstr) > 0) 
    SET @pax = @Pax +@adultpluslines
  if (charindex('4', @debstr) > 0) 
    SET @pax = @Pax +@c1pluslines
  if (charindex('7', @debstr) > 0) 
    SET @pax = @Pax +@c2pluslines
END

RETURN (@Pax)
END

