USP_DATAFORMTEMPLATE_VIEW_EVENTPROFILE_3

The load procedure used by the view dataform template "Event Summary 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(700)	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	Agreed expenses
@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(700)	INOUT	Coordinator
@COORDINATORID	uniqueidentifier	INOUT	Coordinator ID
@CATEGORY	nvarchar(100)	INOUT	Category
@GIFTAIDQUALIFICATIONSTATUS	nvarchar(25)	INOUT	Gift Aid status
@LOCATIONROOM	nvarchar(100)	INOUT	Room/Unit
@HASCOORDINATORS	bit	INOUT	HASCOORDINATORS
@LOOKUPID	nvarchar(36)	INOUT	Lookup ID
@BUDGETEDEXPENSESUBEVENTS	money	INOUT	Expense budget
@ACTUALEXPENSESUBEVENTS	money	INOUT	Agreed expenses
@PERCENTOFBUDGETSUBEVENTS	decimal(10, 2)	INOUT	Percent of budget
@CAPACITYSUBEVENTS	int	INOUT	Event capacity
@WILLATTENDCOUNTSUBEVENTS	int	INOUT	Will attend
@WILLNOTATTENDCOUNTSUBEVENTS	int	INOUT	Will not attend
@ATTENDEDCOUNTSUBEVENTS	int	INOUT	Attended
@PERCENTOFCAPACITYSUBEVENTS	decimal(10, 2)	INOUT	Percent of capacity
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISMULTILEVEL	bit	INOUT	Is multi-level event
@AMOUNTPAID	money	INOUT	Amount paid
@AMOUNTPAIDSUBEVENTS	money	INOUT	Amount paid
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@STARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@HASEXPENSES	bit	INOUT
@SUBEVENTHASEXPENSES	bit	INOUT
@HASREGISTRANTSANDOPTIONS	bit	INOUT
@SUBEVENTHASREGISTRANTSANDOPTIONS	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTPROFILE_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(700) = 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(700) = null output,
                    @COORDINATORID uniqueidentifier = null output,
                    @CATEGORY nvarchar(100) = null output,
                    @GIFTAIDQUALIFICATIONSTATUS nvarchar(25) = null output,
                    @LOCATIONROOM nvarchar(100) = null output,
                    @HASCOORDINATORS bit = null output,
                    @LOOKUPID nvarchar(36) = null output,
                    @BUDGETEDEXPENSESUBEVENTS money = null output,
                    @ACTUALEXPENSESUBEVENTS money = null output,
                    @PERCENTOFBUDGETSUBEVENTS decimal(10, 2) = null output,
                    @CAPACITYSUBEVENTS int = null output,
                    @WILLATTENDCOUNTSUBEVENTS int = null output,
                    @WILLNOTATTENDCOUNTSUBEVENTS int = null output,
                    @ATTENDEDCOUNTSUBEVENTS int = null output,
                    @PERCENTOFCAPACITYSUBEVENTS decimal(10, 2) = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @ISMULTILEVEL bit = null output,
                    @AMOUNTPAID money = null output,
                    @AMOUNTPAIDSUBEVENTS money = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @STARTTIME dbo.UDT_HOURMINUTE = null output,
                    @ENDTIME dbo.UDT_HOURMINUTE = null output,
                    @HASEXPENSES bit = null output,
                    @SUBEVENTHASEXPENSES bit = null output,
                    @HASREGISTRANTSANDOPTIONS bit = null output,
                    @SUBEVENTHASREGISTRANTSANDOPTIONS bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;                    

                    create table #CHILDEVENTSWITHSITEACCESS (ID uniqueidentifier);
                    insert into #CHILDEVENTSWITHSITEACCESS select ID from dbo.UFN_CHILDEVENTSWITHSITEACCESS(@ID, @CURRENTAPPUSERID);

                    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                    declare @BASECURRENCYEXCHANGERATEID uniqueidentifier;

                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    declare @NONCANCELLEDREGISTRANTS table (
                        ID uniqueidentifier,
                        WILLNOTATTEND bit,
                        ATTENDED bit
                    );

                    insert into @NONCANCELLEDREGISTRANTS (ID, WILLNOTATTEND, ATTENDED)    
                    select REGISTRANT.ID, REGISTRANT.WILLNOTATTEND, REGISTRANT.ATTENDED
                    from dbo.REGISTRANT
                    where
                        REGISTRANT.EVENTID = @ID
                        and REGISTRANT.ISCANCELLED = 0;

                    with [CTE] as 
                    (
                        select
                            EVENT.NAME,
                            EVENT.STARTDATE,
                            EVENT.ENDDATE,
                            EVENT.EVENTLOCATIONID,
                            (select EVENTLOCATIONROOM.NAME from dbo.EVENTLOCATIONROOM where EVENTLOCATIONROOM.ID = EVENT.EVENTLOCATIONROOMID) EVENTLOCATIONROOM,
                            EVENT.EVENTLOCATIONCONTACTID,
                            EVENT.CAPACITY,
                            (select count(ID) from @NONCANCELLEDREGISTRANTS where WILLNOTATTEND = 0) as [WILLATTEND],
                            (select count(ID) from @NONCANCELLEDREGISTRANTS where WILLNOTATTEND = 1) as [WILLNOTATTEND],
                            (select count(ID) from @NONCANCELLEDREGISTRANTS where ATTENDED = 1) as [ATTENDED],
                            coalesce((select count([SUPPORTINGEVENT].ID) from dbo.EVENT as [SUPPORTINGEVENT] where [SUPPORTINGEVENT].MAINEVENTID = EVENT.ID), 0) as [SUPPORTINGEVENTSCOUNT],
                            EVENT.MAINEVENTID,

                            EVENT.ISACTIVE,
                            (
                     select top 1 EVENTCOORDINATOR.CONSTITUENTID
                                from dbo.EVENTCOORDINATOR
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENTCOORDINATOR.CONSTITUENTID) NF
                                where EVENTID = EVENT.ID
                                order by EVENTCOORDINATOR.ISPRIMARY desc, NF.NAME
                            ) as [COORDINATORID],
                            EVENTCATEGORYCODE.DESCRIPTION,
                            EVENT.ID,
                            EVENT.LOOKUPID,
                            EVENT.BASECURRENCYID,
                            EVENT.STARTTIME,
                            EVENT.ENDTIME
                        from dbo.EVENT
                        left join dbo.EVENTCATEGORYCODE ON EVENT.EVENTCATEGORYCODEID = EVENTCATEGORYCODE.ID
                        where EVENT.ID = @ID                                
                    )
                    select
                        @DATALOADED = 1,
                        @NAME = [CTE].NAME,
                        @STARTDATE = [CTE].STARTDATE,
                        @ENDDATE = [CTE].ENDDATE,
                        @LOCATION = dbo.UFN_EVENTLOCATION_GETINFORMATION([CTE].EVENTLOCATIONID),
                        @LOCATIONROOM = [CTE].EVENTLOCATIONROOM,
                        @LOCATIONCONTACT = LOCATIONCONTACT_NF.NAME,
                        @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,
                        @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 = @ID
                                ),
                        @COORDINATORID = [CTE].COORDINATORID,
                        @COORDINATOR = coalesce(NF.NAME, N''),
                        @CATEGORY = [CTE].DESCRIPTION,
                        @LOOKUPID = [CTE].LOOKUPID,
                        @BASECURRENCYID = [CTE].BASECURRENCYID,
                        @STARTTIME = [CTE].STARTTIME,
                        @ENDTIME = [CTE].ENDTIME,
                        @HASEXPENSES = isnull(EVENTMANAGEMENTOPTIONS.HASEXPENSES, 1),
                        @HASREGISTRANTSANDOPTIONS = isnull(EVENTMANAGEMENTOPTIONS.HASREGISTRANTSANDOPTIONS, 1)
                    from [CTE]
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME([CTE].COORDINATORID) NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME([CTE].EVENTLOCATIONCONTACTID) LOCATIONCONTACT_NF
                        left join dbo.EVENTMANAGEMENTOPTIONS on EVENTMANAGEMENTOPTIONS.EVENTID = [CTE].ID;             

                    select 
                        @AMOUNTPAID = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETAMOUNTPAIDINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0),
                        @BUDGETEDEXPENSE = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETBUDGETEDAMOUNTINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0),
                        @ACTUALEXPENSE = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETACTUALAMOUNTINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0)
                    from dbo.EVENTEXPENSE
                    where EVENTEXPENSE.EVENTID = @ID;

                    select @PERCENTOFBUDGET = 
                    case 
                        when @BUDGETEDEXPENSE = 0 
                        then cast(0 as decimal(10,2)) 
                        else cast(cast(@ACTUALEXPENSE as decimal(10,2)) / cast(@BUDGETEDEXPENSE as decimal(10,2)) as decimal(10,2)) 
                    end;

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

                    --Gift Aid is for UK only

                    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                    begin
                        set @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_EVENTQUALIFICATIONSTATUS(@ID)
                    end

                    select 
                        @AMOUNTPAIDSUBEVENTS = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETAMOUNTPAIDINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0),
                        @BUDGETEDEXPENSESUBEVENTS = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETBUDGETEDAMOUNTINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0),
                        @ACTUALEXPENSESUBEVENTS = coalesce(sum(dbo.UFN_EVENTEXPENSE_GETACTUALAMOUNTINCURRENCY(EVENTEXPENSE.ID, @BASECURRENCYID)),0)
                    from 
                    #CHILDEVENTSWITHSITEACCESS EVENTS
                    left join dbo.EVENTEXPENSE on EVENTEXPENSE.EVENTID = EVENTS.ID

                    set @SUBEVENTHASEXPENSES = 0;
                    if exists (select 1 from #CHILDEVENTSWITHSITEACCESS EVENTS inner join dbo.EVENTMANAGEMENTOPTIONS on EVENTS.ID = EVENTMANAGEMENTOPTIONS.EVENTID where EVENTS.ID <> @ID and EVENTMANAGEMENTOPTIONS.HASEXPENSES = 1)
                        set @SUBEVENTHASEXPENSES = 1;

                    set @SUBEVENTHASREGISTRANTSANDOPTIONS = 0;
                    if exists (select 1 from #CHILDEVENTSWITHSITEACCESS EVENTS inner join dbo.EVENTMANAGEMENTOPTIONS on EVENTS.ID = EVENTMANAGEMENTOPTIONS.EVENTID where EVENTS.ID <> @ID and EVENTMANAGEMENTOPTIONS.HASREGISTRANTSANDOPTIONS = 1)
                        set @SUBEVENTHASREGISTRANTSANDOPTIONS = 1;


                    select @PERCENTOFBUDGETSUBEVENTS = 
                    case 
                        when @BUDGETEDEXPENSESUBEVENTS = 0 
                        then cast(0 as decimal(10,2)) 
                        else cast(@ACTUALEXPENSESUBEVENTS as decimal(10,2)) / cast(@BUDGETEDEXPENSESUBEVENTS as decimal(10,2)) 
                    end;

                    set @WILLATTENDCOUNTSUBEVENTS = 0;
                    set @WILLNOTATTENDCOUNTSUBEVENTS = 0;
                    set @ATTENDEDCOUNTSUBEVENTS = 0;

                    select @CAPACITYSUBEVENTS = coalesce(sum(EVENT.CAPACITY),0)
                    from #CHILDEVENTSWITHSITEACCESS EVENTS
                    inner join dbo.EVENT on EVENTS.ID = EVENT.ID

                    select  
                        @WILLATTENDCOUNTSUBEVENTS += (select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENTS.ID and REGISTRANT.WILLNOTATTEND = 0 and dbo.[UFN_REGISTRANT_ISCANCELLED]([REGISTRANT].[ID]) = 0),
                        @WILLNOTATTENDCOUNTSUBEVENTS += (select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENTS.ID and REGISTRANT.WILLNOTATTEND = 1 and dbo.[UFN_REGISTRANT_ISCANCELLED]([REGISTRANT].[ID]) = 0),
                        @ATTENDEDCOUNTSUBEVENTS += (select count(REGISTRANT.ID) from dbo.REGISTRANT where REGISTRANT.EVENTID = EVENTS.ID and REGISTRANT.ATTENDED = 1 and dbo.[UFN_REGISTRANT_ISCANCELLED]([REGISTRANT].[ID]) = 0),
                        @PERCENTOFCAPACITYSUBEVENTS = case when @CAPACITYSUBEVENTS = 0 then cast(0 as decimal(10,2)) else cast(@WILLATTENDCOUNTSUBEVENTS as decimal(10,2)) / cast(@CAPACITYSUBEVENTS as decimal(10,2)) end
                    from #CHILDEVENTSWITHSITEACCESS EVENTS

                    set @ISMULTILEVEL = 0
                    if exists(select 1 from dbo.EVENTMANAGEMENTOPTIONS where EVENTID = @ID)
                    begin
                        if exists(select ID from dbo.UFN_EVENT_GETALLEVENTSINHIERARCHY(@ID) where ID != @ID)
                            set @ISMULTILEVEL = 1;
                    end

                    drop table #CHILDEVENTSWITHSITEACCESS;
                    return 0;