CREATE PROCEDURE DBO.LinkArrangementLinesToGuests @ReferenceNumber INT, @GuestNumber INT,  @RoomLineNumber INT, @PersonType INT, @NamelistPerPerson INT AS 
SET NOCOUNT ON

--NOTE: this procedure links the arrangementlines to all the guests on the reservationline

--Last Changed: 
--RN 2012 1121 Replaced IS NULL with ISNULL function
--AV 2009 0109 SQL optimized

DECLARE @ArrNumber INT
DECLARE @CurrentGuestNumber INT
DECLARE @PlusProductLineNumber INT
DECLARE @DebType INT
DECLARE @PacketItemNumber INT

--The cursor below will contain the guestnumbers which is associated to the roomline. E.G there is 3 guests on the roomline : The GuestNumbers will be 4,5,6 IF the roomline is the second one in the roomgrid (and off course IF the first roomline also has 3 adults)

--I have decided NOT to use the cursor just below, instead this routine is called with a guestnumber parameter. 
--So this routine should be called for each guest. At this moment this is done in the createnamelist routine in Manreservation.pas in the Delphi code


--first get all the arrangementlines which is linked to the roomline
    DECLARE CopyCur Cursor  FORWARD_ONLY READ_ONLY FOR
    SELECT  ArrangementLineNumber, PlusProductLineNumber, PacketItemNumber FROM ArrangementRoomXTab WITH (NOLOCK) WHERE Linenumber = @RoomLinenumber AND ISNULL(PacketItemNumber,0) > 0 -- IS NOT NULL
    OPEN CopyCur
    FETCH NEXT FROM CopyCur
    INTO @ArrNumber, @PlusProductLineNumber, @PacketItemNumber
    WHILE  (@@FETCH_STATUS <> -1)
    BEGIN
      SELECT @DebType = DebType FROM PacketItem_New WITH (NOLOCK) WHERE PacketItemNumber = @PacketItemNumber
      IF ISNULL(@DebType,0) > 0 -- IS NOT NULL
      BEGIN
        IF @NameListPerPerson = 1
        BEGIN
          IF (@DebType IN (1, 2, 3) AND @PersonType = 0) OR (@DebType IN (4, 5, 6) AND @PersonType = 1) OR (@DebType IN (7, 8, 9) AND @PersonType = 2) --Only link the arrangementline to guest IF it is the right debtype
            IF NOT EXISTS (SELECT ArrangementLineNumber FROM ArrangementReservationsXTab WITH (NOLOCK) WHERE ArrangementLineNumber = @ArrNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND PlusProductLineNumber = @PlusProductLineNumber) 
              INSERT INTO ArrangementReservationsXTab VALUES (@ArrNumber, @ReferenceNumber, @GuestNumber, @PlusProductLineNumber) 
        END
        ELSE IF NOT EXISTS (SELECT ArrangementLineNumber FROM ArrangementReservationsXTab WITH (NOLOCK) WHERE ArrangementLineNumber = @ArrNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND PlusProductLinenumber = @PlusProductLineNumber) 
          INSERT INTO ArrangementReservationsXtab VALUES (@ArrNumber, @ReferenceNumber, @GuestNumber, @PlusProductLineNumber) 
      END

      FETCH NEXT FROM CopyCur INTO @ArrNumber, @PlusProductLineNumber, @PacketItemnumber 
    END

CLOSE copycur
DEALLOCATE copycur
