USP_ADDRESSPROCESS_EMAILS

CREATE procedure dbo.[USP_ADDRESSPROCESS_EMAILS]
(
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier,
  @CONSTITUENTIDSETTABLENAME nvarchar(128),       --Table or view that contains constituent IDs

  @CONSTITUENTIDSETJOINCOLUMNNAME nvarchar(128),  --Column to use when joining to the table or view

  @IGNORECHANNELPREFERENCEFORSUPPRESSION bit = 0  --0=Respect channel preferences when suppressing email addresses, 1=Ignore channel preferences and do not let other channels (ie: mail) cause email addresses to be suppressed

)
as
  set nocount on;

  --Returns results in the following temp table named #TEMP_ADDRESSPROCESS_EMAILS.  This temp table must exist

  --outside of this stored procedure.  Use the statement below to create the temp table before calling this stored

  --procedure.  The temp table will be indexed on the CONSTITUENTID column.

  --

  --create table #TEMP_ADDRESSPROCESS_EMAILS (

  --  [CONSTITUENTID] uniqueidentifier not null,

  --  [EMAILADDRESS] nvarchar(100) collate database_default,

  --  [CONTACTID] uniqueidentifier

  --);



  declare @CORRESPONDENCECODEID uniqueidentifier;
  declare @EVENTCATEGORYCODEID uniqueidentifier;
  declare @EVENTSITEIDS table ([ID] uniqueidentifier primary key);
  declare @APPEALBUSINESSUNITCODEIDS table ([ID] uniqueidentifier primary key);
  declare @APPEALCATEGORYCODEID uniqueidentifier;
  declare @APPEALSITEID uniqueidentifier;

  declare @OBJID int;
  declare @ISTEMPTABLE bit;
  declare @SQL nvarchar(max);

  --Debug only

  --declare @STARTDATE datetime;

  --declare @STATUS nvarchar(100);



  begin try
    --Validate the required results temp table exists and that it does not have indexes defined on it...

    set @OBJID = object_id('tempdb..#TEMP_ADDRESSPROCESS_EMAILS');
    if @OBJID is null
      raiserror('The temp table #TEMP_ADDRESSPROCESS_EMAILS is required to hold the results of this stored procedure but it does not exist.  Please create the temp table before calling this stored procedure.', 13, 1);
    if exists(select * from tempdb.sys.indexes where object_id = @OBJID and type = 2)  --only concerned with nonclustered indexes

      raiserror('The temp table #TEMP_ADDRESSPROCESS_EMAILS already has indexes defined on it.  Please remove these indexes before calling this stored procedure.', 13, 1);

    --Validate that none of the temp tables used in this stored procedure exist already...

    if object_id('tempdb..#TEMPCONSTITUENT') is not null
      raiserror('The temp table #TEMPCONSTITUENT 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);
    if object_id('tempdb..#TEMPPRIMARYEMAILADDRESS') is not null
      raiserror('The temp table #TEMPPRIMARYEMAILADDRESS 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);
    if object_id('tempdb..#TEMPMAILPREFERENCEEMAILADDRESS') is not null
      raiserror('The temp table #TEMPMAILPREFERENCEEMAILADDRESS 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);
    if object_id('tempdb..#TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS') is not null
      raiserror('The temp table #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS 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);

    --Validate the constituent IDSet table exists and is defined appropriately...

    if len(@CONSTITUENTIDSETTABLENAME) = 0 or len(@CONSTITUENTIDSETJOINCOLUMNNAME) = 0
      raiserror('The parameters @CONSTITUENTIDSETTABLENAME and @CONSTITUENTIDSETJOINCOLUMNNAME are required to execute this stored procedure.', 13, 1);
    set @ISTEMPTABLE = (case when left(@CONSTITUENTIDSETTABLENAME, 1) = '#' then 1 else 0 end);
    set @OBJID = object_id((case when @ISTEMPTABLE = 1 then 'tempdb..' else 'dbo.' end) + '[' + @CONSTITUENTIDSETTABLENAME + ']');
    if @OBJID is null
      raiserror('The constituent IDSet table specified in the @CONSTITUENTIDSETTABLENAME parameter does not exist.', 13, 1);
    if (@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'uniqueidentifier'))) or
       (@ISTEMPTABLE = 0 and not exists(select * from sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'uniqueidentifier')))
      raiserror('The constituent IDSet join column specified in the @CONSTITUENTIDSETJOINCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);

    if @IGNORECHANNELPREFERENCEFORSUPPRESSION is null
      set @IGNORECHANNELPREFERENCEFORSUPPRESSION = 0;



    --Get some info for different mail types...

    --0 = Revenue Acknowledgements

    --1 = Appeals

    --2 = Events

    --3 = General Correspondence

    --4 = Reminders

    --5 = Receipts

    --6 = Planned Gift Acknowledgements

    --7 = Tribute Acknowledgements

    --8 = Stewardship


    if @MAILTYPE = 1
      begin
        select
          @APPEALCATEGORYCODEID = APPEALCATEGORYCODEID,
          @APPEALSITEID = SITEID
        from dbo.APPEAL
        where ID = @PARAMETERSETID;

        insert into @APPEALBUSINESSUNITCODEIDS ([ID])
          select BUSINESSUNITCODEID
          from dbo.APPEALBUSINESSUNIT
          where APPEALID = @PARAMETERSETID;
      end
    else if @MAILTYPE = 2
      begin
        select
          @EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID
        from dbo.EVENT E
        inner join dbo.INVITATION I on E.ID = I.EVENTID
        where I.ID = @PARAMETERSETID;

        insert into @EVENTSITEIDS ([ID])
          select E.SITEID
          from dbo.EVENTSITE E
          inner join dbo.INVITATION I on E.EVENTID = I.EVENTID
          where I.ID = @PARAMETERSETID;
      end
    else if @MAILTYPE = 3
      select
        @CORRESPONDENCECODEID = CORRESPONDENCECODEID
      from dbo.CORRESPONDENCEPROCESS
      where ID = @PARAMETERSETID;





    /****************************************************/
    /* Constituents                                     */
    /****************************************************/
    --Debug only

    --raiserror('Constituents...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


    create table #TEMPCONSTITUENT (
      [ID] uniqueidentifier not null
    );

    set @SQL = 'insert into #TEMPCONSTITUENT ([ID])' + char(13) +
               '  select ' + quotename(@CONSTITUENTIDSETJOINCOLUMNNAME) + char(13) +
               '  from dbo.' + quotename(@CONSTITUENTIDSETTABLENAME);

    exec sp_executesql @SQL;

    --Create an index on the temp table...

    create nonclustered index [IX_TEMPCONSTITUENTS_ID] on #TEMPCONSTITUENT ([ID]);

    --Debug only

    --set @STATUS = 'Constituents... ' + cast((select count(*) from #TEMPCONSTITUENT) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;







    /****************************************************/
    /* Mail Preference Email Addresses                  */
    /****************************************************/
    --Debug only

    --raiserror('Mail Preference Email Addresses...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


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

    insert into #TEMPMAILPREFERENCEEMAILADDRESS ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID])
      select
        E.[CONSTITUENTID],
        E.[EMAILADDRESS],
        E.[CONTACTID]
      from #TEMPCONSTITUENT C
      inner join dbo.[UFN_ADDRESSPROCESS_EMAILPREFERENCES](@MAILTYPE, @PARAMETERSETID) E on E.[CONSTITUENTID] = C.[ID];

    --Create an index on the temp table...

    create nonclustered index [IX_TEMPMAILPREFERENCEEMAILADDRESS_CONSTITUENTID] on #TEMPMAILPREFERENCEEMAILADDRESS ([CONSTITUENTID])
      include ([EMAILADDRESS], [CONTACTID]);

    --Debug only

    --set @STATUS = 'Mail Preference Email Addresses... ' + cast((select count(*) from #TEMPMAILPREFERENCEEMAILADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;






    /*****************************************************/
    /* Orgs With No Preference - Contact Email Addresses */
    /*****************************************************/
    --Debug only

    --raiserror('Orgs With No Preference - Contact Email Addresses...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


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

    --For all organizations that do not have any mail preferences specified, or were excluded from above...

    --Since we don't have "email address" processing options to specify whether or not to use the contact's email (like we do for "mail" addresses),

    --we automatically assume we need to pull the contact emails for all organizations that don't have a preference above.  We also pull the

    --organization's valid primary email since we don't know if we should split out the contacts or not.  It will be the caller's responsibility

    --to join to the resulting temp table correctly on the CONSTITUENTID and CONTACTID fields (remember CONTACTID can be null).  Pull the email address

    --off all the contact records and consider this the contact's "primary email" (instead of their personal email) (we'll still need to check the

    --"Do not email" flag).  We do this instead of showing the contact with the organization's valid primary email or the contact's personal email.

    with [ORG_NO_PREF] ([ID]) as
    (
      --All orgs with no preferences (or they do have a preference - either different preference criteria or the email is not valid)

      select C.[ID]
      from #TEMPCONSTITUENT C
      inner join dbo.[CONSTITUENT] as [ORG] on [ORG].[ID] = C.[ID]
      where [ORG].[ISORGANIZATION] = 1
      and not exists(select * from #TEMPMAILPREFERENCEEMAILADDRESS where [CONSTITUENTID] = C.[ID])
    )
    insert into #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID])
      --All orgs with no preference and a valid primary email

      select
        [ORG_NO_PREF].[ID],
        E.[EMAILADDRESS],
        null
      from [ORG_NO_PREF]
      inner join dbo.[EMAILADDRESS] as E on E.[CONSTITUENTID] = [ORG_NO_PREF].[ID]
      where E.[ISPRIMARY] = 1  --only constits with a primary email

      and E.[DONOTEMAIL] = 0  --only constits with a valid email

      union all
      --All contacts with a valid email of orgs with no preference

      select
        [ORG_NO_PREF].[ID],
        [CA].[EMAILADDRESS],
        [CA].[CONSTITUENTID]
      from [ORG_NO_PREF]
      inner join dbo.[RELATIONSHIP] on [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = [ORG_NO_PREF].[ID]
      inner join dbo.[EMAILADDRESS] as [CA] on [CA].[RELATIONSHIPID] = [RELATIONSHIP].[ID]
      where [RELATIONSHIP].[ISCONTACT] = 1
      and [CA].[DONOTEMAIL] = 0;  --only contacts with a valid email


    --Create an index on the temp table...

    create nonclustered index [IX_TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS_CONSTITUENTID] on #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS ([CONSTITUENTID])
      include ([EMAILADDRESS], [CONTACTID]);

    --Debug only

    --set @STATUS = 'Orgs With No Preference - Contact Email Addresses... ' + cast((select count(*) from #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;







    /****************************************************/
    /* Valid Primary Email Addresses                    */
    /****************************************************/
    --Debug only

    --raiserror('Valid Primary Email Addresses...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


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

    --Since we don't have email address processing options to specify whether or not to use the primary email (like we do for mail addresses),

    --we automatically assume we need to pull the primary emails for all people that don't have a preference above.

    insert into #TEMPPRIMARYEMAILADDRESS ([CONSTITUENTID], [EMAILADDRESS])
      select
        E.[CONSTITUENTID],
        E.[EMAILADDRESS]
      from #TEMPCONSTITUENT as C
      inner join dbo.[EMAILADDRESS] as E on E.[CONSTITUENTID] = C.[ID]
      where E.[ISPRIMARY] = 1  --only constits with a primary email

      and E.[DONOTEMAIL] = 0  --only constits with a valid email

      and not exists(select * from #TEMPMAILPREFERENCEEMAILADDRESS where [CONSTITUENTID] = E.[CONSTITUENTID])
      and not exists(select * from #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS where [CONSTITUENTID] = E.[CONSTITUENTID]);

    --Create some indexes on the temp table...

    create nonclustered index [IX_TEMPPRIMARYEMAILADDRESS_CONSTITUENTID] on #TEMPPRIMARYEMAILADDRESS ([CONSTITUENTID])
      include ([EMAILADDRESS]);

    --Debug only

    --set @STATUS = 'Valid Primary Email Addresses... ' + cast((select count(*) from #TEMPPRIMARYEMAILADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;








    /****************************************************/
    /* BasicDev only                                    */
    /****************************************************/
    declare @USEDONOTSENDOTHERCHANNEL bit;

    set @USEDONOTSENDOTHERCHANNEL = (
      case when exists(
        select * 
        from dbo.[INSTALLEDPRODUCTLIST] 
        where [ID] = '42c15648-749e-4859-a56d-3a6474814cc7' 
        and ([EXPIREDATE] = '' or [EXPIREDATE] >= getdate()))
      then 1 else 0 end);


    /****************************************************/
    /* Return the final email addresses                 */
    /****************************************************/
    --Debug only

    --raiserror('Return the final email addresses...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


    with [EMAILS] ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID]) as
    (
        select [CONSTITUENTID], [EMAILADDRESS], [CONTACTID] from #TEMPMAILPREFERENCEEMAILADDRESS
        union all
        select [CONSTITUENTID], [EMAILADDRESS], [CONTACTID] from #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS
        union all
        select [CONSTITUENTID], [EMAILADDRESS], null from #TEMPPRIMARYEMAILADDRESS
    )
    insert into #TEMP_ADDRESSPROCESS_EMAILS ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID])
      select
        [EMAILS].[CONSTITUENTID],
        [EMAILS].[EMAILADDRESS],
        [EMAILS].[CONTACTID]
      from [EMAILS]
      where
      (
        --Verify that the comm prefs allow the user to receive mailings

        isnull((
        select SUPPRESSMAILING from 
        (
          select top 1
  case when (SENDMAIL = 0 or (@IGNORECHANNELPREFERENCEFORSUPPRESSION = 0 and SENDMAIL = 1 and DELIVERYMETHODCODE = 0 and (@USEDONOTSENDOTHERCHANNEL = 0 or (@USEDONOTSENDOTHERCHANNEL = 1 and DONOTSENDOTHERCHANNEL = 1)))) then 1 else 0 end SUPPRESSMAILING,
            case
              --Revenue acknowledgements

              when @MAILTYPE = 0 and (ACKNOWLEDGEMENTID is not null) then 1
              --Appeals

              when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 3
              when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null) then 2
              when @MAILTYPE = 1 and (SITEID is not null and CATEGORYCODEID is not null) then 2
              when @MAILTYPE = 1 and (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 2
              when @MAILTYPE = 1 and (SITEID is not null or BUSINESSUNITCODEID is not null or CATEGORYCODEID is not null) then 1
              --Events

              when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null and SITEID is not null) then 2
              when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null or SITEID is not null) then 1
              --Correspondence

              when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null and CORRESPONDENCEID is not null) then 2
              when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null or CORRESPONDENCEID is not null) then 1
              --Reminders

              when @MAILTYPE = 4 and (PLEDGEREMINDERID is not null) then 1
              --Stewardship

              when @MAILTYPE = 8 and (PURPOSEID is not null) then 1
              else 0
            end as CRITERIAMATCHSCORE
          from dbo.MAILPREFERENCE
          where CONSTITUENTID = [EMAILS].[CONSTITUENTID]
          and MAILTYPECODE = @MAILTYPE
          and
          (
            (case @MAILTYPE
              when 0 then ACKNOWLEDGEMENTID
              when 3 then CORRESPONDENCEID
              when 4 then PLEDGEREMINDERID
              when 8 then PURPOSEID
              else null
              end) = @PARAMETERSETID
            or 
            (case @MAILTYPE
              when 0 then ACKNOWLEDGEMENTID
              when 3 then CORRESPONDENCEID
              when 4 then PLEDGEREMINDERID
              when 8 then PURPOSEID
              else null
             end) is null
          )
          and ((CORRESPONDENCECODEID is null) or (@MAILTYPE = 3 and CORRESPONDENCECODEID = @CORRESPONDENCECODEID))
          and ((EVENTCATEGORYCODEID is null) or (@MAILTYPE = 2 and EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID))
          and
          (
            (SITEID is null) or 
            (@MAILTYPE = 1 and SITEID = @APPEALSITEID) or
            (@MAILTYPE = 2 and SITEID in (select [ID] from @EVENTSITEIDS))
          )
          and ((PURPOSEID is null) or (@MAILTYPE = 8 and PURPOSEID = @PARAMETERSETID))
          and ((BUSINESSUNITCODEID is null) or (@MAILTYPE = 1 and BUSINESSUNITCODEID in (select [ID] from @APPEALBUSINESSUNITCODEIDS)))
          and ((CATEGORYCODEID is null) or (@MAILTYPE = 1 and CATEGORYCODEID = @APPEALCATEGORYCODEID))
          order by CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
        ) as MATCHEDPREFERENCES
        ), 0) = 0
      );

    --Create indexes on the final email address results...

    create nonclustered index [IX_TEMP_ADDRESSPROCESS_EMAILS_CONSTITUENTID] on #TEMP_ADDRESSPROCESS_EMAILS ([CONSTITUENTID])
      include ([EMAILADDRESS], [CONTACTID]);

    --Debug only

    --set @STATUS = 'Return the final email addresses... ' + cast((select count(*) from #TEMP_ADDRESSPROCESS_EMAILS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;






    /****************************************************/
    /* Clean up                                         */
    /****************************************************/
    drop table #TEMPCONSTITUENT;
    drop table #TEMPMAILPREFERENCEEMAILADDRESS;
    drop table #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS;
    drop table #TEMPPRIMARYEMAILADDRESS;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];

    if object_id('tempdb..#TEMPCONSTITUENT') is not null
      drop table #TEMPCONSTITUENT;
    if object_id('tempdb..#TEMPMAILPREFERENCEEMAILADDRESS') is not null
      drop table #TEMPMAILPREFERENCEEMAILADDRESS;
    if object_id('tempdb..#TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS') is not null
      drop table #TEMPORGANIZATIONNOPREFERENCEEMAILADDRESS;
    if object_id('tempdb..#TEMPPRIMARYEMAILADDRESS') is not null
      drop table #TEMPPRIMARYEMAILADDRESS;

    return 1;
  end catch

  return 0;