USP_MEMBERSHIP_MERGE

CREATE procedure dbo.USP_MEMBERSHIP_MERGE (
    @SOURCEMEMBERSHIPID uniqueidentifier,
    @SOURCECONSTITUENTID uniqueidentifier,
    @TARGETCONSTITUENTID uniqueidentifier,
    @EXCLUDEDUPES bit = 0,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as
    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    declare @SOURCEMEMBERID uniqueidentifier;
    declare @SOURCEISPRIMARY bit;

    begin try

        select
            @SOURCEMEMBERID = ID,
            @SOURCEISPRIMARY = ISPRIMARY
        from
            dbo.MEMBER
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID
            and CONSTITUENTID = @SOURCECONSTITUENTID
            and ISDROPPED = 0;

        -- Check if source is on the membership

        if @SOURCEMEMBERID is null
            raiserror('BBERR_SOURCENOTONMEMBERSHIP', 13, 1);

        declare @TARGETMEMBERID uniqueidentifier;
        declare @TARGETISPRIMARY bit;

        -- Check if the target is currently a member of the merging membership

        select
            @TARGETMEMBERID = ID,
            @TARGETISPRIMARY = ISPRIMARY
        from
            dbo.MEMBER
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID
            and CONSTITUENTID = @TARGETCONSTITUENTID
            and ISDROPPED = 0;

        -- Special case if target is also a member of this membership

        if @TARGETMEMBERID is not null
        begin
            -- Move source's cards to constituent

            update dbo.MEMBERSHIPCARD set
                MEMBERID = @TARGETMEMBERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                MEMBERID = @SOURCEMEMBERID;

            -- Delete the duplicate member record

            -- Should we just drop the member here?

            exec dbo.USP_MEMBER_DELETEBYID_WITHCHANGEAGENTID @SOURCEMEMBERID, @CHANGEAGENTID;

            -- Make the target primary if the source used to be

            update dbo.MEMBER set
                ISPRIMARY = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @TARGETMEMBERID
                and ISPRIMARY = 0
                and @SOURCEISPRIMARY = 1;

            return 0;
        end

        -- Get the membership's program info

        declare @MEMBERSHIPPROGRAMID uniqueidentifier;
        declare @ALLOWMULTIPLEMEMBERSHIPS bit;
        declare @SOURCEMEMBERSHIPSTATUSCODE tinyint;

        select top 1
            @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
            @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
            @SOURCEMEMBERSHIPSTATUSCODE = MEMBERSHIP.STATUSCODE
        from
            dbo.MEMBERSHIP
        inner join
            dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        where
            MEMBERSHIP.ID = @SOURCEMEMBERSHIPID;

        -- Get target's membership ID with the same program if one exists

        declare @TARGETMEMBERSHIPID uniqueidentifier;
        select
            @TARGETMEMBERSHIPID = MEMBERSHIP.ID,
            @TARGETMEMBERID = MEMBER.ID,
            @TARGETISPRIMARY = MEMBER.ISPRIMARY
        from
            dbo.MEMBER
        inner join
            dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
        where
            MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
            and MEMBERSHIP.STATUSCODE <> 1  -- Cancelled

            and MEMBER.CONSTITUENTID = @TARGETCONSTITUENTID
            and MEMBER.ISDROPPED = 0;

        exec dbo.USP_MEMBERSHIP_MERGEEXCEPTIONS @SOURCEMEMBERSHIPID, @SOURCECONSTITUENTID, @MEMBERSHIPPROGRAMID, @TARGETCONSTITUENTID    

        -- Ignore moving or merging memberships of the same program when EXCLUDEDUPES is true

        if @TARGETMEMBERID is not null and @EXCLUDEDUPES = 1 and @SOURCEMEMBERSHIPSTATUSCODE <> 1  -- Cancelled

        begin
            return 0;
        end

        -- If the target does not have a membership with this program or if the program allows multiple memberships or if the membership is cancelled, just move the membership over

        if @ALLOWMULTIPLEMEMBERSHIPS = 1 or @TARGETMEMBERSHIPID is null or @SOURCEMEMBERSHIPSTATUSCODE = 1  -- Cancelled

        begin
            update dbo.MEMBER set
                CONSTITUENTID = @TARGETCONSTITUENTID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @SOURCEMEMBERID;

            return 0;
        end

        -- The target already has a membership with the same program so we need to merge the transactions


        -- This table will contain updates that need to be made to transaction records

        declare @TRANSACTIONUPDATES table (
            ID uniqueidentifier,
            ACTIONCODE tinyint,
            EXPIRATIONDATE datetime
        );

        -- Declare previous and current transaction variables used to determine action changes

        declare @PREVIOUSTRANSACTIONID uniqueidentifier;
        declare @PREVIOUSMEMBERSHIPID uniqueidentifier;
        declare @PREVIOUSACTIONCODE tinyint;
        declare @PREVIOUSTRANSACTIONDATE datetime;
        declare @PREVIOUSEXPIRATIONDATE datetime;
        declare @PREVIOUSMEMBERSHIPLEVELID uniqueidentifier;
        declare @PREVIOUSMEMBERSHIPLEVELTERMID uniqueidentifier;

        declare @CURRENTTRANSACTIONID uniqueidentifier;
        declare @CURRENTMEMBERSHIPID uniqueidentifier;
        declare @CURRENTACTIONCODE tinyint;
        declare @CURRENTTRANSACTIONDATE datetime;
        declare @CURRENTEXPIRATIONDATE datetime;
        declare @CURRENTMEMBERSHIPLEVELID uniqueidentifier;
        declare @CURRENTMEMBERSHIPLEVELTERMID uniqueidentifier;

        -- The join and expiration dates could be different after merging transactions

        declare @NEWJOINDATE datetime;
        declare @LASTRENEWEDON datetime;
        declare @CALCULATEDEXPIRATIONDATE datetime;

        -- Loop through transactions and determine proper actions

        declare TRANSACTION_CURSOR cursor local static for
        select
            MEMBERSHIPTRANSACTION.ID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
            MEMBERSHIPTRANSACTION.EXPIRATIONDATE,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID
        from
            dbo.MEMBERSHIPTRANSACTION
        inner join
            dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
        where
            MEMBERSHIPTRANSACTION.MEMBERSHIPID in (@SOURCEMEMBERSHIPID, @TARGETMEMBERSHIPID)
        order by
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
            MEMBERSHIPTRANSACTION.DATEADDED;

        open TRANSACTION_CURSOR;

        fetch next from TRANSACTION_CURSOR into
            @CURRENTTRANSACTIONID,
            @CURRENTMEMBERSHIPID,
            @CURRENTACTIONCODE,
            @CURRENTTRANSACTIONDATE,
            @CURRENTEXPIRATIONDATE,
            @CURRENTMEMBERSHIPLEVELID,
            @CURRENTMEMBERSHIPLEVELTERMID;

        declare @NUMBEROFTRANSACTIONS int = @@cursor_rows;
        declare @CURRENTTRANSACTIONNUMBER int = 0;

        set @NEWJOINDATE = @CURRENTTRANSACTIONDATE;

        while @@fetch_status = 0
        begin
            set @CURRENTTRANSACTIONNUMBER = @CURRENTTRANSACTIONNUMBER + 1;

            if @PREVIOUSTRANSACTIONID is not null and @CURRENTACTIONCODE <> 4  -- Drop

            begin
                set @CURRENTACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@PREVIOUSMEMBERSHIPLEVELID, @PREVIOUSEXPIRATIONDATE, @PREVIOUSACTIONCODE, @CURRENTMEMBERSHIPLEVELID, @CURRENTTRANSACTIONDATE);

                if @CURRENTACTIONCODE in (1,2,3)  -- Renew, Upgrade, Downgrade

                    set @LASTRENEWEDON = @CURRENTTRANSACTIONDATE;

                if @CURRENTTRANSACTIONNUMBER = @NUMBEROFTRANSACTIONS
                    and not (
                        @CURRENTACTIONCODE = 2  -- Upgrade

                        and @CURRENTEXPIRATIONDATE = @PREVIOUSEXPIRATIONDATE
                    )
                begin
                    if @CURRENTACTIONCODE in (0,5)  -- Join, Rejoin

                        set @CALCULATEDEXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@CURRENTMEMBERSHIPLEVELID, @CURRENTMEMBERSHIPLEVELTERMID, @CURRENTTRANSACTIONDATE);
                    else
                        set @CALCULATEDEXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@CURRENTMEMBERSHIPLEVELID, @CURRENTMEMBERSHIPLEVELTERMID, @PREVIOUSEXPIRATIONDATE);

                    if @CALCULATEDEXPIRATIONDATE > @CURRENTEXPIRATIONDATE
                        set @CURRENTEXPIRATIONDATE = @CALCULATEDEXPIRATIONDATE;
                end
            end

            insert into @TRANSACTIONUPDATES (
                ID,
                ACTIONCODE,
                EXPIRATIONDATE
            )
            values (
                @CURRENTTRANSACTIONID,
                @CURRENTACTIONCODE,
                @CURRENTEXPIRATIONDATE
            );

            set @PREVIOUSTRANSACTIONID = @CURRENTTRANSACTIONID;
            set @PREVIOUSMEMBERSHIPID = @CURRENTMEMBERSHIPID;
            set @PREVIOUSACTIONCODE = @CURRENTACTIONCODE;
            set @PREVIOUSTRANSACTIONDATE = @CURRENTTRANSACTIONDATE;
            set @PREVIOUSEXPIRATIONDATE = @CURRENTEXPIRATIONDATE;
            set @PREVIOUSMEMBERSHIPLEVELID = @CURRENTMEMBERSHIPLEVELID;
            set @PREVIOUSMEMBERSHIPLEVELTERMID = @CURRENTMEMBERSHIPLEVELTERMID;

            fetch next from TRANSACTION_CURSOR into
                @CURRENTTRANSACTIONID,
                @CURRENTMEMBERSHIPID,
                @CURRENTACTIONCODE,
                @CURRENTTRANSACTIONDATE,
                @CURRENTEXPIRATIONDATE,
                @CURRENTMEMBERSHIPLEVELID,
                @CURRENTMEMBERSHIPLEVELTERMID;
        end

        close TRANSACTION_CURSOR;
        deallocate TRANSACTION_CURSOR;

        -- Update the transactions with the new merged values

        -- Should we update only the transactions on the target,

        -- and insert new transaction records for the transactions merged from the source?

        update dbo.MEMBERSHIPTRANSACTION set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            ACTIONCODE = TRANSACTIONUPDATES.ACTIONCODE,
            EXPIRATIONDATE = TRANSACTIONUPDATES.EXPIRATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBERSHIPTRANSACTION
        inner join
            @TRANSACTIONUPDATES as TRANSACTIONUPDATES on TRANSACTIONUPDATES.ID = MEMBERSHIPTRANSACTION.ID
        where
            MEMBERSHIPTRANSACTION.MEMBERSHIPID <> @TARGETMEMBERID
            or MEMBERSHIPTRANSACTION.ACTIONCODE <> TRANSACTIONUPDATES.ACTIONCODE
            or MEMBERSHIPTRANSACTION.EXPIRATIONDATE <> TRANSACTIONUPDATES.EXPIRATIONDATE;

        -- Update the membership with merged values

        update dbo.MEMBERSHIP set
            STATUSCODE = NEWMEMBERSHIPSTATUS.STATUSCODE,
            JOINDATE = @NEWJOINDATE,
            LASTRENEWEDON = @LASTRENEWEDON,
            MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELTERMID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID,
            MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTYPECODEID,
            COMMENTS = MEMBERSHIPTRANSACTION.COMMENTS,
            EXPIRATIONDATE = MEMBERSHIPTRANSACTION.EXPIRATIONDATE,
            GIVENBYID = MEMBERSHIPTRANSACTION.DONORID,
            ISGIFT = MEMBERSHIPTRANSACTION.ISGIFT,
            NUMBEROFCHILDREN = LATESTTRANSACTIONMEMBERSHIP.NUMBEROFCHILDREN,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBERSHIP
        inner join
            dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
        outer apply
            dbo.UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE(MEMBERSHIPTRANSACTION.ID, @CURRENTDATE) as NEWMEMBERSHIPSTATUS
        outer apply (
            select NUMBEROFCHILDREN
            from dbo.MEMBERSHIP
            where ID = @PREVIOUSMEMBERSHIPID
        ) as LATESTTRANSACTIONMEMBERSHIP
        where
            MEMBERSHIPTRANSACTION.ID = @PREVIOUSTRANSACTIONID;

        -- Keep only members and cards from the latest transaction

        -- TODO:  Should we be moving cards from one membership to another

        -- since the membership ID can be printed on cards?

        declare @DROPMEMBERSFROMMEMBERSHIPID uniqueidentifier;

        if @PREVIOUSMEMBERSHIPID = @SOURCEMEMBERSHIPID
        begin
            set @DROPMEMBERSFROMMEMBERSHIPID = @TARGETMEMBERSHIPID;

            if @SOURCEISPRIMARY = 1
            begin
                -- Since the source membership was the last updated,

                -- and the source was primary, make sure that the target is primary as well

                if @TARGETISPRIMARY = 0
                begin
                    update dbo.MEMBER set
                        ISPRIMARY = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        MEMBERSHIPID = @TARGETMEMBERSHIPID
                        and ISPRIMARY = 1;

                    update dbo.MEMBER set
                        ISPRIMARY = 1,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @TARGETMEMBERID;
                end
            end

            else
            begin
                -- We need to unmark the target as primary since someone other

                -- than the source is marked as primary and will be merging over

                if @TARGETISPRIMARY = 1
                begin
                    update dbo.MEMBER set
                        ISPRIMARY = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @TARGETMEMBERID;
                end
            end
        end

        else
        begin
            set @DROPMEMBERSFROMMEMBERSHIPID = @SOURCEMEMBERSHIPID;
        end

        update dbo.MEMBER set
            ISDROPPED = 1,
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @DROPMEMBERSFROMMEMBERSHIPID
            and ID <> @TARGETMEMBERID  -- Make sure we don't drop the Target

            and ISDROPPED = 0;

        update dbo.MEMBERSHIPCARD set
            STATUSCODE = 2,  -- Cancelled

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBERSHIPCARD
        inner join
            dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
        where
            MEMBER.MEMBERSHIPID = @DROPMEMBERSFROMMEMBERSHIPID
            and STATUSCODE <> 2;  -- Cancelled


        -- Moving over all Add-ons regardless if they allow multiple or not.

        -- Add-ons should be moved before members to avoid the valid number of members constraint.

        -- These are linked to revenue and should not be deleted.

        update dbo.MEMBERSHIPADDON set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Move the membership cards over from the source member since that member isn't moved over

        update dbo.MEMBERSHIPCARD set
            MEMBERID = @TARGETMEMBERID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERID = @SOURCEMEMBERID;

        update dbo.MEMBER set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID
            and ID <> @SOURCEMEMBERID;

        update dbo.SALESORDERITEMMEMBERSHIPADDON set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Update the expiration dates of Add-ons linked to the final transaction

        update dbo.MEMBERSHIPADDON set
            EXPIRATIONDATE = @PREVIOUSEXPIRATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPTRANSACTIONID = @PREVIOUSTRANSACTIONID
            and EXPIRATIONDATE <> @PREVIOUSEXPIRATIONDATE;

        -- Update EXISTINGMEMBERSHIPID on membership dues batches so they won't be deleted

        -- when the source membership is deleted

        update dbo.BATCHMEMBERSHIPDUES set
            EXISTINGMEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            EXISTINGMEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Make sure the sales order item membership is updated to the target membership.

        update dbo.SALESORDERITEMMEMBERSHIP
            set MEMBERSHIPID = @TARGETMEMBERSHIPID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        where 
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Make sure MEMBERID fields are updated to the target member ID

        update dbo.SALESORDERITEMMEMBER
            set MEMBERID = @TARGETMEMBERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        where 
            MEMBERID = @SOURCEMEMBERID;

        -- Move over notes from the source membership to the target membership

        update dbo.MEMBERNOTE set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Move over media links from the source membership to the target membership

        update dbo.MEMBERMEDIALINK set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Move over attachments from the source membership to the target membership

        update dbo.MEMBERATTACHMENT set
            MEMBERSHIPID = @TARGETMEMBERSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERSHIPID = @SOURCEMEMBERSHIPID;

        -- Delete the source membership

        exec dbo.USP_MEMBERSHIP_DELETEBYID_WITHCHANGEAGENTID @SOURCEMEMBERSHIPID, @CHANGEAGENTID;
    end try

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

    return 0;