UFN_REGISTRANT_HASCONFLICT

/* This function assumes that the CONFLICTINGEVENTPAIRS table consists of records returned by
   calling UFN_EVENT_RELATEDEVENTPAIRSWITHSCHEDULECONFLICT with the EVENTID from the same REGISTRANT
   row whose ID is passed in to this function.  It is done this way in order to avoid duplicate
   processing to determine conflicting event pairs; in other words, instead of being done dozens or
   hundreds of times inside this function, it is done once by the caller and passed in.  If this 
   assumption is violated, this function will have undefined behavior. */
CREATE function dbo.UFN_REGISTRANT_HASCONFLICT
(
    @REGISTRANTID uniqueidentifier,
    @CONFLICTINGEVENTPAIRS UDT_EVENTPAIR READONLY
)
returns bit
with execute as caller
as
begin
    declare @numConflicts int
    select @numConflicts = count(*) from @CONFLICTINGEVENTPAIRS
    if @numConflicts = 0
        return 0

    declare @eventId uniqueidentifier, @constituentId uniqueidentifier, @hostId uniqueidentifier
    select 
        @eventId = R.EVENTID, 
        @constituentID = R.CONSTITUENTID, 
        @hostId = HOST.CONSTITUENTID
        from dbo.REGISTRANT R
        left join dbo.REGISTRANT HOST on R.GUESTOFREGISTRANTID = HOST.ID
        where R.ID = @REGISTRANTID

    declare @mainEventId uniqueidentifier
    select @mainEventId = coalesce(MAINEVENTID, ID) from dbo.EVENT where ID = @eventId

    declare @singleEventsForRegistrant table(EVENTID uniqueidentifier)
    insert into @singleEventsForRegistrant  
    select REGISTRANTFORCONSTITUENT.EVENTID  
    from 
    (
        select
            ID,
            EVENTID
        from dbo.REGISTRANT
        where 
            @constituentId is not null and
            CONSTITUENTID = @constituentId

        union

        select 
            R.ID,
            R.EVENTID  
        from dbo.REGISTRANT R  
        inner join dbo.REGISTRANT HOST on R.CONSTITUENTID is null and R.GUESTOFREGISTRANTID = HOST.ID and HOST.CONSTITUENTID = @hostID  
        where   
            @constituentId is null and 
            HOST.CONSTITUENTID is not null
    ) REGISTRANTFORCONSTITUENT
    inner join dbo.[EVENT] on REGISTRANTFORCONSTITUENT.EVENTID = [EVENT].ID and ([EVENT].MAINEVENTID = @mainEventId or [EVENT].ID = @eventId)  
    inner join dbo.REGISTRANTREGISTRATIONMAP MAP on MAP.REGISTRANTID = REGISTRANTFORCONSTITUENT.ID and MAP.REGISTRANTPACKAGEID is null


    select @numConflicts = count(*)
        from @CONFLICTINGEVENTPAIRS C
        where 
            C.EVENT1 in (select EVENTID from @singleEventsForRegistrant) 
            and 
            C.EVENT2 in (select EVENTID from @singleEventsForRegistrant)
    return 
        case @numConflicts 
            when 0 then 0 
            else 1 
        end
end