USP_DATALIST_PROGRAMREGISTRANTREPORT

CREATE procedure dbo.USP_DATALIST_PROGRAMREGISTRANTREPORT
(
    @PROGRAMID uniqueidentifier = null,
    @FROM date = null,
    @TO date = null,
    @LASTNAMEFIRST tinyint = 0
)
as
    set nocount on;

    select
        case when @LASTNAMEFIRST = 1
            then dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null)
            else isnull(CONSTITUENT.NAME, '')
        end as REGISTRANTNAME,
        REGISTRANT.ATTENDED as ATTENDED,
        case when ORDERTOTALS.BALANCE > 0
            then 0
        else
            1
        end as ORDERPAID,
        (select dbo.UDA_BUILDLIST(PREFERENCE.NAME) from 
            (    
                select EVENTPREFERENCE.NAME
                from dbo.EVENTPREFERENCE
                inner join dbo.REGISTRANTPREFERENCE
                    on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
                inner join dbo.REGISTRANT REG
                    on REG.ID = REGISTRANTPREFERENCE.REGISTRANTID
                where REG.ID = REGISTRANT.ID
            ) as [PREFERENCE]
        ) as PREFERENCES,
        REGISTRANT.NOTES as NOTES,
        EVENT.NAME as EVENTNAME,
        (select count (ID) from dbo.REGISTRANT REG2 where REG2.EVENTID = REGISTRANT.EVENTID) as REGISTRANTCOUNT,
        (select count (ID) from dbo.REGISTRANT REG3 where REG3.EVENTID = REGISTRANT.EVENTID and REG3.ATTENDED = 1) as ATTENDEDCOUNT,
        REGISTRANT.EVENTID as EVENTID,
        dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.STARTTIME) as STARTTIME,
        dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.ENDTIME) as ENDTIME,
        EVENT.STARTDATETIME as STARTDATE,
        dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONS,
        (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 @LASTNAMEFIRST = 1
            then dbo.UFN_REGISTRANT_GETHOSTNAME_LFM(REGISTRANT.ID, REGISTRANT.GUESTOFREGISTRANTID)
            else dbo.UFN_REGISTRANT_GETHOSTNAME(REGISTRANT.ID, REGISTRANT.GUESTOFREGISTRANTID)
        end as HOST,
        REGISTRANT.ISCANCELLED as CANCELLED
    from dbo.REGISTRANT
    inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
    inner join dbo.PROGRAM on PROGRAM.ID = EVENT.PROGRAMID
    inner join dbo.SALESORDERITEMTICKETREGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
    inner join dbo.SALESORDERITEM on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEM.ID
    left outer join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
    outer apply dbo.UFN_SALESORDER_TOTALS(SALESORDERITEM.SALESORDERID) as ORDERTOTALS
    where ((PROGRAM.ID = @PROGRAMID) or (@PROGRAMID is null))
        and EVENT.STARTDATE between @FROM and @TO
        and REGISTRANT.WILLNOTATTEND = 0
        and PROGRAM.ISACTIVE = 1