USP_DATAFORMTEMPLATE_VIEW_EVENTSUMARRYDETAIL_3

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

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
@CATEGORY	nvarchar(100)	INOUT	Category
@LOCATIONROOM	nvarchar(100)	INOUT	Room/Unit
@HASCOORDINATORS	bit	INOUT	HASCOORDINATORS
@BASECURRENCYID	uniqueidentifier	INOUT	BASECURRENCYID

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTSUMARRYDETAIL_3
                (
                    @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,
                    @CATEGORY nvarchar(100) = null output,
                    @LOCATIONROOM nvarchar(100) = null output,
                    @HASCOORDINATORS bit = null output,
                    @BASECURRENCYID uniqueidentifier = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                with [CTE] as
                (
                    select
                        case when EVENTMANAGEMENTLEVELINSTANCE.NAME = '' then EVENT.NAME else EVENT.NAME + ' - ' + EVENTMANAGEMENTLEVELINSTANCE.NAME end as NAME,
                        EVENT.STARTDATE,
                        EVENT.ENDDATE,
                        dbo.UFN_EVENTLOCATION_GETINFORMATION(EVENT.EVENTLOCATIONID) as [LOCATION],
                        (select EVENTLOCATIONROOM.NAME from dbo.EVENTLOCATIONROOM where EVENTLOCATIONROOM.ID = EVENT.EVENTLOCATIONROOMID) as [EVENTLOCATIONROOM],
                        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 and REGISTRANT.ISCANCELLED = 0), 0) as [WILLATTEND],
                        coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and WILLNOTATTEND = 1 and REGISTRANT.ISCANCELLED = 0), 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 EVENTCOORDINATOR.ISPRIMARY desc, dbo.UFN_CONSTITUENT_BUILDNAME(EVENTCOORDINATOR.CONSTITUENTID)) as [COORDINATORID],
                        EVENTCATEGORYCODE.DESCRIPTION as [DESCRIPTION],
                        EVENT.BASECURRENCYID
                    from dbo.EVENT    
                    left join dbo.EVENTCATEGORYCODE ON EVENT.EVENTCATEGORYCODEID=EVENTCATEGORYCODE.ID
                    left join dbo.EVENTMANAGEMENTOPTIONS on EVENT.ID = EVENTMANAGEMENTOPTIONS.EVENTID
                    left join dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID = EVENTMANAGEMENTLEVELINSTANCE.ID
                    where EVENT.ID = @ID
                )
                select
                    @DATALOADED = 1,
                    @NAME = [CTE].NAME,
                    @STARTDATE = [CTE].STARTDATE,
                    @ENDDATE = [CTE].ENDDATE,
                    @LOCATION = [CTE].LOCATION,
                    @LOCATIONROOM = [CTE].EVENTLOCATIONROOM,
                    @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''),
                    @CATEGORY = [CTE].DESCRIPTION,
                    @BASECURRENCYID = [CTE].BASECURRENCYID
                    --@CATEGORY='ddd'
                from [CTE]

                set @HASCOORDINATORS = 1;
                if exists(select 1 from dbo.EVENTMANAGEMENTOPTIONS where EVENTID = @ID and HASTASKSANDCOORDINATORS = 0)
                    set @HASCOORDINATORS = 0;

                return 0;