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

-- Created by SG, 2015-05-08
-- Updates Hall Namelist allowing changed no. of participants (PAX)
-- Called once per hall in the reservation.
-- @RoomLineNumber will be 0 for a new online reservation allowing only one RoomLine
-- Calls: OnlineUpdateParticipants
--        SplitListMax
-- Can't use table value parameter because we need to be compatible with SQL-Server 2005

-- Updates:
-- 2016-01-20/SG: AutoComfirmed added to Reservations, new optional parameter @AHotelNumber
-- 2016-01-19/SG: IsNameAutoGenerated has been added to ReservationAddress
-- 2015-12-23/sg: Corrected deletes when there are more than one hall in res.
-- 2015-07-02/sg: Many changes!
-- 2015-06-22/sg: Added @RoomLineNumber

Declare @HotelCustomerNumber int
Declare @AutoConfirmationDefaultValue Char(1);

Select @HotelCustomerNumber = CustomerNumber FROM Customer WHERE CustomerTypeNumber = 7 AND HotelNumber = @HotelNumber

SELECT  @AutoConfirmationDefaultValue = dbo.GetHotelSetupKeyValue('AutoConfirmationDefaultValue',@HotelCustomerNumber)
Set @AutoConfirmationDefaultValue = ISNULL(@AutoConfirmationDefaultValue, '1');


--Transform comma separated list of participants to a temp table
DECLARE @ParticipantRaw VARCHAR(max) --Fields separated with ##
DECLARE @ParticipantNew VARCHAR(max) --Names 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 @HasNameList INT --No of pax in excisting namelist
DECLARE @ConfLineNumber INT

SELECT @ConfLineNumber = ReservationConfLineNumber
FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
WHERE ReferenceNumber = @Referencenumber
	AND (
		ReservationConfLineNumber = @RoomLineNumber
		OR @RoomLineNumber = 0
		)

SELECT @HasNameList = Count(*)
FROM RESERVATIONS res WITH (NOLOCK)
INNER JOIN ReservationMeetingXTab remX WITH (NOLOCK) ON res.REFERENCENUMBER = remX.ReferenceNumber
	AND res.GUESTNUMBER = remX.GuestNumber
WHERE res.ReferenceNumber = @Referencenumber
	AND res.GuestNumber > 0
	AND remX.ReservationConfLineNumber = @ConfLineNumber

PRINT '@HasNameList: ' + convert(CHAR(2), @HasNameList) + ' PAX=' + convert(CHAR(3), @PAX);

BEGIN TRANSACTION

BEGIN TRY
	--Delete excisting namelist for guest > @PAX
	IF @PAX < @HasNameList --Antal er reduceret 
	BEGIN
		DECLARE @AddrNumbers VARCHAR(max)
		DECLARE @GuestNumbers VARCHAR(max) 

		SELECT TOP (@HasNameList - @PAX) @AddrNumbers = COALESCE(@AddrNumbers + ',', '') + convert(VARCHAR(6), AddressNumber),
		                                 @GuestNumbers = COALESCE(@GuestNumbers + ',', '') + convert(VARCHAR(6), resAx.GUESTNUMBER)
		FROM RESERVATIONADDRESSXTAB resAX
		INNER JOIN ReservationMeetingXTab remX WITH (NOLOCK) ON resAX.REFERENCENUMBER = remX.ReferenceNumber
			AND resAX.GUESTNUMBER = remX.GuestNumber
		WHERE resAX.REFERENCENUMBER = @Referencenumber
			AND AddressTypeNumber = 1
			AND resAx.GUESTNUMBER > @PAX
			AND remX.ReservationConfLineNumber = @ConfLineNumber
		ORDER BY resAx.GUESTNUMBER DESC	

		PRINT '@AddrNumbers=' + @AddrNumbers + ' @GuestNumbers=' + @GuestNumbers

		DELETE RESERVATIONADDRESSXTAB
		FROM RESERVATIONADDRESSXTAB resAX
		WHERE AddressTypeNumber = 1
			AND AddressNumber IN (
				SELECT *
				FROM SplitListMax(@AddrNumbers)
				)

		PRINT 'Efter delete RESERVATIONADDRESSXTAB'

		DELETE
		FROM ReservationAddress
		WHERE ADDRESSTYPE = 1
			AND AddressNumber IN (
				SELECT *
				FROM SplitListMax(@AddrNumbers)
				)

		PRINT 'Efter delete ReservationAddress'

		DELETE ReservationFollowUpXTab
		FROM ReservationFollowUpXTab resFX
		INNER JOIN ReservationMeetingXTab remX WITH (NOLOCK) ON resFX.REFERENCENUMBER = remX.ReferenceNumber
			AND resFX.GUESTNUMBER = remX.GuestNumber
		WHERE resFX.ReferenceNumber = @Referencenumber
			AND resFX.GuestNumber in (SELECT * FROM SplitListMax(@GuestNumbers)) -- > @PAX
			AND remX.ReservationConfLineNumber = @RoomLineNumber

		DELETE FollowUp
		FROM FollowUp fu
		INNER JOIN ReservationFollowUpXTab resFX ON fu.FollowUpNumber = resFX.FollowUpNumber
		INNER JOIN ReservationMeetingXTab remX WITH (NOLOCK) ON resFX.REFERENCENUMBER = remX.ReferenceNumber
			AND resFX.GUESTNUMBER = remX.GuestNumber
		WHERE resFX.ReferenceNumber = @Referencenumber
			AND resFX.GuestNumber in (SELECT * FROM SplitListMax(@GuestNumbers))-- > @PAX
			AND remX.ReservationConfLineNumber = @RoomLineNumber

		PRINT 'Efter delete FollowUp'

		DELETE ReservationMeetingXTab
		FROM ReservationMeetingXTab remX
		WHERE remX.ReferenceNumber = @Referencenumber
			AND remX.ReservationConfLineNumber = @RoomLineNumber
			AND remX.GuestNumber in (SELECT * FROM SplitListMax(@GuestNumbers))-- > @PAX

		PRINT 'Efter delete ReservationMeetingXTab'

        DELETE RESERVATIONS
        FROM RESERVATIONS res
        WHERE res.ReferenceNumber = @Referencenumber
	    AND res.GuestNumber in (SELECT * FROM SplitListMax(@GuestNumbers)) -- > @PAX			

		PRINT 'Efter delete Reservations'
	END --Antal er reduceret

	DECLARE @GuestNumberOffset INT
	DECLARE @DefaultName VARCHAR(50)
	DECLARE @AddressNumber INT

	SET @GuestNumberOffset = 0

	-- Pax øget eller der er slet ingen navneliste
	IF @PAX > @HasNameList
	BEGIN
		-- er der andre halls?
		SELECT @GuestNumberOffset = IsNull(Max(remX.GUESTNUMBER), 0)
		FROM RESERVATIONS res WITH (NOLOCK)
		INNER JOIN RESERVATIONADDRESSXTAB resax WITH (NOLOCK) ON resax.REFERENCENUMBER = res.ReferenceNumber
		INNER JOIN ReservationMeetingXTab remX WITH (NOLOCK) ON remX.ReferenceNumber = resax.ReferenceNumber
		--                                                and remX.ReservationConfLineNumber <> @ConfLineNumber
		WHERE res.ReferenceNumber = @Referencenumber
			AND remX.GuestNumber = resax.GUESTNUMBER
			AND res.GUESTNUMBER = 1
			AND AddressTypeNumber = 1
			AND resax.GUESTNUMBER > 0

		PRINT '@GuestNumberOffset=' + convert(CHAR(2), @GuestNumberOffset);

        Declare @IsNameAuto INT;
		DECLARE @GuestNumberActual INT;

		SET @GuestNumberActual = @GuestNumberOffset + 1

		DECLARE @GuestCountIndex INT

		SET @GuestCountIndex = @HasNameList --

		-- Build Empty namelist for all new participants
		WHILE @GuestCountIndex < @PAX
		BEGIN
			PRINT '  GuestNumberActual=' + convert(CHAR(2), @GuestNumberActual)

			INSERT INTO RESERVATIONS (
				REFERENCENUMBER
				,GUESTNUMBER
				,[Status]
				,POINTOFSALE
				,AutoConfirmed
				)
			VALUES (
				@Referencenumber
				,@GuestNumberActual
				,2
				,1
				,@AutoConfirmationDefaultValue
				)

			INSERT INTO ReservationMeetingXTab (
				ReferenceNumber
				,GuestNumber
				,ReservationConfLineNumber
				,Adults
				,Children
				,Children2
				,PaysAdults
				,PaysChildren
				,PaysChildren2
				)
			VALUES (
				@Referencenumber
				,@GuestNumberActual
				,@ConfLineNumber
				,1
				,0
				,0
				,0
				,0
				,0
				)

			EXEC GetNextNumber 'ReservationAddressIdentity'
				,1
				,@AddressNumber OUTPUT

			SET @DefaultName = 'Guest/' + CAST(@GuestNumberActual AS VARCHAR(50))

			INSERT INTO RESERVATIONADDRESS (
				AddressNumber
				,ADDRESSTYPE
				,LASTNAME
				,IsNameAutoGenerated
				)
			VALUES (
				@AddressNumber
				,1
				,@DefaultName
				,1
				)

			INSERT INTO RESERVATIONADDRESSXTAB (
				REFERENCENUMBER
				,GUESTNUMBER
				,ADDRESSNUMBER
				,AddressTypeNumber
				)
			VALUES (
				@Referencenumber
				,@GuestNumberActual
				,@AddressNumber
				,1
				)
            If CHARINDEX('Guest/', @Lastname) > 0
            Begin
              Set @IsNameAuto = 1
            End Else
            Begin
              Set @IsNameAuto = 0
            End
            Print 'Lastname=' + @Lastname + ' IsNameAuto=' + convert(char(1), @IsNameAuto)
			UPDATE RESERVATIONADDRESS
			SET FIRSTNAME = @Firstname
				,LASTNAME = @Lastname
				,ADDRESS3 = @CompanyName
				,ADDRESS1 = @Address
				,ZIPCODE = @Zipcode
				,CITY = @City
				,PHONE = @Phone
				,EMAIL = @Email
				,IsNameAutoGenerated = @IsNameAuto
			WHERE AddressNumber = (
					SELECT ADDRESSNUMBER
					FROM RESERVATIONS res WITH (NOLOCK)
					INNER JOIN RESERVATIONADDRESSXTAB resax WITH (NOLOCK) ON resax.REFERENCENUMBER = res.ReferenceNumber
					WHERE res.ReferenceNumber = @Referencenumber
						AND res.GUESTNUMBER = 0
						AND AddressTypeNumber = 1
						AND resax.GUESTNUMBER = @GuestNumberActual
					)

			PRINT 'Defaultname=' + @DefaultName + ' Lastname=' + @LastName
			PRINT '@GuestCountIndex=' + convert(CHAR(2), @GuestCountIndex)
			PRINT '@@GuestNumberActual=' + convert(CHAR(2), @GuestNumberActual)
			PRINT '@ConfLineNumber=' + convert(CHAR(6), @ConfLineNumber)

			SET @GuestCountIndex = @GuestCountIndex + 1
			SET @GuestNumberActual = @GuestNumberActual + 1
		END
	END -- Pax er øget

	UPDATE RESERVATIONCONFERENCELINE
	SET Adults = @PAX
	WHERE ReferenceNumber = @Referencenumber
		AND ReservationConfLineNumber = @ConfLineNumber

	-- Skal være summen af adults
	DECLARE @TotalPax INT

	SELECT @TotalPax = SUM(Adults) --Adults er blevet opdateret allerede?
	FROM RESERVATIONCONFERENCELINE
	WHERE ReferenceNumber = @Referencenumber -- (alle) AND (ReservationConfLineNumber = @RoomLineNumber OR @RoomLineNumber = 0)

	UPDATE RESERVATIONS
	SET Adults = @TotalPax
	WHERE ReferenceNumber = @Referencenumber
		AND GuestNumber = 0

	PRINT 'TotalPax=' + convert(CHAR(2), @TotalPax)


	IF @PAX < @HasNameList --Antal er reduceret
	BEGIN
		--indsætte "manglende" records i #ParticipantList med Lastname = "Guest/nn"
		DECLARE @G INT

		SET @G = @PAX + 1

		WHILE @G < @HasNameList
		BEGIN
			INSERT INTO #ParticipantList (
				Lastname
				,Guestnumber
				)
			VALUES (
				'Guest/' + Convert(CHAR(2), @G)
				,Convert(CHAR(2), @G)
				)

			SET @G = @G + 1
		END
	END
	--End of create empty namelist	  	  

    --Opdatere navneliste
	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

	DECLARE @GuestNumberCorr INT
	DECLARE @i INT
	SET @i = 1
    --Hvis der er slettet gæst(er) i forudgående lokaler, skal gæstenummer korrigeres
    Declare @corr int
    Declare @found int
    Set @corr = -1
    Set @found = 0
    WHILE (@found = 0)
    Begin
        Set @corr = @corr + 1
        Select @found = COUNT(*) FROM RESERVATIONS WHERE ReferenceNumber = @Referencenumber and GuestNumber = @Guestnumber + @corr
        Print 'Find forskudt gæstenummer: @Guestnumber=' + convert(char(2), @Guestnumber) + ' @found=' + convert(char(2), @found) + ' @corr=' + convert(char(2), @corr)
    End

	WHILE (@@FETCH_STATUS <> - 1)
	BEGIN
		PRINT convert(CHAR(2), @i) + ': @GuestNumber=' + convert(CHAR(2), @GuestNumber) + ' @GuestNumberOffset=' + convert(CHAR(2), @GuestNumberOffset)

		IF @HasNameList < @i --  = 0
		BEGIN
			SET @GuestNumberCorr = @GuestNumberOffset + @i
		END
		ELSE
		BEGIN
			SET @GuestNumberCorr = @GuestNumber + @corr
		END

		PRINT 'Call OnlineUpdateParticipants: Lastname=' + @Lastname + ', @GuestNumberCorr=' + convert(CHAR(6), @GuestNumberCorr)

		EXEC [dbo].[OnlineUpdateParticipants] @Referencenumber = @Referencenumber
			,@Firstname = @Firstname
			,@Lastname = @Lastname
			,@CompanyName = @CompanyName
			,@Fullname = @Fullname
			,@Address = @Address
			,@Zipcode = @Zipcode
			,@City = @City
			,@Phone = @Phone
			,@Email = @Email
			,@Guestnumber = @GuestNumberCorr
			,@RoomLineNumber = @RoomLineNumber

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

		SET @i = @i + 1
	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
