USP_DATALIST_FAFEVENTSELECTION

CREATE procedure dbo.USP_DATALIST_FAFEVENTSELECTION
(
  @FILTERNAME nvarchar(200) = null,
  @INCLUDEINACTIVE bit = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @FAFPROGRAMID uniqueidentifier = null
)
as
set nocount on;

declare @ISACTIVE bit;

if ISNULL(@INCLUDEINACTIVE, 0) = 0
    set @ISACTIVE = 1;
else
    set @ISACTIVE = 0;

set @FILTERNAME = ISNULL(@FILTERNAME, '');
set @FILTERNAME = REPLACE(@FILTERNAME, '\', '\\');
set @FILTERNAME = REPLACE(@FILTERNAME, '[', '\[');
set @FILTERNAME = REPLACE(@FILTERNAME, ']', '\]');
set @FILTERNAME = REPLACE(@FILTERNAME, '_', '\_');
set @FILTERNAME = REPLACE(@FILTERNAME, '%', '\%');
set @FILTERNAME = REPLACE(@FILTERNAME, '?', '_');
set @FILTERNAME = REPLACE(@FILTERNAME, '*', '%');

if @FILTERNAME = '' or @FILTERNAME = '%'
    set @FILTERNAME = '';
else
    set @FILTERNAME = '%' + @FILTERNAME + '%';

select top 500 EVENT.ID, EVENT.NAME from dbo.EVENT (nolock)
inner join dbo.EVENTEXTENSION (nolock) on EVENT.ID = EVENTEXTENSION.EVENTID
inner join dbo.EVENTLOCATION (nolock) on EVENT.EVENTLOCATIONID = EVENTLOCATION.ID
where (@FILTERNAME = '' or EVENT.NAME like @FILTERNAME escape '\')
and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = @ISACTIVE)
and (@FAFPROGRAMID is null or EVENTEXTENSION.FAFPROGRAMID = @FAFPROGRAMID)
and
(
  exists
  (
    select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENT.ID) EVENTSITE
    where
      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))
  )
)
order by EVENT.NAME