USP_DATALIST_TEAMMERGEDATA

CREATE procedure dbo.USP_DATALIST_TEAMMERGEDATA
  (
      @EVENTID uniqueidentifier = null,
      @CONSTITUENTID uniqueidentifier = null,
      @REGISTRANTID uniqueidentifier = null
  )
  as
      set nocount on;

DECLARE @PAGEBASEURL NVARCHAR(500)
SELECT @PAGEBASEURL=VALUE 
FROM DBO.CMSSITESETTING
WHERE CLIENTSITESID = 1 AND SETTING='PAGEBASEURL'

select 
    TeamName=(case TE.TYPECODE when 1 then TFT.NAME else null end )
    ,TeamID=(case TE.TYPECODE when 1 then TFT.ID else null end ) 
    ,TeamPageUrl=(@PAGEBASEURL+'?pid='+CAST(TPU.PAGEID AS VARCHAR(10)))
    ,TFundraisingTotalAmount_Goal=(case TE.TYPECODE when 1 then TFT.GOAL else null end )
    ,TNumberOfTeamMembers_Goal=(case TE.TYPECODE when 1 then TE.TEAMMEMBERGOAL else null end )
    ,TPercentageOfTeamMemberToRetain=(case TE.TYPECODE when 1 then TE.PCTTEAMMEMBERRETENSION else null end )
    ,TNumberOfParticipantsToRecruit=(case TE.TYPECODE when 1 then TE.PARTICIPANTGOAL else null end )
    ,TNumberOfCommunicationsToSend=(case TE.TYPECODE when 1 then TE.COMMUNICATIONGOAL else null end )
    ,TLYFundraisingTotalAmount_Goal=null
    ,TLYFundraisingTotalAmount_Actual=null
    ,TLYNumberOfTeamMembers_Goal=null
    ,TLYNumberOfTeamMembers_Actual=null
    ,TLYPercentageOfTeamMembersToRetain=null
    ,TLYPercentageOfTeamMembersRetained=null
    ,TLYNumberOfParticipantsToRecruit=null
    ,TLYNumberOfParticipantsRecruited=null
    ,TLYNumberOfCommunicationsToSend=null
    ,TLYNumberOfCommunicationsSent=null


from dbo.REGISTRANT RG    
inner join [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 TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
left outer join dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
left outer join dbo.REGISTRANTREGISTRATION RGG ON RGG.REGISTRANTID =RG.ID 
left outer join dbo.EVENTPRICE EVP ON EVP.EVENTID=EV.ID AND EVP.ID=RGG.EVENTPRICEID 
LEFT outer join dbo.FAFREGISTRATIONTYPEGOAL HFRGTG ON HFRGTG.EVENTPRICEID=EVP.ID and HFRGTG.REGTYPEGOALCODE=1
left outer join dbo.FAFEVENTPAGETEMPLATE TPU ON TPU.EVENTID=EV.ID AND TPU.PAGECATEGORYCODE=2

WHERE (RG.ID = @REGISTRANTID OR @REGISTRANTID is null) 
and (RG.CONSTITUENTID = @CONSTITUENTID or @CONSTITUENTID is null)
and (EV.id = @EVENTID OR @EVENTID is null)