/** * 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; }
/** * Build a Join Query with a SugarQuery Object * * @param Link2 $link * @param SugarQuery $sugar_query * @param Array $options array of additional paramters. Possible parameters include * - 'myAlias' String name of starting table alias * - 'joinTableAlias' String alias to use for the related table in the final result * - 'reverse' Boolean true if this join should be built in reverse for subpanel style queries where the select is * on the related table * - 'ignoreRole' Boolean true if the role column of the relationship should be ignored for this join . * * @return SugarQuery */ public function buildJoinSugarQuery(Link2 $link, $sugar_query, $options) { $linkIsLHS = $link->getSide() == REL_LHS; if (!empty($options['reverse'])) { $linkIsLHS = !$linkIsLHS; } $startingTable = $linkIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table']; $startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key']; $targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table']; $targetModule = $linkIsLHS ? $this->def['rhs_module'] : $this->def['lhs_module']; $targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key']; $join_type = isset($options['joinType']) ? $options['joinType'] : 'INNER'; $joinParams = array('joinType' => $join_type, 'bean' => BeanFactory::newBean($targetModule)); $jta = $targetTable; if (!empty($options['joinTableAlias'])) { $jta = $joinParams['alias'] = $options['joinTableAlias']; } $joinTable = $sugar_query->joinTable($targetTable, $joinParams); $joinTable->on()->equalsField("{$startingTable}.{$startingKey}", "{$jta}.{$targetKey}")->equals("{$jta}.deleted", "0"); $relTable = $linkIsLHS ? $jta : $startingTable; if (empty($options['ignoreRole'])) { foreach ($this->getRelationshipRoleColumns() as $column => $value) { $sugar_query->where()->equals("{$relTable}.{$column}", $value); } } $this->addCustomToSugarQuery($sugar_query, $options, $linkIsLHS, $jta); return $sugar_query->join[$jta]; }
private function get_project_elements($prj_id, $dia_id, $table) { $boundFields = array('bound.bou_element', 'bound.bou_element_type', 'bound.bou_x', 'bound.bou_y', 'bound.bou_width', 'bound.bou_height', 'bound.bou_rel_position', 'bound.bou_size_identical', 'bound.bou_container'); $elementFields = array(); switch ($table) { case 'bpmn_activity': $bean = BeanFactory::newBean('pmse_BpmnActivity'); $elementFields = array('id', 'name', 'act_type', 'act_is_for_compensation', 'act_start_quantity', 'act_completion_quantity', 'act_task_type', 'act_implementation', 'act_instantiate', 'act_script_type', 'act_script', 'act_loop_type', 'act_test_before', 'act_loop_maximum', 'act_loop_condition', 'act_loop_cardinality', 'act_loop_behavior', 'act_is_adhoc', 'act_is_collapsed', 'act_completion_condition', 'act_ordering', 'act_cancel_remaining_instances', 'act_protocol', 'act_method', 'act_is_global', 'act_referer', 'act_default_flow', 'act_master_diagram'); break; case 'bpmn_event': $bean = BeanFactory::newBean('pmse_BpmnEvent'); $elementFields = array('id', 'name', 'evn_type', 'evn_marker', 'evn_is_interrupting', 'evn_attached_to', 'evn_cancel_activity', 'evn_activity_ref', 'evn_wait_for_completion', 'evn_error_name', 'evn_error_code', 'evn_escalation_name', 'evn_escalation_code', 'evn_condition', 'evn_message', 'evn_operation_name', 'evn_operation_implementation', 'evn_time_date', 'evn_time_cycle', 'evn_time_duration', 'evn_behavior'); break; case 'bpmn_gateway': $bean = BeanFactory::newBean('pmse_BpmnGateway'); $elementFields = array('id', 'name', 'gat_type', 'gat_direction', 'gat_instantiate', 'gat_event_gateway_type', 'gat_activation_count', 'gat_waiting_for_start', 'gat_default_flow'); break; case 'bpmn_artifact': $bean = BeanFactory::newBean('pmse_BpmnArtifact'); $elementFields = array('id', 'name', 'art_type', 'art_category_ref'); break; } $q = new SugarQuery(); //add fields to select $fields = array_merge($elementFields, $boundFields); $q->select($fields); $q->from($bean); $q->joinTable('pmse_bpmn_bound', array('alias' => 'bound', 'joinType' => 'INNER', 'linkingTable' => true))->on()->equalsField('bound.bou_element', 'id')->equals('bound.deleted', 0); $q->select()->field($boundFields); $q->where()->equals('prj_id', $prj_id); $rows = $q->execute(); return $rows; }
/** * Returns query to ask for email addresses for specific bean type * @param string $module * @return SugarQuery */ public function getEmailsQuery($module) { $q = new SugarQuery(); $q->from($this); $q->select(array('email_address', 'opt_out', 'invalid_email', 'ear.primary_address', 'ear.reply_to_address')); $q->joinTable("email_addr_bean_rel", array('alias' => "ear", 'joinType' => "LEFT", "linkingTable" => true))->on()->equalsField('id', 'ear.email_address_id', $this)->equals('ear.deleted', 0); $q->where()->equals('deleted', 0)->equals('ear.bean_module', $this->getCorrectedModule($module)); $q->orderBy('ear.primary_address', 'DESC'); return $q; }
/** * Build a Join using an existing SugarQuery Object * @param Link2 $link * @param SugarQuery $sugar_query * @param Array $options array of additional paramters. Possible parameters include * - 'myAlias' String name of starting table alias * - 'joinTableAlias' String alias to use for the related table in the final result * - 'reverse' Boolean true if this join should be built in reverse for subpanel style queries where the select is * on the related table * - 'ignoreRole' Boolean true if the role column of the relationship should be ignored for this join . * @return SugarQuery */ public function buildJoinSugarQuery(Link2 $link, $sugar_query, $options) { $linkIsLHS = $link->getSide() == REL_LHS; if (!empty($options['reverse'])) { $linkIsLHS = !$linkIsLHS; } $startingTable = $linkIsLHS ? $this->def['lhs_table'] : $this->def['rhs_table']; if (!empty($options['myAlias'])) { $startingTable = $options['myAlias']; } $startingKey = $linkIsLHS ? $this->def['lhs_key'] : $this->def['rhs_key']; $startingJoinKey = $linkIsLHS ? $this->def['join_key_lhs'] : $this->def['join_key_rhs']; $joinTable = $this->getRelationshipTable(); $joinKey = $linkIsLHS ? $this->def['join_key_rhs'] : $this->def['join_key_lhs']; $targetTable = $linkIsLHS ? $this->def['rhs_table'] : $this->def['lhs_table']; $targetKey = $linkIsLHS ? $this->def['rhs_key'] : $this->def['lhs_key']; $targetModule = $linkIsLHS ? $this->def['rhs_module'] : $this->def['lhs_module']; $join_type = isset($options['joinType']) ? $options['joinType'] : 'INNER'; $joinTable_alias = $sugar_query->getJoinTableAlias($joinTable, false, false); $targetTable_alias = !empty($options['joinTableAlias']) ? $options['joinTableAlias'] : $targetTable; $relTableJoin = $sugar_query->joinTable($joinTable, array('alias' => $joinTable_alias, 'joinType' => $join_type, 'linkingTable' => true))->on()->equalsField("{$startingTable}.{$startingKey}", "{$joinTable_alias}.{$startingJoinKey}")->equals("{$joinTable_alias}.deleted", "0"); $targetTableJoin = $sugar_query->joinTable($targetTable, array('alias' => $targetTable_alias, 'joinType' => $join_type, 'bean' => BeanFactory::newBean($targetModule)))->on()->equalsField("{$targetTable_alias}.{$targetKey}", "{$joinTable_alias}.{$joinKey}")->equals("{$targetTable_alias}.deleted", "0"); $sugar_query->join[$targetTable_alias]->relationshipTableAlias = $joinTable_alias; if (empty($options['ignoreRole'])) { $this->buildSugarQueryRoleWhere($sugar_query, $joinTable_alias); } $this->addCustomToSugarQuery($sugar_query, $options, $linkIsLHS, $targetTable_alias); return array($joinTable_alias => $relTableJoin, $targetTable_alias => $targetTableJoin); }
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; }
/** * Add a Favorites block to the SugarQuery Object to fetch favorites for a specific [default to current] user * @param SugarQuery $sugar_query * @param bool $joinTo * @param string $join_type * @param bool|guid $user_id * @return string */ public function addToSugarQuery(SugarQuery $sugar_query, $options = array()) { $alias = ''; $user_id = !isset($options['current_user_id']) ? $GLOBALS['current_user']->id : $options['current_user_id']; $joinTo = !isset($options['joinTo']) ? false : $options['joinTo']; $joinType = !isset($options['joinType']) ? 'INNER' : $options['joinType']; if (!$joinTo) { if (is_array($sugar_query->from)) { list($bean, $alias) = $sugar_query->from; } else { $bean = $sugar_query->from; $alias = $bean->getTableName(); } } else { $alias = $joinTo->joinName(); $bean = $sugar_query->getTableBean($alias); } if (empty($bean)) { return false; } $sfAlias = "sf_" . $bean->getTableName(); $sugar_query->joinTable(self::getTableName(), array('alias' => $sfAlias, 'joinType' => $joinType, 'linkingTable' => true))->on()->equals("{$sfAlias}.module", $bean->module_name, $this)->equalsField("{$sfAlias}.record_id", "{$alias}.id", $this)->equals("{$sfAlias}.assigned_user_id", $user_id, $this)->equals("{$sfAlias}.deleted", 0, $this); return $sfAlias; }
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(); }
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; }
/** * Generates query for fetching non-primary emails for the given beans * * @param SugarBean $bean Bean instance * @param array $ids Bean IDs * * @return SugarQuery */ function getNonPrimaryEmailsExportQuery(SugarBean $bean, array $ids) { $query = new SugarQuery(); $query->from(BeanFactory::newBean('EmailAddresses'), 'ea'); $query->joinTable('email_addr_bean_rel', array('joinType' => 'LEFT', 'alias' => 'eabr', 'linkingTable' => true))->on()->equalsField('eabr.email_address_id', 'id'); $query->select('eabr.bean_id', 'email_address', 'invalid_email', 'opt_out'); $query->where()->in('eabr.bean_id', $ids)->equals('eabr.bean_module', $bean->module_dir)->notEquals('eabr.primary_address', 1)->notEquals('eabr.deleted', 1); $query->orderBy('eabr.bean_id', 'ASC')->orderBy('eabr.reply_to_address', 'ASC')->orderBy('email_address', 'ASC'); return $query; }