USP_SEARCHLIST_PROGRAMEVENT

Provides the ability to search for program events.
Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@EVENTLOCATION	uniqueidentifier	IN	Location
@SALESMETHODTYPECODE	tinyint	IN	Sales method
@STARTDATE	date	IN	from
@ENDDATE	date	IN	to
@DATESELECTTYPE	tinyint	IN	Date
@INCLUDEPREREGISTEREDPROGRAMS	bit	IN	Include preregistered programs
Definition

Copy


                CREATE procedure dbo.USP_SEARCHLIST_PROGRAMEVENT
                (
                    @NAME nvarchar(100) = null,
                    @MAXROWS smallint = 500,
                    @EVENTLOCATION uniqueidentifier = null,
                    @SALESMETHODTYPECODE tinyint = null,
                    @STARTDATE date = null,
                    @ENDDATE date = null,
                    @DATESELECTTYPE tinyint = null,
                    @INCLUDEPREREGISTEREDPROGRAMS bit = 1
                )
                as
                    declare @CURRENTDATE date = getdate();

                    if @DATESELECTTYPE is null
                        set @DATESELECTTYPE = 1;

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

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

                    set @NAME = replace(@NAME, '*', '%');
                    set @NAME = coalesce(@NAME,'') + '%';

                    select distinct top(@MAXROWS)
                        EVENT.ID,
                        EVENT.NAME,
                        EVENT.STARTDATETIME,
                        EVENT.ENDDATETIME,
                        EVENT.STARTTIME,
                        EVENT.ENDTIME,
                        EVENT.CAPACITY,
                        dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONNAME,
                        EVENT.DESCRIPTION
                    from dbo.EVENT
                    inner join dbo.PROGRAM
                        on EVENT.PROGRAMID = PROGRAM.ID
                    left join dbo.PROGRAMEVENTLOCATION 
                        on EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
                    left join dbo.EVENTLOCATION
                        on PROGRAMEVENTLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                    where
                        EVENT.NAME like @NAME
                        and (@EVENTLOCATION is null or EVENTLOCATION.ID = @EVENTLOCATION)
                        and (
                            @SALESMETHODTYPECODE is null
                            or exists (
                                select top 1 1 from dbo.EVENTSALESMETHOD
                                inner join dbo.SALESMETHOD 
                                    on EVENTSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                                where EVENTSALESMETHOD.EVENTID = EVENT.ID and
                                    SALESMETHOD.TYPECODE = @SALESMETHODTYPECODE
                            )
                        )
                        and (
                            @DATESELECTTYPE = 0
                            or [EVENT].[STARTDATE] between @STARTDATE and @ENDDATE
                        )
                        and (PROGRAM.ISPREREGISTERED = 0 or @INCLUDEPREREGISTEREDPROGRAMS = 1)
                    order by
                        EVENT.STARTDATETIME
                    option (recompile);