Changes between Version 4 and Version 5 of CiviCRM GENVASC Report customisation

Timestamp:

03/11/13 18:22:33 (12 years ago)

Author:

Nick Holden

Comment:

--

CiviCRM GENVASC Report customisation

@@ -12 +12 @@
 
 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.
+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[$prop['extends']] 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'). 
@@ -32 +32 @@
 }}}
 
+
+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[$prop['extends']], 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->_columns['civicrm_value_gp_surgery_data_13']['extends'] = 'Organization'
+WITH:
+$this->_columns['civicrm_value_gp_surgery_data_13']['extends'] = 'Relation'
+
+AND ALSO INSERT INTO CRM_Core_BAO_CustomQuery::$extendsMap if we can, thus:
+
+CRM_Core_BAO_CustomQuery::$extendsMap['Relation'] = 'civicrm_relation'
+
+This appears to work!