USP_DATAFORMTEMPLATE_ADDSAVE_MEMBERSHIPTRANSFER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDSAVE_MEMBERSHIPTRANSFER
(
    @ID uniqueidentifier output,
    @OLDPRIMARYMEMBERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NEWPRIMARYCONSTITUENTID uniqueidentifier,    
    @MEMBERNAME nvarchar(700) = null,
    @EXPIRATIONDATE datetime = null
)
as
    set nocount on;

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

    -- Using this ID to bring back the member that was created from Transfer.
    if @ID is null
        set @ID = newid();

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

    declare @SOURCEMEMBERSHIPID uniqueidentifier;
    declare @SOURCEMEMBERSHIPSTATUSCODE tinyint;
    declare @SOURCEMEMBERSHIPPROGRAMID uniqueidentifier;
    declare @SOURCEISPRIMARY bit;
    declare @SOURCEISDROPPED bit;
    declare @ALLOWMULTIPLEMEMBERSHIPS bit;

    select
        @SOURCEMEMBERSHIPID = MEMBER.MEMBERSHIPID,
        @SOURCEMEMBERSHIPSTATUSCODE = MEMBERSHIP.STATUSCODE,
        @SOURCEMEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
        @SOURCEISDROPPED = MEMBER.ISDROPPED,
        @SOURCEISPRIMARY = MEMBER.ISPRIMARY,
        @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS
    from
        dbo.MEMBER
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    inner join
        dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
    where
        MEMBER.ID = @OLDPRIMARYMEMBERID;

    begin try
        if @SOURCEMEMBERSHIPSTATUSCODE = 1  -- Cancelled
            raiserror('BBERR_MEMBERSHIPISCANCELLED', 13, 1);

        if @SOURCEISDROPPED = 1
            raiserror('BBERR_SOURCEISDROPPED', 13, 1);

        if @SOURCEISPRIMARY = 0
            raiserror('BBERR_SOURCENOTPRIMARY', 13, 1);

        if dbo.UFN_CONSTITUENT_ISINACTIVE(@NEWPRIMARYCONSTITUENTID) = 1
            raiserror('BBERR_TARGETISINACTIVE', 13, 1);

        if dbo.UFN_CONSTITUENT_ISDECEASED(@NEWPRIMARYCONSTITUENTID) = 1
            raiserror('BBERR_TARGETISDECEASED', 13, 1);

        if exists (
            select
                1
            from
                dbo.MEMBER
            where
                MEMBERSHIPID = @SOURCEMEMBERSHIPID
                and CONSTITUENTID = @NEWPRIMARYCONSTITUENTID
                and ISDROPPED = 0
        )
            raiserror('This constituent is already in this membership.', 13, 1);

        if @ALLOWMULTIPLEMEMBERSHIPS = 0 and exists (
            select
                1
            from
                dbo.MEMBER
            inner join
                dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
            where
                MEMBER.CONSTITUENTID = @NEWPRIMARYCONSTITUENTID
                and MEMBERSHIP.MEMBERSHIPPROGRAMID = @SOURCEMEMBERSHIPPROGRAMID
                and MEMBER.ISDROPPED = 0
        )
            raiserror('This membership program does not allow multiple memberships from the same constituent.', 13, 1);

        -- 3/13/2012 MDC - made the decision to drop the current member and add another member instead of just switching the constituent.
        --    Since this is an Add form and someone may be using this and expecting the ID passed into the form to have had a record created
        --        it makes sense, otherwise they would be taken to a page that might not show what they would expect to see.
        update dbo.MEMBER set
            ISDROPPED = 1,
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @OLDPRIMARYMEMBERID;

        insert into dbo.MEMBER (
            ID,
            CONSTITUENTID,
            MEMBERSHIPID,
            ISPRIMARY,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values (
            @ID,
            @NEWPRIMARYCONSTITUENTID,
            @SOURCEMEMBERSHIPID,
            1, -- Should always be primary
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );        

        declare @SOURCENUMBEROFCARDS int;

        select @SOURCENUMBEROFCARDS = count(*)
        from dbo.MEMBERSHIPCARD
        where MEMBERID = @OLDPRIMARYMEMBERID
        and STATUSCODE <> 2;  -- Cancelled

        if @SOURCENUMBEROFCARDS > 0
        begin
            -- Cancel existing cards for the source and insert a new card
            update dbo.MEMBERSHIPCARD set
                STATUSCODE = 2,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                MEMBERID = @OLDPRIMARYMEMBERID;

            insert into dbo.MEMBERSHIPCARD (
                ID,
                MEMBERID,
                NAMEONCARD,
                EXPIRATIONDATE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values (
                newid(),
                @ID,
                @MEMBERNAME,
                dbo.UFN_DATE_GETLATESTTIME(@EXPIRATIONDATE),
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );
        end 
    end try

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

    return 0;