WIP-PCHR-3431: Reports Milestone One POC.

[tunbola]

This is not a proper PR per say but a proof of concept to demonstrate how to use the Pivot report extension to display the People reports in CiviHR.

• After studying implementations for the Activity and Membership reports I was able to get the People report(with CSV export) working with: Contacts (with all custom fields) Contracts Roles.
• The Date filter is based on the Job contract start date, there is a Current Contact column in the report that states whether the contract is the one being used by the contact or not. I am still investigating how to filter by current contract of a contact mainly because the extension only allows the report to be filterable by the index key which is usually a date field,(still checking how to see if two index keys can be combined),

Limitations

• The Pivot reports extension works by adding support for Entities and the way it is presently it can support any Entity by extending some required classes and creating template file to display the report. However this has some limitations.
• The classes for the report must be named after the entity name. For example the main entity of the people report is the Contact entity, I could not name my classes e.g CRM_PivotData_DataPeople for the Entity data class but I had to name it CRM_PivotData_DataContact. This has some limitations, for instance it means I can't have two reports of which Contact is the main entity because there would be a name clash.
• The Data for the Entity will always expect to get data from civicrm_api3($EntityName, 'get', $apiParams); See , This means that the data will always be provided via the get method of the API and additional data has to be gotten by chaining API calls together as seen here: See . This will make it difficult to get data for Entities like Leave Request that the get method can not provide all the data we need even with chaining to other API calls due to the fact that complex calculations need to be done before the data set is returned. For the People report however I was able to get it working as the data can be gotten from the API directly and by chaining API calls together.
  Chaining API calls has its own overhead considering the fact that the API's would be called multiple times.
• The extension provides a way to add menu items for reports dynamically by first of all checking for the supported entities and then adding the menu items for the supported entities. The Prospect report for instance has a dependency that the 'uk.co.compucorp.civicrm.prospect' must be installed before the report Link can be added to the menu. See. The links are added via an upgrader Here. The impact of this is that when the dependency of a supported entity is not met after the pivot extension has been enabled, the report menu for that Entity still remains on the Pivot report menu.
• The Pivot Report menu's are added under the core Civicrm Reports menu. The Reports menu is hidden for CiviHR here, I had to manually comment this out to get the Menu to show on my Local.
• For the People report in this PR, when the date filters are used, the total count for the contracts with start period dates between the dates passed is gotten from Here, In reality what is needed is actually the number of contracts with period start dates between those dates but because Contact is the main entity, and there is no way to filter based on any chained entity, the returned total is a false representation, although this is only used to show the progress bar of the data load.
• There is no way to format a report field e.g similar to view handlers, For example on the CiviHR people report, the Contact birth_date field is formatted to display the age group of the contact.

Caching

• The Pivot report has to fetch data for an entity first and store it in the civicrm_pivotreportcache table, the cache can be built manually or automatically via a cron job. The data are are stored as Json in the data column of the table and as such is not searchable or filterable.

Date Filters

• The report cannot be filtered by any of the report fields except the index key which is usually a date field, set for membership report here: , This allows data on the same date to be grouped together and then can be searched using the date filter on the UI. The date filter on the UI only shows up when the data to be loaded is greater than the initial load limit, only then does the filter appear and then data can be filtered by the date on the chosen index field for the entity.

Recommendations

• Rather than name a report and subsequently the class names after the Entity, Let the Report have a Unique name that define what it does e.g instead of CRM_PivotData_DataContact we can have CRM_PivotData_DataPeople. The report name People will be passed in the regular functions that previously expected the Entity names. The implication is that rather than the Data classes relying on the civicrm_api3($EntityName, 'get', $apiParams); to get the reports data, Each report class will have its own getMethod, exposed via the Pivot APi that will allow each report class to return data via any way it choses, either via API calls or via SQL queries. whatever method used must still support an offset and limit which is what the current API method leverages on. This way there won't be any main entity for a report and an Entity can be used across many reports to fetch information. Also each Report class will have a getCount method that will be used everywhere and will accept filter parameters and return results based on those filters. (See the last note on limitations section). This change will be done in such a way as not to affect existing reports as the Entity name will be assumed to be the reports name.
• Rather than supported Entities, it will be supported Reports and each Report can have its dependencies for it to be counted as supported such as an extension being enabled or a component being present e.t.c. The Reports menu will be added dynamically via hook_civicrm_navigationMenu, In this way, if a report is no longer valid due to a non existent dependency, its menu will not be shown on the Pivot reports menu links.
• Allow handlers to be added to a report field which transforms the data to the specified format before it is being cached in the civicrm_pivotreportcache table.
• Everywhere where civicrm_api3($EntityName, 'get', $apiParams); or the Civi APi is called directly for a report should be replaced with a call to the version of the reports method e.g getCount, get exposed via the Pivot API.

update on making the extension work with Leave Report

I tried to see how to get the Leave Report working with the pivot report extension as is, I came up with the following API call:

  $result = civicrm_api3('LeaveRequest', 'get', array(
    'sequential' => 1,
    'return' => array("contact_id", "type_id", "status_id", "request_type"),
    'api.Contact.get' => array(
      'id' => "\$value.contact_id", 
      'return' => array("display_name", "birth_date", "gender_id")
    ),
    'api.HRJobContract.get' => array(
      'contact_id' => "\$value.contact_id", 
      'return' => array("position", "title", "contract_type", "period_start_date", "period_end_date", "is_current", "jobcontract_revision_id"), 

      'api.HRJobHour.get' => array(
        'jobcontract_revision_id' => "\$value.jobcontract_revision_id"
      ),

      'api.HRJobPay.get' => array(
        'jobcontract_revision_id' => "\$value.jobcontract_revision_id",
        'sequential' => 1,
      ),
      'api.HrJobRoles.get' => array('sequential' => 1, 'job_contract_id' => "\$value.id"),
    ),
    'api.LeaveRequestDate.get' => array(
      'leave_request_id' => "\$value.id"),
      'api.LeaveBalanceChange.get' => array('source_id' => "\$value.id", 'source_type' => "leave_request_day"),
  ));

The results for some fields would still need to be modified either via handlers or in the logic when results are fetched e.g Employee Age group(by modifying the birth_date field), Employee Age, Absence Is Credit(by modifying the request_type field), Absence Day of Week, Absence Start Month, Absence End month.... The API calls to api.HRJobPay.get and api.HRJobHour.get did not return any results, This seems to be because the jobcontract_revision_id field returned by the Job contract API is not a true field of the Entity per say because the field actually belongs to the HRJobDetails Entity.

Update on other extensions injecting their report.

I tried making the People report to reside in L&A extension and it was successful to some extent.

• I had to add the Contact entity to the supported entities in the pivot report extension
• I had to add the template for the report display to the pivot report extension
• The other files were added to the L&A extension i.e CRM_PivotCache_GroupContact andCRM_PivotData_DataContactbut I could not add them in theHRLeaveAndAbsences` namespace but outside it due to this, i.e the namespace for the extending classes are already hardcoded.

[jamienovick]

@tunbola @davialexandre can we also consider making the caching mechanism optional in some way? Perhaps that doesn't need to be part of this first milestone but please keep it in mind in case we do want to simplify this point.

[jamienovick]

Also - in terms of the "supported entities" vs "reports".

In future we will want to iterate to a scenario where we have a base "report templates" which define the available fields, filters and perhaps features and then an individual "report instance" which is the configured report (previously our report configurations available from the dropdown). each report instance would be listed in a report listing, then have a separate menu link to open up and view it, can be emailed automatically and also should be available for CiviCRM dashboard.

Whilst I think we support this data structure already, I think we should try and get the terminology correct now if that is possible.

[tunbola]

@jamienovick , Will take a look at how to decouple the caching mechanism but tbh, the whole extension seems to depend on this caching mechanism as all the reports are pulled from the caching table. Will see if its possible to decouple to fetch data in real time but I wonder how effective this would be considering that even the current L&A reports fetch data from views whose data are refreshed at some intervals. Regarding the Report instance listing, I think we have the structure in place like you rightly said but will pay more attention to the terminologies and implementation.

[tunbola]

Not needed anymore