UFN_SMARTQUERY_CONSTITUENTBYADDRESS

-- WARNING:  UFN_CONSTITUENT_ISINCONSTITUENTBYADDRESS is a specialized function

-- used in discounts that mimics the logic of this smart query for a single constituent.

-- If any changes are made here, please make sure UFN_CONSTITUENT_ISINCONSTITUENTBYADDRESS

-- is updated accordingly.

create function dbo.UFN_SMARTQUERY_CONSTITUENTBYADDRESS
(
    @ADDRESSFIELD int = null,
    @VALUES xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @MAXROWS int = 500    
)
returns @T table 
(
    ID uniqueidentifier not null,
    KEYNAME nvarchar(154),
    FIRSTNAME nvarchar(154),
    NAME nvarchar(154),
    ADDRESSBLOCK nvarchar(150),
    CITY nvarchar(50),
    COUNTY nvarchar (100),
    STATE nvarchar(50),
    POSTCODE nvarchar(12),
    LOOKUPID nvarchar(36)
)
as begin
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    if @ISADMIN = 0
    begin
        set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
        if @APPUSER_IN_NONRACROLE = 0
            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
    end

    declare @GUIDFILTERS table (ID uniqueidentifier);
    declare @STRINGFILTERS table (VALUE nvarchar(50));

    if @ADDRESSFIELD = 0 begin
        insert into @GUIDFILTERS
        select T.c.value('(COUNTYCODEID)[1]', 'uniqueidentifier') from @VALUES.nodes('/VALUES/ITEM') T(c)
    end else if @ADDRESSFIELD = 1 begin
        insert into @STRINGFILTERS
        select T.c.value('(CITY)[1]', 'nvarchar(50)') from @VALUES.nodes('/VALUES/ITEM') T(c)
    end else if @ADDRESSFIELD = 2 begin
        insert into @GUIDFILTERS
        select T.c.value('(STATEID)[1]', 'uniqueidentifier') from @VALUES.nodes('/VALUES/ITEM') T(c)
    end else if @ADDRESSFIELD = 3 begin
        insert into @STRINGFILTERS
        select T.c.value('(POSTCODE)[1]', 'nvarchar(12)') from @VALUES.nodes('/VALUES/ITEM') T(c)
    end

    insert into @T
    select top (@MAXROWS)
        CONSTITUENT.ID, 
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.NAME,
        ADDRESS.ADDRESSBLOCK,
        ADDRESS.CITY,
        COUNTYCODE.DESCRIPTION,
        STATE.DESCRIPTION,
        ADDRESS.POSTCODE,
        CONSTITUENT.LOOKUPID
    from dbo.[ADDRESS]
        inner join dbo.CONSTITUENT on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
        left join dbo.STATE on STATE.ID = ADDRESS.STATEID
        left join (dbo.ADDRESSVALIDATIONUPDATE inner join dbo.COUNTYCODE on ADDRESSVALIDATIONUPDATE.COUNTYCODEID = COUNTYCODE.ID) on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
    where
        ADDRESS.ISPRIMARY = 1
        and (
            (@ADDRESSFIELD = 0 and ADDRESSVALIDATIONUPDATE.COUNTYCODEID in (select ID from @GUIDFILTERS))
            or (@ADDRESSFIELD = 1 and ADDRESS.CITY in (select VALUE from @STRINGFILTERS))
            or (@ADDRESSFIELD = 2 and ADDRESS.STATEID in (select ID from @GUIDFILTERS))
            or (@ADDRESSFIELD = 3 and ADDRESS.POSTCODE in (select VALUE from @STRINGFILTERS))
        )
        and (
            @ISADMIN = 1 or 
            @APPUSER_IN_NONRACROLE = 1 or
            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
        )                     
    order by 
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.LOOKUPID
    option (recompile);

    return;
end