UFN_EMAILFINDER_GETMATCHINGHOUSEHOLDS

CREATE function dbo.UFN_EMAILFINDER_GETMATCHINGHOUSEHOLDS
(
    @ISPRIMARY bit,
    @ADDRESSTYPECODEID uniqueidentifier,
    @SELECTIONID uniqueidentifier,
    @OWNERID uniqueidentifier,
    @INCLUDEINACTIVE bit,
    @INCLUDEINCOMPLETEADDRESSES bit
) returns 
@MATCHING table
(
    ADDRESSID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    GROUPID uniqueidentifier,
    ISPRIMARY bit,
    ADDRESSBLOCK nvarchar(150),
    CITY nvarchar(100),
    POSTCODE nvarchar(12),
    STATEID uniqueidentifier,
    ADDRESSTYPECODEID uniqueidentifier
)as
begin
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @SELECTION table (ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, groupid uniqueidentifier);

    declare @BYPASSSECURITY bit;
    declare @BPID uniqueidentifier;
    declare @BYPASSSITESECURITY bit;
    set @BPID = '2be9f4ad-5f54-444c-9176-1807e7a895d9';
    set @BYPASSSECURITY = 0;
    set @BYPASSSITESECURITY = 0;

    declare @ISADMIN bit;
    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
    declare @APPUSER_IN_NONSITEROLE bit;
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@OWNERID);
    declare @APPUSER_IN_NOSITEROLE bit;                
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@OWNERID);

    if @OWNERID is not null
    begin
        select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);

        if @BYPASSSECURITY = 0 
            begin
                select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
                select @BYPASSSITESECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONSITEROLE(@OWNERID, @BPID);                        
            end
        else
            set @BYPASSSITESECURITY = 1;
    end
    else
        begin
            set @BYPASSSECURITY = 1;
            set @BYPASSSITESECURITY = 1;
        end

    -- create selection of addresses

    if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
    begin
        if @SELECTIONID is null or @SELECTIONID = '00000000-0000-0000-0000-000000000000'
            insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
            (
                select
                    ADDRESS.ID,
                    CONSTITUENT.ID,
                    GROUPMEMBER.GROUPID
                from
                    dbo.CONSTITUENT
                left join 
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
                left join 
                    dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                inner join 
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                inner join 
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join 
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                where 
                    (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                    and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                    and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                    and (CONSTITUENT.ISINACTIVE = @INCLUDEINACTIVE or @INCLUDEINACTIVE = 1)
                    and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                    and (@BYPASSSITESECURITY = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@OWNERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                    and (((ADDRESS.ADDRESSBLOCK <> '') and (ADDRESS.STATEID is not null) and (ADDRESS.CITY <> '')) or @INCLUDEINCOMPLETEADDRESSES = 1)
            );
        else
            insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
            (
                select 
                    ADDRESS.ID,
                    CONSTITUENT.ID,
                    GROUPMEMBER.GROUPID
                from 
                    dbo.CONSTITUENT
                inner join 
                    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID
                left join 
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
                left join 
                    dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                inner join
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                inner join 
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join 
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                where 
                    (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                    and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                    and (CONSTITUENT.ISINACTIVE = @INCLUDEINACTIVE or @INCLUDEINACTIVE = 1)
                    and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                    and (@BYPASSSITESECURITY = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@OWNERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                    and (((ADDRESS.ADDRESSBLOCK <> '') and (ADDRESS.STATEID is not null) and (ADDRESS.CITY <> '')) or @INCLUDEINCOMPLETEADDRESSES = 1)

            );                            
    end
    else
    begin
        if @SELECTIONID is null or @SELECTIONID = '00000000-0000-0000-0000-000000000000'
            insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
            (
                select 
                    ADDRESS.ID,
                    CONSTITUENT.ID,
                    GROUPMEMBER.GROUPID
                from 
                    dbo.CONSTITUENT
                left join 
                    dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                inner join 
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                inner join
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join 
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                where 
                    (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                    and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                    and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                    and (CONSTITUENT.ISINACTIVE = @INCLUDEINACTIVE or @INCLUDEINACTIVE = 1)
                    and (((ADDRESS.ADDRESSBLOCK <> '') and (ADDRESS.STATEID is not null) and (ADDRESS.CITY <> '')) or @INCLUDEINCOMPLETEADDRESSES = 1)
            );
        else
            insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
            (
                select
                    ADDRESS.ID,
                    CONSTITUENT.ID,
                    GROUPMEMBER.GROUPID
                from 
                    dbo.CONSTITUENT
                inner join 
                    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID
                left join 
                    dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                inner join 
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                inner join 
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join 
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                where 
                    (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                    and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                    and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                    and (CONSTITUENT.ISINACTIVE = @INCLUDEINACTIVE or @INCLUDEINACTIVE = 1)
                    and (((ADDRESS.ADDRESSBLOCK <> '') and (ADDRESS.STATEID is not null) and (ADDRESS.CITY <> '')) or @INCLUDEINCOMPLETEADDRESSES = 1)
            );
    end

    -- find all group members and household records in address selection

    declare @MEMBER table (ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, GROUPID uniqueidentifier, ISPRIMARY bit, ADDRESSBLOCK nvarchar(150), CITY nvarchar(100), POSTCODE nvarchar(12), STATEID uniqueidentifier, ADDRESSTYPECODEID uniqueidentifier);
    insert into @MEMBER
    (ADDRESSID, CONSTITUENTID, GROUPID, ISPRIMARY, ADDRESSBLOCK, CITY, POSTCODE, STATEID, ADDRESSTYPECODEID)
    (
        select
            ADDRESS.ID,
            ADDRESS.CONSTITUENTID,
            SELECTION.GROUPID,
            GROUPMEMBER.ISPRIMARY,
            ADDRESS.ADDRESSBLOCK,
            ADDRESS.CITY,
            ADDRESS.POSTCODE,
            ADDRESS.STATEID,
            ADDRESS.ADDRESSTYPECODEID
        from 
            dbo.ADDRESS
        inner join 
            @SELECTION SELECTION on SELECTION.ADDRESSID = ADDRESS.ID
        inner join 
            dbo.GROUPMEMBER on ADDRESS.CONSTITUENTID = GROUPMEMBER.MEMBERID
        left outer join 
            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GROUPMEMBER.ID
        where 
            (GROUPMEMBER.GROUPID = SELECTION.GROUPID)
            and 
                (
                    (GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE) )
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE) ) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE) 
                )

        union all

        select
            ADDRESS.ID,
            ADDRESS.CONSTITUENTID,
            SELECTION.GROUPID,
            0,
            ADDRESS.ADDRESSBLOCK,
            ADDRESS.CITY,
            ADDRESS.POSTCODE,
            ADDRESS.STATEID,
            ADDRESS.ADDRESSTYPECODEID
        from 
            dbo.ADDRESS
        inner join 
            @SELECTION SELECTION on ADDRESS.ID = SELECTION.ADDRESSID
        inner join 
            dbo.CONSTITUENT on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
        where 
            CONSTITUENT.ISGROUP = 1
    );

    -- all matching household records

    insert into @MATCHING
    (ADDRESSID, CONSTITUENTID, GROUPID, ISPRIMARY, ADDRESSBLOCK, CITY, POSTCODE, STATEID, ADDRESSTYPECODEID)
    (
        select distinct
            ADDRESS.ID,
            ADDRESS.CONSTITUENTID,
            coalesce(SELECTION.GROUPID, ADDRESS.CONSTITUENTID),
            MEMBER.ISPRIMARY,
            ADDRESS.ADDRESSBLOCK,
            ADDRESS.CITY,
            ADDRESS.POSTCODE,
            ADDRESS.STATEID,
            ADDRESS.ADDRESSTYPECODEID
        from 
            @SELECTION SELECTION
        inner join 
            dbo.ADDRESS on SELECTION.ADDRESSID = ADDRESS.ID
        inner join 
            @MEMBER MEMBER on (SELECTION.GROUPID = MEMBER.GROUPID or SELECTION.CONSTITUENTID = MEMBER.GROUPID)
        where 
            ( (ADDRESS.ADDRESSBLOCK = MEMBER.ADDRESSBLOCK)
            and (ADDRESS.CITY = MEMBER.CITY)
            and ( (ADDRESS.STATEID = MEMBER.STATEID) or (ADDRESS.STATEID is null and MEMBER.STATEID is null) )
            and (ADDRESS.POSTCODE = MEMBER.POSTCODE)
            and ( (ADDRESS.ADDRESSTYPECODEID = MEMBER.ADDRESSTYPECODEID) or (ADDRESS.ADDRESSTYPECODEID is null and MEMBER.ADDRESSTYPECODEID is null) ))
            and (ADDRESS.CONSTITUENTID <> MEMBER.CONSTITUENTID)
    );

    return;
end