USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTREGISTRANT

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTREGISTRANT
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,    
                        @CONSTITUENTID uniqueidentifier,
                        @PREFERENCES xml,
                        @NOTES nvarchar(255)
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate();

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        begin try
                            if @PREFERENCES is not null
                            begin
                                if exists
                                (
                                    select count(PREFERENCEGROUP)
                                    from
                                    (
                                        select T.items.value('(EVENTPREFERENCEGROUPID)[1]','uniqueidentifier') PREFERENCEGROUP
                                        from @PREFERENCES.nodes('/PREFERENCES/ITEM') T(items)
                                        where T.items.value('(EVENTPREFERENCEID)[1]','uniqueidentifier') is not null 
                                            and T.items.value('(EVENTPREFERENCEID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
                                    ) Result
                                    group by PREFERENCEGROUP
                                    having count(*) > 1
                                )
                                raiserror('BBERR_DUPLICATEPREFERENCES', 13, 1)
                            end

                            if exists(select ID from dbo.REGISTRANT where REGISTRANT.GUESTOFREGISTRANTID = @ID)
                                    and @CONSTITUENTID is null
                                raiserror('BBERR_NONCONSTITUENTHOST', 13, 1);

                            --If this constituent is already a host-only registrant for this event (i.e. they don't have a ticket), 
                            --we need to update their existing registrant record to indicate that they are coming and remove this (@ID) registrant
                            declare @EVENTID uniqueidentifier
                            declare @REGISTRANTHOSTID uniqueidentifier
                            select 
                                @EVENTID = REGISTRANT.EVENTID,
                                @REGISTRANTHOSTID =  REGISTRANT.GUESTOFREGISTRANTID
                            from dbo.REGISTRANT
                            where ID = @ID

                            --Only setting if the constituent is currently a host without a ticket
                            declare @CONSTITUENTHOSTREGISTRANTID uniqueidentifier
                            select @CONSTITUENTHOSTREGISTRANTID = HOST.ID
                            from dbo.REGISTRANT [HOST]
                            where
                                --Is a Host
                                HOST.GUESTOFREGISTRANTID is null and
                                --Of this event
                                HOST.EVENTID = @EVENTID and
                                --Is this constituent
                                HOST.CONSTITUENTID = @CONSTITUENTID and
                                --Isn't attending
                                not exists (
                                    select 1
                                    from dbo.SALESORDERITEMTICKETREGISTRANT
                                    where REGISTRANTID = HOST.ID
                                )

                            if @CONSTITUENTHOSTREGISTRANTID is not null and @CONSTITUENTHOSTREGISTRANTID <> @ID
                            begin
                                --We could be making this host a guest now if this registrant (@ID) being changed wasn't a guest of this host originally
                                if @CONSTITUENTHOSTREGISTRANTID <> @REGISTRANTHOSTID
                                begin
                                    --We need to update this host's guests to point to this registrant's (@ID) host first
                                    --Ultimately, this host will need to point to the new host too, but we need to do this first since there is a constraint against a guest pointing to another guest
                                    update dbo.REGISTRANT
                                    set 
                                        GUESTOFREGISTRANTID = @REGISTRANTHOSTID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CURRENTDATE
                                    where GUESTOFREGISTRANTID = @CONSTITUENTHOSTREGISTRANTID
                                end
                                --If this registrant is a host, we need to point its guests to our constituent match host
                                else if @REGISTRANTHOSTID is null
                                begin
                                    update dbo.REGISTRANT
                                    set 
                                        GUESTOFREGISTRANTID = @CONSTITUENTHOSTREGISTRANTID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CURRENTDATE
                                    where GUESTOFREGISTRANTID = @ID
                                end

                                update dbo.REGISTRANT
                                set 
                                    WILLNOTATTEND = 0,
                                    NOTES = @NOTES,
                                    --Update this host to point to the registrant's (@ID) host unless that means pointing to itself
                                    GUESTOFREGISTRANTID = case when @REGISTRANTHOSTID <> REGISTRANT.ID then @REGISTRANTHOSTID else null end,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where ID = @CONSTITUENTHOSTREGISTRANTID;

                                --Replace the current registrant's ticket with the host registrant id
                                update dbo.SALESORDERITEMTICKETREGISTRANT
                                set
                                    REGISTRANTID = @CONSTITUENTHOSTREGISTRANTID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where REGISTRANTID = @ID

                                --Remove current registrant
                                exec dbo.USP_REGISTRANT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                                --Consider this registrant the one we're updating for the remainder of the sproc
                                set @ID = @CONSTITUENTHOSTREGISTRANTID;
                            end
                            else
                            begin
                                update dbo.REGISTRANT
                                set 
                                    CONSTITUENTID = @CONSTITUENTID,
                                    NOTES = @NOTES,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where ID = @ID
                            end

                            exec dbo.USP_REGISTRANT_GETPREFERENCES_UPDATEFROMXML @ID, @PREFERENCES, @CHANGEAGENTID;
                        end try

                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0