USP_DATAFORMTEMPLATE_VIEW_ITINERARYINFORMATIONDETAIL

The load procedure used by the view dataform template "Itinerary Information Detail View 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.
@TRACKNAME	nvarchar(100)	INOUT	Track
@ATTENDEES	xml	INOUT	Visitors
@ITEMS	xml	INOUT	Items
@COMBINEDRESOURCES	xml	INOUT	Resources
Definition

Copy

            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ITINERARYINFORMATIONDETAIL
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @TRACKNAME nvarchar(100) = null output,
                @ATTENDEES xml = null output,
                @ITEMS xml = null output,
                @COMBINEDRESOURCES xml = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;

                select 
                    @DATALOADED = 1,
                    @TRACKNAME = isnull(TRACK.NAME,'')
                from dbo.ITINERARY
                left join dbo.TRACK on TRACK.ID = ITINERARY.TRACKID
                where ITINERARY.ID = @ID

                if @DATALOADED = 1
                begin
                    declare @ATTENDEECOUNT int
                    set @ATTENDEECOUNT = isnull(
                        (
                            select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE 
                            where ITINERARYATTENDEE.ITINERARYID = @ID
                        ),0
                    )

                    set @COMBINEDRESOURCES = 
                    (
                        select
                            ID,
                            NAME,
                            QUANTITYNEEDED,
                            STARTDATETIME,
                            ENDDATETIME,
                            ISSCHEDULED
                        from
                        (
                            -- All itinerary resources
                            select 
                                RESOURCE.ID as ID,
                                RESOURCE.NAME as NAME,
                                case RESOURCE.ISPERTICKETITEM
                                    when 0 then
                                        ITINERARYRESOURCE.QUANTITYNEEDED
                                    else
                                        dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(@ATTENDEECOUNT * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
                                end as QUANTITYNEEDED,
                                ITINERARY.STARTDATETIME as STARTDATETIME,
                                ITINERARY.ENDDATETIME as ENDDATETIME,
                                1 as ISSCHEDULED
                            from dbo.ITINERARYRESOURCE
                            inner join dbo.RESOURCE    on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
                            inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
                            where
                                ITINERARY.ID = @ID

                            union all

                            -- All itinerary item resources
                            select 
                                RESOURCE.ID as ID,
                                RESOURCE.NAME as NAME,
                                case RESOURCE.ISPERTICKETITEM
                                    when 0 then
                                        ITINERARYITEMRESOURCE.QUANTITYNEEDED
                                    else
                                        dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(@ATTENDEECOUNT * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
                                end as QUANTITYNEEDED,
                                ITINERARYITEM.STARTDATETIME as STARTDATETIME, 
                                ITINERARYITEM.ENDDATETIME as ENDDATETIME,
                                case ITINERARYITEM.ITEMTYPECODE when 3 then 0 else 1 end as ISSCHEDULED
                            from dbo.ITINERARYITEMRESOURCE
                            inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
                            inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                            where 
                                ITINERARYITEM.ITINERARYID = @ID

                            union all

                            select 
                                VOLUNTEERTYPE.ID as ID,
                                VOLUNTEERTYPE.NAME as NAME,
                                ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                                ITINERARY.STARTDATETIME as STARTDATETIME,
                                ITINERARY.ENDDATETIME as ENDDATETIME,
                                1 as ISSCHEDULED
                            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.VOLUNTEERTYPE on
                                ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                            where
                                ITINERARY.ID = @ID

                            union all

                            select 
                                VOLUNTEERTYPE.ID as ID,
                                VOLUNTEERTYPE.NAME as NAME,
                                ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
                                ITINERARYITEM.STARTDATETIME as STARTDATETIME, 
                                ITINERARYITEM.ENDDATETIME as ENDDATETIME,
                                case ITINERARYITEM.ITEMTYPECODE when 3 then 0 else 1 end as ISSCHEDULED
                            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.ID = @ID
                        ) [COMBINEDRESOURCES]
                        for xml raw('ITEM'),type,elements,root('COMBINEDRESOURCES'),binary base64                    
                    );

                    set @ATTENDEES = dbo.UFN_ITINERARY_GETATTENDEES_TOITEMLISTXML(@ID);

                    set @ITEMS = dbo.UFN_ITINERARY_GETITINERARYITEMSINFO_TOITEMLISTXML(@ID);
                end



                return 0;