public function testgetGroupUsersJoin() { $securityGroup = new SecurityGroup(); $expected = " LEFT JOIN (\n select distinct sec.user_id as id from securitygroups_users sec\n inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0\n and secu.user_id = '1'\n where sec.deleted = 0\n ) securitygroup_join on securitygroup_join.id = users.id "; $actual = $securityGroup->getGroupUsersJoin(1); $this->assertSame($expected, $actual); }
/** * Return the list query used by the list views and export button. Next generation of create_new_list_query function. * * Override this function to return a custom query. * * @param string $order_by custom order by clause * @param string $where custom where clause * @param array $filter Optioanal * @param array $params Optional * * @param int $show_deleted Optional, default 0, show deleted records is set to 1. * @param string $join_type * @param boolean $return_array Optional, default false, response as array * @param object $parentbean creating a subquery for this bean. * @param boolean $singleSelect Optional, default false. * @return String select query string, optionally an array value will be returned if $return_array= true. */ function create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false) { global $beanFiles, $beanList; $selectedFields = array(); $secondarySelectedFields = array(); $ret_array = array(); $distinct = ''; if ($this->bean_implements('ACL') && ACLController::requireOwner($this->module_dir, 'list')) { global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); if (empty($where)) { $where = $owner_where; } else { $where .= ' AND ' . $owner_where; } } /* BEGIN - SECURITY GROUPS */ /** changed for performance reasons global $current_user, $sugar_config; if($this->module_dir == 'Users' && !is_admin($current_user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] == true ) { require_once('modules/SecurityGroups/SecurityGroup.php'); global $current_user; $group_where = SecurityGroup::getGroupUsersWhere($current_user->id); //$group_where = "user_name = 'admin'"; if(empty($where)) { $where = " (".$group_where.") "; } else { $where .= " AND (".$group_where.") "; } } else if($this->bean_implements('ACL') && ACLController::requireSecurityGroup($this->module_dir, 'list') ) { require_once('modules/SecurityGroups/SecurityGroup.php'); global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); $group_where = SecurityGroup::getGroupWhere($this->table_name,$this->module_dir,$current_user->id); if(!empty($owner_where)){ if(empty($where)) { $where = " (". $owner_where." or ".$group_where.") "; } else { $where .= " AND (". $owner_where." or ".$group_where.") "; } } else { $where .= ' AND '. $group_where; } } */ /* END - SECURITY GROUPS */ if (!empty($params['distinct'])) { $distinct = ' DISTINCT '; } if (empty($filter)) { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.* "; } else { $ret_array['select'] = " SELECT {$distinct} {$this->table_name}.id "; } $ret_array['from'] = " FROM {$this->table_name} "; $ret_array['from_min'] = $ret_array['from']; $ret_array['secondary_from'] = $ret_array['from']; $ret_array['where'] = ''; $ret_array['order_by'] = ''; //secondary selects are selects that need to be run after the primarty query to retrieve additional info on main if ($singleSelect) { $ret_array['secondary_select'] =& $ret_array['select']; $ret_array['secondary_from'] =& $ret_array['from']; } else { $ret_array['secondary_select'] = ''; } $custom_join = false; if ((!isset($params['include_custom_fields']) || $params['include_custom_fields']) && isset($this->custom_fields)) { $custom_join = $this->custom_fields->getJOIN(empty($filter) ? true : $filter); if ($custom_join) { $ret_array['select'] .= ' ' . $custom_join['select']; } } if ($custom_join) { $ret_array['from'] .= ' ' . $custom_join['join']; } $jtcount = 0; //LOOP AROUND FOR FIXIN VARDEF ISSUES require 'include/VarDefHandler/listvardefoverride.php'; $joined_tables = array(); if (isset($params['joined_tables'])) { foreach ($params['joined_tables'] as $table) { $joined_tables[$table] = 1; } } if (!empty($filter)) { $filterKeys = array_keys($filter); if (is_numeric($filterKeys[0])) { $fields = array(); foreach ($filter as $field) { $field = strtolower($field); //remove out id field so we don't duplicate it if ($field == 'id' && !empty($filter)) { continue; } if (isset($this->field_defs[$field])) { $fields[$field] = $this->field_defs[$field]; } else { $fields[$field] = array('force_exists' => true); } } } else { $fields = $filter; } } else { $fields = $this->field_defs; } $used_join_key = array(); foreach ($fields as $field => $value) { //alias is used to alias field names $alias = ''; if (isset($value['alias'])) { $alias = ' as ' . $value['alias'] . ' '; } if (empty($this->field_defs[$field]) || !empty($value['force_blank'])) { if (!empty($filter) && isset($filter[$field]['force_exists']) && $filter[$field]['force_exists']) { if (isset($filter[$field]['force_default'])) { $ret_array['select'] .= ", {$filter[$field]['force_default']} {$field} "; } else { //spaces are a fix for length issue problem with unions. The union only returns the maximum number of characters from the first select statemtn. $ret_array['select'] .= ", ' ' {$field} "; } } continue; } else { $data = $this->field_defs[$field]; } //ignore fields that are a part of the collection and a field has been removed as a result of //layout customization.. this happens in subpanel customizations, use case, from the contacts subpanel //in opportunities module remove the contact_role/opportunity_role field. $process_field = true; if (isset($data['relationship_fields']) and !empty($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $field_name) { if (!isset($fields[$field_name])) { $process_field = false; } } } if (!$process_field) { continue; } if ((!isset($data['source']) || $data['source'] == 'db') && (!empty($alias) || !empty($filter))) { $ret_array['select'] .= ", {$this->table_name}.{$field} {$alias}"; $selectedFields["{$this->table_name}.{$field}"] = true; } if ($data['type'] != 'relate' && isset($data['db_concat_fields'])) { $ret_array['select'] .= ", " . db_concat($this->table_name, $data['db_concat_fields']) . " as {$field}"; $selectedFields[db_concat($this->table_name, $data['db_concat_fields'])] = true; } //Custom relate field or relate fields built in module builder which have no link field associated. if ($data['type'] == 'relate' && (isset($data['custom_module']) || isset($data['ext2']))) { $joinTableAlias = 'jt' . $jtcount; $relateJoinInfo = $this->custom_fields->getRelateJoin($data, $joinTableAlias); $ret_array['select'] .= $relateJoinInfo['select']; $ret_array['from'] .= $relateJoinInfo['from']; //Replace any references to the relationship in the where clause with the new alias //If the link isn't set, assume that search used the local table for the field $searchTable = isset($data['link']) ? $relateJoinInfo['rel_table'] : $this->table_name; $field_name = $relateJoinInfo['rel_table'] . '.' . !empty($data['name']) ? $data['name'] : 'name'; $where = preg_replace('/(^|[\\s(])' . $field_name . '/', '${1}' . $relateJoinInfo['name_field'], $where); $jtcount++; } //Parent Field if ($data['type'] == 'parent') { //See if we need to join anything by inspecting the where clause $match = preg_match('/(^|[\\s(])parent_(\\w+)_(\\w+)\\.name/', $where, $matches); if ($match) { $joinTableAlias = 'jt' . $jtcount; $joinModule = $matches[2]; $joinTable = $matches[3]; $localTable = $this->table_name; if (!empty($data['custom_module'])) { $localTable .= '_cstm'; } global $beanFiles, $beanList, $module; require_once $beanFiles[$beanList[$joinModule]]; $rel_mod = new $beanList[$joinModule](); $nameField = "{$joinTableAlias}.name"; if (isset($rel_mod->field_defs['name'])) { $name_field_def = $rel_mod->field_defs['name']; if (isset($name_field_def['db_concat_fields'])) { $nameField = db_concat($joinTableAlias, $name_field_def['db_concat_fields']); } } $ret_array['select'] .= ", {$nameField} {$data['name']} "; $ret_array['from'] .= " LEFT JOIN {$joinTable} {$joinTableAlias}\n ON {$localTable}.{$data['id_name']} = {$joinTableAlias}.id"; //Replace any references to the relationship in the where clause with the new alias $where = preg_replace('/(^|[\\s(])parent_' . $joinModule . '_' . $joinTable . '\\.name/', '${1}' . $nameField, $where); $jtcount++; } } if ($data['type'] == 'relate' && isset($data['link'])) { $this->load_relationship($data['link']); if (!empty($this->{$data}['link'])) { $params = array(); if (empty($join_type)) { $params['join_type'] = ' LEFT JOIN '; } else { $params['join_type'] = $join_type; } if (isset($data['join_name'])) { $params['join_table_alias'] = $data['join_name']; } else { $params['join_table_alias'] = 'jt' . $jtcount; } if (isset($data['join_link_name'])) { $params['join_table_link_alias'] = $data['join_link_name']; } else { $params['join_table_link_alias'] = 'jtl' . $jtcount; } $join_primary = !isset($data['join_primary']) || $data['join_primary']; $join = $this->{$data}['link']->getJoin($params, true); $used_join_key[] = $join['rel_key']; $rel_module = $this->{$data}['link']->getRelatedModuleName(); $table_joined = !empty($joined_tables[$params['join_table_alias']]) || !empty($joined_tables[$params['join_table_link_alias']]) && isset($data['link_type']) && $data['link_type'] == 'relationship_info'; //if rnanme is set to 'name', and bean files exist, then check if field should be a concatenated name global $beanFiles, $beanList; if ($data['rname'] && !empty($beanFiles[$beanList[$rel_module]])) { //create an instance of the related bean require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); //if bean has first and last name fields, then name should be concatenated if (isset($rel_mod->field_name_map['first_name']) && isset($rel_mod->field_name_map['last_name'])) { $data['db_concat_fields'] = array(0 => 'first_name', 1 => 'last_name'); } } if ($join['type'] == 'many-to-many') { if (empty($ret_array['secondary_select'])) { $ret_array['secondary_select'] = " SELECT {$this->table_name}.id ref_id "; if (!empty($beanFiles[$beanList[$rel_module]]) && $join_primary) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".assigned_user_id {$field}_owner, '{$rel_module}' {$field}_mod"; } else { if (isset($rel_mod->field_defs['created_by'])) { $ret_array['secondary_select'] .= " , " . $params['join_table_alias'] . ".created_by {$field}_owner , '{$rel_module}' {$field}_mod"; } } } } if (isset($data['db_concat_fields'])) { $ret_array['secondary_select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { if (!isset($data['relationship_fields'])) { $ret_array['secondary_select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } } if (!$singleSelect) { $ret_array['select'] .= ", ' ' {$field} "; $ret_array['select'] .= ", ' ' " . $join['rel_key'] . ' '; } $count_used = 0; if ($this->db->dbType != 'mysql') { //bug 26801, these codes are just used to duplicate rel_key in the select sql, or it will throw error in MSSQL and Oracle. foreach ($used_join_key as $used_key) { if ($used_key == $join['rel_key']) { $count_used++; } } } if ($count_used <= 1) { //27416, the $ret_array['secondary_select'] should always generate, regardless the dbtype $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $join['rel_key'] . ' ' . $join['rel_key']; } if (isset($data['relationship_fields'])) { foreach ($data['relationship_fields'] as $r_name => $alias_name) { if (!empty($secondarySelectedFields[$alias_name])) { continue; } $ret_array['secondary_select'] .= ', ' . $params['join_table_link_alias'] . '.' . $r_name . ' ' . $alias_name; $secondarySelectedFields[$alias_name] = true; } } if (!$table_joined) { $ret_array['secondary_from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (isset($data['link_type']) && $data['link_type'] == 'relationship_info' && $parentbean instanceof SugarBean) { $ret_array['secondary_where'] = $params['join_table_link_alias'] . '.' . $join['rel_key'] . "='" . $parentbean->id . "'"; } } } else { if (isset($data['db_concat_fields'])) { $ret_array['select'] .= ' , ' . db_concat($params['join_table_alias'], $data['db_concat_fields']) . ' ' . $field; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field; } if (isset($data['additionalFields'])) { foreach ($data['additionalFields'] as $k => $v) { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $k . ' ' . $v; } } if (!$table_joined) { $ret_array['from'] .= ' ' . $join['join'] . ' AND ' . $params['join_table_alias'] . '.deleted=0'; if (!empty($beanList[$rel_module]) && !empty($beanFiles[$beanList[$rel_module]])) { require_once $beanFiles[$beanList[$rel_module]]; $rel_mod = new $beanList[$rel_module](); if (isset($value['target_record_key']) && !empty($filter)) { $selectedFields[$this->table_name . '.' . $value['target_record_key']] = true; $ret_array['select'] .= " , {$this->table_name}.{$value['target_record_key']} "; } if (isset($rel_mod->field_defs['assigned_user_id'])) { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.assigned_user_id ' . $field . '_owner'; } else { $ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.created_by ' . $field . '_owner'; } $ret_array['select'] .= " , '" . $rel_module . "' " . $field . '_mod'; } } } //Replace references to this table in the where clause with the new alias $join_table_name = $this->{$data}['link']->getRelatedTableName(); // To fix SOAP stuff where we are trying to retrieve all the accounts data where accounts.id = .. // and this code changes accounts to jt4 as there is a self join with the accounts table. //Martin fix #27494 if (isset($data['db_concat_fields'])) { $buildWhere = false; if (in_array('first_name', $data['db_concat_fields']) && in_array('last_name', $data['db_concat_fields'])) { $exp = '/\\(\\s*?' . $data['name'] . '.*?\\%\'\\s*?\\)/'; if (preg_match($exp, $where, $matches)) { $search_expression = $matches[0]; //Create three search conditions - first + last, first, last $first_name_search = str_replace($data['name'], $params['join_table_alias'] . '.first_name', $search_expression); $last_name_search = str_replace($data['name'], $params['join_table_alias'] . '.last_name', $search_expression); $full_name_search = str_replace($data['name'], db_concat($params['join_table_alias'], $data['db_concat_fields']), $search_expression); $buildWhere = true; $where = str_replace($search_expression, '(' . $full_name_search . ' OR ' . $first_name_search . ' OR ' . $last_name_search . ')', $where); } } if (!$buildWhere) { $db_field = db_concat($params['join_table_alias'], $data['db_concat_fields']); $where = preg_replace('/' . $data['name'] . '/', $db_field, $where); } } else { $where = preg_replace('/(^|[\\s(])' . $data['name'] . '/', '${1}' . $params['join_table_alias'] . '.' . $data['rname'], $where); } if (!$table_joined) { $joined_tables[$params['join_table_alias']] = 1; $joined_tables[$params['join_table_link_alias']] = 1; } $jtcount++; } } } if (!empty($filter)) { if (isset($this->field_defs['assigned_user_id']) && empty($selectedFields[$this->table_name . '.assigned_user_id'])) { $ret_array['select'] .= ", {$this->table_name}.assigned_user_id "; } else { if (isset($this->field_defs['created_by']) && empty($selectedFields[$this->table_name . '.created_by'])) { $ret_array['select'] .= ", {$this->table_name}.created_by "; } } if (isset($this->field_defs['system_id']) && empty($selectedFields[$this->table_name . '.system_id'])) { $ret_array['select'] .= ", {$this->table_name}.system_id "; } } /* BEGIN - SECURITY GROUPS */ global $current_user, $sugar_config; if ($this->module_dir == 'Users' && !is_admin($current_user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] == true) { require_once 'modules/SecurityGroups/SecurityGroup.php'; $owner_where = $this->getOwnerWhere($current_user->id); $group_where = 'securitygroup_join.id is not null'; if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ") "; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ") "; } $securitygroup_join .= SecurityGroup::getGroupUsersJoin($current_user->id); $ret_array['from'] .= $securitygroup_join; $ret_array['from_min'] .= $securitygroup_join; if (!$singleSelect) { $ret_array['secondary_from'] .= $securitygroup_join; } } else { if ($this->bean_implements('ACL') && ACLController::requireSecurityGroup($this->module_dir, 'list')) { require_once 'modules/SecurityGroups/SecurityGroup.php'; global $current_user; $owner_where = $this->getOwnerWhere($current_user->id); $group_where = 'securitygroup_join.id is not null'; if (empty($where)) { $where = " (" . $owner_where . " or " . $group_where . ") "; } else { $where .= " AND (" . $owner_where . " or " . $group_where . ") "; } $securitygroup_join = SecurityGroup::getGroupJoin($this->table_name, $this->module_dir, $current_user->id); $ret_array['from'] .= $securitygroup_join; $ret_array['from_min'] .= $securitygroup_join; if (!$singleSelect) { $ret_array['secondary_from'] .= $securitygroup_join; } } } /* END - SECURITY GROUPS */ $where_auto = '1=1'; if ($show_deleted == 0) { $where_auto = "{$this->table_name}.deleted=0"; } else { if ($show_deleted == 1) { $where_auto = "{$this->table_name}.deleted=1"; } } if ($where != "") { $ret_array['where'] = " where ({$where}) AND {$where_auto}"; } else { $ret_array['where'] = " where {$where_auto}"; } if (!empty($order_by)) { //make call to process the order by clause $ret_array['order_by'] = " ORDER BY " . $this->process_order_by($order_by, null); } if ($singleSelect) { unset($ret_array['secondary_where']); unset($ret_array['secondary_from']); unset($ret_array['secondary_select']); } if ($return_array) { return $ret_array; } return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by']; }