USP_MEMBERSHIP_CANCEL

CREATE procedure dbo.USP_MEMBERSHIP_CANCEL
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @REASONCODEID uniqueidentifier = null
) 
as
set nocount on;

declare @CURRENTDATE datetime = getdate();

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

update dbo.MEMBERSHIP
set 
    STATUSCODE = 1,
    CANCELLATIONREASONCODEID = @REASONCODEID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
where MEMBERSHIP.ID = @ID;

--Cancel all membership addons

update dbo.MEMBERSHIPADDON
set
    MEMBERSHIPADDON.NUMCANCELLED = MEMBERSHIPADDON.QUANTITY,
    MEMBERSHIPADDON.CHANGEDBYID = @CHANGEAGENTID,
    MEMBERSHIPADDON.DATECHANGED = @CURRENTDATE
from dbo.MEMBERSHIPADDON
where
    MEMBERSHIPADDON.MEMBERSHIPID = @ID and
    MEMBERSHIPADDON.EXPIRATIONDATE >= @CURRENTDATE;

insert into dbo.MEMBERSHIPTRANSACTION
(
    ID,
    MEMBERSHIPID,
    MEMBERSHIPLEVELID,
    MEMBERSHIPLEVELTERMID,
    MEMBERSHIPLEVELTYPECODEID,
    TRANSACTIONDATE,
    EXPIRATIONDATE,
    ISGIFT,
    DONORID,
    NUMBEROFCHILDREN,
    COMMENTS,
    ACTIONCODE,
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED
)
select
    newid(),
    @ID,
    MEMBERSHIPLEVELID,
    MEMBERSHIPLEVELTERMID,
    MEMBERSHIPLEVELTYPECODEID,
    dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
    EXPIRATIONDATE,
    ISGIFT,
    GIVENBYID,
    NUMBEROFCHILDREN,
    COMMENTS,
    4,
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CURRENTDATE, 
    @CURRENTDATE
from dbo.MEMBERSHIP
where MEMBERSHIP.ID = @ID

-- Mark the recurring gift as 'Completed'.

declare @RECURRINGGIFTID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(@ID)

if @RECURRINGGIFTID is not null
    exec dbo.USP_RECURRINGGIFT_EDITSTATUS @RECURRINGGIFTID, 3, @CHANGEAGENTID, @CURRENTDATE, null, null, null, 14;


-- Write-off the remainder of the installment plan

declare @INSTALLMENTPLANID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETPLEDGE(@ID)

if @INSTALLMENTPLANID is not null
begin
    declare @AMOUNTREMAINING money = dbo.UFN_PLEDGE_GETBALANCE(@INSTALLMENTPLANID);
    if @AMOUNTREMAINING > 0
    begin
        declare @INSTALLMENTS xml;
        set @INSTALLMENTS =
                                    (
                                        select 
                                            ID, 
                                            DATE,
                                            TRANSACTIONAMOUNT as AMOUNT,
                                            BALANCE,
                                            BALANCE as WRITEOFFAMOUNT,
                                            SEQUENCE,
                                            TRANSACTIONCURRENCYID
                                        from dbo.UFN_INSTALLMENT_GETWRITEOFFINSTALLMENTS_2(@INSTALLMENTPLANID) WRITEOFFINSTALLMENT
                                        order by SEQUENCE
                                        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
                                    );

        declare @WRITEOFFDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
        declare @WRITEOFFID uniqueidentifier;

        exec dbo.USP_DATAFORMTEMPLATE_ADD_PLEDGEWRITEOFF2 @WRITEOFFID output, @CHANGEAGENTID, @INSTALLMENTPLANID, @WRITEOFFDATE, @AMOUNTREMAINING, 2, null, null, @INSTALLMENTS, null, null

        --Setting the write-off's DATEADDED to the same value used for the membership transaction's DATEADDED will help with matching them up with the membership transaction if it gets deleted

        update dbo.FINANCIALTRANSACTION
        set DATEADDED = @CURRENTDATE,
            DATECHANGED = @CURRENTDATE
        where ID = @WRITEOFFID
    end
end

--Cancel membership cards

update dbo.MEMBERSHIPCARD 
set
    STATUSCODE = 2,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
from dbo.MEMBERSHIPCARD
inner join dbo.MEMBER on 
    MEMBERSHIPCARD.MEMBERID = MEMBER.ID
where MEMBER.MEMBERSHIPID = @ID