USP_KPI_MARKETING_MEMBERSHIPMAILING_COSTPERRENEWAL_INTERNAL

CREATE procedure dbo.[USP_KPI_MARKETING_MEMBERSHIPMAILING_COSTPERRENEWAL_INTERNAL]
(
  @ASOFDATE datetime,
  @SEGMENTATIONID uniqueidentifier = null,
  @VALUE money = 0 output,
  @CURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGID uniqueidentifier;
  declare @DATEREFRESHED date;
  declare @TOTALCOST money;
  declare @RENEWALS int;
  declare @RESPONSECOUNTS table([RENEWALS] int, [UPGRADES] int, [DOWNGRADES] int, [TOTALRENEWALAMOUNT] money, [ORGANIZATIONTOTALRENEWALAMOUNT] money);
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  set @TOTALCOST = 0;
  set @RENEWALS = 0;
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare MAILINGSCURSOR cursor local fast_forward for
    select
      [MKTSEGMENTATION].[ID],
      cast(isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as date)
    from dbo.[MKTSEGMENTATION]
    /*#IDSETEXTENSION*/
    inner join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[MAILINGTYPECODE] = 2
    and (@SEGMENTATIONID is null or @SEGMENTATIONID = [MKTSEGMENTATION].[ID]);

  open MAILINGSCURSOR;
  fetch next from MAILINGSCURSOR into @MAILINGID, @DATEREFRESHED;

  while (@@FETCH_STATUS = 0)
    begin
      -- if the as of date is the same day or after the date the mailing was last refreshed, then grab

      -- the value from the mailing cache, otherwise perform the calculation using the as of date

      if @ASOFDATE >= @DATEREFRESHED
        select
          @TOTALCOST = @TOTALCOST +
            case when @CURRENCYID = @ORGANIZATIONCURRENCYID
                 then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST]
                 else [MKTSEGMENTATIONACTIVE].[TOTALCOST]
            end,
          @RENEWALS = @RENEWALS + [MKTMEMBERSHIPMAILINGACTIVE].[RENEWALS]
        from dbo.[MKTSEGMENTATIONACTIVE]
        inner join dbo.[MKTMEMBERSHIPMAILINGACTIVE] on [MKTMEMBERSHIPMAILINGACTIVE].[ID] = [MKTSEGMENTATIONACTIVE].[ID]
        where [MKTSEGMENTATIONACTIVE].[ID] = @MAILINGID;
      else
        begin
          delete from @RESPONSECOUNTS;

          insert into @RESPONSECOUNTS
            exec dbo.[USP_MKTMEMBERSHIPMAILING_GETRESPONSECOUNTS] @MAILINGID, @ASOFDATE;

          select
            @RENEWALS = @RENEWALS + [RENEWALS]
          from @RESPONSECOUNTS;

          -- since total cost for the mailing will never change (unless they change the fixed cost on the mailing, but even then

          -- the cache will be updated when they save the new fixed cost), we can just grab the total cost from the mailing cache

          select
            @TOTALCOST = @TOTALCOST +
            case when @CURRENCYID = @ORGANIZATIONCURRENCYID
                 then [ORGANIZATIONTOTALCOST]
                 else [TOTALCOST]
            end
          from dbo.[MKTSEGMENTATIONACTIVE]
          where [ID] = @MAILINGID;
      end

      fetch next from MAILINGSCURSOR into @MAILINGID, @DATEREFRESHED;
    end

  close MAILINGSCURSOR;
  deallocate MAILINGSCURSOR;

  set @VALUE = cast((case when @RENEWALS > 0 then @TOTALCOST / @RENEWALS else 0 end) as money);