USP_DATALIST_REGISTRANTVIEW_2

                CREATE procedure dbo.USP_DATALIST_REGISTRANTVIEW_2
                (
                    @MAXROWS int,
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null
                )
                as
                set nocount on;

                declare @REGISTRATIONS table
                (
                    ISMAINEVENT bit,
                    ISSTANDALONEEVENT bit,
                    REGISTRANTID uniqueidentifier,
                    EVENTID uniqueidentifier,
                    EVENTNAME nvarchar(100),
                    EVENTSTARTDATE datetime,
                    EVENTENDDATE datetime,
                    REGISTRATIONOPTION nvarchar(100),
                    QUANTITY int,
                    REGISTRATIONFEE money,
                    RECEIPTAMOUNT money,
                    MAINEVENTNAME nvarchar(100),
                    MAINEVENTID uniqueidentifier,
                    SITEID uniqueidentifier,
                    EVENTBASECURRENCYID uniqueidentifier
                )

                insert into @REGISTRATIONS(ISMAINEVENT, ISSTANDALONEEVENT, REGISTRANTID, EVENTID, EVENTNAME, EVENTSTARTDATE, EVENTENDDATE, REGISTRATIONOPTION, QUANTITY, REGISTRATIONFEE, RECEIPTAMOUNT, MAINEVENTNAME, MAINEVENTID, EVENTBASECURRENCYID)
                    select top (@MAXROWS)
                        case when (select count(ID) from dbo.EVENT as [SUBEVENT] where [SUBEVENT].MAINEVENTID = EVENT.ID) > 0 then 1 else 0 end, --ISMAINEVENT

                        case when MAINEVENTID is null and (select count(ID) from dbo.EVENT as [SUBEVENT] where [SUBEVENT].MAINEVENTID = EVENT.ID) = 0 then 1 else 0 end, --ISSTANDALONEVENT

                        REGISTRANT.ID, --REGISTRANTID

                        EVENT.ID, --EVENTID

                        EVENT.NAME, --EVENTNAME

                        EVENT.STARTDATE, --EVENTSTARTDATE

                        EVENT.ENDDATE, --EVENTENDDATE

                        dbo.UFN_EVENTPRICE_GETDESCRIPTIONFROMID(REGISTRANTREGISTRATION.EVENTPRICEID), --REGISTRATIONOPTION

                        REGISTRANTREGISTRATION.QUANTITY, --QUANTITY

                        REGISTRANTREGISTRATION.AMOUNT, --REGISTRATIONFEE

                        REGISTRANTREGISTRATION.RECEIPTAMOUNT, --RECEIPTAMOUNT

                        case when not EVENT.MAINEVENTID is null then coalesce(dbo.UFN_EVENT_GETNAME(EVENT.MAINEVENTID), N'')
                             when (select count(ID) from dbo.EVENT as [SUBEVENT] where [SUBEVENT].MAINEVENTID = EVENT.ID) > 0 then EVENT.NAME
                             else N'' end, --MAINEVENTNAME

                        EVENT.MAINEVENTID, --MAINEVENTID

                        EVENT.BASECURRENCYID
                    from dbo.REGISTRANT
                    inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                    left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
                    where REGISTRANT.CONSTITUENTID = @CONSTITUENTID
                        and EVENT.PROGRAMID is null;

                select @MAXROWS = @MAXROWS - count(REGISTRANTID) from @REGISTRATIONS;

                /* AKG CR297799-041008 do not show the main event when constituent is not registered to that event
                insert into @REGISTRATIONS(ISMAINEVENT, ISSTANDALONEEVENT, REGISTRANTID, EVENTID, EVENTNAME, EVENTSTARTDATE, EVENTENDDATE, REGISTRATIONOPTION, QUANTITY, REGISTRATIONFEE, RECEIPTAMOUNT, MAINEVENTNAME, MAINEVENTID)
                    select distinct top (@MAXROWS)
                        1, --ISMAINEVENT
                        0, --ISSTANDALONEEVENT
                        null, --REGISTRANTID
                        EVENT.ID, --EVENTID
                        EVENT.NAME, --EVENTNAME
                        EVENT.STARTDATE, --EVENTSTARTDATE
                        EVENT.ENDDATE, --EVENTENDDATE
                        N'', --REGISTRATIONOPTION
                        0, --QUANTITIY
                        0, --REGISTRATIONFEE
                        0, --RECEIPTAMOUNT
                        EVENT.NAME, --MAINEVENTNAME -- This needs to be filled in for the MAINEVENTNAME so grouping works intelligently
                        null --MAINEVENTID
                    from @REGISTRATIONS as [REGISTRATIONS]
                    inner join dbo.EVENT on EVENT.ID = [REGISTRATIONS].MAINEVENTID
                    left join @REGISTRATIONS as [JOINREGISTRATIONS] on [JOINREGISTRATIONS].EVENTID = EVENT.ID
                    where [JOINREGISTRATIONS].EVENTID is null
                        and EVENT.PROGRAMID is null;
                */

                select 
                    ISMAINEVENT,
                    ISSTANDALONEEVENT,
                    REGISTRANTID,
                    r.EVENTID,
                    EVENTNAME,
                    EVENTSTARTDATE,
                    EVENTENDDATE,
                    REGISTRATIONOPTION,
                    QUANTITY,
                    REGISTRATIONFEE,
                    RECEIPTAMOUNT,
                    MAINEVENTNAME,
                    EVENTBASECURRENCYID,
                    ISFAFREGISTRANT = 
                        Case IsNull(ee.EventType, '')
                            when '' then 0
                            else 1
                        End
                from @REGISTRATIONS r
                left join EVENTEXTENSION ee on r.EVENTID = ee.EVENTID 
                where
                    dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, r.EVENTID) = 1 and
                    (@SITEFILTERMODE = 0
                        or exists
                                (
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where SITEFILTER.SITEID in 
                                        (
                                            select SITEID from dbo.UFN_EVENT_GETSITES(r.EVENTID)
                                        )
                                )
                    )
                order by MAINEVENTNAME, ISMAINEVENT desc, EVENTSTARTDATE, EVENTENDDATE, EVENTNAME;