USP_MKTSEGMENTATIONSEGMENT_CACHEEMAILADDRESSES

create procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_CACHEEMAILADDRESSES]
(
  @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 @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,
      @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 = 1                                         --Email - not mail

       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_EMAILS') is not null
          raiserror('The temp table #TEMP_ADDRESSPROCESS_EMAILS 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_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_CREATETABLE] @SEGMENTATIONID;

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


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

        create table #TEMP_ADDRESSPROCESS_EMAILS (
          [CONSTITUENTID] uniqueidentifier not null,
          [EMAILADDRESS] nvarchar(100) collate database_default,
          [CONTACTID] uniqueidentifier
        );

        exec dbo.[USP_ADDRESSPROCESS_EMAILS]
          @MAILTYPE = @MAILTYPECODE,
          @PARAMETERSETID = @APPEALID,
          @CONSTITUENTIDSETTABLENAME = @GLOBALTEMPSEGMENTCONSTITUENTTABLE,
          @CONSTITUENTIDSETJOINCOLUMNNAME = 'ID',
          @IGNORECHANNELPREFERENCEFORSUPPRESSION = 0;


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

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


        --Drop the temp table...

        drop table #TEMP_ADDRESSPROCESS_EMAILS;
      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;