UFN_REGISTRANT_GETFAFROLE

CREATE function dbo.UFN_REGISTRANT_GETFAFROLE
(  
  @EVENTID uniqueidentifier,
  @GROUPID uniqueidentifier = null
)
returns table
as return 

    With Leaders as 
    (
      select 
        TF.ID, 
        TF.CONSTITUENTID, 
        TX.TEAMFUNDRAISINGTEAMID, 
        TFTC.ID AS LEADERID,
        TX.TYPECODE,
    TX.STATUSCODE
      from dbo.TEAMEXTENSION TX  
      left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
      left join dbo.TEAMFUNDRAISER TF on TF.ID = TFTM.TEAMFUNDRAISERID
      left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC on TF.CONSTITUENTID = TFTC.CONSTITUENTID and TX.TEAMFUNDRAISINGTEAMID = TFTC.TEAMFUNDRAISINGTEAMID
      where TX.EVENTID = @EVENTID
    )
    select 
    '' as NAME,
    case 
       when TF.STATUSCODE = 2 then 'Individual' -- for delined leader

       when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 3 then 'Head of household'
           when TF.ID is not null and TF.LEADERID is null and TF.TYPECODE = 3 then 'Household member'
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 1 then 'Team leader'
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 1) then 'Team member'
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 2 then 'Company leader'
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 2) then 'Individual'
           when TF.ID is null then 'Individual' end as [Role],

      case when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 3 then 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452'
           when TF.ID is not null and TF.LEADERID is null and TF.TYPECODE = 3 then '3182787B-18A8-4B5E-BCBE-B56D97A6F88B'
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 1 then 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF'
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 1) then 'FE9F294E-324D-4237-8761-F6FA0D509F77'
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 2 then '61CBFC50-B232-4267-A20F-1F2E88879293'
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 2) then 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D'
           when TF.ID is null then 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D' end as [RoleGuidID],

    case when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 3 then 3
           when TF.ID is not null and TF.LEADERID is null and TF.TYPECODE = 3 then 4
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 1 then 1
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 1) then 2
           when TF.ID is not null and TF.LEADERID is not null and TF.TYPECODE = 2 then 0
           when TF.ID is not null and TF.LEADERID is null and (TF.TYPECODE = 2) then 5
           when TF.ID is null then 5 end as [RoleCode],
    R.ID as RECORDID,
    R.ID as REGISTRANTID,
    NULL as SPONSORID,
    R.CONSTITUENTID,
    TF.TEAMFUNDRAISINGTEAMID,
    case when TF.LEADERID is null then 0 else 1 end as isLeader     
    from dbo.REGISTRANT R
    left join Leaders TF ON TF.CONSTITUENTID = R.CONSTITUENTID
    where R.EVENTID = @EVENTID and (@GROUPID is null or TF.TEAMFUNDRAISINGTEAMID = @GROUPID) 

union all  

    select 
        '' as NAME,
        'Sponsor' as Role, 
        'B482AB69-4E63-4F76-B872-C6A50C94619C' as [RoleGuidID], 
        6 as RoleCode,
        ES.ID as RECORDID,
        NULL as REGISTRANTID,
        ES.ID as SPONSORID,
        ES.CONSTITUENTID as CONSTITUENTID,
        null as TEAMFUNDRAISINGTEAMID,
        0 as isLeader
    from dbo.EVENTSPONSOR ES
    where ES.EVENTID = @EVENTID