USP_DATAFORMTEMPLATE_VIEW_GROUPSALESCHECKINDETAIL

The load procedure used by the view dataform template "Group Sales Check In 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.
@NAME	nvarchar(100)	INOUT	NAME
@PATRONNAME	nvarchar(100)	INOUT	Patron
@TOTALVISITORS	int	INOUT	Number of visitors
@ARRIVALAREA	nvarchar(100)	INOUT	Arrival area
@NUMBEROFBUSES	smallint	INOUT	Number of buses
@STATUS	nvarchar(50)	INOUT	Status
@BALANCE	money	INOUT	Balance
@RESERVATIONCONTACT	nvarchar(100)	INOUT	Reservation contact
@TRANSPORTATIONCONTACT	nvarchar(200)	INOUT	Transportation contact
@STARTDATETIME	datetime	INOUT	Start date
@ENDDATETIME	datetime	INOUT	End date
@STARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@RESERVATIONNOTES	nvarchar(500)	INOUT	Order comments
@ARRIVALNOTES	nvarchar(500)	INOUT	Arrival notes
@ITINERARYCOUNT	tinyint	INOUT	ITINERARYCOUNT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GROUPSALESCHECKINDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @PATRONNAME nvarchar(100) = null output,
    @TOTALVISITORS integer = null output,
    @ARRIVALAREA nvarchar(100) = null output,
    @NUMBEROFBUSES smallint = null output,
    @STATUS nvarchar(50) = null output,
    @BALANCE money = null output,
    @RESERVATIONCONTACT nvarchar(100) = null output,
    @TRANSPORTATIONCONTACT nvarchar(200) = null output,
    @STARTDATETIME datetime = null output,
    @ENDDATETIME datetime = null output,
    @STARTTIME udt_hourminute = null output,
    @ENDTIME udt_hourminute = null output,
    @RESERVATIONNOTES nvarchar(500) = null output,
    @ARRIVALNOTES nvarchar(500) = null output,
    @ITINERARYCOUNT tinyint = null output
)
as
    set nocount on;
    set @DATALOADED = 0;

    declare @DRIVERNAME nvarchar(100);
    declare @DRIVERPHONENUMBER nvarchar(100);
    declare @CONTACTID uniqueidentifier;
    declare @PHONEID uniqueidentifier;
    declare @CONTACTPHONENUMBER nvarchar(100);
    declare @ARRIVALTIME udt_hourminute;

    select 
        @DATALOADED = 1,
        @NAME = RESERVATION.NAME,
        @TOTALVISITORS = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID),
        @ARRIVALAREA = dbo.UFN_ARRIVALAREACODE_GETDESCRIPTION(RESERVATION.ARRIVALAREACODEID),
        @PATRONNAME = CONSTITUENT.NAME,
        @STATUS = SALESORDER.STATUS,
        @NUMBEROFBUSES = RESERVATION.NUMBEROFBUSES,
        @BALANCE = TOTALS.BALANCE ,
        @DRIVERNAME = DRIVERNAME,
        @DRIVERPHONENUMBER = DRIVERPHONENUMBER,
        @CONTACTID = SALESORDER.RECIPIENTID,
        @PHONEID = CONTACTRECORDS.PHONEID,
        @STARTDATETIME = STARTDATETIME,
        @ENDDATETIME = ENDDATETIME,
        @ARRIVALTIME = ARRIVALTIME,
        @RESERVATIONNOTES = SALESORDER.COMMENTS,
        @ARRIVALNOTES = ARRIVALNOTES
    from 
        dbo.RESERVATION
    inner join 
        dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
    inner join 
        dbo.CONSTITUENT on CONSTITUENT.ID = SALESORDER.CONSTITUENTID
    outer apply 
        dbo.UFN_SALESORDER_TOTALS(@ID) TOTALS
    outer apply
        dbo.UFN_SALESORDER_CONTACTRECORDS(RESERVATION.ID) as CONTACTRECORDS
    where 
        RESERVATION.ID = @ID 

    if @DATALOADED = 1
    begin
        if ((select count(ITINERARYITEM.ID) 
            from dbo.ITINERARY 
            inner join dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
            where ITINERARY.RESERVATIONID = @ID) = 0)
        begin
            set @ENDTIME = '';

            if @ARRIVALTIME = '' or @ARRIVALTIME is null
                set @STARTTIME = '';
            else
                set @STARTTIME = @ARRIVALTIME;
        end
        else
        begin
            if @ARRIVALTIME = ''
            begin
                set @STARTTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(@STARTDATETIME);
                set @ENDTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(@ENDDATETIME);
            end
            else
            begin
                set @STARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATETIME), @ARRIVALTIME);
                set @STARTTIME = @ARRIVALTIME;
                set @ENDTIME = dbo.UFN_HOURMINUTE_GETFROMDATE(@ENDDATETIME);
            end
        end

        if (@DRIVERNAME = '') and (@DRIVERPHONENUMBER <> '')
            set @TRANSPORTATIONCONTACT = @DRIVERPHONENUMBER
        else if (@DRIVERNAME <> '') and (@DRIVERPHONENUMBER = '')
            set @TRANSPORTATIONCONTACT = @DRIVERNAME
        else if (@DRIVERNAME <> '') and (@DRIVERPHONENUMBER <> '')
            set @TRANSPORTATIONCONTACT = @DRIVERNAME + ' - ' + @DRIVERPHONENUMBER

        select 
            @CONTACTPHONENUMBER = NUMBER
        from dbo.PHONE
        where ID = @PHONEID

        select
            @RESERVATIONCONTACT = NAME
        from 
            dbo.CONSTITUENT
        where ID = @CONTACTID

        if @CONTACTPHONENUMBER is null and @RESERVATIONCONTACT is not null
            set @RESERVATIONCONTACT = @RESERVATIONCONTACT
        else if @CONTACTPHONENUMBER is not null and @RESERVATIONCONTACT is not null
            set @RESERVATIONCONTACT = @RESERVATIONCONTACT +' - ' + @CONTACTPHONENUMBER
    end
return 0;