USP_DATALIST_APPEALPACKAGEPERFORMANCEREPORT

CREATE procedure dbo.[USP_DATALIST_APPEALPACKAGEPERFORMANCEREPORT]
(
    @APPEALSELECTIONID uniqueidentifier = null,
    @REVENUESELECTIONID uniqueidentifier = null,
    @CURRENCYCODE tinyint = 1,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
  set nocount on;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  declare @SQL nvarchar(max);
  declare @CURRENTAPPUSERID uniqueidentifier = dbo.[UFN_APPUSER_GETREPORTAPPUSERID](@REPORTUSERID, @ALTREPORTUSERID);

  if object_id('tempdb..#APPEALS') is not null
    drop table #APPEALS;

  create table #APPEALS (
    [ID] uniqueidentifier not null primary key,
    [NAME] nvarchar(100) collate database_default not null,
    [BASECURRENCYID] uniqueidentifier
  );

  set @SQL = 'with [SECURITY] ([CURRENTAPPUSERID], [ISSYSADMIN], [INNONRACROLE], [INNOGROUPROLE]) as ' + char(13) +
             '(' + char(13) +
             '  select' + char(13) +
             '  @CURRENTAPPUSERID as [CURRENTAPPUSERID],' + char(13) +
             '  dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) as [ISSYSADMIN],' + char(13) +
             '  dbo.[UFN_SECURITY_APPUSER_IN_NONRACROLE](@CURRENTAPPUSERID) as [INNONRACROLE],'+ char(13) +
             '  dbo.[UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE](@CURRENTAPPUSERID) as [INNOGROUPROLE] )' + char(13) +
             'insert into #APPEALS ([ID], [NAME], [BASECURRENCYID])' + char(13) +
             '  select' + char(13) +
             '    [ID],' + char(13) +
             '    [NAME],' + char(13) +
             '    [BASECURRENCYID]' + char(13) +
             '  from dbo.[APPEAL]' + char(13) +
             ' cross apply [SECURITY]' + char(13) +
             ' where ([SECURITY].[ISSYSADMIN] = 1 or dbo.[UFN_SITEALLOWEDFORUSER]([SECURITY].[CURRENTAPPUSERID], [APPEAL].[SITEID]) = 1)';

  if @APPEALSELECTIONID is not null
    set @SQL += char(13) + '  and [ID] in (select [ID] from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@APPEALSELECTIONID) + ')';

  exec sp_executesql @SQL, N'@CURRENTAPPUSERID uniqueidentifier', @CURRENTAPPUSERID = @CURRENTAPPUSERID;

  if object_id('tempdb..#SEGMENTS') is not null
    drop table #SEGMENTS;

  create table #SEGMENTS (
    [SEGMENTID] uniqueidentifier not null,
    [TESTSEGMENTID] uniqueidentifier,
    [APPEALID] uniqueidentifier not null,
    [PACKAGEID] uniqueidentifier not null,
    [QUANTITY] int not null
  );

  with [MAILINGS] ([ID], [APPEALID]) as
  (
    select
      [MKTSEGMENTATION].[ID],
      [APPEALS].[ID]
    from #APPEALS as [APPEALS]
    inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast([APPEALS].[ID] as nvarchar(36)) and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]
    where [MKTSEGMENTATION].[ACTIVE] = 1
  )
  insert into #SEGMENTS ([SEGMENTID], [TESTSEGMENTID], [APPEALID], [PACKAGEID], [QUANTITY])
    --Segments

    select
      [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
      null as [TESTSEGMENTID],
      [MAILINGS].[APPEALID],
      [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]
    from [MAILINGS]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MAILINGS].[ID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
    inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null

    union all

    --Test segments

    select
      [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
      [MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID],
      [MAILINGS].[APPEALID],
      [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]
    from [MAILINGS]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MAILINGS].[ID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
    inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID];

  --Check if we are a SQL Server Enterprise server before creating the proper index...

  if serverproperty('engineedition') = 3  --Enterprise

    create nonclustered index [IX_SEGMENTS_APPEALID_PACKAGEID] on #SEGMENTS ([APPEALID], [PACKAGEID]) include ([QUANTITY]) with (DATA_COMPRESSION=PAGE);
  else
    create nonclustered index [IX_SEGMENTS_APPEALID_PACKAGEID] on #SEGMENTS ([APPEALID], [PACKAGEID]) include ([QUANTITY]);



  set @SQL = 'with [MATCHINGIFTCLAIMS] ([REVENUEID], [SEGMENTID], [TESTSEGMENTID]) as' + char(13) +
             '(' + char(13) +
             '  select' + char(13) +
             '    [REVENUEMATCHINGGIFT].[ID],' + char(13) +
             '    [REVENUESEGMENT].[SEGMENTID],' + char(13) +
             '    [REVENUESEGMENT].[TESTSEGMENTID]' + char(13) +
             '  from dbo.[REVENUEMATCHINGGIFT]' + char(13) +
             '  left join dbo.[REVENUESEGMENT] on [REVENUESEGMENT].[REVENUEID] = [REVENUEMATCHINGGIFT].[MGSOURCEREVENUEID]' + char(13) +
             '),' + char(13) +

             '[REVENUES] ([ID], [APPEALID], [APPEALNAME], [SEGMENTID], [TESTSEGMENTID], [AMOUNT], [CURRENCYID]) as' + char(13) +
             '(' + char(13) +
             '  select' + char(13) +
             '    [REVENUE].[ID],' + char(13) +
             '    [REVENUE].[APPEALID],' + char(13) +
             '    [APPEALS].[NAME],' + char(13) +
                  -- If gift is a matching gift claim, then use the matching gift claim segment or test segment id

             '    isnull([REVENUESEGMENT].[SEGMENTID], [MATCHINGIFTCLAIMS].[SEGMENTID]) as [SEGMENTID],' + char(13) +
             '    isnull([REVENUESEGMENT].[TESTSEGMENTID], [MATCHINGIFTCLAIMS].[TESTSEGMENTID]) as [TESTSEGMENTID],' + char(13);

  if @CURRENCYCODE = 1
    set @SQL += '    [REVENUE].[ORGANIZATIONAMOUNT],' + char(13) +
                '    @ORGANIZATIONCURRENCYID' + char(13);
  else
    set @SQL += '    case' + char(13) +  
                '      when [REVENUE].[BASECURRENCYID] = @ORGANIZATIONCURRENCYID then' + char(13) +
                '        [REVENUE].[ORGANIZATIONAMOUNT]' + char(13) +
                '      when [REVENUE].[BASECURRENCYID] = [APPEALS].[BASECURRENCYID] then' + char(13) +
                '        [REVENUE].[AMOUNT]' + char(13) +
                '      when dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED]([REVENUE].[BASECURRENCYID], [APPEALS].[BASECURRENCYID], [REVENUE].[DATEADDED], 1, null) is not null then' + char(13) +
                '        dbo.[UFN_CURRENCY_CONVERT]([REVENUE].[AMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED]([REVENUE].[BASECURRENCYID], [APPEALS].[BASECURRENCYID], [REVENUE].[DATEADDED], 1, null))' + char(13) +
                '      else' + char(13) +
                '        dbo.[UFN_CURRENCY_CONVERTINVERSE]([REVENUE].[AMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED]([APPEALS].[BASECURRENCYID], [REVENUE].[BASECURRENCYID], [REVENUE].[DATEADDED], 1, null))' + char(13) +
                '    end,' + char(13) +
                '    [APPEALS].[BASECURRENCYID]' + char(13);

  set @SQL += '  from dbo.[REVENUE]' + char(13);

  if @REVENUESELECTIONID is not null
    set @SQL += '  inner join dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@REVENUESELECTIONID) + ' as [REVENUESELECTION] on [REVENUESELECTION].[ID] = [REVENUE].[ID]' + char(13);

  set @SQL += '  inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [REVENUE].[ID]' + char(13) +
              '  inner join dbo.[REVENUESPLIT_EXT] on [REVENUESPLIT_EXT].[ID] = [FINANCIALTRANSACTIONLINEITEM].[ID]' + char(13) +
              '  inner join #APPEALS as [APPEALS] on [APPEALS].[ID] = [REVENUE].[APPEALID]' + char(13) +
              '  left join dbo.[REVENUESEGMENT] on [REVENUESEGMENT].[REVENUEID] = [REVENUE].[ID]' + char(13) +
              '  left join [MATCHINGIFTCLAIMS] on [MATCHINGIFTCLAIMS].[REVENUEID] = [REVENUE].[ID]' + char(13) +
              '  where (' + char(13) +
              '    --Revenue (excluding pledge, recurring gift, and matching gift payments)' + char(13) +

              '    ([REVENUE].[TRANSACTIONTYPECODE] = 0 and [REVENUESPLIT_EXT].[APPLICATIONCODE] not in (2,3,7))' + char(13) +
              '    or' + char(13) +
              '    --Pledges' + char(13) +

              '    [REVENUE].[TRANSACTIONTYPECODE] in (1,3,4,8)' + char(13) +
              '  )' + char(13) +
              '),' + char(13);

  --SQL only allows you to concat 4000 chars at a time to a string, so we have to arbitrarily break up how we build this SQL statement...

  set @SQL += '[PACKAGETOTALS] ([APPEALID], [APPEALNAME], [PACKAGEID], [QUANTITY], [RESPONSES], [TOTALREVENUE], [CURRENCYID]) as' + char(13) +
              '(' + char(13) +
              '  select' + char(13) +
              '    [SEGMENTS].[APPEALID],' + char(13) +
              '    [APPEALS].[NAME],' + char(13) +
              '    [SEGMENTS].[PACKAGEID],' + char(13) +
              '    (select sum([S].[QUANTITY]) from #SEGMENTS as [S] where [S].[APPEALID] = [SEGMENTS].[APPEALID] and [S].[PACKAGEID] = [SEGMENTS].[PACKAGEID]) as [QUANTITY],' + char(13) +
              '    count([REVENUES].[ID]) as [RESPONSES],' + char(13) +
              '    isnull(sum([REVENUES].[AMOUNT]), 0) as [TOTALREVENUE],' + char(13) +
              '    isnull([REVENUES].[CURRENCYID], ' + (case when @CURRENCYCODE = 1 then '@ORGANIZATIONCURRENCYID' else '[APPEALS].[BASECURRENCYID]' end) + ')' + char(13) +
              '  from #SEGMENTS as [SEGMENTS]' + char(13) +
              '  left join [REVENUES] on [REVENUES].[SEGMENTID] = [SEGMENTS].[SEGMENTID] and (([REVENUES].[TESTSEGMENTID] is null and [SEGMENTS].[TESTSEGMENTID] is null) or [REVENUES].[TESTSEGMENTID] = [SEGMENTS].[TESTSEGMENTID])' + char(13) +
              '  left join #APPEALS as [APPEALS] on [APPEALS].[ID] = [SEGMENTS].[APPEALID]' + char(13) +
              '  group by [SEGMENTS].[APPEALID], [APPEALS].[NAME], [SEGMENTS].[PACKAGEID], isnull([REVENUES].[CURRENCYID], ' + (case when @CURRENCYCODE = 1 then '@ORGANIZATIONCURRENCYID' else '[APPEALS].[BASECURRENCYID]' end) + ')' + char(13) +
              '),' + char(13) +
              '[NONPACKAGETOTALS] ([APPEALID], [APPEALNAME], [RESPONSES], [TOTALREVENUE], [CURRENCYID]) as' + char(13) +
              '(' + char(13) +
              '  select' + char(13) +
              '    [REVENUES].[APPEALID],' + char(13) +
              '    [REVENUES].[APPEALNAME],' + char(13) +
              '    count([REVENUES].[ID]) as [RESPONSES],' + char(13) +
              '    isnull(sum([REVENUES].[AMOUNT]), 0) as [TOTALREVENUE],' + char(13) +
              '    [REVENUES].[CURRENCYID]' + char(13) +
              '  from [REVENUES]' + char(13) +
              '  where [REVENUES].[SEGMENTID] is null' + char(13) +
              '  group by [REVENUES].[APPEALID], [REVENUES].[APPEALNAME], [REVENUES].[CURRENCYID]' + char(13) +
              ')' + char(13);

  --SQL only allows you to concat 4000 chars at a time to a string, so we have to arbitrarily break up how we build this SQL statement...

  set @SQL += '--Appeals and packages with revenue' + char(13) +

              'select' + char(13) +
              '  [PACKAGETOTALS].[APPEALID],' + char(13) +
              '  [PACKAGETOTALS].[APPEALNAME],' + char(13) +
              '  [MKTPACKAGE].[ID] as [PACKAGEID],' + char(13) +
              '  [MKTPACKAGE].[NAME] as [PACKAGENAME],' + char(13) +
              '  [PACKAGETOTALS].[QUANTITY],' + char(13) +
              '  [PACKAGETOTALS].[RESPONSES],' + char(13) +
              '  [PACKAGETOTALS].[TOTALREVENUE],' + char(13) +
              '  cast((case when [PACKAGETOTALS].[QUANTITY] > 0 then cast([PACKAGETOTALS].[RESPONSES] as decimal(19,4)) / cast([PACKAGETOTALS].[QUANTITY] as decimal(19,4)) else 0 end) as decimal(19,4)) as [RESPONSERATE],' + char(13) +
              '  cast((case when [PACKAGETOTALS].[RESPONSES] > 0 then [PACKAGETOTALS].[TOTALREVENUE] / [PACKAGETOTALS].[RESPONSES] else 0 end) as money) as [AVGGIFT],' + char(13) +
              '  cast((case when [PACKAGETOTALS].[QUANTITY] > 0 then [PACKAGETOTALS].[TOTALREVENUE] / [PACKAGETOTALS].[QUANTITY] else 0 end) as money) as [REVENUEPERPIECE],' + char(13) +
              '  [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],' + char(13) +
              '  [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],' + char(13) +
              '  [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],' + char(13) +
              '  [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],' + char(13) +
              '  ''http://www.blackbaud.com/APPEALID?APPEALID='' + convert(nvarchar(36), [PACKAGETOTALS].[APPEALID]) as [APPEALLINK],' + char(13) +
              '  ''http://www.blackbaud.com/PACKAGEID?PACKAGEID='' + convert(nvarchar(36), [MKTPACKAGE].[ID]) as [PACKAGELINK]' + char(13) +
              'from [PACKAGETOTALS]' + char(13) +
              'left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [PACKAGETOTALS].[PACKAGEID]' + char(13) +
              'left join dbo.[CURRENCY] on [CURRENCY].[ID] = [PACKAGETOTALS].[CURRENCYID]' + char(13) +
              char(13) +
              'union all' + char(13) +
              char(13) +
              '--Appeals with revenue (no packages)' + char(13) +

              'select' + char(13) +
              '  [NONPACKAGETOTALS].[APPEALID],' + char(13) +
              '  [NONPACKAGETOTALS].[APPEALNAME],' + char(13) +
              '  null as [PACKAGEID],' + char(13) +
              '  ''(No package)'' as [PACKAGENAME],' + char(13) +
              '  cast(0 as int) as [QUANTITY],' + char(13) +
              '  [NONPACKAGETOTALS].[RESPONSES],' + char(13) +
              '  [NONPACKAGETOTALS].[TOTALREVENUE],' + char(13) +
              '  cast(0 as decimal(19,4)) as [RESPONSERATE],' + char(13) +
              '  cast((case when [NONPACKAGETOTALS].[RESPONSES] > 0 then [NONPACKAGETOTALS].[TOTALREVENUE] / [NONPACKAGETOTALS].[RESPONSES] else 0 end) as money) as [AVGGIFT],' + char(13) +
              '  cast(0 as money) as [REVENUEPERPIECE],' + char(13) +
              '  [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],' + char(13) +
              '  [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],' + char(13) +
              '  [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],' + char(13) +
              '  [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],' + char(13) +
              '  ''http://www.blackbaud.com/APPEALID?APPEALID='' + convert(nvarchar(36), [NONPACKAGETOTALS].[APPEALID]) as [APPEALLINK],' + char(13) +
              '  '''' as [PACKAGELINK]' + char(13) +
              'from [NONPACKAGETOTALS]' + char(13) +
              'left join dbo.[CURRENCY] on [CURRENCY].[ID] = [NONPACKAGETOTALS].[CURRENCYID]' + char(13) +
              'order by [APPEALNAME], [PACKAGENAME]';

  exec sp_executesql @SQL, N'@ORGANIZATIONCURRENCYID uniqueidentifier', @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID;


  drop table #APPEALS;
  drop table #SEGMENTS;

  return 0;