USP_DATALIST_PREREGISTEREDPROGRAMEVENTREGISTRANT

CREATE procedure dbo.USP_DATALIST_PREREGISTEREDPROGRAMEVENTREGISTRANT(@EVENTID uniqueidentifier)
as
    set nocount on;

    select
        REGISTRANT.ID as ID,
        case
            when REGISTRANT.CONSTITUENTID is null then
                '(Unnamed guest)'
            else
                dbo.UFN_CONSTITUENT_SORTNAME(REGISTRANT.CONSTITUENTID)
        end as REGISTRANTNAME,
        case
            when ORDERTOTALS.BALANCE > 0 then
                0
            else
                1
        end as ORDERPAID,
        case
            when REGISTRANT.ISCANCELLED = 1 then
                'Canceled'
            else
                case
                    when REGISTRANT.ATTENDED = 1 then
                        'Yes'
                    else
                        'No'
                end
        end as STATUS,
        (select dbo.UDA_BUILDLIST(RESTRICTION.NAME) from 
            (    
                select EVENTRESTRICTIONOPTION.NAME
                from dbo.EVENTRESTRICTIONOPTION
                inner join dbo.CONSTITUENTRESTRICTIONOPTION 
                    on CONSTITUENTRESTRICTIONOPTION.EVENTRESTRICTIONOPTIONID = EVENTRESTRICTIONOPTION.ID
                where CONSTITUENTRESTRICTIONOPTION.CONSTITUENTID = REGISTRANT.CONSTITUENTID
            ) as [RESTRICTION]
        ) as RESTRICTIONS,
        case
            when REGISTRANT.GUESTOFREGISTRANTID is null then
                ''
            else
                dbo.UFN_REGISTRANT_GETHOSTNAME_LASTNAMESORT(REGISTRANT.ID, REGISTRANT.GUESTOFREGISTRANTID) 
        end as HOST,
        REGISTRANT.NOTES as NOTES,
        SALESORDERITEM.SALESORDERID as ORDERID,
        REGISTRANT.CONSTITUENTID as CONSTITUENTID,
        REGISTRANT.ATTENDED as ATTENDED,
        REGISTRANT.ISCANCELLED as CANCELLED,
        case
            when REGISTRANT.CONSTITUENTID is null then
                0
            else
                1
        end as ISUNNAMEDGUEST,
        case 
            when REGISTRANT.CONSTITUENTID is not null then
                dbo.UFN_CONSTITUENT_SORTNAME(REGISTRANT.CONSTITUENTID)
            when REGISTRANT.GUESTOFREGISTRANTID is not null then
                dbo.UFN_REGISTRANT_GETHOSTNAME_LASTNAMESORT(REGISTRANT.ID, REGISTRANT.GUESTOFREGISTRANTID) 
            else
                ''
        end as SORTNAME
    from
        dbo.REGISTRANT
    inner join
        dbo.SALESORDERITEMTICKETREGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEM.ID
    outer apply
        dbo.UFN_SALESORDER_TOTALS(SALESORDERITEM.SALESORDERID) as ORDERTOTALS
    where
        REGISTRANT.EVENTID = @EVENTID
        and REGISTRANT.WILLNOTATTEND = 0
    order by
        SORTNAME asc, ISUNNAMEDGUEST desc