/**
* 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();
}
