UFN_ADDRESSPROCESS_ADDRESSES_2

CREATE function dbo.UFN_ADDRESSPROCESS_ADDRESSES_2
    (  
    @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.ID as CONSTITUENTID,  
       coalesce(MP.ADDRESSID,
       case when @INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0 then 
       (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and ADDRESS.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(ADDRESS.ENDDATE as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 order by ADDRESS.SEQUENCE)
        else null end
       ,CA.ADDRESSID,
       case when C.ISORGANIZATION = 0 AND C.ISGROUP = 0 then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @INDALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @INDALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) 
       else null end
        ,case when c.ISORGANIZATION = 0 AND C.ISGROUP = 0 then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @INDALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @INDALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) 
        else null end
        ,case when c.ISORGANIZATION = 1 and (@ORGMAILINGPREFERENCE = 1) then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @ORGALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @ORGALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) 
        else null end        
        ,case when c.ISORGANIZATION = 1 and (@ORGMAILINGPREFERENCE = 1) then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @ORGALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @ORGALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end
        ,case when C.ISGROUP = 1 then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @GROUPALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @GROUPALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end
        ,case when C.ISGROUP = 1 then 
        (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @GROUPALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @GROUPALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE)
        else null end
        ) 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(@DATE, @INCLUDEHOUSEHOLDPROCESSING) C       
     --left join dbo.UFN_ADDRESSPROCESS_MAILPREFERENCES_bez(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@DATE,@INDUSECONSTITUENTPREFS, @ORGUSECONSTITUENTPREFS, @GROUPUSECONSTITUENTPREFS) as MP on C.ID = MP.CONSTITUENTID       

     left join dbo.UFN_ADDRESSPROCESS_MAILPREFERENCES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@DATE) as MP on C.ID = MP.CONSTITUENTID
     --left join dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES_bez(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@ORGINCLUDEWITHNOCONTACT,@ORGUSECONSTITUENTPREFS,@ORGSENDTOALLCONTACTS) CA on C.ID = CA.CONSTITUENTID and (@ORGMAILINGPREFERENCE = 0)

     left join dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID) CA on C.ID = CA.CONSTITUENTID and (@ORGMAILINGPREFERENCE = 0)

     where   
      (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,
      case when c.ISORGANIZATION = 1 and (@ORGMAILINGPREFERENCE = 1) then (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @ORGALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @ORGALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end
      ,case when c.ISORGANIZATION = 1 and (@ORGMAILINGPREFERENCE = 1)  then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @ORGALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @ORGALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE)
      else null end
      ) is not null) or  
      @ORGINCLUDEWITHNOADDRESS = 1))  
      or  
      (C.ISORGANIZATION = 0 and C.ISGROUP = 0 and  
      ((@INDINCLUDEWITHNOADDRESS = 0 and coalesce(MP.ADDRESSID,
      case when @INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0 then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and ADDRESS.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(ADDRESS.ENDDATE as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 order by ADDRESS.SEQUENCE)
      else null end,
      case when C.ISORGANIZATION = 0 AND C.ISGROUP = 0 then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @INDALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @INDALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end,
      case when c.ISORGANIZATION = 0 AND C.ISGROUP = 0 then (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @INDALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @INDALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end) is not null) or  
      @INDINCLUDEWITHNOADDRESS = 1))  
      or  
      (C.ISGROUP = 1 and  
      ((@GROUPINCLUDEWITHNOADDRESS = 0 and coalesce(MP.ADDRESSID,
      case when @INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0 then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and ADDRESS.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(ADDRESS.ENDDATE as int) - cast(ADDRESS.STARTDATE as int)) + 1231) % 1231 order by ADDRESS.SEQUENCE)
      else null end,
      case when C.ISGROUP = 1 then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @GROUPALTADDRESS1TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @GROUPALTADDRESS1ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end
      ,case when C.ISGROUP = 1 then 
      (select top 1 ADDRESS.ID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = C.ID and ADDRESS.DONOTMAIL = 0 and (ADDRESS.ADDRESSTYPECODEID = @GROUPALTADDRESS2TYPECODEID or (ADDRESS.ISPRIMARY = 1 and @GROUPALTADDRESS2ISPRIMARY = 1)) order by ADDRESS.SEQUENCE) else null end
      ) 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

      )  
    )