USP_DATALIST_TEAMMEMBERWIDGET

CREATE procedure dbo.USP_DATALIST_TEAMMEMBERWIDGET(
      @EVENTID       uniqueidentifier,
      @REGISTRANTID  uniqueidentifier
   )
as
    set nocount on;

  Declare @TEAMID    uniqueidentifier
  Declare @PARENTTEAMID uniqueidentifier
  Declare @Role varchar(100)

    -- get teamID and parentteamID

Select @TEAMID = TM.TEAMFUNDRAISINGTEAMID,
       @PARENTTEAMID = T.ParentTeamID,
       @Role = rl.Role
  From dbo.REGISTRANT R
         INNER JOIN dbo.TEAMFUNDRAISER TF 
             ON R.CONSTITUENTID = TF.CONSTITUENTID
         INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TM
             ON TF.ID = TM.TEAMFUNDRAISERID
         INNER JOIN dbo.TEAMFUNDRAISINGTEAM T
               ON TM.TEAMFUNDRAISINGTEAMID = T.ID
         INNER JOIN dbo.EVENT E
            ON E.APPEALID = TF.APPEALID AND R.EVENTID = E.ID
       INNER JOIN dbo.UFN_REGISTRANT_GETFAFROLE (@EVENTID, null) rl on rl.REGISTRANTID = R.ID
  WHERE R.ID = @REGISTRANTID and E.ID = @EVENTID

---removed household logic; going forward, households should work the same as teams

Select (CASE WHEN TT.TypeCode = 3 AND @Role <> 'Household member' AND @Role <> 'Head of household' THEN trt.NAME ELSE c.NAME END) as NAME, 
rg.ID As RegistrantID, c.ID As ConstituentID,
(CASE WHEN TT.TypeCode = 3 AND @Role <> 'Household member' AND @Role <> 'Head of household' THEN 'Household' ELSE rl.Role END) as ROLE,
(CASE WHEN TT.TypeCode = 3 AND @Role <>'Household member' AND @Role <> 'Head of household' THEN IsNull(dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(trt.ID,@EVENTID), 0) ELSE IsNull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(rg.ID, @EVENTID), 0) END) as AMOUNT, trt.ID As TeamID,
re.ISPRIVATE as ISPRIVATE
 from REGISTRANT rg
    inner join REGISTRANTEXTENSION re on rg.ID = re.REGISTRANTID
    inner join CONSTITUENT c on c.ID = rg.CONSTITUENTID 
    inner join TEAMFUNDRAISER tr on c.ID = tr.CONSTITUENTID
    inner join TEAMFUNDRAISINGTEAMMEMBER tra on tra.TEAMFUNDRAISERID = tr.ID
    inner join TEAMFUNDRAISINGTEAM trt on trt.ID = tra.TEAMFUNDRAISINGTEAMID
    inner join TEAMEXTENSION TT on trt.ID = TT.TEAMFUNDRAISINGTEAMID and TT.TypeCode != 2
    inner join dbo.UFN_REGISTRANT_GETFAFROLE (@EVENTID, null) rl on rl.REGISTRANTID = rg.ID
    --Left join (Select SUM(amount) As Amount, ConstituentID from REVENUE group by CONSTITUENTID) rv on c.ID = rv.constituentID

 Where trt.ID = @TEAMID or trt.ParentTeamID = @TEAMID or trt.ID = @PARENTTEAMID 
 Order by rl.RoleCode,c.NAME