USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETCONSTITUENTS

CREATE procedure dbo.USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETCONSTITUENTS  
(  
 @CONTEXTID uniqueidentifier,  
 @CONSTITUENTSOUNDEX nvarchar(4),  
 @REQUIRESECONDARYMATCH bit,  
 @INCLUDEGROUPS bit,  
 @INCLUDEORGANIZATIONS bit,  
 @INCLUDEINACTIVE bit,  
 @INCLUDEDECEASED bit,  
 @CHECKMIDDLENAME bit,  
 @CHECKMAIDENNAME bit,  
 @ISINCREMENTAL bit,  
 @LASTRUNON datetime = null,
 @POSTCODEPREFIXLENGTH tinyint = 12
)  
as  
begin  
if OBJECT_ID('tempdb..#TMP_CONSTITUENTS') is not null 
    drop table #TMP_CONSTITUENTS

create table #TMP_CONSTITUENTS (
    ID uniqueidentifier primary key
    )

insert into #TMP_CONSTITUENTS (ID)
 select  
  CONSTITUENT.ID as ID  
 from dbo.CONSTITUENT with (nolock)  
  left join dbo.DECEASEDCONSTITUENT (nolock) on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID  
 where  
  (  (CONSTITUENT.ISGROUP = 0 and CONSTITUENT.KEYNAMESOUNDEX = @CONSTITUENTSOUNDEX)
  ) and  
  (@INCLUDEINACTIVE = 1 or CONSTITUENT.ISINACTIVE = 0) and  
  (@INCLUDEDECEASED = 1 or DECEASEDCONSTITUENT.ID is null) and  
  (@INCLUDEORGANIZATIONS = 1 or CONSTITUENT.ISORGANIZATION = 0) 

 if @INCLUDEGROUPS = 1 
    begin
        insert into #TMP_CONSTITUENTS (ID)
            select GROUPMEMBER.GROUPID from dbo.CONSTITUENT CONSTITUENTMEMBER 
                inner join #TMP_CONSTITUENTS on #TMP_CONSTITUENTS.ID = CONSTITUENTMEMBER.ID
                inner join dbo.GROUPMEMBER on CONSTITUENTMEMBER.ID = GROUPMEMBER.MEMBERID and GROUPMEMBER.ISPRIMARY = 1
                inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                where GROUPDATA.GROUPTYPECODE = 0

        insert into #TMP_CONSTITUENTS (ID)
            select GROUPDATA.ID from dbo.GROUPDATA
                inner join dbo.CONSTITUENT on GROUPDATA.ID = CONSTITUENT.ID
            where GROUPDATA.GROUPTYPECODE <> 0 and CONSTITUENT.KEYNAMESOUNDEX = @CONSTITUENTSOUNDEX
            and (@INCLUDEINACTIVE = 1 or CONSTITUENT.ISINACTIVE = 0) 
    end

if @CHECKMAIDENNAME = 1
    insert into #TMP_CONSTITUENTS (ID)
        select CONSTITUENT.ID from dbo.CONSTITUENT   
        left join dbo.DECEASEDCONSTITUENT (nolock) on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID  
        where CONSTITUENT.MAIDENNAME <> '' and soundex(CONSTITUENT.MAIDENNAME) = @CONSTITUENTSOUNDEX  
         and  
         CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0 and
          (@INCLUDEINACTIVE = 1 or CONSTITUENT.ISINACTIVE = 0) and  
          (@INCLUDEDECEASED = 1 or DECEASEDCONSTITUENT.ID is null)
          and not exists (select 1 from #TMP_CONSTITUENTS where #TMP_CONSTITUENTS.ID = CONSTITUENT.ID)


 if @ISINCREMENTAL = 1 and @LASTRUNON is null  
  set @ISINCREMENTAL = 0;  

 select  
  CONSTITUENT.ID as ID,  
  case CONSTITUENT.ISORGANIZATION   
   when 1 then CONSTITUENT.NAME  
   else  
    case CONSTITUENT.ISGROUP  
     when 1 then CONSTITUENT.NAME  
     else  
      case CONSTITUENT.FIRSTNAME  
       when '' then ''  
       else CONSTITUENT.FIRSTNAME + ' '  
      end +  
      case CONSTITUENT.MIDDLENAME  
       when '' then ''  
       else  
        case @CHECKMIDDLENAME  
         when 1 then CONSTITUENT.MIDDLENAME + ' '  
         else ''  
        end  
      end +  
      case CONSTITUENT.MAIDENNAME  
       when '' then ''  
       else  
        case @CHECKMAIDENNAME  
         when 1 then CONSTITUENT.MAIDENNAME + ' '  
         else ''  
        end  
      end +  
      CONSTITUENT.KEYNAME  
    end  
  end + ' ' + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(CONSTITUENT.SUFFIXCODEID) as FULLNAME,  
  ADDRESS.ID as ADDRESSID,  
  case  
   when ADDRESS.ID is not null then   
    substring(dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, left(ADDRESS.POSTCODE, @POSTCODEPREFIXLENGTH), ADDRESS.COUNTRYID), 0, 150)  
   else ''  
  end as FULLADDRESS,  
  ADDRESS.STATEID as STATEID,  
  ADDRESS.POSTCODE as POSTCODE,  
  ADDRESS.COUNTRYID as COUNTRYID,  
  PHONE.NUMBERNOFORMAT as PHONENUMBER,  
  EMAILADDRESS.EMAILADDRESS as EMAILADDRESS,  
  CONSTITUENT.BIRTHDATE as BIRTHDATE,  
CONSTITUENT.GENDERCODE as GENDERCODE,  
  CONSTITUENT.KEYNAMESOUNDEX as KEYNAMESOUNDEX,  
  CONSTITUENT.ISGROUP as ISGROUP,  
  CONSTITUENT.ISORGANIZATION as ISORGANIZATION,  
  case   
   when @ISINCREMENTAL = 0 then 1  
   else  
    case  
     when CONSTITUENT.DATEADDED > @LASTRUNON or CONSTITUENT.DATECHANGED > @LASTRUNON then 1  
     when PHONE.ID is not null and   
      (  
       PHONE.DATEADDED > @LASTRUNON or  
       PHONE.DATECHANGED > @LASTRUNON  
      ) then 1  
     when ADDRESS.ID is not null and   
      (  
       ADDRESS.DATEADDED > @LASTRUNON or  
       ADDRESS.DATECHANGED > @LASTRUNON  
      ) then 1  
     when EMAILADDRESS.ID is not null and   
      (  
       EMAILADDRESS.DATEADDED > @LASTRUNON or  
       EMAILADDRESS.DATECHANGED > @LASTRUNON  
      ) then 1  
     else 0  
    end  
  end as CHECKFORMATCH,
  CONSTITUENT.GENDERCODEID as GENDERCODEID
 from dbo.CONSTITUENT with (nolock)  
 inner join #TMP_CONSTITUENTS on #TMP_CONSTITUENTS.ID = CONSTITUENT.ID
  left join dbo.ADDRESS with (nolock) on  
  ADDRESS.CONSTITUENTID = CONSTITUENT.ID and  
  ADDRESS.ISPRIMARY = 1  
 left join dbo.PHONE (nolock) on  
  PHONE.CONSTITUENTID = CONSTITUENT.ID and  
  PHONE.ISPRIMARY = 1  

 left join dbo.EMAILADDRESS (nolock) on  
  EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and  
  EMAILADDRESS.ISPRIMARY = 1  

 where  
  (  
   @REQUIRESECONDARYMATCH = 0 or  
   (ADDRESS.ID is not null or PHONE.ID is not null or EMAILADDRESS.ID is not null)  
  )  
 order by CHECKFORMATCH desc  

end