



CREATE PROCEDURE DBO.UpdateOrdreBookCustomer @CustomerNumber int, @FromDateStr char(8), @AddValues bit  WITH RECOMPILE  AS
SET NOCOUNT ON

-- KJ 20090330: Ext: Calculate for all reservations - don't use the tentative setting
-- Rettet d. 10/06-2004 : Imp. HotelCustomerNumber
-- Rettet d. 28/04-2003 : Tilføjet Dato

/*Create Table #FoundValues (CustomerNumber int NOT NULL,
                                              CustomerTypeNumber Smallint NOT NULL)

Create Table #TempTab (CustomerNumber int NOT NULL,
                                      CustomerTypeNumber int NOT NULL,
                                      ProcessingNumber smallint NOT NULL) -- Used in all AllUplinks....!!!!!!*/ -- moved to madeordrebook

Declare @CustomerTypeNumber SmallInt

Declare @CustomerNumberTab int
Declare @CustomerNumberOrdreBook int

Declare @SegmentNumber int
Declare @DateForValue Char(8)
Declare @TurnoverRooms money
Declare @NumberOfNightRooms int
Declare @NumberOfPaxRooms int
Declare @NumberOfStaysRooms int
Declare @NumberOfCXLRooms int
Declare @NumberOfNoShowRooms int

Declare @TurnoverDaymeetings money
Declare @NumberOfPaxDaymeetings int
Declare @NumberOfStaysDaymeetings int

Declare @TurnoverHalls money
Declare @NumberOfHalls int
Declare @NumberOfPaxHalls int

Declare @TurnoverRestaurant int

Declare @TurnoverOther int

Declare @HotelCustomerNumber int

DECLARE @ReservationStatus INT

Select @CustomerTypeNumber=CustomerTypeNumber From Customer WITH (NoLock) where CustomerNumber=@CustomerNumber;

Exec FindValues  @CustomerNumber,7  -- Finder alle SalesChannel

If  @CustomerTypeNumber=2 --Company
begin
   Exec FindValues  @CustomerNumber,4 -- Finder alle CompanyCorp
   Exec AllUpLinksCompany
end
else
If  @CustomerTypeNumber=3  -- Agency
begin
  Exec FindValues  @CustomerNumber,5 -- Finder alle AgencyCorp
  Exec AllUpLinksAgency
end
else if @CustomerTypeNumber=6 -- Agent
begin
   Exec AllUplinksAgent
end

Insert #FoundValues 
  select CustomerNumber, CustomerTypeNumber from #TempTab

Declare CustomerCursor Cursor Fast_Forward Read_only For
  Select CustomerNumber  from #FoundValues WITH (NOLOCK)
Open CustomerCursor
Fetch Next From CustomerCursor
Into @CustomerNumberTab

While (@@FETCH_STATUS <> -1)
begin
  Declare OrdreBookCur  Cursor Fast_Forward Read_only FOR
  Select CustomerNumber, SegmentNumber, DateForValue, TurnoverRooms, NumberOfNightRooms, NumberOfPaxRooms, NumberOfStaysRooms, NumberOfCXLRooms, NumberOfNoShowRooms,
    TurnoverDaymeetings, NumberOfPaxDaymeetings, NumberOfStaysDaymeetings, TurnoverHalls, NumberOfHalls, NumberOfPaxHalls, TurnoverRestaurant, TurnoverOther, HotelCustomerNumber, ReservationStatus
    From CustomerOrdreBook WITH (NOLOCK)  where CustomerNumber=@CustomerNumber and DateForValue>=@FromDateStr
  Open OrdreBookCur
  Fetch Next From OrdreBookCur

  Into @CustomerNumberOrdreBook, @SegmentNumber, @DateForValue, @TurnoverRooms, @NumberOfNightRooms, @NumberOfPaxRooms, @NumberOfStaysRooms, @NumberOfCXLRooms, @NumberOfNoShowRooms,
         @TurnoverDaymeetings, @NumberOfPaxDaymeetings, @NumberOfStaysDaymeetings, @TurnoverHalls, @NumberOfHalls, @NumberOfPaxHalls, @TurnoverRestaurant, @TurnoverOther, @HotelCustomerNumber, @ReservationStatus
  
  While (@@FETCH_STATUS <> -1)
  begin 
    if (@AddValues = 1)
    begin
        Update CustomerOrdreBook SET
             TurnOverRooms = TurnOverRooms + @TurnoverRooms,
             NumberOfNightRooms = NumberOfNightRooms + @NumberOfNightRooms,
             NumberOfPaxRooms = NumberOfPaxRooms + @NumberOfPaxRooms,
             NumberOfStaysRooms = NumberOfStaysRooms + @NumberOfStaysRooms,
             NumberOfCXLRooms = NumberOfCXLRooms + @NumberOfCXLRooms,
             NumberOfNoShowRooms = NumberOfNoShowRooms + @NumberOfNoShowRooms,
             TurnoverDaymeetings = TurnoverDaymeetings + @TurnoverDaymeetings,
             NumberOfPaxDaymeetings = NumberOfPaxDaymeetings + @NumberOfPaxDaymeetings,
             NumberOfStaysDaymeetings = NumberOfStaysDaymeetings + @NumberOfStaysDaymeetings,
             TurnoverHalls = TurnoverHalls + @TurnoverHalls,
             NumberOfHalls = NumberOfHalls + @NumberOfHalls,
             NumberOfPaxHalls = NumberOfPaxHalls + @NumberOfPaxHalls,
             TurnoverRestaurant = TurnoverRestaurant + @TurnoverRestaurant,
             TurnoverOther = TurnoverOther + @TurnoverOther
       where CustomerNumber=@CustomerNumberTab and  SegmentNumber=@SegmentNumber and DateForValue=@DateForValue AND HotelCustomerNumber = @HotelCustomerNumber AND ReservationStatus = @ReservationStatus

       if @@RowCount = 0 -- Nothing was updated -> insert row!
       INSERT INTO CustomerOrdreBook (CustomerNumber, SegmentNumber,DateForValue,TurnOverRooms, NumberOfNightRooms, NumberOfPaxRooms, NumberOfStaysRooms, NumberOfCXLRooms, NumberOfNoShowRooms, TurnOverDaymeetings, NumberOfPaxDaymeetings, NumberOfStaysDaymeetings, TurnOverHalls,  NumberOfHalls, NumberOfPaxHalls, TurnOverRestaurant, TurnOverOther, HotelCustomerNumber, ReservationStatus)
         VALUES (@CustomerNumberTab, @SegmentNumber, @DateForValue, @TurnoverRooms, @NumberOfNightRooms,@NumberOfPaxRooms, @NumberOfStaysRooms, @NumberOfCXLRooms, @NumberOfNoShowRooms,
         @TurnoverDaymeetings,@NumberOfPaxDaymeetings,@NumberOfStaysDaymeetings,@TurnoverHalls,@NumberOfHalls,@NumberOfPaxHalls,@TurnoverRestaurant,@TurnoverOther, @HotelCustomerNumber, @ReservationStatus) 
    End
    else
    begin
        Update CustomerOrdreBook SET
             TurnOverRooms = TurnOverRooms - @TurnoverRooms,
             NumberOfNightRooms = NumberOfNightRooms - @NumberOfNightRooms,
             NumberOfPaxRooms = NumberOfPaxRooms - @NumberOfPaxRooms,
             NumberOfStaysRooms = NumberOfStaysRooms - @NumberOfStaysRooms,
             NumberOfCXLRooms = NumberOfCXLRooms - @NumberOfCXLRooms,
             NumberOfNoShowRooms = NumberOfNoShowRooms - @NumberOfNoShowRooms,
             TurnoverDaymeetings = TurnoverDaymeetings - @TurnoverDaymeetings,
             NumberOfPaxDaymeetings = NumberOfPaxDaymeetings - @NumberOfPaxDaymeetings,
             NumberOfStaysDaymeetings = NumberOfStaysDaymeetings - @NumberOfStaysDaymeetings,
             TurnoverHalls = TurnoverHalls - @TurnoverHalls,
             NumberOfHalls = NumberOfHalls - @NumberOfHalls,
             NumberOfPaxHalls = NumberOfPaxHalls - @NumberOfPaxHalls,
             TurnoverRestaurant = TurnoverRestaurant - @TurnoverRestaurant,
             TurnoverOther = TurnoverOther - @TurnoverOther
        where CustomerNumber=@CustomerNumberTab and  SegmentNumber=@SegmentNumber and DateForValue=@DateForValue AND HotelCustomerNumber = @HotelCustomerNumber AND ReservationStatus = @ReservationStatus
    end -- end Addvalue=0  

    Fetch Next From OrdreBookCur
    Into @CustomerNumberOrdreBook, @SegmentNumber, @DateForValue, @TurnoverRooms, @NumberOfNightRooms, @NumberOfPaxRooms, @NumberOfStaysRooms, @NumberOfCXLRooms, @NumberOfNoShowRooms,
           @TurnoverDaymeetings,@NumberOfPaxDaymeetings, @NumberOfStaysDaymeetings, @TurnoverHalls, @NumberOfHalls, @NumberOfPaxHalls, @TurnoverRestaurant, @TurnoverOther, @HotelCustomerNumber, @ReservationStatus

  end -- Fetchstatus=-1

  if (@AddValues = 0)
  Exec OrderBookDeleteIfAllValueZeo @CustomerNumberTab

  Close OrdreBookCur
  Deallocate OrdreBookCur
   
--  Print @CustomerNumberTab    
  Fetch Next From CustomerCursor Into @CustomerNumberTab
end

if (@AddValues = 0) -- Hvis link er fjernet : Slet alt hvad denne kunde har af data!!!
Begin
     Delete from CustomerOrdreBook
       Where CustomerNumber=@CustomerNumber
end 

Close CustomerCursor
Deallocate CustomerCursor

Delete from #FoundValues 
Delete from #TempTab