USP_MKTSEGMENTATIONSEGMENT_GETACTIVEOFFERCOUNT

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_GETACTIVEOFFERCOUNT]
(
  @SEGMENTID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTTYPECODE tinyint;
  declare @ISVENDORMANAGED bit;
  declare @ISHISTORICAL bit;
  declare @HISTORICALQUANTITY integer;
  declare @IMPRESSIONCALCULATIONMETHODCODE tinyint;
  declare @IMPRESSIONS integer;
  declare @EXPOSURESTARTDATE datetime;
  declare @EXPOSUREENDDATE datetime;
  declare @OFFERS int;

  select
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENTATIONSEGMENT].[SEGMENTID]),
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL],
    @HISTORICALQUANTITY = (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end),
    @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE],
    @IMPRESSIONS = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS],
    @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
    @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  left join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

  if @MAILINGTYPECODE = 4  --Public media segments

    set @OFFERS = dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY](@SEGMENTID, 1, @IMPRESSIONCALCULATIONMETHODCODE, @IMPRESSIONS, @EXPOSURESTARTDATE, @EXPOSUREENDDATE);

  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 @OFFERS = dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETVENDORMANAGEDQUANTITY](@SEGMENTID, @TESTSEGMENTID);

  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 @OFFERS = dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETOFFERCOUNT](@SEGMENTID, @TESTSEGMENTID, @HISTORICALQUANTITY);

  else
    begin
      -- standard, revenue, membership, sponsorship

      -- get the total active offer count for the segment or test segment...

      set @SQL = 'select @OFFERS = count([' + (case @MAILINGTYPECODE when 1 then 'REVENUEID' when 2 then 'MEMBERSHIPID' when 3 then 'SPONSORSHIPID' when 5 then 'REVENUEID' else 'DONORID' end) + '])' + char(13) +
                 'from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + ']' + char(13) +
                 'where [SEGMENTID] = @SEGMENTID' + char(13) +
                 'and [TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
                 'and [DONORQUERYVIEWCATALOGID] is not null';

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

  select @OFFERS as [TOTALOFFERS];

  return 0;