USP_DATALIST_ITINERARYITEMEVENTCONFLICTBYDATETIME
Returns all of the event conflicts for a given start datetime, end datetime, locations, and resources for itinerary items and extended times for itineraries.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | Start date |
| @STARTTIME | UDT_HOURMINUTE | IN | Start time |
| @ENDDATE | datetime | IN | End date |
| @ENDTIME | UDT_HOURMINUTE | IN | End time |
| @LOCATIONS | xml | IN | Locations |
| @RESOURCES | xml | IN | Supplies/Equipment resources |
| @STAFFRESOURCES | xml | IN | Staff resources |
| @ITINERARYID | uniqueidentifier | IN | Itinerary ID |
| @ITINERARYITEMID | uniqueidentifier | IN | Itinerary Item ID |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_ITINERARYITEMEVENTCONFLICTBYDATETIME
(
@STARTDATE datetime,
@STARTTIME UDT_HOURMINUTE,
@ENDDATE datetime,
@ENDTIME UDT_HOURMINUTE,
@LOCATIONS xml,
@RESOURCES xml,
@STAFFRESOURCES xml,
@ITINERARYID uniqueidentifier = null,
@ITINERARYITEMID uniqueidentifier = null
)
as
set nocount on;
declare @OLDITINERARYSTART datetime;
declare @OLDITINERARYEND datetime;
declare @ITINERARYSTART datetime;
declare @ITINERARYEND datetime;
declare @ITINERARYITEMSTART datetime;
declare @ITINERARYITEMEND datetime;
declare @CHECKITINERARYCONFLICTS bit = 0;
set @ITINERARYITEMSTART = dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),@STARTTIME);
set @ITINERARYITEMEND = dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(@ENDDATE),@ENDTIME);
select
@OLDITINERARYSTART = ITINERARY.STARTDATETIME,
@OLDITINERARYEND = ITINERARY.ENDDATETIME
from dbo.ITINERARY
where ID = @ITINERARYID
select
@ITINERARYSTART = min(STARTDATETIME),
@ITINERARYEND = max(ENDDATETIME)
from
(
select
@ITINERARYITEMSTART as STARTDATETIME,
@ITINERARYITEMEND as ENDDATETIME
union all
select
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from dbo.ITINERARYITEM
where
ITINERARYID = @ITINERARYID and
ID <> @ITINERARYITEMID
) T
if @OLDITINERARYSTART is null or @OLDITINERARYSTART <> @ITINERARYSTART or
@OLDITINERARYEND is null or @OLDITINERARYEND <> @ITINERARYEND
set @CHECKITINERARYCONFLICTS = 1
declare @CONFLICTITEMS table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
LOCATIONID uniqueidentifier,
RESOURCEID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
QUANTITYNEEDED int
)
insert into @CONFLICTITEMS
select
EVENTID,
ITINERARYITEMID,
ITINERARYID,
LOCATIONID,
RESOURCEID,
VOLUNTEERTYPEID,
QUANTITYNEEDED
from dbo.UFN_CONFLICTCHECK_GETCONFLICTITEMS(
@ITINERARYITEMSTART, @ITINERARYITEMEND,
@LOCATIONS, @RESOURCES, @STAFFRESOURCES,
null, @ITINERARYID, @ITINERARYITEMID,
0, -- Ignore Super Record
0, -- Ignore Record
1, -- Ignore Sub Record
0, -- Ignore All Sub Records of Record
null, null
)
-- If itinerary times changing, need to check on conflicts
if @CHECKITINERARYCONFLICTS = 1
begin
declare @ITINERARYRESOURCES xml
set @ITINERARYRESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ITINERARYID);
declare @ITINERARYSTAFFRESOURCES xml
set @ITINERARYSTAFFRESOURCES = dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@ITINERARYID);
insert into @CONFLICTITEMS
select
EVENTID,
ITINERARYITEMID,
ITINERARYID,
LOCATIONID,
RESOURCEID,
VOLUNTEERTYPEID,
QUANTITYNEEDED
from dbo.UFN_CONFLICTCHECK_GETCONFLICTITEMS(
@ITINERARYSTART, @ITINERARYEND,
null, @ITINERARYRESOURCES, @ITINERARYSTAFFRESOURCES,
null, @ITINERARYID, @ITINERARYITEMID,
0, -- Ignore Super Record
1, -- Ignore Record
1, -- Ignore Sub Record
0, -- Ignore All Sub Records of Record
null, null
);
end
declare @CONFLICTITEMSXML xml
set @CONFLICTITEMSXML =
(
select
EVENTID,
ITINERARYITEMID,
ITINERARYID,
LOCATIONID,
RESOURCEID,
VOLUNTEERTYPEID,
QUANTITYNEEDED
from @CONFLICTITEMS
for xml raw('ITEM'),type,elements,root('CONFLICTITEMS'),binary base64
);
if @CHECKITINERARYCONFLICTS = 1
begin
select distinct
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
SUBRECORDID,
LOCATIONSINCONFLICT,
RESOURCESINCONFLICT,
STAFFRESOURCESINCONFLICT,
RECORDTYPE,
DISTINCTLOCATIONSINCONFLICT,
DISTINCTRESOURCESINCONFLICT,
DISTINCTSTAFFRESOURCESINCONFLICT
from dbo.UFN_CONFLICTCHECK_GETCONFLICTINFOFROMITEMSWITHNEWITINERARYTIME
(
@CONFLICTITEMSXML,
@ITINERARYSTART,
@ITINERARYEND,
@ITINERARYID,
@ITINERARYITEMID
)
end
else
begin
select distinct
RECORDID,
PAGEID,
PROGRAMID,
NAME,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
LOCATIONS,
RESOURCES,
STAFFRESOURCES,
ISLOCATIONCONFLICT,
ISRESOURCECONFLICT,
ISSTAFFRESOURCECONFLICT,
SUBRECORDID,
LOCATIONSINCONFLICT,
RESOURCESINCONFLICT,
STAFFRESOURCESINCONFLICT,
RECORDTYPE,
DISTINCTLOCATIONSINCONFLICT,
DISTINCTRESOURCESINCONFLICT,
DISTINCTSTAFFRESOURCESINCONFLICT
from dbo.UFN_CONFLICTCHECK_GETCONFLICTINFOFROMITEMS(@CONFLICTITEMSXML)
end