USP_FAFEVENTEMAILTEMPLATE_ADDUPDATE

Add or update FAF Event email template.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN
@CLIENTSITEID	int	IN
@CHANGEAGENTID	uniqueidentifier	IN
@EMAILTEMPLATEID	int	IN
@USERID	int	IN
@ClientsID	int	IN
@NAME	nvarchar(184)	IN
@DESCRIPTION	nvarchar(500)	IN
@CONTENTHTML	nvarchar(max)	IN
@SUBJECT	nvarchar(500)	IN
@FROMADDRESS	nvarchar(500)	IN
@FROMNAME	nvarchar(500)	IN
@REPLYADDRESS	nvarchar(500)	IN
@FAFEMAILTYPECODE	tinyint	IN
@ISAVAILABLETOINDIVIDUAL	bit	IN
@ISAVAILABLETOHOUSEHOLD	bit	IN
@ISAVAILABLETOTEAMMEMBER	bit	IN
@ISAVAILABLETOTEAMLEADER	bit	IN
@ISAVAILABLETOCOMPANYLEADER	bit	IN
@ISAVAILABLETOFUNDRAISINGCOACH	bit	IN
@ISAVAILABLETOSPONSOR	bit	IN
@RECIPIENTDONOR	bit	IN
@RECIPIENTPROSPECT	bit	IN
@RECIPIENTINDIVIDUAL	bit	IN
@RECIPIENTHOUSEHOLDMEMBER	bit	IN
@RECIPIENTHOUSEHOLDLEADER	bit	IN
@RECIPIENTTEAMMEMBER	bit	IN
@RECIPIENTTEAMLEADER	bit	IN
@RECIPIENTCOMPANYLEADER	bit	IN
@RECIPIENTSTATUSPREVIOUS	bit	IN
@RECIPIENTSTATUSCURRENT	bit	IN
@AVAILABLEASLETTER	bit	IN
@ISAVAILABLETOHOUSEHOLDMEMBER	bit	IN
@FAFTASKID	uniqueidentifier	IN
@ISDEFAULTFORTASK	bit	IN

Definition

Copy



CREATE procedure dbo.USP_FAFEVENTEMAILTEMPLATE_ADDUPDATE
(
  @ID uniqueidentifier = null,
  @EVENTID uniqueidentifier,
  @CLIENTSITEID int,
  @CHANGEAGENTID uniqueidentifier = null,
  @EMAILTEMPLATEID int = 0, -- leave this zero if want to add new email template

  @USERID int,
  @ClientsID int,
  @NAME nvarchar(184) = null,
  @DESCRIPTION nvarchar(500) = '',
  @CONTENTHTML nvarchar(max),
  @SUBJECT nvarchar(500),
  @FROMADDRESS nvarchar(500) = '',
  @FROMNAME nvarchar(500) = '',
  @REPLYADDRESS nvarchar(500) = '',
  @FAFEMAILTYPECODE tinyint,
  @ISAVAILABLETOINDIVIDUAL bit = 0,
    @ISAVAILABLETOHOUSEHOLD bit = 0,
    @ISAVAILABLETOTEAMMEMBER bit = 0,
    @ISAVAILABLETOTEAMLEADER bit = 0,
    @ISAVAILABLETOCOMPANYLEADER bit = 0,
    @ISAVAILABLETOFUNDRAISINGCOACH bit = 0,
  @ISAVAILABLETOSPONSOR bit = 0,
  @RECIPIENTDONOR bit = 0,
  @RECIPIENTPROSPECT bit = 0,
  @RECIPIENTINDIVIDUAL bit = 0,
  @RECIPIENTHOUSEHOLDMEMBER bit = 0,
  @RECIPIENTHOUSEHOLDLEADER bit = 0,
  @RECIPIENTTEAMMEMBER bit = 0,
  @RECIPIENTTEAMLEADER bit = 0,
  @RECIPIENTCOMPANYLEADER bit = 0,
  @RECIPIENTSTATUSPREVIOUS bit = 0,
  @RECIPIENTSTATUSCURRENT bit = 0,
  @AVAILABLEASLETTER bit = 0,
  @ISAVAILABLETOHOUSEHOLDMEMBER bit = 0,  
  @FAFTASKID uniqueidentifier = null,
  @ISDEFAULTFORTASK bit = 0
)
as

declare @TYPE tinyint,
        @tmpemailtemplateid int,
        @appealid int

set @tmpemailtemplateid = 0
set @TYPE = 0

select @appealid=BBA.ID from dbo.EVENT E 
join dbo.EVENTEXTENSION EX on E.ID = EX.EVENTID and EX.EVENTID = @EVENTID
left join dbo.BBNCAPPEALIDMAP BBA on E.APPEALID = BBA.APPEALID

---added new type 34; other types should have also been added, except 33

if @FAFEMAILTYPECODE >= 20 and @FAFEMAILTYPECODE <= 34 and @FAFEMAILTYPECODE <> 33
  set @TYPE = 13

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

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

begin try

  if @EMAILTEMPLATEID = 0
    begin
      select @EMAILTEMPLATEID=ID from dbo.EMAILTEMPLATE where ClientSitesID = @CLIENTSITEID and Name = @NAME  
      if @EMAILTEMPLATEID > 0
        raiserror('DUPLICATTEMPLATEENAME', 13,1)
    end
  else
    begin
      select @tmpemailtemplateid = ET.ID from EVENTEMAILTEMPLATE EET 
        join dbo.EMAILTEMPLATE ET on EET.EMAILTEMPLATEID = ET.ID 
        where EET.EVENTID = @EVENTID and ClientSitesID = @CLIENTSITEID and Name = @NAME  
      if @tmpemailtemplateid <> 0 and @EMAILTEMPLATEID <> @tmpemailtemplateid
        raiserror('DUPLICATTEMPLATEENAME', 13,1)
    end

  exec spAddUpdate_EmailTemplate @PKID=@EMAILTEMPLATEID output, 
    @UserID    = @USERID,
    @ClientsID = @ClientsID,
    @Name = @NAME,
    @Description = @DESCRIPTION,
    @ContentHTML = @CONTENTHTML,
    @RecipientListsXML = N'<root />',
    @ContentText = N'',
    @FromAddress = @FROMADDRESS,
    @FromDisplayName = @FROMNAME,
    @Subject = @SUBJECT,
    @Priority = 3,
    @ReplyAddress = @REPLYADDRESS,
    @ReturnReceipt = 0,
    @ReturnReceiptAddress = N'',
    @ForwardDSN = 0,
    @ForwardDSNAddress = N'',
    @GUID = @ID,
    @Type = @TYPE,
    @DataSourceID = 0,
    @ClientSitesID = @CLIENTSITEID,
    @AppealID=@appealid;

  exec USP_EVENTEMAILTEMPLATE_ADD_EDIT @ID = @ID output, @CHANGEAGENTID=@CHANGEAGENTID, @EVENTID=@EVENTID, @EMAILTEMPLATEID = @EMAILTEMPLATEID, @CONFIRMATIONTYPECODE = @FAFEMAILTYPECODE, 
      @ISAVAILABLETOINDIVIDUAL = @ISAVAILABLETOINDIVIDUAL, @ISAVAILABLETOHOUSEHOLD = @ISAVAILABLETOHOUSEHOLD, @ISAVAILABLETOTEAMMEMBER = @ISAVAILABLETOTEAMMEMBER,
      @ISAVAILABLETOTEAMLEADER =  @ISAVAILABLETOTEAMLEADER, @ISAVAILABLETOCOMPANYLEADER = @ISAVAILABLETOCOMPANYLEADER, 
      @ISAVAILABLETOFUNDRAISINGCOACH = @ISAVAILABLETOFUNDRAISINGCOACH, @ISAVAILABLETOSPONSOR = @ISAVAILABLETOSPONSOR,

      @RECIPIENTDONOR =  @RECIPIENTDONOR, @RECIPIENTPROSPECT = @RECIPIENTPROSPECT, @RECIPIENTINDIVIDUAL = @RECIPIENTINDIVIDUAL,
      @RECIPIENTHOUSEHOLDMEMBER = @RECIPIENTHOUSEHOLDMEMBER, @RECIPIENTHOUSEHOLDLEADER = @RECIPIENTHOUSEHOLDLEADER, @RECIPIENTTEAMMEMBER = @RECIPIENTTEAMMEMBER, 
      @RECIPIENTTEAMLEADER = @RECIPIENTTEAMLEADER, @RECIPIENTCOMPANYLEADER = @RECIPIENTCOMPANYLEADER, @RECIPIENTSTATUSPREVIOUS = @RECIPIENTSTATUSPREVIOUS,    
      @RECIPIENTSTATUSCURRENT = @RECIPIENTSTATUSCURRENT, @AVAILABLEASLETTER = @AVAILABLEASLETTER, @ISAVAILABLETOHOUSEHOLDMEMBER = @ISAVAILABLETOHOUSEHOLDMEMBER,
      @FAFTASKID = @FAFTASKID, @ISDEFAULTFORTASK = @ISDEFAULTFORTASK;

select @ISDEFAULTFORTASK
select @EMAILTEMPLATEID

  --if @ISDEFAULTFORTASK=1, then make sure no other templates for that event and task have ISDEFAULTFORTASK=1

  if @ISDEFAULTFORTASK = 1
    begin
      UPDATE EVENTEMAILTEMPLATE
      SET ISDEFAULTFORTASK=0
      WHERE EVENTID=@EVENTID AND FAFTASKID=@FAFTASKID AND EMAILTEMPLATEID <> @EMAILTEMPLATEID
    end

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0