USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYGIFTTOTALS

        CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYGIFTTOTALS
        (
          @DATEFILTER tinyint, 
          @CURRENTAPPUSERID uniqueidentifier = null, 
          @FUNDRAISERID uniqueidentifier,
          @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 @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;
          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;

          select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
              @DECIMALDIGITS = DECIMALDIGITS,
              @ROUNDINGTYPECODE = ROUNDINGTYPECODE
          from 
            dbo.CURRENCY
          where
            CURRENCY.ID = @CURRENCYID;

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

          select
            @TOTALREVENUECOMMITTEDCOUNT = count(*),
            @TOTALREVENUECOMMITTEDAMOUNT = coalesce(sum(R.AMOUNTINCURRENCY), 0)
          from 
          dbo.OPPORTUNITY O
          inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
          inner join dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP on PP.ID = FPP.ID
          inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
          inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
          inner join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) R on R.ID = RS.REVENUEID
          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 PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID

            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 (
              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
            @TOTALPAIDCOUNT = count(*),
            @TOTALPAIDAMOUNT = coalesce(sum(R.AMOUNTINCURRENCY), 0)
          from 
          dbo.OPPORTUNITY O
          inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
          inner join dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP on PP.ID = FPP.ID
          inner join dbo.REVENUEOPPORTUNITY RO on RO.OPPORTUNITYID = O.ID
          inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
          inner join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) R on R.ID = RS.REVENUEID
          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 PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID

            and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
            and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,2) and RS.TYPECODE =0)
            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 
            @TOTALREVENUECOMMITTEDCOUNT,
            @TOTALREVENUECOMMITTEDAMOUNT,
            @TOTALPAIDCOUNT,
            @TOTALPAIDAMOUNT,
            @CURRENCYID;

          return 0;
        end