CREATE PROCEDURE dbo.CheckArrangementTolerance @ArrangementLineNumber int, @Referencenumber  int, @Changed Int OUTPUT  AS
SET NOCOUNT ON

--20141204 RN: Added check for "Use Arrangement tolerance" setting.
-- 20121121 RN: Replaced IS NULL with ISNULL function
--Last change AV 2008 1008 fix for dublicate records in cursor because of the different guestnumbers that an arrangementline can point at. This caused that the arrangementline will have pieces and price that are many doubled
--Last change AV 2008 0422 fix for not merging arr lines together (Departments variables was out of sync)
-- sidst rettet AV 20060507 exdepartment
--sidste rettet  AV 20050211 hvis tolerancen = 0 minutter then exit
--sidst rettet 20030508 
  
Declare @Time char(5)
Declare @Warenumber int
Declare @Price real
Declare @TempPrice real
Declare @Date char (8)
Declare @Pieces int
Declare @TempPieces int
Declare @FK_ExDepartment int
Declare @HallNumber int
Declare @note varchar (5000)
Declare @FoundHallNumber int
Declare @FoundFK_ExDepartment int

Declare @Tolerance char(5)
Declare @ArrNumber int
Declare @DublicateArrNumber int

Declare @LineNumber int
Declare @PacketItemNumber int
Declare @ArrNumber2 int
Declare @PlusProductLineNumber int

Declare @LastLinkNumber int
Declare @Text char (50)

Declare @MainLineIsManual bit
Declare @foundGuestNumber int

DECLARE @UseArrangementTolerance BIT

Select @Changed = 0

SELECT @UseArrangementTolerance = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'UseCheckTolerance'
IF @UseArrangementTolerance = 0
  RETURN

--find out if the arr line is manual
if not exists(select arrangementlinenumber from arrangementroomxtab WITH (nolock) where arrangementlinenumber=@Arrangementlinenumber)
  set @MainLineIsManual =1
else
  set @MainLineIsManual =0  

select @Time = Time, @Pieces = Pieces, @WareNumber =  Warenumber, @FK_Exdepartment = FK_Exdepartment, @HallNumber=HallNumber, @Price =  Price, @date = date from arrangementlines WITH (nolock) where arrangementLineNumber = @ArrangementLineNumber
if (@@Rowcount >0) --and (@FK_exdepartment is null)
begin
  -- FIND THE TOLERANCE IN HOTELSETUP
  Select @Tolerance = Keyvalue from hotelsetup WITH (nolock) where SearchKey ='ArrTimeTolerance'
  if @tolerance = '00:00'
    return
  

-- arrangementReservationsXtab.Referencenumber, 
   SET @DublicateArrNumber = 0

   Declare FoundArrLinesCur Cursor Forward_Only For
   SELECT distinct arrangementLines.ArrangementLinenumber, Pieces, Price, HallNumber, FK_Exdepartment, convert(varchar(8000),note) as note,GuestNumber
   FROM ArrangementLines WITH (nolock) INNER JOIN
    ArrangementReservationsXtab WITH (nolock) ON 
    ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
   WHERE date = @date and referencenumber = @ReferenceNumber and warenumber = @WareNumber and arrangementLines.ArrangementLineNumber <>@ArrangementLineNumber and Price = @Price and 
   (Time = @Time or ( abs(DATEDIFF(Minute,Convert (DateTime,@Time,108), Convert (DateTime,Time,108)) ) <=  abs (Datediff (Minute, @Tolerance,Convert(datetime,'00:00',108)  )    )  ))  


    Open FoundArrLinesCur
    Fetch Next From FoundArrLinesCur
    Into  @Arrnumber,  @TempPieces, @Price, @FoundHallNumber ,@FoundFK_ExDepartment, @note ,@FoundGuestNumber
    While  (@@FETCH_STATUS <> -1)
    Begin
       IF @DublicateArrNumber = @ArrNumber -- AV 2008 1008 jump over the already handled arrangementline
       BEGIN
         FETCH NEXT FROM FoundArrLinesCur INTO  @Arrnumber,  @TempPieces, @Price, @FoundHallNumber ,@FoundFK_ExDepartment,@note,@FoundGuestNumber          
         CONTINUE
       END
       ELSE
         SET @DublicateArrNumber = @ArrNumber
       if @MainLineIsManual=1
       begin
         declare @guestnumber int
         declare @foundLineIsManual bit
         select @guestnumber=guestnumber from arrangementreservationsxtab WITH (nolock) where referencenumber=@referencenumber and arrangementlinenumber=@Arrangementlinenumber  --the guestnumber for the main line (the input parameter)       
         if not exists(select arrangementlinenumber from arrangementroomxtab WITH (nolock) where arrangementlinenumber=@Arrnumber)
           set @FoundLineIsManual =1
         else
           set @FoundLineIsManual =0  

         if @foundlineIsManual=1       
         begin
           if @guestnumber <>@FoundGuestNumber
           begin
               Fetch Next From FoundArrLinesCur
               Into  @Arrnumber,  @TempPieces, @Price, @FoundHallNumber ,@FoundFK_ExDepartment,@note,@FoundGuestNumber          
             continue
           end
         end

       end        

       if ISNULL(@FoundhallNumber, 0) = 0 -- is null 
        select @foundHallNumber = 0
       if ISNULL(@HallNumber, 0) = 0 -- is null 
        select @HallNumber = 0
       if ISNULL(@FK_ExDepartment, 0) = 0 -- is null 
        select @FK_ExDepartment = 0
       if ISNULL(@FoundFK_ExDepartment, 0) = 0 -- is null 
        select @FoundFK_ExDepartment = 0

        if (@hallnumber = @foundhallnumber)  and (@FK_ExDepartment = @FoundFK_ExDepartment)  
        begin
          Update arrangementLines set Pieces = Pieces + @TempPieces, /*TotalPrice = TotalPrice +  (Price * @TempPieces),*/ note = cast(note as Varchar(4000)) + @note where arrangementLinenumber = @ArrangementLinenumber

          --we have to update arrangementroomxtab table, so it still points at the right Reservationline numbers

          Declare UpdateCur Cursor Forward_Only For

          select LineNumber,PlusProductLineNumber,PacketItemNumber from arrangementRoomXtab WITH (nolock) where arrangementLineNumber = @ArrNumber
    
          Open UpdateCur
          Fetch Next From UpdateCur
          Into  @LineNumber,@PlusProductLineNumber,@PacketItemNumber
          While  (@@FETCH_STATUS <> -1)
          Begin
            if not exists (select arrangementlinenumber from arrangementRoomXtab WITH (nolock) where ArrangementLineNumber = @ArrangementLineNumber and linenumber = @LineNumber and plusProductLineNumber = @PlusProductLineNumber and packetitemnumber = @PacketItemNumber)
--            select ArrangementLineNumber from arrangementRoomXtab WITH (nolock) where ArrangementLineNumber = @ArrangementLineNumber and linenumber = @LineNumber and plusProductLineNumber = @PlusProductLineNumber
--             if @@RowCount = 0
               Insert into ArrangementRoomxtab values (@ArrangementLineNumber,@LineNumber,@PlusProductLineNumber,@PacketItemNumber)       
            Fetch Next From UpdateCur  Into @LineNumber,@PlusProductLineNumber,@PacketItemNumber
          end

         Close UpdateCur
         Deallocate UpdateCur

         --delete all that points on the old arrangementline

        Delete  from arrangementRoomXtab where arrangementlinenumber=@Arrnumber
        delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrNumber 
        delete from ArrangementLineDepartmentXTab where FK_ArrangementLineNumber = @ArrNumber
        DELETE FROM ArrangementReservationConferenceLineXtab where ArrangementLineNumber = @ArrNumber
        delete from arrangementlines where arrangementlinenumber = @ArrNumber
        Select @Changed = 1
      end
       Fetch Next From FoundArrLinesCur
       Into  @Arrnumber,  @TempPieces, @Price, @FoundHallNumber ,@FoundFK_ExDepartment,@note,@FoundGuestNumber 
  end
close FoundArrlinesCur
DeAllocate FoundArrlinesCur

end

