USP_DATALIST_FAFEVENTREGISTRANTS3

create procedure dbo.USP_DATALIST_FAFEVENTREGISTRANTS3
(
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @RECORDTYPE nvarchar(25) = '',
  @STATUS nvarchar(2) = '',
  @LOOKUPID nvarchar(100) = '',
  @FIRSTNAME nvarchar(100) = '',
  @LASTNAME nvarchar(100) = '',
  @GROUP nvarchar(100) = '',
  @RECORDTYPECODE tinyint = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as 
set nocount on;

set @RECORDTYPE = isnull(@RECORDTYPE, '')
set @STATUS = isnull(@STATUS, '')

;With Registrants 
as 
(
    select 
      REGISTRANT.ID as [ID] 
    , REGISTRANT.CONSTITUENTID
    , REGISTRANT.EVENTID
    , REGISTRANT.LOOKUPID 
    , REGISTRANTEXTENSION.TARGETFUNDRAISINGGOAL
    , REGISTRANT.DATEADDED
    , CONSTITUENT.NAME
  , AG.[Role] as [Role] 
    from dbo.REGISTRANT With (NOLOCK)
    inner join    dbo.CONSTITUENT With (NOLOCK) on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.REGISTRANTEXTENSION With (NOLOCK) on REGISTRANT.ID = REGISTRANTEXTENSION.REGISTRANTID
  left join dbo.UFN_REGISTRANT_GETFAFROLE (@eventid, NULL) AG on REGISTRANT.ID = AG.REGISTRANTID
    where REGISTRANT.EVENTID = @EVENTID 
  and (AG.RoleCode = @RECORDTYPECODE or @RECORDTYPECODE is null)
    and (REGISTRANT.LOOKUPID like '' + @LOOKUPID + '%' or @LOOKUPID = '')
    and (CONSTITUENT.FIRSTNAME like '' + @FIRSTNAME + '%' or @FIRSTNAME = '')
    and (CONSTITUENT.KEYNAME like '' + @LASTNAME + '%' or @LASTNAME = '')
  and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(REGISTRANT.EVENTID) EVENTSITE
            where EVENTSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
           )
      )
  -- Check site security
    and (
          exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(REGISTRANT.EVENTID) EVENTSITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null))) )
        )
)
, Members
as 
(
    select TF.CONSTITUENTID, TX.EVENTID, TFT.NAME, TX.StatusCode
    from dbo.TEAMFUNDRAISINGTEAM TFT With (NOLOCK)
    join dbo.TEAMEXTENSION TX With (NOLOCK) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM With (NOLOCK) on TFT.ID = TFTM.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISER TF With (NOLOCK) on TF.ID = TFTM.TEAMFUNDRAISERID
    where TX.EVENTID = @EVENTID and (TFT.NAME like '' + @GROUP + '%' or @GROUP = '')
)


select  
      R.ID as [ID] 
    , R.LOOKUPID 
    , R.NAME 
    , R.[Role] as [Role] 
    , case when M.[StatusCode] != 2 then M.NAME else '' end as [Group] 
    , R.TARGETFUNDRAISINGGOAL
    , VF.AMOUNTRAISED AS [Total amount raised] 
    , case when dbo.UFN_REGISTRANT_ISCANCELLED (R.ID)=0 then 'Active' else 'Cancelled' end as [Status]
    , '' as [URL] 
  , 0 as PROGRESSMEMBERECRUITMENTGOAL
  , 0 as PROGRESSDONORRETENTIONGOAL
  , 0 as PROGRESSCOMMUNICATIONGOAL
  , R.CONSTITUENTID
    from Registrants R
    left join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@eventid) VF ON VF.CONSTITUENTID = R.CONSTITUENTID /*V_QUERY_REGISTRANT_FUNDRAISINGTOTAL VF on VF.ID = REGISTRANT.ID*/
    left join Members M on M.CONSTITUENTID = R.CONSTITUENTID
    where (M.NAME like '' + @GROUP + '%' or @GROUP = '')