USP_REPORT_GIVINGLEVELPROGRAM_WITHIDSET

            CREATE procedure dbo.USP_REPORT_GIVINGLEVELPROGRAM_WITHIDSET
            (
                @IDSETID uniqueidentifier = null,
                @GIVINGLEVELPROGRAMID uniqueidentifier = null
            ) with execute as owner as
                set nocount on;

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

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

                declare @SMARTFIELDID uniqueidentifier;
                declare @SMARTFIELDTABLENAME nvarchar(100);

                select @SMARTFIELDID = GIVINGLEVELPROGRAM.SMARTFIELDID
                from dbo.GIVINGLEVELPROGRAM
                where GIVINGLEVELPROGRAM.ID = @GIVINGLEVELPROGRAMID;

                declare @MINIMUMUNIT money;
                select @MINIMUMUNIT =  coalesce(power(10.0000,-CURRENCY.DECIMALDIGITS), 0.01)
                from dbo.CURRENCY where CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @SQLTOEXEC nvarchar(max);

                if @SMARTFIELDID is not null begin
                    if not exists(select ID from dbo.SMARTFIELD where ID = @SMARTFIELDID) raiserror('Smart field does not exist in the database.', 15, 1);

                    select @SMARTFIELDTABLENAME = TABLENAME
                    from dbo.TABLECATALOG
                    inner join dbo.SMARTFIELD on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
                    where SMARTFIELD.ID = @SMARTFIELDID;
                end                
                set @SQLTOEXEC = N'set nocount on;

                with
                    GIVINGLEVELPROGRAMINNER_CTE as (
                        select
                            GLP.[NAME] PROGRAMNAME,
                            GLPL.[NAME] LEVELNAME,
                            GLPL.[MINIMUMAMOUNT] MINAMOUNT,
                            ROW_NUMBER() over (order by GLPL.[MINIMUMAMOUNT]) ROWNUMBER
                        from
                            dbo.[GIVINGLEVELPROGRAM] GLP
                        inner join
                            dbo.[GIVINGLEVELPROGRAMLEVEL] GLPL
                        on
                            GLPL.[GIVINGLEVELPROGRAMID] = GLP.[ID]
                        where
                            GLP.[ID] = @GIVINGLEVELPROGRAMID),
                    GIVINGLEVELPROGRAM_CTE as (
                        select
                            GIVINGLEVELPROGRAMINNER_CTE.[PROGRAMNAME],
                            GIVINGLEVELPROGRAMINNER_CTE.[LEVELNAME],
                            GIVINGLEVELPROGRAMINNER_CTE.[MINAMOUNT],
                            (select [MINAMOUNT] - @MINIMUMUNIT from GIVINGLEVELPROGRAMINNER_CTE SUB where SUB.[ROWNUMBER] = GIVINGLEVELPROGRAMINNER_CTE.[ROWNUMBER] + 1) MAXAMOUNT
                        from
                            GIVINGLEVELPROGRAMINNER_CTE),
                    CONSTITUENTREVENUE_CTE as (
                        select
                            ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),C.[ID]) ID,
                            C.[KEYNAME],
                            NF.[NAME],
                            SF.[VALUE] AMOUNT
                        from
                            dbo.[CONSTITUENT] C with (nolock)
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                            ' + nchar(13);

                set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @SMARTFIELDTABLENAME + ' as SF on C.[ID] = SF.[ID]' + nchar(13);

                if @IDSETID is not null begin
                    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on C.[ID] = SELECTION.[ID]' + nchar(13);
                end

                set @SQLTOEXEC = @SQLTOEXEC + N'inner join
                            GIVINGLEVELPROGRAMINNER_CTE
                        on
                            GIVINGLEVELPROGRAMINNER_CTE.ROWNUMBER = 1
                        group by
                            C.[ID], C.[KEYNAME], NF.[NAME], GIVINGLEVELPROGRAMINNER_CTE.[MINAMOUNT], SF.[VALUE]
                        having
                            SF.[VALUE] >= GIVINGLEVELPROGRAMINNER_CTE.[MINAMOUNT])
                        select
                            GIVINGLEVELPROGRAM_CTE.PROGRAMNAME,
                            GIVINGLEVELPROGRAM_CTE.LEVELNAME,
                            GIVINGLEVELPROGRAM_CTE.MINAMOUNT,
                            GIVINGLEVELPROGRAM_CTE.MAXAMOUNT,
                            CONSTITUENTREVENUE_CTE.ID CONSTITUENTID,
                            CONSTITUENTREVENUE_CTE.KEYNAME CONSTITUENTKEYNAME,
                            CONSTITUENTREVENUE_CTE.NAME CONSTITUENTNAME,
                            CONSTITUENTREVENUE_CTE.AMOUNT
                        from
                            GIVINGLEVELPROGRAM_CTE
                        left join
                            CONSTITUENTREVENUE_CTE
                        on
                            CONSTITUENTREVENUE_CTE.AMOUNT >= GIVINGLEVELPROGRAM_CTE.MINAMOUNT and
                            (CONSTITUENTREVENUE_CTE.AMOUNT <= GIVINGLEVELPROGRAM_CTE.MAXAMOUNT or
                            GIVINGLEVELPROGRAM_CTE.MAXAMOUNT is null)
                        order by
                            GIVINGLEVELPROGRAM_CTE.MINAMOUNT, CONSTITUENTREVENUE_CTE.KEYNAME, CONSTITUENTREVENUE_CTE.NAME;';

                        exec sp_executesql @SQLTOEXEC, N'@GIVINGLEVELPROGRAMID uniqueidentifier, @MINIMUMUNIT money',@GIVINGLEVELPROGRAMID=@GIVINGLEVELPROGRAMID,@MINIMUMUNIT=@MINIMUMUNIT;