/**
* Formats the bean so it is ready to be handed back to the API's client. Certain fields will get extra processing
* to make them easier to work with from the client end.
*
* @param $bean SugarBean|ForecastManagerWorksheet The bean you want formatted
* @param $fieldList array Which fields do you want formatted and returned (leave blank for all fields)
* @param $options array Currently no options are supported
* @return array The bean in array format, ready for passing out the API to clients.
*/
public function formatForApi(SugarBean $bean, array $fieldList = array(), array $options = array())
{
$data = parent::formatForApi($bean, $fieldList, $options);
$sq = new SugarQuery();
$sq->select('date_modified');
$sq->from($bean)->where()->equals('assigned_user_id', $bean->assigned_user_id)->equals('user_id', $bean->user_id)->equals('draft', 0)->equals('timeperiod_id', $bean->timeperiod_id);
$beans = $sq->execute();
$data['show_history_log'] = 0;
if (empty($beans) && !empty($bean->fetched_row['date_modified'])) {
/* @var $tp TimePeriod */
$tp = BeanFactory::getBean('TimePeriods', $bean->timeperiod_id);
// When reportee has committed but manager has not
// make sure that the reportee actually has a commit for the timeperiod,
// this is to handle the case where the manager saves draft before the reportee can commit
$sq = new SugarQuery();
$sq->select('id');
$sq->from(BeanFactory::getBean('ForecastWorksheets'))->where()->equals('assigned_user_id', $bean->user_id)->equals('draft', 0)->queryAnd()->gte('date_closed_timestamp', $tp->start_date_timestamp)->lte('date_closed_timestamp', $tp->end_date_timestamp);
$worksheets = $sq->execute();
if (!empty($worksheets)) {
$data['show_history_log'] = 1;
}
} else {
if (!empty($beans)) {
$fBean = $beans[0];
$committed_date = $bean->db->fromConvert($fBean["date_modified"], "datetime");
if (strtotime($committed_date) < strtotime($bean->fetched_row['date_modified'])) {
$db = DBManagerFactory::getInstance();
// find the differences via the audit table
// we use a direct query since SugarQuery can't do the audit tables...
$sql = sprintf("SELECT field_name, before_value_string, after_value_string FROM %s\n WHERE parent_id = %s AND date_created >= " . $db->convert('%s', 'datetime'), $bean->get_audit_table_name(), $db->quoted($bean->id), $db->quoted($committed_date));
$results = $db->query($sql);
// get the setting for which fields to compare on
/* @var $admin Administration */
$admin = BeanFactory::getBean('Administration');
$settings = $admin->getConfigForModule('Forecasts', 'base');
while ($row = $db->fetchByAssoc($results)) {
$field = substr($row['field_name'], 0, strpos($row['field_name'], '_'));
if ($settings['show_worksheet_' . $field] == "1") {
// calculate the difference to make sure it actually changed at 2 digits vs changed at 6
$diff = SugarMath::init($row['after_value_string'], 6)->sub($row['before_value_string'])->result();
// due to decimal rounding on the front end, we only want to know about differences greater
// of two decimal places.
// todo-sfa: This hardcoded 0.01 value needs to be changed to a value determined by userprefs
if (abs($diff) >= 0.01) {
$data['show_history_log'] = 1;
break;
}
}
}
}
}
}
if (!empty($bean->user_id)) {
$data['is_manager'] = User::isManager($bean->user_id);
}
return $data;
}
/**
* Rounds the first param down into a specific precision
*/
public function evaluate()
{
$params = $this->getParameters();
$base = $params[0]->evaluate();
$precision = $params[1]->evaluate();
return SugarMath::init($base, $precision)->result();
}
/**
* This checks to see if the only thing that has changed is the quota, if it is, then don't update the date
* modified
*
* @param ForecastManagerWorksheet $worksheet The Bean
* @param string $event Which event is being fired
* @param array $params Extra Params
*/
public static function draftRecordQuotaOnlyCheck(ForecastManagerWorksheet $worksheet, $event, $params = array())
{
// this should only run on before_save and when the worksheet is a draft record
// and the draft_save_type is assign_quota
if ($event == 'before_save' && $worksheet->draft == 1 && $worksheet->draft_save_type == 'assign_quota') {
$mm = MetadataManager::getManager();
$views = $mm->getModuleViews($worksheet->module_name);
$fields = $views['list']['meta']['panels'][0]['fields'];
$onlyQuotaChanged = true;
foreach ($fields as $field) {
if ($field['type'] == 'currency' && preg_match('#\\.[\\d]{6}$#', $worksheet->{$field}['name']) === 0) {
$worksheet->{$field}['name'] = SugarMath::init($worksheet->{$field}['name'], 6)->result();
}
if ($worksheet->fetched_row[$field['name']] !== $worksheet->{$field}['name']) {
if ($field['name'] !== 'quota') {
$onlyQuotaChanged = false;
break;
}
}
}
if ($onlyQuotaChanged === true) {
$worksheet->update_date_modified = false;
}
}
}
/**
* Returns the negative of the expression that it contains.
*/
public function evaluate()
{
$params = $this->getParameters();
$base = $params[0]->evaluate();
$power = $params[1]->evaluate();
return SugarMath::init($base)->pow($power)->result();
}
/**
* The Logic for running in PHP, this uses SugarMath as to avoid potential floating-point errors
*
* @return String
*/
public function evaluate()
{
$product = '1';
foreach ($this->getParameters() as $expr) {
$product = SugarMath::init($product, 6)->mul($expr->evaluate())->result();
}
return (string) $product;
}
/**
* The Logic for running in PHP, this uses SugarMath as to avoid potential floating-point errors
*
* @returns String
*/
public function evaluate()
{
$sum = '0';
foreach ($this->getParameters() as $expr) {
$sum = SugarMath::init($sum, 6)->add($expr->evaluate())->result();
}
return (string) $sum;
}
/**
* The Logic for running in PHP, this uses SugarMath as to avoid potential floating-point errors
*
* @returns string
*/
public function evaluate()
{
$params = $this->getParameters();
$diff = $params[0]->evaluate();
for ($i = 1; $i < sizeof($params); $i++) {
$diff = SugarMath::init($diff, 6)->sub($params[$i]->evaluate())->result();
}
return (string) $diff;
}
/**
* The Logic for running in PHP, this uses SugarMath as to avoid potential floating-point errors
*
* @throws Exception
* @return String
*/
public function evaluate()
{
$params = $this->getParameters();
$numerator = $params[0]->evaluate();
$denominator = $params[1]->evaluate();
if ($denominator == 0) {
throw new Exception("Division by zero");
}
return (string) SugarMath::init($numerator, 6)->div($denominator)->result();
}
/**
* Returns itself when evaluating.
*/
public function evaluate()
{
$params = $this->getParameters();
$base = $params[1]->evaluate();
$value = $params[0]->evaluate();
if ($base == 1) {
throw new Exception("Log base can not be 1");
}
return SugarMath::init(log($value))->div(log($base))->result();
}
/**
* Returns itself when evaluating.
*/
public function evaluate()
{
$sum = 0;
$count = 0;
foreach ($this->getParameters() as $expr) {
$sum = SugarMath::init($sum)->add($expr->evaluate())->result();
$count++;
}
// since Expression guarantees at least 1 parameter
// we can safely assume / by 0 will not happen
return SugarMath::init($sum)->div($count)->result();
}
/**
* Returns the entire enumeration bare.
*/
public function evaluate()
{
$params = $this->getParameters();
//This should be of relate type, which means an array of SugarBean objects
$linkField = $params[0]->evaluate();
$relfield = $params[1]->evaluate();
$ret = '0';
if (!is_array($linkField) || empty($linkField)) {
return $ret;
}
foreach ($linkField as $bean) {
if (!empty($bean->{$relfield})) {
$ret = SugarMath::init($ret)->add($bean->{$relfield})->result();
}
}
return SugarMath::init($ret)->div(count($linkField))->result();
}
/**
* Ability only rollup specific values from related records when a field on the related record is equal to
* something.
*
* @return string
*/
public function evaluate()
{
$params = $this->getParameters();
// This should be of relate type, which means an array of SugarBean objects
$linkField = $params[0]->evaluate();
$relfield = $params[1]->evaluate();
$conditionalField = $params[2]->evaluate();
$conditionalValues = $params[3]->evaluate();
if (!is_array($conditionalValues)) {
$conditionalValues = array($conditionalValues);
}
$ret = '0';
if (!is_array($linkField) || empty($linkField)) {
return $ret;
}
if (!isset($this->context)) {
//If we don't have a context provided, we have to guess. This can be a large performance hit.
$this->setContext();
}
$toRate = isset($this->context->base_rate) ? $this->context->base_rate : null;
$checkedTypeForCurrency = false;
$relFieldIsCurrency = false;
foreach ($linkField as $bean) {
if (!in_array($bean->{$conditionalField}, $conditionalValues)) {
continue;
}
// only check the target field once to see if it's a currency field.
if ($checkedTypeForCurrency === false) {
$checkedTypeForCurrency = true;
$relFieldIsCurrency = $this->isCurrencyField($bean, $relfield);
}
if (!empty($bean->{$relfield})) {
$value = $bean->{$relfield};
// if we have a currency field, it needs to convert the value into the rate of the row it's
// being returned to.
if ($relFieldIsCurrency) {
$value = SugarCurrency::convertWithRate($value, $bean->base_rate, $toRate);
}
$ret = SugarMath::init($ret)->add($value)->result();
}
}
return $ret;
}
/**
* Returns itself when evaluating.
*/
public function evaluate()
{
$params = $this->getParameters();
$values = array();
// find the mean
$sum = 0;
$count = sizeof($params);
foreach ($params as $param) {
$value = $param->evaluate();
$values[] = $value;
$sum = SugarMath::init($sum)->add($value)->result();
}
$mean = SugarMath::init($sum)->div($count)->result();
// find the summation of deviations
$deviation_sum = 0;
foreach ($values as $value) {
$deviation_sum = SugarMath::init($value)->sub($mean)->pow(2)->add($deviation_sum)->result();
}
// find the std dev
return SugarMath::init()->exp('((1/?)*?)', array($count, $deviation_sum))->sqrt()->result();
}
/**
* Get the Numbers for the Manager View
*
* @return array
*/
public function getManagerProgress()
{
$user_id = $this->getArg('user_id');
$timeperiod_id = $this->getArg('timeperiod_id');
$getTargetQuota = (bool) $this->getArg('target_quota');
/* @var $mgr_worksheet ForecastManagerWorksheet */
$mgr_worksheet = BeanFactory::getBean('ForecastManagerWorksheets');
$totals = $mgr_worksheet->worksheetTotals($user_id, $timeperiod_id);
// pull the quota from the worksheet data since we need the draft records if they exist
// to show what could be in draft for the user, if they are the current user.
$totals['quota_amount'] = $totals['quota'];
// add the target quota to the return data if passed target_quota=true as a param
if ($getTargetQuota) {
/* @var $quotaBean Quota */
$quotaBean = BeanFactory::getBean('Quotas');
$quota = $quotaBean->getRollupQuota($timeperiod_id, $user_id, true);
$totals['target_quota_amount'] = $quota['amount'];
}
// we should send back the adjusted totals with out the closed_amount included.
foreach (array('worst_adjusted', 'likely_adjusted', 'best_adjusted') as $field) {
$totals[$field] = SugarMath::init($totals[$field])->sub($totals['closed_amount'])->result();
}
$totals['user_id'] = $user_id;
$totals['timeperiod_id'] = $timeperiod_id;
// unset some vars that come from the worksheet to avoid confusion with correct data
// coming from this endpoint for progress
unset($totals['pipeline_opp_count'], $totals['quota'], $totals['included_opp_count'], $totals['pipeline_amount']);
return $totals;
}
/**
* convert a currency with a given rate
*
* @access public
* @param float $amount
* @param float $fromRate rate to convert from (default base rate)
* @param float $toRate rate to convert to (default base rate)
* @param int $precision Optional decimal precision
* @return float converted amount
*/
public static function convertWithRate($amount, $fromRate = 1.0, $toRate = 1.0, $precision = 6)
{
// if rate is 0 or null, just return the amount
if (empty($fromRate) || empty($toRate)) {
return $amount;
}
// handle the use case for when a currency field get added to a module, where no default is set
// and if the $amount is passed in as a string but $amount is an empty string or $amount is null,
// we should default then we should return as the conversion is not needed
if (is_string($amount) && $amount === '' || is_null($amount)) {
return '0';
}
return SugarMath::init(0, $precision)->exp('?/?*?', array($amount, $fromRate, $toRate))->result();
}
/**
* This method emulates the Forecast Manager Worksheet calculateTotals method.
*
* @param string $userId
* @param string $timeperiodId
* @param boolean $useDraftRecords
* @return array|bool Return calculated totals or boolean false if timeperiod is not valid
*/
public function worksheetTotals($userId, $timeperiodId, $useDraftRecords = false)
{
/* @var $tp TimePeriod */
$tp = BeanFactory::getBean('TimePeriods', $timeperiodId);
if (empty($tp->id)) {
// timeperiod not found
return false;
}
$return = array("quota" => '0', "best_case" => '0', "best_adjusted" => '0', "likely_case" => '0', "likely_adjusted" => '0', "worst_case" => '0', "worst_adjusted" => '0', "included_opp_count" => 0, "pipeline_opp_count" => 0, "pipeline_amount" => '0', "closed_amount" => '0');
global $current_user;
require_once 'include/SugarQuery/SugarQuery.php';
$sq = new SugarQuery();
$bean_obj = BeanFactory::getBean($this->module_name);
$sq->select(array($bean_obj->getTableName() . '.*'));
$sq->from($bean_obj)->where()->equals('timeperiod_id', $tp->id)->equals('assigned_user_id', $userId)->equals('draft', $current_user->id == $userId || $useDraftRecords === true ? 1 : 0)->equals('deleted', 0);
$results = $sq->execute();
foreach ($results as $row) {
$return['quota'] = SugarMath::init($return['quota'], 6)->add(SugarCurrency::convertWithRate($row['quota'], $row['base_rate']))->result();
$return['best_case'] = SugarMath::init($return['best_case'], 6)->add(SugarCurrency::convertWithRate($row['best_case'], $row['base_rate']))->result();
$return['best_adjusted'] = SugarMath::init($return['best_adjusted'], 6)->add(SugarCurrency::convertWithRate($row['best_case_adjusted'], $row['base_rate']))->result();
$return['likely_case'] = SugarMath::init($return['likely_case'], 6)->add(SugarCurrency::convertWithRate($row['likely_case'], $row['base_rate']))->result();
$return['likely_adjusted'] = SugarMath::init($return['likely_adjusted'], 6)->add(SugarCurrency::convertWithRate($row['likely_case_adjusted'], $row['base_rate']))->result();
$return['worst_case'] = SugarMath::init($return['worst_case'], 6)->add(SugarCurrency::convertWithRate($row['worst_case'], $row['base_rate']))->result();
$return['worst_adjusted'] = SugarMath::init($return['worst_adjusted'], 6)->add(SugarCurrency::convertWithRate($row['worst_case_adjusted'], $row['base_rate']))->result();
$return['closed_amount'] = SugarMath::init($return['closed_amount'], 6)->add(SugarCurrency::convertWithRate($row['closed_amount'], $row['base_rate']))->result();
$return['included_opp_count'] += $row['opp_count'];
$return['pipeline_opp_count'] += $row['pipeline_opp_count'];
$return['pipeline_amount'] = SugarMath::init($return['pipeline_amount'], 6)->add($row['pipeline_amount'])->result();
}
return $return;
}
/**
* Used by the dependency manager to pre-load all the related fields required
* to load an entire view.
*/
public function getRelatedValues($api, $args)
{
if (empty($args['module']) || empty($args['fields'])) {
return;
}
$fields = json_decode(html_entity_decode($args['fields']), true);
$focus = $this->loadBean($api, $args);
$ret = array();
foreach ($fields as $rfDef) {
if (!isset($rfDef['link']) || !isset($rfDef['type'])) {
continue;
}
$link = $rfDef['link'];
$type = $rfDef['type'];
$rField = '';
if (!isset($ret[$link])) {
$ret[$link] = array();
}
if (empty($ret[$link][$type])) {
$ret[$link][$type] = array();
}
// count formulas don't have a relate attribute
if (isset($rfDef['relate'])) {
$rField = $rfDef['relate'];
}
switch ($type) {
//The Related function is used for pulling a sing field from a related record
case "related":
//Default it to a blank value
$ret[$link]['related'][$rfDef['relate']] = "";
//If we have neither a focus id nor a related record id, we can't retrieve anything
$relBean = null;
if (empty($rfDef['relId']) || empty($rfDef['relModule'])) {
//If the relationship is invalid, just move onto another field
if (!$focus->load_relationship($link)) {
break;
}
$beans = $focus->{$link}->getBeans(array("enforce_teams" => true));
//No related beans means no value
if (empty($beans)) {
break;
}
//Grab the first bean on the list
reset($beans);
$relBean = current($beans);
} else {
$relBean = BeanFactory::getBean($rfDef['relModule'], $rfDef['relId']);
}
//If we found a bean and the current user has access to the related field, grab a value from it
if (!empty($relBean) && ACLField::hasAccess($rfDef['relate'], $relBean->module_dir, $GLOBALS['current_user']->id, true)) {
$validFields = FormulaHelper::cleanFields($relBean->field_defs, false, true, true);
if (isset($validFields[$rfDef['relate']])) {
$ret[$link]['relId'] = $relBean->id;
$ret[$link]['related'][$rfDef['relate']] = FormulaHelper::getFieldValue($relBean, $rfDef['relate']);
}
}
break;
case "count":
if ($focus->load_relationship($link)) {
$ret[$link][$type] = count($focus->{$link}->get());
} else {
$ret[$link][$type] = 0;
}
break;
case "rollupSum":
case "rollupAve":
case "rollupMin":
case "rollupMax":
//If we are going to calculate one rollup, calculate all the rollups since there is so little cost
if ($focus->load_relationship($link)) {
$relBeans = $focus->{$link}->getBeans(array("enforce_teams" => true));
$sum = 0;
$count = 0;
$min = false;
$max = false;
if (!empty($relBeans)) {
//Check if the related record vardef has banned this field from formulas
$relBean = reset($relBeans);
$validFields = FormulaHelper::cleanFields($relBean->field_defs, false, true, true);
if (!isset($validFields[$rField])) {
$ret[$link][$type][$rField] = 0;
break;
}
}
foreach ($relBeans as $bean) {
if (isset($bean->{$rField}) && is_numeric($bean->{$rField}) && ACLField::hasAccess($rField, $bean->module_dir, $GLOBALS['current_user']->id, true)) {
$count++;
$sum += floatval($bean->{$rField});
if ($min === false || $bean->{$rField} < $min) {
$min = floatval($bean->{$rField});
}
if ($max === false || $bean->{$rField} > $max) {
$max = floatval($bean->{$rField});
}
}
}
if ($type == "rollupSum") {
$ret[$link][$type][$rField] = $sum;
}
if ($type == "rollupAve") {
$ret[$link][$type][$rField] = $count == 0 ? 0 : $sum / $count;
}
if ($type == "rollupMin") {
$ret[$link][$type][$rField] = $min;
}
if ($type == "rollupMax") {
$ret[$link][$type][$rField] = $max;
}
} else {
$ret[$link][$type][$rField] = 0;
}
break;
case "rollupCurrencySum":
$ret[$link][$type][$rField] = 0;
if ($focus->load_relationship($link)) {
$toRate = isset($focus->base_rate) ? $focus->base_rate : null;
$relBeans = $focus->{$link}->getBeans(array("enforce_teams" => true));
$sum = 0;
foreach ($relBeans as $bean) {
if (!empty($bean->{$rField}) && is_numeric($bean->{$rField}) && ACLField::hasAccess($rField, $bean->module_dir, $GLOBALS['current_user']->id, true)) {
$sum = SugarMath::init($sum)->add(SugarCurrency::convertWithRate($bean->{$rField}, $bean->base_rate, $toRate))->result();
}
}
$ret[$link][$type][$rField] = $sum;
}
break;
}
}
return $ret;
}
protected function setDiscountPrice()
{
if (!is_numeric($this->discount_price) && empty($this->product_template_id) && is_numeric($this->likely_case)) {
$quantity = floatval($this->quantity);
if (empty($quantity)) {
$quantity = 1;
}
$this->discount_price = SugarMath::init($this->likely_case)->div($quantity)->result();
}
}
/**
* Converts (copies) a Products (QuotedLineItem) to a Revenue Line Item
* @return RevenueLineItem
*/
public function convertToRevenueLineItem()
{
/* @var $rli RevenueLineItem */
$rli = BeanFactory::getBean('RevenueLineItems');
$rli->id = create_guid();
$rli->new_with_id = true;
$rli->fetched_row = array();
foreach ($this->getFieldDefinitions() as $field) {
if ($field['name'] != 'id' && isset($this->fetched_row[$field['name']])) {
$rli->{$field}['name'] = $this->fetched_row[$field['name']];
// set the fetched row, so we prevent the product_template from fetching again
// when the re-save happens because of the relationships
$rli->fetched_row[$field['name']] = $this->fetched_row[$field['name']];
}
}
if ($this->discount_select == 1) {
// we have a percentage discount, but we don't allow the use of percentages on
// the RevenueLineItem module yet, so we need to set discount_select to 0
// and calculate out the correct discount_amount.
$rli->discount_select = 0;
$rli->discount_amount = SugarMath::init()->exp('(?*?)*(?/100)', array($this->discount_price, $this->quantity, $this->discount_amount))->result();
}
// since we don't have a likely_case on products,
if ($rli->likely_case == '0.00') {
//undo bad math from quotes.
$rli->likely_case = $this->total_amount;
}
$this->revenuelineitem_id = $rli->id;
$this->ignoreQuoteSave = true;
$this->save();
return $rli;
}
/**
* @static
*
* @param Array $timeperiods Array of $timeperiod instances to build forecast data for
*/
public static function populateSeedData($timeperiods)
{
require_once 'modules/Forecasts/Common.php';
global $timedate, $current_user, $app_list_strings;
$user = BeanFactory::getBean('Users');
$comm = new Common();
$commit_order = $comm->get_forecast_commit_order();
// get what we are forecasting on
/* @var $admin Administration */
$admin = BeanFactory::getBean('Administration');
$settings = $admin->getConfigForModule('Forecasts');
$forecast_by = $settings['forecast_by'];
foreach ($timeperiods as $timeperiod_id => $timeperiod) {
foreach ($commit_order as $commit_type_array) {
//direct entry per user, and some user will have a Rollup entry too.
$ratio = array('.8', '1', '1.2', '1.4');
$key = array_rand($ratio);
if ($commit_type_array[1] == 'Direct') {
// get the worksheet total for a given user
/* @var $worksheet ForecastWorksheet */
$worksheet = BeanFactory::getBean('ForecastWorksheets');
$totals = $worksheet->worksheetTotals($timeperiod_id, $commit_type_array[0], $forecast_by, true);
if ($totals['total_opp_count'] == 0) {
continue;
}
/* @var $quota Quota */
$quota = BeanFactory::getBean('Quotas');
$quota->timeperiod_id = $timeperiod_id;
$quota->user_id = $commit_type_array[0];
$quota->quota_type = 'Direct';
$quota->currency_id = -99;
$quota->amount = SugarMath::init()->exp('?*?', array($totals['amount'], $ratio[$key]))->result();
$quota->amount_base_currency = $quota->amount;
$quota->committed = 1;
$quota->set_created_by = false;
if ($commit_type_array[0] == 'seed_sarah_id' || $commit_type_array[0] == 'seed_will_id' || $commit_type_array[0] == 'seed_jim_id') {
$quota->created_by = 'seed_jim_id';
} else {
if ($commit_type_array[0] == 'seed_sally_id' || $commit_type_array[0] == 'seed_max_id') {
$quota->created_by = 'seed_sarah_id';
} else {
if ($commit_type_array[0] == 'seed_chris_id') {
$quota->created_by = 'seed_will_id';
} else {
$quota->created_by = $current_user->id;
}
}
}
$quota->save();
if (!$user->isManager($commit_type_array[0])) {
/* @var $quotaRollup Quota */
$quotaRollup = BeanFactory::getBean('Quotas');
$quotaRollup->timeperiod_id = $timeperiod_id;
$quotaRollup->user_id = $commit_type_array[0];
$quotaRollup->quota_type = 'Rollup';
$quota->currency_id = -99;
$quotaRollup->amount = $quota->amount;
$quotaRollup->amount_base_currency = $quotaRollup->amount;
$quotaRollup->committed = 1;
$quotaRollup->set_created_by = false;
if ($commit_type_array[0] == 'seed_sarah_id' || $commit_type_array[0] == 'seed_will_id' || $commit_type_array[0] == 'seed_jim_id') {
$quotaRollup->created_by = 'seed_jim_id';
} else {
if ($commit_type_array[0] == 'seed_sally_id' || $commit_type_array[0] == 'seed_max_id') {
$quotaRollup->created_by = 'seed_sarah_id';
} else {
if ($commit_type_array[0] == 'seed_chris_id') {
$quotaRollup->created_by = 'seed_will_id';
} else {
$quotaRollup->created_by = $current_user->id;
}
}
}
$quotaRollup->save();
}
// create a previous forecast to simulate a change
/* @var $forecast Forecast */
$forecast = BeanFactory::getBean('Forecasts');
$forecast->timeperiod_id = $timeperiod_id;
$forecast->user_id = $commit_type_array[0];
$forecast->opp_count = $totals['included_opp_count'];
if ($totals['included_opp_count'] > 0) {
$forecast->opp_weigh_value = SugarMath::init()->setScale(0)->exp('(?/?)/?', array($totals['amount'], $ratio[$key], $totals['included_opp_count']))->result();
} else {
$forecast->opp_weigh_value = '0';
}
$forecast->best_case = SugarMath::init()->exp('(?+?)/?', array($totals['best_case'], $totals['won_best'], $ratio[$key]))->result();
$forecast->worst_case = SugarMath::init()->exp('(?+?)/?', array($totals['worst_case'], $totals['won_worst'], $ratio[$key]))->result();
$forecast->likely_case = SugarMath::init()->exp('(?+?)/?', array($totals['amount'], $totals['won_amount'], $ratio[$key]))->result();
$forecast->forecast_type = 'Direct';
$forecast->date_committed = $timedate->asDb($timedate->getNow()->modify("-1 day"));
$forecast->date_entered = $timedate->asDb($timedate->getNow()->modify("-1 day"));
$forecast->date_modified = $timedate->asDb($timedate->getNow()->modify("-1 day"));
$forecast->calculatePipelineData(SugarMath::init()->exp('?/?', array($totals['includedClosedAmount'], $ratio[$key]))->result(), $totals['includedClosedCount']);
$forecast->save();
self::createManagerWorksheet($commit_type_array[0], $forecast->toArray());
// create the current forecast
/* @var $forecast2 Forecast */
$forecast2 = BeanFactory::getBean('Forecasts');
$forecast2->timeperiod_id = $timeperiod_id;
$forecast2->user_id = $commit_type_array[0];
$forecast2->opp_count = $totals['included_opp_count'];
if ($totals['included_opp_count'] > 0) {
$forecast2->opp_weigh_value = SugarMath::init()->setScale(0)->exp('?/?', array($totals['amount'], $totals['included_opp_count']))->result();
} else {
$forecast2->opp_weigh_value = '0';
}
$forecast2->best_case = SugarMath::init($totals['best_case'])->add($totals['won_best'])->result();
$forecast2->worst_case = SugarMath::init($totals['worst_case'])->add($totals['won_worst'])->result();
$forecast2->likely_case = SugarMath::init($totals['amount'])->add($totals['won_amount'])->result();
$forecast2->forecast_type = 'Direct';
$forecast2->date_committed = $timedate->asDb($timedate->getNow());
$forecast2->calculatePipelineData($totals['includedClosedAmount'], $totals['includedClosedCount']);
$forecast2->save();
self::createManagerWorksheet($commit_type_array[0], $forecast2->toArray());
} else {
/* @var $mgr_worksheet ForecastManagerWorksheet */
$mgr_worksheet = BeanFactory::getBean('ForecastManagerWorksheets');
$totals = $mgr_worksheet->worksheetTotals($commit_type_array[0], $timeperiod_id, true);
if ($totals['included_opp_count'] == 0) {
continue;
}
/* @var $quota Quota */
$quota = BeanFactory::getBean('Quotas');
$quota->timeperiod_id = $timeperiod_id;
$quota->user_id = $commit_type_array[0];
$quota->quota_type = 'Rollup';
$quota->currency_id = -99;
$quota->amount = SugarMath::init($totals['quota'], 6)->mul($ratio[$key])->result();
$quota->amount_base_currency = $quota->amount;
$quota->committed = 1;
$quota->save();
/* @var $forecast Forecast */
$forecast = BeanFactory::getBean('Forecasts');
$forecast->timeperiod_id = $timeperiod_id;
$forecast->user_id = $commit_type_array[0];
$forecast->opp_count = $totals['included_opp_count'];
$forecast->opp_weigh_value = SugarMath::init()->setScale(0)->exp('?/?', array($totals['likely_adjusted'], $totals['included_opp_count']))->result();
$forecast->likely_case = $totals['likely_adjusted'];
$forecast->best_case = $totals['best_adjusted'];
$forecast->worst_case = $totals['worst_adjusted'];
$forecast->forecast_type = 'Rollup';
$forecast->pipeline_opp_count = $totals['pipeline_opp_count'];
$forecast->pipeline_amount = $totals['pipeline_amount'];
$forecast->closed_amount = $totals['closed_amount'];
$forecast->date_entered = $timedate->asDb($timedate->getNow());
$forecast->save();
self::createManagerWorksheet($commit_type_array[0], $forecast->toArray());
}
self::commitRepItems($commit_type_array[0], $timeperiod_id, $forecast_by);
}
// loop though all the managers and commit their forecast
$managers = array('seed_sarah_id', 'seed_will_id', 'seed_jim_id');
foreach ($managers as $manager) {
/* @var $user User */
$user = BeanFactory::getBean('Users', $manager);
/* @var $worksheet ForecastManagerWorksheet */
$worksheet = BeanFactory::getBean('ForecastManagerWorksheets');
$worksheet->commitManagerForecast($user, $timeperiod_id);
}
}
$admin = BeanFactory::getBean('Administration');
$admin->saveSetting('Forecasts', 'is_setup', 1, 'base');
// TODO-sfa - remove this once the ability to map buckets when they get changed is implemented (SFA-215).
// this locks the forecasts ranges configs if the apps is installed with demo data and already has commits
$admin->saveSetting('Forecasts', 'has_commits', 1, 'base');
}
/**
* @param ServiceBase $api
* @param array $args
* @return array
* @throws SugarApiExceptionInvalidParameter
* @throws SugarApiExceptionNotAuthorized
* @throws SugarApiExceptionNotFound
*/
public function pipeline(ServiceBase $api, $args)
{
// if not in the allowed module list, then we throw a 404 not found
if (!in_array($args['module'], $this->allowedModules)) {
throw new SugarApiExceptionNotFound();
}
// make sure we can view the module first
// since we don't have a proper record just make an empty one
$args['record'] = '';
/* @var $seed Opportunity|Product|RevenueLineItem */
$seed = $this->loadBean($api, $args, 'view');
if (!$seed->ACLAccess('view')) {
throw new SugarApiExceptionNotAuthorized();
}
$tp = $this->getTimeperiod($args['timeperiod_id']);
// check the type param
if (!isset($args['type']) || $args['type'] != 'user' && $args['type'] != 'group') {
$args['type'] = 'user';
}
$settings = $this->getForecastSettings();
// get sales_stages to ignore
$ignore_stages = array_merge($settings['sales_stage_won'], $settings['sales_stage_lost']);
// get the amount field here
$amount_field = $this->moduleAmountField[$seed->module_name];
$sq = $this->buildQuery($api, $seed, $tp, $amount_field, $args['type']);
// run the query
$rows = $sq->execute();
// data storage
$data = array();
// keep track of the total for later user
$total = SugarMath::init('0', 0);
foreach ($rows as $row) {
// if the sales stage is one we need to ignore, the just continue to the next record
if (in_array($row['sales_stage'], $ignore_stages)) {
continue;
}
// if we have not seen this sales stage before, set the value to zero (0)
if (!isset($data[$row['sales_stage']])) {
$data[$row['sales_stage']] = array('count' => 0, 'total' => '0');
}
// if customers have made amount not required, it saves to the DB as NULL
// make sure we set it to 0 for the math ahead
if (empty($row['amount'])) {
$row['amount'] = 0;
}
// convert to the base currency
$base_amount = SugarCurrency::convertWithRate($row[$amount_field], $row['base_rate']);
// add the new value into what was already there
$data[$row['sales_stage']]['total'] = SugarMath::init($data[$row['sales_stage']]['total'], 0)->add($base_amount)->result();
$data[$row['sales_stage']]['count']++;
// add to the total
$total->add($base_amount);
}
// get the default currency
/* @var $currency Currency */
$currency = SugarCurrency::getBaseCurrency();
// setup for return format
$return_data = array();
$series = 0;
$previous_value = SugarMath::init('0', 0);
foreach ($data as $key => $item) {
$value = $item['total'];
// set up each return key
$return_data[] = array('key' => $key, 'count' => $item['count'], 'values' => array(array('series' => $series++, 'label' => SugarCurrency::formatAmount($value, $currency->id, 0), 'value' => intval($value), 'x' => 0, 'y' => intval($value), 'y0' => intval($previous_value->result()))));
// save the previous value for use in the next item in the series
$previous_value->add($value);
}
// actually return the formatted data
$mod_strings = return_module_language($GLOBALS['current_language'], $seed->module_name);
//return the total from the SugarMath Object.
$total = $total->result();
return array('properties' => array('title' => $mod_strings['LBL_PIPELINE_TOTAL_IS'] . SugarCurrency::formatAmount($total, $currency->id), 'total' => $total, 'scale' => 1000, 'units' => $currency->symbol), 'data' => $return_data);
}
/**
* Returns the negative of the expression that it contains.
*/
public function evaluate()
{
return SugarMath::init('-1')->mul($this->getParameters()->evaluate())->result();
}
/**
* This method emulates the Forecast Rep Worksheet calculateTotals method.
*
* @param string $timeperiod_id
* @param string $user_id
* @param string|null $forecast_by
* @param boolean $useDraftRecords
* @return array|bool
*/
public function worksheetTotals($timeperiod_id, $user_id, $forecast_by = null, $useDraftRecords = false)
{
/* @var $tp TimePeriod */
$tp = BeanFactory::getBean('TimePeriods', $timeperiod_id);
if (empty($tp->id)) {
// timeperiod not found
return false;
}
/* @var $admin Administration */
$admin = BeanFactory::getBean('Administration');
$settings = $admin->getConfigForModule('Forecasts');
if (is_null($forecast_by)) {
$forecast_by = $settings['forecast_by'];
}
// setup the return array
$return = array('amount' => '0', 'best_case' => '0', 'worst_case' => '0', 'overall_amount' => '0', 'overall_best' => '0', 'overall_worst' => '0', 'timeperiod_id' => $tp->id, 'lost_count' => '0', 'lost_amount' => '0', 'lost_best' => '0', 'lost_worst' => '0', 'won_count' => '0', 'won_amount' => '0', 'won_best' => '0', 'won_worst' => '0', 'included_opp_count' => 0, 'total_opp_count' => 0, 'includedClosedCount' => 0, 'includedClosedAmount' => '0', 'includedClosedBest' => '0', 'includedClosedWorst' => '0', 'pipeline_amount' => '0', 'pipeline_opp_count' => 0, 'closed_amount' => '0', 'includedIdsInLikelyTotal' => array());
global $current_user;
$sq = new SugarQuery();
$bean_obj = BeanFactory::getBean($this->module_name);
$sq->select(array($bean_obj->getTableName() . '.*'));
$sq->from($bean_obj)->where()->equals('assigned_user_id', $user_id)->equals('parent_type', $forecast_by)->equals('deleted', 0)->equals('draft', $current_user->id == $user_id || $useDraftRecords === true ? 1 : 0)->queryAnd()->gte('date_closed_timestamp', $tp->start_date_timestamp)->lte('date_closed_timestamp', $tp->end_date_timestamp);
$results = $sq->execute();
foreach ($results as $row) {
// if customers have made likely_case, best_case, or worst_case not required,
// it saves to the DB as NULL, make sure we set it to 0 for the math ahead
if (empty($row['likely_case'])) {
$row['likely_case'] = 0;
}
if (empty($row['best_case'])) {
$row['best_case'] = 0;
}
if (empty($row['worst_case'])) {
$row['worst_case'] = 0;
}
$worst_base = SugarCurrency::convertWithRate($row['worst_case'], $row['base_rate']);
$amount_base = SugarCurrency::convertWithRate($row['likely_case'], $row['base_rate']);
$best_base = SugarCurrency::convertWithRate($row['best_case'], $row['base_rate']);
$closed = false;
if (in_array($row['sales_stage'], $settings['sales_stage_won']) && in_array($row['commit_stage'], $settings['commit_stages_included'])) {
$return['won_amount'] = SugarMath::init($return['won_amount'], 6)->add($amount_base)->result();
$return['won_best'] = SugarMath::init($return['won_best'], 6)->add($best_base)->result();
$return['won_worst'] = SugarMath::init($return['won_worst'], 6)->add($worst_base)->result();
$return['won_count']++;
$return['includedClosedCount']++;
$return['includedClosedAmount'] = SugarMath::init($return['includedClosedAmount'], 6)->add($amount_base)->result();
$closed = true;
} elseif (in_array($row['sales_stage'], $settings['sales_stage_lost'])) {
$return['lost_amount'] = SugarMath::init($return['lost_amount'], 6)->add($amount_base)->result();
$return['lost_best'] = SugarMath::init($return['lost_best'], 6)->add($best_base)->result();
$return['lost_worst'] = SugarMath::init($return['lost_worst'], 6)->add($worst_base)->result();
$return['lost_count']++;
$closed = true;
}
if (in_array($row['commit_stage'], $settings['commit_stages_included'])) {
if (!$closed) {
$return['amount'] = SugarMath::init($return['amount'], 6)->add($amount_base)->result();
$return['best_case'] = SugarMath::init($return['best_case'], 6)->add($best_base)->result();
$return['worst_case'] = SugarMath::init($return['worst_case'], 6)->add($worst_base)->result();
// add RLI/Opp id to includedIds array
array_push($return['includedIdsInLikelyTotal'], $row['parent_id']);
}
$return['included_opp_count']++;
if ($closed) {
$return['includedClosedBest'] = SugarMath::init($return['includedClosedBest'], 6)->add($best_base)->result();
$return['includedClosedWorst'] = SugarMath::init($return['includedClosedWorst'], 6)->add($worst_base)->result();
}
}
$return['total_opp_count']++;
$return['overall_amount'] = SugarMath::init($return['overall_amount'], 6)->add($amount_base)->result();
$return['overall_best'] = SugarMath::init($return['overall_best'], 6)->add($best_base)->result();
$return['overall_worst'] = SugarMath::init($return['overall_worst'], 6)->add($worst_base)->result();
}
// send back the totals
return $return;
}
/**
* Take a list of RLI's and make them into a new Product Bundle
*
* @param array $rlis
* @param string $quote_id The id for the quote we are creating
* @return ProductBundle
*/
protected function createProductBundleFromRLIList(array $rlis, $quote_id = null)
{
/* @var $product_bundle ProductBundle */
$product_bundle = BeanFactory::getBean('ProductBundles');
$product_bundle->id = create_guid();
$product_bundle->new_with_id = true;
$subtotal = SugarMath::init(0);
$deal_tot = SugarMath::init(0);
foreach ($rlis as $key => $rli_id) {
/* @var $rli RevenueLineItem */
$rli = BeanFactory::getBean('RevenueLineItems', $rli_id);
/* @var $product Product */
$product = $rli->convertToQuotedLineItem();
$product_bundle->set_relationship('product_bundle_product', array('bundle_id' => $product_bundle->id, 'product_id' => $product->id, 'product_index' => $key + 1));
if (!is_null($quote_id)) {
$product->quote_id = $quote_id;
$product->status = Product::STATUS_QUOTED;
# Set the quote_id on the product so we know it's linked
$rli->quote_id = $quote_id;
$rli->status = RevenueLineItem::STATUS_QUOTED;
$rli->save();
}
$product->save();
}
$product_bundle->bundle_stage = 'Draft';
$product_bundle->currency_id = $product->currency_id;
$product_bundle->base_rate = $product->base_rate;
$product_bundle->save();
return $product_bundle;
}
/**
* Save any committed values
*
* @return array|mixed
*/
public function save()
{
global $current_user;
$args = $this->getArgs();
$db = DBManagerFactory::getInstance();
if (!isset($args['timeperiod_id']) || empty($args['timeperiod_id'])) {
$args['timeperiod_id'] = TimePeriod::getCurrentId();
}
$commit_type = strtolower($this->getArg('commit_type'));
/* @var $mgr_worksheet ForecastManagerWorksheet */
$mgr_worksheet = BeanFactory::getBean('ForecastManagerWorksheets');
/* @var $worksheet ForecastWorksheet */
$worksheet = BeanFactory::getBean('ForecastWorksheets');
$field_ext = '_case';
if ($commit_type == "manager") {
$worksheet_totals = $mgr_worksheet->worksheetTotals($current_user->id, $args['timeperiod_id']);
// we don't need the *_case values so lets make them the same as the *_adjusted values
$field_ext = '_adjusted';
} else {
$worksheet_totals = $worksheet->worksheetTotals($args['timeperiod_id'], $current_user->id);
// set likely
$worksheet_totals['likely_case'] = SugarMath::init($worksheet_totals['amount'], 6)->add($worksheet_totals['includedClosedAmount'])->result();
$worksheet_totals['best_case'] = SugarMath::init($worksheet_totals['best_case'], 6)->add($worksheet_totals['includedClosedBest'])->result();
$worksheet_totals['worst_case'] = SugarMath::init($worksheet_totals['worst_case'], 6)->add($worksheet_totals['includedClosedWorst'])->result();
}
/* @var $forecast Forecast */
$forecast = BeanFactory::getBean('Forecasts');
$forecast->user_id = $current_user->id;
$forecast->timeperiod_id = $args['timeperiod_id'];
$forecast->best_case = $worksheet_totals['best' . $field_ext];
$forecast->likely_case = $worksheet_totals['likely' . $field_ext];
$forecast->worst_case = $worksheet_totals['worst' . $field_ext];
$forecast->forecast_type = $args['forecast_type'];
$forecast->opp_count = $worksheet_totals['included_opp_count'];
$forecast->currency_id = '-99';
$forecast->base_rate = '1';
//If we are committing a rep forecast, calculate things. Otherwise, for a manager, just use what is passed in.
if ($args['commit_type'] == 'sales_rep') {
$forecast->calculatePipelineData($worksheet_totals['includedClosedAmount'], $worksheet_totals['includedClosedCount']);
//push the pipeline numbers back into the args
$args['pipeline_opp_count'] = $forecast->pipeline_opp_count;
$args['pipeline_amount'] = $forecast->pipeline_amount;
$worksheet_totals['closed_amount'] = $forecast->closed_amount;
} else {
$forecast->pipeline_opp_count = $worksheet_totals['pipeline_opp_count'];
$forecast->pipeline_amount = $worksheet_totals['pipeline_amount'];
$forecast->closed_amount = $worksheet_totals['closed_amount'];
}
if ($worksheet_totals['likely_case'] != 0 && $worksheet_totals['included_opp_count'] != 0) {
$forecast->opp_weigh_value = $worksheet_totals['likely_case'] / $worksheet_totals['included_opp_count'];
}
$forecast->save();
// roll up the committed forecast to that person manager view
// copy the object so we can set some needed values
$mgr_rollup_data = $worksheet_totals;
$mgr_rollup_data['forecast_type'] = $args['forecast_type'];
// pass same timeperiod as the other data to the manager's rollup
$mgr_rollup_data['timeperiod_id'] = $args['timeperiod_id'];
$mgr_worksheet->reporteeForecastRollUp($current_user, $mgr_rollup_data);
if ($this->getArg('commit_type') == "sales_rep") {
$worksheet->commitWorksheet($current_user->id, $args['timeperiod_id']);
} elseif ($this->getArg('commit_type') == "manager") {
$mgr_worksheet->commitManagerForecast($current_user, $args['timeperiod_id']);
}
//TODO-sfa remove this once the ability to map buckets when they get changed is implemented (SFA-215).
$admin = BeanFactory::getBean('Administration');
$settings = $admin->getConfigForModule('Forecasts');
if (!isset($settings['has_commits']) || !$settings['has_commits']) {
$admin->saveSetting('Forecasts', 'has_commits', true, 'base');
MetaDataManager::refreshModulesCache(array('Forecasts'));
}
$forecast->date_entered = $this->convertDateTimeToISO($db->fromConvert($forecast->date_entered, 'datetime'));
$forecast->date_modified = $this->convertDateTimeToISO($db->fromConvert($forecast->date_modified, 'datetime'));
return $worksheet_totals;
}