UFN_ADDRESSPROCESS_MAILPREFERENCES

CREATE function dbo.[UFN_ADDRESSPROCESS_MAILPREFERENCES]
(
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier,
  @DATE datetime
)
returns table
as
return
(
  select
    MP.CONSTITUENTID,
    isnull(SA.ADDRESSID, A.ID) as ADDRESSID,
    NULL AS CONTACTID,
    NULL as POSITION,
    NULL as GROUPCONTACTID
  from dbo.MAILPREFERENCE MP
  inner join dbo.CONSTITUENT C on MP.CONSTITUENTID = C.ID
  left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on MP.CONSTITUENTID = SA.CONSTITUENTID and MP.USESEASONALADDRESS = 1
  inner join (select
                ADDRESS.ID,
                ADDRESS.DONOTMAIL,
                MAILPREFERENCE.ID as MAILPREFERENCEID
              from dbo.MAILPREFERENCE
              inner join dbo.ADDRESS on MAILPREFERENCE.ADDRESSID = ADDRESS.ID
              where MAILPREFERENCE.USEPRIMARYADDRESS = 0

              union all

              select
                ADDRESS.ID,
                ADDRESS.DONOTMAIL,
                MAILPREFERENCE.ID as MAILPREFERENCEID
              from dbo.MAILPREFERENCE
              inner join dbo.ADDRESS on MAILPREFERENCE.CONSTITUENTID = ADDRESS.CONSTITUENTID
              where MAILPREFERENCE.USEPRIMARYADDRESS = 1 and ADDRESS.ISPRIMARY = 1
             ) as A on MP.ID = A.MAILPREFERENCEID
  where MP.SENDMAIL = 1
  and
  (
    (C.ISORGANIZATION = 0 and C.ISGROUP = 0 and (select INDUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)
    or (C.ISORGANIZATION = 1 and (select ORGUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)
    or (C.ISGROUP = 1 and (select GROUPUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)
  )
  and MP.DELIVERYMETHODCODE = 0
  and A.DONOTMAIL = 0
  and MP.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 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 ID 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 ID 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 ID from dbo.MAILPREFERENCEORGCONTACTTYPE where MAILPREFERENCEID = MP.ID)

  union all

  --grab contact addresses

  select
    MP.CONSTITUENTID,
    isnull(CA.ID,OA.ID) as ADDRESSID,
    R.RECIPROCALCONSTITUENTID as CONTACTID,
    isnull((select top 1 JOBTITLE from dbo.RELATIONSHIPJOBINFO where RELATIONSHIPSETID = R.RELATIONSHIPSETID order by STARTDATE desc), '') as POSITION,
    NULL as GROUPCONTACTID
  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.ADDRESS OA on ORG.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTMAIL = 0
  left join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = ORG.ID
  left join dbo.ADDRESS CA on R.ID = CA.RELATIONSHIPID
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 0
  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 ID 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 ID 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)
  )
  and CA.DONOTMAIL = 0
  and ((select ORGUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)

  union all

  --For receipts, only grab the first contact found

  select
    MP.CONSTITUENTID,
    isnull(MPO.ADDRESSID,OA.ID) as ADDRESSID,
    MPO.RECIPROCALCONSTITUENTID as CONTACTID,
    MPO.POSITION as POSITION,
    NULL as GROUPCONTACTID
  from dbo.CONSTITUENT ORG
  inner join dbo.MAILPREFERENCE MP on MP.CONSTITUENTID = ORG.ID
  left join dbo.ADDRESS OA on ORG.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1 and OA.DONOTMAIL = 0
  left join dbo.STATE OS on OA.STATEID = OS.ID
  left join dbo.COUNTRY OC on OA.COUNTRYID = OC.ID
  cross apply (
    select TOP 1
      RELATIONSHIP.ID, 
      RELATIONSHIP.RECIPROCALCONSTITUENTID,
      isnull((select top 1 JOBTITLE from dbo.RELATIONSHIPJOBINFO where RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID order by STARTDATE desc), '') as POSITION,
      A.ID as ADDRESSID
    from dbo.MAILPREFERENCEORGCONTACTTYPE APO
    inner join dbo.CONTACTTYPECODE CTC on APO.CONTACTTYPECODEID = CTC.ID or APO.USEPRIMARYCONTACT = 1
    left join dbo.RELATIONSHIP on APO.CONTACTTYPECODEID = RELATIONSHIP.CONTACTTYPECODEID or (APO.USEPRIMARYCONTACT = 1 and RELATIONSHIP.ISPRIMARYCONTACT = 1)
    left join dbo.ADDRESS A on RELATIONSHIP.ID = A.RELATIONSHIPID 
    where APO.MAILPREFERENCEID = MP.ID 
    and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = ORG.ID 
    and RELATIONSHIP.ISCONTACT = 1
    and A.DONOTMAIL = 0
    order by APO.USEPRIMARYCONTACT desc, CTC.DESCRIPTION asc
  ) MPO
  where MP.SENDMAIL = 1
  and MP.DELIVERYMETHODCODE = 0
  and MP.MAILTYPECODE = @MAILTYPE
  and @MAILTYPE = 5
  --For receipts, only return the first contact

  and ((select ORGUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)

  union all

  -- grab group member addresses

  select
    MP.CONSTITUENTID,
    isnull(SA.ADDRESSID,A.ID) as ADDRESSID,
    NULL AS CONTACTID,
    NULL as POSITION,
    GM.MEMBERID as GROUPCONTACTID
  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)))
  left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on GM.MEMBERID = SA.CONSTITUENTID and MP.USESEASONALADDRESS = 1
  inner join dbo.ADDRESS A on (GM.MEMBERID = A.CONSTITUENTID and (A.ID = GC.ADDRESSID or (GC.USEPRIMARYADDRESS = 1 and A.ISPRIMARY = 1)))
  where MP.SENDMAIL = 1
  and (C.ISGROUP = 1 and (select GROUPUSECONSTITUENTPREFS from dbo.ADDRESSPROCESSINGOPTION where ID = @ADDRESSPROCESSINGOPTIONID) = 1)
  and MP.DELIVERYMETHODCODE = 0
  and A.DONOTMAIL = 0
  and MP.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
   (@MAILTYPE <> 3 or
    (@MAILTYPE = 3 and
      (
        (MP.CORRESPONDENCECODEID is null and not exists(select ID 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 ID from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID))
      )
    )
  )
);