2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
6 * This program is free software; you can redistribute it and/or modify it under
7 * the terms of the GNU Affero General Public License version 3 as published by the
8 * Free Software Foundation with the addition of the following permission added
9 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
11 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
18 * You should have received a copy of the GNU Affero General Public License along with
19 * this program; if not, see http://www.gnu.org/licenses or write to the Free
20 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
24 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
26 * The interactive user interfaces in modified source and object code versions
27 * of this program must display Appropriate Legal Notices, as required under
28 * Section 5 of the GNU Affero General Public License version 3.
30 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31 * these Appropriate Legal Notices must retain the display of the "Powered by
32 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
33 * technical reasons, the Appropriate Legal Notices must display the words
34 * "Powered by SugarCRM".
35 ********************************************************************************/
38 require_once('include/tabs.php');
39 require_once('include/ListView/ListViewSmarty.php');
40 require_once('include/TemplateHandler/TemplateHandler.php');
41 require_once('include/EditView/EditView2.php');
44 class SearchForm extends EditView{
47 var $action = 'index';
48 var $searchdefs = array();
49 var $listViewDefs = array();
53 var $view = 'SearchForm';
54 var $displayView = 'basic_search';
59 var $parsedView = 'basic';
62 var $displaySavedSearch = true;
63 //show the advanced tab
64 var $showAdvanced = true;
66 var $showBasic = true;
67 //array of custom tab to show declare in searchdefs (no custom tab if false)
68 var $showCustom = false;
71 // hide saved searches drop and down near the search button
72 var $showSavedSearchesOptions = true;
74 var $displayType = 'searchView';
81 public function SearchForm($seed, $module, $action = 'index', $options = array())
83 $this->th = new TemplateHandler();
84 $this->th->loadSmarty();
86 $this->module = $module;
87 $this->action = $action;
88 $this->tabs = array(array('title' => $GLOBALS['app_strings']['LNK_BASIC_SEARCH'],
89 'link' => $module . '|basic_search',
90 'key' => $module . '|basic_search',
93 array('title' => $GLOBALS['app_strings']['LNK_ADVANCED_SEARCH'],
94 'link' => $module . '|advanced_search',
95 'key' => $module . '|advanced_search',
97 'displayDiv' => 'display:none'),
99 $this->searchColumns = array () ;
100 $this->setOptions($options);
103 function setup($searchdefs, $searchFields = array(), $tpl, $displayView = 'basic_search', $listViewDefs = array()){
104 $this->searchdefs = $searchdefs[$this->module];
106 //used by advanced search
107 $this->listViewDefs = $listViewDefs;
108 $this->displayView = $displayView;
109 $this->view = $this->view.'_'.$displayView;
110 $tokens = explode('_', $this->displayView);
111 $this->parsedView = $tokens[0];
112 $this->searchFields = $searchFields[$this->module];
113 if($this->displayView != 'saved_views'){
114 $this->_build_field_defs();
118 // Setup the tab array.
119 $this->tabs = array();
120 if($this->showBasic){
122 $this->tabs[]=array('title' => $GLOBALS['app_strings']['LNK_BASIC_SEARCH'],
123 'link' => $this->module . '|basic_search',
124 'key' => $this->module . '|basic_search',
128 if($this->showAdvanced){
130 $this->tabs[]=array('title' => $GLOBALS['app_strings']['LNK_ADVANCED_SEARCH'],
131 'link' => $this->module . '|advanced_search',
132 'key' => $this->module . '|advanced_search',
133 'name' => 'advanced',
134 'displayDiv' => 'display:none');
136 if(isset($this->showCustom) && is_array($this->showCustom)){
137 foreach($this->showCustom as $v){
139 $this->tabs[]=array('title' => $GLOBALS['app_strings']["LNK_" . strtoupper($v)],
140 'link' => $this->module . '|' . $v,
141 'key' => $this->module . '|' . $v,
142 'name' => str_replace('_search','',$v),
143 'displayDiv' => 'display:none',);
148 function display($header = true){
149 global $theme, $timedate, $current_user;
153 $this->th->ss->assign('module', $this->module);
154 $this->th->ss->assign('action', $this->action);
155 $this->th->ss->assign('displayView', $this->displayView);
156 $this->th->ss->assign('APP', $GLOBALS['app_strings']);
157 //Show the tabs only if there is more than one
159 $this->th->ss->assign('TABS', $this->_displayTabs($this->module . '|' . $this->displayView));
161 $this->th->ss->assign('searchTableColumnCount',
162 ((isset($this->searchdefs['templateMeta']['maxColumns']) ? $this->searchdefs['templateMeta']['maxColumns'] : 2) * 2 ) - 1);
163 $this->th->ss->assign('fields', $this->fieldDefs);
164 $this->th->ss->assign('customFields', $this->customFieldDefs);
165 $this->th->ss->assign('formData', $this->formData);
166 $time_format = $timedate->get_user_time_format();
167 $this->th->ss->assign('TIME_FORMAT', $time_format);
168 $this->th->ss->assign('USER_DATEFORMAT', $timedate->get_user_date_format());
169 $this->th->ss->assign('CALENDAR_FDOW', $current_user->get_first_day_of_week());
171 $date_format = $timedate->get_cal_date_format();
172 $time_separator = ":";
173 if(preg_match('/\d+([^\d])\d+([^\d]*)/s', $time_format, $match)) {
174 $time_separator = $match[1];
176 // Create Smarty variables for the Calendar picker widget
177 $t23 = strpos($time_format, '23') !== false ? '%H' : '%I';
178 if(!isset($match[2]) || $match[2] == '') {
179 $this->th->ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M");
181 $pm = $match[2] == "pm" ? "%P" : "%p";
182 $this->th->ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M" . $pm);
184 $this->th->ss->assign('TIME_SEPARATOR', $time_separator);
186 //Show and hide the good tab form
187 foreach($this->tabs as $tabkey=>$viewtab){
188 $viewName=str_replace(array($this->module . '|','_search'),'',$viewtab['key']);
189 if(strpos($this->view,$viewName)!==false){
190 $this->tabs[$tabkey]['displayDiv']='';
191 //if this is advanced tab, use form with saved search sub form built in
192 if($viewName=='advanced'){
193 $this->tpl = 'SearchFormGenericAdvanced.tpl';
194 if ($this->action =='ListView') {
195 $this->th->ss->assign('DISPLAY_SEARCH_HELP', true);
197 $this->th->ss->assign('DISPLAY_SAVED_SEARCH', $this->displaySavedSearch);
198 $this->th->ss->assign('SAVED_SEARCH', $this->displaySavedSearch());
199 //this determines whether the saved search subform should be rendered open or not
200 if(isset($_REQUEST['showSSDIV']) && $_REQUEST['showSSDIV']=='yes'){
201 $this->th->ss->assign('SHOWSSDIV', 'yes');
202 $this->th->ss->assign('DISPLAYSS', '');
204 $this->th->ss->assign('SHOWSSDIV', 'no');
205 $this->th->ss->assign('DISPLAYSS', 'display:none');
209 $this->tabs[$tabkey]['displayDiv']='display:none';
214 $this->th->ss->assign('TAB_ARRAY', $this->tabs);
217 if ( isset($this->searchdefs['templateMeta']['widths'])
218 && isset($this->searchdefs['templateMeta']['maxColumns'])) {
219 $totalWidth = ( $this->searchdefs['templateMeta']['widths']['label'] +
220 $this->searchdefs['templateMeta']['widths']['field'] ) *
221 $this->searchdefs['templateMeta']['maxColumns'];
222 // redo the widths in case they are too big
223 if ( $totalWidth > 100 ) {
224 $resize = 100 / $totalWidth;
225 $this->searchdefs['templateMeta']['widths']['label'] =
226 $this->searchdefs['templateMeta']['widths']['label'] * $resize;
227 $this->searchdefs['templateMeta']['widths']['field'] =
228 $this->searchdefs['templateMeta']['widths']['field'] * $resize;
231 $this->th->ss->assign('templateMeta', $this->searchdefs['templateMeta']);
232 $this->th->ss->assign('HAS_ADVANCED_SEARCH', !empty($this->searchdefs['layout']['advanced_search']));
233 $this->th->ss->assign('displayType', $this->displayType);
234 // return the form of the shown tab only
235 if($this->showSavedSearchesOptions){
236 $this->th->ss->assign('SAVED_SEARCHES_OPTIONS', $this->displaySavedSearchSelect());
238 if ($this->module == 'Documents'){
239 $this->th->ss->assign('DOCUMENTS_MODULE', true);
242 $return_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchForm_'.$this->parsedView, $this->locateFile($this->tpl));
245 $this->th->ss->assign('return_txt', $return_txt);
246 $header_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchFormHeader', $this->locateFile('header.tpl'));
247 //pass in info to render the select dropdown below the form
248 $footer_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchFormFooter', $this->locateFile('footer.tpl'));
249 $return_txt = $header_txt.$footer_txt;
256 * @param array $options
257 * @return SearchForm2
259 public function setOptions($options = null)
262 'locator_class' => 'FileLocator',
263 'locator_class_params' => array(
265 'custom/modules/' . $this->module . '/tpls/SearchForm',
266 'modules/' . $this->module . '/tpls/SearchForm',
267 'custom/include/SearchForm/tpls',
268 'include/SearchForm/tpls'
273 $this->options = empty($options) ? $defaults : $options;
281 public function getOptions()
283 return $this->options;
288 * Locate a file in the custom or stock folders. Look in the custom folders first.
290 * @param string $file The file we are looking for
291 * @return bool|string If the file is found return the path, False if not
293 protected function locateFile($file)
295 $paths = isset($this->options['locator_class_params'])?$this->options['locator_class_params'][0]:array();
296 foreach ($paths as $path) {
297 if (is_file($path . '/' . $file)) {
298 return $path . '/' . $file;
305 function displaySavedSearch()
307 $savedSearch = new SavedSearch($this->listViewDefs[$this->module], $this->lv->data['pageData']['ordering']['orderBy'], $this->lv->data['pageData']['ordering']['sortOrder']);
308 return $savedSearch->getForm($this->module, false);
312 function displaySavedSearchSelect(){
313 $savedSearch = new SavedSearch($this->listViewDefs[$this->module], $this->lv->data['pageData']['ordering']['orderBy'], $this->lv->data['pageData']['ordering']['sortOrder']);
314 return $savedSearch->getSelect($this->module);
320 * displays the tabs (top of the search form)
322 * @param string $currentKey key in $this->tabs to show as the current tab
324 * @return string html
326 function _displayTabs($currentKey)
328 if(isset($_REQUEST['saved_search_select']) && $_REQUEST['saved_search_select']!='_none') {
329 $saved_search=loadBean('SavedSearch');
330 $saved_search->retrieveSavedSearch($_REQUEST['saved_search_select']);
334 if(!empty($_REQUEST['displayColumns']))
335 $str .= 'SUGAR.savedViews.displayColumns = "' . $_REQUEST['displayColumns'] . '";';
336 elseif(isset($saved_search->contents['displayColumns']) && !empty($saved_search->contents['displayColumns']))
337 $str .= 'SUGAR.savedViews.displayColumns = "' . $saved_search->contents['displayColumns'] . '";';
338 if(!empty($_REQUEST['hideTabs']))
339 $str .= 'SUGAR.savedViews.hideTabs = "' . $_REQUEST['hideTabs'] . '";';
340 elseif(isset($saved_search->contents['hideTabs']) && !empty($saved_search->contents['hideTabs']))
341 $str .= 'SUGAR.savedViews.hideTabs = "' . $saved_search->contents['hideTabs'] . '";';
342 if(!empty($_REQUEST['orderBy']))
343 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $_REQUEST['orderBy'] . '";';
344 elseif(isset($saved_search->contents['orderBy']) && !empty($saved_search->contents['orderBy']))
345 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $saved_search->contents['orderBy'] . '";';
346 if(!empty($_REQUEST['sortOrder']))
347 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $_REQUEST['sortOrder'] . '";';
348 elseif(isset($saved_search->contents['sortOrder']) && !empty($saved_search->contents['sortOrder']))
349 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $saved_search->contents['sortOrder'] . '";';
359 function _build_field_defs(){
360 $this->formData = array();
361 $this->fieldDefs = array();
362 foreach($this->searchdefs['layout'][$this->displayView] as $data){
364 //Fields may be listed but disabled so that when they are enabled, they have the correct custom display data.
365 if (isset($data['enabled']) && $data['enabled'] == false)
367 $data['name'] = $data['name'].'_'.$this->parsedView;
368 $this->formData[] = array('field' => $data);
369 $this->fieldDefs[$data['name']]= $data;
371 $this->formData[] = array('field' => array('name'=>$data.'_'.$this->parsedView));
376 $this->seed->fill_in_additional_detail_fields();
377 // hack to make the employee status field for the Users/Employees module display correctly
378 if($this->seed->object_name == 'Employee' || $this->seed->object_name == 'User'){
379 $this->seed->field_defs['employee_status']['type'] = 'enum';
380 $this->seed->field_defs['employee_status']['massupdate'] = true;
381 $this->seed->field_defs['employee_status']['options'] = 'employee_status_dom';
382 unset($this->seed->field_defs['employee_status']['function']);
385 foreach($this->seed->toArray() as $name => $value) {
386 $fvName = $name.'_'.$this->parsedView;
387 if(!empty($this->fieldDefs[$fvName]))
388 $this->fieldDefs[$fvName] = array_merge($this->seed->field_defs[$name], $this->fieldDefs[$fvName]);
390 $this->fieldDefs[$fvName] = $this->seed->field_defs[$name];
391 $this->fieldDefs[$fvName]['name'] = $this->fieldDefs[$fvName]['name'].'_'.$this->parsedView;
394 if(isset($this->fieldDefs[$fvName]['type']) && $this->fieldDefs[$fvName]['type'] == 'relate') {
395 if(isset($this->fieldDefs[$fvName]['id_name'])) {
396 $this->fieldDefs[$fvName]['id_name'] .= '_'.$this->parsedView;
400 if(isset($this->fieldDefs[$fvName]['options']) && isset($GLOBALS['app_list_strings'][$this->fieldDefs[$fvName]['options']]))
403 $this->fieldDefs[$fvName]['options'] = $GLOBALS['app_list_strings'][$this->fieldDefs[$fvName]['options']];
404 //Hack to add blanks for parent types on search views
405 //53131 - add blank option for SearchField options with def 'options_add_blank' set to true
406 if ($this->fieldDefs[$fvName]['type'] == "parent_type" || $this->fieldDefs[$fvName]['type'] == "parent" || (isset($this->searchFields[$name]['options_add_blank']) && $this->searchFields[$name]['options_add_blank']) )
408 if (!array_key_exists('', $this->fieldDefs[$fvName]['options'])) {
409 $this->fieldDefs[$fvName]['options'] =
410 array('' => '') + $this->fieldDefs[$fvName]['options'];
415 if(isset($this->fieldDefs[$fvName]['function'])) {
417 $this->fieldDefs[$fvName]['type']='multienum';
419 if(is_array($this->fieldDefs[$fvName]['function'])) {
420 $this->fieldDefs[$fvName]['function']['preserveFunctionValue']=true;
423 $function = $this->fieldDefs[$fvName]['function'];
425 if(is_array($function) && isset($function['name'])){
426 $function_name = $this->fieldDefs[$fvName]['function']['name'];
428 $function_name = $this->fieldDefs[$fvName]['function'];
431 if(!empty($this->fieldDefs[$fvName]['function']['returns']) && $this->fieldDefs[$fvName]['function']['returns'] == 'html'){
432 if(!empty($this->fieldDefs[$fvName]['function']['include'])){
433 require_once($this->fieldDefs[$fvName]['function']['include']);
435 $value = call_user_func($function_name, $this->seed, $name, $value, $this->view);
436 $this->fieldDefs[$fvName]['value'] = $value;
438 if(!isset($function['params']) || !is_array($function['params'])) {
439 $this->fieldDefs[$fvName]['options'] = call_user_func($function_name, $this->seed, $name, $value, $this->view);
441 $this->fieldDefs[$fvName]['options'] = call_user_func_array($function_name, $function['params']);
445 if(isset($this->fieldDefs[$name]['type']) && $this->fieldDefs[$fvName]['type'] == 'function'
446 && isset($this->fieldDefs[$fvName]['function_name']))
448 $value = $this->callFunction($this->fieldDefs[$fvName]);
449 $this->fieldDefs[$fvName]['value'] = $value;
452 $this->fieldDefs[$name]['value'] = $value;
455 if((!empty($_REQUEST[$fvName]) || (isset($_REQUEST[$fvName]) && $_REQUEST[$fvName] == '0'))
456 && empty($this->fieldDefs[$fvName]['function']['preserveFunctionValue'])) {
457 $value = $_REQUEST[$fvName];
458 $this->fieldDefs[$fvName]['value'] = $value;
469 * Populate the searchFields from an array
471 * @param array $array array to search through
472 * @param string $switchVar variable to use in switch statement
473 * @param bool $addAllBeanFields true to process at all bean fields
475 function populateFromArray(&$array, $switchVar = null, $addAllBeanFields = true) {
477 if((!empty($array['searchFormTab']) || !empty($switchVar)) && !empty($this->searchFields)) {
478 $arrayKeys = array_keys($array);
479 $searchFieldsKeys = array_keys($this->searchFields);
480 if(empty($switchVar)) $switchVar = $array['searchFormTab'];
481 //name of the search tab
482 $SearchName=str_replace('_search', '', $switchVar);
483 if($switchVar=='saved_views'){
484 foreach($this->searchFields as $name => $params) {
485 foreach($this->tabs as $tabName){
486 if(!empty($array[$name . '_' . $tabName['name']])) {
487 $this->searchFields[$name]['value'] = $array[$name . '_' . $tabName['name']];
488 if(empty($this->fieldDefs[$name . '_' . $tabName['name']]['value'])) $this->fieldDefs[$name . '_' . $tabName['name']]['value'] = $array[$name . '_' . $tabName['name']];
492 if($addAllBeanFields) {
493 foreach($this->seed->field_name_map as $key => $params) {
494 if(!in_array($key, $searchFieldsKeys)) {
495 foreach($this->tabs->name as $tabName){
496 if(in_array($key . '_' . $tabName['name'], $arrayKeys) ) {
497 $this->searchFields[$key] = array('query_type' => 'default',
498 'value' => $array[$key . '_' . $tabName['name']]);
506 $fromMergeRecords = isset($array['merge_module']);
508 foreach($this->searchFields as $name => $params) {
509 $long_name = $name.'_'.$SearchName;
510 /*nsingh 21648: Add additional check for bool values=0. empty() considers 0 to be empty Only repopulates if value is 0 or 1:( */
511 if (isset($array[$long_name]) && ( $array[$long_name] !== '' || (isset($this->fieldDefs[$long_name]['type']) && $this->fieldDefs[$long_name]['type'] == 'bool'&& ($array[$long_name]=='0' || $array[$long_name]=='1'))))
513 $this->searchFields[$name]['value'] = $array[$long_name];
514 if(empty($this->fieldDefs[$long_name]['value'])) {
515 $this->fieldDefs[$long_name]['value'] = $array[$long_name];
518 else if(!empty($array[$name]) && !$fromMergeRecords) // basic
520 $this->searchFields[$name]['value'] = $array[$name];
521 if(empty($this->fieldDefs[$long_name]['value'])) {
522 $this->fieldDefs[$long_name]['value'] = $array[$name];
526 if(!empty($params['enable_range_search']) && isset($this->searchFields[$name]['value']))
528 if(preg_match('/^range_(.*?)$/', $long_name, $match) && isset($array[$match[1].'_range_choice']))
530 $this->searchFields[$name]['operator'] = $array[$match[1].'_range_choice'];
534 if(!empty($params['is_date_field']) && isset($this->searchFields[$name]['value']))
537 // FG - bug 45287 - to db conversion is ok, but don't adjust timezone (not now), otherwise you'll jump to the day before (if at GMT-xx)
538 $date_value = $timedate->to_db_date($this->searchFields[$name]['value'], false);
539 $this->searchFields[$name]['value'] = $date_value == '' ? $this->searchFields[$name]['value'] : $date_value;
543 if((empty($array['massupdate']) || $array['massupdate'] == 'false') && $addAllBeanFields) {
544 foreach($this->seed->field_name_map as $key => $params) {
545 if($key != 'assigned_user_name' && $key != 'modified_by_name')
547 $long_name = $key.'_'.$SearchName;
549 if(in_array($key.'_'.$SearchName, $arrayKeys) && !in_array($key, $searchFieldsKeys))
551 $this->searchFields[$key] = array('query_type' => 'default', 'value' => $array[$long_name]);
553 if (!empty($params['type']) && $params['type'] == 'parent'
554 && !empty($params['type_name']) && !empty($this->searchFields[$key]['value']))
556 require_once('include/SugarFields/SugarFieldHandler.php');
557 $sfh = new SugarFieldHandler();
558 $sf = $sfh->getSugarField('Parent');
560 $this->searchFields[$params['type_name']] = array('query_type' => 'default',
561 'value' => $sf->getSearchInput($params['type_name'], $array));
564 if(empty($this->fieldDefs[$long_name]['value'])) {
565 $this->fieldDefs[$long_name]['value'] = $array[$long_name];
575 if ( is_array($this->searchFields) ) {
576 foreach ( $this->searchFields as $fieldName => $field ) {
577 if ( !empty($field['value']) && is_string($field['value']) ) {
578 $this->searchFields[$fieldName]['value'] = trim($field['value']);
586 * Populate the searchFields from $_REQUEST
588 * @param string $switchVar variable to use in switch statement
589 * @param bool $addAllBeanFields true to process at all bean fields
591 function populateFromRequest($switchVar = null, $addAllBeanFields = true) {
592 $this->populateFromArray($_REQUEST, $switchVar, $addAllBeanFields);
597 * Parse date expression and return WHERE clause
598 * @param string $operator Date expression operator
599 * @param string DB field name
600 * @param string DB field type
602 protected function parseDateExpression($operator, $db_field, $field_type = '')
604 if ($field_type == "date") {
611 $dates = TimeDate::getInstance()->parseDateRange($operator, null, $adjForTZ);
612 if(empty($dates)) return '';
613 $start = $this->seed->db->convert($this->seed->db->quoted($dates[0]->asDb()), $type);
614 $end = $this->seed->db->convert($this->seed->db->quoted($dates[1]->asDb()), $type);
615 return "($db_field >= $start AND $db_field <= $end)";
619 * generateSearchWhere
621 * This function serves as the central piece of SearchForm2.php
622 * It is responsible for creating the WHERE clause for a given search operation
624 * @param bool $add_custom_fields boolean indicating whether or not custom fields should be added
625 * @param string $module Module to search against
627 * @return string the SQL WHERE clause based on the arguments supplied in SearchForm2 instance
629 public function generateSearchWhere($add_custom_fields = false, $module='') {
632 $db = $this->seed->db;
633 $this->searchColumns = array () ;
634 $values = $this->searchFields;
636 $where_clauses = array();
638 $table_name = $this->seed->object_name;
639 $this->seed->fill_in_additional_detail_fields();
641 //rrs check for team_id
643 foreach($this->searchFields as $field=>$parms) {
644 $customField = false;
645 // Jenny - Bug 7462: We need a type check here to avoid database errors
646 // when searching for numeric fields. This is a temporary fix until we have
647 // a generic search form validation mechanism.
648 $type = (!empty($this->seed->field_name_map[$field]['type']))?$this->seed->field_name_map[$field]['type']:'';
650 //If range search is enabled for the field, we first check if this is the starting range
651 if(!empty($parms['enable_range_search']) && empty($type))
653 if(preg_match('/^start_range_(.*?)$/', $field, $match))
655 $real_field = $match[1];
656 $start_field = 'start_range_' . $real_field;
657 $end_field = 'end_range_' . $real_field;
659 if(isset($this->searchFields[$start_field]['value']) && isset($this->searchFields[$end_field]['value']))
661 $this->searchFields[$real_field]['value'] = $this->searchFields[$start_field]['value'] . '<>' . $this->searchFields[$end_field]['value'];
662 $this->searchFields[$real_field]['operator'] = 'between';
663 $parms['value'] = $this->searchFields[$real_field]['value'];
664 $parms['operator'] = 'between';
666 $field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
667 if($field_type == 'datetimecombo' || $field_type == 'datetime')
672 $field = $real_field;
673 unset($this->searchFields[$end_field]['value']);
675 //if both start and end ranges have not been defined, skip this filter.
678 } else if (preg_match('/^range_(.*?)$/', $field, $match) && isset($this->searchFields[$field]['value'])) {
679 $real_field = $match[1];
681 //Special case for datetime and datetimecombo fields. By setting the type here we allow an actual between search
682 if(in_array($parms['operator'], array('=', 'between', "not_equal", 'less_than', 'greater_than', 'less_than_equals', 'greater_than_equals')))
684 $field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
685 if(strtolower($field_type) == 'readonly' && isset($this->seed->field_name_map[$real_field]['dbType'])) {
686 $field_type = $this->seed->field_name_map[$real_field]['dbType'];
688 if($field_type == 'datetimecombo' || $field_type == 'datetime' || $field_type == 'int')
694 $this->searchFields[$real_field]['value'] = $this->searchFields[$field]['value'];
695 $this->searchFields[$real_field]['operator'] = $this->searchFields[$field]['operator'];
696 $params['value'] = $this->searchFields[$field]['value'];
697 $params['operator'] = $this->searchFields[$field]['operator'];
698 unset($this->searchFields[$field]['value']);
699 $field = $real_field;
701 //Skip this range search field, it is the end field THIS IS NEEDED or the end range date will break the query
706 //Test to mark whether or not the field is a custom field
707 if(!empty($this->seed->field_name_map[$field]['source'])
708 && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' ||
709 //Non-db custom fields, such as custom relates
710 ($this->seed->field_name_map[$field]['source'] == 'non-db'
711 && (!empty($this->seed->field_name_map[$field]['custom_module']) ||
712 isset($this->seed->field_name_map[$field]['ext2']))))){
716 if ($type == 'int' && isset($parms['value']) && !empty($parms['value'])) {
717 require_once ('include/SugarFields/SugarFieldHandler.php');
718 $intField = SugarFieldHandler::getSugarField('int');
719 $newVal = $intField->getSearchWhereValue($parms['value']);
720 $parms['value'] = $newVal;
721 } elseif($type == 'html' && $customField) {
726 if(isset($parms['value']) && $parms['value'] != "") {
728 $operator = $db->isNumericType($type)?'=':'like';
729 if(!empty($parms['operator'])) {
730 $operator = strtolower($parms['operator']);
733 if(is_array($parms['value'])) {
736 // always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
737 if(!empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
738 // construct the query for multenums
739 // use the 'like' query as both custom and OOB multienums are implemented with types that cannot be used with an 'in'
740 $operator = 'custom_enum';
741 $table_name = $this->seed->table_name ;
743 $table_name .= "_cstm" ;
744 $db_field = $table_name . "." . $field;
746 foreach($parms['value'] as $val) {
747 if($val != ' ' and $val != '') {
748 $qVal = $db->quote($val);
749 if (!empty($field_value)) {
750 $field_value .= ' or ';
752 $field_value .= "$db_field like '%^$qVal^%'";
754 $field_value .= '('.$db_field . ' IS NULL or '.$db_field."='^^' or ".$db_field."='')";
759 $operator = $operator != 'subquery' ? 'in' : $operator;
760 foreach($parms['value'] as $val) {
761 if($val != ' ' and $val != '') {
762 if (!empty($field_value)) {
765 $field_value .= $db->quoteType($type, $val);
767 // Bug 41209: adding a new operator "isnull" here
768 // to handle the case when blank is selected from dropdown.
769 // In that case, $val is empty.
770 // When $val is empty, we need to use "IS NULL",
771 // as "in (null)" won't work
772 else if ($operator=='in') {
773 $operator = 'isnull';
779 $field_value = $parms['value'];
782 //set db_fields array.
783 if(!isset($parms['db_field'])) {
784 $parms['db_field'] = array($field);
787 //This if-else block handles the shortcut checkbox selections for "My Items" and "Closed Only"
788 if(!empty($parms['my_items'])) {
789 if( $parms['value'] == false ) {
792 //my items is checked.
793 global $current_user;
794 $field_value = $db->quote($current_user->id);
797 } else if(!empty($parms['closed_values']) && is_array($parms['closed_values'])) {
798 if( $parms['value'] == false ) {
802 foreach($parms['closed_values'] as $closed_value)
804 $field_value .= "," . $db->quoted($closed_value);
806 $field_value = substr($field_value, 1);
813 if($field_value != '' || $operator=='isnull') {
815 $this->searchColumns [ strtoupper($field) ] = $field ;
817 foreach ($parms['db_field'] as $db_field) {
818 if (strstr($db_field, '.') === false) {
819 //Try to get the table for relate fields from link defs
820 if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link'])
821 && !empty($this->seed->field_name_map[$field]['rname'])) {
822 $link = $this->seed->field_name_map[$field]['link'];
823 $relname = $link['relationship'];
824 if (($this->seed->load_relationship($link))){
826 $db_field = $this->seed->field_name_map[$field]['name'];
828 //Best Guess for table name
829 $db_field = strtolower($link['module']) . '.' . $db_field;
834 else if ($type == 'parent') {
835 if (!empty($this->searchFields['parent_type'])) {
836 $parentType = $this->searchFields['parent_type'];
837 $rel_module = $parentType['value'];
838 global $beanFiles, $beanList;
839 if(!empty($beanFiles[$beanList[$rel_module]])) {
840 require_once($beanFiles[$beanList[$rel_module]]);
841 $rel_seed = new $beanList[$rel_module]();
842 $db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
846 // Relate fields in custom modules and custom relate fields
847 else if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
848 $db_field = !empty($this->seed->field_name_map[$field]['name'])?$this->seed->field_name_map[$field]['name']:'name';
850 else if(!$customField){
851 if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
852 $db_field = $db->concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
854 $db_field = $this->seed->table_name . "." . $db_field;
856 if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
857 $db_field = $db->concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
859 $db_field = $this->seed->table_name . "_cstm." . $db_field;
864 if($type == 'date') {
865 // The regular expression check is to circumvent special case YYYY-MM
867 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) != 0) { // preg_match returns number of matches
868 $db_field = $this->seed->db->convert($db_field, "date_format", array("%Y-%m"));
870 $field_value = $timedate->to_db_date($field_value, false);
871 $db_field = $this->seed->db->convert($db_field, "date_format", array("%Y-%m-%d"));
875 if($type == 'datetime' || $type == 'datetimecombo') {
877 if($operator == '=' || $operator == 'between') {
878 // FG - bug45287 - If User asked for a range, takes edges from it.
879 $placeholderPos = strpos($field_value, "<>");
880 if ($placeholderPos !== FALSE && $placeholderPos > 0)
882 $datesLimit = explode("<>", $field_value);
883 $dateStart = $timedate->getDayStartEndGMT($datesLimit[0]);
884 $dateEnd = $timedate->getDayStartEndGMT($datesLimit[1]);
886 $dates['end'] = $dateEnd['end'];
887 $dates['enddate'] = $dateEnd['enddate'];
888 $dates['endtime'] = $dateEnd['endtime'];
892 $dates = $timedate->getDayStartEndGMT($field_value);
894 // FG - bug45287 - Note "start" and "end" are the correct interval at GMT timezone
895 $field_value = array($dates["start"], $dates["end"]);
896 $operator = 'between';
897 } else if($operator == 'not_equal') {
898 $dates = $timedate->getDayStartEndGMT($field_value);
899 $field_value = array($dates["start"], $dates["end"]);
900 $operator = 'date_not_equal';
901 } else if($operator == 'greater_than') {
902 $dates = $timedate->getDayStartEndGMT($field_value);
903 $field_value = $dates["end"];
904 } else if($operator == 'less_than') {
905 $dates = $timedate->getDayStartEndGMT($field_value);
906 $field_value = $dates["start"];
907 } else if($operator == 'greater_than_equals') {
908 $dates = $timedate->getDayStartEndGMT($field_value);
909 $field_value = $dates["start"];
910 } else if($operator == 'less_than_equals') {
911 $dates = $timedate->getDayStartEndGMT($field_value);
912 $field_value = $dates["end"];
914 } catch(Exception $timeException) {
915 //In the event that a date value is given that cannot be correctly processed by getDayStartEndGMT method,
916 //just skip searching on this field and continue. This may occur if user switches locale date formats
917 //in another browser screen, but re-runs a search with the previous format on another screen
918 $GLOBALS['log']->error($timeException->getMessage());
923 if($type == 'decimal' || $type == 'float' || $type == 'currency' || (!empty($parms['enable_range_search']) && empty($parms['is_date_field']))) {
924 require_once('modules/Currencies/Currency.php');
926 //we need to handle formatting either a single value or 2 values in case the 'between' search option is set
927 //start by splitting the string if the between operator exists
928 $fieldARR = explode('<>', $field_value);
929 //set the first pass through boolean
931 foreach($fieldARR as $fv){
932 //reset the field value, it will be rebuild in the foreach loop below
933 $tmpfield_value = unformat_number($fv);
935 if ( $type == 'currency' && stripos($field,'_usdollar')!==FALSE ) {
936 // It's a US Dollar field, we need to do some conversions from the user's local currency
937 $currency_id = $GLOBALS['current_user']->getPreference('currency');
938 if ( empty($currency_id) ) {
941 if ( $currency_id != -99 ) {
942 $currency = new Currency();
943 $currency->retrieve($currency_id);
944 $tmpfield_value = $currency->convertToDollar($tmpfield_value);
947 $values[] = $tmpfield_value;
950 $field_value = join('<>',$values);
952 if(!empty($parms['enable_range_search']) && $parms['operator'] == '=' && $type != 'int')
954 // Databases can't really search for floating point numbers, because they can't be accurately described in binary,
955 // So we have to fuzz out the math a little bit
956 $field_value = array(($field_value - 0.01) , ($field_value + 0.01));
957 $operator = 'between';
962 if($db->supports("case_sensitive") && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
963 $db_field = 'upper(' . $db_field . ")";
964 $field_value = strtoupper($field_value);
972 //Here we make a last attempt to determine the field type if possible
973 if(empty($type) && isset($parms['db_field']) && isset($parms['db_field'][0]) && isset($this->seed->field_defs[$parms['db_field'][0]]['type']))
975 $type = $this->seed->field_defs[$parms['db_field'][0]]['type'];
978 switch(strtolower($operator)) {
981 if ( isset($parms['subquery_in_clause']) ) {
982 if ( !is_array($parms['subquery_in_clause']) ) {
983 $in = $parms['subquery_in_clause'];
985 elseif ( isset($parms['subquery_in_clause'][$field_value]) ) {
986 $in = $parms['subquery_in_clause'][$field_value];
989 $sq = $parms['subquery'];
992 if (isset($sq['OR'])){
997 if(empty($q) || strlen($q)<2) continue;
1001 $where .= " {$db_field} $in ({$q} ".$this->seed->db->quoted($field_value.'%').") ";
1004 }elseif(!empty($parms['query_type']) && $parms['query_type'] == 'format'){
1005 $stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
1006 $where .= "{$db_field} $in (".string_format($parms['subquery'], $stringFormatParams).")";
1008 //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
1009 //outside in. Additional details: http://bugs.mysql.com/bug.php?id=9021
1011 //use the select column in the subquery if it exists
1012 if(!empty($parms['subquery'])){
1013 $selectCol = $this->getSelectCol($parms['subquery']);
1015 $where .= "{$db_field} $in (select $selectCol from ({$parms['subquery']} ".$this->seed->db->quoted($field_value.'%').") {$field}_derived)";
1021 if($type == 'bool' && $field_value == 0)
1023 // Bug 43452 - FG - Added parenthesis surrounding the OR (without them the WHERE clause would be broken)
1024 $where .= "( " . $db_field . " = '0' OR " . $db_field . " IS NULL )";
1028 // check to see if this is coming from unified search or not
1029 $UnifiedSearch = !empty($parms['force_unifiedsearch']);
1030 if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch'){
1031 $UnifiedSearch = true;
1034 // If it is a unified search and if the search contains more then 1 word (contains space)
1035 // and if it's the last element from db_field (so we do the concat only once, not for every db_field element)
1036 // we concat the db_field array() (both original, and in reverse order) and search for the whole string in it
1037 if ( $UnifiedSearch && strpos($field_value, ' ') !== false && strpos($db_field, $parms['db_field'][count($parms['db_field']) - 1]) !== false )
1039 // Get the table name used for concat
1040 $concat_table = explode('.', $db_field);
1041 $concat_table = $concat_table[0];
1042 // Get the fields for concatenating
1043 $concat_fields = $parms['db_field'];
1045 // If db_fields (e.g. contacts.first_name) contain table name, need to remove it
1046 for ($i = 0; $i < count($concat_fields); $i++)
1048 if (strpos($concat_fields[$i], $concat_table) !== false)
1050 $concat_fields[$i] = substr($concat_fields[$i], strlen($concat_table) + 1);
1054 // Concat the fields and search for the value
1055 $where .= $this->seed->db->concat($concat_table, $concat_fields) . " LIKE " . $this->seed->db->quoted($field_value . $like_char);
1056 $where .= ' OR ' . $this->seed->db->concat($concat_table, array_reverse($concat_fields)) . " LIKE " . $this->seed->db->quoted($field_value . $like_char);
1060 //Check if this is a first_name, last_name search
1061 if(isset($this->seed->field_name_map) && isset($this->seed->field_name_map[$db_field]))
1063 $vardefEntry = $this->seed->field_name_map[$db_field];
1064 if(!empty($vardefEntry['db_concat_fields']) && in_array('first_name', $vardefEntry['db_concat_fields']) && in_array('last_name', $vardefEntry['db_concat_fields']))
1066 if(!empty($GLOBALS['app_list_strings']['salutation_dom']) && is_array($GLOBALS['app_list_strings']['salutation_dom']))
1068 foreach($GLOBALS['app_list_strings']['salutation_dom'] as $salutation)
1070 if(!empty($salutation) && strpos($field_value, $salutation) === 0)
1072 $field_value = trim(substr($field_value, strlen($salutation)));
1080 //field is not last name or this is not from global unified search, so do normal where clause
1081 $where .= $db_field . " like ".$this->seed->db->quoted(sql_like_string($field_value, $like_char));
1086 $where .= $db_field . ' not in ('.$field_value.')';
1089 $where .= $db_field . ' in ('.$field_value.')';
1092 if($type == 'bool' && $field_value == 0) {
1093 $where .= "($db_field = 0 OR $db_field IS NULL)";
1096 $where .= $db_field . " = ".$db->quoteType($type, $field_value);
1099 // tyoung bug 15971 - need to add these special cases into the $where query
1101 $where .= $field_value;
1104 if(!is_array($field_value)) {
1105 $field_value = explode('<>', $field_value);
1107 $field_value[0] = $db->quoteType($type, $field_value[0]);
1108 $field_value[1] = $db->quoteType($type, $field_value[1]);
1109 $where .= "($db_field >= {$field_value[0]} AND $db_field <= {$field_value[1]})";
1111 case 'date_not_equal':
1112 if(!is_array($field_value)) {
1113 $field_value = explode('<>', $field_value);
1115 $field_value[0] = $db->quoteType($type, $field_value[0]);
1116 $field_value[1] = $db->quoteType($type, $field_value[1]);
1117 $where .= "($db_field IS NULL OR $db_field < {$field_value[0]} OR $db_field > {$field_value[1]})";
1120 $this->seed->listview_inner_join[] = $parms['innerjoin'] . " '" . $parms['value'] . "%')";
1123 $field_value = $db->quoteType($type, $field_value);
1124 $where .= "($db_field IS NULL OR $db_field != $field_value)";
1126 case 'greater_than':
1127 $field_value = $db->quoteType($type, $field_value);
1128 $where .= "$db_field > $field_value";
1130 case 'greater_than_equals':
1131 $field_value = $db->quoteType($type, $field_value);
1132 $where .= "$db_field >= $field_value";
1135 $field_value = $db->quoteType($type, $field_value);
1136 $where .= "$db_field < $field_value";
1138 case 'less_than_equals':
1139 $field_value = $db->quoteType($type, $field_value);
1140 $where .= "$db_field <= $field_value";
1147 case 'last_30_days':
1148 case 'next_30_days':
1152 if (!empty($field) && !empty($this->seed->field_name_map[$field]['type'])) {
1153 $where .= $this->parseDateExpression(strtolower($operator), $db_field, $this->seed->field_name_map[$field]['type']);
1155 $where .= $this->parseDateExpression(strtolower($operator), $db_field);
1159 $where .= "($db_field IS NULL OR $db_field = '')";
1160 if ($field_value != '')
1161 $where .= ' OR ' . $db_field . " in (".$field_value.')';
1167 if(!empty($where)) {
1169 array_push($where_clauses, '( '.$where.' )');
1172 array_push($where_clauses, $where);
1178 return $where_clauses;
1184 * isEmptyDropdownField
1186 * This function checks to see if a blank dropdown field was supplied. This scenario will occur where
1187 * a dropdown select is in single selection mode
1189 * @param $value Mixed dropdown value
1191 private function isEmptyDropdownField($name='', $value=array())
1193 $result = is_array($value) && isset($value[0]) && $value[0] == '';
1194 $GLOBALS['log']->debug("Found empty value for {$name} dropdown search key");
1199 * Return the search defs for a particular module.
1204 public static function retrieveSearchDefs($module)
1206 $searchdefs = array();
1207 $searchFields = array();
1209 if(file_exists('custom/modules/'.$module.'/metadata/metafiles.php'))
1211 require('custom/modules/'.$module.'/metadata/metafiles.php');
1213 elseif(file_exists('modules/'.$module.'/metadata/metafiles.php'))
1215 require('modules/'.$module.'/metadata/metafiles.php');
1218 if (file_exists('custom/modules/'.$module.'/metadata/searchdefs.php'))
1220 require('custom/modules/'.$module.'/metadata/searchdefs.php');
1222 elseif (!empty($metafiles[$module]['searchdefs']))
1224 require($metafiles[$module]['searchdefs']);
1226 elseif (file_exists('modules/'.$module.'/metadata/searchdefs.php'))
1228 require('modules/'.$module.'/metadata/searchdefs.php');
1232 if(!empty($metafiles[$module]['searchfields']))
1234 require($metafiles[$module]['searchfields']);
1236 elseif(file_exists('modules/'.$module.'/metadata/SearchFields.php'))
1238 require('modules/'.$module.'/metadata/SearchFields.php');
1240 if(file_exists('custom/modules/'.$module.'/metadata/SearchFields.php'))
1242 require('custom/modules/'.$module.'/metadata/SearchFields.php');
1245 return array('searchdefs' => $searchdefs, 'searchFields' => $searchFields );
1249 * this function will take the subquery string and return the columns to be used in the select of the derived table
1251 * @param string $subquery the subquery string to parse through
1252 * @return string the retrieved column list
1254 protected function getSelectCol($subquery)
1258 if (empty($subquery)) {
1261 $subquery = strtolower($subquery);
1262 //grab the values between the select and from
1263 $select = stripos($subquery, 'select');
1264 $from = stripos($subquery, 'from');
1265 if ($select !==false && $from!==false && $select+6 < $from) {
1266 $selectCol = substr($subquery, $select+6, $from-$select-6);
1268 //remove table names if they exist
1269 $columns = explode(',', $selectCol);
1271 foreach ($columns as $column) {
1272 $dot = strpos($column, '.');
1274 $columns[$i] = substr($column, $dot+1);
1278 $selectCol = implode(',', $columns);