/** * 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; }
public static function get(SugarBean $bean) { $query = new SugarQuery(); $query->from(BeanFactory::get('dm_DedupeHashes')); $query->select('id'); $dupeRules = $query->join('dm_duplicaterules_dm_dedupehashes')->joinName(); $query->where()->equals("{$dupeRules}.bean_module", $bean->module_name); $query->where()->equals("bean_id", $bean->id); $GLOBALS['log']->error("Getting Dedupe Hashes"); $GLOBALS['log']->error($query->compileSQL()); $results = $query->execute(); return $results; }
public static function retrieveRecycled(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 function addVisibilityWhereQuery(SugarQuery $sugarQuery, $options = array()) { $where = null; $this->addVisibilityWhere($where, $options); if (!empty($where)) { $sugarQuery->where()->addRaw($where); } return $sugarQuery; }
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; }
/** * 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; }
/** * Add visibility clauses to the WHERE part of the query for SugarQuery Object * * @param SugarQuery $query * * @return SugarQuery */ public function addVisibilityWhereQuery(SugarQuery $query) { global $current_user; if (!empty($this->targetModuleField) && !$current_user->isAdmin()) { $modules = $current_user->getDeveloperModules(); if (empty($modules)) { $modules = array(''); } $query->where()->in($this->targetModuleField, $modules); } return $query; }
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; }
/** * doCustomUpdateRate * * Return true to skip updates for this module. * Return false to do default update of base_rate column. * To custom processing, do here and return true. * * @access public * @param string $table * @param string $column * @param string $currencyId * @return boolean true if custom processing was done */ public function doCustomUpdateRate($table, $column, $currencyId) { // get the conversion rate $sq = new SugarQuery(); $sq->select(array('conversion_rate')); $sq->from(BeanFactory::getBean('Currencies')); $sq->where()->equals('id', $currencyId); $rate = $sq->getOne(); $ids = $this->getProductsWithNonClosedQuote(); // setup SQL statement $query = sprintf("UPDATE %s SET %s = %s\n WHERE id IN (%s)\n AND currency_id = %s", $table, $column, $this->db->quote($rate), implode(",", $ids), $this->db->quoted($currencyId)); // execute $result = $this->db->query($query, true, string_format($GLOBALS['app_strings']['ERR_DB_QUERY'], array(__CLASS__, $query))); return !empty($result); }
/** * Test to see if the all the Jobs in the group are done. * * @return bool * @throws SugarQueryException */ protected function isJobGroupDone() { // when we don't have a job_group, return true if (empty($this->job->job_group)) { return true; } $sq = new SugarQuery(); $sq->select(array('job_group'))->fieldRaw('count(0)', 'total_jobs')->fieldRaw('sum(case when status = \'done\' AND resolution = \'success\' then 1 else 0 END)', 'total_done'); $sq->from(BeanFactory::getBean('SchedulersJobs')); $sq->where()->equals('job_group', $this->job->job_group); $sq->groupBy('job_group'); $results = $sq->execute(); $result = array_shift($results); return $result['total_jobs'] === $result['total_done']; }
public function salesByCountry($api, $args) { // TODO: Fix information leakage if user cannot list or view records not // belonging to them. It's hard to tell if the user has access if we // never get the bean. // Check for permissions on both Revenue line times and accounts. $seed = BeanFactory::newBean('RevenueLineItems'); if (!$seed->ACLAccess('view')) { return; } // Load up the relationship if (!$seed->load_relationship('account_link')) { // The relationship did not load, I'm guessing it doesn't exist return; } // Figure out what is on the other side of this relationship, check permissions $linkModuleName = $seed->account_link->getRelatedModuleName(); $linkSeed = BeanFactory::newBean($linkModuleName); if (!$linkSeed->ACLAccess('view')) { return; } $query = new SugarQuery(); $query->from($seed); $account_link = $query->join('account_link'); $query->select(array($account_link->joinName() . '.billing_address_country', $account_link->joinName() . '.billing_address_state', 'likely_case', 'base_rate')); $query->where()->equals('sales_stage', 'Closed Won'); // TODO: When we can sum on the database side through SugarQuery, we can // use the group by statement. $data = array(); $results = $query->execute(); foreach ($results as $row) { if (empty($data[$row['billing_address_country']])) { $data[$row['billing_address_country']] = array('_total' => 0); } if (empty($data[$row['billing_address_country']][$row['billing_address_state']])) { $data[$row['billing_address_country']][$row['billing_address_state']] = array('_total' => 0); } $data[$row['billing_address_country']]['_total'] += $row['likely_case'] / $row['base_rate']; $data[$row['billing_address_country']][$row['billing_address_state']]['_total'] += $row['likely_case'] / $row['base_rate']; } return $data; }
/** * 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); }
/** * 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"; } } } }
/** * 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; } }
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; }
/** * 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 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; }
/** * 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'); } }
/** * 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; }
/** * Build a Join Query with a SugarQuery Object * * @param Link2 $link * @param SugarQuery $sugar_query * @param Array $options array of additional paramters. Possible parameters include * - 'myAlias' String name of starting table alias * - 'joinTableAlias' String alias to use for the related table in the final result * - 'reverse' Boolean true if this join should be built in reverse for subpanel style queries where the select is * on the related table * - 'ignoreRole' Boolean true if the role column of the relationship should be ignored for this join . * * @return SugarQuery */ public function buildJoinSugarQuery(Link2 $link, $sugar_query, $options) { $linkIsLHS = $link->getSide() == REL_LHS; if (!empty($options['reverse'])) { $linkIsLHS = !$linkIsLHS; } $startingTable = $linkIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table']; $startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key']; $targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table']; $targetModule = $linkIsLHS ? $this->def['rhs_module'] : $this->def['lhs_module']; $targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key']; $join_type = isset($options['joinType']) ? $options['joinType'] : 'INNER'; $joinParams = array('joinType' => $join_type, 'bean' => BeanFactory::newBean($targetModule)); $jta = $targetTable; if (!empty($options['joinTableAlias'])) { $jta = $joinParams['alias'] = $options['joinTableAlias']; } $joinTable = $sugar_query->joinTable($targetTable, $joinParams); $joinTable->on()->equalsField("{$startingTable}.{$startingKey}", "{$jta}.{$targetKey}")->equals("{$jta}.deleted", "0"); $relTable = $linkIsLHS ? $jta : $startingTable; if (empty($options['ignoreRole'])) { foreach ($this->getRelationshipRoleColumns() as $column => $value) { $sugar_query->where()->equals("{$relTable}.{$column}", $value); } } $this->addCustomToSugarQuery($sugar_query, $options, $linkIsLHS, $jta); return $sugar_query->join[$jta]; }
/** * Returns set of role sets which include any of the given roles * * @param array $roles IDs of roles * @return ACLRoleSet[] * @todo Move this to ACLRoleSet when it's merged */ protected static function getRoleSetsByRoles(array $roles) { if (!$roles) { return array(); } $roleSet = BeanFactory::getBean('ACLRoleSets'); $query = new SugarQuery(); $query->distinct(true); $query->from($roleSet); $query->select('id', 'hash'); $query->join('acl_roles', array('alias' => 'roles')); $query->where()->in('roles.id', $roles); $data = $query->execute(); return self::createCollectionFromDataSet($roleSet, $data); }
/** * 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; }
/** * 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']); } }
/** * Returns query to ask for email addresses for specific bean type * @param string $module * @return SugarQuery */ public function getEmailsQuery($module) { $q = new SugarQuery(); $q->from($this); $q->select(array('email_address', 'opt_out', 'invalid_email', 'ear.primary_address', 'ear.reply_to_address')); $q->joinTable("email_addr_bean_rel", array('alias' => "ear", 'joinType' => "LEFT", "linkingTable" => true))->on()->equalsField('id', 'ear.email_address_id', $this)->equals('ear.deleted', 0); $q->where()->equals('deleted', 0)->equals('ear.bean_module', $this->getCorrectedModule($module)); $q->orderBy('ear.primary_address', 'DESC'); return $q; }
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; }
/** * 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; }
/** * 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; }