USP_HOUSEHOLDSMARTFIELD_AGGREGATEREVENUEAMOUNTS

CREATE procedure dbo.USP_HOUSEHOLDSMARTFIELD_AGGREGATEREVENUEAMOUNTS
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @TYPECODES xml = null,
    @DESIGNATIONS xml = null,
    @AMOUNTCODES xml = null,
    @CALCULATIONTYPE tinyint = 0,
    @ASOF datetime = null,
    @CAMPAIGNS xml = null,
    @DATETYPECODE tinyint = null,
    @STARTDATERECENTDATEUNITCODE tinyint = null,
    @STARTDATERECENTDATEINTERVAL int = null,
    @INCLUDEDATATYPECODE tinyint = 0, /*0: "Include data only from current household", 1: "Include historical data from current household members*/
    @CURRENCYID uniqueidentifier = null
)
with execute as owner
as

    /* This smart field will return an aggregation of revenue values for all households in the database. */

    set nocount on;    


    --Calculate start and end dates

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

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @ORIGINCODE tinyint;
  declare @ASOFDATE datetime = getdate();

  set @CURRENCYID = isnull(@CURRENCYID, @ORGANIZATIONCURRENCYID);

  select @ORIGINCODE = coalesce([ORGANIZATIONAMOUNTORIGINCODE], 0) from dbo.[MULTICURRENCYCONFIGURATION];

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

  if @DATETYPECODE = 1
  begin
    set @COMPUTEDSTARTDATE = 
      case @STARTDATERECENTDATEUNITCODE
        when 0 then dateadd(year, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
        when 1 then dateadd(quarter, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
        when 2 then dateadd(month, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
        when 3 then dateadd(week, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
        when 4 then dateadd(day, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
      end;
    set @COMPUTEDENDDATE = null;
  end
  else
  begin
    -- Use "Specific dates" behavior for @DATETYPECODE = 1 and @DATETYPECODE is null for backwards compatibility

    set @COMPUTEDSTARTDATE = @STARTDATE;
    set @COMPUTEDENDDATE = @ENDDATE;
  end

    set @COMPUTEDSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@COMPUTEDSTARTDATE);
    set @COMPUTEDENDDATE = dbo.UFN_DATE_GETLATESTTIME(@COMPUTEDENDDATE);

    create table #REVENUEINFORMATION(
        REVENUEID uniqueidentifier,
        REVENUESPLITID uniqueidentifier,
        HOUSEHOLDID uniqueidentifier,
        AMOUNT money,
        DATE datetime
        primary key (REVENUEID, REVENUESPLITID, HOUSEHOLDID)
    );

    declare @AMOUNTCODESFILTER table(AMOUNTCODEID tinyint primary key);

    declare @INCLUDERECEIVED bit;
    declare @INCLUDEUNRECOGNIZED bit;
    declare @INCLUDEDEFERRED bit;

    if @AMOUNTCODES is null
        insert into @AMOUNTCODESFILTER(AMOUNTCODEID) values(0)
    else
        insert into @AMOUNTCODESFILTER(AMOUNTCODEID)
            select distinct T.c.value('(AMOUNTCODEID)[1]','tinyint')
            from @AMOUNTCODES.nodes('/AMOUNTCODES/ITEM') T(c)
    ;

    if exists (select AMOUNTCODEID from @AMOUNTCODESFILTER where AMOUNTCODEID = 0)
        set @INCLUDERECEIVED = 1;
    else
        set @INCLUDERECEIVED = 0;

    if exists (select AMOUNTCODEID from @AMOUNTCODESFILTER where AMOUNTCODEID = 1)
        set @INCLUDEUNRECOGNIZED = 1;
    else
        set @INCLUDEUNRECOGNIZED = 0;

    if exists (select AMOUNTCODEID from @AMOUNTCODESFILTER where AMOUNTCODEID = 2)
        set @INCLUDEDEFERRED = 1;
    else
        set @INCLUDEDEFERRED = 0;

    insert into #REVENUEINFORMATION(REVENUEID, REVENUESPLITID, HOUSEHOLDID, AMOUNT, DATE)
        select
            [DATA].REVENUEID,
            [DATA].REVENUESPLITID,
            [DATA].GROUPID,
            case when ([DATA].REVENUEDATE >= @COMPUTEDSTARTDATE OR @COMPUTEDSTARTDATE IS NULL) and ([DATA].REVENUEDATE <= @COMPUTEDENDDATE OR @COMPUTEDENDDATE IS NULL) THEN
            case when @INCLUDERECEIVED = 1 then
                case when @INCLUDEUNRECOGNIZED = 1 then
                    --Planned gift handling

                    case when REVENUE.TRANSACTIONTYPECODE = 4 then
                        case when dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUE.ID) = 0 then  --Deferred

                            case when @INCLUDEDEFERRED = 1 then
                                [REVENUE].[AMOUNTINCURRENCY]
                            else
                                -1
                            end
                        else
                         (select
                            [PLEDGEBALANCEINCURRENCY].[BALANCEINCURRENCY]
                          from 
                            dbo.[UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ASOFDATE, @ORIGINCODE, null) [PLEDGEBALANCEINCURRENCY]
                          where  
                            [PLEDGEBALANCEINCURRENCY].[ID] = [REVENUE].[ID]) --The unrecognized portion.

                        end
          when REVENUE.TRANSACTIONTYPECODE = 2 then
            -1
                    else
                        --If we're including pledge balances, we'll end up using the full pledge amount to avoid double calculations.

                        case when REVENUESPLIT.APPLICATIONCODE = 2 then
                            -1
                        else
                            [REVENUESPLIT].[AMOUNTINCURRENCY]
                        end
                    end
                else
                    case when REVENUE.TRANSACTIONTYPECODE = 4 then
                        case when @INCLUDEDEFERRED = 1 then
                            case when dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUE.ID) = 0 then  --Deferred

                                [REVENUE].[AMOUNTINCURRENCY]
                            else
                                -1
                            end
                        else
                            -1
                        end
                    when REVENUE.TRANSACTIONTYPECODE in (1,2) then
                        -1
                    else
                        [REVENUESPLIT].[AMOUNTINCURRENCY]
                    end
                end
            when @INCLUDEUNRECOGNIZED = 1 then
                --Planned gift handling.

                case when REVENUE.TRANSACTIONTYPECODE = 4 then
                    case when dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUE.ID) = 0 then  --Deferred

                        case when @INCLUDEDEFERRED = 1 then
                            [REVENUE].[AMOUNTINCURRENCY]
                        else
                            -1
                        end
                    else
                       (select
                          [PLEDGEBALANCEINCURRENCY].[BALANCEINCURRENCY]
                        from 
                          dbo.[UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ASOFDATE, @ORIGINCODE, null) [PLEDGEBALANCEINCURRENCY]
                        where  
                          [PLEDGEBALANCEINCURRENCY].[ID] = [REVENUE].[ID]) --The unrecognized portion.

                    end
                when REVENUE.TRANSACTIONTYPECODE = 1 then
                  (select
                    [PLEDGEBALANCEINCURRENCY].[BALANCEINCURRENCY]
                  from 
                    dbo.[UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ASOFDATE, @ORIGINCODE, null) [PLEDGEBALANCEINCURRENCY]
                  where  
              [PLEDGEBALANCEINCURRENCY].[ID] = [REVENUE].[ID])
                else
                    -1
                end
            when @INCLUDEDEFERRED = 1 then
                case when REVENUE.TRANSACTIONTYPECODE = 4 then
                    case when dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUE.ID) = 0 then  --Deferred

                        [REVENUE].[AMOUNTINCURRENCY]
                    else
                        -1
                    end
                else
                    -1
                end
            else
                -1
            end
            else 0 end as AMOUNT,
      [DATA].REVENUEDATE
    from dbo.UFN_GROUP_REVENUESPLITS_FORHOUSEHOLDSMARTFIELD(null, @TYPECODES, @DESIGNATIONS, @CAMPAIGNS, 0, @ASOF, 1, null, null, null) as [DATA]
    inner join dbo.[UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) [REVENUE] on [DATA].[REVENUEID] = [REVENUE].[ID]
    inner join dbo.[UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) [REVENUESPLIT] on [DATA].[REVENUESPLITID] = [REVENUESPLIT].[ID]
    where  ((@INCLUDEDATATYPECODE = 0 and [DATA].GROUPMEMBERSHIPISHISTORICAL = 0) or (@INCLUDEDATATYPECODE = 1))
      and not [DATA].GROUPID is null

    /*
       Calculate totals based on calculation type. 
       The outer joins onto the CONSTITUENTFILTER.CONSTITUENTID column will have the effect of bringing back a single row per household.
    */
    if @CALCULATIONTYPE = 0 --Max(amount)

        select
            REVENUEINFORMATION.HOUSEHOLDID,
            coalesce(max(REVENUEINFORMATION.AMOUNT),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 1 --Min(amount)

       select
            REVENUEINFORMATION.HOUSEHOLDID,
            coalesce(min(REVENUEINFORMATION.AMOUNT),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 2 --Sum(amount)

       select
            REVENUEINFORMATION.HOUSEHOLDID,
            coalesce(sum(REVENUEINFORMATION.AMOUNT),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 3 --Avg(amount) 

       select
            REVENUEINFORMATION.HOUSEHOLDID,
            coalesce(avg(REVENUEINFORMATION.AMOUNT),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID

   else if @CALCULATIONTYPE = 4 --Min(date)

        select
          REVENUEINFORMATION.HOUSEHOLDID,
           coalesce(min(REVENUEINFORMATION.DATE),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
               (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 5 --Max(date)

        select
          REVENUEINFORMATION.HOUSEHOLDID,
           coalesce(max(REVENUEINFORMATION.DATE),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
               (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 6 --Largest revenue date

        select distinct
            REVENUEINFORMATION.HOUSEHOLDID,
            REVENUEINFORMATION.DATE
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            (REVENUEINFORMATION.REVENUEID = (
                select
                    top 1 SUBREVENUEINFORMATION.REVENUEID
                from
                    #REVENUEINFORMATION as SUBREVENUEINFORMATION
                where
                    (SUBREVENUEINFORMATION.HOUSEHOLDID = REVENUEINFORMATION.HOUSEHOLDID)
                order by
                    SUBREVENUEINFORMATION.AMOUNT desc,
                    SUBREVENUEINFORMATION.DATE
                )
            );

    else if @CALCULATIONTYPE = 7 --Giving years

        select
            REVENUEINFORMATION.HOUSEHOLDID,
            coalesce(datediff(year,min(REVENUEINFORMATION.DATE), max(REVENUEINFORMATION.DATE)),0)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            REVENUEINFORMATION.AMOUNT > 0
        group by
            REVENUEINFORMATION.HOUSEHOLDID

    else if @CALCULATIONTYPE = 8 --Distinct giving years

        select
            REVENUEDATES.HOUSEHOLDID,
            count(REVENUEDATES.YEAR)
        from
            (
                select distinct
                    REVENUEINFORMATION.HOUSEHOLDID,
                    datepart(year, REVENUEINFORMATION.DATE) as YEAR
                from #REVENUEINFORMATION as REVENUEINFORMATION
            ) as REVENUEDATES
        group by
            REVENUEDATES.HOUSEHOLDID

    else if @CALCULATIONTYPE = 9 --Is annual giver

        select
            REVENUEDATES.HOUSEHOLDID,
            case REVENUEDATES.YEARS - 1
                when coalesce(datediff(year, min(REVENUEINFORMATION.DATE), max(REVENUEINFORMATION.DATE)), 0) then
                    cast(1 as bit)
                else
                    cast(0 as bit)
                end
        from #REVENUEINFORMATION as REVENUEINFORMATION
        left join 
            (
                select
                    REVENUEINFORMATION.HOUSEHOLDID,                    
                    count(datepart(year, REVENUEINFORMATION.DATE)) as YEARS
                from
                    #REVENUEINFORMATION as REVENUEINFORMATION
                group by
                    REVENUEINFORMATION.HOUSEHOLDID
            ) as REVENUEDATES on REVENUEDATES.HOUSEHOLDID = REVENUEINFORMATION.HOUSEHOLDID
        group by
            REVENUEDATES.HOUSEHOLDID,
            REVENUEDATES.YEARS
        order by REVENUEDATES.HOUSEHOLDID;
    else if @CALCULATIONTYPE = 10 --Count(REVENUE)

        select
            REVENUEINFORMATION.HOUSEHOLDID,
            count(REVENUEINFORMATION.REVENUEID)
        from #REVENUEINFORMATION as REVENUEINFORMATION 
        where
            (REVENUEINFORMATION.AMOUNT > 0)
        group by
            REVENUEINFORMATION.HOUSEHOLDID;   
   else if @CALCULATIONTYPE = 11 --First gift amount

        select
            REVENUEINFORMATION.HOUSEHOLDID,
            sum(REVENUEINFORMATION.AMOUNT)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            REVENUEINFORMATION.REVENUEID = 
                (select top 1 REVENUEID 
                 from #REVENUEINFORMATION as SUBREVENUEINFORMATION 
                 where SUBREVENUEINFORMATION.HOUSEHOLDID = REVENUEINFORMATION.HOUSEHOLDID
                 and SUBREVENUEINFORMATION.AMOUNT > 0 
                 order by SUBREVENUEINFORMATION.DATE asc)
        group by REVENUEINFORMATION.HOUSEHOLDID
  else if @CALCULATIONTYPE = 12 --Last gift amount

         select
            REVENUEINFORMATION.HOUSEHOLDID,
            sum(REVENUEINFORMATION.AMOUNT)
        from #REVENUEINFORMATION as REVENUEINFORMATION
        where
            REVENUEINFORMATION.REVENUEID = 
                (select top 1 REVENUEID 
                 from #REVENUEINFORMATION as SUBREVENUEINFORMATION 
                 where SUBREVENUEINFORMATION.HOUSEHOLDID = REVENUEINFORMATION.HOUSEHOLDID
                 and SUBREVENUEINFORMATION.AMOUNT > 0 
                 order by SUBREVENUEINFORMATION.DATE desc)
        group by REVENUEINFORMATION.HOUSEHOLDID

    drop table #REVENUEINFORMATION;