public function testdb_concat()
{
error_reporting(E_ERROR | E_PARSE);
//execute the method and test if it returns expected values
$table = 'Table1';
$fields = array('Col1', 'Col2', 'Col3');
$expected = "LTRIM(RTRIM(CONCAT(IFNULL(Table1.Col1,''),'',IFNULL(Table1.Col2,''),'',IFNULL(Table1.Col3,''))))";
$actual = db_concat($table, $fields);
$this->assertSame($expected, $actual);
}
/**
* Internal function to construct where clauses
*/
function constructWhere(&$query_obj, $focus)
{
$table = $focus->getTableName();
if (!empty($table)) {
$table .= ".";
}
$cond_arr = array();
if (!is_array($query_obj['conditions'])) {
$query_obj['conditions'] = array();
}
foreach ($query_obj['conditions'] as $condition) {
if ($condition['op'] == 'contains') {
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '%" . $GLOBALS['db']->quote($condition['value']) . "%'");
} else {
if ($condition['op'] == 'like_custom') {
$like = '';
if (!empty($condition['begin'])) {
$like .= $GLOBALS['db']->quote($condition['begin']);
}
$like .= $GLOBALS['db']->quote($condition['value']);
if (!empty($condition['end'])) {
$like .= $GLOBALS['db']->quote($condition['end']);
}
if ($focus instanceof Person) {
$nameFormat = $GLOBALS['locale']->getLocaleFormatMacro($GLOBALS['current_user']);
if (strpos($nameFormat, 'l') > strpos($nameFormat, 'f')) {
array_push($cond_arr, db_concat(rtrim($table, '.'), array('first_name', 'last_name')) . " like '{$like}'");
} else {
array_push($cond_arr, db_concat(rtrim($table, '.'), array('last_name', 'first_name')) . " like '{$like}'");
}
} else {
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '{$like}'");
}
} else {
// starts_with
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '" . $GLOBALS['db']->quote($condition['value']) . "%'");
}
}
}
$whereClause = '(' . implode(" {$query_obj['group']} ", $cond_arr) . ')';
if ($table == 'users.') {
$whereClause .= " AND {$table}status='Active'";
}
// Need to include the default whereStatement
if (!empty($query_obj['whereExtra'])) {
if (!empty($whereClause)) {
$whereClause .= ' AND ';
}
$whereClause .= html_entity_decode($query_obj['whereExtra'], ENT_QUOTES);
}
return $whereClause;
}
/**
* Internal function to construct where clauses
*/
function constructWhere(&$query_obj, $focus)
{
$table = $focus->getTableName();
if (!empty($table)) {
$table .= ".";
}
$cond_arr = array();
if (!is_array($query_obj['conditions'])) {
$query_obj['conditions'] = array();
}
foreach ($query_obj['conditions'] as $condition) {
if ($condition['op'] == 'contains') {
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '%" . $GLOBALS['db']->quote($condition['value']) . "%'");
} else {
if ($condition['op'] == 'like_custom') {
$like = '';
if (!empty($condition['begin'])) {
$like .= $GLOBALS['db']->quote($condition['begin']);
}
$like .= $GLOBALS['db']->quote($condition['value']);
if (!empty($condition['end'])) {
$like .= $GLOBALS['db']->quote($condition['end']);
}
if ($focus instanceof Person) {
if ($condition['name'] == 'name') {
array_push($cond_arr, db_concat(rtrim($table, '.'), array('first_name')) . " like '{$like}'");
array_push($cond_arr, db_concat(rtrim($table, '.'), array('last_name')) . " like '{$like}'");
} else {
array_push($cond_arr, db_concat(rtrim($table, '.'), array($condition['name'])) . " like '{$like}'");
}
} else {
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '{$like}'");
}
} else {
// starts_with
array_push($cond_arr, $GLOBALS['db']->quote($table . $condition['name']) . " like '" . $GLOBALS['db']->quote($condition['value']) . "%'");
}
}
}
if ($table == 'users.') {
array_push($cond_arr, $table . "status='Active'");
}
return implode(" {$query_obj['group']} ", $cond_arr);
}
function getRelateJoin($field_def, $joinTableAlias, $withIdName = true)
{
if (empty($field_def['type']) || $field_def['type'] != "relate") {
return false;
}
global $beanFiles, $beanList, $module;
$rel_module = $field_def['module'];
if (empty($beanFiles[$beanList[$rel_module]])) {
return false;
}
require_once $beanFiles[$beanList[$rel_module]];
$rel_mod = new $beanList[$rel_module]();
$rel_table = $rel_mod->table_name;
if (isset($rel_mod->field_defs['name'])) {
$name_field_def = $rel_mod->field_defs['name'];
if (isset($name_field_def['db_concat_fields'])) {
$name_field = db_concat($joinTableAlias, $name_field_def['db_concat_fields']);
} else {
if (!empty($rel_mod->field_defs['name']['source']) && $rel_mod->field_defs['name']['source'] == "non-db" && !empty($field_def['rname'])) {
$name_field = "{$joinTableAlias}." . $field_def['rname'];
} else {
$name_field = "{$joinTableAlias}.name";
}
}
}
$tableName = isset($field_def['custom_module']) ? "{$this->bean->table_name}_cstm" : $this->bean->table_name;
$relID = $field_def['id_name'];
$ret_array['rel_table'] = $rel_table;
$ret_array['name_field'] = $name_field;
$ret_array['select'] = ($withIdName ? ", {$tableName}.{$relID}" : "") . ", {$name_field} {$field_def['name']} ";
$ret_array['from'] = " LEFT JOIN {$rel_table} {$joinTableAlias} ON {$tableName}.{$relID} = {$joinTableAlias}.id" . " AND {$joinTableAlias}.deleted=0 ";
return $ret_array;
}
function address_popup_create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
{
//if this is any action that is not the contact address popup, then go to parent function in sugarbean
if (isset($_REQUEST['action']) && $_REQUEST['action'] !== 'ContactAddressPopup') {
return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
}
$custom_join = $this->custom_fields->getJOIN();
// MFH - BUG #14208 creates alias name for select
$select_query = "SELECT ";
$select_query .= db_concat($this->table_name, array('first_name', 'last_name')) . " name, ";
$select_query .= "\n\t\t\t\t{$this->table_name}.*,\n accounts.name as account_name,\n accounts.id as account_id,\n accounts.assigned_user_id account_id_owner,\n users.user_name as assigned_user_name ";
if ($custom_join) {
$select_query .= $custom_join['select'];
}
$ret_array['select'] = $select_query;
$from_query = "\n FROM contacts ";
$from_query .= "LEFT JOIN users\n\t ON contacts.assigned_user_id=users.id\n\t LEFT JOIN accounts_contacts\n\t ON contacts.id=accounts_contacts.contact_id and accounts_contacts.deleted = 0\n\t LEFT JOIN accounts\n\t ON accounts_contacts.account_id=accounts.id AND accounts.deleted=0 ";
$from_query .= "LEFT JOIN email_addr_bean_rel eabl ON eabl.bean_id = contacts.id AND eabl.bean_module = 'Contacts' and eabl.primary_address = 1 and eabl.deleted=0 ";
$from_query .= "LEFT JOIN email_addresses ea ON (ea.id = eabl.email_address_id) ";
if ($custom_join) {
$from_query .= $custom_join['join'];
}
$ret_array['from'] = $from_query;
$ret_array['from_min'] = 'from contacts';
$where_auto = '1=1';
if ($show_deleted == 0) {
$where_auto = " {$this->table_name}.deleted=0 ";
//$where_auto .= " AND accounts.deleted=0 ";
} else {
if ($show_deleted == 1) {
$where_auto = " {$this->table_name}.deleted=1 ";
}
}
if ($where != "") {
$where_query = "where ({$where}) AND " . $where_auto;
} else {
$where_query = "where " . $where_auto;
}
$ret_array['where'] = $where_query;
$orderby_query = '';
if (!empty($order_by)) {
$orderby_query = " ORDER BY " . $this->process_order_by($order_by, null);
}
$ret_array['order_by'] = $orderby_query;
if ($return_array) {
return $ret_array;
}
return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by'];
}
/**
* @brief Generate users admin page and handle single item operations.
*
* This function generates the users/account admin page and handles the actions
* if an icon next to an entry was clicked. If several items were selected and
* the form was submitted it is handled by the function admin_page_users_post().
*
* @param App &$a
* @return string
*/
function admin_page_users(&$a)
{
if (argc() > 2) {
$uid = argv(3);
$account = q("SELECT * FROM account WHERE account_id = %d", intval($uid));
if (!$account) {
notice(t('Account not found') . EOL);
goaway(z_root() . '/admin/users');
}
check_form_security_token_redirectOnErr('/admin/users', 'admin_users', 't');
switch (argv(2)) {
case 'delete':
// delete user
require_once 'include/Contact.php';
account_remove($uid, true, false);
notice(sprintf(t("Account '%s' deleted"), $account[0]['account_email']) . EOL);
break;
case 'block':
q("UPDATE account SET account_flags = ( account_flags | %d ) WHERE account_id = %d", intval(ACCOUNT_BLOCKED), intval($uid));
notice(sprintf(t("Account '%s' blocked"), $account[0]['account_email']) . EOL);
break;
case 'unblock':
q("UPDATE account SET account_flags = ( account_flags & ~%d ) WHERE account_id = %d", intval(ACCOUNT_BLOCKED), intval($uid));
notice(sprintf(t("Account '%s' unblocked"), $account[0]['account_email']) . EOL);
break;
}
goaway(z_root() . '/admin/users');
}
/* get pending */
$pending = q("SELECT account.*, register.hash from account left join register on account_id = register.uid where (account_flags & %d )>0 ", intval(ACCOUNT_PENDING));
/* get users */
$total = q("SELECT count(*) as total FROM account");
if (count($total)) {
\App::set_pager_total($total[0]['total']);
\App::set_pager_itemspage(100);
}
// We'll still need to link email addresses to admin/users/channels or some such, but this bit doesn't exist yet.
// That's where we need to be doing last post/channel flags/etc, not here.
$serviceclass = $_REQUEST['class'] ? " and account_service_class = '" . dbesc($_REQUEST['class']) . "' " : '';
$order = " order by account_email asc ";
if ($_REQUEST['order'] === 'expires') {
$order = " order by account_expires desc ";
}
if ($_REQUEST['order'] === 'created') {
$order = " order by account_created desc ";
}
$users = q("SELECT `account_id` , `account_email`, `account_lastlog`, `account_created`, `account_expires`, " . "`account_service_class`, ( account_flags & %d )>0 as `blocked`, " . "(SELECT %s FROM channel as ch " . "WHERE ch.channel_account_id = ac.account_id and ch.channel_removed = 0 ) as `channels` " . "FROM account as ac where true {$serviceclass} {$order} limit %d offset %d ", intval(ACCOUNT_BLOCKED), db_concat('ch.channel_address', ' '), intval(\App::$pager['itemspage']), intval(\App::$pager['start']));
// function _setup_users($e){
// $accounts = Array(
// t('Normal Account'),
// t('Soapbox Account'),
// t('Community/Celebrity Account'),
// t('Automatic Friend Account')
// );
// $e['page_flags'] = $accounts[$e['page-flags']];
// $e['register_date'] = relative_date($e['register_date']);
// $e['login_date'] = relative_date($e['login_date']);
// $e['lastitem_date'] = relative_date($e['lastitem_date']);
// return $e;
// }
// $users = array_map("_setup_users", $users);
$t = get_markup_template('admin_users.tpl');
$o = replace_macros($t, array('$title' => t('Administration'), '$page' => t('Users'), '$submit' => t('Submit'), '$select_all' => t('select all'), '$h_pending' => t('User registrations waiting for confirm'), '$th_pending' => array(t('Request date'), t('Email')), '$no_pending' => t('No registrations.'), '$approve' => t('Approve'), '$deny' => t('Deny'), '$delete' => t('Delete'), '$block' => t('Block'), '$unblock' => t('Unblock'), '$h_users' => t('Users'), '$th_users' => array(t('ID'), t('Email'), t('All Channels'), t('Register date'), t('Last login'), t('Expires'), t('Service Class')), '$confirm_delete_multi' => t('Selected accounts will be deleted!\\n\\nEverything these accounts had posted on this site will be permanently deleted!\\n\\nAre you sure?'), '$confirm_delete' => t('The account {0} will be deleted!\\n\\nEverything this account has posted on this site will be permanently deleted!\\n\\nAre you sure?'), '$form_security_token' => get_form_security_token("admin_users"), '$baseurl' => z_root(), '$pending' => $pending, '$users' => $users));
$o .= paginate($a);
return $o;
}
function generateSearchWhere($add_custom_fields = false, $module = '')
{
global $timedate;
$this->searchColumns = array();
$values = $this->searchFields;
$where_clauses = array();
$like_char = '%';
$table_name = $this->seed->object_name;
$this->seed->fill_in_additional_detail_fields();
//rrs check for team_id
foreach ($this->searchFields as $field => $parms) {
$customField = false;
// Jenny - Bug 7462: We need a type check here to avoid database errors
// when searching for numeric fields. This is a temporary fix until we have
// a generic search form validation mechanism.
$type = !empty($this->seed->field_name_map[$field]['type']) ? $this->seed->field_name_map[$field]['type'] : '';
if (!empty($this->seed->field_name_map[$field]['source']) && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' || $this->seed->field_name_map[$field]['source'] == 'non-db' && (!empty($this->seed->field_name_map[$field]['custom_module']) || isset($this->seed->field_name_map[$field]['ext2'])))) {
$customField = true;
}
if ($type == 'int') {
if (!empty($parms['value'])) {
$tempVal = explode(',', $parms['value']);
$newVal = '';
foreach ($tempVal as $key => $val) {
if (!empty($newVal)) {
$newVal .= ',';
}
if (!empty($val) && !is_numeric($val)) {
$newVal .= -1;
} else {
$newVal .= $val;
}
}
$parms['value'] = $newVal;
}
} elseif ($type == 'html' && $customField) {
continue;
}
if (isset($parms['value']) && $parms['value'] != "") {
$operator = 'like';
if (!empty($parms['operator'])) {
$operator = $parms['operator'];
}
if (is_array($parms['value'])) {
$field_value = '';
// always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
if (!empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
// construct the query for multenums
// use the 'like' query for all mssql and oracle examples as both custom and OOB multienums are implemented with types that cannot be used with an 'in'
$operator = 'custom_enum';
$table_name = $this->seed->table_name;
if ($customField) {
$table_name .= "_cstm";
}
$db_field = $table_name . "." . $field;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
$qVal = $GLOBALS['db']->quote($val);
if (!empty($field_value)) {
$field_value .= ' or ';
}
$field_value .= "{$db_field} like '%^{$qVal}^%'";
}
}
} else {
$operator = $operator != 'subquery' ? 'in' : $operator;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
}
}
}
} else {
$field_value = $GLOBALS['db']->quote($parms['value']);
}
//set db_fields array.
if (!isset($parms['db_field'])) {
$parms['db_field'] = array($field);
}
if (isset($parms['my_items']) and $parms['my_items'] == true) {
if ($parms['value'] == false) {
//do not include where clause for custom fields with checkboxes that are unchecked
continue;
} else {
//my items is checked.
global $current_user;
$field_value = $GLOBALS['db']->quote($current_user->id);
$operator = '=';
}
// $operator = ($parms['value'] == '1') ? '=' : '!=';
}
$where = '';
$itr = 0;
if ($field_value != '') {
$this->searchColumns[strtoupper($field)] = $field;
foreach ($parms['db_field'] as $db_field) {
if (strstr($db_field, '.') === false) {
//Try to get the table for relate fields from link defs
if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link']) && !empty($this->seed->field_name_map[$field]['rname'])) {
$link = $this->seed->field_name_map[$field]['link'];
$relname = $link['relationship'];
if ($this->seed->load_relationship($link)) {
//Martin fix #27494
$db_field = $this->seed->field_name_map[$field]['name'];
} else {
//Best Guess for table name
$db_field = strtolower($link['module']) . '.' . $db_field;
}
} else {
if ($type == 'parent') {
if (!empty($this->searchFields['parent_type'])) {
$parentType = $this->searchFields['parent_type'];
$rel_module = $parentType['value'];
global $beanFiles, $beanList;
if (!empty($beanFiles[$beanList[$rel_module]])) {
require_once $beanFiles[$beanList[$rel_module]];
$rel_seed = new $beanList[$rel_module]();
$db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
}
}
} else {
if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
$db_field = !empty($this->seed->field_name_map[$field]['name']) ? $this->seed->field_name_map[$field]['name'] : 'name';
} else {
if (!$customField) {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "." . $db_field;
}
} else {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "_cstm." . $db_field;
}
}
}
}
}
}
if ($type == 'date') {
// Collin - Have mysql as first because it's usually the case
// The regular expression check is to circumvent special case YYYY-MM
if ($GLOBALS['db']->dbType == 'mysql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
} else {
$operator = 'db_date';
}
} else {
if ($GLOBALS['db']->dbType == 'mssql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = "Convert(DateTime, '" . $timedate->to_db_date($field_value, false) . "')";
}
$operator = 'db_date';
} else {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
}
}
}
if ($type == 'datetime' || $type == 'datetimecombo') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["start"] . "<>" . $dates["end"];
$operator = 'between';
}
if ($type == 'decimal' || $type == 'float' || $type == 'currency') {
require_once 'modules/Currencies/Currency.php';
$field_value = unformat_number($field_value);
if ($type == 'currency' && stripos($field, '_usdollar') !== FALSE) {
// It's a US Dollar field, we need to do some conversions from the user's local currency
$currency_id = $GLOBALS['current_user']->getPreference('currency');
if (empty($currency_id)) {
$currency_id = -99;
}
if ($currency_id != -99) {
$currency = new Currency();
$currency->retrieve($currency_id);
$field_value = $currency->convertToDollar($field_value);
}
}
// Databases can't really search for floating point numbers, because they can't be accurately described in binary,
// So we have to fuzz out the match a little bit
$top = $field_value + 0.01;
$bottom = $field_value - 0.01;
$field_value = $bottom . "<>" . $top;
$operator = 'between';
}
$itr++;
if (!empty($where)) {
$where .= " OR ";
}
switch (strtolower($operator)) {
case 'subquery':
$in = 'IN';
if (isset($parms['subquery_in_clause'])) {
if (!is_array($parms['subquery_in_clause'])) {
$in = $parms['subquery_in_clause'];
} elseif (isset($parms['subquery_in_clause'][$field_value])) {
$in = $parms['subquery_in_clause'][$field_value];
}
}
$sq = $parms['subquery'];
if (is_array($sq)) {
$and_or = ' AND ';
if (isset($sq['OR'])) {
$and_or = ' OR ';
}
$first = true;
foreach ($sq as $q) {
if (empty($q) || strlen($q) < 2) {
continue;
}
if (!$first) {
$where .= $and_or;
}
$where .= " {$db_field} {$in} ({$q} '{$field_value}%') ";
$first = false;
}
} elseif (!empty($parms['query_type']) && $parms['query_type'] == 'format') {
$stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
$where .= "{$db_field} {$in} (" . string_format($parms['subquery'], $stringFormatParams) . ")";
} else {
$where .= "{$db_field} {$in} ({$parms['subquery']} '{$field_value}%')";
}
break;
case 'like':
if ($type == 'bool' && $field_value == 0) {
$where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
} else {
//check to see if this is coming from unified search or not
$UnifiedSearch = !empty($parms['force_unifiedsearch']);
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch') {
$UnifiedSearch = true;
}
//check to see if this is a universal search, AND the field name is "last_name"
if ($UnifiedSearch && strpos($db_field, 'last_name') !== false) {
//split the string value, and the db field name
$string = explode(' ', $field_value);
$column_name = explode('.', $db_field);
//when a search is done with a space, we concatenate and search against the full name.
if (count($string) > 1) {
//add where clause agains concatenated fields
$where .= $GLOBALS['db']->concat($column_name[0], array('first_name', 'last_name')) . " LIKE '{$field_value}%'";
$where .= ' OR ' . $GLOBALS['db']->concat($column_name[0], array('last_name', 'first_name')) . " LIKE '{$field_value}%'";
} else {
//no space was found, add normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
} else {
//field is not last name or this is not from global unified search, so do normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
}
break;
case 'in':
$where .= $db_field . " in (" . $field_value . ')';
break;
case '=':
if ($type == 'bool' && $field_value == 0) {
$where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
} else {
$where .= $db_field . " = '" . $field_value . "'";
}
break;
case 'db_date':
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$where .= $db_field . " = " . $field_value;
} else {
// Create correct date_format conversion String
if ($GLOBALS['db']->dbType == 'oci8') {
$where .= db_convert($db_field, 'date_format', array("'YYYY-MM'")) . " = '" . $field_value . "'";
} else {
$where .= db_convert($db_field, 'date_format', array("'%Y-%m'")) . " = '" . $field_value . "'";
}
}
break;
// tyoung bug 15971 - need to add these special cases into the $where query
// tyoung bug 15971 - need to add these special cases into the $where query
case 'custom_enum':
$where .= $field_value;
break;
case 'between':
$field_value = explode('<>', $field_value);
$where .= $db_field . " >= '" . $field_value[0] . "' AND " . $db_field . " <= '" . $field_value[1] . "'";
break;
case 'innerjoin':
$this->seed->listview_inner_join[] = $parms['innerjoin'] . " '" . $parms['value'] . "%')";
break;
}
}
}
if (!empty($where)) {
if ($itr > 1) {
array_push($where_clauses, '( ' . $where . ' )');
} else {
array_push($where_clauses, $where);
}
}
}
}
return $where_clauses;
}
function generateSearchWhere($add_custom_fields = false, $module = '')
{
global $timedate;
$this->searchColumns = array();
$values = $this->searchFields;
$where_clauses = array();
$like_char = '%';
$table_name = $this->seed->object_name;
$this->seed->fill_in_additional_detail_fields();
//rrs check for team_id
foreach ($this->searchFields as $field => $parms) {
$customField = false;
// Jenny - Bug 7462: We need a type check here to avoid database errors
// when searching for numeric fields. This is a temporary fix until we have
// a generic search form validation mechanism.
$type = !empty($this->seed->field_name_map[$field]['type']) ? $this->seed->field_name_map[$field]['type'] : '';
if (!empty($parms['enable_range_search']) && empty($type)) {
if (preg_match('/^start_range_(.*?)$/', $field, $match)) {
$real_field = $match[1];
$start_field = 'start_range_' . $real_field;
$end_field = 'end_range_' . $real_field;
if (isset($this->searchFields[$start_field]['value']) && isset($this->searchFields[$end_field]['value'])) {
$this->searchFields[$real_field]['value'] = $this->searchFields[$start_field]['value'] . '<>' . $this->searchFields[$end_field]['value'];
$this->searchFields[$real_field]['operator'] = 'between';
$parms['value'] = $this->searchFields[$real_field]['value'];
$parms['operator'] = 'between';
$field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
if ($field_type == 'datetimecombo' || $field_type == 'datetime') {
$type = $field_type;
}
$field = $real_field;
unset($this->searchFields[$end_field]['value']);
}
} else {
if (preg_match('/^range_(.*?)$/', $field, $match) && isset($this->searchFields[$field]['value'])) {
$real_field = $match[1];
//Special case for datetime and datetimecombo fields. By setting the type here we allow an actual between search
if ($parms['operator'] == '=') {
$field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
if ($field_type == 'datetimecombo' || $field_type == 'datetime') {
$type = $field_type;
}
}
$this->searchFields[$real_field]['value'] = $this->searchFields[$field]['value'];
$this->searchFields[$real_field]['operator'] = $this->searchFields[$field]['operator'];
$params['value'] = $this->searchFields[$field]['value'];
$params['operator'] = $this->searchFields[$field]['operator'];
unset($this->searchFields[$field]['value']);
$field = $real_field;
} else {
//Skip this range search field, it is the end field THIS IS NEEDED or the end range date will break the query
continue;
}
}
}
if (!empty($this->seed->field_name_map[$field]['source']) && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' || $this->seed->field_name_map[$field]['source'] == 'non-db' && (!empty($this->seed->field_name_map[$field]['custom_module']) || isset($this->seed->field_name_map[$field]['ext2'])))) {
$customField = true;
}
if ($type == 'int') {
if (!empty($parms['value'])) {
$tempVal = explode(',', $parms['value']);
$newVal = '';
foreach ($tempVal as $key => $val) {
if (!empty($newVal)) {
$newVal .= ',';
}
if (!empty($val) && !is_numeric($val)) {
$newVal .= -1;
} else {
$newVal .= $val;
}
}
$parms['value'] = $newVal;
}
} elseif ($type == 'html' && $customField) {
continue;
}
if (isset($parms['value']) && $parms['value'] != "") {
$operator = 'like';
if (!empty($parms['operator'])) {
$operator = $parms['operator'];
}
if (is_array($parms['value'])) {
$field_value = '';
// always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
if (!empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
// construct the query for multenums
// use the 'like' query for all mssql and oracle examples as both custom and OOB multienums are implemented with types that cannot be used with an 'in'
$operator = 'custom_enum';
$table_name = $this->seed->table_name;
if ($customField) {
$table_name .= "_cstm";
}
$db_field = $table_name . "." . $field;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
$qVal = $GLOBALS['db']->quote($val);
if (!empty($field_value)) {
$field_value .= ' or ';
}
$field_value .= "{$db_field} like '%^{$qVal}^%'";
} else {
$field_value .= '(' . $db_field . ' IS NULL or ' . $db_field . "='^^' or " . $db_field . "='')";
}
}
} else {
$operator = $operator != 'subquery' ? 'in' : $operator;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
} else {
if ($operator == 'in') {
$operator = 'isnull';
}
}
}
}
} else {
$field_value = $GLOBALS['db']->quote($parms['value']);
}
//set db_fields array.
if (!isset($parms['db_field'])) {
$parms['db_field'] = array($field);
}
//This if-else block handles the shortcut checkbox selections for "My Items" and "Closed Only"
if (!empty($parms['my_items'])) {
if ($parms['value'] == false) {
continue;
} else {
//my items is checked.
global $current_user;
$field_value = $GLOBALS['db']->quote($current_user->id);
$operator = '=';
}
} else {
if (!empty($parms['closed_values']) && is_array($parms['closed_values'])) {
if ($parms['value'] == false) {
continue;
} else {
$field_value = '';
foreach ($parms['closed_values'] as $closed_value) {
$field_value .= ",'" . $GLOBALS['db']->quote($closed_value) . "'";
}
$field_value = substr($field_value, 1);
}
}
}
$where = '';
$itr = 0;
if ($field_value != '' || $operator == 'isnull') {
$this->searchColumns[strtoupper($field)] = $field;
foreach ($parms['db_field'] as $db_field) {
if (strstr($db_field, '.') === false) {
//Try to get the table for relate fields from link defs
if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link']) && !empty($this->seed->field_name_map[$field]['rname'])) {
$link = $this->seed->field_name_map[$field]['link'];
$relname = $link['relationship'];
if ($this->seed->load_relationship($link)) {
//Martin fix #27494
$db_field = $this->seed->field_name_map[$field]['name'];
} else {
//Best Guess for table name
$db_field = strtolower($link['module']) . '.' . $db_field;
}
} else {
if ($type == 'parent') {
if (!empty($this->searchFields['parent_type'])) {
$parentType = $this->searchFields['parent_type'];
$rel_module = $parentType['value'];
global $beanFiles, $beanList;
if (!empty($beanFiles[$beanList[$rel_module]])) {
require_once $beanFiles[$beanList[$rel_module]];
$rel_seed = new $beanList[$rel_module]();
$db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
}
}
} else {
if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
$db_field = !empty($this->seed->field_name_map[$field]['name']) ? $this->seed->field_name_map[$field]['name'] : 'name';
} else {
if (!$customField) {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "." . $db_field;
}
} else {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "_cstm." . $db_field;
}
}
}
}
}
}
if ($type == 'date') {
// Collin - Have mysql as first because it's usually the case
// The regular expression check is to circumvent special case YYYY-MM
if ($GLOBALS['db']->dbType == 'mysql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
} else {
$operator = 'db_date';
}
} else {
if ($GLOBALS['db']->dbType == 'mssql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = "Convert(DateTime, '" . $timedate->to_db_date($field_value, false) . "')";
}
$operator = 'db_date';
} else {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
}
}
}
if ($type == 'datetime' || $type == 'datetimecombo') {
try {
// FG - bug45287 - If User asked for a range, takes edges from it.
$placeholderPos = strpos($field_value, "<>");
if ($placeholderPos !== FALSE && $placeholderPos > 0) {
$datesLimit = explode("<>", $field_value);
$dateStart = $timedate->getDayStartEndGMT($datesLimit[0]);
$dateEnd = $timedate->getDayStartEndGMT($datesLimit[1]);
$dates = $dateStart;
$dates['end'] = $dateEnd['end'];
$dates['enddate'] = $dateEnd['enddate'];
$dates['endtime'] = $dateEnd['endtime'];
} else {
$dates = $timedate->getDayStartEndGMT($field_value);
}
// FG - bug45287 - Note "start" and "end" are the correct interval at GMT timezone
$field_value = $dates["start"] . "<>" . $dates["end"];
$operator = 'between';
} catch (Exception $timeException) {
//In the event that a date value is given that cannot be correctly processed by getDayStartEndGMT method,
//just skip searching on this field and continue. This may occur if user switches locale date formats
//in another browser screen, but re-runs a search with the previous format on another screen
$GLOBALS['log']->error($timeException->getMessage());
continue;
}
}
// adjust date searches to take account for user timezone
// 'equals' and 'is between' cases are handled above.
if ($type == '' && !empty($parms['enable_range_search']) && $parms['enable_range_search'] == true) {
// check if value is a db date or db datetime format
if (preg_match('/^(\\d{4}-\\d{2}-\\d{2})( \\d{2}:\\d{2}:\\d{2})?$/', $field_value)) {
if ($operator == 'not_equal') {
$adjDate = $timedate->getDayStartEndGMT($field_value);
$field_value = $adjDate['start'] . '<>' . $adjDate['end'];
$operator = 'date_not_equal';
} elseif ($operator == 'greater_than' || $operator == 'less_than_equals') {
$adjDate = $timedate->getDayStartEndGMT($field_value);
$field_value = $adjDate['end'];
} elseif ($operator == 'less_than' || $operator == 'greater_than_equals') {
$adjDate = $timedate->getDayStartEndGMT($field_value);
$field_value = $adjDate['start'];
}
// check if value is something like [last_month]|[next_7_days]|[this_year]|etc...
} elseif (preg_match('/^\\[[(this|last|next)_][_a-z0-9]*\\]$/', $field_value)) {
switch ($operator) {
case 'last_7_days':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() - 7 * 24 * 60 * 60));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
break;
case 'next_7_days':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() + 7 * 24 * 60 * 60));
break;
case 'next_month':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m") + 1, 01, date("Y"))));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m") + 2, 01, date("Y"))));
break;
case 'last_month':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m") - 1, 01, date("Y"))));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m"), 01, date("Y"))));
break;
case 'this_month':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m"), 01, date("Y"))));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m") + 1, 01, date("Y"))));
break;
case 'last_30_days':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() - 30 * 24 * 60 * 60));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
break;
case 'next_30_days':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() + 30 * 24 * 60 * 60));
break;
case 'this_year':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01, date("Y"))));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31, date("Y"))));
break;
case 'last_year':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01, date("Y") - 1)));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31, date("Y") - 1)));
break;
case 'next_year':
$startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01, date("Y") + 1)));
$endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31, date("Y") + 1)));
break;
}
$field_value = $startDate['start'] . "<>" . $endDate['end'];
$operator = 'between';
}
}
if ($type == 'decimal' || $type == 'float' || $type == 'currency' || !empty($parms['enable_range_search']) && empty($parms['is_date_field'])) {
require_once 'modules/Currencies/Currency.php';
//we need to handle formatting either a single value or 2 values in case the 'between' search option is set
//start by splitting the string if the between operator exists
$fieldARR = explode('<>', $field_value);
//set the first pass through boolean
$first_between = true;
foreach ($fieldARR as $fk => $fv) {
//reset the field value, it will be rebuild in the foreach loop below
$tmpfield_value = unformat_number($fv);
if ($type == 'currency' && stripos($field, '_usdollar') !== FALSE) {
// It's a US Dollar field, we need to do some conversions from the user's local currency
$currency_id = $GLOBALS['current_user']->getPreference('currency');
if (empty($currency_id)) {
$currency_id = -99;
}
if ($currency_id != -99) {
$currency = new Currency();
$currency->retrieve($currency_id);
$field_value = $currency->convertToDollar($tmpfield_value);
}
}
//recreate the field value
if ($first_between) {
//set the field value with the new formatted temp value
$field_value = $tmpfield_value;
} else {
//this is a between query, so append the between operator and add the second formatted temp value
$field_value .= '<>' . $tmpfield_value;
}
//set the first pass through variable to false
$first_between = false;
}
if (!empty($parms['enable_range_search']) && $parms['operator'] == '=') {
// Databases can't really search for floating point numbers, because they can't be accurately described in binary,
// So we have to fuzz out the math a little bit
$field_value = $field_value - 0.01 . "<>" . ($field_value + 0.01);
$operator = 'between';
}
}
$itr++;
if (!empty($where)) {
$where .= " OR ";
}
switch (strtolower($operator)) {
case 'subquery':
$in = 'IN';
if (isset($parms['subquery_in_clause'])) {
if (!is_array($parms['subquery_in_clause'])) {
$in = $parms['subquery_in_clause'];
} elseif (isset($parms['subquery_in_clause'][$field_value])) {
$in = $parms['subquery_in_clause'][$field_value];
}
}
$sq = $parms['subquery'];
if (is_array($sq)) {
$and_or = ' AND ';
if (isset($sq['OR'])) {
$and_or = ' OR ';
}
$first = true;
foreach ($sq as $q) {
if (empty($q) || strlen($q) < 2) {
continue;
}
if (!$first) {
$where .= $and_or;
}
$where .= " {$db_field} {$in} ({$q} '{$field_value}%') ";
$first = false;
}
} elseif (!empty($parms['query_type']) && $parms['query_type'] == 'format') {
$stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
$where .= "{$db_field} {$in} (" . string_format($parms['subquery'], $stringFormatParams) . ")";
} else {
$where .= "{$db_field} {$in} ({$parms['subquery']} '{$field_value}%')";
}
break;
case 'like':
if ($type == 'bool' && $field_value == 0) {
// Bug 43452 - FG - Added parenthesis surrounding the OR (without them the WHERE clause would be broken)
$where .= "( " . $db_field . " = '0' OR " . $db_field . " IS NULL )";
} else {
//check to see if this is coming from unified search or not
$UnifiedSearch = !empty($parms['force_unifiedsearch']);
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch') {
$UnifiedSearch = true;
}
//check to see if this is a universal search OR the field has db_concat_fields set in vardefs, AND the field name is "last_name"
if (($UnifiedSearch || !empty($this->seed->field_name_map[$field]['db_concat_fields'])) && strpos($db_field, 'last_name') !== false) {
//split the string value, and the db field name
$string = explode(' ', $field_value);
$column_name = explode('.', $db_field);
//when a search is done with a space, we concatenate and search against the full name.
if (count($string) > 1) {
//add where clause against concatenated fields
$where .= $GLOBALS['db']->concat($column_name[0], array('first_name', 'last_name')) . " LIKE '{$field_value}%'";
$where .= ' OR ' . $GLOBALS['db']->concat($column_name[0], array('last_name', 'first_name')) . " LIKE '{$field_value}%'";
} else {
//no space was found, add normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
} else {
//Check if this is a first_name, last_name search
if (isset($this->seed->field_name_map) && isset($this->seed->field_name_map[$db_field])) {
$vardefEntry = $this->seed->field_name_map[$db_field];
if (!empty($vardefEntry['db_concat_fields']) && in_array('first_name', $vardefEntry['db_concat_fields']) && in_array('last_name', $vardefEntry['db_concat_fields'])) {
if (!empty($GLOBALS['app_list_strings']['salutation_dom']) && is_array($GLOBALS['app_list_strings']['salutation_dom'])) {
foreach ($GLOBALS['app_list_strings']['salutation_dom'] as $salutation) {
if (!empty($salutation) && strpos($field_value, $salutation) == 0) {
$field_value = trim(substr($field_value, strlen($salutation)));
break;
}
}
}
}
}
//field is not last name or this is not from global unified search, so do normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
}
break;
case 'not in':
$where .= $db_field . ' not in (' . $field_value . ')';
break;
case 'in':
$where .= $db_field . ' in (' . $field_value . ')';
break;
case '=':
if ($type == 'bool' && $field_value == 0) {
$where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
} else {
$where .= $db_field . " = '" . $field_value . "'";
}
break;
case 'db_date':
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$where .= $db_field . " = " . $field_value;
} else {
// Create correct date_format conversion String
if ($GLOBALS['db']->dbType == 'oci8') {
$where .= db_convert($db_field, 'date_format', array("'YYYY-MM'")) . " = '" . $field_value . "'";
} else {
$where .= db_convert($db_field, 'date_format', array("'%Y-%m'")) . " = '" . $field_value . "'";
}
}
break;
// tyoung bug 15971 - need to add these special cases into the $where query
// tyoung bug 15971 - need to add these special cases into the $where query
case 'custom_enum':
$where .= $field_value;
break;
case 'between':
$field_value = explode('<>', $field_value);
$where .= $db_field . " >= '" . $field_value[0] . "' AND " . $db_field . " <= '" . $field_value[1] . "'";
break;
case 'date_not_equal':
$field_value = explode('<>', $field_value);
$where .= $db_field . " < '" . $field_value[0] . "' OR " . $db_field . " > '" . $field_value[1] . "'";
break;
case 'innerjoin':
$this->seed->listview_inner_join[] = $parms['innerjoin'] . " '" . $parms['value'] . "%')";
break;
case 'not_equal':
$where .= $db_field . " != '" . $field_value . "'";
break;
case 'greater_than':
$where .= $db_field . " > '" . $field_value . "'";
break;
case 'greater_than_equals':
$where .= $db_field . " >= '" . $field_value . "'";
break;
case 'less_than':
$where .= $db_field . " < '" . $field_value . "'";
break;
case 'less_than_equals':
$where .= $db_field . " <= '" . $field_value . "'";
break;
case 'isnull':
// OOTB fields are NULL, custom fields are blank
$where .= '(' . $db_field . ' IS NULL or ' . $db_field . "='')";
if ($field_value != '') {
$where .= ' OR ' . $db_field . " in (" . $field_value . ')';
}
break;
}
}
}
if (!empty($where)) {
if ($itr > 1) {
array_push($where_clauses, '( ' . $where . ' )');
} else {
array_push($where_clauses, $where);
}
}
}
}
return $where_clauses;
}
/**
* Validate relate fields
*
* @param $value string
* @param $vardef array
* @param $focus object bean of the module we're importing into
* @param $addRelatedBean bool true if we want to add the related bean if it is not found
* @return string sanitized and validated value on success, bool false on failure
*/
public function relate($value, $vardef, &$focus, $addRelatedBean = true)
{
if (!isset($vardef['module'])) {
return false;
}
$newbean = loadBean($vardef['module']);
// Bug 38885 - If we are relating to the Users table on user_name, there's a good chance
// that the related field data is the full_name, rather than the user_name. So to be sure
// let's try to lookup the field the relationship is expecting to use (user_name).
if ($vardef['module'] == 'Users' && $vardef['rname'] == 'user_name') {
$userFocus = new User();
$userFocus->retrieve_by_string_fields(array($userFocus->db->concat('users', array('first_name', 'last_name')) => $value));
if (!empty($userFocus->id)) {
$value = $userFocus->user_name;
}
}
// Bug 32869 - Assumed related field name is 'name' if it is not specified
if (!isset($vardef['rname'])) {
$vardef['rname'] = 'name';
}
// Bug 27046 - Validate field against type as it is in the related field
$rvardef = $newbean->getFieldDefinition($vardef['rname']);
if (isset($rvardef['type']) && method_exists($this, $rvardef['type'])) {
$fieldtype = $rvardef['type'];
$returnValue = $this->{$fieldtype}($value, $rvardef, $focus, $addRelatedBean);
if (!$returnValue) {
return false;
} else {
$value = $returnValue;
}
}
if (isset($vardef['id_name'])) {
$idField = $vardef['id_name'];
// Bug 24075 - clear out id field value if it is invalid
if (isset($focus->{$idField})) {
$checkfocus = loadBean($vardef['module']);
if ($checkfocus && is_null($checkfocus->retrieve($focus->{$idField}))) {
$focus->{$idField} = '';
}
}
// Bug 38356 - Populate the table entry in the vardef from the bean information in case it's not provided
if (!isset($vardef['table'])) {
// Set target module table as the default table name
$tmpfocus = loadBean($vardef['module']);
$vardef['table'] = $tmpfocus->table_name;
}
// be sure that the id isn't already set for this row
if (empty($focus->{$idField}) && $idField != $vardef['name'] && !empty($vardef['rname']) && !empty($vardef['table'])) {
// Bug 27562 - Check db_concat_fields first to see if the field name is a concat
$relatedFieldDef = $newbean->getFieldDefinition($vardef['rname']);
if (isset($relatedFieldDef['db_concat_fields']) && is_array($relatedFieldDef['db_concat_fields'])) {
$fieldname = db_concat($vardef['table'], $relatedFieldDef['db_concat_fields']);
} else {
$fieldname = $vardef['rname'];
}
// lookup first record that matches in linked table
$query = "SELECT id \n FROM {$vardef['table']} \n WHERE {$fieldname} = '" . $focus->db->quote($value) . "'\n AND deleted != 1";
$result = $focus->db->limitQuery($query, 0, 1, true, "Want only a single row");
if (!empty($result)) {
if ($relaterow = $focus->db->fetchByAssoc($result)) {
$focus->{$idField} = $relaterow['id'];
} elseif (!$addRelatedBean || $newbean->bean_implements('ACL') && !$newbean->ACLAccess('save') || in_array($newbean->module_dir, array('Teams', 'Users'))) {
return false;
} else {
// add this as a new record in that bean, then relate
if (isset($relatedFieldDef['db_concat_fields']) && is_array($relatedFieldDef['db_concat_fields'])) {
$relatedFieldParts = explode(' ', $value);
foreach ($relatedFieldDef['db_concat_fields'] as $relatedField) {
$newbean->{$relatedField} = array_shift($relatedFieldParts);
}
} else {
$newbean->{$vardef}['rname'] = $value;
}
if (!isset($focus->assigned_user_id) || $focus->assigned_user_id == '') {
$newbean->assigned_user_id = $GLOBALS['current_user']->id;
} else {
$newbean->assigned_user_id = $focus->assigned_user_id;
}
if (!isset($focus->modified_user_id) || $focus->modified_user_id == '') {
$newbean->modified_user_id = $GLOBALS['current_user']->id;
} else {
$newbean->modified_user_id = $focus->modified_user_id;
}
// populate fields from the parent bean to the child bean
$focus->populateRelatedBean($newbean);
$newbean->save(false);
$focus->{$idField} = $newbean->id;
$this->createdBeans[] = ImportFile::writeRowToLastImport($focus->module_dir, $newbean->object_name, $newbean->id);
}
}
}
}
return $value;
}
function generateSearchWhere($add_custom_fields = false, $module = '')
{
global $timedate;
$this->searchColumns = array();
$values = $this->searchFields;
$where_clauses = array();
$like_char = '%';
$table_name = $this->seed->object_name;
$this->seed->fill_in_additional_detail_fields();
//rrs check for team_id
foreach ($this->searchFields as $field => $parms) {
$customField = false;
// Jenny - Bug 7462: We need a type check here to avoid database errors
// when searching for numeric fields. This is a temporary fix until we have
// a generic search form validation mechanism.
$type = !empty($this->seed->field_name_map[$field]['type']) ? $this->seed->field_name_map[$field]['type'] : '';
if (!empty($this->seed->field_name_map[$field]['source']) && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' || $this->seed->field_name_map[$field]['source'] == 'non-db' && (!empty($this->seed->field_name_map[$field]['custom_module']) || isset($this->seed->field_name_map[$field]['ext2'])))) {
$customField = true;
}
if ($type == 'int') {
if (!empty($parms['value'])) {
$tempVal = explode(',', $parms['value']);
$newVal = '';
foreach ($tempVal as $key => $val) {
if (!empty($newVal)) {
$newVal .= ',';
}
if (!empty($val) && !is_numeric($val)) {
$newVal .= -1;
} else {
$newVal .= $val;
}
}
$parms['value'] = $newVal;
}
} elseif ($type == 'html' && $customField) {
continue;
}
if (isset($parms['value']) && $parms['value'] != "") {
$operator = 'like';
if (!empty($parms['operator'])) {
$operator = $parms['operator'];
}
if (is_array($parms['value'])) {
$field_value = '';
// always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
if (!empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
// construct the query for multenums
// use the 'like' query for all mssql and oracle examples as both custom and OOB multienums are implemented with types that cannot be used with an 'in'
$operator = 'custom_enum';
$table_name = $this->seed->table_name;
if ($customField) {
$table_name .= "_cstm";
}
$db_field = $table_name . "." . $field;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
$qVal = $GLOBALS['db']->quote($val);
if (!empty($field_value)) {
$field_value .= ' or ';
}
$field_value .= "{$db_field} like '%^{$qVal}^%'";
}
}
} else {
$operator = $operator != 'subquery' ? 'in' : $operator;
foreach ($parms['value'] as $key => $val) {
if ($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
}
}
}
} else {
$field_value = $GLOBALS['db']->quote($parms['value']);
}
//set db_fields array.
if (!isset($parms['db_field'])) {
$parms['db_field'] = array($field);
}
if (isset($parms['my_items']) and $parms['my_items'] == true) {
if ($parms['value'] == false) {
//do not include where clause for custom fields with checkboxes that are unchecked
continue;
} else {
//my items is checked.
global $current_user;
$field_value = $GLOBALS['db']->quote($current_user->id);
$operator = '=';
}
// $operator = ($parms['value'] == '1') ? '=' : '!=';
}
$where = '';
$itr = 0;
if ($field_value != '') {
$this->searchColumns[strtoupper($field)] = $field;
foreach ($parms['db_field'] as $db_field) {
if (strstr($db_field, '.') === false) {
//Try to get the table for relate fields from link defs
if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link']) && !empty($this->seed->field_name_map[$field]['rname'])) {
$link = $this->seed->field_name_map[$field]['link'];
$relname = $link['relationship'];
if ($this->seed->load_relationship($link)) {
//Martin fix #27494
$db_field = $this->seed->field_name_map[$field]['name'];
} else {
//Best Guess for table name
$db_field = strtolower($link['module']) . '.' . $db_field;
}
} else {
if ($type == 'parent') {
if (!empty($this->searchFields['parent_type'])) {
$parentType = $this->searchFields['parent_type'];
$rel_module = $parentType['value'];
global $beanFiles, $beanList;
if (!empty($beanFiles[$beanList[$rel_module]])) {
require_once $beanFiles[$beanList[$rel_module]];
$rel_seed = new $beanList[$rel_module]();
$db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
}
}
} else {
if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
$db_field = !empty($this->seed->field_name_map[$field]['name']) ? $this->seed->field_name_map[$field]['name'] : 'name';
} else {
if (!$customField) {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "." . $db_field;
}
} else {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = db_concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "_cstm." . $db_field;
}
}
}
}
}
}
if ($type == 'date') {
// Collin - Have mysql as first because it's usually the case
// The regular expression check is to circumvent special case YYYY-MM
if ($GLOBALS['db']->dbType == 'mysql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
} else {
$operator = 'db_date';
}
} else {
if ($GLOBALS['db']->dbType == 'oci8') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = $timedate->to_db_date($field_value, false);
$field_value = "to_date('" . $field_value . "', 'YYYY-MM-DD hh24:mi:ss')";
}
$operator = 'db_date';
} else {
if ($GLOBALS['db']->dbType == 'mssql') {
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$field_value = "Convert(DateTime, '" . $timedate->to_db_date($field_value, false) . "')";
}
$operator = 'db_date';
} else {
$field_value = $timedate->to_db_date($field_value, false);
$operator = '=';
}
}
}
}
if ($type == 'datetime') {
//bug 22564, date type field may also have this problem. we may add a date type here.
$field_value = $timedate->to_db_date($field_value, false);
//This think of the timezone problem
$temp_offset = strtotime($timedate->swap_formats($timedate->to_display_date_time($field_value . " 00:00:00"), $timedate->get_date_time_format(), $timedate->get_db_date_time_format())) - strtotime($field_value . " 00:00:00");
$start_datetime = date("Y-m-d H:i:s", strtotime($field_value . " 00:00:00") - $temp_offset);
$end_datetime = date("Y-m-d H:i:s", strtotime($field_value . " 23:59:59") - $temp_offset);
$field_value = $start_datetime . "<>" . $end_datetime;
$operator = 'between';
}
if ($GLOBALS['db']->dbType == 'oci8' && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
$db_field = 'upper(' . $db_field . ")";
$field_value = strtoupper($field_value);
}
$itr++;
if (!empty($where)) {
$where .= " OR ";
}
switch (strtolower($operator)) {
case 'subquery':
$sq = $parms['subquery'];
if (is_array($sq)) {
$and_or = ' AND ';
if (isset($sq['OR'])) {
$and_or = ' OR ';
}
$first = true;
foreach ($sq as $q) {
if (empty($q) || strlen($q) < 2) {
continue;
}
if (!$first) {
$where .= $and_or;
}
$where .= " {$db_field} IN ({$q} '{$field_value}%') ";
$first = false;
}
} elseif (!empty($parms['query_type']) && $parms['query_type'] == 'format') {
$where .= "{$db_field} IN (" . string_format($parms['subquery'], array($field_value)) . ")";
} else {
$where .= "{$db_field} IN ({$parms['subquery']} '{$field_value}%')";
}
break;
case 'like':
if ($type == 'bool' && $field_value == 0) {
$where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
} else {
//check to see if this is coming from unified search or not
$UnifiedSearch = false;
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch') {
$UnifiedSearch = true;
}
//check to see if this is a universal search, AND the field name is "last_name"
if ($UnifiedSearch && strpos($db_field, 'last_name') !== false) {
//split the string value, and the db field name
$string = explode(' ', $field_value);
$column_name = explode('.', $db_field);
//when a search is done with a space, we concatenate and search against the full name.
if (count($string) > 1) {
//add where clause agains concatenated fields
if ($GLOBALS['db']->dbType == 'mysql') {
$where .= "CONCAT({$column_name[0]}.first_name, ' ', {$column_name[0]}.last_name) in ('{$string[0]} {$string[1]}', '{$string[1]} {$string[0]}')";
} elseif ($GLOBALS['db']->dbType == 'mssql') {
$where .= "{$column_name[0]}.first_name + ' '+ {$column_name[0]}.last_name in ('{$string[0]} {$string[1]}', '{$string[1]} {$string[0]}')";
} else {
$where .= "{$column_name[0]}.first_name ||' '|| {$column_name[0]}.last_name in ('{$string[0]} {$string[1]}', '{$string[1]} {$string[0]}')";
}
} else {
//no space was found, add normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
} else {
//field is not last name or this is not from global unified search, so do normal where clause
$where .= $db_field . " like '" . $field_value . $like_char . "'";
}
}
break;
case 'in':
$where .= $db_field . " in (" . $field_value . ')';
break;
case '=':
if ($type == 'bool' && $field_value == 0) {
$where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
} else {
$where .= $db_field . " = '" . $field_value . "'";
}
break;
case 'db_date':
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) == 0) {
$where .= $db_field . " = " . $field_value;
} else {
// Create correct date_format conversion String
if ($GLOBALS['db']->dbType == 'oci8') {
$where .= db_convert($db_field, 'date_format', array("'YYYY-MM'")) . " = '" . $field_value . "'";
} else {
$where .= db_convert($db_field, 'date_format', array("'%Y-%m'")) . " = '" . $field_value . "'";
}
}
break;
// tyoung bug 15971 - need to add these special cases into the $where query
// tyoung bug 15971 - need to add these special cases into the $where query
case 'custom_enum':
$where .= $field_value;
break;
case 'between':
$field_value = explode('<>', $field_value);
$where .= $db_field . " > '" . $field_value[0] . "' AND " . $db_field . " < '" . $field_value[1] . "'";
break;
}
}
}
if (!empty($where)) {
if ($itr > 1) {
array_push($where_clauses, '( ' . $where . ' )');
} else {
array_push($where_clauses, $where);
}
}
}
}
return $where_clauses;
}
function acl_init(&$a)
{
// logger('mod_acl: ' . print_r($_REQUEST,true));
$start = x($_REQUEST, 'start') ? $_REQUEST['start'] : 0;
$count = x($_REQUEST, 'count') ? $_REQUEST['count'] : 100;
$search = x($_REQUEST, 'search') ? $_REQUEST['search'] : "";
$type = x($_REQUEST, 'type') ? $_REQUEST['type'] : "";
$noforums = x($_REQUEST, 'n') ? $_REQUEST['n'] : false;
// List of channels whose connections to also suggest, e.g. currently viewed channel or channels mentioned in a post
$extra_channels = x($_REQUEST, 'extra_channels') ? $_REQUEST['extra_channels'] : array();
// For use with jquery.autocomplete for private mail completion
if (x($_REQUEST, 'query') && strlen($_REQUEST['query'])) {
if (!$type) {
$type = 'm';
}
$search = $_REQUEST['query'];
}
if (!local_channel()) {
if (!($type == 'x' || $type == 'c')) {
killme();
}
}
if ($search != "") {
$sql_extra = " AND `name` LIKE " . protect_sprintf("'%" . dbesc($search) . "%'") . " ";
$sql_extra2 = "AND ( xchan_name LIKE " . protect_sprintf("'%" . dbesc($search) . "%'") . " OR xchan_addr LIKE " . protect_sprintf("'%" . dbesc($search) . (strpos($search, '@') === false ? "%@%'" : "%'")) . ") ";
// This horrible mess is needed because position also returns 0 if nothing is found. W/ould be MUCH easier if it instead returned a very large value
// Otherwise we could just order by LEAST(POSITION($search IN xchan_name),POSITION($search IN xchan_addr)).
$order_extra2 = "CASE WHEN xchan_name LIKE " . protect_sprintf("'%" . dbesc($search) . "%'") . " then POSITION('" . dbesc($search) . "' IN xchan_name) else position('" . dbesc($search) . "' IN xchan_addr) end, ";
$col = strpos($search, '@') !== false ? 'xchan_addr' : 'xchan_name';
$sql_extra3 = "AND {$col} like " . protect_sprintf("'%" . dbesc($search) . "%'") . " ";
} else {
$sql_extra = $sql_extra2 = $sql_extra3 = "";
}
$groups = array();
$contacts = array();
if ($type == '' || $type == 'g') {
$r = q("SELECT `groups`.`id`, `groups`.`hash`, `groups`.`name`, \n\t\t\t\t%s as uids\n\t\t\t\tFROM `groups`,`group_member` \n\t\t\t\tWHERE `groups`.`deleted` = 0 AND `groups`.`uid` = %d \n\t\t\t\t\tAND `group_member`.`gid`=`groups`.`id`\n\t\t\t\t\t{$sql_extra}\n\t\t\t\tGROUP BY `groups`.`id`\n\t\t\t\tORDER BY `groups`.`name` \n\t\t\t\tLIMIT %d OFFSET %d", db_concat('group_member.xchan', ','), intval(local_channel()), intval($count), intval($start));
foreach ($r as $g) {
// logger('acl: group: ' . $g['name'] . ' members: ' . $g['uids']);
$groups[] = array("type" => "g", "photo" => "images/twopeople.png", "name" => $g['name'], "id" => $g['id'], "xid" => $g['hash'], "uids" => explode(",", $g['uids']), "link" => '');
}
}
if ($type == '' || $type == 'c') {
$extra_channels_sql = '';
// Only include channels who allow the observer to view their permissions
foreach ($extra_channels as $channel) {
if (perm_is_allowed(intval($channel), get_observer_hash(), 'view_contacts')) {
$extra_channels_sql .= "," . intval($channel);
}
}
$extra_channels_sql = substr($extra_channels_sql, 1);
// Remove initial comma
// Getting info from the abook is better for local users because it contains info about permissions
if (local_channel()) {
if ($extra_channels_sql != '') {
$extra_channels_sql = " OR (abook_channel IN ({$extra_channels_sql})) and abook_hidden = 0 ";
}
$r = q("SELECT abook_id as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, abook_their_perms, abook_flags, abook_self \n\t\t\t\tFROM abook left join xchan on abook_xchan = xchan_hash \n\t\t\t\tWHERE (abook_channel = %d {$extra_channels_sql}) AND abook_blocked = 0 and abook_pending = 0 and abook_archived = 0 and xchan_deleted = 0 {$sql_extra2} order by {$order_extra2} xchan_name asc", intval(local_channel()));
} else {
// Visitors
$r = q("SELECT xchan_hash as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, 0 as abook_their_perms, 0 as abook_flags, 0 as abook_self\n\t\t\t\tFROM xchan left join xlink on xlink_link = xchan_hash\n\t\t\t\tWHERE xlink_xchan = '%s' AND xchan_deleted = 0 {$sql_extra2} order by {$order_extra2} xchan_name asc", dbesc(get_observer_hash()));
// Find contacts of extra channels
// This is probably more complicated than it needs to be
if ($extra_channels_sql) {
// Build a list of hashes that we got previously so we don't get them again
$known_hashes = array("'" . get_observer_hash() . "'");
if ($r) {
foreach ($r as $rr) {
$known_hashes[] = "'" . $rr['hash'] . "'";
}
}
$known_hashes_sql = 'AND xchan_hash not in (' . join(',', $known_hashes) . ')';
$r2 = q("SELECT abook_id as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, abook_their_perms, abook_flags, abook_self \n\t\t\t\t\tFROM abook left join xchan on abook_xchan = xchan_hash \n\t\t\t\t\tWHERE abook_channel IN ({$extra_channels_sql}) {$known_hashes_sql} AND abook_blocked = 0 and abook_pending = 0 and abook_archived = 0 and abook_hidden = 0 and xchan_deleted = 0 {$sql_extra2} order by {$order_extra2} xchan_name asc");
if ($r2) {
$r = array_merge($r, $r2);
}
// Sort accoring to match position, then alphabetically. This could be avoided if the above two SQL queries could be combined into one, and the sorting could be done on the SQl server (like in the case of a local user)
$matchpos = function ($x) use($search) {
$namepos = strpos($x['name'], $search);
$nickpos = strpos($x['nick'], $search);
// Use a large position if not found
return min($namepos === false ? 9999 : $namepos, $nickpos === false ? 9999 : $nickpos);
};
// This could be made simpler if PHP supported stable sorting
usort($r, function ($a, $b) use($matchpos) {
$pos1 = $matchpos($a);
$pos2 = $matchpos($b);
if ($pos1 == $pos2) {
// Order alphabetically if match position is the same
if ($a['name'] == $b['name']) {
return 0;
} else {
return $a['name'] < $b['name'] ? -1 : 1;
}
}
return $pos1 < $pos2 ? -1 : 1;
});
}
}
if (intval(get_config('system', 'taganyone')) || intval(get_pconfig(local_channel(), 'system', 'taganyone'))) {
if (count($r) < 100 && $type == 'c') {
$r2 = q("SELECT substr(xchan_hash,1,18) as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, 0 as abook_their_perms, 0 as abook_flags, 0 as abook_self \n\t\t\t\t\tFROM xchan \n\t\t\t\t\tWHERE xchan_deleted = 0 {$sql_extra2} order by {$order_extra2} xchan_name asc");
if ($r2) {
$r = array_merge($r, $r2);
}
}
}
} elseif ($type == 'm') {
$r = q("SELECT xchan_hash as id, xchan_name as name, xchan_addr as nick, xchan_photo_s as micro, xchan_url as url \n\t\t\tFROM abook left join xchan on abook_xchan = xchan_hash\n\t\t\tWHERE abook_channel = %d and ( (abook_their_perms = null) or (abook_their_perms & %d )>0)\n\t\t\tand xchan_deleted = 0\n\t\t\t{$sql_extra3}\n\t\t\tORDER BY `xchan_name` ASC ", intval(local_channel()), intval(PERMS_W_MAIL));
} elseif ($type == 'a' || $type == 'p') {
$r = q("SELECT abook_id as id, xchan_name as name, xchan_hash as hash, xchan_addr as nick, xchan_photo_s as micro, xchan_network as network, xchan_url as url, xchan_addr as attag , abook_their_perms FROM abook left join xchan on abook_xchan = xchan_hash\n\t\t\tWHERE abook_channel = %d\n\t\t\tand xchan_deleted = 0\n\t\t\t{$sql_extra3}\n\t\t\tORDER BY xchan_name ASC ", intval(local_channel()));
} elseif ($type == 'x') {
$r = navbar_complete($a);
$contacts = array();
if ($r) {
foreach ($r as $g) {
$contacts[] = array("photo" => $g['photo'], "name" => $g['name'], "nick" => $g['address']);
}
}
$o = array('start' => $start, 'count' => $count, 'items' => $contacts);
echo json_encode($o);
killme();
} else {
$r = array();
}
if (count($r)) {
foreach ($r as $g) {
// remove RSS feeds from ACLs - they are inaccessible
if (strpos($g['hash'], '/') && $type != 'a') {
continue;
}
if ($g['abook_their_perms'] & PERMS_W_TAGWALL && $type == 'c' && !$noforums) {
$contacts[] = array("type" => "c", "photo" => "images/twopeople.png", "name" => $g['name'] . '+', "id" => $g['id'] . '+', "xid" => $g['hash'], "link" => $g['nick'], "nick" => substr($g['nick'], 0, strpos($g['nick'], '@')), "self" => intval($g['abook_self']) ? 'abook-self' : '', "taggable" => 'taggable', "label" => t('network'));
}
$contacts[] = array("type" => "c", "photo" => $g['micro'], "name" => $g['name'], "id" => $g['id'], "xid" => $g['hash'], "link" => $g['nick'], "nick" => $g['nick'] ? substr($g['nick'], 0, strpos($g['nick'], '@')) : t('RSS'), "self" => intval($g['abook_self']) ? 'abook-self' : '', "taggable" => '', "label" => '');
}
}
$items = array_merge($groups, $contacts);
$o = array('start' => $start, 'count' => $count, 'items' => $items);
echo json_encode($o);
killme();
}
/**
* Validate relate fields
*
* @param $value string
* @param $vardef array
* @param $focus object bean of the module we're importing into
* @param $addRelatedBean bool true if we want to add the related bean if it is not found
* @return string sanitized and validated value on success, bool false on failure
*/
public function relate($value, $vardef, &$focus, $addRelatedBean = true)
{
if (!isset($vardef['module'])) {
return false;
}
$newbean = loadBean($vardef['module']);
// Bug 27046 - Validate field against type as it is in the related field
$rvardef = $newbean->getFieldDefinition($vardef['rname']);
if (isset($rvardef['type']) && method_exists($this, $rvardef['type'])) {
$fieldtype = $rvardef['type'];
$returnValue = $this->{$fieldtype}($value, $rvardef);
if (!$returnValue) {
return false;
} else {
$value = $returnValue;
}
}
if (isset($vardef['id_name'])) {
$idField = $vardef['id_name'];
// Bug 24075 - clear out id field value if it is invalid
if (isset($focus->{$idField})) {
$checkfocus = loadBean($vardef['module']);
if ($checkfocus && is_null($checkfocus->retrieve($focus->{$idField}))) {
$focus->{$idField} = '';
}
}
// be sure that the id isn't already set for this row
if (empty($focus->{$idField}) && $idField != $vardef['name'] && !empty($vardef['rname']) && !empty($vardef['table'])) {
// Bug 27562 - Check db_concat_fields first to see if the field name is a concat
$relatedFieldDef = $newbean->getFieldDefinition($vardef['rname']);
if (isset($relatedFieldDef['db_concat_fields']) && is_array($relatedFieldDef['db_concat_fields'])) {
$fieldname = db_concat($vardef['table'], $relatedFieldDef['db_concat_fields']);
} else {
$fieldname = $vardef['rname'];
}
// lookup first record that matches in linked table
$query = "SELECT id \n FROM {$vardef['table']} \n WHERE {$fieldname} = '" . $focus->db->quote($value) . "'\n AND deleted != 1";
$result = $focus->db->limitQuery($query, 0, 1, true, "Want only a single row");
if (!empty($result)) {
if ($relaterow = $focus->db->fetchByAssoc($result)) {
$focus->{$idField} = $relaterow['id'];
} elseif (!$addRelatedBean || $newbean->bean_implements('ACL') && !$newbean->ACLAccess('save') || in_array($newbean->module_dir, array('Teams', 'Users')) && !is_admin($GLOBALS['current_user'])) {
return false;
} else {
// add this as a new record in that bean, then relate
if (isset($relatedFieldDef['db_concat_fields']) && is_array($relatedFieldDef['db_concat_fields'])) {
$relatedFieldParts = explode(' ', $value);
foreach ($relatedFieldDef['db_concat_fields'] as $relatedField) {
$newbean->{$relatedField} = array_shift($relatedFieldParts);
}
} else {
$newbean->{$vardef}['rname'] = $value;
}
if (!isset($focus->assigned_user_id) || $focus->assigned_user_id == '') {
$newbean->assigned_user_id = $GLOBALS['current_user']->id;
} else {
$newbean->assigned_user_id = $focus->assigned_user_id;
}
if (!isset($focus->modified_user_id) || $focus->modified_user_id == '') {
$newbean->modified_user_id = $GLOBALS['current_user']->id;
} else {
$newbean->modified_user_id = $focus->modified_user_id;
}
// populate fields from the parent bean to the child bean
$focus->populateRelatedBean($newbean);
$newbean->save(false);
$focus->{$idField} = $newbean->id;
self::$createdBeans[] = array($newbean->object_name, ImportFile::writeRowToLastImport($focus->module_dir, $newbean->object_name, $newbean->id));
}
}
}
}
return $value;
}
public function getRelateJoin($field_def, $joinTableAlias, $withIdName = true)
{
if (empty($field_def['type']) || $field_def['type'] != "relate" || empty($field_def['module'])) {
return false;
}
$rel_mod = BeanFactory::getBean($field_def['module']);
if (empty($rel_mod)) {
return false;
}
$rel_table = $rel_mod->table_name;
if (isset($rel_mod->field_defs['name'])) {
$name_field_def = $rel_mod->field_defs['name'];
if (isset($name_field_def['db_concat_fields'])) {
$name_field = db_concat($joinTableAlias, $name_field_def['db_concat_fields']);
} else {
if (!empty($rel_mod->field_defs['name']['source']) && $rel_mod->field_defs['name']['source'] == "non-db" && !empty($field_def['rname'])) {
$name_field = "{$joinTableAlias}." . $field_def['rname'];
} else {
$name_field = "{$joinTableAlias}.name";
}
}
}
$tableName = isset($field_def['custom_module']) ? "{$this->bean->table_name}_cstm" : $this->bean->table_name;
$relID = $field_def['id_name'];
$select = '';
if ($withIdName) {
$select .= ', ' . $tableName . '.' . $relID;
}
$relate_query = $rel_mod->getRelateFieldQuery($field_def, $joinTableAlias);
if ($relate_query['select']) {
$select .= ', ' . $relate_query['select'];
}
$ret_array['rel_table'] = $rel_table = $rel_mod->table_name;
$ret_array['name_field'] = $name_field;
$ret_array['select'] = $select;
$ret_array['from'] = " LEFT JOIN {$rel_table} {$joinTableAlias} ON {$tableName}.{$relID} = {$joinTableAlias}.id" . " AND {$joinTableAlias}.deleted=0 " . $relate_query['join'];
return $ret_array;
}
function address_popup_create_new_list_query($order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = '', $return_array = false, $parentbean = null, $singleSelect = false)
{
//if this is any action that is not the contact address popup, then go to parent function in sugarbean
if (isset($_REQUEST['action']) && $_REQUEST['action'] !== 'ContactAddressPopup') {
return parent::create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
}
$custom_join = $this->getCustomJoin();
// MFH - BUG #14208 creates alias name for select
$select_query = "SELECT ";
$select_query .= db_concat($this->table_name, array('first_name', 'last_name')) . " name, ";
$select_query .= "\n\t\t\t\t{$this->table_name}.*,\n accounts.name as account_name,\n accounts.id as account_id,\n accounts.assigned_user_id account_id_owner,\n users.user_name as assigned_user_name ";
$select_query .= ",teams.name AS team_name ";
$select_query .= $custom_join['select'];
$ret_array['select'] = $select_query;
$from_query = "\n FROM contacts ";
// We need to confirm that the user is a member of the team of the item.
$this->addVisibilityFrom($from_query, array('where_condition' => true));
$from_query .= "LEFT JOIN users\n\t ON contacts.assigned_user_id=users.id\n\t LEFT JOIN accounts_contacts\n\t ON contacts.id=accounts_contacts.contact_id and accounts_contacts.deleted = 0\n\t LEFT JOIN accounts\n\t ON accounts_contacts.account_id=accounts.id AND accounts.deleted=0 ";
$from_query .= "LEFT JOIN teams ON contacts.team_id=teams.id AND (teams.deleted=0) ";
$from_query .= "LEFT JOIN email_addr_bean_rel eabl ON eabl.bean_id = contacts.id AND eabl.bean_module = 'Contacts' and eabl.primary_address = 1 and eabl.deleted=0 ";
$from_query .= "LEFT JOIN email_addresses ea ON (ea.id = eabl.email_address_id) ";
$from_query .= $custom_join['join'];
$ret_array['from'] = $from_query;
$ret_array['from_min'] = 'from contacts';
$where_auto = '1=1';
if ($show_deleted == 0) {
$where_auto = " {$this->table_name}.deleted=0 ";
//$where_auto .= " AND accounts.deleted=0 ";
} else {
if ($show_deleted == 1) {
$where_auto = " {$this->table_name}.deleted=1 ";
}
}
if ($where != "") {
$where_query = "where ({$where}) AND " . $where_auto;
} else {
$where_query = "where " . $where_auto;
}
$this->addVisibilityWhere($where_query, array('where_condition' => true));
$acc = BeanFactory::getBean('Accounts');
$acc->addVisibilityWhere($where_query, array('where_condition' => true, 'table_alias' => 'accounts'));
$ret_array['where'] = $where_query;
$ret_array['order_by'] = '';
//process order by and add if it's not empty
$order_by = $this->process_order_by($order_by);
if (!empty($order_by)) {
$ret_array['order_by'] = ' ORDER BY ' . $order_by;
}
if ($return_array) {
return $ret_array;
}
return $ret_array['select'] . $ret_array['from'] . $ret_array['where'] . $ret_array['order_by'];
}
function getRelatedFields($module, $id, $fields, $return_array = false)
{
if (empty($GLOBALS['beanList'][$module])) {
return '';
}
$object = $GLOBALS['beanList'][$module];
if ($object == 'aCase') {
$object = 'Case';
}
VardefManager::loadVardef($module, $object);
if (empty($GLOBALS['dictionary'][$object]['table'])) {
return '';
}
$table = $GLOBALS['dictionary'][$object]['table'];
$query = 'SELECT id';
foreach ($fields as $field => $alias) {
if (!empty($GLOBALS['dictionary'][$object]['fields'][$field]['db_concat_fields'])) {
$query .= ' ,' . db_concat($table, $GLOBALS['dictionary'][$object]['fields'][$field]['db_concat_fields']) . ' as ' . $alias;
} else {
if (!empty($GLOBALS['dictionary'][$object]['fields'][$field]) && (empty($GLOBALS['dictionary'][$object]['fields'][$field]['source']) || $GLOBALS['dictionary'][$object]['fields'][$field]['source'] != "non-db")) {
$query .= ' ,' . $table . '.' . $field . ' as ' . $alias;
}
}
if (!$return_array) {
$this->{$alias} = '';
}
}
if ($query == 'SELECT id' || empty($id)) {
return '';
}
if (isset($GLOBALS['dictionary'][$object]['fields']['assigned_user_id'])) {
$query .= " , " . $table . ".assigned_user_id owner";
} else {
if (isset($GLOBALS['dictionary'][$object]['fields']['created_by'])) {
$query .= " , " . $table . ".created_by owner";
}
}
$query .= ' FROM ' . $table . ' WHERE deleted=0 AND id=';
$result = $GLOBALS['db']->query($query . "'{$id}'");
$row = $GLOBALS['db']->fetchByAssoc($result);
if ($return_array) {
return $row;
}
$owner = empty($row['owner']) ? '' : $row['owner'];
foreach ($fields as $alias) {
$this->{$alias} = !empty($row[$alias]) ? $row[$alias] : '';
$alias = $alias . '_owner';
$this->{$alias} = $owner;
$a_mod = $alias . '_mod';
$this->{$a_mod} = $module;
}
}
function retrieve_parent_fields($type_info)
{
$queries = array();
global $beanList, $beanFiles;
$templates = array();
$parent_child_map = array();
foreach ($type_info as $children_info) {
foreach ($children_info as $child_info) {
if ($child_info['type'] == 'parent') {
if (empty($templates[$child_info['parent_type']])) {
$class = $beanList[$child_info['parent_type']];
require_once $beanFiles[$class];
$templates[$child_info['parent_type']] = new $class();
}
if (empty($queries[$child_info['parent_type']])) {
$queries[$child_info['parent_type']] = "SELECT id ";
$field_def = $templates[$child_info['parent_type']]->field_defs['name'];
if (isset($field_def['db_concat_fields'])) {
$queries[$child_info['parent_type']] .= ' , ' . db_concat($templates[$child_info['parent_type']]->table_name, $field_def['db_concat_fields']) . ' parent_name';
} else {
$queries[$child_info['parent_type']] .= ' , name parent_name';
}
if (isset($templates[$child_info['parent_type']]->field_defs['assigned_user_id'])) {
$queries[$child_info['parent_type']] .= ", assigned_user_id parent_name_owner , '{$child_info['parent_type']}' parent_name_mod";
} else {
if (isset($templates[$child_info['parent_type']]->field_defs['created_by'])) {
$queries[$child_info['parent_type']] .= ", created_by parent_name_owner, '{$child_info['parent_type']}' parent_name_mod";
}
}
$queries[$child_info['parent_type']] .= " FROM " . $templates[$child_info['parent_type']]->table_name . " WHERE id IN ('{$child_info['parent_id']}'";
} else {
if (empty($parent_child_map[$child_info['parent_id']])) {
$queries[$child_info['parent_type']] .= " ,'{$child_info['parent_id']}'";
}
}
$parent_child_map[$child_info['parent_id']][] = $child_info['child_id'];
}
}
}
$results = array();
foreach ($queries as $query) {
$result = $this->db->query($query . ')');
while ($row = $this->db->fetchByAssoc($result)) {
$results[$row['id']] = $row;
}
}
$child_results = array();
foreach ($parent_child_map as $parent_key => $parent_child) {
foreach ($parent_child as $child) {
if (isset($results[$parent_key])) {
$child_results[$child] = $results[$parent_key];
}
}
}
return $child_results;
}
/**
* create_export_query is used for export and massupdate
* We haven't handle the these fields: $field['type'] == 'relate' && isset($field['link']
* This function will correct the where clause and output necessary join condition for them
* @param $module: the module name
* @param $searchFields: searchFields which is got after $searchForm->populateFromArray()
* @param $where: where clauses
* @return $ret_array['where']: corrected where clause
* @return $ret_array['join']: extra join condition
*/
function create_export_query_relate_link_patch($module, $searchFields, $where)
{
if (file_exists('modules/' . $module . '/SearchForm.html')) {
$ret_array['where'] = $where;
return $ret_array;
}
$seed = loadBean($module);
foreach ($seed->field_defs as $name => $field) {
if ($field['type'] == 'relate' && isset($field['link']) && !empty($searchFields[$name]['value'])) {
$seed->load_relationship($field['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'] = $field['join_name'];
} else {
$params['join_table_alias'] = 'join_' . $field['name'];
}
if (isset($data['join_link_name'])) {
$params['join_table_link_alias'] = $field['join_link_name'];
} else {
$params['join_table_link_alias'] = 'join_link_' . $field['name'];
}
$join = $seed->{$field}['link']->getJoin($params, true);
$join_table_alias = 'join_' . $field['name'];
if (isset($field['db_concat_fields'])) {
$db_field = db_concat($join_table_alias, $field['db_concat_fields']);
$where = preg_replace('/' . $field['name'] . '/', $db_field, $where);
} else {
$where = preg_replace('/(^|[\\s(])' . $field['name'] . '/', '${1}' . $join_table_alias . '.' . $field['rname'], $where);
}
}
}
$ret_array = array('where' => $where, 'join' => $join['join']);
return $ret_array;
}
/**
* getExt2FieldDefSelectPiece
*
* This is a private helper function to separate a piece of code that creates the select statement for a field where
* there is an aggregation of columns
*
* @param $field_def Array representing the field definition to build the select piece for
* @param $add_alias boolean true to add the column alias, false otherwise (you would want false for group by)
*/
private function getExt2FieldDefSelectPiece($field_def, $add_alias = true)
{
$extModule = BeanFactory::getBean($field_def['ext2']);
$secondaryTableAlias = $field_def['secondary_table'];
if (!empty($this->selected_loaded_custom_links) && !empty($this->selected_loaded_custom_links[$field_def['secondary_table'] . '_' . $field_def['rep_rel_name']])) {
$secondaryTableAlias = $this->selected_loaded_custom_links[$field_def['secondary_table'] . '_' . $field_def['rep_rel_name']]['join_table_alias'];
} else {
if (!empty($this->selected_loaded_custom_links) && !empty($this->selected_loaded_custom_links[$field_def['secondary_table']])) {
$secondaryTableAlias = $this->selected_loaded_custom_links[$field_def['secondary_table']]['join_table_alias'];
}
}
if (isset($extModule->field_defs['name']['db_concat_fields'])) {
$select_piece = db_concat($secondaryTableAlias, $extModule->field_defs['name']['db_concat_fields']);
} else {
if (isset($field_def['rname']) && isset($extModule->field_defs[$field_def['rname']])) {
$select_piece = $secondaryTableAlias . ".{$field_def['rname']}";
} else {
$select_piece = $secondaryTableAlias . '.name';
//. $secondaryTableAlias.'_name';
}
}
$select_piece .= $add_alias ? " {$secondaryTableAlias}_name" : ' ';
return $select_piece;
}
