USP_DATALIST_CONSTITUENTMEMBERSHIPSDOCUMENTATION
This datalist returns all documentation for all of a constituent's memberships.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @DOCUMENTATIONTYPECODE | tinyint | IN | Documentation type |
| @TITLE | nvarchar(50) | IN | Title |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | xml | IN | |
| @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. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPSDOCUMENTATION
(
@CONSTITUENTID uniqueidentifier = null,
@DOCUMENTATIONTYPECODE tinyint = null,
@TITLE nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
select
MEMBERNOTE.ID,
'f6651922-9ec2-4449-a214-c773f6eae072' as [VIEWFORMID],
MEMBER.ID,
(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID)),
0 as [DOCUMENTATIONTYPECODE],
null as [MEDIAURL],
null as [FILENAME],
null as [FILEFIELDID],
'Note' as [DOCUMENTATIONTYPE],
MEMBERNOTE.DATEENTERED,
MEMBERNOTETYPECODE.DESCRIPTION as [TYPE],
MEMBERNOTE.TITLE,
NF.NAME,
dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Member note',MEMBERNOTE.ID) as ANNOTATIONEXISTS,
SITE.NAME,
MEMBERSHIP.ID
from
dbo.MEMBERNOTE
inner join dbo.MEMBERNOTETYPECODE on MEMBERNOTETYPECODE.ID = MEMBERNOTE.MEMBERNOTETYPECODEID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERNOTE.MEMBERSHIPID
inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
left join dbo.SITE on SITE.ID = MEMBERSHIPPROGRAM.SITEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERNOTE.AUTHORID) NF
where
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null)))
and (@SITEFILTERMODE = 0
or MEMBERSHIPPROGRAM.SITEID in (
select SITEID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
or @CURRENTAPPUSERID is null
) and
MEMBER.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
(@TITLE = '' or MEMBERNOTE.TITLE like @TITLE + '%')
union all
select
MEMBERMEDIALINK.ID,
'53a9494e-27c4-48d7-a78a-a1bbf59e02d7' as [VIEWFORMID],
MEMBER.ID,
(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID)),
1 as [DOCUMENTATIONTYPECODE],
MEMBERMEDIALINK.MEDIAURL,
null,
null,
'Media link' as [DOCUMENTATIONTYPE],
MEMBERMEDIALINK.DATEENTERED,
MEMBERMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
MEMBERMEDIALINK.TITLE,
NF.NAME,
cast(0 as bit) as ANNOTATIONEXISTS,
SITE.NAME,
MEMBERSHIP.ID
from
dbo.MEMBERMEDIALINK
inner join dbo.MEMBERMEDIALINKTYPECODE on MEMBERMEDIALINKTYPECODE.ID = MEMBERMEDIALINK.MEMBERMEDIALINKTYPECODEID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERMEDIALINK.MEMBERSHIPID
inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
left join dbo.SITE on SITE.ID = MEMBERSHIPPROGRAM.SITEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERMEDIALINK.AUTHORID) NF
where
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null)))
and (@SITEFILTERMODE = 0
or MEMBERSHIPPROGRAM.SITEID in (
select SITEID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
or @CURRENTAPPUSERID is null
) and
MEMBER.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
(@TITLE = '' or MEMBERMEDIALINK.TITLE like @TITLE + '%')
union all
select
MEMBERATTACHMENT.ID,
'a5ee4793-6f46-4a05-87ec-43c2da5ea3ca' as [VIEWFORMID],
MEMBER.ID,
(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID)),
2 as [DOCUMENTATIONTYPECODE],
null,
MEMBERATTACHMENT.FILENAME,
'FILE',
'Attachment' as [DOCUMENTATIONTYPE],
MEMBERATTACHMENT.DATEENTERED,
MEMBERATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
MEMBERATTACHMENT.TITLE,
NF.NAME,
cast(0 as bit) as ANNOTATIONEXISTS,
SITE.NAME,
MEMBERSHIP.ID
from
dbo.MEMBERATTACHMENT
inner join dbo.MEMBERATTACHMENTTYPECODE on MEMBERATTACHMENTTYPECODE.ID = MEMBERATTACHMENT.MEMBERATTACHMENTTYPECODEID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERATTACHMENT.MEMBERSHIPID
inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
left join dbo.SITE on SITE.ID = MEMBERSHIPPROGRAM.SITEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERATTACHMENT.AUTHORID) NF
where
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null)))
and (@SITEFILTERMODE = 0
or MEMBERSHIPPROGRAM.SITEID in (
select SITEID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
or @CURRENTAPPUSERID is null
) and
MEMBER.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
(@TITLE = '' or MEMBERATTACHMENT.TITLE like @TITLE + '%')
order by
DATEENTERED desc;