spAddUpdate_Email

Parameters

Parameter	Parameter Type	Mode
@PKID	int	INOUT
@EmailTemplateID	int	IN
@Name	nvarchar(255)	IN
@Description	nvarchar(255)	IN
@UserID	int	IN
@Guid	uniqueidentifier	INOUT
@type	tinyint	IN
@status	tinyint	IN
@ContentHTML	ntext	IN
@ContentText	ntext	IN
@FromAddress	nvarchar(255)	IN
@FromDisplayName	nvarchar(255)	IN
@Subject	nvarchar(4000)	IN
@Priority	int	IN
@ReplyAddress	nvarchar(255)	IN
@ReturnReceipt	bit	IN
@ReturnReceiptAddress	nvarchar(255)	IN
@ForwardDSN	bit	IN
@ForwardDSNAddress	nvarchar(255)	IN
@SendAfterDate	datetime	IN
@RecipientListsDoc	nvarchar(4000)	IN
@RecipientsXML	ntext	IN
@ProjectAppealListEmailID	int	IN
@CLIENTSITESID	int	IN
@AppealID	int	IN
@EmailAddressTypeID	uniqueidentifier	IN
@IsExcludeInactiveRecipient	bit	IN
@InactiveRecipientFromMonths	int	IN
@AlternateSubject	nvarchar(255)	IN
Definition

Copy


CREATE PROCEDURE [dbo].[spAddUpdate_Email]
(
    @PKID int output,
    @EmailTemplateID int,
    @Name nvarchar(255),
    @Description nvarchar(255),
    @UserID    int,        
    @Guid uniqueidentifier output,
    @type tinyint,
    @status tinyint,

    @ContentHTML ntext,
    @ContentText ntext,
    @FromAddress nvarchar(255),
    @FromDisplayName nvarchar(255),
    @Subject nvarchar(4000),
    @Priority int,
    @ReplyAddress nvarchar(255),
    @ReturnReceipt bit,
    @ReturnReceiptAddress nvarchar(255)
    ,    
    @ForwardDSN bit,
    @ForwardDSNAddress nvarchar(255),
    @SendAfterDate datetime,
    @RecipientListsDoc as nvarchar(4000),
    @RecipientsXML as ntext,
    @ProjectAppealListEmailID int,
    @CLIENTSITESID int = null,
    @AppealID int = null,
    @EmailAddressTypeID uniqueidentifier = null,
    @IsExcludeInactiveRecipient bit = 0,
    @InactiveRecipientFromMonths int = 0,
         @AlternateSubject nvarchar(255) = null
)
AS
BEGIN

    set nocount on

    BEGIN TRANSACTION

    IF (@PKID<=0) 
    BEGIN
        if @SendAfterDate < getutcdate()
            set @SendAfterDate = getutcdate()

        INSERT INTO dbo.Email
        (

            EmailTemplateID,

            [Name],
            [Description],
            OwnerID,
            [Type],
            Status,
            ContentHTML,
            ContentText,
            FromAddress,
            FromDisplayName,
            Subject,
            Priority,
                  ReplyAddress,
            ReturnReceipt,
            ReturnReceiptAddress,
            ForwardDSN,
            ForwardDSNAddress,
            SendAfterDate,
            ProjectAppealListEmailID,
            CLIENTSITESID,
            AppealID,
                  EmailAddressTypeID,
            IsExcludeInactiveRecipient,
            InactiveRecipientFromMonths,
                        AlternateSubject
         )
        VALUES
        (
            @EmailTemplateID,
            @Name,
            @Description,
            @UserID,
            @Type,
            @Status,
            @ContentHTML,
            @ContentText,
            @FromAddress,
            @FromDisplayName,
            @Subject,
            @Priority,
            @ReplyAddress,
            @ReturnReceipt,
            @ReturnReceiptAddress,
            @ForwardDSN,
            @ForwardDSNAddress,
            @SendAfterDate,
            @ProjectAppealListEmailID,
            @CLIENTSITESID,
            @AppealID,
            @EmailAddressTypeID,
            @IsExcludeInactiveRecipient,
            @InactiveRecipientFromMonths,
                         @AlternateSubject
        )
        SELECT @PKID = @@Identity

        SELECT @Guid = Guid FROM dbo.[Email] WHERE [ID] = @PKID

        EXEC spAuditThis @UserID, 1, @Guid, 15

END 
    ELSE 
    BEGIN

        declare @CREATEDATE datetime
        select @CREATEDATE = CREATEDATE from dbo.EMAIL where ID = @PKID

        if @CREATEDATE is not null and @SendAfterDate is not null and @CREATEDATE > @SendAfterDate
              set @SendAfterDate = @CREATEDATE



        UPDATE dbo.Email SET
            EmailTemplateID=@EmailTemplateID,
            [Name]=@Name,
            [Description]=@Description,
            [Type]=@Type,
            [Status]=@Status,
            ContentHTML=@ContentHTML,


            ContentText=@ContentText, 
            FromAddress=@FromAddress, 
            FromDisplayName=@FromDisplayName ,
            Subject=@Subject ,
            Priority=@Priority ,
            ReplyAddress=@ReplyAddress ,
            ReturnReceipt=@ReturnReceipt ,
            ReturnReceiptAddress=@ReturnReceiptAddress ,
            ForwardDSN=@ForwardDSN ,
            ForwardDSNAddress=@ForwardDSNAddress ,
            SendAfterDate=@SendAfterDate,
            ProjectAppealListEmailID=@ProjectAppealListEmailID,
            CLIENTSITESID = @CLIENTSITESID,
            AppealID = @AppealID,
            EmailAddressTypeID=@EmailAddressTypeID,
            IsExcludeInactiveRecipient=@IsExcludeInactiveRecipient,
            InactiveRecipientFromMonths=@InactiveRecipientFromMonths,
                        AlternateSubject= @AlternateSubject
        WHERE [ID]=@PKID

        SELECT @Guid = Guid FROM dbo.[Email] WHERE [ID] = @PKID
        EXEC spAuditThis @UserID,
        2, @Guid, 15

        /* Whack old ones */

       DELETE 
       FROM dbo.Email_Recipient
       WHERE EmailID=@PKID  
    END
    /*
    Update RecipientLists Table
    */

        DECLARE @irecipientlistsdoc int
        EXEC sp_xml_preparedocument @irecipientlistsdoc OUTPUT, @RecipientListsDoc



        DELETE FROM dbo.Email_EmailList WHERE EmailID = @PKID 

        INSERT dbo.Email_EmailList (EmailID, EmailListID, IsTest) 
        SELECT @PKID, EmailListID, IsNULL(IsTest,0)
        FROM OPENXML (@irecipientlistsdoc, '/root/RecipientLists',1) 


        WITH (EmailListID int, IsTest bit)

        EXEC sp_xml_removedocument @irecipientlistsdoc

    /* 
    Update Recipients Table
    */
    declare @InactiveRecipients as table (ID  int, Emailaddress nvarchar(250))

--get inactive recipients list only if thix xml have recipients.

    if(@IsExcludeInactiveRecipient =1 and len(cast(@RecipientsXML as nvarchar(max))) >0)
    begin
      insert into @InactiveRecipients
      select 1, EMAILADDRESS from dbo.EMAIL_INACTIVERECIPIENT EIR where EIR.INACTIVEFROMMONTHS >= @InactiveRecipientFromMonths
    end    

        DECLARE @iRecipsDoc int

        EXEC sp_xml_preparedocument @iRecipsDoc OUTPUT, @RecipientsXML

        INSERT INTO dbo.Email_Recipient
        (
            [EmailID],
            [BackOfficeSystemPeopleID],
            [UserID],
            [AddressBookID],
            [DisplayName],
            [EmailAddress],
            [DataSourceID],
                  [INACTIVE]
        ) 
        SELECT
        @PKID, BackOfficeSystemPeopleID, UserID, AddressBookID, DisplayName, X.EmailAddress, DataSourceID, isnull(IR.ID,0)
        FROM OPENXML (@iRecipsDoc, '/ArrayOfEmailRecipient/EmailRecipient/ObjectData',3) 
            WITH(
                EmailID int,


       BackOfficeSystemPeopleID int,
                UserID int, 
                AddressBookID int,
                DisplayName nvarchar(255),
                EmailAddress nvarchar(255),
                DataSourceID int '../DataSourceID'
            ) as X
            left join @InactiveRecipients as IR on IR.Emailaddress =X.EmailAddress


    EXEC sp_xml_removedocument @iRecipsDoc

    COMMIT TRANSACTION
END