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 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);
}
/**
* 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;
}
/**
* Gets recently viewed records.
*
* @param ServiceBase $api Current api.
* @param array $args Arguments from request.
* @param string $acl (optional) ACL action to check, default is `list`.
* @return array List of recently viewed records.
*/
public function getRecentlyViewed($api, $args, $acl = 'list')
{
$this->requireArgs($args, array('module_list'));
$options = $this->parseArguments($args);
$moduleList = $this->filterModules($options['moduleList'], $acl);
if (empty($moduleList)) {
return array('next_offset' => -1, 'records' => array());
}
if (count($moduleList) === 1) {
$moduleName = $moduleList[0];
$seed = BeanFactory::newBean($moduleName);
$mainQuery = $this->getRecentlyViewedQueryObject($seed, $options);
$mainQuery->orderByRaw('MAX(tracker.date_modified)', 'DESC');
} else {
$mainQuery = new SugarQuery();
foreach ($moduleList as $moduleName) {
$seed = BeanFactory::newBean($moduleName);
$mainQuery->union($this->getRecentlyViewedQueryObject($seed, $options), true);
}
$mainQuery->orderByRaw('last_viewed_date', 'DESC');
}
// Add an extra record to the limit so we can detect if there are more records to be found.
$mainQuery->limit($options['limit'] + 1);
$mainQuery->offset($options['offset']);
$data = $beans = array();
$data['next_offset'] = -1;
// 'Cause last_viewed_date is an alias (not a real field), we need to
// temporarily store its values and append it later to each recently
// viewed record
$lastViewedDates = array();
$results = $mainQuery->execute();
$db = DBManagerFactory::getInstance();
foreach ($results as $idx => $recent) {
if ($idx == $options['limit']) {
$data['next_offset'] = (int) ($options['limit'] + $options['offset']);
break;
}
$seed = BeanFactory::getBean($recent['module_name'], $recent['id']);
$lastViewedDates[$seed->id] = $db->fromConvert($recent['last_viewed_date'], 'datetime');
$beans[$seed->id] = $seed;
}
$data['records'] = $this->formatBeans($api, $args, $beans);
global $timedate;
// Append last_viewed_date to each recently viewed record
foreach ($data['records'] as &$record) {
$record['_last_viewed_date'] = $timedate->asIso($timedate->fromDb($lastViewedDates[$record['id']]));
}
return $data;
}
/**
* 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;
}
/**
* 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;
}
public function filterModuleList(ServiceBase $api, array $args, $acl = 'list')
{
if (!empty($args['module_list'])) {
$module_list = explode(',', $args['module_list']);
foreach ($this->moduleList as $link_name => $module) {
if (!in_array($module, $module_list)) {
unset($this->moduleList[$link_name]);
}
}
}
// if the module list is empty then someone passed in bad modules for the history
if (empty($this->moduleList)) {
throw new SugarApiExceptionInvalidParameter("Module List is empty, must contain: Meetings, Calls, Notes, Tasks, or Emails");
}
$query = new SugarQuery();
$api->action = 'list';
$orderBy = array();
// modules is a char field used for sorting on module name
// it is added to the select below, it can be sorted on but needs to be removed from
// the arguments to allow it to be maintained throughout the code
$removedModuleDirection = false;
if (!empty($args['order_by'])) {
$orderBy = explode(',', $args['order_by']);
foreach ($orderBy as $key => $list) {
list($field, $direction) = explode(':', $list);
// `picture` is considered the same field as `module` because it
// corresponds to the module icon.
if ($field === 'module' || $field === 'picture') {
unset($orderBy[$key]);
$removedModuleDirection = !empty($direction) ? $direction : 'DESC';
}
}
$args['order_by'] = implode(',', $orderBy);
$orderBy[] = "module:{$removedModuleDirection}";
}
if (!empty($args['fields'])) {
$args['fields'] .= "," . implode(',', $this->validFields);
} else {
$args['fields'] = implode(',', $this->validFields);
}
if (!empty($args['order_by']) || !empty($args['fields'])) {
$args = $this->scrubFields($args);
}
unset($args['order_by']);
foreach ($this->moduleList as $link_name => $module) {
$args['filter'] = array();
$savedFields = $args['fields'];
$args['link_name'] = $link_name;
$fields = explode(',', $args['fields']);
foreach ($fields as $k => $field) {
if (isset($args['placeholder_fields'][$module][$field])) {
unset($fields[$k]);
}
}
$args['fields'] = implode(',', $fields);
if (!empty($this->moduleFilters[$module])) {
$args['filter'] = $this->moduleFilters[$module];
}
list($args, $q, $options, $linkSeed) = $this->filterRelatedSetup($api, $args);
$q->select()->selectReset();
$q->orderByReset();
// ORACLE doesn't allow order by in UNION queries
if (!empty($args['placeholder_fields'])) {
$newFields = array_merge($args['placeholder_fields'][$module], $fields);
} else {
$newFields = $fields;
}
sort($newFields);
foreach ($newFields as $field) {
if ($field == 'module') {
continue;
}
// special case for description on emails
if ($module == 'Emails' && $field == 'description') {
// ORACLE requires EMPTY_CLOB() for union queries if CLOB fields were used before
$q->select()->fieldRaw(DBManagerFactory::getInstance()->emptyValue('text') . " email_description");
} else {
if (isset($args['placeholder_fields'][$module][$field])) {
$q->select()->fieldRaw("'' {$args['placeholder_fields'][$module][$field]}");
} else {
$q->select()->field($field);
}
}
}
$q->select()->field('id');
$q->select()->field('assigned_user_id');
$q->limit = $q->offset = null;
$q->select()->fieldRaw("'{$module}'", 'module');
$query->union($q);
$query->limit($options['limit'] + 1);
$query->offset($options['offset']);
$args['fields'] = $savedFields;
}
if (!empty($orderBy)) {
if ($removedModuleDirection !== false) {
$orderBy[] = "module:{$removedModuleDirection}";
}
foreach ($orderBy as $order) {
$ordering = explode(':', $order);
if (count($ordering) > 1) {
$query->orderByRaw("{$ordering[0]}", "{$ordering[1]}");
} else {
$query->orderByRaw("{$ordering[0]}");
}
}
} else {
$query->orderByRaw('date_modified');
}
return $this->runQuery($api, $args, $query, $options);
}
public function selectCasesList($api, $args)
{
$this->checkACL($api, $args);
$q = new SugarQuery();
$inboxBean = BeanFactory::getBean('pmse_Inbox');
if ($args['order_by'] == 'cas_due_date:asc') {
$args['order_by'] = 'cas_create_date:asc';
}
$options = self::parseArguments($api, $args, $inboxBean);
$fields = array('a.*');
$q->from($inboxBean, array('alias' => 'a'));
//INNER USER TABLE
$q->joinTable('users', array('alias' => 'u', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('u.id', 'a.created_by')->equals('u.deleted', 0);
$fields[] = array("u.last_name", 'assigned_user_name');
//INNER PROCESS TABLE
$q->joinTable('pmse_bpmn_process', array('alias' => 'pr', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('pr.id', 'a.pro_id')->equals('pr.deleted', 0);
$fields[] = array("pr.prj_id", 'prj_id');
//INNER PROJECT TABLE
$q->joinTable('pmse_project', array('alias' => 'prj', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('prj.id', 'pr.prj_id')->equals('prj.deleted', 0);
$fields[] = array("prj.assigned_user_id", 'prj_created_by');
$fields[] = array("prj.prj_module", 'prj_module');
$q->select($fields);
$q->where()->in('prj.prj_module', PMSEEngineUtils::getSupportedModules());
if (!empty($args['q'])) {
$q->where()->queryAnd()->addRaw("a.cas_title LIKE '%" . $args['q'] . "%' OR a.pro_title LIKE '%" . $args['q'] . "%' OR a.cas_status LIKE '%" . $args['q'] . "%' OR prj.assigned_user_id LIKE '%" . $args['q'] . "%' OR pr.prj_id LIKE '%" . $args['q'] . "%' OR last_name LIKE '%" . $args['q'] . "%'");
}
if (!empty($args['module_list'])) {
switch ($args['module_list']) {
case translate('LBL_STATUS_COMPLETED', 'pmse_Inbox'):
$q->where()->queryAnd()->addRaw("cas_status = 'COMPLETED'");
break;
case translate('LBL_STATUS_TERMINATED', 'pmse_Inbox'):
$q->where()->queryAnd()->addRaw("cas_status = 'TERMINATED'");
break;
case translate('LBL_STATUS_IN_PROGRESS', 'pmse_Inbox'):
$q->where()->queryAnd()->addRaw("cas_status = 'IN PROGRESS'");
break;
case translate('LBL_STATUS_CANCELLED', 'pmse_Inbox'):
$q->where()->queryAnd()->addRaw("cas_status = 'CANCELLED'");
break;
case translate('LBL_STATUS_ERROR', 'pmse_Inbox'):
$q->where()->queryAnd()->addRaw("cas_status = 'ERROR'");
break;
}
}
foreach ($options['order_by'] as $orderBy) {
$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']);
$q->offset($options['offset']);
$offset = $options['offset'] + $options['limit'];
$count = 0;
$list = $q->execute();
foreach ($list as $key => $value) {
if ($value["cas_status"] === 'IN PROGRESS') {
$list[$key]["cas_status"] = '<data class="label label-Leads">' . $value["cas_status"] . '</data>';
} elseif ($value["cas_status"] === 'COMPLETED' || $value["cas_status"] === 'TERMINATED') {
$list[$key]["cas_status"] = '<data class="label label-success">' . $value["cas_status"] . '</data>';
} elseif ($value["cas_status"] === 'CANCELLED') {
$list[$key]["cas_status"] = '<data class="label label-warning">' . $value["cas_status"] . '</data>';
} else {
$list[$key]["cas_status"] = '<data class="label label-important">' . $value["cas_status"] . '</data>';
}
$list[$key]['cas_create_date'] = PMSEEngineUtils::getDateToFE($value['cas_create_date'], 'datetime');
$list[$key]['date_entered'] = PMSEEngineUtils::getDateToFE($value['date_entered'], 'datetime');
$list[$key]['date_modified'] = PMSEEngineUtils::getDateToFE($value['date_modified'], 'datetime');
$prjUsersBean = BeanFactory::getBean('Users', $list[$key]['prj_created_by']);
$list[$key]['prj_user_id_full_name'] = $prjUsersBean->full_name;
$qA = new SugarQuery();
$flowBean = BeanFactory::getBean('pmse_BpmFlow');
$qA->select->fieldRaw('*');
$qA->from($flowBean);
$qA->where()->equals('cas_id', $list[$key]['cas_id']);
$processUsers = $qA->execute();
$processUsersNames = array();
foreach ($processUsers as $k => $v) {
if ($processUsers[$k]['cas_flow_status'] != 'CLOSED') {
$casUsersBean = BeanFactory::getBean('Users', $processUsers[$k]['cas_user_id']);
$processUsersNames[] = !empty($casUsersBean->full_name) ? $casUsersBean->full_name : '';
}
$cas_sugar_module = $processUsers[$k]['cas_sugar_module'];
$cas_sugar_object_id = $processUsers[$k]['cas_sugar_object_id'];
}
if (empty($processUsersNames)) {
$userNames = '';
} else {
$processUsersNames = array_unique($processUsersNames);
$userNames = implode(', ', $processUsersNames);
}
$list[$key]['cas_user_id_full_name'] = $userNames;
$assignedBean = BeanFactory::getBean($cas_sugar_module, $cas_sugar_object_id);
$assignedUsersBean = BeanFactory::getBean('Users', $assignedBean->assigned_user_id);
$list[$key]['assigned_user_name'] = $assignedUsersBean->full_name;
$count++;
}
if ($count == $options['limit']) {
$offset = $options['offset'] + $options['limit'];
} else {
$offset = -1;
}
$data = array();
$data['next_offset'] = $offset;
$data['records'] = $list;
return $data;
}
