How To: Use a custom query in a dashlet

[eggsurplus]

I recently ran into an issue where I did not want the default query for a dashlet to be used. The biggest issue was the table_name.deleted = 0 condition that was being added to the where clause by default. Looking through the code there is no option to remove this. So to trick Sugar into giving the desired query I had to do the following:

1. Open the dashlet file. This one was a custom module so it was modules/CustomModule/Dashlets/CustomModuleDashlet/CustomModuleDashlet.php
2. Add a buildWhere function to override the DashletGeneric buildWhere:

     function buildWhere() {
        global $current_user;
 
        $returnArray = array();
        array_push($returnArray, '(1=1 OR \'dummystring\' = \'');
 
        return $returnArray;
    } 
  

3. Override/add a process function:

     function process() {
        global $current_language, $app_list_strings, $image_path, $current_user;        
        $mod_strings = return_module_language($current_language, 'Meetings');
        
 
        $lvsParams = array(
                            'custom_select' => '',
                            'custom_from' => '',
                            'custom_where' => '\'))) AND custom_module.id is null ',
                            'distinct' => true
                     );
        
        parent::process($lvsParams);
     } 
  

This then creates the following where clause:

where (((1=1 OR 'dummystring' = ')) AND custom_module.deleted=0'))) AND custom_module.id is null

Which turns the custom_module.deleted condition into a string. That along with the 1=1 will cause it to always return true making it return all rows as desired.

[blak3r]

Thanks this was very helpful.

Do you know off hand how you could add to this the ability to include SQL joins

For example, lets say you had a custom account field you wanted to display in a column of the opportunities dashlet.

[sonia28av]

Need to know the same....please help...

[blak3r]

Hi Sonia,

The Tracker dashlet (at least in v6.0 pro) does make use of some custom queries. None that included joins but if you studied that code a lot it might help.

It was a little complicated since there where multiple queries and filter criteria which were selectable plus all the multiple language support makes the code a little harder follow. But, certainly doable.


I'd really like to find a simple sql query dashlet which let you specify a query and it'd create a datagrid with the values returned... and just use the column names from the select statement as the column headers.

[blak3r]

Here is an example dashlet I created which shows Tasks which were assigned to me by other people.

If you want the full code do a search for Task Dashlet ++ on sugarforge. I'll be posting 4 customized task dashlets there once my project is approved.

<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
/*********************************************************************************
 * Skeleton Task Dashlet
 *
 * Author: Blake Robertson 
 * Date: December 15th, 2010
 * 
 * How to use this file...
 *  - Change anywhere the text skeleton (two occurances class name and constructor name)
 *  - Change anywhere CHANGEME appears to the appropriate values.
 *  - Customize the where
 *
 ********************************************************************************/

require_once('include/Dashlets/DashletGeneric.php');


class MyTasksAssignedByOthersDashlet extends DashletGeneric { 

	function process() { 
        global $current_language, $app_list_strings, $image_path, $current_user;         
         
		
// NOTE: date_*_flag = 1 when the date is NULL which is the opposite you'd think... 

// DATES Past Start Date
//  'custom_where' => ' AND ((Date(tasks.date_start) <= Date(Now())) OR (tasks.date_start_flag=1)))',

// OVERDUE TASKS
//	'custom_where' => ' AND ((Date(tasks.date_start) <= Date(Now()) AND (tasks.date_due_flag=1)) OR (Date(tasks.date_due) <= Date(Now())) )',

// Tasks assigned to me by other people!
// Other changes, show the column "Created By" instead of Start Date.
// 'custom_where' => ' AND tasks.created_by != "' . $current_user->id . '"',

// Tasks I created for other people.
// Other changes, show the column assigned_to instead of start date., uncheck the "My Items" by default
// 'custom_where' => ' AND tasks.created_by = "' . $current_user->id . '" AND tasks.assigned_user_id != "' . $current_user->id . '"',
                        

        $lvsParams = array( 
                            'custom_select' => '', 
                            'custom_from' => '', 
							'custom_where' => ' AND tasks.created_by != "' . $current_user->id . '"',
							'distinct' => true							
                     ); 
         
        parent::process($lvsParams); 
    } 

    function MyTasksAssignedByOthersDashlet($id, $def = null) {
        global $current_user, $app_strings;
			
		// Uncomment line below if you don't want dashlet restricted to items you own
		// $def = array( 'myItemsOnly' => false );
        parent::DashletGeneric($id, $def);
		        
        if(empty($def['title'])) $this->title = 'Tasks - Created by a Team Member';

		// This is the filter criteria, it's usually in a .data.php file, i moved it here so i'd have less files to modify.
        $this->searchFields = array('name'           => array('default' => ''),
													   'priority'       => array('default' => ''),
                                                       'status'         => array('default' => array('Not Started', 'In Progress', 'Pending Input')),
                                                       'date_entered'   => array('default' => ''),
                                                       'date_start'       => array('default' => ''),                                                          
                                                       'date_due'       => array('default' => ''),
                                                       'team_id'          => array('default' => '', 'label' => 'LBL_TEAMS'),
                                                       'assigned_user_id' => array('type'    => 'assigned_user_name',
																				   'label'   => 'LBL_ASSIGNED_TO', 
                                                                                   'default' => $current_user->name));
       
	    // This is the columns that show up in the list view, customize accordinly.  
		$this->columns  = array('set_complete' => array('width'    => '1', 
                                                                          'label'    => 'LBL_LIST_CLOSE',
                                                                          'default'  => true,
                                                                          'sortable' => false),
                                                   'name' => array('width'   => '40', 
                                                                   'label'   => 'LBL_SUBJECT',
                                                                   'link'    => true,
                                                                   'default' => true),
                                                   'priority' => array('width'   => '10',
                                                                       'label'   => 'LBL_PRIORITY',
                                                                       'default' => true),
                                                   'status' => array('width'   => '8', 
                                                                     'label'   => 'LBL_STATUS',
																	 'default' => true),                                                               
                                                   'date_start' => array('width'   => '15', 
                                                                         'label'   => 'LBL_START_DATE',
                                                                         'default' => false),                                                                                                       
                                                   'time_start' => array('width'   => '15', 
                                                                         'label'   => 'LBL_START_TIME',
                                                                         'default' => false),
                                                   'date_due' => array('width'   => '15', 
                                                                       'label'   => 'LBL_DUE_DATE',
                                                                       'default' => true),                              
                                                                     
                                                   'date_entered' => array('width'   => '15', 
                                                                           'label'   => 'LBL_DATE_ENTERED'),
                                                   'date_modified' => array('width'   => '15', 
                                                                           'label'   => 'LBL_DATE_MODIFIED'),    
                                                   'created_by' => array('width'   => '8', 
                                                                         'label'   => $GLOBALS['app_strings']['LBL_CREATED'],
                                                                         'sortable' => true,
																		 'default' => true),
                                                   'assigned_user_name' => array('width'   => '8', 
                                                                                 'label'   => 'LBL_LIST_ASSIGNED_USER'),
                                                   'contact_name' => array('width'   => '8', 
                                                                           'label'   => 'LBL_LIST_CONTACT'),
                                                                                 
                                                   'team_name' => array('width'   => '15', 
                                                                        'label'   => 'LBL_LIST_TEAM', 
                                                                        'sortable' => false),
                                                                         );
																		 
											 
        $this->seedBean = new Task();        
    }    
}
?>

[mjunior]

I tried to put this suggested dashlet into 4.5.1 version but neither refresh or edit button work properly...
Anything special while using fixed $lvsParams sql parms in this particular version?

[blak3r]

Not sure if you're asking me or eggsurplus...

But, I have no clue about 4.5.1. I started using sugar at 6.0 and haven't ever come across developer documentation which explains what the major changes were between each revision.

But, I imagine that dashlets have evolved quite a bit since then so i'm surprised it worked at all.

[mjunior]

I asked the dashlet developer for compatible adjustments...but I guess the better approach for this version would be take just the query from this example and adapt it into a copy of "Mytasks" dashlet from existent task folder..
tks for the reply..