USP_DATALIST_MAILINGSSUMMARY

CREATE procedure dbo.[USP_DATALIST_MAILINGSSUMMARY]
(
  @ACTIVATEFROMDATE dbo.[UDT_FUZZYDATE] = null,        --Mailed date from (if date is not set on a mailing, it uses the activate date)

  @ACTIVATETODATE dbo.[UDT_FUZZYDATE] = null,           --Mailed date to (if date is not set on a mailing, it uses the activate date)

  @SOURCECODEID uniqueidentifier = null,
  @SITEID uniqueidentifier = null,
  @MAILINGTYPECODE  tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @CODE nvarchar(10);
  declare @NAME nvarchar(100);
  declare @SITE nvarchar(1024);
  declare @ACTIVATEDATE datetime;
  declare @MAILDATE datetime;
  declare @DAYSOUT int;
  declare @QUANTITYMAILED int;
  declare @TOTALGIFTAMOUNT money;
  declare @TOTALCOST money;
  declare @RESPONSES int;
  declare @AVERAGEGIFTAMOUNT money;
  declare @TOTALCOSTTABLE table([TOTALCOST] money, [TOTALORGANIZATIONCOST] money);
  declare @ISPUBLICMEDIA bit;

  declare @RESPONSECOUNTS table(
    [OFFERS] int, 
    [RESPONDERS] int, 
    [RESPONSES] int, 
    [TOTALGIFTAMOUNT] money, 
    [RESPONSERATE] decimal, 
    [TOTALORGANIZATIONGIFTAMOUNT] money,
    [FIRSTRESPONSEDATE] datetime
  );

  declare @RESULTS table(
    [MAILINGID] uniqueidentifier primary key,
    [CODE] nvarchar(10),
    [NAME] nvarchar(100),
    [SITE] nvarchar(1024),
    [ACTIVATEDATE] datetime,
    [DAYSOUT] int,
    [QUANTITYMAILED] int,
    [TOTALCOST] money,
    [RESPONSES] int,
    [TOTALGIFTAMOUNT] money,
    [AVERAGEGIFTAMOUNT] money,
    [MAILDATE] datetime);

  declare MAILINGCURSOR cursor local fast_forward for
    select
      [MKTSEGMENTATION].[ID],
      [MKTSEGMENTATION].[CODE],
      [MKTSEGMENTATION].[NAME],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([SITEID]) as [SITE],
      [ACTIVATEDATE],
      [MAILDATE],
      datediff(day, isnull([MAILDATE], [ACTIVATEDATE]), getdate())
    from dbo.[MKTSEGMENTATION]
    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 [ACTIVE] = 1
    and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0
    and (@ACTIVATEFROMDATE is null or isnull([MAILDATE], [ACTIVATEDATE]) >= @ACTIVATEFROMDATE)
    and (@ACTIVATETODATE is null or isnull([MAILDATE], [ACTIVATEDATE]) < dateadd(day, 1, @ACTIVATETODATE))
    and (@SOURCECODEID is null or [SOURCECODEID] = @SOURCECODEID)
    and (@SITEID is null or [SITEID] = @SITEID)
    and (@MAILINGTYPECODE is null or [MAILINGTYPECODE] = @MAILINGTYPECODE)
    and (@SOURCECODEID is null or [MKTSEGMENTATION].[SOURCECODEID] = @SOURCECODEID);

  open MAILINGCURSOR;
  fetch next from MAILINGCURSOR into @SEGMENTATIONID, @CODE, @NAME, @SITE, @ACTIVATEDATE, @MAILDATE, @DAYSOUT;

  while (@@FETCH_STATUS = 0)
  begin
    /**************************************/
    /* Get the total cost for the mailing */
    delete @TOTALCOSTTABLE;

    insert into @TOTALCOSTTABLE 
    (
      [TOTALCOST], 
      [TOTALORGANIZATIONCOST]
    )
    select
      isnull([TOTALCOST],0),
      isnull([ORGANIZATIONTOTALCOST],0)
    from dbo.[MKTSEGMENTATIONACTIVE]
    where [ID] = @SEGMENTATIONID;

    select 
      @TOTALCOST = case when @CURRENCYCODE = 1 then [TOTALORGANIZATIONCOST] else [TOTALCOST] end 
    from @TOTALCOSTTABLE;
    /**************************************/

    /**************************************/
    /* Get the number of donors and average gift amount */
    delete @RESPONSECOUNTS;

    insert into @RESPONSECOUNTS
      exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID;

    select
      @QUANTITYMAILED = [OFFERS],
      @RESPONSES = [RESPONSES],
      @TOTALGIFTAMOUNT = case when @CURRENCYCODE = 1 then [TOTALORGANIZATIONGIFTAMOUNT] else [TOTALGIFTAMOUNT] end 
    from @RESPONSECOUNTS;

    if @RESPONSES > 0
      set @AVERAGEGIFTAMOUNT = @TOTALGIFTAMOUNT / @RESPONSES;
    else
      set @AVERAGEGIFTAMOUNT = 0;
    /**************************************/

    insert into @RESULTS
      select @SEGMENTATIONID, @CODE, @NAME, @SITE, @ACTIVATEDATE, @DAYSOUT, @QUANTITYMAILED, @TOTALCOST, @RESPONSES, @TOTALGIFTAMOUNT, @AVERAGEGIFTAMOUNT, @MAILDATE;

    fetch next from MAILINGCURSOR into @SEGMENTATIONID, @CODE, @NAME, @SITE, @ACTIVATEDATE, @MAILDATE, @DAYSOUT;
  end;

  close MAILINGCURSOR;
  deallocate MAILINGCURSOR;

  select
    [RESULTS].[MAILINGID],
    [RESULTS].[CODE],
    [RESULTS].[NAME],
    [RESULTS].[SITE],
    [RESULTS].[ACTIVATEDATE],
    [RESULTS].[DAYSOUT],
    [RESULTS].[QUANTITYMAILED],
    [RESULTS].[TOTALCOST],
    [RESULTS].[RESPONSES],
    [RESULTS].[TOTALGIFTAMOUNT],
    [RESULTS].[AVERAGEGIFTAMOUNT],
    isnull([RESULTS].[MAILDATE], [RESULTS].[ACTIVATEDATE]) as [MAILDATE],
    case when [RESULTS].[MAILDATE] is null then 'true' else 'false' end as [MAILDATENOTSET],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then 1 else 0 end as [ISPUBLICMEDIA]
  from @RESULTS as [RESULTS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [RESULTS].[MAILINGID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  order by [NAME] desc;

  return 0;