USP_BULKUPDATEEMAILJOBRECIPIENTS

CREATE procedure dbo.USP_BULKUPDATEEMAILJOBRECIPIENTS
(
    @EMAILJOBDATAID uniqueidentifier = null output,
    @EMAILID integer,
    @EMAILJOBID integer,
    @DATA xml = null
)
as

--Reset the EMAILJOBDATAID coming in from code.

set @EMAILJOBDATAID = newid()

begin try
    set nocount on
                -- handle inserting the data

          insert into dbo.EMAILJOBDATA
                    (ID, EMAILJOBID, DATA, SENT)
                values
                    (@EMAILJOBDATAID, @EMAILJOBID, @DATA, 0)

                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) 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

                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


                INSERT INTO Email_Recipient(
                    EmailID, 
                    UserID, 
                    AddressBookID, 
                    DisplayName, 
                    EmailAddress, 
                    OptOut, 
                    InvalidAddress, 
                    BackOfficeSystemPeopleID,
                    MERGEID,
          EMAILJOBDATAID)
                SELECT 
                    @EmailID, 
                    USERID, 
                    ABID, 
                    MailDisplay, 
                    MailAddress, 
                    OptOut, 
                    InvalidAddress, 
                    p.[id],
                    x1.MERGEID,
          @EMAILJOBDATAID
                FROM @XMLData x1 
                left outer join dbo.BackOfficeSystemPeople p
                on x1.bosysid = p.BackOfficeSystemID and
                x1.borecid = p.BackofficeRecordID

                --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

                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

        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