UFN_ADDRESSPROCESS_EMAILS

CREATE function dbo.[UFN_ADDRESSPROCESS_EMAILS]
(
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier
)
returns table
as
return
(
  with [EMAILPREFS] ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID]) as
  (
    select [CONSTITUENTID], [EMAILADDRESS], [CONTACTID]
    from dbo.[UFN_ADDRESSPROCESS_EMAILPREFERENCES](@MAILTYPE, @PARAMETERSETID)
  ),
  [PRIMARYEMAILS] ([CONSTITUENTID], [EMAILADDRESS]) as
  (
    --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.

    select [CONSTITUENTID], [EMAILADDRESS]
    from dbo.[EMAILADDRESS]
    where [ISPRIMARY] = 1
    and [DONOTEMAIL] = 0
    and not exists(select * from [EMAILPREFS] where [CONSTITUENTID] = [EMAILADDRESS].[CONSTITUENTID])
  ),
  [EMAILS] ([CONSTITUENTID], [EMAILADDRESS], [CONTACTID]) as
  (
    select [CONSTITUENTID], [EMAILADDRESS], [CONTACTID] from [EMAILPREFS]
    union all
    select [CONSTITUENTID], [EMAILADDRESS], null from [PRIMARYEMAILS]
  )
  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 (SENDMAIL = 1 and DELIVERYMETHODCODE = 0 and (case when exists(select * from dbo.[INSTALLEDPRODUCTLIST] where [ID] = '42c15648-749e-4859-a56d-3a6474814cc7' and ([EXPIREDATE] = '' or [EXPIREDATE] >= getdate())) then [DONOTSENDOTHERCHANNEL] else 1 end) = 1)) then 1 else 0 end) as [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 = (select [CORRESPONDENCECODEID] from dbo.[CORRESPONDENCEPROCESS] where [ID] = @PARAMETERSETID)))
      and ((EVENTCATEGORYCODEID is null) or (@MAILTYPE = 2 and EVENTCATEGORYCODEID = (select E.[EVENTCATEGORYCODEID] from dbo.[EVENT] E inner join dbo.[INVITATION] I on E.[ID] = I.[EVENTID] where I.[ID] = @PARAMETERSETID)))
      and
      (
        (SITEID is null) or 
        (@MAILTYPE = 1 and SITEID = (select [SITEID] from dbo.[APPEAL] where [ID] = @PARAMETERSETID)) or
        (@MAILTYPE = 2 and SITEID in (select E.[SITEID] from dbo.[EVENTSITE] E inner join dbo.[INVITATION] I on E.[EVENTID] = I.[EVENTID] where I.[ID] = @PARAMETERSETID))
      )
      and ((PURPOSEID is null) or (@MAILTYPE = 8 and PURPOSEID = @PARAMETERSETID))
      and ((BUSINESSUNITCODEID is null) or (@MAILTYPE = 1 and BUSINESSUNITCODEID in (select [BUSINESSUNITCODEID] from dbo.[APPEALBUSINESSUNIT] where [APPEALID] = @PARAMETERSETID)))
      and ((CATEGORYCODEID is null) or (@MAILTYPE = 1 and CATEGORYCODEID = (select [APPEALCATEGORYCODEID] from dbo.[APPEAL] where [ID] = @PARAMETERSETID)))
      order by CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
    ) as MATCHEDPREFERENCES
    ), 0) = 0
  )
)