USP_DATAFORMTEMPLATE_DATALIST_OPPORTUNITIESSUMMARYPAST2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_OPPORTUNITIESSUMMARYPAST2
(
  @DATEFILTER tinyint,
  @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;


  /* Initialize parameters */

  declare @TODAY datetime = getdate();

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

  declare @CURRENCYID uniqueidentifier;
  set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;

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


  /* Output variables */

  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;


  /* Store the prospect plans the current user has access to */

  if object_id('tempdb..#PROSPECTPLAN_FILTER') is not null
    drop table #PROSPECTPLAN_FILTER;

  create table #PROSPECTPLAN_FILTER
  (
    PROSPECTPLANID uniqueidentifier
  );

  insert into
    #PROSPECTPLAN_FILTER
  select distinct
    PROSPECTPLAN.ID
  from
    dbo.PROSPECTPLAN
  where
  (
    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;


  /* Store all the filtered and included opportunities */

  if object_id('tempdb..#OPPORTUNITY_FILTER') is not null
    drop table #OPPORTUNITY_FILTER;

  create table #OPPORTUNITY_FILTER
  (
    ID uniqueidentifier,
    AMOUNT money,
    STATUSCODE tinyint
  );

  if @ORGPOSITIONSSELECTIONID is null
  begin

    insert into #OPPORTUNITY_FILTER
    select distinct
      OPPORTUNITY_CURRENCY.ID,
      OPPORTUNITY_CURRENCY.AMOUNTINCURRENCY,
      OPPORTUNITY_CURRENCY.STATUSCODE
    from
      dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) as OPPORTUNITY_CURRENCY
    inner join
      dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY_CURRENCY.PROSPECTPLANID
    inner join
      #PROSPECTPLAN_FILTER as PROSPECTPLAN_FILTER on PROSPECTPLAN_FILTER.PROSPECTPLANID = PROSPECTPLAN.ID
    where
      (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
      and
      (
        (OPPORTUNITY_CURRENCY.STATUSCODE in (3, 4) and (OPPORTUNITY_CURRENCY.RESPONSEDATE between @STARTDATE and @ENDDATE))
        or
        (
          OPPORTUNITY_CURRENCY.STATUSCODE = 5
          and
          (
            OPPORTUNITY_CURRENCY.ASKDATE is null
            and
            (
              OPPORTUNITY_CURRENCY.EXPECTEDASKDATE >= convert(nvarchar(8), @STARTDATE, 112)
              and
              OPPORTUNITY_CURRENCY.EXPECTEDASKDATE <= convert(nvarchar(8), @ENDDATE, 112)
            )
            or
            (
              OPPORTUNITY_CURRENCY.ASKDATE >= convert(nvarchar(8), @STARTDATE, 112)
              and
              OPPORTUNITY_CURRENCY.ASKDATE <= convert(nvarchar(8), @ENDDATE, 112)
            )
            or
            (OPPORTUNITY_CURRENCY.ASKDATE is null and OPPORTUNITY_CURRENCY.EXPECTEDASKDATE is null and @DATEFILTER = 10)
          )
        )
      );


    /*Set @HASACCEPTEDREJECTEDINFUTURE - possibly consolidate */

    if exists(select 1 from dbo.OPPORTUNITY where STATUSCODE in (3, 4, 5) and RESPONSEDATE > @TODAY)
      set @HASACCEPTEDREJECTEDINFUTURE = 1;
    else
      set @HASACCEPTEDREJECTEDINFUTURE = 0;

  end
  else
  begin
    declare @SELECTION_FILTER as table
    (
      ID uniqueidentifier
    );

    insert into @SELECTION_FILTER
    exec dbo.USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

    insert into #OPPORTUNITY_FILTER
    select distinct
      OPPORTUNITY_CURRENCY.ID,
      OPPORTUNITY_CURRENCY.AMOUNTINCURRENCY,
      OPPORTUNITY_CURRENCY.STATUSCODE
    from
      dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) as OPPORTUNITY_CURRENCY
    inner join
      dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY_CURRENCY.PROSPECTPLANID
    inner join
      #PROSPECTPLAN_FILTER as PROSPECTPLAN_FILTER on PROSPECTPLAN_FILTER.PROSPECTPLANID = PROSPECTPLAN.ID
    inner join
      dbo.V_OPPORTUNITYSOLICITOR on V_OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY_CURRENCY.ID
    inner join
      dbo.ORGANIZATIONPOSITIONHOLDER on ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = V_OPPORTUNITYSOLICITOR.FUNDRAISERID
    where
      (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
      and
      (
        (OPPORTUNITY_CURRENCY.STATUSCODE in (3, 4) and (OPPORTUNITY_CURRENCY.RESPONSEDATE between @STARTDATE and @ENDDATE))
        or
        (
          OPPORTUNITY_CURRENCY.STATUSCODE = 5
          and
          (
            OPPORTUNITY_CURRENCY.ASKDATE is null
            and
            (
              OPPORTUNITY_CURRENCY.EXPECTEDASKDATE >= convert(nvarchar(8), @STARTDATE, 112)
              and
              OPPORTUNITY_CURRENCY.EXPECTEDASKDATE <= convert(nvarchar(8), @ENDDATE, 112)
            )
            or
            (
              OPPORTUNITY_CURRENCY.ASKDATE >= convert(nvarchar(8), @STARTDATE, 112)
              and
              OPPORTUNITY_CURRENCY.ASKDATE <= convert(nvarchar(8), @ENDDATE, 112)
            )
            or
            (OPPORTUNITY_CURRENCY.ASKDATE is null and OPPORTUNITY_CURRENCY.EXPECTEDASKDATE is null and @DATEFILTER = 10)
          )
        )
      )
      and
      ORGANIZATIONPOSITIONHOLDER.ID in (select ID from @SELECTION_FILTER)
      and
      (
        coalesce(OPPORTUNITY_CURRENCY.RESPONSEDATE, OPPORTUNITY_CURRENCY.ASKDATE, OPPORTUNITY_CURRENCY.EXPECTEDASKDATE) between
          ORGANIZATIONPOSITIONHOLDER.DATEFROM and coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @ENDDATE)
        or
        coalesce(OPPORTUNITY_CURRENCY.RESPONSEDATE, OPPORTUNITY_CURRENCY.ASKDATE, OPPORTUNITY_CURRENCY.EXPECTEDASKDATE) is null
      );


    /*Set @HASACCEPTEDREJECTEDINFUTURE - possibly consolidate*/

    if exists(
        select 1 
        from dbo.OPPORTUNITY
        inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
        inner join #PROSPECTPLAN_FILTER as PROSPECTPLAN_FILTER on PROSPECTPLAN.ID = PROSPECTPLAN_FILTER.PROSPECTPLANID
        inner join dbo.V_OPPORTUNITYSOLICITOR on V_OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID
        inner join dbo.ORGANIZATIONPOSITIONHOLDER on ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = V_OPPORTUNITYSOLICITOR.FUNDRAISERID
        where (
            (OPPORTUNITY.STATUSCODE in (3,4) and OPPORTUNITY.RESPONSEDATE > @TODAY) 
            or
            (
              OPPORTUNITY.STATUSCODE = 5 and
              (OPPORTUNITY.ASKDATE is null 
              and 
              (OPPORTUNITY.EXPECTEDASKDATE>convert(nvarchar(8), @TODAY, 112)) 
              or 
              (OPPORTUNITY.ASKDATE > convert(nvarchar(8), @TODAY, 112)))
            )
            )
          and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
          and ORGANIZATIONPOSITIONHOLDER.ID in (select ID from @SELECTION_FILTER)
          and (
                coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) between
                  ORGANIZATIONPOSITIONHOLDER.DATEFROM and
                  coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @ENDDATE)

                  or coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) is null
              )
      )
      set @HASACCEPTEDREJECTEDINFUTURE = 1;
    else
      set @HASACCEPTEDREJECTEDINFUTURE = 0;

  end


  /* Calculations from opportunities */

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

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

  select
    @CANCELEDASKSCOUNT = count(ID),
    @CANCELEDASKSAMOUNT =  coalesce(sum(AMOUNT), 0)
  from
    #OPPORTUNITY_FILTER
  where
    STATUSCODE = 5;


  /* Store revenue associated with opportunities */

  if object_id('tempdb..#REVENUE_FILTER') is not null
    drop table #REVENUE_FILTER;

  create table #REVENUE_FILTER
  (
    OPPORTUNITYID uniqueidentifier,
    AMOUNT money,
    TRANSACTIONTYPECODE tinyint,
    APPLICATIONCODE tinyint
  );

  insert into #REVENUE_FILTER
  select
    OPPORTUNITY_FILTER.ID,
    REVENUESPLIT_CURRENCY.AMOUNTINCURRENCY,
    REVENUESPLIT_CURRENCY.TRANSACTIONTYPECODE,
    REVENUESPLIT_CURRENCY.APPLICATIONCODE
  from
    #OPPORTUNITY_FILTER as OPPORTUNITY_FILTER
  inner join
    dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY_FILTER.ID
  inner join
    dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUESPLIT_CURRENCY
      on REVENUESPLIT_CURRENCY.ID = REVENUEOPPORTUNITY.ID
  where
    REVENUESPLIT_CURRENCY.TRANSACTIONTYPECODE in (0, 1, 2)
    and
    REVENUESPLIT_CURRENCY.APPLICATIONCODE in (0,2)
    and
    REVENUESPLIT_CURRENCY.TYPECODE = 0;


  /* Calculations from revenue */

  select
    @TOTALREVENUECOMMITTEDCOUNT = count(distinct OPPORTUNITYID),
    @TOTALREVENUECOMMITTEDAMOUNT = coalesce(sum(AMOUNT), 0)
  from
    #REVENUE_FILTER
  where
    APPLICATIONCODE = 0;

  select
    @TOTALPAIDCOUNT = count(distinct OPPORTUNITYID),
    @TOTALPAIDAMOUNT = coalesce(sum(AMOUNT), 0)
  from
    #REVENUE_FILTER
  where
    TRANSACTIONTYPECODE = 0;


  /* Return output */

  select 
    @ACCEPTEDASKSCOUNT 'Accepted asks count',
    @ACCEPTEDASKSAMOUNT 'Accepted asks amount',
    @REJECTEDASKSCOUNT 'Rejected asks count',
    @REJECTEDASKSAMOUNT 'Rejected asks amount',
    @HASACCEPTEDREJECTEDINFUTURE HASACCEPTEDREJECTEDINFUTURE,
    @TOTALREVENUECOMMITTEDCOUNT 'Total revenue committed count',
    @TOTALREVENUECOMMITTEDAMOUNT 'Total revenue committed amount',
    @TOTALPAIDCOUNT 'Total paid count',
    @TOTALPAIDAMOUNT 'Total paid amount',
    @CURRENCYID CURRENCYID,
    @CANCELEDASKSCOUNT 'Canceled asks count',
    @CANCELEDASKSAMOUNT 'Canceled asks amount';

  return 0;
end