USP_MKTSEGMENTATIONSEGMENT_CACHEADDRESSES

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

  declare @SEGMENTATIONID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @SEGMENTTYPECODE tinyint;
  declare @CHANNELCODE tinyint;

  declare @USEADDRESSPROCESSING bit;
  declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @APPEALID nvarchar(36);
  declare @SQL nvarchar(max);
  declare @TABLENAME nvarchar(128);
  declare @MAILTYPECODE tinyint;
  declare @GLOBALTEMPSEGMENTCONSTITUENTTABLE nvarchar(128);

  declare @ORGMAILINGPREFERENCE tinyint;
  declare @ORGCONTACTALSOINDACTION tinyint;

  begin try
    select
      @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
      @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
      @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
      @USEADDRESSPROCESSING = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] else [MKTSEGMENTATION].[USEADDRESSPROCESSING] end,
      @ADDRESSPROCESSINGOPTIONID = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] end,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = case when [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] else [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end,
      @APPEALID = coalesce(convert(nvarchar(36), [APPEALMAILING].[APPEALID]), [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID]),
      @MAILTYPECODE = dbo.[UFN_MKTSEGMENTATION_GETMAILPREFERENCEMAILTYPECODE]([MKTSEGMENTATION].[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.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
    left join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;

    if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1  --BBEC only

       and (
         (@MAILINGTYPECODE = 0 and @SEGMENTTYPECODE = 1)            --Direct/appeal based mailings - we intentionally do not want to include other mailing types or list segments (acknowledgments is handled in its own process and for membership/sponsorship we don't want to exclude people from the counts based on an invalid address or comm prefs).

         or
         (@MAILINGTYPECODE = 5 and @SEGMENTTYPECODE = 3)            --Altru acknowledgements/reminders - we have to call this out separately because they are not really "appeal" mailings like the rest

       )
       and @CHANNELCODE = 0                                         --Mail - not Email

       and @USEADDRESSPROCESSING = 1
       and @ADDRESSPROCESSINGOPTIONID is not null
      begin
        --Validate that none of the temp tables used in this stored procedure exist already...

        if object_id('tempdb..#TEMP_ADDRESSPROCESS_ADDRESSES') is not null
          raiserror('The temp table #TEMP_ADDRESSPROCESS_ADDRESSES is used by this stored procedure but it already exists in this session.  Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);

        set @GLOBALTEMPSEGMENTCONSTITUENTTABLE = '##TEMP_SEGMENT_CONSTITUENT_' + replace(cast(@SEGMENTID as nvarchar(36)), '-', '_');
        set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @SEGMENTATIONID;

        if len(ltrim(rtrim(@APPEALID))) = 0
          set @APPEALID = null;

        select
          @ORGMAILINGPREFERENCE = [ORGMAILINGPREFERENCE],
          @ORGCONTACTALSOINDACTION = [ORGCONTACTALSOINDACTION]
        from dbo.[ADDRESSPROCESSINGOPTION]
        where [ID] = @ADDRESSPROCESSINGOPTIONID;

        if @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 0 -- consider seasonal dates as of today

          set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = dbo.[UFN_DATE_GETLATESTTIME](getdate());
        else
          set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = dbo.[UFN_DATE_GETLATESTTIME](@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE);


        --Put the addresses into a temp table since we have to get them from a stored procedure...

        create table #TEMP_ADDRESSPROCESS_ADDRESSES (
          [CONSTITUENTID] uniqueidentifier not null,
          [ADDRESSID] uniqueidentifier,
          [CONTACTID] uniqueidentifier,
          [POSITION] nvarchar(100) collate database_default,
          [HOUSEHOLDID] uniqueidentifier,
          [RETURNEDASHOUSEHOLDMEMBER] bit not null,
          [GROUPCONTACTID] uniqueidentifier
        );

        exec dbo.[USP_ADDRESSPROCESS_ADDRESSES]
          @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
          @MAILTYPE = @MAILTYPECODE,
          @PARAMETERSETID = @APPEALID,  --always set to appealID so we exclude people with specific mail preferences

          @DATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
          @INCLUDEHOUSEHOLDPROCESSING = 0,
          @CONSTITUENTIDSETTABLENAME = @GLOBALTEMPSEGMENTCONSTITUENTTABLE,
          @CONSTITUENTIDSETJOINCOLUMNNAME = 'ID',
          @IGNORECHANNELPREFERENCEFORSUPPRESSION = 0,
          @IGNOREADDRESSSUPPRESSION = 0;


        --Depending on the address processing options, we may need to remove some records from the results...

        if @ORGMAILINGPREFERENCE = 0  --Mail to contacts at the organization

          begin
            if @ORGCONTACTALSOINDACTION = 0  --Mail to contact only

              delete from #TEMP_ADDRESSPROCESS_ADDRESSES
              where [CONSTITUENTID] in (select [CONTACTID] from #TEMP_ADDRESSPROCESS_ADDRESSES where [CONTACTID] is not null);
            else if @ORGCONTACTALSOINDACTION = 1 -- Mail to individual only

              delete from #TEMP_ADDRESSPROCESS_ADDRESSES
              where [CONTACTID] in (select [CONSTITUENTID] from #TEMP_ADDRESSPROCESS_ADDRESSES);
          end


        --Insert the final addresses into the cache table...

        set @SQL = 'insert into dbo.[' + @TABLENAME + '] ([SEGMENTID], [CONSTITUENTID], [ADDRESSID], [CONTACTID])' + char(13) +
                   '  select' + char(13) +
                   '    @SEGMENTID as [SEGMENTID],' + char(13) +
                   '    [ADDRESSES].[CONSTITUENTID],' + char(13) +
                   '    [ADDRESSES].[ADDRESSID],' + char(13) +
                   '    [ADDRESSES].[CONTACTID]' + char(13) +
                   '  from ' + @GLOBALTEMPSEGMENTCONSTITUENTTABLE + ' as [CONSTITUENTS]' + char(13) +
                   '  inner join #TEMP_ADDRESSPROCESS_ADDRESSES as [ADDRESSES] on [ADDRESSES].[CONSTITUENTID] = [CONSTITUENTS].[ID]';
        exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;


        --Drop the temp tables...

        drop table #TEMP_ADDRESSPROCESS_ADDRESSES;
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];

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

    return 1;
  end catch

  return 0;