USP_EVENTEMAILTEMPLATE_ADD_EDIT

Add/edit the confirmation email template for a given event

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN
@EMAILTEMPLATEID	int	IN
@CONFIRMATIONTYPECODE	tinyint	IN
@ISAVAILABLETOINDIVIDUAL	bit	IN
@ISAVAILABLETOHOUSEHOLD	bit	IN
@ISAVAILABLETOTEAMMEMBER	bit	IN
@ISAVAILABLETOTEAMLEADER	bit	IN
@ISAVAILABLETOCOMPANYLEADER	bit	IN
@ISAVAILABLETOFUNDRAISINGCOACH	bit	IN
@ISAVAILABLETOSPONSOR	bit	IN
@ISACTIVE	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
@NCNOTIFICATIONID	int	IN
@AVAILABLEASLETTER	bit	IN
@ISAVAILABLETOHOUSEHOLDMEMBER	bit	IN
@FAFTASKID	uniqueidentifier	IN
@ISDEFAULTFORTASK	bit	IN

Definition

Copy



CREATE procedure dbo.USP_EVENTEMAILTEMPLATE_ADD_EDIT
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @EVENTID uniqueidentifier = null,
  @EMAILTEMPLATEID int = null,
  @CONFIRMATIONTYPECODE tinyint = null,
  @ISAVAILABLETOINDIVIDUAL bit = 1,
    @ISAVAILABLETOHOUSEHOLD bit = 1,
    @ISAVAILABLETOTEAMMEMBER bit = 1,
    @ISAVAILABLETOTEAMLEADER bit = 1,
    @ISAVAILABLETOCOMPANYLEADER bit = 1,
    @ISAVAILABLETOFUNDRAISINGCOACH bit = 1,
  @ISAVAILABLETOSPONSOR bit = 1,  
  @ISACTIVE bit = 1,

  @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,
  @NCNOTIFICATIONID int = null,
  @AVAILABLEASLETTER bit = 0,
  @ISAVAILABLETOHOUSEHOLDMEMBER bit = 1, 
  @FAFTASKID uniqueidentifier = null,
  @ISDEFAULTFORTASK 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()    

    begin try

    IF NOT EXISTS (SELECT * FROM dbo.EVENTEMAILTEMPLATE (NOLOCK) WHERE ID= @ID)    
      INSERT INTO dbo.EVENTEMAILTEMPLATE    
        ( ID, EVENTID, EMAILTEMPLATEID, CONFIRMATIONTYPECODE, ISAVAILABLETOINDIVIDUAL, ISAVAILABLETOHOUSEHOLD, ISAVAILABLETOTEAMMEMBER, ISAVAILABLETOTEAMLEADER, 
        ISAVAILABLETOCOMPANYLEADER,ISAVAILABLETOFUNDRAISINGCOACH,ISAVAILABLETOSPONSOR, ISACTIVE,
        RECIPIENTDONOR, RECIPIENTPROSPECT, RECIPIENTINDIVIDUAL, RECIPIENTHOUSEHOLDMEMBER, RECIPIENTHOUSEHOLDLEADER, RECIPIENTTEAMMEMBER, 
        RECIPIENTTEAMLEADER, RECIPIENTCOMPANYLEADER, RECIPIENTSTATUSPREVIOUS, RECIPIENTSTATUSCURRENT, NCNOTIFICATIONID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, AVAILABLEASLETTER, ISAVAILABLETOHOUSEHOLDMEMBER, FAFTASKID, ISDEFAULTFORTASK)    
      VALUES    
        ( @ID, @EVENTID, @EMAILTEMPLATEID, @CONFIRMATIONTYPECODE, @ISAVAILABLETOINDIVIDUAL, @ISAVAILABLETOHOUSEHOLD, @ISAVAILABLETOTEAMMEMBER, @ISAVAILABLETOTEAMLEADER, 
        @ISAVAILABLETOCOMPANYLEADER,@ISAVAILABLETOFUNDRAISINGCOACH,@ISAVAILABLETOSPONSOR, @ISACTIVE, 
        @RECIPIENTDONOR, @RECIPIENTPROSPECT,@RECIPIENTINDIVIDUAL, @RECIPIENTHOUSEHOLDMEMBER, @RECIPIENTHOUSEHOLDLEADER, @RECIPIENTTEAMMEMBER, 
        @RECIPIENTTEAMLEADER, @RECIPIENTCOMPANYLEADER, @RECIPIENTSTATUSPREVIOUS, @RECIPIENTSTATUSCURRENT, @NCNOTIFICATIONID,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @AVAILABLEASLETTER, @ISAVAILABLETOHOUSEHOLDMEMBER, @FAFTASKID, @ISDEFAULTFORTASK) 
    ELSE
      UPDATE dbo.EVENTEMAILTEMPLATE
    SET 
          EVENTID = isnull(@EVENTID, EVENTID),
          EMAILTEMPLATEID = isnull(@EMAILTEMPLATEID,EMAILTEMPLATEID),
          CONFIRMATIONTYPECODE = isnull(@CONFIRMATIONTYPECODE,CONFIRMATIONTYPECODE),
      ISAVAILABLETOINDIVIDUAL = isnull(@ISAVAILABLETOINDIVIDUAL,ISAVAILABLETOINDIVIDUAL), 
      ISAVAILABLETOHOUSEHOLD = isnull(@ISAVAILABLETOHOUSEHOLD, ISAVAILABLETOHOUSEHOLD),
      ISAVAILABLETOTEAMMEMBER = isnull(@ISAVAILABLETOTEAMMEMBER, ISAVAILABLETOTEAMMEMBER),
      ISAVAILABLETOTEAMLEADER = isnull(@ISAVAILABLETOTEAMLEADER, ISAVAILABLETOTEAMLEADER),
      ISAVAILABLETOCOMPANYLEADER = isnull(@ISAVAILABLETOCOMPANYLEADER, ISAVAILABLETOCOMPANYLEADER),
      ISAVAILABLETOFUNDRAISINGCOACH = isnull(@ISAVAILABLETOFUNDRAISINGCOACH, ISAVAILABLETOFUNDRAISINGCOACH),
      ISAVAILABLETOSPONSOR = isnull(@ISAVAILABLETOSPONSOR, ISAVAILABLETOSPONSOR),
      ISACTIVE = @ISACTIVE,
      RECIPIENTDONOR =            isnull(@RECIPIENTDONOR,RECIPIENTDONOR), 
      RECIPIENTPROSPECT =         isnull(@RECIPIENTPROSPECT, RECIPIENTPROSPECT),
      RECIPIENTINDIVIDUAL =       isnull(@RECIPIENTINDIVIDUAL, RECIPIENTINDIVIDUAL),
      RECIPIENTHOUSEHOLDMEMBER =  isnull(@RECIPIENTHOUSEHOLDMEMBER, RECIPIENTHOUSEHOLDMEMBER),
      RECIPIENTHOUSEHOLDLEADER =  isnull(@RECIPIENTHOUSEHOLDLEADER, RECIPIENTHOUSEHOLDLEADER),
      RECIPIENTTEAMMEMBER =       isnull(@RECIPIENTTEAMMEMBER, RECIPIENTTEAMMEMBER),
      RECIPIENTTEAMLEADER =       isnull(@RECIPIENTTEAMLEADER, RECIPIENTTEAMLEADER),
      RECIPIENTCOMPANYLEADER =    isnull(@RECIPIENTCOMPANYLEADER, RECIPIENTCOMPANYLEADER),
      RECIPIENTSTATUSPREVIOUS =   isnull(@RECIPIENTSTATUSPREVIOUS, RECIPIENTSTATUSPREVIOUS),
      RECIPIENTSTATUSCURRENT =    isnull(@RECIPIENTSTATUSCURRENT, RECIPIENTSTATUSCURRENT),
      NCNOTIFICATIONID = isnull(@NCNOTIFICATIONID, NCNOTIFICATIONID),
      AVAILABLEASLETTER =         isnull(@AVAILABLEASLETTER, AVAILABLEASLETTER),
      ISAVAILABLETOHOUSEHOLDMEMBER = isnull(@ISAVAILABLETOHOUSEHOLDMEMBER, ISAVAILABLETOHOUSEHOLDMEMBER),
      FAFTASKID = isnull(@FAFTASKID, FAFTASKID),
      ISDEFAULTFORTASK = isnull(@ISDEFAULTFORTASK, ISDEFAULTFORTASK)
      WHERE ID= @ID

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

    if @ISDEFAULTFORTASK = 1
      begin
        if @ID is not null
          UPDATE EVENTEMAILTEMPLATE
          SET ISDEFAULTFORTASK = 0
          WHERE EVENTID = (SELECT EVENTID FROM dbo.EVENTEMAILTEMPLATE WHERE ID= @ID) 
                AND FAFTASKID = @FAFTASKID 
                AND EMAILTEMPLATEID <> (SELECT EMAILTEMPLATEID FROM dbo.EVENTEMAILTEMPLATE WHERE ID= @ID)
        else
          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;