USP_DATAFORMTEMPLATE_EDITLOAD_ITINERARYGROUP

The load procedure used by the edit dataform template "Itinerary Group 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.
@LEADERID	uniqueidentifier	INOUT	Leader
@NAME	nvarchar(154)	INOUT	Name
@ATTENDEES	xml	INOUT	Visitors
@RESERVATIONID	uniqueidentifier	INOUT
@ARRIVALDATE	date	INOUT
@EXCEEDCAPACITY	bit	INOUT	Exceed capacity available
@CHECKCAPACITY	bit	INOUT	Check capacity
@GROUPSALESGROUPTYPECODEID	uniqueidentifier	INOUT	Group type
@GROUPSALESGROUPTYPECODE	nvarchar(100)	INOUT	Group type
@IGNORECONFLICTS	bit	INOUT	Ignore conflicts when saving
@RESOURCES	xml	INOUT
@STAFFRESOURCES	xml	INOUT
@STARTDATE	datetime	INOUT
@STARTTIME	UDT_HOURMINUTE	INOUT
@ENDDATE	datetime	INOUT
@ENDTIME	UDT_HOURMINUTE	INOUT
@ITINERARYITEMSEXIST	bit	INOUT
@CAPACITYAVAILABLE	int	INOUT	Capacity available

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ITINERARYGROUP(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @LEADERID uniqueidentifier = null output,
    @NAME nvarchar(154) = null output,
    @ATTENDEES xml = null output,
    @RESERVATIONID uniqueidentifier = null output,
    @ARRIVALDATE date = null output,
    @EXCEEDCAPACITY bit = null output,
    @CHECKCAPACITY bit = null output,
    @GROUPSALESGROUPTYPECODEID uniqueidentifier = null output,
    @GROUPSALESGROUPTYPECODE nvarchar(100) = null output,
    @IGNORECONFLICTS bit = null output,
    @RESOURCES xml = null output,
    @STAFFRESOURCES xml = null output,
    @STARTDATE datetime = null output,
    @STARTTIME UDT_HOURMINUTE = null output,
    @ENDDATE datetime = null output,
    @ENDTIME UDT_HOURMINUTE = null output,
    @ITINERARYITEMSEXIST bit = null output,
    @CAPACITYAVAILABLE int = null output
)
as

    set nocount on;

    set @DATALOADED = 0
    set @TSLONG = 0

    select
        @DATALOADED = 1,
        @EXCEEDCAPACITY = 0,
        @IGNORECONFLICTS = 0,
        @TSLONG = ITINERARY.TSLONG,
        @LEADERID = ITINERARY.LEADERID,
        @NAME = ITINERARY.NAME,
        @RESERVATIONID = ITINERARY.RESERVATIONID,
        @ARRIVALDATE = isnull(ITINERARY.STARTDATETIME, ARRIVALDATE),
        @GROUPSALESGROUPTYPECODEID = ITINERARY.GROUPSALESGROUPTYPECODEID,
        @GROUPSALESGROUPTYPECODE = GROUPSALESGROUPTYPECODE.DESCRIPTION,
        @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(isnull(ITINERARY.STARTDATETIME, RESERVATION.ARRIVALDATE)),
        @ENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(isnull(ITINERARY.ENDDATETIME, RESERVATION.ARRIVALDATE)),
        @STARTTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
        @ENDTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME)
    from dbo.ITINERARY
    inner join dbo.RESERVATION on 
        RESERVATION.ID = ITINERARY.RESERVATIONID
    left outer join dbo.GROUPSALESGROUPTYPECODE on 
        ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPSALESGROUPTYPECODE.ID
    where ITINERARY.ID = @ID;

    if @DATALOADED = 1
    begin
        select @CHECKCAPACITY = case when (MAXIMUMCAPACITY > 0) then  1 else 0 end
        from dbo.GROUPSALESDEFAULT

        if exists 
            (
                select 1 from dbo.ITINERARYITEM 
                where 
                    ITINERARYITEM.ITINERARYID = @ID and 
                    ITINERARYITEM.ITEMTYPECODE <> 3
            )
            set @ITINERARYITEMSEXIST = 1;
        else
            set @ITINERARYITEMSEXIST = 0;

        set @ATTENDEES = dbo.UFN_ITINERARY_GETATTENDEES_TOITEMLISTXML(@ID)

        -- Loading Resources for Conflict Checking
        -- Per ticket resources will be multiplied by attendees at time of checking
        set @RESOURCES = 
        (
            select
                ID,
                ITINERARYID,
                ITINERARYITEMID,
                RESOURCEID,
                QUANTITYNEEDED,
                ISPERTICKETITEM,
                PERTICKETDIVISOR
            from
            (
                select 
                    ITINERARYRESOURCE.ID,
                    @ID as ITINERARYID,
                    null as ITINERARYITEMID,
                    RESOURCEID,
                    case RESOURCE.ISPERTICKETITEM
                        when 0 then
                            ITINERARYRESOURCE.QUANTITYNEEDED
                        else
                            ITINERARYRESOURCE.PERTICKETQUANTITY
                    end as QUANTITYNEEDED,
                    RESOURCE.ISPERTICKETITEM,
                    RESOURCE.PERTICKETDIVISOR
                from dbo.ITINERARYRESOURCE
                inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
                where ITINERARYID = @ID    

                union all

                select 
                    ITINERARYITEMRESOURCE.ID,
                    @ID as ITINERARYID,
                    ITINERARYITEM.ID as ITINERARYITEMID,
                    RESOURCEID,
                    case RESOURCE.ISPERTICKETITEM
                        when 0 then
                            ITINERARYITEMRESOURCE.QUANTITYNEEDED
                        else
                            ITINERARYITEMRESOURCE.PERTICKETQUANTITY
                    end as QUANTITYNEEDED,
                    RESOURCE.ISPERTICKETITEM,
                    RESOURCE.PERTICKETDIVISOR
                from dbo.ITINERARYITEMRESOURCE
                inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
                inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                where ITINERARYID = @ID    
            ) as RE
            for xml raw('ITEM'),type,elements,root('RESOURCES'),BINARY BASE64
        )    

        set @STAFFRESOURCES = 
        (
            select
                ID,
                ITINERARYID,
                ITINERARYITEMID,
                VOLUNTEERTYPEID,
                QUANTITYNEEDED,
                FILLEDBYCODE,
                CAPACITYPERRESOURCE
            from
            (
                select
                    ITINERARYSTAFFRESOURCE.ID,
                    @ID as ITINERARYID,
                    null as ITINERARYITEMID,
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                    ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
                    VOLUNTEERTYPE.CAPACITYPERRESOURCE
                from dbo.ITINERARYSTAFFRESOURCE
                inner join dbo.VOLUNTEERTYPE on 
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                where 
                    ITINERARYSTAFFRESOURCE.ITINERARYID = @ID and
                    ITINERARYSTAFFRESOURCE.FILLEDBYCODE = 0

                union all

                select
                    ITINERARYITEMSTAFFRESOURCE.ID,
                    @ID as ITINERARYID,
                    ITINERARYITEM.ID as ITINERARYITEMID,
                    ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
                    ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
                    VOLUNTEERTYPE.CAPACITYPERRESOURCE
                from dbo.ITINERARYITEMSTAFFRESOURCE
                inner join dbo.VOLUNTEERTYPE on 
                    ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                inner join dbo.ITINERARYITEM on ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                where 
                    ITINERARYITEM.ITINERARYID = @ID and
                    ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE = 0
            ) as SRE
            for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),BINARY BASE64
        )

        set @CAPACITYAVAILABLE = dbo.UFN_GROUPSALESCAPACITY_MINCAPACITYREMAININGFORDATES(@STARTDATE, @ENDDATE, @ID, 1);  -- Itinerary
    end

return 0;