public function testconvertToDollar()
{
error_reporting(E_ERROR | E_PARSE);
$currency = new Currency();
//test without setting attributes
$this->assertEquals(0, $currency->convertToDollar(100, 2));
//test with required attributes set
$currency->conversion_rate = 1.6;
$this->assertEquals(62.5, $currency->convertToDollar(100, 2));
}
function perform_save(&$focus)
{
//US DOLLAR
if (isset($focus->amount) && !number_empty($focus->amount)) {
$currency = new Currency();
$currency->retrieve($focus->currency_id);
$focus->amount_usdollar = $currency->convertToDollar($focus->amount);
}
}
function perform_save(&$focus)
{
require_once 'modules/Currencies/Currency.php';
//US DOLLAR
if (isset($focus->price) && !number_empty($focus->price)) {
$currency = new Currency();
$currency->retrieve($focus->currency_id);
$focus->price_usdollar = $currency->convertToDollar(unformat_number($focus->price));
}
}
function updateAmountByID($id, $curID, $price)
{
global $db, $currencies;
if (isset($id) && !empty($id)) {
$currency = new Currency();
$currency->retrieve($curID);
$dollars = $currency->convertToDollar($price);
$query = "update opportunities set price='{$price}', currency_id='{$curID}', price_usdollar='{$dollars}' where id='{$id}';";
$db->query($query);
}
}
function save($check_notify = FALSE)
{
//"amount_usdollar" is really amount_basecurrency. We need to save a copy of the amount in the base currency.
if (isset($this->amount) && !number_empty($this->amount)) {
if (!number_empty($this->currency_id)) {
$currency = new Currency();
$currency->retrieve($this->currency_id);
$this->amount_usdollar = $currency->convertToDollar($this->amount);
} else {
$this->amount_usdollar = $this->amount;
}
}
return parent::save($check_notify);
}
/**
* Products, Quotations & Invoices modules.
* Extensions to SugarCRM
* @package Advanced OpenSales for SugarCRM
* @subpackage Products
* @copyright SalesAgility Ltd http://www.salesagility.com
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE as published by
* the Free Software Foundation; either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU AFFERO GENERAL PUBLIC LICENSE
* along with this program; if not, see http://www.gnu.org/licenses
* or write to the Free Software Foundation,Inc., 51 Franklin Street,
* Fifth Floor, Boston, MA 02110-1301 USA
*
* @author Salesagility Ltd <*****@*****.**>
*/
function perform_aos_save($focus)
{
foreach ($focus->field_defs as $field) {
$fieldName = $field['name'];
$fieldNameDollar = $field['name'] . '_usdollar';
if (isset($focus->field_defs[$fieldNameDollar])) {
$focus->{$fieldNameDollar} = '';
if (!number_empty($focus->field_defs[$field['name']])) {
$currency = new Currency();
$currency->retrieve($focus->currency_id);
$focus->{$fieldNameDollar} = $currency->convertToDollar(unformat_number($fieldName));
}
}
}
}
/**
* Products, Quotations & Invoices modules.
* Extensions to SugarCRM
* @package Advanced OpenSales for SugarCRM
* @subpackage Products
* @copyright SalesAgility Ltd http://www.salesagility.com
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU AFFERO GENERAL PUBLIC LICENSE as published by
* the Free Software Foundation; either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU AFFERO GENERAL PUBLIC LICENSE
* along with this program; if not, see http://www.gnu.org/licenses
* or write to the Free Software Foundation,Inc., 51 Franklin Street,
* Fifth Floor, Boston, MA 02110-1301 USA
*
* @author Salesagility Ltd <*****@*****.**>
*/
function perform_aos_save($focus)
{
//US DOLLAR
foreach ($focus->field_defs as $field) {
if (isset($focus->field_defs[$field['name'] . '_usdollar'])) {
$fieldName = $field['name'] . '_usdollar';
$focus->{$fieldName} = '';
if (!number_empty($focus->field_defs[$field['name']])) {
$currency = new Currency();
$currency->retrieve($focus->currency_id);
$focus->{$fieldName} = $currency->convertToDollar($focus->{$field}['name']);
}
}
}
}
function save($check_notify = FALSE)
{
//US DOLLAR
if (isset($this->amount) && !empty($this->amount)) {
$currency = new Currency();
$currency->retrieve($this->currency_id);
$this->amount_usdollar = $currency->convertToDollar($this->amount);
}
$this->unformat_all_fields();
return parent::save($check_notify);
}
function update_currency_id($fromid, $toid)
{
$idequals = '';
$currency = new Currency();
$currency->retrieve($toid);
foreach ($fromid as $f) {
if (!empty($idequals)) {
$idequals .= ' or ';
}
$idequals .= "currency_id='{$f}'";
}
if (!empty($idequals)) {
$query = "select amount, id from opportunities where (" . $idequals . ") and deleted=0 and opportunities.sales_stage <> 'Closed Won' AND opportunities.sales_stage <> 'Closed Lost';";
$result = $this->db->query($query);
while ($row = $this->db->fetchByAssoc($result)) {
$query = "update opportunities set currency_id='" . $currency->id . "', amount_usdollar='" . $currency->convertToDollar($row['amount']) . "' where id='" . $row['id'] . "';";
$this->db->query($query);
}
}
}
/**
* generateSearchWhere
*
* This function serves as the central piece of SearchForm2.php
* It is responsible for creating the WHERE clause for a given search operation
*
* @param bool $add_custom_fields boolean indicating whether or not custom fields should be added
* @param string $module Module to search against
*
* @return string the SQL WHERE clause based on the arguments supplied in SearchForm2 instance
*/
public function generateSearchWhere($add_custom_fields = false, $module = '')
{
global $timedate;
$db = $this->seed->db;
$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) {
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, field: ' . $field);
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, parms: ' . print_r($parms, TRUE));
$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'] : '';
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, type: ' . $type);
//If range search is enabled for the field, we first check if this is the starting range
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 both start and end ranges have not been defined, skip this filter.
continue;
}
} 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 (in_array($parms['operator'], array('=', 'between', "not_equal", 'less_than', 'greater_than', 'less_than_equals', 'greater_than_equals'))) {
$field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
if (strtolower($field_type) == 'readonly' && isset($this->seed->field_name_map[$real_field]['dbType'])) {
$field_type = $this->seed->field_name_map[$real_field]['dbType'];
}
if ($field_type == 'datetimecombo' || $field_type == 'datetime' || $field_type == 'int') {
$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;
}
}
}
//Test to mark whether or not the field is a custom field
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' && isset($parms['value']) && !empty($parms['value'])) {
require_once 'include/SugarFields/SugarFieldHandler.php';
$intField = SugarFieldHandler::getSugarField('int');
$newVal = $intField->getSearchWhereValue($parms['value']);
$parms['value'] = $newVal;
} elseif ($type == 'html' && $customField) {
continue;
}
if (isset($parms['value']) && $parms['value'] != "") {
$operator = $db->isNumericType($type) ? '=' : 'like';
if (!empty($parms['operator'])) {
$operator = strtolower($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 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 $val) {
if ($val != ' ' and $val != '') {
$qVal = $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;
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, value_count: ' . count($parms['value']));
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, value-0: ' . $parms['value'][0]);
if (count($parms['value']) == 1 && empty($parms['value'][0])) {
continue;
}
foreach ($parms['value'] as $val) {
if ($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= $db->quoteType($type, $val);
} else {
if ($operator == 'in') {
$operator = 'isnull';
}
}
}
}
} else {
$field_value = $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 = $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 .= "," . $db->quoted($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') {
// The regular expression check is to circumvent special case YYYY-MM
$operator = '=';
if (preg_match('/^\\d{4}.\\d{1,2}$/', $field_value) != 0) {
// preg_match returns number of matches
$db_field = $this->seed->db->convert($db_field, "date_format", array("%Y-%m"));
} else {
$field_value = $timedate->to_db_date($field_value, false);
$db_field = $this->seed->db->convert($db_field, "date_format", array("%Y-%m-%d"));
}
}
if ($type == 'datetime' || $type == 'datetimecombo') {
try {
if ($operator == '=' || $operator == 'between') {
// 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 = array($dates["start"], $dates["end"]);
$operator = 'between';
} else {
if ($operator == 'not_equal') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = array($dates["start"], $dates["end"]);
$operator = 'date_not_equal';
} else {
if ($operator == 'greater_than') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["end"];
} else {
if ($operator == 'less_than') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["start"];
} else {
if ($operator == 'greater_than_equals') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["start"];
} else {
if ($operator == 'less_than_equals') {
$dates = $timedate->getDayStartEndGMT($field_value);
$field_value = $dates["end"];
}
}
}
}
}
}
} 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;
}
}
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
$values = array();
foreach ($fieldARR as $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);
$tmpfield_value = $currency->convertToDollar($tmpfield_value);
}
}
$values[] = $tmpfield_value;
}
$field_value = join('<>', $values);
if (!empty($parms['enable_range_search']) && $parms['operator'] == '=' && $type != 'int') {
// 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 = array($field_value - 0.01, $field_value + 0.01);
$operator = 'between';
}
}
if ($db->supports("case_sensitive") && 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 ";
}
//Here we make a last attempt to determine the field type if possible
if (empty($type) && isset($parms['db_field']) && isset($parms['db_field'][0]) && isset($this->seed->field_defs[$parms['db_field'][0]]['type'])) {
$type = $this->seed->field_defs[$parms['db_field'][0]]['type'];
}
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} " . $this->seed->db->quoted($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 {
//Bug#37087: Re-write our sub-query to it is executed first and contents stored in a derived table to avoid mysql executing the query
//outside in. Additional details: http://bugs.mysql.com/bug.php?id=9021
$selectCol = ' * ';
//use the select column in the subquery if it exists
if (!empty($parms['subquery'])) {
$selectCol = $this->getSelectCol($parms['subquery']);
}
$where .= "{$db_field} {$in} (select {$selectCol} from ({$parms['subquery']} " . $this->seed->db->quoted($field_value . '%') . ") {$field}_derived)";
}
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;
}
// If it is a unified search and if the search contains more then 1 word (contains space)
// and if it's the last element from db_field (so we do the concat only once, not for every db_field element)
// we concat the db_field array() (both original, and in reverse order) and search for the whole string in it
if ($UnifiedSearch && strpos($field_value, ' ') !== false && strpos($db_field, $parms['db_field'][count($parms['db_field']) - 1]) !== false) {
// Get the table name used for concat
$concat_table = explode('.', $db_field);
$concat_table = $concat_table[0];
// Get the fields for concatenating
$concat_fields = $parms['db_field'];
// If db_fields (e.g. contacts.first_name) contain table name, need to remove it
for ($i = 0; $i < count($concat_fields); $i++) {
if (strpos($concat_fields[$i], $concat_table) !== false) {
$concat_fields[$i] = substr($concat_fields[$i], strlen($concat_table) + 1);
}
}
// Concat the fields and search for the value
$where .= $this->seed->db->concat($concat_table, $concat_fields) . " LIKE " . $this->seed->db->quoted($field_value . $like_char);
$where .= ' OR ' . $this->seed->db->concat($concat_table, array_reverse($concat_fields)) . " LIKE " . $this->seed->db->quoted($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 " . $this->seed->db->quoted(sql_like_string($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 . " = " . $db->quoteType($type, $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':
if (!is_array($field_value)) {
$field_value = explode('<>', $field_value);
}
$field_value[0] = $db->quoteType($type, $field_value[0]);
$field_value[1] = $db->quoteType($type, $field_value[1]);
$where .= "({$db_field} >= {$field_value[0]} AND {$db_field} <= {$field_value[1]})";
break;
case 'date_not_equal':
if (!is_array($field_value)) {
$field_value = explode('<>', $field_value);
}
$field_value[0] = $db->quoteType($type, $field_value[0]);
$field_value[1] = $db->quoteType($type, $field_value[1]);
$where .= "({$db_field} IS NULL OR {$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':
$field_value = $db->quoteType($type, $field_value);
$where .= "({$db_field} IS NULL OR {$db_field} != {$field_value})";
break;
case 'greater_than':
$field_value = $db->quoteType($type, $field_value);
$where .= "{$db_field} > {$field_value}";
break;
case 'greater_than_equals':
$field_value = $db->quoteType($type, $field_value);
$where .= "{$db_field} >= {$field_value}";
break;
case 'less_than':
$field_value = $db->quoteType($type, $field_value);
$where .= "{$db_field} < {$field_value}";
break;
case 'less_than_equals':
$field_value = $db->quoteType($type, $field_value);
$where .= "{$db_field} <= {$field_value}";
break;
case 'next_7_days':
case 'last_7_days':
case 'last_month':
case 'this_month':
case 'next_month':
case 'last_30_days':
case 'next_30_days':
case 'this_year':
case 'last_year':
case 'next_year':
if (!empty($field) && !empty($this->seed->field_name_map[$field]['type'])) {
$where .= $this->parseDateExpression(strtolower($operator), $db_field, $this->seed->field_name_map[$field]['type']);
} else {
$where .= $this->parseDateExpression(strtolower($operator), $db_field);
}
break;
case 'isnull':
$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);
}
}
}
}
$GLOBALS['log']->debug('SearchForm2.generateSearchWhere, where_clauses: ' . print_r($where_clauses, TRUE));
return $where_clauses;
}
function save($check_notify = false)
{
//US DOLLAR
if (isset($this->amount) && !empty($this->amount)) {
$currency = new Currency();
$currency->retrieve($this->currency_id);
$this->amount_usdollar = $currency->convertToDollar($this->amount);
}
// Bug53301
if ($this->campaign_type != 'NewsLetter') {
$this->frequency = '';
}
return parent::save($check_notify);
}
function getModuleField($module, $fieldname, $aow_field, $view = 'EditView', $value = '', $alt_type = '', $currency_id = '', $params = array())
{
global $current_language, $app_strings, $app_list_strings, $current_user, $beanFiles, $beanList;
// use the mod_strings for this module
$mod_strings = return_module_language($current_language, $module);
// set the filename for this control
$file = create_cache_directory('modules/AOW_WorkFlow/') . $module . $view . $alt_type . $fieldname . '.tpl';
$displayParams = array();
if (!is_file($file) || inDeveloperMode() || !empty($_SESSION['developerMode'])) {
if (!isset($vardef)) {
require_once $beanFiles[$beanList[$module]];
$focus = new $beanList[$module]();
$vardef = $focus->getFieldDefinition($fieldname);
}
// Bug: check for AOR value SecurityGroups value missing
if (stristr($fieldname, 'securitygroups') != false && empty($vardef)) {
require_once $beanFiles[$beanList['SecurityGroups']];
$module = 'SecurityGroups';
$focus = new $beanList[$module]();
$vardef = $focus->getFieldDefinition($fieldname);
}
//$displayParams['formName'] = 'EditView';
// if this is the id relation field, then don't have a pop-up selector.
if ($vardef['type'] == 'relate' && $vardef['id_name'] == $vardef['name']) {
$vardef['type'] = 'varchar';
}
if (isset($vardef['precision'])) {
unset($vardef['precision']);
}
//$vardef['precision'] = $locale->getPrecedentPreference('default_currency_significant_digits', $current_user);
//TODO Fix datetimecomebo
//temp work around
if ($vardef['type'] == 'datetimecombo') {
$vardef['type'] = 'datetime';
}
// trim down textbox display
if ($vardef['type'] == 'text') {
$vardef['rows'] = 2;
$vardef['cols'] = 32;
}
// create the dropdowns for the parent type fields
if ($vardef['type'] == 'parent_type') {
$vardef['type'] = 'enum';
}
if ($vardef['type'] == 'link') {
$vardef['type'] = 'relate';
$vardef['rname'] = 'name';
$vardef['id_name'] = $vardef['name'] . '_id';
if ((!isset($vardef['module']) || $vardef['module'] == '') && $focus->load_relationship($vardef['name'])) {
$relName = $vardef['name'];
$vardef['module'] = $focus->{$relName}->getRelatedModuleName();
}
}
//check for $alt_type
if ($alt_type != '') {
$vardef['type'] = $alt_type;
}
// remove the special text entry field function 'getEmailAddressWidget'
if (isset($vardef['function']) && ($vardef['function'] == 'getEmailAddressWidget' || $vardef['function']['name'] == 'getEmailAddressWidget')) {
unset($vardef['function']);
}
if (isset($vardef['name']) && ($vardef['name'] == 'date_entered' || $vardef['name'] == 'date_modified')) {
$vardef['name'] = 'aow_temp_date';
}
// load SugarFieldHandler to render the field tpl file
static $sfh;
if (!isset($sfh)) {
require_once 'include/SugarFields/SugarFieldHandler.php';
$sfh = new SugarFieldHandler();
}
$contents = $sfh->displaySmarty('fields', $vardef, $view, $displayParams);
// Remove all the copyright comments
$contents = preg_replace('/\\{\\*[^\\}]*?\\*\\}/', '', $contents);
if ($view == 'EditView' && ($vardef['type'] == 'relate' || $vardef['type'] == 'parent')) {
$contents = str_replace('"' . $vardef['id_name'] . '"', '{/literal}"{$fields.' . $vardef['name'] . '.id_name}"{literal}', $contents);
$contents = str_replace('"' . $vardef['name'] . '"', '{/literal}"{$fields.' . $vardef['name'] . '.name}"{literal}', $contents);
}
// hack to disable one of the js calls in this control
if (isset($vardef['function']) && ($vardef['function'] == 'getCurrencyDropDown' || $vardef['function']['name'] == 'getCurrencyDropDown')) {
$contents .= "{literal}<script>function CurrencyConvertAll() { return; }</script>{/literal}";
}
// Save it to the cache file
if ($fh = @sugar_fopen($file, 'w')) {
fputs($fh, $contents);
fclose($fh);
}
}
// Now render the template we received
$ss = new Sugar_Smarty();
// Create Smarty variables for the Calendar picker widget
global $timedate;
$time_format = $timedate->get_user_time_format();
$date_format = $timedate->get_cal_date_format();
$ss->assign('USER_DATEFORMAT', $timedate->get_user_date_format());
$ss->assign('TIME_FORMAT', $time_format);
$time_separator = ":";
$match = array();
if (preg_match('/\\d+([^\\d])\\d+([^\\d]*)/s', $time_format, $match)) {
$time_separator = $match[1];
}
$t23 = strpos($time_format, '23') !== false ? '%H' : '%I';
if (!isset($match[2]) || $match[2] == '') {
$ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M");
} else {
$pm = $match[2] == "pm" ? "%P" : "%p";
$ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M" . $pm);
}
$ss->assign('CALENDAR_FDOW', $current_user->get_first_day_of_week());
// populate the fieldlist from the vardefs
$fieldlist = array();
if (!isset($focus) || !$focus instanceof SugarBean) {
require_once $beanFiles[$beanList[$module]];
}
$focus = new $beanList[$module]();
// create the dropdowns for the parent type fields
$vardefFields = $focus->getFieldDefinitions();
if (isset($vardefFields[$fieldname]['type']) && $vardefFields[$fieldname]['type'] == 'parent_type') {
$focus->field_defs[$fieldname]['options'] = $focus->field_defs[$vardefFields[$fieldname]['group']]['options'];
}
foreach ($vardefFields as $name => $properties) {
$fieldlist[$name] = $properties;
// fill in enums
if (isset($fieldlist[$name]['options']) && is_string($fieldlist[$name]['options']) && isset($app_list_strings[$fieldlist[$name]['options']])) {
$fieldlist[$name]['options'] = $app_list_strings[$fieldlist[$name]['options']];
} elseif (isset($fieldlist[$name]['options']) && is_string($fieldlist[$name]['options']) && isset($mod_strings[$fieldlist[$name]['options']])) {
$fieldlist[$name]['options'] = $mod_strings[$fieldlist[$name]['options']];
}
// Bug 22730: make sure all enums have the ability to select blank as the default value.
if (!isset($fieldlist[$name]['options'][''])) {
$fieldlist[$name]['options'][''] = '';
}
}
// fill in function return values
if (!in_array($fieldname, array('email1', 'email2'))) {
if (!empty($fieldlist[$fieldname]['function']['returns']) && $fieldlist[$fieldname]['function']['returns'] == 'html') {
$function = $fieldlist[$fieldname]['function']['name'];
// include various functions required in the various vardefs
if (isset($fieldlist[$fieldname]['function']['include']) && is_file($fieldlist[$fieldname]['function']['include'])) {
require_once $fieldlist[$fieldname]['function']['include'];
}
$_REQUEST[$fieldname] = $value;
$value = $function($focus, $fieldname, $value, $view);
$value = str_ireplace($fieldname, $aow_field, $value);
}
}
if (isset($fieldlist[$fieldname]['type']) && $fieldlist[$fieldname]['type'] == 'link') {
$fieldlist[$fieldname]['id_name'] = $fieldlist[$fieldname]['name'] . '_id';
if ((!isset($fieldlist[$fieldname]['module']) || $fieldlist[$fieldname]['module'] == '') && $focus->load_relationship($fieldlist[$fieldname]['name'])) {
$relName = $fieldlist[$fieldname]['name'];
$fieldlist[$fieldname]['module'] = $focus->{$relName}->getRelatedModuleName();
}
}
if (isset($fieldlist[$fieldname]['name']) && ($fieldlist[$fieldname]['name'] == 'date_entered' || $fieldlist[$fieldname]['name'] == 'date_modified')) {
$fieldlist[$fieldname]['name'] = 'aow_temp_date';
$fieldlist['aow_temp_date'] = $fieldlist[$fieldname];
$fieldname = 'aow_temp_date';
}
$quicksearch_js = '';
if (isset($fieldlist[$fieldname]['id_name']) && $fieldlist[$fieldname]['id_name'] != '' && $fieldlist[$fieldname]['id_name'] != $fieldlist[$fieldname]['name']) {
$rel_value = $value;
require_once "include/TemplateHandler/TemplateHandler.php";
$template_handler = new TemplateHandler();
$quicksearch_js = $template_handler->createQuickSearchCode($fieldlist, $fieldlist, $view);
$quicksearch_js = str_replace($fieldname, $aow_field . '_display', $quicksearch_js);
$quicksearch_js = str_replace($fieldlist[$fieldname]['id_name'], $aow_field, $quicksearch_js);
echo $quicksearch_js;
if (isset($fieldlist[$fieldname]['module']) && $fieldlist[$fieldname]['module'] == 'Users') {
$rel_value = get_assigned_user_name($value);
} else {
if (isset($fieldlist[$fieldname]['module'])) {
require_once $beanFiles[$beanList[$fieldlist[$fieldname]['module']]];
$rel_focus = new $beanList[$fieldlist[$fieldname]['module']]();
$rel_focus->retrieve($value);
if (isset($fieldlist[$fieldname]['rname']) && $fieldlist[$fieldname]['rname'] != '') {
$relDisplayField = $fieldlist[$fieldname]['rname'];
} else {
$relDisplayField = 'name';
}
$rel_value = $rel_focus->{$relDisplayField};
}
}
$fieldlist[$fieldlist[$fieldname]['id_name']]['value'] = $value;
$fieldlist[$fieldname]['value'] = $rel_value;
$fieldlist[$fieldname]['id_name'] = $aow_field;
$fieldlist[$fieldlist[$fieldname]['id_name']]['name'] = $aow_field;
$fieldlist[$fieldname]['name'] = $aow_field . '_display';
} else {
if (isset($fieldlist[$fieldname]['type']) && $view == 'DetailView' && ($fieldlist[$fieldname]['type'] == 'datetimecombo' || $fieldlist[$fieldname]['type'] == 'datetime' || $fieldlist[$fieldname]['type'] == 'date')) {
$value = $focus->convertField($value, $fieldlist[$fieldname]);
if (!empty($params['date_format']) && isset($params['date_format'])) {
$convert_format = "Y-m-d H:i:s";
if ($fieldlist[$fieldname]['type'] == 'date') {
$convert_format = "Y-m-d";
}
$fieldlist[$fieldname]['value'] = $timedate->to_display($value, $convert_format, $params['date_format']);
} else {
$fieldlist[$fieldname]['value'] = $timedate->to_display_date_time($value, true, true);
}
$fieldlist[$fieldname]['name'] = $aow_field;
} else {
if (isset($fieldlist[$fieldname]['type']) && ($fieldlist[$fieldname]['type'] == 'datetimecombo' || $fieldlist[$fieldname]['type'] == 'datetime' || $fieldlist[$fieldname]['type'] == 'date')) {
$value = $focus->convertField($value, $fieldlist[$fieldname]);
$fieldlist[$fieldname]['value'] = $timedate->to_display_date($value);
//$fieldlist[$fieldname]['value'] = $timedate->to_display_date_time($value, true, true);
//$fieldlist[$fieldname]['value'] = $value;
$fieldlist[$fieldname]['name'] = $aow_field;
} else {
$fieldlist[$fieldname]['value'] = $value;
$fieldlist[$fieldname]['name'] = $aow_field;
}
}
}
if (isset($fieldlist[$fieldname]['type']) && $fieldlist[$fieldname]['type'] == 'currency' && $view != 'EditView') {
static $sfh;
if (!isset($sfh)) {
require_once 'include/SugarFields/SugarFieldHandler.php';
$sfh = new SugarFieldHandler();
}
if ($currency_id != '' && !stripos($fieldname, '_USD')) {
$userCurrencyId = $current_user->getPreference('currency');
if ($currency_id != $userCurrencyId) {
$currency = new Currency();
$currency->retrieve($currency_id);
$value = $currency->convertToDollar($value);
$currency->retrieve($userCurrencyId);
$value = $currency->convertFromDollar($value);
}
}
$parentfieldlist[strtoupper($fieldname)] = $value;
return $sfh->displaySmarty($parentfieldlist, $fieldlist[$fieldname], 'ListView', $displayParams);
}
$ss->assign("QS_JS", $quicksearch_js);
$ss->assign("fields", $fieldlist);
$ss->assign("form_name", $view);
$ss->assign("bean", $focus);
// add in any additional strings
$ss->assign("MOD", $mod_strings);
$ss->assign("APP", $app_strings);
//$return = str_replace($fieldname,$ss->fetch($file));
return $ss->fetch($file);
}
function getEditFieldHTML($module, $fieldname, $aow_field, $view = 'EditView', $id = '', $alt_type = '', $currency_id = '')
{
global $current_language, $app_strings, $app_list_strings, $current_user, $beanFiles, $beanList;
$bean = BeanFactory::getBean($module, $id);
if (!checkAccess($bean)) {
return false;
}
$value = getFieldValueFromModule($fieldname, $module, $id);
// use the mod_strings for this module
$mod_strings = return_module_language($current_language, $module);
// set the filename for this control
$file = create_cache_directory('include/InlineEditing/') . $module . $view . $alt_type . $fieldname . '.tpl';
if (!is_file($file) || inDeveloperMode() || !empty($_SESSION['developerMode'])) {
if (!isset($vardef)) {
require_once $beanFiles[$beanList[$module]];
$focus = new $beanList[$module]();
$vardef = $focus->getFieldDefinition($fieldname);
}
$displayParams = array();
//$displayParams['formName'] = 'EditView';
// if this is the id relation field, then don't have a pop-up selector.
if ($vardef['type'] == 'relate' && $vardef['id_name'] == $vardef['name']) {
$vardef['type'] = 'varchar';
}
if (isset($vardef['precision'])) {
unset($vardef['precision']);
}
//$vardef['precision'] = $locale->getPrecedentPreference('default_currency_significant_digits', $current_user);
//TODO Fix datetimecomebo
//temp work around
if ($vardef['type'] == 'datetime') {
$vardef['type'] = 'datetimecombo';
}
// trim down textbox display
if ($vardef['type'] == 'text') {
$vardef['rows'] = 2;
$vardef['cols'] = 32;
}
// create the dropdowns for the parent type fields
if ($vardef['type'] == 'parent_type') {
$vardef['type'] = 'enum';
}
if ($vardef['type'] == 'link') {
$vardef['type'] = 'relate';
$vardef['rname'] = 'name';
$vardef['id_name'] = $vardef['name'] . '_id';
if ((!isset($vardef['module']) || $vardef['module'] == '') && $focus->load_relationship($vardef['name'])) {
$vardef['module'] = $focus->{$vardef['name']}->getRelatedModuleName();
}
}
//check for $alt_type
if ($alt_type != '') {
$vardef['type'] = $alt_type;
}
// remove the special text entry field function 'getEmailAddressWidget'
if (isset($vardef['function']) && ($vardef['function'] == 'getEmailAddressWidget' || $vardef['function']['name'] == 'getEmailAddressWidget')) {
unset($vardef['function']);
}
if (isset($vardef['name']) && $vardef['name'] == 'date_modified') {
$vardef['name'] = 'aow_temp_date';
}
// load SugarFieldHandler to render the field tpl file
static $sfh;
if (!isset($sfh)) {
require_once 'include/SugarFields/SugarFieldHandler.php';
$sfh = new SugarFieldHandler();
}
$contents = $sfh->displaySmarty('fields', $vardef, $view, $displayParams);
// Remove all the copyright comments
$contents = preg_replace('/\\{\\*[^\\}]*?\\*\\}/', '', $contents);
// remove extra wrong javascript which breaks auto complete on flexi relationship parent fields
$contents = preg_replace("/<script language=\"javascript\">if\\(typeof sqs_objects == \\'undefined\\'\\){var sqs_objects = new Array;}sqs_objects\\[\\'EditView_parent_name\\'\\].*?<\\/script>/", "", $contents);
if ($view == 'EditView' && ($vardef['type'] == 'relate' || $vardef['type'] == 'parent')) {
$contents = str_replace('"' . $vardef['id_name'] . '"', '{/literal}"{$fields.' . $vardef['name'] . '.id_name}"{literal}', $contents);
$contents = str_replace('"' . $vardef['name'] . '"', '{/literal}"{$fields.' . $vardef['name'] . '.name}"{literal}', $contents);
// regex below fixes button javascript for flexi relationship
if ($vardef['type'] == 'parent') {
$contents = str_replace("onclick='open_popup(document.{\$form_name}.parent_type.value, 600, 400, \"\", true, false, {literal}{\"call_back_function\":\"set_return\",\"form_name\":\"EditView\",\"field_to_name_array\":{\"id\":{/literal}\"{\$fields.parent_name.id_name}", "onclick='open_popup(document.{\$form_name}.parent_type.value, 600, 400, \"\", true, false, {literal}{\"call_back_function\":\"set_return\",\"form_name\":\"EditView\",\"field_to_name_array\":{\"id\":{/literal}\"parent_id", $contents);
}
}
// hack to disable one of the js calls in this control
if (isset($vardef['function']) && ($vardef['function'] == 'getCurrencyDropDown' || $vardef['function']['name'] == 'getCurrencyDropDown')) {
$contents .= "{literal}<script>function CurrencyConvertAll() { return; }</script>{/literal}";
}
// Save it to the cache file
if ($fh = @sugar_fopen($file, 'w')) {
fputs($fh, $contents);
fclose($fh);
}
}
// Now render the template we received
$ss = new Sugar_Smarty();
// Create Smarty variables for the Calendar picker widget
global $timedate;
$time_format = $timedate->get_user_time_format();
$date_format = $timedate->get_cal_date_format();
$ss->assign('USER_DATEFORMAT', $timedate->get_user_date_format());
$ss->assign('TIME_FORMAT', $time_format);
$time_separator = ":";
$match = array();
if (preg_match('/\\d+([^\\d])\\d+([^\\d]*)/s', $time_format, $match)) {
$time_separator = $match[1];
}
$t23 = strpos($time_format, '23') !== false ? '%H' : '%I';
if (!isset($match[2]) || $match[2] == '') {
$ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M");
} else {
$pm = $match[2] == "pm" ? "%P" : "%p";
$ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M" . $pm);
}
$ss->assign('CALENDAR_FDOW', $current_user->get_first_day_of_week());
$fieldlist = array();
if (!isset($focus) || !$focus instanceof SugarBean) {
require_once $beanFiles[$beanList[$module]];
}
$focus = new $beanList[$module]();
// create the dropdowns for the parent type fields
$vardefFields[$fieldname] = $focus->field_defs[$fieldname];
if ($vardefFields[$fieldname]['type'] == 'parent') {
$focus->field_defs[$fieldname]['options'] = $focus->field_defs[$vardefFields[$fieldname]['group']]['options'];
}
foreach ($vardefFields as $name => $properties) {
$fieldlist[$name] = $properties;
// fill in enums
if (isset($fieldlist[$name]['options']) && is_string($fieldlist[$name]['options']) && isset($app_list_strings[$fieldlist[$name]['options']])) {
$fieldlist[$name]['options'] = $app_list_strings[$fieldlist[$name]['options']];
} elseif (isset($fieldlist[$name]['options']) && is_string($fieldlist[$name]['options']) && isset($mod_strings[$fieldlist[$name]['options']])) {
$fieldlist[$name]['options'] = $mod_strings[$fieldlist[$name]['options']];
}
}
// fill in function return values
if (!in_array($fieldname, array('email1', 'email2'))) {
if (!empty($fieldlist[$fieldname]['function']['returns']) && $fieldlist[$fieldname]['function']['returns'] == 'html') {
$function = $fieldlist[$fieldname]['function']['name'];
// include various functions required in the various vardefs
if (isset($fieldlist[$fieldname]['function']['include']) && is_file($fieldlist[$fieldname]['function']['include'])) {
require_once $fieldlist[$fieldname]['function']['include'];
}
$_REQUEST[$fieldname] = $value;
$value = $function($focus, $fieldname, $value, $view);
$value = str_ireplace($fieldname, $aow_field, $value);
}
}
if ($fieldlist[$fieldname]['type'] == 'link') {
$fieldlist[$fieldname]['id_name'] = $fieldlist[$fieldname]['name'] . '_id';
if ((!isset($fieldlist[$fieldname]['module']) || $fieldlist[$fieldname]['module'] == '') && $focus->load_relationship($fieldlist[$fieldname]['name'])) {
$relateField = $fieldlist[$fieldname]['name'];
$fieldlist[$fieldname]['module'] = $focus->{$relateField}->getRelatedModuleName();
}
}
if ($fieldlist[$fieldname]['type'] == 'parent') {
$fieldlist['parent_id']['name'] = 'parent_id';
}
if (isset($fieldlist[$fieldname]['name']) && $fieldlist[$fieldname]['name'] == 'date_modified') {
$fieldlist[$fieldname]['name'] = 'aow_temp_date';
$fieldlist['aow_temp_date'] = $fieldlist[$fieldname];
$fieldname = 'aow_temp_date';
}
if (isset($fieldlist[$fieldname]['id_name']) && $fieldlist[$fieldname]['id_name'] != '' && $fieldlist[$fieldname]['id_name'] != $fieldlist[$fieldname]['name']) {
if ($value) {
$relateIdField = $fieldlist[$fieldname]['id_name'];
$rel_value = $bean->{$relateIdField};
}
$fieldlist[$fieldlist[$fieldname]['id_name']]['value'] = $rel_value;
$fieldlist[$fieldname]['value'] = $value;
$fieldlist[$fieldname]['id_name'] = $aow_field;
$fieldlist[$fieldname]['name'] = $aow_field . '_display';
} else {
if (isset($fieldlist[$fieldname]['type']) && ($fieldlist[$fieldname]['type'] == 'datetimecombo' || $fieldlist[$fieldname]['type'] == 'datetime')) {
$value = $focus->convertField($value, $fieldlist[$fieldname]);
if (!$value) {
$value = date($timedate->get_date_time_format());
}
$fieldlist[$fieldname]['name'] = $aow_field;
$fieldlist[$fieldname]['value'] = $value;
} else {
if (isset($fieldlist[$fieldname]['type']) && $fieldlist[$fieldname]['type'] == 'date') {
$value = $focus->convertField($value, $fieldlist[$fieldname]);
$fieldlist[$fieldname]['name'] = $aow_field;
if (empty($value) == "") {
$value = str_replace("%", "", date($date_format));
}
$fieldlist[$fieldname]['value'] = $value;
} else {
$fieldlist[$fieldname]['value'] = $value;
$fieldlist[$fieldname]['name'] = $aow_field;
}
}
}
if ($fieldlist[$fieldname]['type'] == 'currency' && $view != 'EditView') {
static $sfh;
if (!isset($sfh)) {
require_once 'include/SugarFields/SugarFieldHandler.php';
$sfh = new SugarFieldHandler();
}
if ($currency_id != '' && !stripos($fieldname, '_USD')) {
$userCurrencyId = $current_user->getPreference('currency');
if ($currency_id != $userCurrencyId) {
$currency = new Currency();
$currency->retrieve($currency_id);
$value = $currency->convertToDollar($value);
$currency->retrieve($userCurrencyId);
$value = $currency->convertFromDollar($value);
}
}
$parentfieldlist[strtoupper($fieldname)] = $value;
return $sfh->displaySmarty($parentfieldlist, $fieldlist[$fieldname], 'ListView', $displayParams);
}
$ss->assign("fields", $fieldlist);
$ss->assign("form_name", $view);
$ss->assign("bean", $focus);
$ss->assign("MOD", $mod_strings);
$ss->assign("APP", $app_strings);
return json_encode($ss->fetch($file));
}
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;
}
function update_currency_id($fromid, $toid)
{
$idequals = '';
require_once 'modules/Currencies/Currency.php';
$currency = new Currency();
$currency->retrieve($toid);
foreach ($fromid as $f) {
if (!empty($idequals)) {
$idequals .= ' or ';
}
$idequals .= "currency_id='{$f}'";
}
if (!empty($idequals)) {
$query = "select price, id from prices where (" . $idequals . ") and deleted=0;";
$result = $this->db->query($query);
while ($row = $this->db->fetchByAssoc($result)) {
$query = "update prices set currency_id='" . $currency->id . "', price_usdollar='" . $currency->convertToDollar($row['price']) . "' where id='" . $row['id'] . "';";
$this->db->query($query);
}
}
}
