USP_MKTSEGMENTATION_GETPACKAGEOFFERCOUNTSFORSEGMENTATION

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETPACKAGEOFFERCOUNTSFORSEGMENTATION]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @ACTIVE bit;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @HISTORICALQUANTITY int;

  begin try
    create table #PACKAGETOTALOFFER (
      [PACKAGEID] uniqueidentifier primary key,
      [TOTALOFFERS] int
    );

    insert into #PACKAGETOTALOFFER ([PACKAGEID], [TOTALOFFERS])
      select [PACKAGEID], 0
      from dbo.[MKTSEGMENTATIONPACKAGE]
      where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID;

    select
      @MAILINGTYPECODE = [MAILINGTYPECODE],
      @ACTIVE = [ACTIVE],
      @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID])
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;

    if @MAILINGTYPECODE <> 4
      begin
        --Get the total offer count for the mailing data table (if exists)...

        if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
          begin
            set @SQL = 'update #PACKAGETOTALOFFER set' + char(13) +
                       '  [TOTALOFFERS] = [PACKAGEOFFER].[OFFERS]' + char(13) +
                       'from #PACKAGETOTALOFFER as [PACKAGETOTALOFFER]' + char(13) +
                       'inner join (' + char(13) +
                       '  select' + char(13) +
                       '    isnull([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID]) as [PACKAGEID],' + char(13) +
                       '    count([DONORS].[DONORID]) as [OFFERS]' + char(13) +
                       '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
                       '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DONORS].[SEGMENTID]' + char(13) +
                       '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
                       '  left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
                       '  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = ' + (case when @ACTIVE = 1 then '[MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]' else '[MKTSEGMENT].[CURRENTSEGMENTLISTID]' end) + char(13) +
                       '  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [DONORS].[TESTSEGMENTID]' + char(13) +
                       '  where ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0)' + char(13) +
                       '  and [MKTSEGMENT].[ISHISTORICAL] = 0' + char(13) +
                       '  and [DONORS].[DONORQUERYVIEWCATALOGID] is not null' + char(13) +
                       '  group by isnull([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID])' + char(13) +
                       ') as [PACKAGEOFFER] on [PACKAGEOFFER].[PACKAGEID] = [PACKAGETOTALOFFER].[PACKAGEID]';
            exec sp_executesql @SQL;
          end


        --Add in the seeds...

        update #PACKAGETOTALOFFER set
          [TOTALOFFERS] += [SEED].[NUMSEEDS]
        from (
          select count([ID]) as [NUMSEEDS]
          from dbo.[MKTSEGMENTATIONSEED]
          where [SEGMENTATIONID] = @SEGMENTATIONID
        ) as [SEED];


        --Get the total offer count for any vendor managed segments and add their offers to the total offer count...

        declare VENDORMANAGEDSEGMENTCURSOR cursor local fast_forward for
          --Vendor managed list segments

          select
            [MKTSEGMENTATIONSEGMENT].[ID],
            null,
            [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
          left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = (case when @ACTIVE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] else [MKTSEGMENT].[CURRENTSEGMENTLISTID] end)
          where [MKTSEGMENTLIST].[TYPECODE] = 1
          and [MKTSEGMENTATION].[ID] = @SEGMENTATIONID

          union

          --Vendor managed list test segments

          select
            [MKTSEGMENTATIONSEGMENT].[ID],
            [MKTSEGMENTATIONTESTSEGMENT].[ID],
            [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
          inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
          left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = (case when @ACTIVE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] else [MKTSEGMENT].[CURRENTSEGMENTLISTID] end)
          where [MKTSEGMENTLIST].[TYPECODE] = 1
          and [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

        open VENDORMANAGEDSEGMENTCURSOR;
        fetch next from VENDORMANAGEDSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @PACKAGEID;

        while (@@FETCH_STATUS = 0)
        begin
          update #PACKAGETOTALOFFER set
            [TOTALOFFERS] += dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETVENDORMANAGEDQUANTITY](@SEGMENTID, @TESTSEGMENTID)
          where [PACKAGEID] = @PACKAGEID;

          fetch next from VENDORMANAGEDSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @PACKAGEID;
        end

        close VENDORMANAGEDSEGMENTCURSOR;
        deallocate VENDORMANAGEDSEGMENTCURSOR;


        --Get the total offer count for any historical segments (non-lists) and add their offers to the total offer count...

        declare HISTORICALSEGMENTCURSOR cursor local fast_forward for
          --Historical segments

          select
            [MKTSEGMENTATIONSEGMENT].[ID],
            null,
            (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end),
            [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          where [MKTSEGMENT].[ISHISTORICAL] = 1
          and [MKTSEGMENTATION].[ID] = @SEGMENTATIONID

          union

          --Historical test segments

          select
            [MKTSEGMENTATIONSEGMENT].[ID],
            [MKTSEGMENTATIONTESTSEGMENT].[ID],
            (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end),
            [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
          from dbo.[MKTSEGMENTATION]
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
          inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          where [MKTSEGMENT].[ISHISTORICAL] = 1
          and [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

        open HISTORICALSEGMENTCURSOR;
        fetch next from HISTORICALSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @HISTORICALQUANTITY, @PACKAGEID;

        while (@@FETCH_STATUS = 0)
        begin
          update #PACKAGETOTALOFFER set
            [TOTALOFFERS] += dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETOFFERCOUNT](@SEGMENTID, @TESTSEGMENTID, @HISTORICALQUANTITY)
          where [PACKAGEID] = @PACKAGEID;

          fetch next from HISTORICALSEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @HISTORICALQUANTITY, @PACKAGEID;
        end

        close HISTORICALSEGMENTCURSOR;
        deallocate HISTORICALSEGMENTCURSOR;
      end
    else
      update #PACKAGETOTALOFFER set
        [TOTALOFFERS] = dbo.[UFN_MKTSEGMENTATIONPASSIVEPACKAGE_GETQUANTITY](@SEGMENTATIONID, [PACKAGEID], @ACTIVE);

    select
      [PACKAGEID],
      [TOTALOFFERS]
    from #PACKAGETOTALOFFER;

    drop table #PACKAGETOTALOFFER;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];

    if object_id('tempdb..#PACKAGETOTALOFFER') is not null
      drop table #PACKAGETOTALOFFER;

    return 1;
  end catch

  return 0;