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