USP_DATALIST_OPPORTUNITIESBYAMOUNTRANGEANDLIKELIHOOD

CREATE procedure dbo.USP_DATALIST_OPPORTUNITIESBYAMOUNTRANGEANDLIKELIHOOD
(
    @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,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @LIKELIHOOD1NAME nvarchar(100) = null output,
    @LIKELIHOOD2NAME nvarchar(100) = null output,
    @LIKELIHOOD3NAME nvarchar(100) = null output,
    @LIKELIHOOD4NAME nvarchar(100) = null output,
    @LIKELIHOOD5NAME nvarchar(100) = null output,
    @TOTALOPPORTUNITIES int = null output,
    @TOTALAMOUNT money = null output,
    @TOTALPROJECTEDREVENUE money = null output,
    @USERBASECURRENCYID uniqueidentifier = null output
)
as
    set nocount on;

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    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 @LIKELIHOOD1ID uniqueidentifier;
    declare @LIKELIHOOD2ID uniqueidentifier;
    declare @LIKELIHOOD3ID uniqueidentifier;
    declare @LIKELIHOOD4ID uniqueidentifier;
    declare @LIKELIHOOD5ID uniqueidentifier;

    select 
        @LIKELIHOOD1ID = coalesce(T.c.value('(LIKELIHOODID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000'),
        @LIKELIHOOD1NAME = T.c.value('(LIKELIHOODNAME)[1]','nvarchar(100)')
    from 
        @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) 
    where 
        T.c.value('(SEQUENCE)[1]','tinyint') = 1;

    select 
        @LIKELIHOOD2ID = coalesce(T.c.value('(LIKELIHOODID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000'),
        @LIKELIHOOD2NAME = T.c.value('(LIKELIHOODNAME)[1]','nvarchar(100)')
    from 
        @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) 
    where 
        T.c.value('(SEQUENCE)[1]','tinyint') = 2;

    select 
        @LIKELIHOOD3ID = coalesce(T.c.value('(LIKELIHOODID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000'),
        @LIKELIHOOD3NAME = T.c.value('(LIKELIHOODNAME)[1]','nvarchar(100)')
    from 
        @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) 
    where 
        T.c.value('(SEQUENCE)[1]','tinyint') = 3;

    select 
        @LIKELIHOOD4ID = coalesce(T.c.value('(LIKELIHOODID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000'),
        @LIKELIHOOD4NAME = T.c.value('(LIKELIHOODNAME)[1]','nvarchar(100)')
    from 
        @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) 
    where 
        T.c.value('(SEQUENCE)[1]','tinyint') = 4;

    select 
        @LIKELIHOOD5ID = coalesce(T.c.value('(LIKELIHOODID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000'),
        @LIKELIHOOD5NAME = T.c.value('(LIKELIHOODNAME)[1]','nvarchar(100)')
    from 
        @LIKELIHOODS.nodes('/LIKELIHOODID/ITEM') T(c) 
    where 
        T.c.value('(SEQUENCE)[1]','tinyint') = 5;

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

    if @ORGPOSITIONSSELECTIONID is null
    begin
        insert into @temp
        select
            OPPORTUNITYAMOUNTRANGES.NAME AMOUNTRANGE,
            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,
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT
        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,
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT
        order by 
            OPPORTUNITYAMOUNTRANGES.LOWERLIMIT DESC,
            LIKELIHOODTYPECODE.SEQUENCE;
    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,
            LIKELIHOODTYPECODEID,
            DESCRIPTION,
            count(*) ASKCOUNT,
            sum(ASKAMOUNT),
            sum(ASKAMOUNT  * [PERCENT] / 100) PROJECTEDREVENUE,
            LOWERLIMIT 
        from 
        (
          select distinct
            OPPORTUNITY.ID,
              OPPORTUNITYAMOUNTRANGES.NAME AMOUNTRANGE,
              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,
              coalesce(LIKELIHOODPERCENT.[PERCENT], 0) [PERCENT],
              OPPORTUNITYAMOUNTRANGES.LOWERLIMIT LOWERLIMIT
          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
              (@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0) 
        and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
              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,
            [PERCENT],
            DESCRIPTION 
    end

    select
        AMOUNTRANGE, 
        case when @LIKELIHOOD1ID is not null then (select coalesce(sum(t.ASKCOUNT), 0) from @temp t where (t.LIKELIHOODID = @LIKELIHOOD1ID) and t.AMOUNTRANGE = OPPORTUNITYDATA.AMOUNTRANGE) else null end as LIKELIHOOD1ASKCOUNT, 
        case when @LIKELIHOOD2ID is not null then (select coalesce(sum(t.ASKCOUNT), 0) from @temp t where (t.LIKELIHOODID = @LIKELIHOOD2ID) and t.AMOUNTRANGE = OPPORTUNITYDATA.AMOUNTRANGE) else null end as LIKELIHOOD2ASKCOUNT, 
        case when @LIKELIHOOD3ID is not null then (select coalesce(sum(t.ASKCOUNT), 0) from @temp t where (t.LIKELIHOODID = @LIKELIHOOD3ID) and t.AMOUNTRANGE = OPPORTUNITYDATA.AMOUNTRANGE) else null end as LIKELIHOOD3ASKCOUNT, 
        case when @LIKELIHOOD4ID is not null then (select coalesce(sum(t.ASKCOUNT), 0) from @temp t where (t.LIKELIHOODID = @LIKELIHOOD4ID) and t.AMOUNTRANGE = OPPORTUNITYDATA.AMOUNTRANGE) else null end as LIKELIHOOD4ASKCOUNT, 
        case when @LIKELIHOOD5ID is not null then (select coalesce(sum(t.ASKCOUNT), 0) from @temp t where (t.LIKELIHOODID = @LIKELIHOOD5ID) and t.AMOUNTRANGE = OPPORTUNITYDATA.AMOUNTRANGE) else null end as LIKELIHOOD5ASKCOUNT
    from 
        @temp OPPORTUNITYDATA
    group by
        AMOUNTRANGE, LOWERLIMIT
    order by
        LOWERLIMIT

    select 
        @TOTALOPPORTUNITIES = sum(ASKCOUNT),
        @TOTALAMOUNT = sum(ASKAMOUNT),
        @TOTALPROJECTEDREVENUE = sum(PROJECTEDREVENUE)
    from @temp;