USP_DATALIST_CONSTITUENTGROUPMATCHEDGIFTS

CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUPMATCHEDGIFTS
(
    @CONSTITUENTID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CURRENCYCODE tinyint = 2
)
as
set nocount on;

    declare @CURRENCYID uniqueidentifier
    declare @ORGTOMYBASEEXCHANGERATE uniqueidentifier = null

    declare @MULTICURRENCYENABLED bit;
    set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
    if @MULTICURRENCYENABLED = 0 
        set @CURRENCYCODE = 1;

    if @CURRENCYCODE = 1
        set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    if @CURRENCYCODE = 3
    begin
        set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
        if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
        begin     
            set @CURRENCYCODE = 1
        end
    end;

    declare @GROUPINCLUDESMEMBERGIVING bit = null;

    select 
        @GROUPINCLUDESMEMBERGIVING = 
            case
                when GD.GROUPTYPECODE = 0 then 1
                when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
            end
    from 
        dbo.GROUPDATA GD
    left join 
        dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
    where 
        GD.ID=@CONSTITUENTID;

    declare @MEMBERREVENUE table
    (
        REVENUEID uniqueidentifier
    )

    if @GROUPINCLUDESMEMBERGIVING = 1
    begin
        insert into @MEMBERREVENUE
            select 
                FINANCIALTRANSACTION.ID
            from 
                dbo.FINANCIALTRANSACTION
            inner join
                dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = FINANCIALTRANSACTION.CONSTITUENTID
            left join 
                dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
            where 
                GROUPMEMBER.GROUPID = @CONSTITUENTID
            and
                ((GROUPMEMBERDATERANGE.DATEFROM is null or FINANCIALTRANSACTION.DATE >= GROUPMEMBERDATERANGE.DATEFROM)
                and (GROUPMEMBERDATERANGE.DATETO is null or FINANCIALTRANSACTION.DATE <= GROUPMEMBERDATERANGE.DATETO))
    end;

    with CONSTITUENTREVENUE_CTE as
    (
        select
            FINANCIALTRANSACTION.ID,
            FINANCIALTRANSACTION.CONSTITUENTID,
            FINANCIALTRANSACTION.TYPE,
            FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT,
            FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
            cast(FINANCIALTRANSACTION.DATE as datetime) DATE,
            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
            REVENUE_EXT.GIVENANONYMOUSLY
        from 
            dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID            
        where
            (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID or FINANCIALTRANSACTION.ID in (select REVENUEID from @MEMBERREVENUE))
        and    exists 
            (
                select top 1 RSSUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                where RSSUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                and 
                (
                    @SITEFILTERMODE = 0
                    or 
                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                )
            )
    )

    select
        CONSTITUENTREVENUE_CTE.ID as REVENUEID,
        CONSTITUENT.ID as CONSTITUENTID,
        CONSTITUENT.NAME as CONSTITUENTNAME,
        null as MGCLAIMID,
        'Original gift' as TYPE,
        CONSTITUENTREVENUE_CTE.DATE as DATE,
        null as MGORGID,
        null as MGORGNAME,
        case @CURRENCYCODE
            when 2 then CONSTITUENTREVENUE_CTE.TRANSACTIONAMOUNT
            when 1 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(CONSTITUENTREVENUE_CTE.ID,@CURRENCYID)
            when 3 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(CONSTITUENTREVENUE_CTE.ID,@CURRENCYID)
            else CONSTITUENTREVENUE_CTE.AMOUNT
        end as AMOUNT,
        null as MGCLAIMAMOUNT,
        null as PARENT,
        row_number() over (order by CONSTITUENTREVENUE_CTE.DATE, CONSTITUENTREVENUE_CTE.AMOUNT desc) as SEQUENCE,
        dbo.UFN_REVENUE_BUILDSITELIST(CONSTITUENTREVENUE_CTE.ID) as SITES,
        case @CURRENCYCODE
            when 2 then CONSTITUENTREVENUE_CTE.TRANSACTIONCURRENCYID
            when 0 then CONSTITUENTREVENUE_CTE.BASECURRENCYID
            else @CURRENCYID
        end as DISPLAYCURRENCY,
        case when CONSTITUENTREVENUE_CTE.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY
    from
        CONSTITUENTREVENUE_CTE
    inner join
        dbo.CONSTITUENT on CONSTITUENT.ID = CONSTITUENTREVENUE_CTE.CONSTITUENTID
    where exists(select top(1) ID from dbo.REVENUEMATCHINGGIFT where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = CONSTITUENTREVENUE_CTE.ID)

    union all

    select
        REVENUE.ID as REVENUEID,
        null as CONSTITUENTID,
        null as CONSTITUENTNAME,
        REVENUE.ID as MGCLAIMID,
        REVENUE.TRANSACTIONTYPE as TYPE,
        REVENUE.DATE as DATE,
        CONSTITUENT.ID as MGORGID,
        CONSTITUENT.NAME as MGORGNAME,
        case @CURRENCYCODE
            when 2 then (select TRANSACTIONAMOUNT from dbo.REVENUE where ID=REVENUEMATCHINGGIFT.ID)
            when 1 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
            when 3 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
            else REVENUE.AMOUNT
        end as AMOUNT,
        case @CURRENCYCODE 
            when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, REVENUE.TRANSACTIONCURRENCYID)
            when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, REVENUE.BASECURRENCYID)
            else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, @CURRENCYID) 
        end as MGCLAIMBALANCE,
        REVENUEMATCHINGGIFT.MGSOURCEREVENUEID as PARENT,
        row_number() over (order by REVENUE.DATE, REVENUE.AMOUNT desc) as SEQUENCE,
        null as SITES,
        case @CURRENCYCODE
            when 2 then REVENUE.TRANSACTIONCURRENCYID
            when 0 then REVENUE.BASECURRENCYID
            else @CURRENCYID
        end as DISPLAYCURRENCY,
        case when REVENUE.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY
    from
        dbo.REVENUEMATCHINGGIFT
    inner join
        CONSTITUENTREVENUE_CTE on CONSTITUENTREVENUE_CTE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
    inner join
        dbo.REVENUE on REVENUE.ID = REVENUEMATCHINGGIFT.ID
    inner join
        dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID