CREATE PROCEDURE DBO.CopyArrangementDay  @ReferenceNumber int,  @SourceDate datetime, @DestDate datetime, @KeepExistingData int AS
SET NOCOUNT ON

-- 20121121 RN: Replaced IS NULL with ISNULL function
--sidst rettet 2007 0410 use the new dep xtab

Declare @arrNumber int
Declare @Time Char (5)
Declare @Price real
Declare @TotalPrice real
Declare @Pieces int
Declare @WareText char (50)
--Declare @Departments VARCHAR(32)
Declare @PK_Department int
Declare @FK_Exdepartment int
Declare @PlaceText char (20)
Declare @WareNumber int
Declare @Note varchar (4000)
Declare @hallnumber int

Declare @arrangementLineNumber int
Declare @ArrangementLinksNumber int
Declare @ArrangementLinksText char (30)
Declare @IdentityValue int

Declare @ArrlineDeleted int --a boolean

Declare @FormatedSourceDate Char (10)
Declare @FormatedDestDate Char (10)

select  @FormatedSourceDate =  Convert (Char (8), @SourceDate, 112)
select  @FormatedDestDate =  Convert (Char (8), @DestDate, 112)

--delete existing arrangementLines on the dest day
if @KeepExistingData =0 
begin
  Declare DeleteDestday Cursor For
  SELECT arrangementLineNumber FROM ArrangementLines WITH (nolock) 
  where  ArrangementLines.ArrangementLineNumber in (select arrangementLineNumber from arrangementReservationsXtab WITH (nolock) where referencenumber=@referencenumber)
  and  ArrangementLines.date = @FormatedDestDate
  Open DeleteDestDay
  Fetch Next From DeleteDestday
  Into @arrnumber
 
  While  (@@FETCH_STATUS = 0)
  Begin
     Delete  from arrangementRoomXtab where  arrangementlinenumber=@Arrnumber
     delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrNumber 
     delete from ArrangementLineDepartmentXTab where FK_ArrangementLineNumber = @ArrNumber
     delete from arrangementlines where arrangementlinenumber = @ArrNumber
     Fetch Next From DeleteDestDay Into @arrnumber
  end
  Close DeleteDestday
  Deallocate DeleteDestday
end

Declare CopyDay Cursor For
SELECT ArrangementLineNumber,Time, Price, TotalPrice, Pieces, WareText, FK_ExDepartment,PlaceText,WareNumber, note, Hallnumber
FROM ArrangementLines WITH (nolock) 
where  ArrangementLines.ArrangementLineNumber in (select arrangementLineNumber from arrangementReservationsXtab WITH (nolock) where referencenumber=@referencenumber)
and  ArrangementLines.date = @FormatedSourceDate

    Open Copyday
    Fetch Next From Copyday
    Into @ArrNumber,@Time, @Price, @TotalPrice, @Pieces, @WareText, @FK_ExDepartment,@PlaceText,@WareNumber, @note, @hallnumber 

   -- Loop through records of sourcedate
    While  (@@FETCH_STATUS = 0)
    Begin
       if ISNULL(@hallnumber,0) > 0 -- is not null
       begin
         select conferenceroomnumber from reservationconferenceline WITH (nolock) where referencenumber= @referencenumber and arrivaldate <= @FormatedDestDate and departuredate >=@FormatedDestDate and conferenceroomnumber = @hallnumber
         if @@rowcount = 0
           set @hallnumber = null
       end
       Insert Into ArrangementLines (Date,Time,Price,/*TotalPrice,*/Pieces,WareTExt,FK_Exdepartment,PlaceText,WareNumber,note,hallnumber) Values (@FormatedDestDate,@Time,@Price, /*@TotalPrice,*/ @Pieces,@waretext, @FK_exdepartment,@PlaceText,@WareNumber,@note,@hallnumber)
       select @identityvalue=@@identity from ArrangementLines WITH (NOLOCK)        
       Insert into arrangementReservationsXtab values (@IdentityValue,@Referencenumber,0,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
       --If the lines we are about to copy is linked to a linenumber and a PacketItemNumber then we can not lose this link we have to transfer the links to the new day

       if @KeepExistingData = 1            
         EXEC CheckArrangementTolerance @IdentityValue, @ReferenceNumber, @ArrLineDeleted  
       Fetch Next From CopyDay Into @ArrNumber,@Time, @Price, @TotalPrice, @Pieces, @WareText ,@FK_ExDepartment,@PlaceText,@WareNumber, @Note, @hallnumber
    end


  Close Copyday
 Deallocate CopyDay

