USP_DATALIST_FUNDRAISERS

CREATE procedure dbo.USP_DATALIST_FUNDRAISERS 
(
    @CURRENTAPPUSERID uniqueidentifier,
    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
    @INCLUDESTEWARDSHIP bit = 0,
    @SHOWINACTIVE bit = 0,
    @ONLYOWNEDINTERACTIONS bit = 0,
    @MAXROWS int = null
)  
as begin
    set nocount on;
    declare @TODAY datetime;
    set @TODAY = getdate();

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();
    declare @UPPERBOUND datetime;
    set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
    declare @LOWERBOUND datetime;
    set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);

    declare @ASSIGNMENTS int = 0;

    -- AdamBu - 11/12/09 - The average calculation should be using the number of plans assigned to fundraisers, not the total number of all plans

    select @ASSIGNMENTS = sum(coalesce(COUNT,0)) 
    from dbo.UFN_PLANCOUNTBYFUNDRAISER()


    declare @AVERAGE int;
    declare @FUNDRAISERS int;
    select @FUNDRAISERS = count(distinct fdr.CONSTITUENTID) 
                            from 
                            dbo.FUNDRAISERDATERANGE fdr
                            where (fdr.DATEFROM <= @UPPERBOUND or fdr.DATEFROM is null) 
                            and (fdr.DATETO >= @LOWERBOUND or fdr.DATETO is null);

    if @FUNDRAISERS>0
        set @AVERAGE = round(convert(float,@ASSIGNMENTS) / @FUNDRAISERS, 0);

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

    if @ORGPOSITIONSSELECTIONID is null
    begin

    if @MAXROWS is not null
        set ROWCOUNT @MAXROWS

        select
            CONSTITUENT.ID, 
            NF.NAME, 
            coalesce(PLANCOUNT.COUNT,0), 
            '     ' + 
                case when coalesce(PLANCOUNT.COUNT,0)<@AVERAGE then 
                    convert(varchar(10),@AVERAGE-coalesce(PLANCOUNT.COUNT,0)) + ' under'
                when coalesce(PLANCOUNT.COUNT,0)>@AVERAGE then 
                    convert(varchar(10),coalesce(PLANCOUNT.COUNT,0)-@AVERAGE) + ' over'
                else
                    'even'
                end
            DIFF,
            coalesce(P7.COUNT,0), 
            coalesce(P30.COUNT,0), 
            coalesce(C7.COUNT,0), 
            coalesce(C30.COUNT,0)
        from 
            dbo.CONSTITUENT
            inner join dbo.FUNDRAISERDATERANGE on FUNDRAISERDATERANGE.CONSTITUENTID = CONSTITUENT.ID and
                (@SHOWINACTIVE = 1 or 
                    (FUNDRAISERDATERANGE.DATEFROM <= @UPPERBOUND or FUNDRAISERDATERANGE.DATEFROM is null) and
                    (FUNDRAISERDATERANGE.DATETO >= @LOWERBOUND or FUNDRAISERDATERANGE.DATETO is null))
            left outer join dbo.UFN_PLANCOUNTBYFUNDRAISER() PLANCOUNT on CONSTITUENT.ID=PLANCOUNT.ID
            left outer join dbo.UFN_STEPSUMMARY_PLANNED_4  (dateadd(day,   7, @LOWERBOUND), 0, @LOWERBOUND, @INCLUDESTEWARDSHIP) P7  on CONSTITUENT.ID=P7.ID
            left outer join dbo.UFN_STEPSUMMARY_PLANNED_4  (dateadd(day,  30, @LOWERBOUND), 0, @LOWERBOUND, @INCLUDESTEWARDSHIP) P30 on CONSTITUENT.ID=P30.ID
            left outer join dbo.UFN_STEPSUMMARY_COMPLETED_3(dateadd(day,  -7, @UPPERBOUND), @INCLUDESTEWARDSHIP, 0, @UPPERBOUND) C7  on CONSTITUENT.ID=C7.ID
            left outer join dbo.UFN_STEPSUMMARY_COMPLETED_3(dateadd(day, -30, @UPPERBOUND), @INCLUDESTEWARDSHIP, 0, @UPPERBOUND) C30 on CONSTITUENT.ID=C30.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where
            (
                @SHOWINACTIVE = 1
                or CONSTITUENT.ISINACTIVE = 0
            )
            and (
                @ISSYSADMIN = 1
                or dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'364848B8-02F7-4b3f-A7AD-689C374ECF92',CONSTITUENT.ID) = 1
            )

        order by 
            CONSTITUENT.KEYNAME, NF.NAME;

        set rowcount 0;
    end
    else
    begin
        declare @IDS as table(ID uniqueidentifier);
        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

        if @MAXROWS is not null
            set ROWCOUNT @MAXROWS

        select 
            CONSTITUENT.ID, 
            NF.NAME, 
            coalesce(PLANCOUNT.COUNT,0), 
            '     ' + 
                case when coalesce(PLANCOUNT.COUNT,0)<@AVERAGE then 
                    convert(varchar(10),@AVERAGE-coalesce(PLANCOUNT.COUNT,0)) + ' under'
                when coalesce(PLANCOUNT.COUNT,0)>@AVERAGE then 
                    convert(varchar(10),coalesce(PLANCOUNT.COUNT,0)-@AVERAGE) + ' over'
                else
                    'even'
                end
            DIFF,
            coalesce(P7.COUNT,0), 
            coalesce(P30.COUNT,0), 
            coalesce(C7.COUNT,0), 
            coalesce(C30.COUNT,0)
        from 
            dbo.CONSTITUENT
            inner join dbo.FUNDRAISERDATERANGE on FUNDRAISERDATERANGE.CONSTITUENTID = CONSTITUENT.ID and
                (
                    @SHOWINACTIVE = 1 or 
                    (FUNDRAISERDATERANGE.DATEFROM <= @UPPERBOUND or FUNDRAISERDATERANGE.DATEFROM is null) and
                    (FUNDRAISERDATERANGE.DATETO >= @LOWERBOUND or FUNDRAISERDATERANGE.DATETO is null)
                )
            left outer join dbo.UFN_PLANCOUNTBYFUNDRAISER() PLANCOUNT on CONSTITUENT.ID=PLANCOUNT.ID
            left outer join dbo.UFN_STEPSUMMARY_PLANNED_4  (dateadd(day,   7, @LOWERBOUND), @ONLYOWNEDINTERACTIONS, @LOWERBOUND, @INCLUDESTEWARDSHIP) P7  on CONSTITUENT.ID=P7.ID
            left outer join dbo.UFN_STEPSUMMARY_PLANNED_4  (dateadd(day,  30, @LOWERBOUND), @ONLYOWNEDINTERACTIONS, @LOWERBOUND, @INCLUDESTEWARDSHIP) P30 on CONSTITUENT.ID=P30.ID
            left outer join dbo.UFN_STEPSUMMARY_COMPLETED_3(dateadd(day,  -7, @UPPERBOUND), @INCLUDESTEWARDSHIP, @ONLYOWNEDINTERACTIONS, @UPPERBOUND) C7  on CONSTITUENT.ID=C7.ID
            left outer join dbo.UFN_STEPSUMMARY_COMPLETED_3(dateadd(day, -30, @UPPERBOUND), @INCLUDESTEWARDSHIP, @ONLYOWNEDINTERACTIONS, @UPPERBOUND) C30 on CONSTITUENT.ID=C30.ID
            inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = CONSTITUENT.ID and @TODAY between OPH.DATEFROM and coalesce(OPH.DATETO, @TODAY)
            inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where
            (
                @SHOWINACTIVE = 1
                or CONSTITUENT.ISINACTIVE = 0
            )
            and (
                @ISSYSADMIN = 1
                or dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'364848B8-02F7-4b3f-A7AD-689C374ECF92',CONSTITUENT.ID) = 1
            )
        order by 
            CONSTITUENT.KEYNAME, NF.NAME;

        set rowcount 0;
    end                                              
end;