SugarCRM Reports Customizations – Custom Filter Hacking

Goal:

Customize the UI Front-end of SugarCRM Reports to allow custom widgets for use with Reports filtering. The particular case is a series of dependent dropdowns with a key/value lists totalling over 18,000 items.

Assumptions:

1. Each filter equates to a single WHERE clause in Report query generation. Custom widget values may or may not play nice with this format, fortunately my case does.

2. All dependent dropdown values may be used (up to four), but they must be used sequentially; i.e., if a value in a 3rd degree dropdown is the desired filter value, the first and second dropdowns must be selected in sequence. The counter-point to this would be to support a dropdown at the fourth degree with over 18,000 options.

Plan of Attack

1. Hook or implement the javascript generation routines to allow calling of my DD javascript. Maybe with a custom attribute in the vardefs?

2. The key of each enumerated list (option key/value pairs) is what is stored in their respective columns in the Cases module; once this value is obtained, then the query can run as usual ([WHERE] [col] = ‘[value]’). This would be the most workable solution.

Performance impact of trying to filter on a 3rd/4th degree dependency should be negligible, even slightly faster since those four columns should be indexed.

Solution Implementation Steps:

1. The javascript file that supplies the metadata for the Reports UI is ./cache/modules/modules_def_[lang].js.

  • That file is generated as a sub-routine when within the Reports module.
  • The direct call is made when a Reports object is instantiated. The call is the cache_modules_def_js() method of the Reports class.
    • This is a perfect example of how this module is horribly written. The call to “cache” the above file is left as a: if(true || somecondition) {…}
  • The above inline includes a PHP file that generates the javascript cache file: ./modules/Reports/templates/templates_modules_def.php.
    • This is where the customization will occur.

2. To properly hook the javascript generation routine, the usage of that metadata must be explored. On a typical Reports creation screen a tabbed UI is generated based on the setup sequence (brand new vs. Saved Report).

template_reports_report($reporter, $args)

All Reports javascript calls that aren’t generated dynamically live in ./include/javascript/report_addtionals.js

Clicking “Add Filter” calls addFilter().

This creates a row in the Filters table with 5 cells. The cell that we need to hack is cell (column) 4, the “input_cell”.

Creating the “input_cell” is a call to addFilterInput(input_cell, filter). This call parses the generated field metadata and builds up the widget. Our particular instance is an “enum” field_type which calls either addFilterInputSelectMultiple() or addFilterInputSelectSingle() depending on other attributes.

At this point, we have the option of over-loading addFilterInputSelectSingle or hooking a third option at the previous step in the call stack (addFilterInput).  Either way, to make this all as upgrade-safe as possible, we need to leave the various javascript files alone and only replicate the minimum amount of code.

The other hard-criteria is that dependent dropdowns get their enum pairs via an asynchronous call which doesn’t block code execution which may make the rest of the addFilterInput() call fail.

  • force the async call to run synchronously?
Advertisements