USP_MKTSEGMENTATIONSEGMENT_GETRESPONSECOUNTS

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_GETRESPONSECOUNTS]
(
  @SEGMENTID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier = null,
  @ASOFDATE datetime = null
)
as
  set nocount on;

  declare @EXCLUDE bit;
  declare @MAILINGTYPECODE tinyint;
  declare @ACTIVE bit;
  declare @IMPRESSIONCALCULATIONMETHODCODE tinyint;
  declare @IMPRESSIONS integer;
  declare @EXPOSURESTARTDATE datetime;
  declare @EXPOSUREENDDATE datetime;
  declare @SEGMENTTYPECODE tinyint;
  declare @ISVENDORMANAGED bit;
  declare @ISHISTORICAL bit;
  declare @HISTORICALQUANTITY integer;

  declare @GIFTIDSETNAME nvarchar(128);
  declare @DATATABLE nvarchar(128);
  declare @REVENUESEGMENTTABLE nvarchar(128);

  declare @OFFERSQL nvarchar(max);
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(max);

  declare @OFFERS int;
  declare @RESPONDERS int;
  declare @RESPONSES int;
  declare @TOTALGIFTAMOUNT money;
  declare @AVERAGEGIFTAMOUNT money;
  declare @ORGANIZATIONTOTALGIFTAMOUNT money;
  declare @ORGANIZATIONAVERAGEGIFTAMOUNT money;


  begin try
    --Gather some info so we can build the SQL...

    select distinct
      @EXCLUDE = [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
      @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
      @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
      @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
      @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]),
      @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL],
      @HISTORICALQUANTITY = (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end),
      @GIFTIDSETNAME = dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]),
      @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID]),
      @REVENUESEGMENTTABLE = dbo.[UFN_REVENUESEGMENT_MAKETABLENAME]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID])
    from dbo.[MKTSEGMENTATIONSEGMENT]
    left join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
    and (@TESTSEGMENTID is null or [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID);

    --Get the total offer count for this segment depending on the mailing type...

    if @MAILINGTYPECODE = 4  --Public media segments

      begin
        if @ACTIVE = 1 and exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTACTIVE] where [SEGMENTID] = @SEGMENTID)
          select
            @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE],
            @IMPRESSIONS = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS]
          from dbo.[MKTSEGMENTATIONSEGMENT]
          inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
          where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
        else
          select
            @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE],
            @IMPRESSIONS = [MKTSEGMENTPASSIVE].[IMPRESSIONS]
          from dbo.[MKTSEGMENTATIONSEGMENT]
          left join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

        set @OFFERSQL = cast(dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY](@SEGMENTID, @ACTIVE, @IMPRESSIONCALCULATIONMETHODCODE, @IMPRESSIONS, @EXPOSURESTARTDATE, @EXPOSUREENDDATE) as nvarchar(10));
      end

    else if @ISVENDORMANAGED = 1 or (@SEGMENTTYPECODE = 2 and @ISHISTORICAL = 1)
      -- vendor managed segments and historical list segments work the same way: get the total offers from the segment and

      -- calculate the offers for each test segment, taking into account fractions/percents and distributing any remainders

      set @OFFERSQL = cast(dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETVENDORMANAGEDQUANTITY](@SEGMENTID, @TESTSEGMENTID) as nvarchar(10));

    else if @ISHISTORICAL = 1
      -- historical house file segments work similarly: get the total offers from the segment and

      -- calculate the offers for each test segment, taking into account fractions/percents and distributing any remainders

      set @OFFERSQL = cast(dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETOFFERCOUNT](@SEGMENTID, @TESTSEGMENTID, @HISTORICALQUANTITY) as nvarchar(10));

    else
      --Get the offers from the activated data table...

      set @OFFERSQL = '(select count([D1].[' + (case @MAILINGTYPECODE when 1 then 'REVENUEID' when 2 then 'MEMBERSHIPID' when 3 then 'SPONSORSHIPID' when 5 then 'REVENUEID' else 'DONORID' end) + ']) from dbo.[' + @DATATABLE + '] as [D1] where [D1].[SEGMENTID] = @SEGMENTID and [D1].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')';

    if @EXCLUDE = 0
      begin
        --Build the SQL to get the response counts...

        set @SQL = 'select' + char(13) +
                   '  @OFFERS = ' + @OFFERSQL + ',' + char(13) +
                   '  @RESPONDERS = count(distinct([GIFTIDSET].[DONORID])),' + char(13) + 
                   '  @RESPONSES = count([GIFTIDSET].[ID]),' + char(13) + 
                   '  @TOTALGIFTAMOUNT = isnull(sum([GIFTIDSET].[AMOUNT]),0),' + char(13) +
                   '  @AVERAGEGIFTAMOUNT = isnull(avg([GIFTIDSET].[AMOUNT]),0),' + char(13) +
                   '  @ORGANIZATIONTOTALGIFTAMOUNT = isnull(sum([GIFTIDSET].[ORGANIZATIONAMOUNT]),0),' + char(13) +
                   '  @ORGANIZATIONAVERAGEGIFTAMOUNT = isnull(avg([GIFTIDSET].[ORGANIZATIONAMOUNT]),0)' + char(13) +
                   'from dbo.[' + @REVENUESEGMENTTABLE + '] as [RS]' + char(13) +
                   'inner join dbo.' + @GIFTIDSETNAME + ' as [GIFTIDSET] on [GIFTIDSET].[ID] = [RS].[REVENUEID]' + char(13) +
                   'where [RS].[SEGMENTID] = @SEGMENTID' + char(13) +
                   'and [RS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
                   (case when @ASOFDATE is not null then 'and [GIFTIDSET].[DATE] <= @ASOFDATE' else '' end);

        set @PARAMDEF = '@SEGMENTID uniqueidentifier, ' +
                        '@TESTSEGMENTID uniqueidentifier, ' +
                        '@ASOFDATE datetime, ' +
                        '@OFFERS int output, ' +
                        '@RESPONDERS int output, ' +
                        '@RESPONSES int output, ' +
                        '@TOTALGIFTAMOUNT money output, ' +
                        '@AVERAGEGIFTAMOUNT money output, ' +
                        '@ORGANIZATIONTOTALGIFTAMOUNT money output, ' +
                        '@ORGANIZATIONAVERAGEGIFTAMOUNT money output';

        --Execute the SQL to get the counts/amounts...

        exec sp_executesql @SQL, @PARAMDEF,
          @SEGMENTID = @SEGMENTID,
          @TESTSEGMENTID = @TESTSEGMENTID,
          @ASOFDATE = @ASOFDATE,
          @OFFERS = @OFFERS output,
@RESPONDERS = @RESPONDERS output, 
          @RESPONSES = @RESPONSES output, 
          @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT output, 
          @AVERAGEGIFTAMOUNT = @AVERAGEGIFTAMOUNT output,
          @ORGANIZATIONTOTALGIFTAMOUNT = @ORGANIZATIONTOTALGIFTAMOUNT output, 
          @ORGANIZATIONAVERAGEGIFTAMOUNT = @ORGANIZATIONAVERAGEGIFTAMOUNT output;
      end
    else -- @EXCLUDE = 1

      begin
        --Inline exclusions cannot receive responses and cannot be a test segment...

        set @SQL = 'select' + char(13) +
                   '  @OFFERS = ' + @OFFERSQL + char(13);

        exec sp_executesql @SQL, N'@OFFERS int output, @SEGMENTID uniqueidentifier', @OFFERS = @OFFERS output, @SEGMENTID = @SEGMENTID;

        set @RESPONDERS = 0;
        set @RESPONSES = 0;
        set @TOTALGIFTAMOUNT = 0;
        set @AVERAGEGIFTAMOUNT = 0;
        set @ORGANIZATIONTOTALGIFTAMOUNT = 0;
        set @ORGANIZATIONAVERAGEGIFTAMOUNT = 0;
      end

    --Return the values

    select 
      @OFFERS as [OFFERS],
      @RESPONDERS as [RESPONDERS], 
      @RESPONSES as [RESPONSES],
      @TOTALGIFTAMOUNT as [TOTALGIFTAMOUNT],
      @AVERAGEGIFTAMOUNT as [AVERAGEGIFTAMOUNT],
      @ORGANIZATIONTOTALGIFTAMOUNT as [ORGANIZATIONTOTALGIFTAMOUNT],
      @ORGANIZATIONAVERAGEGIFTAMOUNT as [ORGANIZATIONAVERAGEGIFTAMOUNT];
  end try

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

  return 0;