USP_DATAFORMTEMPLATE_ADD_TRACKFROMITINERARY
The save procedure used by the add dataform template "Track From Itinerary Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @ITINERARYID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_TRACKFROMITINERARY
(
@ID uniqueidentifier = null output,
@ITINERARYID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = null,
@DESCRIPTION nvarchar(255) = ''
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @ARRIVALDATE datetime;
select
@ARRIVALDATE = RESERVATION.ARRIVALDATE
from
dbo.RESERVATION
inner join
dbo.ITINERARY on RESERVATION.ID = ITINERARY.RESERVATIONID
where
ITINERARY.ID = @ITINERARYID;
begin try
insert into dbo.TRACK
(
ID,
NAME,
[DESCRIPTION],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@NAME,
@DESCRIPTION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- Track resources
insert into dbo.TRACKRESOURCE
(
ID,
TRACKID,
RESOURCEID,
QUANTITYNEEDED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
RESOURCEID,
QUANTITYNEEDED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.ITINERARYRESOURCE
where
ITINERARYRESOURCE.ITINERARYID = @ITINERARYID
-- Track staff resources
insert into dbo.TRACKSTAFFRESOURCE
(
ID,
TRACKID,
VOLUNTEERTYPEID,
QUANTITYNEEDED,
FILLEDBYCODE,
JOBID,
JOBNAME,
JOBDESCRIPTION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
case when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 0 else ITINERARYSTAFFRESOURCE.QUANTITYNEEDED end,
ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
ITINERARYSTAFFRESOURCE.JOBID,
coalesce(JOB.NAME, ''),
coalesce(JOB.DESCRIPTION, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.ITINERARYSTAFFRESOURCE
left outer join dbo.JOB
on ITINERARYSTAFFRESOURCE.JOBID = JOB.ID
inner join dbo.VOLUNTEERTYPE
on VOLUNTEERTYPE.ID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID
where ITINERARYSTAFFRESOURCE.ITINERARYID = @ITINERARYID
-- build a map between track item and itinerary item ids
declare @ITEMIDMAP TABLE
(
TRACKITEMID uniqueidentifier,
ITINERARYITEMID uniqueidentifier
)
insert into @ITEMIDMAP
(
TRACKITEMID,
ITINERARYITEMID
)
select
newid(),
ID
from
dbo.ITINERARYITEM
where
ITINERARYITEM.ITINERARYID = @ITINERARYID
insert into dbo.TRACKITEM
(
ID,
TRACKID,
NAME,
PROGRAMID,
EVENTLOCATIONID,
STARTDAY,
STARTTIME,
ENDDAY,
ENDTIME,
BLOCKEVENT,
NOTES,
TYPECODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ITEMIDMAP.TRACKITEMID,
@ID,
ITINERARYITEM.NAME,
isnull(ITINERARYITEM.PROGRAMID, EVENT.PROGRAMID),
ITINERARYITEM.EVENTLOCATIONID,
datediff(day, @ARRIVALDATE, ITINERARYITEM.STARTDATE),
ITINERARYITEM.STARTTIME,
datediff(day, @ARRIVALDATE, ITINERARYITEM.ENDDATE),
ITINERARYITEM.ENDTIME,
ITINERARYITEM.BLOCKEVENT,
ITINERARYITEM.NOTES,
ITINERARYITEM.ITEMTYPECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.ITINERARYITEM
inner join
@ITEMIDMAP ITEMIDMAP on dbo.ITINERARYITEM.ID = ITEMIDMAP.ITINERARYITEMID
left outer join
dbo.EVENT on ITINERARYITEM.EVENTID = EVENT.ID
where
ITINERARYITEM.ITINERARYID = @ITINERARYID and
ITINERARYITEM.ITEMTYPECODE <> 3;
-- Track item resources
insert into dbo.TRACKITEMRESOURCE
(
ID,
TRACKITEMID,
RESOURCEID,
QUANTITYNEEDED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
ITEMIDMAP.TRACKITEMID,
RESOURCEID,
QUANTITYNEEDED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.ITINERARYITEMRESOURCE
inner join @ITEMIDMAP ITEMIDMAP
on dbo.ITINERARYITEMRESOURCE.ITINERARYITEMID = ITEMIDMAP.ITINERARYITEMID
inner join dbo.ITINERARYITEM
on ITINERARYITEM.ID = ITEMIDMAP.ITINERARYITEMID
where ITINERARYITEM.ITEMTYPECODE <> 3;
-- Track item staff resources
insert into dbo.TRACKITEMSTAFFRESOURCE
(
ID,
TRACKITEMID,
VOLUNTEERTYPEID,
QUANTITYNEEDED,
FILLEDBYCODE,
JOBID,
JOBNAME,
JOBDESCRIPTION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
ITEMIDMAP.TRACKITEMID,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
case when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 0 else ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED end,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
ITINERARYITEMSTAFFRESOURCE.JOBID,
coalesce(JOB.NAME, ''),
coalesce(JOB.DESCRIPTION, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.ITINERARYITEMSTAFFRESOURCE
left outer join dbo.JOB
on ITINERARYITEMSTAFFRESOURCE.JOBID = JOB.ID
inner join @ITEMIDMAP ITEMIDMAP
on dbo.ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITEMIDMAP.ITINERARYITEMID
inner join dbo.ITINERARYITEM
on ITINERARYITEM.ID = ITEMIDMAP.ITINERARYITEMID
inner join dbo.VOLUNTEERTYPE
on VOLUNTEERTYPE.ID = ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
where ITINERARYITEM.ITEMTYPECODE <> 3;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;