USP_DATALIST_EVENTBUDGETANDEXPENSES

Lists budget and expense information for a given event to be used by the Event Budget and Expenses widget.

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Event
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@INCLUDESUBEVENTS	bit	IN	Include sub-events
@HASSUBEVENTS	bit	INOUT	Has sub events

Definition

Copy



CREATE procedure dbo.USP_DATALIST_EVENTBUDGETANDEXPENSES
(
    @EVENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null, 
    @INCLUDESUBEVENTS bit = 0,
    @HASSUBEVENTS bit = null output
)
as
    set nocount on;

    declare @EVENTS table (ID uniqueidentifier);

    declare @EVENTHIERARCHYPATH hierarchyid;

    select
        @EVENTHIERARCHYPATH = HIERARCHYPATH
    from dbo.EVENTHIERARCHY
    where EVENTHIERARCHY.ID = @EVENTID;

    if @INCLUDESUBEVENTS = 1 and @EVENTHIERARCHYPATH is not null
        insert into @EVENTS
            select
                ID
            from dbo.EVENTHIERARCHY
            where EVENTHIERARCHY.HIERARCHYPATH.IsDescendantOf(@EVENTHIERARCHYPATH) = 1;        
    else
        insert into @EVENTS (ID) values (@EVENTID);

    -- Determine if the current event has sub events

    select
        @HASSUBEVENTS = case when count(ID) > 0 then 1 else 0 end
    from dbo.EVENTHIERARCHY
    where EVENTHIERARCHY.HIERARCHYPATH.IsDescendantOf(@EVENTHIERARCHYPATH) = 1
        and EVENTHIERARCHY.ID <> @EVENTID;    

    select
        'Paid' as AMOUNTCATEGORY,
        sum(EVENTEXPENSE.AMOUNTPAID) as AMOUNT        
    from @EVENTS events
    inner join dbo.EVENTEXPENSE
        on events.ID = EVENTEXPENSE.EVENTID
    where dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, events.ID) = 1

    union all

    select
        'Agreed' as AMOUNTCATEGORY,
        sum(EVENTEXPENSE.ACTUALAMOUNT) as AMOUNTAGREED        
    from @EVENTS events
    inner join dbo.EVENTEXPENSE
        on events.ID = EVENTEXPENSE.EVENTID
    where dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, events.ID) = 1

    union all

    select
        'Budgeted' as AMOUNTCATEGORY,
        sum(EVENTEXPENSE.BUDGETEDAMOUNT) as AMOUNT            
    from @EVENTS events
    inner join dbo.EVENTEXPENSE
        on events.ID = EVENTEXPENSE.EVENTID
    where dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, events.ID) = 1;