News & Blogs

This is some blog description about this site

SugarCRM is a demanding development environment. This is our software engineers space. They know SugarCRM .. it's what they do ..

Logging custom error messages in SugarCRM

Depending on what your Log Level is set to in the system settings you can log errors to the sugarcrm.log located in the root folder with the following code snippets
$GLOBALS['log']->fatal("My fatal message");
 
$GLOBALS['log']->debug("My debug message");
 
$GLOBALS['log']->info("My log message");
Continue reading
3253 Hits
1 Comment

Working out percentages in a Mysql query for reporting tool zuckerreports

Although not strictly Sugarcrm related I had requirement the needed me to work out the percentage of opportunities which had the sale_stage field set to ‘Closed Lost’ and ‘Closed Won’. This would be displayed as part of sugar via Zuckerreports, effectively showing a report with the percentage of all Opportunities that were Won and Lost.

As it turned out this was easy:

SELECT
   sum(sales_stage="Closed Won")/count(*) * 100 AS won_pct,
   sum(sales_stage="Closed Lost")/count(*) * 100 AS lost_pct
FROM opportunities

The second part of this report was much more complicated as follows:

For our purposes the opportunities table contains the following fields: id and sales_stage. The opportunities_cstm table contains the fields id_c and sales_stage_before_closed_c. id_c is what relates the two tables.

sales_stage contains the values from 1 to 10 and also either ‘Closed Lost’ or ‘Closed Won’. In the actual SugarCRM instance 1 to 10 represents percentage bands from 0-9% to 90-99% and closed lost is 0% and closed won is 100%.

sales_stage_before_closed_c is the percentage band that the opportunity was at before it was closed.

So in my actual query I needed to display a percentage for each sales_stage on how many opportunities reached this stage and resulted in a won opportunity and how many reached this stage and resulted in a lost opportunity.

After much brain strain and help from a college:
SELECT opportunities_c_top.sales_stage_before_closed_c AS 'Sales Stage',
FORMAT(
( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Won' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Won',
FORMAT(
 ( SELECT COUNT(*)
 FROM `opportunities_cstm` opportunities_cstm join
 `opportunities` opportunities
 on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Lost' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c )* 100 / COUNT(*), 2) AS 'Closed Lost'
 
FROM `opportunities_cstm` opportunities_c_top join
 `opportunities` opportunities_top
 on opportunities_top.id = opportunities_c_top.id_c
WHERE (opportunities_top.`sales_stage` = 'Closed Won' OR opportunities_top.`sales_stage` = 'Closed Lost')
GROUP BY opportunities_c_top.sales_stage_before_closed_c

An sql fiddle showing a working example: Fiddle
Continue reading
2408 Hits
0 Comments

Workflow - Adding a Custom Action Part 2

In Part 1 we looked at adding a custom action to AOW, in this part we are going to look at enhancing that action to make it more flexible by adding options to the user interface.

We add a new method 'edit_display' to our class 'actionNewAction' in 'custom/modules/AOW_Actions/actions/actionNewAction.php' which is were we put the code for the user interface for our action:-

https://gist.github.com/9350281a20e4490a48a8


The way you layout your display is really up to you, as long as it is valid html, but the important points to take from this example is the naming conventions used for fields. The name of any fields should be formatted like 'aow_actions_param[".$line."][{variable_name}]' replacing {variable_name} with the name you want to give to you variable. If it is not done in this format, it will not be saved. Additionally for loading variables in your edit display you should set the value of the field to $params['{variable_name}'], again replacing {variable_name} with the name of your variable, which will allow you field to be reloaded, when editing the record. The id of the field is not as important for naming but incase you want to use it in JavaScript or just for consistency I would set it to something like 'aow_actions_param_{variable_name}".$line."'.

Now we have setup our display we need to add the new languages string to our language file 'custom/Extension/modules/AOW_Actions/Ext/language/en_us.myNewAction.php':-

https://gist.github.com/3e84d2c2259fac2ee01b

And finally we need to modify our action to use our new variables:-

 
https://gist.github.com/069d95ba5625859c2b68

So that it you should now have a dynamic action. However, you can make action user interface more dynamic and load JavaScript files by using the using the 'loadJS' method, allowing you to return an array of JavaScript files you require to be loaded for you user interface :-

https://gist.github.com/90a5b54f7a14d1d91193

If you have any suggestions on how Workflow can be improved or anything you like to see let us know

Continue reading
3793 Hits
0 Comments

Workflow - Adding a Custom Action

One of the great things about workflow is the ability to add you own custom actions that are completely upgrade safe and blend in just as if they where there all along.  In this example we are going to add a custom action that simply logs to the sugarcrm.log, not very useful but it will make a nice example!

To start we will create a new file 'actionNewAction.php' in 'custom/modules/AOW_Actions/actions' that extends the base action. You can extent any of the current action if your action is similar or you can make use of the current functionality, but for this example we will just use the base:-

php

require_once('modules/AOW_Actions/actions/actionBase.php');

class actionNewAction extends actionBase {

    function actionNewAction($id = ''){

        parent::actionBase($id);

    }

?>

Now we have our class file, we can add a new method 'run_action' to do the new action, which is passed two parameters $bean the workflow bean this is running for and $params which we will cover in part 2:-

    function run_action(SugarBean $bean, $params = array()){

       $GLOBALS['log']->fatal($bean->name' ran in Wokflow!!!');

        return true;

    }

Now we have created our class file and set its run action, we need to add it to the workflow actions dropdown and to do that we need to create a file called anything relevant like 'myNewAction.php' in 'custom/Extension/modules/AOW_Actions/Ext/Actions'  and add it to the array using the same name as out new action:-

?php

$aow_actions_list[]='NewAction';

And finally for out action to display nicely in the dropdown we add a language entry for all languages you want it to display in, English in my case, so create a new file 'en_us.myNewAction' in 'custom/Extension/modules/AOW_Actions/Ext/language' and the appropriate language string for you action:-

?php

$mod_strings['LBL_NEWACTION']='My New Action';

Quick repair and Rebuild and your new action is ready to go!

In Part2 we will look adding options to display when creating and editing the action, to make our action more flexible.

Continue reading
4226 Hits
1 Comment

Workflow – What's it all about!

Vision

Advanced OpenWorkflow(AOW) was conceived from the idea of building a open source workflow engine which is simple yet powerful and dynamic. The main issue we had with other workflow engines was that you could not create a workflow action from one screen! Constantly switching between screens and pop-up windows is frustrating for the user. It over complicates what could be a simple process!

 

Development

Development of AOW was agile and iterative. From our original idea we drew up a roadmap of the functionality we wanted to include in the first production release. This was developed into four alpha releases and one beta release. This allowed us to harness feedback from the community to ensure we were going in the right direction. As lead developer of AOW the biggest challenge was bringing the ability to do it all in one screen but still keeping it clear and flexible for the user.


Delivery

AOW 1.0 was released as planned on the 8th August. We look forward to hearing the community's feedback and suggestions on this new product!

Tags:
Continue reading
2039 Hits
0 Comments