USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYPAST2

        CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYPAST2
        (
          @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;

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

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

          declare @REJECTEDASKSCOUNT int;
          declare @REJECTEDASKSAMOUNT money;
          declare @ACCEPTEDASKSCOUNT int;
          declare @ACCEPTEDASKSAMOUNT money;
          declare @CANCELEDASKSCOUNT int;
          declare @CANCELEDASKSAMOUNT money;
          declare @HASACCEPTEDREJECTEDINFUTURE bit;
          declare @TOTALREVENUECOMMITTEDCOUNT int;
          declare @TOTALREVENUECOMMITTEDAMOUNT money;
          declare @TOTALPAIDCOUNT int;
          declare @TOTALPAIDAMOUNT money;

          declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
          declare @CURRENCYID uniqueidentifier;
          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;

          select @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(APPUSER.ID) 
          from dbo.APPUSER 
          where APPUSER.CONSTITUENTID = @FUNDRAISERID;

          if @CURRENCYID is null
            set @CURRENCYID = @ORGANIZATIONCURRENCYID;    

          select 
            @DECIMALDIGITS = DECIMALDIGITS,
            @ROUNDINGTYPECODE = ROUNDINGTYPECODE
          from dbo.CURRENCY
          where
            CURRENCY.ID = @CURRENCYID;



          declare @OPPORTUNITYINFO table(
            ID uniqueidentifier,
            AMOUNT money,
            STATUSCODE tinyint
          );

          insert into @OPPORTUNITYINFO
          select 
            OPPORTUNITYBULK.ID,
            OPPORTUNITYBULK.AMOUNTINCURRENCY,
            OPPORTUNITYBULK.STATUSCODE 
          from dbo.PROSPECTPLAN
          inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(
              @CURRENCYID, 
              @ORGANIZATIONCURRENCYID
            ) OPPORTUNITYBULK on OPPORTUNITYBULK.PROSPECTPLANID = PROSPECTPLAN.ID
          where 
            (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
            and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = OPPORTUNITYBULK.ID)
            and(
                (OPPORTUNITYBULK.STATUSCODE in (3,4) and OPPORTUNITYBULK.RESPONSEDATE between @STARTDATE and @ENDDATE) 
                or
                (
                  OPPORTUNITYBULK.STATUSCODE = 5 and 
                  (OPPORTUNITYBULK.ASKDATE is null 
                  and 
                  (OPPORTUNITYBULK.EXPECTEDASKDATE>=convert(nvarchar(8), @STARTDATE, 112) and 
                   OPPORTUNITYBULK.EXPECTEDASKDATE<=convert(nvarchar(8), @ENDDATE, 112)) 
                  or 
                  (OPPORTUNITYBULK.ASKDATE>=convert(nvarchar(8), @STARTDATE, 112)and 
                   OPPORTUNITYBULK.ASKDATE<=convert(nvarchar(8), @ENDDATE, 112))
                  or 
                  (OPPORTUNITYBULK.ASKDATE is null and OPPORTUNITYBULK.EXPECTEDASKDATE is null and @DATEFILTER = 10))
                )
              )
            and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.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 PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID;    


          select
            @REJECTEDASKSCOUNT = count(ID),
            @REJECTEDASKSAMOUNT = coalesce(sum(AMOUNT), 0)
          from @OPPORTUNITYINFO
          where STATUSCODE=4;

          select
            @ACCEPTEDASKSCOUNT = count(ID),
            @ACCEPTEDASKSAMOUNT = coalesce(sum(AMOUNT), 0)
          from @OPPORTUNITYINFO
          where STATUSCODE=3;

          select
            @CANCELEDASKSCOUNT = count(*),
            @CANCELEDASKSAMOUNT = coalesce(sum(AMOUNT), 0)
          from @OPPORTUNITYINFO
          where STATUSCODE=5

          if exists(  select 1 from dbo.OPPORTUNITY 
                inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID                
                where 
                  STATUSCODE in (3,4,5) 
                  and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = OPPORTUNITY.ID)
                  and RESPONSEDATE>@TODAY
                  and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                  and (
                    select count(*) 
                    from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.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 PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID)

            set @HASACCEPTEDREJECTEDINFUTURE = 1;
          else
            set @HASACCEPTEDREJECTEDINFUTURE = 0;

          declare @REVENUEINFO table(
            ID uniqueidentifier,
            AMOUNT money,
            TRANSACTIONTYPECODE tinyint,
            APPLICATIONCODE tinyint,
            OPPORTUNITYID uniqueidentifier
          )
          insert into @REVENUEINFO
          select
            REVENUEBULK.ID,
            REVENUEBULK.AMOUNTINCURRENCY,
            REVENUEBULK.TRANSACTIONTYPECODE,
            REVENUESPLIT_EXT.APPLICATIONCODE,
            OPPORTUNITYINFO.ID
          from @OPPORTUNITYINFO OPPORTUNITYINFO
            inner join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITYINFO.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
              @CURRENCYID, 
              @ORGANIZATIONCURRENCYID, 
              @DECIMALDIGITS, 
              @ROUNDINGTYPECODE
            ) REVENUEBULK on REVENUEBULK.ID = FINANCIALTRANSACTIONLINEITEM.ID
          where OPPORTUNITYINFO.STATUSCODE in (3,4)
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
          and REVENUESPLIT_EXT.APPLICATIONCODE in (0,2);

          select
            @TOTALREVENUECOMMITTEDCOUNT = count(distinct OPPORTUNITYID),
            @TOTALREVENUECOMMITTEDAMOUNT = coalesce(sum(AMOUNT), 0)
          from @REVENUEINFO
          where (TRANSACTIONTYPECODE in (0,1,2) and APPLICATIONCODE = 0);

          select
            @TOTALPAIDCOUNT = count(distinct OPPORTUNITYID),
            @TOTALPAIDAMOUNT = coalesce(sum(AMOUNT), 0)
          from @REVENUEINFO
          where (TRANSACTIONTYPECODE = 0 and APPLICATIONCODE in (0,2));

select 
            @ACCEPTEDASKSCOUNT,
            @ACCEPTEDASKSAMOUNT,
            @REJECTEDASKSCOUNT,
            @REJECTEDASKSAMOUNT,
            @HASACCEPTEDREJECTEDINFUTURE,
            @TOTALREVENUECOMMITTEDCOUNT,
            @TOTALREVENUECOMMITTEDAMOUNT,
            @TOTALPAIDCOUNT,
            @TOTALPAIDAMOUNT,
            @CURRENCYID,
            @CANCELEDASKSCOUNT,
            @CANCELEDASKSAMOUNT;

          return 0;
        end