CREATE PROCEDURE dbo.OnlinePostChangeStatus @AReferenceNumber INT, @AGuestNumber INT, @ASystemDate SMALLDATETIME, @AShownName INT, @AUserId INT, @AHotelNumber INT AS
/*
  Created by Kent Johansen, Februar 2024
  Changes reservation status when paid online
  Changes:

*/

DECLARE @AllotmentNumber INT
DECLARE @AllotmentOverbook BIT
DECLARE @RoomTypeNumber INT
DECLARE @ArrivalDT DATETIME
DECLARE @DepartureDT DATETIME
DECLARE @tmpOverbook TINYINT
DECLARE @s VARCHAR(200)
DECLARE @Iden INT

SET @AllotmentOverbook = 0

SELECT @AllotmentNumber = AllotmentNumber
FROM ReservationMain WITH (NOLOCK)
WHERE ReferenceNumber = @AReferencenumber

IF @AllotmentNumber > 0
BEGIN
  DECLARE #db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT CONVERT(DATETIME, CASE WHEN @AGuestNumber = 0 THEN rrl.ArrivalDate ELSE r.ArrivalDate END, 112),
    CONVERT(DATETIME, CASE WHEN @AGuestNumber = 0 THEN rrl.DepartureDate ELSE r.DepartureDate END, 112),
    ISNULL(RoomTypes.RoomTypeNumber, rrl.BookedTypeNumber)
    FROM ReservationRoomLine rrl WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.LineNumber = rrl.LineNumber
      AND rrlx.ReferenceNumber = @AReferencenumber
      AND rrlx.GuestNumber = @AGuestNumber
    INNER JOIN Reservations r WITH (NOLOCK) ON r.ReferenceNumber = rrlx.ReferenceNumber
      AND r.GuestNumber = rrlx.GuestNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
    WHERE r.Status <= 5
  OPEN #db_cur

  FETCH NEXT FROM #db_cur INTO @ArrivalDT, @DepartureDT, @RoomTypeNumber

  WHILE @@FETCH_STATUS = 0 AND @AllotmentOverbook = 0
  BEGIN
    EXEC AllotmentCheckForOverbooking @AllotmentNumber, @RoomTypeNumber, @ArrivalDT, @DepartureDT, @tmpOverbook OUTPUT

    IF @tmpOverbook > 0
      SET @AllotmentOverbook = 1

    FETCH NEXT FROM #db_cur INTO @ArrivalDT, @DepartureDT, @RoomTypeNumber
  END
END

IF @AllotmentOverbook = 0
BEGIN
  DECLARE @ChangeStatusOnAllSubReservations BIT

  SELECT @ChangeStatusOnAllSubReservations = CAST(KeyValue AS BIT) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ChangeStatusOnAllSubReservations'
  SET @ChangeStatusOnAllSubReservations = ISNULL(@ChangeStatusOnAllSubReservations, 0)

  UPDATE Reservations 
  SET Status = 2 
  WHERE ReferenceNumber = @AReferencenumber 
  AND (@ChangeStatusOnAllSubReservations = 1 OR GuestNumber = @AGuestNumber)
  AND Status < 5 

  UPDATE rrl
  SET StatusRL = 2
  FROM ReservationRoomLine rrl WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.LineNumber = rrl.LineNumber
    AND rrlx.ReferenceNumber = @AReferencenumber
    AND (@ChangeStatusOnAllSubReservations = 1 OR rrlx.GuestNumber = @AGuestNumber)
  WHERE StatusRL < 5

  UPDATE ReservationConferenceLine
  SET TableStatus = 2
  WHERE ReferenceNumber = @AReferencenumber
  AND (@ChangeStatusOnAllSubReservations = 1 OR GuestNo = @AGuestNumber)
  AND TableBooking = 1
  AND TableStatus < 5
      
  SET @s = 'Status changed to Guaranteed'
  
  DECLARE @SystemDateTime DATETIME
  EXEC SystemDateTime '', @SystemDateTime OUTPUT

  INSERT INTO FollowUp ([Date], Type, Priority, State, Note, Shownname, CreatedDate, CreatedBy, UserCreated, SubType, OverWritten, ReservationStatus, HotelNumber)
  VALUES (@ASystemDate, 5, 17, 3, @s, @AShownName, @SystemDateTime, @AUserId, 0, 2, 0, 2, @AHotelNumber)

  SELECT @Iden = @@Identity

  INSERT INTO ReservationFollowUpXTab (FollowUpNumber, ReferenceNumber, GuestNumber)
  VALUES (@Iden, @AReferencenumber, @AGuestNumber)
END