USP_BBNC_COMMITSIGNUPBIOUPDATE
Updates a constituent's biographical information from a Blackbaud Internet Solutions signup transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @GENDERCODE | tinyint | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @CUSTOMIDENTIFIER | nvarchar(36) | IN | |
| @CONSTITUENCYCODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @TITLEID | uniqueidentifier | IN | |
| @REQUESTNOEMAIL | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITSIGNUPBIOUPDATE
(
@ID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@FIRSTNAME nvarchar(50) = '',
@GENDERCODE tinyint = 0,
@KEYNAME nvarchar(100),
@MAIDENNAME nvarchar(100) = '',
@MIDDLENAME nvarchar(50) = '',
@CUSTOMIDENTIFIER nvarchar(36) = '',
@CONSTITUENCYCODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@TITLEID uniqueidentifier = null,
@REQUESTNOEMAIL bit = 0
) as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
set @CURRENTDATE = getdate();
begin try
begin
update dbo.CONSTITUENT set
[BIRTHDATE] = @BIRTHDATE,
[FIRSTNAME] = @FIRSTNAME,
[KEYNAME] = @KEYNAME,
[MAIDENNAME] = @MAIDENNAME,
[MIDDLENAME] = @MIDDLENAME,
[NETCOMMUNITYMEMBER] = 1,
[ISCONSTITUENT] = 1,
[TITLECODEID] = @TITLEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @ID;
-- Only update the gender if it's not "Unknown"
if (@GENDERCODE is not null) and (@GENDERCODE > 0)
update dbo.CONSTITUENT set
[GENDERCODE] = @GENDERCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @ID;
if @CONSTITUENCYCODEID is not null
and not exists
(
select
1
from
dbo.CONSTITUENCY
where
[CONSTITUENTID] = @ID
and [CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
and
(
([DATEFROM] is null and [DATETO] is null)
or ([DATEFROM] <= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) and [DATETO] is null)
or ([DATEFROM] is null and dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) <= [DATETO])
or (dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) between [DATEFROM] and [DATETO])
)
)
begin
declare @CONSTITUENCYDATETO datetime;
select top (1)
@CONSTITUENCYDATETO = dateadd(day, -1, [CONSTITUENCY].[DATEFROM])
from
dbo.CONSTITUENCY
where
[CONSTITUENCY].[CONSTITUENTID] = @ID
and [CONSTITUENCY].[CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
and [CONSTITUENCY].[DATEFROM] > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
order by
[DATETO];
insert into dbo.CONSTITUENCY
(
[CONSTITUENTID],
[CONSTITUENCYCODEID],
[DATEFROM],
[DATETO],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@CONSTITUENCYCODEID,
dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
dbo.UFN_DATE_GETEARLIESTTIME(@CONSTITUENCYDATETO),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
--Retrieve the ID of the solicit code being used to flag do not email.
declare @NOEMAILSOLICITCODEID uniqueidentifier
select top 1 @NOEMAILSOLICITCODEID=DONOTEMAILSOLICITCODEID
from NETCOMMUNITYDEFAULTCODEMAP
--Does the constituent already have that solicit code assigned?
declare @CONSTITUENTSOLICITCODEID uniqueidentifier
select @CONSTITUENTSOLICITCODEID=ID
from CONSTITUENTSOLICITCODE
where CONSTITUENTID=@ID and
SOLICITCODEID=@NOEMAILSOLICITCODEID
--If they request to not receive email...
if @REQUESTNOEMAIL = 1
begin
--And if they don't have the appropriate solicit code, add it.
if @CONSTITUENTSOLICITCODEID is null
begin
exec dbo.USP_DATAFORM_ADD_CONSTITUENTSOLICITCODE null, @NOEMAILSOLICITCODEID, @ID, null, null, '', @CHANGEAGENTID
end
--Otherwise, if they do have the solicit code and the dates on the code do not include today,
-- unbound its date range.
else
begin
update CONSTITUENTSOLICITCODE
set
STARTDATE=null,
ENDDATE=null,
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CHANGEDATE
where
ID=@CONSTITUENTSOLICITCODEID and
(((not STARTDATE is null) and (datediff(day, STARTDATE, getdate())<0)) or
((not ENDDATE is null) and (datediff(day, getdate(), ENDDATE)<0)))
end
end
--If they request to receive email and have the solicit code, remove it.
if @REQUESTNOEMAIL = 0 and not @CONSTITUENTSOLICITCODEID is null
begin
exec dbo.USP_CONSTITUENTSOLICITCODE_DELETE @CONSTITUENTSOLICITCODEID, @CHANGEAGENTID
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;