spBulkUpdate_EmailJob_Recipient

CREATE procedure dbo.SPBULKUPDATE_EMAILJOB_RECIPIENT
(
    @XML as ntext = null
)
as
begin
set nocount on
begin transaction


declare @temp table (
    SIGNATUREID int,
    CATEGORY int,
    DATEPROCESSED datetime,
    DSNBRIEFING ntext,
    CLIENTEMAILJOBRECIPIENTGUID uniqueidentifier,
    HARDBOUNCE int,
    SOFTBOUNCE int,
    SPAMCOMPLAINT int,
  EMAILADDRESS nvarchar(255),
    LISTUNSUBSCRIBE int
)
declare @CONTINUE bit
set @CONTINUE = 0
declare @IDOC int
exec sp_xml_preparedocument @IDOC output, @XML
insert into @TEMP(SIGNATUREID,CATEGORY, DATEPROCESSED,DSNBRIEFING,CLIENTEMAILJOBRECIPIENTGUID,HARDBOUNCE,SOFTBOUNCE,SPAMCOMPLAINT,EMAILADDRESS,LISTUNSUBSCRIBE)
select     
        SignatureID,
    case when len(BounceCode) >0 then 
                                    case when SpamComplaint = 1 or isnull(BounceCode,0) in (108, 109) then 
                                          BounceCode
                                   else
                                      left(BounceCode, 1) 
                                    end
                               else null 
  end as Category,
    convert(datetime, left(DateProcessed,23),126),
    BounceMessage,
    case when try_cast(ClientRecipientIdentifier as uniqueidentifier) is not null   -- sometimes we received text inn this field.

    then cast(ClientRecipientIdentifier as uniqueidentifier) 
    else cast(cast(0 as binary) as uniqueidentifier) end,
    HardBounce,
    SoftBounce,
    SpamComplaint,
  EmailAddress,
    ListUnsubscribe
from openxml (@idoc, '/NewDataSet/RecipientDSN', 2)  -- Updated as per new response from service

with (
        SignatureID int, 
        BounceCode int,
        DateProcessed nvarchar(66),
        BounceMessage ntext,  -- The label value is changed 

        ClientRecipientIdentifier  nvarchar(100) , -- The label value is changed

        HardBounce int,
        SoftBounce int,
        SpamComplaint int,
    EmailAddress nvarchar(255),
        ListUnsubscribe int
    ) as d

if @@rowcount > 0
    set @CONTINUE = 1
exec sp_xml_removedocument @IDOC
if @CONTINUE = 1
begin

--handling list unsubscribe case ,mark that email unsubscribe if its listunsubscribe=1

    update [dbo].EMAILJOB_RECIPIENT
    set
  [UPDATEDATE]=getutcdate(),
    UNSUBSCRIBED=1
    from [dbo].EMAILJOB_RECIPIENT as EJR
    inner join @Temp as tmpDta
    on EJR.MERGEID = tmpDta.CLIENTEMAILJOBRECIPIENTGUID 
    and tmpDta.LISTUNSUBSCRIBE =1

    --make that email unsubscribe by mark as Global optout

        declare @ListUnsubscribeCursor CURSOR,
        @EmailAddress nvarchar(255),
      @Email nvarchar(255),
        @DATE date =GETDATE(),
        @SENDMAIL bit
        begin
            set @ListUnsubscribeCursor = CURSOR local FORWARD_ONLY FOR
            select EMAILADDRESS
           from @Temp as tmpData
               where tmpData.LISTUNSUBSCRIBE =1      

            open @ListUnsubscribeCursor 
            fetch next from @ListUnsubscribeCursor 
            into @Email

            while @@FETCH_STATUS = 0
            begin
              exec dbo.USP_ADD_GLOBALOPTOUTPREFERENCEADD_LISTUNSUBSCRIBE @EMAILADDRESS=@Email,@SENDMAIL=0,@STARTDATE=@Date 
              fetch next from @ListUnsubscribeCursor 
              into @Email 
            end;

            close @ListUnsubscribeCursor ;
            DEALLOCATE @ListUnsubscribeCursor;
        end;

--handling soft bounce case, mark that email address as soft bounce which have softbounce =1

    update [dbo].EMAILJOB_RECIPIENT
    set
        [UPDATEDATE]=getutcdate(),
        [DSNED]= 1,
        [RECENTDSNDATE]=DATEPROCESSED,
        [RECENTDSNSIGNATUREID] = SIGNATUREID,
        [RECENTDSNCATEGORY] = case when CATEGORY =2 then 1 else CATEGORY end, -- This is fine as all category except 2 are soft bounce as per BBIS code for email report

        [STATUSREPORT] = DSNBRIEFING
    from [dbo].EMAILJOB_RECIPIENT as R
    inner join @Temp as D
    on R.MERGEID = D.CLIENTEMAILJOBRECIPIENTGUID 
    and R.OPENED = 0
    and D.SOFTBOUNCE =1

    update [dbo].EMAILJOB_RECIPIENT
    set
        [UPDATEDATE]=getutcdate(),
        [DSNED]= 1,
        [RECENTDSNDATE]=DATEPROCESSED,
        [RECENTDSNSIGNATUREID] = SIGNATUREID,
        [RECENTDSNCATEGORY] = CATEGORY,
        [STATUSREPORT] = DSNBRIEFING
    from [dbo].EMAILJOB_RECIPIENT as R
    inner join @Temp as D
    on R.MERGEID = D.CLIENTEMAILJOBRECIPIENTGUID 
    AND D.CATEGORY = 0 


    --handling hard bounce case, mark that email address as hard bounce which have hardbounce =1

    update [dbo].EMAILJOB_RECIPIENT
    SET
        [UPDATEDATE]=getutcdate(),
        [DSNED]= 1,
        [RECENTDSNDATE]=DATEPROCESSED,
        [RECENTDSNSIGNATUREID] = SIGNATUREID,
        [RECENTDSNCATEGORY] = case when D.SPAMCOMPLAINT =1 then 112 when CATEGORY in (108,109) then CATEGORY else 2 end,  --spam complaint its value is 112, for previously marked as hard bounce it is 108 for previously marked as spamcomplaint it is 109. Only for spam, previously Marked as spam complaint and previously marked as hard bounce we are using 3 digits.

        [STATUSREPORT] = DSNBRIEFING
    from [dbo].EMAILJOB_RECIPIENT as R
    inner join @Temp as D
    on R.MERGEID = D.CLIENTEMAILJOBRECIPIENTGUID 
    and (D.HARDBOUNCE =1 or D.SPAMCOMPLAINT =1)



    --Hard bounce should be mark as Invalid Account Recipients 

    insert into EMAILINVALIDACCOUNTRECIPIENT(EMAILADDRESS, EMAILJOBRECIPIENTID, CREATEDATE, UPDATEDATE, SPAM)
    select R.EMAILADDRESS, R.ID, D.DATEPROCESSED, D.DATEPROCESSED, D.SPAMCOMPLAINT
    from [dbo].EMAILJOB_RECIPIENT as R
    inner join @Temp AS D
    on R.MERGEID = D.CLIENTEMAILJOBRECIPIENTGUID 
    and (D.HARDBOUNCE =1 or D.SPAMCOMPLAINT =1)

    update EMAIL_RECIPIENT
    set INVALIDACCOUNT = 1
    from EMAIL_RECIPIENT ER
    inner join EMAILJOB_RECIPIENT EJR on EJR.EMAILRECIPIENTID = ER.ID
    inner join @Temp D on D.CLIENTEMAILJOBRECIPIENTGUID = EJR.MERGEID and D.CATEGORY = 6
    update EMAILJOB
    set NUMBERINVALIDACCOUNT = NUMBERINVALIDACCOUNT + X.ADDITIONALINVALIDACCOUNTS
    from EMAILJOB EJ
    inner join (
        select EJR.EMAILJOBID EMAILJOBID, count(*) ADDITIONALINVALIDACCOUNTS
        from @Temp D
        inner join EMAILJOB_RECIPIENT EJR on D.CLIENTEMAILJOBRECIPIENTGUID = EJR.MERGEID and D.CATEGORY = 6
        group by EJR.EMAILJOBID
    ) X on X.EMAILJOBID = EJ.ID

    delete from EMAILJOB_RECIPIENT
    where MERGEID in (
        select CLIENTEMAILJOBRECIPIENTGUID from @Temp where CATEGORY = 6
    )    

    declare @UPDATEDATE datetime
    select @UPDATEDATE = max(DATEPROCESSED)
    from @Temp
    update [dbo].[SERVERSTATUS]
    set UPDATEDATE = @UPDATEDATE
    where ID = 2
    if @@rowcount = 0
        insert into dbo.SERVERSTATUS(ID, UPDATEDATE) values (2, @UPDATEDATE)    
end
commit
end