USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATION_MOVE
The load procedure used by the edit dataform template "Reservation Move Edit Data Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @TSLONG | bigint | INOUT | Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record. |
| @ARRIVALDATE | date | INOUT | Current visit date |
| @NEWVISITDATE | date | INOUT | New visit date |
| @IGNOREITINERARYRESOURCECONFLICTS | bit | INOUT | Ignore itinerary resource conflicts |
| @CHECKCAPACITY | bit | INOUT | |
| @CAPACITYNEEDED | int | INOUT | Capacity needed |
| @NUMBEROFDAYS | int | INOUT | |
| @EXCEEDCAPACITY | bit | INOUT | Exceed capacity available |
| @RESOURCES | xml | INOUT | Resources |
| @STAFFRESOURCES | xml | INOUT | Staffing resources |
| @NAME | nvarchar(100) | INOUT | Reservation name |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RESERVATION_MOVE
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@TSLONG bigint = 0 output,
@ARRIVALDATE date = null output,
@NEWVISITDATE date = null output,
@IGNOREITINERARYRESOURCECONFLICTS bit = null output,
@CHECKCAPACITY bit = null output,
@CAPACITYNEEDED int = null output,
@NUMBEROFDAYS int = null output,
@EXCEEDCAPACITY bit = null output,
@RESOURCES xml = null output,
@STAFFRESOURCES xml = null output,
@NAME nvarchar(100) = null output
)
as
set nocount on;
set @DATALOADED = 0
set @TSLONG = 0
select
@DATALOADED = 1,
@TSLONG = RESERVATION.TSLONG,
@ARRIVALDATE = ARRIVALDATE,
@IGNOREITINERARYRESOURCECONFLICTS = 0,
@NUMBEROFDAYS = datediff(day, RESERVATION.STARTDATETIME, RESERVATION.ENDDATETIME) + 1,
@NAME = RESERVATION.NAME
from dbo.RESERVATION
inner join dbo.SALESORDER on
SALESORDER.ID = RESERVATION.ID
where
RESERVATION.ID = @ID
if @DATALOADED = 1
begin
select top 1 @CHECKCAPACITY = case when MAXIMUMCAPACITY > 0 then 1 else 0 end from dbo.GROUPSALESDEFAULT;
set @CAPACITYNEEDED = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@ID);
end
set @RESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
RESOURCEID,
ISPERTICKETITEM,
ISREQUIREDRESOURCE,
REQUIREDRESOURCEQUANTITY,
STARTTIME,
ENDTIME
from
(
-- All itinerary resources
select
ITINERARYRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
ITINERARYRESOURCE.QUANTITYNEEDED,
ITINERARYRESOURCE.RESOURCEID,
RESOURCE.ISPERTICKETITEM,
case when (GROUPTYPEREQUIREDRESOURCE.ID is null) then 0 else 1 end as ISREQUIREDRESOURCE,
coalesce(GROUPTYPEREQUIREDRESOURCE.QUANTITYNEEDED, 0) as REQUIREDRESOURCEQUANTITY,
[dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.STARTDATETIME) as STARTTIME,
[dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.ENDDATETIME) as ENDTIME
from dbo.ITINERARYRESOURCE
inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
left outer join dbo.GROUPTYPEREQUIREDRESOURCE on
(ITINERARYRESOURCE.RESOURCEID = GROUPTYPEREQUIREDRESOURCE.RESOURCEID) and
(ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDRESOURCE.GROUPSALESGROUPTYPECODEID)
where
ITINERARY.RESERVATIONID = @ID
union all
-- All itinerary item resources
select
ITINERARYITEMRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
ITINERARYITEMRESOURCE.QUANTITYNEEDED,
ITINERARYITEMRESOURCE.RESOURCEID,
RESOURCE.ISPERTICKETITEM,
0 as ISREQUIREDRESOURCE,
0 as REQUIREDRESOURCEQUANTITY,
ITINERARYITEM.STARTTIME,
ITINERARYITEM.ENDTIME
from dbo.ITINERARYITEMRESOURCE
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
where
ITINERARY.RESERVATIONID = @ID
) [RESOURCES]
for xml raw('ITEM'),type,elements,root('RESOURCES'),binary base64
);
set @STAFFRESOURCES =
(
select
ID,
ITINERARYID,
ITINERARYITEMID,
QUANTITYNEEDED,
VOLUNTEERTYPEID,
JOBID,
JOBOCCURRENCEID,
FILLEDBYCODE,
ISREQUIREDRESOURCE,
REQUIREDRESOURCEQUANTITY,
STARTTIME,
ENDTIME
from
(
select
ITINERARYSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
null as ITINERARYITEMID,
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYSTAFFRESOURCE.JOBID,
ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
case when (GROUPTYPEREQUIREDSTAFFRESOURCE.ID is null) then 0 else 1 end as ISREQUIREDRESOURCE,
coalesce(GROUPTYPEREQUIREDSTAFFRESOURCE.QUANTITYNEEDED, 0) as REQUIREDRESOURCEQUANTITY,
[dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.STARTDATETIME) as STARTTIME,
[dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.ENDDATETIME) as ENDTIME
from dbo.ITINERARYSTAFFRESOURCE
inner join dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
left outer join dbo.JOB on ITINERARYSTAFFRESOURCE.JOBID = JOB.ID
left outer join dbo.GROUPTYPEREQUIREDSTAFFRESOURCE on
(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = GROUPTYPEREQUIREDSTAFFRESOURCE.VOLUNTEERTYPEID) and
(ITINERARYSTAFFRESOURCE.JOBID = GROUPTYPEREQUIREDSTAFFRESOURCE.JOBID) and
(ITINERARYSTAFFRESOURCE.FILLEDBYCODE = GROUPTYPEREQUIREDSTAFFRESOURCE.FILLEDBYCODE) and
(ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID)
left outer join dbo.VOLUNTEERTYPE on
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @ID
union all
select
ITINERARYITEMSTAFFRESOURCE.ID AS ID,
ITINERARY.ID as ITINERARYID,
ITINERARYITEM.ID as ITINERARYITEMID,
ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
ITINERARYITEMSTAFFRESOURCE.JOBID,
ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID,
ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
0 as ISREQUIREDRESOURCE,
0 as REQUIREDRESOURCEQUANTITY,
ITINERARYITEM.STARTTIME,
ITINERARYITEM.ENDTIME
from dbo.ITINERARYITEMSTAFFRESOURCE
inner join dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
left outer join dbo.JOB on ITINERARYITEMSTAFFRESOURCE.JOBID = JOB.ID
left outer join dbo.VOLUNTEERTYPE on
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
where
ITINERARY.RESERVATIONID = @ID
) [STAFFRESOURCES]
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
);
return 0;