USP_ITINERARYITEMCUSTOMITEM_EDIT
Allows the user to edit an existing custom item on an itinerary.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @ITINERARYID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @NAME | nvarchar(100) | IN | |
| @STARTDATE | date | IN | |
| @ENDDATE | date | IN | |
| @STARTTIME | UDT_HOURMINUTE | IN | |
| @ENDTIME | UDT_HOURMINUTE | IN | |
| @NOTES | nvarchar(500) | IN | |
| @LOCATIONID | uniqueidentifier | IN | |
| @MARKLOCATIONBUSY | bit | IN | |
| @RESOURCES | xml | IN | |
| @IGNORECONFLICTS | bit | IN | |
| @STAFFRESOURCES | xml | IN | |
| @LOCATIONPRICE | money | IN |
Definition
Copy
CREATE procedure dbo.USP_ITINERARYITEMCUSTOMITEM_EDIT
(
@ID uniqueidentifier,
@ITINERARYID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null,
@NAME nvarchar(100),
@STARTDATE date,
@ENDDATE date,
@STARTTIME dbo.UDT_HOURMINUTE,
@ENDTIME dbo.UDT_HOURMINUTE,
@NOTES nvarchar(500),
@LOCATIONID uniqueidentifier,
@MARKLOCATIONBUSY bit,
@RESOURCES xml,
@IGNORECONFLICTS bit,
@STAFFRESOURCES xml = null,
@LOCATIONPRICE money = 0.0
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CURRENTDATE is null
set @CURRENTDATE = getdate()
begin try
declare @ARRIVALDATE datetime;
declare @ARRIVALTIME UDT_HOURMINUTE;
declare @STARTDATETIME datetime;
declare @ENDDATETIME datetime;
declare @MAXDATE 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(@ENDDATE, @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 @LOCATIONID is not null
begin
declare @LOCATIONS xml = '<LOCATIONS><ITEM><EVENTLOCATIONID>' + convert(nvarchar(36), @LOCATIONID) + '</EVENTLOCATIONID></ITEM></LOCATIONS>'
if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
(
@ITINERARYITEMSTART, @ITINERARYITEMEND,
@LOCATIONS, null, 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_LOCATIONCONFLICTSEXIST', 13, 1);
return 1;
end
end
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
@ARRIVALDATE = ARRIVALDATE,
@ARRIVALTIME = ARRIVALTIME,
@ISFLATRATE = case when PRICINGCODE = 1 then 1 else 0 end,
@STATUSCODE = SALESORDER.STATUSCODE
from dbo.RESERVATION
inner join dbo.ITINERARY on RESERVATION.ID = ITINERARY.RESERVATIONID
inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
where ITINERARY.ID = @ITINERARYID
if @STATUSCODE in (1, 5)
raiserror('BBERR_INVALIDSTATUS', 13, 1);
set @STARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME);
set @ENDDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(@ENDDATE, @ENDTIME);
if dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE, @ARRIVALTIME) > @STARTDATETIME
raiserror('BBERR_INVALIDDATE', 13, 1)
set @MAXDATE = dateadd(d, 4, dbo.UFN_DATE_GETLATESTTIME(@ARRIVALDATE))
if @STARTDATETIME > @MAXDATE or @ENDDATETIME > @MAXDATE
raiserror('BBERR_OUTSIDEDATERANGE', 13, 1)
declare @EDATE datetime;
declare @SDATE datetime;
set @EDATE = dbo.UFN_DATE_ADDHOURMINUTE(@ENDDATE, @ENDTIME);
set @SDATE = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME);
if @ENDTIME = '0000'
begin
set @ENDTIME = '2359'
end
update dbo.ITINERARYITEM set
NAME = @NAME,
STARTTIME = @STARTTIME,
ENDTIME = @ENDTIME,
NOTES = @NOTES,
BLOCKEVENT = @MARKLOCATIONBUSY,
EVENTLOCATIONID = @LOCATIONID,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
declare @ORDERID uniqueidentifier
select @ORDERID = RESERVATION.ID
from dbo.RESERVATION
inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
inner join dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARYITEM.ID = @ID
declare @SALESORDERITEMID uniqueidentifier
select
@SALESORDERITEMID = SALESORDERITEMID
from dbo.ITINERARYITEMLOCATION
where
ITINERARYITEMLOCATION.ID = @ID
if @LOCATIONID is not null
begin
declare @EVENTLOCATIONNAME nvarchar(100)
select
@EVENTLOCATIONNAME = NAME
from dbo.EVENTLOCATION
where ID = @LOCATIONID
if @SALESORDERITEMID is not null
begin
update dbo.SALESORDERITEM set
FLATRATEPRICE = @LOCATIONPRICE,
DESCRIPTION = @NAME + ' - ' + coalesce(@EVENTLOCATIONNAME,''),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.EVENTLOCATION
where
EVENTLOCATION.ID = @LOCATIONID and
SALESORDERITEM.ID = @SALESORDERITEMID and
(
FLATRATEPRICE <> @LOCATIONPRICE or
DESCRIPTION <> @NAME + ' - ' + coalesce(@EVENTLOCATIONNAME,'')
)
update dbo.SALESORDERITEMFACILITY set
EVENTLOCATIONID = @LOCATIONID,
EVENTLOCATIONNAME = @EVENTLOCATIONNAME,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
SALESORDERITEMFACILITY.ID = @SALESORDERITEMID and
EVENTLOCATIONID <> @LOCATIONID
end
else
begin
set @SALESORDERITEMID = newid()
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
FLATRATEPRICE,
PRICINGSTRUCTURECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@SALESORDERITEMID,
@ORDERID,
7,
@NAME + ' - ' + coalesce(@EVENTLOCATIONNAME, ''),
1,
@LOCATIONPRICE,
2,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.SALESORDERITEMFACILITY
(
ID,
EVENTLOCATIONID,
EVENTLOCATIONNAME,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@SALESORDERITEMID,
@LOCATIONID,
@EVENTLOCATIONNAME,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.ITINERARYITEMLOCATION
(
ID,
SALESORDERITEMID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
@SALESORDERITEMID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
end
end
else if @SALESORDERITEMID is not null
begin
exec dbo.USP_SALESORDERITEM_DELETE @SALESORDERITEMID, @CHANGEAGENTID;
end
-- 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
exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ORDERID, @CHANGEAGENTID, @CURRENTDATE;
-- Generate taxes last
exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
if @ISFLATRATE = 1
exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ORDERID, @CHANGEAGENTID, @CURRENTDATE;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0