USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATELOOKUPID

CREATE procedure dbo.USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATELOOKUPID
(
    @PRIMARYRECORDID uniqueidentifier = null,
    @LOOKUP_ID nvarchar(100) = null,
    @KEYNAME nvarchar(100) = null,
    @FIRSTNAME nvarchar(50) = null,
    @ADDRESSES xml = null,
    @CONSTITUENTTYPECODE tinyint = null,
    @MAXROWS smallint = 500,
    @OVERALLMATCHTHRESHOLD decimal(20, 4) = null,
    @DUPLICATERECORDID uniqueidentifier = null,
    @TITLECODEID uniqueidentifier = null,
    @MIDDLENAME nvarchar(50) = '',
    @SUFFIXCODEID uniqueidentifier = null,
    @ALTERNATELOOKUPIDS xml = null,
    @PHONES xml = null,
    @EMAILADDRESSES xml = null,
    @AUTOMATCHTHRESHOLD decimal(20, 4) = null,
    @DOMANUALREVIEWFORAUTOMATCH bit = 0,
    @BATCHID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null
)
as
    set nocount on;

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

        declare @POSTCODE nvarchar(12), @ADDRESSBLOCK nvarchar(150), @COUNTRYID uniqueidentifier,
                            @PHONENUMBER nvarchar(100), @EMAILADDRESS nvarchar(100), @KEYNAMEBODY nvarchar(100), @ADDRESSTYPECODEID uniqueidentifier,
                            @PHONETYPECODEID uniqueidentifier, @EMAILTYPECODEID uniqueidentifier;

    if @PRIMARYRECORDID is null  
        begin

            select top 1
                @POSTCODE = POSTCODE,
                @ADDRESSBLOCK = ADDRESSBLOCK,
                @COUNTRYID = COUNTRYID,
                @ADDRESSTYPECODEID = ADDRESSTYPECODEID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_FROMITEMLISTXML(@ADDRESSES)
            order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE

            select top 1 @PHONENUMBER = NUMBER, @PHONETYPECODEID = PHONETYPECODEID
            from dbo.UFN_CONSTITUENT_GETPHONES_FORUPDATEBATCH_FROMITEMLISTXML(@PHONES)
            order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE

            select top 1 @EMAILADDRESS = EMAILADDRESS, @EMAILTYPECODEID = EMAILADDRESSTYPECODEID
            from dbo.UFN_CONSTITUENT_GETEMAILADDRESSES_WITHDATES_FORUPDATEBATCH_FROMITEMLISTXML(@EMAILADDRESSES)
            order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE

            if @CONSTITUENTTYPECODE = 1 -- orgs

            begin
                declare @KEYNAMEPREFIX nvarchar(50);

                begin try
                    exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAMEBODY output, @KEYNAMEPREFIX output;
                end try
                begin catch
                    set @KEYNAMEBODY = @KEYNAME;
                end catch
            end
            else
                set @KEYNAMEBODY = @KEYNAME;

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

            insert into @CANDIDATE (CONSTITUENTID, ADDRESSID, EMAILADDRESSID, PHONEID, MATCHPERCENTAGE, LOOKUPIDTYPE, LOOKUPID, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT)
            select
                DUPLICATES.CONSTITUENTID,
                DUPLICATES.ADDRESSID,
                DUPLICATES.EMAILADDRESSID,
                DUPLICATES.PHONEID,
                DUPLICATES.MATCHPERCENTAGE,
                'Lookup ID' LOOKUPIDTYPE,
                null, 
                STREETNUMBERRESULT, 
                STREETNAMERESULT, 
                POSTCODERESULT
            from
                dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3 (
                    @TITLECODEID,
                    @FIRSTNAME,
                    @MIDDLENAME,
                    @KEYNAMEBODY,
                    @SUFFIXCODEID,
                    @ADDRESSBLOCK,
                    @POSTCODE,
                    @COUNTRYID,
                    case @CONSTITUENTTYPECODE when 1 then 1 else 0 end,
                    case when @CONSTITUENTTYPECODE in(2,3) then 1 else 0 end,
                    @LOOKUP_ID,
                    @ALTERNATELOOKUPIDS,
                    @EMAILADDRESS,
                    @PHONENUMBER,
                    @OVERALLMATCHTHRESHOLD,
                    @AUTOMATCHTHRESHOLD,
                    @CURRENTAPPUSERID,
                    @CONSTITUENTDUPLICATEMATCHINGENABLED
                ) DUPLICATES
            end    
        else if @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 = @DUPLICATERECORDID and ISMANUALEXCEPTION = 1)
                    begin             
                        select top 1
                            @POSTCODE = POSTCODE,
                            @ADDRESSBLOCK = ADDRESSBLOCK,
                            @COUNTRYID = COUNTRYID
                        from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_FROMITEMLISTXML(@ADDRESSES)
                        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 = @DUPLICATERECORDID 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;

        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)  
            ID,
            MATCHPERCENTAGE,
            NAME,
            ADDRESSBLOCK,
            CITY,
            ABBREVIATION,
            POSTCODE,
            LOOKUPIDTYPE,
            LOOKUPID,
            DATECHANGED,
            ADDRESSID,
            EMAILADDRESSID,
            PHONEID,
            STREETNUMBERRESULT, 
            STREETNAMERESULT, 
            POSTCODERESULT  ,
            case when ADDRESSID is not null then dbo.UFN_BUILDFULLADDRESS(ADDRESSID,ADDRESSBLOCK,CITY,STATEID,POSTCODE,COUNTRYID) else dbo.UFN_CONSTITUENTDUPLICATESEARCH_DEFAULTADDRESS(ID,@ADDRESSTYPECODEID) end as FULLADDRESS     
        from 
                (select
                    C.ID,
                    D.MATCHPERCENTAGE,
                    C.NAME,
                    A.ADDRESSBLOCK,
                    A.CITY,
                    S.ABBREVIATION,
                    A.POSTCODE,
                    D.LOOKUPIDTYPE,
                    coalesce(D.LOOKUPID, C.LOOKUPID) as LOOKUPID,
                    C.DATECHANGED,
                    A.ID ADDRESSID,
                    D.EMAILADDRESSID,
                    D.PHONEID,
                    ROW_NUMBER() over(partition by D.CONSTITUENTID order by MATCHPERCENTAGE desc,
                                                                            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,
                                                                            case when A.ISPRIMARY = 1 then 1 else 2 end,
                                                                            case when P.ISPRIMARY = 1 then 1 else 2 end,
                                                                            case when E.ISPRIMARY = 1 then 1 else 2 end) ROWNO,
                    C.KEYNAME,
                    C.FIRSTNAME,
                    D.STREETNUMBERRESULT, 
                    D.STREETNAMERESULT, 
                    D.POSTCODERESULT,A.STATEID,A.COUNTRYID,
                    A.ADDRESSTYPECODEID,
                    P.PHONETYPECODEID,
                    E.EMAILADDRESSTYPECODEID,
                    A.ISPRIMARY ADDRESSISPRIMARY,
                    P.ISPRIMARY PHONEISPRIMARY,
                    E.ISPRIMARY EMAILISPRIMARY
                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
                ) Q
        where ROWNO = 1
        and (@ISADMIN = 1 or @CURRENTAPPUSERID is null or @APPUSER_IN_NONRACROLE = 1 or
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID,  ID, @APPUSER_IN_NOSECGROUPROLE) = 1)     
        order by MATCHPERCENTAGE desc , 
                case when KEYNAME = @KEYNAME then 1 else 2 end, 
                case when isnull(FIRSTNAME,'') = isnull(@FIRSTNAME,'') then 1 else 2 end,
                case when isnull(ADDRESSTYPECODEID,@NULLVALUE) = isnull(@ADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
                case when isnull(PHONETYPECODEID,@NULLVALUE) = isnull(@PHONETYPECODEID,@NULLVALUE) then 1 else 2 end, 
                case when isnull(EMAILADDRESSTYPECODEID,@NULLVALUE) = isnull(@EMAILTYPECODEID,@NULLVALUE) then 1 else 2 end,
                case when isnull(ADDRESSBLOCK,'') = isnull(@ADDRESSBLOCK,'') then 1 else 2 end,
                ADDRESSISPRIMARY desc,
                PHONEISPRIMARY desc,
                EMAILISPRIMARY desc