USP_BBNC_INTEGRATIONEMAILJOBRECIPIENTADDUPDATE
Adds an email job recipient from the Blackbaud Internet Solutions integration service.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EMAILJOBRECIPIENTMAPID | int | IN | |
| @CONSTITUENTMAPID | int | IN | |
| @EMAILBBNCMAPID | int | IN | |
| @EMAILTEMPLATEBBNCMAPID | int | IN | |
| @EMAILNAME | nvarchar(255) | IN | |
| @EMAILSUBJECT | nvarchar(4000) | IN | |
| @SENTDATE | datetime | IN | |
| @BOUNCED | bit | IN | |
| @BOUNCETEXT | nvarchar(1000) | IN | |
| @OPENED | bit | IN | |
| @UPDATEDATE | datetime | IN | |
| @EMAILADDRESS | nvarchar(255) | IN | |
| @CLICKEDTHROUGH | bit | IN | |
| @DONATED | bit | IN | |
| @URL | UDT_WEBADDRESS | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @SPAMCOMPLAINT | bit | IN | |
| @OPTEDOUT | bit | IN | |
| @EJRUPDATEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_INTEGRATIONEMAILJOBRECIPIENTADDUPDATE
(
@EMAILJOBRECIPIENTMAPID int,
@CONSTITUENTMAPID int,
@EMAILBBNCMAPID int,
@EMAILTEMPLATEBBNCMAPID int,
@EMAILNAME nvarchar(255),
@EMAILSUBJECT nvarchar(4000),
@SENTDATE datetime,
@BOUNCED bit,
@BOUNCETEXT nvarchar(1000),
@OPENED bit,
@UPDATEDATE datetime,
@EMAILADDRESS nvarchar(255),
@CLICKEDTHROUGH bit,
@DONATED bit,
@URL dbo.UDT_WEBADDRESS,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@SPAMCOMPLAINT bit = null,
@OPTEDOUT bit = null,
@EJRUPDATEDATE datetime
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
begin try
if not (@EMAILJOBRECIPIENTMAPID > 0)
raiserror('ERR_EMAILJOBRECIPIENTMAPID_REQUIRED',16,1);
declare @CONSTITUENTID uniqueidentifier;
select
@CONSTITUENTID = CONSTITUENT.ID
from
dbo.CONSTITUENT
where
CONSTITUENT.SEQUENCEID = @CONSTITUENTMAPID;
-- If the constituent does not exist it must have been deleted, skip this email record
if @CONSTITUENTID is not null
begin
if exists
(
select
[ID]
from
dbo.NETCOMMUNITYEMAILJOBRECIPIENT
where
@EMAILJOBRECIPIENTMAPID = [BBNCMAPID]
)
update dbo.NETCOMMUNITYEMAILJOBRECIPIENT
set
[CONSTITUENTID] = @CONSTITUENTID,
[EMAILBBNCMAPID] = coalesce(@EMAILBBNCMAPID, 0),
[EMAILTEMPLATEBBNCMAPID] = coalesce(@EMAILTEMPLATEBBNCMAPID, 0),
[EMAILNAME] = coalesce(@EMAILNAME, ''),
[EMAILSUBJECT] = coalesce(@EMAILSUBJECT, ''),
[SENTDATE] = @SENTDATE,
[BOUNCED] = coalesce(@BOUNCED, 0),
[BOUNCETEXT] = coalesce(@BOUNCETEXT, ''),
[OPENED] = coalesce(@OPENED, 0),
[UPDATEDATE] = @UPDATEDATE,
[EMAILADDRESS] = coalesce(@EMAILADDRESS, ''),
[CLICKEDTHROUGH] = case when [CLICKEDTHROUGH] = 1 then 1 else coalesce(@CLICKEDTHROUGH, 0) end,
[DONATED] = coalesce(@DONATED, 0),
[URL] = coalesce(@URL, ''),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[SPAMCOMPLAINT] = @SPAMCOMPLAINT,
[OPTEDOUT] = @OPTEDOUT
where
[BBNCMAPID] = @EMAILJOBRECIPIENTMAPID
and
(
coalesce([CONSTITUENTID], '00000000-0000-0000-0000-000000000000') <> coalesce(@CONSTITUENTID, '00000000-0000-0000-0000-000000000000')
or [EMAILBBNCMAPID] <> coalesce(@EMAILBBNCMAPID, 0)
or [EMAILTEMPLATEBBNCMAPID] <> coalesce(@EMAILTEMPLATEBBNCMAPID, 0)
or [EMAILNAME] <> coalesce(@EMAILNAME, '')
or [EMAILSUBJECT] <> coalesce(@EMAILSUBJECT, '')
or ([SENTDATE] <> @SENTDATE or ([SENTDATE] is null and @SENTDATE is not null) or ([SENTDATE] is not null and @SENTDATE is null))
or [BOUNCED] <> coalesce(@BOUNCED, 0)
or [BOUNCETEXT] <> coalesce(@BOUNCETEXT, '')
or [OPENED] <> coalesce(@OPENED, 0)
or ([UPDATEDATE] <> @UPDATEDATE or ([UPDATEDATE] is null and @UPDATEDATE is not null) or ([UPDATEDATE] is not null and @UPDATEDATE is null))
or [EMAILADDRESS] <> coalesce(@EMAILADDRESS, '')
or ([CLICKEDTHROUGH] <> 1 and [CLICKEDTHROUGH] <> coalesce(@CLICKEDTHROUGH, 0))
or [DONATED] <> coalesce(@DONATED, 0)
or [URL] <> coalesce(@URL, '')
or [SPAMCOMPLAINT] <> coalesce(@SPAMCOMPLAINT, 0)
or [OPTEDOUT] <> coalesce(@OPTEDOUT, 0)
);
else
insert into dbo.NETCOMMUNITYEMAILJOBRECIPIENT
(
[BBNCMAPID],
[CONSTITUENTID],
[EMAILBBNCMAPID],
[EMAILTEMPLATEBBNCMAPID],
[EMAILNAME],
[EMAILSUBJECT],
[SENTDATE],
[BOUNCED],
[BOUNCETEXT],
[OPENED],
[UPDATEDATE],
[EMAILADDRESS],
[CLICKEDTHROUGH],
[DONATED],
[URL],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[SPAMCOMPLAINT],
[OPTEDOUT]
)
values
(
@EMAILJOBRECIPIENTMAPID,
@CONSTITUENTID,
coalesce(@EMAILBBNCMAPID, 0),
coalesce(@EMAILTEMPLATEBBNCMAPID, 0),
coalesce(@EMAILNAME, ''),
coalesce(@EMAILSUBJECT, ''),
@SENTDATE,
coalesce(@BOUNCED, 0),
coalesce(@BOUNCETEXT, ''),
coalesce(@OPENED, 0),
@UPDATEDATE,
coalesce(@EMAILADDRESS, ''),
coalesce(@CLICKEDTHROUGH, 0),
coalesce(@DONATED, 0),
coalesce(@URL, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@SPAMCOMPLAINT,
@OPTEDOUT
);
end
-- cant be sure this exists.
if @UPDATEDATE is not null
begin
if exists(select 1 from dbo.SERVERSTATUS where ID = 4)
begin
update dbo.SERVERSTATUS
set UPDATEDATE = @EJRUPDATEDATE
where ID = 4;
end
else
begin
insert into dbo.SERVERSTATUS (ID, UPDATEDATE)
values (4, @EJRUPDATEDATE);
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;