USP_DATALIST_RESERVATIONEVENTCONFLICTBYDATETIME
Returns all of the event conflicts for a given start datetime, end datetime, locations, and resources on a reservation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RESERVATIONID | uniqueidentifier | IN | |
| @RESOURCES | xml | IN | Resources |
| @STAFFRESOURCES | xml | IN | Staffing resources |
| @IGNORESUPERRECORDID | uniqueidentifier | IN | |
| @FORMTYPE | tinyint | IN | |
| @DATEOFFSET | int | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_RESERVATIONEVENTCONFLICTBYDATETIME
(
@RESERVATIONID uniqueidentifier,
@RESOURCES xml,
@STAFFRESOURCES xml,
@IGNORESUPERRECORDID uniqueidentifier = null,
@FORMTYPE tinyint = 0,
@DATEOFFSET integer = 0
)
as
set nocount on;
declare @CONFLICTITEMS table
(
EVENTID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
ITINERARYID uniqueidentifier,
LOCATIONID uniqueidentifier,
RESOURCEID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
QUANTITYNEEDED int
)
declare @OFFSETRESOURCES xml;
declare @OFFSETSTAFFRESOURCES xml;
-- add offset to xml collections to pass in for check against the whole reservation.
-- Because we are sending in multiple level items(itinerary, itinerary item, etc)
-- Conflicts will behave differently and return all items in conflict for Conflicts With:
if @FORMTYPE = 0 or @FORMTYPE = 1 -- Reservation Copy, Reservation Move
begin
set @OFFSETRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
RESOURCEID,
ISPERTICKETITEM,
STARTDATETIME,
ENDDATETIME
from
(
-- All itinerary resources
select
ITINERARYRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
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 = ITINERARY.ID
),0)
* ITINERARYRESOURCE.PERTICKETQUANTITY,
ITINERARYRESOURCE.PERTICKETDIVISOR
)
end as QUANTITYNEEDED,
ITINERARYRESOURCE.RESOURCEID,
RESOURCE.ISPERTICKETITEM,
dateadd(d, @DATEOFFSET, ITINERARY.STARTDATETIME) as STARTDATETIME,
dateadd(d, @DATEOFFSET, ITINERARY.ENDDATETIME) as ENDDATETIME
from dbo.ITINERARYRESOURCE
inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
left outer join dbo.GROUPTYPEREQUIREDRESOURCE on
(ITINERARYRESOURCE.RESOURCEID = GROUPTYPEREQUIREDRESOURCE.RESOURCEID) and
(ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDRESOURCE.GROUPSALESGROUPTYPECODEID)
where
ITINERARY.RESERVATIONID = @RESERVATIONID
union all
-- All itinerary item resources
select
ITINERARYITEMRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
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,
ITINERARYITEMRESOURCE.RESOURCEID,
RESOURCE.ISPERTICKETITEM,
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_STARTDATETIME(ITINERARYITEM.ID)) as STARTDATETIME,
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_ENDDATETIME(ITINERARYITEM.ID)) as ENDDATETIME
from dbo.ITINERARYITEMRESOURCE
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
where
ITINERARY.RESERVATIONID = @RESERVATIONID
) [OFFSETRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETRESOURCES'),binary base64
);
set @OFFSETSTAFFRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
VOLUNTEERTYPEID,
FILLEDBYCODE,
STARTDATETIME,
ENDDATETIME
from
(
select
ITINERARYSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
dateadd(d, @DATEOFFSET, ITINERARY.STARTDATETIME) as STARTDATETIME,
dateadd(d, @DATEOFFSET, ITINERARY.ENDDATETIME) as ENDDATETIME
from dbo.ITINERARYSTAFFRESOURCE
inner join dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
left outer join dbo.JOB on ITINERARYSTAFFRESOURCE.JOBID = JOB.ID
left outer join dbo.GROUPTYPEREQUIREDSTAFFRESOURCE on
(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = GROUPTYPEREQUIREDSTAFFRESOURCE.VOLUNTEERTYPEID) and
(ITINERARYSTAFFRESOURCE.JOBID = GROUPTYPEREQUIREDSTAFFRESOURCE.JOBID) and
(ITINERARYSTAFFRESOURCE.FILLEDBYCODE = GROUPTYPEREQUIREDSTAFFRESOURCE.FILLEDBYCODE) and
(ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID)
left outer join dbo.VOLUNTEERTYPE on
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @RESERVATIONID
union all
select
ITINERARYITEMSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_STARTDATETIME(ITINERARYITEM.ID)) as STARTDATETIME,
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_ENDDATETIME(ITINERARYITEM.ID)) as ENDDATETIME
from dbo.ITINERARYITEMSTAFFRESOURCE
inner join dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
left outer join dbo.JOB on ITINERARYITEMSTAFFRESOURCE.JOBID = JOB.ID
left outer join dbo.VOLUNTEERTYPE on
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @RESERVATIONID
) [OFFSETSTAFFRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETSTAFFRESOURCES'),binary base64
);
end
if @FORMTYPE = 2
begin
set @OFFSETRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
RESOURCEID,
STARTDATETIME,
ENDDATETIME
from
(
-- All itinerary resources
select
ITINERARYRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYRESOURCE.RESOURCEID,
ITINERARY.STARTDATETIME as STARTDATETIME,
ITINERARY.ENDDATETIME as ENDDATETIME
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
inner join dbo.ITINERARYRESOURCE on
ITINERARYRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
where
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
RESOURCE.ISPERTICKETITEM = 1
union all
-- All itinerary item resources
select
ITINERARYITEMRESOURCE.ID AS ID,
ITINERARYITEM.ITINERARYID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYITEMRESOURCE.RESOURCEID,
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
inner join dbo.ITINERARYITEMRESOURCE on
ITINERARYITEMRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
where
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
RESOURCE.ISPERTICKETITEM = 1
) [OFFSETRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETRESOURCES'),binary base64
);
set @OFFSETSTAFFRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
VOLUNTEERTYPEID,
FILLEDBYCODE,
STARTDATETIME,
ENDDATETIME
from
(
select
ITINERARYSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
ITINERARY.STARTDATETIME as STARTDATETIME,
ITINERARY.ENDDATETIME as ENDDATETIME
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
inner join dbo.ITINERARYSTAFFRESOURCE on
ITINERARYSTAFFRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
where
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
union all
select
ITINERARYITEMSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
inner join dbo.ITINERARYITEMSTAFFRESOURCE on
ITINERARYITEMSTAFFRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
where
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
) [OFFSETSTAFFRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETSTAFFRESOURCES'),binary base64
);
end
if @FORMTYPE = 3
begin
set @OFFSETRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
RESOURCEID,
STARTDATETIME,
ENDDATETIME
from
(
-- All itinerary resources
-- Special case for adding Group Type Resources
select
ITINERARYRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
RESOURCE.ID as RESOURCEID,
ITINERARY.STARTDATETIME as STARTDATETIME,
ITINERARY.ENDDATETIME as ENDDATETIME
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
left join dbo.ITINERARYRESOURCE on
ITINERARYRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.RESOURCE on
RESOURCE.ID = T.c.value('(RESOURCEID)[1]','uniqueidentifier')
inner join dbo.ITINERARY on
ITINERARY.ID = T.c.value('(ITINERARYID)[1]','uniqueidentifier')
where
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
) and
(
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
RESOURCE.ISPERTICKETITEM = 1
)
union all
-- All itinerary item resources
select
ITINERARYITEMRESOURCE.ID AS ID,
ITINERARYITEM.ITINERARYID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYITEMRESOURCE.RESOURCEID,
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
inner join dbo.ITINERARYITEMRESOURCE on
ITINERARYITEMRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
where
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is not null and
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
) and
(
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
RESOURCE.ISPERTICKETITEM = 1
)
) [OFFSETRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETRESOURCES'),binary base64
);
set @OFFSETSTAFFRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
VOLUNTEERTYPEID,
FILLEDBYCODE,
STARTDATETIME,
ENDDATETIME
from
(
-- Special case for adding Group Type Staff Resources
select
ITINERARYSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
VOLUNTEERTYPE.ID as VOLUNTEERTYPEID,
0 as FILLEDBYCODE, -- Only passing in Volunteers for Conflict Checking
ITINERARY.STARTDATETIME as STARTDATETIME,
ITINERARY.ENDDATETIME as ENDDATETIME
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
left join dbo.ITINERARYSTAFFRESOURCE on
ITINERARYSTAFFRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.VOLUNTEERTYPE on
VOLUNTEERTYPE.ID = T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier')
inner join dbo.ITINERARY on
ITINERARY.ID = T.c.value('(ITINERARYID)[1]','uniqueidentifier')
where
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
) and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
union all
select
ITINERARYITEMSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
T.c.value('(QUANTITYNEEDED)[1]','int') as QUANTITYNEEDED,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
ITINERARYITEM.STARTDATETIME as STARTDATETIME,
ITINERARYITEM.ENDDATETIME as ENDDATETIME
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
inner join dbo.ITINERARYITEMSTAFFRESOURCE on
ITINERARYITEMSTAFFRESOURCE.ID = T.c.value('(ID)[1]','uniqueidentifier')
inner join dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
where
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is not null and
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
) and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
) [OFFSETSTAFFRESOURCES]
for xml raw('ITEM'),type,elements,root('OFFSETSTAFFRESOURCES'),binary base64
);
end
declare @ITINERARYID uniqueidentifier;
declare @ITINERARYITEMID uniqueidentifier;
declare itineraries_cursor cursor LOCAL FAST_FORWARD for
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') as ITINERARYID
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null
union
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') as ITINERARYID
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null
OPEN itineraries_cursor
FETCH NEXT FROM itineraries_cursor
INTO @ITINERARYID
while @@FETCH_STATUS = 0
begin
insert into @CONFLICTITEMS
select
EVENTID,
ITINERARYITEMID,
ITINERARYID,
LOCATIONID,
RESOURCEID,
VOLUNTEERTYPEID,
QUANTITYNEEDED
from dbo.UFN_CONFLICTCHECK_GETCONFLICTITEMS
(
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARY_STARTDATETIME(@ITINERARYID)),
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARY_ENDDATETIME(@ITINERARYID)),
null,
(
select
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(RESOURCEID)[1]','uniqueidentifier') AS 'RESOURCEID'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYID)[1]','uniqueidentifier') = @ITINERARYID and
(T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' or T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null) and
(
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
T.c.value('(ISPERTICKETITEM)[1]','bit') = 1
)
for xml raw('ITEM'),type,elements,root('RESOURCES'),binary base64
),
(
select
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYID)[1]','uniqueidentifier') = @ITINERARYID and
(T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' or T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null) and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
),
@IGNORESUPERRECORDID,
@ITINERARYID,
null,
case when @FORMTYPE = 0 or @FORMTYPE = 3 then 0 else 1 end, -- Ignore super record unless copy or itinerary edit
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore record if not copy
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore subrecord is not copy
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore all subrecords of record if not copy
@OFFSETRESOURCES,
@OFFSETSTAFFRESOURCES
)
FETCH NEXT FROM itineraries_cursor
INTO @ITINERARYID
end
close itineraries_cursor
deallocate itineraries_cursor
set @ITINERARYID = null
declare items_cursor cursor LOCAL FAST_FORWARD for
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') AS 'ITINERARYID',
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') AS 'ITINERARYITEMID'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is not null
union
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') AS 'ITINERARYID',
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') AS 'ITINERARYITEMID'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'
or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is not null
OPEN items_cursor
FETCH NEXT FROM items_cursor
INTO @ITINERARYID, @ITINERARYITEMID
while @@FETCH_STATUS = 0
begin
insert into @CONFLICTITEMS
select
EVENTID,
ITINERARYITEMID,
ITINERARYID,
LOCATIONID,
RESOURCEID,
VOLUNTEERTYPEID,
QUANTITYNEEDED
from dbo.UFN_CONFLICTCHECK_GETCONFLICTITEMS
(
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_STARTDATETIME(@ITINERARYITEMID)),
dateadd(d, @DATEOFFSET, dbo.UFN_ITINERARYITEM_ENDDATETIME(@ITINERARYITEMID)),
null,
(
select
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(RESOURCEID)[1]','uniqueidentifier') AS 'RESOURCEID'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @ITINERARYITEMID and
(
T.c.value('(QUANTITYNEEDED)[1]','int') > 0 or
T.c.value('(ISPERTICKETITEM)[1]','bit') = 1
)
for xml raw('ITEM'),type,elements,root('RESOURCES'),binary base64
),
(
select
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @ITINERARYITEMID and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
),
@IGNORESUPERRECORDID,
@ITINERARYID,
@ITINERARYITEMID,
case when @FORMTYPE = 0 or @FORMTYPE = 3 then 0 else 1 end, -- Ignore super record unless copy reservation or itinerary edit
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore record, won't matter if ignoring super record
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore subrecord, won't matter if ignoring super record
case when @FORMTYPE = 0 then 0 else 1 end, -- Ignore all subrecords of record, won't matter if ignoring super record
@OFFSETRESOURCES,
@OFFSETSTAFFRESOURCES
)
FETCH NEXT FROM items_cursor
INTO @ITINERARYID, @ITINERARYITEMID
end
close items_cursor
deallocate items_cursor
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
);
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);