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

IF @guest > 0
BEGIN
  DECLARE @HallAdults INT
  DECLARE @HallChildren1 INT
  DECLARE @HallChildren2 INT
  DECLARE @HallPacketItemNumber INT
  DECLARE @HallNumberOfRooms INT
  DECLARE @DebType INT
  DECLARE ArrPaxCursor CURSOR FOR
  SELECT ReservationConferenceline.NumberOfRooms, ReservationConferenceLine.Adults, ReservationConferenceLine.Children1, ReservationConferenceLine.Children2, ArrangementRoomXtab.PacketItemNumber
  FROM ReservationConferenceLine WITH (NOLOCK)
  INNER JOIN ArrangementRoomXtab WITH (NOLOCK) ON ArrangementRoomXtab.ArrangementLineNumber = @arrline
    AND ArrangementRoomXtab.Linenumber = ReservationConferenceLine.ReservationConfLineNumber
    AND ReservationConferenceLine.ReferenceNumber = @ref AND ReservationConferenceLine.GuestNo = @guest
  OPEN ArrPaxCursor
  FETCH NEXT FROM ArrPaxCursor INTO @HallNumberOfRooms, @HallAdults, @HallChildren1, @HallChildren2, @HallPacketItemNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT @DebType = DebType FROM PacketItem_NEW WITH (NOLOCK) WHERE PacketItem_NEW.PacketItemNumber = @HallPacketItemNumber
	IF @DebType IN (2,3)
	  SET @Pax = @Pax + (@HallNumberOfRooms * @HallAdults)
    IF @DebType IN (5,6)
	  SET @Pax = @Pax + (@HallNumberOfRooms * @HallChildren1)
	IF @DebType IN (8,9)
	  SET @Pax = @Pax + (@HallNumberOfRooms * @HallChildren2)
	IF @DebType = 1
	  SET @Pax = @Pax + @HallAdults
	IF @DebType = 4
	  SET @Pax = @Pax + @HallChildren1
    IF @DebType = 7
	  SET @Pax = @Pax + @HallChildren2
    FETCH NEXT FROM ArrPaxCursor INTO @HallNumberOfRooms, @HallAdults, @HallChildren1, @HallChildren2, @HallPacketItemNumber
  END
  CLOSE ArrPaxCursor
  DEALLOCATE ArrPaxCursor
END
RETURN (@Pax)
END

