/** * 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 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; }
/** * 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; }
protected static function commitRepItems($user_id, $timeperiod, $forecast_by) { /* @var $tp TimePeriod */ $tp = BeanFactory::getBean('TimePeriods', $timeperiod); $bean = BeanFactory::getBean($forecast_by); $sq = new SugarQuery(); $sq->select(array('forecast_by.*')); $sq->from($bean, array('alias' => 'forecast_by'))->where()->equals('forecast_by.assigned_user_id', $user_id)->queryAnd()->gte('forecast_by.date_closed_timestamp', $tp->start_date_timestamp)->lte('forecast_by.date_closed_timestamp', $tp->end_date_timestamp); $link_name = $forecast_by == 'RevenueLineItems' ? 'account_link' : 'accounts'; $bean->load_relationship($link_name); $bean->{$link_name}->buildJoinSugarQuery($sq, array('joinTableAlias', 'account')); $sq->select(array('account.id', 'account_id')); $beans = $sq->execute(); unset($bean); foreach ($beans as $bean) { /* @var $obj Opportunity|Product */ $obj = BeanFactory::getBean($forecast_by); $obj->loadFromRow($bean); /* @var $opp_wkst ForecastWorksheet */ $opp_wkst = BeanFactory::getBean('ForecastWorksheets'); if ($forecast_by == 'Opportunities') { $opp_wkst->saveRelatedOpportunity($obj, true); } else { $opp_wkst->saveRelatedProduct($obj, true); } } }
/** * 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'); } }
/** * 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; }
/** * Lists Records already present in sugar db using the $columnName & $moduleName parameters * @param $columnName * @param $moduleName * @param $recordIds array (array of id to be used in the in clause of the query) * @return array */ private function getExistingRecords($columnName, $moduleName, $recordIds = null) { $seed = BeanFactory::newBean($moduleName); //if duns_num, format all duns to be 0 padded // for 9 digits if ($columnName == 'duns_num') { foreach ($recordIds as &$duns) { $duns = str_pad($duns, 9, "0", STR_PAD_LEFT); } } $options = array(); $options['offset'] = 0; $options['order_by'] = array(array('date_modified', 'DESC')); $options['add_deleted'] = true; $options['offset'] = 'end'; $options['module'] = $seed->module_name; $options['team_security'] = false; $q = new SugarQuery(); $q->from($seed, $options); $fields = array($columnName); $q->select($fields); $where = $q->where(); $where->in($columnName, $recordIds); $where = $where->queryAnd(); $where->equals('deleted', 0); $q->compileSql(); $queryResults = $q->execute('json'); return $queryResults; }
/** * 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 runQuery(ServiceBase $api, array $args, SugarQuery $q, array $options) { $GLOBALS['log']->info("Filter SQL: " . $q->compileSql()); $beans = array('_rows' => array()); foreach ($q->execute() as $row) { $beans[$row['id']] = BeanFactory::getBean($row['module'], $row['id']); $beans['_rows'][$row['id']] = $row; } $rows = $beans['_rows']; unset($beans['_rows']); $data = array(); $data['next_offset'] = -1; $i = 0; foreach ($beans as $bean_id => $bean) { if ($i == $options['limit']) { unset($beans[$bean_id]); $data['next_offset'] = (int) ($options['limit'] + $options['offset']); continue; } $i++; $this->populateRelatedFields($bean, $rows[$bean_id]); } // add on the contact_id and contact_name fields so we get those // returned in the response $args['fields'] .= ',contact_id,contact_name'; $data['records'] = $this->formatBeans($api, $args, $beans); foreach ($data['records'] as $id => $record) { $data['records'][$id]['moduleNameSingular'] = $GLOBALS['app_list_strings']['moduleListSingular'][$record['_module']]; $data['records'][$id]['moduleName'] = $GLOBALS['app_list_strings']['moduleList'][$record['_module']]; // Have to tack on from/to/description here due to not all modules // having all these fields if ($record['_module'] == 'Emails') { /* @var $q SugarQuery */ $q = new SugarQuery(); $q->select(array('description', 'from_addr', 'to_addrs')); $q->from(BeanFactory::getBean('EmailText')); $q->where()->equals('email_id', $data['records'][$id]['id']); foreach ($q->execute() as $row) { $data['records'][$id]['description'] = $row['description']; $data['records'][$id]['from_addr'] = $row['from_addr']; $data['records'][$id]['to_addrs'] = $row['to_addrs']; } } } return $data; }
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); }
/** * 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; }
/** * 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); }
/** * 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; }