USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYFUTURE2

Fetches summary information for a fundraiser's outstanding asks.
Parameters

Parameter	Parameter Type	Mode	Description
@DATEFILTER	tinyint	IN
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@PROSPECTPLANTYPECODEID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYFUTURE2
        (
          @DATEFILTER tinyint, 
          @FUNDRAISERID uniqueidentifier,
          @PROSPECTPLANTYPECODEID uniqueidentifier = null,
          @STARTDATE datetime = null,
          @ENDDATE datetime = null,
          @CURRENTAPPUSERID uniqueidentifier = null, 
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null
        )
        as begin
          set nocount on;



        declare @QUALIFIEDASKSCOUNT int;
        declare @QUALIFIEDASKSAMOUNT money;
        declare @UNQUALIFIEDASKSCOUNT int;
        declare @UNQUALIFIEDASKSAMOUNT money;
        declare @RESPONSEPENDINGASKCOUNT int;
        declare @RESPONSEPENDINGASKAMOUNT money;

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

        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 @ORGANIZATIONCURRENCYID uniqueidentifier;       
        select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        select
          @QUALIFIEDASKSCOUNT = count(*),
          @QUALIFIEDASKSAMOUNT = coalesce(sum(OPPORTUNITYBULK.AMOUNTINCURRENCY), 0)
        from 
          dbo.OPPORTUNITY O
          inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) OPPORTUNITYBULK on O.ID = OPPORTUNITYBULK.ID
          inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
        where
          O.STATUSCODE=1
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = O.ID)
          --and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID

          and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
          and (O.ASKDATE is null 
                and (O.EXPECTEDASKDATE>=convert(nvarchar(8), @STARTDATE, 112) 
                      and O.EXPECTEDASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
            or (O.ASKDATE>=convert(nvarchar(8), @STARTDATE, 112)
              and O.ASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
            or (O.ASKDATE is null and O.EXPECTEDASKDATE is null and @DATEFILTER in (10,26))
              )
          and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
          ) > 0;

        select
          @RESPONSEPENDINGASKCOUNT = count(*),
          @RESPONSEPENDINGASKAMOUNT = coalesce(sum(OPPORTUNITYBULK.AMOUNTINCURRENCY), 0)
        from 
          dbo.OPPORTUNITY O
          inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) OPPORTUNITYBULK on O.ID = OPPORTUNITYBULK.ID
          inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
        where
          O.STATUSCODE=2
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = O.ID)
          --and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID

          and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
          and (O.ASKDATE is null 
                and (O.EXPECTEDASKDATE>=convert(nvarchar(8), @STARTDATE, 112) 
                      and O.EXPECTEDASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
            or (O.ASKDATE>=convert(nvarchar(8), @STARTDATE, 112)
              and O.ASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
            or (O.ASKDATE is null and O.EXPECTEDASKDATE is null and @DATEFILTER in (10,26))
              )
          and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
          ) > 0;

        select
          @UNQUALIFIEDASKSCOUNT = count(*),
          @UNQUALIFIEDASKSAMOUNT = coalesce(sum(OPPORTUNITYBULK.AMOUNTINCURRENCY), 0)
        from 
          dbo.OPPORTUNITY O
          inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) OPPORTUNITYBULK on O.ID = OPPORTUNITYBULK.ID
          inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
        where
          O.STATUSCODE=0
          and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = O.ID)
          --and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID

          and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
          and ((O.EXPECTEDASKDATE>=convert(nvarchar(8), @STARTDATE, 112) and O.EXPECTEDASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
            or (O.EXPECTEDASKDATE is null and @DATEFILTER in (10,26)))
          and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
          ) > 0;

        select 
          @QUALIFIEDASKSCOUNT,
          @QUALIFIEDASKSAMOUNT,
          @RESPONSEPENDINGASKCOUNT,
          @RESPONSEPENDINGASKAMOUNT,
          @CURRENCYID,
          @UNQUALIFIEDASKSCOUNT,
          @UNQUALIFIEDASKSAMOUNT;

        return 0;
        end