/** * 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); }
/** * Gets an array of beans from a SugarQuery * * @param SugarQuery $query - Query object with everything but the from() section filled in * @param array $fields - (Optional) A list of fields to populate in the beans * @param array $options - (Optional) Optional parameters for the function: * returnRawRows - Returns raw rows in the _row key, indexed by bean id * beanList - An array of beans to merge the results into * skipSecondaryQuery - Don't perform the secondary queries * @return array SugarBean - An array of SugarBeans populated with the requested fields */ function fetchFromQuery(SugarQuery $query, array $fields = array(), array $options = array()) { $queryFields = array(); $secondaryFields = array(); $beans = array(); $sfh = new SugarFieldHandler(); if (empty($fields)) { $fields = array_keys($this->field_defs); } foreach ($fields as $field) { if (!isset($this->field_defs[$field]) || !isset($this->field_defs[$field]['type'])) { // Not a valid field, remove it from the list continue; } $def = $this->field_defs[$field]; if ($def['type'] == 'link') { continue; } if (isset($def['link_type']) && $def['link_type'] == 'relationship_info') { // These fields are only here for backwards compatibility continue; } if (isset($def['link']) && $def['link'] != true && !isset($this->field_defs[$def['link']])) { $GLOBALS['log']->error("Invalid link detected: {$field} is looking for {$def['link']}"); continue; } if (!isset($options['skipSecondaryQuery']) || $options['skipSecondaryQuery'] == false) { $type = !empty($def['custom_type']) ? $def['custom_type'] : $this->db->getFieldType($def); $sugarField = $sfh->getSugarField($type); if ($sugarField->fieldNeedsSecondaryQuery($field, $this)) { $secondaryFields[$field] = $sugarField; continue; } } if (isset($def['source']) && $def['source'] == 'non-db' && (empty($def['rname']) || empty($def['link']))) { // Non-db that isn't a relate field. continue; } $queryFields[$field] = $field; // Disable distinct on text type fields, since Oracle doesn't // allow distinct selects on CLOB types $fieldType = $this->db->getFieldType($def); $isTextType = $fieldType ? $this->db->isTextType($fieldType) : false; if ($isTextType) { $query->distinct(false); } } foreach ($this->field_defs as $field => $fieldDef) { if (isset($fieldDef['mandatory_fetch']) && $fieldDef['mandatory_fetch'] == true) { $queryFields[$field] = $field; } } $queryFields['id'] = 'id'; if (isset($this->field_defs['assigned_user_id'])) { $queryFields['assigned_user_id'] = 'assigned_user_id'; } $query->select($queryFields); $this->call_custom_logic('before_fetch_query', array('query' => $query, 'fields' => $fields)); $rows = $query->execute(); $rawRows = array(); foreach ($rows as $row) { if (isset($options['beanList'][$row['id']])) { $bean = $options['beanList'][$row['id']]; } else { $bean = $this->getCleanCopy(); } //true parameter below tells populate to perform conversions on row data $bean->fetched_row = $bean->populateFromRow($row, true); $bean->call_custom_logic("process_record"); $beans[$bean->id] = $bean; $rawRows[$bean->id] = $row; } if (!isset($options['skipSecondaryQuery']) || $options['skipSecondaryQuery'] == false) { foreach ($secondaryFields as $fieldName => $sugarField) { $sugarField->runSecondaryQuery($fieldName, $this, $beans); } } $this->call_custom_logic('after_fetch_query', array('beans' => $beans, 'fields' => $fields, 'rows' => $rawRows)); if (!empty($options['compensateDistinct'])) { $beans['_distinctCompensation'] = $this->computeDistinctCompensation($rows, $beans); } if (!empty($options['returnRawRows'])) { $beans['_rows'] = $rawRows; } return $beans; }
/** * 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 static function addTrackerFilter(SugarQuery $q, SugarQuery_Builder_Where $where, $interval) { global $db; $td = new SugarDateTime(); $td->modify($interval); $min_date = $td->asDb(); // Have to do a subselect because MAX() and GROUP BY don't get along with // databases other than MySQL $q->joinRaw(" INNER JOIN ( SELECT t.item_id item_id, MAX(t.date_modified) track_max " . " FROM tracker t " . " WHERE t.module_name = '" . $db->quote($q->from->module_name) . "' " . " AND t.user_id = '" . $db->quote($GLOBALS['current_user']->id) . "' " . " AND t.date_modified >= " . $db->convert("'" . $min_date . "'", 'datetime') . " " . " GROUP BY t.item_id " . " ) tracker ON tracker.item_id = " . $q->from->getTableName() . ".id ", array('alias' => 'tracker')); // Now, if they want tracker records, so let's order it by the tracker date_modified $q->order_by = array(); $q->orderByRaw('tracker.track_max', 'DESC'); $q->distinct(false); }
/** * * Moved function from BpmInboxViewShowHistoryEntries class [view.showhistoryentries.php] * Using variable members and some fields added. */ public function assemblyEntries() { $entries = array(); $queryOptions = array('add_deleted' => true); $beanFlow = BeanFactory::getBean('pmse_BpmFlow'); $fields = array('id', 'date_entered', 'date_modified', 'cas_id', 'cas_index', 'pro_id', 'cas_previous', 'cas_reassign_level', 'bpmn_id', 'bpmn_type', 'cas_user_id', 'cas_thread', 'cas_flow_status', 'cas_sugar_module', 'cas_sugar_object_id', 'cas_sugar_action', 'cas_adhoc_type', 'cas_adhoc_parent_id', 'cas_task_start_date', 'cas_delegate_date', 'cas_start_date', 'cas_finish_date', 'cas_due_date', 'cas_queue_duration', 'cas_duration', 'cas_delay_duration', 'cas_started', 'cas_finished', 'cas_delayed'); $q = new SugarQuery(); $q->from($beanFlow, $queryOptions); $q->distinct(false); $q->where()->equals('cas_id', $this->case_id); $q->orderBy('cas_index', 'ASC'); $q->select($fields); $caseDerivations = $q->execute(); foreach ($caseDerivations as $key => $caseData) { $entry = $this->fetchUserType($caseData); $currentDate = new DateTime(); $entry['due_date'] = !empty($caseData['cas_due_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_due_date'], 'datetime') : ''; $entry['end_date'] = !empty($caseData['cas_finish_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_finish_date'], 'datetime') : ''; $entry['current_date'] = PMSEEngineUtils::getDateToFE(TimeDate::getInstance()->nowDb(), 'datetime'); $entry['delegate_date'] = !empty($caseData['cas_delegate_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_delegate_date'], 'datetime') : ''; $entry['start_date'] = !empty($caseData['cas_start_date']) ? PMSEEngineUtils::getDateToFE($caseData['cas_start_date'], 'datetime') : ''; $entry['var_values'] = ''; $entry['completed'] = true; $entry['cas_user_id'] = $caseData['cas_user_id']; if ($caseData['cas_previous'] == 0) { //cas_flow_status field should set something instead be empty. $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_CREATED_CASE', 'pmse_Inbox'), $caseData['cas_id']); } else { if ($caseData['cas_flow_status'] == 'CLOSED') { $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_DERIVATED_CASE', 'pmse_Inbox'), $caseData['bpmn_id']); } else { $dataString = sprintf(translate('LBL_PMSE_HISTORY_LOG_CURRENTLY_HAS_CASE', 'pmse_Inbox'), $caseData['bpmn_id']); } } $action = ''; if ($caseData['bpmn_type'] == 'bpmnActivity') { $currentCaseState = $this->getActionStatusAndAction($caseData['cas_flow_status'], $caseData['cas_sugar_action']); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getActivityName($caseData['bpmn_id'])); if ($caseData['cas_flow_status'] != 'FORM') { $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); $res = $this->formAction->retrieve_by_string_fields(array('cas_id' => $caseData['cas_id'], 'act_id' => $caseData['bpmn_id'], 'user_id' => $caseData['cas_user_id'])); if (isset($this->formAction->frm_action) && !empty($this->formAction->frm_action)) { $action = strtoupper($this->formAction->frm_action); } else { $action = translate('LBL_PMSE_HISTORY_LOG_NOT_REGISTED_ACTION', 'pmse_Inbox'); } $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTION_PERFORMED', 'pmse_Inbox'), $action); if (isset($this->formAction->cas_pre_data)) { $logdata = unserialize($this->formAction->cas_pre_data); $entry['var_values'] = $logdata; } } else { $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTION_STILL_ASSIGNED', 'pmse_Inbox')); $entry['completed'] = false; } } else { if ($caseData['bpmn_type'] == 'bpmnEvent') { $name = sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getEventName($caseData['bpmn_id'])); $currentCaseState = sprintf(translate('LBL_PMSE_HISTORY_LOG_WITH_EVENT', 'pmse_Inbox'), $name); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); } else { if ($caseData['bpmn_type'] == 'bpmnGateway') { $name = sprintf(translate('LBL_PMSE_HISTORY_LOG_ACTIVITY_NAME', 'pmse_Inbox'), $this->getEventName($caseData['bpmn_id'])); $currentCaseState = sprintf(translate('LBL_PMSE_HISTORY_LOG_WITH_GATEWAY', 'pmse_Inbox'), $name); $dataString .= sprintf(translate('LBL_PMSE_HISTORY_LOG_MODULE_ACTION', 'pmse_Inbox'), $this->getActivityModule($caseData), $currentCaseState); } } } $entry['data_info'] = $dataString; $entries[] = $entry; } return $entries; }
private function getUnattendedCasesByFlow() { $queryOptions = array('add_deleted' => true); //GET CASES ID WITH INACTIVE USERS $beanFlow = BeanFactory::getBean('pmse_BpmFlow'); $q = new SugarQuery(); $q->from($beanFlow, $queryOptions); $q->distinct(true); $fields = array('cas_id', 'cas_sugar_module', 'cas_sugar_object_id', 'cas_user_id'); //INNER JOIN USERS TABLE $q->joinTable('users', array('alias' => 'users', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('users.id', 'cas_user_id')->equals('users.deleted', 0); $q->where()->equals('cas_flow_status', 'FORM')->in('cas_sugar_module', PMSEEngineUtils::getSupportedModules()); $q->where()->queryOr()->notequals('users.status', 'Active')->notequals('users.employee_status', 'Active'); $q->select($fields); $rows = $q->execute(); return $rows; }