UFN_SMARTQUERY_RECOGNITION_SYBUNT_DONORS

                create function dbo.UFN_SMARTQUERY_RECOGNITION_SYBUNT_DONORS
                (
                    @THISYEAR_DATERANGE tinyint=0, --0=This calendar year , 1=NMonths, 2=Specific range

                    @THISYEAR_NMONTHS tinyint=12,
                    @THISYEAR_STARTDATE datetime=null,
                    @THISYEAR_ENDDATE datetime=null,
                    @SELECTIONID uniqueidentifier=null,
                    @CURRENTAPPUSERID uniqueidentifier=null,
                    @MAXROWS int=500
                )
                returns @T table 
                (
                    ID uniqueidentifier not null, 
                    LASTRECOGNITIONDATE datetime,
                    NAME nvarchar(154),
                    ADDRESSBLOCK nvarchar(150),
                    CITY nvarchar(50),
                    STATE nvarchar(50),
                    POSTCODE nvarchar(12),
                    LOOKUPID nvarchar(36)
                )

                as

                begin

                    declare @ISADMIN bit;
                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    declare @ids table (ID uniqueidentifier PRIMARY KEY);

                    IF @SELECTIONID IS NOT NULL
                    BEGIN
                        insert into @ids
                            select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID);
                    END


                    IF (@THISYEAR_DATERANGE IS NULL) OR (@THISYEAR_DATERANGE NOT IN (0,1,2))
                        SET @THISYEAR_DATERANGE =0;

                    DECLARE @NOW datetime;
                    set @NOW=GETDATE();

                    --Calculate date range

                    IF @THISYEAR_DATERANGE = 0
                    BEGIN

                        SET @THISYEAR_STARTDATE=dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@NOW,0);
                        SET @THISYEAR_ENDDATE=dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@NOW,1);

                    END

                    ELSE IF @THISYEAR_DATERANGE = 1
                    BEGIN

                        SET @THISYEAR_ENDDATE=dbo.UFN_DATE_GETLATESTTIME(@NOW);


                        SET @THISYEAR_STARTDATE=dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month,-@THISYEAR_NMONTHS, @NOW));

                    END    

                    ELSE IF @THISYEAR_DATERANGE = 2        
                    BEGIN
                        IF @THISYEAR_STARTDATE IS NULL AND @THISYEAR_ENDDATE IS NULL RETURN;

                        IF @THISYEAR_STARTDATE  IS NULL SET @THISYEAR_STARTDATE = '1900-01-01';

                        IF @THISYEAR_ENDDATE  IS NULL SET @THISYEAR_ENDDATE = '2900-01-01';

                        SET @THISYEAR_STARTDATE =dbo.UFN_DATE_GETEARLIESTTIME(@THISYEAR_STARTDATE);

                        SET @THISYEAR_ENDDATE =dbo.UFN_DATE_GETLATESTTIME(@THISYEAR_ENDDATE);

                    END;                    

                    WITH RECOGNITION_CTE as
                    (
                        select 
                            REVENUERECOGNITION.EFFECTIVEDATE,
                            REVENUERECOGNITION.CONSTITUENTID
                        from 
                            dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) REVENUERECOGNITION
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUERECOGNITION.REVENUESPLITID) REVSITES
                        where
                            (
                                dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                                exists (
                                        select 
                                            1 
                                        from 
                                            dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '0104FFD1-A51F-482e-B7D9-ACAECC5DF539', 9) 
                                        where 
                                            SITEID=REVSITES.SITEID or 
                                            (
                                                SITEID is null and REVSITES.SITEID is null
                                            )
                                        )
                            )
                    )
                    insert into @T                     
                        select top (@MAXROWS) 
                            C.ID,                             
                            (SELECT MAX([EFFECTIVEDATE]) as LGD from RECOGNITION_CTE AS RR WHERE RR.CONSTITUENTID = C.ID) AS LASTRECOGNITIONDATE,
                            C.NAME,
                            A.ADDRESSBLOCK,
                            A.CITY,
                            STATE.DESCRIPTION,
                            A.POSTCODE,
                            C.LOOKUPID
                        from                         
                            dbo.CONSTITUENT AS C WITH (NOLOCK) 
                            left outer join dbo.ADDRESS AS A WITH (NOLOCK) on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                            left outer join dbo.STATE on STATE.ID = A.STATEID                                                                                                                        
                        WHERE                            
                            --Not this year

                            not exists (
                                select 1 from RECOGNITION_CTE as RR
                                where RR.[EFFECTIVEDATE] between @THISYEAR_STARTDATE and @THISYEAR_ENDDATE
                                and RR.CONSTITUENTID = C.ID
                            )
                            AND            
                            --Some Year

                            C.ID IN             
                            (
                                    select CONSTITUENTID from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) as R
                            )                            
                            AND                            
                            (@SELECTIONID IS NULL OR  C.ID IN (SELECT ID FROM @ids))                            
                            AND
                            (@ISADMIN = 1 or 
                                (
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '0104FFD1-A51F-482e-B7D9-ACAECC5DF539', C.ID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '0104FFD1-A51F-482e-B7D9-ACAECC5DF539', C.ID) = 1)
                                )
                            )                                            
                        ORDER BY 
                            LASTRECOGNITIONDATE DESC
                        OPTION 
                            (OPTIMIZE FOR (@THISYEAR_STARTDATE='20050101',@THISYEAR_ENDDATE='20051231')    );

                    return;
                end;