Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Search between dates.

  1. #1
    lucasnomail is offline Member
    Join Date
    Nov 2009
    Posts
    15

    Default Search between dates.

    Hi I was wandering how to do search between two dates. Now I know so I want to share it.

    Idea is to search (filter) in oport
    user andopes shared just an idea how to do it but it took me some time to change his suggestion to work.

    here is what I did(easy and fast) for opportunity (by simple replacing opportunity it should work for other modules as well ):
    1. Go to Admin>Studio>Opportunities>Fields
    2. Add 2 fields from_date_c till_date_c
      • Data Type: Date
      • Default Value: -none-
      • Mass Update: (not checked)
      • Required Field:(not checked)
      • Audit:(not checked)
      • Importable: no
    3. Add those fields to Admin>Studio>Opportunities>Layouts>Search>(it is up to you if you use Basic or Advanced)
    4. Decide against which field you want to search (in my case date_closed)
    5. In folder (yourSugar)/custom/modules/Oppurtunities/ (if folder does not exit create it) create 2 files:
    6. controller.php
      PHP Code:
      <?php
      require_once('include/MVC/Controller/SugarController.php');
      require_once(
      'custom/modules/Opportunities/OpportunityInListView.php');

      class 
      OpportunitiesController extends SugarController {
          function 
      action_listview() {
              
      $this->view_object_map['bean'] = $this->bean;
              
      $this->view 'list';
              
      $GLOBALS['view'] = $this->view;
              
      $this->bean = new OpportunityInListView();
          }
      }
    7. OpportunityInListView.php
      PHP Code:
      <?php
      if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

      class 
      OpportunityInListView extends Opportunity {

          function 
      Opportunity() {
              
      parent::Opportunity();
          }

          function 
      create_new_list_query($order_by$where,$filter=array(),$params=array(), $show_deleted 0,$join_type=''$return_array false,$parentbean$singleSelect false){
              
      $ret_array parent::create_new_list_query($order_by$where,$filter,$params$show_deleted,$join_type$return_array,$parentbean$singleSelect);

              
      //this is for start date, don't forgot to change it for your field name
      //what this does is it replaces oportunities_cstm.from_date_c (the field we created and put to search view)
      $ret_array['where'] = str_replace("(opportunities_cstm.from_date_c = '""( opportunities.date_closed >= '"$ret_array['where']);
      ////this is for end of date range(upper bound)..
              
      $ret_array['where'] = str_replace("( opportunities_cstm.till_date_c = '""( opportunities.date_closed <= '"$ret_array['where']);

              return 
      $ret_array;
          }
      }
      ?>


    take care of opportunities_cstm.from_date_c, opportunities_cstm.till_date_c and don't forgot that both are checked against opportunities.date_closed(in my case)
    Last edited by lucasnomail; 2010-06-12 at 09:32 PM.

  2. #2
    nickgiguere is offline Senior Member
    Join Date
    Jun 2009
    Posts
    24

    Default Re: Search between dates.

    Is there anyway to do a date search using operators such as greater than or less than? e.g. search for any date before 12/31/2009 ...

  3. #3
    nickgiguere is offline Senior Member
    Join Date
    Jun 2009
    Posts
    24

    Default Re: Search between dates.

    One more question:

    I need to implement this in the leads module. How would I do that exactly? I've tried the following:
    PHP Code:
    <?php
    if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

    class 
    LeadInListView extends Person {

        function 
    LeadInListView() {
            
    parent::Person();
        }

        function 
    create_new_list_query($order_by$where,$filter=array(),$params=array(), $show_deleted 0,$join_type=''$return_array false,$parentbean$singleSelect false){
            
    $ret_array parent::create_new_list_query($order_by$where,$filter,$params$show_deleted,$join_type$return_array,$parentbean$singleSelect);

            
    //this is for start date, don't forgot to change it for your field name
    //what this does is it replaces leads_cstm.from_date_c (the field we created and put to search view)
    $ret_array['where'] = str_replace("(leads_cstm.from_date_c = '""( leads_cstm.last_inv_c >= '"$ret_array['where']);
    ////this is for end of date range(upper bound)..
            
    $ret_array['where'] = str_replace("( leads_cstm.to_date_c = '""( leads_cstm.last_inv_c <= '"$ret_array['where']);

            return 
    $ret_array;
        }
    }
    ?>
    and this for Controller.php

    PHP Code:
    <?php
    require_once('include/MVC/Controller/SugarController.php');
    require_once(
    'custom/modules/Leads/LeadInListView.php');

    class 
    LeadController extends SugarController {
        function 
    action_listview() {
            
    $this->view_object_map['bean'] = $this->bean;
            
    $this->view 'list';
            
    $GLOBALS['view'] = $this->view;
            
    $this->bean = new LeadInListView();
        }
    }
    it errors out

    It errors out

  4. #4
    datasponge is offline Sugar Community Member
    Join Date
    Mar 2008
    Location
    San Jose, CA, USA
    Posts
    552

    Default Re: Search between dates.

    I use Enhanced Search from SugarForge. It adds all these features and lots more to all modules.

    But I like the simple version posted by lucasnomail. It's a good alternative if I there are any issues. Thanks for posting it.

    Phil

  5. #5
    nickgiguere is offline Senior Member
    Join Date
    Jun 2009
    Posts
    24

    Default Re: Search between dates.

    Quote Originally Posted by datasponge View Post
    I use Enhanced Search from SugarForge. It adds all these features and lots more to all modules.

    But I like the simple version posted by lucasnomail. It's a good alternative if I there are any issues. Thanks for posting it.

    Phil
    I'd love to use Enhanced Search but I don't have $200 unfortunately.

  6. #6
    kenshiro is offline Sugar Community Member
    Join Date
    Mar 2007
    Location
    Macerata - ITALY
    Posts
    437

    Default Re: Search between dates.

    Quote Originally Posted by nickgiguere View Post
    I'd love to use Enhanced Search but I don't have $200 unfortunately.
    Hi,

    Good news:

    If you are running SugarCRM 5.1 or 5.2, the feature to search between dates is in the last demo (FREE) version of Enhanced Search 3.0 that you can freely download from the Sugarforge project's page.

    If you are running SugarCRM 5.5 you can use Enhanced Search 3.1 with other useful date features, such as the Extended Date Search options.
    Patrizio Gelosi.
    Dispage - http://www.dispage.com

    NEW!! Discover how to modify and draw on web pages from your browser with Jelliphy 2

    Follow us on Facebook and Twitter

  7. #7
    lucasnomail is offline Member
    Join Date
    Nov 2009
    Posts
    15

    Default Re: Search between dates.

    Don't get mad at me but I need to first ask.
    Have you named the other file (not controller.php) like this LeadInListView.php .... (I'm joking)
    There are few things that needs to be changed in order to use it in any model , sorry for not pointing those out earlier .
    I'm not sure if you are changing Person module or Lead but lets say is is lead and you put those files to
    custom/modules/Leads/
    code needs to look like this (I will show just chunks that require change )
    Code:
     class LeadInListView extends Lead {
        function Lead() {
            parent::Lead(); }
    following is just upgrade (It is safer) , but this will work if last_inv_c is date type field
    Code:
    $ret_array['where'] = str_replace("leads_cstm.from_date_c =", "leads_cstm.last_inv_c >=", $ret_array['where']); 
     $ret_array['where'] = str_replace("leads_cstm.to_date_c =", "leads_cstm.last_inv_c <=", $ret_array['where']);
    and just in case change controller.php as well
    Code:
    class LeadsController extends SugarController
    with those adjustments you are ready to go...

  8. #8
    nickgiguere is offline Senior Member
    Join Date
    Jun 2009
    Posts
    24

    Smile Re: Search between dates.

    DUDE! You are awesome. Thank you for your help. Works as it should ...

  9. #9
    bernyperera is offline Senior Member
    Join Date
    Nov 2009
    Posts
    49

    Default Re: Search between dates.

    Quote Originally Posted by lucasnomail View Post
    Don't get mad at me but I need to first ask.
    Have you named the other file (not controller.php) like this LeadInListView.php .... (I'm joking)
    There are few things that needs to be changed in order to use it in any model , sorry for not pointing those out earlier .
    I'm not sure if you are changing Person module or Lead but lets say is is lead and you put those files to
    custom/modules/Leads/
    code needs to look like this (I will show just chunks that require change )
    Code:
     class LeadInListView extends Lead {
        function Lead() {
            parent::Lead(); }
    following is just upgrade (It is safer) , but this will work if last_inv_c is date type field
    Code:
    $ret_array['where'] = str_replace("leads_cstm.from_date_c =", "leads_cstm.last_inv_c >=", $ret_array['where']); 
     $ret_array['where'] = str_replace("leads_cstm.to_date_c =", "leads_cstm.last_inv_c <=", $ret_array['where']);
    and just in case change controller.php as well
    Code:
    class LeadsController extends SugarController
    with those adjustments you are ready to go...
    Hi

    I tried to implement this for cases.

    My controller.php is as follows

    <?php
    require_once('include/MVC/Controller/SugarController.php');
    require_once('custom/modules/Cases/CaseInListView.php');

    class CasesController extends SugarController {
    function action_listview() {
    $this->view_object_map['bean'] = $this->bean;
    $this->view = 'list';
    $GLOBALS['view'] = $this->view;
    $this->bean = new CaseInListView();
    }
    }
    ?>

    My CaseInListView.php is as follows

    <?php
    if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

    class CaseInListView extends Case {

    function Case() {
    parent::Case();
    }

    function create_new_list_query($order_by, $where,$filter=array(),$params=array(), $show_deleted = 0,$join_type='', $return_array = false,$parentbean, $singleSelect = false){
    $ret_array = parent::create_new_list_query($order_by, $where,$filter,$params, $show_deleted,$join_type, $return_array,$parentbean, $singleSelect);


    //this is for start date, don't forgot to change it for your field name
    //what this does is it replaces oportunities_cstm.from_date_c (the field we created and put to search view)
    $ret_array['where'] = str_replace("(cases_cstm.from_date_c = '", "( cases_cstm.date_entered >= '", $ret_array['where']);
    ////this is for end of date range(upper bound)..
    $ret_array['where'] = str_replace("( cases_cstm.till_date_c = '", "( cases_cstm.date_entered <= '", $ret_array['where']);

    return $ret_array;
    }
    }
    ?>

    This did not work for me. When I try to go to Cases, it looks blank - nothing is shown.

    Please help me by pointing what wrong I have done.
    I am using 5.5.0.CE

    Thank you,
    Berny

  10. #10
    lucasnomail is offline Member
    Join Date
    Nov 2009
    Posts
    15

    Default Re: Search between dates.

    Hi bernyperera. Because you haven't tried enough. You will be the last one I will help. Otherwise people would just rename all Opportunity for whatever and tell me fix it, it doesn't work.

    Your code doesn't work because of this part
    Code:
    class CaseInListView extends Case {
    function Case() {
    parent::Case();
    }
    even thou file Case.php exists there is no class named Case (and never could be, "case" is PHP's reserved word) so the same is true about function.. However there is class named aCase and function named the same way.
    So fix for you should be
    PHP Code:
    class CaseInListView extends aCase {
    function 
    aCase() {
    parent::aCase();

    Sorry if I offended you,but you could try harder before posting(for example turn on E_ALL for php). I know sugar is complex, but.....

    Have a nice day everybody.

Page 1 of 3 123 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search between dates
    By tatty27 in forum Help
    Replies: 0
    Last Post: 2009-07-12, 10:16 PM
  2. Replies: 0
    Last Post: 2009-01-22, 05:58 PM
  3. Search between dates in Sugar 5.x
    By alesandro in forum Developer Help
    Replies: 1
    Last Post: 2008-03-28, 12:21 AM
  4. Search between dates
    By alvaher in forum Help
    Replies: 3
    Last Post: 2007-11-09, 04:55 PM
  5. 4.5 - how to search between dates
    By wojkal in forum Help
    Replies: 0
    Last Post: 2006-12-12, 12:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •