USP_DATALIST_CONSTITUENT_INHOUSEHOLD_REVENUESUMMARYBREAKDOWN

            CREATE procedure dbo.USP_DATALIST_CONSTITUENT_INHOUSEHOLD_REVENUESUMMARYBREAKDOWN
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITEFILTERMODE tinyint = 0,
                /*DanielCo [12/19/08] Dashboards don't support parameters that cannot 
                    be cast as strings like this collection so an error will occur if
                    user selects more sites than fits here (~73)*/
                @SITESSELECTED nvarchar(max) = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 1,
                @DATEFILTER tinyint, 
                @REVENUEFILTERID uniqueidentifier = null,
                @BREAKDOWNBY tinyint = 0,
                @CAMPAIGNFILTERMODE tinyint = 0,
                @CAMPAIGNSSELECTED nvarchar(max) = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) 
            with execute as OWNER
            as
                set nocount on;

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

                  if @STARTDATE is null and @ENDDATE is null
                    exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;
                  else
                    begin
                      set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
                      set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
                    end

                  /*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

                  declare @INCLUDEMEMBERGIVING bit = 0;

                  set @CONSTITUENTID = @HOUSEHOLDID;
                  set @HOUSEHOLDID = null;

                  if @HOUSEHOLDID is null
                    select
                      @ISGROUP = 1,
                      @INCLUDEMEMBERGIVING = 
                      case 
                          when GROUPDATA.GROUPTYPECODE = 0 or GROUPTYPE.INCLUDEMEMBERGIVING = 1 then 1 
                          else 0 
                       end
                    from dbo.GROUPDATA
                    left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
                    where GROUPDATA.ID = @CONSTITUENTID;

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

                  /* Get currency info */
                  set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    declare @ORIGINCODE tinyint
                    select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
                    from dbo.MULTICURRENCYCONFIGURATION;

                  if @CURRENCYCODE = 1
                    set @CURRENCYID = @ORGANIZATIONCURRENCYID;
                  else
                     set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);        

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


                  /* Get Revenue Splits IDs */
                  if object_id('tempdb..#TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS') is not null
                      drop table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS;


                  create table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS
                  (  
                      ID uniqueidentifier,
                      REVENUEID uniqueidentifier,
                      TRANSACTIONTYPECODE tinyint,
                      [DATE] datetime,
                  );

                declare @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);
                end

                select @SQL = @SQL + '
                  with CONSTITS_CTE as (
                    select @CONSTITUENTID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE' + char(13);

                    if @INCLUDEMEMBERGIVING = 1
                    set @SQL = @SQL + '
                      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
                  '

                  set @SQL = @SQL + '
                  )
                  insert into #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS(ID,REVENUEID,TRANSACTIONTYPECODE,DATE)
                  select FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,FINANCIALTRANSACTION.TYPECODE,cast(FINANCIALTRANSACTION.DATE as datetime)
                  from dbo.FINANCIALTRANSACTIONLINEITEM
                  inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID' + char(13);

                 if @REVENUEFILTERID is not null
                  set @SQL = @SQL + 'inner join dbo.UFN_REVENUEFILTER_BYID(''' + cast(@REVENUEFILTERID as nvarchar(36)) + ''') FILTERED on FINANCIALTRANSACTIONLINEITEM.ID = FILTERED.ID ' + char(13);

                 set @SQL = @SQL +
                    '
                    inner join dbo.FINANCIALTRANSACTION
                      on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUE_EXT
                        on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    inner join CONSTITS_CTE CONSTITS
                    on CONSTITS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                    where
                      FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and
                      -- Exclude order payment splits and use the order splits

                      REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
                      FINANCIALTRANSACTION.DELETEDON is null and
                      FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                      FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
                      (CONSTITS.STARTDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) >= CONSTITS.STARTDATE)
                      and
                      (CONSTITS.ENDDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) <= CONSTITS.ENDDATE)';

                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 = FINANCIALTRANSACTIONLINEITEM.ID
                        )' + char(13);
                end

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

                  /*remove based on site filter*/
                delete FILTERED 
                from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
                where
                    not exists 
                    (
                        select top 1 REVSITES.SITEID 
                        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
                        where
                        -- Using a case statement since the standard site extension filters

                        -- resulted in a poor plan

                        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)
                        )
                    );


                   with CONSTIT_CTE
                   as
                   (
                       select 
                        Year(case when @BREAKDOWNBY = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(FILTERED.DATE,0) else FILTERED.DATE end) [YEAR],
                        FILTERED.REVENUEID ID,
                        REVENUESPLIT.AMOUNTINCURRENCY AMOUNT
                        from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
                              left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                                @CURRENCYID, 
                                @ORGANIZATIONCURRENCYID, 
                                @CURRENCYDECIMALDIGITS, 
                                @CURRENCYROUNDINGTYPECODE) as REVENUESPLIT on FILTERED.ID = REVENUESPLIT.ID
                        where FILTERED.TRANSACTIONTYPECODE <> 2 --Exclude RGs

                            and FILTERED.TRANSACTIONTYPECODE <> 15 --Exclude membership installment plans

                   )

                   select 
                    CONSTIT.YEAR as [YEAR],
                    CONSTIT.TOTALNUMBER as TOTALNUMBER,
                    CONSTIT.TOTALAMOUNT - coalesce(CONSTITREFUNDS.AMOUNT, 0) as TOTALAMOUNT,
                    0 as TOTALAMOUNT_HOUSEHOLD,
                    @CURRENCYISOCURRENCYCODE as CURRENCYISOCURRENCYCODE,
                    @CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
                    @CURRENCYSYMBOL as CURRENCYSYMBOL,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                  from 
                    (
                       select YEAR,
                        count(distinct ID) TOTALNUMBER,
                        sum(AMOUNT) TOTALAMOUNT
                        from CONSTIT_CTE
                        group by [YEAR]
                       ) CONSTIT               
                    left join
                    (
                        select
                              Year(case when @BREAKDOWNBY = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(CREDIT.TRANSACTIONDATE,0) else CREDIT.TRANSACTIONDATE end) YEAR,
                              sum(CREDITITEM.TOTAL) AMOUNT
                        from CREDITITEM
                        inner join dbo.CREDIT
                              on CREDITITEM.CREDITID = CREDIT.ID
                        inner join dbo.SALESORDER
                              on CREDIT.SALESORDERID = SALESORDER.ID
                        inner join 
                            (
                              select distinct 
                                    SALESORDERPAYMENT.SALESORDERID, REVENUESPLIT_EXT.TYPE, REVENUESPLIT_EXT.TYPECODE
                              from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
                              inner join dbo.FINANCIALTRANSACTIONLINEITEM on FILTERED.ID = FINANCIALTRANSACTIONLINEITEM.ID 
                              inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                              inner join dbo.SALESORDERPAYMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
                              where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                            ) REVS
                          on SALESORDER.ID = REVS.SALESORDERID
                                and 
                                ((CREDITITEM.TYPE = REVS.TYPE) or (CREDITITEM.TYPECODE = 2 and REVS.TYPECODE = 0))
                        where CREDIT.TYPECODE = 0
                        group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(CREDIT.TRANSACTIONDATE,0) else CREDIT.TRANSACTIONDATE end)
                    ) CONSTITREFUNDS
                    on
                    CONSTITREFUNDS.YEAR = CONSTIT.YEAR             

                   order by YEAR;

                  return 0;