USP_REPORT_APPEALBUDGETPERFORMANCE

CREATE procedure dbo.[USP_REPORT_APPEALBUDGETPERFORMANCE]
(
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @CATEGORYID uniqueidentifier = null,
  @BUSINESSUNITID uniqueidentifier = null,
  @REPORTCODEID uniqueidentifier = null,
  @SITEID uniqueidentifier = null,
  @REPORTUSERID nvarchar(128) = null,
  @APPEALID uniqueidentifier = null,
  @CURRENCYCODE tinyint = null,
  @ALTREPORTUSERID nvarchar(128) = null
)
as
  set nocount on;
  set transaction isolation level read uncommitted;

  declare @CURRENTAPPUSERID uniqueidentifier = dbo.[UFN_APPUSER_GETREPORTAPPUSERID](@REPORTUSERID, @ALTREPORTUSERID);
  declare @ISSYSADMIN as bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);
  declare @SELECTEDCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;

  if @STARTDATE is not null
    set @STARTDATE = dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE);
  if @ENDDATE is not null
    set @ENDDATE = dbo.[UFN_DATE_GETLATESTTIME](@ENDDATE);

  if @CURRENCYCODE = 0
    set @SELECTEDCURRENCYID = null;
  else if @CURRENCYCODE = 2
    set @SELECTEDCURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
  else
    begin
      set @CURRENCYCODE = 1;
      set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
    end

  select
    @DECIMALDIGITS = [DECIMALDIGITS],
    @ROUNDINGTYPECODE = [ROUNDINGTYPECODE]
  from dbo.[CURRENCY]
  where [ID] = @SELECTEDCURRENCYID;



  declare @APPEALS table (
    [ID] uniqueidentifier not null primary key,
    [NAME] nvarchar(100) not null,
    [CURRENCYID] uniqueidentifier,
    [CURRENCYISO] nvarchar(3),
    [CURRENCYDECIMALDIGITS] tinyint,
    [CURRENCYSYMBOL] nvarchar(5),
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  --All appeals that meet the criteria

  insert into @APPEALS ([ID], [NAME], [CURRENCYID], [CURRENCYISO], [CURRENCYDECIMALDIGITS], [CURRENCYSYMBOL], [CURRENCYSYMBOLDISPLAYSETTINGCODE])
    select distinct
      [APPEAL].[ID],
      [APPEAL].[NAME],
      [CURRENCYPROPERTIES].[ID] as [CURRENCYID],
      [CURRENCYPROPERTIES].[ISO4217] as [CURRENCYISO],
      [CURRENCYPROPERTIES].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
      [CURRENCYPROPERTIES].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
      [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
    from dbo.[UFN_APPEAL_GETGOALINCURRENCY_BULK2](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, 1) as [APPEAL]
    /*#IDSETEXTENSION*/
    left join dbo.[APPEALBUSINESSUNIT] on [APPEALBUSINESSUNIT].[APPEALID] = [APPEAL].[ID]
    outer apply dbo.[UFN_CURRENCY_GETPROPERTIES](isnull(@SELECTEDCURRENCYID, [APPEAL].[BASECURRENCYID])) as [CURRENCYPROPERTIES]
    where (@REPORTCODEID is null or [APPEAL].[APPEALREPORT1CODEID] = @REPORTCODEID)
    and (@CATEGORYID is null or [APPEAL].[APPEALCATEGORYCODEID] = @CATEGORYID)
    and (@BUSINESSUNITID is null or [APPEALBUSINESSUNIT].[BUSINESSUNITCODEID] = @BUSINESSUNITID)
    and (@SITEID is null or [APPEAL].[SITEID] = @SITEID)
    and (@ISSYSADMIN = 1 or dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [APPEAL].[SITEID]) = 1)
    and (@APPEALID is null or [APPEAL].[ID] = @APPEALID);




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

  create table #APPEALREVENUESPLIT (
    [APPEALID] uniqueidentifier not null,
    [REVENUEID] uniqueidentifier not null,
    [ISPLEDGE] bit not null,
    [REVENUESPLITAMOUNTINCURRENCY] money,
    [WRITEOFFSPLITAMOUNTINCURRENCY] money
  );

  --Revenue that matches the appeal and meets the date criteria

  insert into #APPEALREVENUESPLIT ([APPEALID], [REVENUEID], [ISPLEDGE], [REVENUESPLITAMOUNTINCURRENCY], [WRITEOFFSPLITAMOUNTINCURRENCY])
    select
      [APPEALS].[ID] as [APPEALID],
      [REVENUESPLITAMOUNTS].[REVENUEID],
      (case when [TRANSACTIONTYPECODE] in (1,3,4,8) then 1 else 0 end) as [ISPLEDGE],
      [REVENUESPLITAMOUNTS].[REVENUESPLITAMOUNTINCURRENCY],
      [REVENUESPLITAMOUNTS].[WRITEOFFSPLITAMOUNTINCURRENCY]
    from @APPEALS as [APPEALS]
    inner join dbo.[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) as [REVENUESPLITAMOUNTS] on [REVENUESPLITAMOUNTS].[APPEALID] = [APPEALS].[ID]
    where (
      --Revenue (excluding pledge payments)

      ([REVENUESPLITAMOUNTS].[TRANSACTIONTYPECODE] = 0 and [REVENUESPLITAMOUNTS].[APPLICATIONCODE] not in (2,3))
      or
      --Pledges

      [REVENUESPLITAMOUNTS].[TRANSACTIONTYPECODE] in (1,3,4,8)
    )
    and (@STARTDATE is null or [REVENUESPLITAMOUNTS].[DATE] >= @STARTDATE)
    and (@ENDDATE is null or [REVENUESPLITAMOUNTS].[DATE] <= @ENDDATE);




  with [APPEALREVENUE_CTE] ([APPEALID], [REVENUEID], [RECEIVEDAMOUNT], [PLEDGEDAMOUNT], [PLEDGEWRITEOFFAMOUNT]) as
  (
    select
      [APPEALID],
      [REVENUEID],
      (case when [ISPLEDGE] = 1 then 0 else isnull(sum([REVENUESPLITAMOUNTINCURRENCY]), 0) end) as [RECEIVEDAMOUNT],
      (case when [ISPLEDGE] = 1 then isnull(sum([REVENUESPLITAMOUNTINCURRENCY]), 0) else 0 end) as [PLEDGEDAMOUNT],
      (case when [ISPLEDGE] = 1 then isnull(sum([WRITEOFFSPLITAMOUNTINCURRENCY]), 0) else 0 end) as [PLEDGEWRITEOFFAMOUNT]
    from #APPEALREVENUESPLIT as [ARS]
    group by [APPEALID], [REVENUEID], [ISPLEDGE]
  ),
  [APPEALMAILINGREVENUE_CTE] ([APPEALID], [MAILINGID], [RECEIVEDAMOUNT], [PLEDGEDAMOUNT], [PLEDGEWRITEOFFAMOUNT]) as
  (
    --Segment revenue: Matches to a segment within a mailing

    select
      [APPEALREVENUE_CTE].[APPEALID],
      [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      [APPEALREVENUE_CTE].[RECEIVEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]
    from [APPEALREVENUE_CTE]
    inner join dbo.[REVENUESEGMENT] on [REVENUESEGMENT].[REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [REVENUESEGMENT].[SEGMENTID]

    union all

    --Indirect revenue: Matches to the mailing only (no segment)

    select
      [APPEALREVENUE_CTE].[APPEALID],
      [REVENUE_EXT].[MAILINGID],
      [APPEALREVENUE_CTE].[RECEIVEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]
    from [APPEALREVENUE_CTE]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [APPEALREVENUE_CTE].[REVENUEID]
    where not exists(select * from dbo.[REVENUESEGMENT] where [REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID])
    and [REVENUE_EXT].[MAILINGID] is not null
  ),
  [MAILINGREVENUE_CTE] ([APPEALID], [MAILINGID], [TOTALRECEIVED], [TOTALPLEDGED], [TOTALPLEDGESWRITTENOFF]) as
  (
    --Revenue and cost totals for revenue that matches to a mailing

    select
      [APPEALS].[ID] as [APPEALID],
      [MKTSEGMENTATION].[ID] as [MAILINGID],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[RECEIVEDAMOUNT]), 0) as [TOTALRECEIVED],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[PLEDGEDAMOUNT]), 0) as [TOTALPLEDGED],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]), 0) as [TOTALPLEDGESWRITTENOFF]
    from @APPEALS as [APPEALS]
    inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast([APPEALS].[ID] as nvarchar(36)) and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] and [MKTSEGMENTATION].[ACTIVE] = 1  /*Activated mailings only*/
    left join [APPEALMAILINGREVENUE_CTE] on [APPEALMAILINGREVENUE_CTE].[APPEALID] = [APPEALS].[ID] and [APPEALMAILINGREVENUE_CTE].[MAILINGID] = [MKTSEGMENTATION].[ID]
    group by [APPEALS].[ID], [MKTSEGMENTATION].[ID]
  ),
  [NONMAILINGREVENUE_CTE] ([APPEALID], [TOTALRECEIVED], [TOTALPLEDGED], [TOTALPLEDGESWRITTENOFF]) as
  (
    --Revenue totals for revenue that does NOT match to any mailing

    select
      [APPEALREVENUE_CTE].[APPEALID],
      isnull(sum([APPEALREVENUE_CTE].[RECEIVEDAMOUNT]), 0) as [TOTALRECEIVED],
      isnull(sum([APPEALREVENUE_CTE].[PLEDGEDAMOUNT]), 0) as [TOTALPLEDGED],
      isnull(sum([APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]), 0) as [TOTALPLEDGESWRITTENOFF]
    from @APPEALS as [APPEALS]
    left join [APPEALREVENUE_CTE] on [APPEALREVENUE_CTE].[APPEALID] = [APPEALS].[ID]
    left join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [APPEALREVENUE_CTE].[REVENUEID]
    where [REVENUE_EXT].[MAILINGID] is null
    and not exists(select * from dbo.[REVENUESEGMENT] where [REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID])
    group by [APPEALREVENUE_CTE].[APPEALID]
  )
  --Revenue that matches to a mailing

  select
    [MAILINGREVENUE_CTE].[MAILINGID],
    [MKTSEGMENTATION].[NAME],
    convert(money, [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY]) as [TOTALCOST],
    convert(money, ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF])) as [TOTALINCOME],
    convert(money, ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF] - [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY])) as [TOTALPROFIT],
    (case when [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY] <> 0
       then convert(money, ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF] - [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY]) / [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY])
       else convert(money, 0)
     end) as [ROI],
    (case when ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF]) <> 0
       then convert(money, ([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY] / ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF])))
       else convert(money, (case when isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0) = 0 then 0 else 1 end))
     end) as [COSTPERDOLLARRAISED],
    [MAILINGREVENUE_CTE].[APPEALID],
    [APPEALS].[NAME] as [APPEALNAME],
    'http://www.blackbaud.com/APPEALID?APPEALID=' + convert(nvarchar(36), [MAILINGREVENUE_CTE].[APPEALID]) as [APPEALLINK],
    'http://www.blackbaud.com/' + (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then 'PUBLICMEDIAEFFORTID' else 'MAILINGID' end) + '?MAILINGID=' + convert(nvarchar(36), [MAILINGREVENUE_CTE].[MAILINGID]) as [MAILINGLINK],
    cast((case when [APPEALMAILING].[ID] is not null then 1 else 0 end) as bit) as [ISAPPEALMAILING],
    'http://www.blackbaud.com/APPEALMAILINGID?APPEALMAILINGID=' + convert(nvarchar(36), [APPEALMAILING].[ID]) as [APPEALMAILINGLINK],
    [APPEALS].[CURRENCYID],
    [APPEALS].[CURRENCYISO],
    [APPEALS].[CURRENCYDECIMALDIGITS],
    [APPEALS].[CURRENCYSYMBOL],
    [APPEALS].[CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from [MAILINGREVENUE_CTE]
  left join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MAILINGREVENUE_CTE].[MAILINGID]
  left join dbo.[UFN_MKTSEGMENTATION_GETAMOUNTSINCURRENCY_BULK](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [MAILINGAMOUNTS] on [MAILINGAMOUNTS].[ID] = [MKTSEGMENTATION].[ID]
  left join @APPEALS as [APPEALS] on [APPEALS].[ID] = [MAILINGREVENUE_CTE].[APPEALID]
  left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MAILINGREVENUE_CTE].[MAILINGID]

  union all

  --Revenue that does NOT match to any mailing

  select
    null as [MAILINGID],
    '(No mailing)' as [NAME],
    convert(money, 0) as [TOTALCOST],
    convert(money, ([NONMAILINGREVENUE_CTE].[TOTALRECEIVED] + [NONMAILINGREVENUE_CTE].[TOTALPLEDGED] - [NONMAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF])) as [TOTALINCOME],
    convert(money, ([NONMAILINGREVENUE_CTE].[TOTALRECEIVED] + [NONMAILINGREVENUE_CTE].[TOTALPLEDGED] - [NONMAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF])) as [TOTALPROFIT],
    0 as [ROI],
    0 as [COSTPERDOLLARRAISED],
    [NONMAILINGREVENUE_CTE].[APPEALID],
    [APPEALS].[NAME] as [APPEALNAME],
    'http://www.blackbaud.com/APPEALID?APPEALID=' + convert(nvarchar(36), [NONMAILINGREVENUE_CTE].[APPEALID]) as [APPEALLINK],
    null as [MAILINGLINK],
    cast(0 as bit) as [ISAPPEALMAILING],
    null as [APPEALMAILINGLINK],
    [APPEALS].[CURRENCYID],
    [APPEALS].[CURRENCYISO],
    [APPEALS].[CURRENCYDECIMALDIGITS],
    [APPEALS].[CURRENCYSYMBOL],
    [APPEALS].[CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from [NONMAILINGREVENUE_CTE]
  left join @APPEALS as [APPEALS] on [APPEALS].[ID] = [NONMAILINGREVENUE_CTE].[APPEALID]

  order by [APPEALNAME], [NAME];


  drop table #APPEALREVENUESPLIT;