USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE]
(
  @SEGMENTID uniqueidentifier,
  @ONLYCLEARSAMERECORDTYPE bit = 0,
  @QUICKDELETE bit = 0
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEQUENCE int;
  declare @RECORDTYPEID uniqueidentifier;
  declare @RECORDSOURCETYPE tinyint;
  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  declare @LOCKPREFIX nvarchar(50);
  declare @LOCKNAME nvarchar(255);
  declare @LOCKRESULT int;
  declare @RETVAL int;

  begin try
    if @ONLYCLEARSAMERECORDTYPE is null
      set @ONLYCLEARSAMERECORDTYPE = 0;
    if @QUICKDELETE is null
      set @QUICKDELETE = 0;

    set @LOCKPREFIX = 'SegmentExclusionCache:';

    /* Get an app lock for this segment so that we don't run into problems when this SP gets called while trying to cache values for this segment. */
    set @LOCKNAME = @LOCKPREFIX + cast(@SEGMENTID as nvarchar(36));
    exec @LOCKRESULT = sp_getapplock @Resource=@LOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=3600000;
    if @LOCKRESULT < 0
      raiserror('A failure or timeout occurred while requesting an app lock to clear a marketing effort segment''s cache.', 13, 1);


    select
      @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      @SEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
      @RECORDTYPEID = [IDSETREGISTER].[RECORDTYPEID],
      @RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID])
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;


    /* Build the sql of segments to clear */
    set @SQL = 'declare @SEGMENTS table([ID] uniqueidentifier primary key);' + char(13) +
               char(13) +
               'insert into @SEGMENTS ([ID])' + char(13) +
               '  select [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
               '  from dbo.[MKTSEGMENTATIONSEGMENT]' + char(13) +
               '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
               '  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]' + char(13);

    if @ONLYCLEARSAMERECORDTYPE = 1
      set @SQL = @SQL +
                 '  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]' + char(13);

    set @SQL = @SQL +
               '  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
               '  and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] <> 1))  --exclude vendor managed segments' + char(13) +

               '  and [MKTSEGMENTATIONSEGMENT].[SEQUENCE] >= @SEQUENCE';

    if @ONLYCLEARSAMERECORDTYPE = 1
      begin
        --Need to clear all segments with the same record type or the consolidated recordtype for the related record source...

        if @RECORDSOURCETYPE = 1  --House file

          set @SQL = @SQL + char(13) +
                     '  and ([IDSETREGISTER].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                     '    or exists(' + char(13) +
                     '      select 1' + char(13) +
                     '      from dbo.[QUERYVIEWCATALOG]' + char(13) +
                     '      inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [QUERYVIEWCATALOG].[ID]' + char(13) +
                     '      inner join dbo.[QUERYVIEWCATALOG] as [CQV] on [CQV].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]' + char(13) +
                     '      where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                     '      and [CQV].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]' + char(13) +
                     '    )' + char(13) +
                     '  )';
        else
          begin
            if @RECORDSOURCETYPE = 2  --List

              set @SQL = @SQL + char(13) +
                         '  and ([IDSETREGISTER].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                         '    or exists(' + char(13) +
                         '      select 1' + char(13) +
                         '      from dbo.[QUERYVIEWCATALOG]' + char(13) +
                         '      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]' + char(13) +
                         '      inner join dbo.[QUERYVIEWCATALOG] as [CQV] on [CQV].[ID] = [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID]' + char(13) +
                         '      where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                         '      and [CQV].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]' + char(13) +
                         '    )' + char(13) +
                         '  )';
            else  --Consolidated list

              set @SQL = @SQL + char(13) +
                         '  and ([IDSETREGISTER].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                         '    or [IDSETREGISTER].[RECORDTYPEID] in (' + char(13) +
                         '      select [HQV].[RECORDTYPEID]' + char(13) +
                         '      from dbo.[QUERYVIEWCATALOG]' + char(13) +
                         '      inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]' + char(13) +
                         '      inner join dbo.[QUERYVIEWCATALOG] as [HQV] on [HQV].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID]' + char(13) +
                         '      where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                         '      union all' + char(13) +
                         '      select [LQV].[RECORDTYPEID]' + char(13) +
                         '      from dbo.[QUERYVIEWCATALOG]' + char(13) +
                         '      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] = [QUERYVIEWCATALOG].[ID]' + char(13) +
                         '      inner join dbo.[QUERYVIEWCATALOG] as [LQV] on [LQV].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]' + char(13) +
                         '      where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID' + char(13) +
                         '    )' + char(13) +
                         '  )';
          end
      end

    /* Delete all the cache info records for this segment and any segments after it because they will be invalid now */
    set @SQL = @SQL + ';' + char(13) +
               char(13) +
               'delete from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO]' + char(13) +
               'where [SEGMENTID] in (select [ID] from @SEGMENTS);' + char(13);

    /* This can be slow for large mailings, so only do this when we need to */
    if @QUICKDELETE = 0
      begin
        /* Delete all the records for this segment in each of the cache tables below */
        set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
          set @SQL = @SQL + char(13) +
                     'delete from dbo.[' + @TABLENAME + ']' + char(13) +
                     'where [SEGMENTID] in (select [ID] from @SEGMENTS);' + char(13);

        set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
         set @SQL = @SQL + char(13) +
                     'delete from dbo.[' + @TABLENAME + ']' + char(13) +
                     'where [SEGMENTID] in (select [ID] from @SEGMENTS);' + char(13);
      end

    exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @SEQUENCE int, @RECORDTYPEID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @SEQUENCE = @SEQUENCE, @RECORDTYPEID = @RECORDTYPEID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    set @RETVAL = 1;
  end catch

  /* Release the app lock */
  exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';

  return isnull(@RETVAL, 0);