USP_DATAFORMTEMPLATE_VIEW_EVENTSUMARRYDETAIL_2

The load procedure used by the view dataform template "Event Summary Detail View Form 2"

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.
@NAME	nvarchar(205)	INOUT	Name
@STARTDATE	date	INOUT	Event date
@ENDDATE	date	INOUT	Event date
@LOCATION	nvarchar(100)	INOUT	Location
@LOCATIONCONTACT	nvarchar(100)	INOUT	Contact
@LOCATIONCONTACTID	uniqueidentifier	INOUT	LOCATIONCONTACTID
@CAPACITY	int	INOUT	Event capacity
@WILLATTEND	int	INOUT	Will attend
@WILLNOTATTEND	int	INOUT	Will not attend
@PERCENTOFCAPACITY	decimal(10, 2)	INOUT	Percent of capacity
@BUDGETEDEXPENSE	money	INOUT	Expense budget
@ACTUALEXPENSE	money	INOUT	Expense total
@PERCENTOFBUDGET	decimal(10, 2)	INOUT	Percent of budget
@ISACTIVE	bit	INOUT	ISACTIVE
@COORDINATOR	nvarchar(100)	INOUT	Coordinator
@COORDINATORID	uniqueidentifier	INOUT	Coordinator ID
@ATTENDED	int	INOUT	Attended

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTSUMARRYDETAIL_2
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(205) = null output,
                    @STARTDATE date = null output,
                    @ENDDATE date = null output,
                    @LOCATION nvarchar(100) = null output,
                    @LOCATIONCONTACT nvarchar(100) = null output,
                    @LOCATIONCONTACTID uniqueidentifier = null output, 
                    @CAPACITY int = null output,
                    @WILLATTEND int = null output,
                    @WILLNOTATTEND int = null output,
                    @PERCENTOFCAPACITY decimal(10, 2) = null output,
                    @BUDGETEDEXPENSE money = null output,
                    @ACTUALEXPENSE money = null output,
                    @PERCENTOFBUDGET decimal(10, 2) = null output,
                    @ISACTIVE bit = null output,
                    @COORDINATOR nvarchar(100) = null output,
                    @COORDINATORID uniqueidentifier = null output,
                    @ATTENDED int = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                with [CTE] as
                (
                    select
                        EVENT.NAME,
                        EVENT.STARTDATE,
                        EVENT.ENDDATE,
                        dbo.UFN_EVENTLOCATION_GETINFORMATION(EVENT.EVENTLOCATIONID) as [LOCATION],
                        dbo.UFN_CONSTITUENT_BUILDNAME(EVENT.EVENTLOCATIONCONTACTID) as [LOCATIONCONTACT],
                        EVENT.EVENTLOCATIONCONTACTID as [LOCATIONCONTACTID],
                        EVENT.CAPACITY,
                        coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and WILLNOTATTEND = 0), 0) as [WILLATTEND],
                        coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and WILLNOTATTEND = 1), 0) as [WILLNOTATTEND],
                        coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and ATTENDED = 1), 0) as [ATTENDED],
                        coalesce((select sum(EVENTEXPENSE.BUDGETEDAMOUNT) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),0) as [BUDGETEDAMOUNT],
                        coalesce((select sum(EVENTEXPENSE.ACTUALAMOUNT) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),0) as [ACTUALEXPENSE],
                        EVENT.ISACTIVE,
                        (select top 1 EVENTCOORDINATOR.CONSTITUENTID from dbo.EVENTCOORDINATOR where EVENTID = EVENT.ID order by dbo.UFN_CONSTITUENT_BUILDNAME(EVENTCOORDINATOR.CONSTITUENTID)) as [COORDINATORID]
                    from dbo.EVENT    
                    where EVENT.ID = @ID
                )
                select
                    @DATALOADED = 1,
                    @NAME = [CTE].NAME,
                    @STARTDATE = [CTE].STARTDATE,
                    @ENDDATE = [CTE].ENDDATE,
                    @LOCATION = [CTE].LOCATION,
                    @LOCATIONCONTACT = [CTE].LOCATIONCONTACT,
                    @LOCATIONCONTACTID = [CTE].LOCATIONCONTACTID,
                    @CAPACITY = [CTE].CAPACITY,
                    @WILLATTEND = [CTE].WILLATTEND,
                    @WILLNOTATTEND = [CTE].WILLNOTATTEND,
                    @ATTENDED = [CTE].ATTENDED,
                    @PERCENTOFCAPACITY = case when [CTE].CAPACITY = 0 then cast(0 as decimal(10,2)) else cast(cast([CTE].WILLATTEND as decimal(10,2)) / cast([CTE].CAPACITY as decimal(10,2)) as decimal(10,2)) end,
                    @BUDGETEDEXPENSE = [CTE].BUDGETEDAMOUNT,
                    @ACTUALEXPENSE = [CTE].ACTUALEXPENSE,
                    @PERCENTOFBUDGET = case when [CTE].BUDGETEDAMOUNT = 0 then cast(0 as decimal(10,2)) else cast(cast([CTE].ACTUALEXPENSE as decimal(10,2)) / cast([CTE].BUDGETEDAMOUNT as decimal(10,2)) as decimal(10,2)) end,
                    @ISACTIVE = [CTE].ISACTIVE,
                    @COORDINATORID = [CTE].COORDINATORID,
                    @COORDINATOR = coalesce(dbo.UFN_CONSTITUENT_BUILDNAME([CTE].COORDINATORID), N'')
                from [CTE]

                return 0;