USP_SEGMENTAVERAGEGIFTAMOUNT_GETSQL

CREATE procedure dbo.[USP_SEGMENTAVERAGEGIFTAMOUNT_GETSQL]
(
  @SEGMENTID uniqueidentifier = null,
  @NUMYEARS int = 5,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1, /* 0 = base, 1 = organization */
  @SQL nvarchar(max) output,
  @SEGMENTNAME nvarchar(100) output,
  @RECORDTYPEID uniqueidentifier output,
  @CURRENCYISOCURRENCYCODE nvarchar(6) output,
  @CURRENCYDECIMALDIGITS tinyint output,
  @CURRENCYSYMBOL nvarchar(10) output,
  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint output

)
as
begin
  set nocount on;

  declare @RECORDSOURCEID 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 @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;

  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])';
      end
end;