USP_DATALIST_SEGMENTAVERAGEGIFTAMOUNT

CREATE procedure dbo.[USP_DATALIST_SEGMENTAVERAGEGIFTAMOUNT]
(
  @SEGMENTID uniqueidentifier = null,
  @NUMYEARS int = 5,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  declare @RESULTSTABLE table (
    [SEGMENTNAME] nvarchar(100), 
    [YEAR] int, 
    [AVERAGEGIFTAMOUNT] money,
    [CURRENCYISOCURRENCYCODE] nvarchar(6), 
    [CURRENCYDECIMALDIGITS] tinyint, 
    [CURRENCYSYMBOL] nvarchar(10), 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  declare @SEGMENTNAME nvarchar(100);
  declare @RECORDSOURCEID uniqueidentifier;
  declare @RECORDTYPEID uniqueidentifier;
  declare @MARKETINGRECORDTYPE tinyint;
  declare @MAILINGSTABLE table([ID] uniqueidentifier not null, [DATATABLE] nvarchar(128) not null, [MAILINGSEGMENTID] uniqueidentifier not null)
  declare @MAILINGSCOUNT int;
  declare @LISTMATCHBACKTABLE nvarchar(128);
  declare @DATATABLE nvarchar(128);
  declare @MAILINGSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @GIFTIDSETNAME nvarchar(128);
  declare @SQL nvarchar(max);
  declare @INNERDONORSQL nvarchar(max);
  declare @MATCHBACKFIELD nvarchar(20);

  select
    @SEGMENTNAME = [MKTSEGMENT].[NAME],
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @RECORDTYPEID = dbo.[UFN_MKTSEGMENT_GETRECORDTYPE]([MKTSEGMENT].[ID]),
    @MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
    @LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
    @MATCHBACKFIELD = (case when [MKTSEGMENT].[SEGMENTTYPECODE] = 3 then '[FINDERNUMBER]' else '[DONORID]' end) --Revenue segments can only be used in acknowledgement mailings which require finder number instead of donor ID for matchback.

  from dbo.[MKTSEGMENT]
  where [MKTSEGMENT].[ID] = @SEGMENTID;

  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  select 
    @CURRENCYISOCURRENCYCODE = [ISO4217],
    @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
  from dbo.[CURRENCY]
  where [ID] = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  insert into @MAILINGSTABLE
    select distinct
      [MKTSEGMENTATION].[ID],
      dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID]),
      [MKTSEGMENTATIONSEGMENT].[ID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID;

  --Check if the segment exists, if not, don't execute any code below, just return nothing...

  if @RECORDSOURCEID is not null and exists(select top 1 1 from @MAILINGSTABLE)
    begin
      select @MAILINGSCOUNT = count([ID])
      from @MAILINGSTABLE;

      set @SQL = 'select' + char(13) +
                 '  @SEGMENTNAME as [SEGMENTNAME],' + char(13) +
                 '  year([DONORS].[DATE]) as [YEAR],' + char(13) +
                 '  avg([DONORS].[AMOUNT]) as [AVERAGEGIFTAMOUNT],' + char(13) +
                 '  @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],' + char(13) +
                 '  @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],' + char(13) +
                 '  @CURRENCYSYMBOL as [CURRENCYSYMBOL],' + char(13) +
                 '  @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]' + char(13);

      --Loop through all the activated mailings that use the segment and join to each mailing's activated data table...

      declare MAILINGCURSOR cursor local fast_forward for
        select [ID], [DATATABLE], [MAILINGSEGMENTID]
        from @MAILINGSTABLE;

      open MAILINGCURSOR;
      fetch next from MAILINGCURSOR into @SEGMENTATIONID, @DATATABLE, @MAILINGSEGMENTID;

      while (@@FETCH_STATUS = 0)
      begin
        select @GIFTIDSETNAME = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([NORMALGIFTIDSETREGISTERID]) from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @SEGMENTATIONID;

        if @INNERDONORSQL is not null
          set @INNERDONORSQL = @INNERDONORSQL + char(13) + space(6) + 'union' + char(13) + space(6);

        set @INNERDONORSQL = isnull(@INNERDONORSQL, '') + 'select' + char(13) + space(6) +
                                                          '  [GIFTIDSETS].[ID],' + char(13) + space(6) +
                                                          '  [GIFTIDSETS].' + @MATCHBACKFIELD + ',' + char(13) + space(6) +
                                                          '  [GIFTIDSETS].[ORGANIZATIONAMOUNT] as [AMOUNT],' + char(13) + space(6) +
                                                          '  [GIFTIDSETS].[DATE]' + char(13) + space(6) +
                                                          'from dbo.[' + @DATATABLE + '] [DATA]' + char(13);

        if @MARKETINGRECORDTYPE = 1  --Constituent Segment

        begin                 
          set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'inner join dbo.' + @GIFTIDSETNAME + ' [GIFTIDSETS] on [GIFTIDSETS].' + @MATCHBACKFIELD + ' = [DATA].' + @MATCHBACKFIELD + char(13);
        end
        else if @MARKETINGRECORDTYPE = 2  --List segment

        begin
          set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'inner join (select distinct [ID], [GIFTID] from dbo.[' + @LISTMATCHBACKTABLE + '] where [SEGMENTATIONID] = ''' + cast(@SEGMENTATIONID as nvarchar(36)) + ''') as [LISTMATCHBACK] on [LISTMATCHBACK].[ID] = [DATA].[DONORID]' + char(13);       

          set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'inner join dbo.' + @GIFTIDSETNAME + ' [GIFTIDSETS] on [GIFTIDSETS].[ID] = [LISTMATCHBACK].[GIFTID]' + char(13);       
        end
        else if @MARKETINGRECORDTYPE = 3  --Consolidated list segment

        begin
          set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'left join (select distinct' + char(13) + space(6) + 
                            '             [LISTDONORS].[ID],' + char(13) + space(6) + 
                            '             [LISTDONORS].[GIFTID]' + char(13) + space(6) + 
                            '           from dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS]' + char(13) + space(6) + 
                            '           inner join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [LISTDONORS].[ID]' + char(13) + space(6) + 
                            '           inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTLISTDATA].[SEGMENTLISTID]' + char(13) + space(6) + 
                            '           inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID]' + char(13) + space(6) + 
                            '           where [LISTDONORS].[SEGMENTATIONID] = ''' + cast(@SEGMENTATIONID as nvarchar(36)) + '''' + char(13) + space(6) + 
                            '           and [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID' + char(13) + space(6) + 
                            '          ) as [LISTMATCHBACK] on cast([LISTMATCHBACK].[ID] as varchar(36)) = [DATA].[DONORID]' + char(13);

          set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'inner join dbo.' + @GIFTIDSETNAME + ' [GIFTIDSETS] on [GIFTIDSETS].[ID] = [LISTMATCHBACK].[GIFTID] or cast([GIFTIDSETS].[DONORID] as varchar(36)) = [DATA].[DONORID]' + char(13);
        end

        set @INNERDONORSQL = @INNERDONORSQL + space(6) + 'where [DATA].[SEGMENTID] = ''' + cast(@MAILINGSEGMENTID as nvarchar(36)) + ''''

        fetch next from MAILINGCURSOR into @SEGMENTATIONID, @DATATABLE, @MAILINGSEGMENTID;
      end

      close MAILINGCURSOR;
      deallocate MAILINGCURSOR;

      set @SQL = @SQL + 
                 'from (' + @INNERDONORSQL + ') as [DONORS]' + char(13) +
                 'where year([DONORS].[DATE]) > year(GetDate()) - @NUMYEARS' + char(13) +
                 'and year([DONORS].[DATE]) <= year(GetDate())' + char(13) +
                 'group by year([DONORS].[DATE])' + char(13) +
                 'order by year([DONORS].[DATE])';

        --Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.

        begin try
          declare @SQLWITHJOINHINT nvarchar(max);
          set @SQLWITHJOINHINT = @SQL + char(13) + 'option (hash join, merge join);';
          insert into @RESULTSTABLE
            exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
              @SEGMENTID = @SEGMENTID, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
              @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
              @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
        end try
        begin catch
          if ERROR_NUMBER() = 8622
            insert into @RESULTSTABLE
              exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @NUMYEARS int, @CURRENTAPPUSERID uniqueidentifier, @RECORDTYPEID uniqueidentifier, @CURRENCYISOCURRENCYCODE nvarchar(6), @CURRENCYDECIMALDIGITS tinyint, @CURRENCYSYMBOL nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint', 
                @SEGMENTID = @SEGMENTID, @SEGMENTNAME = @SEGMENTNAME, @NUMYEARS = @NUMYEARS, 
                @CURRENTAPPUSERID = @CURRENTAPPUSERID, @RECORDTYPEID = @RECORDTYPEID,
                @CURRENCYISOCURRENCYCODE = @CURRENCYISOCURRENCYCODE, @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS,
                @CURRENCYSYMBOL = @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE;
          else
            begin
              exec dbo.[USP_RAISE_ERROR];
              return 1;
            end
        end catch
      end

  /* Insert correct number of blank years into the table depending on @NUMYEARS.  So if a gift was not given to the segment in 
     2007, insert 2007 with 0 for gifts. */
  if not @SEGMENTID is null
    begin
      declare @YEARTABLE table (
        [GIFTYEAR] integer
      )

      declare @NUMBEROFYEARSTOINSERT integer = @NUMYEARS - 1;
      declare @INSERTYEAR bit = 0;
      declare @YEAR int;

      while @NUMBEROFYEARSTOINSERT >= 0
      begin

        set @YEAR = year(getdate()) - @NUMBEROFYEARSTOINSERT;
        set @INSERTYEAR =  case when exists (select 1 from @RESULTSTABLE AS [RESULTS] where [RESULTS].[YEAR] = @YEAR) then 0 else 1 end;

        -- Insert blank year if no gifts given in that year.  

        if @INSERTYEAR = 1
          begin
            insert into @YEARTABLE
              select @YEAR;
          end

          set @NUMBEROFYEARSTOINSERT = @NUMBEROFYEARSTOINSERT - 1;
      end

      insert into @RESULTSTABLE
        select
          @SEGMENTNAME,
          [GIFTYEAR],
          0,
          @CURRENCYISOCURRENCYCODE,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE
        from @YEARTABLE;
    end

  select
    [SEGMENTNAME],
    [YEAR], 
    [AVERAGEGIFTAMOUNT],
    [CURRENCYISOCURRENCYCODE], 
    [CURRENCYDECIMALDIGITS], 
    [CURRENCYSYMBOL], 
    [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from @RESULTSTABLE
  order by [YEAR];

  return 0;