USP_DATALIST_FUNDINGREQUESTSUMMARYDASHBOARD

Returns a summary of funding requests for use by the dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATEFILTER	tinyint	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@INCLUDENODATE	bit	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_FUNDINGREQUESTSUMMARYDASHBOARD
                (
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @DATEFILTER tinyint,
                    @STARTDATE datetime = null,
                    @ENDDATE datetime = null, 
                    @INCLUDENODATE bit = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER, @STARTDATE output, @ENDDATE output;

                    declare @FUNDINGREQUEST table (ID uniqueidentifier, AMOUNT money);

                    declare @BASECURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;

          select @DECIMALDIGITS = DECIMALDIGITS, 
                 @ROUNDINGTYPECODE = ROUNDINGTYPECODE 
          from dbo.CURRENCY where ID = @BASECURRENCYID

                    if @ORGPOSITIONSSELECTIONID is null
                    begin
                        insert into @FUNDINGREQUEST
                            (ID, AMOUNT)
                        select
                            FUNDINGREQUEST.ID,
                            FRB.AMOUNTINCURRENCY
                        from dbo.FUNDINGREQUEST
            left join dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY_BULK(@BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS, @ROUNDINGTYPECODE) FRB
            on FRB.ID = FUNDINGREQUEST.ID
                        inner join dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
                        where FUNDINGPLAN.INACTIVE = 0
                            and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[FUNDINGPLAN].[SITEID] or (SITEID is null and [FUNDINGPLAN].[SITEID] is null)))                            
                            and (
                                (@INCLUDENODATE = 1 and FUNDINGREQUEST.DATESUBMITTED is null)
                                or (
                                    FUNDINGREQUEST.DATESUBMITTED >= @STARTDATE
                                    and FUNDINGREQUEST.DATESUBMITTED <= @ENDDATE
                                )
                            )
                    end
                    else                    
                    begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

                        declare @TODAY datetime;
                        set @TODAY = getdate();

                        insert into @FUNDINGREQUEST
                            (ID, AMOUNT)
                        select
                            FUNDINGREQUEST.ID,
                            FRB.AMOUNTINCURRENCY
                        from dbo.FUNDINGREQUEST
            left join dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY_BULK(@BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS, @ROUNDINGTYPECODE) FRB
            on FRB.ID = FUNDINGREQUEST.ID
                            inner join dbo.ORGANIZATIONPOSITIONHOLDER 
                                on ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = FUNDINGREQUEST.PRIMARYMANAGERID 
                                    and @TODAY between ORGANIZATIONPOSITIONHOLDER.DATEFROM and coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @TODAY)
                            inner join @IDS as SELECTION on ORGANIZATIONPOSITIONHOLDER.ID = SELECTION.ID
                            inner join dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
                        where FUNDINGPLAN.INACTIVE = 0
                            and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[FUNDINGPLAN].[SITEID] or (SITEID is null and [FUNDINGPLAN].[SITEID] is null)))                            
                            and (
                                (@INCLUDENODATE = 1 and FUNDINGREQUEST.DATESUBMITTED is null)
                                or (
                                    FUNDINGREQUEST.DATESUBMITTED >= @STARTDATE
                                    and FUNDINGREQUEST.DATESUBMITTED <= @ENDDATE
                                )
                            )
                    end

                    declare @AMOUNTREQUESTED money
                    declare @REQUESTEDCOUNT int
                    select @AMOUNTREQUESTED = sum(coalesce(AMOUNT,0)), @REQUESTEDCOUNT = count(ID)
                    from @FUNDINGREQUEST 

                    declare @AMOUNTAWARDED money
                    select @AMOUNTAWARDED = sum(coalesce(RB.AMOUNTINCURRENCY,0))
                    from dbo.REVENUE           
                        inner join dbo.REVENUEFUNDINGREQUEST on REVENUEFUNDINGREQUEST.ID = REVENUE.ID
                        inner join @FUNDINGREQUEST FUNDINGREQUEST on FUNDINGREQUEST.ID = REVENUEFUNDINGREQUEST.FUNDINGREQUESTID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS, @ROUNDINGTYPECODE) RB on RB.ID = REVENUE.ID

                    declare @AWARDEDCOUNT int
                    select @AWARDEDCOUNT = count(ID)
                    from @FUNDINGREQUEST FUNDINGREQUEST
                    where exists(
                        select ID
                        from dbo.REVENUEFUNDINGREQUEST
                        where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                    )

                    select coalesce(@AMOUNTREQUESTED, 0), @REQUESTEDCOUNT, coalesce(@AMOUNTAWARDED, 0), @AWARDEDCOUNT, @BASECURRENCYID