UFN_MEMBERSHIPDUESBATCH_CANADDCONSTITUENTTOPROGRAM_3

CREATE function dbo.UFN_MEMBERSHIPDUESBATCH_CANADDCONSTITUENTTOPROGRAM_3
(
    @CONSTITUENTID uniqueidentifier,
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @WHATPAYINGFORVALUE tinyint = 0, --0 = Add, 1 = Renew, 2 = Pay, 3 = Upgrade

    @MEMBERSHIPID uniqueidentifier
)
returns bit with execute as caller
as begin
    declare @ALLOWMULTIPLEMEMBERSHIPS bit = 0;
    declare @CURRENTDATE date = getdate();
    declare @PROGRAMTYPECODE tinyint;

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

    declare @ISREJOIN bit = 0;
    select
        @ISREJOIN = case
            -- Annual

            when @PROGRAMTYPECODE = 0 then
                case
                    when
                    (
                        MEMBERSHIPPROGRAM.RENEWALWINDOWREVENUETYPECODE = 0 and
                        (
                            MEMBERSHIP.EXPIRATIONDATE is not null and
                            dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) < @CURRENTDATE
                        )
                    )
                    or
                    not (
                        MEMBERSHIP.STATUSCODE <> 1 or
                        MEMBERSHIP.STATUSCODE = 5
                    )
                    then 1
                    else 0
                end
            -- Lifetime/Recurring

            when @PROGRAMTYPECODE in (1, 2) then
                case
                    when not (MEMBERSHIP.STATUSCODE <> 1 or MEMBERSHIP.STATUSCODE = 5) then 1
                    else 0
                end
            else 0
        end
    from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    where MEMBERSHIP.ID = @MEMBERSHIPID;

    if @ALLOWMULTIPLEMEMBERSHIPS = 0
    begin
        -- If the program does not allow multiple memberships... check and see if this constituent is already associated with the program.

        declare @MEMBERSHIPCOUNT int = 0;

        if @PROGRAMTYPECODE = 0
        begin
            select @MEMBERSHIPCOUNT = count('x')
            from dbo.MEMBER
                inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            where
                MEMBER.CONSTITUENTID = @CONSTITUENTID and
                MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
                MEMBERSHIP.STATUSCODE <> 1 and -- Not cancelled

                MEMBER.ISDROPPED <> 1 and -- Not dropped

                dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) >= @CURRENTDATE; -- Not expired

        end
        else
        begin
            select @MEMBERSHIPCOUNT = count('x')
            from dbo.MEMBER
                inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
            where
                MEMBER.CONSTITUENTID = @CONSTITUENTID and
                MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
                MEMBERSHIP.STATUSCODE <> 1 and -- Not cancelled

                MEMBER.ISDROPPED <> 1; -- Not dropped

        end

        -- Add

        if @WHATPAYINGFORVALUE = 0
        begin
            if @MEMBERSHIPCOUNT > 0 -- A membership to this program already exists

                return 0;
        end

        -- Renew, Rejoin

        if @WHATPAYINGFORVALUE = 1
        begin
            if (@MEMBERSHIPID is null and @MEMBERSHIPCOUNT = 0) -- There is no membership to renew/rejoin

                return 0;

            if @ISREJOIN = 0
            begin
                -- This is a renewal

                if @MEMBERSHIPCOUNT > 1 -- More than one membership shouldn't be possible

                    return 0;
            end
            else
            begin
                -- This is a rejoin

                if @MEMBERSHIPCOUNT > 0 -- A membership to this program already exists

                    return 0;
            end
        end
    end

    return 1;
end