USP_SALESORDER_PREREGISTEREDEVENT_UPDATEHOST

            CREATE procedure dbo.USP_SALESORDER_PREREGISTEREDEVENT_UPDATEHOST
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @SALESORDERID uniqueidentifier = null,
                @EVENTID uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null
            )
            as
            begin
                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                if @CONSTITUENTID is null
                begin
                    select @CONSTITUENTID = [SALESORDER].[CONSTITUENTID]
                    from dbo.[SALESORDER]
                    where [SALESORDER].[ID] = @SALESORDERID
                end

                declare @HOSTREGISTRANTID uniqueidentifier
                declare @HOSTWILLNOTATTEND bit = 0
                declare @HOSTHOSTSOTHERORDERS bit = 0
                select top 1 
                    @HOSTREGISTRANTID = [REGISTRANT].[GUESTOFREGISTRANTID],
                    @HOSTWILLNOTATTEND = (select [REG].[WILLNOTATTEND] from dbo.[REGISTRANT] as [REG] where [ID] =[REGISTRANT].[GUESTOFREGISTRANTID])
                from dbo.[REGISTRANT]
                inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                    on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
                inner join dbo.[SALESORDERITEMTICKET]
                    on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                inner join dbo.[SALESORDERITEM]
                    on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                where
                    [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                    [SALESORDERITEMTICKET].[EVENTID] = @EVENTID and
                    [REGISTRANT].[GUESTOFREGISTRANTID] is not null

                if @HOSTREGISTRANTID is null
                begin
                    --Host is only registrant

                    select top 1 
                        @HOSTREGISTRANTID = [REGISTRANT].[ID],
                        @HOSTWILLNOTATTEND = [REGISTRANT].[WILLNOTATTEND]
                    from dbo.[REGISTRANT]
                    inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                        on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
                    inner join dbo.[SALESORDERITEMTICKET]
                        on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                    inner join dbo.[SALESORDERITEM]
                        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                    where
                        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                        [SALESORDERITEMTICKET].[EVENTID] = @EVENTID
                end

                if (select count(distinct [SALESORDERITEM].[SALESORDERID])
                    from dbo.[SALESORDERITEM]
                    inner join dbo.[SALESORDERITEMTICKET]
                        on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                    inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID]
                    inner join dbo.[REGISTRANT]
                        on [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [REGISTRANT].[ID]
                    where 
                        [REGISTRANT].[EVENTID] = @EVENTID and
                        [REGISTRANT].[GUESTOFREGISTRANTID] = @HOSTREGISTRANTID
                    ) > 1
                        set @HOSTHOSTSOTHERORDERS = 1

                declare @CONSTITUENTREGISTRANTID uniqueidentifier
                declare @CONSTITUENTGUESTOFREGISTRANTID uniqueidentifier
                declare @CONSTITUENTREGISTRANTONORDER bit = 0
                select
                    @CONSTITUENTREGISTRANTID = [REGISTRANT].[ID],
                    @CONSTITUENTGUESTOFREGISTRANTID = [REGISTRANT].[GUESTOFREGISTRANTID]
                from dbo.[REGISTRANT]
                where 
                    [REGISTRANT].[EVENTID] = @EVENTID and
                    [REGISTRANT].[CONSTITUENTID] = @CONSTITUENTID

                select @CONSTITUENTREGISTRANTONORDER = 1
                from dbo.[SALESORDERITEMTICKETREGISTRANT]
                inner join dbo.[SALESORDERITEMTICKET]
                    on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                inner join dbo.[SALESORDERITEM]
                    on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                where 
                    [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = @CONSTITUENTREGISTRANTID and
                    [SALESORDERITEM].[SALESORDERID] = @SALESORDERID

                declare @GUESTOFREGISTRANTID uniqueidentifier
                begin try            
                    if @CONSTITUENTREGISTRANTID is not null and @CONSTITUENTGUESTOFREGISTRANTID is null
                        -- If current constituent is a host, we can put the registrants under them

                        set @GUESTOFREGISTRANTID = @CONSTITUENTREGISTRANTID 
                    else if @CONSTITUENTGUESTOFREGISTRANTID is not null and @CONSTITUENTREGISTRANTONORDER = 0
                        -- If the current constituent is a guest on another order, make their host everyone's

                        set @GUESTOFREGISTRANTID = @CONSTITUENTGUESTOFREGISTRANTID
                    else if @CONSTITUENTREGISTRANTID is not null
                    begin
                        -- Make this guest the host

                        set @GUESTOFREGISTRANTID = @CONSTITUENTREGISTRANTID
                        update dbo.[REGISTRANT]
                        set
                            [REGISTRANT].[GUESTOFREGISTRANTID] = null,
                            [REGISTRANT].[CHANGEDBYID] = @CHANGEAGENTID,
                            [REGISTRANT].[DATECHANGED] = @CURRENTDATE
                        where [REGISTRANT].[ID] = @CONSTITUENTREGISTRANTID
                    end
                    else if @HOSTWILLNOTATTEND = 1 and @HOSTHOSTSOTHERORDERS = 0
                    begin
                        -- If the old host was only a host and only the host for this order, we can replace them as the registrant constituent

                        set @GUESTOFREGISTRANTID = @HOSTREGISTRANTID
                        update dbo.[REGISTRANT]
                        set 
                            [REGISTRANT].[CONSTITUENTID] = @CONSTITUENTID,
                            [REGISTRANT].[CHANGEDBYID] = @CHANGEAGENTID,
                            [REGISTRANT].[DATECHANGED] = @CURRENTDATE
                        where [REGISTRANT].[ID] = @HOSTREGISTRANTID
                    end
                    else
                    begin
                        -- Otherwise, we need to add a host registrant record for the new constituent

                        set @GUESTOFREGISTRANTID = newid()
                        insert into dbo.[REGISTRANT]
                        (
                            [ID],
                            [EVENTID],
                            [CONSTITUENTID],
                            [WILLNOTATTEND],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values (
                            @GUESTOFREGISTRANTID,
                            @EVENTID,
                            @CONSTITUENTID,
                            1,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end

                    if @GUESTOFREGISTRANTID <> @HOSTREGISTRANTID
                    begin
                        --If there's a new host registrant, update the guests

                        update dbo.[REGISTRANT]
                        set
                            [REGISTRANT].[GUESTOFREGISTRANTID] = @GUESTOFREGISTRANTID,
                            [REGISTRANT].[DATECHANGED] = @CURRENTDATE,
                            [REGISTRANT].[CHANGEDBYID] = @CHANGEAGENTID
                        from dbo.[REGISTRANT]
                        inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                            on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
                        inner join dbo.[SALESORDERITEMTICKET]
                            on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                        inner join dbo.[SALESORDERITEM]
                            on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                        where
                            [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [REGISTRANT].[ID] and
                            [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                            [SALESORDERITEMTICKET].[EVENTID] = @EVENTID and
                            [REGISTRANT].[ID] not in (@GUESTOFREGISTRANTID, @HOSTREGISTRANTID)

                        if @HOSTHOSTSOTHERORDERS = 0
                        begin
                            update dbo.[REGISTRANT]
                            set
                                [REGISTRANT].[GUESTOFREGISTRANTID] = @GUESTOFREGISTRANTID,
                                [REGISTRANT].[DATECHANGED] = @CURRENTDATE,
                                [REGISTRANT].[CHANGEDBYID] = @CHANGEAGENTID
                            from dbo.[SALESORDERITEMTICKETREGISTRANT]
                            inner join dbo.[SALESORDERITEMTICKET]
                                on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                            inner join dbo.[SALESORDERITEM]
                                on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                            where
                                [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID] = [REGISTRANT].[ID] and
                                [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                                [SALESORDERITEMTICKET].[EVENTID] = @EVENTID and
                                [REGISTRANT].[ID] = @HOSTREGISTRANTID
                        end

                        if @CONSTITUENTREGISTRANTID is not null and @HOSTWILLNOTATTEND = 1 and @HOSTHOSTSOTHERORDERS = 0
                        begin
                            -- If we're using the new constituent's registrant record to host

                            -- and the old host is only a host and only hosting for this sales order, we should delete that registrant record

                            exec dbo.[USP_REGISTRANT_DELETEBYID_WITHCHANGEAGENTID] @HOSTREGISTRANTID, @CHANGEAGENTID;
                        end
                    end
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch
            end