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.

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.

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