CREATE PROCEDURE [dbo].[CreateCourseAttendeeReservation] @AMainReferenceNumber INT, @ALastname VARCHAR(50), @AFirstname VARCHAR(50), @HotelNumber INT=0, @AAttendeeReferenceNumber INT OUTPUT AS

/*
  20160208 RN - Initial version

  Changed
  TBU20170329: Added @HotelNumber
  TBU20170329: Added followup
  AHE20200611: Added ”, CreationInProgress = 1”
*/
BEGIN
  SET NOCOUNT ON

  DECLARE @NewReferenceNumber INT
  DECLARE @AllotmentNumber INT
  DECLARE @CancellationNumber INT
  DECLARE @FK_ProjectCode INT  
  DECLARE @OriginType INT
  DECLARE @ReservationType INT
  DECLARE @StaffID INT
  DECLARE @ResConfLineNumber INT
  DECLARE @NewResConfLineNumber INT
  DECLARE @NewAddressNumber INT
  DECLARE @ConferenceTypeNumber INT

  EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @NewReferenceNumber OUTPUT

  INSERT INTO ReservationReservationXtab (FK_ReferenceNumber, FK_ReferenceNumberSub) VALUES (@AMainReferenceNumber, @NewReferenceNumber)
  
  -- Copy ReservationMain contents first:
  SELECT *
  INTO #TempReservationMain
  FROM RESERVATIONMAIN WITH (NOLOCK)
  WHERE RESERVATIONMAIN.REFERENCENUMBER = @AMainReferenceNumber

  UPDATE #TempReservationMain
  SET REFERENCENUMBER = @NewReferenceNumber, CreationInProgress = 1
  WHERE REFERENCENUMBER = @AMainReferenceNumber

  INSERT INTO RESERVATIONMAIN SELECT * FROM #TempReservationMain

  DROP TABLE #TempReservationMain

  -- Copy Reservations content:
  SELECT * 
  INTO #TempReservations
  FROM Reservations WITH (NOLOCK)
  WHERE Reservations.ReferenceNumber = @AMainReferenceNumber 

  UPDATE #TempReservations
  SET ReferenceNumber = @NewReferenceNumber
  WHERE ReferenceNumber = @AMainReferenceNumber

  INSERT INTO RESERVATIONS SELECT * FROM #TempReservations

  DROP TABLE #TempReservations
  
  -- TODO: Add ReservationAddress and ReservationAddressXtab for AddressTypeNumber = 1
  -- TODO: And something about POS, Segment etc.

  EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @NewAddressNumber OUTPUT

  INSERT INTO ReservationAddress (AddressNumber, AddressType, Lastname, Firstname) VALUES (@NewAddressNumber, 1, @ALastname, @AFirstname)
  INSERT INTO RESERVATIONADDRESSXTAB (REFERENCENUMBER, GUESTNUMBER, AddressTypeNumber, ADDRESSNUMBER) VALUES (@NewReferenceNumber, 0, 1, @NewAddressNumber)

  -- Copy relevant Hall lines:
  SELECT ReservationConferenceLine.*
  INTO #TempReservationConferenceLine
  FROM ReservationConferenceLine WITH (NOLOCK)
  INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ReservationConferenceLine.BookedConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  WHERE ConferenceRoomTypes.DoCopyType = 1
    AND ReservationConferenceLine.ReferenceNumber = @AMainReferenceNumber
	AND RESERVATIONCONFERENCELINE.GuestNo = 0

  UPDATE #TempReservationConferenceLine
  SET ReferenceNumber = @NewReferenceNumber
  WHERE ReferenceNumber = @AMainReferenceNumber

  SELECT @ConferenceTypeNumber = ConferenceRoomTypeNumber FROM CONFERENCEROOMTYPES WHERE ResourceType = 16 -- htrtMainReservation

  UPDATE #TempReservationConferenceLine
  SET Adults = 1
  WHERE BookedConferenceRoomTypeNumber = @ConferenceTypeNumber

  DECLARE TempCursor CURSOR FORWARD_ONLY FOR
    SELECT ReservationConfLineNumber
	FROM #TempReservationConferenceLine WITH (NOLOCK)

  OPEN TempCursor
  FETCH NEXT FROM TempCursor INTO @ResConfLineNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    
	EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @NewResConfLineNumber OUTPUT

    UPDATE #TempReservationConferenceLine
	SET ReservationConfLineNumber = @NewResConfLineNumber
	WHERE ReservationConfLineNumber = @ResConfLineNumber
    FETCH NEXT FROM TempCursor INTO @ResConfLineNumber
  END

  CLOSE TempCursor
  DEALLOCATE TempCursor

  INSERT INTO RESERVATIONCONFERENCELINE SELECT * FROM #TempReservationConferenceLine

  SET @AAttendeeReferenceNumber = @NewReferenceNumber

  

-- FOLLOWUP ---------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @NewFollowUpNumber INT,
@SystemDateTime DATETIME,
@SystemDateStr CHAR(8),
@Text VARCHAR(8000),
@UserIDNumber INT
SELECT  @SystemDateStr = SYSTEMDATE FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record YYYYMMDD
SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDateStr, 112)+CONVERT (CHAR, GetDate(), 108)

/* Find the UserIDNumber for PicassoOnline. If not found, create it */
SELECT @UserIDNumber=UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber=5
IF @@rowcount = 0
BEGIN
  EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIDNumber OUTPUT
  --EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT
  --UPDATE PersonelIdentity SET @UserIDNumber= IdentityField = IdentityField + 1
  INSERT INTO Personel (UserIDNumber, UserIDCode, FirstName, LastName, PasswordHash, UserTypeNumber) VALUES (@UserIDNumber, 'PicassoOnline', 'PicassoOnline', 'PicassoOnline', dbo.GetPasswordHash('Picasso'), 5 /*utPicassoOnline*/)
END

/* Create log */
set @Text = 'Created'
INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType, HotelNumber)
VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 1, @HotelNumber)
SELECT @NewFollowUpNumber = @@identity
INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@AAttendeeReferenceNumber, 0, @NewFollowUpNumber)

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

END;


