USP_CONSTITUENT_5YEARRECOGNITIONGET

            CREATE procedure dbo.USP_CONSTITUENT_5YEARRECOGNITIONGET
            (
                @CONSTITUENTID uniqueidentifier,
                @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null,
                @INCLUDERECOGNITIONALLDATES bit = 0,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @CAMPAIGNID 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 @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                declare @RANGESTARTDATE datetime;
                set @RANGESTARTDATE = dateadd(year,-5,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(getdate(),1))

                declare @RANGEENDDATE datetime;
                set @RANGEENDDATE = dateadd(year,1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(getdate(),0))

                declare @CURRENTDATEEARLIESTTIME datetime
                set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                declare @ENDDATE datetime
                select @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(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');

                with MYSITESREV_CTE as 
                (
                    select
                        ID,
                        TRANSACTIONTYPECODE
                    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                        
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RECOGNITION.EFFECTIVEDATE, 0) FISCALYEAR,
                    sum(case when ISGIFT = 1 then RECOGNITIONAMOUNT else 0 end) as GIFTS,
                    sum(case when ISPLEDGE = 1 then RECOGNITIONAMOUNT else 0 end) as PLEDGES,
                    sum(case when ISPAYMENT = 1 then RECOGNITIONAMOUNT else 0 end) as PAYMENTS,
                    0 as WRITEOFFS,
                    count(distinct case when ISGIFT = 1 or ISPLEDGE = 1 then RECOGNITIONID else null end) as NUMBER,
                    0 as PLEDGEBALANCE,
                    isnull(sum(TAXCLAIMAMOUNT), 0) TAXCLAIMAMOUNT,
                    @CURRENCYISOCODE ISO4217,
                    @CURRENCYDECIMALDIGITS DECIMALDIGITS,
                    @CURRENCYSYMBOL CURRENCYSYMBOL,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE SYMBOLDISPLAYSETTINGCODE          
                from
                (
                    select
                        REVENUERECOGNITION.ID as RECOGNITIONID,
                        REVENUERECOGNITION.EFFECTIVEDATE,
                        case 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0, 3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)) then 1 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13) then 1
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then 1
                            else 0 
                        end ISGIFT,
                        case 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 or MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 then 1 -- Pledge or matching gift claim

                            else 0
                        end ISPLEDGE,
                        case 
                            when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2 then 1
                            else 0
                        end ISPAYMENT,
                        REVENUERECOGNITION.AMOUNTINCURRENCY RECOGNITIONAMOUNT,
                        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 REVENUERECOGNITION.AMOUNTINCURRENCY 
                                end
                            else 0
                        end, 0) [TAXCLAIMAMOUNT]       
                    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
                        left join MYSITESREV_CTE on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
                        left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = MYSITESREV_CTE.ID
                        left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID                    
                        left join dbo.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 > @RANGESTARTDATE and
                        REVENUERECOGNITION.EFFECTIVEDATE < @RANGEENDDATE and
                        (@INCLUDERECOGNITIONALLDATES = 1 or REVENUERECOGNITION.EFFECTIVEDATE <= @ENDDATE) 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 (@ISADMIN = 1 or 
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUERECOGNITION.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, REVENUERECOGNITION.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            )
                ) as RECOGNITION
                group by dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RECOGNITION.EFFECTIVEDATE, 0)
                order by dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RECOGNITION.EFFECTIVEDATE, 0);