Report customisation for GENVASC

This was our first attempt at customising a CiviReport. We used the Case Detail report as the model.

Two major issues:

Firstly, the report needs to show the relationships of the case subject, specifically their GP surgery. This was acheived by having two entries in the $this->_columns array that use the same DAO, specifically the CRM_Contact_DAO_Contact DAO, one for the contact being reported on (the subject of the study enrollment) and one for the 'relation' in question, viz. the GP surgery.

Secondly, the report needs to include the practice 'C' code, which is custom data associated with the GP surgery contact. Simply using $_customGroupExtends to include custom data against Individuals, Cases, Organisations and Addresses doesn't work, because although they show up on the form, there is no way to specify that the search criteria shouldn't search for them against the subject of the case, but instead use the related GP surgery.

This is more difficult. Looking at the snippets of a 'DB error' generated below from the report template, it is evident that the search criteria include matching the civicrm_value_genvasc_recruitment_data_15 table (with an alias that is even longer) on the basis of matching the entity_id in that table to the id column in the case table in order to select the 'genvasc_site_ice_cod_37' and 'genvasc_id_38' values from it.

The next criteria is to get the practice ICE code from the civicrm_value_gp_site_based_data_14 table but there is a problem with the JOIN because no table is being specified for the matching id column. This is probably because the matching table should be the civicrm_address table but this isn't being specified anywhere in the code, presumably because no columns from the address table are directly required in the search. NH TO FIX THIS. Fixed it for the time being by deleting the 'Address' option from $customGroupExtends - we don't actually need that in there, and the search results don't actually return any addresses anyway.

The fourth criteria is to obtain the practice_code_35 value from the civicrm_value_gp_surgery_data_13 table which is syntactically correct, but returns no data because the matching in the LEFT JOIN is being done on the "contact_civireport.id" value (i.e. the contact ID of the subject of the enrollment) rather than the "relation_civireport.id" (i.e. the contact ID of the related GP practice). If we could get the report template script to mandate this LEFT JOIN instead then we'd be sorted. But the LEFT JOIN is generated programmatically at CRM/Report/Form.php in the customDataForm() function at line 2680 - 2709. The crucial line is 2696, where the LEFT JOIN is built, using the the $this->_aliases object, and specifically the [$extendsTable] attribute for it. We need to alter that object so that the [$extendsTable] attribute for this stanza of the SQL query referenced the "relation_civireport" table instead of the "contact_civireport". $extendsTable is built from $mapper[$propextends] and $mapper is built from CRM_Core_BAO_CustomQuery::$extendsMap - this could get awkward now, as that is a core array which maps CiviCRM conceptual objects (e.g. 'Individual') with the database table their custom data extends (e.g. 'civicrm_contact'). NH TO FIX THIS. DO MORE NEXT WEEK.

After that there are two more criteria in this example, for healthworker gp_ice_code and nhs number data, both of which are formed correctly. The nhs number one correctly returns the subject's NHS number, the healthworker search doesn't return anything because the subject is not a healthworker.

SELECT 
...
value_genvasc_recruitment_data_15_civireport.genvasc_recruitment_site_ice_cod_37 as civicrm_value_genvasc_recruitment_data_15_custom_37, value_genvasc_recruitment_data_15_civireport.genvasc_id_38 as civicrm_value_genvasc_recruitment_data_15_custom_38, value_gp_site_based_data_14_civireport.practice_ice_code_36 as civicrm_value_gp_site_based_data_14_custom_36, value_gp_surgery_data_13_civireport.practice_code_35 as civicrm_value_gp_surgery_data_13_custom_35, value_health_worker_data_12_civireport.gp_ice_code_33 as civicrm_value_health_worker_data_12_custom_33, value_contact_ids_11_civireport.nhs_number_29 as civicrm_value_contact_ids_11_custom_29  
...         
LEFT JOIN civicrm_value_genvasc_recruitment_data_15 value_genvasc_recruitment_data_15_civireport ON value_genvasc_recruitment_data_15_civireport.entity_id = case_civireport.id
LEFT JOIN civicrm_value_gp_site_based_data_14 value_gp_site_based_data_14_civireport ON value_gp_site_based_data_14_civireport.entity_id = .id
LEFT JOIN civicrm_value_gp_surgery_data_13 value_gp_surgery_data_13_civireport ON value_gp_surgery_data_13_civireport.entity_id = contact_civireport.id
LEFT JOIN civicrm_value_health_worker_data_12 value_health_worker_data_12_civireport ON value_health_worker_data_12_civireport.entity_id = contact_civireport.id
LEFT JOIN civicrm_value_contact_ids_11 value_contact_ids_11_civireport ON value_contact_ids_11_civireport.entity_id = contact_civireport.id 

Experiments:

Added a new 'extends' concept called 'Relation' in $_customGroupExtends, and by editing CRM/Core/BAO/CustomQuery.php in the $extendsMap array, I added a mapping for 'Relation' => 'civicrm_relation' (which isn't a table, but is aliased and thus should be accessible when the search is run. This doesn't help, because the gp_surgery_data_13 data is known by CiviCRM to extend 'Organization' data objects, not 'Relation' ones.

Tried a variant of the above, which points 'Organization' in the $extendsMap to civicrm_relation instead of civicrm_contact - this isn't a sustainable solution, as it would break other reports, but it is useful for experimentation. IT WORKS!

So the question is how to get $this->_columns (which is run through a foreach() to generate a $table => $prop relationship, which in turn is used to find the table to be extended (i.e. keyed against) by looking in $mapper[$propextends], which could reference a virtual object type (e.g. 'Relation') in the $extendsMap array, rather than the one it really extends, without wrecking the core code functionality.

What is currently being passed in $this->_columns must be a key and an array. The key maps to $table, so is the table name, and the array contains at least one mapping for 'extends' to map to the object being extended.

Key function is addCustomDataToColumns() in CRM/Report/Form.php - it creates a $customDAO by executing a query to obtain custom group and custom field data, and then populates the $this->_columns array with an array of entries keyed on the table_name, including dao (which is fixed to CRM_Contact_DAO_Contact), extends which is set to the value of $customDAO->extends (i.e. the entry in the database for 'cg.extends' and also groupings and group_title. So whatever is in the database for 'cg.extends' gets passed ultimately through the $extendsMap and results in

What I think we need is the capability in a form to set an 'over-ride' for the 'extends' value of $this->_columns, and a catch at line 2688 that looks up in the extendsMap UNLESS the over-ride is set, in which case, the over-ride value is used instead. But how to do that, when the custom data isn't directly referenced in the recruitmentreport.php - only the $customGroupExtends array is set. After that, everything is done in core code.

Process is addCustomDataToColumns() happens when form is built (triggered by the _ _ construct() function call) , but customDataFrom() happens when query is run, triggered by buildQuery(), called by postProcess() - so maybe something could be slotted in to the postProcess() function ahead of the call to buildQuery() which over-rules the 'extends' attribute for certain data?

Let's do a fixed one to test it:

What we would want would be to over-rule THIS: $this->_columnscivicrm_value_gp_surgery_data_13extends = 'Organization' WITH: $this->_columnscivicrm_value_gp_surgery_data_13extends = 'Relation'

AND ALSO INSERT INTO CRM_Core_BAO_CustomQuery::$extendsMap if we can, thus:

CRM_Core_BAO_CustomQuery::$extendsMapRelation = 'civicrm_relation'

This appears to work!