USP_CONSTITUENT_RECOGNITIONSUMMARYGET

      CREATE procedure dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYGET
      (
        @CONSTITUENTID uniqueidentifier,
        @RECOGNITIONTOTALNUMBER int = null output,
        @RECOGNITIONTOTALAMOUNT money = null output,
        @RECOGNITIONAVERAGEAMOUNT money = null output,
        @RECOGNITIONMOSTFREQUENTAMOUNT money = null output,
        @RECOGNITIONTOTALYEARS int = null output,
        @RECOGNITIONCONSECUTIVEYEARS int = null output,
        @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null output,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @SITEFILTERMODE tinyint = 0,
        @SITESSELECTED xml = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @TOTALRECOGNITIONWITHGIFTAID money = null output,
        @CURRENCYID uniqueidentifier = null
      )
      as
        set nocount on

        declare @CURRENTDATE datetime;
        declare @CURRENTDATEEARLIESTTIME datetime;
        declare @FIRSTDAYTHISFISCALYEAR datetime;
        declare @FIRSTDAYPREVIOUSFISCALYEAR datetime;
        declare @ISSYSADMIN bit;    
        declare @DECIMALDIGITS tinyint;
        declare @ROUNDINGTYPECODE tinyint;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        set @CURRENTDATE = getdate();
        set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
        set @FIRSTDAYTHISFISCALYEAR = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0);
        set @FIRSTDAYPREVIOUSFISCALYEAR = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0);
        set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);        

        if @CURRENCYID is null
            set @CURRENCYID = @ORGANIZATIONCURRENCYID

        select 
            @ROUNDINGTYPECODE = ROUNDINGTYPECODE,
            @DECIMALDIGITS = DECIMALDIGITS
        from
            dbo.CURRENCY
        where
            CURRENCY.ID = @CURRENCYID

        create table #RECOGNITIONS (
            ID uniqueidentifier primary key,
            REVENUESPLITID uniqueidentifier,
            TRANSACTIONTYPECODE tinyint,
            FIRSTDAYOFEFFECTIVEDATEYEAR datetime
        );

        insert into #RECOGNITIONS (ID, REVENUESPLITID, TRANSACTIONTYPECODE, FIRSTDAYOFEFFECTIVEDATEYEAR)
        select
            RR.ID,
            RR.REVENUESPLITID,
            RR.TRANSACTIONTYPECODE,
            dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0) as FIRSTDAYOFEFFECTIVEDATEYEAR
        from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_NOCONSTITNAME(@CURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORGANIZATIONCURRENCYID) RR
        where RR.CONSTITUENTID = @CONSTITUENTID;

        with AIC_CTE (ID, CONSTITUENTID, AMOUNTINCURRENCY, EFFECTIVEDATE, APPLICATIONCODE, TRANSACTIONTYPECODE, REVENUECONSTITUENTID, DATE, REVENUEID, REVENUESPLITID)
        as 
        (
          select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID
          from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, null, 2, 1) RR

          union all

          select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID
          from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, null, 2, 1) RC
        )

        select
          @RECOGNITIONTOTALNUMBER = count(RR.ID),
          @RECOGNITIONTOTALAMOUNT = sum(AIC_CTE.AMOUNTINCURRENCY),
          @RECOGNITIONAVERAGEAMOUNT = cast(avg(cast(AIC_CTE.AMOUNTINCURRENCY as decimal(20,5))) as money),
          @TOTALRECOGNITIONWITHGIFTAID = cast(sum(cast(AIC_CTE.AMOUNTINCURRENCY + coalesce(dbo.UFN_RECOGNITIONCREDIT_GETTAXCLAIMAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID, dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID, @CURRENCYID), AIC_CTE.AMOUNTINCURRENCY, RR.TRANSACTIONTYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, @CURRENCYID), 0) as decimal(20,5))) as money)
        from #RECOGNITIONS RR
          inner join AIC_CTE
            on AIC_CTE.ID = RR.ID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
          left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
          (RR.TRANSACTIONTYPECODE = 1 or --Pledge

           RR.TRANSACTIONTYPECODE = 7 or --Auction donation

           RR.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

           RR.TRANSACTIONTYPECODE = 3 or --Matching Gift Claim

          (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift

          (RR.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 7) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0)))) --Payment

          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
          and    exists 
          (
            select top 1 FTLISUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM FTLISUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FTLISUB.ID) REVSITES
            where FTLISUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and case 
                when @ISSYSADMIN = 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
            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)
            )
          )

        set @RECOGNITIONTOTALNUMBER = coalesce(@RECOGNITIONTOTALNUMBER, 0);
        set @RECOGNITIONTOTALAMOUNT = coalesce(@RECOGNITIONTOTALAMOUNT, 0);

        --MODE

        select top 1
          @RECOGNITIONMOSTFREQUENTAMOUNT = X.AMOUNT
        from
          (select
            dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @CURRENCYID) as AMOUNT,
            count(RR.ID) TOTAL,
            max(count(RR.ID)) over () MAXCOUNT
          from
            #RECOGNITIONS RR
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR.REVENUESPLITID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
          where
            (RR.TRANSACTIONTYPECODE = 1 or --Pledge

             RR.TRANSACTIONTYPECODE = 7 or --Auction donation

             RR.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

             RR.TRANSACTIONTYPECODE = 3 or --Matching Gift Claim

            (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift

            (RR.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 7) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0)))) --Payment

            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
            and    exists 
            (
              select top 1 FTLISUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM FTLISUB
              cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FTLISUB.ID) REVSITES
              where FTLISUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
              -- Using a case statement since the standard site extension filters

              -- resulted in a poor plan

              and case 
                  when @ISSYSADMIN = 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
              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)
              )
            )                    
          group by
            dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @CURRENCYID)) X
        where
          X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
        order by
          X.AMOUNT desc;

        --TOTALYEARS

        select
          @RECOGNITIONTOTALYEARS = count(distinct(RR.FIRSTDAYOFEFFECTIVEDATEYEAR))
        from
          #RECOGNITIONS RR
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
          left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
          (RR.TRANSACTIONTYPECODE = 1 or --Pledge

           RR.TRANSACTIONTYPECODE = 7 or --Auction donation

           RR.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

          RR.TRANSACTIONTYPECODE = 3 or --Matching Gift Claim

          (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift

          (RR.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 7) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0)))) --Payment

          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
          and    exists 
          (
            select top 1 FTLISUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM FTLISUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FTLISUB.ID) REVSITES
            where FTLISUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and case 
                when @ISSYSADMIN = 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
            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)
            )
          )                

        --CONSECUTIVE YEARS

        declare @RECOGNITIONFISCALYEAR_FIRSTDAY datetime;
        declare @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY datetime;
        declare @CONTINUE bit;

        declare RECOGNITIONFISCALYEARCURSOR cursor local fast_forward for
        select
          RR.FIRSTDAYOFEFFECTIVEDATEYEAR FISCALYEAR_FIRSTDAY
        from
          #RECOGNITIONS RR
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
          left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
          (RR.TRANSACTIONTYPECODE = 1 or --Pledge

           RR.TRANSACTIONTYPECODE = 7 or --Auction donation

           RR.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

           RR.TRANSACTIONTYPECODE = 3 or --Matching Gift Claim

          (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift

          (RR.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 7) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0)))) --Payment

          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
          and    exists 
          (
            select top 1 FTLISUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM FTLISUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FTLISUB.ID) REVSITES
            where FTLISUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and case 
                when @ISSYSADMIN = 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
            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)
            )
          )                
        group by
          RR.FIRSTDAYOFEFFECTIVEDATEYEAR
        order by
          RR.FIRSTDAYOFEFFECTIVEDATEYEAR desc;

        set @RECOGNITIONCONSECUTIVEYEARS = 0;
        set @RECOGNITIONGIVENSINCEFISCALYEAR = null;

        open RECOGNITIONFISCALYEARCURSOR;
          fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONFISCALYEAR_FIRSTDAY;
          if @@FETCH_STATUS = 0 begin
            if @RECOGNITIONFISCALYEAR_FIRSTDAY > @FIRSTDAYTHISFISCALYEAR begin
              set @CONTINUE = 1;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
            end
            else if @RECOGNITIONFISCALYEAR_FIRSTDAY = @FIRSTDAYTHISFISCALYEAR or
              @RECOGNITIONFISCALYEAR_FIRSTDAY = @FIRSTDAYPREVIOUSFISCALYEAR begin
              set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
              set @CONTINUE = 1;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
            end
            else
              set @CONTINUE = 0;
          end

          while @@FETCH_STATUS = 0 and @CONTINUE = 1 begin
            if @RECOGNITIONCONSECUTIVEYEARS = 0 begin
              if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY > @FIRSTDAYTHISFISCALYEAR begin
                set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
                fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              end
              else if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = @FIRSTDAYTHISFISCALYEAR or
                @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = @FIRSTDAYPREVIOUSFISCALYEAR begin
                set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
                set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
                fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              end
              else
                set @CONTINUE = 0
            end
            else if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = dateadd(year, -1, @RECOGNITIONFISCALYEAR_FIRSTDAY) begin
              set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
              set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
            end
            else
              set @CONTINUE = 0;
          end
        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

        close RECOGNITIONFISCALYEARCURSOR;
        deallocate RECOGNITIONFISCALYEARCURSOR;

        if @RECOGNITIONCONSECUTIVEYEARS > 0
          set @RECOGNITIONGIVENSINCEFISCALYEAR = @RECOGNITIONFISCALYEAR_FIRSTDAY;

        drop table #RECOGNITIONS;