Filtering Opportunities by Probability on Forecast Module

[ianmaciel]

Hey there,


I'm trying to make a filter on Forecast modules:


By default, Sugar show all opportunities open for the user, and calculate Probability * Value.


Different from Sugar default our company want to sum only opportunities with more than 65% and use the real value.
Trying to make it work, I found the file "/modules/Forecasts/ForecastOpportunities.php", and edited the code:


I changed a couple parts of the code, changing code like this:

//$query .= " opportunities.id, opportunities.name , amount_usdollar as revenue, ((amount_usdollar * probability)/100) as weighted_value, probability";

to this:

$query .= " opportunities.id, opportunities.name , amount_usdollar as revenue, amount_usdollar as weighted_value, probability";

I also looked for the "where" entries, and ADD, here:
$ret_array['where'] = " INNER JOIN timeperiods on 1=1 LEFT JOIN worksheet on opportunities.id = worksheet.related_id and worksheet.user_id='{$this->fo_user_id}' and worksheet.timeperiod_id='{$this->fo_timeperiod_id}' and worksheet.forecast_type='{$this->fo_forecast_type}' AND opportunities.probability >= 65";

and here:
$query1 .= " WHERE date_closed >= timeperiods.start_date";
$query1 .= " AND opportunities.probability >= 65";



The first custom worked fine: the sugar doesn't calculate the value automatically, it gets the full value of the opportunity.

But the second one, is not work. The forecast page keep showing all the opportunities, including the ones which have less than 65% of probability.

Does anybody know what I am doing wrong?

Ian Maciel

[ianmaciel]

Solved!

including the customs above and these other changes below...
If you are using SugarPRO 6.0.0 and want to do this, just change the original files for this file attached.


I've changed this:
$ret_array['where'] = " INNER JOIN timeperiods on 1=1 LEFT JOIN worksheet on opportunities.id = worksheet.related_id and worksheet.user_id='{$this->fo_user_id}' and worksheet.timeperiod_id='{$this->fo_timeperiod_id}' and worksheet.forecast_type='{$this->fo_forecast_type}' AND opportunities.probability >= 65";

to:
$ret_array['where'] = " INNER JOIN timeperiods on 1=1 LEFT JOIN worksheet on opportunities.id = worksheet.related_id and worksheet.user_id='{$this->fo_user_id}' and worksheet.timeperiod_id='{$this->fo_timeperiod_id}' and worksheet.forecast_type='{$this->fo_forecast_type}'";


and than, changed:
$ret_array['where'] .= ' WHERE '. $where;

to:
$ret_array['where'] .= ' WHERE '. $where .' AND opportunities.probability>=65';