USP_DATALIST_FAFEVENT_REGISTRATION_MERGEDATA

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

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

declare @Name varchar(200)
declare @Description varchar(500)
declare @Amount money
declare @Benefits varchar(500)
declare @FundraisingGroups varchar(500)
declare @NumberAvailable varchar(500)
declare @ISACTIVE VARCHAR(10)

If @CONSTITUENTID is NULL And Not @REGISTRANTID is null
begin
  SET @CONSTITUENTID = (select constituentid from dbo.registrant where id=@REGISTRANTID)
end

declare @RegistrationsHtml nvarchar(max)
set @RegistrationsHtml='<table border="1px" cellspacing="0px">    
       <tr>
       <td>Name</td>
       <td>Description</td>
       <td>Amount</td>
       <td>Benefits</td>
       <td>Fundraising Groups&nbsp;</td>
       <td>Number available</td>
       <td>Active</td>
       </tr>'

DECLARE myCursor CURSOR for 
select EVENTPRICE.NAME,
FAF.DESCRIPTION,
EVENTPRICE.AMOUNT,
dbo.UDA_BUILDLIST(BENEFIT.NAME) as BENEFITS,
((case FAF.ISREGTYPEAVAILABLEINDIVIDUAL when 1 then 'Individuals' ELSE '' end)+
(case  FAF.ISREGTYPEAVAILABLEFAMILYMEMBER when 1 then ';Household members' else '' end)+
(case  FAF.ISREGTYPEAVAILABLEHEADHOUSEHOLD when 1 then ';Heads of households' else ''  end)+
(case  FAF.ISREGTYPEAVAILABLETEAMMEMBER when 1 then ';Team members' else ''  end)+ 
(case  FAF.ISREGTYPEAVAILABLETEAMLEADER when 1 then ';Team leaders' else ''  end)+
(case  FAF.ISREGTYPEAVAILABLECOMPANYLEADER when 1 then ';Company leaders' else ''  end)
) as FundraisingGroups, 
CASE WHEN FAF.MAXQUANTITY > 0 THEN CAST(FAF.MAXQUANTITY AS nvarchar(20)) ELSE 'Unlimited' END as NUMBERAVAILABLE,
(case when FAF.ISACTIVE = 1 then 'Yes' else 'No' end) as ISACTIVE
from dbo.EVENTPRICE (nolock)
join dbo.FAFREGISTRATIONTYPE FAF (nolock) on EVENTPRICE.ID = FAF.EVENTPRICEID
left join dbo.EVENTPRICEBENEFIT (nolock) on FAF.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.BENEFIT (nolock) on BENEFIT.ID = EVENTPRICEBENEFIT.BENEFITID
inner join dbo.REGISTRANTREGISTRATION(nolock) on REGISTRANTREGISTRATION.EVENTPRICEID= EVENTPRICE.ID
inner join dbo.REGISTRANT(nolock) on  REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID
where EVENTPRICE.EVENTID = @EVENTID --and REGISTRANT.ConstituentID=@ConstituentID

and FAF.ISACTIVE = 1
group by EVENTPRICE.ID, EVENTPRICE.NAME,FAF.DESCRIPTION,EVENTPRICE.AMOUNT, 
FAF.ISREGTYPEAVAILABLEINDIVIDUAL, FAF.ISREGTYPEAVAILABLEFAMILYMEMBER, FAF.ISREGTYPEAVAILABLEHEADHOUSEHOLD, 
FAF.ISREGTYPEAVAILABLETEAMMEMBER, FAF.ISREGTYPEAVAILABLETEAMLEADER, FAF.ISREGTYPEAVAILABLECOMPANYLEADER, 
FAF.MAXQUANTITY, FAF.ISACTIVE

open myCursor
fetch next from myCursor into @Name,@Description,@Amount,@Benefits,@FundraisingGroups,@NumberAvailable,@ISActive
while(@@fetch_status=0)
  begin
    set @Benefits=isnull(@Benefits,'&nbsp;')
    set @Benefits=(case when @Benefits='' then '&nbsp;' else '&nbsp;' end )

    set @RegistrationsHtml=@RegistrationsHtml+    '
    <tr>
        <td>'+@Name+'</td>
        <td>'+@Description+'</td>
        <td>'+cast(@Amount as varchar(20))+'</td>
        <td>'+@Benefits+'</td>
        <td>'+(case when left(@FundraisingGroups,1)=';' then SUBSTRING(@FundraisingGroups,2,LEN(@FundraisingGroups)-1) else @FundraisingGroups end)+'</td>
        <td>'+@NumberAvailable+'</td>
        <td>'+@ISActive+'</td>
        </tr>'

    fetch next from myCursor into  @Name,@Description,@Amount,@Benefits,@FundraisingGroups,@NumberAvailable,@ISActive
  end
close myCursor
deallocate myCursor

set @RegistrationsHtml=@RegistrationsHtml+'</table>'

    select 
    --Event

    EventName= EV.NAME --0

    ,EventLookupID=ET.LOOKUPEVENTID  --1

    ,EventLocation=EL.NAME  --2

    ,EventStartDate=EV.STARTDATE --3

    ,EventEndDate=EV.ENDDATE --4

    ,EventStartTime=EV.STARTTIME --5

    ,EventEndTime=EV.ENDTIME --6

    ,EventContactName= EVContact.NAME --7

    ,EventContactEmail=EVCEA.EMAILADDRESS--8

    ,EventContactPhone=EVCP.NUMBER--9

    ,EventSupportPhone=ET.supportphone --10

    ,EventSupportEmail=null--11

    ,EventSupportURL=ET.supporturl--12

    ,EventLoginLink=null--13

    ,FundraisingGroup=(case TE.TYPECODE when 1 then 'Team' when 2 then 'Company' when 3 then 'Household' else null end)--14



    --Profile

    ,BillingAddress=null--15

    ,ShippingAddress=null--16


    --Revenue

    ,RegistrationOptionDetail=@RegistrationsHtml--17

    ,AdditionalDonationAmount=RD.VOLUNTARYDONATION --18

    ,PaymentID=RM.ID --19

    ,PaymentName=RS.DESCRIPTION --20

    ,PaymentType=RM.PAYMENTMETHOD --21

    ,PaymentAmount=RS.AMOUNT --22

    ,PaymentDate=R.POSTDATE --not sure--23

    ,CreditCard=null--24

    ,MatchingGiftCompayName=null--25

    ,MatchingGiftAmount=R.AMOUNT  --26


    --Tribute

    ,Tributee=(case when TR.TRIBUTEEID is null then C0.NAME else CTR.NAME end) --27

    ,TributeType=TRC.DESCRIPTION --28

    ,TributeMessage=TR.TRIBUTETEXT --29

    ,AnonymousDonation=(case R.GIVENANONYMOUSLY when '1' then 'Yes' else  'No' END) --Not sure--30

    ,Acknowledgee=TRE.ACKNOWLEDGEELASTNAME+' '+TRE.ACKNOWLEDGEEFIRSTNAME --31


    --Individual participants

    ,ParticipantID=C0.ID --32

    ,IndividualEventPageUrl=(@PAGEBASEURL+'?pid='+CAST(IPU.PAGEID AS VARCHAR(10)))--33

    ,IMinFundraisingGoal=RGE.FUNDRAISINGGOAL --34

    ,IParticipantPaysBalanceMessage=IFRGTG.PARTICIPANTPAYSBALANCEMESSAGE --35

    ,ITargetFundraisingGoal=RGE.TARGETFUNDRAISINGGOAL--36

    ,ITotalFundraisingAmount=(select amountRaised from dbo. UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@EVENTID) Where constituentID = @CONSTITUENTID)
    ,INumberOfParticipantsToRecruit=RGE.MEMBERECRUITMENTGOAL--38

    ,IPercentageOfDonorsToRetain=RGE.DONORRETENTIONGOAL--39

    ,INumberForOtherGoal=RgE.OTHERUNITGOAL--40

    ,ILYMinFundraisingGoal=null--41

    ,ILYTargetFundraisingGoal=null--42

    ,ILYTotalAmoutRaised=null--43

    ,ILYNumberOfParticipantsToRecruit=null--44

    ,ILYNumberOfParticipantsRecruited=null--45

    ,ILYPercentageOfDonorsToRetain=null--46

    ,ILYPercentageOfDonorsRetained=null--47

    ,ILYNumberOfCommunicationsToSend=null--48

    ,ILYNumberOfCommunicationsSent=null--49


    --HouseHolds

    ,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


    --Team

    ,TeamName=(case TE.TYPECODE when 1 then TFT.NAME else null end ) --64

    ,TeamID=(case TE.TYPECODE when 1 then TFT.ID else null end ) --65

    ,TeamPageUrl=(@PAGEBASEURL+'?pid='+CAST(TPU.PAGEID AS VARCHAR(10)))--66

    ,TFundraisingTotalAmount_Goal=(case TE.TYPECODE when 1 then TFT.GOAL else null end )--67

    ,TNumberOfTeamMembers_Goal=(case TE.TYPECODE when 1 then TE.TEAMMEMBERGOAL else null end )--68

    ,TPercentageOfTeamMemberToRetain=(case TE.TYPECODE when 1 then TE.PCTTEAMMEMBERRETENSION else null end )--69

    ,TNumberOfParticipantsToRecruit=(case TE.TYPECODE when 1 then TE.PARTICIPANTGOAL else null end )--70

    ,TNumberOfCommunicationsToSend=(case TE.TYPECODE when 1 then TE.COMMUNICATIONGOAL else null end )--71

    ,TLYFundraisingTotalAmount_Goal=null--72

    ,TLYFundraisingTotalAmount_Actual=null--73

    ,TLYNumberOfTeamMembers_Goal=null--74

    ,TLYNumberOfTeamMembers_Actual=null--75

    ,TLYPercentageOfTeamMembersToRetain=null--76

    ,TLYPercentageOfTeamMembersRetained=null--77

    ,TLYNumberOfParticipantsToRecruit=null--78

    ,TLYNumberOfParticipantsRecruited=null--79

    ,TLYNumberOfCommunicationsToSend=null--80

    ,TLYNumberOfCommunicationsSent=null--81


    --Companies 

    ,CompanyTeamName=(case when TE.TYPECODE=2 then  TFT.Name When TFT.PARENTTEAMID is not null then CTFT.Name else null end)--82

    ,CompanyID=(case when TE.TYPECODE=2 then  TFT.ID When TFT.PARENTTEAMID is not null then CTFT.ID else null end)--83

    ,CompanyPageUrl=@PAGEBASEURL+'?pid='+CAST(CPU.PAGEID AS VARCHAR(10))--84

    ,CFundraisingTotalAmount_Goal=(case when TE.TYPECODE=2 then  TFT.GOAL When TFT.PARENTTEAMID is not null then CTFT.GOAL else null end)--85

    ,CNumberOfTeams_Goal=(case when TE.TYPECODE=2 then  TE.TEAMRECRUITMENTGOAL When TFT.PARENTTEAMID is not null then CTE.TEAMRECRUITMENTGOAL else null end)--86

    ,CPercentageOfTeamsToRetain=(case when TE.TYPECODE=2 then  TE.PCTTEAMSRETENSION When TFT.PARENTTEAMID is not null then CTE.PCTTEAMSRETENSION else null end)--87

    ,CNumberOfTeamMembers_Goal=(case when TE.TYPECODE=2 then  TFT.GOAL When TE.TEAMMEMBERGOAL is not null then CTE.TEAMMEMBERGOAL else null end)--88

    ,CPercentageOfTeamMembersToRetain=(case when TE.TYPECODE=2 then  TE.PCTTEAMMEMBERRETENSION  When TFT.PARENTTEAMID is not null then CTE.PCTTEAMMEMBERRETENSION  else null end)--89

    ,CNumberOfParticipantsToRecruit=(case when TE.TYPECODE=2 then  TE.PARTICIPANTGOAL When TFT.PARENTTEAMID is not null then CTE.PARTICIPANTGOAL else null end)--90

    ,CNumberOfCommunicationsToSend=(case when TE.TYPECODE=2 then  TE.COMMUNICATIONGOAL When TFT.PARENTTEAMID is not null then CTE.COMMUNICATIONGOAL else null end)--91

    ,CLYFundraisingTotalAmount_Goal=null--92

    ,CLYFundraisingTotalAmount_Actual=null--93

    ,CLYNumberOfTeams_Goal=null--94

    ,CLYNumberOfTeams_Actual=null--95

    ,CLYPercentageOfTeamsToRetain=null--96

    ,CLYPercentageOfTeamsRetained=null--97

    ,CLYNumberOfTeamMembers_Goal=null--98

    ,CLYNumberOfTeamMembers_Actual=null--99

    ,CLYPercentageOfTeamMembersToRetain=null--100

    ,CLYPercentageOfTeamMembersRecruited=null--101

    ,CLYNumberOfParticipantsToRecruit=null--102

    ,CLYNumberOfParticipantsRecruited=null--103

    ,CLYNumberOfCommunicationsToSend=null--104

    ,CLYNumberOfCommunicationsSent=null--105


  ,IPNumberOfParticipantsRecruited=(select  COUNT(fc.TYPEGUID)    
                                            from   FAFCOMMUNICATIONSLOG fl  
                                            join   FAFEVENTCOMMUNICATIONCHANNEL fc on fl.EMAILJOBID = fc.EMAILJOBID  
                                            WHERE   fl.CLIENTUSERSID  IN (SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT (RG.CONSTITUENTID)))  


from dbo.REGISTRANT RG    
inner join [EVENT] EV ON EV.ID = RG.EVENTID  
inner join dbo.EVENTEXTENSION ET ON EV.ID = ET.EVENTID
left outer join dbo.CONSTITUENT C ON EV.EVENTLOCATIONCONTACTID = C.ID    
left outer join dbo.CONSTITUENT C0 ON RG.CONSTITUENTID = C0.id-- and (Not @REGISTRANTID is null or Not @TEAMID is null)


--Registrant

left outer join dbo.REGISTRANTREGISTRATION RGG ON RGG.REGISTRANTID =RG.ID 
left outer join dbo.EVENTPRICE EVP ON EVP.EVENTID=@EVENTID AND EVP.ID=RGG.EVENTPRICEID 
--Individual Goal

LEFT outer join dbo.FAFREGISTRATIONTYPEGOAL IFRGTG ON IFRGTG.EVENTPRICEID=EVP.ID and IFRGTG.REGTYPEGOALCODE=0 
--Team Goal

LEFT outer join dbo.FAFREGISTRATIONTYPEGOAL TFRGTG ON TFRGTG.EVENTPRICEID=EVP.ID and TFRGTG.REGTYPEGOALCODE=1 
--Company Goal

LEFT outer join dbo.FAFREGISTRATIONTYPEGOAL CFRGTG ON CFRGTG.EVENTPRICEID=EVP.ID and CFRGTG.REGTYPEGOALCODE=2 
--Household

LEFT outer join dbo.FAFREGISTRATIONTYPEGOAL HFRGTG ON HFRGTG.EVENTPRICEID=EVP.ID and HFRGTG.REGTYPEGOALCODE=3


--Event contact information

left outer join dbo.EVENTLOCATION EL ON EV.EVENTLOCATIONID = EL.ID
left outer join dbo.CONSTITUENT EVContact ON EV.EVENTLOCATIONCONTACTID=EVContact.ID 
left outer join dbo.EMAILADDRESS EVCEA ON EVCEA.CONSTITUENTID=EVContact.ID AND EVCEA.ISPRIMARY=1
LEFT outer JOIN dbo.PHONE EVCP ON EVCP.CONSTITUENTID=EVContact.ID AND EVCP.ISPRIMARY=1

--Revenue information

left outer join dbo.REGISTRANTDONATION RD ON RD.REGISTRANTID=RG.ID
left outer join dbo.REVENUE R on R.CONSTITUENTID=RG.CONSTITUENTID --and R.APPEALID=EV.APPEALID  

left outer join dbo.REVENUESPLIT RS ON R.ID = RS.REVENUEID AND RS.TYPE = 'EVENT REGISTRATION'
left outer join dbo.REVENUEPAYMENTMETHOD RM ON RM.REVENUEID=R.ID 

--Treebute information

LEFT outer join dbo.REVENUETRIBUTE RT ON RT.REVENUEID=R.ID 
left outer join dbo.TRIBUTE TR ON TR.ID=RT.TRIBUTEID 
LEFT OUTER JOIN dbo.CONSTITUENT CTR ON CTR.ID=TR.TRIBUTEEID
left outer join dbo.TRIBUTETYPECODE TRC ON TRC.ID=TR.TRIBUTETYPECODEID
left outer join dbo.TRIBUTEEXTENSION TRE ON TRE.TRIBUTEID=TR.ID 

--PageUrl 

left outer join dbo.FAFEVENTPAGETEMPLATE IPU ON IPU.EVENTID=EV.ID AND IPU.PAGECATEGORYCODE=1
left outer join dbo.FAFEVENTPAGETEMPLATE TPU ON TPU.EVENTID=EV.ID AND IPU.PAGECATEGORYCODE=2
left outer join dbo.FAFEVENTPAGETEMPLATE HPU ON HPU.EVENTID=EV.ID AND IPU.PAGECATEGORYCODE=3
left outer join dbo.FAFEVENTPAGETEMPLATE CPU ON CPU.EVENTID=EV.ID AND IPU.PAGECATEGORYCODE=4

--Individual

left outer join dbo.REGISTRANTEXTENSION RGE ON RGE.REGISTRANTID=RG.ID  

--team?household

left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C0.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

--Company

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) 
and (TFT.ID = @TEAMID or @TEAMID is null)