UFN_ITINERARYITEM_CANBLOCK

On a custom item, determine whether it can block a location.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@EVENTLOCATIONID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@STARTTIME	UDT_HOURMINUTE	IN
@ENDTIME	UDT_HOURMINUTE	IN
Definition

Copy


CREATE function dbo.UFN_ITINERARYITEM_CANBLOCK
(
    @EVENTLOCATIONID uniqueidentifier = null,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @STARTTIME dbo.UDT_HOURMINUTE,
    @ENDTIME dbo.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 
        inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
        inner join dbo.SALESORDER on ITINERARY.RESERVATIONID = SALESORDER.ID
        where
            (SALESORDER.STATUSCODE <> 5) and -- MDC - Ignore cancelled

            (
                (
                    ITINERARYITEM.ITEMTYPECODE = 0 and
                    exists
                    (
                        select 1 from dbo.PROGRAMEVENTLOCATION PEL
                        where
                            ITINERARYITEM.EVENTID = PEL.EVENTID and
                            PEL.EVENTLOCATIONID = @EVENTLOCATIONID
                    )
                )
                or
                (
                    ITINERARYITEM.ITEMTYPECODE = 1 and
                    exists
                    (
                        select 1 from dbo.PROGRAMLOCATION 
                        where
                            ITINERARYITEM.PROGRAMID = PROGRAMLOCATION.PROGRAMID and
                            PROGRAMLOCATION.EVENTLOCATIONID = @EVENTLOCATIONID
                    )
                )
                or
                (
                    ITINERARYITEM.ITEMTYPECODE = 2 and
                    ITINERARYITEM.EVENTLOCATIONID = @EVENTLOCATIONID
                )
            )
            and
            (
                (
                    @SDATE >= ITINERARYITEM.STARTDATETIME and 
                    @SDATE < ITINERARYITEM.ENDDATETIME
                ) or
                (
                    @EDATE > ITINERARYITEM.STARTDATETIME and 
                    @EDATE <= ITINERARYITEM.ENDDATETIME
                ) or
                (
                    @SDATE <= ITINERARYITEM.STARTDATETIME and 
                    @EDATE >= ITINERARYITEM.ENDDATETIME
                ) or
                (
                    @EDATE > ITINERARYITEM.STARTDATETIME and 
                    @EDATE <= ITINERARYITEM.ENDDATETIME
                )
            )
    )
    begin
        set @VALID = 0
    end

    return @VALID
end