USP_DATALIST_ADDONSBYMEMBERSHIP

CREATE procedure dbo.USP_DATALIST_ADDONSBYMEMBERSHIP(
    @CONTEXTID uniqueidentifier, 
    @INCLUDEEXPIRED bit = 0,
    @INCLUDEREFUNDED bit = 0
)
as
    set nocount on;

    declare @RECURRINGPROGRAMTYPECODE tinyint = 1;
    declare @LIFETIMEPROGRAMTYPECODE tinyint = 2;

    select
        MEMBERSHIPADDON.ID,
        coalesce(MEMBERSHIPTRANSACTION.TRANSACTIONDATE, MEMBERSHIPADDON.DATEADDED) as DATE,
        ADDONTOTALS.QUANTITY,
        ADDON.NAME,
        MEMBERSHIPADDON.BASECURRENCYID,
        ADDONTOTALS.TOTAL,
        ADDON.ID as ADDONID,
        MEMBERSHIPADDON.NUMCANCELLED
    from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
        left join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.ID = MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
        left join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        left join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
        outer apply (
            select 
                sum(FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT) as TOTAL
            from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where @INCLUDEREFUNDED = 0
                and FINANCIALTRANSACTION.TYPECODE = 23 --refund

                and CREDITITEM_EXT.TYPECODE = 16 --membershipaddon

                and FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = MEMBERSHIPADDON.[REVENUESPLITID]
        ) REFUNDS
        cross apply (
            select
                MEMBERSHIPADDON.QUANTITY - MEMBERSHIPADDON.NUMCANCELLED as QUANTITY,
                (MEMBERSHIPADDON.PURCHASEPRICE * MEMBERSHIPADDON.QUANTITY) - isnull(REFUNDS.TOTAL,0) as TOTAL
        ) ADDONTOTALS
    where 
        MEMBERSHIP.ID = @CONTEXTID  
        and (
            @INCLUDEEXPIRED = 1
                or MEMBERSHIPPROGRAM.PROGRAMTYPECODE = @RECURRINGPROGRAMTYPECODE
                or MEMBERSHIPPROGRAM.PROGRAMTYPECODE = @LIFETIMEPROGRAMTYPECODE
                --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

                or (getdate() <= cast(dateadd(day, 1, MEMBERSHIPADDON.EXPIRATIONDATE) as date)) 
        );

    return 0;