Advanced Reports for SuiteCRM/SugarCRM (using SQL)

Oct 21
2018

Download

I simply could not find a reporting tool for SuiteCRM/SugarCRM that had the power to report based on any database query, so I decided to build one.

If you know SQL and the CRM schema and you would like to use it to build reports available inside your CRM this tool is for you. If you don’t know SQL or the schema, this is not for you.

Installation

Simply install the package using the module loader as with any other module.
You have to run a quick repair after installation since it uses the extension framework for the menu links to work. (See below).

How To

To use this reporting tool, simply write a query that return the data you want, include some smarty style variable(s) (eg . {$city}) in your query and paste the query into a new record in this module.
The code will extract the variables and allow you to set the label, type, etc. for each variable found in your query.
It will also run the query and allow you to set the label, type, size, grouping, etc. for every column the query returns and voila! You have your first report.

Examples

Want to return all contacts for a specified city:

  1. SELECT first_name, last_name, phone_mobile FROM contacts
  2. WHERE primary_address_city='{$city}' AND deleted=0

How about all contacts for a state and a count per city?

  1. SELECT first_name, last_name, phone_mobile, primary_address_city
  2. FROM contacts
  3. WHERE primary_address_state='{$state}' AND deleted=0
  4. ORDER BY primary_address_city

Then select the city as group one field and set any other field to count.
Get the drift?

You can set any column to be the outer group, inner group, value,
count, average or sum in a subtotal or total line.

Unions, Views or Stored Procedures?

Want to use a really complex query with UNION(s) or database views or even a
hugely complex stored proc that calculate loads of stuff before returning rows?
Be my guest, just put

  1. call sp_name({$var1}...)
inside the query of a record in this module.
The power of what you can do is limited only by your imagination and SQL skils!

Templates and Style Sheets

Looks is not my strong point.
The module comes with one very basic look for reports that is built using smarty templates in modules/adrep_report/tpls/header|footer|row|css folders.
You can create your own templates or CSS by adding files (with a different name) to custom/modules/adrep_report/tpls/header|footer|row|css folders and then add the name(s) of your style(s) to the adrep_css_file_list dropdown.
You can create only the files you want to change, any missing file(s) will default to the built-in Basic one. If you only want one look just call your custom files Basic and they will get used instead of mine.

Menu Links

Something that I find really useful (for users) is to link reports directly to modules or even records all over the CRM by adding them to the menus.
Let’s sat you have a products module and every product has prices. Just build a price list report that has a variable {$product_id} and then link your report to the DetailView of your products module. Voila!  Now your users can run the
price list directly from any product’s DetailView. 🙂

Pagination and caching

The data returned by your queries are cached in the adrep_cache module to allow pagination or exporting without having to re-run the query every time.
The query is re-run every time a specific user runs the report from scratch, not for paging through results or exporting.
Reports can be downloaded/exported as CSV, HTML or PDF (on SuiteCRM).

Charts/Graphs

You will see in the schema and modules that I am adding charts/graphs to the reports using the cached data. That is a work in progress so watch this space.

Security notice!

Lastly – Be very careful who you give edit rights on this module!!
Since it queries the database directly a user could use this to extract any data from your database. Really only trusted admins should be able to edit reports!

I hope you enjoy the power this gives you.
Please let me know by email to crm _at_ mjvn.net if you use or try this tool.

Download

SuiteCRM MailChimp Sync

Oct 19
2018

I have just created a SuiteCRM (or SugarCRM) module that can export data from any module(s) to a MailChimp list(s).

It allows multiple records/exports to be set up and runs from the scheduler to do the export and sync.

Please give it a try and send feedback to crm at mjvn.net.  See the full README below.

You can download the module from the SuiteCRM Forum here.

This module can export data from any SuiteCRM (or SugarCRM) module to a MailChimp List.

After installation you need to:
1) Run a quick repair and rebuild to build the extended schedular
2) Create a scheduler to run the MailChimpSync schedular at least once a day
3) Create at least one chimp_export record that defines an export
4) Set the mailchimp API ID either in the individual record(s) or in the key
    $sugar_config['mailchimp_api_id'] in config_override.php to apply to
    all records that do not have an api_key filled in.
    This also allows you have a default api_id in the config and override it
    in individual exports if needed.

For 3) above you need to set the following:
1) The module to export from
2) The first name field of that module (normally first_name)
3) The last name field of that module (normally last_name)
4) 1 or 2 filter fields and values to limit the records exported to MailChimp. (eg. by city)

The scheduler will:
1) Create the MailChimp list(s) specified if it does not exist
2) Export all matching records from the module to the list
3) Keep the status of each record in the chimp_status table
4) Update the status if it changes on MailChimp
5) Delete the record from MailChimp if it no longer matches the filters

Every list created by this module has a merge field CRM_ID and each record has the id from
the module in that merge field.  This is useful for custom links in MailChimp templates.

Please notify me at crm at mjvn.net if you use (or try) this module!

Change relate to dropdown on EditView

Jun 30
2011

Ever wanted to display a relate field as a dropdown list on an EditView?  Here is how you can do it.

Assume you have a custom module that has a relate field to Accounts.  The module will have a field account_name and a field account_id.

In the vardefs.php for your module add the list of options to the account_id field:

  1. 'account_id' =>
  2. 'name' => 'account_id',
  3. ...
  4. 'options' => 'account_list',
  5. ),

Then in the metadata/editviewdefs.php change account_name to account_id and add a type =>’enum’ to it.

  1. 'name' => 'account_id',
  2. ...
  3. 'type' => 'enum',
  4. ),

Then lastly add some code at the top of your modules class file (my_class.php) to create and populate the list.  You can of course add more filters to the where clause to display a subset of related records.

  1. global $db, $app_list_strings, $current_user;
  2. if ($_REQUEST['action'] == 'EditView')
  3. {
  4. $app_list_strings['account_list'] = array(''=>'');
  5. $query = "SELECT a.id,a.name FROM accounts a WHERE a.deleted=0 ORDER BY a.name";
  6. $res = $db->query($query);
  7. while ($row = $db->fetchByAssoc($res))
  8. $app_list_strings['account_list'][$row['id']] = $row['name'];
  9. }

Now do a quick repair on your module to remove the cached vardefs and EditView template and you should see a dropdown list where the select box used to be 🙂

For custom modules built in module builder you should edit the vardefs.php and metadata/editviewdefs.php inside custom/modulebuilder/packages/<package>/modules/<module>/ and the class file in custom/modulebuilder/builds/<package>/SugarModules/modules/<module>/<module>.php.

If the module is a built in module you will have to make the vardefs changes in an extended vardefs file in custom/Extension/modules/<module>/Ext/Vardefs/, change the metadata in custom/modules/<module>/metadata/editviewdefs.php and create the list in an after_retrieve logic hook.

 

 

Date/Time in correct format

Jun 30
2011

Here is a simple way to get two variables that contain the current datetime in the current user’s preferred format and in the correct database format.  Use $now_user to set datetime values on SugarBean objects and $now_db to set datetime values in database queries.

  1. global $timedate;
  2.  
  3. $now_db = $timedate->get_gmt_db_datetime();
  4. $now_user = date($timedate->get_date_time_format());

 

 

SuiteCRM

Jun 27
2011

SuiteCRM (and SugarCRM it is based on) is an amazing open source customer relations management (“CRM”) system, but it is also much more than that.

Because of it’s open source design it can serve as a great platform for building rich, web-based, database driven, dynamic applications.  I now use it for the admin / back-office portion of nearly all of my projects.

Over the last couple of years I have built quite a few tools to help administrators and developers manage SuiteCRM and SugarCRM installations better.

Not unto the swift…

The race is not to the swift or the battle to the strong, nor does food come to the wise or wealth to the brilliant or favor to the learned; but time and chance happen to them all.
— Ecclesiates 9:11 (NIV)