USP_SEARCHLIST_REGISTRANT

CREATE procedure dbo.USP_SEARCHLIST_REGISTRANT
(
    @LASTNAME nvarchar(100) = null,
    @FIRSTNAME nvarchar(100) = null,
    @LOOKUPID nvarchar(100) = null,
    @EMAILADDRESS nvarchar(300) = null,
    @ADDRESS nvarchar(300) = null,
    @CITY nvarchar(100) = null,
    @STATEID uniqueidentifier = null,
    @POSTCODE nvarchar(24) = null,
    @EVENTNAME nvarchar(200) = null,
    @SITESSELECTED xml = null,
    @SITEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = null,
    @MAXROWS smallint = 500
)
as
--    set @NAME = COALESCE(@NAME,'') + '%' ;

DECLARE @CHANGEAGENTID uniqueidentifier 
DECLARE @CURRENTAPPUSERID uniqueidentifier 

if @CHANGEAGENTID is null  
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output    

set @CURRENTAPPUSERID = ( select top 1 a.[ID]
from CHANGEAGENT  ch  join APPUSER a
    on coalesce(lower(a.[USERNAME]), lower( a.[WindowsUserName]) ) = lower(ch.[USERNAME]) 
where ch.ID = @CHANGEAGENTID )


;With RegSearch
as
(
    select top(@MAXROWS)
     c.[ID] [ID],
     r.[LOOKUPID]  [LOOKUPID], 
     c.[NAME]  [NAME],
     e.[NAME] [EVENTNAME], 
     r.[ID]  [REGISTRANTID], 
     a.[ADDRESSBLOCK]  [ADDRESS],
     a.[CITY]  [CITY],
     em.[EMAILADDRESS]  [EMAIL],
     e.[ID] AS [EVENTID],
     s.[ABBREVIATION],
     a.[POSTCODE] [ZIPCODE]
    from dbo.[EVENT] e (nolock)
    inner join dbo.[REGISTRANT] r (nolock) on e.ID = r.[EVENTID]
    inner join dbo.[CONSTITUENT] c (nolock) on r.[CONSTITUENTID] = c.[ID] and [ISORGANIZATION]=0
    left join dbo.[ADDRESS] a (nolock) on c.[ID] = a.[CONSTITUENTID] and a.[ISPRIMARY] = 1
    left join dbo.[EMAILADDRESS] em (nolock) on c.[ID] = em.[CONSTITUENTID] and em.[ISPRIMARY] = 1
    left join dbo.[STATE] s (nolock) on a.[STATEID] = s.[ID]
    where 
    ( @LASTNAME is null or c.[KEYNAME] like @LASTNAME + '%' )                and
    ( @FIRSTNAME is null or c.[FIRSTNAME] like @FIRSTNAME + '%' )            and
    ( @LOOKUPID is null or r.[LOOKUPID] like @LOOKUPID + '%')                and
    ( @EMAILADDRESS is null or em.[EMAILADDRESS] like @EMAILADDRESS + '%' ) and
    ( @ADDRESS is null or a.[ADDRESSBLOCK]  like @ADDRESS + '%' )            and
    ( @CITY is null or a.[CITY] like @CITY + '%' )                            and
    ( @STATEID is null or s.[ID] = @STATEID )                                and
    ( @POSTCODE is null or a.[POSTCODE] like @POSTCODE + '%' )                and
    ( @EVENTNAME is null or e.[NAME] like @EVENTNAME + '%' )
     --site filter

    and
    (
      @SITEFILTERMODE = 0
      or
      exists
      (
        select 1 from UFN_SITEID_MAPFROM_EVENTID(e.[ID]) EVENTSITE
          where EVENTSITE.SITEID in
          (
            select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
          )
      )
    )

),
GroupData
as
(
select 
 tf.[CONSTITUENTID] [CONSTITUENTID],
 tx.[EVENTID] [EVENTID], 
 tft.[NAME] [GROUPNAME]
 from [TEAMEXTENSION] tx (nolock)
 left join dbo.[TEAMFUNDRAISINGTEAM] tft (nolock)
     on tx.[TEAMFUNDRAISINGTEAMID] = tft.[ID] 
 left join dbo.[TEAMFUNDRAISINGTEAMMEMBER] tftm (nolock) 
     on tftm.[TEAMFUNDRAISINGTEAMID] = tx.[TEAMFUNDRAISINGTEAMID]  
 left join dbo.[TEAMFUNDRAISER] tf (nolock) 
     on tf.[ID] = tftm.[TEAMFUNDRAISERID]  
  where tf.[CONSTITUENTID] in ( SELECT ID FROM RegSearch )
)


    select 
     reg.[REGISTRANTID] [ID],
     reg.[REGISTRANTID] , 
     reg.[LOOKUPID], 
     reg.[NAME],
     g.[GROUPNAME],
     reg.[ADDRESS],
     reg.[CITY],
     reg.[ABBREVIATION] [STATE],
     reg.[ZIPCODE],
     reg.[EMAIL],
     reg.[EVENTNAME]

from RegSearch reg
left join GroupData g
    on reg.ID = g.CONSTITUENTID and reg.EVENTID = g.EVENTID 
    order by 
        reg.[NAME] asc