USP_DATAFORMTEMPLATE_VIEW_COMMITTEEPROFILE

The load procedure used by the view dataform template "Committee Profile View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PICTURE	varbinary	INOUT	PICTURE
@CANCOORDINATEEVENTS	bit	INOUT	Can coordinate events
@CANSOLICITREVENUE	bit	INOUT	Can solicit revenue
@GROUPGOALS	xml	INOUT	GROUPGOALS
@GROUPEVENTID	uniqueidentifier	INOUT	GROUPEVENTID
@GROUPEVENTNAME	nvarchar(100)	INOUT	Event
@TOTALRAISED	money	INOUT	Total raised
@GROUPTYPE	nvarchar(300)	INOUT	Group type
@GROUPMEMBERCOUNT	int	INOUT	No. of members
@CAMPAIGNID	uniqueidentifier	INOUT	CAMPAIGNID
@CAMPAIGNNAME	nvarchar(100)	INOUT	Campaign
@CAMPAIGNPOSITIONID_TRANSLATION	nvarchar(100)	INOUT	Position
@CAMPAIGNTASKID_TRANSLATION	nvarchar(100)	INOUT	Task
@BASECURRENCYID	uniqueidentifier	INOUT	BASECURRENCYID

Definition

Copy



            CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_COMMITTEEPROFILE]

                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier=null,
                @DATALOADED bit = 0 output,
                @PICTURE varbinary(max) = null output,
                @CANCOORDINATEEVENTS bit = null output,
                @CANSOLICITREVENUE bit = null output,
                @GROUPGOALS xml = null output,
                @GROUPEVENTID uniqueidentifier = null output,
                @GROUPEVENTNAME nvarchar(100) = null output,
                @TOTALRAISED money = null output,
                @GROUPTYPE nvarchar(300) = null output,
                @GROUPMEMBERCOUNT integer = null output,
                @CAMPAIGNID uniqueidentifier = null output,
                @CAMPAIGNNAME nvarchar(100) = null output,
                @CAMPAIGNPOSITIONID_TRANSLATION nvarchar(100) = null output,
                @CAMPAIGNTASKID_TRANSLATION nvarchar(100) = null output,
                @BASECURRENCYID uniqueidentifier = null output

     as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                set @DATALOADED = 0;

                select
                    @DATALOADED = 1,
                    @PICTURE = CONSTITUENT.PICTURETHUMBNAIL
                from
                    dbo.CONSTITUENT
                where
                    CONSTITUENT.ID = @ID;

                --CONSTIT FIELDS

                select
                    @CANCOORDINATEEVENTS = CANCOORDINATEEVENTS,
                    @CANSOLICITREVENUE = CANSOLICITREVENUE
                from
                    dbo.COMMITTEE
                where
                    COMMITTEE.ID = @ID;

                select top 1
                    @GROUPEVENTID = EVENT.ID,
                    @GROUPEVENTNAME = EVENT.NAME
                from dbo.EVENTCOORDINATOR
                inner join dbo.EVENT
                    on EVENT.ID = EVENTCOORDINATOR.EVENTID
                where CONSTITUENTID = @ID
                order by EVENT.ISACTIVE desc,
                        case when EVENT.ENDDATE >= @CURRENTDATE then 0 else 1 end asc,
                        EVENT.STARTDATE asc,
                        EVENT.NAME asc;

                set @GROUPGOALS = (select top 2
                        ID, 
                        ISFUNDRAISINGGOAL, 
                        GROUPGOALUNITCODEID, 
                        AMOUNT, 
                        NAME, 
                        GOALDATE,
                        BASECURRENCYID
                    from dbo.UFN_GROUPGOAL_DATALIST(@ID,null)
                    order by 
                        DATESORT desc,
                        Case DATESORT When 1 Then GOALDATE When 0 Then null Else GetDate() - GOALDATE End asc
                    for xml raw('ITEM'),type,elements,root('GROUPGOALS'),BINARY BASE64);

                select top 1
                    @CAMPAIGNID = CAMPAIGN.ID,
                    @CAMPAIGNNAME = CAMPAIGN.NAME,
                    @CAMPAIGNPOSITIONID_TRANSLATION = CAMPAIGNFUNDRAISERPOSITIONCODE.DESCRIPTION,
                    @CAMPAIGNTASKID_TRANSLATION = CAMPAIGNFUNDRAISERTASKCODE.DESCRIPTION
                from dbo.CAMPAIGNFUNDRAISER
                inner join dbo.CAMPAIGN
                    on CAMPAIGNFUNDRAISER.CAMPAIGNID = CAMPAIGN.ID
                left join CAMPAIGNFUNDRAISERPOSITIONCODE
                    on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERPOSITIONCODEID = CAMPAIGNFUNDRAISERPOSITIONCODE.ID
                left join CAMPAIGNFUNDRAISERTASKCODE
                    on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERTASKCODEID = CAMPAIGNFUNDRAISERTASKCODE.ID
                where 
                    CAMPAIGNFUNDRAISER.CONSTITUENTID = @ID
                order by 
                    case when CAMPAIGNFUNDRAISER.DATETO >= @CURRENTDATE then 0 else 1 end asc,
                    CAMPAIGNFUNDRAISER.DATEFROM asc,
                    CAMPAIGN.NAME asc;

                set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
                set @TOTALRAISED = dbo.UFN_REVENUESOLICITOR_GETSUM_INCURRENCY(@ID, null, null, @BASECURRENCYID);

                select 
                    @GROUPTYPE = GT.NAME
                from dbo.GROUPDATA G
                inner join dbo.GROUPTYPE GT
                    on GT.ID = G.GROUPTYPEID
                where G.ID = @ID;

                select 
                    @GROUPMEMBERCOUNT = count(GM.ID)
                from dbo.GROUPMEMBER GM
                left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                where GM.GROUPID = @ID
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                  or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                  or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))

                return 0;