USP_DATALIST_HOUSEHOLDMERGEDATA

CREATE procedure dbo.USP_DATALIST_HOUSEHOLDMERGEDATA
  (
      @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 
    HouseholdID=(case TE.TYPECODE when 3 then TFT.ID else null end ) --50

    ,HouseholdName=(case TE.TYPECODE when 3 then TFT.NAME else null end )--51

    ,HouseholdPageUrl=(@PAGEBASEURL+'?pid='+CAST(HPU.PAGEID AS VARCHAR(10)))--52

    ,HParticipantPaysBalanceMessage=HFRGTG.PARTICIPANTPAYSBALANCEMESSAGE --53

    ,HMinFundraisingGoal=(case TE.TYPECODE when 3 then TFT.GOAL  else null end )--54

    ,HTargetFundraisingGoal=(case TE.TYPECODE when 3 then TE.TARGETFUNDRAISINGGOAL else null end )--55

    ,HNumberOfParticipantsToRecruit=(case TE.TYPECODE when 3 then TE.PARTICIPANTGOAL else null end )--56

    ,HPercentageOfDonorsToRetain=(case TE.TYPECODE when 3 then TE.DONORRETENTIONGOAL else null end )--57

    ,HNumberOfCommunicationsToSend=(case TE.TYPECODE when 3 then TE.COMMUNICATIONGOAL else null end )--58

    ,HLYMinFundraisingGoal=null--59

    ,HLYTargetFundraisingGoal=null--60

    ,HLYNumberOfParticipantsToRecruit=null--61

    ,HLYPercentageOfDonorsToRetain=null--62

    ,HLYNumberOfCommunicationsToSend=null--63



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=3
left outer join dbo.FAFEVENTPAGETEMPLATE HPU ON HPU.EVENTID=EV.ID AND HPU.PAGECATEGORYCODE=3

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