USP_DATAFORMTEMPLATE_ADD_PARTICIPANT_EMAIL_TEMPLATE

The save procedure used by the add dataform template "Participant Email Template Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EVENTID	uniqueidentifier	IN	Event
@CLIENTUSERSID	int	IN	Clientusers
@NAME	nvarchar(255)	IN	Name
@DESCRIPTION	nvarchar(512)	IN	Description
@CONTENTHTML	nvarchar(max)	IN	Contenthtml
@CONTENTTEXT	nvarchar(max)	IN	Contenttext
@SUBJECT	nvarchar(255)	IN	Subject
@CONTACTTYPE	nvarchar(255)	IN	Contacttype
@DELETED	bit	IN	Deleted
@AVAILABLEASLETTER	bit	IN	Available as letter
@FAFTASKID	uniqueidentifier	IN
@LASTDAYSENT	datetime	IN
@ISLETTER	bit	IN

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PARTICIPANT_EMAIL_TEMPLATE
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @EVENTID uniqueidentifier,
    @CLIENTUSERSID int,
    @NAME nvarchar(255),
    @DESCRIPTION nvarchar(512) = '',
    @CONTENTHTML nvarchar(max) = '',
    @CONTENTTEXT nvarchar(max) = '',
    @SUBJECT nvarchar(255) = '',
    @CONTACTTYPE nvarchar(255) = '',
    @DELETED bit = 0,
    @AVAILABLEASLETTER bit = 0,
    @FAFTASKID uniqueidentifier = null,
    @LASTDAYSENT datetime = null,
    @ISLETTER bit = 0
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

DECLARE @TEMPLATECOUNT INT
DECLARE @TEMPLATE_TO_REMOVE uniqueidentifier


SET @TEMPLATECOUNT = ( SELECT COUNT(*) FROM PARTICIPANTEMAILTEMPLATE (NOLOCK)  WHERE FAFTASKID = @FAFTASKID and CLIENTUSERSID = @CLIENTUSERSID and EVENTID = @EVENTID )
IF @TEMPLATECOUNT > 75 -- keeping up to 75 records total
BEGIN
    -- Warnings off because records saved with old create template did not have column LASTDAYSENT
    SET ANSI_WARNINGS OFF
    SET @TEMPLATE_TO_REMOVE = 
    (
        SELECT top 1 id
    FROM PARTICIPANTEMAILTEMPLATE (NOLOCK)
    WHERE FAFTASKID = @FAFTASKID
        and CLIENTUSERSID = @CLIENTUSERSID 
        and EVENTID = @EVENTID
        and DATECHANGED = 
        ( 
        SELECT  MIN (DATECHANGED) 
        FROM PARTICIPANTEMAILTEMPLATE (NOLOCK)
        WHERE FAFTASKID = @FAFTASKID
            and CLIENTUSERSID = @CLIENTUSERSID 
            and EVENTID = @EVENTID
        )
    )
    if @TEMPLATE_TO_REMOVE is not null
    BEGIN
    DELETE PARTICIPANTEMAILTEMPLATE WHERE ID =@TEMPLATE_TO_REMOVE
    END    
END

begin try
    -- handle inserting the data
    insert into dbo.PARTICIPANTEMAILTEMPLATE
        (ID, EVENTID, CLIENTUSERSID, NAME, DESCRIPTION, CONTENTHTML, CONTENTTEXT, SUBJECT, CONTACTTYPE, DELETED, AVAILABLEASLETTER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, FAFTASKID,  ISLETTER)
    values
        (@ID, @EVENTID, @CLIENTUSERSID, @NAME, @DESCRIPTION, @CONTENTHTML, @CONTENTTEXT, @SUBJECT, @CONTACTTYPE, @DELETED, @AVAILABLEASLETTER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @FAFTASKID, @ISLETTER)
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0