UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT

CREATE function dbo.UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT
(
@EVENTID uniqueidentifier,
@CONSTITUENTID uniqueidentifier
)
returns table
AS 
return
(
SELECT RG.ID as [RegistrantID]

,CASE TED.TYPECODE WHEN 3 THEN TFTD.ID ELSE NULL END as [HouseholdID]
, CASE TED.TYPECODE WHEN 3 THEN TFTD.Name ELSE NULL END as [HouseholdName]

, CASE WHEN TED.TYPECODE = 1 THEN TFTD.ID  
        WHEN TEP.TYPECODE = 1 THEN TFTP.ID 
        ELSE NULL END AS [TEAMID]
, CASE WHEN TED.TYPECODE = 1 THEN TFTD.NAME  
    WHEN TEP.TYPECODE = 1 THEN TFTP.Name 
    ELSE NULL END AS [TEAMNAME]

, CASE WHEN TED.TYPECODE = 1 THEN TFTD.GOAL  
    WHEN TEP.TYPECODE = 1 THEN TFTP.GOAL 
    ELSE NULL END AS [TEAMGOAL]

, CASE WHEN TED.TYPECODE = 2 THEN TFTD.ID 
    WHEN TEP.TYPECODE = 2 THEN TFTP.ID 
    WHEN TEGP.TYPECODE = 2 THEN TFTGP.ID  
    ELSE NULL END as [COMPANYID]    
, CASE WHEN TED.TYPECODE = 2 THEN TFTD.NAME 
    WHEN TEP.TYPECODE = 2 THEN TFTP.NAME 
    WHEN TEGP.TYPECODE = 2 THEN TFTGP.NAME  
    ELSE NULL END as [COMPANYNAME]        

, CASE WHEN TED.TYPECODE = 2 THEN TFTD.GOAL 
    WHEN TEP.TYPECODE = 2 THEN TFTP.GOAL 
    WHEN TEGP.TYPECODE = 2 THEN TFTGP.GOAL  
    ELSE NULL END as [COMPANYGOAL]     

, CASE WHEN TED.TYPECODE = 3 THEN TFTD.GOAL
    WHEN TEP.TYPECODE = 3 THEN TFTP.GOAL
    ELSE NULL END as [HOUSEHOLDGOAL]  
FROM dbo.REGISTRANT RG    
inner join dbo.EVENT EV ON EV.ID = RG.EVENTID  
inner join dbo.CONSTITUENT C ON C.ID=RG.CONSTITUENTID 
left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EV.APPEALID 
left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID 

left outer join dbo.TEAMFUNDRAISINGTEAM TFTD ON TFTD.ID=TFTM.TEAMFUNDRAISINGTEAMID 
left outer join dbo.TEAMEXTENSION TED ON TED.TEAMFUNDRAISINGTEAMID=TFTD.ID  -- Direct


left outer join dbo.TEAMFUNDRAISINGTEAM TFTP ON TFTP.ID=TFTD.PARENTTEAMID
left outer join dbo.TEAMEXTENSION TEP ON TEP.TEAMFUNDRAISINGTEAMID=TFTP.ID  -- Parent


left outer join dbo.TEAMFUNDRAISINGTEAM TFTGP ON TFTP.PARENTTEAMID = TFTGP.ID
left outer join dbo.TEAMEXTENSION TEGP ON TFTGP.ID=TEGP.TEAMFUNDRAISINGTEAMID  -- GrandParent


WHERE C.ID = @constituentID
AND EV.ID = @eventID 
)