USP_DATALIST_CONSTITUENTDATAREVIEW
This datalist returns changes to email and addresses.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TYPECODEID | int | IN | Type |
| @CHANGEAGENT | nvarchar(128) | IN | User |
| @DATERANGE | int | IN | Date |
| @INCLUDEUNASSIGNED | bit | IN | Include unassigned |
| @STARTDATE | datetime | IN | From |
| @ENDDATE | datetime | IN | To |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @MAXROWS | int | IN | Input parameter indicating the maximum number of rows to return. |
| @RECORDID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTDATAREVIEW
(
@TYPECODEID int = null,
@CHANGEAGENT nvarchar(128) = null,
@DATERANGE int = null,
@INCLUDEUNASSIGNED bit = 0,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@MAXROWS int,
@RECORDID uniqueidentifier = null
)
with execute as owner
as
set nocount on;
declare @ISADMIN bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
if @MAXROWS > 500 set @MAXROWS = 500;
declare @FUNDRAISERID uniqueidentifier;
set @FUNDRAISERID = dbo.UFN_CONSTITUENT_GETIDFROMAPPUSERID(@CURRENTAPPUSERID);
-- turn off all filters if viewing record history
if @RECORDID is not null
begin
set @DATERANGE = 10;
set @CHANGEAGENT = null;
end
declare @APPLYDATEFILTER bit = 1;
if @DATERANGE = 0
begin
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
end
else
begin
if @DATERANGE = 10
set @APPLYDATEFILTER = 0;
else
exec dbo.USP_RESOLVEDATEFILTER @DATERANGE, @STARTDATE output, @ENDDATE output;
end
declare @EMAILSQL nvarchar(max)
if coalesce(@TYPECODEID, 1) = 1
set @EMAILSQL = dbo.UFN_CONSTITUENT_BUILDRECENTCHANGESQUERY('EMAILADDRESS',
'<DISPLAYTABLE>.EMAILADDRESS',
@RECORDID,
@CHANGEAGENT,
@INCLUDEUNASSIGNED,
@FUNDRAISERID,
'Email',
'Email',
'B1569ADE-ACEA-40EC-A52B-F9D5DBD9AA33',
'5B4DB500-90D5-4FAD-BD8D-ABB14D35D5AC',
'5B4DB500-90D5-4FAD-BD8D-ABB14D35D5AC',
'69520774-D096-49FE-BD4D-FF26B7D83658',
@ISADMIN,
@APPLYDATEFILTER)
declare @ADDRESSSQL nvarchar(max)
if coalesce(@TYPECODEID, 2) = 2
set @ADDRESSSQL = dbo.UFN_CONSTITUENT_BUILDRECENTCHANGESQUERY( 'ADDRESS',
'replace(replace(dbo.UFN_BUILDFULLADDRESS(null, <DISPLAYTABLE>.ADDRESSBLOCK, <DISPLAYTABLE>.CITY, <DISPLAYTABLE>.STATEID, <DISPLAYTABLE>.POSTCODE, <DISPLAYTABLE>.COUNTRYID), char(10), '' ''), char(13), '''')',
@RECORDID,
@CHANGEAGENT,
@INCLUDEUNASSIGNED,
@FUNDRAISERID,
'Address',
'Address',
'110E4587-C7E4-442D-BF18-9502E02C3976',
'1AF9ED37-36D8-4B82-A6C7-29747D49757D',
'1AF9ED37-36D8-4B82-A6C7-29747D49757D',
'1C3F93CF-B73C-47F4-BAF2-305DF944812C',
@ISADMIN,
@APPLYDATEFILTER)
declare @PHONESQL nvarchar(max)
if coalesce(@TYPECODEID, 3) = 3
set @PHONESQL = dbo.UFN_CONSTITUENT_BUILDRECENTCHANGESQUERY('PHONE',
'<DISPLAYTABLE>.NUMBER',
@RECORDID,
@CHANGEAGENT,
@INCLUDEUNASSIGNED,
@FUNDRAISERID,
'Phone',
'Phone',
'748CCA16-4A1F-475D-BD94-150AD804ADCF',
'3925553C-FA64-4BB1-ABAB-6BD2552B1FB9',
'3925553C-FA64-4BB1-ABAB-6BD2552B1FB9',
'7796E11E-F191-4B2B-86F5-3BAC29FD527E',
@ISADMIN,
@APPLYDATEFILTER)
-- Make sure at least one section is included
if @EMAILSQL is not null or @ADDRESSSQL is not null or @PHONESQL is not null
begin
declare @COMBINEDSQL nvarchar(max) = '
select top(@MAXROWS)
RECORDID,
AUDITID,
VIEWFORMID,
coalesce(AUDITKEY, RECORDID) as VIEWCONTEXTID,
[UPDATES].CONSTITUENTID,
CONSTITUENT.NAME + '' ('' + CONSTITUENT.LOOKUPID + '')'' NAME,
[TYPE],
CHANGEAGENT.USERNAME,
CHANGEDATE,
case EDITTYPECODE
when 1 then ''Edit''
when 2 then ''Delete''
else ''Add'' end as EDITTYPE,
[DISPLAY],
LASTCHANGE,
ROWEXISTS,
ROLLBACKTODATE,
ROLLBACKREASON
from
( ' + char(13)
declare @NEEDSUNIONALL bit = 0
if @EMAILSQL is not null
begin
set @COMBINEDSQL = @COMBINEDSQL + @EMAILSQL + char(13)
set @NEEDSUNIONALL = 1
end
if @ADDRESSSQL is not null
begin
if @NEEDSUNIONALL = 1
set @COMBINEDSQL = @COMBINEDSQL + ' union all ' + char(13)
set @COMBINEDSQL = @COMBINEDSQL + @ADDRESSSQL + char(13)
set @NEEDSUNIONALL = 1
end
if @PHONESQL is not null
begin
if @NEEDSUNIONALL = 1
set @COMBINEDSQL = @COMBINEDSQL + ' union all ' + char(13)
set @COMBINEDSQL = @COMBINEDSQL + @PHONESQL + char(13)
end
set @COMBINEDSQL = @COMBINEDSQL + ') as UPDATES
inner join dbo.CONSTITUENT on UPDATES.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.CHANGEAGENT on UPDATES.CHANGEAGENTID = CHANGEAGENT.ID
order by CHANGEDATE desc
option (recompile)'
declare @SQLCOMMONJOINS nvarchar(max) = '';
declare @SQLCOMMONPREDICATES nvarchar(max) = '';
if @RECORDID is null
begin
set @SQLCOMMONJOINS = ' left join dbo.RELATIONSHIPMANAGER on RELATIONSHIPMANAGER.CONSTITUENTID = CONSTITUENT.ID ' + char(13);
end
if coalesce(@CHANGEAGENT, '') <> ''
begin
set @SQLCOMMONJOINS = @SQLCOMMONJOINS + ' left join dbo.CHANGEAGENT on CHANGEAGENT.ID = BASETABLE.AUDITCHANGEAGENTID ' + char(13);
set @SQLCOMMONPREDICATES = ' and CHANGEAGENT.ID in (select ID from dbo.CHANGEAGENT where USERNAME = @CHANGEAGENT) ' + char(13)
end
if @APPLYDATEFILTER = 1
begin
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and BASETABLE.AUDITDATE between @STARTDATE and @ENDDATE' + char(13);
end
if @ISADMIN = 0
begin
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + '
and
(
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYFEATUREID, BASETABLE.CONSTITUENTID) = 1 and
exists
(
select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(BASETABLE.CONSTITUENTID) as CONSTITUENTSITE
where
exists
(
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
where
SITEID= CONSTITUENTSITE.[SITEID] or
(
SITEID is null and CONSTITUENTSITE.[SITEID] is null
)
)
)
) ' + char(13)
end
if @RECORDID is not null
begin
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and BASETABLE.AUDITRECORDID = @RECORDID ' + char(13);
end
else
begin
if @FUNDRAISERID is not null and @INCLUDEUNASSIGNED = 1
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + '
and
(
RELATIONSHIPMANAGER.FUNDRAISERID = @FUNDRAISERID or
RELATIONSHIPMANAGER.ID is null
) ' + char(13);
else if @FUNDRAISERID is not null
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and RELATIONSHIPMANAGER.FUNDRAISERID = @FUNDRAISERID ' + char(13);
else if @INCLUDEUNASSIGNED = 1
set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and RELATIONSHIPMANAGER.ID is null ' + char(13);
end
--This is used when looking for edits to address/email/phone records. Using a temp table rather than a sub-select improves performance.
declare @TEMPTABLESQL nvarchar(max) = '
declare @<BASETABLE>EDITBASETABLE table (
AUDITRECORDID uniqueidentifier,
AUDITID uniqueidentifier,
AUDITKEY uniqueidentifier,
CONSTITUENTID uniqueidentifier,
AUDITCHANGEAGENTID uniqueidentifier,
AUDITDATE datetime
);
insert into @<BASETABLE>EDITBASETABLE
select top(@MAXROWS)
BASETABLE.AUDITRECORDID,
BASETABLE.AUDITID,
BASETABLE.AUDITKEY,
BASETABLE.CONSTITUENTID,
BASETABLE.AUDITCHANGEAGENTID,
BASETABLE.AUDITDATE
from
dbo.<BASETABLE>AUDIT as BASETABLE
inner join dbo.CONSTITUENT on BASETABLE.CONSTITUENTID = CONSTITUENT.ID '
+ @SQLCOMMONJOINS + '
where
BASETABLE.AUDITTYPECODE = 0
and CONSTITUENT.ISCONSTITUENT = 1 '
+ @SQLCOMMONPREDICATES + char(13) + '
order by
BASETABLE.AUDITDATE desc;
'
if @ADDRESSSQL is not null
begin
declare @ADDRESSTEMPTABLESQL nvarchar(max) = replace(@TEMPTABLESQL, '<BASETABLE>', 'ADDRESS');
set @COMBINEDSQL = @ADDRESSTEMPTABLESQL + char(13) + @COMBINEDSQL;
end
if @EMAILSQL is not null
begin
declare @EMAILADDRESSTEMPTABLESQL nvarchar(max) = replace(@TEMPTABLESQL, '<BASETABLE>', 'EMAILADDRESS');
set @COMBINEDSQL = @EMAILADDRESSTEMPTABLESQL + char(13) + @COMBINEDSQL;
end
if @PHONESQL is not null
begin
declare @PHONETEMPTABLESQL nvarchar(max) = replace(@TEMPTABLESQL, '<BASETABLE>', 'PHONE');
set @COMBINEDSQL = @PHONETEMPTABLESQL + char(13) + @COMBINEDSQL;
end
exec sp_executesql @COMBINEDSQL,
N'@CHANGEAGENT nvarchar(128) = null,
@DATERANGE int,
@INCLUDEUNASSIGNED bit,
@STARTDATE datetime,
@ENDDATE datetime,
@CURRENTAPPUSERID uniqueidentifier,
@SECURITYFEATUREID uniqueidentifier,
@SECURITYFEATURETYPE tinyint,
@MAXROWS int,
@RECORDID uniqueidentifier,
@FUNDRAISERID uniqueidentifier',
@CHANGEAGENT,
@DATERANGE,
@INCLUDEUNASSIGNED,
@STARTDATE,
@ENDDATE,
@CURRENTAPPUSERID,
@SECURITYFEATUREID,
@SECURITYFEATURETYPE,
@MAXROWS,
@RECORDID,
@FUNDRAISERID
end