UFN_SMARTQUERY_DONORLIST

                create function dbo.UFN_SMARTQUERY_DONORLIST
                (
                    @DESIGNATIONID uniqueidentifier = null,
                    @APPEALID uniqueidentifier = null,
                    @SELECTIONID uniqueidentifier = null,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null,
                    @AMOUNTTYPE tinyint = null,
                    @AMOUNTONE money = null,
                    @AMOUNTTWO money = null,
                    @ONLYRETURNTOPDONORS bit = null,
                    @RETURNVALUE int = null,
                    @RETURNVALUEISPERCENT bit = null,
                    @CAMPAIGNID uniqueidentifier = null,
                    @CURRENCYID uniqueidentifier = null,
                    @CURRENCYCODE tinyint = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @MAXROWS int = null
                )
                returns @RETURNTABLE table
                    (
                        ID uniqueidentifier,
                        NAME nvarchar(154),
                        ADDRESSBLOCK nvarchar(150),
                        CITY nvarchar(50),
                        STATE nvarchar(50),
                        POSTCODE nvarchar(12),
                        TOTALGIVEN money,
                        TOTALRECEIVED money,
                        NUMBEROFGIFTS int,
                        LOOKUPID nvarchar(100),
                        CURRENCYID uniqueidentifier
                    )
                as
                begin

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

                    declare @ORGANIZATIONCURRENCYID as uniqueidentifier
                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

                    set @CURRENCYID = case @CURRENCYCODE 
                        when 0 then dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
                        else @ORGANIZATIONCURRENCYID
                    end

                    declare @CAMPAIGNHIERARCHYPATH hierarchyid;
                    select @CAMPAIGNHIERARCHYPATH = HIERARCHYPATH from CAMPAIGN where ID=@CAMPAIGNID;

                    -- Build list table of all revenue records that need to be included

                    declare @DATASET table
                    (
                        CONSTITUENTID uniqueidentifier,
                        TOTALGIVEN money,
                        WRITEOFF money,
                        TOTALRECEIVED money,
                        NUMBEROFGIFTS int
                    );

                    -- Roll up designations

                    declare @DL1ID uniqueidentifier;
                    declare @DL2ID uniqueidentifier;
                    declare @DL3ID uniqueidentifier;
                    declare @DL4ID uniqueidentifier;
                    declare @DL5ID uniqueidentifier;

                    select @DL1ID = DESIGNATIONLEVEL1ID,
                        @DL2ID = DESIGNATIONLEVEL2ID,
                        @DL3ID = DESIGNATIONLEVEL3ID,
                        @DL4ID = DESIGNATIONLEVEL4ID,
                        @DL5ID = DESIGNATIONLEVEL5ID
                    from dbo.DESIGNATION
                    where ID = @DESIGNATIONID or @DESIGNATIONID is null;

                    if @SELECTIONID is null
                    begin
                        with DESIGNATIONS_CTE as 
                        (
                            select 
                                ID 
                            from 
                                dbo.DESIGNATION D
                            where 
                                D.DESIGNATIONLEVEL1ID = @DL1ID and
                                (D.DESIGNATIONLEVEL2ID = @DL2ID or (@DL2ID is null and D.DESIGNATIONLEVEL2ID is null)) and 
                                (D.DESIGNATIONLEVEL3ID = @DL3ID or (@DL3ID is null and D.DESIGNATIONLEVEL3ID is null)) and 
                                (D.DESIGNATIONLEVEL4ID = @DL4ID or (@DL4ID is null and D.DESIGNATIONLEVEL4ID is null)) and 
                                (D.DESIGNATIONLEVEL5ID = @DL5ID or (@DL5ID is null and D.DESIGNATIONLEVEL5ID is null))
                        ),
                        REVENUE_CTE as
                        (
                            select
                                FINANCIALTRANSACTION.ID,
                                FINANCIALTRANSACTION.CONSTITUENTID,
                                REVENUE_EXT.APPEALID,
                                FINANCIALTRANSACTION.DATE,
                                FINANCIALTRANSACTION.TYPECODE
                            from dbo.FINANCIALTRANSACTION 
                            inner join dbo.REVENUE_EXT
                                on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            where
                                FINANCIALTRANSACTION.DELETEDON is null and
                                (@ISADMIN = 1 or
                                    (
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.CONSTITUENTID) = 1)
                                        and
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.CONSTITUENTID) = 1)
                                        and
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.ID) = 1)
                                    ))
                        )
                        insert into @DATASET
                        (CONSTITUENTID,TOTALGIVEN,WRITEOFF,TOTALRECEIVED,NUMBEROFGIFTS)
                        (
                            select
                                R.CONSTITUENTID,
                                sum(case 
                                        when dbo.UFN_REVENUE_HASDESIGNATION(R.TYPECODE, RS.APPLICATIONCODE) = 1 and not (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 1) 
                                            then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        else null 
                                    end
                                ) as TOTALGIVEN,
                                sum(WS.WRITEOFFAMOUNT) as WRITEOFF,
                                sum(case 
                                        when R.TYPECODE = 0 and (RS.APPLICATIONCODE in (0,2,3,4,6,7) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)) then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        when R.TYPECODE = 5 and RS.APPLICATIONCODE = 0 then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        else null 
                                    end
                                ) as TOTALRECEIVED,
                                count(distinct case when dbo.UFN_REVENUE_HASDESIGNATION(R.TYPECODE, RS.APPLICATIONCODE) = 1 and not (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 1) then R.ID else null end) as NUMBEROFGIFTS
                            from
                                REVENUE_CTE as R
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = R.ID
                                inner join dbo.REVENUESPLIT_EXT RS on FTLI.ID = RS.ID
                                inner join DESIGNATIONS_CTE D on D.ID = RS.DESIGNATIONID or @DESIGNATIONID is null
                                left join dbo.REVENUESPLITCAMPAIGN on FTLI.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID and (@CAMPAIGNID is not null)
                                left join (select W.REVENUEID, sum(WRITEOFFSPLIT_INCURRENCY.AMOUNTINCURRENCY) as WRITEOFFAMOUNT 
                                    from dbo.WRITEOFFSPLIT 
                                    inner join dbo.WRITEOFF W on W.ID = WRITEOFFSPLIT.WRITEOFFID
                                    inner join UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, 2, 0) WRITEOFFSPLIT_INCURRENCY on WRITEOFFSPLIT_INCURRENCY.ID = WRITEOFFSPLIT.ID
                                    group by W.REVENUEID) WS on WS.REVENUEID = R.ID
                                left join (select ID RSEID, AMOUNTINCURRENCY from UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, 2, 0)) RSE_INCURRENCY on RS.ID = RSE_INCURRENCY.RSEID
                                left join dbo.APPEAL AP on AP.ID = R.APPEALID
                                left join CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                            where
                                FTLI.DELETEDON is null and FTLI.TYPECODE != 1 and
                                (R.TYPECODE <> 3) and -- exclude matching gift claims

                                (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                (AP.ID = @APPEALID or @APPEALID is null) and
                                (@CAMPAIGNID is null or CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH) = 1)
                            group by R.CONSTITUENTID
                        );
                    end
                    else
                    begin
                        with DESIGNATIONS_CTE as 
                        (
                            select 
                                ID 
                            from 
                                dbo.DESIGNATION D
                            where 
                                D.DESIGNATIONLEVEL1ID = @DL1ID and
                                (D.DESIGNATIONLEVEL2ID = @DL2ID or (@DL2ID is null and D.DESIGNATIONLEVEL2ID is null)) and 
                                (D.DESIGNATIONLEVEL3ID = @DL3ID or (@DL3ID is null and D.DESIGNATIONLEVEL3ID is null)) and 
                                (D.DESIGNATIONLEVEL4ID = @DL4ID or (@DL4ID is null and D.DESIGNATIONLEVEL4ID is null)) and 
                                (D.DESIGNATIONLEVEL5ID = @DL5ID or (@DL5ID is null and D.DESIGNATIONLEVEL5ID is null))
                        ),
                        REVENUE_CTE as
                        (
                            select
                                FINANCIALTRANSACTION.ID,
                                FINANCIALTRANSACTION.CONSTITUENTID,
                                REVENUE_EXT.APPEALID,
                                FINANCIALTRANSACTION.DATE,
                                FINANCIALTRANSACTION.TYPECODE
                            from dbo.FINANCIALTRANSACTION 
                            inner join dbo.REVENUE_EXT
                                on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            where
                                FINANCIALTRANSACTION.DELETEDON is null and
                                (@ISADMIN = 1 or
                                    (
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.CONSTITUENTID) = 1)
                                        and
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.CONSTITUENTID) = 1)
                                        and
                                        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '8bf052d7-7b2b-41b9-9f0f-52d04c02b62b', FINANCIALTRANSACTION.ID) = 1)
                                    ))
                        )
                        insert into @DATASET
                        (CONSTITUENTID,TOTALGIVEN,WRITEOFF,TOTALRECEIVED,NUMBEROFGIFTS)
                        (
                            select
                                R.CONSTITUENTID,
                                sum(case 
                                        when dbo.UFN_REVENUE_HASDESIGNATION(R.TYPECODE, RS.APPLICATIONCODE) = 1 and not (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 1) 
                                            then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        else null 
                                    end
                                ) as TOTALGIVEN,
                                sum(WS.WRITEOFFAMOUNT) as WRITEOFF,
                                sum(case 
                                        when R.TYPECODE = 0 and (RS.APPLICATIONCODE in (0,2,3,4,6,7) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)) then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        when R.TYPECODE = 5 and RS.APPLICATIONCODE = 0 then RSE_INCURRENCY.AMOUNTINCURRENCY
                                        else null 
                                    end
                                ) as TOTALRECEIVED,
                                count(distinct case when dbo.UFN_REVENUE_HASDESIGNATION(R.TYPECODE, RS.APPLICATIONCODE) = 1 and not (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 1) then R.ID else null end) as NUMBEROFGIFTS
                            from
                                REVENUE_CTE as R
                                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on R.CONSTITUENTID = SELECTION.ID and @SELECTIONID is not null
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = R.ID
                                inner join dbo.REVENUESPLIT_EXT RS on FTLI.ID = RS.ID
                                inner join DESIGNATIONS_CTE D on D.ID = RS.DESIGNATIONID or @DESIGNATIONID is null
                                left join dbo.REVENUESPLITCAMPAIGN on FTLI.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID and (@CAMPAIGNID is not null)
                                left join (select W.REVENUEID, sum(WRITEOFFSPLIT_INCURRENCY.AMOUNTINCURRENCY) as WRITEOFFAMOUNT 
                                    from dbo.WRITEOFFSPLIT 
                                    inner join dbo.WRITEOFF W on W.ID = WRITEOFFSPLIT.WRITEOFFID
                                    inner join UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, 2, 0) WRITEOFFSPLIT_INCURRENCY on WRITEOFFSPLIT_INCURRENCY.ID = WRITEOFFSPLIT.ID
                                    group by W.REVENUEID) WS on WS.REVENUEID = R.ID     
                                left join (select ID RSEID, AMOUNTINCURRENCY from UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, 2, 0)) RSE_INCURRENCY on RS.ID = RSE_INCURRENCY.RSEID
                                left join dbo.APPEAL AP on AP.ID = R.APPEALID
                                left join CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                            where
                                FTLI.DELETEDON is null and FTLI.TYPECODE != 1 and
                                (R.TYPECODE <> 3) and -- exclude matching gift claims

                                (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                (AP.ID = @APPEALID or @APPEALID is null) and
                                (@CAMPAIGNID is null or CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH) = 1)
                            group by 
                                R.CONSTITUENTID
                        );
                    end

                    -- Calculate number of rows returned

                    if @ONLYRETURNTOPDONORS = 1
                    begin
                        if @RETURNVALUEISPERCENT = 1
                        begin
                            select
                                @MAXROWS = ceiling(count(distinct CONSTITUENTID) * (convert(decimal,@RETURNVALUE) / 100.00))
                            from
                                @DATASET;
                        end
                        else
                            set @MAXROWS = @RETURNVALUE;
                    end;

                    -- Insert filtered records into the return table

                    insert into @RETURNTABLE
                    select top(@MAXROWS) with ties C.ID,
                        C.NAME,
                        A.ADDRESSBLOCK,
                        A.CITY,
                        dbo.UFN_STATE_GETABBREVIATION(A.STATEID),
                        A.POSTCODE,
                        DS.TOTALGIVEN - coalesce(DS.WRITEOFF, 0),
                        DS.TOTALRECEIVED,
                        DS.NUMBEROFGIFTS,
                        C.LOOKUPID,
                        @CURRENCYID
                    from
                        @DATASET DS
                        inner join dbo.CONSTITUENT C on C.ID = DS.CONSTITUENTID
                        left join dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                    where 
                        (DS.TOTALGIVEN - coalesce(DS.WRITEOFF, 0) > @AMOUNTONE and @AMOUNTTYPE = 0) or
                        (DS.TOTALGIVEN - coalesce(DS.WRITEOFF, 0) < @AMOUNTONE and @AMOUNTTYPE = 1) or
                        (DS.TOTALGIVEN - coalesce(DS.WRITEOFF, 0) between @AMOUNTONE and @AMOUNTTWO and @AMOUNTTYPE = 2)
                    order by (DS.TOTALGIVEN - coalesce(DS.WRITEOFF, 0)) desc;

                    return;
                end;