USP_DATALIST_CONSTITUENTGROUPDOCUMENTATION
List of documentation for a group and its members
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUPID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUPDOCUMENTATION
(
@GROUPID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;
-- the ID of this documentation datalist, used to check permissions
declare @DATALISTID uniqueidentifier;
set @DATALISTID = 'e3ffb38a-74f6-4f24-9a82-42bf217649d5';
declare @ISSYSADMIN bit;
select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
-- group member notes
select
CONSTITUENTNOTE.ID,
CONSTITUENTNOTE.CONSTITUENTID as 'SUBJECTID',
SUBJECT_NF.NAME as 'SUBJECTNAME',
'55EBA39F-1513-46EC-85DC-7FEBAE3F0698' as [VIEWFORMID],
0 as [DOCUMENTATIONTYPECODE],
null as [MEDIAURL],
null as [FILENAME],
null as [FILEFIELDID],
'Note' as [DOCUMENTATIONTYPE],
CONSTITUENTNOTE.DATEENTERED,
CONSTITUENTNOTETYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTNOTE.TITLE,
NF.NAME,
CAST(CONSTITUENTNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
case when CONSTITUENTNOTE.CONSTITUENTID = @GROUPID then
'0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTNOTE.CONSTITUENTID) = 1 then
'1'
else
'2' +
(select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTNOTE.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then
1
else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTNOTE.CONSTITUENTID)
end as 'HASPERMISSIONS',
dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Constituent note',CONSTITUENTNOTE.ID) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTNOTE
inner join dbo.CONSTITUENTNOTETYPECODE on CONSTITUENTNOTETYPECODE.ID = CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.AUTHORID) NF
inner join dbo.GROUPMEMBER GM on GM.MEMBERID = CONSTITUENTNOTE.CONSTITUENTID
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.CONSTITUENTID) SUBJECT_NF
where
GM.GROUPID = @GROUPID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= CONSTITUENTNOTE.DATEENTERED)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= CONSTITUENTNOTE.DATEENTERED)) or
(GMDR.DATEFROM <= CONSTITUENTNOTE.DATEENTERED and GMDR.DATETO >= CONSTITUENTNOTE.DATEENTERED)) and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTNOTETYPECODE.ID) = 1)
union all
-- group notes
select
CONSTITUENTNOTE.ID,
CONSTITUENTNOTE.CONSTITUENTID as 'SUBJECTID',
SUBJECT_NF.NAME as 'SUBJECTNAME',
'55EBA39F-1513-46EC-85DC-7FEBAE3F0698' as [VIEWFORMID],
0 as [DOCUMENTATIONTYPECODE],
null as [MEDIAURL],
null as [FILENAME],
null as [FILEFIELDID],
'Note' as [DOCUMENTATIONTYPE],
CONSTITUENTNOTE.DATEENTERED,
CONSTITUENTNOTETYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTNOTE.TITLE,
NF.NAME,
CAST(CONSTITUENTNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
case when CONSTITUENTNOTE.CONSTITUENTID = @GROUPID then
'0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTNOTE.CONSTITUENTID) = 1 then
'1'
else
'2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTNOTE.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then
1
else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTNOTE.CONSTITUENTID)
end as 'HASPERMISSIONS',
dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Constituent note',CONSTITUENTNOTE.ID) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTNOTE
inner join dbo.CONSTITUENTNOTETYPECODE on CONSTITUENTNOTETYPECODE.ID = CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.AUTHORID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.CONSTITUENTID) SUBJECT_NF
where
CONSTITUENTNOTE.CONSTITUENTID = @GROUPID and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTNOTETYPECODE.ID) = 1)
union all
-- group member media links
select
CONSTITUENTMEDIALINK.ID,
CONSTITUENTMEDIALINK.CONSTITUENTID,
SUBJECT_NF.NAME,
'E708EC1F-89D7-4453-BE18-808F6DE7419F' as [VIEWFORMID],
1 as [DOCUMENTATIONTYPECODE],
CONSTITUENTMEDIALINK.MEDIAURL,
null,
null,
'Media link' as [DOCUMENTATIONTYPE],
CONSTITUENTMEDIALINK.DATEENTERED,
CONSTITUENTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTMEDIALINK.TITLE,
NF.NAME as 'AUTHOR',
CAST(CONSTITUENTMEDIALINK.ID AS NVARCHAR(36)) + ':1' AS RSSID,
case when CONSTITUENTMEDIALINK.CONSTITUENTID = @GROUPID then
'0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTMEDIALINK.CONSTITUENTID) = 1 then
'1'
else
'2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTMEDIALINK.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then
1
else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTMEDIALINK.CONSTITUENTID)
end as 'HASPERMISSIONS',
cast(0 as bit) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTMEDIALINK
inner join dbo.CONSTITUENTMEDIALINKTYPECODE on CONSTITUENTMEDIALINKTYPECODE.ID = CONSTITUENTMEDIALINK.CONSTITUENTMEDIALINKTYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEDIALINK.AUTHORID) NF
inner join dbo.GROUPMEMBER GM on GM.MEMBERID = CONSTITUENTMEDIALINK.CONSTITUENTID
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEDIALINK.CONSTITUENTID) SUBJECT_NF
where
GM.GROUPID = @GROUPID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED)) or
(GMDR.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED and GMDR.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED)) and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTMEDIALINKTYPECODE.ID) = 1)
union all
-- group media links
select
CONSTITUENTMEDIALINK.ID,
CONSTITUENTMEDIALINK.CONSTITUENTID,
SUBJECT_NF.NAME,
'E708EC1F-89D7-4453-BE18-808F6DE7419F' as [VIEWFORMID],
1 as [DOCUMENTATIONTYPECODE],
CONSTITUENTMEDIALINK.MEDIAURL,
null,
null,
'Media link' as [DOCUMENTATIONTYPE],
CONSTITUENTMEDIALINK.DATEENTERED,
CONSTITUENTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTMEDIALINK.TITLE,
NF.NAME as 'AUTHOR',
CAST(CONSTITUENTMEDIALINK.ID AS NVARCHAR(36)) + ':1' AS RSSID,
case
when CONSTITUENTMEDIALINK.CONSTITUENTID = @GROUPID then '0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTMEDIALINK.CONSTITUENTID) = 1 then '1'
else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTMEDIALINK.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTMEDIALINK.CONSTITUENTID)
end as 'HASPERMISSIONS',
cast(0 as bit) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTMEDIALINK
inner join dbo.CONSTITUENTMEDIALINKTYPECODE on CONSTITUENTMEDIALINKTYPECODE.ID = CONSTITUENTMEDIALINK.CONSTITUENTMEDIALINKTYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEDIALINK.AUTHORID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEDIALINK.CONSTITUENTID) SUBJECT_NF
where
CONSTITUENTMEDIALINK.CONSTITUENTID = @GROUPID and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTMEDIALINKTYPECODE.ID) = 1)
union all
-- group member attachments
select
CONSTITUENTATTACHMENT.ID,
CONSTITUENTATTACHMENT.CONSTITUENTID,
SUBJECT_NF.NAME,
'7126725A-12F2-4A37-A992-10891CEEA336' as [VIEWFORMID],
2 as [DOCUMENTATIONTYPECODE],
null,
CONSTITUENTATTACHMENT.FILENAME,
'FILE',
'Attachment' as [DOCUMENTATIONTYPE],
CONSTITUENTATTACHMENT.DATEENTERED,
CONSTITUENTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTATTACHMENT.TITLE,
NF.NAME as 'AUTHOR',
CAST(CONSTITUENTATTACHMENT.ID AS NVARCHAR(36)) + ':2' AS RSSID,
case
when CONSTITUENTATTACHMENT.CONSTITUENTID = @GROUPID then '0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTATTACHMENT.CONSTITUENTID) = 1 then '1'
else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTATTACHMENT.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTATTACHMENT.CONSTITUENTID)
end as 'HASPERMISSIONS',
cast(0 as bit) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTATTACHMENT
inner join dbo.CONSTITUENTATTACHMENTTYPECODE on CONSTITUENTATTACHMENTTYPECODE.ID = CONSTITUENTATTACHMENT.CONSTITUENTATTACHMENTTYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTATTACHMENT.AUTHORID) NF
inner join dbo.GROUPMEMBER GM on GM.MEMBERID = CONSTITUENTATTACHMENT.CONSTITUENTID
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTATTACHMENT.CONSTITUENTID) SUBJECT_NF
where
GM.GROUPID=@GROUPID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED))or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED)) or
(GMDR.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED and GMDR.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED)) and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTATTACHMENTTYPECODE.ID) = 1)
union all
-- group attachments
select
CONSTITUENTATTACHMENT.ID,
CONSTITUENTATTACHMENT.CONSTITUENTID,
SUBJECT_NF.NAME,
'7126725A-12F2-4A37-A992-10891CEEA336' as [VIEWFORMID],
2 as [DOCUMENTATIONTYPECODE],
null,
CONSTITUENTATTACHMENT.FILENAME,
'FILE',
'Attachment' as [DOCUMENTATIONTYPE],
CONSTITUENTATTACHMENT.DATEENTERED,
CONSTITUENTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
CONSTITUENTATTACHMENT.TITLE,
NF.NAME as 'AUTHOR',
CAST(CONSTITUENTATTACHMENT.ID AS NVARCHAR(36)) + ':2' AS RSSID,
case
when CONSTITUENTATTACHMENT.CONSTITUENTID = @GROUPID then '0'
when (select ISPRIMARY from GROUPMEMBER where GROUPID=@GROUPID and MEMBERID=CONSTITUENTATTACHMENT.CONSTITUENTID) = 1 then '1'
else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=CONSTITUENTATTACHMENT.CONSTITUENTID)
end as 'GROUPORPRIMARYSORT',
case when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 else
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENTATTACHMENT.CONSTITUENTID)
end as 'HASPERMISSIONS',
cast(0 as bit) as ANNOTATIONEXISTS
from
dbo.CONSTITUENTATTACHMENT
inner join dbo.CONSTITUENTATTACHMENTTYPECODE on CONSTITUENTATTACHMENTTYPECODE.ID = CONSTITUENTATTACHMENT.CONSTITUENTATTACHMENTTYPECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTATTACHMENT.AUTHORID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTATTACHMENT.CONSTITUENTID) SUBJECT_NF
where
CONSTITUENTATTACHMENT.CONSTITUENTID = @GROUPID and
(@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTATTACHMENTTYPECODE.ID) = 1)
order by
GROUPORPRIMARYSORT desc, DATEENTERED desc;