UFN_EVENTCONFLICT_GETEVENTINFO
Gets conflict information for all events in conflict with the supplied information.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @START | datetime | IN | |
| @END | datetime | IN | |
| @LOCATIONS | xml | IN | |
| @RESOURCES | xml | IN | |
| @STAFFRESOURCES | xml | IN | |
| @IGNORERECORDID | uniqueidentifier | IN | |
| @IGNORESUBRECORDID | uniqueidentifier | IN | |
| @IGNORESUPERRECORDID | uniqueidentifier | IN | |
| @OFFSETRESOURCES | xml | IN | |
| @OFFSETSTAFFRESOURCES | xml | IN |
Definition
Copy
CREATE function dbo.UFN_EVENTCONFLICT_GETEVENTINFO
(
@START datetime,
@END datetime,
@LOCATIONS xml,
@RESOURCES xml,
@STAFFRESOURCES xml,
@IGNORERECORDID uniqueidentifier = null,
@IGNORESUBRECORDID uniqueidentifier = null,
@IGNORESUPERRECORDID uniqueidentifier = null,
@OFFSETRESOURCES xml = null,
@OFFSETSTAFFRESOURCES xml = null
)
returns @EVENTINFO table
(
RECORDID uniqueidentifier,
PAGEID uniqueidentifier,
PROGRAMID uniqueidentifier,
NAME nvarchar(100),
STARTDATE datetime,
ENDDATE datetime,
STARTTIME UDT_HOURMINUTE,
ENDTIME UDT_HOURMINUTE,
TIMESPANDISPLAY nvarchar(25),
LOCATIONS nvarchar(500),
RESOURCES nvarchar(500),
STAFFRESOURCES nvarchar(500),
ISLOCATIONCONFLICT bit,
ISRESOURCECONFLICT bit,
ISSTAFFRESOURCECONFLICT bit,
SUBRECORDID uniqueidentifier,
LOCATIONSINCONFLICT nvarchar(500),
RESOURCESINCONFLICT nvarchar(500),
STAFFRESOURCESINCONFLICT nvarchar(500),
RECORDTYPE tinyint,
DISTINCTLOCATIONSINCONFLICT nvarchar(500),
DISTINCTRESOURCESINCONFLICT nvarchar(500),
DISTINCTSTAFFRESOURCESINCONFLICT nvarchar(500)
)
as begin
declare @EVENTINFOTEMP table
(
RECORDID uniqueidentifier,
PAGEID uniqueidentifier,
PROGRAMID uniqueidentifier,
NAME nvarchar(100),
STARTDATE datetime,
ENDDATE datetime,
STARTTIME UDT_HOURMINUTE,
ENDTIME UDT_HOURMINUTE,
TIMESPANDISPLAY nvarchar(25),
LOCATIONS nvarchar(500),
RESOURCES nvarchar(500),
STAFFRESOURCES nvarchar(500),
ISLOCATIONCONFLICT bit,
ISRESOURCECONFLICT bit,
ISSTAFFRESOURCECONFLICT bit,
SUBRECORDID uniqueidentifier,
LOCATIONINCONFLICT uniqueidentifier,
RESOURCEINCONFLICT uniqueidentifier,
RESOURCEINCONFLICTQUANTITY int,
STAFFRESOURCEINCONFLICT uniqueidentifier,
STAFFRESOURCEINCONFLICTQUANTITY int,
RECORDTYPE tinyint
)
-- First load the items in a non-distinct manner so that we can get the distinct resource and location conflicts later
-- Handle events first
insert into @EVENTINFOTEMP
(
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
TIMESPANDISPLAY,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
LOCATIONINCONFLICT,
RESOURCEINCONFLICT,
RESOURCEINCONFLICTQUANTITY,
STAFFRESOURCEINCONFLICT,
STAFFRESOURCEINCONFLICTQUANTITY,
RECORDTYPE
)
select distinct
EVENT.ID,
case when PROGRAMID is null then '9988B807-97B2-434C-8BE1-BBEE6B944B2C' else 'C113696F-8318-4B14-B6DA-54DEE3077995' end,
EVENT.PROGRAMID,
EVENT.NAME,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.STARTTIME,
EVENT.ENDTIME,
dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.STARTTIME) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.ENDTIME),
coalesce(dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID), ''),
coalesce(dbo.UFN_EVENTRESOURCE_GETRESOURCESTRING(EVENT.ID), ''),
coalesce(dbo.UFN_EVENTSTAFFRESOURCE_GETSTAFFRESOURCESTRING(EVENT.ID), ''),
case when LOCATIONCONFLICT.EVENTID is null then 0 else 1 end,
case when RESOURCECONFLICT.EVENTID is null then 0 else 1 end,
case when STAFFRESOURCECONFLICT.EVENTID is null then 0 else 1 end,
LOCATIONCONFLICT.LOCATIONID,
RESOURCECONFLICT.RESOURCEID,
RESOURCECONFLICT.QUANTITYNEEDED,
STAFFRESOURCECONFLICT.VOLUNTEERTYPEID,
STAFFRESOURCECONFLICT.QUANTITYNEEDED,
0
from dbo.EVENT
left outer join dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILS(@START, @END, @LOCATIONS, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 1, 0) as LOCATIONCONFLICT on
EVENT.ID = LOCATIONCONFLICT.EVENTID
left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS(@START, @END, @RESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 1, 0, 0, @OFFSETRESOURCES) as RESOURCECONFLICT on
EVENT.ID = RESOURCECONFLICT.EVENTID
left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS(@START, @END, @STAFFRESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 1, 0, 0, @OFFSETSTAFFRESOURCES) as STAFFRESOURCECONFLICT on
EVENT.ID = STAFFRESOURCECONFLICT.EVENTID
where
(
LOCATIONCONFLICT.EVENTID is not null or
RESOURCECONFLICT.EVENTID is not null or
STAFFRESOURCECONFLICT.EVENTID is not null
) and
(
(
@START >= EVENT.STARTDATETIME and
@START < EVENT.ENDDATETIME
) or
(
@END > EVENT.STARTDATETIME and
@END <= EVENT.ENDDATETIME
) or
(
@START < EVENT.STARTDATETIME and
@END > EVENT.ENDDATETIME
)
)
-- Handle itinerary items
insert into @EVENTINFOTEMP
(
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
TIMESPANDISPLAY,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
SUBRECORDID,
LOCATIONINCONFLICT,
RESOURCEINCONFLICT,
RESOURCEINCONFLICTQUANTITY,
STAFFRESOURCEINCONFLICT,
STAFFRESOURCEINCONFLICTQUANTITY,
RECORDTYPE
)
select distinct
ITINERARY.ID,
'7cc8e595-84d1-401c-b4f9-05a0361afde2',
ITINERARYITEM.PROGRAMID,
ITINERARY.NAME + ' - ' + ITINERARYITEM.NAME,
ITINERARYITEM.STARTDATE,
ITINERARYITEM.ENDDATE,
ITINERARYITEM.STARTTIME,
ITINERARYITEM.ENDTIME,
dbo.UFN_HOURMINUTE_DISPLAYTIME(ITINERARYITEM.STARTTIME) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(ITINERARYITEM.ENDTIME),
coalesce(EVENTLOCATION.NAME, ''),
coalesce(dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCESTRING(ITINERARYITEM.ID), ''),
coalesce(dbo.UFN_ITINERARYITEMSTAFFRESOURCE_GETSTAFFRESOURCESTRING(ITINERARYITEM.ID), ''),
case when LOCATIONCONFLICT.ITINERARYITEMID is null then 0 else 1 end,
case when RESOURCECONFLICT.ITINERARYITEMID is null then 0 else 1 end,
case when STAFFRESOURCECONFLICT.ITINERARYITEMID is null then 0 else 1 end,
ITINERARYITEM.ID,
LOCATIONCONFLICT.LOCATIONID,
RESOURCECONFLICT.RESOURCEID,
RESOURCECONFLICT.QUANTITYNEEDED,
STAFFRESOURCECONFLICT.VOLUNTEERTYPEID,
STAFFRESOURCECONFLICT.QUANTITYNEEDED,
1
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on
ITINERARYITEM.ITINERARYID = ITINERARY.ID
left outer join dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILS(@START, @END, @LOCATIONS, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 0, 1) as LOCATIONCONFLICT on
ITINERARYITEM.ID = LOCATIONCONFLICT.ITINERARYITEMID
left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS(@START, @END, @RESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 0, 1, 0, @OFFSETRESOURCES) as RESOURCECONFLICT on
ITINERARYITEM.ID = RESOURCECONFLICT.ITINERARYITEMID
left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS(@START, @END, @STAFFRESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 0, 1, 0, @OFFSETSTAFFRESOURCES) as STAFFRESOURCECONFLICT on
ITINERARYITEM.ID = STAFFRESOURCECONFLICT.ITINERARYITEMID
left outer join dbo.EVENTLOCATION on
ITINERARYITEM.EVENTLOCATIONID = EVENTLOCATION.ID
where
(
LOCATIONCONFLICT.ITINERARYITEMID is not null or
RESOURCECONFLICT.ITINERARYITEMID is not null or
STAFFRESOURCECONFLICT.ITINERARYITEMID is not null
) and
(
(
@START >= ITINERARYITEM.STARTDATETIME and
@START < ITINERARYITEM.ENDDATETIME
) or
(
@END > ITINERARYITEM.STARTDATETIME and
@END <= ITINERARYITEM.ENDDATETIME
) or
(
@START < ITINERARYITEM.STARTDATETIME and
@END > ITINERARYITEM.ENDDATETIME
)
)
-- Handle itineraries
insert into @EVENTINFOTEMP
(
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
TIMESPANDISPLAY,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
LOCATIONINCONFLICT,
RESOURCEINCONFLICT,
RESOURCEINCONFLICTQUANTITY,
STAFFRESOURCEINCONFLICT,
STAFFRESOURCEINCONFLICTQUANTITY,
RECORDTYPE
)
select distinct
ITINERARY.ID,
'7cc8e595-84d1-401c-b4f9-05a0361afde2',
null,
ITINERARY.NAME,
ITINERARY.STARTDATETIME,
ITINERARY.ENDDATETIME,
dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME),
dbo.UFN_HOURMINUTE_DISPLAYTIME(dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME)) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME)),
'',
dbo.UFN_ITINERARYRESOURCE_GETRESOURCESTRING(ITINERARY.ID),
dbo.UFN_ITINERARYSTAFFRESOURCE_GETSTAFFRESOURCESTRING(ITINERARY.ID),
0,
case when RESOURCECONFLICT.ITINERARYID is null then 0 else 1 end,
case when STAFFRESOURCECONFLICT.ITINERARYID is null then 0 else 1 end,
null,
RESOURCECONFLICT.RESOURCEID,
RESOURCECONFLICT.QUANTITYNEEDED,
STAFFRESOURCECONFLICT.VOLUNTEERTYPEID,
STAFFRESOURCECONFLICT.QUANTITYNEEDED,
2
from dbo.ITINERARY
left outer join dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS(@START, @END, @RESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 0, 0, 1, @OFFSETRESOURCES) as RESOURCECONFLICT on
ITINERARY.ID = RESOURCECONFLICT.ITINERARYID
left outer join dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS(@START, @END, @STAFFRESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, @IGNORESUPERRECORDID, 0, 0, 1, @OFFSETSTAFFRESOURCES) as STAFFRESOURCECONFLICT on
ITINERARY.ID = STAFFRESOURCECONFLICT.ITINERARYID
where
(
RESOURCECONFLICT.ITINERARYID is not null or
STAFFRESOURCECONFLICT.ITINERARYID is not null
) and
(
(@START >= ITINERARY.STARTDATETIME and @START < ITINERARY.ENDDATETIME) or
(@END > ITINERARY.STARTDATETIME and @END <= ITINERARY.ENDDATETIME) or
(@START < ITINERARY.STARTDATETIME and @END > ITINERARY.ENDDATETIME)
)
-- Now we need to get the distinct information about events in conflict
insert into @EVENTINFO
(
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
TIMESPANDISPLAY,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
LOCATIONSINCONFLICT,
RESOURCESINCONFLICT,
STAFFRESOURCESINCONFLICT,
RECORDTYPE
)
select
EVENTINFOTEMP.RECORDID,
EVENTINFOTEMP.PAGEID,
EVENTINFOTEMP.PROGRAMID,
EVENTINFOTEMP.NAME,
EVENTINFOTEMP.STARTDATE,
EVENTINFOTEMP.ENDDATE,
EVENTINFOTEMP.STARTTIME,
EVENTINFOTEMP.ENDTIME,
EVENTINFOTEMP.TIMESPANDISPLAY,
EVENTINFOTEMP.LOCATIONS,
EVENTINFOTEMP.RESOURCES,
EVENTINFOTEMP.STAFFRESOURCES,
EVENTINFOTEMP.ISLOCATIONCONFLICT,
EVENTINFOTEMP.ISRESOURCECONFLICT,
EVENTINFOTEMP.ISSTAFFRESOURCECONFLICT,
dbo.UDA_BUILDLIST(distinct EVENTLOCATION.NAME),
dbo.UDA_BUILDLIST(distinct RESOURCE.NAME + ': ' + cast(EVENTINFOTEMP.RESOURCEINCONFLICTQUANTITY as nvarchar)),
dbo.UDA_BUILDLIST(distinct VOLUNTEERTYPE.NAME + ': ' + cast(EVENTINFOTEMP.STAFFRESOURCEINCONFLICTQUANTITY as nvarchar)),
RECORDTYPE
from @EVENTINFOTEMP as EVENTINFOTEMP
left outer join dbo.EVENTLOCATION on
EVENTLOCATION.ID = EVENTINFOTEMP.LOCATIONINCONFLICT
left outer join dbo.RESOURCE on
RESOURCE.ID = EVENTINFOTEMP.RESOURCEINCONFLICT
left outer join dbo.VOLUNTEERTYPE on
VOLUNTEERTYPE.ID = EVENTINFOTEMP.STAFFRESOURCEINCONFLICT
group by EVENTINFOTEMP.RECORDID, EVENTINFOTEMP.PAGEID, EVENTINFOTEMP.PROGRAMID, EVENTINFOTEMP.NAME, EVENTINFOTEMP.STARTDATE, EVENTINFOTEMP.ENDDATE, EVENTINFOTEMP.STARTTIME, EVENTINFOTEMP.ENDTIME, EVENTINFOTEMP.TIMESPANDISPLAY, EVENTINFOTEMP.LOCATIONS, EVENTINFOTEMP.RESOURCES, EVENTINFOTEMP.STAFFRESOURCES, EVENTINFOTEMP.ISLOCATIONCONFLICT, EVENTINFOTEMP.ISRESOURCECONFLICT, EVENTINFOTEMP.ISSTAFFRESOURCECONFLICT, EVENTINFOTEMP.RECORDTYPE
-- Now get the distinct information about locations and resources in conflict.
-- This is silly (since we are returning the same data for every row) but its either this or conflict detection runs twice.
-- First locations
update @EVENTINFO set
DISTINCTLOCATIONSINCONFLICT = LOCATIONSINCONFLICTTABLE.LOCATIONSINCONFLICT
from
(
select dbo.UDA_BUILDLIST(distinct EVENTLOCATION.NAME) as LOCATIONSINCONFLICT
from @EVENTINFOTEMP as EVENTINFOTEMP
inner join dbo.EVENTLOCATION on
EVENTINFOTEMP.LOCATIONINCONFLICT = EVENTLOCATION.ID
) as LOCATIONSINCONFLICTTABLE
-- Next resources
update @EVENTINFO set
DISTINCTSTAFFRESOURCESINCONFLICT = RESOURCESINCONFLICTTABLE.RESOURCESINCONFLICT
from
(
select dbo.UDA_BUILDLIST(distinct RESOURCE.NAME + ': ' + cast(EVENTINFOTEMP.RESOURCEINCONFLICTQUANTITY as nvarchar)) as RESOURCESINCONFLICT
from @EVENTINFOTEMP as EVENTINFOTEMP
inner join dbo.RESOURCE on EVENTINFOTEMP.RESOURCEINCONFLICT = RESOURCE.ID
) as RESOURCESINCONFLICTTABLE
-- Next staff resources
update @EVENTINFO set
DISTINCTRESOURCESINCONFLICT = STAFFRESOURCESINCONFLICTTABLE.STAFFRESOURCESINCONFLICT
from
(
select dbo.UDA_BUILDLIST(distinct VOLUNTEERTYPE.NAME + ': ' + cast(EVENTINFOTEMP.STAFFRESOURCEINCONFLICTQUANTITY as nvarchar)) as STAFFRESOURCESINCONFLICT
from @EVENTINFOTEMP as EVENTINFOTEMP
inner join dbo.VOLUNTEERTYPE on EVENTINFOTEMP.STAFFRESOURCEINCONFLICT = VOLUNTEERTYPE.ID
) as STAFFRESOURCESINCONFLICTTABLE
return;
end