USP_MEMBERSHIP_MERGEEXCEPTIONS

CREATE procedure dbo.USP_MEMBERSHIP_MERGEEXCEPTIONS(@SOURCEMEMBERSHIPID uniqueidentifier, @SOURCECONSTITUENTID uniqueidentifier, @MEMBERSHIPPROGRAMID uniqueidentifier, @TARGETCONSTITUENTID uniqueidentifier)
as
begin
-- MDC - things that should throw an exception for Q1 2012

--    Memberships that are paid with recurring gifts.

--    Lifetime membership programs.

--    Annual Dues membership programs that are contribution based or marked as "both" (contribution and dues).

--    Any membership that has a pledge/installment made towards it.

--    Source and target are on the same membership from a same order.


    set nocount on;
    declare @TARGETMEMBERSHIPID uniqueidentifier;

    begin try

        select
            @TARGETMEMBERSHIPID = MEMBERSHIP.ID
        from
            dbo.MEMBER
        inner join
            dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
        where
            MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
            and MEMBER.CONSTITUENTID = @TARGETCONSTITUENTID
            and MEMBER.ISDROPPED = 0;

        -- need to check to see if any of the source's sales order item membership records are of the same membership id as the target

        -- and also see if they are from the same order... same order same membership = no no.

        if (@SOURCEMEMBERSHIPID = @TARGETMEMBERSHIPID) and exists 
        (
            select 
                1
            from 
                dbo.SALESORDERITEM
            inner join 
                dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
            inner join
                dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
            inner join
            (    
                select 
                    SALESORDERID SOURCESALESORDERID
                from 
                    dbo.SALESORDERITEM
                inner join 
                    dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
                inner join
                    dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
                where 
                    SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = @SOURCEMEMBERSHIPID
                    and SALESORDERITEMMEMBER.CONSTITUENTID = @SOURCECONSTITUENTID
            ) SOURCE on SOURCE.SOURCESALESORDERID = SALESORDERITEM.SALESORDERID
            where 
                SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = @TARGETMEMBERSHIPID 
                and SALESORDERITEMMEMBER.CONSTITUENTID = @TARGETCONSTITUENTID
        )
            raiserror('BBERR_MEMBERSHIP_SALESORDERDUPLICATEMEMBERSHIP', 13, 1);

        declare @PROGRAMTYPECODE tinyint;
        declare @PROGRAMBASEDONCODE tinyint;

        select @PROGRAMTYPECODE = PROGRAMTYPECODE,
            @PROGRAMBASEDONCODE = PROGRAMBASEDONCODE
        from dbo.MEMBERSHIPPROGRAM
        where ID = @MEMBERSHIPPROGRAMID

        if (dbo.UFN_MEMBERSHIP_GETPLEDGE(@SOURCEMEMBERSHIPID) is not null) or (dbo.UFN_MEMBERSHIP_GETPLEDGE(@TARGETMEMBERSHIPID) is not null)
            raiserror('BBERR_MEMBERSHIP_PLEDGEORINSTALLMENT', 13, 1);

        -- Membership paid with recurring gifts

        if (@PROGRAMTYPECODE = 1)
            raiserror('BBERR_MEMBERSHIP_INVALIDPROGRAMRECURRING', 13, 1);

        -- Lifetime memberships

        if (@PROGRAMTYPECODE = 2)
            raiserror('BBERR_MEMBERSHIP_INVALIDPROGRAMLIFETIME', 13, 1);

        -- Annual set to contribution based or "both"

        if (@PROGRAMTYPECODE = 0 and @PROGRAMBASEDONCODE in (1,2))
            raiserror('BBERR_MEMBERSHIP_INVALIDPROGRAMANNUALDUESCONTRIBUTION', 13, 1);

    end try

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

    return 0;
end