USP_EVENTSEATING_UNSEATEDREGISTRANTS

            CREATE procedure dbo.USP_EVENTSEATING_UNSEATEDREGISTRANTS
            (
                @EVENTID uniqueidentifier,
                @REPORTUSERID nvarchar(128) = null,
                @SHOWSUBREPORT bit = 0,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as caller
            as

            set nocount on;

            if @SHOWSUBREPORT = 1
            begin
                declare @CURRENTAPPUSERID uniqueidentifier;
                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;

                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                select REGISTRANTNAME, HOSTNAME, GROUPNAME, LASTNAME from(
                select
                    REGISTRANT.ID,
                    CONSTITUENT_NF.NAME REGISTRANTNAME,
                    case when REGISTRANT.GUESTOFREGISTRANTID is not null then
                        (select NF.NAME
                            from dbo.REGISTRANT GUESTOF 
                            cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GUESTOF.CONSTITUENTID) NF
                            where GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID)
                    else 
                        case when exists(select ID from dbo.REGISTRANT HOST where HOST.GUESTOFREGISTRANTID = REGISTRANT.ID) then
                            CONSTITUENT_NF.NAME
                        end
                    end HOSTNAME,
                    (select 
                        EVENTGROUP.NAME 
                    from 
                        dbo.EVENTGROUP
                        inner join dbo.EVENTGROUPMEMBER on EVENTGROUPMEMBER.EVENTGROUPID = EVENTGROUP.ID
                    where
                        EVENTGROUPMEMBER.REGISTRANTID = REGISTRANT.ID) as GROUPNAME,
                    CONSTITUENT.KEYNAME as LASTNAME
                from
                    dbo.REGISTRANT
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
                    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) CONSTITUENT_NF
                where
                    REGISTRANT.EVENTID = @EVENTID
                    and REGISTRANT.WILLNOTATTEND = 0
                    and REGISTRANT.ISCANCELLED = 0
                    and (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REGISTRANT.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)

                union all

                select
                    REGISTRANT.ID,
                    'Guest of ' + GUESTOFCONSTITUENT_NF.NAME REGISTRANTNAME,
                    GUESTOFCONSTITUENT_NF.NAME HOSTNAME,
                    (select 
                        EVENTGROUP.NAME 
                    from 
                        dbo.EVENTGROUP
                        inner join dbo.EVENTGROUPMEMBER on EVENTGROUPMEMBER.EVENTGROUPID = EVENTGROUP.ID
                    where
                        EVENTGROUPMEMBER.REGISTRANTID = REGISTRANT.ID) as GROUPNAME,
                    CONSTITUENT.KEYNAME as LASTNAME
                from
                    dbo.REGISTRANT
                    inner join dbo.REGISTRANT GUESTOF on GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = GUESTOF.CONSTITUENTID
                    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GUESTOF.CONSTITUENTID) GUESTOFCONSTITUENT_NF
                where
                    REGISTRANT.EVENTID = @EVENTID
                    and REGISTRANT.WILLNOTATTEND = 0
                    and REGISTRANT.ISCANCELLED = 0
                    and REGISTRANT.CONSTITUENTID is null
                    and (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REGISTRANT.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                )SUBQ
                where
                    not exists(select top 1 REGISTRANTID from dbo.EVENTSEATINGSEAT where EVENTSEATINGSEAT.REGISTRANTID = SUBQ.ID) 
                order by
                    REGISTRANTNAME;

            end