USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPEVENTPROFILE
The load procedure used by the view dataform template "Constituent Group Event 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. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @NAME | nvarchar(205) | INOUT | Name |
| @STARTDATE | datetime | INOUT | Event date |
| @ENDDATE | datetime | INOUT | Event date |
| @LOCATION | nvarchar(100) | INOUT | Location |
| @LOCATIONCONTACT | nvarchar(124) | INOUT | Contact |
| @EVENTLOCATIONCONTACTID | uniqueidentifier | INOUT | EVENTLOCATIONCONTACTID |
| @CAPACITY | int | INOUT | Event capacity |
| @WILLATTENDCOUNT | int | INOUT | Will attend |
| @WILLNOTATTENDCOUNT | int | INOUT | Will not attend |
| @PERCENTOFCAPACITY | decimal(10, 2) | INOUT | Percent of capacity |
| @ATTENDEDCOUNT | int | INOUT | Attended |
| @BUDGETEDEXPENSE | money | INOUT | Expense budget |
| @ACTUALEXPENSE | money | INOUT | Expenses to date |
| @PERCENTOFBUDGET | decimal(10, 2) | INOUT | Percent of budget |
| @SUPPORTINGEVENTSCOUNT | int | INOUT | SUPPORTINGEVENTSCOUNT |
| @MAINEVENTNAME | nvarchar(100) | INOUT | Main Event |
| @ISACTIVE | bit | INOUT | ISACTIVE |
| @SITE | nvarchar(max) | INOUT | Site |
| @COORDINATOR | nvarchar(100) | INOUT | Coordinator |
| @COORDINATORID | uniqueidentifier | INOUT | Coordinator ID |
| @CATEGORY | nvarchar(100) | INOUT | Category |
| @GIFTAIDQUALIFICATIONSTATUS | nvarchar(25) | INOUT | Gift Aid status |
| @INCOMPLETETASKS | int | INOUT | Incomplete tasks |
| @BASECURRENCYID | uniqueidentifier | INOUT | Event Base Currency ID |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPEVENTPROFILE
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@NAME nvarchar(205) = null output,
@STARTDATE datetime = null output,
@ENDDATE datetime = null output,
@LOCATION nvarchar(100) = null output,
@LOCATIONCONTACT nvarchar(124) = null output,
@EVENTLOCATIONCONTACTID uniqueidentifier = null output,
@CAPACITY int = null output,
@WILLATTENDCOUNT int = null output,
@WILLNOTATTENDCOUNT int = null output,
@PERCENTOFCAPACITY decimal(10, 2) = null output,
@ATTENDEDCOUNT int = null output,
@BUDGETEDEXPENSE money = null output,
@ACTUALEXPENSE money = null output,
@PERCENTOFBUDGET decimal(10, 2) = null output,
@SUPPORTINGEVENTSCOUNT int = null output,
@MAINEVENTNAME nvarchar(100) = null output,
@ISACTIVE bit = null output,
@SITE nvarchar(max) = null output,
@COORDINATOR nvarchar(100) = null output,
@COORDINATORID uniqueidentifier = null output,
@CATEGORY nvarchar(100) = null output,
@GIFTAIDQUALIFICATIONSTATUS nvarchar(25) = null output,
@INCOMPLETETASKS integer = null output,
@BASECURRENCYID uniqueidentifier = null output
)
as
set nocount on;
declare @EVENTID uniqueidentifier;
declare @GROUPID uniqueidentifier;
select @EVENTID = EVENTID,
@GROUPID = CONSTITUENTID
from dbo.EVENTCOORDINATOR
where ID = @ID;
set @DATALOADED = 0;
with [CTE] as
(
select
EVENT.NAME,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.EVENTLOCATIONID,
EVENT.EVENTLOCATIONCONTACTID,
EVENT.CAPACITY,
coalesce((select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENT.ID and REGISTRANT.WILLNOTATTEND = 0), 0) as [WILLATTEND],
coalesce((select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENT.ID and REGISTRANT.WILLNOTATTEND = 1), 0) as [WILLNOTATTEND],
coalesce((select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENT.ID and REGISTRANT.ATTENDED = 1), 0) as [ATTENDED],
coalesce((select count([SUPPORTINGEVENT].ID) from dbo.EVENT as [SUPPORTINGEVENT] where [SUPPORTINGEVENT].MAINEVENTID = EVENT.ID), 0) as [SUPPORTINGEVENTSCOUNT],
EVENT.MAINEVENTID,
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],
EVENTCATEGORYCODE.DESCRIPTION,
EVENT.ID,
EVENT.BASECURRENCYID
from dbo.EVENT
left join dbo.EVENTCATEGORYCODE ON EVENT.EVENTCATEGORYCODEID = EVENTCATEGORYCODE.ID
where EVENT.ID = @EVENTID
)
select
@DATALOADED = 1,
@NAME = [CTE].NAME,
@STARTDATE = [CTE].STARTDATE,
@ENDDATE = [CTE].ENDDATE,
@LOCATION = dbo.UFN_EVENTLOCATION_GETINFORMATION([CTE].EVENTLOCATIONID),
@LOCATIONCONTACT = dbo.UFN_CONSTITUENT_BUILDNAME([CTE].EVENTLOCATIONCONTACTID),
@EVENTLOCATIONCONTACTID = [CTE].EVENTLOCATIONCONTACTID,
@CAPACITY = [CTE].CAPACITY,
@WILLATTENDCOUNT = [CTE].WILLATTEND,
@WILLNOTATTENDCOUNT = [CTE].WILLNOTATTEND,
@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,
@ATTENDEDCOUNT = [CTE].ATTENDED,
@SUPPORTINGEVENTSCOUNT = [CTE].SUPPORTINGEVENTSCOUNT,
@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,
@SITE = (select
dbo.UDA_BUILDLIST(SITE.NAME)
FROM
dbo.SITE
inner join dbo.EVENTSITE ON EVENTSITE.SITEID = SITE.ID
where
EVENTSITE.EVENTID = @EVENTID
),
@COORDINATORID = [CTE].COORDINATORID,
@COORDINATOR = coalesce(dbo.UFN_CONSTITUENT_BUILDNAME([CTE].COORDINATORID), N''),
@CATEGORY = [CTE].DESCRIPTION,
@INCOMPLETETASKS = dbo.UFN_GROUP_GETINCOMPLETEEVENTTASKCOUNT(@EVENTID, @GROUPID),
@BASECURRENCYID = [CTE].BASECURRENCYID
from [CTE];
--Gift Aid is for UK only
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
begin
set @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_EVENTQUALIFICATIONSTATUS(@EVENTID)
end
return 0;