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 if(!empty($GLOBALS['layout_edit_mode'])){
102 $this->tpl = sugar_cached('studio/custom/working/modules/' . $module . '/SearchForm.html');
105 $this->tpl = get_custom_file_if_exists('modules/' . $module . '/SearchForm.html');
112 $this->bean = $seedBean;
113 $this->tabs = array(array('title' => $app_strings['LNK_BASIC_SEARCH'],
114 'link' => $module . '|basic_search',
115 'key' => $module . '|basic_search'),
116 array('title' => $app_strings['LNK_ADVANCED_SEARCH'],
117 'link' => $module . '|advanced_search',
118 'key' => $module . '|advanced_search'));
120 if(file_exists('modules/'.$this->module.'/index.php')){
121 $this->tabs[] = array('title' => $app_strings['LNK_SAVED_VIEWS'],
122 'link' => $module . '|saved_views',
123 'key' => $module . '|saved_views');
129 * Populate the searchFields from an array
131 * @param array $array array to search through
132 * @param string $switchVar variable to use in switch statement
133 * @param bool $addAllBeanFields true to process at all bean fields
135 function populateFromArray(&$array, $switchVar = null, $addAllBeanFields = true) {
138 if(empty($array['searchFormTab']) && empty($switchVar)) {
139 $array['searchFormTab'] = 'advanced_search';
142 if(!empty($array['searchFormTab']) || !empty($switchVar)) {
143 $arrayKeys = array_keys($array);
144 $searchFieldsKeys = array_keys($this->searchFields);
145 if(empty($switchVar)) $switchVar = $array['searchFormTab'];
148 foreach($this->searchFields as $name => $params) {
149 if(isset($array[$name . '_basic'])) {
150 $this->searchFields[$name]['value'] =
151 is_string($array[$name . '_basic'])?trim($array[$name . '_basic']):$array[$name . '_basic'];
154 if($addAllBeanFields) {
155 foreach($this->bean->field_name_map as $key => $params) {
156 if(in_array($key . '_basic' , $arrayKeys) && !in_array($key, $searchFieldsKeys)) {
158 $this->searchFields[$key] = array('query_type' => 'default',
159 'value' => $array[$key . '_basic']);
164 case 'advanced_search':
165 foreach($this->searchFields as $name => $params) {
166 if(isset($array[$name])) {
167 $this->searchFields[$name]['value'] = is_string($array[$name])?trim($array[$name]):$array[$name];
170 if((empty($array['massupdate']) || $array['massupdate'] == 'false') && $addAllBeanFields) {
171 foreach($this->bean->field_name_map as $key => $params) {
172 if(in_array($key, $arrayKeys) && !in_array($key, $searchFieldsKeys)) {
173 $this->searchFields[$key] = array('query_type' => 'default',
174 'value' => $array[$key]);
180 foreach($this->searchFields as $name => $params) {
181 if(isset($array[$name . '_basic'])) { // save basic first
182 $this->searchFields[$name]['value'] = $array[$name . '_basic'];
184 if(isset($array[$name])) { // overwrite by advanced if available
185 $this->searchFields[$name]['value'] = $array[$name];
188 if($addAllBeanFields) {
189 foreach($this->bean->field_name_map as $key => $params) {
190 if(!in_array($key, $searchFieldsKeys)) {
191 if(in_array($key . '_basic', $arrayKeys) ) {
192 $this->searchFields[$key] = array('query_type' => 'default',
193 'value' => $array[$key . '_basic']);
195 if(in_array($key, $arrayKeys)) {
196 $this->searchFields[$key] = array('query_type' => 'default',
197 'value' => $array[$key]);
207 * Populate the searchFields from $_REQUEST
209 * @param string $switchVar variable to use in switch statement
210 * @param bool $addAllBeanFields true to process at all bean fields
212 function populateFromRequest($switchVar = null, $addAllBeanFields = true) {
213 $this->populateFromArray($_REQUEST, $switchVar, $addAllBeanFields);
218 * The fuction will returns an array of filter conditions.
221 function generateSearchWhere($add_custom_fields = false, $module='') {
223 $values = $this->searchFields;
225 $where_clauses = array();
227 $table_name = $this->bean->object_name;
229 foreach($this->searchFields as $field=>$parms) {
230 $customField = false;
231 // Jenny - Bug 7462: We need a type check here to avoid database errors
232 // when searching for numeric fields. This is a temporary fix until we have
233 // a generic search form validation mechanism.
234 $type = (!empty($this->bean->field_name_map[$field]['type']))?$this->bean->field_name_map[$field]['type']:'';
235 if(!empty($this->bean->field_name_map[$field]['source']) && $this->bean->field_name_map[$field]['source'] == 'custom_fields'){
239 if ($type == 'int') {
240 if (!empty($parms['value'])) {
241 $tempVal = explode(',', $parms['value']);
243 foreach($tempVal as $key => $val) {
246 if(!empty($val) && !(is_numeric($val)))
251 $parms['value'] = $newVal;
254 // do not include where clause for custom fields with checkboxes that are unchecked
255 elseif($type == 'bool' && empty($parms['value']) && $customField) {
258 elseif($type == 'bool' && !empty($parms['value'])){
259 if ($parms['value'] == 'on'){
264 if(isset($parms['value']) && $parms['value'] != "") {
266 if(!empty($parms['operator'])) {
267 $operator = strtolower($parms['operator']);
270 if(is_array($parms['value'])) {
273 // If it is a custom field of multiselect we have to do some special processing
274 if($customField && !empty($this->bean->field_name_map[$field]['isMultiSelect']) && $this->bean->field_name_map[$field]['isMultiSelect']) {
275 $operator = 'custom_enum';
276 $db_field = $this->bean->table_name . "_cstm." . $field;
277 foreach($parms['value'] as $key => $val) {
278 if($val != ' ' and $val != '') {
279 $qVal = $GLOBALS['db']->quote($val);
280 if (!empty($field_value)) {
281 $field_value .= ' or ';
283 $field_value .= "$db_field like '$qVal' or $db_field like '%$qVal^%' or $db_field like '%^$qVal%' or $db_field like '%^$qVal^%'";
287 $operator = $operator != 'subquery' ? 'in' : $operator;
288 foreach($parms['value'] as $key => $val) {
289 if($val != ' ' and $val != '') {
290 if (!empty($field_value)) {
293 $field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
299 $field_value = $GLOBALS['db']->quote($parms['value']);
302 //set db_fields array.
303 if(!isset($parms['db_field'])) {
304 $parms['db_field'] = array($field);
307 if(isset($parms['my_items']) and $parms['my_items'] == true) {
308 global $current_user;
309 $field_value = $GLOBALS['db']->quote($current_user->id);
315 if($field_value != '') {
316 foreach ($parms['db_field'] as $db_field) {
318 if (strstr($db_field, '.') === false) {
320 $db_field = $this->bean->table_name . "." . $db_field;
322 $db_field = $this->bean->table_name . "_cstm." . $db_field;
327 if($type == 'date') {
328 // The regular expression check is to circumvent special case YYYY-MM
330 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
331 $db_field = $this->bean->db->convert($db_field, "date_format", "%Y-%m");
333 $field_value = $timedate->to_db_date($field_value, false);
334 $db_field = $this->bean->db->convert($db_field, "date_format", "%Y-%m-%d");
338 if($type == 'datetime'|| $type == 'datetimecombo') {
339 $dates = $timedate->getDayStartEndGMT($field_value);
340 $field_value = array($this->bean->db->convert($dates["start"], "datetime"),
341 $this->bean->db->convert($dates["end"], "datetime"));
342 $operator = 'between';
345 if($this->bean->db->supports('case_sensitive') && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
346 $db_field = 'upper(' . $db_field . ")";
347 $field_value = strtoupper($field_value);
357 if ( isset($parms['subquery_in_clause']) ) {
358 if ( !is_array($parms['subquery_in_clause']) ) {
359 $in = $parms['subquery_in_clause'];
361 elseif ( isset($parms['subquery_in_clause'][$field_value]) ) {
362 $in = $parms['subquery_in_clause'][$field_value];
365 $sq = $parms['subquery'];
368 if (isset($sq['OR'])){
373 if(empty($q) || strlen($q)<2) continue;
377 $where .= " {$db_field} $in ({$q} '{$field_value}%') ";
380 }elseif(!empty($parms['query_type']) && $parms['query_type'] == 'format'){
381 $stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
382 $where .= "{$db_field} $in (".string_format($parms['subquery'], $stringFormatParams).")";
384 $where .= "{$db_field} $in ({$parms['subquery']} '{$field_value}%')";
389 $where .= $db_field . " like ".$this->bean->db->quoted($field_value.'%');
392 $where .= $db_field . " in (".$field_value.')';
395 $where .= $db_field . " = ".$this->bean->db->quoted($field_value);
398 if(!is_array($field_value)) {
399 $field_value = explode('<>', $field_value);
401 $where .= "(". $db_field . " >= ".$this->bean->db->quoted($field_value[0]) .
402 " AND " .$db_field . " <= ".$this->bean->db->quoted($field_value[1]).")";
409 array_push($where_clauses, '( '.$where.' )');
412 array_push($where_clauses, $where);
418 return $where_clauses;
422 * displays the tabs (top of the search form)
424 * @param string $currentKey key in $this->tabs to show as the current tab
426 * @return string html
428 function displayTabs($currentKey) {
429 $GLOBALS['log']->debug('SearchForm.php->displayTabs(): tabs='.print_r($this->tabs,true));
431 $tabPanel = new SugarWidgetTabs($this->tabs, $currentKey, 'SUGAR.searchForm.searchFormSelect');
433 if(isset($_REQUEST['saved_search_select']) && $_REQUEST['saved_search_select']!='_none') {
434 $saved_search=loadBean('SavedSearch');
435 $saved_search->retrieveSavedSearch($_REQUEST['saved_search_select']);
438 $str = $tabPanel->display();
440 if(!empty($_REQUEST['displayColumns']))
441 $str .= 'SUGAR.savedViews.displayColumns = "' . $_REQUEST['displayColumns'] . '";';
442 elseif(isset($saved_search->contents['displayColumns']) && !empty($saved_search->contents['displayColumns']))
443 $str .= 'SUGAR.savedViews.displayColumns = "' . $saved_search->contents['displayColumns'] . '";';
444 if(!empty($_REQUEST['hideTabs']))
445 $str .= 'SUGAR.savedViews.hideTabs = "' . $_REQUEST['hideTabs'] . '";';
446 elseif(isset($saved_search->contents['hideTabs']) && !empty($saved_search->contents['hideTabs']))
447 $str .= 'SUGAR.savedViews.hideTabs = "' . $saved_search->contents['hideTabs'] . '";';
448 if(!empty($_REQUEST['orderBy']))
449 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $_REQUEST['orderBy'] . '";';
450 elseif(isset($saved_search->contents['orderBy']) && !empty($saved_search->contents['orderBy']))
451 $str .= 'SUGAR.savedViews.selectedOrderBy = "' . $saved_search->contents['orderBy'] . '";';
452 if(!empty($_REQUEST['sortOrder']))
453 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $_REQUEST['sortOrder'] . '";';
454 elseif(isset($saved_search->contents['sortOrder']) && !empty($saved_search->contents['sortOrder']))
455 $str .= 'SUGAR.savedViews.selectedSortOrder = "' . $saved_search->contents['sortOrder'] . '";';
463 * sets up the search forms, populates the preset values
467 global $mod_strings, $app_strings, $app_list_strings, $theme, $timedate;
468 $GLOBALS['log']->debug('SearchForm.php->setup()');
469 $this->xtpl = new XTemplate($this->tpl);
470 $this->xtpl->assign("MOD", $mod_strings);
471 $this->xtpl->assign("APP", $app_strings);
472 $this->xtpl->assign("THEME", $theme);
473 $this->xtpl->assign("CALENDAR_DATEFORMAT", $timedate->get_cal_date_format());
474 $this->xtpl->assign("USER_DATEFORMAT", '('. $timedate->get_user_date_format().')');
476 foreach($this->searchFields as $name => $params) {
477 if(isset($params['template_var'])) $templateVar = $params['template_var'];
478 else $templateVar = strtoupper($name);
479 if(isset($params['value'])) { // populate w/ preselected values
480 if(isset($params['options'])) {
481 $options = $app_list_strings[$params['options']];
482 if(isset($params['options_add_blank']) && $params['options_add_blank']) array_unshift($options, '');
483 $this->xtpl->assign($templateVar, get_select_options_with_id($options, $params['value']));
486 if(isset($params['input_type'])) {
487 switch($params['input_type']) {
488 case 'checkbox': // checkbox input
489 if($params['value'] == 'on' || $params['value'])
490 $this->xtpl->assign($templateVar, 'checked');
494 else {// regular text input
495 $this->xtpl->assign($templateVar, to_html($params['value']));
499 else { // populate w/o preselected values
500 if(isset($params['options'])) {
501 $options = $app_list_strings[$params['options']];
502 if(isset($params['options_add_blank']) && $params['options_add_blank']) array_unshift($options, '');
503 $this->xtpl->assign($templateVar, get_select_options_with_id($options, ''));
507 if (!empty($_REQUEST['assigned_user_id'])) $this->xtpl->assign("USER_FILTER", get_select_options_with_id(get_user_array(FALSE), $_REQUEST['assigned_user_id']));
508 else $this->xtpl->assign("USER_FILTER", get_select_options_with_id(get_user_array(FALSE), ''));
510 // handle my items only
511 if(isset($this->searchFields['current_user_only']) && isset($this->searchFields['current_user_only']['value']))
512 $this->xtpl->assign("CURRENT_USER_ONLY", "checked");
516 * displays the search form header
518 * @param string $view which view is currently being displayed
521 function displayHeader($view) {
522 global $current_user;
523 $GLOBALS['log']->debug('SearchForm.php->displayHeader()');
525 if(is_admin($current_user) && $_REQUEST['module'] != 'DynamicLayout' && !empty($_SESSION['editinplace'])){
526 $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>";
529 echo $header_text . $this->displayTabs($this->module . '|' . $view);
530 echo "<form name='search_form' class='search_form'>" .
531 "<input type='hidden' name='searchFormTab' value='{$view}'/>" .
532 "<input type='hidden' name='module' value='{$_REQUEST['module']}'/>" .
533 "<input type='hidden' name='action' value='{$_REQUEST['action']}'/>" .
534 "<input type='hidden' name='query' value='true'/>";
538 * displays the search form body, for example if basic_search is being displayed, then the function call would be
539 * displayWithHeaders('basic_search', $htmlForBasicSearchBody) {
541 * @param string $view which view is currently being displayed
542 * @param string $basic_search_text body of the basic search tab
543 * @param string $advanced_search_text body of the advanced search tab
544 * @param string $saved_views_text body of the saved views tab
547 function displayWithHeaders($view, $basic_search_text = '', $advanced_search_text = '', $saved_views_text = '') {
548 $GLOBALS['log']->debug('SearchForm.php->displayWithHeaders()');
549 $this->displayHeader($view);
550 echo "<div id='{$this->module}basic_searchSearchForm' " . (($view == 'basic_search') ? '' : "style='display: none'") . ">" . $basic_search_text . "</div>";
551 echo "<div id='{$this->module}advanced_searchSearchForm' " . (($view == 'advanced_search') ? '' : "style='display: none'") . ">" . $advanced_search_text . "</div>";
552 echo "<div id='{$this->module}saved_viewsSearchForm' " . (($view == 'saved_views') ? '' : "style='display: none'") . ">" . $saved_views_text . "</div>";
553 echo $this->getButtons();
555 // 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>';
559 * displays the basic search form body
561 * @param bool $header display this with headers
562 * @param bool $return echo or return the html
564 * @return string html of contents
566 function displayBasic($header = true, $return = false) {
567 global $current_user;
569 $this->bean->custom_fields->populateAllXTPL($this->xtpl, 'search' );
570 $this->xtpl->parse("main");
571 if(!empty($GLOBALS['layout_edit_mode'])){
572 $this->xtpl->parse("advanced");
574 $text = $this->xtpl->text("main");
575 if(!empty($GLOBALS['layout_edit_mode'])){
576 $text .= $this->xtpl->text("advanced");
578 if($header && empty($GLOBALS['layout_edit_mode'])) {
579 $this->displayWithHeaders('basic_search', $text);
582 if($return) return $text;
588 * displays the advanced search form body
590 * @param bool $header display this with headers
591 * @param bool $return echo or return the html
593 * @return string html of contents
595 function displayAdvanced($header = true, $return = false, $listViewDefs='', $lv='') {
596 global $current_user, $current_language;
597 $GLOBALS['log']->debug('SearchForm.php->displayAdvanced()');
598 $this->bean->custom_fields->populateAllXTPL($this->xtpl, 'search' );
599 if(!empty($listViewDefs) && !empty($lv)){
600 $GLOBALS['log']->debug('SearchForm.php->displayAdvanced(): showing saved search');
601 $savedSearch = new SavedSearch($listViewDefs[$this->module], $lv->data['pageData']['ordering']['orderBy'], $lv->data['pageData']['ordering']['sortOrder']);
602 $this->xtpl->assign('SAVED_SEARCH', $savedSearch->getForm($this->module, false));
603 $this->xtpl->assign('MOD_SAVEDSEARCH', return_module_language($current_language, 'SavedSearch'));
604 $this->xtpl->assign('ADVANCED_SEARCH_IMG', SugarThemeRegistry::current()->getImageURL('advanced_search.gif'));
605 //this determines whether the saved search subform should be rendered open or not
606 if(isset($_REQUEST['showSSDIV']) && $_REQUEST['showSSDIV']=='yes'){
607 $this->xtpl->assign('SHOWSSDIV', 'yes');
608 $this->xtpl->assign('DISPLAYSS', '');
610 $this->xtpl->assign('SHOWSSDIV', 'no');
611 $this->xtpl->assign('DISPLAYSS', 'display:none');
614 $this->xtpl->parse("advanced");
615 $text = $this->xtpl->text("advanced");
618 $this->displayWithHeaders('advanced_search', '', $text);
621 if($return) return $text;
627 * displays the saved views form body
629 * @param bool $header display this with headers
630 * @param bool $return echo or return the html
632 * @return string html of contents
634 function displaySavedViews($listViewDefs, $lv, $header = true, $return = false) {
635 global $current_user;
637 $savedSearch = new SavedSearch($listViewDefs[$this->module], $lv->data['pageData']['ordering']['orderBy'], $lv->data['pageData']['ordering']['sortOrder']);
640 $this->displayWithHeaders('saved_views', $this->displayBasic(false, true), $this->displayAdvanced(false, true), $savedSearch->getForm($this->module));
641 echo '<script>SUGAR.savedViews.handleForm();</script>';
644 echo $savedSearch->getForm($this->module, false);
649 * get the search buttons
651 * @return string html of contents
653 function getButtons() {
656 $SAVED_SEARCHES_OPTIONS = '';
657 $savedSearch = new SavedSearch();
658 $SAVED_SEARCHES_OPTIONS = $savedSearch->getSelect($this->module);
659 $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'/> ";
660 $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'/>";
662 if(!empty($SAVED_SEARCHES_OPTIONS) && $this->showSavedSearchOptions){
663 $str .= " <span class='white-space'>
664 | <b>{$app_strings['LBL_SAVED_SEARCH_SHORTCUT']}</b>
665 {$SAVED_SEARCHES_OPTIONS}
666 <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>
672 function toggleInlineSearch(){
673 if (document.getElementById('inlineSavedSearch').style.display == 'none'){
674 document.getElementById('showSSDIV').value = 'yes'
675 document.getElementById('inlineSavedSearch').style.display = '';
677 document.getElementById('up_down_img').src='".SugarThemeRegistry::current()->getImageURL('basic_search.gif')."';
678 document.getElementById('up_down_img').setAttribute('alt','".$GLOBALS['app_strings']['LBL_ALT_HIDE_OPTIONS']."');
682 document.getElementById('up_down_img').src='".SugarThemeRegistry::current()->getImageURL('advanced_search.gif')."';
683 document.getElementById('up_down_img').setAttribute('alt','".$GLOBALS['app_strings']['LBL_ALT_SHOW_OPTIONS']."');
684 document.getElementById('showSSDIV').value = 'no';
685 document.getElementById('inlineSavedSearch').style.display = 'none';