USP_DATALIST_PREREGISTEREDPROGRAMEVENTREGISTRANTVIEW

                CREATE procedure dbo.USP_DATALIST_PREREGISTEREDPROGRAMEVENTREGISTRANTVIEW
                (
                    @CONSTITUENTID uniqueidentifier,
                    @DATESELECTTYPE tinyint = null,
                    @STARTDATE date = null,
                    @ENDDATE date = null
                )
                as
                set nocount on;

                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

                if @DATESELECTTYPE is null
                set @DATESELECTTYPE = 2;

                set @STARTDATE = case @DATESELECTTYPE
                    when 0 then @CURRENTDATE 
                    when 1 then @CURRENTDATE 
                    when 3 then @STARTDATE
                    when 4 then @STARTDATE
                end;

                set @ENDDATE = case @DATESELECTTYPE
                    when 0 then @CURRENTDATE 
                    when 1 then dateadd(week,1, @CURRENTDATE) 
                    when 3 then @ENDDATE
                    when 4 then @STARTDATE
                end;                

                select REGISTRANT.ID,
                    EVENT.ID,
                    SALESORDERITEM.SALESORDERID,
                    EVENT.NAME,
                    EVENT.STARTDATE,
                    EVENT.ENDDATE,
                    case when ORDERTOTALS.BALANCE > 0 
                        then 0
                    else
                        1
                    end as ORDERPAID,
                    ~REGISTRANT.ISCANCELLED as WILLATTEND,
                    case when SALESORDERITEM.SALESORDERID is null
                        then 1
                    else 
                        0
                    end as ISHOST,
                    case when REGISTRANT.ISCANCELLED = 1
                        then 'Cancelled'
                    else
                        case when REGISTRANT.ATTENDED = 1
                            then 'Attended'
                        else
                            'Not attended'
                        end
                    end as STATUS
                from dbo.REGISTRANT
                inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
                left join dbo.SALESORDERITEMTICKETREGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
                left join dbo.SALESORDERITEM on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEM.ID
                outer apply dbo.UFN_SALESORDER_TOTALS(SALESORDERITEM.SALESORDERID) as ORDERTOTALS
                where EVENT.PROGRAMID is not null
                    and ((EVENT.STARTDATE between @STARTDATE and @ENDDATE) or (@DATESELECTTYPE = 2))
                    and REGISTRANT.CONSTITUENTID = @CONSTITUENTID
                order by EVENT.STARTDATETIME asc;