USP_DATALIST_RE7APPEALPACKAGEPERFORMANCEREPORT

create procedure dbo.USP_DATALIST_RE7APPEALPACKAGEPERFORMANCEREPORT
(
    @APPEALSELECTIONID uniqueidentifier = null,
    @REVENUESELECTIONID uniqueidentifier = null
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @APPEALSELECTIONNAME nvarchar(300) = '';
  declare @REVENUESELECTIONNAME nvarchar(300) = '';

  set @SQL =  'with [CONSTITAPPEAL]([ID],[CONSTITUENTID],[APPEALID],[PACKAGEID]) as' + char(13) +
              '(' + char(13) +
              '  select ca.[LOCALID], ca.[CONSTITUENT_LOCALID], ca.[APPEAL_LOCALID], ca.[PACKAGE_LOCALID]' + char(13) +
              '  from dbo.[V_QUERY_RE7_CONSTITUENTAPPEAL] ca' + char(13);

  if @APPEALSELECTIONID is not null
    set @SQL += '  inner join dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@APPEALSELECTIONID) + ' [APPEALS] on [APPEALS].[ID] = ca.[APPEAL_LOCALID]' + char(13);

  set @SQL += '), [REV]([ID],[CONSTITUENTID],[APPEALID],[AMOUNT]) as' + char(13) +
              '(' + char(13) +
              '  select g.[GIFTID], g.[CONSTITUENT_SYSTEMID], g.[APPEALSYSTEMID], g.[SPLITAMOUNT]' + char(13) +
              '  from dbo.[V_QUERY_RE7_GIFT] g' + char(13);            

  if @APPEALSELECTIONID is not null
  begin
    set @SQL += '  inner join dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@APPEALSELECTIONID) + ' [APPEALS] on [APPEALS].[ID] = g.[APPEALSYSTEMID]' + char(13);
    select @APPEALSELECTIONNAME = [NAME] from dbo.[IDSETREGISTER] where [ID] = @APPEALSELECTIONID;
  end

  if @REVENUESELECTIONID is not null
  begin
    set @SQL += '  inner join dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@REVENUESELECTIONID) + ' [REVENUES] on [REVENUES].[ID] = g.[GIFTSPLITID]' + char(13);
    select @REVENUESELECTIONNAME = [NAME] from dbo.[IDSETREGISTER] where [ID] = @REVENUESELECTIONID;
  end

  set @SQL += '), [BASE]([APPEALID],[PACKAGEID],[QUANTITY],[RESPONSES],[TOTALGIVING]) as' + char(13) +
              '(' + char(13) +
              '  select' + char(13) +
              '    isnull(ca.[APPEALID],  r.[APPEALID]),' + char(13) +
              '    ca.[PACKAGEID],' + char(13) +
              '    count(ca.[ID]) [QUANTITY],' + char(13) +
              '    count(r.[ID]) [RESPONSES], '+ char(13) +
              '    sum(isnull(r.[AMOUNT],0)) [TOTALGIVING]' + char(13) +
              '  from [CONSTITAPPEAL] ca ' + char(13) +
              '  full outer join [REV] r on ca.[CONSTITUENTID] = r.[CONSTITUENTID] and ca.[APPEALID] = r.[APPEALID]' + char(13) +
              '  group by ca.[APPEALID], ca.[PACKAGEID], r.[APPEALID]' + char(13) +
              '), AGGR([APPEALID],[PACKAGEID],[QUANTITY],[RESPONSES],[TOTALGIVING]) as' + char(13) +
              '(' + char(13) +
              '  select ' + char(13) +
              '    [BASE].[APPEALID],' + char(13) +
              '    [BASE].[PACKAGEID],' + char(13) +
              '    sum([BASE].[QUANTITY]) [QUANTITY],' + char(13) +
              '    sum([BASE].[RESPONSES]) [RESPONSES],' + char(13) +
              '    sum([BASE].[TOTALGIVING]) [TOTALGIVING]' + char(13) +
              '  from [BASE]' + char(13) +
              '  group by [BASE].[APPEALID], [BASE].[PACKAGEID]' + char(13) +
              ')' + char(13) +
              'select' + char(13) +
              '  [AGGR].[APPEALID],' + char(13) +
              '  [APPEAL].[DESCRIPTION] [APPEALNAME],' + char(13) +
              '  [AGGR].[PACKAGEID],' + char(13) +
              '  isnull([PACKAGE].[DESCRIPTION], ''None'') [PACKAGENAME],' + char(13) +
              '  [AGGR].[QUANTITY],' + char(13) +
              '  [AGGR].[RESPONSES],' + char(13) +
              '  [AGGR].[TOTALGIVING],' + char(13) +
              '  cast((case when [AGGR].[QUANTITY] > 0 then cast([AGGR].[RESPONSES] as decimal(19,4)) / cast([AGGR].[QUANTITY] as decimal(19,4)) else 0 end) as decimal(19,4)) as [RESPONSERATE],' + char(13) +
              '  cast((case when [AGGR].[RESPONSES] > 0 then [AGGR].[TOTALGIVING] / [AGGR].[RESPONSES] else 0 end) as money) as [AVGGIFT],' + char(13) +
              '  cast((case when [AGGR].[QUANTITY] > 0 then [AGGR].[TOTALGIVING] / [AGGR].[QUANTITY] else 0 end) as money) as [REVENUEPERPIECE],' + char(13) +
              '  @APPEALSELECTIONNAME [APPEALSELECTIONNAME],' + char(13) +
              '  @REVENUESELECTIONNAME [REVENUESELECTIONNAME]' + char(13) +
              'from [AGGR]' + char(13) +
              'inner join dbo.[V_QUERY_RE7_APPEAL] [APPEAL] on [AGGR].[APPEALID] = [APPEAL].[LOCALID]' + char(13) +
              'left join dbo.[V_QUERY_RE7_PACKAGE] [PACKAGE] on [AGGR].[PACKAGEID] = [PACKAGE].[LOCALID];';            
  exec sp_executesql @SQL,N'@APPEALSELECTIONNAME nvarchar(300), @REVENUESELECTIONNAME nvarchar(300)', @REVENUESELECTIONNAME = @REVENUESELECTIONNAME, @APPEALSELECTIONNAME = @APPEALSELECTIONNAME;

  return 0;