UFN_CONSTITUENT_BUILDRECENTCHANGESQUERY

            CREATE function dbo.UFN_CONSTITUENT_BUILDRECENTCHANGESQUERY
            (
                @SOURCETABLENAME nvarchar(100),
                @DISPLAYEXPRESSION nvarchar(300),
                @RECORDID uniqueidentifier,
                @CHANGEAGENT nvarchar(128),
                @INCLUDEUNASSIGNED bit,
                @FUNDRAISERID uniqueidentifier,
                @TYPE nvarchar(20),
                @DEFAULTDISPLAYTEXT nvarchar(20),
                @ADDEDVIEWFORMID uniqueidentifier,
                @EDITEDVIEWFORMID uniqueidentifier,
                @DELETEDVIEWFORMID uniqueidentifier,
                @ORIGINALADDVIEWFORMID uniqueidentifier,
                @ISADMIN bit,
                @APPLYDATEFILTER bit
            )
            returns nvarchar(max)
            with execute as caller
            as begin

                /* EDITTYPECODE  
                    0 = Add
                    1 = Edit
                    2 = Delete
                    100 = Original Add (after edited)
                */

                -- Find recent changes by selecting the most recent changes for each change

                -- type independently and then unioning the results and getting the most 

                -- recent changes of the combined list.


                -- Setup common query elements

                declare @SQLCOMMONJOINS nvarchar(max) = 'inner join dbo.CONSTITUENT on BASETABLE.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
                if @RECORDID is null -- If RECORDID is null, we need to check if either INCLUDEUNASSIGNED is true or the constituent's manager matches FUNDRAISERID

                    set @SQLCOMMONJOINS = @SQLCOMMONJOINS + 'left join dbo.RELATIONSHIPMANAGER on RELATIONSHIPMANAGER.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
                if coalesce(@CHANGEAGENT, '') <> ''
                    set @SQLCOMMONJOINS = @SQLCOMMONJOINS + 'left join dbo.CHANGEAGENT on CHANGEAGENT.ID = BASETABLE.<CHANGEAGENT> ' + char(13)

                declare @SQLCOMMONPREDICATES nvarchar(max) = 'CONSTITUENT.ISCONSTITUENT = 1' + char(13)

                if @APPLYDATEFILTER = 1
                    set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and BASETABLE.<CHANGEDATE> between @STARTDATE and @ENDDATE ' + char(13)

                if @ISADMIN = 0
                    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)

                -- Viewing a record turns off all other filters so only apply FUNDRAISER filters when @RECORDID is null

                if @RECORDID is not null
                    set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and BASETABLE.<RECORDID> = @RECORDID ' + CHAR(13)
                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 '
                    else if @INCLUDEUNASSIGNED = 1
                        set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and RELATIONSHIPMANAGER.ID is null '
                    else -- If the user isn't a FUNDRAISER and INCLUDEUNASSIGNED is false, no constituents will meet the criteria so the routine should just return an empty string indicating this section isn't applicable

                        return null
                end

                declare @SQLCURRENTVALUEJOINS nvarchar(max) = '
                    left join dbo.<SOURCETABLENAME> [CURRENTEXISTING] on [CURRENTEXISTING].ID = BASETABLE.AUDITRECORDID
                    left join 
                    (
                        select top 1
                            AUDITRECORDID,
                            ' + replace(@DISPLAYEXPRESSION, '<DISPLAYTABLE>', 'AUDIT') + ' as DISPLAY,
                            DATEADDED,
                            AUDITID
                        from dbo.<SOURCETABLENAME>AUDIT as AUDIT
                        where AUDITTYPECODE = 2
                        order by AUDITDATE desc    
                    ) as [CURRENTDELETED] on 
                        [CURRENTDELETED].AUDITRECORDID = BASETABLE.AUDITRECORDID and
                        [CURRENTEXISTING].ID is null'

                declare @SQLCURRENTVALUEDISPLAY nvarchar(max) = '
                    case 
                        when [CURRENTEXISTING].ID is not null then ' + replace(@DISPLAYEXPRESSION, '<DISPLAYTABLE>', 'CURRENTEXISTING') + '
                        else [CURRENTDELETED].DISPLAY
                    end'                

                if coalesce(@CHANGEAGENT, '') <> ''
                    set @SQLCOMMONPREDICATES = @SQLCOMMONPREDICATES + ' and CHANGEAGENT.ID in (select ID from dbo.CHANGEAGENT where USERNAME=@CHANGEAGENT) ' + CHAR(13)

                -- Find changes as a result of being added

                declare @SQLADDEDRECORDS nvarchar(max) = '
                select top(@MAXROWS)
                    BASETABLE.ID RECORDID,
                    cast(null as uniqueidentifier) as AUDITID,
                    cast(null as uniqueidentifier) as AUDITKEY,
                    BASETABLE.CONSTITUENTID, 
                    cast(0 as tinyint) EDITTYPECODE,
                    BASETABLE.ADDEDBYID CHANGEAGENTID,
                    BASETABLE.DATEADDED CHANGEDATE,
                    ' + replace(@DISPLAYEXPRESSION, '<DISPLAYTABLE>', 'BASETABLE') + ' DISPLAY,
                    1 LASTCHANGE,
                    1 ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + '' - '' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.<SOURCETABLENAME> as BASETABLE
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.CONTEXTRECORDID = BASETABLE.ID and CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                left join dbo.<SOURCETABLENAME>AUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID ' + char(13) +
                replace(@SQLCOMMONJOINS, '<CHANGEAGENT>', 'ADDEDBYID') + char(13) + 
                'where 
                    BASETABLE.DATEADDED = BASETABLE.DATECHANGED and ' + char(13) + 
                    @SQLCOMMONPREDICATES + char(13) +
                'order by BASETABLE.DATEADDED desc'

                set @SQLADDEDRECORDS = replace(replace(@SQLADDEDRECORDS, '<CHANGEDATE>', 'DATEADDED'), '<RECORDID>', 'ID')


                -- Find changes as a result of being edited

                declare @SQLEDITEDRECORDS nvarchar(max) = '
                select
                    BASETABLE.AUDITRECORDID RECORDID,
                    BASETABLE.AUDITID,
                    BASETABLE.AUDITKEY,
                    BASETABLE.CONSTITUENTID, 
                    cast(1 as tinyint) EDITTYPECODE,
                    BASETABLE.AUDITCHANGEAGENTID CHANGEAGENTID,
                    BASETABLE.AUDITDATE CHANGEDATE,
                    ' + @SQLCURRENTVALUEDISPLAY + ',
                    case when [CURRENTEXISTING].ID is not null and AFTER.DATECHANGED = [CURRENTEXISTING].DATECHANGED then 1 else 0 end LASTCHANGE,
                    case when [CURRENTEXISTING].ID is not null then 1 else 0 end ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + '' - '' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from 
                @<SOURCETABLENAME>EDITBASETABLE BASETABLE ' + char(13) + 
                @SQLCURRENTVALUEJOINS + char(13) + '
                inner join dbo.<SOURCETABLENAME>AUDIT AFTER on AFTER.AUDITKEY = BASETABLE.AUDITKEY and AFTER.AUDITRECORDID = BASETABLE.AUDITRECORDID and AFTER.AUDITTYPECODE = 1
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = AFTER.AUDITID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                left join dbo.<SOURCETABLENAME>AUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID'


                set @SQLEDITEDRECORDS = replace(replace(@SQLEDITEDRECORDS, '<CHANGEDATE>', 'AUDITDATE'), '<RECORDID>', 'AUDITRECORDID')


                -- Find changes as a result of being deleted

                declare @SQLDELETEDRECORDS nvarchar(max) = '
                select top(@MAXROWS)
                    BASETABLE.AUDITRECORDID RECORDID,
                    BASETABLE.AUDITID,
                    BASETABLE.AUDITKEY,
                    BASETABLE.CONSTITUENTID,
                    cast(2 as tinyint) EDITTYPECODE,
                    BASETABLE.AUDITCHANGEAGENTID CHANGEAGENTID,
                    BASETABLE.AUDITDATE CHANGEDATE,
                    ' + @SQLCURRENTVALUEDISPLAY + ',
                    case when BASETABLE.AUDITID = [CURRENTDELETED].AUDITID then 1 else 0 end LASTCHANGE,
                    case when [CURRENTEXISTING].ID is not null then 1 else 0 end ROWEXISTS,
                    null,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + '' - '' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.<SOURCETABLENAME>AUDIT as BASETABLE' + char(13) + 
                @SQLCURRENTVALUEJOINS + char(13) + '
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = BASETABLE.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID ' + char(13) +
                replace(@SQLCOMMONJOINS, '<CHANGEAGENT>', 'AUDITCHANGEAGENTID') + char(13) + 
                'where 
                    AUDITTYPECODE = 2 and ' + 
                    @SQLCOMMONPREDICATES + 
                ' order by BASETABLE.AUDITDATE desc'

                set @SQLDELETEDRECORDS = replace(replace(@SQLDELETEDRECORDS, '<CHANGEDATE>', 'AUDITDATE'), '<RECORDID>', 'AUDITRECORDID')


                -- Find changes as a result of being added (after edited)

                declare @SQLORIGINALADD nvarchar(MAX) = '
                select top(@MAXROWS)
                    BASETABLE.AUDITRECORDID RECORDID,
                    BASETABLE.AUDITID,
                    BASETABLE.AUDITKEY,
                    BASETABLE.CONSTITUENTID, 
                    cast(100 as tinyint) EDITTYPECODE,
                    BASETABLE.ADDEDBYID CHANGEAGENTID,
                    BASETABLE.DATEADDED CHANGEDATE,
                    ' + @SQLCURRENTVALUEDISPLAY + ',
                    0 LASTCHANGE,
                    case when [CURRENTEXISTING].ID is not null then 1 else 0 end ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when isnull(CONSTITUENTDATAREVIEWROLLBACK.ID,[ROLLBACKAUDITIDNULL].ID) is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + '' - '' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.<SOURCETABLENAME>AUDIT as BASETABLE ' + char(13) + 
                @SQLCURRENTVALUEJOINS + char(13) + '
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = BASETABLE.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is not null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK [ROLLBACKAUDITIDNULL] on 
                    [ROLLBACKAUDITIDNULL].CONTEXTRECORDID = BASETABLE.AUDITRECORDID and 
                    [ROLLBACKAUDITIDNULL].ROLLBACKAUDITID is null and 
                    BASETABLE.DATEADDED = case when [CURRENTEXISTING].ID is not null then [CURRENTEXISTING].DATEADDED else [CURRENTDELETED].DATEADDED end
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = isnull(CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID,[ROLLBACKAUDITIDNULL].CONSTITUENTDATAREVIEWROLLBACKREASONID)
                left join dbo.<SOURCETABLENAME>AUDIT [ROLLBACK] on [ROLLBACK].AUDITID = isnull(CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID,[ROLLBACKAUDITIDNULL].SOURCEAUDITID) ' + char(13) + 
                replace(@SQLCOMMONJOINS, '<CHANGEAGENT>', 'ADDEDBYID') + 
                'where 
                    BASETABLE.DATEADDED = BASETABLE.DATECHANGED and ' + char(13) + 
                    @SQLCOMMONPREDICATES + char(13) +
                ' order by BASETABLE.DATEADDED desc'

                set @SQLORIGINALADD = replace(replace(@SQLORIGINALADD, '<CHANGEDATE>', 'DATEADDED'), '<RECORDID>', 'AUDITRECORDID')


                -- Combine the four change type queries

                declare @COMBINEDSQL nvarchar(max) = '
                    select top(@MAXROWS)
                        RECORDID,
                        AUDITID,
                        AUDITKEY,
                        CONSTITUENTID,
                        EDITTYPECODE,
                        CHANGEAGENTID,
                        CHANGEDATE,
                        case len(DISPLAY) when 0 then ''' + @DEFAULTDISPLAYTEXT + ''' else DISPLAY end [DISPLAY],
                        LASTCHANGE,
                        ROWEXISTS,
                        ROLLBACKTODATE,
                        ROLLBACKREASON,
                        case EDITTYPECODE 
                            when 0 then ''' + cast(@ADDEDVIEWFORMID as nvarchar(36)) + '''
                            when 1 then ''' + cast(@EDITEDVIEWFORMID as nvarchar(36)) + '''
                            when 2 then ''' + cast(@DELETEDVIEWFORMID as nvarchar(36)) + '''
                            when 100 then ''' + cast(@ORIGINALADDVIEWFORMID as nvarchar(36)) + '''
                        end VIEWFORMID,
                        ''' + @TYPE + ''' as [TYPE]
                    from (' + 
                    @SQLADDEDRECORDS + char(13) + 
                    ' union all ' + 
                    @SQLEDITEDRECORDS + char(13) + 
                    ' union all ' + 
                    @SQLDELETEDRECORDS + char(13) + 
                    ' union all ' + 
                    @SQLORIGINALADD + 
                    ') as DERIVED order by CHANGEDATE desc'

                return replace(@COMBINEDSQL, '<SOURCETABLENAME>', @SOURCETABLENAME)
            end