where is db query from base modules?

[Holusa]

Hallo,

where is queryies definition for accounts, contacts, etc...?

I need insert custom where prarameter to basic query...for ex.: select * from accounts where name like '%a%'.

thanks,

@mh

[andopes]

Hi Holusa

You meed to:
1. Create a new script containing a class which extends the module class: custom/modules/<ModuleName>/ModuleNameListView.php
This class will extends the default module class and implement the method create_new_list_query()

2. Create a custom controller for these modules: custom/modules/<ModuleName>/controller.php
Implement the method action_listview() where you will override the $bean->list for the ListView:

     function action_listview() {
        require_once('custom/modules/<ModuleName>/<ModuleName>ListView.php');
 
        $this->bean = new <ClassName>ListView();
    } 
  

3. Override the $ret_array inside the create_new_list_query according to your needs.

[Holusa]

Hi Andopes,

thx for your quick reply. But im testing this two days and nothitg.
Any tutorial or help?

What i need?
Filter data in base list in accounts module.
SELECT * FROM accounts WHERE account.name LIKE '%a%';

and use this query as base for extending with searches.

Thank you.

Hi Holusa

You meed to:
1. Create a new script containing a class which extends the module class: custom/modules/<ModuleName>/ModuleNameListView.php
This class will extends the default module class and implement the method create_new_list_query()

2. Create a custom controller for these modules: custom/modules/<ModuleName>/controller.php
Implement the method action_listview() where you will override the $bean->list for the ListView:

     function action_listview() {
        require_once('custom/modules/<ModuleName>/<ModuleName>ListView.php');
 
        $this->bean = new <ClassName>ListView();
    } 
  

3. Override the $ret_array inside the create_new_list_query according to your needs.

[andopes]

Hi Holusa

custom/modules/<ModuleName>/controller.php

 <?PHP
require_once('include/MVC/Controller/SugarController.php');
require_once('modules/Opportunities/OpportunityListView.php');
 
class OpportunitiesController extends SugarController {
    function action_listview() {
        $this->view = 'list';
        $GLOBALS['view'] = $this->view;
        $this->bean = new OpportunityListView();
        $this->view_object_map['bean'] = $this->bean;
    }
}
?>  

custom/modules/OpportunityListView

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
class OportunityListView extends Opportunity {
    function OpportunityListView() {
        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);
        
        $ret_array['select'] .= " , up2_insertionorders.department_id, up2_insertionorders.advertiser_id, up2_insertionorders.publisher_id ";
        
        $ret_array['from'] .= " INNER JOIN up2_insertionorders ON up2_placements.up2_insertionorders_id = up2_insertionorders.id ";
        
        $ret_array['where'] = str_replace('up2_placements.department_id', 'up2_insertionorders.department_id', $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.advertiser_name like '" . $_POST['advertiser_name_basic'] . "%'", "up2_insertionorders.advertiser_id in (SELECT id FROM `accounts` WHERE name LIKE '" . $_POST['advertiser_name_basic'] . "')", $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.publisher_name like '" . $_POST['publisher_name_basic'] . "%'", "up2_insertionorders.publisher_id in (SELECT id FROM `accounts` WHERE name LIKE '" . $_POST['publisher_name_basic'] . "')", $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.io_order_number_name", 'up2_insertionorders.name', $ret_array['where']);
        
        $date_modified_basicFormatted = $_POST['date_modified_basic'];
        
        $date_modified_basicFormatted = date("Y-m-d", strtotime($date_modified_basicFormatted));
        
        $ret_array['where'] = str_replace("up2_placements.date_modified like '" . $_POST['date_modified_basic'] . "%'", "up2_placements.date_modified LIKE '" . $date_modified_basicFormatted . "%' ", $ret_array['where']);
        
        $ret_array['where'] .= " AND up2_insertionorders.status = 'booked' ";
 
        return $ret_array;
    }
}
?>  

Obviously you need to modify the class name, function name and other stuffs to accomplish your needs.

Cheers

[Holusa]

Hi Andopes,

If i use your solution, Sugar wrote "Call to a member function setup() on a non-object in D:\xampp\htdocs\sugar\include\MVC\Controller\Contr ollerFactory.php on line 59"

What is this? How i setup this setup()?

@mh

Hi Holusa

custom/modules/<ModuleName>/controller.php

 <?PHP
require_once('include/MVC/Controller/SugarController.php');
require_once('modules/Opportunities/OpportunityListView.php');
 
class OpportunitiesController extends SugarController {
    function action_listview() {
        $this->view = 'list';
        $GLOBALS['view'] = $this->view;
        $this->bean = new OpportunityListView();
        $this->view_object_map['bean'] = $this->bean;
    }
}
?>  

custom/modules/OpportunityListView

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
class OportunityListView extends Opportunity {
    function OpportunityListView() {
        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);
        
        $ret_array['select'] .= " , up2_insertionorders.department_id, up2_insertionorders.advertiser_id, up2_insertionorders.publisher_id ";
        
        $ret_array['from'] .= " INNER JOIN up2_insertionorders ON up2_placements.up2_insertionorders_id = up2_insertionorders.id ";
        
        $ret_array['where'] = str_replace('up2_placements.department_id', 'up2_insertionorders.department_id', $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.advertiser_name like '" . $_POST['advertiser_name_basic'] . "%'", "up2_insertionorders.advertiser_id in (SELECT id FROM `accounts` WHERE name LIKE '" . $_POST['advertiser_name_basic'] . "')", $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.publisher_name like '" . $_POST['publisher_name_basic'] . "%'", "up2_insertionorders.publisher_id in (SELECT id FROM `accounts` WHERE name LIKE '" . $_POST['publisher_name_basic'] . "')", $ret_array['where']);
        
        $ret_array['where'] = str_replace("up2_placements.io_order_number_name", 'up2_insertionorders.name', $ret_array['where']);
        
        $date_modified_basicFormatted = $_POST['date_modified_basic'];
        
        $date_modified_basicFormatted = date("Y-m-d", strtotime($date_modified_basicFormatted));
        
        $ret_array['where'] = str_replace("up2_placements.date_modified like '" . $_POST['date_modified_basic'] . "%'", "up2_placements.date_modified LIKE '" . $date_modified_basicFormatted . "%' ", $ret_array['where']);
        
        $ret_array['where'] .= " AND up2_insertionorders.status = 'booked' ";
 
        return $ret_array;
    }
}
?>  

Obviously you need to modify the class name, function name and other stuffs to accomplish your needs.

Cheers

[vishwasrao]

Hi andopes,

It worked for me.

I done acoording to ur instructions.but there is one problem .

In listview when i click on record there is error

Error retrieving record. This record may be deleted or you may not be authorized to view it.
you know reason behind it

[andopes]

Hi guys

Let me know the script you had wrote based on my example.

Cheers

[mel0]

Hi Andopes,

If i use your solution, Sugar wrote: "Notice: Undefined index: phone_mobile in D:\wamp\www\Sugar\modules\imob_Cerere\imob_CerereL istView.php on line 15"

 <?PHP
require_once('include/MVC/Controller/SugarController.php');
require_once('modules/imob_Cerere/imob_CerereListView.php');
 
class imob_CerereController extends SugarController {
function action_listview() {
        $this->view = 'list';
        $GLOBALS['view'] = $this->view;
        $this->bean = new imob_CerereListView();
        $this->view_object_map['bean'] = $this->bean;
    }
}
?>  

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
require_once('modules/imob_Cerere/imob_Cerere.php');
class imob_CerereListView extends imob_Cerere {
    function imob_CerereListView(){
    parent::imob_Cerere();
    }
function create_new_list_query( $order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = 'inner', $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);
        $ret_array['select'] .= ' ,phone_mobile as phone_mobilel ';
        $ret_array['from'] = ' inner join Contacts Contacts.id=imob_cerere.contact_id_c ';
        $ret_array['where'] .= " AND Contacts.phone_mobile like '". $_POST['phone_mobile'] ."%' "; //line 15
        $ret_array['order_by'] .= ' ';
                      
        return $ret_array;
    }
   }
?>  

The problem is $_POST['phone_mobile'] ...how can I write?

Thank you.

[eggsurplus]

That means it's not being passed. Are you entering the mobile phone #? If so, try the $_REQUEST var instead.

Here's an alternate implementation:

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
require_once('modules/imob_Cerere/imob_Cerere.php');
class imob_CerereListView extends imob_Cerere {
    function imob_CerereListView(){
    parent::imob_Cerere();
    }
function create_new_list_query( $order_by, $where, $filter = array(), $params = array(), $show_deleted = 0, $join_type = 'inner', $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);
        $ret_array['select'] .= ' ,phone_mobile as phone_mobilel ';
        $ret_array['from'] = ' inner join Contacts Contacts.id=imob_cerere.contact_id_c ';
        if(!empty($_REQUEST['phone_mobile'] )) $ret_array['where'] .= " AND Contacts.phone_mobile like '". $_REQUEST['phone_mobile'] ."%' "; //line 15
        $ret_array['order_by'] .= ' ';
                      
        return $ret_array;
    }
   }
?>  

[mel0]

Hi eggsurplus,

thx for your quick reply. I'im testing this and nothitg, we have another solution? I need search phone_mobile but this is in Contacts module.

Thank you.