USP_SEARCHLIST_REVENUEBATCHDUPLICATECONSTITUENT

CREATE procedure dbo.USP_SEARCHLIST_REVENUEBATCHDUPLICATECONSTITUENT
(
        @CONSTITUENTID uniqueidentifier = null,
        @CONSTITUENTLOOKUPID uniqueidentifier = null,
        @NEWCONSTITUENT xml = null,
        @MAXROWS smallint = 500,
        @OVERALLMATCHTHRESHOLD decimal(20, 4) = null,
        @AUTOMATCHTHRESHOLD decimal(20, 4) = null,
        @EDITCONSTITUENTCONTEXT nvarchar(110) = null,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @BATCHID uniqueidentifier = null,
        @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null
)
as
set nocount on

declare @NAME nvarchar(100),
    @KEYNAME nvarchar(100),
    @FIRSTNAME nvarchar(50),
    @POSTCODE nvarchar(12),
    @ADDRESSBLOCK nvarchar(150),
    @ISORGANIZATION bit,
    @ISGROUP bit,
    @TITLECODEID uniqueidentifier,
    @MIDDLENAME nvarchar(50),
    @SUFFIXCODEID uniqueidentifier,
    @COUNTRYID uniqueidentifier,
    @EMAILADDRESS nvarchar(100),
    @PHONENUMBER nvarchar(100),
    @ADDRESSTYPECODEID uniqueidentifier,
    @PHONETYPECODEID uniqueidentifier,
    @EMAILTYPECODEID uniqueidentifier

if (@CONSTITUENTID is null and @CONSTITUENTLOOKUPID is null)
begin
        select
                @NAME = LASTNAME,
                @KEYNAME = LASTNAME,
                @FIRSTNAME = FIRSTNAME,
                @POSTCODE = ADDRESS_POSTCODE,
                @ADDRESSBLOCK = ADDRESS_ADDRESSBLOCK,
                @ISORGANIZATION = ISORGANIZATION,
                @ISGROUP = ISGROUP,
                @TITLECODEID = TITLECODEID,
                @MIDDLENAME = MIDDLENAME,
                @SUFFIXCODEID = SUFFIXCODEID,
                @COUNTRYID = ADDRESS_COUNTRYID,
                @EMAILADDRESS = EMAILADDRESS_EMAILADDRESS,
                @PHONENUMBER = PHONE_NUMBER,
                @ADDRESSTYPECODEID = ADDRESS_ADDRESSTYPECODEID,
                @PHONETYPECODEID = PHONE_PHONETYPECODEID,
                @EMAILTYPECODEID = EMAILADDRESS_EMAILADDRESSTYPECODEID
        from dbo.UFN_REVENUEBATCH_GETNEWCONSTITUENT_FROMITEMLISTXML(@NEWCONSTITUENT)
end

if @KEYNAME is null
begin                                   
    if exists (select ID from dbo.BATCHREVENUECONSTITUENT where ID = @CONSTITUENTID)
    begin
            select
                @KEYNAME = KEYNAME,
                @NAME = NAME,
                @FIRSTNAME = FIRSTNAME,
                @POSTCODE = POSTCODE,
                @ADDRESSBLOCK = ADDRESSBLOCK,
                @ISORGANIZATION = ISORGANIZATION,
                @ISGROUP = ISGROUP,
                @TITLECODEID = TITLECODEID,
                @MIDDLENAME = MIDDLENAME,
                @SUFFIXCODEID = SUFFIXCODEID,
                @COUNTRYID = COUNTRYID,
                @EMAILADDRESS = EMAILADDRESS,
                @PHONENUMBER = NUMBER,
                @ADDRESSTYPECODEID = ADDRESSTYPECODEID,
                @PHONETYPECODEID = PHONETYPECODEID,
                @EMAILTYPECODEID = EMAILADDRESSTYPECODEID
            from dbo.BATCHREVENUECONSTITUENT
            where ID = @CONSTITUENTID
    end
end

declare @CANDIDATE table
(
        CONSTITUENTID uniqueidentifier,
        ADDRESSID uniqueidentifier,
        EMAILADDRESSID uniqueidentifier,
        PHONEID uniqueidentifier,
        MATCHPERCENTAGE numeric(5, 2),
        STREETNUMBERRESULT tinyint,
        STREETNAMERESULT tinyint,
        POSTCODERESULT tinyint        
)

if @OVERALLMATCHTHRESHOLD is null and @BATCHID is not null
begin
    select @OVERALLMATCHTHRESHOLD = OVERALLMATCHTHRESHOLD, @AUTOMATCHTHRESHOLD = AUTOMATCHTHRESHOLD from dbo.BATCH where ID = @BATCHID
end

if @KEYNAME is not null
    insert into @CANDIDATE (CONSTITUENTID, ADDRESSID, EMAILADDRESSID, PHONEID, MATCHPERCENTAGE, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT)
    select CONSTITUENTID, ADDRESSID, EMAILADDRESSID, PHONEID, MATCHPERCENTAGE, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT
    from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3 (
                @TITLECODEID,
                @FIRSTNAME,
                @MIDDLENAME,
                @KEYNAME,
                @SUFFIXCODEID,
                @ADDRESSBLOCK,
                @POSTCODE,
                @COUNTRYID,
                @ISORGANIZATION,
                @ISGROUP,
                null,
                null,
                @EMAILADDRESS,
                @PHONENUMBER,
                @OVERALLMATCHTHRESHOLD,
                @AUTOMATCHTHRESHOLD,
                @CURRENTAPPUSERID,
                @CONSTITUENTDUPLICATEMATCHINGENABLED
                            ) DUPLICATES
else if @EDITCONSTITUENTCONTEXT is not null
begin
    declare @BATCHCONSTITUENTUPDATEID uniqueidentifier = convert(uniqueidentifier, substring(@EDITCONSTITUENTCONTEXT,75,36))
    declare @PRIMARYRECORDID uniqueidentifier = convert(uniqueidentifier, substring(@EDITCONSTITUENTCONTEXT,1,36))

    if exists(select DOMANUALREVIEWFORAUTOMATCH from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHCONSTITUENTUPDATEID  and DOMANUALREVIEWFORAUTOMATCH = 1)
    begin
        -- if none of the addresses generated an exception we need to find a possible match for it

        if not exists (select 'x' from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID and ISMANUALEXCEPTION = 1)
        begin 
                    select top 1
                            @POSTCODE = POSTCODE,
                            @ADDRESSBLOCK = ADDRESSBLOCK,
                            @COUNTRYID = COUNTRYID
                    from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
                    order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE   

                    declare @ADDRESSMATCHTHRESHOLD decimal(20,4);

                    select top 1
                        @ADDRESSMATCHTHRESHOLD = ADDRESSMATCHTHRESHOLD
                    from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS;

                    insert into @CANDIDATE (CONSTITUENTID, ADDRESSID, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT)
                    select
                        @PRIMARYRECORDID,
                        DUPLICATES.ADDRESSID,                      
                        STREETNUMBERRESULT, 
                        STREETNAMERESULT, 
                        POSTCODERESULT
                    from dbo.UFN_FINDADDRESSMATCH_CONSTITUENTUPDATEBATCH(@PRIMARYRECORDID, @ADDRESSBLOCK, @POSTCODE, @COUNTRYID, '', null, null, @ADDRESSMATCHTHRESHOLD, null) DUPLICATES                        
            end
            else -- if there where addresses that generate an exception select the one that was similar, otherwise do nothing.

            begin
                insert into @CANDIDATE(CONSTITUENTID, ADDRESSID) 
                select top 1 @PRIMARYRECORDID, MANUALEXCEPTIONSIMILARADDRESSID
                from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID and ISMANUALEXCEPTION = 1
                order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE; 
            end

        --make sure we always return the auto-matched constituent id

        if not exists(select CONSTITUENTID from @CANDIDATE)
                insert into @CANDIDATE(CONSTITUENTID) values(@PRIMARYRECORDID);                  
    end                              
end

declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @APPUSER_IN_NONRACROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
declare @APPUSER_IN_NOSECGROUPROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
declare @NULLVALUE uniqueidentifier = (select cast(cast(0 as binary) as uniqueidentifier))

select top(@MAXROWS)
        C.ID,
        D.MATCHPERCENTAGE,
        C.NAME,
        A.ADDRESSBLOCK,
        A.CITY,
        S.ABBREVIATION,
        A.POSTCODE,
        C.LOOKUPID,
        C.DATECHANGED,
        A.ID ADDRESSID,
        D.EMAILADDRESSID,
        D.PHONEID,
        D.STREETNUMBERRESULT,
        D.STREETNAMERESULT,
        D.POSTCODERESULT,
        case when ADDRESSID is not null then dbo.UFN_BUILDFULLADDRESS(ADDRESSID,a.ADDRESSBLOCK,a.CITY,a.STATEID,a.POSTCODE,a.COUNTRYID) else dbo.UFN_CONSTITUENTDUPLICATESEARCH_DEFAULTADDRESS(C.ID,@ADDRESSTYPECODEID) end as FULLADDRESS     
from @CANDIDATE D
inner join dbo.CONSTITUENT C on D.CONSTITUENTID = C.ID
left join dbo.ADDRESS A on D.ADDRESSID = A.ID
left join dbo.STATE S on A.STATEID = S.ID
left join dbo.PHONE P on D.PHONEID = P.ID
left join dbo.EMAILADDRESS E on D.EMAILADDRESSID = E.ID
where (@ISADMIN = 1 or @CURRENTAPPUSERID is null or @APPUSER_IN_NONRACROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, D.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)                           
order by    
        D.MATCHPERCENTAGE desc, 
        C.KEYNAME, 
        C.FIRSTNAME,
        case when isnull(A.ADDRESSTYPECODEID,@NULLVALUE) = isnull(@ADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
        case when isnull(P.PHONETYPECODEID,@NULLVALUE) = isnull(@PHONETYPECODEID,@NULLVALUE) then 1 else 2 end, 
        case when isnull(E.EMAILADDRESSTYPECODEID,@NULLVALUE) = isnull(@EMAILTYPECODEID,@NULLVALUE) then 1 else 2 end,
        case when isnull(A.ADDRESSBLOCK,'') = isnull(@ADDRESSBLOCK,'') then 1 else 2 end,
        A.ISPRIMARY desc,
        P.ISPRIMARY desc,
        E.ISPRIMARY desc