USP_KPI_MARKETING_SEGMENT_COSTPERDOLLARRAISED_INTERNAL

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

  declare @MAILINGSEGMENTID uniqueidentifier;
  declare @MAILINGTESTSEGMENTID uniqueidentifier;
  declare @MAILINGDATEREFRESHED date;
  declare @TOTALCOST money;
  declare @TOTALGIFTAMOUNT money;
  declare @RESPONSECOUNTS table([OFFERS] int, [RESPONDERS] int, [RESPONSES] int, [TOTALGIFTAMOUNT] money, [AVERAGEGIFTAMOUNT] money, [ORGANIZATIONTOTALGIFTAMOUNT] money, [ORGANIZATIONAVERAGEGIFTAMOUNT] money);

  set @TOTALGIFTAMOUNT = 0;
  set @TOTALCOST = 0;

  declare SEGMENTCURSOR cursor local fast_forward for
    select
      [MKTSEGMENTATIONSEGMENT].[ID],
      null,
      cast(isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as date)
    from dbo.[MKTSEGMENT]
    /*#IDSETEXTENSION*/
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where (@SEGMENTID is null or @SEGMENTID = [MKTSEGMENT].[ID])
    union
    select
      [MKTSEGMENTATIONSEGMENT].[ID],
      [MKTSEGMENTATIONTESTSEGMENT].[ID],
      cast(isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as date)
    from dbo.[MKTSEGMENT]
    /*#IDSETEXTENSION*/
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where (@SEGMENTID is null or @SEGMENTID = [MKTSEGMENT].[ID]);

  open SEGMENTCURSOR;
  fetch next from SEGMENTCURSOR into @MAILINGSEGMENTID, @MAILINGTESTSEGMENTID, @MAILINGDATEREFRESHED;

  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 >= @MAILINGDATEREFRESHED
      select
          @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT + [ORGANIZATIONTOTALGIFTAMOUNT],
          @TOTALCOST = @TOTALCOST + [ORGANIZATIONTOTALCOST]
      from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
      where [SEGMENTID] = @MAILINGSEGMENTID
      and ((@MAILINGTESTSEGMENTID is null and [TESTSEGMENTID] is null) or (@MAILINGTESTSEGMENTID is not null and [TESTSEGMENTID] = @MAILINGTESTSEGMENTID));
    else
      begin
        delete from @RESPONSECOUNTS;

        insert into @RESPONSECOUNTS
          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_GETRESPONSECOUNTS] @MAILINGSEGMENTID, @MAILINGTESTSEGMENTID, @ASOFDATE;

        select
          @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT + [ORGANIZATIONTOTALGIFTAMOUNT]
        from @RESPONSECOUNTS;

        /* Since total cost for the segment 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), so just grab the total cost from the segment cache. */
        select
          @TOTALCOST = @TOTALCOST + [ORGANIZATIONTOTALCOST]
        from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
        where [SEGMENTID] = @MAILINGSEGMENTID
        and ((@MAILINGTESTSEGMENTID is null and [TESTSEGMENTID] is null) or (@MAILINGTESTSEGMENTID is not null and [TESTSEGMENTID] = @MAILINGTESTSEGMENTID));
      end

    fetch next from SEGMENTCURSOR into @MAILINGSEGMENTID, @MAILINGTESTSEGMENTID, @MAILINGDATEREFRESHED;
  end

  close SEGMENTCURSOR;
  deallocate SEGMENTCURSOR;

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