UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT

CREATE function dbo.UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT
    (@CONSTITUENTID uniqueidentifier,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier,    
    @MAILTYPE tinyint,    
    @PARAMETERSETID uniqueidentifier,    
    @DATE datetime,        
    @INCLUDEHOUSEHOLDPROCESSING bit,  
 @INDUSESEASONALADDRESS bit,  
 @ORGMAILINGPREFERENCE tinyint,  
 @INDALTADDRESS1TYPECODEID uniqueidentifier,  
 @INDALTADDRESS1ISPRIMARY bit,  
 @INDALTADDRESS2TYPECODEID uniqueidentifier,  
 @INDALTADDRESS2ISPRIMARY bit,  
 @ORGALTADDRESS1TYPECODEID uniqueidentifier,  
 @ORGALTADDRESS1ISPRIMARY bit,  
 @ORGALTADDRESS2TYPECODEID uniqueidentifier,  
 @ORGALTADDRESS2ISPRIMARY bit,  
 @INDINCLUDEWITHNOADDRESS bit,  
 @ORGINCLUDEWITHNOADDRESS bit,  
 @ORGINCLUDEWITHNOCONTACT bit,  
 @ORGSENDTOALLCONTACTS bit,  
 @INDUSECONSTITUENTPREFS bit,  
 @ORGUSECONSTITUENTPREFS bit,  
 @GROUPALTADDRESS1TYPECODEID uniqueidentifier,  
 @GROUPALTADDRESS1ISPRIMARY bit,  
 @GROUPALTADDRESS2TYPECODEID uniqueidentifier,  
 @GROUPALTADDRESS2ISPRIMARY bit,  
 @GROUPINCLUDEWITHNOADDRESS bit,  
 @GROUPUSECONSTITUENTPREFS tinyint     
    )    
   returns table    
   as    
   return    
    (       
     select distinct C.CONSTITUENTID as CONSTITUENTID,    
       coalesce(MP.ADDRESSID,SA.ADDRESSID,CA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID, G1.ADDRESSID, G2.ADDRESSID) as ADDRESSID,    
       coalesce(MP.CONTACTID,CA.CONTACTID) as CONTACTID,    
       coalesce(MP.POSITION,CA.POSITION) as POSITION,    
       C.HOUSEHOLDID,    
       C.RETURNEDASHOUSEHOLDMEMBER,    
       case    
        when (C.RETURNEDASHOUSEHOLDMEMBER = 1) then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)    
        when (C.ISGROUP = 1 and C.HOUSEHOLDID is not null) then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)    
        else null    
       end as GROUPCONTACTID
     from dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT(@CONSTITUENTID, @DATE, @INCLUDEHOUSEHOLDPROCESSING) C    
     left join dbo.UFN_ADDRESSPROCESS_MAILPREFERENCES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@DATE) as MP on C.ID = MP.CONSTITUENTID    
     left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on C.ID = SA.CONSTITUENTID and @INDUSESEASONALADDRESS = 1    
     left join dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID) CA on C.ID = CA.CONSTITUENTID and (@ORGMAILINGPREFERENCE = 0)     
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@INDALTADDRESS1TYPECODEID,@INDALTADDRESS1ISPRIMARY) as A1 on C.ID = A1.CONSTITUENTID AND C.ISORGANIZATION = 0 AND C.ISGROUP = 0    
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@INDALTADDRESS2TYPECODEID,@INDALTADDRESS2ISPRIMARY) as a2 on C.ID = a2.constituentid AND c.isorganization = 0 AND C.ISGROUP = 0    
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@ORGALTADDRESS1TYPECODEID,@ORGALTADDRESS1ISPRIMARY) as O1 on C.ID = O1.constituentid AND c.isorganization = 1 and (@ORGMAILINGPREFERENCE = 1)    
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@ORGALTADDRESS2TYPECODEID,@ORGALTADDRESS2ISPRIMARY) as O2 on C.ID = O2.constituentid AND c.isorganization = 1 and (@ORGMAILINGPREFERENCE = 1)    
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@GROUPALTADDRESS1TYPECODEID,@GROUPALTADDRESS1ISPRIMARY) as G1 on C.ID = G1.CONSTITUENTID AND C.ISGROUP = 1    
     left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@GROUPALTADDRESS2TYPECODEID,@GROUPALTADDRESS2ISPRIMARY ) as G2 on C.ID = G2.CONSTITUENTID AND C.ISGROUP = 1    

     where  
        C.CONSTITUENTID = @CONSTITUENTID and    
      (C.ISORGANIZATION = 1 and    
      (@ORGMAILINGPREFERENCE = 0 and --if a valid mail pref is found, keep the record despite contact option    

      ((@ORGINCLUDEWITHNOCONTACT = 0 and (MP.ADDRESSID is not null or CA.CONTACTID is not null)) or    
      @ORGINCLUDEWITHNOCONTACT = 1)) or    
      (@ORGMAILINGPREFERENCE = 1 and    
      ((@ORGINCLUDEWITHNOADDRESS = 0 and coalesce(MP.ADDRESSID,CA.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID) is not null) or    
      @ORGINCLUDEWITHNOADDRESS = 1))    
      or    
      (C.ISORGANIZATION = 0 and C.ISGROUP = 0 and    
      ((@INDINCLUDEWITHNOADDRESS = 0 and coalesce(MP.ADDRESSID,SA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID) is not null) or    
      @INDINCLUDEWITHNOADDRESS = 1))    
      or    
      (C.ISGROUP = 1 and    
      ((@GROUPINCLUDEWITHNOADDRESS = 0 and coalesce(MP.ADDRESSID,SA.ADDRESSID,G1.ADDRESSID,G2.ADDRESSID) is not null) or    
      @GROUPINCLUDEWITHNOADDRESS = 1)))    
      and     
      (
        coalesce((
            select SUPPRESSMAILING from 
            (
                select top 1
                    case 
                        when (SENDMAIL = 0 
                                or (SENDMAIL = 1 and DELIVERYMETHODCODE = 1 
                                    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    when @MAILTYPE = 0 and ACKNOWLEDGEMENTID is not null then 1 -- Revenue acknowledgements

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

                            when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null) then 2 -- Appeals

                            when @MAILTYPE = 1 and (SITEID is not null and CATEGORYCODEID is not null) then 2 -- Appeals

                            when @MAILTYPE = 1 and (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 2 -- Appeals

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

                            when @MAILTYPE = 2 and EVENTCATEGORYCODEID is not null and SITEID is not null then 2 -- Events

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

                            when @MAILTYPE = 3 and CORRESPONDENCECODEID is not null and CORRESPONDENCEID is not null then 2 -- Correspondence

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

                            when @MAILTYPE = 4 and PLEDGEREMINDERID is not null then 1 -- Reminders

                            when @MAILTYPE = 8 and PURPOSEID is not null then 1 -- Stewardship

                            else 0
                    end as CRITERIAMATCHSCORE
                from 
                    dbo.MAILPREFERENCE
                where 
                    CONSTITUENTID = C.ID and
                    MAILTYPECODE = @MAILTYPE and
                    (
                        (C.ISORGANIZATION = 0 and C.ISGROUP = 0 and @INDUSECONSTITUENTPREFS = 1) or
                        (C.ISORGANIZATION = 1 and @ORGUSECONSTITUENTPREFS = 1) or
                        (C.ISGROUP = 1 and @GROUPUSECONSTITUENTPREFS = 1)
                    ) and        
                    (
                        (@PARAMETERSETID = case @MAILTYPE
                                        when 0 then ACKNOWLEDGEMENTID
                                        when 3 then CORRESPONDENCEID
                                        when 4 then PLEDGEREMINDERID
                                        else null
                                        end) or 
                        (case @MAILTYPE    when 0 then ACKNOWLEDGEMENTID
                                    when 3 then CORRESPONDENCEID
                                    when 4 then PLEDGEREMINDERID
                                    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 -- Verify that the comm prefs allow the user to receive mailings

      )
    )