USP_DATALIST_EVENTPROFILEREPORT_REGISTRANT

                CREATE procedure dbo.USP_DATALIST_EVENTPROFILEREPORT_REGISTRANT
                (
                    @EVENTID uniqueidentifier,
                    @CURRENCYCODE smallint = null,
                    @ISVISIBLE bit = 1,
                    @CURRENTAPPUSERID uniqueidentifier
                )
                as
                    set nocount on;

                    if @ISVISIBLE = 1
                    begin

                        declare @ISADMIN bit;
                        declare @APPUSER_IN_NONRACROLE bit;
                        declare @APPUSER_IN_NOSECGROUPROLE bit;

                        declare @SELECTEDCURRENCYID uniqueidentifier = null;
                        declare @DECIMALDIGITS tinyint;
                        declare @ISOCURRENCYCODE nvarchar(3);
                        declare @SYMBOLDISPLAYSETTINGCODE tinyint;
                        declare @CURRENCYSYMBOL nvarchar(5);
                        declare @REGISTRATIONTYPE nvarchar(12);

                        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);

                        if @CURRENCYCODE = 0
                            select @SELECTEDCURRENCYID = EVENT.BASECURRENCYID
                            from dbo.EVENT
                            where EVENT.ID = @EVENTID
                        else
                            set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                        select
                            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                            @ISOCURRENCYCODE = CURRENCY.ISO4217,
                            @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
                            @SYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
                        from
                            dbo.CURRENCY
                        where
                            CURRENCY.ID = @SELECTEDCURRENCYID;

                        select
                            REG.ID as REGISTRANTID,
                            --dbo.UFN_REGISTRANT_GETNAME(REG.ID) as REGISTRANTNAME,

                            case
                                when REG.CONSTITUENTID is null then dbo.UFN_REGISTRANT_GETNAME(REG.ID)
                                else dbo.UFN_NAMEFORMAT_08(coalesce(C.ID, C2.ID),coalesce(C.KEYNAME,C2.KEYNAME),coalesce(C.FIRSTNAME,C2.FIRSTNAME),coalesce(C.MIDDLENAME,C2.MIDDLENAME),NULL,NULL,NULL,NULL,NULL,NULL,NULL)
                            end [REGISTRANTNAME],
                            dbo.UFN_REGISTRANT_GETHOSTNAME(REG.ID, REG.GUESTOFREGISTRANTID) as HOST,
                            REG.ATTENDED,
                            REGISTRANTBALANCE.BALANCEINCURRENCY as BALANCE,
                            (select count(GUESTS.ID) from dbo.REGISTRANT as GUESTS where GUESTS.GUESTOFREGISTRANTID = REG.ID and dbo.[UFN_REGISTRANT_ISCANCELLED](GUESTS.ID) = 0 and GUESTS.WILLNOTATTEND = 0) as NUMGUESTS,
                            'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID='+convert(nvarchar(36),REG.CONSTITUENTID) as REGISTRANTLINK,
                            'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID='+convert(nvarchar(36),REG.ID) as EVENTREGISTRANTLINK,
                            --0 Registered, 1 Walk-in, 2 Canceled, 3 Will not attend

                            case 
                                when REG.ISWALKIN=1 then 1
                                when dbo.UFN_REGISTRANT_ISCANCELLED(REG.ID) = 1 then 2
                                when REG.WILLNOTATTEND=1 then 3
                                else 0 --Registered

                            end as STATUSCODE,
                            (select count(ID) from dbo.REGISTRANT where REGISTRANT.ID = REG.ID and dbo.[UFN_REGISTRANT_ISCANCELLED](REGISTRANT.ID) = 0 and REGISTRANT.WILLNOTATTEND = 0) as NUMREGISTRANTS,
                            REG.DATEADDED as DATEREGISTERED,
                            @ISOCURRENCYCODE [ISOCURRENCYCODE],
                            @CURRENCYSYMBOL [CURRENCYSYMBOL],
                            @SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                            @DECIMALDIGITS [DECIMALDIGITS],
                            -- 0 No, 1 Yes, 2 blank, 3 No show

                            case 
                               when REG.WILLNOTATTEND = 1 or dbo.UFN_REGISTRANT_ISCANCELLED(REG.ID) = 1 then 0
                                when REG.ISWALKIN = 0 and REG.WILLNOTATTEND = 0 and dbo.UFN_REGISTRANT_ISCANCELLED(REG.ID) = 0 and REG.ATTENDED=0 and (EVENT.ISPROCESSED=1 or REG.USERMARKEDATTENDANCE = 1) then 3
                                when REG.ISWALKIN = 0 and dbo.UFN_REGISTRANT_ISCANCELLED(REG.ID) = 0 and REG.ATTENDED=0 and EVENT.ISPROCESSED=0 and REG.USERMARKEDATTENDANCE = 0  then 2
                                else REG.ATTENDED
                            end as [ATTENDED2]
                        from
                            dbo.REGISTRANT as REG
                            left join dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK(@SELECTEDCURRENCYID) as REGISTRANTBALANCE on REG.ID = REGISTRANTBALANCE.ID
                            inner join EVENT on EVENT.ID = REG.EVENTID
                            left join dbo.CONSTITUENT C on C.ID = REG.CONSTITUENTID
                            left join dbo.CONSTITUENT C2 on C2.ID = REG.GUESTOFREGISTRANTID
                        where
                            REG.EVENTID = @EVENTID
                            and ((REG.CONSTITUENTID is null) 
                                or (@ISADMIN = 1 or 
                                    @APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REG.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1))
                        order by
                            REGISTRANTNAME, HOST;
                    end