USP_DATALIST_SEGMENTPACKAGEPERFORMANCE

CREATE procedure dbo.[USP_DATALIST_SEGMENTPACKAGEPERFORMANCE]
(
  @SELECTIONID uniqueidentifier = null,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @GROUPBY integer = 0,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  /* Set currency symbols using the organization currency */
  /* Note, for now this report will only display in the organization currency.  Since this report
     sums efforts that could have different base currencies, it was decided the amount of processing it would take to 
     convert different base currencies on the fly is too much and a different approach would be needed. */
  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]();

  declare @SEGMENTCURSOR cursor;
  declare @SQL nvarchar(max);

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

  if @SELECTIONID is null
  begin
    set @SQL = 'set @SEGMENTCURSOR = cursor local fast_forward for select [ID] from dbo.[MKTSEGMENT];';
    set @SQL = @SQL + char(13) + 'open @SEGMENTCURSOR;';  
  end
  else
  begin
    set @SQL = 'set @SEGMENTCURSOR = cursor local fast_forward for select [ID] from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ';';
    set @SQL = @SQL + char(13) + 'open @SEGMENTCURSOR;';  
  end;
  exec sp_executesql @SQL, N'@SEGMENTCURSOR cursor output', @SEGMENTCURSOR = @SEGMENTCURSOR output; 

  declare @ALL table (
    [SELECTIONNAME] nvarchar(300),
    [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
    );

  --To put the selection name on the report

  declare @SELECTIONNAME nvarchar(300);
  select @SELECTIONNAME = [NAME] from dbo.[IDSETREGISTER] where [ID] = @SELECTIONID;

  declare @SEGMENTID uniqueidentifier;
  fetch next from @SEGMENTCURSOR into @SEGMENTID;
  while (@@FETCH_STATUS = 0)
  begin

  insert into @ALL 
    select 
      @SELECTIONNAME,
      [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
        [MKTSEGMENT].[ID] as [SEGMENTID],
        [MKTSEGMENT].[NAME] as [SEGMENTNAME],
        [MKTSEGMENTATION].[ID] as [MAILINGID],
        [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
        isnull([MKTTESTPACKAGE].[ID],[MKTPACKAGE].[ID]) as [PACKAGEID],
        isnull([MKTTESTPACKAGE].[NAME],[MKTPACKAGE].[NAME]) as [PACKAGENAME],
        sum([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) as [QUANTITY],
        sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST]) as [TOTALCOST],
        sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES]) as [RESPONSES],
        sum([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]) as [TOTALGIFTAMOUNT]
      from dbo.[MKTSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
      left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID]
      left join dbo.[MKTPACKAGE] as [MKTTESTPACKAGE] on [MKTTESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
      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 [MKTSEGMENT].[ID] = @SEGMENTID
      and isnull([MKTSEGMENTATION].[MAILDATE], cast([MKTSEGMENTATION].[ACTIVATEDATE] as date)) between @STARTDATE and @ENDDATE
      group by 
        [MKTSEGMENT].[ID],
        [MKTSEGMENT].[NAME],
        [MKTSEGMENTATION].[ID],
        [MKTSEGMENTATION].[NAME],
        [MKTTESTPACKAGE].[ID],
        [MKTTESTPACKAGE].[NAME],
        [MKTPACKAGE].[ID],
        [MKTPACKAGE].[NAME]
      ) as [INNER];

  fetch next from @SEGMENTCURSOR into @SEGMENTID;
  end;

  close @SEGMENTCURSOR;
  deallocate @SEGMENTCURSOR;

  --if no result rows return parameters only

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

  --Display

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

  return 0;