USP_CREDIT_REFUNDMEMBERSHIP

CREATE procedure dbo.USP_CREDIT_REFUNDMEMBERSHIP
(
    @ID uniqueidentifier,  -- MEMBERSHIPID

    @CHANGEAGENTID uniqueidentifier,
    @MEMBERSHIPTRANSACTIONID uniqueidentifier
)
as
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime = getdate();

    declare @RENEWALAFTEREXPIRATION datetime = (
        select top 1 dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIPLEVELID, EXPIRATIONDATE)
        from dbo.MEMBERSHIPTRANSACTION
        where MEMBERSHIPID = @ID
        order by DATEADDED desc
    );

    -- Only delete if it's a renewal, and we're still in the renewal window for the previous transaction

    if DateDiff(day, @CURRENTDATE, @RENEWALAFTEREXPIRATION) >= 0
        and exists (select ID from dbo.MEMBERSHIPTRANSACTION where ID = @MEMBERSHIPTRANSACTIONID and (ACTIONCODE = 1 or ACTIONCODE = 2 or ACTIONCODE = 3))
    begin
        declare @EXPIRATIONDATE date = (select EXPIRATIONDATE from dbo.MEMBERSHIPTRANSACTION where ID = @MEMBERSHIPTRANSACTIONID);

        --Cancel all membership addons

        update dbo.MEMBERSHIPADDON
        set
            MEMBERSHIPADDON.NUMCANCELLED = MEMBERSHIPADDON.QUANTITY,
            MEMBERSHIPADDON.CHANGEDBYID = @CHANGEAGENTID,
            MEMBERSHIPADDON.DATECHANGED = @CURRENTDATE
        from dbo.MEMBERSHIPADDON
        where MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID;

        exec dbo.USP_MEMBERSHIPTRANSACTION_DELETEBYID_WITHCHANGEAGENTID @MEMBERSHIPTRANSACTIONID, @CHANGEAGENTID;
        exec dbo.USP_MEMBERSHIP_RESETFROMLASTTRANSACTION @ID, @EXPIRATIONDATE, @CHANGEAGENTID;
    end
    else
    begin
        -- Change the status to canceled

        exec dbo.USP_MEMBERSHIP_CANCEL @ID, @CHANGEAGENTID;
    end

    return 0;