USP_EMAIL_TRANSACTIONAL_ADD_FROM_ACKNOWLEDGEMENT

CREATE procedure dbo.USP_EMAIL_TRANSACTIONAL_ADD_FROM_ACKNOWLEDGEMENT
(
    @EMAILID int,
    @EMAILJOBID int,
    @USERID int,
    @EMAILADDRESS nvarchar(255),
    @DISPLAYNAME nvarchar(255),
    @BACKOFFICERECORDID int,
    @MERGEID uniqueidentifier,
    @IsInvalidAccount bit = null output
)
as
begin

    declare @NOW datetime
    set @NOW = GETUTCDATE()

    --get backoffice/people id

    declare @BACKOFFICEPEOPLEID int
    select @BACKOFFICEPEOPLEID=ID from dbo.BackOfficeSystemPeople where BackofficeRecordID=@BACKOFFICERECORDID and BackOfficeSystemID=0
    if @BACKOFFICEPEOPLEID is NULL
    begin
        insert into dbo.BackOfficeSystemPeople (BackOfficeSystemID,BackofficeRecordID) values (0,@BACKOFFICERECORDID)
        select @BACKOFFICEPEOPLEID=@@IDENTITY 
    end

    declare @EMAILRECIPIENTID int = null;
    select @EMAILRECIPIENTID = ID from Email_Recipient where EmailID = @EMAILID and UserID = @USERID;

    set @IsInvalidAccount = 0;
    select @IsInvalidAccount = ISBLACKLISTED from dbo.EMAILINVALIDRECIPIENT where ADDRESS = @EMAILADDRESS    

    --Doing an Add/Update in case of a resend where you want to do an update

    if @EMAILRECIPIENTID is not null
        update Email_Recipient
        set AddressBookID=0,
            DisplayName=@DISPLAYNAME,
            EmailAddress=@EMAILADDRESS,
            DataSourceID=null,
            [INVALIDACCOUNT] = @IsInvalidAccount,
            BackOfficeSystemPeopleID=@BACKOFFICEPEOPLEID
        where ID = @EMAILRECIPIENTID;
    else
    begin
        --insert into Email_Recipient

        INSERT INTO Email_Recipient
        (
            [EmailID],
            [UserID],
            [AddressBookID],
            [DisplayName],
            [EmailAddress],
            [DataSourceID],
            [INVALIDACCOUNT],
            [BackOfficeSystemPeopleID]
        )
        VALUES
        (
            @EMAILID,
            @USERID,
            0,
            @DISPLAYNAME,
            @EMAILADDRESS,
            NULL,
            @IsInvalidAccount,
            @BACKOFFICEPEOPLEID
        );
        set @EMAILRECIPIENTID=@@IDENTITY;
    end

    declare @ERID int

    if @IsInvalidAccount = 0
    begin

        --insert into EmailJob_Recipient

        INSERT INTO EmailJob_Recipient
        (
            [EMAILID],
            [EMAILJOBID],
            [USERID],
            [EMAILADDRESS],
            [MESSAGEDATE],
            [SENTDATE],
            [UPDATEDATE],
            [SENT],
            [OPENED],
            [OPENEDDATE],
            [DSNED],
            [RECENTDSNDATE],
            [RECENTDSNSIGNATUREID],
            [RECENTDSNCATEGORY],
            [STATUSREPORT],
            [COMPLETED],
            [ADDRESSBOOKID],
            [EMAILRECIPIENTID],
            [BACKOFFICESYSTEMPEOPLEID],
            [MERGEID]
        )
        VALUES
        (
            @EMAILID,
            @EMAILJOBID,
            @USERID,
            @EMAILADDRESS,
            @NOW,
            null,
            @NOW,
            0,
            0,
            NULL,
            0,
            NULL,
            NULL,
            NULL,
            NULL,
            0,
            0,
            @EMAILRECIPIENTID,
            @BACKOFFICEPEOPLEID,
            @MERGEID
        )
        set @ERID= @@IDENTITY

        --update EMAILJOB's status to 'processed'

        update EMAILJOB set Status=12 where ID=@EMAILJOBID

    end

    return @ERID
end