UFN_ADDRESSPROCESS_EMAILPREFERENCES

CREATE function dbo.[UFN_ADDRESSPROCESS_EMAILPREFERENCES]
(
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier
)
returns table
as
return (
  select
    MP.CONSTITUENTID,
    E.EMAILADDRESS,
    null as CONTACTID
  from dbo.MAILPREFERENCE MP
  inner join dbo.CONSTITUENT C on MP.CONSTITUENTID = C.ID
  inner join dbo.EMAILADDRESS E on ((MP.USEPRIMARYEMAIL = 0 and MP.EMAILADDRESSID = E.ID) or (MP.CONSTITUENTID = E.CONSTITUENTID and MP.USEPRIMARYEMAIL = 1 and E.ISPRIMARY = 1))
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 1
  and MP.MAILTYPECODE = @MAILTYPE
  and E.DONOTEMAIL = 0
  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 not exists(select *
                     from dbo.MAILPREFERENCE 
                     where (case @MAILTYPE
                             when 0 then ACKNOWLEDGEMENTID
                             when 3 then CORRESPONDENCEID
                             when 4 then PLEDGEREMINDERID
                             when 8 then PURPOSEID
                             else null
                            end) = @PARAMETERSETID
                     and CONSTITUENTID = MP.CONSTITUENTID)
      -- only consider correspondence codes when there isn't a specific process preference

      and
      (@MAILTYPE <> 3 or
        (@MAILTYPE = 3 and
          (
            (
              MP.CORRESPONDENCECODEID is null and
              not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and CONSTITUENTID = MP.CONSTITUENTID)
            )
            or
            (
              MP.CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and
              not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID)
            )
          )
        )
      )
    )
  )
  and
  (@MAILTYPE <> 1 or
    (@MAILTYPE = 1 and
      (
        MP.BUSINESSUNITCODEID is null and
        MP.CATEGORYCODEID is null and
        MP.SITEID is null and
        not exists(select *
                   from dbo.MAILPREFERENCE sub, dbo.APPEAL
                   left join dbo.APPEALBUSINESSUNIT AB on AB.APPEALID = APPEAL.ID
                   where APPEAL.ID = @PARAMETERSETID and sub.ID <> MP.ID
                   and sub.CONSTITUENTID = MP.CONSTITUENTID
                   and ((sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                     or (sub.SITEID is null and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID is null)))
      )
      or
      (
        (MP.BUSINESSUNITCODEID in (select BUSINESSUNITCODEID from dbo.APPEALBUSINESSUNIT where APPEALID = @PARAMETERSETID) or MP.BUSINESSUNITCODEID is null) and
        (MP.CATEGORYCODEID = (select APPEALCATEGORYCODEID from dbo.APPEAL where ID = @PARAMETERSETID) or MP.CATEGORYCODEID is null) and
        (MP.SITEID = (select SITEID from dbo.APPEAL where ID = @PARAMETERSETID) or MP.SITEID is null) and
        not (MP.CATEGORYCODEID is null and MP.SITEID is null and MP.BUSINESSUNITCODEID is null)
      )
    )
  )
  and
  (@MAILTYPE <> 2 or
    (@MAILTYPE = 2 and
      (
        (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null) and
        not exists(select *
                   from dbo.MAILPREFERENCE sub, dbo.INVITATION I 
                   inner join dbo.EVENT E on I.EVENTID = E.ID 
                   left join dbo.EVENTSITE on EVENTSITE.EVENTID = E.ID
                   where I.ID = @PARAMETERSETID and sub.ID <> MP.ID
                   and sub.CONSTITUENTID = MP.CONSTITUENTID 
                   and ((sub.SITEID = EVENTSITE.SITEID and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
                     or (sub.SITEID is null and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
                     or (sub.EVENTCATEGORYCODEID is null and sub.SITEID = EVENTSITE.SITEID))
                  )
      )
      or
      (
        (MP.EVENTCATEGORYCODEID = (select EVENTCATEGORYCODEID from dbo.INVITATION I inner join dbo.EVENT E on I.EVENTID = E.ID where I.ID = @PARAMETERSETID) or MP.EVENTCATEGORYCODEID is null) and
        (MP.SITEID in (select SITEID from dbo.INVITATION I inner join dbo.EVENTSITE E on I.EVENTID = E.EVENTID where I.ID = @PARAMETERSETID) or MP.SITEID is null) and
        not (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null)
      )
    )
  )
  and not exists(select * from dbo.MAILPREFERENCEORGCONTACTTYPE where MAILPREFERENCEID = MP.ID)
  and not exists(select * from dbo.MAILPREFERENCEGROUPCONTACT where MAILPREFERENCEID = MP.ID)

  union all

  --grab contact addresses

  select distinct
    MP.CONSTITUENTID,
    isnull(CA.EMAILADDRESS, OA.EMAILADDRESS) as EMAILADDRESS,
    R.RECIPROCALCONSTITUENTID as CONTACTID
  from dbo.MAILPREFERENCEORGCONTACTTYPE 
  inner join dbo.MAILPREFERENCE MP on MAILPREFERENCEORGCONTACTTYPE.MAILPREFERENCEID = MP.ID
  inner join dbo.CONSTITUENT ORG on MP.CONSTITUENTID = ORG.ID
  left join dbo.EMAILADDRESS OA on ORG.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTEMAIL = 0
  inner join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = ORG.ID
  left join dbo.EMAILADDRESS CA on CA.RELATIONSHIPID = R.ID and CA.DONOTEMAIL = 0
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 1
  and MP.MAILTYPECODE = @MAILTYPE
  and @MAILTYPE <> 5  --For receipts, only return the first contact

  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 not exists(select *
                     from dbo.MAILPREFERENCE 
                     where (case @MAILTYPE
                             when 0 then ACKNOWLEDGEMENTID
                             when 3 then CORRESPONDENCEID
                             when 4 then PLEDGEREMINDERID
                             when 8 then PURPOSEID
                             else null
                            end) = @PARAMETERSETID
                     and CONSTITUENTID = MP.CONSTITUENTID)
      -- only consider correspondence codes when there isn't a specific process preference

      and
      (@MAILTYPE <> 3 or
        (@MAILTYPE = 3 and
          (
            (
              MP.CORRESPONDENCECODEID is null and
              not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and CONSTITUENTID = MP.CONSTITUENTID)
            )
            or
            (
              MP.CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and
              not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID)
            )
          )
        )
      )
    )
  )
  and
  (@MAILTYPE <> 1 or
    (@MAILTYPE = 1 and
      (
        MP.BUSINESSUNITCODEID is null and
        MP.CATEGORYCODEID is null and
        MP.SITEID is null and
        not exists(select *
                   from dbo.MAILPREFERENCE sub, dbo.APPEAL
                   left join dbo.APPEALBUSINESSUNIT AB on AB.APPEALID = APPEAL.ID
                   where APPEAL.ID = @PARAMETERSETID and sub.ID <> MP.ID
                   and sub.CONSTITUENTID = MP.CONSTITUENTID
                   and ((sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                     or (sub.SITEID is null and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                     or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                     or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID is null)))
      )
      or
      (
        (MP.BUSINESSUNITCODEID in (select BUSINESSUNITCODEID from dbo.APPEALBUSINESSUNIT where APPEALID = @PARAMETERSETID) or MP.BUSINESSUNITCODEID is null) and
        (MP.CATEGORYCODEID = (select APPEALCATEGORYCODEID from dbo.APPEAL where ID = @PARAMETERSETID) or MP.CATEGORYCODEID is null) and
        (MP.SITEID = (select SITEID from dbo.APPEAL where ID = @PARAMETERSETID) or MP.SITEID is null) and
        not (MP.CATEGORYCODEID is null and MP.SITEID is null and MP.BUSINESSUNITCODEID is null)
      )
    )
  )
  and
  (@MAILTYPE <> 2 or
    (@MAILTYPE = 2 and
      (
        (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null) and
        not exists(select *
                   from dbo.MAILPREFERENCE sub, dbo.INVITATION I 
                   inner join dbo.EVENT E on I.EVENTID = E.ID 
                   left join dbo.EVENTSITE on EVENTSITE.EVENTID = E.ID
                   where I.ID = @PARAMETERSETID and sub.ID <> MP.ID
                   and sub.CONSTITUENTID = MP.CONSTITUENTID 
                   and ((sub.SITEID = EVENTSITE.SITEID and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
                     or (sub.SITEID is null and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
                     or (sub.EVENTCATEGORYCODEID is null and sub.SITEID = EVENTSITE.SITEID)))
      )
      or
      (
        (MP.EVENTCATEGORYCODEID = (select EVENTCATEGORYCODEID from dbo.INVITATION I inner join dbo.EVENT E on I.EVENTID = E.ID where I.ID = @PARAMETERSETID) or MP.EVENTCATEGORYCODEID is null) and
        (MP.SITEID in (select SITEID from dbo.INVITATION I inner join dbo.EVENTSITE E on I.EVENTID = E.EVENTID where I.ID = @PARAMETERSETID) or MP.SITEID is null) and
        not (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null)
      )
    )
  )
  and R.ISCONTACT = 1
  and 
  (
    (R.CONTACTTYPECODEID = MAILPREFERENCEORGCONTACTTYPE.CONTACTTYPECODEID)
    or 
    (R.ISPRIMARYCONTACT = 1 and MAILPREFERENCEORGCONTACTTYPE.USEPRIMARYCONTACT = 1)
  )

  union all

  --For receipts, only grab the first contact found

  select
    MP.CONSTITUENTID,
    isnull(MPO.EMAILADDRESS, OA.EMAILADDRESS) as EMAILADDRESS,
    MPO.RECIPROCALCONSTITUENTID as CONTACTID
  from dbo.MAILPREFERENCE MP
  left join dbo.EMAILADDRESS OA on OA.CONSTITUENTID = MP.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTEMAIL = 0
  cross apply
  (
    select top 1
      RELATIONSHIP.RECIPROCALCONSTITUENTID,
      E.EMAILADDRESS
    from dbo.MAILPREFERENCEORGCONTACTTYPE APO
    inner join dbo.RELATIONSHIP on APO.CONTACTTYPECODEID = RELATIONSHIP.CONTACTTYPECODEID or (APO.USEPRIMARYCONTACT = 1 and RELATIONSHIP.ISPRIMARYCONTACT = 1)
    inner join dbo.CONTACTTYPECODE CTC on CTC.ID = RELATIONSHIP.CONTACTTYPECODEID
    inner join dbo.EMAILADDRESS E on RELATIONSHIP.ID = E.RELATIONSHIPID
    where APO.MAILPREFERENCEID = MP.ID
    and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = MP.CONSTITUENTID
    and RELATIONSHIP.ISCONTACT = 1
    and E.DONOTEMAIL = 0
    order by APO.USEPRIMARYCONTACT desc, CTC.[DESCRIPTION] asc
  ) MPO
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 1
  and MP.MAILTYPECODE = @MAILTYPE
  and @MAILTYPE = 5

  union all

  -- grab group member addresses

  select
    MP.CONSTITUENTID,
    E.EMAILADDRESS,
    null as CONTACTID
  from dbo.MAILPREFERENCEGROUPCONTACT GC
  inner join dbo.MAILPREFERENCE MP on GC.MAILPREFERENCEID = MP.ID
  inner join dbo.CONSTITUENT C on MP.CONSTITUENTID = C.ID
  inner join dbo.GROUPMEMBER GM on (GM.GROUPID=C.ID and (GC.CONSTITUENTID=GM.MEMBERID or (GC.USEPRIMARYCONTACT=1 and GM.ISPRIMARY=1)))
  inner join dbo.EMAILADDRESS E on (GM.MEMBERID = E.CONSTITUENTID and (GC.EMAILADDRESSID = E.ID or (GC.USEPRIMARYEMAIL = 1 and E.ISPRIMARY = 1)))
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 1
  and MP.MAILTYPECODE = @MAILTYPE
  and E.DONOTEMAIL = 0
  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
  (@MAILTYPE <> 3 or
    (@MAILTYPE = 3 and
      (
        (
          MP.CORRESPONDENCECODEID is null and
          not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and CONSTITUENTID = MP.CONSTITUENTID)
        )
        or
        (
          MP.CORRESPONDENCECODEID = (select CORRESPONDENCECODEID from dbo.CORRESPONDENCEPROCESS where ID = @PARAMETERSETID) and
          not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID)
        )
      )
    )
  )
);