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