Published
Wednesday, January 2, 2013 - 18:21
Written by

These features were quite a roller-coaster to implement as an extension.  In the end, I made some small core customizations, which are not good enough to commit, but might be inspiring to fellow travelers.  The extension code itself is alarmingly hacky, and to spoil a long story, doesn't work in production ;)

The goal was to allow a Civi admin to go to the Advanced Search page, enter criteria and then view results as a list of contacts, with all that individual's contributions appearing in an indented list below the contact.  Selected data would then be exported in a maximal form, with all available info about the contact, and a list of their contributions laid out as columns on that person's spreadsheet row, along with various, calculated LYBUNT flags.

 

Custom Advanced Search Results

First, it turns out that advanced search results (called "modes" in the source code) come in a small number of flavors.  I wanted to print both contact details and contribution details, so I had to write a new result type.  Here, I used a common trick which is useful for almost any project: I created a new CRM_Utils_Hook handler, and implemented the hook in my extension. This limited the CiviCRM changes to one line, making release upgrades much simpler than if I had put all my logic in core.  It's not always possible to do things this way, especially when you want to change existing functionality rather than simply augmenting it.

My implementation of an advanced search mode was probably the most difficult component of this project, and I'm not sure it will be any easier the next time.  Note the many commented-out calls to "dpm" in the Devel module :P  Luckily, it was actually possible to display results as multi-line records from within the template.

Custom Export Mapping

The real fun came when I wanted to get the contact & contributions data into a spreadsheet.  I needed to run custom PHP and SQL to generate the data, so I had to override several steps in the export process.  My solution begins with a drupal update hook to programatically create a new export mapping in the schema.  I've added a new search results task which exports from the advanced search results list, including all the fields from my custom export mapping and overriding the usual field mapping selection screen.  Then, extension code performs calculations on these export values and rolls each contact up into a single row.  See wmf_reports.module for the top-level logic needed to implement this workflow.

 

Failures

The first blocker was a mysql JOIN limitation.  It turns out that you can only join 61 tables in a single SELECT statement, and my custom export mapping plus the built-in tables exceeded that number.  Luckily, I was close enough that cutting out a few export fields overcame that limit.  Specifically, there was a join performed for each phone and address location type, so I decided the "primary" values should be good enough in this case.

Finally, the export worked quite well on my development system, but died without throwing any errors on production.

 

References

Comments

Great work.

Do you think the code to create export mapping is "clean enough" to allow to create a new entity ExportMapping to wrap it?

 

WHen you mean it doesn't work in production, the "only" roadblock is that we have to patch the core or is there another problem?

Adam, thank you for posting this - it makes fascinating reading.  I am starting out on creating a simple custom search, so I am hoping to use your experience as a backgrounder in what I need to do.

However, I was a bit perplexed that, although your post was written only a year ago, that a number of the links that you give no longer work, specfically...

Would I find the materials that these links should lead to anywhere else?

Thanks
Mark

Thanks, Mark!  I'm working to exhume the SVN history, good to know it might be of use to you.  I'll update the article with live links and post another reply.

-Adam