USP_SEARCHLIST_BANK

CREATE procedure dbo.USP_SEARCHLIST_BANK
(
    @KEYNAME nvarchar(50) = null
    ,@LOOKUPID nvarchar(36) = null
    ,@DEFAULTROUTINGNUMBER nvarchar(9) = null
    ,@DEFAULTSORTCODE nvarchar(6) = null

    ,@ADDRESSBLOCK nvarchar(100) = null
    ,@CITY nvarchar(100) = null
    ,@STATEID uniqueidentifier = null
    ,@POSTCODE nvarchar(20) = null
    ,@COUNTRYID uniqueidentifier = null

    ,@EXACTMATCHONLY bit = null
    ,@CHECKALIASES bit = null
    ,@PRIMARYADDRESSONLY bit = null
    ,@CHECKALTERNATELOOKUPIDS bit = null
    ,@FUZZYSEARCHONNAME bit = null

    ,@MAXROWS smallint = 500
    ,@CURRENTAPPUSERID uniqueidentifier = null
)
as

    declare @IsSysAdmin bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)
    declare @BypassSecurityGroupCheck bit = @IsSysAdmin
    declare @SecurityGroupSecurityForUser table(GRANTORDENY bit, SYSTEMROLEID uniqueidentifier, RECORDSECURITYMODE tinyint)
    declare @SecurityGroupsUserCanView table(SECURITYATTRIBUTEID uniqueidentifier)

    if @BypassSecurityGroupCheck = 0
    begin
        insert into @SecurityGroupSecurityForUser
        select GRANTORDENY, SYSTEMROLEID, RECORDSECURITYMODE 
        from 
            dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SEARCHLIST 
        where 
            APPUSERID = @CURRENTAPPUSERID AND 
            SEARCHLISTCATALOGID = N'aed9181f-7e54-4a76-95d4-309e504e285a'  -- this searchlists's ID


        declare @NumSecurityGroupSecurityPermissionsForUser int = (select COUNT(*) from @SecurityGroupSecurityForUser)
        if @NumSecurityGroupSecurityPermissionsForUser = 0
            return -- user is not allowed to view any records


        if exists(
                select 
                    1 
                from 
                    @SecurityGroupSecurityForUser 
                where 
                    GRANTORDENY = 1 AND 
                    RECORDSECURITYMODE = 0)
            set @BypassSecurityGroupCheck = 1
        else
            set @BypassSecurityGroupCheck = 0
        if @BypassSecurityGroupCheck is null
            set @ByPassSecurityGroupCheck = 0
        if @BypassSecurityGroupCheck = 0
        begin
            insert into @SecurityGroupsUserCanView
            select 
                SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
            from 
                @SecurityGroupSecurityForUser SGS
                inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SGS.SYSTEMROLEID
                inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID                                
            where
                SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID and
                SGS.RECORDSECURITYMODE = 2 and
                SGS.GRANTORDENY = 1
            union
            -- if the user is allowed to view constits with null security group attribute, 

            -- signify by inserting a null into the table var

            select
                null
            from
                @SecurityGroupSecurityForUser
            where
                GRANTORDENY = 1 and
                RECORDSECURITYMODE = 1
        end
    end

    declare @CHECKADDRESS bit = 1;
    if (@ADDRESSBLOCK is null or @ADDRESSBLOCK = '')
          and (@CITY is null or @CITY = '') 
          and (@POSTCODE is null or @POSTCODE = '') 
          and @STATEID is null 
          and @COUNTRYID is null

        set @CHECKADDRESS = 0;

        set @EXACTMATCHONLY = COALESCE(@EXACTMATCHONLY, 0);
        set @PRIMARYADDRESSONLY = COALESCE(@PRIMARYADDRESSONLY, 0);
        set @CHECKALTERNATELOOKUPIDS = COALESCE(@CHECKALTERNATELOOKUPIDS, 0);
        set @FUZZYSEARCHONNAME = COALESCE(@FUZZYSEARCHONNAME, 0);

        if @EXACTMATCHONLY = 0
        begin
            set @KEYNAME = REPLACE(REPLACE(COALESCE(@KEYNAME,''), '*', '%'), '?', '_') + '%';
            set @LOOKUPID = REPLACE(REPLACE(COALESCE(@LOOKUPID,''), '*', '%'), '?', '_') + '%';
            set @DEFAULTROUTINGNUMBER = REPLACE(REPLACE(COALESCE(@DEFAULTROUTINGNUMBER,''), '*', '%'), '?', '_') + '%';
            set @DEFAULTSORTCODE = REPLACE(REPLACE(COALESCE(@DEFAULTSORTCODE,''), '*', '%'), '?', '_') + '%';
            set @ADDRESSBLOCK = REPLACE(REPLACE(COALESCE(@ADDRESSBLOCK,''), '*', '%'), '?', '_') + '%';
            set @CITY = REPLACE(REPLACE(COALESCE(@CITY,''), '*', '%'), '?', '_') + '%';
            set @POSTCODE = REPLACE(REPLACE(COALESCE(@POSTCODE,''), '*', '%'), '?', '_') + '%';

            select top(@MAXROWS) 
                  B.ID
                ,C.LOOKUPID
                ,C.NAME
                ,B.DEFAULTROUTINGNUMBER
                ,B.DEFAULTSORTCODE
                ,A.ADDRESSBLOCK [ADDRESS]
                ,A.CITY
                ,S.ABBREVIATION [STATE]
                ,A.POSTCODE
                ,C.WEBADDRESS
                ,E.EMAILADDRESS
                ,P.NUMBER [PHONE]
            from dbo.BANK B
                inner join dbo.CONSTITUENT C on C.ID = B.ID
                left join dbo.PHONE P on P.ISPRIMARY = 1 and P.CONSTITUENTID = C.ID
                left join dbo.EMAILADDRESS E on E.ISPRIMARY = 1 and E.CONSTITUENTID = C.ID
                left join dbo.ADDRESS A on A.ISPRIMARY = 1 and A.CONSTITUENTID = C.ID
                left join dbo.STATE S on A.STATEID = S.ID
            where 
                -- Bank/Org name & fuzzy search

                (C.KEYNAME like @KEYNAME
                    or (C.KEYNAMESOUNDEX = soundex(@KEYNAME) and @FUZZYSEARCHONNAME <> 0)
                    or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME like @KEYNAME) and @CHECKALIASES <> 0))
                --Lookup ID

                and ((C.LOOKUPID like @LOOKUPID)
                    or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where AL.ALTERNATELOOKUPID like @LOOKUPID)
                        and @CHECKALTERNATELOOKUPIDS <> 0)))
                --Default routing number

                and (B.DEFAULTROUTINGNUMBER like @DEFAULTROUTINGNUMBER)
                --Default sort code

                and (B.DEFAULTSORTCODE like @DEFAULTSORTCODE)
                --Address fields

                and ((
                    A.ADDRESSBLOCK like @ADDRESSBLOCK
                    and A.CITY like @CITY
                    and A.POSTCODE like @POSTCODE
                    and (A.STATEID = @STATEID or @STATEID is null)
                    and (A.COUNTRYID = @COUNTRYID or @COUNTRYID is null)) 
                    or (A.ID is null and @CHECKADDRESS = 0) 
                    or (@PRIMARYADDRESSONLY = 0 and @CHECKADDRESS = 1
                        and C.ID in (select AD.CONSTITUENTID 
                                 from dbo.[ADDRESS] AD 
                                 where 
                                    AD.ADDRESSBLOCK like @ADDRESSBLOCK 
                                    and AD.CITY like @CITY 
                                    and AD.POSTCODE like @POSTCODE 
                                    and (AD.STATEID = @STATEID or @STATEID is null) 
                                    and (AD.COUNTRYID = @COUNTRYID or @COUNTRYID is null)))
                )
                and    (
                    (@BypassSecurityGroupCheck = 1) or 
                    exists(
                        select 1 
                        from 
                            @SecurityGroupsUserCanView SG 
                        left join 
                            dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT CSSA on CSSA.CONSTITUENTID = C.ID 
                        where 
                            (SG.SECURITYATTRIBUTEID = CSSA.CONSTIT_SECURITY_ATTRIBUTEID) or 
                            (SG.SECURITYATTRIBUTEID is null and CSSA.CONSTIT_SECURITY_ATTRIBUTEID is null)) 
                )
            order by
                C.KEYNAME ASC
        end
        else
        begin
            select top(@MAXROWS) 
                  B.ID
                ,C.LOOKUPID
                ,C.NAME
                ,B.DEFAULTROUTINGNUMBER
                ,B.DEFAULTSORTCODE
                ,A.ADDRESSBLOCK [ADDRESS]
                ,A.CITY
                ,S.ABBREVIATION [STATE]
                ,A.POSTCODE
                ,C.WEBADDRESS
                ,E.EMAILADDRESS
                ,P.NUMBER [PHONE]
            from dbo.BANK B
                inner join dbo.CONSTITUENT C on C.ID = B.ID
                left join dbo.PHONE P on P.ISPRIMARY = 1 and P.CONSTITUENTID = C.ID
                left join dbo.EMAILADDRESS E on E.ISPRIMARY = 1 and E.CONSTITUENTID = C.ID
                left join dbo.ADDRESS A on A.ISPRIMARY = 1 and A.CONSTITUENTID = C.ID
                left join dbo.STATE S on A.STATEID = S.ID
            where
                --Bank/Org name & fuzzy search

                ((@KEYNAME is null) 
                or (c.KEYNAME = @KEYNAME 
                or (C.KEYNAMESOUNDEX = soundex(@KEYNAME) and @FUZZYSEARCHONNAME <> 0) 
                or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME = @KEYNAME) and @CHECKALIASES <> 0)))
                --Lookup ID

                and (((@LOOKUPID is null) 
                or (C.LOOKUPID = @LOOKUPID) 
                or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where @LOOKUPID is not null and AL.ALTERNATELOOKUPID = @LOOKUPID) 
                and @CHECKALTERNATELOOKUPIDS <> 0))))
                --Default routing number

                and ((@DEFAULTROUTINGNUMBER is null) or (B.DEFAULTROUTINGNUMBER = @DEFAULTROUTINGNUMBER))
                --Default sort code

                and ((@DEFAULTSORTCODE is null) or (B.DEFAULTSORTCODE like @DEFAULTSORTCODE))
                --Address fields

                and (
                ((A.ADDRESSBLOCK = @ADDRESSBLOCK or @ADDRESSBLOCK is null)
                    and (A.CITY = @CITY or @CITY is null)
                    and (A.POSTCODE = @POSTCODE or @POSTCODE is null)
                    and (A.STATEID = @STATEID or @STATEID is null)
                    and (A.COUNTRYID = @COUNTRYID or @COUNTRYID is null)) 
                or (A.ID is null and @CHECKADDRESS = 0) 
                or (@PRIMARYADDRESSONLY = 0 and @CHECKADDRESS = 1
                    and C.ID in (select AD.CONSTITUENTID 
                                 from dbo.[ADDRESS] AD 
                                 where 
                                    (AD.ADDRESSBLOCK = @ADDRESSBLOCK or @ADDRESSBLOCK is null)
                                    and (AD.CITY = @CITY or @CITY is null)
                                    and (AD.POSTCODE = @POSTCODE or @POSTCODE is null)
                                    and (AD.STATEID = @STATEID or @STATEID is null) 
                                    and (AD.COUNTRYID = @COUNTRYID or @COUNTRYID is null)))
                )
                and    (
                    (@BypassSecurityGroupCheck = 1) or 
                    exists(
                        select 1 
                        from 
                            @SecurityGroupsUserCanView SG 
                        left join 
                            dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT CSSA on CSSA.CONSTITUENTID = C.ID 
                        where 
                            (SG.SECURITYATTRIBUTEID = CSSA.CONSTIT_SECURITY_ATTRIBUTEID) or 
                            (SG.SECURITYATTRIBUTEID is null and CSSA.CONSTIT_SECURITY_ATTRIBUTEID is null)) 
                )
            order by
                C.KEYNAME ASC
        end