USP_DATALIST_COMPANYMERGEDATA

CREATE procedure dbo.USP_DATALIST_COMPANYMERGEDATA
  (
      @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 
    CompanyTeamName=(case when TE.TYPECODE=2 then  TFT.Name When TFT.PARENTTEAMID is not null then CTFT.Name else null end)
    ,CompanyID=(case when TE.TYPECODE=2 then  TFT.ID When TFT.PARENTTEAMID is not null then CTFT.ID else null end)
    ,CompanyPageUrl=@PAGEBASEURL+'?pid='+CAST(CPU.PAGEID AS VARCHAR(10))
    ,CFundraisingTotalAmount_Goal=(case when TE.TYPECODE=2 then  TFT.GOAL When TFT.PARENTTEAMID is not null then CTFT.GOAL else null end)
    ,CNumberOfTeams_Goal=(case when TE.TYPECODE=2 then  TE.TEAMRECRUITMENTGOAL When TFT.PARENTTEAMID is not null then CTE.TEAMRECRUITMENTGOAL else null end)
    ,CPercentageOfTeamsToRetain=(case when TE.TYPECODE=2 then  TE.PCTTEAMSRETENSION When TFT.PARENTTEAMID is not null then CTE.PCTTEAMSRETENSION else null end)
    ,CNumberOfTeamMembers_Goal=(case when TE.TYPECODE=2 then  TFT.GOAL When TE.TEAMMEMBERGOAL is not null then CTE.TEAMMEMBERGOAL else null end)
    ,CPercentageOfTeamMembersToRetain=(case when TE.TYPECODE=2 then  TE.PCTTEAMMEMBERRETENSION  When TFT.PARENTTEAMID is not null then CTE.PCTTEAMMEMBERRETENSION  else null end)
    ,CNumberOfParticipantsToRecruit=(case when TE.TYPECODE=2 then  TE.PARTICIPANTGOAL When TFT.PARENTTEAMID is not null then CTE.PARTICIPANTGOAL else null end)
    ,CNumberOfCommunicationsToSend=(case when TE.TYPECODE=2 then  TE.COMMUNICATIONGOAL When TFT.PARENTTEAMID is not null then CTE.COMMUNICATIONGOAL else null end)
    ,CLYFundraisingTotalAmount_Goal=null
    ,CLYFundraisingTotalAmount_Actual=null
    ,CLYNumberOfTeams_Goal=null
    ,CLYNumberOfTeams_Actual=null
    ,CLYPercentageOfTeamsToRetain=null
    ,CLYPercentageOfTeamsRetained=null
    ,CLYNumberOfTeamMembers_Goal=null
    ,CLYNumberOfTeamMembers_Actual=null
    ,CLYPercentageOfTeamMembersToRetain=null
    ,CLYPercentageOfTeamMembersRecruited=null
    ,CLYNumberOfParticipantsToRecruit=null
    ,CLYNumberOfParticipantsRecruited=null
    ,CLYNumberOfCommunicationsToSend=null
    ,CLYNumberOfCommunicationsSent=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 CFRGTG ON CFRGTG.EVENTPRICEID=EVP.ID and CFRGTG.REGTYPEGOALCODE=2 
left outer join dbo.FAFEVENTPAGETEMPLATE CPU ON CPU.EVENTID=EV.ID AND CPU.PAGECATEGORYCODE=4
left outer join dbo.TEAMFUNDRAISINGTEAM CTFT ON CTFT.ID=TFT.ParentTeamID 
left outer join dbo.TEAMEXTENSION CTE ON CTE.TEAMFUNDRAISINGTEAMID=CTFT.ID

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