Changes between Version 3 and Version 4 of CiviCRM GENVASC Report customisation
- Timestamp:
- 03/08/13 17:03:45 (11 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
CiviCRM GENVASC Report customisation
v3 v4 11 11 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. 12 12 13 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. 13 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. 14 NH TO FIX THIS. 14 15 15 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. NH TO FIX THIS. 16 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'). 17 NH TO FIX THIS. DO MORE NEXT WEEK. 16 18 17 19 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.
