2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2011 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 * Created on May 30, 2007
40 * To change the template for this generated file go to
41 * Window - Preferences - PHPeclipse - PHP - Code Templates
43 require_once('include/tabs.php');
44 require_once('include/ListView/ListViewSmarty.php');
46 require_once('include/TemplateHandler/TemplateHandler.php');
47 require_once('include/EditView/EditView2.php');
50 class SearchForm extends EditView{
53 var $action = 'index';
54 var $searchdefs = array();
55 var $listViewDefs = array();
59 var $view = 'SearchForm';
60 var $displayView = 'basic_search';
65 var $parsedView = 'basic';
68 var $displaySavedSearch = true;
69 //show the advanced tab
70 var $showAdvanced = true;
72 var $showBasic = true;
73 //array of custom tab to show declare in searchdefs (no custom tab if false)
74 var $showCustom = false;
77 // hide saved searches drop and down near the search button
78 var $showSavedSearchesOptions = true;
80 var $displayType = 'searchView';
82 function SearchForm($seed, $module, $action = 'index'){
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 () ;
102 function setup($searchdefs, $searchFields = array(), $tpl, $displayView = 'basic_search', $listViewDefs = array()){
103 $this->searchdefs = $searchdefs[$this->module];
105 //used by advanced search
106 $this->listViewDefs = $listViewDefs;
107 $this->displayView = $displayView;
108 $this->view = $this->view.'_'.$displayView;
109 $tokens = explode('_', $this->displayView);
110 $this->parsedView = $tokens[0];
111 if($this->displayView != 'saved_views'){
112 $this->_build_field_defs();
115 $this->searchFields = $searchFields[$this->module];
117 // Setub the tab array
118 $this->tabs = array();
119 if($this->showBasic){
121 $this->tabs[]=array('title' => $GLOBALS['app_strings']['LNK_BASIC_SEARCH'],
122 'link' => $this->module . '|basic_search',
123 'key' => $this->module . '|basic_search',
127 if($this->showAdvanced){
129 $this->tabs[]=array('title' => $GLOBALS['app_strings']['LNK_ADVANCED_SEARCH'],
130 'link' => $this->module . '|advanced_search',
131 'key' => $this->module . '|advanced_search',
132 'name' => 'advanced',
133 'displayDiv' => 'display:none');
135 if($this->showCustom){
136 foreach($this->showCustom as $v){
138 $this->tabs[]=array('title' => $GLOBALS['app_strings']["LNK_" . strtoupper($v)],
139 'link' => $this->module . '|' . $v,
140 'key' => $this->module . '|' . $v,
141 'name' => str_replace('_search','',$v),
142 'displayDiv' => 'display:none',);
147 function display($header = true){
148 global $theme, $timedate;
152 $this->th->ss->assign('module', $this->module);
153 $this->th->ss->assign('action', $this->action);
154 $this->th->ss->assign('displayView', $this->displayView);
155 $this->th->ss->assign('APP', $GLOBALS['app_strings']);
156 //Show the tabs only if there is more than one
158 $this->th->ss->assign('TABS', $this->_displayTabs($this->module . '|' . $this->displayView));
160 $this->th->ss->assign('searchTableColumnCount',
161 ((isset($this->searchdefs['templateMeta']['maxColumns']) ? $this->searchdefs['templateMeta']['maxColumns'] : 2) * 2 ) - 1);
162 $this->th->ss->assign('fields', $this->fieldDefs);
163 $this->th->ss->assign('customFields', $this->customFieldDefs);
164 $this->th->ss->assign('formData', $this->formData);
165 $time_format = $timedate->get_user_time_format();
166 $this->th->ss->assign('TIME_FORMAT', $time_format);
167 $this->th->ss->assign('USER_DATEFORMAT', $timedate->get_user_date_format());
169 $date_format = $timedate->get_cal_date_format();
170 $time_separator = ":";
171 if(preg_match('/\d+([^\d])\d+([^\d]*)/s', $time_format, $match)) {
172 $time_separator = $match[1];
174 // Create Smarty variables for the Calendar picker widget
175 $t23 = strpos($time_format, '23') !== false ? '%H' : '%I';
176 if(!isset($match[2]) || $match[2] == '') {
177 $this->th->ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M");
179 $pm = $match[2] == "pm" ? "%P" : "%p";
180 $this->th->ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M" . $pm);
182 $this->th->ss->assign('TIME_SEPARATOR', $time_separator);
184 //Show and hide the good tab form
185 foreach($this->tabs as $tabkey=>$viewtab){
186 $viewName=str_replace(array($this->module . '|','_search'),'',$viewtab['key']);
187 if(strpos($this->view,$viewName)!==false){
188 $this->tabs[$tabkey]['displayDiv']='';
189 //if this is advanced tab, use form with saved search sub form built in
190 if($viewName=='advanced'){
191 $this->tpl = 'include/SearchForm/tpls/SearchFormGenericAdvanced.tpl';
192 if ($this->action =='ListView') {
193 $this->th->ss->assign('DISPLAY_SEARCH_HELP', true);
195 $this->th->ss->assign('DISPLAY_SAVED_SEARCH', $this->displaySavedSearch);
196 $this->th->ss->assign('SAVED_SEARCH', $this->displaySavedSearch());
197 //this determines whether the saved search subform should be rendered open or not
198 if(isset($_REQUEST['showSSDIV']) && $_REQUEST['showSSDIV']=='yes'){
199 $this->th->ss->assign('SHOWSSDIV', 'yes');
200 $this->th->ss->assign('DISPLAYSS', '');
202 $this->th->ss->assign('SHOWSSDIV', 'no');
203 $this->th->ss->assign('DISPLAYSS', 'display:none');
207 $this->tabs[$tabkey]['displayDiv']='display:none';
212 $this->th->ss->assign('TAB_ARRAY', $this->tabs);
215 if ( isset($this->searchdefs['templateMeta']['widths'])
216 && isset($this->searchdefs['templateMeta']['maxColumns'])) {
217 $totalWidth = ( $this->searchdefs['templateMeta']['widths']['label'] +
218 $this->searchdefs['templateMeta']['widths']['field'] ) *
219 $this->searchdefs['templateMeta']['maxColumns'];
220 // redo the widths in case they are too big
221 if ( $totalWidth > 100 ) {
222 $resize = 100 / $totalWidth;
223 $this->searchdefs['templateMeta']['widths']['label'] =
224 $this->searchdefs['templateMeta']['widths']['label'] * $resize;
225 $this->searchdefs['templateMeta']['widths']['field'] =
226 $this->searchdefs['templateMeta']['widths']['field'] * $resize;
229 $this->th->ss->assign('templateMeta', $this->searchdefs['templateMeta']);
230 $this->th->ss->assign('HAS_ADVANCED_SEARCH', !empty($this->searchdefs['layout']['advanced_search']));
231 $this->th->ss->assign('displayType', $this->displayType);
232 // return the form of the shown tab only
233 if($this->showSavedSearchesOptions){
234 $this->th->ss->assign('SAVED_SEARCHES_OPTIONS', $this->displaySavedSearchSelect());
236 if ($this->module == 'Documents'){
237 $this->th->ss->assign('DOCUMENTS_MODULE', true);
239 $return_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchForm_'.$this->parsedView, $this->tpl);
241 $this->th->ss->assign('return_txt', $return_txt);
242 $header_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchFormHeader', 'include/SearchForm/tpls/header.tpl');
243 //pass in info to render the select dropdown below the form
244 $footer_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchFormFooter', 'include/SearchForm/tpls/footer.tpl');
245 $return_txt = $header_txt.$footer_txt;
250 function displaySavedSearch(){
251 $savedSearch = new SavedSearch($this->listViewDefs[$this->module], $this->lv->data['pageData']['ordering']['orderBy'], $this->lv->data['pageData']['ordering']['sortOrder']);
252 return $savedSearch->getForm($this->module, false);
256 function displaySavedSearchSelect(){
257 $savedSearch = new SavedSearch($this->listViewDefs[$this->module], $this->lv->data['pageData']['ordering']['orderBy'], $this->lv->data['pageData']['ordering']['sortOrder']);
258 return $savedSearch->getSelect($this->module);
264 * displays the tabs (top of the search form)
266 * @param string $currentKey key in $this->tabs to show as the current tab
268 * @return string html
270 function _displayTabs($currentKey)
272 if(isset($_REQUEST['saved_search_select']) && $_REQUEST['saved_search_select']!='_none') {
273 $saved_search=loadBean('SavedSearch');
274 $saved_search->retrieveSavedSearch($_REQUEST['saved_search_select']);
278 if(!empty($_REQUEST['displayColumns']))
279 $str .= 'SUGAR.savedViews.displayColumns = "' . $_REQUEST['displayColumns'] . '";';
280 elseif(isset($saved_search->contents['displayColumns']) && !empty($saved_search->contents['displayColumns']))
281 $str .= 'SUGAR.savedViews.displayColumns = "' . $saved_search->contents['displayColumns'] . '";';
282 if(!empty($_REQUEST['hideTabs']))
283 $str .= 'SUGAR.savedViews.hideTabs = "' . $_REQUEST['hideTabs'] . '";';
284 elseif(isset($saved_search->contents['hideTabs']) && !empty($saved_search->contents['hideTabs']))
285 $str .= 'SUGAR.savedViews.hideTabs = "' . $saved_search->contents['hideTabs'] . '";';
286 if(!empty($_REQUEST['orderBy']))
287 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $_REQUEST['orderBy'] . '";';
288 elseif(isset($saved_search->contents['orderBy']) && !empty($saved_search->contents['orderBy']))
289 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $saved_search->contents['orderBy'] . '";';
290 if(!empty($_REQUEST['sortOrder']))
291 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $_REQUEST['sortOrder'] . '";';
292 elseif(isset($saved_search->contents['sortOrder']) && !empty($saved_search->contents['sortOrder']))
293 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $saved_search->contents['sortOrder'] . '";';
303 function _build_field_defs(){
304 $this->formData = array();
305 $this->fieldDefs = array();
306 foreach($this->searchdefs['layout'][$this->displayView] as $data){
308 //Fields may be listed but disabled so that when they are enabled, they have the correct custom display data.
309 if (isset($data['enabled']) && $data['enabled'] == false)
311 $data['name'] = $data['name'].'_'.$this->parsedView;
312 $this->formData[] = array('field' => $data);
313 $this->fieldDefs[$data['name']]= $data;
315 $this->formData[] = array('field' => array('name'=>$data.'_'.$this->parsedView));
320 $this->seed->fill_in_additional_detail_fields();
321 // hack to make the employee status field for the Users/Employees module display correctly
322 if($this->seed->object_name == 'Employee' || $this->seed->object_name == 'User'){
323 $this->seed->field_defs['employee_status']['type'] = 'enum';
324 $this->seed->field_defs['employee_status']['massupdate'] = true;
325 $this->seed->field_defs['employee_status']['options'] = 'employee_status_dom';
326 unset($this->seed->field_defs['employee_status']['function']);
329 foreach($this->seed->toArray() as $name => $value) {
330 if(!empty($this->fieldDefs[$name.'_'.$this->parsedView]))
331 $this->fieldDefs[$name.'_'.$this->parsedView] = array_merge($this->seed->field_defs[$name], $this->fieldDefs[$name.'_'.$this->parsedView]);
333 $this->fieldDefs[$name.'_'.$this->parsedView] = $this->seed->field_defs[$name];
334 $this->fieldDefs[$name.'_'.$this->parsedView]['name'] = $this->fieldDefs[$name.'_'.$this->parsedView]['name'].'_'.$this->parsedView;
337 if(isset($this->fieldDefs[$name.'_'.$this->parsedView]['type']) && $this->fieldDefs[$name.'_'.$this->parsedView]['type'] == 'relate') {
338 if(isset($this->fieldDefs[$name.'_'.$this->parsedView]['id_name'])) {
339 $this->fieldDefs[$name.'_'.$this->parsedView]['id_name'] .= '_'.$this->parsedView;
343 if(isset($this->fieldDefs[$name.'_'.$this->parsedView]['options']) && isset($GLOBALS['app_list_strings'][$this->fieldDefs[$name.'_'.$this->parsedView]['options']])) {
344 $this->fieldDefs[$name.'_'.$this->parsedView]['options'] = $GLOBALS['app_list_strings'][$this->fieldDefs[$name.'_'.$this->parsedView]['options']]; // fill in enums
347 if(isset($this->fieldDefs[$name.'_'.$this->parsedView]['function'])) {
349 $this->fieldDefs[$name.'_'.$this->parsedView]['type']='multienum';
351 if(is_array($this->fieldDefs[$name.'_'.$this->parsedView]['function'])) {
352 $this->fieldDefs[$name.'_'.$this->parsedView]['function']['preserveFunctionValue']=true;
355 $function = $this->fieldDefs[$name.'_'.$this->parsedView]['function'];
357 if(is_array($function) && isset($function['name'])){
358 $function_name = $this->fieldDefs[$name.'_'.$this->parsedView]['function']['name'];
360 $function_name = $this->fieldDefs[$name.'_'.$this->parsedView]['function'];
363 if(!empty($this->fieldDefs[$name.'_'.$this->parsedView]['function']['returns']) && $this->fieldDefs[$name.'_'.$this->parsedView]['function']['returns'] == 'html'){
364 if(!empty($this->fieldDefs[$name.'_'.$this->parsedView]['function']['include'])){
365 require_once($this->fieldDefs[$name.'_'.$this->parsedView]['function']['include']);
367 $value = $function_name($this->seed, $name, $value, $this->view);
368 $this->fieldDefs[$name.'_'.$this->parsedView]['value'] = $value;
370 if(!isset($function['params']) || !is_array($function['params'])) {
371 $this->fieldDefs[$name.'_'.$this->parsedView]['options'] = $function_name($this->seed, $name, $value, $this->view);
373 $this->fieldDefs[$name.'_'.$this->parsedView]['options'] = call_user_func_array($function_name, $function['params']);
377 if(isset($this->fieldDefs[$name]['type']) && $this->fieldDefs[$name.'_'.$this->parsedView]['type'] == 'function' && isset($this->fieldDefs[$name.'_'.$this->parsedView]['function_name'])){
378 $value = $this->callFunction($this->fieldDefs[$name.'_'.$this->parsedView]);
379 $this->fieldDefs[$name.'_'.$this->parsedView]['value'] = $value;
382 $this->fieldDefs[$name]['value'] = $value;
385 if((!empty($_REQUEST[$name.'_'.$this->parsedView]) || (isset($_REQUEST[$name.'_'.$this->parsedView]) && $_REQUEST[$name.'_'.$this->parsedView] == '0'))
386 && empty($this->fieldDefs[$name.'_'.$this->parsedView]['function']['preserveFunctionValue'])) {
387 $value = $_REQUEST[$name.'_'.$this->parsedView];
388 $this->fieldDefs[$name.'_'.$this->parsedView]['value'] = $value;
399 * Populate the searchFields from an array
401 * @param array $array array to search through
402 * @param string $switchVar variable to use in switch statement
403 * @param bool $addAllBeanFields true to process at all bean fields
405 function populateFromArray(&$array, $switchVar = null, $addAllBeanFields = true) {
407 if((!empty($array['searchFormTab']) || !empty($switchVar)) && !empty($this->searchFields)) {
408 $arrayKeys = array_keys($array);
409 $searchFieldsKeys = array_keys($this->searchFields);
410 if(empty($switchVar)) $switchVar = $array['searchFormTab'];
411 //name of the search tab
412 $SearchName=str_replace('_search', '', $switchVar);
413 if($switchVar=='saved_views'){
414 foreach($this->searchFields as $name => $params) {
415 foreach($this->tabs as $tabName){
416 if(!empty($array[$name . '_' . $tabName['name']])) {
417 $this->searchFields[$name]['value'] = $array[$name . '_' . $tabName['name']];
418 if(empty($this->fieldDefs[$name . '_' . $tabName['name']]['value'])) $this->fieldDefs[$name . '_' . $tabName['name']]['value'] = $array[$name . '_' . $tabName['name']];
422 if($addAllBeanFields) {
423 foreach($this->seed->field_name_map as $key => $params) {
424 if(!in_array($key, $searchFieldsKeys)) {
425 foreach($this->tabs->name as $tabName){
426 if(in_array($key . '_' . $tabName['name'], $arrayKeys) ) {
427 $this->searchFields[$key] = array('query_type' => 'default',
428 'value' => $array[$key . '_' . $tabName['name']]);
437 $fromMergeRecords = isset($array['merge_module']);
439 foreach($this->searchFields as $name => $params) {
440 $long_name = $name.'_'.$SearchName;
441 /*nsingh 21648: Add additional check for bool values=0. empty() considers 0 to be empty Only repopulates if value is 0 or 1:( */
442 if(isset($array[$long_name]) && !$this->isEmptyDropdownField($long_name, $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'))))
444 $this->searchFields[$name]['value'] = $array[$long_name];
445 if(empty($this->fieldDefs[$long_name]['value'])) {
446 $this->fieldDefs[$long_name]['value'] = $array[$long_name];
448 }else if(!empty($array[$name]) && !$fromMergeRecords && !$this->isEmptyDropdownField($name, $array[$name])) { //basic
449 $this->searchFields[$name]['value'] = $array[$name];
450 if(empty($this->fieldDefs[$name]['value'])) {
451 $this->fieldDefs[$name]['value'] = $array[$name];
455 if(!empty($params['enable_range_search']) && isset($this->searchFields[$name]['value']))
457 if(preg_match('/^range_(.*?)$/', $long_name, $match) && isset($array[$match[1].'_range_choice']))
459 $this->searchFields[$name]['operator'] = $array[$match[1].'_range_choice'];
463 if(!empty($params['is_date_field']) && isset($this->searchFields[$name]['value']))
466 $date_value = $timedate->to_db_date($this->searchFields[$name]['value']);
467 $this->searchFields[$name]['value'] = $date_value == '' ? $this->searchFields[$name]['value'] : $date_value;
471 if((empty($array['massupdate']) || $array['massupdate'] == 'false') && $addAllBeanFields) {
472 foreach($this->seed->field_name_map as $key => $params) {
473 if($key != 'assigned_user_name' && $key != 'modified_by_name')
475 $long_name = $key.'_'.$SearchName;
477 if(in_array($key.'_'.$SearchName, $arrayKeys) && !in_array($key, $searchFieldsKeys) && !$this->isEmptyDropdownField($long_name, $array[$long_name]))
480 $this->searchFields[$key] = array('query_type' => 'default', 'value' => $array[$long_name]);
482 if (!empty($params['type']) && $params['type'] == 'parent'
483 && !empty($params['type_name']) && !empty($this->searchFields[$key]['value']))
485 $this->searchFields[$params['type_name']] = array('query_type' => 'default',
486 'value' => $array[$params['type_name']]);
489 if(empty($this->fieldDefs[$long_name]['value'])) {
490 $this->fieldDefs[$long_name]['value'] = $array[$long_name];
501 if ( is_array($this->searchFields) ) {
502 foreach ( $this->searchFields as $fieldName => $field ) {
503 if ( !empty($field['value']) && is_string($field['value']) ) {
504 $this->searchFields[$fieldName]['value'] = trim($field['value']);
512 * Populate the searchFields from $_REQUEST
514 * @param string $switchVar variable to use in switch statement
515 * @param bool $addAllBeanFields true to process at all bean fields
517 function populateFromRequest($switchVar = null, $addAllBeanFields = true) {
518 $this->populateFromArray($_REQUEST, $switchVar, $addAllBeanFields);
521 function generateSearchWhere($add_custom_fields = false, $module='') {
524 $this->searchColumns = array () ;
525 $values = $this->searchFields;
527 $where_clauses = array();
529 $table_name = $this->seed->object_name;
530 $this->seed->fill_in_additional_detail_fields();
532 //rrs check for team_id
534 foreach($this->searchFields as $field=>$parms) {
535 $customField = false;
536 // Jenny - Bug 7462: We need a type check here to avoid database errors
537 // when searching for numeric fields. This is a temporary fix until we have
538 // a generic search form validation mechanism.
539 $type = (!empty($this->seed->field_name_map[$field]['type']))?$this->seed->field_name_map[$field]['type']:'';
541 if(!empty($parms['enable_range_search']) && empty($type))
543 if(preg_match('/^start_range_(.*?)$/', $field, $match))
545 $real_field = $match[1];
546 $start_field = 'start_range_' . $real_field;
547 $end_field = 'end_range_' . $real_field;
549 if(isset($this->searchFields[$start_field]['value']) && isset($this->searchFields[$end_field]['value']))
551 $this->searchFields[$real_field]['value'] = $this->searchFields[$start_field]['value'] . '<>' . $this->searchFields[$end_field]['value'];
552 $this->searchFields[$real_field]['operator'] = 'between';
553 $parms['value'] = $this->searchFields[$real_field]['value'];
554 $parms['operator'] = 'between';
555 $field = $real_field;
556 unset($this->searchFields[$end_field]['value']);
558 } else if (preg_match('/^range_(.*?)$/', $field, $match) && isset($this->searchFields[$field]['value'])) {
559 $real_field = $match[1];
561 //Special case for datetime and datetimecombo fields. By setting the type here we allow an actual between search
562 if($parms['operator'] == '=')
564 $field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
565 if($field_type == 'datetimecombo' || $field_type == 'datetime')
571 $this->searchFields[$real_field]['value'] = $this->searchFields[$field]['value'];
572 $this->searchFields[$real_field]['operator'] = $this->searchFields[$field]['operator'];
573 $params['value'] = $this->searchFields[$field]['value'];
574 $params['operator'] = $this->searchFields[$field]['operator'];
575 unset($this->searchFields[$field]['value']);
576 $field = $real_field;
578 //Skip this range search field, it is the end field THIS IS NEEDED or the end range date will break the query
583 if(!empty($this->seed->field_name_map[$field]['source'])
584 && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' ||
585 //Non-db custom fields, such as custom relates
586 ($this->seed->field_name_map[$field]['source'] == 'non-db'
587 && (!empty($this->seed->field_name_map[$field]['custom_module']) ||
588 isset($this->seed->field_name_map[$field]['ext2']))))){
592 if ($type == 'int') {
593 if (!empty($parms['value'])) {
594 $tempVal = explode(',', $parms['value']);
596 foreach($tempVal as $key => $val) {
599 if(!empty($val) && !(is_numeric($val)))
604 $parms['value'] = $newVal;
608 //Navjeet- 6/24/08 checkboxes have been changed to dropdowns, so we can query unchecked checkboxes! Bug: 21648.
610 // elseif($type == 'bool' && empty($parms['value']) && preg_match("/current_user_only/", string subject, array subpatterns, int flags, [int offset])) {
614 elseif($type == 'html' && $customField) {
619 if(isset($parms['value']) && $parms['value'] != "") {
622 if(!empty($parms['operator'])) {
623 $operator = $parms['operator'];
626 if(is_array($parms['value'])) {
629 // always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
630 if(/*$GLOBALS['db']->dbType != 'mysql' &&*/ !empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
631 // construct the query for multenums
632 // 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'
633 $operator = 'custom_enum';
634 $table_name = $this->seed->table_name ;
636 $table_name .= "_cstm" ;
637 $db_field = $table_name . "." . $field;
639 foreach($parms['value'] as $key => $val) {
640 if($val != ' ' and $val != '') {
641 $qVal = $GLOBALS['db']->quote($val);
642 if (!empty($field_value)) {
643 $field_value .= ' or ';
645 $field_value .= "$db_field like '%^$qVal^%'";
647 $field_value .= '('.$db_field . ' IS NULL or '.$db_field."='^^' or ".$db_field."='')";
652 $operator = $operator != 'subquery' ? 'in' : $operator;
653 foreach($parms['value'] as $key => $val) {
654 if($val != ' ' and $val != '') {
655 if (!empty($field_value)) {
658 $field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
660 // Bug 41209: adding a new operator "isnull" here
661 // to handle the case when blank is selected from dropdown.
662 // In that case, $val is empty.
663 // When $val is empty, we need to use "IS NULL",
664 // as "in (null)" won't work
665 else if ($operator=='in') {
666 $operator = 'isnull';
672 $field_value = $GLOBALS['db']->quote($parms['value']);
675 //set db_fields array.
676 if(!isset($parms['db_field'])) {
677 $parms['db_field'] = array($field);
680 //This if-else block handles the shortcut checkbox selections for "My Items" and "Closed Only"
681 if(!empty($parms['my_items'])) {
682 if( $parms['value'] == false ) {
685 //my items is checked.
686 global $current_user;
687 $field_value = $GLOBALS['db']->quote($current_user->id);
690 } else if(!empty($parms['closed_values']) && is_array($parms['closed_values'])) {
691 if( $parms['value'] == false ) {
695 foreach($parms['closed_values'] as $closed_value)
697 $field_value .= ",'" . $GLOBALS['db']->quote($closed_value) . "'";
699 $field_value = substr($field_value, 1);
706 if($field_value != '' || $operator=='isnull') {
708 $this->searchColumns [ strtoupper($field) ] = $field ;
710 foreach ($parms['db_field'] as $db_field) {
711 if (strstr($db_field, '.') === false) {
712 //Try to get the table for relate fields from link defs
713 if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link'])
714 && !empty($this->seed->field_name_map[$field]['rname'])) {
715 $link = $this->seed->field_name_map[$field]['link'];
716 $relname = $link['relationship'];
717 if (($this->seed->load_relationship($link))){
719 $db_field = $this->seed->field_name_map[$field]['name'];
721 //Best Guess for table name
722 $db_field = strtolower($link['module']) . '.' . $db_field;
727 else if ($type == 'parent') {
728 if (!empty($this->searchFields['parent_type'])) {
729 $parentType = $this->searchFields['parent_type'];
730 $rel_module = $parentType['value'];
731 global $beanFiles, $beanList;
732 if(!empty($beanFiles[$beanList[$rel_module]])) {
733 require_once($beanFiles[$beanList[$rel_module]]);
734 $rel_seed = new $beanList[$rel_module]();
735 $db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
739 // Relate fields in custom modules and custom relate fields
740 else if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
741 $db_field = !empty($this->seed->field_name_map[$field]['name'])?$this->seed->field_name_map[$field]['name']:'name';
743 else if(!$customField){
744 if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
745 $db_field = db_concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
747 $db_field = $this->seed->table_name . "." . $db_field;
749 if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
750 $db_field = db_concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
752 $db_field = $this->seed->table_name . "_cstm." . $db_field;
757 if($type == 'date') {
758 // Collin - Have mysql as first because it's usually the case
759 // The regular expression check is to circumvent special case YYYY-MM
760 if($GLOBALS['db']->dbType == 'mysql') {
761 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
762 $field_value = $timedate->to_db_date($field_value, false);
765 $operator = 'db_date';
767 } else if($GLOBALS['db']->dbType == 'mssql') {
768 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
769 $field_value = "Convert(DateTime, '".$timedate->to_db_date($field_value, false)."')";
771 $operator = 'db_date';
773 $field_value = $timedate->to_db_date($field_value, false);
778 if($type == 'datetime' || $type == 'datetimecombo') {
780 $dates = $timedate->getDayStartEndGMT($field_value);
781 $field_value = $dates["start"] . "<>" . $dates["end"];
782 $operator = 'between';
783 } catch(Exception $timeException) {
784 //In the event that a date value is given that cannot be correctly processed by getDayStartEndGMT method,
785 //just skip searching on this field and continue. This may occur if user switches locale date formats
786 //in another browser screen, but re-runs a search with the previous format on another screen
787 $GLOBALS['log']->error($timeException->getMessage());
792 if($type == 'decimal' || $type == 'float' || $type == 'currency' || (!empty($parms['enable_range_search']) && empty($parms['is_date_field']))) {
793 require_once('modules/Currencies/Currency.php');
795 //we need to handle formatting either a single value or 2 values in case the 'between' search option is set
796 //start by splitting the string if the between operator exists
797 $fieldARR = explode('<>', $field_value);
798 //set the first pass through boolean
799 $first_between = true;
801 foreach($fieldARR as $fk => $fv){
802 //reset the field value, it will be rebuild in the foreach loop below
803 $tmpfield_value = unformat_number($fv);
805 if ( $type == 'currency' && stripos($field,'_usdollar')!==FALSE ) {
806 // It's a US Dollar field, we need to do some conversions from the user's local currency
807 $currency_id = $GLOBALS['current_user']->getPreference('currency');
808 if ( empty($currency_id) ) {
811 if ( $currency_id != -99 ) {
812 $currency = new Currency();
813 $currency->retrieve($currency_id);
814 $field_value = $currency->convertToDollar($tmpfield_value);
818 //recreate the field value
820 //set the field value with the new formatted temp value
821 $field_value = $tmpfield_value;
823 //this is a between query, so append the between operator and add the second formatted temp value
824 $field_value .= '<>'.$tmpfield_value;
826 //set the first pass through variable to false
827 $first_between = false;
830 if(!empty($parms['enable_range_search']) && $parms['operator'] == '=')
832 // Databases can't really search for floating point numbers, because they can't be accurately described in binary,
833 // So we have to fuzz out the math a little bit
834 $field_value = ($field_value - 0.01) . "<>" . ($field_value + 0.01);
835 $operator = 'between';
846 switch(strtolower($operator)) {
849 if ( isset($parms['subquery_in_clause']) ) {
850 if ( !is_array($parms['subquery_in_clause']) ) {
851 $in = $parms['subquery_in_clause'];
853 elseif ( isset($parms['subquery_in_clause'][$field_value]) ) {
854 $in = $parms['subquery_in_clause'][$field_value];
857 $sq = $parms['subquery'];
860 if (isset($sq['OR'])){
865 if(empty($q) || strlen($q)<2) continue;
869 $where .= " {$db_field} $in ({$q} '{$field_value}%') ";
872 }elseif(!empty($parms['query_type']) && $parms['query_type'] == 'format'){
873 $stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
874 $where .= "{$db_field} $in (".string_format($parms['subquery'], $stringFormatParams).")";
876 $where .= "{$db_field} $in ({$parms['subquery']} '{$field_value}%')";
882 if($type == 'bool' && $field_value == 0) {
883 $where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
886 //check to see if this is coming from unified search or not
887 $UnifiedSearch = !empty($parms['force_unifiedsearch']);
888 if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch'){
889 $UnifiedSearch = true;
892 //check to see if this is a universal search, AND the field name is "last_name"
893 if($UnifiedSearch && strpos($db_field, 'last_name') !== false){
894 //split the string value, and the db field name
895 $string = explode(' ', $field_value);
896 $column_name = explode('.', $db_field);
897 //when a search is done with a space, we concatenate and search against the full name.
898 if(count($string)>1){
899 //add where clause against concatenated fields
900 $where .= $GLOBALS['db']->concat($column_name[0],array('first_name','last_name')) . " LIKE '{$field_value}%'";
901 $where .= ' OR ' . $GLOBALS['db']->concat($column_name[0],array('last_name','first_name')) . " LIKE '{$field_value}%'";
903 //no space was found, add normal where clause
904 $where .= $db_field . " like '".$field_value.$like_char."'";
909 //Check if this is a first_name, last_name search
910 if(isset($this->seed->field_name_map) && isset($this->seed->field_name_map[$db_field]))
912 $vardefEntry = $this->seed->field_name_map[$db_field];
913 if(!empty($vardefEntry['db_concat_fields']) && in_array('first_name', $vardefEntry['db_concat_fields']) && in_array('last_name', $vardefEntry['db_concat_fields']))
915 if(!empty($GLOBALS['app_list_strings']['salutation_dom']) && is_array($GLOBALS['app_list_strings']['salutation_dom']))
917 foreach($GLOBALS['app_list_strings']['salutation_dom'] as $salutation)
919 if(!empty($salutation) && strpos($field_value, $salutation) == 0)
921 $field_value = trim(substr($field_value, strlen($salutation)));
929 //field is not last name or this is not from global unified search, so do normal where clause
930 $where .= $db_field . " like '".$field_value.$like_char."'";
935 $where .= $db_field . ' not in ('.$field_value.')';
938 $where .= $db_field . ' in ('.$field_value.')';
941 if($type == 'bool' && $field_value == 0) {
942 $where .= $db_field . " = '0' OR " . $db_field . " IS NULL";
945 $where .= $db_field . " = '".$field_value ."'";
949 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
950 $where .= $db_field . " = ". $field_value;
952 // Create correct date_format conversion String
953 if($GLOBALS['db']->dbType == 'oci8') {
954 $where .= db_convert($db_field,'date_format',array("'YYYY-MM'")) . " = '" . $field_value . "'";
956 $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " = '" . $field_value . "'";
960 // tyoung bug 15971 - need to add these special cases into the $where query
962 $where .= $field_value;
965 $field_value = explode('<>', $field_value);
966 $where .= $db_field . " >= '".$field_value[0] . "' AND " .$db_field . " <= '".$field_value[1]."'";
969 $this->seed->listview_inner_join[] = $parms['innerjoin'] . " '" . $parms['value'] . "%')";
972 $where .= $db_field . " != '". $field_value . "'";
975 $where .= $db_field . " > '". $field_value . "'";
977 case 'greater_than_equals':
978 $where .= $db_field . " >= '". $field_value . "'";
981 $where .= $db_field . " < '". $field_value . "'";
983 case 'less_than_equals':
984 $where .= $db_field . " <= '". $field_value . "'";
987 if($GLOBALS['db']->dbType == 'mysql') {
988 $where .= "LEFT(" . $db_field . ",10) BETWEEN LEFT((current_date - interval '7' day),10) AND LEFT(current_date,10)";
990 elseif ($GLOBALS['db']->dbType == 'mssql') {
991 $where .= "DATEDIFF ( d , " . $db_field . " , GETDATE() ) <= 7 and DATEDIFF ( d , " . $db_field . " , GETDATE() ) >= 0";
995 if($GLOBALS['db']->dbType == 'mysql') {
996 $where .= "LEFT(" . $db_field . ",10) BETWEEN LEFT(current_date,10) AND LEFT((current_date + interval '7' day),10)";
998 elseif ($GLOBALS['db']->dbType == 'mssql') {
999 $where .= "DATEDIFF ( d , GETDATE() , " . $db_field . " ) <= 7 and DATEDIFF ( d , GETDATE() , " . $db_field . " ) >= 0";
1003 if ($GLOBALS['db']->dbType == 'mysql') {
1004 $where .= "LEFT(" . $db_field . ",7) = LEFT( (current_date + interval '1' month),7)";
1006 elseif ($GLOBALS['db']->dbType == 'mssql') {
1007 $where .= "(LEFT( ".$db_field.",4) = LEFT( (DATEADD(mm,1,GETDATE())),4)) and (DATEPART(yy, DATEADD(mm,1,GETDATE())) = DATEPART(yy, DATEADD(mm,1,".$db_field.")))";
1011 if ($GLOBALS['db']->dbType == 'mysql') {
1012 $where .= "LEFT(" . $db_field . ",7) = LEFT( (current_date - interval '1' month),7)";
1014 elseif ($GLOBALS['db']->dbType == 'mssql') {
1015 $where .= "LEFT(" . $db_field . ",4) = LEFT((DATEADD(mm,-1,GETDATE())),4) and DATEPART(yy," . $db_field . ") = DATEPART(yy, GETDATE())";
1019 if ($GLOBALS['db']->dbType == 'mysql') {
1020 $where .= "LEFT(" . $db_field . ",7) = LEFT( current_date,7)";
1022 elseif ($GLOBALS['db']->dbType == 'mssql') {
1023 $where .= "LEFT (" . $db_field . ",4) = LEFT( GETDATE(),4) and DATEPART(yy," . $db_field . ") = DATEPART(yy, GETDATE())";
1026 case 'last_30_days':
1027 if ($GLOBALS['db']->dbType == 'mysql') {
1028 $where .= "LEFT(" . $db_field . ",10) BETWEEN LEFT((current_date - interval '30' day),10) AND LEFT(current_date,10)";
1030 elseif ($GLOBALS['db']->dbType == 'mssql') {
1031 $where .= "DATEDIFF ( d , " . $db_field . " , GETDATE() ) <= 30 and DATEDIFF ( d , " . $db_field . " , GETDATE() ) >= 0";
1034 case 'next_30_days':
1035 if ($GLOBALS['db']->dbType == 'mysql') {
1036 $where .= $db_field . " BETWEEN (current_date) AND (current_date + interval '1' month)";
1038 elseif ($GLOBALS['db']->dbType == 'mssql') {
1039 $where .= "DATEDIFF ( d , GETDATE() , " . $db_field . " ) <= 30 and DATEDIFF ( d , GETDATE() , " . $db_field . " ) >= 0";
1043 if ($GLOBALS['db']->dbType == 'mysql') {
1044 $where .= "LEFT(" . $db_field . ",4) = EXTRACT(YEAR FROM ( current_date ))";
1046 elseif ($GLOBALS['db']->dbType == 'mssql') {
1047 $where .= "DATEPART(yy," . $db_field . ") = DATEPART(yy, GETDATE())";
1051 if ($GLOBALS['db']->dbType == 'mysql') {
1052 $where .= "LEFT(" . $db_field . ",4) = EXTRACT(YEAR FROM ( current_date - interval '1' year))";
1054 elseif ($GLOBALS['db']->dbType == 'mssql') {
1055 $where .= "DATEPART(yy," . $db_field . ") = DATEPART(yy,( dateadd(yy,-1,GETDATE())))";
1059 if ($GLOBALS['db']->dbType == 'mysql') {
1060 $where .= "LEFT(" . $db_field . ",4) = EXTRACT(YEAR FROM ( current_date + interval '1' year))";
1062 elseif ($GLOBALS['db']->dbType == 'mssql') {
1063 $where .= "DATEPART(yy," . $db_field . ") = DATEPART(yy,( dateadd(yy, 1,GETDATE())))";
1067 // OOTB fields are NULL, custom fields are blank
1068 $where .= '('.$db_field . ' IS NULL or ' . $db_field . "='')";
1069 if ($field_value != '')
1070 $where .= ' OR ' . $db_field . " in (".$field_value.')';
1076 if(!empty($where)) {
1078 array_push($where_clauses, '( '.$where.' )');
1081 array_push($where_clauses, $where);
1087 return $where_clauses;
1092 * isEmptyDropdownField
1094 * This function checks to see if a blank dropdown field was supplied. This scenario will occur where
1095 * a dropdown select is in single selection mode
1097 * @param $value Mixed dropdown value
1099 private function isEmptyDropdownField($name='', $value=array())
1101 $result = is_array($value) && isset($value[0]) && $value[0] == '';
1102 $GLOBALS['log']->debug("Found empty value for {$name} dropdown search key");