CREATE PROCEDURE [dbo].[OnlineConfirmReservation] @AReferenceNumber INT, @ErrorLevel INT OUTPUT, @AVoucher VarChar(20)= '', @GuestNumberNamelist INT = 0 AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen July 2004

    Changes:
	AHE 2019-10-21: Added default values for SaveStatus
	SG 2019-10-04: Update CreationInProgress in ReservationMain
	KJ 20161230: Using UPPER results in table scan. removed to use index
	SG 20151217: Removed check if TableStatus < 5 allowing cancelled tables to be confirmed as long as the reservation
	TBU20151126: Added Update roomlines
	TB 2010 1911: Added @GuestNumberNamelist
    AV 2009 0610 also update the table status
    AV 2008 0129 added voucher
    TB20051108: Removed check if status = 7 (Cancelled)
    TB20050415:  Save Reservation with status readed fromhotelsetup: PicassoOnlineConfirmedState
     

    NOTES: 	- 

*/
/*
  Error Levels:
  00 : No errors
  01 : Reservation is cancelled
*/
SET @ErrorLevel = 00
DECLARE @Status TINYINT
DECLARE @SaveStatus INT

SELECT @SaveStatus=IsNull(KeyValue, 2) FROM HotelSetup (NOLOCK) WHERE SearchKey='PicassoOnlineConfirmedState'
SELECT @Status=Status FROM Reservations (NOLOCK) WHERE ReferenceNumber=@AReferenceNumber AND GuestNumber=@GuestNumberNamelist

--IF @Status=7 /* Cancelled */
  --SET @ErrorLevel=01
--ELSE
  UPDATE Reservations SET Status = @SaveStatus WHERE ReferenceNumber=@AReferenceNumber AND GuestNumber=@GuestNumberNamelist
  UPDATE ReservationMain SET CreationInProgress = 0 WHERE ReferenceNumber = @AReferenceNumber
  IF @AVoucher <> ''
    UPDATE Reservations SET Voucher = @AVoucher WHERE ReferenceNumber=@AReferenceNumber AND GuestNumber=@GuestNumberNamelist


  --Update table status. To avoid locktimeout when not updating through the key, we need either a cursor that updates through the key or an index on the involved columns in the where clause

  DECLARE @Id INT
  DECLARE TempCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT ReservationConfLineNumber FROM ReservationConferenceLine(NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND ISNULL(GuestNo, 0 ) = @GuestNumberNamelist AND TableBooking = 1 --AND  ISNULL(TableStatus, 0) < 5 
  OPEN TempCur
  FETCH NEXT FROM TempCur  INTO @Id
  WHILE (@@Fetch_status=0)
  BEGIN
   UPDATE ReservationConferenceLine SET TableStatus = @SaveStatus WHERE ReservationConfLineNumber = @Id
    FETCH NEXT FROM TempCur  INTO @Id
  END
  CLOSE TempCur
  DEALLOCATE TempCur

  
  -- Update roomlines
  update ReservationRoomline set StatusRL = @SaveStatus
  where ReservationRoomline.linenumber IN (  select ReservationRoomline.linenumber from ReservationRoomline (nolock)
  INNER JOIN Reservationroomlinextab (NOLOCK) ON Reservationroomlinextab.linenumber = ReservationRoomline.linenumber 
  where Reservationroomlinextab.referencenumber = @AReferenceNumber)
  
-- FOLLOWUP ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXEC OnlineUpdateFollowUp @AReferenceNumber

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


