USP_ITINERARY_INSERTITEMS
Attempts to clear an itinerary and insert new itinerary items.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @ITINERARYITEMS | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CLEAREXISTINGITEMS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_ITINERARY_INSERTITEMS
(
@ID uniqueidentifier,
@ITINERARYITEMS xml,
@CHANGEAGENTID uniqueidentifier = null,
@CLEAREXISTINGITEMS bit = 1
)
as
begin
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @RESERVATIONID uniqueidentifier
select @RESERVATIONID = RESERVATIONID
from dbo.ITINERARY where ID = @ID
declare @ITEMS table
(
ID uniqueidentifier,
COPYITINERARYITEMID uniqueidentifier,
TRACKITEMID uniqueidentifier,
EVENTID uniqueidentifier,
PROGRAMID uniqueidentifier,
NAME nvarchar(100),
NOTES nvarchar(500),
BLOCKEVENT bit,
STARTTIME dbo.UDT_HOURMINUTE,
ENDTIME dbo.UDT_HOURMINUTE,
STARTDATE datetime,
ENDDATE datetime,
EVENTLOCATIONID uniqueidentifier,
ITEMTYPECODE tinyint,
INVALIDREASON tinyint,
STARTDATETIME datetime,
ENDDATETIME datetime
)
insert into @ITEMS
(
ID,
COPYITINERARYITEMID,
TRACKITEMID,
EVENTID,
PROGRAMID,
NAME,
NOTES,
BLOCKEVENT,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
EVENTLOCATIONID,
ITEMTYPECODE,
INVALIDREASON
)
select
newid(),
T.items.value('(COPYITINERARYITEMID)[1]','uniqueidentifier'),
T.items.value('(TRACKITEMID)[1]','uniqueidentifier'),
T.items.value('(EVENTID)[1]','uniqueidentifier'),
T.items.value('(PROGRAMID)[1]','uniqueidentifier'),
T.items.value('(NAME)[1]','nvarchar(100)'),
T.items.value('(NOTES)[1]','nvarchar(500)'),
T.items.value('(BLOCKEVENT)[1]','bit'),
T.items.value('(STARTTIME)[1]','dbo.UDT_HOURMINUTE'),
T.items.value('(ENDTIME)[1]','dbo.UDT_HOURMINUTE'),
T.items.value('(STARTDATE)[1]','date'),
T.items.value('(ENDDATE)[1]','date'),
T.items.value('(EVENTLOCATIONID)[1]','uniqueidentifier'),
T.items.value('(ITEMTYPECODE)[1]','tinyint'),
0
from @ITINERARYITEMS.nodes('/ITINERARYITEMS/ITEM') T(items)
update @ITEMS set
STARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE([@ITEMS].STARTDATE,[@ITEMS].STARTTIME),
ENDDATETIME = dbo.UFN_DATE_ADDHOURMINUTE([@ITEMS].ENDDATE,[@ITEMS].ENDTIME)
-- Get the total number of people in the itinerary
declare @QUANTITY int
select @QUANTITY = isnull(sum(QUANTITY),0)
from dbo.ITINERARYATTENDEE
where ITINERARYID = @ID
declare @VALIDEVENTSFORITEMTIME table
(
EVENTID uniqueidentifier,
ITEMID uniqueidentifier,
QUANTITY int
)
insert into @VALIDEVENTSFORITEMTIME
select
EVENT.ID,
ITEMS.ID,
EVENTAVAILABILITY.QUANTITY
from dbo.EVENT
inner join @ITEMS ITEMS on ITEMS.PROGRAMID = EVENT.PROGRAMID
inner join dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS() as EVENTAVAILABILITY on EVENTAVAILABILITY.EVENTID = EVENT.ID
where
(
ITEMS.STARTDATETIME >= EVENT.STARTDATETIME and
ITEMS.STARTDATETIME < EVENT.ENDDATETIME
) or
(
ITEMS.ENDDATETIME > EVENT.STARTDATETIME and
ITEMS.ENDDATETIME <= EVENT.ENDDATETIME
) or
(
ITEMS.STARTDATETIME < EVENT.STARTDATETIME and
ITEMS.ENDDATETIME > EVENT.ENDDATETIME
)
-- Get valid events for the date of this itinerary
update @ITEMS set
EVENTID = EVENT.ID,
PROGRAMID = null
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
EVENTONSALE.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 1 and
EVENT.ISACTIVE = 1 and
[@ITEMS].ID = VALIDEVENTS.ITEMID and
VALIDEVENTS.QUANTITY >= @QUANTITY and
not exists (select 1
from dbo.ITINERARYITEM with (nolock)
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
inner join dbo.SALESORDER on ITINERARY.RESERVATIONID = SALESORDER.ID
where SALESORDER.STATUSCODE <> 5 -- MDC - Ignore cancelled
and ITINERARYITEM.BLOCKEVENT = 1
and ITINERARYITEM.EVENTID = EVENT.ID) and
not exists
(
select 1 from dbo.ITINERARYATTENDEE
where
ITINERARYID = @ID and
not exists
(
select 1 from dbo.PROGRAMEVENTPRICE
where PROGRAMEVENTPRICE.EVENTID = EVENT.ID and
PROGRAMEVENTPRICE.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
)
and
not exists
(
select 1 from dbo.PROGRAMPRICE
where
PROGRAMPRICE.PROGRAMID = PROGRAM.ID and
ITINERARYATTENDEE.PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID and
not exists
(
select 1 from dbo.PROGRAMEVENTPRICE
where PROGRAMEVENTPRICE.EVENTID = EVENT.ID
)
)
) and
(
EVENTONSALE.SALESMETHODTYPECODE = 3 and
@CURRENTDATE >= EVENTONSALE.ONSALEDATETIME
)
-- Remove events that do not have a valid time
update @ITEMS set
ITEMTYPECODE = 3
where
ITEMTYPECODE = 0 and
EVENTID is null
-- Retrieve reasons why the event is invalid for invalid items
-- Validate items on all other reasons and remove on condition to narrow
-- the reason why an event is invalid.
-- Once we remove a condition, that condition can remain removed
-- because it is no longer needed to check against whether an item is invalid
if exists (select 1 from @ITEMS where ITEMTYPECODE = 3)
begin
-- No events at the time of the item
update @ITEMS set
INVALIDREASON = 5
where
[@ITEMS].ITEMTYPECODE = 3 and
not exists
(
select 1 from @VALIDEVENTSFORITEMTIME
where [@VALIDEVENTSFORITEMTIME].ITEMID = [@ITEMS].ID
)
-- Invalid price types, because all other validation passed
update @ITEMS set
INVALIDREASON = 4
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
EVENTONSALE.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 3 and
[@ITEMS].INVALIDREASON = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 1 and
EVENT.ISACTIVE = 1 and
[@ITEMS].ID = VALIDEVENTS.ITEMID and
VALIDEVENTS.QUANTITY >= @QUANTITY and
not exists (select 1 from dbo.ITINERARYITEM with (nolock) where ITINERARYITEM.BLOCKEVENT = 1 and ITINERARYITEM.EVENTID = EVENT.ID) and
(
EVENTONSALE.SALESMETHODTYPECODE = 3 and
@CURRENTDATE >= EVENTONSALE.ONSALEDATETIME
)
-- Event is blocked, because all other validation passed
update @ITEMS set
INVALIDREASON = 3
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
EVENTONSALE.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 3 and
[@ITEMS].INVALIDREASON = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 1 and
EVENT.ISACTIVE = 1 and
[@ITEMS].ID = VALIDEVENTS.ITEMID and
VALIDEVENTS.QUANTITY >= @QUANTITY and
(
EVENTONSALE.SALESMETHODTYPECODE = 3 and
@CURRENTDATE >= EVENTONSALE.ONSALEDATETIME
)
-- Invalid quantity, because all other validation passed
update @ITEMS set
INVALIDREASON = 2
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
EVENTONSALE.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 3 and
[@ITEMS].INVALIDREASON = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 1 and
EVENT.ISACTIVE = 1 and
[@ITEMS].ID = VALIDEVENTS.ITEMID and
VALIDEVENTS.QUANTITY < @QUANTITY and
(
EVENTONSALE.SALESMETHODTYPECODE = 3 and
@CURRENTDATE >= EVENTONSALE.ONSALEDATETIME
)
-- Event is not on sale
update @ITEMS set
INVALIDREASON = 8
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 3 and
[@ITEMS].INVALIDREASON = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 1 and
EVENT.ISACTIVE = 1 and
[@ITEMS].ID = VALIDEVENTS.ITEMID
-- Inactive program/event, because all other validation passed
update @ITEMS set
INVALIDREASON = 1
from dbo.EVENT
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID
inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
where
[@ITEMS].ITEMTYPECODE = 3 and
[@ITEMS].INVALIDREASON = 0 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
[@ITEMS].ID = VALIDEVENTS.ITEMID
end
-- Remove inactive daily admission programs
update @ITEMS set
ITEMTYPECODE = 3,
INVALIDREASON = 6
from dbo.PROGRAM
where
[@ITEMS].ITEMTYPECODE = 1 and
[@ITEMS].PROGRAMID = PROGRAM.ID and
PROGRAM.ISACTIVE = 0
-- Remove daily admission programs that are not on sale
update @ITEMS set
ITEMTYPECODE = 3,
INVALIDREASON = 9
where
ITEMTYPECODE = 1 and
dbo.UFN_PROGRAMDAILYADMISSION_ISONSALE(PROGRAMID,@CURRENTDATE,3) = 0
-- Remove blocks on items/locations that cannot block
update @ITEMS set
BLOCKEVENT = 0
where
(
[@ITEMS].BLOCKEVENT = 1 and
[@ITEMS].ITEMTYPECODE = 0 and
exists (select 1 from dbo.ITINERARYITEM with (nolock) where ITINERARYITEM.EVENTID = [@ITEMS].EVENTID)
)
or
(
[@ITEMS].ITEMTYPECODE = 2 and
dbo.UFN_ITINERARYITEM_CANBLOCK([@ITEMS].EVENTLOCATIONID,[@ITEMS].STARTDATE,[@ITEMS].ENDDATE,[@ITEMS].STARTTIME,[@ITEMS].ENDTIME) = 0
)
-- Remove programs that don't have price types that are in the itinerary
update @ITEMS set
ITEMTYPECODE = 3,
INVALIDREASON = 11
from dbo.ITINERARYATTENDEE
where
ITINERARYID = @ID and
ITEMTYPECODE = 1 and
not exists
(select 1 from dbo.PROGRAMPRICE
where PROGRAMPRICE.PROGRAMID = [@ITEMS].PROGRAMID and
ITINERARYATTENDEE.PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID)
-- Get name for events that are now scheduled
update @ITEMS set
NAME = dbo.UFN_TRANSLATIONFUNCTION_EVENT_GETNAME(EVENTID)
where
ITEMTYPECODE = 0 or
(
ITEMTYPECODE = 3 and
EVENTID is not null
)
-- Before we insert items, update Reservation Arrival Time
-- to be the earliest item in the itinerary
declare @RESERVATIONARRIVALDATE date
declare @RESERVATIONARRIVALTIME dbo.UDT_HOURMINUTE
select
@RESERVATIONARRIVALDATE = ARRIVALDATE,
@RESERVATIONARRIVALTIME = ARRIVALTIME
from dbo.RESERVATION
where
ID = @RESERVATIONID
if @RESERVATIONARRIVALDATE is not null and @RESERVATIONARRIVALTIME is not null
begin
declare @MINITINERARYDATE date
declare @MINITINERARYTIME dbo.UDT_HOURMINUTE
select @MINITINERARYDATE = min([@ITEMS].STARTDATE)
from @ITEMS
select @MINITINERARYTIME = min([@ITEMS].STARTTIME)
from @ITEMS
where [@ITEMS].STARTDATE = @MINITINERARYDATE
if @RESERVATIONARRIVALDATE = @MINITINERARYDATE and @RESERVATIONARRIVALTIME > @MINITINERARYTIME
begin
update dbo.RESERVATION set
ARRIVALTIME = @MINITINERARYTIME,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @RESERVATIONID
end
end
-- Now add charged locations
declare @ITINERARYITEMLOCATIONS table
(
ITINERARYITEMID uniqueidentifier,
EVENTLOCATIONID uniqueidentifier,
PRICE money,
SALESORDERITEMID uniqueidentifier,
NAME nvarchar(100)
)
insert into @ITINERARYITEMLOCATIONS
(
ITINERARYITEMID,
EVENTLOCATIONID,
PRICE,
SALESORDERITEMID,
NAME
)
select
ITEMS.ID,
ITEMS.EVENTLOCATIONID,
[SALESORDERITEM].[TOTAL],
newid(),
ITEMS.NAME
from @ITEMS ITEMS
inner join dbo.ITINERARYITEMLOCATION on
ITEMS.COPYITINERARYITEMID = ITINERARYITEMLOCATION.ID
inner join dbo.SALESORDERITEM on
SALESORDERITEM.ID = ITINERARYITEMLOCATION.SALESORDERITEMID
-- Now add track items that have locations that have a default price
insert into @ITINERARYITEMLOCATIONS
(
ITINERARYITEMID,
EVENTLOCATIONID,
PRICE,
SALESORDERITEMID,
NAME
)
select
ITEMS.ID,
ITEMS.EVENTLOCATIONID,
FACILITY.PRICE,
newid(),
ITEMS.NAME
from @ITEMS ITEMS
inner join dbo.FACILITY on
ITEMS.EVENTLOCATIONID = FACILITY.ID
where
ITEMS.COPYITINERARYITEMID is null
-- insert new itinerary items
insert into dbo.ITINERARYITEM
(
ID,
EVENTID,
PROGRAMID,
NAME,
NOTES,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
BLOCKEVENT,
EVENTLOCATIONID,
ITEMTYPECODE,
INVALIDREASONCODE,
ITINERARYID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ID,
EVENTID,
PROGRAMID,
NAME,
NOTES,
STARTTIME,
ENDTIME,
STARTDATE,
ENDDATE,
BLOCKEVENT,
EVENTLOCATIONID,
ITEMTYPECODE,
INVALIDREASON,
@ID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITEMS
-- Now add the itinerary item resources
-- First from tracks
insert into dbo.ITINERARYITEMRESOURCE
(
ITINERARYITEMID,
RESOURCEID,
QUANTITYNEEDED,
PRICE,
PRICINGSTRUCTURECODE,
PERTICKETQUANTITY,
PERTICKETDIVISOR,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
TRACKITEMRESOURCE.RESOURCEID,
TRACKITEMRESOURCE.QUANTITYNEEDED,
COALESCE(RESOURCEPRICING.PRICE, 0),
COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
PERTICKETQUANTITY = RESOURCE.PERTICKETQUANTITY,
PERTICKETDIVISOR = RESOURCE.PERTICKETDIVISOR,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITEMS as ITEMS
inner join TRACKITEMRESOURCE on ITEMS.TRACKITEMID = TRACKITEMRESOURCE.TRACKITEMID
inner join RESOURCE on TRACKITEMRESOURCE.RESOURCEID = RESOURCE.ID
left join dbo.RESOURCEPRICING on RESOURCE.ID = RESOURCEPRICING.ID;
-- Second from itinerary items
insert into dbo.ITINERARYITEMRESOURCE
(
ITINERARYITEMID,
RESOURCEID,
QUANTITYNEEDED,
PRICE,
PRICINGSTRUCTURECODE,
PERTICKETQUANTITY,
PERTICKETDIVISOR,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
ITINERARYITEMRESOURCE.RESOURCEID,
ITINERARYITEMRESOURCE.QUANTITYNEEDED,
COALESCE(RESOURCEPRICING.PRICE, 0),
COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
PERTICKETQUANTITY = RESOURCE.PERTICKETQUANTITY,
PERTICKETDIVISOR = RESOURCE.PERTICKETDIVISOR,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITEMS as ITEMS
inner join ITINERARYITEMRESOURCE on ITEMS.COPYITINERARYITEMID = ITINERARYITEMRESOURCE.ITINERARYITEMID
inner join RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
left join dbo.RESOURCEPRICING on RESOURCE.ID = RESOURCEPRICING.ID;
-- Now add staff resources
-- First create jobs for any of the track items that do not have a job associated.
-- Create a map table so we can set the job ids on the track items as well
declare @JOBMAP table
(
TRACKITEMSTAFFRESOURCEID uniqueidentifier,
JOBID uniqueidentifier,
JOBNAME nvarchar(100),
JOBDESCRIPTION nvarchar(255),
VOLUNTEERTYPEID uniqueidentifier,
FILLEDBYCODE tinyint
);
insert into @JOBMAP
(TRACKITEMSTAFFRESOURCEID, JOBNAME, JOBDESCRIPTION, VOLUNTEERTYPEID, FILLEDBYCODE)
select
TRACKITEMSTAFFRESOURCE.ID,
TRACKITEMSTAFFRESOURCE.JOBNAME,
TRACKITEMSTAFFRESOURCE.JOBDESCRIPTION,
TRACKITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
TRACKITEMSTAFFRESOURCE.FILLEDBYCODE
from @ITEMS as ITEMS
inner join TRACKITEMSTAFFRESOURCE on ITEMS.TRACKITEMID = TRACKITEMSTAFFRESOURCE.TRACKITEMID
where
TRACKITEMSTAFFRESOURCE.JOBID is null and
TRACKITEMSTAFFRESOURCE.FILLEDBYCODE = 0;
-- update the ids with any existing jobs
update @JOBMAP set
JOBID = JOB.ID
from dbo.JOB
inner join @JOBMAP as STAFFRESOURCESTABLE on
STAFFRESOURCESTABLE.JOBNAME = JOB.NAME and
STAFFRESOURCESTABLE.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID
where
(JOBID is null or JOBID = '00000000-0000-0000-0000-000000000000') and
FILLEDBYCODE = 0
-- Finally check that there is only one new job that is going to be created per job name and volunteer type combo
update @JOBMAP set
JOBDESCRIPTION = TOPJOBNAME.JOBDESCRIPTION
from @JOBMAP as STAFFRESOURCESTABLE
inner join
(
select
DISTINCTJOBDESCRIPTION.JOBNAME,
DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID,
(
select top 1 TOPJOBDESCRIPTION.JOBDESCRIPTION
from @JOBMAP as TOPJOBDESCRIPTION
where
TOPJOBDESCRIPTION.JOBNAME = DISTINCTJOBDESCRIPTION.JOBNAME and
TOPJOBDESCRIPTION.VOLUNTEERTYPEID = DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID
) as JOBDESCRIPTION
from @JOBMAP as DISTINCTJOBDESCRIPTION
) as TOPJOBNAME on
TOPJOBNAME.JOBNAME = STAFFRESOURCESTABLE.JOBNAME and
TOPJOBNAME.VOLUNTEERTYPEID = STAFFRESOURCESTABLE.VOLUNTEERTYPEID
where
(STAFFRESOURCESTABLE.JOBID is null or STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000') and
STAFFRESOURCESTABLE.FILLEDBYCODE = 0
declare @DISTINCTJOB table
(
JOBID uniqueidentifier,
JOBNAME nvarchar(100),
JOBDESCRIPTION nvarchar(255)
);
insert into @DISTINCTJOB (JOBNAME, JOBDESCRIPTION)
select distinct
JOBNAME,
JOBDESCRIPTION
from @JOBMAP as STAFFRESOURCESTABLE
where
(STAFFRESOURCESTABLE.JOBID is null or STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000') and
STAFFRESOURCESTABLE.FILLEDBYCODE = 0
update @DISTINCTJOB set JOBID = NEWID();
update @JOBMAP set
JOBID = DISTINCTJOB.JOBID
from @JOBMAP as STAFFRESOURCESTABLE
inner join @DISTINCTJOB as DISTINCTJOB on
STAFFRESOURCESTABLE.JOBNAME = DISTINCTJOB.JOBNAME and
STAFFRESOURCESTABLE.JOBDESCRIPTION = DISTINCTJOB.JOBDESCRIPTION;
-- Create the jobs.
-- Left outer join on Job is to make sure JOBID is not already an existing job.
insert into dbo.JOB
(
ID,
NAME,
DESCRIPTION,
VOLUNTEERTYPEID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select distinct
JOBMAP.JOBID,
JOBMAP.JOBNAME,
JOBMAP.JOBDESCRIPTION,
JOBMAP.VOLUNTEERTYPEID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @JOBMAP as JOBMAP
left outer join dbo.JOB on JOBMAP.JOBID = JOB.ID
where
JOB.ID is null and
JOBMAP.FILLEDBYCODE = 0
-- Update the track item staff resources to have the correct jobs
update dbo.TRACKITEMSTAFFRESOURCE set
JOBID = JOBMAP.JOBID,
JOBNAME = JOB.NAME,
JOBDESCRIPTION = JOB.DESCRIPTION,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @JOBMAP as JOBMAP
inner join dbo.JOB on JOBMAP.JOBID = JOB.ID
where JOBMAP.TRACKITEMSTAFFRESOURCEID = TRACKITEMSTAFFRESOURCE.ID
/* Get the total attendees for the itinerary */
declare @ATTENDEECOUNT decimal;
select
@ATTENDEECOUNT = sum(ITINERARYATTENDEE.QUANTITY)
from dbo.ITINERARYATTENDEE
where ITINERARYATTENDEE.ITINERARYID = @ID
/* Now that all track items have a job create the itinerary item staff resources */
insert into dbo.ITINERARYITEMSTAFFRESOURCE
(
ITINERARYITEMID,
VOLUNTEERTYPEID,
QUANTITYNEEDED,
JOBID,
FILLEDBYCODE,
PRICE,
PRICINGSTRUCTURECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
TRACKITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
case
when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then
ceiling(@ATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE)
else TRACKITEMSTAFFRESOURCE.QUANTITYNEEDED
end,
TRACKITEMSTAFFRESOURCE.JOBID,
TRACKITEMSTAFFRESOURCE.FILLEDBYCODE,
COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0),
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITEMS as ITEMS
inner join TRACKITEMSTAFFRESOURCE on
ITEMS.TRACKITEMID = TRACKITEMSTAFFRESOURCE.TRACKITEMID
left join dbo.VOLUNTEERTYPEPRICING on
TRACKITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
inner join dbo.VOLUNTEERTYPE on
TRACKITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID;
/* Second from itinerary items, these always have jobs associated with them */
insert into dbo.ITINERARYITEMSTAFFRESOURCE
(
ITINERARYITEMID,
VOLUNTEERTYPEID,
QUANTITYNEEDED,
JOBID,
FILLEDBYCODE,
PRICE,
PRICINGSTRUCTURECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITEMS.ID,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
case when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then ceiling(@ATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE) else ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED end,
ITINERARYITEMSTAFFRESOURCE.JOBID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0),
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITEMS as ITEMS
inner join ITINERARYITEMSTAFFRESOURCE on ITEMS.COPYITINERARYITEMID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
left join dbo.VOLUNTEERTYPEPRICING on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
inner join dbo.VOLUNTEERTYPE on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
-- JLM 3/1/2012 : WI 125730
/*
With a large number of events in the system, conflict checking
causes a significant loss in performance. This is due to the
fact that we are calling 3 separate scalar functions per entry
in @ITEMS to check for conflicts that each query the entire
event table for conflicts. If we filter the events such that we
only check events that fall into the applicable time frame, we
gain much of that performance back since we no longer have to do
6 datetime comparisons per event, per function, per @ITEM.
We send in this smaller set of events so that the costly comparisons
in the scalar functions are only done on events that could possibly
conflict.
I had to choose between sending in XML and populating a persisted table
(can't use # type temp tables in a function), and the performance difference
was negligible considering how small this set will be since group visits
generally span 1 day.
This improvement should serve it's purpose until conflict checking gets some
much needed refactoring.
*/
declare @MINSTART datetime, @MAXEND datetime;
set @MINSTART = (select min(STARTDATETIME) from @ITEMS);
set @MAXEND = (select max(ENDDATETIME) from @ITEMS);
declare @FILTEREDEVENTS xml = (
select
ID,
PROGRAMID,
EVENTLOCATIONID,
STARTDATETIME,
ENDDATETIME
from dbo.EVENT with (nolock)
where
(
(
@MINSTART >= EVENT.STARTDATETIME and
@MINSTART < EVENT.ENDDATETIME
) or
(
@MAXEND > EVENT.STARTDATETIME and
@MAXEND <= EVENT.ENDDATETIME
) or
(
@MINSTART < EVENT.STARTDATETIME and
@MAXEND > EVENT.ENDDATETIME
)
)for xml raw('ITEM'),type,elements,root('CONFLICTEVENTS'),BINARY BASE64
)
-- Now we can check for conflicts with the itinerary items
-- Doing conflict checking before Update on Itinerary Item table for some deadlock prevention
delete from @ITEMS
where
not exists
(
select 1 from dbo.ITINERARYITEM with (nolock)
where
ITINERARYITEM.ITINERARYID = @ID and
ITINERARYITEM.ID = [@ITEMS].ID and
ITINERARYITEM.INVALIDREASONCODE = 0 and
dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST_WITHFILTEREDEVENTS
(
[@ITEMS].STARTDATETIME,
[@ITEMS].ENDDATETIME,
case
when ITINERARYITEM.EVENTLOCATIONID is null then ''
else '<LOCATIONS><ITEM><EVENTLOCATIONID>' + cast(ITINERARYITEM.EVENTLOCATIONID as nvarchar(36)) + '</EVENTLOCATIONID></ITEM></LOCATIONS>'
end,
dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
null,
ITINERARYITEM.ITINERARYID,
ITINERARYITEM.ID,
0, -- Do not ignore super record
0, -- Ignore record
1, -- Ignore sub record
0, -- Do not ignore subrecords of record
@FILTEREDEVENTS
) = 1
)
update dbo.ITINERARYITEM set
INVALIDREASONCODE = 10, -- Invalid reason 10 for conflicts
ITEMTYPECODE = 3, --Make sure the event is marked as unscheduled
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from @ITEMS as ITEMS
where
ITINERARYITEM.ITINERARYID = @ID and
ITEMS.ID = ITINERARYITEM.ID
-- Insert sales order items for locations that have a price and are not unscheduled
if (select count(1) from @ITINERARYITEMLOCATIONS) > 0
begin
-- Insert sales order items for locations that have a price and are not unscheduled
delete from @ITINERARYITEMLOCATIONS
from dbo.ITINERARYITEM
where
ITINERARYITEM.ID = [@ITINERARYITEMLOCATIONS].ITINERARYITEMID and
ITINERARYITEM.ITEMTYPECODE = 3
insert into dbo.SALESORDERITEM
(
ID,
SALESORDERID,
TYPECODE,
DESCRIPTION,
QUANTITY,
FLATRATEPRICE,
PRICINGSTRUCTURECODE,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
SALESORDERITEMID,
@RESERVATIONID,
7,
LOCATIONS.NAME + ' - ' + coalesce(EVENTLOCATION.NAME, ''),
1,
LOCATIONS.PRICE,
2,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITINERARYITEMLOCATIONS LOCATIONS
inner join dbo.EVENTLOCATION on
LOCATIONS.EVENTLOCATIONID = EVENTLOCATION.ID
insert into dbo.SALESORDERITEMFACILITY
(
ID,
EVENTLOCATIONID,
EVENTLOCATIONNAME,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
[@ITINERARYITEMLOCATIONS].SALESORDERITEMID,
[@ITINERARYITEMLOCATIONS].EVENTLOCATIONID,
EVENTLOCATION.NAME,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITINERARYITEMLOCATIONS
inner join dbo.EVENTLOCATION on
EVENTLOCATION.ID = [@ITINERARYITEMLOCATIONS].EVENTLOCATIONID
insert into dbo.ITINERARYITEMLOCATION
(
ID,
SALESORDERITEMID,
ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ITINERARYITEMID,
SALESORDERITEMID,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @ITINERARYITEMLOCATIONS
end
-- update Sales order items for the resources
exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC @ID, @RESERVATIONID, @CHANGEAGENTID
exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @ID, @RESERVATIONID, @CHANGEAGENTID
-- call the bulk versions to update all itinerary item records
exec dbo.USP_ITINERARYITEMS_RESOURCES_SALESORDERSYNC @ID, @RESERVATIONID, @CHANGEAGENTID
exec dbo.USP_ITINERARYITEMS_STAFFRESOURCES_SALESORDERSYNC @ID, @RESERVATIONID, @CHANGEAGENTID
-- update Sales order Items for Itinerary items (Includes taxes for resource line items, flat rate calculations, etc)
exec dbo.USP_ITINERARY_UPDATEITEMQUANTITIES @ID, @CHANGEAGENTID, @CURRENTDATE;
end