USP_DATAFORMTEMPLATE_EDIT_GROUPSALESCHECKINVISITOR_2
The save procedure used by the edit dataform template "Group Sales Check In Visitor Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ITINERARIES | xml | IN | |
| @PRICETYPES | xml | IN | |
| @RESOURCES | xml | IN | Resources |
| @STAFFRESOURCES | xml | IN | Staffing resources |
| @UPDATERATESCALE | bit | IN | Update group rate based on new number of visitors |
| @IGNORECONFLICTS | bit | IN | Ignore conflicts when checking in |
| @ADDITIONALPRICETYPES | xml | IN | |
| @UPDATEPERTICKETBALANCE | bit | IN | Update order balance based on new number of visitors |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GROUPSALESCHECKINVISITOR_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ITINERARIES xml,
@PRICETYPES xml,
@RESOURCES xml,
@STAFFRESOURCES xml,
@UPDATERATESCALE bit,
@IGNORECONFLICTS bit,
@ADDITIONALPRICETYPES xml,
@UPDATEPERTICKETBALANCE bit
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
set @UPDATERATESCALE = isnull(@UPDATERATESCALE, 1);
set @UPDATEPERTICKETBALANCE = isnull(@UPDATEPERTICKETBALANCE, 1);
declare @ARRIVALDATE date;
declare @PRICINGCODE tinyint;
select
@ARRIVALDATE = ARRIVALDATE,
@PRICINGCODE = PRICINGCODE
from dbo.RESERVATION
where ID = @ID
begin try
declare @VISITORCOUNT integer;
select @VISITORCOUNT = coalesce(sum(T.itineraries.value('(QUANTITY)[1]', 'integer')), 0)
from @ITINERARIES.nodes('/ITINERARIES/ITEM') T(itineraries)
if @VISITORCOUNT = 0
raiserror('BBERR_INVALIDVISITORCOUNT', 13, 1);
if (@IGNORECONFLICTS = 0)
begin
declare @MAXIMUMCAPACITY int;
select @MAXIMUMCAPACITY = MAXIMUMCAPACITY
from dbo.GROUPSALESDEFAULT
if (@MAXIMUMCAPACITY > 0)
begin
if (@VISITORCOUNT > dbo.UFN_GROUPSALESCAPACITY_CAPACITYREMAINING(@ARRIVALDATE, @ID))
raiserror('ERR_EXCEEDSCAPACITY', 13, 1);
end
end
if @IGNORECONFLICTS = 1 begin
update dbo.ITINERARYRESOURCE set
IGNORESQUANTITYFORCAPACITY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where ID = T.c.value('(ID)[1]','uniqueidentifier')
update dbo.ITINERARYSTAFFRESOURCE set
IGNORESQUANTITYFORCAPACITY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where ID = T.c.value('(ID)[1]','uniqueidentifier')
update dbo.ITINERARYITEMRESOURCE set
IGNORESQUANTITYFORCAPACITY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where ID = T.c.value('(ID)[1]','uniqueidentifier')
update dbo.ITINERARYITEMSTAFFRESOURCE set
IGNORESQUANTITYFORCAPACITY = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where ID = T.c.value('(ID)[1]','uniqueidentifier')
end
-- Determine if the order has changed before updating the itinerary tables
declare @ORDER xml;
declare @ORDERHASCHANGED bit;
set @ORDER = (
select @ITINERARIES, @RESOURCES, @STAFFRESOURCES
for xml raw('ITEM'), type, elements, root('ORDERS'), binary base64
);
set @ORDERHASCHANGED = dbo.UFN_RESERVATION_HAVEQUANTITIESCHANGED(@ID, @ORDER);
if @ORDERHASCHANGED = 1 begin
exec dbo.USP_RESERVATION_GETATTENDEES_UPDATEFROMXML @ID, @ITINERARIES, @CHANGEAGENTID, @CURRENTDATE;
declare @ITINERARYRESOURCES table
(
ITINERARYID uniqueidentifier,
STARTDATETIME datetime,
ENDDATETIME datetime,
RESOURCES xml,
STAFFRESOURCES xml
);
insert into @ITINERARYRESOURCES(ITINERARYID)
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') AS 'ITINERARYID'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @EMPTYGUID or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null
insert into @ITINERARYRESOURCES (ITINERARYID)
select distinct
T.c.value('(ITINERARYID)[1]','uniqueidentifier') AS 'ITINERARYID'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @EMPTYGUID or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null
) and
not exists
(
select 1 from @ITINERARYRESOURCES
where ITINERARYID = T.c.value('(ITINERARYID)[1]','uniqueidentifier')
)
update @ITINERARYRESOURCES set
[@ITINERARYRESOURCES].STARTDATETIME = ITINERARY.STARTDATETIME,
[@ITINERARYRESOURCES].ENDDATETIME = ITINERARY.ENDDATETIME
from dbo.ITINERARY with (nolock)
where
ITINERARY.ID = [@ITINERARYRESOURCES].ITINERARYID
update @ITINERARYRESOURCES set
RESOURCES = (
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(RESOURCEID)[1]','uniqueidentifier') AS 'RESOURCEID',
T.c.value('(ISPERTICKETITEM)[1]','bit') AS 'ISPERTICKETITEM',
T.c.value('(PRICE)[1]','money') AS 'PRICE',
T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE',
T.c.value('(PERTICKETQUANTITY)[1]','int') AS 'PERTICKETQUANTITY',
T.c.value('(PERTICKETDIVISOR)[1]','int') AS 'PERTICKETDIVISOR'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYID)[1]','uniqueidentifier') = [@ITINERARYRESOURCES].ITINERARYID and
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @EMPTYGUID 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
),
STAFFRESOURCES = (
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
T.c.value('(JOBOCCURRENCEID)[1]','uniqueidentifier') AS 'JOBOCCURRENCEID',
T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYID)[1]','uniqueidentifier') = [@ITINERARYRESOURCES].ITINERARYID and
(
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = @EMPTYGUID or
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') is null
)
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
)
declare @THISITINERARYID uniqueidentifier
declare @THISSTARTDATETIME datetime
declare @THISENDDATETIME datetime
declare @THISRESOURCES xml
declare @THISSTAFFRESOURCES xml
declare RESOURCES_CURSOR cursor local fast_forward for
select ITINERARYID, STARTDATETIME, ENDDATETIME, RESOURCES, STAFFRESOURCES
from @ITINERARYRESOURCES
open RESOURCES_CURSOR
fetch next from RESOURCES_CURSOR
into @THISITINERARYID, @THISSTARTDATETIME, @THISENDDATETIME, @THISRESOURCES, @THISSTAFFRESOURCES
while @@FETCH_STATUS = 0 begin
exec dbo.USP_ITINERARYRESOURCE_GETRESOURCES_UPDATEFROMXML @THISITINERARYID,@THISRESOURCES,@CHANGEAGENTID,@CURRENTDATE;
update dbo.ITINERARYSTAFFRESOURCE with (rowlock) set
QUANTITYNEEDED = T.c.value('(QUANTITYNEEDED)[1]','int'),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @THISSTAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
ID = T.c.value('(ID)[1]','uniqueidentifier') and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
declare @contextCache varbinary(128);
declare @e int;
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.ITINERARYSTAFFRESOURCE
where
exists
(
select 1 from @THISSTAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
[ITINERARYSTAFFRESOURCE].ID = T.c.value('(ID)[1]','uniqueidentifier') and
T.c.value('(QUANTITYNEEDED)[1]','int') <= 0
)
select @e=@@error;
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @e <> 0
return 2;
update dbo.JOBOCCURRENCE set
VOLUNTEERSNEEDED = T.c.value('(QUANTITYNEEDED)[1]','int'),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @THISSTAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
ID = T.c.value('(JOBOCCURRENCEID)[1]','uniqueidentifier')
exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @THISITINERARYID;
fetch next from RESOURCES_CURSOR
into @THISITINERARYID, @THISSTARTDATETIME, @THISENDDATETIME, @THISRESOURCES, @THISSTAFFRESOURCES
end
close RESOURCES_CURSOR
deallocate RESOURCES_CURSOR
declare @ITINERARYITEMRESOURCES table
(
ITINERARYID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
STARTDATETIME datetime,
ENDDATETIME datetime,
RESOURCES xml,
STAFFRESOURCES xml
);
insert into @ITINERARYITEMRESOURCES (ITINERARYID, ITINERARYITEMID)
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') <> @EMPTYGUID
insert into @ITINERARYITEMRESOURCES (ITINERARYID, ITINERARYITEMID)
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') <> @EMPTYGUID and
not exists
(
select 1 from @ITINERARYITEMRESOURCES
where ITINERARYITEMID = T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier')
)
update @ITINERARYITEMRESOURCES set
[@ITINERARYITEMRESOURCES].STARTDATETIME = ITINERARYITEM.STARTDATETIME,
[@ITINERARYITEMRESOURCES].ENDDATETIME = ITINERARYITEM.ENDDATETIME
from dbo.ITINERARYITEM with (nolock)
where
ITINERARYITEM.ID = [@ITINERARYITEMRESOURCES].ITINERARYITEMID
update @ITINERARYITEMRESOURCES set
RESOURCES = (
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(RESOURCEID)[1]','uniqueidentifier') AS 'RESOURCEID',
T.c.value('(ISPERTICKETITEM)[1]','bit') AS 'ISPERTICKETITEM',
T.c.value('(PRICE)[1]','money') AS 'PRICE',
T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE',
T.c.value('(PERTICKETQUANTITY)[1]','int') AS 'PERTICKETQUANTITY',
T.c.value('(PERTICKETDIVISOR)[1]','int') AS 'PERTICKETDIVISOR'
from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = [@ITINERARYITEMRESOURCES].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
),
STAFFRESOURCES = (
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
case
when T.c.value('(JOBID)[1]','uniqueidentifier') = @EMPTYGUID then null
else T.c.value('(JOBID)[1]','uniqueidentifier')
end as 'JOBID',
T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE',
T.c.value('(PRICE)[1]','money') AS 'PRICE',
T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE'
from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
where
T.c.value('(ITINERARYITEMID)[1]','uniqueidentifier') = [@ITINERARYITEMRESOURCES].ITINERARYITEMID and
T.c.value('(QUANTITYNEEDED)[1]','int') > 0
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
)
declare @THISITINERARYITEMID uniqueidentifier
declare ITEMRESOURCES_CURSOR cursor local fast_forward for
select ITINERARYID, ITINERARYITEMID, STARTDATETIME, ENDDATETIME, RESOURCES, STAFFRESOURCES
from @ITINERARYITEMRESOURCES
open ITEMRESOURCES_CURSOR
fetch next from ITEMRESOURCES_CURSOR
into @THISITINERARYID, @THISITINERARYITEMID, @THISSTARTDATETIME, @THISENDDATETIME, @THISRESOURCES, @THISSTAFFRESOURCES
while @@FETCH_STATUS = 0 begin
exec dbo.USP_ITINERARYITEMRESOURCE_GETRESOURCES_UPDATEFROMXML @THISITINERARYITEMID,@THISRESOURCES,@CHANGEAGENTID,@CURRENTDATE;
exec dbo.USP_ITINERARYITEM_GETSTAFFRESOURCES_UPDATEFROMXML @THISITINERARYITEMID, @THISSTAFFRESOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @THISITINERARYID;
fetch next from ITEMRESOURCES_CURSOR
into @THISITINERARYID, @THISITINERARYITEMID, @THISSTARTDATETIME, @THISENDDATETIME, @THISRESOURCES, @THISSTAFFRESOURCES
end
close ITEMRESOURCES_CURSOR
deallocate ITEMRESOURCES_CURSOR
-- Conflict Checking must be done after all resources are updated to ensure
-- there are no false positives for old conflicts
if @IGNORECONFLICTS = 0
begin
declare @CONFLICTRESOURCES table
(
ITINERARYID uniqueidentifier,
ITINERARYITEMID uniqueidentifier,
STARTTIME datetime,
ENDTIME datetime,
RESOURCES xml,
STAFFRESOURCES xml
)
insert into @CONFLICTRESOURCES
select
ITINERARY.ID,
null,
ITINERARY.STARTDATETIME,
ITINERARY.ENDDATETIME,
dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARY.ID),
dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARY.ID)
from dbo.ITINERARY
where
ITINERARY.RESERVATIONID = @ID and
(
exists
(select 1 from dbo.ITINERARYRESOURCE where ITINERARYID = ITINERARY.ID)
or
exists
(select 1 from dbo.ITINERARYSTAFFRESOURCE where ITINERARYID = ITINERARY.ID)
)
union all
select
ITINERARY.ID,
ITINERARYITEM.ID,
ITINERARYITEM.STARTDATETIME,
ITINERARYITEM.ENDDATETIME,
dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID)
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where
ITINERARY.RESERVATIONID = @ID and
(
exists
(select 1 from dbo.ITINERARYITEMRESOURCE where ITINERARYITEMID = ITINERARYITEM.ID)
or
exists
(select 1 from dbo.ITINERARYITEMSTAFFRESOURCE where ITINERARYITEMID = ITINERARYITEM.ID)
)
if exists
(
select top 1 1 from @CONFLICTRESOURCES
where
dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
STARTTIME, ENDTIME,
null, RESOURCES, null,
null, ITINERARYID, ITINERARYITEMID,
0,
1, -- Ignore Itinerary
1, -- Ignore Itinerary Item
0
) = 1
)
begin
raiserror('BBERR_RESOURCECONFLICTSEXIST', 13, 1);
end
if exists
(
select top 1 1 from @CONFLICTRESOURCES
where
dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
STARTTIME, ENDTIME,
null, null, STAFFRESOURCES,
null, ITINERARYID, ITINERARYITEMID,
0,
1, -- Ignore Itinerary
1, -- Ignore Itinerary Item
0
) = 1
)
begin
raiserror('BBERR_STAFFRESOURCECONFLICTSEXIST', 13, 1);
end
end
exec dbo.USP_RESERVATION_UPDATEATTENDEES @ID, @IGNORECONFLICTS, @CHANGEAGENTID, @CURRENTDATE, @UPDATEPERTICKETBALANCE, 0;
declare @CALCULATEFEESDISCOUNTSANDTAXES bit = 0;
if ((@PRICINGCODE = 1 and @UPDATERATESCALE = 1) or @UPDATEPERTICKETBALANCE = 1) and @ORDERHASCHANGED = 1 begin
set @CALCULATEFEESDISCOUNTSANDTAXES = 1;
end
-- USP_RESERVATION_UPDATEFLATRATE also calls resource sync functions
exec dbo.USP_RESERVATION_UPDATEFLATRATE @ID, @CHANGEAGENTID, @CURRENTDATE, @UPDATERATESCALE, @CALCULATEFEESDISCOUNTSANDTAXES;
end
exec dbo.USP_RESERVATION_COMPLETEORDER_IMPLEMENTATION @ID, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;