USP_RESCHEDULETICKETS_UPDATEREGISTRANTS

CREATE procedure dbo.USP_RESCHEDULETICKETS_UPDATEREGISTRANTS
(
    @REGISTRANTS xml,
    @CHANGEAGENTID uniqueidentifier
)
as
begin
    declare @CURRENTTIME datetime = getdate();

    declare @REGISTRANTSTOUPDATE table (ID uniqueidentifier, ORIGINALEVENTID uniqueidentifier, NEWEVENTID uniqueidentifier, HOSTID uniqueidentifier, CONSTITUENTID uniqueidentifier, HISTORICALCOPYID uniqueidentifier);
    insert into @REGISTRANTSTOUPDATE
    select
        REGISTRANT.ID,
        REGISTRANT.EVENTID,
        T.item.value('(@NEWEVENTID)[1]','uniqueidentifier'),
        REGISTRANT.GUESTOFREGISTRANTID,
        REGISTRANT.CONSTITUENTID,
        newid()
    from @REGISTRANTS.nodes('/ITEMS/ITEM') T(item)
        inner join dbo.REGISTRANT on REGISTRANT.ID = T.item.value('(@REGISTRANTID)[1]','uniqueidentifier')
    where REGISTRANT.EVENTID <> T.item.value('(@NEWEVENTID)[1]','uniqueidentifier')
        and T.item.value('(@NEWEVENTID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000';

    begin try

        declare @ID uniqueidentifier, @ORIGINALEVENTID uniqueidentifier, @NEWEVENTID uniqueidentifier, @HOSTID uniqueidentifier, @CONSTITUENTID uniqueidentifier, @HISTORICALCOPYID uniqueidentifier;

        -- Reschedule hosts before guests.

        declare host_cur cursor local fast_forward for (select R.ID, R.ORIGINALEVENTID, R.NEWEVENTID, R.CONSTITUENTID, R.HISTORICALCOPYID from @REGISTRANTSTOUPDATE R where HOSTID is null);
        open host_cur;
        fetch next from host_cur into @ID, @ORIGINALEVENTID, @NEWEVENTID, @CONSTITUENTID, @HISTORICALCOPYID;
        while @@FETCH_STATUS = 0
        begin
            -- Try to find an existing registrant on the new event for this constituent.

            declare @WNAREGISTRANTONNEWEVENT uniqueidentifier = (select ID from dbo.REGISTRANT where CONSTITUENTID = @CONSTITUENTID and EVENTID = @NEWEVENTID and WILLNOTATTEND = 1 and ISCANCELLED = 0);

            if @WNAREGISTRANTONNEWEVENT is not null
            begin
                -- Cancel the hidden record so we can move the original registration without violating any constraints.

                update dbo.REGISTRANT
                set ISCANCELLED = 1, DATECHANGED = @CURRENTTIME, CHANGEDBYID = @CHANGEAGENTID
                where ID = @WNAREGISTRANTONNEWEVENT;
            end

            update dbo.REGISTRANT
            set EVENTID = @NEWEVENTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
            where ID = @ID;

            -- Insert historical cancelled registrant.

            insert into dbo.REGISTRANT
                (ID, EVENTID, ISCANCELLED, USERMARKEDATTENDANCE, ATTENDED, WILLNOTATTEND, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, GUESTOFREGISTRANTID, ISWALKIN, NOTES, ONLINEREGISTRANT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                @HISTORICALCOPYID, @ORIGINALEVENTID, 1, 1, 0, 0, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, GUESTOFREGISTRANTID, ISWALKIN, NOTES, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
            from dbo.REGISTRANT
            where REGISTRANT.ID = @ID;

            -- Add historical registrant to the same SOITR that the current registrant is already linked to.

            insert into dbo.SALESORDERITEMTICKETREGISTRANT
                (ID, SALESORDERITEMTICKETID, REGISTRANTID, ISHISTORICAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                newid(), SALESORDERITEMTICKETID, @HISTORICALCOPYID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
            from dbo.SALESORDERITEMTICKETREGISTRANT
            where REGISTRANTID = @ID;

            -- Update host ID for guests who aren't being rescheduled or who referenced the hidden "Will not attend" registrant.

            update GUEST
            set GUESTOFREGISTRANTID = @HISTORICALCOPYID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
            from dbo.REGISTRANT GUEST
                left join @REGISTRANTSTOUPDATE GUESTTOUPDATE on GUESTTOUPDATE.ID = GUEST.ID
            where (GUEST.GUESTOFREGISTRANTID = @ID and GUESTTOUPDATE.ID is null)
                or GUEST.GUESTOFREGISTRANTID = @WNAREGISTRANTONNEWEVENT;

            fetch next from host_cur into @ID, @ORIGINALEVENTID, @NEWEVENTID, @CONSTITUENTID, @HISTORICALCOPYID;
        end
        close host_cur;
        deallocate host_cur;

        declare guest_cur cursor local fast_forward for (select R.ID, R.ORIGINALEVENTID, R.NEWEVENTID, R.HOSTID, R.CONSTITUENTID, R.HISTORICALCOPYID from @REGISTRANTSTOUPDATE R where HOSTID is not null);
        open guest_cur;
        fetch next from guest_cur into @ID, @ORIGINALEVENTID, @NEWEVENTID, @HOSTID, @CONSTITUENTID, @HISTORICALCOPYID;
        while @@FETCH_STATUS = 0
        begin
            -- Find the host constituent as a registrant on the new event.

            declare @NEWHOSTID uniqueidentifier = (
                select top 1 NEWHOST.ID from dbo.REGISTRANT ORIGINALHOST
                    inner join dbo.REGISTRANT NEWHOST on NEWHOST.CONSTITUENTID = ORIGINALHOST.CONSTITUENTID
                where ORIGINALHOST.ID = @HOSTID
                    and NEWHOST.EVENTID = @NEWEVENTID
                order by NEWHOST.ISCANCELLED, NEWHOST.WILLNOTATTEND
            );

            if @NEWHOSTID is null and @CONSTITUENTID is null
            begin
                -- The host is not marked "Will not attend" on the original event, and is not being rescheduled to the unnamed guest's event.

                -- We need to create a WNA host to link the unnamed registrant to.

                -- (if this registrant does have a name, it becomes its own host; this is only a problem when the guest has no name).

                set @NEWHOSTID = newid();

                insert into dbo.REGISTRANT
                    (ID, EVENTID, ISCANCELLED, USERMARKEDATTENDANCE, ATTENDED, WILLNOTATTEND, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, GUESTOFREGISTRANTID, ISWALKIN, NOTES, ONLINEREGISTRANT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    @NEWHOSTID, @NEWEVENTID, 0, 0, 0, 1, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, null, ISWALKIN, NOTES, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
                from dbo.REGISTRANT HOST
                where HOST.ID = @HOSTID;
            end

            -- Update guest registrant with new event and host.

            update dbo.REGISTRANT
            set EVENTID = @NEWEVENTID, GUESTOFREGISTRANTID = @NEWHOSTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTTIME
            where ID = @ID;

            -- Insert historical cancelled registrant.

            insert into dbo.REGISTRANT
                (ID, EVENTID, ISCANCELLED, USERMARKEDATTENDANCE, ATTENDED, WILLNOTATTEND, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, GUESTOFREGISTRANTID, ISWALKIN, NOTES, ONLINEREGISTRANT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                @HISTORICALCOPYID, @ORIGINALEVENTID, 1, 1, 0, 0, BENEFITSWAIVED, CONSTITUENTID, CUSTOMIDENTIFIER, EVENTSEATINGNOTE, @HOSTID, ISWALKIN, NOTES, ONLINEREGISTRANT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
            from dbo.REGISTRANT
            where REGISTRANT.ID = @ID;

            -- Add historical registrant to the same SOITR that the current registrant is already linked to.

            insert into dbo.SALESORDERITEMTICKETREGISTRANT
                (ID, SALESORDERITEMTICKETID, REGISTRANTID, ISHISTORICAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                newid(), SALESORDERITEMTICKETID, @HISTORICALCOPYID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTTIME, @CURRENTTIME
            from dbo.SALESORDERITEMTICKETREGISTRANT
            where REGISTRANTID = @ID;

            fetch next from guest_cur into @ID, @ORIGINALEVENTID, @NEWEVENTID, @HOSTID, @CONSTITUENTID, @HISTORICALCOPYID;
        end
            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

            close guest_cur;
            deallocate guest_cur;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        select 1;
    end catch
end