UFN_CONFLICTCHECK_GETRESOURCECONFLICTS
Get all of the records that are in conflict with the given resources.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @START | datetime | IN | |
| @END | datetime | IN | |
| @RESOURCES | xml | IN | |
| @OFFSETRESOURCES | xml | IN | |
| @SUPERRECORDID | uniqueidentifier | IN | |
| @RECORDID | uniqueidentifier | IN | |
| @SUBRECORDID | uniqueidentifier | IN | |
| @IGNORESUPERRECORD | bit | IN | |
| @IGNORERECORD | bit | IN | |
| @IGNORESUBRECORD | bit | IN | |
| @IGNORERECORDSUBRECORDS | bit | IN |
Definition
Copy
CREATE function dbo.UFN_CONFLICTCHECK_GETRESOURCECONFLICTS
(
@START datetime,
@END datetime,
@RESOURCES xml,
@OFFSETRESOURCES xml = null,
@SUPERRECORDID uniqueidentifier = null,
@RECORDID uniqueidentifier = null,
@SUBRECORDID uniqueidentifier = null,
@IGNORESUPERRECORD bit = 1,
@IGNORERECORD bit = 1,
@IGNORESUBRECORD bit = 1,
@IGNORERECORDSUBRECORDS bit = 1
)
returns @CONFLICTS TABLE
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
RESOURCEID uniqueidentifier,
QUANTITYNEEDED int
)
as
begin
-- Take the resource xml passed in and generate a table of resources
declare @RESOURCESTABLE table
(
RESOURCEID uniqueidentifier,
QUANTITYNEEDED int
)
insert into @RESOURCESTABLE
(RESOURCEID, QUANTITYNEEDED)
select
T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
T.c.value('(QUANTITYNEEDED)[1]','int')
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
if @RECORDID is not null
begin
declare @VISITORCOUNT int
set @VISITORCOUNT = isnull((
select sum(ITINERARYATTENDEE.QUANTITY)
from dbo.ITINERARYATTENDEE
where ITINERARYID = @RECORDID
),0);
update @RESOURCESTABLE set
QUANTITYNEEDED = dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(@VISITORCOUNT * RESOURCE.PERTICKETQUANTITY, RESOURCE.PERTICKETDIVISOR)
from dbo.RESOURCE
where
RESOURCE.ID = [@RESOURCESTABLE].RESOURCEID and
-- Special checks for Itinerary Edit, Reservation Add, Group Checkin
-- already set this up because of attendee quantity change
[@RESOURCESTABLE].QUANTITYNEEDED = 0 and
RESOURCE.ISPERTICKETITEM = 1
end
insert into @RESOURCESTABLE
(RESOURCEID, QUANTITYNEEDED)
select
T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
T.c.value('(QUANTITYNEEDED)[1]','int')
from @RESOURCES.nodes('/EVENTRESOURCES/ITEM') T(c)
-- Build a table to store resource counts
declare @RESOURCESUSED table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
RESOURCEID uniqueidentifier,
QUANTITYNEEDED int,
STARTDATETIME datetime,
ENDDATETIME datetime
)
-- fill the table based on those events occurring at the same time
insert into @RESOURCESUSED
(
EVENTID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME,
ENDDATETIME
)
select
EVENT.ID,
EVENTRESOURCE.RESOURCEID,
EVENTRESOURCE.QUANTITYNEEDED,
EVENT.STARTDATETIME as STARTDATETIME,
EVENT.ENDDATETIME as ENDDATETIME
from dbo.EVENT
inner join dbo.EVENTRESOURCE on EVENT.ID = EVENTRESOURCE.EVENTID
inner join dbo.RESOURCE on
(EVENTRESOURCE.RESOURCEID = RESOURCE.ID) and (RESOURCE.TYPECODE = 0) --A non-consumable rsource
left outer join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on
EVENTRESOURCECONFLICT.RESOURCEID = EVENTRESOURCE.RESOURCEID
where
(PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
(@IGNORERECORD = 0 or @RECORDID is null or EVENT.ID <> @RECORDID) and
(
(
@START >= EVENT.STARTDATETIME and
@START < EVENT.ENDDATETIME
) or
(
@END > EVENT.STARTDATETIME and
@END <= EVENT.ENDDATETIME
) or
(
@START < EVENT.STARTDATETIME and
@END > EVENT.ENDDATETIME
)
)
-- fill the table based on those itinerary items occurring at the same time
insert into @RESOURCESUSED
(
ITINERARYITEMID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME,
ENDDATETIME
)
select
ITINERARYITEM.ID,
ITINERARYITEMRESOURCE.RESOURCEID,
case RESOURCE.ISPERTICKETITEM
when 0 then
ITINERARYITEMRESOURCE.QUANTITYNEEDED
else
dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARY.ID),0) * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
end as QUANTITYNEEDED,
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on
ITINERARYITEM.ITINERARYID = ITINERARY.ID
inner join dbo.SALESORDER on
ITINERARY.RESERVATIONID = SALESORDER.ID
inner join dbo.ITINERARYITEMRESOURCE on
ITINERARYITEM.ID = ITINERARYITEMRESOURCE.ITINERARYITEMID
inner join dbo.RESOURCE on
ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID and
RESOURCE.TYPECODE = 0 --A non-consumable resource
inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on
EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYITEMRESOURCE.RESOURCEID
where
SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total
ITINERARYITEM.INVALIDREASONCODE = 0 and -- Make sure that the itinerary item does not have an invalid reason
ITINERARYITEM.ITEMTYPECODE <> 3 and -- Make sure the itinerary item is showing as scheduled
(@IGNORESUPERRECORD = 0 or @SUPERRECORDID is null or ITINERARY.RESERVATIONID <> @SUPERRECORDID) and
(
not (ITINERARYITEM.ITINERARYID = @RECORDID and @IGNORERECORDSUBRECORDS = 1) and
(@IGNORESUBRECORD = 0 or @SUBRECORDID is null or ITINERARYITEM.ID <> @SUBRECORDID)
) and
(
(
@START >= ITINERARYITEM.STARTDATETIME and
@START < ITINERARYITEM.ENDDATETIME
) or
(
@END > ITINERARYITEM.STARTDATETIME and
@END <= ITINERARYITEM.ENDDATETIME
) or
(
@START < ITINERARYITEM.STARTDATETIME and
@END > ITINERARYITEM.ENDDATETIME
)
)
-- fill the table based on those itineraries occurring at the same time
insert into @RESOURCESUSED
(
ITINERARYID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME,
ENDDATETIME
)
select
ITINERARY.ID,
ITINERARYRESOURCE.RESOURCEID,
case RESOURCE.ISPERTICKETITEM
when 0 then
ITINERARYRESOURCE.QUANTITYNEEDED
else
dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID),0) * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
end as QUANTITYNEEDED,
ITINERARY.STARTDATETIME as STARTDATETIME,
ITINERARY.ENDDATETIME as ENDDATETIME
from dbo.ITINERARY
inner join dbo.SALESORDER on
ITINERARY.RESERVATIONID = SALESORDER.ID
inner join dbo.ITINERARYRESOURCE on
ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID
inner join dbo.RESOURCE on
ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID and
RESOURCE.TYPECODE = 0 --A non-consumable resource
inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on
EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
where
SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total
(@IGNORERECORD = 0 or @RECORDID is null or ITINERARY.ID <> @RECORDID) and
(@IGNORESUPERRECORD = 0 or @SUPERRECORDID is null or ITINERARY.RESERVATIONID <> @SUPERRECORDID) and
(
(@START >= ITINERARY.STARTDATETIME and @START < ITINERARY.ENDDATETIME) or
(@END > ITINERARY.STARTDATETIME and @END <= ITINERARY.ENDDATETIME) or
(@START < ITINERARY.STARTDATETIME and @END > ITINERARY.ENDDATETIME) or
-- Checking against an itinerary that has a time extended by the itinerary item
(@RECORDID is not null and @RECORDID = ITINERARY.ID)
)
-- Set itinerary times to be within itinerary item window when changing time of itinerary
-- Times outside of @START and @END are not an issue against conflicts
if @RECORDID is not null and @SUBRECORDID is not null and @IGNORERECORD = 0
update @RESOURCESUSED set
STARTDATETIME = @START,
ENDDATETIME = @END
where
ITINERARYID is not null and
ITINERARYID = @RECORDID
-- Checking against reservation this is being moved or copied.
-- Picking up conflicts that would be caused by the whole reservation.
if @OFFSETRESOURCES is not null
begin
with RESOURCES_CTE as
(
select
T.item.value('(ITINERARYID)[1]','uniqueidentifier') as ITINERARYID,
T.item.value('(ITINERARYITEMID)[1]','uniqueidentifier') as ITINERARYITEMID,
T.item.value('(RESOURCEID)[1]','uniqueidentifier') as RESOURCEID,
T.item.value('(QUANTITYNEEDED)[1]','integer') as QUANTITYNEEDED,
T.item.value('(STARTDATETIME)[1]','datetime') as STARTDATETIME,
T.item.value('(ENDDATETIME)[1]','datetime') as ENDDATETIME
from @OFFSETRESOURCES.nodes('/OFFSETRESOURCES/ITEM') T(item)
where
(
@START >= T.item.value('(STARTDATETIME)[1]','datetime') and
@START < T.item.value('(ENDDATETIME)[1]','datetime')
) or
(
@END > T.item.value('(STARTDATETIME)[1]','datetime') and
@END <= T.item.value('(ENDDATETIME)[1]','datetime')
) or
(
@START < T.item.value('(STARTDATETIME)[1]','datetime') and
@END > T.item.value('(ENDDATETIME)[1]','datetime')
)
)
insert into @RESOURCESUSED
(
ITINERARYID,
ITINERARYITEMID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME,
ENDDATETIME
)
select
case
when ITINERARYITEMID is null or ITINERARYITEMID = '00000000-0000-0000-0000-000000000000'
then ITINERARYID
else null
end as ITINERARYID,
ITINERARYITEMID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME,
ENDDATETIME
from RESOURCES_CTE RE
where
-- Resources already in itinerary should be in RESOURCESTABLE
(
@IGNORERECORD = 0 or -- Double counting resources if not ignoring record
@RECORDID is null or
ITINERARYID is null or
@SUBRECORDID is not null or
(
ITINERARYITEMID is not null and
ITINERARYITEMID <> '00000000-0000-0000-0000-000000000000'
) or
ITINERARYID <> @RECORDID
) and
(
@IGNORESUBRECORD = 0 or
@SUBRECORDID is null or
(
ITINERARYITEMID is null or
ITINERARYITEMID = '00000000-0000-0000-0000-000000000000'
) or
ITINERARYITEMID <> @SUBRECORDID
)
end
declare @CURRENTUSEDRESOURCES table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
RESOURCEID uniqueidentifier,
QUANTITYNEEDED int
)
declare @THISITINERARYID uniqueidentifier
declare @THISITINERARYITEMID uniqueidentifier
declare @THISEVENTID uniqueidentifier
declare @THISRESOURCEID uniqueidentifier
declare @THISQUANTITYNEEDED int
declare @THISTIME datetime
declare @THISISSTARTTIME bit
declare CONFLICTS_CURSOR cursor local fast_forward for
select
ITINERARYID,
ITINERARYITEMID,
EVENTID,
RESOURCEID,
QUANTITYNEEDED,
TIME,
ISSTARTTIME
from
(
select
ITINERARYID,
ITINERARYITEMID,
EVENTID,
RESOURCEID,
QUANTITYNEEDED,
STARTDATETIME as TIME,
1 as ISSTARTTIME
from @RESOURCESUSED
union all
select
ITINERARYID,
ITINERARYITEMID,
EVENTID,
RESOURCEID,
QUANTITYNEEDED,
ENDDATETIME as TIME,
0 as ISSTARTTIME
from @RESOURCESUSED
) as T
order by T.TIME, T.ISSTARTTIME
open CONFLICTS_CURSOR;
fetch next from CONFLICTS_CURSOR
into
@THISITINERARYID,
@THISITINERARYITEMID,
@THISEVENTID,
@THISRESOURCEID,
@THISQUANTITYNEEDED,
@THISTIME,
@THISISSTARTTIME;
while @@FETCH_STATUS = 0
begin
if @THISISSTARTTIME = 1
-- PUSH
begin
insert into @CURRENTUSEDRESOURCES
(
EVENTID,
ITINERARYITEMID,
ITINERARYID,
RESOURCEID,
QUANTITYNEEDED
)
values
(
@THISEVENTID,
@THISITINERARYITEMID,
@THISITINERARYID,
@THISRESOURCEID,
@THISQUANTITYNEEDED
);
-- If in conflict, dump @CURRENTUSEDRESOURCES into @CONFLICTS
-- Not inserting duplicates
with RESOURCETOTAL_CTE as
(
select
RESOURCEID,
sum(QUANTITYNEEDED) as QUANTITYNEEDED,
count(RESOURCEID) as RESOURCECOUNT
from
(
select
RESOURCEID,
QUANTITYNEEDED
from @CURRENTUSEDRESOURCES
union all
select
RESOURCEID,
QUANTITYNEEDED
from @RESOURCESTABLE
) as T
group by T.RESOURCEID
)
insert into @CONFLICTS
(
EVENTID,
ITINERARYITEMID,
ITINERARYID,
RESOURCEID,
QUANTITYNEEDED
)
select
RESOURCESUSED.EVENTID,
RESOURCESUSED.ITINERARYITEMID,
RESOURCESUSED.ITINERARYID,
RESOURCESUSED.RESOURCEID,
RESOURCESUSED.QUANTITYNEEDED
from @CURRENTUSEDRESOURCES as RESOURCESUSED
inner join RESOURCETOTAL_CTE RESOURCETOTAL on
RESOURCETOTAL.RESOURCEID = RESOURCESUSED.RESOURCEID
inner join dbo.RESOURCE on
RESOURCESUSED.RESOURCEID = RESOURCE.ID
where
not exists
(
select 1 from @CONFLICTS
where
(
[@CONFLICTS].EVENTID = RESOURCESUSED.EVENTID or
[@CONFLICTS].ITINERARYITEMID = RESOURCESUSED.ITINERARYITEMID or
[@CONFLICTS].ITINERARYID = RESOURCESUSED.ITINERARYID
) and
[@CONFLICTS].RESOURCEID = RESOURCESUSED.RESOURCEID
) and
RESOURCETOTAL.QUANTITYNEEDED > RESOURCE.QUANTITY and
RESOURCETOTAL.RESOURCECOUNT > 1
end
else
-- POP
-- Do not need to check for conflicts as we are actually decrementing the count
begin
delete from @CURRENTUSEDRESOURCES
where
(
@THISEVENTID = [@CURRENTUSEDRESOURCES].EVENTID or
@THISITINERARYITEMID = [@CURRENTUSEDRESOURCES].ITINERARYITEMID or
@THISITINERARYID = [@CURRENTUSEDRESOURCES].ITINERARYID
) and
@THISRESOURCEID = [@CURRENTUSEDRESOURCES].RESOURCEID
end
fetch next from CONFLICTS_CURSOR
into
@THISITINERARYID,
@THISITINERARYITEMID,
@THISEVENTID,
@THISRESOURCEID,
@THISQUANTITYNEEDED,
@THISTIME,
@THISISSTARTTIME;
end
close CONFLICTS_CURSOR;
deallocate CONFLICTS_CURSOR;
return;
end