SugarCRM Cookbook – SugarQuery – The Basics

Sugar Developer Blog - SugarCRM

You have found yourself in a bind, and you need to query the database directly. There is no other recourse than to write a query to get the data you need. This cookbook entry is going to give you some examples on how to use our new SugarQuery API instead of direct SQL.

1. What is SugarQuery?

SugarQuery is a SQL query builder for retrieving data directly from the database.  It is used extensively within the core of the application.  For instance, the FilterAPI uses it.

It uses a bean, the beans relationships, and visibility models to build a SQL query that can be used to retrieve data.

2. The Basics

SugarQuery has a very simple interface for building queries.

The basic methods you will need to create a query are:

  • select($fields) – accepts an array of fields you would like to select
  • from($bean) – validates the query against a SugarBean at generation
  • where()…

View original post 292 more words

Advertisements

Getting Reports To User Hierarchy in Sugar CRM as Array

Create File custom_utils.php in custom/include directory and add the following functions in to it

The getReportsToUserArray Function has two parameters
$userid is the id of user
$teamArray returns the array of private team of reporting user hierarchy



function getReportsToUserArray($userId, $teamArray = array()) {
    if (!empty($userId) && $userId != 1) {
        $reportingUser = new User();
        $reportingUser->retrieve($userId);
        if (!empty($reportingUser->reports_to_id) && $reportingUser->reports_to_id != 1) {
            $teamArray[] = User::staticGetPrivateTeamID($reportingUser->reports_to_id);
            $nextuserId = $reportingUser->reports_to_id;
            return getReportsToUserArray($nextuserId, $teamArray);
        }
        return $teamArray;
    }
}

The getUserIdByPrivateTeam Function has two parameters
$teamArray is the array of private team of reporting user which will be return by above function
This Function returns the Array of userid by private team of users

function getUserIdByPrivateTeam($teamArray = array()) {
    global $db;
    if (!empty($teamArray)) {
        $privateteamstring = implode("','", $teamArray);
        $result = $db->Query("SELECT associated_user_id  from teams WHERE id in ('$privateteamstring')");
        while ($row = $db->fetchByAssoc($result)) {
            $UserIDArray[] = $row['associated_user_id'];
        }
    }
    return $UserIDArray;
}

After Getting this array we can check the selected assigned user id in this array
if the id is available we can restrict the save action by javascript or jquery in view.edit.php of case module

Pass Php Array to Javascript using JSON – Sugar CRM


Consider that we are restricting user to assign a case to his/her reporting person so create the file like below as view.edit.php in custom/module/cases/view/ .
The functions which are used to get reporting person of current user are in another post getReportsToUserArray(); getUserIdByPrivateTeam();

require_once('include/EditView/EditView2.php');

class CasesViewEdit extends SugarView {

    var $ev;
    var $type = 'edit';
    var $useForSubpanel = true;  //boolean variable to determine whether view can be used for subpanel creates
    var $useModuleQuickCreateTemplate = false; //boolean variable to determine whether or not SubpanelQuickCreate has a separate display function
    var $showTitle = true;

    function CasesViewEdit() {
        parent::SugarView();
    }

    /**
     * @see SugarView::preDisplay()
     */
    public function preDisplay() {
        $metadataFile = $this->getMetaDataFile();
        $this->ev = $this->getEditView();
        $this->ev->ss = & $this->ss;
        $this->ev->setup($this->module, $this->bean, $metadataFile, 'include/EditView/EditView.tpl');
    }

    function display() {

        global $db, $current_user;
        //Get All Reports To Id User of Current User in Array Format
        $ReportsToTeamArray = getReportsToUserArray($current_user->id);
        //Get User Id By Reports To Id from team table
        $AssignedUserIdArray = getUserIdByPrivateTeam($ReportsToTeamArray);

        $js1 = <<<EOF
<script>

                            var restictedUsers='$AssignedUserIdArray';                           
                            if(document.getElementById('EditView')!=null)
                            {
                                var _form = document.getElementById('EditView');
                                var formElements = _form.getElementsByTagName("*"); 
 for(var i=0;i < formElements.length; i++)
                                {
                                      // Check if the value of the current element is Save
                                      if (formElements[i].value == "Save")
                                      {
                                         formElements[i].setAttribute("onclick", "return SetSaveActionEditView()");
                                      }
   }  
                            }
                            function SetSaveActionEditView()
                            {                              
                                var _form = document.getElementById('EditView'); 
                                _form.action.value='Save';                            
                                if(check_form('EditView'))
                                {
  data=eval('('+restictedUsers+')');
                                    if(data.indexOf(_form.assigned_user_id.value) > -1)
                                    {
                                     alert('Not Autorized To Assign Case To Reporting To Person');
                                     return false;
                                    }

                                    SUGAR.ajaxUI.submitForm(_form);
                                    return false;
                                }
                                return false;
</script>
EOF;

        $this->ev->process();
        echo $this->ev->display($this->showTitle);       
        echo $js1;
    }

    /**
     * Get EditView object
     * @return EditView
     */
    protected function getEditView() {
        return new EditView();
    }

}

Get Reports To User in Array Format – Sugar CRM

The Following Function will return the Users Reports To one by one
like category and its parent category concept

/*
$userId is the id of user
$teamArray is the result which will return array of reports to hierarchy
*/

function getReportsToUserArray($userId, $teamArray = array()) {
    if (!empty($userId) && $userId != 1) {
        $reportingUser = new User();
        $reportingUser->retrieve($userId);
        if (!empty($reportingUser->reports_to_id) && $reportingUser->reports_to_id != 1) {
            $teamArray[] = User::staticGetPrivateTeamID($reportingUser->reports_to_id);
            $nextuserId = $reportingUser->reports_to_id;            
            return getReportsToUserArray($nextuserId, $teamArray);
        }
        return $teamArray;
    }
}

How to Add Custom Subpanel in DetailView of Sugar CRM

There comes the time when sales rep finds it irritating to view opportunities Closed Won and Closed Lost under same subpanel.
Lets make custom subpanels separating Closed Won and Closed Lost.
In this blog post, we will add those subpanels under Accounts module.
Step 1 : Create custom/Extension/modules/Accounts/Ext/Layoutdefs/<any_name>.php and write the following code into it.

<?php 
$layout_defs[‘Accounts’][‘subpanel_setup’][‘opp_closed_lost’] =
array(‘order’ => 49,
‘module’ => ‘Opportunities’,
‘subpanel_name’ => ‘ForAccounts’,
‘get_subpanel_data’ => ‘function:get_closed_lost_closed_won_opportunities’,
‘generate_select’ => true,
‘title_key’ => ‘LBL_OPPORTUNITIES_WITH_CLOSED_LOST’,
‘top_buttons’ => array(),
‘function_parameters’ => array(
‘import_function_file’ => ‘custom/modules/Accounts/customOpportunitiesSubpanel.php’,
‘sales_stage’ => ‘Closed Lost’,
‘account_id’ => $this->_focus->id,
‘return_as_array’ => ‘true’
),
);
$layout_defs[‘Accounts’][‘subpanel_setup’][‘opp_closed_won’] =
array(‘order’ => 50,
‘module’ => ‘Opportunities’,
‘subpanel_name’ => ‘ForAccounts’,
‘get_subpanel_data’ => ‘function:get_closed_lost_closed_won_opportunities’,
‘generate_select’ => true,
‘title_key’ => ‘LBL_OPPORTUNITIES_WITH_CLOSED_WON’,
‘top_buttons’ => array(),
‘function_parameters’ => array(
‘import_function_file’ => ‘custom/modules/Accounts/customOpportunitiesSubpanel.php’,
‘sales_stage’ => ‘Closed Won’,
‘account_id’ => $this->_focus->id,
‘return_as_array’ => ‘true’
),
);
?>

Step 2 : Create custom/modules/Accounts/customOpportunitiesSubpanel.php and write following code into it.

<?php 

function get_closed_lost_closed_won_opportunities($params) {
$args = func_get_args();
$opportunitiesSalesStage = $args[0][‘sales_stage’];
$accountId = $args[0][‘account_id’];
$return_array[‘select’] = ” SELECT opportunities.*”;
$return_array[‘from’] = ” FROM opportunities “;
$return_array[‘where’] = ” WHERE opportunities.deleted = ‘0’ AND opportunities.sales_stage = ‘” . $opportunitiesSalesStage . “‘”;
$return_array[‘join’] = ” INNER JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id AND accounts_opportunities.deleted = ‘0’ INNER JOIN accounts ON accounts.id = accounts_opportunities.account_id AND accounts.deleted = ‘0’ AND accounts.id = ‘” . $accountId . “‘”;
$return_array[‘join_tables’] = ”;
return $return_array;
}
?>

Step 3 : Create custom/Extension/modules/Accounts/Ext/Language/en_us.<any_name>.php and write the following custom label into it.

<?php 

$mod_strings[‘LBL_OPPORTUNITIES_WITH_CLOSED_LOST’] = “Opportunities With Closed Lost”;
$mod_strings[‘LBL_OPPORTUNITIES_WITH_CLOSED_WON’] = “Opportunities With Closed Won”;
?>

Step 4 : Do Quick Repair and Rebuild from Admin panel.

Modify SugerField Address – Country dropdown in Sugar CRM

First change the type of country fields Suppose we are changing the address control fields in Leads module. Create one php file and add the code in and put it to custom/extension/modules/Leads/Ext/Vardefs/

Following code will change the primary_address_country field and convert it to dropdown
$dictionary['Lead']['fields']['primary_address_country'] =
        array(
            'name' => 'primary_address_country',
            'vname' => 'LBL_PRIMARY_ADDRESS_COUNTRY',
            'type' => 'enum',
            'options' => 'countries_dom',
            'group' => 'primary_address',
            'comment' => 'Country for primary address',
            'merge_filter' => 'enabled',
);

Following code will change the alt_address_country field and convert it to dropdown
$dictionary['Lead']['fields']['alt_address_country'] =
        array(
            'name' => 'alt_address_country',
            'vname' => 'LBL_ALT_ADDRESS_COUNTRY',
            'type' => 'enum',
            'options' => 'countries_dom',
            'group' => 'alt_address',
            'comment' => 'Country for alternate address',
            'merge_filter' => 'enabled',
);

Note: Similarly we can change the country field for Account,Contacts Module

Now copy include/SugarFields/Fields/Address/en_us.EditView.tplfile and paste as custom/include/SugarFields/Fields/Address/en_us.EditView.tpl And change the type of address_country from input to dropdown and add following code to fetch all contries in dropdown

{if isset($fields.{{$country}}.value) && $fields.{{$country}}.value != ''} {html_options options=$fields.{{$country}}.options selected=$fields.{{$country}}.value} {else} {html_options options=$fields.{{$country}}.options selected=$fields.{{$country}}.default_value} {/if}

After making this changes Repair and Rebuild the sugar crm This will add the country dropdown in address Field Now create one dropdown named as countries_dom and add the countries in it The countries added in this dropdown will be shown in country dropdown in address SugarField this dropdown array will be added in custom/include/language/en_us.lang.php like

$GLOBALS['app_list_strings']['countries_dom'] = array(
    "AF" => "Afghanistan",
    "AL" => "Albania",
);