USP_MKTSEGMENTATION_CALCULATEDAILYRESPONSES

CREATE procedure dbo.[USP_MKTSEGMENTATION_CALCULATEDAILYRESPONSES]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
    set nocount on;

    declare @NORMALGIFTSTABLENAME nvarchar(128);
    declare @FIRSTRESPONSEDATE date = null;
    declare @ACTIVE bit;
    declare @COUNT int = 0;
    declare @NORMALGIFTS nvarchar(max) = null;
    declare @CURRENTDATE datetime = null;
    declare @SQL nvarchar(max);
    declare @PARAMDEF nvarchar(255);

    begin try
      select
        @FIRSTRESPONSEDATE = [FIRSTRESPONSEDATE],
        @ACTIVE = [MKTSEGMENTATION].[ACTIVE]
      from dbo.[MKTSEGMENTATION]
      left join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
      where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

      --Only calculate responses we have a first response date for, and the mailing is actually active...

      if @FIRSTRESPONSEDATE is not null and @ACTIVE = 1
        begin
          if @CHANGEAGENTID is null
            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

          set @CURRENTDATE = getdate(); 

          set @PARAMDEF = '@SEGMENTATIONID uniqueidentifier,' +
                          '@FIRSTRESPONSEDATE date,' +
                          '@CHANGEAGENTID uniqueidentifier,' +
                          '@CURRENTDATE datetime';

          --Check for multiple Record Sources. If found, cursor through and union all gifts from each corresponding normal gifts table for the effort.

          set @COUNT = (select count(*) from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @SEGMENTATIONID);

          if @COUNT > 1
            begin
              declare RECORDSOURCECURSOR cursor local fast_forward for
                select [IDSETREGISTER].[DBOBJECTNAME]
                from dbo.[MKTSEGMENTATIONACTIVATE]
                inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]
                where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID;

              open RECORDSOURCECURSOR;
              fetch next from RECORDSOURCECURSOR into @NORMALGIFTSTABLENAME;

              while (@@FETCH_STATUS = 0)
              begin
                if @NORMALGIFTS is null                
                  set @NORMALGIFTS = '(select AMOUNT, [DATE] from ' + @NORMALGIFTSTABLENAME
                else
                  set @NORMALGIFTS = @NORMALGIFTS + ' union all select AMOUNT, [DATE] from ' + @NORMALGIFTSTABLENAME                       

                fetch next from RECORDSOURCECURSOR into @NORMALGIFTSTABLENAME;
              end;

              close RECORDSOURCECURSOR;
              deallocate RECORDSOURCECURSOR;

              set @NORMALGIFTS = @NORMALGIFTS + ') g';
            end
          else
            begin
              select @NORMALGIFTS = [IDSETREGISTER].[DBOBJECTNAME]
                from dbo.[MKTSEGMENTATIONACTIVATE]
                inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]
                where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID;

            end

          -- This is going to pull the response dates and revenue from the parameterized table and sum up the total revenue and response count for each day 

          -- since the first response was received. 

          -- It also keeps a running total for the cumulative responses and revenue columns.

          -- At the end we will have data for responses and revenue for each day of the effort's activity as well as cumulative totals to use when comparing to

          -- older efforts responses and revenue for each day of it's life cycle when calculating pace.

          -- Since this is called during the effort refresh process we do a merge to account for new and updated information. 


          set @SQL = 'merge into dbo.MKTSEGMENTATIONDAILYRESPONSE t' + char(13) +
                     'using (' + char(13) +
                     'select @SEGMENTATIONID SEGID,' + char(13) +  
                     '    n1.NUM DSFR,' + char(13) +
                     '    sum(case when n1.NUM = n2.NUM then isnull(REVENUE,0) else 0 end) DAILYREVENUE,' + char(13) +
                     '    sum(case when n1.NUM = n2.NUM then isnull(RESPONSES,0) else 0 end) DAILYRESPONSES,' + char(13) +
                     '    sum(isnull(REVENUE,0)) CUMULATIVEREVENUE,' + char(13) +
                     '    sum(isnull(RESPONSES,0)) CUMULATIVERESPONSES' + char(13) +
                     'from dbo.NUMBERS n1' + char(13) +
                     'inner join dbo.NUMBERS n2 on n2.NUM <= n1.NUM' + char(13) +
                     'left join (select datediff(DAY, @FIRSTRESPONSEDATE, [DATE]) DSFR, sum(AMOUNT) REVENUE, count(*) RESPONSES' + char(13) +
                     '            from ' + @NORMALGIFTS + ' group by datediff(DAY, @FIRSTRESPONSEDATE, [DATE])) d on d.DSFR = n2.NUM' + char(13) +
                     'where n1.NUM between 0 and case when datediff(DAY, @FIRSTRESPONSEDATE, @CURRENTDATE) < 119 then datediff(DAY, @FIRSTRESPONSEDATE, @CURRENTDATE) else 119 end' + char(13) +
                     'group by n1.NUM) s on (s.SEGID = t.SEGMENTATIONID and s.DSFR = t.DAYSSINCEFIRSTRESPONSE)' + char(13) +                
                     'when matched then' + char(13) +
                     '     update set    t.DAILYREVENUE = s.DAILYREVENUE,' + char(13) +
                     '                t.DAILYRESPONSES = s.DAILYRESPONSES,' + char(13) + 
                     '                t.CUMULATIVEREVENUE = s.CUMULATIVEREVENUE,' + char(13) +
                     '                t.CUMULATIVERESPONSES = s.CUMULATIVERESPONSES,' + char(13) + 
                     '                t.CHANGEDBYID = @CHANGEAGENTID,' + char(13) +
                     '                t.DATECHANGED = @CURRENTDATE' + char(13) +
                     'when not matched then' + char(13) +
                     '    insert (SEGMENTATIONID, DAYSSINCEFIRSTRESPONSE, DAILYREVENUE, DAILYRESPONSES, CUMULATIVEREVENUE, CUMULATIVERESPONSES, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)' + char(13) +
                     '    values (s.SEGID, s.DSFR, s.DAILYREVENUE, s.DAILYRESPONSES, s.CUMULATIVEREVENUE, s.CUMULATIVERESPONSES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);';

          exec sp_executesql @SQL, @PARAMDEF,
            @SEGMENTATIONID = @SEGMENTATIONID,
            @FIRSTRESPONSEDATE = @FIRSTRESPONSEDATE,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTDATE = @CURRENTDATE;
        end
    end try

    begin catch
      exec dbo.[USP_RAISE_ERROR];
      return 1;
    end catch

    return 0;