USP_DATALIST_OPPORTUNITYSUMMARYBYAMOUNTRANGEANDLIKELIHOOD

CREATE procedure dbo.USP_DATALIST_OPPORTUNITYSUMMARYBYAMOUNTRANGEANDLIKELIHOOD
(
    @DATEFILTER tinyint = 2,
    @CURRENTAPPUSERID uniqueidentifier = null, 
    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
    @PROSPECTPLANTYPECODEID uniqueidentifier = null,
    @INCLUDEUNQUALIFIED bit = 0,
    @INCLUDEQUALIFIED bit = 0,
    @INCLUDERESPONSEPENDING bit = 0,
    @INCLUDEACCEPTED bit = 0,
    @LIKELIHOODS xml = null,
    @OPPORTUNITYAMOUNTRANGEID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

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

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

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

    declare @LIKELIHOODIDS table(TYPECODE uniqueidentifier);
    if @LIKELIHOODS is not null
        begin
            insert into @LIKELIHOODIDS(TYPECODE) 
                select 
                    T.c.value('(LIKELIHOODID)[1]','uniqueidentifier') AS 'TYPECODE'
                from 
                    @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) --Note: the root is named LIKELIHOODID to correspond with existing datalist CFF3A21B-C268-4A60-921C-D046964773BB, which is expecting it to be named in this manner.
        end
    else
        begin
            insert into @LIKELIHOODIDS(TYPECODE) 
                select ID from LIKELIHOODTYPECODE;

            insert into @LIKELIHOODIDS(TYPECODE) values(null);
        end

    declare @temp table(
        AMOUNTRANGE nvarchar(255), 
        LOWERLIMIT money,
        LIKELIHOODID uniqueidentifier, 
        LIKELIHOODNAME nvarchar(110), 
        ASKCOUNT integer, 
        ASKAMOUNT money, 
        PROJECTEDREVENUE money,
        CURRENCYID uniqueidentifier,
        SEQUENCE integer,
        AMOUNTRANGEID uniqueidentifier
    );


    if @ORGPOSITIONSSELECTIONID is null
    begin

        insert into @temp
        select
            OPPORTUNITYAMOUNTRANGES.NAME AMOUNTRANGE,
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT,
            coalesce(OPPORTUNITY.LIKELIHOODTYPECODEID,'00000000-0000-0000-0000-000000000000'),
            case when OPPORTUNITY.LIKELIHOODTYPECODEID is null then 'Undefined likelihood'
                else dbo.UFN_LIKELIHOODPERCENT_GETNAMEANDPERCENT(OPPORTUNITY.LIKELIHOODTYPECODEID)
            end DESCRIPTION,
            count(*) ASKCOUNT,
            sum(OPPORTUNITYBULK.AMOUNTINCURRENCY) ASKAMOUNT,
            sum(OPPORTUNITYBULK.AMOUNTINCURRENCY * coalesce(LIKELIHOODPERCENT.[PERCENT], 0) / 100) PROJECTEDREVENUE,
            @USERBASECURRENCYID [CURRENCYID],
            coalesce(LIKELIHOODTYPECODE.SEQUENCE, 0),
            OPPORTUNITYAMOUNTRANGES.ID AMOUNTRANGEID
        from 
            OPPORTUNITY
            inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@USERBASECURRENCYID, @ORGANIZATIONCURRENCYID) OPPORTUNITYBULK on OPPORTUNITY.ID = OPPORTUNITYBULK.ID
            inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID=OPPORTUNITY.PROSPECTPLANID
            inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID=PROSPECTPLAN.PROSPECTPLANTYPECODEID
            inner join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) OPPORTUNITYAMOUNTRANGES on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID) between OPPORTUNITYAMOUNTRANGES.LOWERLIMIT and OPPORTUNITYAMOUNTRANGES.UPPERLIMIT
            left outer join dbo.LIKELIHOODTYPECODE on LIKELIHOODTYPECODE.ID = OPPORTUNITY.LIKELIHOODTYPECODEID
            left outer join dbo.LIKELIHOODPERCENT on LIKELIHOODTYPECODE.ID = LIKELIHOODPERCENT.ID
        where
            (@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0) 
            and (@INCLUDEQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 1) 
            and (@INCLUDERESPONSEPENDING = 1 or OPPORTUNITY.STATUSCODE <> 2) 
            and (@INCLUDEACCEPTED = 1 or OPPORTUNITY.STATUSCODE <> 3)
            and (OPPORTUNITY.STATUSCODE not in (4,5))
            and (OPPORTUNITY.ASKDATE between @STARTDATE and @ENDDATE
                    or OPPORTUNITY.ASKDATE is null and OPPORTUNITY.EXPECTEDASKDATE between @STARTDATE and @ENDDATE)
            and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
            and (@LIKELIHOODS is null 
                        or OPPORTUNITY.LIKELIHOODTYPECODEID in (select typecode from @LIKELIHOODIDS)
                        or (OPPORTUNITY.LIKELIHOODTYPECODEID is null 
                            and (exists (select TYPECODE from @LIKELIHOODIDS where TYPECODE is null)))
                    )
            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
        group by
            OPPORTUNITYAMOUNTRANGES.NAME,
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT,
            OPPORTUNITY.LIKELIHOODTYPECODEID,
            LIKELIHOODTYPECODE.SEQUENCE,
            LIKELIHOODPERCENT.[PERCENT],
            OPPORTUNITYAMOUNTRANGES.ID
    end

    else --@ORGPOSITIONSSELECTIONID is not null
    begin
        declare @IDS as table(ID uniqueidentifier);
        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

        insert into @temp
    select AMOUNTRANGE,
                LOWERLIMIT,
                LIKELIHOODTYPECODEID,
                DESCRIPTION,
                count(*) ASKCOUNT,
                sum(ASKAMOUNT),
                sum(ASKAMOUNT* coalesce([PERCENT], 0) / 100) PROJECTEDREVENUE,
                [CURRENCYID],
                SEQUENCE,
                AMOUNTRANGEID
    from 
    (
          select distinct
          OPPORTUNITY.ID,
            OPPORTUNITYAMOUNTRANGES.NAME AMOUNTRANGE,
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT LOWERLIMIT,
            coalesce(OPPORTUNITY.LIKELIHOODTYPECODEID,'00000000-0000-0000-0000-000000000000') LIKELIHOODTYPECODEID,
            case when OPPORTUNITY.LIKELIHOODTYPECODEID is null then 'Undefined likelihood'
                else dbo.UFN_LIKELIHOODPERCENT_GETNAMEANDPERCENT(OPPORTUNITY.LIKELIHOODTYPECODEID)
            end DESCRIPTION,
            OPPORTUNITYBULK.AMOUNTINCURRENCY ASKAMOUNT,
            LIKELIHOODPERCENT.[PERCENT] [PERCENT],
            @USERBASECURRENCYID [CURRENCYID],
            coalesce(LIKELIHOODTYPECODE.SEQUENCE, 0) SEQUENCE,
            OPPORTUNITYAMOUNTRANGES.ID AMOUNTRANGEID
          from 
              OPPORTUNITY
              inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@USERBASECURRENCYID, @ORGANIZATIONCURRENCYID) OPPORTUNITYBULK on OPPORTUNITY.ID = OPPORTUNITYBULK.ID
              inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID=OPPORTUNITY.PROSPECTPLANID
              inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID=PROSPECTPLAN.PROSPECTPLANTYPECODEID
              inner join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) OPPORTUNITYAMOUNTRANGES on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID) between OPPORTUNITYAMOUNTRANGES.LOWERLIMIT and OPPORTUNITYAMOUNTRANGES.UPPERLIMIT
        inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = OPPORTUNITY.ID
        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
              left outer join dbo.LIKELIHOODTYPECODE on LIKELIHOODTYPECODE.ID = OPPORTUNITY.LIKELIHOODTYPECODEID
              left outer join dbo.LIKELIHOODPERCENT on LIKELIHOODTYPECODE.ID = LIKELIHOODPERCENT.ID
          where
        OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
              and (@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0)         
              and (@INCLUDEQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 1) 
              and (@INCLUDERESPONSEPENDING = 1 or OPPORTUNITY.STATUSCODE <> 2) 
              and (@INCLUDEACCEPTED = 1 or OPPORTUNITY.STATUSCODE <> 3)
              and (OPPORTUNITY.STATUSCODE not in (4,5))
              and (OPPORTUNITY.ASKDATE between @STARTDATE and @ENDDATE
                      or OPPORTUNITY.ASKDATE is null and OPPORTUNITY.EXPECTEDASKDATE between @STARTDATE and @ENDDATE)
              and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
              and (@LIKELIHOODS is null 
                          or OPPORTUNITY.LIKELIHOODTYPECODEID in (select typecode from @LIKELIHOODIDS)
                          or (OPPORTUNITY.LIKELIHOODTYPECODEID is null 
                              and (exists (select TYPECODE from @LIKELIHOODIDS where TYPECODE is null)))
                      )
              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 (
                    coalesce(OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE)
              )
      ) as RESULT  
          group by
              AMOUNTRANGE,
              LOWERLIMIT,
              LIKELIHOODTYPECODEID,
              SEQUENCE,
              [PERCENT],
        DESCRIPTION,
        CURRENCYID,
              AMOUNTRANGEID
    end


    if @OPPORTUNITYAMOUNTRANGEID is null --Just return the opportunity data summarized by amount range
    begin
        select
            AMOUNTRANGEID as ID,
            AMOUNTRANGE as DESCRIPTION,
            sum(ASKCOUNT) as ASKCOUNT, 
            sum(ASKAMOUNT) as ASKAMOUNT,
            sum(PROJECTEDREVENUE) as PROJECTEDREVENUE,
            @USERBASECURRENCYID as CURRENCYID,
            AMOUNTRANGEID as AMOUNTRANGEID,
            null as LIKELIHOODID
        from 
            @temp
        group by
            AMOUNTRANGE,
            AMOUNTRANGEID,
            CURRENCYID,
            LOWERLIMIT
        order by
            LOWERLIMIT desc;
    end

    else --Return the opportunity data broken down for the given amount range
    begin

        select
            LIKELIHOODID as ID,
            LIKELIHOODNAME as DESCRIPTION,
            ASKCOUNT, 
            ASKAMOUNT,
            PROJECTEDREVENUE,
            @USERBASECURRENCYID as CURRENCYID,
            AMOUNTRANGEID as AMOUNTRANGEID,
            LIKELIHOODID as LIKELIHOODID
        from 
            @temp T
        where
            T.AMOUNTRANGEID = @OPPORTUNITYAMOUNTRANGEID
        order by
            SEQUENCE;
    end