USP_DATALIST_FAFGROUPTOPLEVELMEMBERS

CREATE procedure dbo.USP_DATALIST_FAFGROUPTOPLEVELMEMBERS
(
  @TEAMFUNDRAISINGTEAMID uniqueidentifier,
  @REGISTRANTNAME nvarchar(200) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @EVENTID uniqueidentifier

    select @EVENTID=TX.EVENTID from TEAMEXTENSION TX where TX.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID

    select R.ID,1 AS ISMEMBER, PL.TEAMID as TEAMFUNDRAISINGTEAMID, PL.TEAMNAME, 
      PL.name as NAME,  
      PARTICIPANTROLE.ROLE,
      case when PARTICIPANTROLE.RoleGuidID in ('BC6B6D42-C7E6-4983-9A99-09ABAE79F452','3182787B-18A8-4B5E-BCBE-B56D97A6F88B') then TFT.GOAL else RX.FUNDRAISINGGOAL end AS GOAL, 
      dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, @EVENTID) AS TOTALAMOUNTRAISED, 
      dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(R.ID, @EVENTID, 0) as URL,
      EA.EMAILADDRESS, P.NUMBER, 
      '' as [STATUS] 

    from dbo.UFN_FAF_GROUPPARTICIPANTSLIST(NULL, @EVENTID ,NULL) PL
    join dbo.REGISTRANT R ON PL.ID = R.CONSTITUENTID and PL.EVENTID = R.EVENTID
    join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, null) PARTICIPANTROLE on PARTICIPANTROLE.REGISTRANTID = R.ID
    join dbo.REGISTRANTEXTENSION RX ON R.ID = RX.REGISTRANTID
    left join dbo.TEAMFUNDRAISINGTEAM TFT on TFT.ID = PL.TEAMID
    left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TFTC.CONSTITUENTID = PL.ID and TFTC.TEAMFUNDRAISINGTEAMID = PL.TEAMID
    left join dbo.EMAILADDRESS EA on PL.ID = EA.CONSTITUENTID and EA.ISPRIMARY = 1
    left join dbo.PHONE P on PL.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
    Where PL.TEAMID = @TEAMFUNDRAISINGTEAMID and (@REGISTRANTNAME is null or PL.NAME like '%' + @REGISTRANTNAME + '%')
    and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(PL.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(PL.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))) )
        )


    Union all

    select TFT.ID, 0 as ISMEMBER, TX.TEAMFUNDRAISINGTEAMID, TFT.NAME as TEAMNAME, c.NAME, TX.[TYPE] AS [ROLE], 
    TFT.GOAL AS GOAL, 
    (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(TX.EVENTID,TFT.ID)) AS TOTALAMOUNTRAISED,
    dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(TX.TEAMFUNDRAISINGTEAMID, TX.EVENTID, TX.TYPECODE) as URL,
    '' as EMAILADDRESS,
    '' as NUMBER, 
    TX.[STATUS]
    from dbo.TEAMFUNDRAISINGTEAM TFT
    join dbo.TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.CONSTITUENT C on TX.TEAMCONSTITUENTID = C.ID
    where TFT.PARENTTEAMID = @TEAMFUNDRAISINGTEAMID and (@REGISTRANTNAME is null or @REGISTRANTNAME = '')
    and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.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(TX.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)))
          )
   )