UFN_CONSTITUENT_ISINCONSTITUENTBYADDRESS

create function dbo.UFN_CONSTITUENT_ISINCONSTITUENTBYADDRESS (
    @ID uniqueidentifier,
    @CONSTITUENTBYADDRESSIDSETREGISTERID uniqueidentifier
)
returns bit
as begin
    declare @DATAFORMITEMFILTERXML xml;

    select
        @DATAFORMITEMFILTERXML = SMARTQUERYINSTANCE.DATAFORMITEMFILTERXML
    from
        dbo.IDSETREGISTER
    inner join
        dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
    inner join
        dbo.SMARTQUERYINSTANCE on SMARTQUERYINSTANCE.ID = IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID
    where
        IDSETREGISTER.ID = @CONSTITUENTBYADDRESSIDSETREGISTERID
        and SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = '9ed9e3b7-8b1e-4f3f-87c2-814d6b3e5a59';

    if @DATAFORMITEMFILTERXML is not null begin
        declare @GUIDFILTERS table (ID uniqueidentifier);
        declare @STRINGFILTERS table (VALUE nvarchar(50));
        declare @ADDRESSFIELD int;

        with xmlnamespaces (default 'bb_appfx_dataforms')
        select @ADDRESSFIELD = T.c.value('(Value)[1]', 'int')
        from @DATAFORMITEMFILTERXML.nodes('/DataFormItem/Values/fv') as T(c)
        where T.c.value('./@ID', 'nvarchar(max)') = 'ADDRESSFIELD';

        if @ADDRESSFIELD = 0 begin
            with xmlnamespaces (default 'bb_appfx_dataforms')
            insert into @GUIDFILTERS
            select T.c.value('(Value)[1]', 'uniqueidentifier')
            from @DATAFORMITEMFILTERXML.nodes('/DataFormItem/Values/fv/Value/Items/DataFormItem/Values/fv') T(c)
            where T.c.value('@ID', 'nvarchar(max)') = 'COUNTYCODEID';

        end else if @ADDRESSFIELD = 1 begin
            with xmlnamespaces (default 'bb_appfx_dataforms')
            insert into @STRINGFILTERS
            select T.c.value('(Value)[1]', 'nvarchar(50)')
            from @DATAFORMITEMFILTERXML.nodes('/DataFormItem/Values/fv/Value/Items/DataFormItem/Values/fv') T(c)
            where T.c.value('@ID', 'nvarchar(max)') = 'CITY';

        end else if @ADDRESSFIELD = 2 begin
            with xmlnamespaces (default 'bb_appfx_dataforms')
            insert into @GUIDFILTERS
            select T.c.value('(Value)[1]', 'uniqueidentifier')
            from @DATAFORMITEMFILTERXML.nodes('/DataFormItem/Values/fv/Value/Items/DataFormItem/Values/fv') T(c)
            where T.c.value('@ID', 'nvarchar(max)') = 'STATEID';

        end else if @ADDRESSFIELD = 3 begin
            with xmlnamespaces (default 'bb_appfx_dataforms')
            insert into @STRINGFILTERS
            select T.c.value('(Value)[1]', 'nvarchar(12)')
            from @DATAFORMITEMFILTERXML.nodes('/DataFormItem/Values/fv/Value/Items/DataFormItem/Values/fv') T(c)
            where T.c.value('@ID', 'nvarchar(max)') = 'POSTCODE';

        end

        if exists (
            select 1
            from dbo.[ADDRESS]
            left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
            where
                ADDRESS.CONSTITUENTID = @ID
                and 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))
                )
        )
            return 1;
    end

    return 0;
end