USP_DATALIST_PARTICIPANTCOMMUNICATIONTEMPLATESELECTION

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

declare @ISACTIVE bit;

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

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

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

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

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

select top 500 EMAILTEMPLATE.Guid as ID, EMAILTEMPLATE.NAME, EVENT.NAME, EMAILTEMPLATE.SUBJECT from dbo.EMAILTEMPLATE (nolock)
inner join dbo.EVENTEMAILTEMPLATE (nolock) on EVENTEMAILTEMPLATE.EMAILTEMPLATEID = EMAILTEMPLATE.ID
inner join dbo.EVENT (nolock) on EVENT.ID = EVENTEMAILTEMPLATE.EVENTID
inner join dbo.EVENTEXTENSION (nolock) on EVENT.ID = EVENTEXTENSION.EVENTID
where EVENTEMAILTEMPLATE.CONFIRMATIONTYPECODE = 100
and EVENTEMAILTEMPLATE.ISACTIVE = 1
and (@INCLUDEINACTIVE = 1 or EVENT.ISACTIVE = @ISACTIVE) 
and (@FAFPROGRAMID is null or EVENTEXTENSION.FAFPROGRAMID = @FAFPROGRAMID)
and (@FAFTASKID is null or EVENTEMAILTEMPLATE.FAFTASKID = @FAFTASKID)
and (@FILTERTEMPLATENAME = '' or EMAILTEMPLATE.SUBJECT like @FILTERTEMPLATENAME escape '\')
and (@FILTEREVENTNAME = '' or EVENT.NAME like @FILTEREVENTNAME escape '\')
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 EMAILTEMPLATE.NAME