USP_DATAFORMTEMPLATE_VIEW_RESERVATIONSUMMARY

The load procedure used by the view dataform template "Reservation Summary 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	Reservation name
@ARRIVALDATE	datetime	INOUT	Visit date
@CONTACTCONSTITUENTNAME	nvarchar(80)	INOUT	Contact
@CONTACTADDRESS	nvarchar(300)	INOUT	CONTACTADDRESS
@CONTACTPHONENUMBER	nvarchar(100)	INOUT	CONTACTPHONENUMBER
@CONTACTEMAILADDRESS	UDT_EMAILADDRESS	INOUT	CONTACTEMAILADDRESS
@COMMENTS	nvarchar(1000)	INOUT	Comments
@AMOUNTDUE	money	INOUT	Balance
@TOTALVISITORS	int	INOUT	Total visitors
@STATUS	nvarchar(20)	INOUT	Status
@DEPOSITREQUIRED	bit	INOUT	DEPOSITREQUIRED
@DEPOSITDUEDATE	date	INOUT	Reservation deposit due
@FINALDUEDATE	date	INOUT	Order balance due
@DEPOSITPASTDUE	bit	INOUT	DEPOSITPASTDUE
@FINALPAYMENTPASTDUE	bit	INOUT	FINALPAYMENTPASTDUE
@FINALCOUNTREQUIRED	bit	INOUT	FINALCOUNTREQUIRED
@FINALCOUNTRECEIVED	bit	INOUT	FINALCOUNTRECEIVED
@FINALCOUNTPASTDUE	bit	INOUT	FINALCOUNTPASTDUE
@FINALCOUNTRECEIVEDDATE	date	INOUT	Final count received
@FINALCOUNTDUEDATE	date	INOUT	Final count due
@CONTRACTREQUIRED	bit	INOUT	CONTRACTREQUIRED
@CONTRACTRECEIVED	bit	INOUT	CONTRACTRECEIVED
@CONTRACTDUEDATE	date	INOUT	Contract due
@CONTRACTPASTDUE	bit	INOUT	CONTRACTPASTDUE
@CONTRACTRECEIVEDDATE	date	INOUT	Contract received
@PRICING	nvarchar(100)	INOUT	Pricing structure
@RATESCALE	nvarchar(100)	INOUT	Rate scale
@CONTACTDONOTMAIL	bit	INOUT	CONTACTDONOTMAIL
@CONTACTDONOTCALL	bit	INOUT	CONTACTDONOTCALL
@CONTACTDONOTEMAIL	bit	INOUT	CONTACTDONOTEMAIL
@DEPOSITPAID	bit	INOUT	Deposit has been paid
@DEPOSITPAIDDATE	date	INOUT	Reservation deposit paid
@RESERVATIONTOTAL	money	INOUT	Total
@AMOUNTPAID	money	INOUT	Amount paid
@FINALPAYMENTRECEIVED	bit	INOUT	Final payment received
@FINALPAYMENTRECEIVEDDATE	date	INOUT	Order balance paid
@HASINVALIDDEPOSITAMOUNT	bit	INOUT	HASINVALIDDEPOSITAMOUNT
@STATUSCODE	tinyint	INOUT	STATUSCODE
@SECURITYDEPOSITREQUIRED	bit	INOUT	SECURITYDEPOSITREQUIRED
@SECURITYDEPOSITDUEDATE	date	INOUT	Security deposit due
@SECURITYDEPOSITPASTDUE	bit	INOUT	SECURITYDEPOSITPASTDUE
@SECURITYDEPOSITPAID	bit	INOUT	Security deposit has been paid
@SECURITYDEPOSITPAIDDATE	date	INOUT	Security deposit paid
@HASINVALIDSECURITYDEPOSITAMOUNT	bit	INOUT	HASINVALIDSECURITYDEPOSITAMOUNT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESERVATIONSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @ARRIVALDATE datetime = null output,
    @CONTACTCONSTITUENTNAME nvarchar(80) = null output,
    @CONTACTADDRESS nvarchar(300) = null output,
    @CONTACTPHONENUMBER nvarchar(100) = null output,
    @CONTACTEMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @COMMENTS nvarchar(1000) = null output,
    @AMOUNTDUE money = null output,
    @TOTALVISITORS int = null output,
    @STATUS nvarchar(20) = null output,
    @DEPOSITREQUIRED bit = null output,
    @DEPOSITDUEDATE date = null output,
    @FINALDUEDATE date = null output,
    @DEPOSITPASTDUE bit = null output,
    @FINALPAYMENTPASTDUE bit = null output,
    @FINALCOUNTREQUIRED bit = null output,
    @FINALCOUNTRECEIVED bit = null output,
    @FINALCOUNTPASTDUE bit = null output,
    @FINALCOUNTRECEIVEDDATE date = null output,
    @FINALCOUNTDUEDATE date = null output,
    @CONTRACTREQUIRED bit = null output,
    @CONTRACTRECEIVED bit = null output,
    @CONTRACTDUEDATE date = null output,
    @CONTRACTPASTDUE bit = null output,
    @CONTRACTRECEIVEDDATE date = null output,
    @PRICING nvarchar(100) = null output,
    @RATESCALE nvarchar(100) = null output,
    @CONTACTDONOTMAIL bit = null output,
    @CONTACTDONOTCALL bit = null output,
    @CONTACTDONOTEMAIL bit = null output,
    @DEPOSITPAID bit = null output,
    @DEPOSITPAIDDATE date = null output,
    @RESERVATIONTOTAL money = null output,
    @AMOUNTPAID money = null output,
    @FINALPAYMENTRECEIVED bit = null output,
    @FINALPAYMENTRECEIVEDDATE date = null output,
    @HASINVALIDDEPOSITAMOUNT bit = null output,
    @STATUSCODE tinyint = null output,
    @SECURITYDEPOSITREQUIRED bit = null output,
    @SECURITYDEPOSITDUEDATE date = null output,
    @SECURITYDEPOSITPASTDUE bit = null output,
    @SECURITYDEPOSITPAID bit = null output,
    @SECURITYDEPOSITPAIDDATE date = null output,
    @HASINVALIDSECURITYDEPOSITAMOUNT bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @CONTACTID uniqueidentifier;
    declare @ADDRESSID uniqueidentifier;
    declare @PHONEID uniqueidentifier;
    declare @EMAILADDRESSID uniqueidentifier;
    declare @DEPOSITAMOUNT money = 0;
    declare @PRICINGCODE bit;
    declare @SECURITYDEPOSITAMOUNT money = 0;

    select
        @DATALOADED = 1,
        @NAME = RESERVATION.NAME,
        @ARRIVALDATE = RESERVATION.ARRIVALDATE,
        @COMMENTS = SALESORDER.COMMENTS,
        @STATUS = SALESORDER.STATUS,
        @CONTACTID = SALESORDER.RECIPIENTID,
        @ADDRESSID = CONTACTRECORDS.ADDRESSID,
        @PHONEID = CONTACTRECORDS.PHONEID,
        @EMAILADDRESSID = CONTACTRECORDS.EMAILADDRESSID,
        @DEPOSITREQUIRED = DEPOSITREQUIRED,
        @DEPOSITAMOUNT = DEPOSITAMOUNT,
        @DEPOSITDUEDATE = DEPOSITDUEDATE,
        @FINALDUEDATE = FINALDUEDATE,
        @DEPOSITPASTDUE = dbo.UFN_RESERVATION_DEPOSITPASTDUE(@ID,getdate()),
        @FINALPAYMENTPASTDUE = dbo.UFN_RESERVATION_FINALPAYMENTPASTDUE(@ID,getdate()),
        @FINALCOUNTREQUIRED = FINALCOUNTREQUIRED,
        @FINALCOUNTDUEDATE = FINALCOUNTDUEDATE,
        @FINALCOUNTRECEIVED = FINALCOUNTRECEIVED,
        @FINALCOUNTRECEIVEDDATE = FINALCOUNTRECEIVEDDATE,
        @CONTRACTREQUIRED = CONTRACTREQUIRED,
        @CONTRACTRECEIVED = CONTRACTRECEIVED,
        @CONTRACTDUEDATE = CONTRACTDUEDATE,
        @PRICINGCODE = PRICINGCODE,
        @PRICING = PRICING,
        @DEPOSITPAID = dbo.UFN_RESERVATION_ISDEPOSITPAID(@ID),
        @DEPOSITPAIDDATE = dbo.UFN_RESERVATION_GETDEPOSITPAIDDATE(@ID),
        @STATUSCODE = SALESORDER.STATUSCODE,
        @SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITAMOUNT = SECURITYDEPOSITAMOUNT,
        @SECURITYDEPOSITDUEDATE = SECURITYDEPOSITDUEDATE,
        @SECURITYDEPOSITPASTDUE = case when datediff(d, @SECURITYDEPOSITDUEDATE, getdate()) > 0 and dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@ID) > 0 then 1
                                        else 0 end,
        @SECURITYDEPOSITPAID = case when SECURITYDEPOSITREQUIRED = 1 and dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@ID) <= 0 and SECURITYDEPOSITAMOUNT > 0 then 1
                                        else 0 end,
        @SECURITYDEPOSITPAIDDATE = (select max(PAYMENT.DATEADDED) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT PAYMENT where PAYMENT.RESERVATIONID = @ID),
        @RESERVATIONTOTAL = TOTALS.TOTAL,
        @AMOUNTDUE = TOTALS.BALANCE,
        @AMOUNTPAID = TOTALS.AMOUNTPAID
    from
        dbo.RESERVATION
    inner join
        dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
    inner join
        dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    outer apply
        dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
    outer apply
        dbo.UFN_SALESORDER_CONTACTRECORDS(RESERVATION.ID) as CONTACTRECORDS
    where
        RESERVATION.ID = @ID;

    if @DATALOADED = 1 begin
        declare @CURRENTDATE date;
        set @CURRENTDATE = getdate()

        set @CONTRACTPASTDUE = 0;

        if @CONTRACTREQUIRED = 1 and @CONTRACTRECEIVED = 0 and @CONTRACTDUEDATE < @CURRENTDATE
            set @CONTRACTPASTDUE = 1;

        set @HASINVALIDDEPOSITAMOUNT = 0
        if @DEPOSITREQUIRED = 1 and @DEPOSITAMOUNT = 0
            set @HASINVALIDDEPOSITAMOUNT = 1

        declare @DEPOSITTYPE int
        declare @DEPOSITPERCENT decimal(5,2)
        declare @SECURITYDEPOSITTYPE int
        declare @SECURITYDEPOSITPERCENT decimal(5,2)
        declare @DEPOSITREQUIREDDEFAULT bit
        declare @SECURITYDEPOSITREQUIREDDEFAULT bit

        select top 1
            @DEPOSITPERCENT = DEPOSITPERCENT,
            @DEPOSITTYPE = DEPOSITTYPECODE,
            @SECURITYDEPOSITTYPE = SECURITYDEPOSITTYPECODE,
            @SECURITYDEPOSITPERCENT = SECURITYDEPOSITPERCENT,
            @DEPOSITREQUIREDDEFAULT = DEPOSITREQUIRED,
            @SECURITYDEPOSITREQUIREDDEFAULT = SECURITYDEPOSITREQUIRED
        from
            dbo.GROUPSALESDEFAULT

        if @DEPOSITTYPE = 1 and @DEPOSITREQUIREDDEFAULT = 1 begin
            if @DEPOSITAMOUNT <> round(@RESERVATIONTOTAL * @DEPOSITPERCENT * .01, 2) and @STATUSCODE not in (1, 5)
                set @HASINVALIDDEPOSITAMOUNT = 1;
        end

        set @HASINVALIDSECURITYDEPOSITAMOUNT = 0
        if @SECURITYDEPOSITREQUIRED = 1 and @SECURITYDEPOSITAMOUNT = 0
            set @HASINVALIDSECURITYDEPOSITAMOUNT = 1

        if @SECURITYDEPOSITTYPE = 1 and @SECURITYDEPOSITREQUIREDDEFAULT = 1 begin
            if @SECURITYDEPOSITAMOUNT <> round(@RESERVATIONTOTAL * @SECURITYDEPOSITPERCENT * .01, 2) and @STATUSCODE not in (1, 5)
                set @HASINVALIDSECURITYDEPOSITAMOUNT = 1;
        end

        if @CONTRACTRECEIVED = 1 begin
            select top 1 @CONTRACTRECEIVEDDATE = STATUSDATE
            from dbo.RESERVATIONSTATUSHISTORY 
            where RESERVATIONID = @ID and
                STATUSCODE = 7
            order by STATUSDATE desc
        end

        set @FINALCOUNTPASTDUE = 0;
        if @FINALCOUNTREQUIRED = 1 and @FINALCOUNTRECEIVED = 0 begin
            if @FINALCOUNTDUEDATE < @CURRENTDATE
                set @FINALCOUNTPASTDUE = 1;
        end

        set @TOTALVISITORS = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(@ID)

        set @CONTACTCONSTITUENTNAME = dbo.UFN_CONSTITUENTSEARCH_DISPLAYNAME(@CONTACTID);

        select
            @CONTACTADDRESS = dbo.UFN_BUILDFULLADDRESS(ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID),
            @CONTACTDONOTMAIL = DONOTMAIL
        from dbo.ADDRESS where ID = @ADDRESSID    

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

        select
            @CONTACTEMAILADDRESS = EMAILADDRESS,
            @CONTACTDONOTEMAIL = DONOTEMAIL
        from dbo.EMAILADDRESS where ID = @EMAILADDRESSID

        if @PRICINGCODE = 1 begin
            declare @RATESCALEID uniqueidentifier;

            select
                @RATESCALEID = RATESCALEID
            from dbo.RESERVATIONRATESCALE
            where ID = @ID

            select @RATESCALE = NAME
            from dbo.RATESCALE
            where ID = @RATESCALEID
        end

        select top 1 @FINALPAYMENTRECEIVEDDATE = [REVENUE].[DATE]
        from dbo.[SALESORDERPAYMENT]
        inner join dbo.[REVENUE] on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
        where [SALESORDERPAYMENT].[SALESORDERID] = @ID
        order by REVENUE.DATE desc;

        set @FINALPAYMENTRECEIVED = 0
        if @AMOUNTPAID >= @RESERVATIONTOTAL and @FINALPAYMENTRECEIVEDDATE is not null
            set @FINALPAYMENTRECEIVED = 1
    end

    return 0;