USP_REPORT_APPEALMAILINGPERFORMANCE

CREATE procedure dbo.[USP_REPORT_APPEALMAILINGPERFORMANCE]
(
  @MAILINGID uniqueidentifier = null,
  @IDSETREGISTERID uniqueidentifier = null,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @CURRENCYCODE tinyint = 1, /* 0 = base, 1 = organization */
  @ALTREPORTUSERID nvarchar(128) = null,
  @REPORTUSERID nvarchar(128) = null
)
with execute as owner
as
  set nocount on;

  declare @SELECTEDCURRENCYID uniqueidentifier;
  declare @SQL nvarchar(max);

  begin try
    if @CURRENCYCODE is null
      set @CURRENCYCODE = 1;
    if @CURRENCYCODE = 1
      set @SELECTEDCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();


    --Since the number of mailings in an IDSet should be fairly small, it should be ok to just use a

    --temp table here instead of writing this entire procedure in dynamic sql.

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

    create table #MAILINGIDSET (
      [ID] uniqueidentifier not null primary key
    );

    if @IDSETREGISTERID is not null
      begin
        if not exists(select * from dbo.[IDSETREGISTER] where [ID] = @IDSETREGISTERID)
          raiserror('Selection does not exist in the database.', 13, 1);

        set @SQL = 'insert into #MAILINGIDSET ([ID])' + char(13) +
                   '  select distinct [ID] from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@IDSETREGISTERID);
        exec (@SQL);
      end;

    declare @CURRENTAPPUSERID uniqueidentifier = dbo.[UFN_APPUSER_GETREPORTAPPUSERID](@ALTREPORTUSERID,@REPORTUSERID);

    with [MAILINGS_CTE] ([MAILINGID], [MAILINGNAME], [MAILINGLINK], [BASECURRENCYID], [MAILINGBUDGETAMOUNT], [MAILINGTOTALGIFTAMOUNT], [MAILINGAVERAGEGIFTAMOUNT], [MAILINGRESPONSES], [MAILINGRESPONDERS], [MAILINGRESPONSERATE], [MAILINGTOTALCOST], [MAILINGCOSTPERDOLLARRAISED], [MAILINGQUANTITY], [MAILINGROIPERCENT], [REGULARGIFTPAYMENTS], [REGULARGIFTREVENUE], [REGULARGIFTAVERAGE], [CASHGIFTPAYMENTS], [CASHGIFTREVENUE], [CASHGIFTAVERAGE], [GROSSAMOUNT]) as
    (
      --All mailings that meet the criteria

      select
        [MKTSEGMENTATION].[ID],
        [MKTSEGMENTATION].[NAME],
        'http://www.blackbaud.com/' + (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then 'PUBLICMEDIAEFFORTID' when [APPEALMAILING].[ID] is not null then 'APPEALMAILINGID' else 'MAILINGID' end) + '?MAILINGID=' + convert(nvarchar(36), [MKTSEGMENTATION].[ID]) as [MAILINGLINK],
        [MKTSEGMENTATION].[BASECURRENCYID],
        isnull((case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONBUDGET].[ORGANIZATIONBUDGETAMOUNT] else [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT] end), 0) as [MAILINGBUDGETAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT] else [MKTSEGMENTATIONACTIVE].[TOTALGIFTAMOUNT] end) as [MAILINGTOTALGIFTAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONAVERAGEGIFTAMOUNT] else [MKTSEGMENTATIONACTIVE].[AVERAGEGIFTAMOUNT] end) as [MAILINGAVERAGEGIFTAMOUNT],
        [MKTSEGMENTATIONACTIVE].[RESPONSES] as [MAILINGRESPONSES],
        [MKTSEGMENTATIONACTIVE].[RESPONDERS] as [MAILINGRESPONDERS],
        [MKTSEGMENTATIONACTIVE].[RESPONSERATE] as [MAILINGRESPONSERATE],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST] else [MKTSEGMENTATIONACTIVE].[TOTALCOST] end) as [MAILINGTOTALCOST],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONCOSTPERDOLLARRAISED] else [MKTSEGMENTATIONACTIVE].[COSTPERDOLLARRAISED] end) as [MAILINGCOSTPERDOLLARRAISED],
        [MKTSEGMENTATIONACTIVE].[QUANTITY] as [MAILINGQUANTITY],
        [MKTSEGMENTATIONACTIVE].[ROIPERCENT] as [MAILINGROIPERCENT],
        [UKGIFTINFO].[REGULARGIFTPAYMENTS],
        (case when @CURRENCYCODE = 1 then [UKGIFTINFO].[ORGANIZATIONREGULARGIFTREVENUE] else [UKGIFTINFO].[REGULARGIFTREVENUE] end) as [REGULARGIFTREVENUE],
        (case when @CURRENCYCODE = 1 then [UKGIFTINFO].[ORGANIZATIONREGULARGIFTAVERAGE] else [UKGIFTINFO].[REGULARGIFTAVERAGE] end) as [REGULARGIFTAVERAGE],
        [UKGIFTINFO].[CASHGIFTPAYMENTS],
        (case when @CURRENCYCODE = 1 then [UKGIFTINFO].[ORGANIZATIONCASHGIFTREVENUE] else [UKGIFTINFO].[CASHGIFTREVENUE] end) as [CASHGIFTREVENUE],
        (case when @CURRENCYCODE = 1 then [UKGIFTINFO].[ORGANIZATIONCASHGIFTAVERAGE] else [UKGIFTINFO].[CASHGIFTAVERAGE] end) as [CASHGIFTAVERAGE],
        (case when @CURRENCYCODE = 1 then [UKGIFTINFO].[ORGANIZATIONGROSSAMOUNT] else [UKGIFTINFO].[GROSSAMOUNT] end) as [GROSSAMOUNT]
      from dbo.[MKTSEGMENTATION]
      /* Filter out mailing types that don't track revenue to appeals (ie: reminders, events) */
      inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0' and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> ''
      inner join dbo.[MKTSEGMENTATIONACTIVE] on  [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
      outer apply dbo.[UFN_MKTSEGMENTATION_GETUKGIFTINFO]([MKTSEGMENTATION].[ID]) as [UKGIFTINFO]
      left join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
      left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
      where [MKTSEGMENTATION].[ACTIVE] = 1  /* Activated mailings only */
      and dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID,[MKTSEGMENTATION].[SITEID],'38616d87-3b4f-4e6e-98b1-fe28c5455be1',21) = 1
      and (@MAILINGID is null or [MKTSEGMENTATION].[ID] = @MAILINGID)
      and (@IDSETREGISTERID is null or exists(select * from #MAILINGIDSET where [ID] = [MKTSEGMENTATION].[ID]))
      and (@STARTDATE is null or isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) >= @STARTDATE)
      and (@ENDDATE is null or isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) <= @ENDDATE)
    ),
    [SEGMENTTOTALS_CTE] ([SEGMENTID], [TESTSEGMENTID], [TOTALGIFTAMOUNT], [EXPECTEDTOTALGIFTAMOUNT], [AVERAGEGIFTAMOUNT], [VARIABLECOST], [FIXEDCOST], [TOTALCOST], [ROIPERCENT], [EXPECTEDROIPERCENT], [ROIAMOUNT], [COSTPERDOLLARRAISED], [EXPECTEDCOSTPERDOLLARRAISED], [RESPONDERS], [RESPONSES], [RESPONSERATE], [QUANTITY]) as
    (
      --Grab all the segment calcs in one place to simplify the next CTE

      select
        [SEGMENTID],
        [TESTSEGMENTID],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT] else [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] end) as [TOTALGIFTAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT] else [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT] end) as [EXPECTEDTOTALGIFTAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONAVERAGEGIFTAMOUNT] else [MKTSEGMENTATIONSEGMENTACTIVE].[AVERAGEGIFTAMOUNT] end) as [AVERAGEGIFTAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONVARIABLECOST] else [MKTSEGMENTATIONSEGMENTACTIVE].[VARIABLECOST] end) as [VARIABLECOST],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONFIXEDCOST] else [MKTSEGMENTATIONSEGMENTACTIVE].[FIXEDCOST] end) as [FIXEDCOST],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST] else [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] end) as [TOTALCOST],
        [MKTSEGMENTATIONSEGMENTACTIVE].[ROIPERCENT],
        [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIPERCENT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONROIAMOUNT] else [MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT] end) as [ROIAMOUNT],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONCOSTPERDOLLARRAISED] else [MKTSEGMENTATIONSEGMENTACTIVE].[COSTPERDOLLARRAISED] end) as [COSTPERDOLLARRAISED],
        (case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED] else [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDCOSTPERDOLLARRAISED] end) as [EXPECTEDCOSTPERDOLLARRAISED],
        [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONDERS],
        [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES],
        [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSERATE],
        [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]
      from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
    ),
    [SEGMENTS_CTE] ([MAILINGID], [SEGMENTID], [SEGMENTNAME], [TESTSEGMENTNAME], [SEGMENTSEQUENCE], [TESTSEGMENTSEQUENCE], [TOTALGIFTAMOUNT], [EXPECTEDTOTALGIFTAMOUNT], [AVERAGEGIFTAMOUNT], [VARIABLECOST], [FIXEDCOST], [TOTALCOST], [ROIPERCENT], [EXPECTEDROIPERCENT], [ROIAMOUNT], [COSTPERDOLLARRAISED], [EXPECTEDCOSTPERDOLLARRAISED], [RESPONDERS], [RESPONSES], [RESPONSERATE], [QUANTITY]) as
    (
      --Segments

      select
        [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        [MKTSEGMENT].[ID] as [SEGMENTID],
        [MKTSEGMENT].[NAME] as [SEGMENTNAME],
        null as [TESTSEGMENTNAME],
        [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE],
        0 as [TESTSEGMENTSEQUENCE],
        [SEGMENTTOTALS_CTE].[TOTALGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[EXPECTEDTOTALGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[AVERAGEGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[VARIABLECOST],
        [SEGMENTTOTALS_CTE].[FIXEDCOST],
        [SEGMENTTOTALS_CTE].[TOTALCOST],
        [SEGMENTTOTALS_CTE].[ROIPERCENT],
        [SEGMENTTOTALS_CTE].[EXPECTEDROIPERCENT],
        [SEGMENTTOTALS_CTE].[ROIAMOUNT],
        [SEGMENTTOTALS_CTE].[COSTPERDOLLARRAISED],
        [SEGMENTTOTALS_CTE].[EXPECTEDCOSTPERDOLLARRAISED],
        [SEGMENTTOTALS_CTE].[RESPONDERS],
        [SEGMENTTOTALS_CTE].[RESPONSES],
        [SEGMENTTOTALS_CTE].[RESPONSERATE],
        [SEGMENTTOTALS_CTE].[QUANTITY]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join [SEGMENTTOTALS_CTE] on [SEGMENTTOTALS_CTE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [SEGMENTTOTALS_CTE].[TESTSEGMENTID] is null

      union all

      --Test segments

      select
        [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        [MKTSEGMENT].[ID] as [SEGMENTID],
        [MKTSEGMENT].[NAME] as [SEGMENTNAME],
        dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) as [TESTSEGMENTNAME],
        [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE],
        [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] as [TESTSEGMENTSEQUENCE],
        [SEGMENTTOTALS_CTE].[TOTALGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[EXPECTEDTOTALGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[AVERAGEGIFTAMOUNT],
        [SEGMENTTOTALS_CTE].[VARIABLECOST],
        [SEGMENTTOTALS_CTE].[FIXEDCOST],
        [SEGMENTTOTALS_CTE].[TOTALCOST],
        [SEGMENTTOTALS_CTE].[ROIPERCENT],
        [SEGMENTTOTALS_CTE].[EXPECTEDROIPERCENT],
        [SEGMENTTOTALS_CTE].[ROIAMOUNT],
        [SEGMENTTOTALS_CTE].[COSTPERDOLLARRAISED],
        [SEGMENTTOTALS_CTE].[EXPECTEDCOSTPERDOLLARRAISED],
        [SEGMENTTOTALS_CTE].[RESPONDERS],
        [SEGMENTTOTALS_CTE].[RESPONSES],
        [SEGMENTTOTALS_CTE].[RESPONSERATE],
        [SEGMENTTOTALS_CTE].[QUANTITY]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join [SEGMENTTOTALS_CTE] on [SEGMENTTOTALS_CTE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [SEGMENTTOTALS_CTE].[TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
    )
    select
      [MAILINGS_CTE].[MAILINGLINK] as [MAILINGID],
      [MAILINGS_CTE].[MAILINGNAME],
      [MAILINGS_CTE].[MAILINGBUDGETAMOUNT],
      [MAILINGS_CTE].[MAILINGTOTALGIFTAMOUNT],
      [MAILINGS_CTE].[MAILINGAVERAGEGIFTAMOUNT],
      [MAILINGS_CTE].[MAILINGRESPONSES],
      [MAILINGS_CTE].[MAILINGRESPONDERS],
      [MAILINGS_CTE].[MAILINGRESPONSERATE],
      [MAILINGS_CTE].[MAILINGTOTALCOST],
      [MAILINGS_CTE].[MAILINGCOSTPERDOLLARRAISED],
      [MAILINGS_CTE].[MAILINGQUANTITY],
      [MAILINGS_CTE].[MAILINGROIPERCENT],

      'http://www.blackbaud.com/SEGMENTID?SEGMENTID=' + convert(nvarchar(36), [SEGMENTS_CTE].[SEGMENTID]) as [SEGMENTID],
      [SEGMENTS_CTE].[SEGMENTNAME],
      [SEGMENTS_CTE].[TOTALGIFTAMOUNT],
      [SEGMENTS_CTE].[EXPECTEDTOTALGIFTAMOUNT],
      [SEGMENTS_CTE].[AVERAGEGIFTAMOUNT],
      [SEGMENTS_CTE].[VARIABLECOST],
      [SEGMENTS_CTE].[FIXEDCOST],
      [SEGMENTS_CTE].[TOTALCOST],
      [SEGMENTS_CTE].[ROIPERCENT],
      [SEGMENTS_CTE].[EXPECTEDROIPERCENT],
      [SEGMENTS_CTE].[ROIAMOUNT],
      [SEGMENTS_CTE].[COSTPERDOLLARRAISED],
      [SEGMENTS_CTE].[EXPECTEDCOSTPERDOLLARRAISED],
      [SEGMENTS_CTE].[RESPONDERS],
      [SEGMENTS_CTE].[RESPONSES],
      [SEGMENTS_CTE].[RESPONSERATE],
      [SEGMENTS_CTE].[QUANTITY],

      [MAILINGS_CTE].[REGULARGIFTPAYMENTS],
      [MAILINGS_CTE].[REGULARGIFTREVENUE],
      [MAILINGS_CTE].[REGULARGIFTAVERAGE],
      [MAILINGS_CTE].[CASHGIFTPAYMENTS],
      [MAILINGS_CTE].[CASHGIFTREVENUE],
      [MAILINGS_CTE].[CASHGIFTAVERAGE],
      [MAILINGS_CTE].[GROSSAMOUNT],

      [CURRENCYPROPERTIES].[ISO4217] as [CURRENCYISOCURRENCYCODE],
      [CURRENCYPROPERTIES].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
      [CURRENCYPROPERTIES].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
      [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],

      [SEGMENTS_CTE].[SEGMENTSEQUENCE],
      [SEGMENTS_CTE].[TESTSEGMENTSEQUENCE],
      [SEGMENTS_CTE].[TESTSEGMENTNAME]
    from [MAILINGS_CTE]
    inner join [SEGMENTS_CTE] on [SEGMENTS_CTE].[MAILINGID] = [MAILINGS_CTE].[MAILINGID]
    outer apply dbo.[UFN_CURRENCY_GETPROPERTIES](isnull(@SELECTEDCURRENCYID, [MAILINGS_CTE].[BASECURRENCYID])) as [CURRENCYPROPERTIES]
    order by [MAILINGS_CTE].[MAILINGNAME], [SEGMENTS_CTE].[SEGMENTSEQUENCE], [SEGMENTS_CTE].[TESTSEGMENTSEQUENCE];
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];

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

    return 1;
  end catch

  return 0;