USP_DATALIST_MKTLISTPERFORMANCE

CREATE procedure dbo.[USP_DATALIST_MKTLISTPERFORMANCE]
(
  @SELECTIONID uniqueidentifier = null,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1
)
with execute as owner
as
  set nocount on;

  declare @SELECTIONNAME nvarchar(300);
  declare @SQL nvarchar(max);
  declare @LISTCURSOR cursor;
  declare @LISTID uniqueidentifier;
  declare @SELECTIONISLIST bit = case when exists (select * from dbo.[MKTLIST] where [ID] = @SELECTIONID) then 1 else 0 end;

  /* Get multicurrency values for the organization */
  declare @ISO4217 nvarchar(100);
  declare @DECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);
  declare @SYMBOLDISPLAYSETTINGCODE tinyint;

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

  declare @ALL table (
    [SELECTIONNAME] nvarchar(300),
    [LISTID] uniqueidentifier,
    [LISTNAME] nvarchar(43),
    [SEGMENTID] uniqueidentifier,
    [SEGMENTNAME] nvarchar(100),
    [PACKAGEID] uniqueidentifier,
    [PACKAGENAME] nvarchar(100),
    [MAILINGID] uniqueidentifier,
    [MAILINGNAME] nvarchar(100),
    [QUANTITY] int,
    [TOTALCOST] money,
    [RESPONSES] int,
    [TOTALREVENUE] money,
    [COSTPERPIECE] money,
    [RESPONSERATE] decimal(19,4),  
    [AVGGIFT] money,
    [REVENUEPERPIECE] money
  );

  if @ENDDATE is null
    set @ENDDATE = getdate();
  if @STARTDATE is null
    set @STARTDATE = dateadd(yy, -1, @ENDDATE);

  --To put the selection name on the report

  select
    @SELECTIONNAME = [NAME]
  from dbo.[IDSETREGISTER]
  where [ID] = @SELECTIONID;


  --Loop through all lists (or lists in the selection), and gather response and cost information...

  if @SELECTIONISLIST = 1
    begin
      declare @SELECTIONLISTID uniqueidentifier = @SELECTIONID;

      set @SQL = 'set @LISTCURSOR = cursor local fast_forward for' + char(13) +
                 '  select [ID]' + char(13) +
                 '  from dbo.[MKTLIST] where [ID] = ''' + convert(nvarchar(36), @SELECTIONLISTID) + ''';' + char(13) +
                 'open @LISTCURSOR;';
    end 
  else
    set @SQL = 'set @LISTCURSOR = cursor local fast_forward for' + char(13) +
               '  select [ID]' + char(13) +
               '  from dbo.' + (case when @SELECTIONID is null then '[MKTLIST]' else dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) end) + ';' + char(13) +
               'open @LISTCURSOR;';


  exec sp_executesql @SQL, N'@LISTCURSOR cursor output', @LISTCURSOR = @LISTCURSOR output;
  fetch next from @LISTCURSOR into @LISTID;

  while (@@FETCH_STATUS = 0)
  begin
    insert into @ALL 
      select
        @SELECTIONNAME,
        [LISTID],
        [LISTNAME],
        [SEGMENTID],
        [SEGMENTNAME],
        [PACKAGEID],
        [PACKAGENAME],
        [MAILINGID],
        [MAILINGNAME],  
        [QUANTITY] as [QUANTITY],
        [TOTALCOST] as [TOTALCOST],
        [RESPONSES] as [RESPONSES],
        [TOTALGIFTAMOUNT] as [TOTALREVENUE],
        cast((case when [QUANTITY] > 0 then [TOTALCOST] / [QUANTITY] else 0 end) as money) as [COSTPERPIECE],
        cast((case when [QUANTITY] > 0 then cast([RESPONSES] as decimal(19,4)) / cast([QUANTITY] as decimal(19,4)) else 0 end) as decimal(19,4)) as [RESPONSERATE],
        cast((case when [RESPONSES] > 0 then [TOTALGIFTAMOUNT] / [RESPONSES] else 0 end) as money) as [AVGGIFT],
        cast((case when [QUANTITY] > 0 then [TOTALGIFTAMOUNT] / [QUANTITY] else 0 end) as money) as [REVENUEPERPIECE]
      from (
        select
  [MKTLIST].[ID] as [LISTID],
          [MKTLIST].[NAME] as [LISTNAME],
          [MKTSEGMENT].[ID] as [SEGMENTID],
          [MKTSEGMENT].[NAME] as [SEGMENTNAME],
          [MKTSEGMENTATION].[ID] as [MAILINGID],
          [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
          [MKTPACKAGE].[ID] as [PACKAGEID],
          [MKTPACKAGE].[NAME] as [PACKAGENAME],
          isnull(sum([MKTSEGMENTATIONLISTACTIVE].[QUANTITY]), 0) as [QUANTITY],
          isnull(sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALCOST]), 0) as [TOTALCOST],
          isnull(sum([MKTSEGMENTATIONLISTACTIVE].[RESPONSES]), 0) as [RESPONSES],
          isnull(sum([MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]), 0) as [TOTALGIFTAMOUNT]
        from dbo.[MKTLIST]
        inner join dbo.[MKTSEGMENTATIONLISTACTIVE] on [MKTSEGMENTATIONLISTACTIVE].[LISTID] = [MKTLIST].[ID]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONLISTACTIVE].[PACKAGEID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))
        and [MKTLIST].[ID] = @LISTID
        and isnull([MKTSEGMENTATION].[MAILDATE], cast([MKTSEGMENTATION].[ACTIVATEDATE] as date)) between @STARTDATE and @ENDDATE
        group by
          [MKTLIST].[ID],
          [MKTLIST].[NAME],
          [MKTSEGMENT].[ID],
          [MKTSEGMENT].[NAME],
          [MKTSEGMENTATION].[ID],
          [MKTSEGMENTATION].[NAME],
          [MKTPACKAGE].[ID],
          [MKTPACKAGE].[NAME]
      ) as [INNER];

    fetch next from @LISTCURSOR into @LISTID;
  end

  close @LISTCURSOR;
  deallocate @LISTCURSOR;


  --if no result rows return parameters only

  if (select count(*) from @ALL) = 0
    insert into @ALL ([SELECTIONNAME]) values (@SELECTIONNAME);

  --Display

  select 
    [SELECTIONNAME],
    [LISTID],
    [LISTNAME],
    [SEGMENTID],
    [SEGMENTNAME],
    [PACKAGEID],
    [PACKAGENAME],
    [MAILINGID],
    [MAILINGNAME],
    [QUANTITY],
    [TOTALCOST],
    [RESPONSES],
    [TOTALREVENUE],
    [COSTPERPIECE],
    [RESPONSERATE],  
    [AVGGIFT],
    [REVENUEPERPIECE],
    @ISO4217 as [CURRENCYISOCURRENCYCODE],
    @DECIMALDIGITS as [CURRENCYDECIMALDIGITS],
    @CURRENCYSYMBOL as [CURRENCYSYMBOL],
    @SYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]    
  from @ALL
  order by
    [LISTNAME],
    [SEGMENTNAME],
    [PACKAGENAME],
    [MAILINGNAME];

  return 0;