date range

[mpatwi]

can you do a date range search in sugarcrm search. from x date to y date retreive all records that match. if not i think that would be an important feature to add i think?

[andopes]

Hi, mpatwi

it is right possible.

You have to modify the modules/Opportunities/metadata/SearchFields.php and set a search field for begin date (operator: >) and another one for end date (operator: <)
Cheers

[pblag]

Hi mpatwi!

if you want we can do it for you.

please feel free to contact us.

[kenshiro]

can you do a date range search in sugarcrm search. from x date to y date retreive all records that match. if not i think that would be an important feature to add i think?

Hi,
it's available on the DEMO version of Enhanced Search.
You'll find also many options for text search and OR / NOT operators.

Take a look at this thread for more details.

[850039]

Hi, mpatwi

it is right possible.

You have to modify the modules/Opportunities/metadata/SearchFields.php and set a search field for begin date (operator: >) and another one for end date (operator: <)
Cheers

Hello, andopes

This modification does not work. Look into the source code of SearchForm.php and SearchForm2.php, there are no cases for operators '<' and '>':

 switch(strtolower($operator)) {
                            case 'subquery':
                                $sq = $parms['subquery'];
                                if(is_array($sq)){
                                    $and_or = ' AND ';
                                    if (isset($sq['OR'])){
                                        $and_or = ' OR ';
                                    }
                                    $first = true;
                                    foreach($sq as $q){
                                        if(empty($q) || strlen($q)<2) continue;
                                        if(!$first){
                                            $where .= $and_or;
                                        }
                                        $where .= " {$db_field} IN ({$q} '{$field_value}%') ";
                                        $first = false;
                                    }
                                }else{
                                  $where .= "{$db_field} IN ({$parms['subquery']} '{$field_value}%')";
                                }
 
                                break;
 
                            case 'like':
 
                                if(isset($parms['extra_search']) && !empty($parms['extra_search'])){
                                    //$where .=  "$parms[extra_search] like '$field_value$like_char' OR (($parms[extra_search]='') AND $db_field like '$field_value$like_char')";
                                    if($GLOBALS['db']->dbType == 'mysql') {
                                        $where .= "IFNULL($parms[extra_search],$db_field) like '$field_value$like_char'";
                                    }elseif($GLOBALS['db']->dbType == 'mssql'){
                                        $where .= "ISNULL($parms[extra_search],$db_field) like '$field_value$like_char'";
                                    }elseif($GLOBALS['db']->dbType == 'oci8'){
                                        $where .= "NVL($parms[extra_search],$db_field) like '$field_value$like_char'";
                                    }
                                }else{
                                    $where .=  $db_field . " like '".$field_value.$like_char."'";
                                }
                                break;
                            case 'in':
                                $where .=  $db_field . " in (".$field_value.')';
                                break;
                            case '=':
                                $where .=  $db_field . " = '".$field_value ."'";
                                break;
                            case 'db_date':
                                if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
                                  $where .=  $db_field . " = ". $field_value;
                                } else {
                                  // Create correct date_format conversion String
                                  if($GLOBALS['db']->dbType == 'oci8') {
                                      $where .= db_convert($db_field,'date_format',array("'YYYY-MM'")) . " = '" . $field_value . "'";
                                  } else {
                                      $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " = '" . $field_value . "'";
                                  }
                                }
                                break;
                            // tyoung bug 15971 - need to add these special cases into the $where query
                            case 'custom_enum':
                                $where .= $field_value;
                                break;
                        } 
  

So, do you have any idea how to make it work?

Thank you

[850039]

I solved this problem in next way:
I made custom class CustomSearchForm which extends standard SearchForm, then I copied method generateSearchWhere and added my own cases. All that I needed to make it work just require my custom class instead of standard class in my view.

[precar]

Hi,

I am trying to do the same thing as you. Would you mind giving me more detail on exactly what you did? Which files did you modify/add?


Thanks a lot,
Precar