UFN_VANITYURL_GETFAFPARTICIPANTURL

create function dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL
(
@RECORDID uniqueidentifier,
@EVENTID uniqueidentifier,
@PAGETYPE tinyint = 0
)
returns nvarchar(max)
with execute as caller
as begin

  declare @PARTICIPANTURL nvarchar(max),
          @PAGEID nvarchar(20),
            @PAGEVANITYURL nvarchar(510)

  select @PAGEID = S1.Value, @PAGEVANITYURL = V1.VanityURL
      from EVENTEXTENSION EX 
      join CMSSITESETTING S1 on EX.CLIENTSITESID = S1.CLIENTSITESID and S1.ENUMID = 30      
      left join VanityURL V1 on V1.RealmID = CAST(S1.VALUE as Integer) and V1.RealmTypeID = 4
      where EX.EVENTID = @EVENTID    

  if @PAGETYPE > 0 
    select @PARTICIPANTURL = Case when VURL.VanityURL is null and @PAGEVANITYURL is null
                                  then S3.VALUE + '?pid=' +  @PAGEID + '&pt=' + cast(@PAGETYPE as nvarchar(2)) + '&aid=' +  cast(TX.TEAMFUNDRAISINGTEAMID as nvarchar(36)) 
                                  when VURL.VanityURL is null and @PAGEVANITYURL is not null
                                  then S2.VALUE + @PAGEVANITYURL + '/?pt=' + cast(@PAGETYPE as nvarchar(2)) + '&aid=' +  cast(TX.TEAMFUNDRAISINGTEAMID as nvarchar(36))                                   
                                  else S2.VALUE + VURL.VanityURL + '/' end
    from TEAMEXTENSION TX
    join EVENTEXTENSION EX on EX.EVENTID = TX.EVENTID
    left join CMSSITESETTING S2 on EX.CLIENTSITESID = S2.CLIENTSITESID and S2.ENUMID = 11
    left join CMSSITESETTING S3 on EX.CLIENTSITESID = S3.CLIENTSITESID and S3.ENUMID = 10
    left join FAFFRIENDLYURLPARAMS FAFURL on TX.TEAMFUNDRAISINGTEAMID = FAFURL.PARTICIPANTID and PAGETYPE = @PAGETYPE 
    left join VanityURL VURL on VURL.RealmID = FAFURL.SEQUENCEID and VURL.RealmTypeID = 7
    where EX.EVENTID = @EVENTID and TX.TEAMFUNDRAISINGTEAMID = @RECORDID
  else
    select @PARTICIPANTURL = Case when VURL.VanityURL is null and @PAGEVANITYURL is null 
                                  then S3.VALUE + '?pid=' + @PAGEID + '&pt=' + cast(@PAGETYPE as nvarchar(2)) + '&aid=' +  cast(R.ID as nvarchar(36)) 
                                  when VURL.VanityURL is null and @PAGEVANITYURL is not null
                                  then S2.VALUE + @PAGEVANITYURL + '/?pt=' + cast(@PAGETYPE as nvarchar(2)) + '&aid=' +  cast(R.ID as nvarchar(36)) 
                                  else S2.VALUE + VURL.VanityURL + '/' end
    from REGISTRANT R
    join EVENTEXTENSION EX on EX.EVENTID = R.EVENTID
    left join CMSSITESETTING S2 on EX.CLIENTSITESID = S2.CLIENTSITESID and S2.ENUMID = 11
    left join CMSSITESETTING S3 on EX.CLIENTSITESID = S3.CLIENTSITESID and S3.ENUMID = 10
    left join FAFFRIENDLYURLPARAMS FAFURL on R.ID = FAFURL.PARTICIPANTID and PAGETYPE = @PAGETYPE 
    left join VanityURL VURL on VURL.RealmID = FAFURL.SEQUENCEID and VURL.RealmTypeID = 7
    where EX.EVENTID = @EVENTID and R.ID = @RECORDID

  return @PARTICIPANTURL
end