USP_DATALIST_RECOGNITIONREVENUEREPORT

CREATE procedure dbo.USP_DATALIST_RECOGNITIONREVENUEREPORT(
    @PROGRAMID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @CURRENCYCODE tinyint = null --0 = Record base, (null, 1) = Organization

) 
with execute as owner
as
    set nocount on;

    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    declare @SELECTIONID uniqueidentifier;
    declare @PLANNEDGIFTCODE tinyint;
    declare @USEGROSSAMOUNT bit;

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @SELECTEDCURRENCYID uniqueidentifier;
    if coalesce(@CURRENCYCODE, 1) = 1
    begin
        set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
    end

    select
        @SELECTIONID = SELECTIONID,
        @PLANNEDGIFTCODE = PLANNEDGIFTCODE,
        @USEGROSSAMOUNT = USEGROSSAMOUNT,
        @SELECTEDCURRENCYID = coalesce(@SELECTEDCURRENCYID, BASECURRENCYID)
    from dbo.RECOGNITIONPROGRAM
    where ID = @PROGRAMID;

    declare @ISOCURRENCYCODE nvarchar(3);
    declare @DECIMALDIGITS tinyint;
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    select
        @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
        @ROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
    from
        dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

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

    create table #MASTERJOIN (
        TIERCODE uniqueidentifier,
        RECOGNITIONLEVEL uniqueidentifier,
        REVENUESPLITAMOUNT money,
        REVENUETYPE tinyint,
        REVENUEID uniqueidentifier,
        FTLIID uniqueidentifier
    );

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

    create table #RECOGNITIONPROGRAMTYPECODE (
        TYPECODE int primary key
    );

    insert into #RECOGNITIONPROGRAMTYPECODE (TYPECODE)
        select distinct
            T.c.value('(TYPECODEID)[1]','tinyint') as TYPECODE
        from
            dbo.RECOGNITIONPROGRAM with (nolock)
            cross apply GIFTTYPESFILTER.nodes('/GIFTTYPESFILTER/ITEM') T(c)
        where
            RECOGNITIONPROGRAM.ID = @PROGRAMID;

    declare @SQL nvarchar(max);

    -- Get program-related constituents

    set @SQL = 
    'with CONSTITRECS_SEQUENCE_CTE as (
        select
            REVENUERECOGNITION.CONSTITUENTID,
            CONSTITUENTRECOGNITION.RECOGNITIONLEVELID,
            ROW_NUMBER() over(PARTITION BY CONSTITUENTRECOGNITION.CONSTITUENTID order by CONSTITUENTRECOGNITION.JOINDATE desc,CONSTITUENTRECOGNITION.DATEADDED desc) as SEQUENCE
        from dbo.REVENUERECOGNITION
        inner join dbo.CONSTITUENTRECOGNITION on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENTRECOGNITION.CONSTITUENTID
        inner join dbo.UFN_RECOGNITIONPROGRAM_GET_VALIDCONSTITUENTS(@PROGRAMID) as ISVALID on CONSTITUENTRECOGNITION.CONSTITUENTID = ISVALID.ID
        where
            RECOGNITIONPROGRAMID = @PROGRAMID
            and JOINDATE <= @ENDDATE
    )
    insert into #MASTERJOIN (
        TIERCODE,
        RECOGNITIONLEVEL,
        REVENUESPLITAMOUNT,
        REVENUETYPE,
        REVENUEID,
        FTLIID
    )
    select
        RL.TIERCODEID as TIERCODE,
        CR.RECOGNITIONLEVELID as RECOGNITIONLEVEL,
        max(RS.AMOUNTINCURRENCY) as REVENUESPLITAMOUNT,
        R.TRANSACTIONTYPECODE as REVENUETYPE,
        R.ID as REVENUEID,
        RS.ID as FTLIID
    from dbo.REVENUERECOGNITION with (nolock)
        inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS on REVENUERECOGNITION.REVENUESPLITID = RS.ID
        inner join dbo.REVENUE as R with (nolock) on RS.REVENUEID = R.ID
        inner join CONSTITRECS_SEQUENCE_CTE as CR on CR.SEQUENCE = 1 and CR.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID 
        inner join dbo.RECOGNITIONLEVEL as RL with (nolock) on CR.RECOGNITIONLEVELID = RL.ID
    where
        REVENUERECOGNITION.EFFECTIVEDATE between @STARTDATE and @ENDDATE
        and(
            exists (             
                select 1
                from #RECOGNITIONPROGRAMTYPECODE as RECOGNITIONPROGRAMTYPECODE
                where

                    (R.TRANSACTIONTYPECODE = 0 and RECOGNITIONPROGRAMTYPECODE.TYPECODE = RS.APPLICATIONCODE) or -- Payment

                    (RECOGNITIONPROGRAMTYPECODE.TYPECODE = 21 and R.TRANSACTIONTYPECODE = 1) or --Pledge

                    (RECOGNITIONPROGRAMTYPECODE.TYPECODE = 22 and R.TRANSACTIONTYPECODE = 2) or --Recurring Gift

                    (RECOGNITIONPROGRAMTYPECODE.TYPECODE = 23 and R.TRANSACTIONTYPECODE = 3) or -- Matching Gift Claim

                    (RECOGNITIONPROGRAMTYPECODE.TYPECODE = 24 and R.TRANSACTIONTYPECODE = 7) --Auction donations

            ) 
            or
            (R.TRANSACTIONTYPECODE = 4 and @PLANNEDGIFTCODE <> 0) -- Planned Gift, not separate

        )
        and(
            (
                select count(DESIGNATIONID) from dbo.RECOGNITIONPROGRAMDESIGNATION RPD with (nolock)
                where RPD.RECOGNITIONPROGRAMID = @PROGRAMID
            ) = 0 
            or exists(
                select ID from dbo.UFN_RECOGNITIONPROGRAM_GETDESIGNATIONS_ALL(@PROGRAMID) DS
                where RS.DESIGNATIONID = DS.ID
            )
        )'

        if @SELECTIONID is not null
        begin
            declare @DBOBJECTNAME nvarchar(128);
            declare @DBOBJECTTYPE smallint;

            if not exists(select ID from dbo.IDSETREGISTER where ID = @SELECTIONID) 
                raiserror('ID set does not exist in the database.', 15, 1);

            select
                @DBOBJECTNAME = DBOBJECTNAME, 
                @DBOBJECTTYPE = OBJECTTYPE 
            from dbo.IDSETREGISTER where ID = @SELECTIONID;

            if @DBOBJECTTYPE = 1 
                set @DBOBJECTNAME = @DBOBJECTNAME + '()';
            else if @DBOBJECTTYPE = 2 
                set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @SELECTIONID) + ''')';

            set @SQL = @SQL + ' and exists (select 1 from dbo.' + @DBOBJECTNAME + ' as SELECTION where SELECTION.ID = R.ID) '
        end

    set @SQL = @SQL + ' 
    group by
        RL.TIERCODEID,
        CR.RECOGNITIONLEVELID,
        R.TRANSACTIONTYPECODE,
        R.ID,
        RS.ID'

    exec sp_executesql 
        @SQL, 
        N'    @PROGRAMID uniqueidentifier,@PLANNEDGIFTCODE tinyint,@SELECTIONID uniqueidentifier,@STARTDATE datetime,@ENDDATE datetime,
            @SELECTEDCURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint',
        @PROGRAMID,@PLANNEDGIFTCODE,@SELECTIONID,@STARTDATE,@ENDDATE,@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE

--The if statement below contains the additional logic to calculate totals based on gross amounts.

    if (@USEGROSSAMOUNT = 1)
        begin
            update
                #MASTERJOIN
            set 
                REVENUESPLITAMOUNT =
                    case #MASTERJOIN.REVENUETYPE
                    when 0 then
                        REVENUESPLITAMOUNT + UFN_RSGA.TAXCLAIMAMOUNTINCURRENCY
                    when 1 then
                        REVENUESPLITAMOUNT + PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY
                    else
                        REVENUESPLITAMOUNT
            end
            from #MASTERJOIN
            inner join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as UFN_RSGA on UFN_RSGA.ID = #MASTERJOIN.FTLIID
            inner join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = UFN_RSGA.ID
            left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = UFN_RSGA.ID
            where
                #MASTERJOIN.REVENUESPLITAMOUNT > 0 and
                #MASTERJOIN.REVENUETYPE in (0,1);
    end 

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

    create table #ADDITIONALGIFTS (
        FTLIID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        RECOGNITIONLEVELID uniqueidentifier,
        REVENUESPLITAMOUNTINCURRENCY money,
        TRANSACTIONTYPECODE tinyint,
        DATE date
    );


    with CONSTITUENTS_RECLEVEL_CTE as (
        select distinct
            CONSTITUENTRECOGNITION.CONSTITUENTID as CONSTITUENTID,
            CONSTITUENTRECOGNITION.RECOGNITIONLEVELID as RECOGNITIONLEVELID
        from
            #MASTERJOIN MJ
            inner join dbo.CONSTITUENTRECOGNITION with (nolock) on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = MJ.RECOGNITIONLEVEL
    )
    insert into #ADDITIONALGIFTS
    (
        FTLIID,
        CONSTITUENTID,
        RECOGNITIONLEVELID,
        REVENUESPLITAMOUNTINCURRENCY,
        TRANSACTIONTYPECODE,
        DATE
    )
    select
        UFN_RS.ID as FTLIID,
        UFN_RS.CONSTITUENTID as CONSTITUENTID,
        CONSTITUENTS_RECLEVEL_CTE.RECOGNITIONLEVELID,
        UFN_RS.AMOUNTINCURRENCY as REVENUESPLITAMOUNTINCURRENCY,
        UFN_RS.TRANSACTIONTYPECODE as TRANSACTIONTYPECODE,
        UFN_RS.DATE as DATE
    from
    CONSTITUENTS_RECLEVEL_CTE
    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as UFN_RS on UFN_RS.CONSTITUENTID = CONSTITUENTS_RECLEVEL_CTE.CONSTITUENTID
    where
        UFN_RS.[DATE] between @STARTDATE and @ENDDATE and
        UFN_RS.ID not in (select FTLIID from #MASTERJOIN);

    if @USEGROSSAMOUNT = 1
    begin
        update
            #ADDITIONALGIFTS
        set
            REVENUESPLITAMOUNTINCURRENCY = 
            case #ADDITIONALGIFTS.TRANSACTIONTYPECODE
            when 0 then
                REVENUESPLITAMOUNTINCURRENCY + UFN_RSGA.TAXCLAIMAMOUNTINCURRENCY
            when 1 then
                REVENUESPLITAMOUNTINCURRENCY + PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY
            else
                REVENUESPLITAMOUNTINCURRENCY
            end
        from
        #ADDITIONALGIFTS
        inner join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as UFN_RSGA on UFN_RSGA.ID = #ADDITIONALGIFTS.FTLIID
        inner join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = UFN_RSGA.ID
        left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = UFN_RSGA.ID
        where
            #ADDITIONALGIFTS.REVENUESPLITAMOUNTINCURRENCY > 0 and
            #ADDITIONALGIFTS.TRANSACTIONTYPECODE in (0,1);
    end

    select
        dbo.UFN_TIERCODE_GETDESCRIPTION(MJ1.TIERCODE) as TIER,
        dbo.UFN_RECOGNITIONLEVEL_GETNAME(MJ1.RECOGNITIONLEVEL) as RECOGNITIONLEVEL,
        (
            select sum(REVENUESPLITAMOUNT)
            from #MASTERJOIN as MJ2
            where MJ2.REVENUETYPE is not null 
                and    MJ2.RECOGNITIONLEVEL = MJ1.RECOGNITIONLEVEL
        ) as TOTALREVENUE,
        (
            select sum(REVENUESPLITAMOUNT)
            from #MASTERJOIN as MJ2
            where MJ2.REVENUETYPE = 0 
                and MJ2.RECOGNITIONLEVEL = MJ1.RECOGNITIONLEVEL
        ) as RECEIVEDREVENUE,
        (
            select sum(REVENUESPLITAMOUNT)
            from #MASTERJOIN as MJ2
            where MJ2.REVENUETYPE = 1 
                and MJ2.RECOGNITIONLEVEL = MJ1.RECOGNITIONLEVEL
        ) as EXPECTEDREVENUE,
        dbo.UFN_CONSTITUENTRECOGNITION_COUNTBYLEVEL(@ENDDATE, MJ1.RECOGNITIONLEVEL) as NUMMEMBERS,
        (
            select
                sum(AD.REVENUESPLITAMOUNTINCURRENCY)
            from #ADDITIONALGIFTS AD
            where
                AD.RECOGNITIONLEVELID = MJ1.RECOGNITIONLEVEL
        ) as ADDITIONALGIFTS,
        @SELECTEDCURRENCYID SELECTEDCURRENCYID,
        @ISOCURRENCYCODE ISOCURRENCYCODE,
        @CURRENCYSYMBOL CURRENCYSYMBOL,
        @DECIMALDIGITS DECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE
    from #MASTERJOIN as MJ1
    group by
        MJ1.TIERCODE,
        MJ1.RECOGNITIONLEVEL;

    return 0;