USP_DATALIST_PARTICIPANTEMAILTEMPLATEWITHTASK

CREATE procedure dbo.USP_DATALIST_PARTICIPANTEMAILTEMPLATEWITHTASK
(
  @CLIENTUSERSID int,
    @EVENTID uniqueidentifier,
  @FAFTASKID uniqueidentifier = null,
  @SEARCHTEXT nvarchar(255) = null,
  @ISLETTER bit = 0
)
as
set nocount on;

set @SEARCHTEXT = ISNULL(@SEARCHTEXT, '');

declare @INSERTSQL nvarchar(max);
declare @PARAMETERDEFINITION nvarchar(500);

set @PARAMETERDEFINITION = '@CLIENTUSERSIDPARAMETER int, @EVENTIDPARAMETER uniqueidentifier, @FAFTASKIDPARAMETER uniqueidentifier, @SEARCHTEXTPARAMETER nvarchar(255)'

CREATE TABLE #TEMPTEMPLATE
(
  ID uniqueidentifier,
  SUBJECT nvarchar(510),
  CONTENTHTML nvarchar(max),
  FAFTASKID uniqueidentifier,
  FAFTASKNAME nvarchar(200),
  LASTDAYSENT datetime,
  ADMINTEMPLATE bit
)

set @INSERTSQL = 'insert into #TEMPTEMPLATE (ID, SUBJECT, CONTENTHTML, FAFTASKID, FAFTASKNAME, LASTDAYSENT, ADMINTEMPLATE) ';

set @INSERTSQL = @INSERTSQL + 'select ';
set @INSERTSQL = @INSERTSQL + 'PARTICIPANTEMAILTEMPLATE.ID, ';
set @INSERTSQL = @INSERTSQL + 'PARTICIPANTEMAILTEMPLATE.SUBJECT, ';
set @INSERTSQL = @INSERTSQL + 'PARTICIPANTEMAILTEMPLATE.CONTENTHTML, ';
set @INSERTSQL = @INSERTSQL + 'PARTICIPANTEMAILTEMPLATE.FAFTASKID, ';
set @INSERTSQL = @INSERTSQL + 'FAFTASK.NAME as FAFTASKNAME, ';
set @INSERTSQL = @INSERTSQL + 'PARTICIPANTEMAILTEMPLATE.DATECHANGED as LASTDAYSENT, ';
set @INSERTSQL = @INSERTSQL + '0 as ADMINTEMPLATE ';
set @INSERTSQL = @INSERTSQL + 'from dbo.PARTICIPANTEMAILTEMPLATE (nolock) ';
set @INSERTSQL = @INSERTSQL + 'inner join dbo.FAFTASK (nolock) on FAFTASK.ID = PARTICIPANTEMAILTEMPLATE.FAFTASKID ';
set @INSERTSQL = @INSERTSQL + 'where ';
set @INSERTSQL = @INSERTSQL + 'FAFTASK.HASTEMPLATE = 1 ';
set @INSERTSQL = @INSERTSQL + 'and PARTICIPANTEMAILTEMPLATE.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERSIDPARAMETER)) ';
set @INSERTSQL = @INSERTSQL + 'and PARTICIPANTEMAILTEMPLATE.EVENTID = @EVENTIDPARAMETER ';
set @INSERTSQL = @INSERTSQL + 'and (@FAFTASKIDPARAMETER is null or PARTICIPANTEMAILTEMPLATE.FAFTASKID = @FAFTASKIDPARAMETER) ';
if @SEARCHTEXT <> ''
    set @INSERTSQL = @INSERTSQL + 'and (contains(PARTICIPANTEMAILTEMPLATE.SUBJECT, @SEARCHTEXTPARAMETER) or contains(PARTICIPANTEMAILTEMPLATE.CONTENTHTML, @SEARCHTEXTPARAMETER)) ';
if @ISLETTER = 0
  begin
    set @INSERTSQL = @INSERTSQL + 'and PARTICIPANTEMAILTEMPLATE.ISLETTER = 0 ';
  end
else
  begin
    set @INSERTSQL = @INSERTSQL + 'and (PARTICIPANTEMAILTEMPLATE.ISLETTER = 1 or PARTICIPANTEMAILTEMPLATE.AVAILABLEASLETTER = 1) ';
  end

set @INSERTSQL = @INSERTSQL + 'union all ';

set @INSERTSQL = @INSERTSQL + 'select ';
set @INSERTSQL = @INSERTSQL + 'EVENTEMAILTEMPLATE.ID, ';
set @INSERTSQL = @INSERTSQL + 'EMAILTEMPLATE.SUBJECT, ';
set @INSERTSQL = @INSERTSQL + 'EMAILTEMPLATE.CONTENTHTML, ';
set @INSERTSQL = @INSERTSQL + 'EVENTEMAILTEMPLATE.FAFTASKID, ';
set @INSERTSQL = @INSERTSQL + 'FAFTASK.NAME as FAFTASKNAME, ';
set @INSERTSQL = @INSERTSQL + 'null as LASTDAYSENT, ';
set @INSERTSQL = @INSERTSQL + '1 as ADMINTEMPLATE ';
set @INSERTSQL = @INSERTSQL + 'from dbo.EVENTEMAILTEMPLATE (nolock) ';
set @INSERTSQL = @INSERTSQL + 'inner join dbo.EMAILTEMPLATE (nolock) on EMAILTEMPLATE.ID = EVENTEMAILTEMPLATE.EMAILTEMPLATEID ';
set @INSERTSQL = @INSERTSQL + 'inner join dbo.FAFTASK (nolock) on FAFTASK.ID = EVENTEMAILTEMPLATE.FAFTASKID ';
set @INSERTSQL = @INSERTSQL + 'where ';
set @INSERTSQL = @INSERTSQL + 'FAFTASK.HASTEMPLATE = 1 ';
set @INSERTSQL = @INSERTSQL + 'and EVENTEMAILTEMPLATE.ISACTIVE = 1 ';
set @INSERTSQL = @INSERTSQL + 'and EVENTEMAILTEMPLATE.EVENTID = @EVENTIDPARAMETER ';
set @INSERTSQL = @INSERTSQL + 'and (@FAFTASKIDPARAMETER is null or EVENTEMAILTEMPLATE.FAFTASKID = @FAFTASKIDPARAMETER) ';
if @SEARCHTEXT <> ''
    set @INSERTSQL = @INSERTSQL + 'and (contains(EMAILTEMPLATE.SUBJECT, @SEARCHTEXTPARAMETER) or contains(EMAILTEMPLATE.CONTENTHTML, @SEARCHTEXTPARAMETER)) ';

exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @CLIENTUSERSIDPARAMETER = @CLIENTUSERSID, @EVENTIDPARAMETER = @EVENTID, @FAFTASKIDPARAMETER = @FAFTASKID, @SEARCHTEXTPARAMETER = @SEARCHTEXT;

select * from #TEMPTEMPLATE order by ADMINTEMPLATE asc, LASTDAYSENT desc, FAFTASKNAME asc, SUBJECT asc
drop table #TEMPTEMPLATE