USP_DATALIST_CONSTITUENT_INHOUSEHOLD_RECOGNITIONSUMMARYBREAKDOWN

      CREATE procedure dbo.USP_DATALIST_CONSTITUENT_INHOUSEHOLD_RECOGNITIONSUMMARYBREAKDOWN
      (
        @CONSTITUENTID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier,
        @SITEFILTERMODE tinyint = 0,
        @SITESSELECTED nvarchar(max) = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @CURRENCYCODE tinyint = 1,
        @DATEFILTER tinyint, 
        @HOUSEHOLDRECOGNITIONFILTERID uniqueidentifier = null,
        @BREAKDOWNBY tinyint = 0,
        @CAMPAIGNFILTERMODE tinyint = 0,
        @CAMPAIGNSSELECTED nvarchar(max) = null
      ) 
      with EXECUTE as OWNER
      as
        set nocount on;

          declare
          @STARTDATE datetime,
          @ENDDATE datetime,         
          @HOUSEHOLDID uniqueidentifier = null,
          @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
          @CURRENCYDECIMALDIGITS tinyint = 0,
          @CURRENCYSYMBOL nvarchar(5) = null,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
          @CURRENCYROUNDINGTYPECODE tinyint = 0,
          @CONSTITID uniqueidentifier,
          @DATEFROM datetime,
          @DATETO datetime;

          exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;


          /*Get Household ID*/
          select top(1) @HOUSEHOLDID = GM.GROUPID
          from dbo.GROUPMEMBER as GM
          left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
          where GM.MEMBERID = @CONSTITUENTID
          and GD.GROUPTYPECODE = 0
          and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1

          --get the recognitions for the household constituent

          set @CONSTITUENTID = @HOUSEHOLDID


          declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          /* Get currency info */
          declare @CURRENCYID uniqueidentifier = null;
          if @CURRENCYCODE = 1
            set @CURRENCYID = @ORGANIZATIONCURRENCYID;
          else
              set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);        

          select
            @CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
            @CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
            @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE,    
            @CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
          from dbo.CURRENCY where ID = @CURRENCYID;

          /* Get Recognition IDs */
          if object_id('tempdb..#TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS') is not null
            drop table #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS;


          create table #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS
          (  
            ID uniqueidentifier
          );

          declare @SQL nvarchar(max) = '';
          declare @RECOGNITIONCREDITS_SQL nvarchar(max) = '';

          if @CAMPAIGNFILTERMODE != 0
          begin
            set @SQL = '
              declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
              insert into @CAMPAIGNFILTERTABLE
              select T.c.value(''(ID)[1]'',''uniqueidentifier'')
              from @CAMPAIGNSSELECTED.nodes(''/CAMPAIGNSSELECTED/ITEM'') T(c);' + char(13);
            set @RECOGNITIONCREDITS_SQL = replace(@SQL, '@CAMPAIGNFILTERTABLE', '@CAMPAIGNFILTERTABLE_RC');
          end

          select @SQL = @SQL + '
            with CONSTITS_CTE as (
              select @CONSTITUENTID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE
              union all
              select
                GROUPMEMBER.MEMBERID as CONSTITUENTID,
                case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
                case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
              from dbo.GROUPMEMBER
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
              where
                GROUPMEMBER.GROUPID = @CONSTITUENTID
            )
            insert into #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS(ID)
            select distinct 
                REVENUERECOGNITION.ID
            from dbo.REVENUERECOGNITION ' + char(13);

        select @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + replace(@SQL, 'REVENUERECOGNITION', 'RECOGNITIONCREDIT')

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        declare @REVENUEFILTERID uniqueidentifier
        declare @REVENUEFILTERFUNCTION nvarchar(250)
        declare @PLEDGERECOGNITIONCODE tinyint
        if @HOUSEHOLDRECOGNITIONFILTERID is not null
        begin
            select 
                @REVENUEFILTERID = RECOGNITIONFILTER.REVENUEFILTERID,
                @PLEDGERECOGNITIONCODE = RECOGNITIONFILTER.PLEDGERECOGNITIONCODE,
                @REVENUEFILTERFUNCTION = REVENUEFILTER.FUNCTIONNAME
            from 
                dbo.RECOGNITIONFILTER 
                left join dbo.REVENUEFILTER on REVENUEFILTER.ID = RECOGNITIONFILTER.REVENUEFILTERID
            where
                RECOGNITIONFILTER.ID = @HOUSEHOLDRECOGNITIONFILTERID

            if @REVENUEFILTERID is not null and @REVENUEFILTERFUNCTION is not null and @REVENUEFILTERFUNCTION <> ''
            begin
                select @SQL = @SQL + '
                    inner join ' + @REVENUEFILTERFUNCTION + '() FILTERED on FILTERED.ID = REVENUERECOGNITION.REVENUESPLITID ' + CHAR(13);
            end

          set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'inner join dbo.UFN_RECOGNITIONCREDIT_VALIDFILTER(@HOUSEHOLDRECOGNITIONFILTERID) FILTERED on RECOGNITIONCREDIT.ID = FILTERED.ID ' + char(13);
        end

        set @SQL = @SQL + '
            inner join CONSTITS_CTE CONSTITS on CONSTITS.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID ' + char(13)

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        if @HOUSEHOLDRECOGNITIONFILTERID is not null
        begin
            select @SQL = @SQL + '
              left join dbo.RECOGNITIONFILTERRECOGNTIONTYPE on RECOGNITIONFILTERRECOGNTIONTYPE.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID
                  and RECOGNITIONFILTERRECOGNTIONTYPE.RECOGNITIONFILTERID = @HOUSEHOLDRECOGNITIONFILTERID ' + CHAR(13)

          if @PLEDGERECOGNITIONCODE <> 1
            begin
                select @SQL = @SQL + '
                  inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
              inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
              left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
              left join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID ' + CHAR(13)
            end
        end

        select @SQL = @SQL + '
          where
            (CONSTITS.STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= CONSTITS.STARTDATE)
            and
            (CONSTITS.ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= CONSTITS.ENDDATE) ' + char(13);

        set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
          '
          inner join CONSTITS_CTE CONSTITS
            on CONSTITS.CONSTITUENTID = RECOGNITIONCREDIT.CONSTITUENTID
          left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
          where
            (CONSTITS.STARTDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE >= CONSTITS.STARTDATE)
            and
            (CONSTITS.ENDDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE <= CONSTITS.ENDDATE)
            and            
            ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))';

        if @CAMPAIGNFILTERMODE != 0
        begin
            set @SQL = @SQL +
                'and exists (
                    select 1
                    from dbo.REVENUESPLITCAMPAIGN
                        inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                    where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUERECOGNITION.REVENUESPLITID
                ) ' + char(13);

            set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
                'and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or exists (
                    select 1
                    from dbo.REVENUESPLITCAMPAIGN
                        inner join @CAMPAIGNFILTERTABLE_RC CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                    where REVENUESPLITCAMPAIGN.REVENUESPLITID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                ))' + char(13);
        end

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        if @HOUSEHOLDRECOGNITIONFILTERID is not null
        begin
            if @PLEDGERECOGNITIONCODE <> 1
            begin
              select @SQL = @SQL + '
                  and
                  (
                        -- Find pledges

                        (FINANCIALTRANSACTION.TYPECODE = 1 and REVENUESPLIT_EXT.APPLICATIONCODE = 0)
                        or
                        -- Find payments without pledge recognition

                        (
                            (FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 2)
                            and
                            (not exists (select top 1 RR.ID from dbo.REVENUERECOGNITION as RR inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = RR.REVENUESPLITID where RR.CONSTITUENTID = @CONSTITUENTID and FTLI.FINANCIALTRANSACTIONID = PLEDGE.ID))
                        )
                  ) ' + char(13)
            end

            select @SQL = @SQL + '
              and 
              (
                  RECOGNITIONFILTERRECOGNTIONTYPE.ID is not null
                  or not exists(select top 1 ID from dbo.RECOGNITIONFILTERRECOGNTIONTYPE RSUB where RSUB.RECOGNITIONFILTERID = @HOUSEHOLDRECOGNITIONFILTERID)
              ) ' + char(13)
        end

        exec sp_executesql @SQL, N'@CONSTITUENTID uniqueidentifier, @HOUSEHOLDRECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
        @CONSTITUENTID, @HOUSEHOLDRECOGNITIONFILTERID, @STARTDATE,  @ENDDATE, @CAMPAIGNSSELECTED;

        exec sp_executesql @RECOGNITIONCREDITS_SQL, N'@CONSTITUENTID uniqueidentifier, @HOUSEHOLDRECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
        @CONSTITUENTID, @HOUSEHOLDRECOGNITIONFILTERID, @STARTDATE,  @ENDDATE, @CAMPAIGNSSELECTED;

        /*remove based on site filter*/
        delete FILTERED 
        from #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
        inner join dbo.REVENUERECOGNITION on FILTERED.ID = REVENUERECOGNITION.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
        where
          not exists 
          (
            select top 1 FINANCIALTRANSACTIONLINEITEM_SUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM FINANCIALTRANSACTIONLINEITEM_SUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM_SUB.ID) REVSITES
            where FINANCIALTRANSACTIONLINEITEM_SUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and case 
                when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 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)
            )
          )

        delete FILTERED 
        from #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
        inner join dbo.RECOGNITIONCREDIT on FILTERED.ID = RECOGNITIONCREDIT.ID
        where not exists (
            select top 1 RECOGNITIONCREDIT.ID 
            from dbo.RECOGNITIONCREDIT
            where RECOGNITIONCREDIT.ID = FILTERED.ID
              -- Using a case statement since the standard site extension filters

              -- resulted in a poor plan

              and case 
                when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                when exists (
                    select 1 
                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                    where SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) or (SITEID is null and dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) 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 = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID)
                  )
              )
          );              

        select 
          CONSTIT.YEAR as YEAR,
          CONSTIT.TOTALNUMBER as RECOGNITIONTOTALNUMBER,
          CONSTIT.TOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
          CONSTIT.CURRENCYISOCURRENCYCODE as CURRENCYISOCURRENCYCODE,
          CONSTIT.CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
          CONSTIT.CURRENCYSYMBOL as CURRENCYSYMBOL, 
          CONSTIT.CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
        from 
          (
            select 
              Year(case when @BREAKDOWNBY = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(RECOGNITION.EFFECTIVEDATE,0) else RECOGNITION.EFFECTIVEDATE end) YEAR,
              count(distinct RECOGNITION.ID) TOTALNUMBER,
              isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0) TOTALAMOUNT,
              @CURRENCYISOCURRENCYCODE CURRENCYISOCURRENCYCODE,
              @CURRENCYDECIMALDIGITS CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL CURRENCYSYMBOL,
              @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE
            from 
            (
              select
                RR.ID,
                EFFECTIVEDATE,
                AMOUNTINCURRENCY
              from 
                #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID              

              union

              select 
                RC.ID,
                EFFECTIVEDATE,
                AMOUNTINCURRENCY
              from 
                #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID
            ) RECOGNITION
            group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(RECOGNITION.EFFECTIVEDATE,0) else RECOGNITION.EFFECTIVEDATE end)
          ) CONSTIT         
          order by YEAR;

      return 0;