USP_CREDIT_CANCELMEMBERSHIPADDON

create procedure dbo.USP_CREDIT_CANCELMEMBERSHIPADDON
(
    @MEMBERSHIPADDONID uniqueidentifier,
    @NUMBERTOCANCEL smallint,
    @CHANGEAGENTID uniqueidentifier
)
as
begin
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime = getdate();

    declare @MEMBERSHIPTRANSACTIONID uniqueidentifier = (
        select MEMBERSHIPTRANSACTIONID
        from dbo.MEMBERSHIPADDON
        where ID = @MEMBERSHIPADDONID
    );

    -- Update the add-on first, so we can use the stored values uniformly in the additional member calculations below.

    update dbo.MEMBERSHIPADDON
    set
        NUMCANCELLED += @NUMBERTOCANCEL,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where ID = @MEMBERSHIPADDONID;

    if (
        select ADDON.ADDONTYPECODE
        from dbo.MEMBERSHIPADDON
        inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
        where MEMBERSHIPADDON.ID = @MEMBERSHIPADDONID
    ) = 1  -- Additional member

    begin
        -- We might need to drop some members. We have to calculate the total number of members allowed

        --    (number of members the level allows + number of extra member add-ons sold - previously refunded add-ons).

        -- If there are now too many members, we drop as many as we need to.


        declare @MEMBERSTODROP dbo.UDT_GENERICID;

        with CTE_ADDONINFO as
        (
            select
                (
                    -- Number of members on the associated transaction

                    select
                        count(MEMBER.ID)
                    from dbo.MEMBERSHIPTRANSACTION
                    inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                    where
                        MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
                        and MEMBER.ISDROPPED = 0
                ) -
                (
                    -- Number of members allowed, based on the current membership level and the add-ons remaining

                    select
                        MEMBERSHIPLEVEL.MEMBERSALLOWED + sum(MEMBERSHIPADDON.QUANTITY) - sum(MEMBERSHIPADDON.NUMCANCELLED)
                    from dbo.MEMBERSHIPTRANSACTION
                    inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
                    inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
                    where
                        MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
                        and ADDON.ADDONTYPECODE = 1
                    group by MEMBERSHIPLEVEL.MEMBERSALLOWED

                ) as NUMBEROFMEMBERSTODROP
        ),
        CTE_MEMBERINFO as
        (
            select
                MEMBER.ID as MEMBERID,
                row_number() over (partition by MEMBERSHIPTRANSACTION.ID order by MEMBER.DATEADDED desc) as ROWNUMBER
            from dbo.MEMBERSHIPTRANSACTION 
            inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
            where MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
                and MEMBER.ISPRIMARY = 0
                and MEMBER.ISDROPPED = 0
        )
        insert into @MEMBERSTODROP (ID)
        select MEMBERID
        from CTE_MEMBERINFO
        where ROWNUMBER <= (select NUMBEROFMEMBERSTODROP from CTE_ADDONINFO);

        update dbo.MEMBER set
            ISDROPPED = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID in (select ID from @MEMBERSTODROP);

        update dbo.MEMBERSHIPCARD set
            STATUSCODE = 2,  -- Cancelled

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            MEMBERID in (select ID from @MEMBERSTODROP)
            and STATUSCODE <> 2;  -- Cancelled

    end

    return 0;
end