USP_WEBFORMS_CONSTITUENT_ADDRESSINFORMATION_AUTOMATCHORCREATE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @DATECHANGED | datetime | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @PHONE | nvarchar(100) | IN | |
| nvarchar(100) | IN | ||
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @ADDRESSID | uniqueidentifier | INOUT | |
| @PHONEID | uniqueidentifier | INOUT | |
| @EMAILADDRESSID | uniqueidentifier | INOUT | |
| @ORIGINCODE | tinyint | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @EMAILADDRESSTYPECODEID | uniqueidentifier | IN | |
| @PHONETYPECODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_WEBFORMS_CONSTITUENT_ADDRESSINFORMATION_AUTOMATCHORCREATE (
@CONSTITUENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DATECHANGED datetime,
@TITLECODEID uniqueidentifier,
@FIRSTNAME nvarchar(50),
@KEYNAME nvarchar(100),
@PHONE nvarchar(100) = null,
@EMAIL nvarchar(100) = null,
@COUNTRYID uniqueidentifier = null,
@STATEID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = null,
@CITY nvarchar(50) = null,
@POSTCODE nvarchar(12) = null,
@ADDRESSID uniqueidentifier = null output, --If populated in, we will only create an address if there is no address with that ID
@PHONEID uniqueidentifier = null output, --If populated in, we will only create a phone number if there is no phone with that ID
@EMAILADDRESSID uniqueidentifier = null output, --If populated in, we will only create an email address if there is no email with that ID
@ORIGINCODE tinyint = 1, --Default Web Forms
@INFOSOURCECODEID uniqueidentifier = null,
@ADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESSTYPECODEID uniqueidentifier = null,
@PHONETYPECODEID uniqueidentifier = null
)
as
begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @DATECHANGED is null
set @DATECHANGED = getdate()
--Start auto match or create
--Starting with Address
if @STATEID = '00000000-0000-0000-0000-000000000000'
set @STATEID = null
set @ADDRESSBLOCK = isnull(@ADDRESSBLOCK,'')
set @CITY = isnull(@CITY, '')
set @POSTCODE = isnull(@POSTCODE, '')
declare @VALIDADDRESS bit = 0
if (@COUNTRYID is not null and @COUNTRYID <> '00000000-0000-0000-0000-000000000000') and
(
@STATEID is not null or
@ADDRESSBLOCK <> '' or
@CITY <> '' or
@POSTCODE <> ''
)
begin
set @VALIDADDRESS = 1
end
declare @MAKEPRIMARY bit = 0
if @VALIDADDRESS = 1
begin
if @ADDRESSID is null or
@ADDRESSID = '00000000-0000-0000-0000-000000000000' or
not exists(select ID from dbo.ADDRESS where ID = @ADDRESSID and CONSTITUENTID = @CONSTITUENTID)
begin
--Seeing if address exists
declare @LEFTPOSTCODECOUNT tinyint = 0
select @LEFTPOSTCODECOUNT = [LEFTPOSTCODECOUNT]
from dbo.[CONSTITUENTDUPLICATESEARCHSETTINGS]
where [ID] = '7BDE63AA-73B8-4A31-BE9F-82D92B67E2F4'
set @ADDRESSID = null
select @ADDRESSID = [ADDRESS].[ID]
from dbo.[ADDRESS] with (nolock)
where
[ADDRESS].[CONSTITUENTID] = @CONSTITUENTID and
[ADDRESS].[COUNTRYID] = @COUNTRYID and
([ADDRESS].[STATEID] = @STATEID or @STATEID is null) and
lower([ADDRESS].[CITY]) = lower(@CITY) and
left([ADDRESS].[POSTCODE], @LEFTPOSTCODECOUNT) = left(@POSTCODE, @LEFTPOSTCODECOUNT) and
lower(dbo.UFN_ADDRESS_STANDARDIZE([ADDRESS].[ADDRESSBLOCK], [ADDRESS].[COUNTRYID])) = lower(dbo.UFN_ADDRESS_STANDARDIZE(@ADDRESSBLOCK, @COUNTRYID))
--Create address if it doesn't exist
if @ADDRESSID is null
begin
if not exists (
select top 1 [ID]
from dbo.ADDRESS with (nolock)
where
[CONSTITUENTID] = @CONSTITUENTID and
ISPRIMARY=1
)
set @MAKEPRIMARY = 1
else
set @MAKEPRIMARY = 0
exec dbo.[USP_ADDRESS_CREATE]
@ID = @ADDRESSID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@CONSTITUENTID = @CONSTITUENTID,
@COUNTRYID = @COUNTRYID,
@STATEID = @STATEID,
@ADDRESSBLOCK = @ADDRESSBLOCK,
@CITY = @CITY,
@POSTCODE = @POSTCODE,
@ORIGINCODE = @ORIGINCODE,
@PRIMARY = @MAKEPRIMARY,
@ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
@INFOSOURCECODEID = @INFOSOURCECODEID;
end
end
else --the passed in address should be updated
begin
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where ID = @ADDRESSID;
end
end
else --Address is not valid
set @ADDRESSID = null
if len(@PHONE)>(0)
begin
if @PHONEID is null or
@PHONEID = '00000000-0000-0000-0000-000000000000' or
not exists(select ID from dbo.PHONE where ID = @PHONEID and CONSTITUENTID = @CONSTITUENTID)
begin
select @PHONEID = [PHONE].[ID]
from [dbo].[PHONE] with (nolock)
where
[PHONE].[CONSTITUENTID] = @CONSTITUENTID and
[PHONE].[NUMBERNOFORMAT] = dbo.[UFN_PHONE_REMOVEFORMATTING](@PHONE)
if @PHONEID is null
begin
if not exists(
select top 1 1
from dbo.[PHONE] with (nolock)
where
[CONSTITUENTID] = @CONSTITUENTID and
ISPRIMARY = 1
)
set @MAKEPRIMARY = 1
else
set @MAKEPRIMARY = 0
exec [dbo].[USP_PHONE_CREATE]
@ID = @PHONEID output,
@CONSTITUENTID = @CONSTITUENTID,
@NUMBER = @PHONE,
@PRIMARY = @MAKEPRIMARY,
@ORIGINCODE = @ORIGINCODE,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@PHONETYPECODEID = @PHONETYPECODEID,
@COUNTRYID = @COUNTRYID;
end
end
else --The passed in phone should be updated
begin
update dbo.PHONE
set
PHONETYPECODEID = @PHONETYPECODEID,
NUMBER = @PHONE,
COUNTRYID = @COUNTRYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where ID = @PHONEID
end
end
else --Phone is not valid
set @PHONEID = null
if len(@EMAIL) > 0
begin
if @EMAILADDRESSID is null or
@EMAILADDRESSID = '00000000-0000-0000-0000-000000000000' or
not exists(select ID from dbo.EMAILADDRESS where ID = @EMAILADDRESSID)
begin
--WI 449029 set @EMAILADDRESSID to null to prevent default address from creeping in.
set @EMAILADDRESSID = null
select @EMAILADDRESSID = [ID]
from [dbo].[EMAILADDRESS] with (nolock)
where
lower([EMAILADDRESS]) = lower(@EMAIL) and
[CONSTITUENTID] = @CONSTITUENTID
if @EMAILADDRESSID is null
begin
if not exists(
select top 1 1
from dbo.[EMAILADDRESS] with (nolock)
where
[CONSTITUENTID] = @CONSTITUENTID and
ISPRIMARY = 1
)
set @MAKEPRIMARY = 1
else
set @MAKEPRIMARY = 0
exec [dbo].[USP_EMAILADDRESS_CREATE]
@ID = @EMAILADDRESSID output,
@CONSTITUENTID = @CONSTITUENTID,
@EMAILADDRESS = @EMAIL,
@PRIMARY = @MAKEPRIMARY,
@ORIGINCODE = @ORIGINCODE,
@EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
@INFOSOURCECODEID = @INFOSOURCECODEID;
end
end
else --The passed in email address should be updated
begin
update dbo.EMAILADDRESS
set
EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
EMAILADDRESS = @EMAIL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
ID = @EMAILADDRESSID;
end
end
else --Email address is not valid
set @EMAILADDRESSID = null
--We are only currently using title code to update if the constituent does not have one (and the name matches, otherwise, we'll create an alias for that name later in this sp -- about 30 lines down)
if @TITLECODEID is not null
begin
if exists(
select top 1 1
from dbo.[CONSTITUENT] with (nolock)
where
[ID] = @CONSTITUENTID and
[TITLECODEID] is null and
([FIRSTNAME] = @FIRSTNAME or len([FIRSTNAME]) = 0) and
[KEYNAME] = @KEYNAME
)
begin
update dbo.[CONSTITUENT]
set
[TITLECODEID] = @TITLECODEID,
[DATECHANGED] = @DATECHANGED,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @CONSTITUENTID
end
end
--Update firstname if constituent did not have one
set @FIRSTNAME = isnull(@FIRSTNAME, '')
if len(@FIRSTNAME) > 0
begin
if len((select top 1 [FIRSTNAME] from dbo.[CONSTITUENT] with (nolock) where [ID] = @CONSTITUENTID)) = 0
begin
update dbo.[CONSTITUENT]
set
[FIRSTNAME] = @FIRSTNAME,
[DATECHANGED] = @DATECHANGED,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @CONSTITUENTID
end
end
--Create an alias for the constituent if the constituent name does not match the information provided
if @KEYNAME is not null and len(@KEYNAME) > 0
begin
if not exists(
select top 1 1
from dbo.[CONSTITUENT] with (nolock)
left join dbo.[ALIAS] with (nolock)
on [CONSTITUENT].[ID] = [ALIAS].[CONSTITUENTID]
where
(
[CONSTITUENT].[NICKNAME] = @FIRSTNAME or
[CONSTITUENT].[FIRSTNAME] = @FIRSTNAME or
[ALIAS].[FIRSTNAME] = @FIRSTNAME
) and
(
[CONSTITUENT].[KEYNAME] = @KEYNAME or
[ALIAS].[KEYNAME] = @KEYNAME
) and
(
@TITLECODEID is null or
[CONSTITUENT].[TITLECODEID] = @TITLECODEID or
[CONSTITUENT].[TITLE2CODEID] = @TITLECODEID or
[ALIAS].[TITLECODEID] = @TITLECODEID or
[ALIAS].[TITLE2CODEID] = @TITLECODEID
) and
[CONSTITUENT].[ID] = @CONSTITUENTID
)
begin
insert into dbo.[ALIAS]
(
[ID],
[CONSTITUENTID],
[KEYNAME],
[FIRSTNAME],
[TITLECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
newid(),
@CONSTITUENTID,
coalesce(@KEYNAME,''),
coalesce(@FIRSTNAME,''),
@TITLECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATECHANGED,
@DATECHANGED
);
end
end
end