USP_DATAFORMTEMPLATE_VIEW_FAFGROUPREGISTRATIONSUMMARY

The load procedure used by the view dataform template "FAF Group Registration Summary"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PICTURE	varbinary	INOUT	PICTURE
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@GROUPNAME	nvarchar(100)	INOUT	Group name
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@WEBSITESTATUS	bit	INOUT	WEBSITESTATUS
@STATUS	nvarchar(20)	INOUT	Status
@PARTICIPANTURL	nvarchar(max)	INOUT	PARTICIPANTURL
@DATEREGISTERED	datetime	INOUT	Date registered
@TOTALFUNDRAISING	money	INOUT	Total fundraising
@TOTALFUNDRAISINGGOAL	money	INOUT	Total fundraising goal
@GROUPTYPE	tinyint	INOUT	GROUPTYPE
@GROUPVANITYID	int	INOUT	GROUPVANITYID
@LEADERS	nvarchar(250)	INOUT	Leaders
@LEADERIDS	nvarchar(500)	INOUT	LEADERIDS
@CURRENTGROUPTYPENAME	nvarchar(100)	INOUT
@GROUPFUNDRAISINGHISTORYNAME	nvarchar(100)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFGROUPREGISTRATIONSUMMARY
(
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,        
        @PICTURE varbinary(max) = null output,
    @ISORGANIZATION bit = null output,
        @GROUPNAME nvarchar(100) = null output,
        @CONSTITUENTID uniqueidentifier = null output,    
    @WEBSITESTATUS bit = null output,
    @STATUS nvarchar(20) = null output,
    @PARTICIPANTURL nvarchar(max) = null output,
    @DATEREGISTERED datetime = null output,
    @TOTALFUNDRAISING money = null output,
    @TOTALFUNDRAISINGGOAL money = null output,
    --@TARGETFUNDRAISINGGOAL money = null output,
    @GROUPTYPE tinyint = null output,
    @GROUPVANITYID int = null output,
    @LEADERS nvarchar(250) = null output,
    @LEADERIDS nvarchar(500) = null output,
    @CURRENTGROUPTYPENAME nvarchar(100) = null output,
    @GROUPFUNDRAISINGHISTORYNAME nvarchar(100) = null output
    )
    as
    set nocount on;
    set @DATALOADED = 0;
  DECLARE @EVENTID uniqueidentifier = null,
    @CONSTITUENTSEQUENCEID int,
    @CURRENTGROUPTYPE tinyint
    select
        @DATALOADED = 1,        
        @CONSTITUENTID = TX.TEAMCONSTITUENTID,
        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
    @EVENTID = EVENT.ID,
    @DATEREGISTERED = TFT.DATEADDED,
    @CONSTITUENTSEQUENCEID = CONSTITUENT.SEQUENCEID,
    @CURRENTGROUPTYPE = TX.TYPECODE,
    @GROUPTYPE = TX2.TYPECODE,
    @GROUPNAME = TFT2.NAME,
    @WEBSITESTATUS = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(TX.EVENTID),
    @STATUS = TX.STATUS,
    @TOTALFUNDRAISING = case tx.TYPECODE when  1 then dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TFT.ID, TX.EVENTID)
              when 2 then dbo.UFN_REVENUE_GETCOMPANYRAISEDTOTAL(TFT.ID, TX.EVENTID)
              when 3 then dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID, TX.EVENTID) 
              end,
    --@TARGETFUNDRAISINGGOAL = TX.TARGETFUNDRAISINGGOAL,
    @TOTALFUNDRAISINGGOAL = TFT.GOAL,
    @CURRENTGROUPTYPENAME = TX.TYPE,
    @GROUPFUNDRAISINGHISTORYNAME = CONSTITUENT.KEYNAME
    from dbo.TEAMFUNDRAISINGTEAM TFT
    join dbo.TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    left join dbo.TEAMFUNDRAISINGTEAM TFT2 on TFT.PARENTTEAMID = TFT2.ID
    left join dbo.TEAMEXTENSION TX2 ON TFT2.ID = TX2.TEAMFUNDRAISINGTEAMID
        left join dbo.EVENT on TX.EVENTID = EVENT.ID
        left join dbo.CONSTITUENT on TX.TEAMCONSTITUENTID = CONSTITUENT.ID
    where TFT.ID = @ID;

  if @WEBSITESTATUS = 1  
  BEGIN          
    set @PARTICIPANTURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@ID, @EVENTID, @CURRENTGROUPTYPE)
  END  

  select @GROUPVANITYID = vURL.ID from VanityURL vURL
    join FAFFRIENDLYURLPARAMS vFAF on vFAF.SEQUENCEID = vURL.RealmID and vURL.RealmTypeID = 7 and vFAF.PAGETYPE = @CURRENTGROUPTYPE
    where PARTICIPANTID = @ID

  SELECT @LEADERS = dbo.UDA_BUILDLIST(C.NAME), @LEADERIDS = dbo.UDA_BUILDLIST(C.ID)
      FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC 
      LEFT OUTER JOIN dbo.CONSTITUENT C ON C.ID = TC.CONSTITUENTID
      WHERE TC.TEAMFUNDRAISINGTEAMID = @ID
      GROUP BY TC.TEAMFUNDRAISINGTEAMID