USP_ITINERARYITEMCUSTOMITEM_ADD
Adds a custom item to an itinerary.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @ITINERARYID | uniqueidentifier | 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_ADD
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null,
@ITINERARYID uniqueidentifier,
@NAME nvarchar(100) = null,
@STARTDATE date = null,
@ENDDATE date = null,
@STARTTIME dbo.UDT_HOURMINUTE = null,
@ENDTIME dbo.UDT_HOURMINUTE = null,
@NOTES nvarchar(500) = '',
@LOCATIONID uniqueidentifier = null,
@MARKLOCATIONBUSY bit = 0,
@RESOURCES xml = null,
@IGNORECONFLICTS bit = 0,
@STAFFRESOURCES xml = null,
@LOCATIONPRICE money = 0.0
)
as
begin
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()
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
begin try
declare @ARRIVALDATE datetime;
declare @STARTDATETIME datetime;
declare @ENDDATETIME datetime;
declare @MAXDATE datetime;
declare @ORDERID uniqueidentifier;
declare @ISFLATRATE bit = 0;
declare @STATUSCODE tinyint;
select
@ARRIVALDATE = ARRIVALDATE,
@ORDERID = RESERVATION.ID,
@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(@ARRIVALDATE,@STARTTIME);
set @ENDDATETIME=dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE,@ENDTIME);
set @MAXDATE = dateadd(d, 4, dbo.UFN_DATE_GETLATESTTIME(@ARRIVALDATE))
if dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME) > @MAXDATE or dbo.UFN_DATE_ADDHOURMINUTE(@ENDDATE, @ENDTIME) > @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
insert into dbo.ITINERARYITEM
(
ID,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
NOTES,
ITINERARYID,
NAME,
ITEMTYPECODE,
BLOCKEVENT,
EVENTLOCATIONID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values
(
@ID,
@STARTTIME,
@ENDTIME,
@STARTDATE,
@ENDDATE,
@NOTES,
@ITINERARYID,
@NAME,
2,
@MARKLOCATIONBUSY,
@LOCATIONID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
if @LOCATIONPRICE is not null and @LOCATIONID is not null
begin
declare @SALESORDERITEMID uniqueidentifier
set @SALESORDERITEMID = newid()
declare @EVENTLOCATIONNAME nvarchar(100)
select
@EVENTLOCATIONNAME = NAME
from dbo.EVENTLOCATION
where ID = @LOCATIONID
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
-- 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_ADDFROMXML @ID, @RESOURCES, @CHANGEAGENTID;
exec dbo.USP_ITINERARYITEM_GETSTAFFRESOURCES_ADDFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID;
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
end