USP_REPORT_DUPLICATECONSTITUENTSREPORT

CREATE procedure dbo.USP_REPORT_DUPLICATECONSTITUENTSREPORT
(
    @TABLENAME nvarchar(200),
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null,
    @CONSTITUENTQUERY uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
  @RECORDSECURITYCONSTITUENTDUPLICATEMATCHING bit = 0
)
with execute as owner
as

--Build smart security clauses

declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

declare @SECURITY_DECLARATIONS nvarchar(300) = '';
declare @SECURITY_WHERECLAUSE nvarchar(600) = '';

if @ISADMIN = 0
begin
    declare @APPUSER_IN_NONRACROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    declare @APPUSER_IN_NONSITEROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);

    if @RECORDSECURITYCONSTITUENTDUPLICATEMATCHING = 1
    begin
        --This is CLR SP that create new groups based on site security

            exec USP_REPORT_DUPLICATECONSTITUENTSREPORT_SITE @TABLENAME=@TABLENAME,@REPORTUSERID=@REPORTUSERID, @ALTREPORTUSERID=@ALTREPORTUSERID;

            --This is new table created from above CLR SP and used to populate report

          set @TABLENAME  = @TABLENAME + '_DATA_' + replace(Convert(nvarchar(256),@CURRENTAPPUSERID),'-','_')
    end

    if @APPUSER_IN_NONRACROLE = 0 or @APPUSER_IN_NONSITEROLE = 0
    begin
        set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'where '

        if @APPUSER_IN_NONRACROLE = 0
        begin
            set @SECURITY_DECLARATIONS = @SECURITY_DECLARATIONS + 'declare @APPUSER_IN_NOSECGROUPROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);' + CHAR(13);
            set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, dupes.CONSTITID, @APPUSER_IN_NOSECGROUPROLE) = 1' + CHAR(13);

            if @TABLENAME = 'FINDDIFFERENTIALDUPLICATERESULTS'
                set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, dupes._key_CONSTITID, @APPUSER_IN_NOSECGROUPROLE) = 1' + CHAR(13);
        end

        if @APPUSER_IN_NONSITEROLE = 0
        begin
            if @APPUSER_IN_NONRACROLE = 0
                set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'and '

            set @SECURITY_DECLARATIONS = @SECURITY_DECLARATIONS + 'declare @APPUSER_IN_NOSITEROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);' + CHAR(13);

            set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, dupes.CONSTITID, @APPUSER_IN_NOSITEROLE) = 1' + CHAR(13);

            if @TABLENAME = 'FINDDIFFERENTIALDUPLICATERESULTS'
                set @SECURITY_WHERECLAUSE = @SECURITY_WHERECLAUSE + 'and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, dupes._key_CONSTITID, @APPUSER_IN_NOSITEROLE) = 1' + CHAR(13);
        end
    end

end

declare @SQLTOEXEC nvarchar(max);

set @SQLTOEXEC = '
if exists(select name from sysobjects where name = N''' + @TABLENAME + ''' and type = ''U'')
begin

' + @SECURITY_DECLARATIONS + '

select
    ''http://www.blackbaud.com/CONSTITID?CONSTITID='' + CONVERT(nvarchar(36),c.id) as CONSTITID,
    dupes._key_in,
    dupes._key_out,
    dupes._score,
    c.NAME + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(c.SUFFIXCODEID) as NAME, 
    dbo.UFN_BUILDFULLADDRESS(a.ID, a.ADDRESSBLOCK, a.CITY, a.STATEID, a.POSTCODE, a.COUNTRYID) as ADDRESS,
    case
        when canonicalconstituent.ID is not null then canonicalconstituent.NAME + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(canonicalconstituent.SUFFIXCODEID)
        else c.NAME + dbo.UFN_SUFFIXCODE_GETDESCRIPTION(c.SUFFIXCODEID)
    end as CANONICALCONSTITUENTSNAME,
  C.LOOKUPID,
  case
    when canonicalconstituent.ID is not null then canonicalconstituent.LOOKUPID
    else c.LOOKUPID 
  end as CANONICALLOOKUPID,
  C.KEYNAME
from
    dbo.' + @TABLENAME + ' dupes
inner join dbo.CONSTITUENT c on c.ID = dupes.CONSTITID
left join ' + @TABLENAME + ' canonicalrow
on canonicalrow._key_in = dupes._key_out
left join dbo.CONSTITUENT canonicalconstituent
on canonicalconstituent.ID = canonicalrow.CONSTITID
left outer join 
    dbo.ADDRESS a on a.CONSTITUENTID = c.ID and a.ISPRIMARY = 1
';


declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;    

if @CONSTITUENTQUERY is not null
begin
    if not exists(select ID from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY) raiserror('ID set does not exist in the database.', 15, 1);
    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY;
    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @CONSTITUENTQUERY) + ''')';

    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on canonicalconstituent.ID = SELECTION.ID' + nchar(13);
end

set @SQLTOEXEC = @SQLTOEXEC + @SECURITY_WHERECLAUSE;

if @CONSTITUENTID is not null
begin
    if @SECURITY_WHERECLAUSE = ''
    begin
        set @SQLTOEXEC = @SQLTOEXEC + nchar(13) + 'where canonicalconstituent.ID = @CONSTITUENTID';
    end
    else
    begin
        set @SQLTOEXEC = @SQLTOEXEC + nchar(13) + 'and canonicalconstituent.ID = @CONSTITUENTID';
    end
end

set @SQLTOEXEC = @SQLTOEXEC + nchar(13) + '
end
else
begin
    declare @msg nvarchar(400)
    set @msg = ''You must successfully run the necessary duplicate constituent search package in SQL Server Integration Services before running this report.  Contact your IT staff for assistance with running a package in SQL Server Integration Services.''
    raiserror(N''%s'', 15, 1, @msg)
end'

exec sp_executesql @SQLTOEXEC, N'@CURRENTAPPUSERID uniqueidentifier, @CONSTITUENTID uniqueidentifier', @CURRENTAPPUSERID = @CURRENTAPPUSERID, @CONSTITUENTID = @CONSTITUENTID;

--Drop user table created 

if @RECORDSECURITYCONSTITUENTDUPLICATEMATCHING = 1 and CHARINDEX('_DATA_',@TABLENAME) > 0
begin

    set @SQLTOEXEC = 'if object_id('''+ @TABLENAME +''') is not null
        drop table ' + @TABLENAME+ ';'

    exec sp_executesql @SQLTOEXEC, N'@TABLENAME nvarchar(256)', @TABLENAME = @TABLENAME;
end