USP_EMAIL_UPDATEEXTERNALSTATUS

            CREATE procedure dbo.USP_EMAIL_UPDATEEXTERNALSTATUS
            (
                @STATUSDATA nvarchar(max)
            )
            as
            begin

                declare @STATUSTABLE table
                (
                    EXTERNALJOBID uniqueidentifier,
                    STATUS integer,
                    DATEMODIFIED datetime,
                    ACTUALSENT integer,
                    REQUESTEDSENT integer,
                    EXTERNALSERVICESTATUS integer
                );

         declare @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA table 
         (
            DATEPROCESSED datetime,
            BOUNCEMESSAGE nvarchar(max),
            EMAILADDRESS nvarchar(max),
            HARDBOUNCE integer,
            SOFTBOUNCE integer,
            BOUNCECODE nvarchar(10),
            BOUNCECODEDESCRIPTION nvarchar(100),
            SPAMCOMPLAINT integer
         );

                declare @INVALIDACCOUNTSTEMP table
                (
                    EMAILADDRESS nvarchar(255),
                    BOUNCEMESSAGE nvarchar(max),
                    DATEPROCESSED datetime,
                    CATEGORY integer
                );

                declare @INVALIDACCOUNTS table
                (
                    EMAILADDRESS nvarchar(255),
                    BOUNCEMESSAGE nvarchar(max),
                    DATEPROCESSED datetime,
                    CATEGORY integer
                );

                declare @IDOC int;
                declare @ISSHAREDSERVICESDATA bit=0;

                exec sp_xml_preparedocument @IDOC output, @STATUSDATA;

                insert into 
                    @STATUSTABLE (EXTERNALJOBID, STATUS, DATEMODIFIED, ACTUALSENT, REQUESTEDSENT, EXTERNALSERVICESTATUS)
                select 
                    ExternalJobIdentifier, 
                    dbo.UFN_GET_CONVERTEDEXTERNALSERVICESTATUS(JobStatus), 
                    convert(datetime,
                        case 
                            when (substring(DateModified,23,1)='-') then left(DateModified,22)+'0' 
                            when (substring(DateModified,22,1)='-') then left(DateModified,21)+'00' 
                            else left(DateModified,23) end,127), 
                    ActualSent, 
                    RequestedSent, 
                    JobStatus 
                from 
                    openxml(@IDOC, '/Emails/ArrayOfEmailJobStatusData/EmailJobStatusData', 2)
                    with
                    (
                        ExternalJobIdentifier uniqueidentifier,
                        JobStatus integer,
                        DateModified char(33),
                        ActualSent integer,
                        RequestedSent integer
                    );

                insert into @INVALIDACCOUNTSTEMP (EMAILADDRESS, BOUNCEMESSAGE, DATEPROCESSED, CATEGORY)
                select 
                    EmailAddress, 
                    ResponseMessage, 
                    convert(datetime, left(DateProcessed,23),127), 
                    ResponseCategory
                from 
                    openxml(@IDOC, '/Emails/ArrayOfEmailRecipientStatusData/EmailRecipientStatusData', 2) 
                    with
                    (
                        EmailAddress nvarchar(255),
                        ResponseMessage nvarchar(max),
                        DateProcessed nvarchar(66),
                        ResponseCategory integer
                    );


             insert into @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA
                  (DATEPROCESSED,BOUNCEMESSAGE,EMAILADDRESS,HARDBOUNCE,SOFTBOUNCE,BOUNCECODE,BOUNCECODEDESCRIPTION,SPAMCOMPLAINT)

                  select     
                  convert(datetime, left(DATEPROCESSED,23),127),
                  BOUNCEMESSAGE,
                  EMAILADDRESS,
                  HARDBOUNCE,
                  SOFTBOUNCE,
                  BOUNCECODE, 
                  BOUNCECODEDESCRIPTION,
                  SPAMCOMPLAINT
                  from 
                openxml(@IDOC, '/Emails/ArrayOfUnsentEmailRecipientStatusData/UnsentEmailRecipientStatusData', 2)
                with
                (
                    DateProcessed nvarchar(30),
                    BounceMessage nvarchar(max),
                    EmailAddress nvarchar(max),
                    HardBounce integer,
                    SoftBounce integer,
                    BounceCode nvarchar(10),
                    BounceCodeDescription nvarchar(100),
                    SpamComplaint integer
                );

                if exists (select 1 from @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA where len(Emailaddress)>0)
                begin
                set @ISSHAREDSERVICESDATA =1;
                end 

        exec sp_xml_removedocument @IDOC;        

                if @ISSHAREDSERVICESDATA =1
                begin

                --Spam complaint code will be 112 and we are using 3 digit code only for spam complaint

                --An email address can be either spam complaint or can be hard bounce only at a time.

        insert into @INVALIDACCOUNTS (EMAILADDRESS, BOUNCEMESSAGE, DATEPROCESSED, CATEGORY)
                select distinct 
                    EMAILADDRESS, 
                    BOUNCEMESSAGE, 
                    DATEPROCESSED, 
                    case
                        when O.HARDBOUNCE = 1 then 2
                        when O.SPAMCOMPLAINT = 1 then 112
                        else 
                            case when len(O.BOUNCECODE) >0 then left(O.BOUNCECODE, 1) 
                                else null 
                            end 
                    end 
                from 
                    @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA O
                group by 
                    EMAILADDRESS, 
                    BOUNCEMESSAGE, 
                    DATEPROCESSED, 
                    BOUNCECODE,
                    HARDBOUNCE,
                    SPAMCOMPLAINT
                having 
                    O.DATEPROCESSED = (select MAX(DATEPROCESSED) from @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA O1 where O.EMAILADDRESS = O1.EMAILADDRESS)
                    and O.BOUNCEMESSAGE = (select top 1 BOUNCEMESSAGE from @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA O2 where O.EMAILADDRESS = O2.EMAILADDRESS order by O2.DATEPROCESSED desc)
                    and O.BounceCode = (select top 1 BounceCode  from @INVALIDACCOUNTSTAMPSHAREDSERVICEDATA O3 where O.EMAILADDRESS = O3.EMAILADDRESS order by O3.DATEPROCESSED desc);

        end
                else
                begin

        insert into @INVALIDACCOUNTS (EMAILADDRESS, BOUNCEMESSAGE, DATEPROCESSED, CATEGORY)
                select distinct 
                    EMAILADDRESS, 
                    BOUNCEMESSAGE, 
                    DATEPROCESSED, 
                    CATEGORY 
                from 
                    @INVALIDACCOUNTSTEMP O
                group by 
                    EMAILADDRESS, 
                    BOUNCEMESSAGE, 
                    DATEPROCESSED, 
                    CATEGORY
                having 
                    O.DATEPROCESSED = (select MAX(DATEPROCESSED) from @INVALIDACCOUNTSTEMP O1 where O.EMAILADDRESS = O1.EMAILADDRESS)
                    and O.BOUNCEMESSAGE = (select top 1 BOUNCEMESSAGE from @INVALIDACCOUNTSTEMP O2 where O.EMAILADDRESS = O2.EMAILADDRESS order by O2.DATEPROCESSED desc)
                    and O.CATEGORY = (select top 1 CATEGORY from @INVALIDACCOUNTSTEMP O3 where O.EMAILADDRESS = O3.EMAILADDRESS order by O3.DATEPROCESSED desc);

        end

                -- EMAIL JOB STATUS PROCESSING          

                update 
                    S 
                set 
                    [STATUS] = T.[STATUS],
                    ACTUALSENT = T.ACTUALSENT, 
                    REQUESTEDSENT = T.REQUESTEDSENT, 
                    S.SERVERMODIFIEDDATE = T.DATEMODIFIED, 
                    S.EXTERNALSERVICESTATUS = T.EXTERNALSERVICESTATUS 
                from 
                    dbo.EMAILSTATUSTRANSACTIONAL S
                inner join 
                    @STATUSTABLE T on S.EXTERNALJOBIDENTITY = T.EXTERNALJOBID;

                -- Only update status if email job was not deleted or if it was marked as deleted, but already sent to the external mail server  

                update 
                    S 
                set 
                    [STATUS] = T.[STATUS], 
                    ACTUALSENT = T.ACTUALSENT, 
                    REQUESTEDSENT = T.REQUESTEDSENT, 
                    S.SERVERMODIFIEDDATE = T.DATEMODIFIED, 
                    S.EXTERNALSERVICESTATUS = T.EXTERNALSERVICESTATUS 
                from 
                    dbo.EMAILSTATUSGENERALPURPOSE S
                inner join 
                    @STATUSTABLE T on S.EXTERNALJOBIDENTITY = T.EXTERNALJOBID
                where 
                    (S.[STATUS] <> 18 or T.[STATUS] <> 10);

                declare @UPDATEDATE datetime;          
                select 
                    @UPDATEDATE = max(DATEMODIFIED)
                from 
                    @STATUSTABLE;

                -- cant be sure this exists.

                if @UPDATEDATE is not null
                begin
                    if exists(select * from dbo.EMAILEXTERNALSERVERSTATUS where STATUSTYPE='EMAILJOB')
                    begin
                        update dbo.EMAILEXTERNALSERVERSTATUS 
                        set UPDATEDATE = @UPDATEDATE
                        where STATUSTYPE='EMAILJOB';
                    end
                else
                    begin
                        insert into dbo.EMAILEXTERNALSERVERSTATUS (STATUSTYPE, UPDATEDATE)
                        values ('EMAILJOB', @UPDATEDATE);
                    end
                end            

                -- INVALID ACCOUNT PROCESSING  


                declare @CHANGEAGENTID uniqueidentifier;
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                merge into 
                    dbo.EMAILINVALIDRECIPIENT I
                using 
                    @INVALIDACCOUNTS A on I.ADDRESS = A.EMAILADDRESS
                when matched and (A.CATEGORY = 2 or A.CATEGORY = 6 or A.CATEGORY =112) then
                    update set I.FAILEDATTEMPTS = I.FAILEDATTEMPTS + 1, I.FAILEDMESSAGE = A.BOUNCEMESSAGE, CHANGEDBYID = @CHANGEAGENTID, I.DATEPROCESSED = A.DATEPROCESSED, I.ISBLACKLISTED = 1, I.CATEGORY = A.CATEGORY
                when not matched and A.EMAILADDRESS is not null and (A.CATEGORY = 2 or A.CATEGORY = 6 or A.CATEGORY =112) then
                    insert (ADDRESS, FAILEDATTEMPTS, FAILEDMESSAGE, ADDEDBYID, CHANGEDBYID, DATEPROCESSED, CATEGORY)
                    values (A.EMAILADDRESS, 1, A.BOUNCEMESSAGE, @CHANGEAGENTID, @CHANGEAGENTID, A.DATEPROCESSED, A.CATEGORY);


                select 
                    @UPDATEDATE = max(DATEPROCESSED)
                from 
                    @INVALIDACCOUNTS;

                -- cant be sure this exists.

                if @UPDATEDATE is not null
                begin
                if exists(select * from dbo.EMAILEXTERNALSERVERSTATUS where STATUSTYPE = 'EMAILRECIPIENT')
                    begin
                        update dbo.EMAILEXTERNALSERVERSTATUS 
                        set UPDATEDATE = @UPDATEDATE
                        where STATUSTYPE = 'EMAILRECIPIENT';
                    end
                else
                    begin
                        insert into dbo.EMAILEXTERNALSERVERSTATUS (STATUSTYPE, UPDATEDATE)
                        values ('EMAILRECIPIENT', @UPDATEDATE);
                    end
                end
            end