USP_REPORT_PROGRAMREGISTRANTS

create procedure dbo.USP_REPORT_PROGRAMREGISTRANTS
(
    @PROGRAMID uniqueidentifier = null,
    @FROMDATE date = null,
    @TODATE date = null,
    @SORTTYPE tinyint = 0,
    @EVENTID uniqueidentifier = null ,
    @REGISTRATIONSECTIONSTOINCLUDE xml = null
)
as
    set nocount on;

    select
        case when @SORTTYPE = 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 (REG2.ID) from dbo.REGISTRANT REG2 
            inner join dbo.SALESORDERITEMTICKETREGISTRANT S on REG2.ID = S.REGISTRANTID -- this inner join filters out registrants without tickets (in case 'willnotattend' is not marked)

            where REG2.EVENTID = REGISTRANT.EVENTID and REG2.WILLNOTATTEND = 0 and REG2.ISCANCELLED = 0) as REGISTRANTCOUNT,
        (select count (ID) from dbo.REGISTRANT REG3 where REG3.EVENTID = REGISTRANT.EVENTID and REG3.ATTENDED = 1 and REG3.ISCANCELLED = 0) 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 @SORTTYPE = 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,
        REGISTRANT.ID as REGISTRANTID,
        (
            select
                dbo.UDA_BUILDLISTWITHDELIMITER(REGINFO.VALUE, ',')
            from dbo.UFN_REPORT_REGISTRANTREGISTRATIONINFORMATION(REGISTRANT.ID, @REGISTRATIONSECTIONSTOINCLUDE) REGINFO
        ) as REGISTRATIONINFORMATION,
        coalesce(PHONE.NUMBER, '') as PHONE,
        coalesce(EMAILADDRESS.EMAILADDRESS, '') as EMAIL,
        case
            when EVENT.HASCUSTOMREGISTRATIONINFORMATION = 1 then
                case 
                    when exists (
                            select 1
                            from dbo.PROGRAMEVENTREGISTRATIONSECTION
                            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
                                on PROGRAMEVENTREGISTRATIONSECTION.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID
                            where PROGRAMEVENTREGISTRATIONSECTION.EVENTID = EVENT.ID
                        ) then 1
                    else 0
                end
            else
                case
                    when exists (
                            select 1
                            from dbo.PROGRAMEVENTREGISTRATIONSECTION
                            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
                                on PROGRAMEVENTREGISTRATIONSECTION.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID
                            where PROGRAMEVENTREGISTRATIONSECTION.PROGRAMID = PROGRAM.ID
                        ) then 1
                    else 0
                end            
        end as HASREGISTRATIONINFORMATION
    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
    left outer join dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
    left outer join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
    outer apply dbo.UFN_SALESORDER_TOTALS(SALESORDERITEM.SALESORDERID) as ORDERTOTALS

    where (@EVENTID is null and ((PROGRAM.ID = @PROGRAMID) or (@PROGRAMID is null))
        and EVENT.STARTDATE between @FROMDATE and @TODATE
        and REGISTRANT.WILLNOTATTEND = 0
        and PROGRAM.ISACTIVE = 1)
    or (@EVENTID = EVENT.ID
        and REGISTRANT.WILLNOTATTEND = 0)
    order by STARTDATE