USP_ITINERARYITEMDAILYADMISSION_EDIT
Allows the user to edit an existing daily admission program on an itinerary.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @ITINERARYID | uniqueidentifier | IN | |
| @PROGRAMID | uniqueidentifier | IN | |
| @STARTDATE | date | IN | |
| @STARTTIME | UDT_HOURMINUTE | IN | |
| @ENDTIME | UDT_HOURMINUTE | IN | |
| @NOTES | nvarchar(500) | IN | |
| @RESOURCES | xml | IN | |
| @IGNORECONFLICTS | bit | IN | |
| @STAFFRESOURCES | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_ITINERARYITEMDAILYADMISSION_EDIT
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null,
@ITINERARYID uniqueidentifier,
@PROGRAMID uniqueidentifier,
@STARTDATE date,
@STARTTIME dbo.UDT_HOURMINUTE,
@ENDTIME dbo.UDT_HOURMINUTE,
@NOTES nvarchar(500),
@RESOURCES xml,
@IGNORECONFLICTS bit,
@STAFFRESOURCES xml
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CURRENTDATE is null
set @CURRENTDATE = getdate()
begin try
declare @ORDERID uniqueidentifier;
declare @ARRIVALDATE datetime;
declare @STARTDATETIME datetime;
declare @ARRIVALTIME dbo.UDT_HOURMINUTE;
declare @DAILYADMISSIONDATE datetime;
declare @ISFLATRATE bit = 0;
declare @STATUSCODE tinyint;
if @IGNORECONFLICTS = 0
begin
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(@STARTDATE, @STARTTIME);
set @ITINERARYITEMEND = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @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 <> @ID
) T
if @OLDITINERARYSTART is null or @OLDITINERARYSTART <> @ITINERARYSTART or
@OLDITINERARYEND is null or @OLDITINERARYEND <> @ITINERARYEND
set @CHECKITINERARYCONFLICTS = 1
if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
@ITINERARYITEMSTART, @ITINERARYITEMEND,
null, @RESOURCES, null,
null, @ITINERARYID, @ID,
0, -- Ignore Super Record
0, -- Ignore Record
1, -- Ignore Sub Record
0 -- Ignore All Sub Records of Record
) = 1
begin
raiserror('BBERR_RESOURCECONFLICTSEXIST', 13, 1);
return 1;
end
if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
@ITINERARYITEMSTART, @ITINERARYITEMEND,
null, null, @STAFFRESOURCES,
null, @ITINERARYID, @ID,
0, -- Ignore Super Record
0, -- Ignore Record
1, -- Ignore Sub Record
0 -- Ignore All Sub Records of Record
) = 1
begin
raiserror('BBERR_STAFFRESOURCECONFLICTSEXIST', 13, 1);
return 1;
end
-- 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);
if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
@ITINERARYSTART, @ITINERARYEND,
null, @ITINERARYRESOURCES, @ITINERARYSTAFFRESOURCES,
null, @ITINERARYID, @ID,
0, -- Ignore Super Record
1, -- Ignore Record
1, -- Ignore Sub Record
0 -- Ignore All Sub Records of Record
) = 1
begin
raiserror('BBERR_ITINERARYRESOURCECONFLICTSEXIST', 13, 1);
return 1;
end
end
end
select
@ORDERID = RESERVATION.ID,
@ARRIVALDATE = RESERVATION.ARRIVALDATE,
@ARRIVALTIME = RESERVATION.ARRIVALTIME,
@ISFLATRATE = case when PRICINGCODE = 1 then 1 else 0 end,
@STATUSCODE = SALESORDER.STATUSCODE
from dbo.RESERVATION
inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
inner join dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
where ITINERARY.ID = @ITINERARYID
if @STATUSCODE in (1, 5)
raiserror('BBERR_INVALIDSTATUS', 13, 1);
set @ARRIVALDATE = dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE, @ARRIVALTIME);
set @STARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME);
declare @DESCRIPTION nvarchar(100);
select @DESCRIPTION = PROGRAM.NAME from dbo.PROGRAM where PROGRAM.ID = @PROGRAMID;
if @ARRIVALDATE > @STARTDATETIME
raiserror('BBERR_INVALIDDATE', 13, 1)
if datediff(day, @ARRIVALDATE, @STARTDATE) > 4
raiserror('BBERR_OUTSIDEDATERANGE', 13, 1)
if not exists (select 1 from dbo.ITINERARYITEM where PROGRAMID = @PROGRAMID and ITEMTYPECODE = 1 and ITINERARYID = @ITINERARYID)
begin
if not exists (select 1 from dbo.PROGRAM where PROGRAM.ID = @PROGRAMID and PROGRAM.ISACTIVE = 1 and dbo.UFN_PROGRAMDAILYADMISSION_ISONSALE(@PROGRAMID, @CURRENTDATE, 3) = 1)
begin
raiserror('BBERR_INACTIVEPROGRAM', 13, 1);
end
end
if not exists (
select 1 from dbo.ITINERARYITEM
where ITINERARYITEM.ITINERARYID = @ITINERARYID and
ITEMTYPECODE = 1 and PROGRAMID = @PROGRAMID and STARTDATE = @STARTDATE
)
begin
update dbo.[SALESORDERITEM] set
[SALESORDERITEM].[QUANTITY] = SALESORDERITEM.[QUANTITY] - ITINERARYATTENDEE.QUANTITY,
[SALESORDERITEM].CHANGEDBYID = @CHANGEAGENTID,
[SALESORDERITEM].DATECHANGED = @CURRENTDATE
from dbo.[SALESORDERITEM]
inner join SALESORDERITEMTICKET as SOIT on
[SALESORDERITEM].ID = SOIT.ID
inner join dbo.ITINERARYITEM on
(SOIT.EVENTID = ITINERARYITEM.EVENTID and ITINERARYITEM.PROGRAMID is null) or
(SOIT.PROGRAMID = ITINERARYITEM.PROGRAMID and ITINERARYITEM.EVENTID is null)
inner join dbo.ITINERARYATTENDEE on
ITINERARYATTENDEE.PRICETYPECODEID = SOIT.PRICETYPECODEID
where
SALESORDERITEM.SALESORDERID = @ORDERID and
ITINERARYITEM.ID = @ID and
ITINERARYATTENDEE.ITINERARYID = @ITINERARYID and
ITINERARYITEM.ITEMTYPECODE <> 3
declare @e int;
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID
delete from dbo.[SALESORDERITEM]
where ID in
(
select SALESORDERITEMFEE.ID from SALESORDERITEM
inner join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.SALESORDERITEMID
where SALESORDERITEM.SALESORDERID = @ORDERID and
SALESORDERITEM.QUANTITY = 0
)
delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where SALESORDERITEMID in
(
select ID
from dbo.SALESORDERITEM
where SALESORDERITEM.QUANTITY = 0 AND
SALESORDERITEM.SALESORDERID = @ORDERID
)
delete dbo.SALESORDERITEM
where SALESORDERITEM.QUANTITY = 0 and
SALESORDERITEM.SALESORDERID = @ORDERID
if not @contextCache is null
set CONTEXT_INFO @contextCache
select @e=@@error;
if @e <> 0
return 2;
declare @ORDERITEMS table
(
ID uniqueidentifier,
QUANTITY decimal(20,4),
PRICETYPE nvarchar(100),
PRICETYPECODEID uniqueidentifier,
PRICE money,
PRICINGSTRUCTURECODE tinyint
);
insert into @ORDERITEMS
select
newid(),
IT.QUANTITY,
dbo.UFN_PRICETYPECODE_GETDESCRIPTION(IT.PRICETYPECODEID) as PRICETYPE,
IT.PRICETYPECODEID,
PP.FACEPRICE,
0
from dbo.ITINERARYATTENDEE IT
inner join dbo.PROGRAMPRICE PP on PP.PRICETYPECODEID = IT.PRICETYPECODEID
inner join dbo.PRICETYPECODE PTC on PTC.ID = PP.PRICETYPECODEID
where PP.PROGRAMID = @PROGRAMID and
IT.ITINERARYID = @ITINERARYID and
not exists (select 1
from dbo.SALESMETHODEXCLUDEDPRICETYPE
inner join dbo.SALESMETHOD on SALESMETHOD.ID = SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID
where SALESMETHOD.TYPECODE = 3 and
SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = PP.PRICETYPECODEID)
if exists
(
select ITINERARYATTENDEE.PRICETYPECODEID
from dbo.ITINERARYATTENDEE
where
ITINERARYATTENDEE.ITINERARYID = @ITINERARYID and
PRICETYPECODEID not in (select PRICETYPECODEID from @ORDERITEMS)
)
begin
raiserror('BBERR_INVALIDPRICETYPE', 13, 1);
end
update dbo.SALESORDERITEM set
SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @ORDERITEMS items
where
SALESORDERID = @ORDERID and
SALESORDERITEM.ID in
(
select ID from dbo.SALESORDERITEMTICKET
where PROGRAMID = @PROGRAMID and
SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
)
-- Set items to be flat rate if the reservation is flat rate and the program is included
if @ISFLATRATE = 1
begin
update @ORDERITEMS set PRICINGSTRUCTURECODE = 1
where dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@ORDERID,@PROGRAMID) = 1
end
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
PRICE,
PRICINGSTRUCTURECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
@ORDERID,
0,
@DESCRIPTION + ' - ' + ITEMS.PRICETYPE,
ITEMS.QUANTITY,
ITEMS.PRICE,
ITEMS.PRICINGSTRUCTURECODE,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ORDERITEMS ITEMS
where
not exists
(
select 1 from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
where
SALESORDERID = @ORDERID and
PROGRAMID = @PROGRAMID and
SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
);
insert into dbo.SALESORDERITEMTICKET
(
ID,
PROGRAMID,
PROGRAMNAME,
PROGRAMCATEGORYNAME,
PRICETYPECODEID,
PRICE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
@PROGRAMID,
coalesce((select [NAME] from dbo.[PROGRAM] where [PROGRAM].[ID] = @PROGRAMID),''),
coalesce((
select [PROGRAMCATEGORYCODE].[DESCRIPTION]
from dbo.[PROGRAM]
inner join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where [PROGRAM].[ID] = @PROGRAMID
),''),
ITEMS.PRICETYPECODEID,
ITEMS.PRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ORDERITEMS ITEMS
where
ITEMS.ID in
( select ID from dbo.SALESORDERITEM where SALESORDERID = @ORDERID )
end
if @ENDTIME = '0000'
begin
set @ENDTIME = '2359'
end
update dbo.ITINERARYITEM set
STARTTIME = @STARTTIME,
ENDTIME = @ENDTIME,
NOTES = @NOTES,
PROGRAMID = @PROGRAMID,
ITINERARYID = @ITINERARYID,
NAME = @DESCRIPTION,
ITEMTYPECODE = 1,
INVALIDREASONCODE = 0,
STARTDATE = @STARTDATE,
ENDDATE = @STARTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID
-- Need to create the job, the job occurrence will be created using the trigger
exec dbo.USP_RESOURCE_PROCESS @RESOURCES output, @CHANGEAGENTID, 0;
exec dbo.USP_STAFFRESOURCE_PROCESS @STAFFRESOURCES output, @CHANGEAGENTID, 0, 0, 0;
exec dbo.USP_ITINERARYITEMRESOURCE_GETRESOURCES_UPDATEFROMXML @ID, @RESOURCES, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_ITINERARYITEM_GETSTAFFRESOURCES_UPDATEFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @ITINERARYID;
exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
exec dbo.USP_ITINERARYITEM_RESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID
exec dbo.USP_ITINERARYITEM_STAFFRESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID
if @ISFLATRATE = 1
begin
exec dbo.USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE @ORDERID, null, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ORDERID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ORDERID, @CHANGEAGENTID;
--exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
--exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
-- Generate taxes last
exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ORDERID, @CHANGEAGENTID, @CURRENTDATE;
end
else
begin
exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
-- Generate taxes last
exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;