GSSPRT-100: Fix Issue With Missing Entities Data

[tunbola]

Overview

There are some issues with missing entities data in the pivot report. For example in the prospect pivot report for a client, some prospects that were present were missing in the prospect pivot report. The previous temporary fix for this was to increase the value of the ROWS_API_LIMIT constant which seems to fix this issue. This PR properly fixes this issue.

Before

There were missing entities data in the pivot report for an entity.

After

The issue with missing entities data in the pivot report for an entity is fixed.

Technical Details

The pivot report extension has business logic that expects the index for an entity (Usually the start data) to be ordered either in ascending or descending order, See. When the data is not ordered, because when building the pivot report cache for entities, it does so in batches defined by the ROWS_API_LIMIT value, for entities that has total greater than the value of this constant, on subsequent runs, the value cached on the first run is overridden and updated. The prospect report does not have an order set for the start date when fetching report, hence there will be values of same start date index that can override the first index cached in the second run, hence the reason why we have issues with this entity report. Fixing this is as simple as setting the start_date to be in ascending order.

        'sort' => 'start_date ASC, id ASC',

However there might be another issue which is that on the second run, an index(start_date) that was cached in the first run still has some left over data to be cached in the second run and this will override the previous values cached. To fix this issue, a unique key is added to the path for each run so that on subsequent runs, the previous values is not updated for the same index path.

  protected function getPath($index, $page = NULL, $addUniqueSuffix = TRUE) {
    $path = 'data_' . $index . '_' . str_pad($page, 6, '0', STR_PAD_LEFT);
    if ($addUniqueSuffix) {
      $path .= '_' . uniqid();
    }

    return $path;
  }

A third issue is the fact that some data was wrongly indexed . For example an index of data_2020-03-04 in the civicrm_pivotreportcache table could be storing data with dates that are unrelated to the index. This was an issue fixed in the getPaginatedResults function due to the loop using an old index because the new index was not properly set.