CREATE PROCEDURE dbo.CopyExtras @ReferenceNumber INT, @FromGuestNumber INT, @ToGuestNumber INT AS
SET NOCOUNT ON
/*

    CREATED: 17/12-2002 Jacob Pedersen

    LAST UPDATED: 08/01-2003 Jacob Pedersen must not copy past dates to inhouse
    AV 2007 0410
    AV 20060507 : exdepartment to fk_exdepartment
    AV 20071025: rewrite from Inner selects to Inner joins

    Description: Copies all extra's from one person in a group to another.

*/

DECLARE @SystemDateStr DATETIME
SELECT @SystemDateStr=SystemDate FROM System WITH (NOLOCK) WHERE Number=1

DECLARE @ToArrivalDate CHAR (8)
DECLARE @ToDepartureDate CHAR (8)
DECLARE @Status INT

SELECT @ToArrivalDate=ArrivalDate, @ToDepartureDate=DepartureDate, @Status=Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@ToGuestNumber

DECLARE @ArrNumber INT
DECLARE @Date CHAR (8)
DECLARE @Time CHAR (5)
DECLARE @Price MONEY
DECLARE @TotalPrice MONEY
DECLARE @Pieces INT
DECLARE @WareText VARCHAR (50)
DECLARE @PK_Department INT
DECLARE @FK_ExDepartment INT
DECLARE @PlaceText VARCHAR (30)
DECLARE @WareNumber INT
DECLARE @HallNumber INT
DECLARE @Note VARCHAR (8000)
DECLARE @IdentityValue INT

DECLARE #ExtraCopyCur CURSOR FORWARD_ONLY FOR

--SELECT ArrangementLineNumber, [Date],[Time], Price, TotalPrice, Pieces, WareText, FK_ExDepartment, PlaceText, WareNumber, HallNumber, Note FROM ArrangementLines(nolock) where arrangementlinenumber in
--(select arrangementlinenumber from arrangementreservationsxtab (nolock) where referencenumber=@ReferenceNumber and guestnumber=@FromGuestNumber) and arrangementlinenumber not in (select arrangementlinenumber from arrangementroomxtab (nolock))

SELECT DISTINCT ArrangementLines.ArrangementLineNumber, [Date],[Time], Price, TotalPrice, Pieces, WareText, FK_ExDepartment, PlaceText, WareNumber, HallNumber, CONVERT(VARCHAR(8000), Note) AS Note  
FROM ArrangementLines WITH (NOLOCK) 
INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber LEFT OUTER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementRoomXTab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
WHERE ArrangementReservationsXTab.ReferenceNumber = @ReferenceNumber 
  AND ArrangementReservationsXTab.GuestNumber = @FromGuestNumber
  AND ISNULL(ArrangementRoomXTab.ArrangementLineNumber,0) = 0

OPEN #ExtraCopyCur

FETCH NEXT FROM #ExtraCopyCur
INTO @ArrNumber, @Date, @Time, @Price, @TotalPrice, @Pieces, @WareText,  @FK_ExDepartment, @PlaceText, @WareNumber, @HallNumber, @Note

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  IF @Date >= @ToArrivalDate and @Date <= @ToDepartureDate and ((@Date >= @SystemDateStr and @Status = 5) or (@Status<5))  -- JP 20030108 only today and future
  BEGIN
    INSERT INTO ArrangementLines ([Date],[Time], Price, /*TotalPrice,*/ Pieces, WareText, FK_ExDepartment, PlaceText, WareNumber, HallNumber, Note) VALUES (@Date, @Time, @Price, /*@TotalPrice,*/ @Pieces, @WareText, @FK_ExDepartment, @PlaceText, @WareNumber, @HallNumber, @Note)
    SET @IdentityValue = @@Identity 
    INSERT INTO ArrangementReservationsXTab (ArrangementLineNumber, ReferenceNumber, GuestNumber, PlusProductLineNumber) VALUES (@IdentityValue, @ReferenceNumber, @ToGuestNumber, 0)

     -- ArrangmentLineDepartmentXTab
     DECLARE DepCur Cursor Forward_Only For
     SELECT DISTINCT FK_DepartmentNumber FROM ArrangementLineDepartmentXTab  WITH (NOLOCK)
      WHERE FK_ArrangementLineNumber=@ArrNumber 
      OPEN DepCur
      FETCH NEXT FROM DepCur INTO @PK_Department
      WHILE  (@@FETCH_STATUS <> -1)
      BEGIN
       IF NOT EXISTS (SELECT FK_ArrangementLineNumber FROM ArrangementLineDepartmentXTab WITH (NOLOCK) WHERE FK_ArrangementLineNumber = @IdentityValue AND FK_DepartmentNumber = @PK_Department)
         INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES (@IdentityValue, @PK_Department)
       FETCH NEXT FROM DepCur INTO @PK_Department
       END
       CLOSE DepCur
       DEALLOCATE DepCur

  END

  FETCH NEXT FROM #ExtraCopyCur
  INTO @ArrNumber, @Date, @Time, @Price, @TotalPrice, @Pieces, @WareText, @FK_ExDepartment, @PlaceText, @WareNumber, @HallNumber, @Note
END

CLOSE #ExtraCopyCur
DEALLOCATE #ExtraCopyCur

