UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILSBYID

create function dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILSBYID (
    @START datetime,
    @END datetime,
    @ID uniqueidentifier,
    @IGNORERECORDID uniqueidentifier = null,
    @IGNORESUBRECORDID uniqueidentifier = null,
    @IGNORESUPERRECORDID uniqueidentifier = null,
    @INCLUDEEVENTCONFLICTS bit = 1,
    @INCLUDEITINERARYITEMCONFLICTS bit = 1
)
returns table
as return (
    with LOCATION_CTE as (
        select EVENTLOCATIONID as LOCATIONID
        from dbo.PROGRAMEVENTLOCATION
        where EVENTCONFLICTID = @ID
    )
    select
        EVENT.ID as EVENTID, 
        null as ITINERARYITEMID,
        isnull(PROGRAMEVENTLOCATION.EVENTLOCATIONID, EVENT.EVENTLOCATIONID) as LOCATIONID
    from
        dbo.EVENT
    left outer join
        dbo.PROGRAMEVENTLOCATION on EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
    left outer join
        dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
    inner join
        LOCATION_CTE as CONFLICTPROGRAMEVENTLOCATION on CONFLICTPROGRAMEVENTLOCATION.LOCATIONID = PROGRAMEVENTLOCATION.EVENTLOCATIONID or EVENT.EVENTLOCATIONID = CONFLICTPROGRAMEVENTLOCATION.LOCATIONID
    where
        @INCLUDEEVENTCONFLICTS = 1
        and (
            PROGRAM.ISACTIVE = 1
            or EVENT.PROGRAMID is null
        )
        and (
            @IGNORERECORDID is null
            or EVENT.ID <> @IGNORERECORDID
        )
        and (
            (
                @START >= EVENT.STARTDATETIME and 
                @START < EVENT.ENDDATETIME
            )
            or (
                @END > EVENT.STARTDATETIME and 
                @END <= EVENT.ENDDATETIME
            )
            or (
                @START < EVENT.STARTDATETIME and 
                @END > EVENT.ENDDATETIME
            )
        )

    union all    

    select
        null as EVENTID,
        ITINERARYITEM.ID as ITINERARYITEMID,
        ITINERARYITEM.EVENTLOCATIONID as LOCATIONID
    from
        dbo.ITINERARYITEM
    inner join
        dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
    inner join
        dbo.SALESORDER on SALESORDER.ID = ITINERARY.RESERVATIONID
    inner join
        LOCATION_CTE as CONFLICTLOCATIONS on ITINERARYITEM.EVENTLOCATIONID = CONFLICTLOCATIONS.LOCATIONID
    where
        @INCLUDEITINERARYITEMCONFLICTS = 1
        and SALESORDER.STATUSCODE <> 5
        and ITINERARYITEM.INVALIDREASONCODE = 0  -- Make sure that the itinerary item does not have an invalid reason

        and ITINERARYITEM.ITEMTYPECODE <> 3  -- Make sure the itinerary item is showing as scheduled

        and (
            @IGNORESUPERRECORDID is null
            or ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID
        )
        and (
            ITINERARYITEM.ITEMTYPECODE <> 2
            or ITINERARYITEM.BLOCKEVENT = 1 --Make sure the item is either not a custom item, if it is a custom item only count it if the location is marked busy

        )
        and (
            @IGNORERECORDID is null
            or ITINERARYITEM.ITINERARYID <> @IGNORERECORDID
        )
        and (
            @IGNORESUBRECORDID is null
            or ITINERARYITEM.ID <> @IGNORESUBRECORDID
        )
        and (
            (
                @START >= ITINERARYITEM.STARTDATETIME and 
                @START < ITINERARYITEM.ENDDATETIME
            )
            or (
                @END > ITINERARYITEM.STARTDATETIME and 
                @END <= ITINERARYITEM.ENDDATETIME
            )
            or (
                @START < ITINERARYITEM.STARTDATETIME and 
                @END > ITINERARYITEM.ENDDATETIME
            )
        )
)