spBulkUpdate_Email_Recipient

CREATE     PROCEDURE [dbo].[spBulkUpdate_Email_Recipient]
            (
                @EmailID as int,
                @EmailJobID as int,
                @XML as ntext
            )

            AS
            BEGIN
                set nocount on
                DECLARE @Email_RecipientCount int
                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), MailAddress nvarchar(512), OptOut bit, InvalidAddress bit, PEOPLEID int, BORECID int, BOSYSID int, 
                    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, @XML
                INSERT INTO @XMLData(USERID, ABID, MailDisplay, MailAddress, OptOut, InvalidAddress, PEOPLEID, BORECID, BOSYSID)
                SELECT 
                    U, 
                    AB, 
                    N, 
                    E, 
                    O, 
                    X,
                    BP,
                    BR,
                    BS
                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

                )
                EXEC sp_xml_removedocument @idoc

                SELECT @Email_RecipientCount = COUNT(*)
                FROM Email_Recipient
                WHERE [EmailID] = @EmailID

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

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

                -- First Make sure that all of our recipients exist in the 
                -- Backoffice people table - w/out dupes

                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

                -- If the recipient count > 0 then we have already populated the recipient table
                -- This happens for Fundraiser Emails 
                -- Or if we are attempting to resend a job
                if @Email_RecipientCount = 0
                BEGIN

                    -- No recipients yet - add them all

                    INSERT INTO Email_Recipient(
                        EmailID, 
                        UserID, 
                        AddressBookID, 
                        DisplayName, 
                        EmailAddress, 
                        OptOut, 
                        InvalidAddress, 
                        BackOfficeSystemPeopleID)
                    SELECT 
                        @EmailID, 
                        USERID, 
                        ABID, 
                        MailDisplay, 
                        MailAddress, 
                        OptOut, 
                        InvalidAddress, 
                        p.[id]
                    FROM @XMLData x1 
                    left outer join dbo.BackOfficeSystemPeople p
                    on x1.bosysid = p.BackOfficeSystemID and
                    x1.borecid = p.BackofficeRecordID
                END
                else
                BEGIN
                    -- 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
                    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]
                    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
                    UPDATE Email_Recipient
                        SET 
                        DisplayName = x1.MailDisplay,
                        EmailAddress = x1.MailAddress,
                        BackOfficeSystemPeopleID = p.[ID]
                    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.AddressBookID = x1.ABID
                    AND x1.ABID > 0

                    --if @Email_JobCount = 0
                    --BEGIN
                    --    --Email send has never been attempted 
                    --    --This is an emailjob where the Email_Recipient was populated directly...
                    --END
                    --ELSE
                    --BEGIN
                    --    --We have existing recips and a resend job
                    --END
                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


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

                        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 
                    r.[id], 
                    UserID, 
                    AddressbookID, 
                    EmailAddress, 
                    BackOfficeSystemPeopleID,
                    p.BackOfficeRecordID BORECID,
                    p.BackOfficeSystemID BOSYSID
                FROM EmailJob_Recipient r
                LEFT OUTER JOIN BackOfficeSystemPeople p
                ON p.[id] = BackOfficeSystemPeopleID
                WHERE EmailJobID = @EmailJobID
            END