/**
* 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;
}
public function addVisibilityWhereQuery(SugarQuery $sugarQuery, $options = array())
{
$where = null;
$this->addVisibilityWhere($where, $options);
if (!empty($where)) {
$sugarQuery->where()->addRaw($where);
}
return $sugarQuery;
}
/**
* Add visibility clauses to the WHERE part of the query for SugarQuery Object
*
* @param SugarQuery $query
*
* @return SugarQuery
*/
public function addVisibilityWhereQuery(SugarQuery $query)
{
global $current_user;
if (!empty($this->targetModuleField) && !$current_user->isAdmin()) {
$modules = $current_user->getDeveloperModules();
if (empty($modules)) {
$modules = array('');
}
$query->where()->in($this->targetModuleField, $modules);
}
return $query;
}
/**
* Retrieves all saved reports that meet args-driven criteria
*
* @param $api ServiceBase The API class of the request
* @param $args array The arguments array passed in from the API
* @return array
*/
public function getSavedReports($api, $args)
{
// Make sure the user isn't seeing reports they don't have access to
require_once 'modules/Reports/SavedReport.php';
$modules = array_keys(getACLDisAllowedModules());
$fieldList = array('id', 'name', 'module', 'report_type', 'content', 'chart_type', 'assigned_user_id');
$sq = new SugarQuery();
$sq->from(BeanFactory::getBean('Reports'));
$sq->select($fieldList);
$sq->orderBy('name', 'asc');
// if there were restricted modules, add those to the query
if (count($modules)) {
$sq->where()->notIn('module', $modules);
}
if (isset($args['has_charts']) && $args['has_charts'] == 'true') {
$sq->where()->notEquals('chart_type', 'none');
}
if (isset($args['module']) && $args['module'] !== '') {
$sq->where()->in('module', array($args['module']));
}
$result = $sq->execute();
// check acls
foreach ($result as $key => &$row) {
$savedReport = $this->getSavedReportFromData($row);
if ($savedReport->ACLAccess('list')) {
// for front-end to check acls
$row['_acl'] = ApiHelper::getHelper($api, $savedReport)->getBeanAcl($savedReport, $fieldList);
} else {
unset($result[$key]);
}
}
return $result;
}
/**
* Add visibility clauses to the FROM part of the query
*
* @param string $query
*
* @return string
*/
public function addVisibilityFromQuery(SugarQuery $query)
{
if (!empty($this->parentLink)) {
$linkName = $this->parentLink;
$query->from->load_relationship($linkName);
if (empty($query->from->{$linkName})) {
throw new SugarApiExceptionInvalidParameter("Invalid link {$linkName} for owner clause");
}
if ($query->from->{$linkName}->getType() == "many") {
throw new SugarApiExceptionInvalidParameter("Cannot serch for owners through multi-link {$linkName}");
}
$this->join = $query->join($linkName, array('joinType' => 'LEFT'));
}
return $query;
}
/**
* Makin' the magic in the sugar field
* @param $field
* @param SugarQuery $query
*/
public function __construct($field, SugarQuery $query)
{
if (is_array($field)) {
$this->field = $field[0];
$this->alias = $field[1];
if (!empty($field[2])) {
$this->original_alias = $field[2];
}
} else {
$this->field = $field;
}
if ($query->getFromBean()) {
$this->setupField($query);
$this->shouldMarkNonDb();
}
}
/**
* Formats the bean so it is ready to be handed back to the API's client. Certian fields will get extra processing
* to make them easier to work with from the client end.
*
* @param $bean SugarBean|ForecastWorksheet 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);
$data['parent_deleted'] = 0;
if ($bean->draft == 0) {
$sq = new SugarQuery();
$sq->select('id');
$sq->from(BeanFactory::getBean($bean->parent_type))->where()->equals('id', $bean->parent_id);
$beans = $sq->execute();
if (empty($beans)) {
$data['parent_name'] = $data['name'];
$data['parent_deleted'] = 1;
}
}
return $data;
}
/**
* Prepares value for insertion into SQL
* @param string $value
* @param SugarQuery_Builder_Condition $condition
* @return string
*/
protected function prepareValue($value, SugarQuery_Builder_Condition $condition)
{
if ($this->sugar_query->usePreparedStatements && !empty($condition->field->def) && !$value instanceof SugarQuery_Builder_Literal) {
$type = $this->db->getFieldType($condition->field->def);
$this->sugar_query->addData($condition->field->quoteValue($value, $condition->operator, true));
return "?{$type}";
} else {
return $condition->field->quoteValue($value, $condition->operator);
}
}
public static function get(SugarBean $bean)
{
$query = new SugarQuery();
$query->from(BeanFactory::get('dm_DedupeHashes'));
$query->select('id');
$dupeRules = $query->join('dm_duplicaterules_dm_dedupehashes')->joinName();
$query->where()->equals("{$dupeRules}.bean_module", $bean->module_name);
$query->where()->equals("bean_id", $bean->id);
$GLOBALS['log']->error("Getting Dedupe Hashes");
$GLOBALS['log']->error($query->compileSQL());
$results = $query->execute();
return $results;
}
public static function retrieveRecycled($right_id, $relationship, $left_id)
{
$query = new SugarQuery();
$query->select(array('id'));
$query->from(BeanFactory::getBean("dm_RecycledLinks"));
$query->where()->equals('right_id', $right_id);
$query->where()->equals('relatiomship', $relationship);
$query->where()->equals('left_id', $left_id);
$query->where()->equals('deleted', 0);
$query->limit(1);
$results = $query->execute();
if (count($results) > 0) {
foreach ($results as $result) {
return BeanFactory::getBean('dm_RecycledLinks', $result['id']);
}
}
return false;
}
public static function retrieveRecycled(SugarBean $bean)
{
$query = new SugarQuery();
$query->select(array('id'));
$query->from(BeanFactory::getBean("dm_Recycler"));
$query->where()->equals('bean_module', $bean->module_name);
$query->where()->equals('bean_id', $bean->id);
$query->where()->equals('deleted', 0);
$query->limit(1);
$results = $query->execute();
if (count($results) > 0) {
foreach ($results as $result) {
return BeanFactory::getBean('dm_Recycler', $result['id']);
}
}
return false;
}
/**
* Checks to see if the given user is an invitee on the meeting
*
* @param $userId
* @param $meetingBean
* @return bool
*/
protected function isUserInvitedToMeeting($userId, $meetingBean)
{
$query = new SugarQuery();
$query->select(array('id'));
$query->from($meetingBean);
$query->join('users', array('alias' => 'users'));
$query->where()->equals('meetings.id', $meetingBean->id)->equals('users.id', $userId);
$results = $query->execute();
return count($results) > 0;
}
/**
* For Floats we need to round down to the precision of the passed in value, since the db's could be showing
* something different
*
* @param Number $value The value for which we are trying to filter
* @param String $fieldName What field we are trying to modify
* @param SugarBean $bean The associated SugarBean
* @param SugarQuery $q The full query object
* @param SugarQuery_Builder_Where $where The where object for the filter
* @param String $op The filter operation we are trying to do
* @return bool
* @throws SugarApiExceptionInvalidParameter
*/
public function fixForFilter(&$value, $fieldName, SugarBean $bean, SugarQuery $q, SugarQuery_Builder_Where $where, $op)
{
// if we have an array, pull the first value
if (is_array($value)) {
$v = $value[1];
} else {
$v = $value;
}
$decimal_separator_location = substr(strrchr($v, '.'), 1);
// if we don't have a decimal, just use the normal methods back up the chain
// since it's a whole number that is being searched on
if ($decimal_separator_location === false) {
return true;
}
// ROUND(<value>, <precision>) is the standard across all DB's we support
$field = "ROUND({$fieldName}, " . strlen($decimal_separator_location) . ")";
switch ($op) {
case '$equals':
$q->whereRaw("{$field} = {$value}");
return false;
case '$not_equals':
$q->whereRaw("{$field} != {$value}");
return false;
case '$between':
if (!is_array($value) || count($value) != 2) {
throw new SugarApiExceptionInvalidParameter('$between requires an array with two values.');
}
$q->whereRaw("{$field} BETWEEN {$value['0']} AND {$value['1']}");
return false;
case '$lt':
$q->whereRaw("{$field} < {$value}");
return false;
case '$lte':
$q->whereRaw("{$field} <= {$value}");
return false;
case '$gt':
$q->whereRaw("{$field} > {$value}");
return false;
case '$gte':
$q->whereRaw("{$field} >= {$value}");
return false;
}
return true;
}
public function opportunityStats($api, $args)
{
// TODO make all APIs wrapped on tries and catches
// TODO: move this to own module (in this case accounts)
// TODO: Fix information leakage if user cannot list or view records not
// belonging to them. It's hard to tell if the user has access if we
// never get the bean.
// Check for permissions on both Accounts and opportunities.
// Load up the bean
$record = BeanFactory::getBean($args['module'], $args['record']);
if (!$record->ACLAccess('view')) {
return;
}
// Load up the relationship
if (!$record->load_relationship('opportunities')) {
// The relationship did not load, I'm guessing it doesn't exist
return;
}
// Figure out what is on the other side of this relationship, check permissions
$linkModuleName = $record->opportunities->getRelatedModuleName();
$linkSeed = BeanFactory::newBean($linkModuleName);
if (!$linkSeed->ACLAccess('view')) {
return;
}
$status_field = $this->getOpportunityStatusField();
$query = new SugarQuery();
$query->select(array($status_field, 'amount_usdollar'));
$query->from($linkSeed);
// making this more generic so we can use this on contacts also as soon
// as we move it to a proper module
$query->join('accounts', array('alias' => 'record'));
$query->where()->equals('record.id', $record->id);
// FIXME add the security query here!!!
// TODO: When we can sum on the database side through SugarQuery, we can
// use the group by statement.
$results = $query->execute();
// TODO this can't be done this way since we can change the status on
// studio and add more
$data = array('won' => array('amount_usdollar' => 0, 'count' => 0), 'lost' => array('amount_usdollar' => 0, 'count' => 0), 'active' => array('amount_usdollar' => 0, 'count' => 0));
foreach ($results as $row) {
$map = array('Closed Lost' => 'lost', 'Closed Won' => 'won');
if (array_key_exists($row[$status_field], $map)) {
$status = $map[$row[$status_field]];
} else {
$status = 'active';
}
$data[$status]['amount_usdollar'] += $row['amount_usdollar'];
$data[$status]['count']++;
}
return $data;
}
/**
* Test to see if the all the Jobs in the group are done.
*
* @return bool
* @throws SugarQueryException
*/
protected function isJobGroupDone()
{
// when we don't have a job_group, return true
if (empty($this->job->job_group)) {
return true;
}
$sq = new SugarQuery();
$sq->select(array('job_group'))->fieldRaw('count(0)', 'total_jobs')->fieldRaw('sum(case when status = \'done\' AND resolution = \'success\' then 1 else 0 END)', 'total_done');
$sq->from(BeanFactory::getBean('SchedulersJobs'));
$sq->where()->equals('job_group', $this->job->job_group);
$sq->groupBy('job_group');
$results = $sq->execute();
$result = array_shift($results);
return $result['total_jobs'] === $result['total_done'];
}
public function salesByCountry($api, $args)
{
// TODO: Fix information leakage if user cannot list or view records not
// belonging to them. It's hard to tell if the user has access if we
// never get the bean.
// Check for permissions on both Revenue line times and accounts.
$seed = BeanFactory::newBean('RevenueLineItems');
if (!$seed->ACLAccess('view')) {
return;
}
// Load up the relationship
if (!$seed->load_relationship('account_link')) {
// The relationship did not load, I'm guessing it doesn't exist
return;
}
// Figure out what is on the other side of this relationship, check permissions
$linkModuleName = $seed->account_link->getRelatedModuleName();
$linkSeed = BeanFactory::newBean($linkModuleName);
if (!$linkSeed->ACLAccess('view')) {
return;
}
$query = new SugarQuery();
$query->from($seed);
$account_link = $query->join('account_link');
$query->select(array($account_link->joinName() . '.billing_address_country', $account_link->joinName() . '.billing_address_state', 'likely_case', 'base_rate'));
$query->where()->equals('sales_stage', 'Closed Won');
// TODO: When we can sum on the database side through SugarQuery, we can
// use the group by statement.
$data = array();
$results = $query->execute();
foreach ($results as $row) {
if (empty($data[$row['billing_address_country']])) {
$data[$row['billing_address_country']] = array('_total' => 0);
}
if (empty($data[$row['billing_address_country']][$row['billing_address_state']])) {
$data[$row['billing_address_country']][$row['billing_address_state']] = array('_total' => 0);
}
$data[$row['billing_address_country']]['_total'] += $row['likely_case'] / $row['base_rate'];
$data[$row['billing_address_country']][$row['billing_address_state']]['_total'] += $row['likely_case'] / $row['base_rate'];
}
return $data;
}
public function run()
{
if (version_compare($this->from_version, '7.2.2', '>=')) {
return;
}
$qty = 100;
$chunk = 0;
$bean = BeanFactory::getBean('UserPreferences');
do {
$fetchCount = 0;
$query = new SugarQuery();
$query->from($bean);
$query->limit($qty);
$query->offset($chunk * $qty);
$query->where()->equals('category', 'global');
$query->select(array('id', 'assigned_user_id', 'contents'));
$rows = $query->execute();
foreach ($rows as $row) {
$fetchCount++;
$preferences = @unserialize(base64_decode($row['contents']));
if (!empty($preferences)) {
foreach ($preferences as $key => $value) {
if (substr($key, -1) == 'Q') {
$insert = array('category' => "sq_{$key}", 'deleted' => 0, 'assigned_user_id' => $row['assigned_user_id'], 'contents' => base64_encode(serialize($value)));
$bean->populateFromRow($insert);
$bean->save();
unset($preferences[$key]);
}
}
$insert = array('id' => $row['id'], 'category' => 'global', 'deleted' => 0, 'assigned_user_id' => $row['assigned_user_id'], 'contents' => base64_encode(serialize($preferences)));
$bean->populateFromRow($insert);
$bean->save();
}
}
$chunk++;
} while ($fetchCount == $qty);
}
/**
* Process to get an array of Timeperiods based on system configurations. It will return the n number
* of backward timeperiods + current set of timeperiod + n number of future timeperiods.
*
* @return array id/name of TimePeriods
*/
public function process()
{
$admin = BeanFactory::getBean('Administration');
$settings = $admin->getConfigForModule('Forecasts', 'base');
$forward = $settings['timeperiod_shown_forward'];
$backward = $settings['timeperiod_shown_backward'];
$type = $settings['timeperiod_interval'];
$leafType = $settings['timeperiod_leaf_interval'];
$timedate = TimeDate::getInstance();
$timePeriods = array();
$current = TimePeriod::getCurrentTimePeriod($type);
//If the current TimePeriod cannot be found for the type, just create one using the current date as a reference point
if (empty($current)) {
$current = TimePeriod::getByType($type);
$current->setStartDate($timedate->getNow()->asDbDate());
}
$startDate = $timedate->fromDbDate($current->start_date);
//Move back for the number of backward TimePeriod(s)
while ($backward-- > 0) {
$startDate->modify($current->previous_date_modifier);
}
$endDate = $timedate->fromDbDate($current->end_date);
//Increment for the number of forward TimePeriod(s)
while ($forward-- > 0) {
$endDate->modify($current->next_date_modifier);
}
$db = DBManagerFactory::getInstance();
$sq = new SugarQuery();
$sq->from(BeanFactory::getBean('TimePeriods'));
$sq->select(array('id', 'name'));
$sq->where()->notNull('parent_id')->gte('start_date', $startDate->asDbDate())->lte('start_date', $endDate->asDbDate())->addRaw("coalesce({$db->convert('type', 'length')},0) > 0");
$sq->orderBy('start_date', 'ASC');
$beans = $sq->execute();
foreach ($beans as $row) {
$timePeriods[$row['id']] = $row['name'];
}
return $timePeriods;
}
/**
* Uninstalls module filters
*/
protected function uninstall_filters()
{
if (empty($this->installdefs['beans'])) {
return;
}
$modules = array();
foreach ($this->installdefs['beans'] as $definition) {
$modules[] = $definition['module'];
}
$filter = BeanFactory::getBean('Filters');
$query = new SugarQuery();
$query->select('id');
$query->from($filter)->where()->in('module_name', $modules);
$data = $query->execute();
foreach ($data as $row) {
$filter->mark_deleted($row['id']);
}
}
/**
* Gets an array of beans from a SugarQuery
*
* @param SugarQuery $query - Query object with everything but the from() section filled in
* @param array $fields - (Optional) A list of fields to populate in the beans
* @param array $options - (Optional) Optional parameters for the function:
* returnRawRows - Returns raw rows in the _row key, indexed by bean id
* beanList - An array of beans to merge the results into
* skipSecondaryQuery - Don't perform the secondary queries
* @return array SugarBean - An array of SugarBeans populated with the requested fields
*/
function fetchFromQuery(SugarQuery $query, array $fields = array(), array $options = array())
{
$queryFields = array();
$secondaryFields = array();
$beans = array();
$sfh = new SugarFieldHandler();
if (empty($fields)) {
$fields = array_keys($this->field_defs);
}
foreach ($fields as $field) {
if (!isset($this->field_defs[$field]) || !isset($this->field_defs[$field]['type'])) {
// Not a valid field, remove it from the list
continue;
}
$def = $this->field_defs[$field];
if ($def['type'] == 'link') {
continue;
}
if (isset($def['link_type']) && $def['link_type'] == 'relationship_info') {
// These fields are only here for backwards compatibility
continue;
}
if (isset($def['link']) && $def['link'] != true && !isset($this->field_defs[$def['link']])) {
$GLOBALS['log']->error("Invalid link detected: {$field} is looking for {$def['link']}");
continue;
}
if (!isset($options['skipSecondaryQuery']) || $options['skipSecondaryQuery'] == false) {
$type = !empty($def['custom_type']) ? $def['custom_type'] : $this->db->getFieldType($def);
$sugarField = $sfh->getSugarField($type);
if ($sugarField->fieldNeedsSecondaryQuery($field, $this)) {
$secondaryFields[$field] = $sugarField;
continue;
}
}
if (isset($def['source']) && $def['source'] == 'non-db' && (empty($def['rname']) || empty($def['link']))) {
// Non-db that isn't a relate field.
continue;
}
$queryFields[$field] = $field;
// Disable distinct on text type fields, since Oracle doesn't
// allow distinct selects on CLOB types
$fieldType = $this->db->getFieldType($def);
$isTextType = $fieldType ? $this->db->isTextType($fieldType) : false;
if ($isTextType) {
$query->distinct(false);
}
}
foreach ($this->field_defs as $field => $fieldDef) {
if (isset($fieldDef['mandatory_fetch']) && $fieldDef['mandatory_fetch'] == true) {
$queryFields[$field] = $field;
}
}
$queryFields['id'] = 'id';
if (isset($this->field_defs['assigned_user_id'])) {
$queryFields['assigned_user_id'] = 'assigned_user_id';
}
$query->select($queryFields);
$this->call_custom_logic('before_fetch_query', array('query' => $query, 'fields' => $fields));
$rows = $query->execute();
$rawRows = array();
foreach ($rows as $row) {
if (isset($options['beanList'][$row['id']])) {
$bean = $options['beanList'][$row['id']];
} else {
$bean = $this->getCleanCopy();
}
//true parameter below tells populate to perform conversions on row data
$bean->fetched_row = $bean->populateFromRow($row, true);
$bean->call_custom_logic("process_record");
$beans[$bean->id] = $bean;
$rawRows[$bean->id] = $row;
}
if (!isset($options['skipSecondaryQuery']) || $options['skipSecondaryQuery'] == false) {
foreach ($secondaryFields as $fieldName => $sugarField) {
$sugarField->runSecondaryQuery($fieldName, $this, $beans);
}
}
$this->call_custom_logic('after_fetch_query', array('beans' => $beans, 'fields' => $fields, 'rows' => $rawRows));
if (!empty($options['compensateDistinct'])) {
$beans['_distinctCompensation'] = $this->computeDistinctCompensation($rows, $beans);
}
if (!empty($options['returnRawRows'])) {
$beans['_rows'] = $rawRows;
}
return $beans;
}
public function migrateToOpportunities()
{
$sq = new SugarQuery();
$sq->select(array('id', 'content'));
$sq->from(BeanFactory::getBean('Reports'), array('team_security' => false));
$sq->where()->equals('module', 'Opportunities')->contains('content', '"name":"sales_stage"');
$results = $sq->execute('array', false);
$fixedReports = array();
// since we are dealing with json data, don't have fetchByAssoc encode the data
foreach ($results as $row) {
// reset the name, just in case.
$this->rli_table_name = 'Opportunities:revenuelineitems';
$report = json_decode($row['content'], true);
// if links_defs is there, we need to unset it from there
if (isset($report['links_def'])) {
if ($loc = array_search('revenuelineitems', $report['links_def'])) {
unset($report['links_def'][$loc]);
}
// if we are setting the links_defs, the rli_table_name needs to be changed
$this->rli_table_name = 'revenuelineitems';
} elseif (isset($report['full_table_list'])) {
if (isset($report['full_table_list']['self']['children']) && is_array($report['full_table_list']['self']['children'])) {
// find the RLI module
foreach ($report['full_table_list']['self']['children'] as $child) {
if (isset($report['full_table_list'][$child]['module']) && $report['full_table_list'][$child]['module'] === 'RevenueLineItems') {
$this->rli_table_name = $child;
break;
}
}
unset($report['full_table_list']['self']['children'][$this->rli_table_name]);
}
// if it's in the full_table_list, it should be removed from there.
if (isset($report['full_table_list'][$this->rli_table_name])) {
unset($report['full_table_list'][$this->rli_table_name]);
}
} else {
// if we don't have a links_def or the full_table_list, we should just bail out now.
continue;
}
// lets loop though all the display_columns and find anyone that is sales_stage
foreach (array('group_defs', 'display_columns', 'summary_columns') as $type) {
foreach ($report[$type] as $key => $column) {
if ($column['name'] == 'sales_stage' && $column['table_key'] == $this->rli_table_name) {
$report[$type][$key]['table_key'] = 'self';
}
}
}
// now lets fix all the filters.
foreach ($report['filters_def'] as $name => $filter) {
$returnSingleFilter = false;
if (isset($filter['name']) && isset($filter['table_key'])) {
$returnSingleFilter = true;
$filter = array($filter);
}
$filter = $this->fixFilters($filter, 'self');
if ($returnSingleFilter) {
$filter = array_shift($filter);
}
$report['filters_def'][$name] = $filter;
}
$json_def = json_encode($report, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT);
$fixedReports[] = $json_def;
$sql = 'UPDATE saved_reports
SET content = ' . $this->db->quoted($json_def) . '
WHERE id = ' . $this->db->quoted($row['id']);
$this->db->query($sql);
$this->cleanUp();
}
return $fixedReports;
}
/**
* Returns a query object for subscription queries.
* @param Array $params
* disable_row_level_security
* @return SugarQuery
*/
protected static function getQueryObject($params = array())
{
$subscription = BeanFactory::getBean('Subscriptions');
// Pro+ versions able to override visibility on subscriptions (Portal)
// to allow Contact change activity messages to be linked to subscribers
if (!empty($params['disable_row_level_security'])) {
$subscription->disable_row_level_security = true;
}
$query = new SugarQuery();
$query->from($subscription);
$query->where()->equals('deleted', '0');
if (!empty($params['limit'])) {
$query->limit($params['limit'] + 1);
}
if (!empty($params['offset'])) {
$query->offset($params['offset']);
}
return $query;
}
/**
* This method removes all pending event flows that are 'sleeping' and are
* associated this this event.
*
* @param $eventData Object Event
*/
public function removeActiveTimerEvents($eventData)
{
$this->logger->debug("Removing sleeping timer events for the event: " . print_r($eventData, true));
$bpmFlowBean = BeanFactory::newBean('pmse_BpmFlow');
$sq = new SugarQuery();
$sq->select(array('id'));
$sq->from($bpmFlowBean);
$sq->where()->equals('bpmn_id', $eventData['id'])->equals('cas_flow_status', 'SLEEPING');
$result = $sq->execute();
foreach ($result as $row) {
$e = BeanFactory::getBean('pmse_BpmFlow', $row['id']);
$e->cas_flow_status = 'DELETED';
$e->save();
// Update 'Process' to ERROR status
$cas_id = $e->cas_id;
$cf = new PMSECaseFlowHandler();
$cf->changeCaseStatus($cas_id, 'TERMINATED');
}
}
function getReportCharts($category)
{
global $current_user;
$chartsList = array();
require_once 'modules/Reports/SavedReport.php';
$sq = new SugarQuery();
$savedReportBean = BeanFactory::getBean('Reports');
$sq->from($savedReportBean);
// Make sure the user isn't seeing reports they don't have access to
$modules = array_keys(getACLDisAllowedModules());
if (count($modules)) {
$sq->where()->notIn('module', $modules);
}
//create the $where statement(s)
$sq->where()->notEquals('chart_type', 'none');
switch ($category) {
case 'global':
// build global where string
$sq->where()->equals('saved_reports.team_set_id', '1');
break;
case 'myTeams':
// build myTeams where string
$myTeams = $current_user->get_my_teams();
$teamWhere = '';
foreach ($myTeams as $team_id => $team_name) {
if ($team_id != '1' && $team_id != $current_user->getPrivateTeamID()) {
if ($teamWhere == '') {
$teamWhere .= ' ';
} else {
$teamWhere .= 'OR ';
}
$teamWhere .= "saved_reports.team_set_id='" . $team_id . "' ";
}
}
$sq->whereRaw($teamWhere);
break;
case 'mySaved':
// build mySaved where string
$sq->where()->equals('saved_reports.team_set_id', $current_user->getPrivateTeamID());
break;
case 'myFavorites':
global $current_user;
$sugaFav = BeanFactory::getBean('SugarFavorites');
$current_favorites_beans = $sugaFav->getUserFavoritesByModule('Reports', $current_user);
$current_favorites = array();
foreach ((array) $current_favorites_beans as $key => $val) {
array_push($current_favorites, $val->record_id);
}
if (is_array($current_favorites) && !empty($current_favorites)) {
$sq->where()->in('saved_reports.id', array_values($current_favorites));
} else {
$sq->where()->in('saved_reports.id', array('-1'));
}
break;
default:
break;
}
//retrieve array of reports
$savedReports = $savedReportBean->fetchFromQuery($sq);
$chartsList = array();
if (!empty($savedReports)) {
foreach ($savedReports as $savedReport) {
// clint - fixes bug #20398
// only display dashlets that are from visibile modules and that the user has permission to list
require_once 'include/MySugar/MySugar.php';
$myDashlet = new MySugar($savedReport->module);
$displayDashlet = $myDashlet->checkDashletDisplay();
if ($displayDashlet) {
$title = getReportNameTranslation($savedReport->name);
$report_def = array('title' => $title, 'onclick' => 'return SUGAR.mySugar.addDashlet(\'' . $savedReport->id . '\', \'chart\', \'' . $savedReport->module . '\');');
array_push($chartsList, $report_def);
}
}
}
asort($chartsList);
$this->dashlets[$category] = $chartsList;
}
/**
* Do the repair
*
* @throws SugarQueryException
*/
public function repairReportsToStructure()
{
$mgr_worksheet = BeanFactory::getBean('ForecastManagerWorksheets');
$db = DBManagerFactory::getInstance();
//Iterate through the list of users
foreach ($this->userData as $id => $data) {
$reports_to_id = $data['reports_to_id'];
$status = $data['status'];
$deleted = $data['deleted'];
//Get all the worksheets for this user for this timeperiod
$query = new SugarQuery();
$query->select(array('id', 'name', 'user_id', 'assigned_user_id', 'timeperiod_id'));
$query->from($mgr_worksheet);
$query->where()->equals('timeperiod_id', $this->currentTimePeriod['current']);
$query->where()->equals('user_id', $id);
$query->where()->equals('deleted', 0);
$query->orderBy('date_modified', 'DESC');
$rows = $query->execute();
//Only the first worksheet (by date_modified DESC) is kept
$firstOne = true;
$timePeriodName = $this->currentTimePeriod['list'][$this->currentTimePeriod['current']]['name'];
//now iterate through the list of worksheets and check the assigned_user_id against the reports_to_id
foreach ($rows as $row) {
if ($firstOne) {
$firstOne = false;
if ($row['assigned_user_id'] != $reports_to_id) {
//This record needs updating
$mgr_worksheet->retrieve($row['id']);
$mgr_worksheet->assigned_user_id = $reports_to_id;
if ($deleted == 1 || $status != 'Active' || empty($reports_to_id)) {
//If the user has been deleted or marked as Inactive or has no reports_to_id then mark the sheets as deleted
$mgr_worksheet->deleted = 1;
$mgr_worksheet->save();
if ($deleted == 1 || $status != 'Active') {
$reason = "as the user is no longer active";
} elseif (empty($reports_to_id)) {
$reason = "as the user has no reports_to_is set";
}
$this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was DELETED {$reason}";
} else {
//It just needs to be reassigned to the new manager
$mgr_worksheet->save();
$timePeriodName = $this->currentTimePeriod['list'][$this->currentTimePeriod['current']]['name'];
$from = $this->getUserName($row['assigned_user_id']);
$to = $this->getUserName($reports_to_id);
$this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was reassigned from '{$from}' to '{$to}'";
}
} else {
//Nothing needs to be changed
$this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} correct and not changed";
}
} else {
//This is a duplicate commit (IT SEEMS, maybe, possibly, more than likely, NOT SURE OF THIS ONE YET)
$mgr_worksheet->retrieve($row['id']);
$mgr_worksheet->deleted = 1;
$mgr_worksheet->save();
$this->results[$row['id']] = "Worksheet in TimePeriod '{$timePeriodName}' for {$row['name']} was DELETED as a DUPLICATE";
}
}
}
}
/**
* This function creates a job for to run the SugarJobUpdateOpportunities class
* @param integer $perJob
* @returns array|string An array of the jobs that were created, unless there
* is one, then just that job's id
*/
public static function updateOpportunitiesForForecasting($perJob = 100)
{
$sq = new SugarQuery();
$sq->select(array('id'));
$sq->from(BeanFactory::getBean('Opportunities'));
$sq->orderBy('date_closed');
$rows = $sq->execute();
if (empty($rows)) {
return false;
}
$chunks = array_chunk($rows, $perJob);
$jobs = array();
// process the first job now
$job = static::createJob($chunks[0], true);
$jobs[] = $job->id;
// run the first job
$self = new self();
$self->setJob($job);
$self->sendNotifications = false;
$self->run($job->data);
$job_group = md5(microtime());
for ($i = 1; $i < count($chunks); $i++) {
$jobs[] = static::createJob($chunks[$i], false, $job_group);
}
// if only one job was created, just return that id
if (count($jobs) == 1) {
return array_shift($jobs);
}
return $jobs;
}
protected function getProductsWithNonClosedQuote()
{
$product = BeanFactory::getBean('Products');
$sq = new SugarQuery();
$sq->select(array('id'));
$sq->from($product);
// join in the product bundles table
$product->load_relationships('product_bundles');
// we use a left join here so we can get products that do not have quotes
$product->product_bundles->buildJoinSugarQuery($sq, array('joinType' => 'LEFT'));
// join in the quotes table off of Product Bundles
$bundle = BeanFactory::getBean('ProductBundles');
$bundle->load_relationship('quotes');
$bundle->quotes->buildJoinSugarQuery($sq, array('joinType' => 'LEFT'));
$quote = BeanFactory::getBean('Quotes');
$sq->where()->queryOr()->isNull('quotes.quote_stage', $quote)->notIn('quotes.quote_stage', $quote->closed_statuses, $quote);
$results = $sq->execute();
$db = $this->db;
// we just need the array, so use array_map to pull it out of the results
return array_map(function ($a) use($db) {
return $db->quoted($a['id']);
}, $results);
}
/**
* Returns all role sets
*
* @return ACLRoleSet[]
* @todo Move this to ACLRoleSet when it's merged
*/
protected static function getAllRoleSets()
{
$roleSet = BeanFactory::getBean('ACLRoleSets');
//Verify that rolesets are operable before attempting to use them.
if (empty($roleSet)) {
return array();
}
$query = new SugarQuery();
$query->from($roleSet);
$query->select('id', 'hash');
$data = $query->execute();
return self::createCollectionFromDataSet($roleSet, $data);
}
/**
* 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;
}
/**
* Generates query for fetching non-primary emails for the given beans
*
* @param SugarBean $bean Bean instance
* @param array $ids Bean IDs
*
* @return SugarQuery
*/
function getNonPrimaryEmailsExportQuery(SugarBean $bean, array $ids)
{
$query = new SugarQuery();
$query->from(BeanFactory::newBean('EmailAddresses'), 'ea');
$query->joinTable('email_addr_bean_rel', array('joinType' => 'LEFT', 'alias' => 'eabr', 'linkingTable' => true))->on()->equalsField('eabr.email_address_id', 'id');
$query->select('eabr.bean_id', 'email_address', 'invalid_email', 'opt_out');
$query->where()->in('eabr.bean_id', $ids)->equals('eabr.bean_module', $bean->module_dir)->notEquals('eabr.primary_address', 1)->notEquals('eabr.deleted', 1);
$query->orderBy('eabr.bean_id', 'ASC')->orderBy('eabr.reply_to_address', 'ASC')->orderBy('email_address', 'ASC');
return $query;
}
