USP_BBNC_INTEGRATIONNEWSLETTERSUBSCRIPTIONADDUPDATE
Adds a newsletter subscription record from the Blackbaud Internet Solutions integration service.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BBNCUSERID | int | IN | |
| @EMAILJOBRECIPIENTMAPID | int | IN | |
| @EMAILBBNCMAPID | int | IN | |
| @EMAILTEMPLATEBBNCMAPID | int | IN | |
| @SENTDATE | datetime | IN | |
| @OPENED | bit | IN | |
| @UPDATEDATE | datetime | IN | |
| @CLICKEDTHROUGH | bit | IN | |
| @URL | UDT_WEBADDRESS | IN | |
| @EMAILLISTMAPID | int | IN | |
| @EMAILLISTNAME | nvarchar(50) | IN | |
| @SUBSCRIBED | bit | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @BACKOFFICERECORDID | int | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_INTEGRATIONNEWSLETTERSUBSCRIPTIONADDUPDATE
(
@BBNCUSERID int,
@EMAILJOBRECIPIENTMAPID int,
@EMAILBBNCMAPID int,
@EMAILTEMPLATEBBNCMAPID int,
@SENTDATE datetime,
@OPENED bit,
@UPDATEDATE datetime,
@CLICKEDTHROUGH bit,
@URL dbo.UDT_WEBADDRESS,
@EMAILLISTMAPID int,
@EMAILLISTNAME nvarchar(50),
@SUBSCRIBED bit,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@BACKOFFICERECORDID int = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
begin try
declare @NETCOMMUNITYCLIENTUSERID uniqueidentifier;
declare @CONSTITUENTID uniqueidentifier;
set @CONSTITUENTID = null;
if not @BACKOFFICERECORDID is null
set @CONSTITUENTID = dbo.UFN_BBNC_GETCONSTITIDFROMMAPID(@BACKOFFICERECORDID);
if @BBNCUSERID > 0
select
@NETCOMMUNITYCLIENTUSERID = NETCOMMUNITYCLIENTUSER.ID,
@CONSTITUENTID = case when @CONSTITUENTID is null then NETCOMMUNITYCLIENTUSER.CONSTITUENTID else @CONSTITUENTID end
from
dbo.NETCOMMUNITYCLIENTUSER
where
BBNCUSERID = @BBNCUSERID;
--JamesWill CR282489-090607 Don't throw an error when the constituent ID is null. None of our integration tables require the CONSTITUENTID
--and this will give us a chance to link the records up later based on the NETCOMMUNITYEMAILJOBRECIPIENTID.
declare @NETCOMMUNITYEMAILJOBRECIPIENTID uniqueidentifier;
if @EMAILJOBRECIPIENTMAPID > 0
begin
select
@NETCOMMUNITYEMAILJOBRECIPIENTID = NETCOMMUNITYEMAILJOBRECIPIENT.ID
from
dbo.NETCOMMUNITYEMAILJOBRECIPIENT
where
BBNCMAPID = @EMAILJOBRECIPIENTMAPID;
if @NETCOMMUNITYEMAILJOBRECIPIENTID is null
begin
set @NETCOMMUNITYEMAILJOBRECIPIENTID = newid();
insert into dbo.NETCOMMUNITYEMAILJOBRECIPIENT
(
[ID],
[BBNCMAPID],
[CONSTITUENTID],
[EMAILBBNCMAPID],
[EMAILTEMPLATEBBNCMAPID],
[SENTDATE],
[OPENED],
[UPDATEDATE],
[CLICKEDTHROUGH],
[URL],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@NETCOMMUNITYEMAILJOBRECIPIENTID,
@EMAILJOBRECIPIENTMAPID,
@CONSTITUENTID,
coalesce(@EMAILBBNCMAPID, 0),
coalesce(@EMAILTEMPLATEBBNCMAPID, 0),
@SENTDATE,
coalesce(@OPENED, 0),
null, -- Only use UPDATEDATE when email data is downloaded through USP_BBNC_INTEGRATIONEMAILJOBRECIPIENTADDUPDATE
coalesce(@CLICKEDTHROUGH, 0),
coalesce(@URL, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
else
update dbo.NETCOMMUNITYEMAILJOBRECIPIENT
set
[CONSTITUENTID] = @CONSTITUENTID,
[EMAILBBNCMAPID] = coalesce(@EMAILBBNCMAPID, [EMAILBBNCMAPID]),
[EMAILTEMPLATEBBNCMAPID] = coalesce(@EMAILTEMPLATEBBNCMAPID, [EMAILTEMPLATEBBNCMAPID]),
[SENTDATE] = coalesce(@SENTDATE, [SENTDATE]),
[OPENED] = case when @OPENED > [OPENED] then @OPENED else [OPENED] end,
--Only use UPDATEDATE when email data is downloaded through USP_BBNC_INTEGRATIONEMAILJOBRECIPIENTADDUPDATE
[CLICKEDTHROUGH] = case when @CLICKEDTHROUGH > [CLICKEDTHROUGH] then @CLICKEDTHROUGH else [CLICKEDTHROUGH] end,
[URL] = coalesce(@URL, [URL]),
[UPDATEDATE] = coalesce(@UPDATEDATE, [UPDATEDATE]),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @NETCOMMUNITYEMAILJOBRECIPIENTID
and
(
coalesce([CONSTITUENTID], '00000000-0000-0000-0000-000000000000') <> coalesce(@CONSTITUENTID, '00000000-0000-0000-0000-000000000000')
or [EMAILBBNCMAPID] <> coalesce(@EMAILBBNCMAPID, [EMAILBBNCMAPID])
or [EMAILTEMPLATEBBNCMAPID] <> coalesce(@EMAILTEMPLATEBBNCMAPID, [EMAILTEMPLATEBBNCMAPID])
or (@SENTDATE is not null and ([SENTDATE] <> coalesce(@SENTDATE, [SENTDATE]) or [SENTDATE] is null))
or [OPENED] <> coalesce(@OPENED, [OPENED])
or [CLICKEDTHROUGH] <> coalesce(@CLICKEDTHROUGH, [CLICKEDTHROUGH])
or [URL] <> coalesce(@URL, [URL])
or [UPDATEDATE] <> coalesce(@UPDATEDATE, [UPDATEDATE])
);
end
if exists
(
select
[ID]
from
dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
where
(
[NETCOMMUNITYCLIENTUSERID] = @NETCOMMUNITYCLIENTUSERID
or [CONSTITUENTID] = @CONSTITUENTID
)
and [EMAILLISTMAPID] = @EMAILLISTMAPID
)
update dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
set
[EMAILLISTNAME] = coalesce(@EMAILLISTNAME, [EMAILLISTNAME]),
[SUBSCRIBED] = coalesce(@SUBSCRIBED, [SUBSCRIBED]),
[LATESTEMAILJOBRECIPIENTID] = coalesce(@NETCOMMUNITYEMAILJOBRECIPIENTID, [LATESTEMAILJOBRECIPIENTID]),
[UPDATEDATE] = coalesce(@UPDATEDATE, [UPDATEDATE]),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
(
[NETCOMMUNITYCLIENTUSERID] = @NETCOMMUNITYCLIENTUSERID
or [CONSTITUENTID] = @CONSTITUENTID
)
and [EMAILLISTMAPID] = @EMAILLISTMAPID
and
(
[EMAILLISTNAME] <> coalesce(@EMAILLISTNAME, [EMAILLISTNAME])
or [SUBSCRIBED] <> coalesce(@SUBSCRIBED, [SUBSCRIBED])
or coalesce([LATESTEMAILJOBRECIPIENTID], '00000000-0000-0000-0000-000000000000') <> coalesce(@NETCOMMUNITYEMAILJOBRECIPIENTID, '00000000-0000-0000-0000-000000000000')
or ([UPDATEDATE] <> @UPDATEDATE or ([UPDATEDATE] is null and @UPDATEDATE is not null) or ([UPDATEDATE] is not null and @UPDATEDATE is null))
);
else
insert into dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
(
[ID],
[NETCOMMUNITYCLIENTUSERID],
[CONSTITUENTID],
[EMAILLISTMAPID],
[EMAILLISTNAME],
[SUBSCRIBED],
[LATESTEMAILJOBRECIPIENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
newid(),
@NETCOMMUNITYCLIENTUSERID,
@CONSTITUENTID,
coalesce(@EMAILLISTMAPID, 0),
coalesce(@EMAILLISTNAME, ''),
coalesce(@SUBSCRIBED, 0),
@NETCOMMUNITYEMAILJOBRECIPIENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;