USP_DATAFORMTEMPLATE_DATALIST_OPPORTUNITIESSUMMARYGIFTTOTALS

Fetches summary information.
Parameters

Parameter	Parameter Type	Mode	Description
@DATEFILTER	tinyint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN
@PROSPECTPLANTYPECODEID	uniqueidentifier	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_DATAFORMTEMPLATE_DATALIST_OPPORTUNITIESSUMMARYGIFTTOTALS
        (
          @DATEFILTER tinyint, 
          @CURRENTAPPUSERID uniqueidentifier = null, 
          @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
          @PROSPECTPLANTYPECODEID uniqueidentifier = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null
        )
        as begin
          set nocount on;

          declare @STARTDATE datetime;
          declare @ENDDATE datetime;

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

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

          declare @TOTALREVENUECOMMITTEDCOUNT int;
          declare @TOTALREVENUECOMMITTEDAMOUNT money;
          declare @TOTALPAIDCOUNT int;
          declare @TOTALPAIDAMOUNT money;

          declare @PROSPECTPLANSFORUSER table (PROSPECTPLANID uniqueidentifier)
          insert into @PROSPECTPLANSFORUSER 
            select distinct PP.ID
            from dbo.PROSPECTPLAN PP
            where (
                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

          if @ORGPOSITIONSSELECTIONID is null
            begin
              select
                @TOTALREVENUECOMMITTEDCOUNT = count(*),
                @TOTALREVENUECOMMITTEDAMOUNT = coalesce(sum(R.AMOUNT), 0)
              from 
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
                inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
              where
                (
                  (O.STATUSCODE in (3,4) and O.RESPONSEDATE > @STARTDATE) 
                  or
                  (
                  O.STATUSCODE = 5 and 
                  (O.ASKDATE is null 
                    and 
                  (O.EXPECTEDASKDATE>convert(nvarchar(8), @STARTDATE, 112)) 
                    or 
                  (O.ASKDATE>convert(nvarchar(8), @STARTDATE, 112)))
                  )
                )
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and ((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 0 and RS.TYPECODE = 0) or
                   (R.TRANSACTIONTYPECODE in (1,2) and RS.APPLICATIONCODE = 0 and RS.TYPECODE = 0));

              select
                @TOTALPAIDCOUNT = count(*),
                @TOTALPAIDAMOUNT = coalesce(sum(R.AMOUNT), 0)
              from 
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
                inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
              where
                (
                  (O.STATUSCODE in (3,4) and O.RESPONSEDATE > @STARTDATE) 
                  or
                  (
                  O.STATUSCODE = 5 and 
                  (O.ASKDATE is null 
                    and 
                  (O.EXPECTEDASKDATE>convert(nvarchar(8), @STARTDATE, 112)) 
                    or 
      (O.ASKDATE>convert(nvarchar(8), @STARTDATE, 112)))
                  )
                )
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,2) and RS.TYPECODE =0);

            end
          else --@ORGPOSITIONSSELECTIONID is not null

            begin
              declare @IDS as table(ID uniqueidentifier);
              insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

              select
                @TOTALREVENUECOMMITTEDCOUNT = count(*),
                @TOTALREVENUECOMMITTEDAMOUNT = coalesce(sum(R.AMOUNT), 0)
              from 
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
                inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
              where
                (
                  (O.STATUSCODE in (3,4) and O.RESPONSEDATE > @STARTDATE) 
                  or
                  (
                  O.STATUSCODE = 5 and 
                  (O.ASKDATE is null 
                    and 
                  (O.EXPECTEDASKDATE>convert(nvarchar(8), @STARTDATE, 112)) 
                    or 
                  (O.ASKDATE>convert(nvarchar(8), @STARTDATE, 112)))
                  )
                )
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and ((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 0 and RS.TYPECODE = 0) or
                   (R.TRANSACTIONTYPECODE in (1,2) and RS.APPLICATIONCODE = 0 and RS.TYPECODE = 0))
                and exists (
                  select SELECTION.ID
                  from @IDS as SELECTION
                  inner join dbo.ORGANIZATIONPOSITIONHOLDER as OPH on OPH.ID = SELECTION.ID
                  where
                    (OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PP.SECONDARYMANAGERFUNDRAISERID) or ((select count(SF.ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PP.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0)  
                    and (O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE))
                );

              select
                @TOTALPAIDCOUNT = count(*),
                @TOTALPAIDAMOUNT = coalesce(sum(R.AMOUNT), 0)
              from 
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
                inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
              where
                (
                  (O.STATUSCODE in (3,4) and O.RESPONSEDATE > @STARTDATE) 
                  or
                  (
                  O.STATUSCODE = 5 and 
                  (O.ASKDATE is null 
                    and 
                  (O.EXPECTEDASKDATE>convert(nvarchar(8), @STARTDATE, 112)) 
                    or 
                  (O.ASKDATE>convert(nvarchar(8), @STARTDATE, 112)))
                  )
                )
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,2) and RS.TYPECODE =0)
                and exists (
                  select SELECTION.ID
                  from @IDS as SELECTION
inner join dbo.ORGANIZATIONPOSITIONHOLDER as OPH on OPH.ID = SELECTION.ID
                  where
                    (OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PP.SECONDARYMANAGERFUNDRAISERID) or ((select count(SF.ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PP.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0)  
                    and (O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE))
                );
            end

          select 
            @TOTALREVENUECOMMITTEDCOUNT,
            @TOTALREVENUECOMMITTEDAMOUNT,
            @TOTALPAIDCOUNT,
            @TOTALPAIDAMOUNT;

          return 0;
        end