USP_DATAFORMTEMPLATE_VIEW_COMMITTEEMEMBERSHIP

The load procedure used by the view dataform template "Committee Membership 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.
@GROUPTYPE	nvarchar(300)	INOUT	Group type
@DESCRIPTION	nvarchar(max)	INOUT	Description
@PRIMARYCONTACTID	uniqueidentifier	INOUT	PRIMARYCONTACTID
@PRIMARYCONTACTNAME	nvarchar(700)	INOUT	Primary contact
@CANCOORDINATEEVENTS	bit	INOUT	Can coordinate events
@CANSOLICITREVENUE	bit	INOUT	Can solicit revenue
@STARTDATE	date	INOUT	STARTDATE
@GROUPEVENTID	uniqueidentifier	INOUT	GROUPEVENTID
@GROUPEVENTNAME	nvarchar(100)	INOUT	Event
@GROUPCAMPAIGNID	uniqueidentifier	INOUT	GROUPCAMPAIGNID
@GROUPCAMPAIGNNAME	nvarchar(100)	INOUT	Campaign
@DATEFROM	date	INOUT	Membership dates:
@DATETO	date	INOUT	End date
@CANSETCOMMITTEEGOALS	bit	INOUT	Can set committee goals

Definition

Copy



            CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_COMMITTEEMEMBERSHIP]

                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier=null,
                @DATALOADED bit = 0 output,
                @GROUPTYPE nvarchar(300) = null output,
                @DESCRIPTION nvarchar(max) = null output,
                @PRIMARYCONTACTID uniqueidentifier = null output,
                @PRIMARYCONTACTNAME nvarchar(700) = null output,
                @CANCOORDINATEEVENTS bit = null output,
                @CANSOLICITREVENUE bit = null output,
                @STARTDATE date = null output,
                @GROUPEVENTID uniqueidentifier = null output,
                @GROUPEVENTNAME nvarchar(100) = null output,
                @GROUPCAMPAIGNID uniqueidentifier = null output,
                @GROUPCAMPAIGNNAME nvarchar(100) = null output,
                @DATEFROM date = null output,
                @DATETO date = null output,
                @CANSETCOMMITTEEGOALS bit = null output
     as
                set nocount on;

                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

                set @DATALOADED = 0;

                declare @GROUPID uniqueidentifier;

                select 
                    @GROUPID = GROUPMEMBER.GROUPID,
                    @DATEFROM = GMDR.DATEFROM,
                    @DATETO = GMDR.DATETO
                from dbo.GROUPMEMBER
                left outer join dbo.GROUPMEMBERDATERANGE GMDR
                    on GROUPMEMBER.ID = GMDR.GROUPMEMBERID
                where GROUPMEMBER.ID = @ID;

                select 
                    @DATALOADED = 1,
                    @GROUPTYPE = GROUPTYPE.NAME,
                    @DESCRIPTION = GROUPDATA.DESCRIPTION,
                    @STARTDATE = GROUPDATA.STARTDATE,
                    @PRIMARYCONTACTID = GROUPMEMBER.MEMBERID,
                    @PRIMARYCONTACTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(GROUPMEMBER.MEMBERID)
                from dbo.GROUPDATA
                inner join dbo.GROUPTYPE
                    on GROUPTYPE.ID = GROUPDATA.GROUPTYPEID
                left join dbo.GROUPMEMBER
                    on GROUPMEMBER.GROUPID = GROUPDATA.ID and GROUPMEMBER.ISPRIMARY = 1
                where GROUPDATA.ID = @GROUPID;

                --COMMITTEE FIELDS

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

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

                select top 1
                    @GROUPCAMPAIGNID = CAMPAIGN.ID,
                    @GROUPCAMPAIGNNAME = CAMPAIGN.NAME
                from dbo.CAMPAIGNFUNDRAISER
                inner join dbo.CAMPAIGN
                    on CAMPAIGN.ID = CAMPAIGNFUNDRAISER.CAMPAIGNID
                where CAMPAIGNFUNDRAISER.CONSTITUENTID = @GROUPID
                order by CAMPAIGN.ISACTIVE desc,
                        CAMPAIGN.NAME asc;



                return 0;