UFN_ITINERARYITEM_VALIDLOCATION

Validates that an itinerary item's location(s) are not currently being blocked.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@PROGRAMID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@STARTTIME	UDT_HOURMINUTE	IN
@ENDTIME	UDT_HOURMINUTE	IN

Definition

Copy



CREATE function dbo.UFN_ITINERARYITEM_VALIDLOCATION
(
    @PROGRAMID uniqueidentifier = null,
    @EVENTID uniqueidentifier = null,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @STARTTIME udt_HOURMINUTE,
    @ENDTIME udt_HOURMINUTE
)
returns bit
with execute as caller
as begin
    declare @VALID bit;
    set @VALID = 1;

    declare @SDATE datetime;
    set @SDATE = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME)

    declare @EDATE datetime;
    set @EDATE = dbo.UFN_DATE_ADDHOURMINUTE(@ENDDATE, @ENDTIME)


    if exists(select 1 
            from dbo.ITINERARYITEM IT1
            inner join dbo.ITINERARY on ITINERARY.ID = IT1.ITINERARYID
            inner join dbo.SALESORDER on SALESORDER.ID = ITINERARY.RESERVATIONID
            where SALESORDER.STATUSCODE <> 5 and 
                ITEMTYPECODE = 2 and
                BLOCKEVENT = 1 and
                (
                    (
                    exists( select 1 
                            from dbo.EVENT
                            inner join dbo.PROGRAMEVENTLOCATION on PROGRAMEVENTLOCATION.EVENTID = EVENT.ID
                            where PROGRAMEVENTLOCATION.EVENTLOCATIONID = IT1.EVENTLOCATIONID and
                                EVENT.ID = @EVENTID
                            )
                    or
                    exists( select 1 
                            from dbo.PROGRAM
                            inner join dbo.PROGRAMLOCATION on PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
                            where PROGRAMLOCATION.EVENTLOCATIONID = IT1.EVENTLOCATIONID and
                                PROGRAM.ID = @PROGRAMID 
                            )
                    )
                    and                             
                        ((@SDATE >= IT1.STARTDATETIME and @SDATE < IT1.ENDDATETIME) or
                        (@EDATE > IT1.STARTDATETIME and @EDATE <= IT1.ENDDATETIME) or
                        (@SDATE <= IT1.STARTDATETIME and @EDATE >= IT1.ENDDATETIME) or
                        (@EDATE > IT1.STARTDATETIME and @EDATE <= IT1.ENDDATETIME))
                    )
                )
        begin
            set @VALID = 0
        end
    return @VALID
end