USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYOPPORTUNITIES2

Fetches summary information for a fundraiser's opportunities and asks.

Parameters

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

          declare @OVERDUERESPONSESCOUNT int;
          declare @OVERDUERESPONSESAMOUNT money;
          declare @OVERDUEASKSCOUNT int;
          declare @OVERDUEASKSAMOUNT money;
          declare @FUZZYTODAY dbo.UDT_FUZZYDATE;

          set @FUZZYTODAY = convert(nvarchar(8), getdate(), 112);

          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
            @OVERDUEASKSCOUNT = count(*),
            @OVERDUEASKSAMOUNT = 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 (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
            and O.ASKDATE<@FUZZYTODAY
            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
            --and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID;



          select
            @OVERDUERESPONSESCOUNT = count(*),
            @OVERDUERESPONSESAMOUNT = 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 (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
            and O.RESPONSEDATE<@FUZZYTODAY
            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
            --and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID;


          select 
            @OVERDUEASKSCOUNT,
            @OVERDUEASKSAMOUNT,
            @OVERDUERESPONSESCOUNT,
            @OVERDUERESPONSESAMOUNT,
            @CURRENCYID

          return 0;
        end