USP_DATALIST_FUNDINGREQUESTSFORFUNDRAISERDASHBOARD

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

Parameter	Parameter Type	Mode	Description
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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_FUNDINGREQUESTSFORFUNDRAISERDASHBOARD
                (
                    @FUNDRAISERID uniqueidentifier,
                    @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 @AMOUNTREQUESTED money
                    declare @AMOUNTAWARDED money

          declare @CURRENCYID uniqueidentifier;
          select @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(APPUSER.ID) from dbo.APPUSER where APPUSER.CONSTITUENTID = @FUNDRAISERID;
          if @CURRENCYID is null
            set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;

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

                    select @AMOUNTREQUESTED = sum(coalesce(FRB.AMOUNTINCURRENCY,0))
                    from dbo.FUNDINGREQUEST
            left join dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY_BULK(@CURRENCYID, 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 (
                            FUNDINGREQUEST.PRIMARYMANAGERID = @FUNDRAISERID
                            or FUNDINGREQUEST.SECONDARYMANAGERID = @FUNDRAISERID
                            or @FUNDRAISERID in(
                                select SPONSORID
                                from dbo.FUNDINGREQUESTSPONSOR
                                where FUNDINGREQUESTSPONSOR.FUNDINGREQUESTID = FUNDINGREQUEST.ID
                            )
                        )
                        and (
                            (@INCLUDENODATE = 1 and FUNDINGREQUEST.DATESUBMITTED is null)
                            or (
                                FUNDINGREQUEST.DATESUBMITTED >= @STARTDATE
                                and FUNDINGREQUEST.DATESUBMITTED <= @ENDDATE
                            )
                        )

                    select @AMOUNTAWARDED = sum(coalesce(RB.AMOUNTINCURRENCY,0))
                    from dbo.REVENUE
                        inner join dbo.REVENUEFUNDINGREQUEST on REVENUEFUNDINGREQUEST.ID = REVENUE.ID
                        inner join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = REVENUEFUNDINGREQUEST.FUNDINGREQUESTID
                        inner join dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
            left join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS, @ROUNDINGTYPECODE) RB on RB.ID = REVENUE.ID
                    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 (
                            FUNDINGREQUEST.PRIMARYMANAGERID = @FUNDRAISERID
                            or FUNDINGREQUEST.SECONDARYMANAGERID = @FUNDRAISERID
                            or @FUNDRAISERID in(
                                select SPONSORID
                                from dbo.FUNDINGREQUESTSPONSOR
                                where FUNDINGREQUESTSPONSOR.FUNDINGREQUESTID = FUNDINGREQUEST.ID
                            )
                        )
                        and (
                            (@INCLUDENODATE = 1 and FUNDINGREQUEST.DATESUBMITTED is null)
                            or (
                                FUNDINGREQUEST.DATESUBMITTED >= @STARTDATE
                                and FUNDINGREQUEST.DATESUBMITTED <= @ENDDATE
                            )
                        )


                    select 
            coalesce(@AMOUNTREQUESTED, 0) AMOUNTREQUESTED,
            coalesce(@AMOUNTAWARDED, 0) AMOUNTAWARDED,
            @CURRENCYID CURRENCYID