USP_BULKUPDATEEXISTINGEMAILRECIPIENTS

CREATE   PROCEDURE [dbo].[USP_BULKUPDATEEXISTINGEMAILRECIPIENTS]
            (
        @EMAILJOBDATAID uniqueidentifier = null output,
                @EmailID as int,
                @EmailJobID as int,
                @DATA as xml
            )

            AS

    begin try
        set nocount on

        if @EMAILJOBDATAID is null
        begin
          set @EMAILJOBDATAID = newid()
            -- handle inserting the data

            insert into dbo.EMAILJOBDATA
                    (ID, EMAILJOBID, DATA, SENT)
                values
                    (@EMAILJOBDATAID, @EMAILJOBID, @DATA, 0)
        end
        else
        begin
           update dbo.EMAILJOBDATA 
           set EMAILJOBID = @EMAILJOBID,
               DATA = @DATA
           where ID = @EMAILJOBDATAID
        end

        DECLARE @EmailJob_RecipientCount int
        DECLARE @Email_JobCount int
        DECLARE @idoc int
        DECLARE @XMLData table(id uniqueidentifier default(newid()) primary key, USERID int, ABID int, MailDisplay nvarchar(512) COLLATE database_default, MailAddress nvarchar(512) COLLATE database_default, OptOut bit, InvalidAddress bit, PEOPLEID int, BORECID int, BOSYSID int, MERGEID uniqueidentifier, 
            unique(BOSYSID, BORECID, maildisplay, mailaddress, USERID, ABID, OptOut, InvalidAddress, id), unique(UserID, maildisplay, mailaddress, id), unique(ABID, maildisplay, mailaddress, id))


        --Load Up the XML into a temporary table

        EXEC sp_xml_preparedocument @idoc OUTPUT, @DATA
        INSERT INTO @XMLData(USERID, ABID, MailDisplay, MailAddress, OptOut, InvalidAddress, PEOPLEID, BORECID, BOSYSID, MERGEID)
        SELECT 
            U, 
            AB, 
            N, 
            E, 
            O, 
            X,
            BP,
            BR,
            BS,
      M
        FROM OPENXML (@idoc, '/Recipients/R', 2) 
        WITH (
            U int,
            AB int, 
            N nvarchar(512), 
            E nvarchar(512),
            O bit,
            X bit,
            BP int,
            BR int,
            BS int,
      M uniqueidentifier
        )
        EXEC sp_xml_removedocument @idoc

        SELECT @Email_JobCount = COUNT(*)
        FROM EmailJob
        WHERE [EmailID] = @EmailID
        AND [ID] <> @EmailJobID

        SELECT @EmailJob_RecipientCount = COUNT(*)
        FROM EmailJob_Recipient
        WHERE [EmailJobID] = @EmailJobID AND EMAILJOBDATAID = @EMAILJOBDATAID

    if exists(select NULL from EMAIL where ID = @EMAILID and TYPE in (12,14))
      begin
        -- 12 & 14 are ECard and PageSharing emails

        -- we have a recipients list saved already

        -- we need to add mergeid and emailjobdataid 

        -- these recipients have nothing but an email address to track them by

        -- so we'll find them via address and update accordingly

            UPDATE Email_Recipient
            SET 
                MERGEID = x1.MERGEID,
                EMAILJOBDATAID = @EMAILJOBDATAID
            FROM @XMLData x1 
            inner join Email_Recipient er on 
        er.EMAILADDRESS = x1.MAILADDRESS and er.EMAILID = @EmailID 

      end
    else
      begin
        INSERT INTO dbo.BackOfficeSystemPeople 
                (BackOfficeSystemID, BackofficeRecordID)
            SELECT BOSYSID, BORECID 
            FROM @XMLData x1 
            WHERE NOT EXISTS (
                SELECT *
                FROM dbo.BackOfficeSystemPeople p
                WHERE x1.bosysid = p.BackOfficeSystemID
                AND x1.borecid = p.BackofficeRecordID
            )
            AND BORECID > 0

            -- we have a recipients list saved already

            --We are going to make sure the Email Address/DisplayName are current...

            -- find those needing update via BOID

            UPDATE Email_Recipient
            SET 
                DisplayName = x1.MailDisplay,
                EmailAddress = x1.MailAddress,
                  MERGEID = x1.MERGEID,
                  EMAILJOBDATAID = @EMAILJOBDATAID
            FROM @XMLData x1 
            INNER JOIN BackOfficeSystemPeople p
            ON p.BackOfficeSystemID = x1.BOSYSID
            AND p.BackOfficeRecordID = x1.BORECID
            INNER JOIN Email_Recipient er on er.emailid = @EmailID
            AND er.BackOfficeSystemPeopleID = p.[id] 

            -- find those needing update via UserID

            UPDATE Email_Recipient
                SET 
                DisplayName = x1.MailDisplay,
                EmailAddress = x1.MailAddress,
                BackOfficeSystemPeopleID = p.[ID],
          MERGEID = x1.MERGEID,
          EMAILJOBDATAID = @EMAILJOBDATAID
            FROM @XMLData x1                
            INNER JOIN BackOfficeSystemPeople p
            ON p.BackOfficeSystemID = x1.BOSYSID
            AND p.BackOfficeRecordID = x1.BORECID
            INNER JOIN Email_Recipient er on er.emailid = @EmailID 
            AND er.userid = x1.userid
            AND x1.userid > 0 

            -- find those needing update via AddressBookID

            -- Address book contacts never get a backofficesystempeople record so no need to join to that table.

            UPDATE Email_Recipient
                SET 
                DisplayName = x1.MailDisplay,
                EmailAddress = x1.MailAddress,
                MERGEID = x1.MERGEID,
                EMAILJOBDATAID = @EMAILJOBDATAID
            FROM @XMLData x1
            INNER JOIN Email_Recipient er on er.emailid = @EmailID 
            AND er.AddressBookID = x1.ABID
            AND x1.ABID > 0 
      end

        --Mark all records that have Invalid Accounts so we do not send to them...

        UPDATE Email_Recipient
        SET InvalidAccount = 1
        FROM Email_Recipient er
        INNER JOIN EmailInvalidAccount eia on eia.emailaddress = er.emailaddress and eia.active = 1
        AND er.EmailID = @EmailID AND er.EMAILJOBDATAID = @EMAILJOBDATAID

        --We will only populate the EmailJob_Recipient table if it doesn't have any rows

        --Otherwise, the job inadvertently failed and we are trying to resend

        if @EmailJob_RecipientCount = 0
        BEGIN
            if @Email_JobCount = 0
            begin
                --This is a first time send


                INSERT INTO EmailJob_Recipient(
                    EmailID, 
                    EmailJobID, 
                    UserID, 
                    EmailAddress, 
                    MessageDate, 
                    UpdateDate, 
                    AddressBookID, 
                    EmailRecipientID,
                    BackOfficeSystemPeopleID,
          MERGEID,
          EMAILJOBDATAID)
                SELECT 
                    @EmailID, 
                    @EmailJobID,
                    UserID, 
                    EmailAddress, 
                    getutcdate(), 
                    getutcdate(),
                    AddressBookID, 
                    [ID],
                    BackOfficeSystemPeopleID,
          MERGEID,
          @EMAILJOBDATAID
                FROM Email_Recipient
                WHERE EmailID = @EmailID
                AND OptOut = 0
                AND InvalidAddress = 0
                AND InvalidAccount = 0
                AND Email_Recipient.EMAILJOBDATAID = @EMAILJOBDATAID
            end
            else
            begin
                --This is a resend of previously failed message

                INSERT INTO EmailJob_Recipient(
                    EmailID, 
                    EmailJobID, 
                    UserID, 
                    EmailAddress, 
                    MessageDate, 
                    UpdateDate, 
                    AddressBookID, 
                    EmailRecipientID,
                    BackOfficeSystemPeopleID,
          MERGEID,
          EMAILJOBDATAID)
                SELECT 
                    @EmailID, 
                    @EmailJobID,
                    r.UserID, 
                    r.EmailAddress, 
                    getutcdate(), 
                    getutcdate(),
                    r.AddressBookID, 
                    r.[ID],
                    r.BackOfficeSystemPeopleID,
          r.MERGEID,
          @EMAILJOBDATAID
                FROM Email_Recipient r
                INNER JOIN EmailJob_Recipient ejr on ejr.emailrecipientid = r.id and ejr.dsned = 1
                WHERE r.EmailID = @EmailID
                AND ejr.[Current] = 1
                AND OptOut = 0
                AND InvalidAddress = 0
                AND InvalidAccount = 0
                AND r.EMAILJOBDATAID = @EMAILJOBDATAID

                UPDATE EmailJob_Recipient
                SET [Current] = 0
                FROM EmailJob_Recipient ejr
                INNER JOIN Email_Recipient r on r.id = ejr.emailrecipientid and ejr.dsned = 1
                WHERE r.EmailID = @EmailID
                AND ejr.[Current] = 1
    end
   END

      select ID, MERGEID
        from dbo.EMAILJOB_RECIPIENT 
      where EMAILJOBDATAID = @EMAILJOBDATAID

    end try


    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0