USP_DATAFORMTEMPLATE_EDITLOAD_GROUPSALESCHECKINVISITOR

The load procedure used by the edit dataform template "Group Sales Check In Visitor 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.
@ITINERARIES	xml	INOUT
@PRICETYPES	xml	INOUT
@RESOURCES	xml	INOUT	Resources
@STAFFRESOURCES	xml	INOUT	Staffing resources
@RESERVATIONNAME	nvarchar(100)	INOUT	Name
@ITINERARYCOUNT	tinyint	INOUT
@TOTALVISITORS	int	INOUT	Total visitors
@ISFLATRATE	bit	INOUT
@RATESCALEMIN	int	INOUT
@RATESCALEMAX	int	INOUT
@UPDATERATESCALE	bit	INOUT	Update group rate based on new number of visitors
@IGNORECONFLICTS	bit	INOUT	Ignore conflicts when checking in
@ADDITIONALPRICETYPES	xml	INOUT
@USEPERTICKETAFTERMAX	bit	INOUT
@UPDATEPERTICKETBALANCE	bit	INOUT	Update order balance based on new number of visitors
@CURRENTBALANCE	money	INOUT	Current balance
@HASEMPTYITINERARY	bit	INOUT
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_GROUPSALESCHECKINVISITOR
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @ITINERARIES xml = null output,
    @PRICETYPES xml = null output,
    @RESOURCES xml = null output,
    @STAFFRESOURCES xml = null output,
    @RESERVATIONNAME nvarchar(100) = null output,
    @ITINERARYCOUNT tinyint = null output,
    @TOTALVISITORS integer = null output,
    @ISFLATRATE bit = null output,
    @RATESCALEMIN integer = null output,
    @RATESCALEMAX integer = null output,
    @UPDATERATESCALE bit = null output,
    @IGNORECONFLICTS bit = null output,
    @ADDITIONALPRICETYPES xml = null output,
    @USEPERTICKETAFTERMAX bit = null output,
    @UPDATEPERTICKETBALANCE bit = null output,
    @CURRENTBALANCE money = null output,
    @HASEMPTYITINERARY bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0
    set @TSLONG = 0

    select
        @DATALOADED = 1,
        @TSLONG = RESERVATION.TSLONG,
        @RESERVATIONNAME = RESERVATION.NAME,
        @IGNORECONFLICTS = 0,
        @ISFLATRATE = RESERVATION.PRICINGCODE,
        @USEPERTICKETAFTERMAX = coalesce(RESERVATIONRATESCALE.USEPERTICKETAFTERMAX, 0),
        @HASEMPTYITINERARY = dbo.UFN_RESERVATION_HASEMPTYITINERARY([RESERVATION].[ID])
    from dbo.RESERVATION
        left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
    where RESERVATION.ID = @ID

    if @DATALOADED = 1 begin
        select @ITINERARYCOUNT = count(ID)
        from dbo.ITINERARY
        where RESERVATIONID = @ID

        declare @ATTENDEES table (
            ITINERARYATTENDEEID uniqueidentifier,
            ITINERARYID uniqueidentifier,
            ITINERARYNAME nvarchar(154),
            PRICETYPECODEID uniqueidentifier,
            PRICETYPECODEDESCRIPTION nvarchar(100),
            QUANTITY smallint
        );

        insert into @ATTENDEES (
            ITINERARYATTENDEEID,
            ITINERARYID,
            ITINERARYNAME,
            PRICETYPECODEID,
            PRICETYPECODEDESCRIPTION,
            QUANTITY
        )
        select
            ITINERARYATTENDEE.ID,
            ITINERARY.ID,
            ITINERARY.NAME,
            ITINERARYATTENDEE.PRICETYPECODEID,
            PRICETYPECODE.DESCRIPTION,
            ITINERARYATTENDEE.QUANTITY
        from dbo.ITINERARY
        inner join dbo.ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
        inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = ITINERARYATTENDEE.PRICETYPECODEID
        where ITINERARY.RESERVATIONID = @ID;

        set @ITINERARIES =
        (
            select
                ITINERARYATTENDEEID as ID,
                ITINERARYID,
                PRICETYPECODEDESCRIPTION as PRICETYPE,
                ITINERARYNAME as NAME,
                PRICETYPECODEID,
                QUANTITY
            from
                @ATTENDEES
            for xml raw('ITEM'),type,elements,root('ITINERARIES'),binary base64
        );

        set @PRICETYPES =
        (
            select
                PRICETYPECODEDESCRIPTION as PRICETYPE,
                sum(QUANTITY) as QUANTITY,
                PRICETYPECODEID
            from
                @ATTENDEES
            group by
                PRICETYPECODEID, PRICETYPECODEDESCRIPTION
            for xml raw('ITEM'),type,elements,root('PRICETYPES'),binary base64    
        );

        set @ADDITIONALPRICETYPES =
        (
            select
                ITINERARYID,
                PRICETYPECODEDESCRIPTION as PRICETYPE,
                ITINERARYNAME as NAME,
                PRICETYPECODEID,
                QUANTITY
            from
                @ATTENDEES
            for xml raw('ITEM'), type, elements, root('ADDITIONALPRICETYPES'), binary base64
        );

        set @RESOURCES = 
        (
            select
                ID,
                ITINERARYID,
                ITINERARYITEMID,
                QUANTITYNEEDED,
                RESOURCENAME,
                RESOURCEID,
                ITINERARYITEMNAME,
                ISPERTICKETITEM,
                PRICINGSTRUCTURECODE,
                ISREQUIREDRESOURCE,
                REQUIREDRESOURCEQUANTITY,
                PRICE,
                STARTTIME,
                ENDTIME,
                PERTICKETQUANTITY,
                PERTICKETDIVISOR
            from
            (
                -- All itinerary resources
                select 
                    ITINERARYRESOURCE.ID AS ID,
                    ITINERARY.ID as ITINERARYID,
                    null as ITINERARYITEMID,
                    ITINERARYRESOURCE.QUANTITYNEEDED,
                    RESOURCE.NAME as RESOURCENAME,
                    ITINERARYRESOURCE.RESOURCEID,
                    'Entire itinerary' as ITINERARYITEMNAME,
                    RESOURCE.ISPERTICKETITEM,
                    ITINERARYRESOURCE.PRICINGSTRUCTURECODE,
                    case when (GROUPTYPEREQUIREDRESOURCE.ID is null) then 0 else 1 end as ISREQUIREDRESOURCE,
                    coalesce(GROUPTYPEREQUIREDRESOURCE.QUANTITYNEEDED, 0) as REQUIREDRESOURCEQUANTITY,
                    ITINERARYRESOURCE.PRICE,
                    [dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.STARTDATETIME) as STARTTIME,
                    [dbo].[UFN_HOURMINUTE_GETFROMDATE](ITINERARY.ENDDATETIME) as ENDTIME,
                    ITINERARYRESOURCE.PERTICKETQUANTITY,
                    ITINERARYRESOURCE.PERTICKETDIVISOR
                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,
                    RESOURCE.NAME as RESOURCENAME,
                    ITINERARYITEMRESOURCE.RESOURCEID,
                    ITINERARYITEM.NAME as ITINERARYITEMNAME,
                    RESOURCE.ISPERTICKETITEM,
                    ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE,
                    0 as ISREQUIREDRESOURCE,
                    0 as REQUIREDRESOURCEQUANTITY,
                    ITINERARYITEMRESOURCE.PRICE,
                    ITINERARYITEM.STARTTIME,
                    ITINERARYITEM.ENDTIME,
                    ITINERARYITEMRESOURCE.PERTICKETQUANTITY,
                    ITINERARYITEMRESOURCE.PERTICKETDIVISOR
                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 and
                    ITINERARYITEM.ITEMTYPECODE <> 3
            ) [RESOURCES]
            for xml raw('ITEM'),type,elements,root('RESOURCES'),binary base64
        );

        set @STAFFRESOURCES = 
        (
            select
                ID,
                ITINERARYID,
                ITINERARYITEMID,
                QUANTITYNEEDED,
                JOBNAME,
                ITINERARYITEMNAME,
                VOLUNTEERTYPEID,
                JOBID,
                JOBOCCURRENCEID,
                FILLEDBYCODE,
                PRICINGSTRUCTURECODE,
                PRICE,
                ISREQUIREDRESOURCE,
                REQUIREDRESOURCEQUANTITY,
                STARTTIME,
                ENDTIME
            from
            (
                select 
                    ITINERARYSTAFFRESOURCE.ID AS ID,
                    ITINERARY.ID as ITINERARYID,
                    null as ITINERARYITEMID,
                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                    coalesce(JOB.NAME, VOLUNTEERTYPE.NAME,'') as JOBNAME,
                    'Entire itinerary' as ITINERARYITEMNAME,
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYSTAFFRESOURCE.JOBID,
                    ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
                    ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
                    ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE,
                    ITINERARYSTAFFRESOURCE.PRICE,
                    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,
                    coalesce(JOB.NAME, VOLUNTEERTYPE.NAME,'') as JOBNAME,
                    ITINERARYITEM.NAME as ITINERARYITEMNAME,
                    ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYITEMSTAFFRESOURCE.JOBID,
                    ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID,
                    ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
                    ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE,
                    ITINERARYITEMSTAFFRESOURCE.PRICE,
                    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 and 
                    ITINERARYITEM.ITEMTYPECODE <> 3
            ) [STAFFRESOURCES]
            for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),binary base64                    
        );

        set @TOTALVISITORS = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@ID);

        if @ISFLATRATE = 1 begin
            select @RATESCALEMIN = RSP.GROUPMINIMUM,
                @RATESCALEMAX = RSP.GROUPMAXIMUM
            from dbo.RESERVATIONRATESCALEPRICE RSP
            inner join dbo.RESERVATIONRATESCALE RRS ON RRS.ID = RSP.RESERVATIONRATESCALEID
            where RRS.ID = @ID 
                and    RSP.INUSE = 1
        end

        set @UPDATEPERTICKETBALANCE = 1;
        select @CURRENTBALANCE = BALANCE from dbo.UFN_SALESORDER_TOTALS(@ID)
    end

    return 0;