USP_DATAFORMTEMPLATE_EDITSAVE_MEMBERSHIPREMOVEMEMBER

The save procedure used by the edit dataform template "Membership Remove Member Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SEPARATEMEMBERSHIP	bit	IN	Create a separate membership for this member
@TRANSFERCARDS	bit	IN	Transfer active cards to new membership
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_MEMBERSHIPREMOVEMEMBER
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @SEPARATEMEMBERSHIP bit,
                        @TRANSFERCARDS bit
                    )
                    as
                        set nocount on;

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

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @ISPRIMARY bit
                        declare @MEMBERSHIPID uniqueidentifier
                        declare @CONSTITUENTID uniqueidentifier

                        select 
                            @ISPRIMARY = ISPRIMARY,
                            @MEMBERSHIPID = MEMBERSHIPID,
                            @CONSTITUENTID = CONSTITUENTID
                        from dbo.MEMBER
                        where ID = @ID

                        begin try
                            -- Updating member and membership tables to reflect removed member.

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

                            -- If removed member is primary member, find next in line and make them primary.

                            if @ISPRIMARY = 1
                                begin
                                    declare @NEWPRIMARYMEMBERID uniqueidentifier
                                    select top(1) @NEWPRIMARYMEMBERID = ID
                                    from dbo.MEMBER
                                    where MEMBERSHIPID = @MEMBERSHIPID and ID <> @ID

                                    if @NEWPRIMARYMEMBERID is null
                                        raiserror('Cannot remove member.  This is the only member listed for the membership.', 13,1);
                                    else
                                    begin
                                        update dbo.MEMBER set
                                            ISPRIMARY = 0,
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        where ID = @ID

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


                            if @SEPARATEMEMBERSHIP = 1
                            begin
                                -- Declaring variables to use in creating a new membership

                                declare @NEWMEMBERSHIPID uniqueidentifier
                                declare @MEMBERSHIPPROGRAMID uniqueidentifier
                                declare @MEMBERSHIPLEVELID uniqueidentifier
                                declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier
                                declare @MEMBERSHIPLEVELTERMID uniqueidentifier
                                declare @JOINDATE datetime
                                declare @EXPIRATIONDATE datetime

                                select
                                    @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID,
                                    @MEMBERSHIPLEVELID = MEMBERSHIPLEVELID,
                                    @MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID,
                                    @MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPLEVELTYPECODEID,
                                    @JOINDATE = JOINDATE
                                from dbo.MEMBERSHIP
                                where ID = @MEMBERSHIPID

                                set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE)

                                set @NEWMEMBERSHIPID = newid()

                                -- Insert into Membership

                                insert into dbo.MEMBERSHIP
                                (ID, MEMBERSHIPPROGRAMID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, MEMBERSHIPLEVELTYPECODEID, NUMBEROFCHILDREN, JOINDATE, EXPIRATIONDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values
                                (@NEWMEMBERSHIPID, @MEMBERSHIPPROGRAMID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @MEMBERSHIPLEVELTYPECODEID, 0, @JOINDATE, @EXPIRATIONDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                                -- Add to transaction table

                                insert into dbo.MEMBERSHIPTRANSACTION
                                (ID, MEMBERSHIPID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, TRANSACTIONDATE, EXPIRATIONDATE, ISGIFT, NUMBEROFCHILDREN, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values
                                (newid(), @NEWMEMBERSHIPID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, cast(@CURRENTDATE as date), @EXPIRATIONDATE, 0, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                                declare @NEWMEMBERID uniqueidentifier
                                set @NEWMEMBERID = newid()

                                -- Insert into Member

                                insert into dbo.MEMBER
                                (ID, CONSTITUENTID, MEMBERSHIPID, ISPRIMARY, ISDROPPED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values
                                (@NEWMEMBERID, @CONSTITUENTID, @NEWMEMBERSHIPID, 1, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                                -- Transfer Cards if prompted.

                                if @TRANSFERCARDS = 1
                                begin
                                    update dbo.MEMBERSHIPCARD
                                        set 
                                            MEMBERID = @NEWMEMBERID,
                                            NAMEONCARD = MC.NAMEONCARD,
                                            CARDNUMBER = MC.CARDNUMBER,
                                            EXPIRATIONDATE = @EXPIRATIONDATE,
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        from dbo.MEMBERSHIPCARD MC
                                        where MEMBERID = @ID and STATUSCODE <> 2
                                end
                            end

                            --update cards to cancelled

                            update dbo.MEMBERSHIPCARD set
                                STATUSCODE = 2,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where MEMBERID = @ID;

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

                    return 0;