USP_DATAFORMTEMPLATE_ADD_RESERVATIONCOPY_PRELOAD
The load procedure used by the edit dataform template "Reservation Copy Add Data Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTRESERVATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTID | uniqueidentifier | INOUT | Patron |
| @CONTACTRELATIONSHIPID | uniqueidentifier | INOUT | Contact |
| @NAME | nvarchar(100) | INOUT | Reservation name |
| @CONTEXTARRIVALDATE | date | INOUT | |
| @CHECKCAPACITY | bit | INOUT | |
| @CAPACITYNEEDED | int | INOUT | Capacity needed |
| @NUMBEROFDAYS | int | INOUT | |
| @RESOURCES | xml | INOUT | Resources |
| @STAFFRESOURCES | xml | INOUT | Staffing resources |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RESERVATIONCOPY_PRELOAD
(
@CONTEXTRESERVATIONID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null output,
@CONTACTRELATIONSHIPID uniqueidentifier = null output,
@NAME nvarchar(100) = null output,
@CONTEXTARRIVALDATE date = null output,
@CHECKCAPACITY bit = null output,
@CAPACITYNEEDED int = null output,
@NUMBEROFDAYS int = null output,
@RESOURCES xml = null output,
@STAFFRESOURCES xml = null output
)
as
set nocount on;
declare @CONTACTCONSTITUENTID uniqueidentifier;
declare @CONTACTADDRESSID uniqueidentifier;
declare @ISORGANIZATION bit;
declare @CONSTITUENTKEYNAME nvarchar(100);
declare @CONTACTKEYNAME nvarchar(100);
select
@CONSTITUENTID = case when CONSTITUENT.ISINACTIVE = 0 then SALESORDER.CONSTITUENTID else null end,
@ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
@CONSTITUENTKEYNAME = CONSTITUENT.KEYNAME,
@CONTACTCONSTITUENTID = case when CONTACT.ISINACTIVE = 0 then SALESORDER.RECIPIENTID else null end,
@CONTACTRELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID,
@CONTACTKEYNAME = CONTACT.KEYNAME,
@CONTACTADDRESSID = SALESORDER.ADDRESSID,
@CONTEXTARRIVALDATE = RESERVATION.ARRIVALDATE,
@NUMBEROFDAYS = datediff(day, RESERVATION.STARTDATETIME, RESERVATION.ENDDATETIME) + 1
from
dbo.SALESORDER
inner join
dbo.RESERVATION on SALESORDER.ID = RESERVATION.ID
inner join
dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
left outer join
dbo.CONSTITUENT as CONTACT on SALESORDER.RECIPIENTID = CONTACT.ID
where
RESERVATION.ID = @CONTEXTRESERVATIONID;
if @CONSTITUENTID is not null
set @NAME = @CONSTITUENTKEYNAME;
if @ISORGANIZATION = 1 and @CONTACTCONSTITUENTID is not null
set @NAME = @NAME + ' - ' + @CONTACTKEYNAME;
select top 1 @CHECKCAPACITY = case when MAXIMUMCAPACITY > 0 then 1 else 0 end from dbo.GROUPSALESDEFAULT;
set @CAPACITYNEEDED = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@CONTEXTRESERVATIONID);
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 = @CONTEXTRESERVATIONID
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 = @CONTEXTRESERVATIONID
) [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 = @CONTEXTRESERVATIONID
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 = @CONTEXTRESERVATIONID
) [STAFFRESOURCES]
for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64
);
return 0;