/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
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 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'];
}
/**
* 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;
}
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;
}
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 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;
}
/**
* 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;
}
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);
}
/**
* 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']);
}
}
/**
* 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";
}
}
}
}
/**
* Gets the EAPM bean for the user and this connector
*
* @param User $user A User object
* @return EAPM
*/
protected function getEAPMForUser($user)
{
// In any case we will need the EAPM bean
$seed = BeanFactory::getBean("EAPM");
// Setup our query to get what we need from the EAPM bean
$sq = new SugarQuery();
$sq->from($seed)->where()->equals('assigned_user_id', $user->id)->equals('application', $this->connector);
// Run the query and get the first result
$eapm = current($seed->fetchFromQuery($sq, array('api_data')));
if ($eapm) {
if (!empty($eapm->api_data)) {
// Get the decoded EAPM data for this connector and load it on
// the fetched bean as properties
$data = json_decode(base64_decode($eapm->api_data));
foreach ($data as $key => $val) {
$eapm->{$key} = $val;
}
}
// Send back this bean
return $eapm;
} else {
// No EAPM... load the basics and send it back
$seed->assigned_user_id = $user->id;
$seed->application = $this->connector;
return $seed;
}
}
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');
}
}
/**
* Creates a SugarQuery instance according PA relationships
* @param SugarBean $seed
* @param array $options
* @return SugarQuery
* @throws SugarQueryException
*/
protected static function getQueryObjectPA(SugarBean $seed, array $options)
{
if (empty($options['select'])) {
$options['select'] = self::$mandatory_fields;
}
$queryOptions = array('add_deleted' => !isset($options['add_deleted']) || $options['add_deleted'] ? true : false);
if ($queryOptions['add_deleted'] == false) {
$options['select'][] = 'deleted';
}
$q = new SugarQuery();
$q->from($seed, $queryOptions);
$q->distinct(false);
$fields = array();
foreach ($options['select'] as $field) {
// fields that aren't in field defs are removed, since we don't know
// what to do with them
if (!empty($seed->field_defs[$field])) {
// Set the field into the field list
$fields[] = $field;
}
}
//INNER JOIN BPM INBOX TABLE
$fields[] = array("date_entered", 'date_entered');
$fields[] = array("cas_id", 'cas_id');
$fields[] = array("cas_sugar_module", 'cas_sugar_module');
$fields[] = array("cas_sugar_object_id", 'cas_sugar_object_id');
$fields[] = array("cas_user_id", 'cas_user_id');
$q->joinTable('pmse_inbox', array('alias' => 'inbox', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('inbox.cas_id', 'cas_id')->equals('inbox.deleted', 0);
$fields[] = array("inbox.id", 'inbox_id');
$fields[] = array("inbox.cas_title", 'cas_title');
$q->where()->equals('cas_flow_status', 'FORM');
//INNER JOIN BPMN ACTIVITY DEFINITION
$q->joinTable('pmse_bpmn_activity', array('alias' => 'activity', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('activity.id', 'bpmn_id')->equals('activity.deleted', 0);
$fields[] = array("activity.name", 'act_name');
//INNER JOIN BPMN ACTIVITY DEFINTION
$q->joinTable('pmse_bpm_activity_definition', array('alias' => 'activity_definition', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('activity_definition.id', 'activity.id')->equals('activity_definition.deleted', 0);
$fields[] = array("activity_definition.act_assignment_method", 'act_assignment_method');
//INNER JOIN BPMN PROCESS DEFINTION
$q->joinTable('pmse_bpmn_process', array('alias' => 'process', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('process.id', 'inbox.pro_id')->equals('process.deleted', 0);
$fields[] = array("process.name", 'pro_title');
$fields[] = array("process.prj_id", 'prj_id');
$fields[] = array("process.created_by", 'prj_created_by');
//INNER JOIN USER_DATA DEFINTION
$q->joinTable('users', array('alias' => 'user_data', 'joinType' => 'LEFT', 'linkingTable' => true))->on()->equalsField('user_data.id', 'cas_user_id')->equals('user_data.deleted', 0);
$fields[] = array("user_data.first_name", 'first_name');
$fields[] = array("user_data.last_name", 'last_name');
//INNER JOIN TEAM_DATA DEFINTION
$q->joinTable('teams', array('alias' => 'team_data', 'joinType' => 'LEFT', 'linkingTable' => true))->on()->equalsField('team_data.id', 'cas_user_id')->equals('team_data.deleted', 0);
$fields[] = array("team.name", 'team_name');
$q->select($fields)->fieldRaw('user_data.last_name', 'assigned_user_name');
foreach ($options['order_by'] as $orderBy) {
if ($orderBy[0] == 'pro_title') {
$orderBy[0] = 'process.name';
}
if ($orderBy[0] == 'task_name') {
$orderBy[0] = 'activity.name';
}
if ($orderBy[0] == 'cas_title') {
$orderBy[0] = 'inbox.cas_title';
}
if ($orderBy[0] == 'cas_user_id_full_name') {
$orderBy[0] = 'cas_user_id';
}
if ($orderBy[0] == 'prj_user_id_full_name') {
$orderBy[0] = 'prj_created_by';
}
if ($orderBy[0] == 'assigned_user_name') {
$orderBy[0] = 'assigned_user_name';
}
$q->orderBy($orderBy[0], $orderBy[1]);
}
// Add an extra record to the limit so we can detect if there are more records to be found
$q->limit($options['limit'] + 1);
$q->offset($options['offset']);
return $q;
}
/**
* Get the project Diagram data with a determined Project Id
* @param string $prjID
* @return array
*/
public function getProjectDiagram($prjID)
{
$diagramBean = BeanFactory::getBean('pmse_BpmnDiagram');
//new BpmnDiagram();
$diagramData = array();
$activityBean = BeanFactory::getBean('pmse_BpmnActivity');
//new BpmnActivity();
$activityData = array();
$artifactBean = BeanFactory::getBean('pmse_BpmnArtifact');
//new BpmnArtifact();
$artifactData = array();
$gatewayBean = BeanFactory::getBean('pmse_BpmnGateway');
//new BpmnGateway();
$gatewayData = array();
$eventBean = BeanFactory::getBean('pmse_BpmnEvent');
//new BpmnEvent();
$eventData = array();
$flowBean = BeanFactory::getBean('pmse_BpmnFlow');
//new BpmnFlow();
$flowData = array();
$rulesetBean = BeanFactory::getBean('pmse_Business_Rules');
//new BpmRuleSet();
$rulesetData = array();
$lanesetBean = BeanFactory::getBean('pmse_BpmnLaneset');
//new BpmnLaneset();
$lanesetData = array();
$laneBean = BeanFactory::getBean('pmse_BpmnLane');
//new BpmnLane();
$laneData = array();
// $participantBean = BeanFactory::getBean('pmse_BpmnParticipant'); //new BpmnParticipant();
// $participantData = array();
$processBean = BeanFactory::getBean('pmse_BpmnProcess');
//new BpmnProcess();
$processData = array();
$retrievedDataBean = BeanFactory::getBean('pmse_BpmnData');
//new BpmnData();
$retrievedData = array();
$documentationBean = BeanFactory::getBean('pmse_BpmnDocumentation');
//new BpmnDocumentation();
$documentationData = array();
$extensionBean = BeanFactory::getBean('pmse_BpmnExtension');
//new BpmnExtension();
$extensionData = array();
$conditions = array("prj_id" => $prjID);
if ($diagramBean->retrieve_by_string_fields($conditions)) {
$diagramBean->fetched_row = PMSEEngineUtils::unsetCommonFields($diagramBean->fetched_row);
// list of activities based in the project id
//$data = $activityBean->getSelectRows("", "bpmn_activity.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnActivity'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_activity.act_id=bpmn_bound.bou_element'), array('LEFT', 'bpm_activity_definition', 'bpmn_activity.act_id=bpm_activity_definition.act_id')));
$q = new SugarQuery();
$q->from($activityBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$q->joinRaw("LEFT JOIN pmse_bpm_activity_definition c ON (a.id=c.id)", array('alias' => 'c'));
$fields = $this->getFields('pmse_BpmnActivity', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnActivity'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$fields_ad = $this->getFields('pmse_BpmActivityDefinition', array(), 'c');
foreach ($fields_ad as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$tmpObject = $flowBean->retrieve_by_string_fields(array("id" => $row['act_default_flow']));
$row['act_default_flow'] = isset($tmpObject->flo_uid) ? $tmpObject->flo_uid : '';
if ($row['act_task_type'] == "SCRIPTTASK" && $row['act_script_type'] == "BUSINESS_RULE") {
$row['act_fields'] = isset($row['act_fields']) ? $row['act_fields'] : '';
$ruleset = $rulesetBean->retrieve_by_string_fields(array('id' => $row['act_fields']));
if ($ruleset) {
$row['act_fields'] = $ruleset->rst_uid;
$this->rulesetList[] = $ruleset->fetched_row;
}
}
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$activityData[] = $row;
}
}
$diagramBean->fetched_row['activities'] = $activityData;
// list of events based in the project id
//$data = $eventBean->getSelectRows("", "bpmn_event.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnEvent'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_event.evn_id=bpmn_bound.bou_element'), array('LEFT', 'bpm_event_definition', 'bpmn_event.evn_id=bpm_event_definition.evn_id')));
$q = new SugarQuery();
$q->from($eventBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$q->joinRaw("LEFT JOIN pmse_bpm_event_definition c ON (a.id=c.id)", array('alias' => 'c'));
$fields = $this->getFields('pmse_BpmnEvent', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnEvent'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$fields_ad = $this->getFields('pmse_BpmEventDefinition', array(), 'c');
foreach ($fields_ad as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_attached_to']));
$row['evn_attached_to'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : '';
$tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_cancel_activity']));
$row['evn_cancel_activity'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : '';
$tmpActBean = $activityBean->retrieve_by_string_fields(array("id" => $row['evn_activity_ref']));
$row['evn_activity_ref'] = isset($tmpActBean->act_uid) ? $tmpActBean->act_uid : '';
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$eventData[] = $row;
}
}
$diagramBean->fetched_row['events'] = $eventData;
// list of gateways based in the project id
//$data = $gatewayBean->getSelectRows("", "bpmn_gateway.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnGateway'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_gateway.gat_id=bpmn_bound.bou_element')));
$q = new SugarQuery();
$q->from($gatewayBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$fields = $this->getFields('pmse_BpmnGateway', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnGateway'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$flowObject = $flowBean->retrieve_by_string_fields(array("id" => $row['gat_default_flow']));
$row['gat_default_flow'] = isset($flowObject->flo_uid) ? $flowObject->flo_uid : '';
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$gatewayData[] = $row;
}
}
$diagramBean->fetched_row['gateways'] = $gatewayData;
// list of artifacts based in the project id
//$data = $artifactBean->getSelectRows("", "bpmn_artifact.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnArtifact'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_artifact.art_id=bpmn_bound.bou_element')));
$q = new SugarQuery();
$q->from($artifactBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$fields = $this->getFields('pmse_BpmnArtifact', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnArtifact'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$artifactData[] = $row;
}
}
$diagramBean->fetched_row['artifacts'] = $artifactData;
// list of flows based in the project id
//$data = $flowBean->getSelectRows("", "bpmn_flow.prj_id=" . $prjID, 0, -1, -1, array());
$rows = $flowBean->get_full_list('', "prj_id='" . $prjID . "'");
if (!empty($rows)) {
foreach ($rows as $row) {
$row = $row->fetched_row;
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeKeyFields($row);
$row['prj_id'] = $prjID;
$row['flo_element_origin'] = PMSEEngineUtils::getElementUid($row['flo_element_origin'], $row['flo_element_origin_type'], PMSEEngineUtils::getEntityUid($row['flo_element_origin_type']));
$row['flo_element_dest'] = PMSEEngineUtils::getElementUid($row['flo_element_dest'], $row['flo_element_dest_type'], PMSEEngineUtils::getEntityUid($row['flo_element_dest_type']));
$row['flo_state'] = json_decode($row['flo_state']);
$row['flo_condition'] = json_decode($row['flo_condition']);
$row['flo_condition'] = !empty($row['flo_condition']) ? $this->processBusinessRulesData($row['flo_condition']) : '';
$flowData[] = $row;
}
}
$diagramBean->fetched_row['flows'] = $flowData;
// list of pools based in the project id
//$data = $lanesetBean->getSelectRows("", "bpmn_laneset.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnLaneset'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_laneset.lns_id=bpmn_bound.bou_element')));
$q = new SugarQuery();
$q->from($lanesetBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$fields = $this->getFields('pmse_BpmnLaneset', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnLaneset'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$lanesetData[] = $row;
}
}
$diagramBean->fetched_row['pools'] = $lanesetData;
// list of lanes based in the project id
//$data = $laneBean->getSelectRows("", "bpmn_lane.prj_id=" . $prjID . " AND bpmn_bound.bou_element_type='bpmnLane'", 0, -1, -1, array(), array(array('INNER', 'bpmn_bound', 'bpmn_lane.lan_id=bpmn_bound.bou_element')));
$q = new SugarQuery();
$q->from($laneBean, array('alias' => 'a'));
$q->joinRaw("INNER JOIN pmse_bpmn_bound b ON (a.id=b.bou_element)", array('alias' => 'b'));
$fields = $this->getFields('pmse_BpmnLane', array('name'), 'a');
$q->select($fields);
$q->where()->queryAnd()->addRaw("a.prj_id='" . $prjID . "' AND b.bou_element_type='bpmnLane'");
$fields_bound = $this->getFields('pmse_BpmnBound', array(), 'b');
foreach ($fields_bound as $key => $value) {
$q->select->fieldRaw($value);
}
$rows = $q->execute();
if (!empty($rows)) {
foreach ($rows as $row) {
$row['bou_element'] = $row['bou_uid'];
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$row = PMSEEngineUtils::sanitizeFields($row);
$laneData[] = $row;
}
}
$diagramBean->fetched_row['lanes'] = $laneData;
// list of participants based in the project id
//$data = $participantBean->getSelectRows("", "bpmn_participant.prj_id=" . $prjID, 0, -1, -1, array(), array());
// $rows = $participantBean->get_full_list("", "prj_id='" . $prjID . "'");
// if (!empty($rows)) {
// foreach ($rows as $row) {
// $row = $row->fetched_row;
// $row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
// $row['bou_element'] = $row['bou_uid'];
// $participantData[] = $row;
// }
// }
// $diagramBean->fetched_row['participants'] = $participantData;
$diagramBean->fetched_row['participants'] = array();
// data list based in the project id
//$data = $retrievedDataBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array());
$rows = $retrievedDataBean->get_full_list("", "prj_id='" . $prjID . "'");
if (!empty($rows)) {
foreach ($rows as $row) {
$retrievedData[] = $row->fetched_row;
}
}
$diagramBean->fetched_row['data'] = $retrievedData;
// documentation list based in the project id
//$data = $documentationBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array());
$rows = $documentationBean->get_full_list("", "prj_id='" . $prjID . "'");
if (!empty($rows)) {
foreach ($rows as $row) {
$row = $row->fetched_row;
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$documentationData[] = $row;
}
}
$diagramBean->fetched_row['documentation'] = $documentationData;
// data list based in the project id
//$data = $extensionBean->getSelectRows("", "prj_id=" . $prjID, 0, -1, -1, array(), array());
$rows = $extensionBean->get_full_list("", "prj_id='" . $prjID . "'");
if (!empty($rows)) {
foreach ($rows as $row) {
$row = $row->fetched_row;
$row = PMSEEngineUtils::unsetCommonFields($row, array('name', 'description'));
$extensionData[] = $row;
}
}
$diagramBean->fetched_row['extension'] = $extensionData;
array_push($diagramData, $diagramBean->fetched_row);
}
return $diagramData;
}
/**
* Delete all the Triggers that are triggered by the RLI Module
*
* @throws SugarQueryException
*/
private function deleteRevenueLineItemsRelatedTriggers()
{
// get the action shells
$triggerShells = BeanFactory::getBean('WorkFlowTriggerShells');
$sq = new SugarQuery();
$sq->select(array('id', 'parent_id'));
$sq->from($triggerShells);
$sq->where()->equals('rel_module', 'revenuelineitems');
$rows = $sq->execute();
foreach ($rows as $row) {
$triggerShells->mark_deleted($row['id']);
}
}
/**
* Retrieve a user's quota using the rollup value, if available. This method is useful for
* fetching user quota data when you're unsure about whether or not the given user is a manager.
* If you would like to force a direct quota, pass a false value to $should_rollup.
*
* @param $timeperiod_id String id of the TimePeriod to retrieve quota for
* @param $user_id String value of the user id to retrieve. If NULL, the $current_user is used
* @param $should_rollup boolean value indicating whether or not the quota should be a rollup calculation; false by default
*
* @return array [currency_id => int, amount => number, formatted_amount => String]
*/
public function getRollupQuota($timeperiod_id, $user_id = null, $should_rollup = false)
{
if (is_null($user_id)) {
global $current_user;
$user_id = $current_user->id;
}
// figure out the timeperiod
// if we didn't find a time period, set the time period to be the current time period
if (!is_guid($timeperiod_id) && is_numeric($timeperiod_id) && $timeperiod_id != 0) {
// we have a timestamp, find timeperiod it belongs in
$timeperiod_id = TimePeriod::getIdFromTimestamp($timeperiod_id);
}
if (!is_guid($timeperiod_id)) {
$timeperiod_id = TimePeriod::getCurrentId();
}
$sq = new SugarQuery();
$sq->select(array('quotas.currency_id', 'quotas.amount'));
$sq->from(BeanFactory::getBean('Quotas'));
$sq->where()->equals('user_id', $user_id)->equals('quota_type', $should_rollup ? 'Rollup' : 'Direct')->equals('timeperiod_id', $timeperiod_id);
$sq->orderBy('date_modified', 'DESC');
$sq->limit(1);
// since there is only ever one row, just shift the value off the results
$row = array_shift($sq->execute());
if (empty($row)) {
// This is to prevent return value of false when a given timeperiod has no quota.
$row = array('currency_id' => -99, 'amount' => 0);
}
$row['formatted_amount'] = SugarCurrency::formatAmountUserLocale($row['amount'], $row['currency_id']);
return $row;
}
/**
* Destructively loads a bean with only the specified fields
*
* @param string - $id The id of the bean you wish to load, for multiple beans use ->fetchFromQuery
* @param array - $fields (Optional) A list of fields to populate in the bean
* @param array $options - (Optional) Optional parameters for the function:
* @return SugarBean - AhThe bean you requested
*/
function fetch($id, array $fields = array(), array $options = array())
{
$query = new SugarQuery();
$query->from($this);
$query->where()->equals('id', $id);
// Pass this in so fetchFromQuery mutates $this instead of grabbing a fresh bean
// This is so fetch() can work like retrieve()
$options['beanList'][$id] = $this;
$this->fetchFromQuery($query, $fields, $options);
if (!isset($this->id)) {
return false;
} else {
return $this;
}
}
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;
}
/**
* Returns query object to retrieve list of recently viewed records by
* module.
*
* @param SugarBean $seed Instance of current bean.
* @param array $options Prepared options.
* @return SugarQuery query to execute.
*/
protected function getRecentlyViewedQueryObject($seed, $options)
{
$currentUser = $this->getUserBean();
$query = new SugarQuery();
$query->from($seed);
// FIXME: FRM-226, logic for these needs to be moved to SugarQuery
// Since tracker relationships don't actually exist, we're gonna have to add a direct join
$query->joinRaw(sprintf(" JOIN tracker ON tracker.item_id=%s.id AND tracker.module_name='%s' AND tracker.user_id='%s' ", $query->from->getTableName(), $query->from->module_name, $currentUser->id), array('alias' => 'tracker'));
// we need to set the linkName to hack around tracker not having real relationships
/* TODO think about how to fix this so we can be less restrictive to raw joins that don't have a relationship */
$query->join['tracker']->linkName = 'tracker';
$query->select(array('id', array('tracker.module_name', 'module_name')));
if (!empty($options['date'])) {
$td = new SugarDateTime();
$td->modify($options['date']);
$query->where()->queryAnd()->gte('tracker.date_modified', $td->asDb());
}
foreach ($query->select()->select as $v) {
$query->groupBy($v->table . '.' . $v->field);
}
$query->select()->fieldRaw('MAX(tracker.date_modified)', 'last_viewed_date');
return $query;
}
/**
* 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;
}
