How to search from date to date?

[octant]

Dear Members,

I am looking for a way to search some records from date to date, don't know how to handle those kinds of requests.
For example, how to search for all meetings occurred in November 2007 with appropriate fields in the request form.

I tried wildcards in the date field search form but it seems that wildcards are accepted only in text fields, not in dates fields. Off course, I can retirn all meetings and sort them by the "date" column ... but it's not so friendly.

Any idea,
Thanks for Help

Alain Ganuchaud
OCTANT INFORMATIQUE
alain.ganuchaud@octant-fr.com

[jspencer]

I second this request.

I have tried an old "module" that added improved search fields to the system, but it didn't quite work right with v4.5x and after updating from .e to .f it got even worse so I just removed it. I also tried using the studio to add the "Start Date" to the meetings Search Form but that didn't work either.

[andopes]

Hi, Alain.

Searching part of a date, not the full date is not easily customized.

You probably will need to edit the ListView.php of your module and perhaps something else.

We can discuss it better if you can.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[GameboyRMH]

I got this to work in 5 beta 2 and 5 RC:

I've added a date field for my Accounts (for what date the company launched). My dates show as yyyy-mm-dd, but I suppose this depends on your format settings. Anyways, if I search this field using "2007-11" any companies that launched in November 2007 show up. This is the only date related search I've discovered though :\

A date-to-date search is definitely something that's needed...

[anna]

I have a search for date ranges in my custom module. To be able to this I had to modify core files in include/SearchForm/SearchForm.php (add any operator, rather than just '='). The addition in SearchFields.html and SearchFields.php of each module should be minimal. The date search I have works with any date format.

If the fields you are searching on are datetime fields, it is a little more tricky because all times are stored in GMT, thus you would need to account for the difference before passing the value to WHERE clause to get the cutoff dates right.

Let me know if you need more info.

[GameboyRMH]

Anna, please post some more info on your search customizations!

[andopes]

Hi, Anna.

Your search customizations should be right useful for all.

Thank you in advice.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[anna]

Sorry for the delay, got caught up in some other stuff..

I hope I got all the files that needed to be modified listed below - if it doesn't work out for you, I might have skipped something, so let me know if there are problems!

Note #1: the timezones must be loaded in your mysql DB for the conversion in SearchFields.php to work out. If you are only comparing date fields (not datetime) then you do not need to worry about conversion or loading timezones.

Note #2: this comes with no warranty! To the best of my knowledge, it works properly on my system

Hope this helps

================================================== ============

Changes in include/SearchForm/SearchForm.php (look for AKB comments)

Around line 306

 if($type == 'date') {
  // Collin - Have mysql as first because it's usually the case
  // The regular expression check is to circumvent special case YYYY-MM
  if($GLOBALS['db']->dbType == 'mysql') {
    if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
       $field_value = $timedate->to_db_date($field_value, false);
       //===AKB: removed to allow any operator $operator = '=';  
    } else {
       $operator = 'db_date';    
 } 
 
.....
} 
  

Around line 338:

 switch(strtolower($operator)) {
  case 'like':
    $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) {
       //=== AKB: removed  $where .=  $db_field . " = ". $field_value;
       //=== AKB: added
       $where .=  $db_field . " $operator ". $field_value;
       //=== AKB: end added
     } 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 {
          //=== AKB removed:  $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " = '" . $field_value . "'";    
          //=== AKB added
      $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " $operator '" . $field_value . "'";
          //=== AKB end added    
       }
.....
 
} 
  

Then in SearchFields.php of a module add this:

 global $current_user;
// determine user timezone to enable search for date on a datetime field
$user_timezone = $current_user->getPreference('timezone');
if (strlen($user_timezone) <= 0 ){
    // set default
    $user_timezone = "America/Kentucky/Louisville";
}
 
$searchFields['your_module'] = array (
 ......        
'date_entered_from' => array('query_type'=> 'default', 'operator' => '>=', 'db_field' => array("DATE(CONVERT_TZ(your_module.date_entered, 'GMT', '$user_timezone'))")),
        'date_entered_till' => array('query_type'=> 'default', 'operator' => '<=', 'db_field' => array("DATE(CONVERT_TZ(your_module.date_entered, 'GMT', '$user_timezone'))")),
 
); 
  

Finally, in SearchForm.html of a module adjust the search fields (here it is just adding _till and _from to date_entered):

  <tr>
     <td class="dataLabel" valign="top"><slot>{MOD.LBL_DATE_ENTERED}<br> <span class='dateFormat'>{USER_DATEFORMAT}</span>    </slot></td>    
    <td class="dataField" valign="top"><slot>
      <span class='dataLabel'>{MOD.LBL_FROM}</span>
      <input name='date_entered_from' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');" id='jscal_fielddate_entered_from' type='text'  size='10' maxlength='10' value='{DATE_ENTERED_FROM}'> 
      <img src='themes/{THEME}/images/jscalendar.gif' alt='Enter Date'  id='jscal_triggerdate_entered_from' align='absmiddle'> 
      <span class='dataLabel'>{MOD.LBL_TILL}</span>        
      <script type='text/javascript'> Calendar.setup ({inputField : 'jscal_fielddate_entered_from', ifFormat : '{CALENDAR_DATEFORMAT}', showsTime : false, button : 'jscal_triggerdate_entered_from', singleClick : true, step : 1});</script>
      <input name='date_entered_till' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');" id='jscal_fielddate_entered_till' type='text'  size='10' maxlength='10' value='{DATE_ENTERED_TILL}'> 
      <img src='themes/{THEME}/images/jscalendar.gif' alt='Enter Date'  id='jscal_triggerdate_entered_till' align='absmiddle'>         
      <script type='text/javascript'> Calendar.setup ({inputField : 'jscal_fielddate_entered_till', ifFormat : '{CALENDAR_DATEFORMAT}', showsTime : false, button : 'jscal_triggerdate_entered_till', singleClick : true, step : 1});</script>
     </slot>
</td> 
</tr> 
 

[andopes]

Thank you a lot, Anna.

Everybody will appreciate this solution.

Have a great weekend.

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[maschek]

I just took a look how to do this in Sugar5, so, based on Anna's solution above, you have to do the following steps:

1. add date from and date to to your search from
Edit modules/module/metadata/searchdefs.php
for me the new fields will be:

  array('name' => 'start_date', 'type'=>'date', 'label' => 'LBL_DATA_FROM', 'displayParams'=>array('showFormats'=>true)),
 array('name' => 'end_date', 'type'=>'date', 'label' => 'LBL_DATA_TO', 'displayParams'=>array('showFormats'=>true)), 
  

2. tell Sugar how to deal with the new fields
Edit modules/module/metadata/SearchFields.php

  'start_date' => array( 'query_type'=>'default', 'operator' => '>=', 'db_field'=>array('data_custom')),
 'end_date' => array( 'query_type'=>'default', 'operator' => '<=', 'db_field'=>array('data_custom')), 
  

for me data_custom is the actual name of the field in the database.
The operators above will not do anything yet, see further step.

3. tell Sugar some more about your date fields
All we need to tell that these are date fields, but for this we have to edit
modules/module/vardefs.php

   'start_date' => 
  array (
    'name' => 'start_date',
    'type' => 'date',
    'source'=>'non-db',
  ),
  'end_date' => 
  array (
    'name' => 'end_date',
    'type' => 'date',
    'source'=>'non-db',
  ), 
  

4. patch Sugar to be able to use the new operators
Edit include/SearchForm/SearchForm2.php
around line 522

 if($GLOBALS['db']->dbType == 'mysql') {
     if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
        $field_value = $timedate->to_db_date($field_value, false);
        //$operator = '=';  
     } else {
        //$operator = 'db_date';    
     }
     //adam@dri - stay with operator db_date
     $operator = 'db_date'; 
  

and around line 573

 case 'db_date':
    //adam@dri, have a chance to modify operator here
    $realop = ($parms['operator']) ? $parms['operator'] : '=';
    if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
      $where .=  $db_field . " $realop '$field_value'";
    } else {
      // Create correct date_format conversion String
      if($GLOBALS['db']->dbType == 'oci8') {
          $where .= db_convert($db_field,'date_format',array("'YYYY-MM'")) . " $realop '" . $field_value . "'";    
      } else {
          $where .= db_convert($db_field,'date_format',array("'%Y-%m'")) . " $realop '" . $field_value . "'";    
      }
    }
    break;