



CREATE PROCEDURE DBO.UpdateSchemeBooking @LineNumber int , @firstIdNumber int AS
SET NOCOUNT ON

-- Sidst rettet RN 2012 0827   Bruger nu GetNextNumber sp istedet for set @identityvalue = @identityvalue + 1
-- Sidst rettet AV 2005 0928   SET @TableBooking = ISNULL(@TableBooking,0)
-- Sidst rettet AV d. 2107 2004

Declare @CurrentDate Char (8)
Declare @DateCounter datetime
Declare @IdentityValue int
Declare @Counter int

Declare @Ex_Result bit

Declare @Tempdate datetime --AV 300802

Declare @Ex_Arrivaldate char(8)
Declare @Ex_DepartureDate char(8)
Declare @Ex_SchemePattern int


Declare @WeekdayOfFirstdate char(2)
Declare @WeekdayNumberOfFirstdate int


Declare  @BookedConferenceRoomTypeNumber int
Declare  @NumberOfRooms int
Declare  @ArrivalDate char (8)
Declare  @ArrivalTime char (5)
Declare  @DepartureDate char (8)
Declare  @DepartureTime char (5)
Declare  @ProductNumber int
Declare  @PackageNumber int
Declare  @Price real
Declare  @TotalPrice real 
Declare  @PriceOverWritten int
Declare  @ConferenceRoomSetupNumber int
Declare  @ConferenceRoomNumber int
Declare  @Adults int
Declare  @Children1 int
Declare  @ScreenLine int
Declare  @ReferenceNumber int
Declare  @SchemePattern int
Declare  @SchemeWeekDays char(7)
Declare  @LinkLineNumber int
Declare  @TableBooking int
Declare @TableStatus int

Declare @ChosenWeekday Char(7)

Declare @MainResArrival datetime
Declare @MainResDeparture datetime

Declare @MainResArrivalTime datetime
Declare @MainResDepartureTime datetime

Declare @BookDate datetime

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare @refno int
select @refno = referencenumber from reservationConferenceLine WITH (nolock) where ReservationConfLineNumber = @LineNumber

if Exists (Select ReservationConfLineNumber from ReservationConferenceLine WITH (nolock) where referencenumber = @Refno and LinkLineNumber is not null and linkLineNumber <> @LineNumber)
begin
  declare @TheKey int

  Declare loopCur Cursor Forward_Only For
  select ReservationConfLineNumber from ReservationConferenceLine WITH (nolock) where referencenumber = @Refno and LinkLineNumber is not null and linkLineNumber <> @LineNumber
  Open LoopCur

  Fetch Next From LoopCur
  Into @TheKey
  While  (@@FETCH_STATUS <> -1)
  Begin
      Delete from ReservationConferenceLine where ReservationConfLineNumber=@TheKey
      Fetch Next From LoopCur  Into @TheKey
  end
  close loopcur
  Deallocate loopcur
end  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  if @FirstIdNumber = -1 --delete all
  begin
    Delete  from reservationConferenceLine where Linklinenumber = @LineNumber
    Return --goodbye
  end 
    

  select @BookedConferenceRoomTypeNumber = BookedConferenceRoomTypeNumber,@NumberOfRooms=NumberOfRooms,@ArrivalDate=ArrivalDate,@ArrivalTime=ArrivalTime,@DepartureDate=DepartureDate, @DepartureTime=DepartureTime,
  @ProductNumber=ProductNumber,@PackageNumber=PackageNumber,@Price=Price,@TotalPrice=TotalPrice,@PriceOverWritten=PriceOverWritten,@ConferenceRoomSetupNumber=ConferenceRoomSetupNumber,@ConferenceRoomNumber=ConferenceRoomNumber,
  @Adults=Adults,@Children1=Children1,@ScreenLine=ScreenLine,@referencenumber=referencenumber,@SchemePattern=SchemePattern,@SchemeWeekDays =SchemeWeekDays, @TableBooking = TableBooking, @TableStatus = TableStatus  from reservationconferenceline WITH (nolock) where reservationconflinenumber=@Linenumber

  select @TotalPrice =Abs( @Price * datediff (hour, convert (datetime,@departuretime ),convert(datetime,@arrivaltime)))

  SET @TableBooking = ISNULL(@TableBooking,0)
 
  select @currentDate=@arrivaldate

  Select @WeekDayOfFirstDate = datename(weekday,convert(datetime,@ArrivalDate,112)) 
  if  @WeekDayOfFirstDate ='Mo' set @WeekDayNumberOfFirstDate = 0
  else if  @WeekDayOfFirstDate ='Tu' set @WeekDayNumberOfFirstDate = 1
  else if  @WeekDayOfFirstDate ='We' set @WeekDayNumberOfFirstDate = 2
  else if  @WeekDayOfFirstDate ='Th' set @WeekDayNumberOfFirstDate = 3

  else if  @WeekDayOfFirstDate ='Fr' set @WeekDayNumberOfFirstDate = 4
  else if  @WeekDayOfFirstDate ='Sa' set @WeekDayNumberOfFirstDate = 5
  else if  @WeekDayOfFirstDate ='Su' set @WeekDayNumberOfFirstDate = 6

  if @schemePattern in (3,4)
  begin
    set @Tempdate = Convert(datetime,@Currentdate)
    select @currentdate = convert(char(8),(@tempdate -  @WeekDayNumberOfFirstDate),112)   --current day is now monday and then we loop through the weeks... 
  end
  
  Select @Datecounter =  Convert (DateTime, @currentdate, 112)

 set @Ex_Result = 0

  set @IdentityValue = @FirstIdNumber

  Delete  from reservationConferenceLine where Linklinenumber = @LineNumber

While @currentDate <=@DepartureDate
begin

--FIRST WE HAVE TO CHECK IF THIS CURRENT DATE SHOULD NOT BE INCLUDED IN THE SCHEMA PERIOD, BECAUSE IT IS IN AN EXCEPTIOIN
    if @schemePattern not in (3,4)
    begin
      set @Ex_Result = 0

      Declare loopCur Cursor Forward_Only For
      select SchemePattern, Arrivaldate, DepartureDate from reservationconferenceline  WITH (NOLOCK) where referencenumber=@ReferenceNumber and Schemepattern = 7 
      Open LoopCur

      Fetch Next From LoopCur
      Into @Ex_SchemePattern,@Ex_Arrivaldate,@Ex_departuredate
      While  (@@FETCH_STATUS <> -1)
      Begin
        if @Currentdate >= @Ex_Arrivaldate and @Currentdate <=@Ex_departuredate
        begin
          set @Ex_result = 1
          break
        end

        Fetch Next From LoopCur  Into @Ex_SchemePattern,@Ex_Arrivaldate,@Ex_departuredate
      end
      close loopcur
      Deallocate loopcur
    end

  if (@Ex_result = 0) or (@schemePattern  in (3,4) )   --The date is not in the exception period
  begin

    if (@SchemePattern = 3) or (@SchemePattern = 4)
    begin
    --Okay we will construct a conferenceline for each chosen day in the week
      Set @Counter = 1
      While @Counter < =len(@SchemeWeekdays)
      begin
        set @ChosenWeekday = substring(@SchemeWeekdays, @Counter,1)

        select @bookdate =   @DateCounter + convert(int,@ChosenWeekday)

---------------------------------------------------------------------------------------------------------------------------------------------------- is the day in an exception
        set @Ex_Result = 0
  
        Declare loopCur Cursor Forward_Only For
        select SchemePattern,Arrivaldate,DepartureDate from reservationconferenceline  WITH (NOLOCK) where referencenumber=@ReferenceNumber and Schemepattern = 7 
        Open LoopCur

        Fetch Next From LoopCur
        Into @Ex_SchemePattern,@Ex_Arrivaldate,@Ex_departuredate
        While  (@@FETCH_STATUS <> -1)
        Begin
          if convert(char(8),@Bookdate,112) >= @Ex_Arrivaldate and Convert(Char(8),@Bookdate,112) <=@Ex_departuredate
          begin
            set @Ex_result = 1
            break
          end

          Fetch Next From LoopCur  Into @Ex_SchemePattern,@Ex_Arrivaldate,@Ex_departuredate
        end
        close loopcur
        Deallocate loopcur
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        if @Ex_result=0 --The date is not in the exception period
        begin
          if Convert(char(8),@bookdate,112) >= @Arrivaldate and convert(char(8),@bookdate,112) <=@departuredate -- OK the date is within the period
          begin

            if not exists (select ReservationconflineNumber from reservationconferenceline WITH (nolock) where reservationconflinenumber = @identityValue)
            begin
              insert into reservationconferenceline (ReservationConfLineNumber,BookedConferenceRoomTypeNumber,NumberOfRooms,ArrivalDate,ArrivalTime,DepartureDate,DepartureTime,ProductNumber,PackageNumber,Price,TotalPrice,PriceOverWritten,ConferenceRoomSetupNumber,ConferenceRoomNumber,Adults,Children1,ScreenLine,ReferenceNumber,LinkLineNumber, TableBooking, TableStatus) values
              (@identityValue,@BookedConferenceRoomTypeNumber,@NumberOfRooms,Convert(char(8),@bookdate,112),@ArrivalTime,Convert(char(8),@bookdate,112),@DepartureTime,@ProductNumber,@PackageNumber,@Price,@TotalPrice,@PriceOverWritten,@ConferenceRoomSetupNumber,@ConferenceRoomNumber,@Adults,@Children1,@ScreenLine,@ReferenceNumber,@LineNumber, @TableBooking, @TableStatus)
              EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @identityvalue OUTPUT
              -- set @identityValue = @identityValue +1 // 20120827/RN: CR046184, WTF ?????, replaced by GetNextNumber call above...
            end
          end
        end
        
        set @Counter=@Counter+1

      end
    end
    else
    begin

      if not exists (select ReservationconflineNumber from reservationconferenceline WITH (nolock) where reservationconflinenumber = @identityValue )
      begin
        insert into reservationconferenceline (ReservationConfLineNumber,BookedConferenceRoomTypeNumber,NumberOfRooms,ArrivalDate,ArrivalTime,DepartureDate,DepartureTime,ProductNumber,PackageNumber,Price,TotalPrice,PriceOverWritten,ConferenceRoomSetupNumber,ConferenceRoomNumber,Adults,Children1,ScreenLine,ReferenceNumber,LinkLineNumber, TableBooking, TableStatus) values
        (@identityValue,@BookedConferenceRoomTypeNumber,@NumberOfRooms,@currentdate,@ArrivalTime,@CurrentDate,@DepartureTime,@ProductNumber,@PackageNumber,@Price,@TotalPrice,@PriceOverWritten,@ConferenceRoomSetupNumber,@ConferenceRoomNumber,@Adults,@Children1,@ScreenLine,@ReferenceNumber,@LineNumber, @TableBooking, @TableStatus)
        EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @identityvalue OUTPUT
        -- set @identityValue = @identityValue +1 // 20120827/RN: CR046184, WTF ?????, replaced by GetNextNumber call above...
      end
    end
end

  if @schemepattern = 1  set @DateCounter = DateAdd(day,1,@datecounter)
  else if @SchemePattern= 2 set @DateCounter = DateAdd(day,2,@datecounter)
  else if @SchemePattern= 3 set @DateCounter = DateAdd(week,1,@datecounter)
  else if @SchemePattern= 4 set @DateCounter = DateAdd(week,2,@datecounter)
  else if @SchemePattern= 5 set @DateCounter = DateAdd(Month,1,@datecounter)
  else if @SchemePattern= 6 set @DateCounter = DateAdd(year,1,@datecounter)
select @CurrentDate = Convert (Char (8), @DateCounter, 112)

end

