2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2012 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');
45 * SearchForm Template to use (xtpl)
50 * SearchField meta data array to use. Populated from moduleDir/metadata/SearchFields
60 * Module the search from is for
65 * meta data for the tabs to display
75 * Use to determine whether or not to show the saved search options
78 var $showSavedSearchOptions = true;
81 * loads SearchFields MetaData, sets member variables
83 * @param string $module moduleDir
84 * @param bean $seedBean seed bean to use
85 * @param string $tpl template to use, defaults to moduleDir/SearchForm.html
88 function SearchForm($module, &$seedBean, $tpl = null) {
91 $this->module = $module;
92 require_once('modules/' . $module . '/metadata/SearchFields.php');
93 if(file_exists('custom/modules/' . $module . '/metadata/SearchFields.php')){
94 require_once('custom/modules/' . $module . '/metadata/SearchFields.php');
98 //require_once('modules/' . $module . '/metadata/SearchFields.php');
99 $this->searchFields = $searchFields[$module];
101 $this->tpl = 'modules/' . $module . '/SearchForm.html';
102 if(!empty($GLOBALS['layout_edit_mode'])){
103 $this->tpl = sugar_cached('studio/custom/working/modules/' . $module . '/SearchForm.html');
110 $this->bean = $seedBean;
111 $this->tabs = array(array('title' => $app_strings['LNK_BASIC_SEARCH'],
112 'link' => $module . '|basic_search',
113 'key' => $module . '|basic_search'),
114 array('title' => $app_strings['LNK_ADVANCED_SEARCH'],
115 'link' => $module . '|advanced_search',
116 'key' => $module . '|advanced_search'));
118 if(file_exists('modules/'.$this->module.'/index.php')){
119 $this->tabs[] = array('title' => $app_strings['LNK_SAVED_VIEWS'],
120 'link' => $module . '|saved_views',
121 'key' => $module . '|saved_views');
127 * Populate the searchFields from an array
129 * @param array $array array to search through
130 * @param string $switchVar variable to use in switch statement
131 * @param bool $addAllBeanFields true to process at all bean fields
133 function populateFromArray(&$array, $switchVar = null, $addAllBeanFields = true) {
136 if(empty($array['searchFormTab']) && empty($switchVar)) {
137 $array['searchFormTab'] = 'advanced_search';
140 if(!empty($array['searchFormTab']) || !empty($switchVar)) {
141 $arrayKeys = array_keys($array);
142 $searchFieldsKeys = array_keys($this->searchFields);
143 if(empty($switchVar)) $switchVar = $array['searchFormTab'];
146 foreach($this->searchFields as $name => $params) {
147 if(isset($array[$name . '_basic'])) {
148 $this->searchFields[$name]['value'] =
149 is_string($array[$name . '_basic'])?trim($array[$name . '_basic']):$array[$name . '_basic'];
152 if($addAllBeanFields) {
153 foreach($this->bean->field_name_map as $key => $params) {
154 if(in_array($key . '_basic' , $arrayKeys) && !in_array($key, $searchFieldsKeys)) {
156 $this->searchFields[$key] = array('query_type' => 'default',
157 'value' => $array[$key . '_basic']);
162 case 'advanced_search':
163 foreach($this->searchFields as $name => $params) {
164 if(isset($array[$name])) {
165 $this->searchFields[$name]['value'] = is_string($array[$name])?trim($array[$name]):$array[$name];
168 if((empty($array['massupdate']) || $array['massupdate'] == 'false') && $addAllBeanFields) {
169 foreach($this->bean->field_name_map as $key => $params) {
170 if(in_array($key, $arrayKeys) && !in_array($key, $searchFieldsKeys)) {
171 $this->searchFields[$key] = array('query_type' => 'default',
172 'value' => $array[$key]);
178 foreach($this->searchFields as $name => $params) {
179 if(isset($array[$name . '_basic'])) { // save basic first
180 $this->searchFields[$name]['value'] = $array[$name . '_basic'];
182 if(isset($array[$name])) { // overwrite by advanced if available
183 $this->searchFields[$name]['value'] = $array[$name];
186 if($addAllBeanFields) {
187 foreach($this->bean->field_name_map as $key => $params) {
188 if(!in_array($key, $searchFieldsKeys)) {
189 if(in_array($key . '_basic', $arrayKeys) ) {
190 $this->searchFields[$key] = array('query_type' => 'default',
191 'value' => $array[$key . '_basic']);
193 if(in_array($key, $arrayKeys)) {
194 $this->searchFields[$key] = array('query_type' => 'default',
195 'value' => $array[$key]);
205 * Populate the searchFields from $_REQUEST
207 * @param string $switchVar variable to use in switch statement
208 * @param bool $addAllBeanFields true to process at all bean fields
210 function populateFromRequest($switchVar = null, $addAllBeanFields = true) {
211 $this->populateFromArray($_REQUEST, $switchVar, $addAllBeanFields);
216 * The fuction will returns an array of filter conditions.
219 function generateSearchWhere($add_custom_fields = false, $module='') {
221 $values = $this->searchFields;
223 $where_clauses = array();
225 $table_name = $this->bean->object_name;
227 foreach($this->searchFields as $field=>$parms) {
228 $customField = false;
229 // Jenny - Bug 7462: We need a type check here to avoid database errors
230 // when searching for numeric fields. This is a temporary fix until we have
231 // a generic search form validation mechanism.
232 $type = (!empty($this->bean->field_name_map[$field]['type']))?$this->bean->field_name_map[$field]['type']:'';
233 if(!empty($this->bean->field_name_map[$field]['source']) && $this->bean->field_name_map[$field]['source'] == 'custom_fields'){
237 if ($type == 'int') {
238 if (!empty($parms['value'])) {
239 $tempVal = explode(',', $parms['value']);
241 foreach($tempVal as $key => $val) {
244 if(!empty($val) && !(is_numeric($val)))
249 $parms['value'] = $newVal;
252 // do not include where clause for custom fields with checkboxes that are unchecked
253 elseif($type == 'bool' && empty($parms['value']) && $customField) {
256 elseif($type == 'bool' && !empty($parms['value'])){
257 if ($parms['value'] == 'on'){
262 if(isset($parms['value']) && $parms['value'] != "") {
264 if(!empty($parms['operator'])) {
265 $operator = strtolower($parms['operator']);
268 if(is_array($parms['value'])) {
271 // If it is a custom field of multiselect we have to do some special processing
272 if($customField && !empty($this->bean->field_name_map[$field]['isMultiSelect']) && $this->bean->field_name_map[$field]['isMultiSelect']) {
273 $operator = 'custom_enum';
274 $db_field = $this->bean->table_name . "_cstm." . $field;
275 foreach($parms['value'] as $key => $val) {
276 if($val != ' ' and $val != '') {
277 $qVal = $GLOBALS['db']->quote($val);
278 if (!empty($field_value)) {
279 $field_value .= ' or ';
281 $field_value .= "$db_field like '$qVal' or $db_field like '%$qVal^%' or $db_field like '%^$qVal%' or $db_field like '%^$qVal^%'";
285 $operator = $operator != 'subquery' ? 'in' : $operator;
286 foreach($parms['value'] as $key => $val) {
287 if($val != ' ' and $val != '') {
288 if (!empty($field_value)) {
291 $field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
297 $field_value = $GLOBALS['db']->quote($parms['value']);
300 //set db_fields array.
301 if(!isset($parms['db_field'])) {
302 $parms['db_field'] = array($field);
305 if(isset($parms['my_items']) and $parms['my_items'] == true) {
306 global $current_user;
307 $field_value = $GLOBALS['db']->quote($current_user->id);
313 if($field_value != '') {
314 foreach ($parms['db_field'] as $db_field) {
316 if (strstr($db_field, '.') === false) {
318 $db_field = $this->bean->table_name . "." . $db_field;
320 $db_field = $this->bean->table_name . "_cstm." . $db_field;
325 if($type == 'date') {
326 // The regular expression check is to circumvent special case YYYY-MM
328 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
329 $db_field = $this->bean->db->convert($db_field, "date_format", "%Y-%m");
331 $field_value = $timedate->to_db_date($field_value, false);
332 $db_field = $this->bean->db->convert($db_field, "date_format", "%Y-%m-%d");
336 if($type == 'datetime'|| $type == 'datetimecombo') {
337 $dates = $timedate->getDayStartEndGMT($field_value);
338 $field_value = array($this->bean->db->convert($dates["start"], "datetime"),
339 $this->bean->db->convert($dates["end"], "datetime"));
340 $operator = 'between';
343 if($this->bean->db->supports('case_sensitive') && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
344 $db_field = 'upper(' . $db_field . ")";
345 $field_value = strtoupper($field_value);
355 if ( isset($parms['subquery_in_clause']) ) {
356 if ( !is_array($parms['subquery_in_clause']) ) {
357 $in = $parms['subquery_in_clause'];
359 elseif ( isset($parms['subquery_in_clause'][$field_value]) ) {
360 $in = $parms['subquery_in_clause'][$field_value];
363 $sq = $parms['subquery'];
366 if (isset($sq['OR'])){
371 if(empty($q) || strlen($q)<2) continue;
375 $where .= " {$db_field} $in ({$q} '{$field_value}%') ";
378 }elseif(!empty($parms['query_type']) && $parms['query_type'] == 'format'){
379 $stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
380 $where .= "{$db_field} $in (".string_format($parms['subquery'], $stringFormatParams).")";
382 $where .= "{$db_field} $in ({$parms['subquery']} '{$field_value}%')";
387 $where .= $db_field . " like ".$this->bean->db->quoted($field_value.'%');
390 $where .= $db_field . " in (".$field_value.')';
393 $where .= $db_field . " = ".$this->bean->db->quoted($field_value);
396 if(!is_array($field_value)) {
397 $field_value = explode('<>', $field_value);
399 $where .= "(". $db_field . " >= ".$this->bean->db->quoted($field_value[0]) .
400 " AND " .$db_field . " <= ".$this->bean->db->quoted($field_value[1]).")";
407 array_push($where_clauses, '( '.$where.' )');
410 array_push($where_clauses, $where);
416 return $where_clauses;
420 * displays the tabs (top of the search form)
422 * @param string $currentKey key in $this->tabs to show as the current tab
424 * @return string html
426 function displayTabs($currentKey) {
427 $GLOBALS['log']->debug('SearchForm.php->displayTabs(): tabs='.print_r($this->tabs,true));
429 $tabPanel = new SugarWidgetTabs($this->tabs, $currentKey, 'SUGAR.searchForm.searchFormSelect');
431 if(isset($_REQUEST['saved_search_select']) && $_REQUEST['saved_search_select']!='_none') {
432 $saved_search=loadBean('SavedSearch');
433 $saved_search->retrieveSavedSearch($_REQUEST['saved_search_select']);
436 $str = $tabPanel->display();
438 if(!empty($_REQUEST['displayColumns']))
439 $str .= 'SUGAR.savedViews.displayColumns = "' . $_REQUEST['displayColumns'] . '";';
440 elseif(isset($saved_search->contents['displayColumns']) && !empty($saved_search->contents['displayColumns']))
441 $str .= 'SUGAR.savedViews.displayColumns = "' . $saved_search->contents['displayColumns'] . '";';
442 if(!empty($_REQUEST['hideTabs']))
443 $str .= 'SUGAR.savedViews.hideTabs = "' . $_REQUEST['hideTabs'] . '";';
444 elseif(isset($saved_search->contents['hideTabs']) && !empty($saved_search->contents['hideTabs']))
445 $str .= 'SUGAR.savedViews.hideTabs = "' . $saved_search->contents['hideTabs'] . '";';
446 if(!empty($_REQUEST['orderBy']))
447 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $_REQUEST['orderBy'] . '";';
448 elseif(isset($saved_search->contents['orderBy']) && !empty($saved_search->contents['orderBy']))
449 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $saved_search->contents['orderBy'] . '";';
450 if(!empty($_REQUEST['sortOrder']))
451 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $_REQUEST['sortOrder'] . '";';
452 elseif(isset($saved_search->contents['sortOrder']) && !empty($saved_search->contents['sortOrder']))
453 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $saved_search->contents['sortOrder'] . '";';
461 * sets up the search forms, populates the preset values
465 global $mod_strings, $app_strings, $app_list_strings, $theme, $timedate;
466 $GLOBALS['log']->debug('SearchForm.php->setup()');
467 $this->xtpl = new XTemplate($this->tpl);
468 $this->xtpl->assign("MOD", $mod_strings);
469 $this->xtpl->assign("APP", $app_strings);
470 $this->xtpl->assign("THEME", $theme);
471 $this->xtpl->assign("CALENDAR_DATEFORMAT", $timedate->get_cal_date_format());
472 $this->xtpl->assign("USER_DATEFORMAT", '('. $timedate->get_user_date_format().')');
474 foreach($this->searchFields as $name => $params) {
475 if(isset($params['template_var'])) $templateVar = $params['template_var'];
476 else $templateVar = strtoupper($name);
477 if(isset($params['value'])) { // populate w/ preselected values
478 if(isset($params['options'])) {
479 $options = $app_list_strings[$params['options']];
480 if(isset($params['options_add_blank']) && $params['options_add_blank']) array_unshift($options, '');
481 $this->xtpl->assign($templateVar, get_select_options_with_id($options, $params['value']));
484 if(isset($params['input_type'])) {
485 switch($params['input_type']) {
486 case 'checkbox': // checkbox input
487 if($params['value'] == 'on' || $params['value'])
488 $this->xtpl->assign($templateVar, 'checked');
492 else {// regular text input
493 $this->xtpl->assign($templateVar, to_html($params['value']));
497 else { // populate w/o preselected values
498 if(isset($params['options'])) {
499 $options = $app_list_strings[$params['options']];
500 if(isset($params['options_add_blank']) && $params['options_add_blank']) array_unshift($options, '');
501 $this->xtpl->assign($templateVar, get_select_options_with_id($options, ''));
505 if (!empty($_REQUEST['assigned_user_id'])) $this->xtpl->assign("USER_FILTER", get_select_options_with_id(get_user_array(FALSE), $_REQUEST['assigned_user_id']));
506 else $this->xtpl->assign("USER_FILTER", get_select_options_with_id(get_user_array(FALSE), ''));
508 // handle my items only
509 if(isset($this->searchFields['current_user_only']) && isset($this->searchFields['current_user_only']['value']))
510 $this->xtpl->assign("CURRENT_USER_ONLY", "checked");
514 * displays the search form header
516 * @param string $view which view is currently being displayed
519 function displayHeader($view) {
520 global $current_user;
521 $GLOBALS['log']->debug('SearchForm.php->displayHeader()');
523 if(is_admin($current_user) && $_REQUEST['module'] != 'DynamicLayout' && !empty($_SESSION['editinplace'])){
524 $header_text = "<a href='index.php?action=index&module=DynamicLayout&from_action=SearchForm&from_module=".$_REQUEST['module'] ."'>".SugarThemeRegistry::current()->getImage("EditLayout","border='0' align='bottom'",null,null,'.gif','Edit Layout')."</a>";
527 echo $header_text . $this->displayTabs($this->module . '|' . $view);
528 echo "<form name='search_form' class='search_form'>" .
529 "<input type='hidden' name='searchFormTab' value='{$view}'/>" .
530 "<input type='hidden' name='module' value='{$_REQUEST['module']}'/>" .
531 "<input type='hidden' name='action' value='{$_REQUEST['action']}'/>" .
532 "<input type='hidden' name='query' value='true'/>";
536 * displays the search form body, for example if basic_search is being displayed, then the function call would be
537 * displayWithHeaders('basic_search', $htmlForBasicSearchBody) {
539 * @param string $view which view is currently being displayed
540 * @param string $basic_search_text body of the basic search tab
541 * @param string $advanced_search_text body of the advanced search tab
542 * @param string $saved_views_text body of the saved views tab
545 function displayWithHeaders($view, $basic_search_text = '', $advanced_search_text = '', $saved_views_text = '') {
546 $GLOBALS['log']->debug('SearchForm.php->displayWithHeaders()');
547 $this->displayHeader($view);
548 echo "<div id='{$this->module}basic_searchSearchForm' " . (($view == 'basic_search') ? '' : "style='display: none'") . ">" . $basic_search_text . "</div>";
549 echo "<div id='{$this->module}advanced_searchSearchForm' " . (($view == 'advanced_search') ? '' : "style='display: none'") . ">" . $advanced_search_text . "</div>";
550 echo "<div id='{$this->module}saved_viewsSearchForm' " . (($view == 'saved_views') ? '' : "style='display: none'") . ">" . $saved_views_text . "</div>";
551 echo $this->getButtons();
553 // echo '<script type="text/javascript">Calendar.setup ({inputField : "search_jscal_field", ifFormat : "'.$timedate->get_cal_date_format().'", showsTime : false, button : "search_jscal_trigger", singleClick : true, step : 1});</script>';
557 * displays the basic search form body
559 * @param bool $header display this with headers
560 * @param bool $return echo or return the html
562 * @return string html of contents
564 function displayBasic($header = true, $return = false) {
565 global $current_user;
567 $this->bean->custom_fields->populateAllXTPL($this->xtpl, 'search' );
568 $this->xtpl->parse("main");
569 if(!empty($GLOBALS['layout_edit_mode'])){
570 $this->xtpl->parse("advanced");
572 $text = $this->xtpl->text("main");
573 if(!empty($GLOBALS['layout_edit_mode'])){
574 $text .= $this->xtpl->text("advanced");
576 if($header && empty($GLOBALS['layout_edit_mode'])) {
577 $this->displayWithHeaders('basic_search', $text);
580 if($return) return $text;
586 * displays the advanced search form body
588 * @param bool $header display this with headers
589 * @param bool $return echo or return the html
591 * @return string html of contents
593 function displayAdvanced($header = true, $return = false, $listViewDefs='', $lv='') {
594 global $current_user, $current_language;
595 $GLOBALS['log']->debug('SearchForm.php->displayAdvanced()');
596 $this->bean->custom_fields->populateAllXTPL($this->xtpl, 'search' );
597 if(!empty($listViewDefs) && !empty($lv)){
598 $GLOBALS['log']->debug('SearchForm.php->displayAdvanced(): showing saved search');
599 $savedSearch = new SavedSearch($listViewDefs[$this->module], $lv->data['pageData']['ordering']['orderBy'], $lv->data['pageData']['ordering']['sortOrder']);
600 $this->xtpl->assign('SAVED_SEARCH', $savedSearch->getForm($this->module, false));
601 $this->xtpl->assign('MOD_SAVEDSEARCH', return_module_language($current_language, 'SavedSearch'));
602 $this->xtpl->assign('ADVANCED_SEARCH_IMG', SugarThemeRegistry::current()->getImageURL('advanced_search.gif'));
603 //this determines whether the saved search subform should be rendered open or not
604 if(isset($_REQUEST['showSSDIV']) && $_REQUEST['showSSDIV']=='yes'){
605 $this->xtpl->assign('SHOWSSDIV', 'yes');
606 $this->xtpl->assign('DISPLAYSS', '');
608 $this->xtpl->assign('SHOWSSDIV', 'no');
609 $this->xtpl->assign('DISPLAYSS', 'display:none');
612 $this->xtpl->parse("advanced");
613 $text = $this->xtpl->text("advanced");
616 $this->displayWithHeaders('advanced_search', '', $text);
619 if($return) return $text;
625 * displays the saved views form body
627 * @param bool $header display this with headers
628 * @param bool $return echo or return the html
630 * @return string html of contents
632 function displaySavedViews($listViewDefs, $lv, $header = true, $return = false) {
633 global $current_user;
635 $savedSearch = new SavedSearch($listViewDefs[$this->module], $lv->data['pageData']['ordering']['orderBy'], $lv->data['pageData']['ordering']['sortOrder']);
638 $this->displayWithHeaders('saved_views', $this->displayBasic(false, true), $this->displayAdvanced(false, true), $savedSearch->getForm($this->module));
639 echo '<script>SUGAR.savedViews.handleForm();</script>';
642 echo $savedSearch->getForm($this->module, false);
647 * get the search buttons
649 * @return string html of contents
651 function getButtons() {
654 $SAVED_SEARCHES_OPTIONS = '';
655 $savedSearch = new SavedSearch();
656 $SAVED_SEARCHES_OPTIONS = $savedSearch->getSelect($this->module);
657 $str = "<input tabindex='2' title='{$app_strings['LBL_SEARCH_BUTTON_TITLE']}' onclick='SUGAR.savedViews.setChooser()' class='button' type='submit' name='button' value='{$app_strings['LBL_SEARCH_BUTTON_LABEL']}' id='search_form_submit'/> ";
658 $str .= "<input tabindex='2' title='{$app_strings['LBL_CLEAR_BUTTON_TITLE']}' onclick='SUGAR.searchForm.clear_form(this.form); return false;' class='button' type='button' name='clear' value=' {$app_strings['LBL_CLEAR_BUTTON_LABEL']} ' id='search_form_clear'/>";
660 if(!empty($SAVED_SEARCHES_OPTIONS) && $this->showSavedSearchOptions){
661 $str .= " <span class='white-space'>
662 | <b>{$app_strings['LBL_SAVED_SEARCH_SHORTCUT']}</b>
663 {$SAVED_SEARCHES_OPTIONS}
664 <span id='go_btn_span' style='display:none'><input tabindex='2' title='go_select' id='go_select' onclick='SUGAR.searchForm.clear_form(this.form);' class='button' type='button' name='go_select' value=' {$app_strings['LBL_GO_BUTTON_LABEL']} '/></span>
670 function toggleInlineSearch(){
671 if (document.getElementById('inlineSavedSearch').style.display == 'none'){
672 document.getElementById('showSSDIV').value = 'yes'
673 document.getElementById('inlineSavedSearch').style.display = '';
675 document.getElementById('up_down_img').src='".SugarThemeRegistry::current()->getImageURL('basic_search.gif')."';
676 document.getElementById('up_down_img').setAttribute('alt','".$GLOBALS['app_strings']['LBL_ALT_HIDE_OPTIONS']."');
680 document.getElementById('up_down_img').src='".SugarThemeRegistry::current()->getImageURL('advanced_search.gif')."';
681 document.getElementById('up_down_img').setAttribute('alt','".$GLOBALS['app_strings']['LBL_ALT_SHOW_OPTIONS']."');
682 document.getElementById('showSSDIV').value = 'no';
683 document.getElementById('inlineSavedSearch').style.display = 'none';