/**
* 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, $ifListForExport = 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 */
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 primary 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 = $this->getCustomJoin(empty($filter) ? true : $filter);
if (!isset($params['include_custom_fields']) || $params['include_custom_fields']) {
$ret_array['select'] .= $custom_join['select'];
}
$ret_array['from'] .= $custom_join['join'];
// Bug 52490 - Captivea (Sve) - To be able to add custom fields inside where clause in a subpanel
$ret_array['from_min'] .= $custom_join['join'];
$jtcount = 0;
//LOOP AROUND FOR FIXIN VARDEF ISSUES
require 'include/VarDefHandler/listvardefoverride.php';
if (file_exists('custom/include/VarDefHandler/listvardefoverride.php')) {
require 'custom/include/VarDefHandler/listvardefoverride.php';
}
$joined_tables = array();
if (!empty($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();
//walk through the fields and for every relationship field add their relationship_info field
//relationshipfield-aliases are resolved in SugarBean::create_new_list_query through their relationship_info field
$addrelate = array();
foreach ($fields as $field => $value) {
if (isset($this->field_defs[$field]) && isset($this->field_defs[$field]['source']) && $this->field_defs[$field]['source'] == 'non-db') {
$addrelatefield = $this->get_relationship_field($field);
if ($addrelatefield) {
$addrelate[$addrelatefield] = true;
}
}
if (!empty($this->field_defs[$field]['id_name'])) {
$addrelate[$this->field_defs[$field]['id_name']] = true;
}
}
$fields = array_merge($addrelate, $fields);
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 statement.
$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.
if (isset($data['relationship_fields']) and !empty($data['relationship_fields'])) {
$process_field = false;
foreach ($data['relationship_fields'] as $field_name) {
if (isset($fields[$field_name])) {
$process_field = true;
break;
}
}
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;
} else {
if ((!isset($data['source']) || $data['source'] == 'custom_fields') && (!empty($alias) || !empty($filter))) {
//add this column only if it has NOT already been added to select statement string
$colPos = strpos($ret_array['select'], "{$this->table_name}" . "_cstm" . ".{$field}");
if (!$colPos || $colPos < 0) {
$ret_array['select'] .= ", {$this->table_name}" . "_cstm" . ".{$field} {$alias}";
}
$selectedFields["{$this->table_name}.{$field}"] = true;
}
}
if ($data['type'] != 'relate' && isset($data['db_concat_fields'])) {
$ret_array['select'] .= ", " . $this->db->concat($this->table_name, $data['db_concat_fields']) . " as {$field}";
$selectedFields[$this->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, false);
$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 = $this->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 ($this->is_relate_field($field)) {
$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 rname is set to 'name', and bean files exist, then check if field should be a concatenated name
global $beanFiles, $beanList;
// °3/21/2014 FIX NS-TEAM - Relationship fields could not be displayed in subpanels
//if($data['rname'] && !empty($beanFiles[$beanList[$rel_module]])) {
if (isset($data['rname']) && $data['rname'] == 'name' && !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'] .= ' , ' . $this->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} ";
}
$count_used = 0;
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
// add rel_key only if it was not aready added
if (!$singleSelect) {
$ret_array['select'] .= ", ' ' " . $join['rel_key'] . ' ';
}
$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'] .= ' , ' . $this->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) {
if (!empty($data['id_name']) && $data['id_name'] == $v && !empty($fields[$data['id_name']])) {
continue;
}
$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';
}
}
}
// 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'], $this->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 = $this->db->concat($params['join_table_alias'], $data['db_concat_fields']);
$where = preg_replace('/' . $data['name'] . '/', $db_field, $where);
// For relationship fields replace their alias by the corresponsding link table and r_name
if (isset($data['relationship_fields'])) {
foreach ($data['relationship_fields'] as $r_name => $alias_name) {
$db_field = $this->db->concat($params['join_table_link_alias'], $r_name);
$where = preg_replace('/' . $alias_name . '/', $db_field, $where);
}
}
}
} else {
$where = preg_replace('/(^|[\\s(])' . $data['name'] . '/', '${1}' . $params['join_table_alias'] . '.' . $data['rname'], $where);
// For relationship fields replace their alias by the corresponsding link table and r_name
if (isset($data['relationship_fields'])) {
foreach ($data['relationship_fields'] as $r_name => $alias_name) {
$where = preg_replace('/(^|[\\s(])' . $alias_name . '/', '${1}' . $params['join_table_link_alias'] . '.' . $r_name, $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 ";
}
}
if ($ifListForExport) {
if (isset($this->field_defs['email1'])) {
$ret_array['select'] .= " ,email_addresses.email_address email1";
$ret_array['from'] .= " LEFT JOIN email_addr_bean_rel on {$this->table_name}.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module='{$this->module_dir}' and email_addr_bean_rel.deleted=0 and email_addr_bean_rel.primary_address=1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id ";
}
}
$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}";
}
//make call to process the order by clause
$order_by = $this->process_order_by($order_by);
if (!empty($order_by)) {
$ret_array['order_by'] = " ORDER BY " . $order_by;
}
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'];
}
function get_bean_select_array($add_blank = true, $bean_name, $display_columns, $where = '', $order_by = '', $blank_is_none = false)
{
global $beanFiles;
require_once $beanFiles[$bean_name];
$focus = new $bean_name();
$user_array = array();
$key = $bean_name == 'EmailTemplate' ? $bean_name : $bean_name . $display_columns . $where . $order_by;
$user_array = get_register_value('select_array', $key);
if (!$user_array) {
$db = DBManagerFactory::getInstance();
$temp_result = array();
$query = "SELECT {$focus->table_name}.id, {$display_columns} as display from {$focus->table_name} ";
$query .= "where ";
if ($where != '') {
$query .= $where . " AND ";
}
$query .= " {$focus->table_name}.deleted=0";
/* BEGIN - SECURITY GROUPS */
global $current_user, $sugar_config;
if ($focus->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';
$group_where = SecurityGroup::getGroupUsersWhere($current_user->id);
$query .= " AND (" . $group_where . ") ";
} else {
if ($focus->bean_implements('ACL') && ACLController::requireSecurityGroup($focus->module_dir, 'list')) {
require_once 'modules/SecurityGroups/SecurityGroup.php';
$owner_where = $focus->getOwnerWhere($current_user->id);
$group_where = SecurityGroup::getGroupWhere($focus->table_name, $focus->module_dir, $current_user->id);
if (!empty($owner_where)) {
$query .= " AND (" . $owner_where . " or " . $group_where . ") ";
} else {
$query .= ' AND ' . $group_where;
}
}
}
/* END - SECURITY GROUPS */
if ($order_by != '') {
$query .= " order by {$focus->table_name}.{$order_by}";
}
$GLOBALS['log']->debug("get_user_array query: {$query}");
$result = $db->query($query, true, "Error filling in user array: ");
if ($add_blank == true) {
// Add in a blank row
if ($blank_is_none == true) {
// set 'blank row' to "--None--"
global $app_strings;
$temp_result[''] = $app_strings['LBL_NONE'];
} else {
$temp_result[''] = '';
}
}
// Get the id and the name.
while ($row = $db->fetchByAssoc($result)) {
$temp_result[$row['id']] = $row['display'];
}
$user_array = $temp_result;
set_register_value('select_array', $key, $temp_result);
}
return $user_array;
}
/**
* uses a different query to return a list of users than get_user_array()
* @param args string where clause entry
* @return array Array of Users' details that match passed criteria
*/
function getUserArrayFromFullName($args, $hide_portal_users = false)
{
global $locale;
$db = DBManagerFactory::getInstance();
$argArray = array();
if (strpos($args, " ")) {
$argArray = explode(" ", $args);
} else {
$argArray[] = $args;
}
$inClause = '';
foreach ($argArray as $arg) {
if (!empty($inClause)) {
$inClause .= ' OR ';
}
if (empty($arg)) {
continue;
}
$inClause .= "(first_name LIKE '{$arg}%' OR last_name LIKE '{$arg}%')";
}
$query = "SELECT id, first_name, last_name, user_name FROM users WHERE status='Active' AND deleted=0 AND ";
if ($hide_portal_users) {
$query .= " portal_only=0 AND ";
}
$query .= $inClause;
/* BEGIN - SECURITY GROUPS */
global $current_user, $sugar_config;
if (!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);
$query .= " AND (" . $group_where . ") ";
}
/* END - SECURITY GROUPS */
$query .= " ORDER BY last_name ASC";
$r = $db->query($query);
$ret = array();
while ($a = $db->fetchByAssoc($r)) {
$ret[$a['id']] = $locale->getLocaleFormattedName($a['first_name'], $a['last_name']);
}
return $ret;
}