USP_GROUP_5YEARRECOGNITIONGET

            CREATE procedure dbo.USP_GROUP_5YEARRECOGNITIONGET
            (
                @CONSTITUENTID uniqueidentifier,
                @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null,
                @INCLUDERECOGNITIONALLDATES bit = 0,
                @CAMPAIGNID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 1 -- (1, null) = organization, 3 = my base  

            )
            as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @SELECTEDCURRENCYID uniqueidentifier;

                if @CURRENCYCODE = 3
                begin
                    if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
                    begin                    
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                            from dbo.CURRENCYSET
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
                    end
                    else
                    begin
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                            from dbo.CURRENCYSET
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
                    end
                end
                else
                    set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

                declare @CURRENCYISOCODE nvarchar(3);
                declare @CURRENCYDECIMALDIGITS tinyint;
                declare @CURRENCYSYMBOL nvarchar(5);
                declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
                declare @CURRENCYROUNDINGTYPECODE tinyint;

                select
                    @CURRENCYISOCODE = CURRENCYPROPERTIES.ISO4217,
                    @CURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
                    @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @CURRENCYROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES;

                declare @ISUK bit = 0;
                set @ISUK = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');

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

                with MYSITESREV_CTE as 
                (
                    select
                        ID,
                        CONSTITUENTID,
                        TRANSACTIONTYPECODE,
                        DATE
                    from
                        dbo.REVENUE
                    where exists 
                        (
                            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                            where RSSUB.REVENUEID = REVENUE.ID
                            /*next line is #SITEEXTENTION code*/
                            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 
                    FISCALYEAR,
                    sum(case when TYPE = 0 then RECOGNITIONAMOUNT else 0 end) as GIFTS, -- (Gift)

                    sum(case when TYPE = 1 then RECOGNITIONAMOUNT else 0 end) as PLEDGES,   -- (Pledge)

                    sum(case when TYPE = 2 then RECOGNITIONAMOUNT else 0 end) as PAYMENTS, -- (Pledge Payments)

                    sum(case when TYPE = 3 then RECOGNITIONAMOUNT else 0 end) as WRITEOFFS,
                    count(distinct case when TYPE in (0, 1) then REVENUERECOGNITIONID else null end) as NUMBER,
                    0 as PLEDGEBALANCE,
                    isnull(sum(
                        case
                            when (TYPE = 0 or TYPE = 1) then TAXCLAIMAMOUNT
                            else 0
                        end
                    ), 0) TAXCLAIMAMOUNT,
                    ISO4217,
                    DECIMALDIGITS,
                    CURRENCYSYMBOL,
                    SYMBOLDISPLAYSETTINGCODE 
                from
                (
                    -- group member gifts, pledges, or payments

                    select
                        REVENUERECOGNITION.ID as REVENUERECOGNITIONID,
                        REVENUESPLIT.ID as REVENUESPLITID,
                        case 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3) then 0                -- Gift

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 or MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 then 1    --Pledge and Matching Gift Claims

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2 then 2                    --Payment (Pledge payment)

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then 0    --Auction donation

                        end as TYPE,
                        REVENUERECOGNITION.AMOUNTINCURRENCY RECOGNITIONAMOUNT,
                        REVENUESPLIT.AMOUNTINCURRENCY as REVENUESPLITAMOUNT,
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(REVENUERECOGNITION.EFFECTIVEDATE,0) FISCALYEAR,
                        MYSITESREV_CTE.TRANSACTIONTYPECODE,
                        REVENUESPLIT.APPLICATIONCODE,
                        case 
                            when @ISHOUSEHOLD = 1 then
                                isnull (case 
                                    when REVENUESPLIT.AMOUNTINCURRENCY > 0 then
                                        case MYSITESREV_CTE.TRANSACTIONTYPECODE 
                                        when 0 then 
                                            case 
                                            when REVENUERECOGNITION.AMOUNTINCURRENCY > REVENUESPLIT.AMOUNTINCURRENCY then REVENUESPLITGIFTAID.TAXCLAIMAMOUNTINCURRENCY
                                            else REVENUERECOGNITION.AMOUNTINCURRENCY/REVENUESPLIT.AMOUNTINCURRENCY * REVENUESPLITGIFTAID.TAXCLAIMAMOUNTINCURRENCY 
                                        end
                                        when 1 then 
                                        case 
                                            when REVENUERECOGNITION.AMOUNTINCURRENCY > REVENUESPLIT.AMOUNTINCURRENCY 
                                            then PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY 
                                            else REVENUERECOGNITION.AMOUNTINCURRENCY/REVENUESPLIT.AMOUNTINCURRENCY * PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY 
                                        end
                                        else 0
                                        end
                                    else 0
                                end, 0) 
                            else 0
                        end as [TAXCLAIMAMOUNT],
                        @CURRENCYISOCODE ISO4217,
                        @CURRENCYDECIMALDIGITS DECIMALDIGITS,
                        @CURRENCYSYMBOL CURRENCYSYMBOL,
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE SYMBOLDISPLAYSETTINGCODE              
                    from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUERECOGNITION
                        left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                        inner join MYSITESREV_CTE on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
                        inner join dbo.GROUPMEMBER GM on REVENUERECOGNITION.CONSTITUENTID = GM.MEMBERID
                        left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                        left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
                        left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID =
                            case 
                                when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
                                when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
                                when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
                                when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
                                else DESIGNATION.DESIGNATIONLEVEL1ID
                            end
                        left join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = REVENUESPLIT.ID
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = ELIGIBLEGIFTAID.ID
                        left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = REVENUESPLIT.ID
                    where 
                        @GROUPINCLUDESMEMBERGIVING = 1
                        and GM.GROUPID = @CONSTITUENTID
                        and REVENUERECOGNITION.EFFECTIVEDATE > dateadd(year,-5,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@CURRENTDATE,1)) 
                        and REVENUERECOGNITION.EFFECTIVEDATE < dateadd(year,1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0)) 
                        and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DL1.DESIGNATIONLEVELCATEGORYCODEID)
                        and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
                        and (@CAMPAIGNID is null or REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID)
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= REVENUERECOGNITION.EFFECTIVEDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= REVENUERECOGNITION.EFFECTIVEDATE)) 
                        or (GMDR.DATEFROM <= REVENUERECOGNITION.EFFECTIVEDATE and GMDR.DATETO >= REVENUERECOGNITION.EFFECTIVEDATE))
                        and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(MYSITESREV_CTE.ID,@CURRENTAPPUSERID) = 1

                    union all

                    -- group gifts, pledges, or payments

                    select
                        REVENUERECOGNITION.ID as REVENUERECOGNITIONID,
                        REVENUESPLIT.ID as REVENUESPLITID,
                        case 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3) then 0                -- Gift

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 or MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 then 1    --Pledge

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2 then 2                    --Payment (Pledge payment)

                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then 0    --Auction donation

                        end as TYPE,
                        REVENUERECOGNITION.AMOUNTINCURRENCY RECOGNITIONAMOUNT,
                        REVENUESPLIT.AMOUNTINCURRENCY as REVENUESPLITAMOUNT,
                        dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(REVENUERECOGNITION.EFFECTIVEDATE,0) FISCALYEAR,
                        MYSITESREV_CTE.TRANSACTIONTYPECODE,
                        REVENUESPLIT.APPLICATIONCODE,
                        0 TAXCLAIMAMOUNT,
                        @CURRENCYISOCODE ISO4217,
                        @CURRENCYDECIMALDIGITS DECIMALDIGITS,
                        @CURRENCYSYMBOL CURRENCYSYMBOL,
                        @CURRENCYSYMBOLDISPLAYSETTINGCODE SYMBOLDISPLAYSETTINGCODE                    
                    from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUERECOGNITION
                        left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                        inner join MYSITESREV_CTE on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
                        left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
                        left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID =
                            case 
                                when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
                                when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
                                when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
                                when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
                                else DESIGNATION.DESIGNATIONLEVEL1ID
                            end
                        left join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = REVENUESPLIT.ID
                        left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = ELIGIBLEGIFTAID.ID
                        left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = REVENUESPLIT.ID
                    where 
                        REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
                        and REVENUERECOGNITION.EFFECTIVEDATE > dateadd(year,-5,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@CURRENTDATE,1)) 
                        and REVENUERECOGNITION.EFFECTIVEDATE < dateadd(year,1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0)) 
                        and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DL1.DESIGNATIONLEVELCATEGORYCODEID)
                        and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
                        and (@CAMPAIGNID is null or REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID)
                        and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(MYSITESREV_CTE.ID,@CURRENTAPPUSERID) = 1
                ) as DATA
                where TYPE is not NULL
                group by FISCALYEAR, ISO4217, DECIMALDIGITS, CURRENCYSYMBOL, SYMBOLDISPLAYSETTINGCODE;