CREATE PROCEDURE [dbo].[OnlineUpdateParticipantsAll] @Referencenumber INT, @RoomLineNumber INT = 0
	,@PAX INT
	,@ParticipantList VARCHAR(max) --Participants separated with , Fields separated with ##
AS
SET NOCOUNT ON;

-- Created by SG, 2015-05-08
-- Updates Hall Namelist allowing changed no. of participants
-- @RoomLineNumber will be 0 for a new online reservation allowing only one RoomLine

-- Calls: OnlineUpdateParticipants1
--        SplitListMax
-- Can't use table value parameter because we need to be compatible with SQL-Server 2005
-- Updates:
-- 2015-06-22/sg: Added @RoomLineNumber

DECLARE @ParticipantRaw VARCHAR(max) --Fields separated with ##
DECLARE @ParticipantNew VARCHAR(max) --Fields separated with ,

CREATE TABLE #ParticipantList (
	Firstname VARCHAR(50)
	,Lastname VARCHAR(50)
	,CompanyName VARCHAR(50)
	,Fullname VARCHAR(100)
	,Address VARCHAR(100)
	,Zipcode VARCHAR(50)
	,City VARCHAR(50)
	,Phone VARCHAR(50)
	,Email VARCHAR(100)
	,Guestnumber INT
	)

DECLARE @Firstname VARCHAR(50)
	,@Lastname VARCHAR(50)
	,@CompanyName VARCHAR(50)
	,@Fullname VARCHAR(100)
	,@Address VARCHAR(100)
	,@Zipcode VARCHAR(50)
	,@City VARCHAR(50)
	,@Phone VARCHAR(50)
	,@Email VARCHAR(100)
	,@Guestnumber INT

DECLARE ParticipantCur CURSOR FORWARD_ONLY
FOR
SELECT *
FROM SplitListMax(@ParticipantList)

OPEN ParticipantCur

FETCH NEXT
FROM ParticipantCur
INTO @ParticipantRaw

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
	SET @ParticipantNew = Replace(@ParticipantRaw, '##', ',')

	DECLARE FieldsCur CURSOR FORWARD_ONLY
	FOR
	SELECT *
	FROM SplitListMax(@ParticipantNew)

	OPEN FieldsCur

	FETCH NEXT
	FROM FieldsCur
	INTO @Firstname

	FETCH NEXT
	FROM FieldsCur
	INTO @Lastname

	FETCH NEXT
	FROM FieldsCur
	INTO @CompanyName

	FETCH NEXT
	FROM FieldsCur
	INTO @Fullname

	FETCH NEXT
	FROM FieldsCur
	INTO @Address

	FETCH NEXT
	FROM FieldsCur
	INTO @Zipcode

	FETCH NEXT
	FROM FieldsCur
	INTO @City

	FETCH NEXT
	FROM FieldsCur
	INTO @Phone

	FETCH NEXT
	FROM FieldsCur
	INTO @Email

	FETCH NEXT
	FROM FieldsCur
	INTO @Guestnumber

	CLOSE FieldsCur

	DEALLOCATE FieldsCur

	INSERT INTO #ParticipantList (
		Firstname
		,Lastname
		,CompanyName
		,Fullname
		,Address
		,Zipcode
		,City
		,Phone
		,Email
		,Guestnumber
		)
	VALUES (
		@Firstname
		,@Lastname
		,@CompanyName
		,@Fullname
		,@Address
		,@Zipcode
		,@City
		,@Phone
		,@Email
		,@Guestnumber
		)

	FETCH NEXT
	FROM ParticipantCur
	INTO @ParticipantRaw
END

CLOSE ParticipantCur
DEALLOCATE ParticipantCur

DECLARE @AddresNumbers CHAR(100)
DECLARE @AddrNo VARCHAR(10)

SET @AddresNumbers = ''

DECLARE AddrNumbers CURSOR FORWARD_ONLY
FOR
SELECT AddressNumber
FROM RESERVATIONADDRESSXTAB
WHERE REFERENCENUMBER = @Referencenumber
	AND AddressTypeNumber = 1
	AND GUESTNUMBER > 0

OPEN AddrNumbers

FETCH NEXT
FROM AddrNumbers
INTO @AddrNo

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
	IF @AddresNumbers <> ''
	BEGIN
		SET @AddresNumbers = @AddresNumbers + ',';
	END

	SET @AddresNumbers = @AddresNumbers + @AddrNo

	FETCH NEXT
	FROM AddrNumbers
	INTO @AddrNo
END

CLOSE AddrNumbers

DEALLOCATE AddrNumbers

PRINT @AddresNumbers

BEGIN TRANSACTION

BEGIN TRY
	DELETE
	FROM RESERVATIONADDRESSXTAB
	WHERE REFERENCENUMBER = @Referencenumber
		AND AddressTypeNumber = 1
		AND GUESTNUMBER > 0

	IF @AddresNumbers <> ''
	BEGIN
		DELETE
		FROM ReservationAddress
		WHERE ADDRESSTYPE = 1
			AND AddressNumber IN (@AddresNumbers)
	END

	DELETE
	FROM ReservationMeetingXTab
	WHERE ReferenceNumber = @Referencenumber

    DELETE FROM ReservationFollowUpXTab where ReferenceNumber = @Referencenumber 
           AND GuestNumber > 0

    DELETE from FollowUp 
    WHERE FollowUpNumber in (SELECT x.FollowUpNumber 
                             FROM ReservationFollowUpXTab x 
                             WHERE x.ReferenceNumber = @Referencenumber and x.GuestNumber > 0)
	DELETE
	FROM RESERVATIONS
	WHERE ReferenceNumber = @Referencenumber
		AND GuestNumber > 0

    -- !! skal være summen af adults
    UPDATE RESERVATIONS
	  SET Adults = @PAX
	  WHERE ReferenceNumber = @Referencenumber
	  	  AND GuestNumber = 0
    -- !! Vi skal kende 
	UPDATE RESERVATIONCONFERENCELINE
	SET Adults = @PAX
	WHERE ReferenceNumber = @Referencenumber AND (ReservationConfLineNumber = @RoomLineNumber OR @RoomLineNumber = 0)

	DECLARE ParticipantCur CURSOR FORWARD_ONLY
	FOR
	SELECT Firstname
		,Lastname
		,CompanyName
		,Fullname
		,Address
		,Zipcode
		,City
		,Phone
		,Email
		,Guestnumber
	FROM #ParticipantList

	OPEN ParticipantCur

	FETCH NEXT
	FROM ParticipantCur
	INTO @Firstname
		,@Lastname
		,@CompanyName
		,@Fullname
		,@Address
		,@Zipcode
		,@City
		,@Phone
		,@Email
		,@Guestnumber

	WHILE (@@FETCH_STATUS <> - 1)
	BEGIN
		EXEC [dbo].[OnlineUpdateParticipants1] @Referencenumber = @Referencenumber
			,@Firstname = @Firstname
			,@Lastname = @Lastname
			,@CompanyName = @CompanyName
			,@Fullname = @Fullname
			,@Address = @Address
			,@Zipcode = @Zipcode
			,@City = @City
			,@Phone = @Phone
			,@Email = @Email
			,@Guestnumber = @Guestnumber

		FETCH NEXT
		FROM ParticipantCur
		INTO @Firstname
			,@Lastname
			,@CompanyName
			,@Fullname
			,@Address
			,@Zipcode
			,@City
			,@Phone
			,@Email
			,@Guestnumber
	END

	CLOSE ParticipantCur
	DEALLOCATE ParticipantCur
	DROP TABLE #ParticipantList
END TRY

BEGIN CATCH
	SELECT ERROR_LINE() AS ErrorLine
		,ERROR_MESSAGE() AS ErrorMessage;

	IF @@TRANCOUNT > 0
	BEGIN
	    Print 'ROLLBACK TRANSACTION'
		ROLLBACK TRANSACTION;
		Return 0
	END	
END CATCH

IF @@TRANCOUNT > 0
BEGIN
    Print 'COMMIT TRANSACTION'
	COMMIT TRANSACTION;
	Return 1
END