/** * 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; }
/** * Add visibility clauses to the FROM part of the query * * @param string $query * * @return string */ public function addVisibilityFromQuery(SugarQuery $query) { if (!empty($this->parentLink)) { $linkName = $this->parentLink; $query->from->load_relationship($linkName); if (empty($query->from->{$linkName})) { throw new SugarApiExceptionInvalidParameter("Invalid link {$linkName} for owner clause"); } if ($query->from->{$linkName}->getType() == "many") { throw new SugarApiExceptionInvalidParameter("Cannot serch for owners through multi-link {$linkName}"); } $this->join = $query->join($linkName, array('joinType' => 'LEFT')); } return $query; }
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; }
/** * 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); }
public function join($link, $options = array()) { $options['relatedJoin'] = $this->options['alias']; return $this->query->join($link, $options); }
/** * This function adds a favorite filter to the sugar query * * @param SugarQuery $q The whole SugarQuery object * @param SugarQuery_Builder_Where $where The Where part of the SugarQuery object * @param string $link Which module are you adding the favorite filter to. */ protected static function addFavoriteFilter(SugarQuery $q, SugarQuery_Builder_Where $where, $link, $joinType = 'LEFT') { $sfOptions = array('joinType' => $joinType, 'favorites' => true); if ($link == '' || $link == '_this') { $link_name = 'favorites'; } else { $joinTo = $q->join($link, array('joinType' => 'LEFT')); $sfOptions['joinTo'] = $joinTo; $sfOptions['joinModule'] = $q->getFromBean()->module_name; $link_name = "sf_" . $link; } $fjoin = $q->join($link_name, $sfOptions); $where->notNull($fjoin->joinName() . '.id'); }
/** * Run a secondary query and populate the results into the array of beans * * @overrides SugarFieldBase::runSecondaryQuery */ public function runSecondaryQuery($fieldName, SugarBean $seed, array $beans) { if (empty($beans)) { return; } $teamsetToBean = array(); foreach ($beans as $bean) { if (empty($bean->team_set_id)) { continue; } $teamsetToBean[$bean->team_set_id][] = $bean->id; } if (!$teamsetToBean) { return; } $tsb = BeanFactory::newBean('TeamSets'); $query = new SugarQuery(); $query->from($tsb); $query->join('teams', array('alias' => 'teams')); $query->select(array('id', array('teams.id', 'team_id'), array('teams.name', 'name'), array('teams.name_2', 'name_2'))); $query->where()->in('id', array_keys($teamsetToBean)); $rows = $query->execute(); $teamsets = array(); foreach ($rows as $row) { $team = array('id' => $row['team_id']); $team['name'] = !empty($row['name']) ? $row['name'] : ''; $team['name_2'] = !empty($row['name_2']) ? $row['name_2'] : ''; $teamsets[$row['id']][] = $team; } foreach ($teamsetToBean as $teamSetId => $beansWithTeam) { foreach ($beansWithTeam as $beanId) { $beans[$beanId]->teamList = $teamsets[$teamSetId]; } } }
protected function generateFTSQuery($module, $fieldDefs) { $queueTableName = self::QUEUE_TABLE; $bean = BeanFactory::getBean($module); $ftsQuery = new SugarQuery(); $ftsQuery->from($bean); // add fts enabled fields to the filter $fieldsFilter = array('id'); foreach ($fieldDefs as $value) { // skip nondb fields if (!empty($value['source']) && $value['source'] == 'non-db') { continue; } // filter email1 field and add the join. if ($value['name'] == 'email1') { $ftsQuery->join('email_addresses_primary', array('alias' => 'email1')); $fieldsFilter[] = 'email1.email_address'; } else { $fieldsFilter[] = $value['name']; } } // need to get the doc owner to be indexed if (isset($bean->field_defs['assigned_user_id'])) { $docOwnerField = 'assigned_user_id'; } else { if (isset($bean->field_defs['created_by'])) { $docOwnerField = 'created_by'; } } if (!empty($docOwnerField)) { $fieldsFilter[] = $docOwnerField; } $ftsQuery->select($fieldsFilter); // join fts_queue table $ftsQuery->joinTable($queueTableName)->on()->equalsField($queueTableName . '.bean_id', 'id'); // additional fts_queue fields $ftsQueueFields = array(array($queueTableName . '.id', 'fts_id'), array($queueTableName . '.processed', 'fts_processed')); $ftsQuery->select($ftsQueueFields); return $ftsQuery->compileSql(); }