USP_MERGETASK_CONSTITUENTMEMBERSHIP

CREATE procedure dbo.USP_MERGETASK_CONSTITUENTMEMBERSHIP
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @DELETEDUPES bit = 0
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();
    declare @CONTEXTCACHE varbinary(128) = context_info();

    begin try
        -- Update given by and donor ID fields on memberships
        update dbo.MEMBERSHIP set
            GIVENBYID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            GIVENBYID = @SOURCEID;

        update dbo.MEMBERSHIPTRANSACTION set
            DONORID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            DONORID = @SOURCEID;

        -- Updating all the source's member records that are dropped
        -- to point to the target.  Since they are dropped, we shouldn't
        -- be violating any constraints.
        update dbo.MEMBER set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID
            and ISDROPPED = 1;

        -- Update authors on membership notes
        update dbo.MEMBERNOTE set
            AUTHORID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            AUTHORID = @SOURCEID;

        -- Update authors on membership media links
        update dbo.MEMBERMEDIALINK set
            AUTHORID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            AUTHORID = @SOURCEID;

        -- Update authors on membership attachments
        update dbo.MEMBERATTACHMENT set
            AUTHORID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            AUTHORID = @SOURCEID;

        -- Update given by field for sales order item membership records
        update dbo.SALESORDERITEMMEMBERSHIP set
            GIVENBYID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            GIVENBYID = @SOURCEID;

        -- Delete any SALESORDERITEMMEMBERSHIP records on incomplete sales orders that contain either the source or target
        declare SALESORDERITEMMEMBERSHIP_CURSOR cursor local fast_forward for
        select distinct
            SALESORDERITEM.ID
        from
            dbo.SALESORDER
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join
            dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
        inner join
            dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
        where
            SALESORDER.STATUSCODE <> 1  -- Complete
            and SALESORDERITEMMEMBER.CONSTITUENTID in (@SOURCEID, @TARGETID);

        open SALESORDERITEMMEMBERSHIP_CURSOR;

        declare @SALESORDERITEMID uniqueidentifier;

        fetch next from SALESORDERITEMMEMBERSHIP_CURSOR into @SALESORDERITEMID;

        while @@fetch_status = 0
        begin
            exec dbo.USP_SALESORDERITEM_DELETE @SALESORDERITEMID, @CHANGEAGENTID;
            fetch next from SALESORDERITEMMEMBERSHIP_CURSOR into @SALESORDERITEMID;
        end

        close SALESORDERITEMMEMBERSHIP_CURSOR;
        deallocate SALESORDERITEMMEMBERSHIP_CURSOR;

        -- There's a chance that sales orders exist where the source and target
        -- where both members of the same membership.  Update primary record accordingly
        -- and delete the source sales order item member records.
        declare @SHAREDORDERMEMBERSHIPS table (
            SALESORDERITEMMEMBERSHIPID uniqueidentifier,
            SOURCESALESORDERITEMMEMBERID uniqueidentifier,
            TARGETSALESORDERITEMMEMBERID uniqueidentifier,
            SOURCEISPRIMARY bit
        );

        insert into @SHAREDORDERMEMBERSHIPS (
            SALESORDERITEMMEMBERSHIPID,
            SOURCESALESORDERITEMMEMBERID,
            TARGETSALESORDERITEMMEMBERID,
            SOURCEISPRIMARY
        )
        select
            SALESORDERITEMMEMBERSHIP.ID,
            SOURCEMEMBER.ID,
            TARGETMEMBER.ID,
            SOURCEMEMBER.ISPRIMARY
        from
            dbo.SALESORDERITEMMEMBERSHIP
        inner join
            dbo.SALESORDERITEMMEMBER as SOURCEMEMBER on SOURCEMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID and SOURCEMEMBER.CONSTITUENTID = @SOURCEID
        inner join
            dbo.SALESORDERITEMMEMBER as TARGETMEMBER on TARGETMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID and TARGETMEMBER.CONSTITUENTID = @TARGETID

        if @@rowcount > 0
        begin
            -- Move sales order membership cards over from the source to the target
            update dbo.SALESORDERITEMMEMBERSHIPCARD set
                SALESORDERITEMMEMBERID = SHAREDORDERMEMBERSHIPS.TARGETSALESORDERITEMMEMBERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
                dbo.SALESORDERITEMMEMBERSHIPCARD
            inner join
                @SHAREDORDERMEMBERSHIPS as SHAREDORDERMEMBERSHIPS on SHAREDORDERMEMBERSHIPS.SOURCESALESORDERITEMMEMBERID = SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID;

            set context_info @CHANGEAGENTID;

            -- Delete source sales order item member records
            delete from dbo.SALESORDERITEMMEMBER
            where ID in (select SOURCESALESORDERITEMMEMBERID from @SHAREDORDERMEMBERSHIPS);

            if @CONTEXTCACHE is not null
                set context_info @CONTEXTCACHE;

            -- Turn on the ISPRIMARY flag for the target if source was primary
            update dbo.SALESORDERITEMMEMBER set
                ISPRIMARY = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
                dbo.SALESORDERITEMMEMBER
            inner join
                @SHAREDORDERMEMBERSHIPS as SHAREDORDERMEMBERSHIPS on SHAREDORDERMEMBERSHIPS.TARGETSALESORDERITEMMEMBERID = SALESORDERITEMMEMBER.ID
            where
                SHAREDORDERMEMBERSHIPS.SOURCEISPRIMARY = 1;
        end

        -- At this point, we should be ok to update sales order item member records
        -- to point from the source to the target.  The MEMBERID field will be updated
        -- in USP_MEMBERSHIP_MERGE if two memberships are merged into one.
        update dbo.SALESORDERITEMMEMBER set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.SALESORDERITEMMEMBER
        inner join
            dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
        where
            SALESORDERITEMMEMBER.CONSTITUENTID = @SOURCEID;

        -- Update membership dues batch information records to point to the new constituent
        update dbo.BATCHMEMBERSHIPDUES set
            BILLTOCONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            BILLTOCONSTITUENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUES set
            MEMBERSHIPRECIPIENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPRECIPIENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUESMEMBER set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUESMEMBERSHIPCARD set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUESDONATIONRECOGNITION set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUESDONATIONSOLICITOR set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID;

        update dbo.BATCHMEMBERSHIPDUESMEMBERSHIPRECOGNITION set
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            CONSTITUENTID = @SOURCEID;

        -- Loop through source memberships and move them over to the target
        declare @MEMBERSHIPID uniqueidentifier;

        declare MEMBERSHIP_CURSOR cursor local fast_forward for
        select distinct MEMBER.MEMBERSHIPID
        from dbo.MEMBER
        where CONSTITUENTID = @SOURCEID;

        open MEMBERSHIP_CURSOR;

        fetch next from MEMBERSHIP_CURSOR into @MEMBERSHIPID;

        while @@fetch_status = 0
        begin
            exec dbo.USP_MEMBERSHIP_MERGE @MEMBERSHIPID, @SOURCEID, @TARGETID, @DELETEDUPES, @CHANGEAGENTID, @CURRENTDATE;
            fetch next from MEMBERSHIP_CURSOR into @MEMBERSHIPID;
        end

        close MEMBERSHIP_CURSOR;
        deallocate MEMBERSHIP_CURSOR;
    end try

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

    return 0;