USP_DATALIST_CONSTITUENTREVENUERECENT

CREATE procedure dbo.USP_DATALIST_CONSTITUENTREVENUERECENT
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @MAXROWS int,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @NAME nvarchar(1000);
    select @NAME = C.NAME
    from dbo.CONSTITUENT C
    where C.ID = @CONSTITUENTID

    if object_id('tempdb..#CONSTITUENTREVENUE') is not null
        drop table #CONSTITUENTREVENUE;

    create table #CONSTITUENTREVENUE
    (
        ID uniqueidentifier primary key,
        AMOUNT money,
        TRANSACTIONTYPE nvarchar(150) collate database_default,
        TRANSACTIONTYPECODE tinyint,
        [DATE] date,
        TRANSACTIONCURRENCYID uniqueidentifier,
        TRANSACTIONAMOUNT money,
        GIVENANONYMOUSLY bit
    );

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 0 and exists(select top 1 1 from dbo.SITE)
    begin
        insert into #CONSTITUENTREVENUE
        (
            ID,
            AMOUNT,
            TRANSACTIONTYPE,
            TRANSACTIONTYPECODE,
            DATE,
            TRANSACTIONCURRENCYID,
            TRANSACTIONAMOUNT,
            GIVENANONYMOUSLY
        )
        select
            FINANCIALTRANSACTION.ID,
            TRANSACTIONAMOUNT,
            FINANCIALTRANSACTION.[TYPE],
            FINANCIALTRANSACTION.TYPECODE,
            DATE,
            TRANSACTIONCURRENCYID,
            FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
            GIVENANONYMOUSLY
        from
            dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT
                on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        where
            FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID and
            FINANCIALTRANSACTION.DELETEDON is null and
            FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,6,7,8,9,15) and --Showing order payment summary, so order revenue redundant

            exists
            (
                select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) REVSITES
                -- Using a case statement since the standard site extension filters

                -- resulted in a poor plan

                where case
                        when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                        when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                        else 0
                    end = 1
            );
    end
    else
    begin
        insert into #CONSTITUENTREVENUE
        (
            ID,
            AMOUNT,
            TRANSACTIONTYPE,
            TRANSACTIONTYPECODE,
            DATE,
            TRANSACTIONCURRENCYID,
            TRANSACTIONAMOUNT,
            GIVENANONYMOUSLY
        )
        select
            FINANCIALTRANSACTION.ID,
            TRANSACTIONAMOUNT,
            FINANCIALTRANSACTION.[TYPE],
            FINANCIALTRANSACTION.TYPECODE,
            DATE,
            TRANSACTIONCURRENCYID,
            FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
            GIVENANONYMOUSLY
        from
            dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT
                on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        where
            FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID and
            FINANCIALTRANSACTION.DELETEDON is null and
            FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,6,7,8,9,15)
    end

    if object_id('tempdb..#CONSTITUENTWRITEOFF') is not null
        drop table #CONSTITUENTWRITEOFF;

    create table #CONSTITUENTWRITEOFF
    (
        ID uniqueidentifier,
        TRANSACTIONTYPECODE tinyint,
        DATE datetime,
        AMOUNT money,
        TRANSACTIONCURRENCYID uniqueidentifier,
        GIVENANONYMOUSLY bit,
        COMMITMENTREVENUEID uniqueidentifier
    );

    insert into #CONSTITUENTWRITEOFF (ID, TRANSACTIONTYPECODE, DATE, AMOUNT, TRANSACTIONCURRENCYID, GIVENANONYMOUSLY, COMMITMENTREVENUEID)
    select
        MYSITESREV_CTE.ID,
        MYSITESREV_CTE.TRANSACTIONTYPECODE,
        WO.CALCULATEDDATE [DATE],
        sum(WOS.BASEAMOUNT),
        MYSITESREV_CTE.TRANSACTIONCURRENCYID,
        MYSITESREV_CTE.GIVENANONYMOUSLY,
        MYSITESREV_CTE.ID
    from #CONSTITUENTREVENUE as MYSITESREV_CTE
    inner join dbo.FINANCIALTRANSACTION WO on MYSITESREV_CTE.ID = WO.PARENTID
    inner join dbo.WRITEOFF_EXT on WO.ID = WRITEOFF_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM WOS on WOS.FINANCIALTRANSACTIONID = WO.ID
    inner join dbo.REVENUESPLIT_EXT on WOS.ID = REVENUESPLIT_EXT.ID
    where
        WO.TYPECODE = 20
        and WOS.DELETEDON is null
    group by MYSITESREV_CTE.ID, WO.ID, MYSITESREV_CTE.TRANSACTIONTYPECODE, WO.CALCULATEDDATE, MYSITESREV_CTE.TRANSACTIONCURRENCYID, MYSITESREV_CTE.GIVENANONYMOUSLY;

    select top (@MAXROWS)
        [RECORDID],
        [DATE],
        [TYPE],
        [AMOUNT],
        [DISPLAYCURRENCY],
        [GIVENANONYMOUSLY]
    from
    (
        -- Donations, event registration payments, membership, membership add-on, and other payments

        select
            MYSITESREV_CTE.ID as RECORDID,
            case MYSITESREV_CTE.TRANSACTIONTYPECODE
                when 0 then
                    case
                        when REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,4,5,12,18) then
                            case
                                when REVENUESPLIT_EXT.TYPECODE = 9 then  -- Sponsorship

                                    case
                                        when REVENUESPLIT_EXT.APPLICATIONCODE = 0 then
                                            'Sponsorship ' + lower(REVENUESPLIT_EXT.APPLICATION)
                                        else
                                            'Sponsorship ' + REVENUESPLIT_EXT.APPLICATION + ' payment'
                                    end
                                when RS.TYPECODE = 17 then
                                    'Sponsorship additional donation'
                                else
                                    case
                                        when REVENUESPLIT_EXT.APPLICATIONCODE = 0 then
                                            REVENUESPLIT_EXT.APPLICATION
                                        else
                                            REVENUESPLIT_EXT.APPLICATION + ' payment'
                                    end
                            end
                        when REVENUESPLIT_EXT.APPLICATIONCODE in (2,3,6,7,8,13,19) then
                            case
                                when REVENUESPLIT_EXT.TYPECODE = 9 then  -- Sponsorship

                                    'Sponsorship payment'
                                when REVENUESPLIT_EXT.TYPECODE = 17 then
                                    'Sponsorship recurring additional gift payment'
                                when REVENUESPLIT_EXT.TYPECODE = 2 and REVENUESPLIT_EXT.APPLICATIONCODE = 3 then
                                    'Recurring membership payment'
                                else
                                    REVENUESPLIT_EXT.APPLICATION + ' payment'
                            end
                    end
            end as TYPE,
            MYSITESREV_CTE.DATE,
            RS.TRANSACTIONAMOUNT as AMOUNT,
            MYSITESREV_CTE.TRANSACTIONCURRENCYID as DISPLAYCURRENCY,
            MYSITESREV_CTE.GIVENANONYMOUSLY
        from
            #CONSTITUENTREVENUE as MYSITESREV_CTE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on MYSITESREV_CTE.ID = RS.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
        where
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 0
            and REVENUESPLIT_EXT.APPLICATIONCODE <> 10  -- Order transactions are selected below; we don't want their payments separately.

            and RS.DELETEDON is null

        union all

        -- Pledges, Recurring gifts, Auction donations, Matching gift claims (by this constituent matching someone else's gift --> Orgs Only),

        -- Planned gifts, Grant awards, Membership installment plans

        select
            MYSITESREV_CTE.ID as RECORDID,
            case
                when REVENUESPLIT_EXT.TYPECODE = 9 then  -- Sponsorship

                    'Sponsorship ' + LOWER(MYSITESREV_CTE.TRANSACTIONTYPE)
                when REVENUESPLIT_EXT.TYPECODE = 17 then
                    'Sponsorship recurring additional gift'
                when REVENUESPLIT_EXT.APPLICATIONCODE = 5 and REVENUESPLIT_EXT.TYPECODE = 2 and MYSITESREV_CTE.TRANSACTIONTYPECODE = 2 then
                    'Membership'
                else
                    MYSITESREV_CTE.TRANSACTIONTYPE
            end as TYPE,
            MYSITESREV_CTE.DATE,
            MYSITESREV_CTE.TRANSACTIONAMOUNT as AMOUNT,
            MYSITESREV_CTE.TRANSACTIONCURRENCYID as DISPLAYCURRENCY,
            MYSITESREV_CTE.GIVENANONYMOUSLY
        from
            #CONSTITUENTREVENUE as MYSITESREV_CTE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on MYSITESREV_CTE.ID = RS.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
        where
            ((MYSITESREV_CTE.TRANSACTIONTYPECODE not in (0,15)) or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 15 AND REVENUESPLIT_EXT.TYPECODE <> 0 and REVENUESPLIT_EXT.TYPECODE <> 18)) and
            RS.DELETEDON is null
        group by
            MYSITESREV_CTE.ID,
            REVENUESPLIT_EXT.TYPECODE,
            REVENUESPLIT_EXT.APPLICATIONCODE,
            MYSITESREV_CTE.DATE,
            MYSITESREV_CTE.TRANSACTIONAMOUNT,
            MYSITESREV_CTE.TRANSACTIONCURRENCYID,
            MYSITESREV_CTE.TRANSACTIONTYPE,
            MYSITESREV_CTE.GIVENANONYMOUSLY,
            MYSITESREV_CTE.TRANSACTIONTYPECODE

        union all

        -- write-offs

        select
            CONSTITUENTWRITEOFF.ID as RECORDID,
            case CONSTITUENTWRITEOFF.TRANSACTIONTYPECODE
                when 1 then 'Pledge write-off'
                when 6 then 'Grant award write-off'
                when 15 then 'Membership write-off' 
            end as TYPE,
            CONSTITUENTWRITEOFF.DATE as DATE,
            CONSTITUENTWRITEOFF.AMOUNT,
            CONSTITUENTWRITEOFF.TRANSACTIONCURRENCYID as DISPLAYCURRENCY,
            CONSTITUENTWRITEOFF.GIVENANONYMOUSLY
        from
            #CONSTITUENTWRITEOFF CONSTITUENTWRITEOFF

        union all
                -- Matching gift claims, matching revenue from this constituent

        select distinct
            MYSITESREV_CTE.ID as RECORDID,
            MYSITESREV_CTE.TRANSACTIONTYPE + ' (' + @NAME + ')' as TYPE,
            MYSITESREV_CTE.DATE,
            RS.TRANSACTIONAMOUNT as AMOUNT,
            MYSITESREV_CTE.TRANSACTIONCURRENCYID as DISPLAYCURRENCY,
            MYSITESREV_CTE.GIVENANONYMOUSLY
        from
            #CONSTITUENTREVENUE as MYSITESREV_CTE
            inner join dbo.REVENUEMATCHINGGIFT RMG on RMG.ID = MYSITESREV_CTE.ID
            inner join dbo.FINANCIALTRANSACTION R2 on R2.ID = RMG.MGSOURCEREVENUEID
            inner join dbo.REVENUE_EXT RE2 on R2.ID = RE2.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = MYSITESREV_CTE.ID
            inner join dbo.REVENUESPLIT_EXT RSE on RS.ID = RSE.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS2 on RS2.FINANCIALTRANSACTIONID = R2.ID
            inner join dbo.REVENUESPLIT_EXT RSE2 on RS2.ID = RSE2.ID
        where
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 and -- Matching gift claims

            R2.CONSTITUENTID = @CONSTITUENTID and
            RSE.DESIGNATIONID = RSE2.DESIGNATIONID and
            RS.DELETEDON is null and
            RS2.DELETEDON is null

        union all

        -- bwj 9/23/09 Get payments by this constituent on orders (the orders might have been made by a different

        --    constituent). This will likely need to be updated when work occurs to display sales orders on

        --    the constituent revenue history datalist selected out of above.

        select
            SALESORDER.REVENUEID,
            (select FINANCIALTRANSACTION.TYPE from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID and FINANCIALTRANSACTION.DELETEDON is null) as TRANSACTIONTYPE,
            SALESORDER.TRANSACTIONDATE,
            SUM(SALESORDERPAYMENT.AMOUNT) as AMOUNT,
            null as DISPLAYCURRENCY,
            REVENUE_EXT.GIVENANONYMOUSLY
        from
            dbo.SALESORDERPAYMENT
        inner join
            dbo.FINANCIALTRANSACTION ON SALESORDERPAYMENT.PAYMENTID = FINANCIALTRANSACTION.ID
        inner join
            dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        inner join
            dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
        where
            FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID
        and
            FINANCIALTRANSACTION.DELETEDON is null
        and
            SALESORDER.STATUSCODE not in (0, 5, 7) -- exclude pending, cancelled, unresolved orders

        and
            SALESORDER.REVENUEID is not null
        group by
            SALESORDERPAYMENT.SALESORDERID, SALESORDER.REVENUEID, SALESORDER.TRANSACTIONDATE, REVENUE_EXT.GIVENANONYMOUSLY

        union all

        -- fetches the 0 dollar payments having 100% adjustable discount

        select distinct
            SALESORDER.REVENUEID,
            'Order' as TRANSACTIONTYPE,
            SALESORDER.TRANSACTIONDATE,
            SALESORDER.AMOUNT,
            null as DISPLAYCURRENCY,
            0 as GIVENANONYMOUSLY
        from
            dbo.SALESORDER
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join
            dbo.SALESORDERITEMORDERDISCOUNT on SALESORDERITEMORDERDISCOUNT.ID = SALESORDERITEM.ID
        where
            SALESORDER.CONSTITUENTID = @CONSTITUENTID
        and
            SALESORDER.STATUSCODE not in (0, 5, 7) -- exclude pending, cancelled, unresolved orders

        and
            SALESORDER.REVENUEID is not null
        and
            SALESORDER.AMOUNT=0 and SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT=1
    ) as REVENUEHISTORY
    order by [DATE] desc;