UFN_CONFLICTCHECK_GETCONFLICTINFOFROMITEMS
Returns all conflict information from the items in conflict provided.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONFLICTITEMS | xml | IN |
Definition
Copy
CREATE function dbo.UFN_CONFLICTCHECK_GETCONFLICTINFOFROMITEMS
(
@CONFLICTITEMS xml
)
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
declare @LOCATIONCONFLICTITEMS table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
LOCATIONID uniqueidentifier
)
insert into @LOCATIONCONFLICTITEMS
select
T.c.value('(EVENTID)[1]','uniqueidentifier'),
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier'),
T.c.value('(LOCATIONID)[1]','uniqueidentifier')
from @CONFLICTITEMS.nodes('/CONFLICTITEMS/ITEM') T(c)
where
T.c.value('(LOCATIONID)[1]','uniqueidentifier') is not null and
T.c.value('(LOCATIONID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
declare @RESOURCECONFLICTITEMS table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
RESOURCEID uniqueidentifier,
QUANTITYNEEDED int
)
insert into @RESOURCECONFLICTITEMS
select
T.c.value('(EVENTID)[1]','uniqueidentifier'),
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier'),
T.c.value('(ITINERARYID)[1]','uniqueidentifier'),
T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
T.c.value('(QUANTITYNEEDED)[1]','int')
from @CONFLICTITEMS.nodes('/CONFLICTITEMS/ITEM') T(c)
where
T.c.value('(RESOURCEID)[1]','uniqueidentifier') is not null and
T.c.value('(RESOURCEID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
declare @STAFFRESOURCECONFLICTITEMS table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
QUANTITYNEEDED int
)
insert into @STAFFRESOURCECONFLICTITEMS
select
T.c.value('(EVENTID)[1]','uniqueidentifier'),
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier'),
T.c.value('(ITINERARYID)[1]','uniqueidentifier'),
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier'),
T.c.value('(QUANTITYNEEDED)[1]','int')
from @CONFLICTITEMS.nodes('/CONFLICTITEMS/ITEM') T(c)
where
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') is not null and
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
-- 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 @LOCATIONCONFLICTITEMS as LOCATIONCONFLICT on
EVENT.ID = LOCATIONCONFLICT.EVENTID
left outer join @RESOURCECONFLICTITEMS as RESOURCECONFLICT on
EVENT.ID = RESOURCECONFLICT.EVENTID
left outer join @STAFFRESOURCECONFLICTITEMS 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
)
-- 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
inner join dbo.SALESORDER on
SALESORDER.ID = ITINERARY.RESERVATIONID
left outer join @LOCATIONCONFLICTITEMS as LOCATIONCONFLICT on
ITINERARYITEM.ID = LOCATIONCONFLICT.ITINERARYITEMID
left outer join @RESOURCECONFLICTITEMS as RESOURCECONFLICT on
ITINERARYITEM.ID = RESOURCECONFLICT.ITINERARYITEMID
left outer join @STAFFRESOURCECONFLICTITEMS 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
SALESORDER.STATUSCODE <> 5
-- 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
inner join dbo.SALESORDER on
SALESORDER.ID = ITINERARY.RESERVATIONID
left outer join @RESOURCECONFLICTITEMS as RESOURCECONFLICT on
ITINERARY.ID = RESOURCECONFLICT.ITINERARYID
left outer join @STAFFRESOURCECONFLICTITEMS as STAFFRESOURCECONFLICT on
ITINERARY.ID = STAFFRESOURCECONFLICT.ITINERARYID
where
(
RESOURCECONFLICT.ITINERARYID is not null or
STAFFRESOURCECONFLICT.ITINERARYID is not null
) and
SALESORDER.STATUSCODE <> 5
-- 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,
dbo.UDA_BUILDLIST(RESOURCE.NAME + ': ' + cast(EVENTINFOTEMP.RESOURCEINCONFLICTQUANTITY as nvarchar)),
dbo.UDA_BUILDLIST(VOLUNTEERTYPE.NAME + ': ' + cast(EVENTINFOTEMP.STAFFRESOURCEINCONFLICTQUANTITY as nvarchar)),
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
DISTINCTRESOURCESINCONFLICT = 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
DISTINCTSTAFFRESOURCESINCONFLICT = 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