include/SearchForm/SearchForm2.php

   1 <?php
   2 /*********************************************************************************
   3  * SugarCRM Community Edition is a customer relationship management program developed by
   4  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   5  *
   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.
  12  *
  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
  16  * details.
  17  *
  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
  21  * 02110-1301 USA.
  22  *
  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.
  25  *
  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.
  29  *
  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  ********************************************************************************/
  36
  37 /*
  38  * Created on May 30, 2007
  39  *
  40  * To change the template for this generated file go to
  41  * Window - Preferences - PHPeclipse - PHP - Code Templates
  42  */
  43 require_once('include/tabs.php');
  44 require_once('include/ListView/ListViewSmarty.php');
  45
  46 require_once('include/TemplateHandler/TemplateHandler.php');
  47 require_once('include/EditView/EditView2.php');
  48
  49
  50  class SearchForm extends EditView{
  51         var $seed = null;
  52         var $module = '';
  53         var $action = 'index';
  54         var $searchdefs = array();
  55         var $listViewDefs = array();
  56         var $lv;
  57         var $th;
  58     var $tpl;
  59     var $view = 'SearchForm';
  60     var $displayView = 'basic_search';
  61     var $formData;
  62     var $fieldDefs;
  63     var $customFieldDefs;
  64     var $tabs;
  65     var $parsedView = 'basic';
  66     //may remove
  67     var $searchFields;
  68     var $displaySavedSearch = true;
  69     //show the advanced tab
  70     var $showAdvanced = true;
  71     //show the basic tab
  72     var $showBasic = true;
  73     //array of custom tab to show declare in searchdefs (no custom tab if false)
  74     var $showCustom = false;
  75     // nb of tab to show
  76     var $nbTabs = 0;
  77     // hide saved searches drop and down near the search button
  78     var $showSavedSearchesOptions = true;
  79
  80     var $displayType = 'searchView';
  81
  82         function SearchForm($seed, $module, $action = 'index'){
  83                 $this->th = new TemplateHandler();
  84                 $this->th->loadSmarty();
  85                 $this->seed = $seed;
  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',
  91                             'name'   => 'basic',
  92                             'displayDiv'   => ''),
  93                       array('title'  => $GLOBALS['app_strings']['LNK_ADVANCED_SEARCH'],
  94                             'link'   => $module . '|advanced_search',
  95                             'key'    => $module . '|advanced_search',
  96                             'name'   => 'advanced',
  97                             'displayDiv'   => 'display:none'),
  98                        );
  99         $this->searchColumns = array () ;
 100         }
 101
 102         function setup($searchdefs, $searchFields = array(), $tpl, $displayView = 'basic_search', $listViewDefs = array()){
 103                 $this->searchdefs =  $searchdefs[$this->module];
 104                 $this->tpl = $tpl;
 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();
 113                 }
 114
 115         $this->searchFields = $searchFields[$this->module];
 116
 117         // Setub the tab array
 118         $this->tabs = array();
 119         if($this->showBasic){
 120             $this->nbTabs++;
 121             $this->tabs[]=array('title'  => $GLOBALS['app_strings']['LNK_BASIC_SEARCH'],
 122                                 'link'   => $this->module . '|basic_search',
 123                                 'key'    => $this->module . '|basic_search',
 124                                 'name'   => 'basic',
 125                                 'displayDiv' => '');
 126         }
 127         if($this->showAdvanced){
 128             $this->nbTabs++;
 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');
 134         }
 135         if(isset($this->showCustom) && is_array($this->showCustom)){
 136             foreach($this->showCustom as $v){
 137                 $this->nbTabs++;
 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',);
 143             }
 144         }
 145         }
 146
 147         function display($header = true){
 148         global $theme, $timedate;
 149                 $header_txt = '';
 150                 $footer_txt = '';
 151                 $return_txt = '';
 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
 157                 if($this->nbTabs>1){
 158                     $this->th->ss->assign('TABS', $this->_displayTabs($this->module . '|' . $this->displayView));
 159                 }
 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());
 168
 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];
 173         }
 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");
 178         } else {
 179           $pm = $match[2] == "pm" ? "%P" : "%p";
 180           $this->th->ss->assign('CALENDAR_FORMAT', $date_format . ' ' . $t23 . $time_separator . "%M" . $pm);
 181         }
 182         $this->th->ss->assign('TIME_SEPARATOR', $time_separator);
 183
 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);
 194                     }
 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', '');
 201                     }else{
 202                         $this->th->ss->assign('SHOWSSDIV', 'no');
 203                         $this->th->ss->assign('DISPLAYSS', 'display:none');
 204                     }
 205                 }
 206             }else{
 207                 $this->tabs[$tabkey]['displayDiv']='display:none';
 208             }
 209
 210         }
 211
 212         $this->th->ss->assign('TAB_ARRAY', $this->tabs);
 213
 214         $totalWidth = 0;
 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;
 227             }
 228         }
 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());
 235         }
 236         if ($this->module == 'Documents'){
 237             $this->th->ss->assign('DOCUMENTS_MODULE', true);
 238         }
 239         $return_txt = $this->th->displayTemplate($this->seed->module_dir, 'SearchForm_'.$this->parsedView, $this->tpl);
 240         if($header){
 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;
 246                 }
 247                 return $return_txt;
 248         }
 249
 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);
 253     }
 254
 255
 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);
 259     }
 260
 261
 262
 263         /**
 264      * displays the tabs (top of the search form)
 265      *
 266      * @param string $currentKey key in $this->tabs to show as the current tab
 267      *
 268      * @return string html
 269      */
 270     function _displayTabs($currentKey)
 271     {
 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']);
 275         }
 276
 277         $str = '<script>';
 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'] . '";';
 294
 295         $str .= '</script>';
 296
 297         return $str;
 298     }
 299
 300         /*
 301          * Generate the data
 302          */
 303         function _build_field_defs(){
 304                 $this->formData = array();
 305                 $this->fieldDefs = array();
 306                 foreach($this->searchdefs['layout'][$this->displayView] as $data){
 307                         if(is_array($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)
 310                                         continue;
 311                                 $data['name'] = $data['name'].'_'.$this->parsedView;
 312                                 $this->formData[] = array('field' => $data);
 313                                 $this->fieldDefs[$data['name']]= $data;
 314                         } else {
 315                                 $this->formData[] = array('field' => array('name'=>$data.'_'.$this->parsedView));
 316                         }
 317                 }
 318
 319                 if($this->seed){
 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']);
 327             }
 328
 329                 foreach($this->seed->toArray() as $name => $value) {
 330                     $fvName = $name.'_'.$this->parsedView;
 331                 if(!empty($this->fieldDefs[$fvName]))
 332                         $this->fieldDefs[$fvName] = array_merge($this->seed->field_defs[$name], $this->fieldDefs[$fvName]);
 333                     else{
 334                         $this->fieldDefs[$fvName] = $this->seed->field_defs[$name];
 335                         $this->fieldDefs[$fvName]['name'] = $this->fieldDefs[$fvName]['name'].'_'.$this->parsedView;
 336                     }
 337
 338                     if(isset($this->fieldDefs[$fvName]['type']) && $this->fieldDefs[$fvName]['type'] == 'relate') {
 339                         if(isset($this->fieldDefs[$fvName]['id_name'])) {
 340                            $this->fieldDefs[$fvName]['id_name'] .= '_'.$this->parsedView;
 341                         }
 342                     }
 343
 344                     if(isset($this->fieldDefs[$fvName]['options']) && isset($GLOBALS['app_list_strings'][$this->fieldDefs[$fvName]['options']]))
 345                 {
 346                         // fill in enums
 347                     $this->fieldDefs[$fvName]['options'] = $GLOBALS['app_list_strings'][$this->fieldDefs[$fvName]['options']];
 348                     //Hack to add blanks for parent types on search views
 349                     if ($this->fieldDefs[$fvName]['type'] == "parent_type" || $this->fieldDefs[$fvName]['type'] == "parent")
 350                     {
 351                         $this->fieldDefs[$fvName]['options'] = array_merge(array(""=>""), $this->fieldDefs[$fvName]['options']);
 352                     }
 353                     }
 354
 355                     if(isset($this->fieldDefs[$fvName]['function'])) {
 356
 357                         $this->fieldDefs[$fvName]['type']='multienum';
 358
 359                                 if(is_array($this->fieldDefs[$fvName]['function'])) {
 360                                    $this->fieldDefs[$fvName]['function']['preserveFunctionValue']=true;
 361                                 }
 362
 363                                 $function = $this->fieldDefs[$fvName]['function'];
 364
 365                                 if(is_array($function) && isset($function['name'])){
 366                                         $function_name = $this->fieldDefs[$fvName]['function']['name'];
 367                                 }else{
 368                                         $function_name = $this->fieldDefs[$fvName]['function'];
 369                                 }
 370
 371                                         if(!empty($this->fieldDefs[$fvName]['function']['returns']) && $this->fieldDefs[$fvName]['function']['returns'] == 'html'){
 372                                                 if(!empty($this->fieldDefs[$fvName]['function']['include'])){
 373                                                                 require_once($this->fieldDefs[$fvName]['function']['include']);
 374                                                 }
 375                                                 $value = $function_name($this->seed, $name, $value, $this->view);
 376                                                 $this->fieldDefs[$fvName]['value'] = $value;
 377                                         }else{
 378                                                 if(!isset($function['params']) || !is_array($function['params'])) {
 379                                                         $this->fieldDefs[$fvName]['options'] = $function_name($this->seed, $name, $value, $this->view);
 380                                                 } else {
 381                                                         $this->fieldDefs[$fvName]['options'] = call_user_func_array($function_name, $function['params']);
 382                                                 }
 383                                         }
 384                         }
 385                         if(isset($this->fieldDefs[$name]['type']) && $this->fieldDefs[$fvName]['type'] == 'function'
 386                        && isset($this->fieldDefs[$fvName]['function_name']))
 387                 {
 388                                 $value = $this->callFunction($this->fieldDefs[$fvName]);
 389                                 $this->fieldDefs[$fvName]['value'] = $value;
 390                         }
 391
 392                     $this->fieldDefs[$name]['value'] = $value;
 393
 394
 395                     if((!empty($_REQUEST[$fvName]) || (isset($_REQUEST[$fvName]) && $_REQUEST[$fvName] == '0'))
 396                 && empty($this->fieldDefs[$fvName]['function']['preserveFunctionValue'])) {
 397                         $value = $_REQUEST[$fvName];
 398                         $this->fieldDefs[$fvName]['value'] = $value;
 399                     }
 400
 401                 } //foreach
 402
 403
 404                 }
 405
 406         }
 407
 408             /**
 409      * Populate the searchFields from an array
 410      *
 411      * @param array $array array to search through
 412      * @param string $switchVar variable to use in switch statement
 413      * @param bool $addAllBeanFields true to process at all bean fields
 414      */
 415     function populateFromArray(&$array, $switchVar = null, $addAllBeanFields = true) {
 416
 417        if((!empty($array['searchFormTab']) || !empty($switchVar)) && !empty($this->searchFields)) {
 418                         $arrayKeys = array_keys($array);
 419             $searchFieldsKeys = array_keys($this->searchFields);
 420             if(empty($switchVar)) $switchVar = $array['searchFormTab'];
 421             //name of  the search tab
 422             $SearchName=str_replace('_search', '', $switchVar);
 423             if($switchVar=='saved_views'){
 424                 foreach($this->searchFields as $name => $params) {
 425                     foreach($this->tabs as $tabName){
 426                         if(!empty($array[$name . '_' . $tabName['name']])) {
 427                              $this->searchFields[$name]['value'] = $array[$name . '_' . $tabName['name']];
 428                              if(empty($this->fieldDefs[$name . '_' . $tabName['name']]['value'])) $this->fieldDefs[$name . '_' . $tabName['name']]['value'] = $array[$name . '_' . $tabName['name']];
 429                         }
 430                     }
 431                 }
 432                 if($addAllBeanFields) {
 433                     foreach($this->seed->field_name_map as $key => $params) {
 434                         if(!in_array($key, $searchFieldsKeys)) {
 435                             foreach($this->tabs->name as $tabName){
 436                                 if(in_array($key . '_' . $tabName['name'], $arrayKeys) ) {
 437                                                                         $this->searchFields[$key] = array('query_type' => 'default',
 438                                                                       'value'      => $array[$key . '_' . $tabName['name']]);
 439                                 }
 440                             }
 441                         }
 442                     }
 443                 }
 444
 445             }else{
 446
 447                 $fromMergeRecords = isset($array['merge_module']);
 448
 449                 foreach($this->searchFields as $name => $params) {
 450                                         $long_name = $name.'_'.$SearchName;
 451                                         /*nsingh 21648: Add additional check for bool values=0. empty() considers 0 to be empty Only repopulates if value is 0 or 1:( */
 452                         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'))))
 453                                         {
 454                         $this->searchFields[$name]['value'] = $array[$long_name];
 455                         if(empty($this->fieldDefs[$long_name]['value'])) {
 456                                 $this->fieldDefs[$long_name]['value'] = $array[$long_name];
 457                         }
 458                     }else if(!empty($array[$name]) && !$fromMergeRecords && !$this->isEmptyDropdownField($name, $array[$name])) { //basic
 459                         $this->searchFields[$name]['value'] = $array[$name];
 460                         if(empty($this->fieldDefs[$long_name]['value'])) {
 461                                 $this->fieldDefs[$long_name]['value'] = $array[$name];
 462                         }
 463                     }
 464
 465                     if(!empty($params['enable_range_search']) && isset($this->searchFields[$name]['value']))
 466                                         {
 467                                                 if(preg_match('/^range_(.*?)$/', $long_name, $match) && isset($array[$match[1].'_range_choice']))
 468                                                 {
 469                                                         $this->searchFields[$name]['operator'] = $array[$match[1].'_range_choice'];
 470                                                 }
 471                                         }
 472
 473                                         if(!empty($params['is_date_field']) && isset($this->searchFields[$name]['value']))
 474                                         {
 475                                                 global $timedate;
 476                                                 // 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)
 477                                                 $date_value = $timedate->to_db_date($this->searchFields[$name]['value'], false);
 478                                                 $this->searchFields[$name]['value'] = $date_value == '' ? $this->searchFields[$name]['value'] : $date_value;
 479                                         }
 480                 }
 481
 482                 if((empty($array['massupdate']) || $array['massupdate'] == 'false') && $addAllBeanFields) {
 483                     foreach($this->seed->field_name_map as $key => $params) {
 484                         if($key != 'assigned_user_name' && $key != 'modified_by_name')
 485                         {
 486                                 $long_name = $key.'_'.$SearchName;
 487
 488                                 if(in_array($key.'_'.$SearchName, $arrayKeys) && !in_array($key, $searchFieldsKeys) && !$this->isEmptyDropdownField($long_name, $array[$long_name]))
 489                                 {
 490
 491                                         $this->searchFields[$key] = array('query_type' => 'default', 'value' => $array[$long_name]);
 492
 493                                 if (!empty($params['type']) && $params['type'] == 'parent'
 494                                     && !empty($params['type_name']) && !empty($this->searchFields[$key]['value']))
 495                                 {
 496                                             require_once('include/SugarFields/SugarFieldHandler.php');
 497                                                                                 $sfh = new SugarFieldHandler();
 498                                                                 $sf = $sfh->getSugarField('Parent');
 499
 500                                         $this->searchFields[$params['type_name']] = array('query_type' => 'default',
 501                                                                                           'value'      => $sf->getSearchInput($params['type_name'], $array));
 502                                 }
 503
 504                                 if(empty($this->fieldDefs[$long_name]['value'])) {
 505                                     $this->fieldDefs[$long_name]['value'] =  $array[$long_name];
 506                                 }
 507                             }
 508                         }
 509                     }
 510                 }
 511
 512             }
 513         }
 514
 515
 516        if ( is_array($this->searchFields) ) {
 517            foreach ( $this->searchFields as $fieldName => $field ) {
 518                if ( !empty($field['value']) && is_string($field['value']) ) {
 519                    $this->searchFields[$fieldName]['value'] = trim($field['value']);
 520                }
 521            }
 522        }
 523
 524     }
 525
 526     /**
 527      * Populate the searchFields from $_REQUEST
 528      *
 529      * @param string $switchVar variable to use in switch statement
 530      * @param bool $addAllBeanFields true to process at all bean fields
 531      */
 532     function populateFromRequest($switchVar = null, $addAllBeanFields = true) {
 533         $this->populateFromArray($_REQUEST, $switchVar, $addAllBeanFields);
 534     }
 535
 536         function generateSearchWhere($add_custom_fields = false, $module='') {
 537         global $timedate;
 538
 539         $this->searchColumns = array () ;
 540         $values = $this->searchFields;
 541
 542         $where_clauses = array();
 543         $like_char = '%';
 544         $table_name = $this->seed->object_name;
 545         $this->seed->fill_in_additional_detail_fields();
 546
 547         //rrs check for team_id
 548
 549         foreach($this->searchFields as $field=>$parms) {
 550                         $customField = false;
 551             // Jenny - Bug 7462: We need a type check here to avoid database errors
 552             // when searching for numeric fields. This is a temporary fix until we have
 553             // a generic search form validation mechanism.
 554             $type = (!empty($this->seed->field_name_map[$field]['type']))?$this->seed->field_name_map[$field]['type']:'';
 555
 556                         if(!empty($parms['enable_range_search']) && empty($type))
 557                         {
 558                                 if(preg_match('/^start_range_(.*?)$/', $field, $match))
 559                                 {
 560                                         $real_field = $match[1];
 561                                         $start_field = 'start_range_' . $real_field;
 562                                         $end_field = 'end_range_' . $real_field;
 563
 564                                         if(isset($this->searchFields[$start_field]['value']) && isset($this->searchFields[$end_field]['value']))
 565                                         {
 566                                                 $this->searchFields[$real_field]['value'] = $this->searchFields[$start_field]['value'] . '<>' . $this->searchFields[$end_field]['value'];
 567                                                 $this->searchFields[$real_field]['operator'] = 'between';
 568                                                 $parms['value'] = $this->searchFields[$real_field]['value'];
 569                                                 $parms['operator'] = 'between';
 570
 571                                                 $field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
 572                                                 if($field_type == 'datetimecombo' || $field_type == 'datetime')
 573                                                 {
 574                                                     $type = $field_type;
 575                                                 }
 576
 577                                                 $field = $real_field;
 578                                                 unset($this->searchFields[$end_field]['value']);
 579                                         }
 580                                 } else if (preg_match('/^range_(.*?)$/', $field, $match) && isset($this->searchFields[$field]['value'])) {
 581                                         $real_field = $match[1];
 582
 583                                         //Special case for datetime and datetimecombo fields.  By setting the type here we allow an actual between search
 584                                         if($parms['operator'] == '=')
 585                                         {
 586                                            $field_type = isset($this->seed->field_name_map[$real_field]['type']) ? $this->seed->field_name_map[$real_field]['type'] : '';
 587                        if(strtolower($field_type) == 'readonly' && isset($this->seed->field_name_map[$real_field]['dbType']))
 588                            $field_type = $this->seed->field_name_map[$real_field]['dbType'];
 589
 590                                            if($field_type == 'datetimecombo' || $field_type == 'datetime' || $field_type == 'int')
 591                                            {
 592                                                   $type = $field_type;
 593                                            }
 594                                         }
 595
 596                                         $this->searchFields[$real_field]['value'] = $this->searchFields[$field]['value'];
 597                                         $this->searchFields[$real_field]['operator'] = $this->searchFields[$field]['operator'];
 598                                         $params['value'] = $this->searchFields[$field]['value'];
 599                                         $params['operator'] = $this->searchFields[$field]['operator'];
 600                                         unset($this->searchFields[$field]['value']);
 601                                         $field = $real_field;
 602                                 } else {
 603                             //Skip this range search field, it is the end field THIS IS NEEDED or the end range date will break the query
 604                         continue;
 605                                 }
 606                         }
 607
 608                 if(!empty($this->seed->field_name_map[$field]['source'])
 609                         && ($this->seed->field_name_map[$field]['source'] == 'custom_fields' ||
 610                                 //Non-db custom fields, such as custom relates
 611                                 ($this->seed->field_name_map[$field]['source'] == 'non-db'
 612                                 && (!empty($this->seed->field_name_map[$field]['custom_module']) ||
 613                                          isset($this->seed->field_name_map[$field]['ext2']))))){
 614                 $customField = true;
 615               }
 616
 617             if ($type == 'int') {
 618                 if (!empty($parms['value'])) {
 619                     $tempVal = explode(',', $parms['value']);
 620                     $newVal = '';
 621                     foreach($tempVal as $key => $val) {
 622                         if (!empty($newVal))
 623                             $newVal .= ',';
 624                         if(!empty($val) && !(is_numeric($val)))
 625                             $newVal .= -1;
 626                         else
 627                             $newVal .= $val;
 628                     }
 629                     $parms['value'] = $newVal;
 630                 }
 631             }
 632
 633             //Navjeet- 6/24/08 checkboxes have been changed to dropdowns, so we can query unchecked checkboxes! Bug: 21648.
 634
 635             // elseif($type == 'bool' && empty($parms['value']) && preg_match("/current_user_only/", string subject, array subpatterns, int flags, [int offset])) {
 636             //     continue;
 637             // }
 638             //
 639             elseif($type == 'html' && $customField) {
 640                 continue;
 641             }
 642
 643
 644             if(isset($parms['value']) && $parms['value'] != "") {
 645
 646                 $operator = 'like';
 647                 if(!empty($parms['operator'])) {
 648                     $operator = $parms['operator'];
 649                 }
 650
 651                 if(is_array($parms['value'])) {
 652                     $field_value = '';
 653
 654                     // always construct the where clause for multiselects using the 'like' form to handle combinations of multiple $vals and multiple $parms
 655                      if(/*$GLOBALS['db']->dbType != 'mysql' &&*/ !empty($this->seed->field_name_map[$field]['isMultiSelect']) && $this->seed->field_name_map[$field]['isMultiSelect']) {
 656                         // construct the query for multenums
 657                         // 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'
 658                         $operator = 'custom_enum';
 659                         $table_name = $this->seed->table_name ;
 660                         if ($customField)
 661                             $table_name .= "_cstm" ;
 662                         $db_field = $table_name . "." . $field;
 663
 664                             foreach($parms['value'] as $key => $val) {
 665                             if($val != ' ' and $val != '') {
 666                                        $qVal = $GLOBALS['db']->quote($val);
 667                                        if (!empty($field_value)) {
 668                                            $field_value .= ' or ';
 669                                        }
 670                                        $field_value .= "$db_field like '%^$qVal^%'";
 671                                 } else {
 672                                         $field_value .= '('.$db_field . ' IS NULL or '.$db_field."='^^' or ".$db_field."='')";
 673                                 }
 674                             }
 675
 676                     } else {
 677                         $operator = $operator != 'subquery' ? 'in' : $operator;
 678                             foreach($parms['value'] as $key => $val) {
 679                                 if($val != ' ' and $val != '') {
 680                                     if (!empty($field_value)) {
 681                                         $field_value .= ',';
 682                                     }
 683                                     $field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
 684                                 }
 685                                 // Bug 41209: adding a new operator "isnull" here
 686                                 // to handle the case when blank is selected from dropdown.
 687                                 // In that case, $val is empty.
 688                                 // When $val is empty, we need to use "IS NULL",
 689                                 // as "in (null)" won't work
 690                                 else if ($operator=='in') {
 691                                     $operator = 'isnull';
 692                                 }
 693                             }
 694                     }
 695
 696                 } else {
 697                     $field_value = $GLOBALS['db']->quote($parms['value']);
 698                 }
 699
 700                 //set db_fields array.
 701                 if(!isset($parms['db_field'])) {
 702                     $parms['db_field'] = array($field);
 703                 }
 704
 705                 //This if-else block handles the shortcut checkbox selections for "My Items" and "Closed Only"
 706                 if(!empty($parms['my_items'])) {
 707                     if( $parms['value'] == false ) {
 708                                                 continue;
 709                                         } else {
 710                                                 //my items is checked.
 711                                                 global $current_user;
 712                             $field_value = $GLOBALS['db']->quote($current_user->id);
 713                                                 $operator = '=' ;
 714                                         }
 715                 } else if(!empty($parms['closed_values']) && is_array($parms['closed_values'])) {
 716                     if( $parms['value'] == false ) {
 717                                                 continue;
 718                                         } else {
 719                                                 $field_value = '';
 720                                                 foreach($parms['closed_values'] as $closed_value)
 721                                                 {
 722                                                         $field_value .= ",'" . $GLOBALS['db']->quote($closed_value) . "'";
 723                                                 }
 724                             $field_value = substr($field_value, 1);
 725                                         }
 726                 }
 727
 728                 $where = '';
 729                 $itr = 0;
 730
 731                 if($field_value != '' || $operator=='isnull') {
 732
 733                     $this->searchColumns [ strtoupper($field) ] = $field ;
 734
 735                     foreach ($parms['db_field'] as $db_field) {
 736                                                 if (strstr($db_field, '.') === false) {
 737                                 //Try to get the table for relate fields from link defs
 738                                 if ($type == 'relate' && !empty($this->seed->field_name_map[$field]['link'])
 739                                         && !empty($this->seed->field_name_map[$field]['rname'])) {
 740                                                 $link = $this->seed->field_name_map[$field]['link'];
 741                                                 $relname = $link['relationship'];
 742                                                 if (($this->seed->load_relationship($link))){
 743                                                                                 //Martin fix #27494
 744                                                                                 $db_field = $this->seed->field_name_map[$field]['name'];
 745                                                 } else {
 746                                                         //Best Guess for table name
 747                                                         $db_field = strtolower($link['module']) . '.' . $db_field;
 748                                                 }
 749
 750
 751                                 }
 752                                 else if ($type == 'parent') {
 753                                         if (!empty($this->searchFields['parent_type'])) {
 754                                                 $parentType = $this->searchFields['parent_type'];
 755                                                 $rel_module = $parentType['value'];
 756                                                                         global $beanFiles, $beanList;
 757                                                 if(!empty($beanFiles[$beanList[$rel_module]])) {
 758                                                                         require_once($beanFiles[$beanList[$rel_module]]);
 759                                                                             $rel_seed = new $beanList[$rel_module]();
 760                                                                             $db_field = 'parent_' . $rel_module . '_' . $rel_seed->table_name . '.name';
 761                                                 }
 762                                         }
 763                                 }
 764                                 // Relate fields in custom modules and custom relate fields
 765                                 else if ($type == 'relate' && $customField && !empty($this->seed->field_name_map[$field]['module'])) {
 766                                         $db_field = !empty($this->seed->field_name_map[$field]['name'])?$this->seed->field_name_map[$field]['name']:'name';
 767                                 }
 768                            else if(!$customField){
 769                                if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
 770                                    $db_field = db_concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
 771                                else
 772                                    $db_field = $this->seed->table_name .  "." . $db_field;
 773                                 }else{
 774                                         if ( !empty($this->seed->field_name_map[$field]['db_concat_fields']) )
 775                                    $db_field = db_concat($this->seed->table_name .  "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
 776                                else
 777                                    $db_field = $this->seed->table_name .  "_cstm." . $db_field;
 778                                 }
 779
 780                         }
 781
 782                         if($type == 'date') {
 783                            // Collin - Have mysql as first because it's usually the case
 784                            // The regular expression check is to circumvent special case YYYY-MM
 785                            if($GLOBALS['db']->dbType == 'mysql') {
 786                                  if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
 787                                     $field_value = $timedate->to_db_date($field_value, false);
 788                                     $operator = '=';
 789                                  } else {
 790                                     $operator = 'db_date';
 791                                  }
 792                            } else if($GLOBALS['db']->dbType == 'mssql') {
 793                                  if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
 794                                     $field_value = "Convert(DateTime, '".$timedate->to_db_date($field_value, false)."')";
 795                                  }
 796                                  $operator = 'db_date';
 797                            } else {
 798                                      $field_value = $timedate->to_db_date($field_value, false);
 799                                      $operator = '=';
 800                            }
 801                         }
 802
 803                         if($type == 'datetime' || $type == 'datetimecombo') {
 804                                 try {
 805                                 // FG - bug45287 - If User asked for a range, takes edges from it.
 806                                 $placeholderPos = strpos($field_value, "<>");
 807                                 if ($placeholderPos !== FALSE && $placeholderPos > 0)
 808                                 {
 809                                     $datesLimit = explode("<>", $field_value);
 810                                     $dateStart = $timedate->getDayStartEndGMT($datesLimit[0]);
 811                                     $dateEnd = $timedate->getDayStartEndGMT($datesLimit[1]);
 812                                     $dates = $dateStart;
 813                                     $dates['end'] = $dateEnd['end'];
 814                                     $dates['enddate'] = $dateEnd['enddate'];
 815                                     $dates['endtime'] = $dateEnd['endtime'];
 816                                 }
 817                                 else
 818                                 {
 819                                     $dates = $timedate->getDayStartEndGMT($field_value);
 820                                 }
 821                                 // FG - bug45287 - Note "start" and "end" are the correct interval at GMT timezone
 822                                 $field_value = $dates["start"] . "<>" . $dates["end"];
 823                                 $operator = 'between';
 824                                 } catch(Exception $timeException) {
 825                                         //In the event that a date value is given that cannot be correctly processed by getDayStartEndGMT method,
 826                                         //just skip searching on this field and continue.  This may occur if user switches locale date formats
 827                                         //in another browser screen, but re-runs a search with the previous format on another screen
 828                                         $GLOBALS['log']->error($timeException->getMessage());
 829                                         continue;
 830                                 }
 831                         }
 832
 833                         // adjust date searches to take account for user timezone
 834                         // 'equals' and 'is between' cases are handled above.
 835                         if ($type =='' && !empty($parms['enable_range_search']) && $parms['enable_range_search']==true) {
 836                             // check if value is a db date or db datetime format
 837                             if (preg_match('/^(\d{4}-\d{2}-\d{2})( \d{2}:\d{2}:\d{2})?$/', $field_value)) {
 838                                 if ($operator == 'not_equal') {
 839                                     $adjDate = $timedate->getDayStartEndGMT($field_value);
 840                                     $field_value = $adjDate['start'] . '<>' . $adjDate['end'];
 841                                     $operator = 'date_not_equal';
 842                                 } elseif ($operator == 'greater_than' || $operator == 'less_than_equals') {
 843                                     $adjDate = $timedate->getDayStartEndGMT($field_value);
 844                                     $field_value = $adjDate['end'];
 845                                 } elseif ($operator == 'less_than' || $operator == 'greater_than_equals') {
 846                                     $adjDate = $timedate->getDayStartEndGMT($field_value);
 847                                     $field_value = $adjDate['start'];
 848                                 }
 849                                 // check if value is something like [last_month]|[next_7_days]|[this_year]|etc...
 850                             } elseif (preg_match('/^\[[(this|last|next)_][_a-z0-9]*\]$/', $field_value)) {
 851                                 switch($operator) {
 852                                     case 'last_7_days':
 853                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() - (6 * TimeDate::SECONDS_IN_A_DAY)));
 854                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
 855                                         break;
 856                                     case 'next_7_days':
 857                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
 858                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() + (6 * TimeDate::SECONDS_IN_A_DAY)));
 859                                         break;
 860                                     case 'next_month':
 861                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m")+1, 01,   date("Y"))));
 862                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m")+2, 01,   date("Y"))));
 863                                         break;
 864                                     case 'last_month':
 865                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m")-1, 01,   date("Y"))));
 866                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m"), 01,   date("Y"))));
 867                                         break;
 868                                     case 'this_month':
 869                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, date("m"), 01,   date("Y"))));
 870                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, -1, date("m")+1, 01,   date("Y"))));
 871                                         break;
 872                                     case 'last_30_days':
 873                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() - (29 * TimeDate::SECONDS_IN_A_DAY)));
 874                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
 875                                         break;
 876                                     case 'next_30_days':
 877                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y'));
 878                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', time() + (29 * TimeDate::SECONDS_IN_A_DAY)));
 879                                         break;
 880                                     case 'this_year':
 881                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01,   date("Y"))));
 882                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31,   date("Y"))));
 883                                         break;
 884                                     case 'last_year':
 885                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01,   date("Y")-1)));
 886                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31,   date("Y")-1)));
 887                                         break;
 888                                     case 'next_year':
 889                                         $startDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 01, 01,   date("Y")+1)));
 890                                         $endDate = $timedate->getDayStartEndGMT(date('m/d/Y', mktime(0, 0, 0, 12, 31,   date("Y")+1)));
 891                                         break;
 892                                 }
 893                                 $field_value = $startDate['start'] . "<>" . $endDate['end'];
 894                                 $operator = 'between';
 895                             }
 896                         }
 897
 898                         if($type == 'decimal' || $type == 'float' || $type == 'currency' || (!empty($parms['enable_range_search']) && empty($parms['is_date_field']))) {
 899                                                         require_once('modules/Currencies/Currency.php');
 900
 901                                                         //we need to handle formatting either a single value or 2 values in case the 'between' search option is set
 902                                                         //start by splitting the string if the between operator exists
 903                                                         $fieldARR = explode('<>', $field_value);
 904                                                         //set the first pass through boolean
 905                                                         $first_between = true;
 906
 907                                                         foreach($fieldARR as $fk => $fv){
 908                                                                 //reset the field value, it will be rebuild in the foreach loop below
 909                                                                 $tmpfield_value = unformat_number($fv);
 910
 911                                                                 if ( $type == 'currency' && stripos($field,'_usdollar')!==FALSE ) {
 912                                                                         // It's a US Dollar field, we need to do some conversions from the user's local currency
 913                                                                         $currency_id = $GLOBALS['current_user']->getPreference('currency');
 914                                                                         if ( empty($currency_id) ) {
 915                                                                                 $currency_id = -99;
 916                                                                         }
 917                                                                         if ( $currency_id != -99 ) {
 918                                                                                 $currency = new Currency();
 919                                                                                 $currency->retrieve($currency_id);
 920                                                                                 $field_value = $currency->convertToDollar($tmpfield_value);
 921                                                                         }
 922                                                                 }
 923
 924                                                                 //recreate the field value
 925                                                                 if($first_between){
 926                                                                         //set the field value with the new formatted temp value
 927                                                                         $field_value = $tmpfield_value;
 928                                                                 }else{
 929                                                                         //this is a between query, so append the between operator and add the second formatted temp value
 930                                                                         $field_value .= '<>'.$tmpfield_value;
 931                                                                 }
 932                                                                 //set the first pass through variable to false
 933                                                                 $first_between = false;
 934                                                         }
 935
 936                                                         if(!empty($parms['enable_range_search']) && $parms['operator'] == '=' && $type != 'int')
 937                                                         {
 938                                                                 // Databases can't really search for floating point numbers, because they can't be accurately described in binary,
 939                                                                 // So we have to fuzz out the math a little bit
 940                                                                 $field_value = ($field_value - 0.01) . "<>" . ($field_value + 0.01);
 941                                                                 $operator = 'between';
 942                                                         }
 943                                                 }
 944
 945
 946
 947                         $itr++;
 948                         if(!empty($where)) {
 949                             $where .= " OR ";
 950                         }
 951
 952                         switch(strtolower($operator)) {
 953                                 case 'subquery':
 954                                     $in = 'IN';
 955                                     if ( isset($parms['subquery_in_clause']) ) {
 956                                         if ( !is_array($parms['subquery_in_clause']) ) {
 957                                             $in = $parms['subquery_in_clause'];
 958                                         }
 959                                         elseif ( isset($parms['subquery_in_clause'][$field_value]) ) {
 960                                             $in = $parms['subquery_in_clause'][$field_value];
 961                                         }
 962                                     }
 963                                 $sq = $parms['subquery'];
 964                                         if(is_array($sq)){
 965                                     $and_or = ' AND ';
 966                                     if (isset($sq['OR'])){
 967                                         $and_or = ' OR ';
 968                                     }
 969                                     $first = true;
 970                                     foreach($sq as $q){
 971                                         if(empty($q) || strlen($q)<2) continue;
 972                                         if(!$first){
 973                                             $where .= $and_or;
 974                                         }
 975                                         $where .= " {$db_field} $in ({$q} '{$field_value}%') ";
 976                                         $first = false;
 977                                     }
 978                                 }elseif(!empty($parms['query_type']) && $parms['query_type'] == 'format'){
 979                                     $stringFormatParams = array(0 => $field_value, 1 => $GLOBALS['current_user']->id);
 980                                     $where .= "{$db_field} $in (".string_format($parms['subquery'], $stringFormatParams).")";
 981                                 }else{
 982                                     $where .= "{$db_field} $in ({$parms['subquery']} '{$field_value}%')";
 983                                 }
 984
 985                                 break;
 986
 987                             case 'like':
 988                                 if($type == 'bool' && $field_value == 0) {
 989                                     // Bug 43452 - FG - Added parenthesis surrounding the OR (without them the WHERE clause would be broken)
 990                                     $where .=  "( " . $db_field . " = '0' OR " . $db_field . " IS NULL )";
 991                                 }
 992                                 else {
 993                                         //check to see if this is coming from unified search or not
 994                                         $UnifiedSearch = !empty($parms['force_unifiedsearch']);
 995                                         if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'UnifiedSearch'){
 996                                                 $UnifiedSearch = true;
 997                                         }
 998
 999                                         //check to see if this is a universal search OR the field has db_concat_fields set in vardefs, AND the field name is "last_name"
1000                                                                         if(($UnifiedSearch || !empty($this->seed->field_name_map[$field]['db_concat_fields'])) && strpos($db_field, 'last_name') !== false){
1001                                                                                 //split the string value, and the db field name
1002                                                                                 $string = explode(' ', $field_value);
1003                                                                                 $column_name =  explode('.', $db_field);
1004                                                                                 //when a search is done with a space, we concatenate and search against the full name.
1005                                                                                 if(count($string)>1){
1006                                                                                     //add where clause against concatenated fields
1007                                                                                         $where .= $GLOBALS['db']->concat($column_name[0],array('first_name','last_name')) . " LIKE '{$field_value}%'";
1008                                                                                     $where .= ' OR ' . $GLOBALS['db']->concat($column_name[0],array('last_name','first_name')) . " LIKE '{$field_value}%'";
1009                                                                                 }else{
1010                                                                                         //no space was found, add normal where clause
1011                                                                                         $where .=  $db_field . " like '".$field_value.$like_char."'";
1012                                                                                 }
1013
1014                                                                         }
1015                                                                         else {
1016
1017                                                                                 //Check if this is a first_name, last_name search
1018                                                                                 if(isset($this->seed->field_name_map) && isset($this->seed->field_name_map[$db_field]))
1019                                                                                 {
1020                                                                                         $vardefEntry = $this->seed->field_name_map[$db_field];
1021                                                                                         if(!empty($vardefEntry['db_concat_fields']) && in_array('first_name', $vardefEntry['db_concat_fields']) && in_array('last_name', $vardefEntry['db_concat_fields']))
1022                                                                 {
1023                                                                           if(!empty($GLOBALS['app_list_strings']['salutation_dom']) && is_array($GLOBALS['app_list_strings']['salutation_dom']))
1024                                                                           {
1025                                                                                  foreach($GLOBALS['app_list_strings']['salutation_dom'] as $salutation)
1026                                                                                  {
1027                                                                                         if(!empty($salutation) && strpos($field_value, $salutation) == 0)
1028                                                                                         {
1029                                                                                            $field_value = trim(substr($field_value, strlen($salutation)));
1030                                                                                            break;
1031                                                                                         }
1032                                                                                  }
1033                                                                           }
1034                                                                 }
1035                                                                                 }
1036
1037                                                                                 //field is not last name or this is not from global unified search, so do normal where clause
1038                                                                                 $where .=  $db_field . " like '".$field_value.$like_char."'";
1039                                                                         }
1040                                 }
1041                                 break;
1042                             case 'not in':
1043                                 $where .= $db_field . ' not in ('.$field_value.')';
1044                                 break;
1045                             case 'in':
1046                                 $where .=  $db_field . ' in ('.$field_value.')';
1047                                 break;
1048                             case '=':
1049                                 if($type == 'bool' && $field_value == 0) {
1050                                     $where .=  $db_field . " = '0' OR " . $db_field . " IS NULL";
1051                                 }
1052                                 else {
1053                                     $where .=  $db_field . " = '".$field_value ."'";
1054                                 }
1055                                 break;
1056                             case 'db_date':
1057                                 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
1058                                   $where .=  $db_field . " = ". $field_value;
1059                                 } else {
1060                                   // Create correct date_format conversion String
1061                                   if($GLOBALS['db']->dbType == 'oci8') {
1062                                         $where .= db_convert($db_field,'date_format',array("'YYYY-MM'")) . " = '" . $field_value . "'";
1063                                   } else {
1064                                         $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " = '" . $field_value . "'";
1065                                   }
1066                                 }
1067                                 break;
1068                             // tyoung bug 15971 - need to add these special cases into the $where query
1069                             case 'custom_enum':
1070                                 $where .= $field_value;
1071                                 break;
1072                             case 'between':
1073                                 $field_value = explode('<>', $field_value);
1074                                 if (isset($field_type) && $field_type == 'int') {
1075                                     // mssql does not like to compare an int column with quoted decimal like '0.1'
1076                                     $where .= $db_field . " >= ".$field_value[0] . " AND " .$db_field . " <= ".$field_value[1];
1077                                 } else {
1078                                     $where .= $db_field . " >= '".$field_value[0] . "' AND " .$db_field . " <= '".$field_value[1]."'";
1079                                 }
1080                                 break;
1081                             case 'date_not_equal':
1082                                 $field_value = explode('<>', $field_value);
1083                                 $where .= $db_field . " < '".$field_value[0] . "' OR " .$db_field . " > '".$field_value[1]."'";
1084                                 break;
1085                             case 'innerjoin':
1086                                 $this->seed->listview_inner_join[] = $parms['innerjoin'] . " '" . $parms['value'] . "%')";
1087                                 break;
1088                                                         case 'not_equal':
1089                                                                 $where .= $db_field . " != '". $field_value . "'";
1090                                                                 break;
1091                                                         case 'greater_than':
1092                                                                 $where .= $db_field . " > '". $field_value . "'";
1093                                                                 break;
1094                                                         case 'greater_than_equals':
1095                                                                 $where .= $db_field . " >= '". $field_value . "'";
1096                                                                 break;
1097                                                         case 'less_than':
1098                                                                 $where .= $db_field . " < '". $field_value . "'";
1099                                                                 break;
1100                                                         case 'less_than_equals':
1101                                                                 $where .= $db_field . " <= '". $field_value . "'";
1102                                                                 break;
1103                             case 'isnull':
1104                                 // OOTB fields are NULL, custom fields are blank
1105                                 $where .= '('.$db_field . ' IS NULL or ' . $db_field . "='')";
1106                                 if ($field_value != '')
1107                                     $where .=  ' OR ' . $db_field . " in (".$field_value.')';
1108                                 break;
1109                         }
1110                     }
1111                 }
1112
1113                 if(!empty($where)) {
1114                     if($itr > 1) {
1115                         array_push($where_clauses, '( '.$where.' )');
1116                     }
1117                     else {
1118                         array_push($where_clauses, $where);
1119                     }
1120                 }
1121             }
1122         }
1123
1124         return $where_clauses;
1125     }
1126
1127
1128     /**
1129      * isEmptyDropdownField
1130      *
1131      * This function checks to see if a blank dropdown field was supplied.  This scenario will occur where
1132      * a dropdown select is in single selection mode
1133      *
1134      * @param $value Mixed dropdown value
1135      */
1136     private function isEmptyDropdownField($name='', $value=array())
1137     {
1138         $result = is_array($value) && isset($value[0]) && $value[0] == '';
1139         $GLOBALS['log']->debug("Found empty value for {$name} dropdown search key");
1140         return $result;
1141     }
1142  }
1143