USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYFUTURE2

Fetches summary information for opportunities and asks.
Parameters

Parameter	Parameter Type	Mode	Description
@DATEFILTER	smallint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN
@PROSPECTPLANTYPECODEID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	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_FUNDRAISERSSUMMARYFUTURE2
        (
          @DATEFILTER smallint, 
          @CURRENTAPPUSERID uniqueidentifier = null, 
          @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
          @PROSPECTPLANTYPECODEID uniqueidentifier = null,
          @STARTDATE datetime = null,
          @ENDDATE datetime = 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;
        declare @CURRENCYID uniqueidentifier;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier;

        exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER, @STARTDATE output, @ENDDATE output
        set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        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
              @QUALIFIEDASKSCOUNT = count(*),
              @QUALIFIEDASKSAMOUNT = coalesce(sum(O.AMOUNTINCURRENCY), 0)
            from 
              dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
              inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
              inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
            where
              O.STATUSCODE=1
              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 = 26 or @DATEFILTER = 10))
                  )

            select
              @RESPONSEPENDINGASKCOUNT = count(*),
              @RESPONSEPENDINGASKAMOUNT = coalesce(sum(O.AMOUNTINCURRENCY), 0)
            from 
              dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
              inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
              inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
            where
              O.STATUSCODE=2
              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 = 26 or @DATEFILTER = 10))
                  );

            select
              @UNQUALIFIEDASKSCOUNT = count(*),
              @UNQUALIFIEDASKSAMOUNT = coalesce(sum(O.AMOUNTINCURRENCY), 0)
            from 
              dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
              inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
              inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
            where
              O.STATUSCODE=0
              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)));
          end

        else --@ORGPOSITIONSSELECTIONID is not null

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

            select
              @QUALIFIEDASKSCOUNT = count(*),
              @QUALIFIEDASKSAMOUNT =  coalesce(sum(AMOUNT), 0)
            from
            (
                select
                      distinct O.ID, 
                      O.AMOUNTINCURRENCY as AMOUNT    
                    from
                        dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                        inner join dbo.PROSPECTPLAN PP on O.PROSPECTPLANID = PP.ID
                        inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
                        inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                    where
                      O.STATUSCODE=1
                      and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                      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 = 26 or @DATEFILTER = 10))
                          )
                      and (
                            coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, @ENDDATE)
                              or coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) is null
                          )
             ) as QUALIFIEDRESULT

            select
              @RESPONSEPENDINGASKCOUNT = count(*),
              @RESPONSEPENDINGASKAMOUNT =  coalesce(sum(AMOUNT), 0)
            from
            (
                 select
                      distinct O.ID, 
                      O.AMOUNTINCURRENCY as AMOUNT    
                  from
                      dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                      inner join dbo.PROSPECTPLAN PP on O.PROSPECTPLANID = PP.ID
                      inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
                      inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                      inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                    where
                      O.STATUSCODE=2
    and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                      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 = 26 or @DATEFILTER = 10))
                          )
                      and (
                            coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, @ENDDATE)
                              or coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) is null
                          )
             ) as RESPONSEPENDINGRESULT

            select
              @UNQUALIFIEDASKSCOUNT = count(*),
              @UNQUALIFIEDASKSAMOUNT = coalesce(sum(AMOUNT), 0)
            from 
            (
                select
                      distinct O.ID, 
                      O.AMOUNTINCURRENCY as AMOUNT
                  from    
                      dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                      inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                      inner join @PROSPECTPLANSFORUSER PPS on PP.ID = PPS.PROSPECTPLANID
                      inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                      inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                    where
                      O.STATUSCODE=0
                      and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                      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 (
                            coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, @ENDDATE)
                              or coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) is null
                          )
            )  as UNQUALIFIEDRESULT

          end

        select 
          @QUALIFIEDASKSCOUNT 'Qualified asks count',
          @QUALIFIEDASKSAMOUNT 'Qualified asks amount',
          @RESPONSEPENDINGASKCOUNT 'Response pending asks count',
          @RESPONSEPENDINGASKAMOUNT 'Response pending asks amount',
          @CURRENCYID CURRENCYID,
          @UNQUALIFIEDASKSCOUNT 'Unqualified asks count',
          @UNQUALIFIEDASKSAMOUNT 'Unqualified asks amount';

        return 0;
      end