UFN_GETUNPAIDPATRONEVENTREGISTRATIONS

CREATE function dbo.UFN_GETUNPAIDPATRONEVENTREGISTRATIONS
(
    @CONSTITUENTID uniqueidentifier = null,
    @SALESORDERID uniqueidentifier = null,
    @EVENTID uniqueidentifier = null,
    @FILTERZEROBALANCE bit = 1
)
returns @REGISTRATIONS table (
    [REGISTRANTID] uniqueidentifier,
    [REGISTRANTNAME] nvarchar(100),
    [BALANCE] money,
    [EVENTNAME] nvarchar(100),
    [EVENTDATE] datetime,
    [EVENTALLOWSDESIGNATIONSONFEES] bit
)
begin
    --The user could have selected multiple events in which to register 

    --(if event selected was within a main event)

    --Determine those other events

    declare @REGISTRANTEVENTS table ([EVENTID] uniqueidentifier)

    if @EVENTID is not null begin
        insert @REGISTRANTEVENTS ([EVENTID])
        values(@EVENTID)

        insert @REGISTRANTEVENTS
        select [EVENT].[ID]
        from (select [ID], [MAINEVENTID] from dbo.[EVENT] where [ID] = @EVENTID) as [REGISTRANTEVENT]
        inner join dbo.[EVENT]
            on [REGISTRANTEVENT].[MAINEVENTID] = [EVENT].[MAINEVENTID] or
                [REGISTRANTEVENT].[MAINEVENTID] = [EVENT].[ID] or
                [REGISTRANTEVENT].[ID] = [EVENT].[MAINEVENTID];
    end

    --CONSTITUENTID might be a group, and we'll need to return its members' registrations

    declare @CONSTITUENTS table (
        ID uniqueidentifier
    )

    if @CONSTITUENTID is not null begin
        insert into @CONSTITUENTS (ID)
        values (@CONSTITUENTID)

        if (select [ISGROUP] from dbo.[CONSTITUENT] where [ID] = @CONSTITUENTID) = 1 begin
            insert into @CONSTITUENTS
            select [MEMBERID]
            from dbo.[GROUPMEMBER]
            where [GROUPID] = @CONSTITUENTID;
        end
    end

    insert into @REGISTRATIONS
    select distinct
        [REGISTRATIONS].[REGISTRANTID] as [REGISTRANTID],
        [REGISTRATIONS].[REGISTRANTNAME] as [REGISTRANTNAME],
        [REGISTRATIONS].[BALANCE] as [BALANCE],
        [REGISTRATIONS].[EVENTNAME] as [EVENTNAME],
        [REGISTRATIONS].[EVENTDATE] as [EVENTDATE],
        [REGISTRATIONS].[EVENTALLOWSDESIGNATIONSONFEES] as [EVENTALLOWSDESIGNATIONSONFEES]
    from (
        select
            [REGISTRANT].[ID] as [REGISTRANTID],
            NF.NAME as [REGISTRANTNAME],
            [EVENT].[NAME] as [EVENTNAME],
            [EVENT].[STARTDATE] as [EVENTDATE],
            dbo.UFN_EVENTREGISTRANT_GETBALANCE([REGISTRANT].[ID]) as [BALANCE],
            case 
                when [EVENT].[DESIGNATIONSONFEES] = 1 
                    and exists (select * from dbo.REGISTRANTDESIGNATION where REGISTRANTID = [REGISTRANT].ID) 
                    then 1
                else 0
            end as EVENTALLOWSDESIGNATIONSONFEES
        from dbo.[REGISTRANT]
        inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
        left outer join dbo.[SALESORDERITEMEVENTREGISTRATION] SOIER on SOIER.REGISTRANTID = REGISTRANT.ID
        left outer join dbo.[SALESORDERITEM] SOI on SOI.ID = SOIER.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as NF
        where
            EVENT.PROGRAMID is null and
            [REGISTRANT].[GUESTOFREGISTRANTID] is null and
            [REGISTRANT].[ISCANCELLED] = 0 and
            (
                @CONSTITUENTID is null
                or REGISTRANT.CONSTITUENTID in (select ID from @CONSTITUENTS)
                or REGISTRANT.ID in ( --CONSTITUENTID is the guest in the registration

                    select [REG].[GUESTOFREGISTRANTID]
                    from dbo.[REGISTRANT] as [REG]
                    inner join @CONSTITUENTS [CONSTITUENTS]
                        on [REG].[CONSTITUENTID] = [CONSTITUENTS].[ID]
                )
            ) and 
            ( --Registration is for the EVENT

                @EVENTID is null or
                [EVENT].[ID] in (select [EVENTID] from @REGISTRANTEVENTS)
            )
            and (@SALESORDERID is null or SOI.SALESORDERID = @SALESORDERID)
        ) as [REGISTRATIONS]
    where 
        (
            @FILTERZEROBALANCE = 0 or
            (@FILTERZEROBALANCE = 1 and [REGISTRATIONS].[BALANCE] > 0) 
        )
    option (recompile);

    return;

end