USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM_LIFETIMEINFO

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM_LIFETIMEINFO
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TOTALMEMBERSHIPAMOUNT  money = null output,
    @LASTRENEWALAMOUNT money = null output,
    @LASTRENEWALDATE datetime = null output,
    @TRANSACTIONCOUNT int = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @WHEREISREVENUETRACKED smallint = null output
)
as
    set nocount on;

    set @DATALOADED = 1;
    set @TOTALMEMBERSHIPAMOUNT = 0
    set @TRANSACTIONCOUNT = 0
    set @LASTRENEWALAMOUNT = 0

    declare @RECURRINGGIFTID uniqueidentifier
    set @RECURRINGGIFTID = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT_2(@ID, 1)

    declare @OBTAINLEVELCODE tinyint
    declare @REVENUEISTRACKEDINANOTHERSYSTEM tinyint = 1

    select @OBTAINLEVELCODE = MEMBERSHIPLEVEL.OBTAINLEVELCODE 
        ,@BASECURRENCYID = MEMBERSHIPLEVEL.BASECURRENCYID
        ,@WHEREISREVENUETRACKED = MEMBERSHIPPROGRAM.WHEREISREVENUETRACKEDCODE
    from dbo.MEMBERSHIP
    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    where MEMBERSHIP.ID = @ID

    if @RECURRINGGIFTID is null
    begin
        select @TOTALMEMBERSHIPAMOUNT = coalesce(sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT),0) - isnull(sum(CREDITS.TOTAL),0)
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
    cross apply (
        select sum(TOTAL) as TOTAL
        from dbo.CREDITITEM
        where [REVENUESPLITID] = MEMBERSHIPTRANSACTION.REVENUESPLITID
    ) as CREDITS
    where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID
        and MEMBERSHIPLEVEL.OBTAINLEVELCODE = 0

        if @OBTAINLEVELCODE = 0
        begin
            select @TRANSACTIONCOUNT = count(1)
            from dbo.MEMBERSHIPTRANSACTION
            where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID

            select @TOTALMEMBERSHIPAMOUNT = @TOTALMEMBERSHIPAMOUNT + coalesce(sum(BASEAMOUNT),0)
            from dbo.MEMBERSHIPCONTRIBUTIONPORTION
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPCONTRIBUTIONPORTION.FINANCIALTRANSACTIONLINEITEMID                                                        
            where MEMBERSHIPCONTRIBUTIONPORTION.MEMBERSHIPTRANSACTIONID in ( select MEMBERSHIPTRANSACTION.ID from dbo.MEMBERSHIPTRANSACTION where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID)
        end
        else
        begin
            select @TRANSACTIONCOUNT = count(1) from (select FINANCIALTRANSACTION.ID from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID
            inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID = @ID
            group by FINANCIALTRANSACTION.ID) TRANCOUNT

         select @TOTALMEMBERSHIPAMOUNT = @TOTALMEMBERSHIPAMOUNT + coalesce(sum(MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.AMOUNT),0)
         from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
         where MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPID = @ID
        end

    end


    if @RECURRINGGIFTID is not null
    begin
        select @TOTALMEMBERSHIPAMOUNT = sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT),
                     @TRANSACTIONCOUNT = count(distinct FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.RECURRINGGIFTACTIVITY on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
        where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID
    end

    declare @LASTRENEWTRANSACTIONID uniqueidentifier
    declare @RENEWALUPGRADE int = 1
    declare @MIDTERMUPGRADE int = 2

    declare @RENEWACTIONCODE int = 1
    declare @UPGRADEACTIONCODE int = 2
    declare @DOWNGRADEACTIONCODE int = 3

    select @LASTRENEWTRANSACTIONID = (select top 1 MEMBERSHIPTRANSACTION.ID from dbo.MEMBERSHIPTRANSACTION
                                                            where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID
                                                            and  (MEMBERSHIPTRANSACTION.ACTIONCODE IN (@RENEWACTIONCODE, @UPGRADEACTIONCODE, @DOWNGRADEACTIONCODE ) )
                                                            and  (MEMBERSHIPTRANSACTION.UPGRADEMETHODCODE <> @MIDTERMUPGRADE)
                                                            order by TRANSACTIONDATE DESC, DATEADDED DESC)

    if @LASTRENEWTRANSACTIONID is not null
    begin
        select 
                     @LASTRENEWALAMOUNT = 
                        case
                            when @WHEREISREVENUETRACKED = @REVENUEISTRACKEDINANOTHERSYSTEM then
                                MEMBERSHIPTRANSACTION.BASEAMOUNT
                            else
                                coalesce(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,0)  - isnull(CREDITS.TOTAL,0)
                        end
                     ,@LASTRENEWALDATE =  TRANSACTIONDATE    
        from dbo.MEMBERSHIPTRANSACTION
        left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
    cross apply (
        select sum(TOTAL) as TOTAL
        from dbo.CREDITITEM
        where [REVENUESPLITID] = MEMBERSHIPTRANSACTION.REVENUESPLITID
    ) as CREDITS
        where MEMBERSHIPTRANSACTION.ID = @LASTRENEWTRANSACTIONID

        if @OBTAINLEVELCODE = 0
        begin
            select @LASTRENEWALAMOUNT = @LASTRENEWALAMOUNT + coalesce(sum(BASEAMOUNT),0)
            from dbo.MEMBERSHIPCONTRIBUTIONPORTION
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPCONTRIBUTIONPORTION.FINANCIALTRANSACTIONLINEITEMID                                                        
            where MEMBERSHIPCONTRIBUTIONPORTION.MEMBERSHIPTRANSACTIONID = @LASTRENEWTRANSACTIONID
        end
        else
        begin
                select @LASTRENEWALAMOUNT = coalesce(sum(MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.AMOUNT),0),
                                                                @LASTRENEWALDATE = case when @LASTRENEWALDATE is null then max(MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.EFFECTIVEDATE)
                                                                else @LASTRENEWALDATE
                                                                end
                from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE 
                where ORIGINALMEMBERSHIPTRANSACTIONID = @LASTRENEWTRANSACTIONID

        end

    end

    select @TOTALMEMBERSHIPAMOUNT = @TOTALMEMBERSHIPAMOUNT + coalesce(sum(MEMBERSHIPADDON.PURCHASEPRICE * MEMBERSHIPADDON.QUANTITY),0) - isnull(sum(CREDITS.TOTAL),0)
    from dbo.MEMBERSHIPADDON
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on MEMBERSHIPADDON.REVENUESPLITID = FTLI.ID
    cross apply (
        select sum(TOTAL) as TOTAL
        from dbo.CREDITITEM
        where [REVENUESPLITID] = MEMBERSHIPADDON.REVENUESPLITID
    ) as CREDITS
    where MEMBERSHIPADDON.MEMBERSHIPID = @ID
        and FTLI.DELETEDON is null
        and FTLI.TYPECODE <> 1

    return 0;