USP_MERGETASK_CONSTITUENTSPONSORSHIP

CREATE procedure dbo.USP_MERGETASK_CONSTITUENTSPONSORSHIP
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    declare @CHANGEDATE datetime = getdate();

    -- cannot merge constituents if they both have an active/pending sponsorship to the same opportunity

    if exists(select SPONSORSHIPOPPORTUNITYID
              from dbo.SPONSORSHIP
              where CONSTITUENTID = @SOURCEID
              and STATUSCODE in(0,1)
              intersect
              select SPONSORSHIPOPPORTUNITYID
              from dbo.SPONSORSHIP
              where CONSTITUENTID = @TARGETID
              and STATUSCODE in(0,1))
        raiserror('Cannot merge constituents that both have active or pending sponsorships to the same sponsorship opportunity.', 13, 1)

    declare @CONTEXTCACHE varbinary(128);
    set @CONTEXTCACHE = CONTEXT_INFO();

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

    begin try
        -- update all sponsorship RGs for the source constituent, both gifts and non-gifts

        update dbo.REVENUE
        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where ID in(select REVENUEID
                    from dbo.REVENUESPLIT
                    inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
                    where SPONSORSHIP.CONSTITUENTID = @SOURCEID
                    union all
                    select REVENUEID
                    from dbo.REVENUESPLIT
                    inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
                    inner join dbo.SPONSORSHIPTRANSACTION on SPONSORSHIPTRANSACTION.TARGETSPONSORSHIPID = SPONSORSHIP.ID
                    where SPONSORSHIPTRANSACTION.GIFTFINANCIALSPONSORID = @SOURCEID)
        and CONSTITUENTID = @SOURCEID;

        update dbo.SPONSORSHIP
        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where CONSTITUENTID = @SOURCEID;

        update dbo.SPONSORSHIPCOMMITMENT
        set CONSTITUENTID = @TARGETID,
            COMMITMENTSEQUENCE = -COMMITMENTSEQUENCE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
        where CONSTITUENTID = @SOURCEID;

        -- renumber the commitments

        update dbo.SPONSORSHIPCOMMITMENT
        set COMMITMENTSEQUENCE = X.SEQ, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        from (
            select SPONSORSHIPCOMMITMENT.ID,
                   row_number() over (order by SPONSORSHIPTRANSACTION.TRANSACTIONDATE) SEQ
            from dbo.SPONSORSHIPCOMMITMENT
            inner join dbo.SPONSORSHIPTRANSACTION on SPONSORSHIPTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIPCOMMITMENT.ID and SPONSORSHIPTRANSACTION.TRANSACTIONSEQUENCE = 1
            where SPONSORSHIPCOMMITMENT.CONSTITUENTID = @TARGETID) X
        where SPONSORSHIPCOMMITMENT.ID = X.ID; 

        -- if corresponding and financial sponsors are now the same constituent, clear out gift financial sponsor

        update ST
        set GIFTFINANCIALSPONSORID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        from dbo.SPONSORSHIPTRANSACTION ST
        inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = ST.TARGETSPONSORSHIPID
        where ST.GIFTFINANCIALSPONSORID in(@SOURCEID,@TARGETID)
        and SPONSORSHIP.CONSTITUENTID = @TARGETID;

        -- fix any remaining gift financial sponsors

        update dbo.SPONSORSHIPTRANSACTION
        set GIFTFINANCIALSPONSORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where GIFTFINANCIALSPONSORID = @SOURCEID;

        -- if corresponding and financial sponsors are now the same constituent, clear out gift financial sponsor

        -- otherwise update gift financial sponsor

        update SP
        set GIFTFINANCIALSPONSORID = case when SPONSORSHIP.CONSTITUENTID = @TARGETID then null else @TARGETID end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
        from dbo.SPONSORSHIPPAYMENT SP
        inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SP.SPONSORSHIPID
        where SP.GIFTFINANCIALSPONSORID in(@SOURCEID,@TARGETID);

        ------------------------------------------------------------------------

        -- SPONSOR

        if not exists(select 'x' from dbo.SPONSOR where ID = @TARGETID)
        begin
            insert into dbo.SPONSOR
            (
                ID,
                DONOTTERMINATE,
                UNIQUEOPPORTUNITIESFORGIFTDONOR,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                @TARGETID,
                DONOTTERMINATE,
                UNIQUEOPPORTUNITIESFORGIFTDONOR,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from dbo.SPONSOR
            where ID = @SOURCEID;
        end
        else
        begin
            -- update target fields w/ source values if target doesn't have changes

            update [TARGET]
            set DONOTTERMINATE = case [TARGET].DONOTTERMINATE when 0 then [SOURCE].DONOTTERMINATE else [TARGET].DONOTTERMINATE end,
                UNIQUEOPPORTUNITIESFORGIFTDONOR = case [TARGET].UNIQUEOPPORTUNITIESFORGIFTDONOR when 0 then [SOURCE].UNIQUEOPPORTUNITIESFORGIFTDONOR else [TARGET].UNIQUEOPPORTUNITIESFORGIFTDONOR end,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE
            from dbo.SPONSOR [TARGET]
            inner join dbo.SPONSOR [SOURCE] on [SOURCE].ID = @SOURCEID
            where [TARGET].ID = @TARGETID;
        end

        -- delete source record

        exec dbo.USP_SPONSOR_DELETEBYID_WITHCHANGEAGENTID @SOURCEID, @CHANGEAGENTID

        ------------------------------------------------------------------------

        -- SPONSORDATERANGE


        exec dbo.USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES @TARGETID, -1, @CHANGEAGENTID

        -- cache current context

        if @CHANGEAGENTID is not null
            set CONTEXT_INFO @CHANGEAGENTID

        -- remove sponsordaterange records from both constituents

        delete from dbo.SPONSORDATERANGE
        where CONSTITUENTID = @SOURCEID;

        -- restore cached context

        if not @CONTEXTCACHE is null
            set CONTEXT_INFO @CONTEXTCACHE

        ------------------------------------------------------------------------

        -- other tables


        update dbo.SPONSORSHIPOPPORTUNITYNOTE
        set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where AUTHORID = @SOURCEID;

        update dbo.SPONSORSHIPOPPORTUNITYMEDIALINK
        set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where AUTHORID = @SOURCEID;

        update dbo.SPONSORSHIPOPPORTUNITYATTACHMENT
        set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where AUTHORID = @SOURCEID;

        update dbo.SPONSORSHIPAFFILIATEPROGRAM
        set AFFILIATEID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
        where AFFILIATEID = @SOURCEID;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;