USP_DATALIST_MKTREVENUEREPORT

CREATE procedure dbo.[USP_DATALIST_MKTREVENUEREPORT]
(
  @SEARCHRESULT nvarchar(64),
  @DATEWINDOW tinyint,
  @STARTDATE dbo.[UDT_FUZZYDATE] = null,
  @ENDDATE dbo.[UDT_FUZZYDATE] = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  if @DATEWINDOW not between 1 and 4
  begin
    raiserror('Unknown Date Window',13,1);
      return 1;
  end

  -- Get currency fields.

  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  select 
    @CURRENCYISOCURRENCYCODE = [ISO4217],
    @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
  from dbo.[CURRENCY]
  where [ID] = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILDATE datetime;
  declare @COUNTDATE datetime;
  declare @MINIMUMDATE datetime = '1/1/1753';
  declare @ALL table (
    [ID] int identity,
    [GIFTS] int,
    [REVENUE] money,
    [TOTAL_GIFTS] int,
    [TOTAL_REVENUE] money,
    [TOTAL_OFFERS] int,
    [TOTAL_RESPONDERS] int,
    [GIFTDATE] datetime
    );

  --Get list of mailings to process

  declare @IDTYPE nvarchar(16);
  declare @ID nvarchar(36);
  set @IDTYPE = upper(substring(@SEARCHRESULT,1,charindex('|',@SEARCHRESULT)-1));
  set @ID = substring(@SEARCHRESULT,charindex('|',@SEARCHRESULT)+1,len(@SEARCHRESULT));

  declare @MAILINGCURSOR cursor;

  if @IDTYPE = 'PLANITEM'
  begin
    set @MAILINGCURSOR = cursor local fast_forward for
      with [MAILINGS]([ID], [LEVEL]) as
      (
        select [ID], 0 as [LEVEL]
        from dbo.[MKTMARKETINGPLANITEM]
        where [ID] = @ID
        union all
        select [MKTMARKETINGPLANITEM].[ID], [MAILINGS].[LEVEL] + 1
        from dbo.[MKTMARKETINGPLANITEM]
        inner join [MAILINGS]
        on [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] = [MAILINGS].[ID]
      )
      select [MKTSEGMENTATION].[ID], [MKTSEGMENTATION].[MAILDATE]
      from dbo.[MKTSEGMENTATION]
        inner join  [MAILINGS]
        on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MAILINGS].[ID]
       where [MKTSEGMENTATION].[ACTIVE] = 1;        
  end
  else
  begin
    if @IDTYPE = 'MARKETING EFFORT'
    begin
      set @MAILINGCURSOR = cursor local fast_forward for
        select [ID], isnull([MAILDATE],[ACTIVATEDATE])
        from dbo.[MKTSEGMENTATION]
        where [ID] = @ID
        and [MKTSEGMENTATION].[ACTIVE] = 1;
    end
    else
    begin
      if @IDTYPE = 'APPEAL'
      begin
        set @MAILINGCURSOR = cursor local fast_forward for    
          select [MKTSEGMENTATION].[ID], isnull([MKTSEGMENTATION].[MAILDATE],[MKTSEGMENTATION].[ACTIVATEDATE])
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]
          where [MKTSEGMENTATIONACTIVATE].[APPEALID] = @ID
          and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0  --Exclude appeal mailings

          and [MKTSEGMENTATION].[ACTIVE] = 1;
      end
      else
      begin
        raiserror('Unknown ID type',13,1);
          return 1;
      end
    end
  end

  --Process Mailings

  open @MAILINGCURSOR;
  fetch next from @MAILINGCURSOR into @SEGMENTATIONID, @MAILDATE;
  while (@@FETCH_STATUS = 0)
  begin
    if @STARTDATE is not null
      set @MAILDATE = @STARTDATE;

    --Don't calculate previous gifts if the count date will be less than the minimum sql date

    if @MAILDATE < dateadd(day,1,@MINIMUMDATE)
      set @COUNTDATE = @MAILDATE;
    else
      begin
        --Set @COUNTDATE back one datewindow to get previous totals

        --@DATEWINDOW: 1 = Daily, 2 = Weekly, 3 = Monthly, 4 = Yearly

        select @COUNTDATE =
          case @DATEWINDOW
            when 1 then dateadd(day,-1,@MAILDATE)
            when 2 then dateadd(day,-1,@MAILDATE)
            when 3 then dateadd(day,-1,@MAILDATE)
            when 4 then dateadd(day,-1,@MAILDATE)
            else @MAILDATE
          end;
      end

    --Populate @RESULT table

    declare @RESULT table (
      [ID] int identity,
      [TOTAL_OFFERS] int,
      [TOTAL_RESPONDERS] int,
      [TOTAL_GIFTS] int,
      [TOTAL_REVENUE] money, 
      [RESPONSERATE] decimal(20,5),
      [TOTALORGANIZATIONGIFTAMOUNT] money,
      [FIRSTRESPONSEDATE] datetime
      );

    if @ENDDATE is null
      set @ENDDATE = getdate();

    while (@COUNTDATE < @ENDDATE)
    begin
      insert into @RESULT
        exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @SEGMENTATIONID, @ASOFDATE = @COUNTDATE;        

      --@DATEWINDOW: 1 = Daily, 2 = Weekly, 3 = Monthly, 4 = Yearly

      select @COUNTDATE =
        case @DATEWINDOW
          when 1 then dateadd(day,1,@COUNTDATE)
          when 2 then dateadd(week,1,@COUNTDATE)
          when 3 then dateadd(day,-1,dateadd(month,1,dateadd(day,1,@COUNTDATE)))
          when 4 then dateadd(year,1,@COUNTDATE)
          else @MAILDATE
        end;
    end

    --Remaining time in the given time frame

    insert into @RESULT
      exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @SEGMENTATIONID, @ASOFDATE = @ENDDATE;    

    --Populate the @ALL table

    declare @PREVIOUS_TOTAL_GIFTS int;
    declare @PREVIOUS_TOTAL_REVENUE money;
    declare @TOTAL_OFFERS int;
    declare @TOTAL_RESPONDERS int;
    declare @GIFTS int;
    declare @REVENUE money;
    declare @TOTAL_GIFTS int;
    declare @TOTAL_REVENUE money;
    declare @RESPONSERATE decimal(20,5);
    declare @TOTAL_ORGANIZATIONREVENUE money;

    declare RESULTCURSOR cursor local fast_forward for
      select     
        [TOTAL_OFFERS],
        [TOTAL_RESPONDERS],    
        [TOTAL_GIFTS],
        [TOTALORGANIZATIONGIFTAMOUNT] as [TOTAL_REVENUE],
        [RESPONSERATE]
      from @RESULT
      where [ID] not in (select min([ID]) from @RESULT)
      order by [ID];

    --Reset @COUNTDATE

    set @COUNTDATE = @MAILDATE;

    --Get values for any date before the starting date

    select 
      @PREVIOUS_TOTAL_GIFTS = [TOTAL_GIFTS], 
      @PREVIOUS_TOTAL_REVENUE = [TOTALORGANIZATIONGIFTAMOUNT]
    from @RESULT
    where [ID] in (select min([ID]) from @RESULT);

    open RESULTCURSOR;
    fetch next from RESULTCURSOR into @TOTAL_OFFERS, @TOTAL_RESPONDERS, @TOTAL_GIFTS, @TOTAL_REVENUE, @RESPONSERATE;
    while (@@FETCH_STATUS = 0)
    begin
      insert into @ALL (
        [GIFTS],
        [REVENUE],
        [TOTAL_GIFTS],
        [TOTAL_REVENUE],
        [TOTAL_OFFERS],
        [TOTAL_RESPONDERS],
        [GIFTDATE])
        values(
          @TOTAL_GIFTS - @PREVIOUS_TOTAL_GIFTS,            
          @TOTAL_REVENUE - @PREVIOUS_TOTAL_REVENUE,
          @TOTAL_GIFTS,
          @TOTAL_REVENUE,
          @TOTAL_OFFERS,
          @TOTAL_RESPONDERS,    
          @COUNTDATE
        );
      --@DATEWINDOW: 1 = Daily, 2 = Weekly, 3 = Monthly, 4 = Yearly

      select @COUNTDATE =
        case @DATEWINDOW
          when 1 then dateadd(day,1,@COUNTDATE)
          when 2 then dateadd(week,1,@COUNTDATE)
          when 3 then dateadd(day,-1,dateadd(month,1,dateadd(day,1,@COUNTDATE)))
          when 4 then dateadd(year,1,@COUNTDATE)
          else @MAILDATE
        end;
      set @PREVIOUS_TOTAL_GIFTS = @TOTAL_GIFTS;
      set @PREVIOUS_TOTAL_REVENUE = @TOTAL_REVENUE;
      fetch next from RESULTCURSOR into @TOTAL_OFFERS, @TOTAL_RESPONDERS, @TOTAL_GIFTS, @TOTAL_REVENUE, @RESPONSERATE;
    end;

    close RESULTCURSOR;
    deallocate RESULTCURSOR;

    delete from @RESULT;

    fetch next from @MAILINGCURSOR into @SEGMENTATIONID, @MAILDATE;
  end
  close @MAILINGCURSOR;
  deallocate @MAILINGCURSOR;


  --Populate the @DISPLAY table

  declare @DISPLAY table (
    [ID] int identity,
    [GIFTS] int,
    [PERCENT_GIFTS] decimal(10,4),
    [REVENUE] money,
    [PERCENT_REVENUE] decimal(10,4),
    [AVG_GIFTS] money,
    [TOTAL_GIFTS] int,
    [PERCENT_TOTALGIFTS] decimal(10,4),
    [TOTAL_REVENUE] money,
    [PERCENT_TOTALREVENUE] decimal(10,4),
    [TOTAL_OFFERS] int,
    [TOTAL_RESPONDERS] int,
    [RESPONSERATE] decimal(20,5),
    [STARTDATE] datetime,
    [ENDDATE] datetime
    );

  insert into @DISPLAY (
    [GIFTS],
    [REVENUE],
    [TOTAL_GIFTS],
    [TOTAL_REVENUE],
    [TOTAL_OFFERS],
    [TOTAL_RESPONDERS],
    [STARTDATE]
    )
    select
      sum([GIFTS]),
      sum([REVENUE]), 
      sum([TOTAL_GIFTS]),
      sum([TOTAL_REVENUE]),
      sum([TOTAL_OFFERS]),
      sum([TOTAL_RESPONDERS]),
      [GIFTDATE]
    from @ALL
    group by [GIFTDATE]
    order by [GIFTDATE];

  --Get totals for computations

  select
    @TOTAL_GIFTS = [TOTAL_GIFTS],
    @TOTAL_REVENUE = [TOTAL_REVENUE]
  from
    @DISPLAY
  group by [ID], [TOTAL_GIFTS], [TOTAL_REVENUE]
  having [ID] = max([ID]);

  --Do row level computations

  update @DISPLAY
  set 
    [PERCENT_GIFTS] = case when @TOTAL_GIFTS = 0 then 0 else cast([GIFTS] as decimal(10,4))/cast(@TOTAL_GIFTS as decimal(10,4)) end,
    [PERCENT_REVENUE] = case when @TOTAL_REVENUE = 0 then 0 else cast([REVENUE]/@TOTAL_REVENUE as decimal(10,4)) end,
    [AVG_GIFTS] = case when [GIFTS] = 0 then 0 else cast([REVENUE]/[GIFTS] as money) end,
    [PERCENT_TOTALGIFTS] = case when @TOTAL_GIFTS = 0 then 0 else cast([TOTAL_GIFTS] as decimal(10,4))/cast(@TOTAL_GIFTS as decimal(10,4)) end,
    [PERCENT_TOTALREVENUE] = case when @TOTAL_REVENUE = 0 then 0 else cast([TOTAL_REVENUE]/@TOTAL_REVENUE as decimal(10,4)) end,
    [RESPONSERATE] = case when [TOTAL_OFFERS] = 0 then 0 else (cast([TOTAL_RESPONDERS] as decimal(20,5)) / cast([TOTAL_OFFERS] as decimal(20,5))) end,
    [ENDDATE] =    case @DATEWINDOW 
            when 1 then [STARTDATE] 
            when 2 then dateadd(day,-1,dateadd(week,1,[STARTDATE]))
            when 3 then dateadd(day,-1,dateadd(month,1,[STARTDATE]))
            when 4 then dateadd(day,-1,dateadd(year,1,[STARTDATE]))
            else [STARTDATE]
          end; 

  update @DISPLAY
  set [ENDDATE] = @ENDDATE
  where [ENDDATE] > @ENDDATE;

  --Display 

  select
    [ID],
    [GIFTS],
    [PERCENT_GIFTS],
    [REVENUE],
    [PERCENT_REVENUE],
    [AVG_GIFTS],
    [TOTAL_GIFTS],
    [PERCENT_TOTALGIFTS],
    [TOTAL_REVENUE],
    [PERCENT_TOTALREVENUE],
    [RESPONSERATE],
    [STARTDATE],
    [ENDDATE],
    @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
    @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
    @CURRENCYSYMBOL as [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from
    @DISPLAY;

  return 0;