Reports: Building a custom report plugin (3/3)

In addition to the report helper introduced in the previous part of building a custom report plugin, it is also supported to access the database directly. This last part explains everything from creating your own database model and accessing the database to passing this data to your views and rendering it.

Database access

Custom database queries for report plugins are usually bundled in a model to separate the database code from the actual report logic. We follow the same convention in our sample report plugin and jump right into the code and the necessary changes for this:

class Tests_property_results_report_plugin extends Report_plugin
{
  private $_model;
 
  ..
 
  public function __construct()
  {
    parent::__construct();
    $this->_model = new Tests_property_results_summary_model();
    $this->_model->init();
    $this->_controls = $this->create_controls(
      self::$_control_schema
    );
  }
 
  ..
}
 
class Tests_property_results_summary_model extends BaseModel
{
  ..
}

A model is a separate class that derives from a base model (BaseModel). An instance of this class is created in the report plugin’s constructor and can be accessed via $this→_model. We can then already add the first methods to the model:

class Tests_property_results_summary_model extends BaseModel
{
  public function get_statuses()
  {
    $this->db->select('*');
    $this->db->from('statuses');
    $this->db->where('is_active', true);
    $this->db->order_by('display_order');
    return $this->db->get_result();
  }
 
  public function get_types()
  {
    $this->db->select('*');
    $this->db->from('case_types');
    $this->db->where('is_deleted', false);
    $this->db->order_by('name', 'asc');
    return $this->db->get_result();
  }
 
  public function get_type_results($run_ids, $type_id)
  {
    $query = $this->db->query(
      'SELECT
        tests.status_id,
        COUNT(*) as status_count
      FROM
        tests
      JOIN
        cases
          on
        cases.id = tests.content_id
      WHERE
        tests.run_id in ({0}) and
        cases.type_id = {1}
      GROUP BY
        tests.status_id',
      $run_ids,
      $type_id
    );
 
    $results = $query->result();
    return obj::get_lookup_scalar(
      $results,
      'status_id',
      'status_count'
    );
  }
 
  /* Skipped for priorities */
 
  ..
}

There are a few things to explain here. Models get access to the database via the $this→_db object and can issue raw queries (as in get_type_results) or use a lightweight wrapper (as in get_statuses). The wrapper doesn’t support all possible SQL commands and we recommend using raw queries for anything that is more complex than a simple select, from, where, get sequence. Note how SQL parameters are automatically quoted, converted and escaped in both cases if needed.

Going back to the report plugin class, we can leverage the new model methods as follows:

public function run($context, $options)
{
  ..
 
  // Get all active statuses from the database.
  $statuses = $this->_model->get_statuses();
  $status_ids = obj::get_ids($statuses);
 
  // Read the case types and priorities from the database.
  $types_include = $options['types_include'];
  $types = array();
  $types_results = array();
 
  if ($types_include && $run_ids)
  {
    $types = $this->_model->get_types();
    foreach ($types as $type)
    {
      $types_results[$type->id] = 
        $this->_model->get_type_results(
          $run_ids,
          $type->id
        );
    }
  }
 
  /* Skipped for priorities *
 
  ..
 
}

Rendering data

Recall that the actual data is rendered to static HTML pages via views. The missing piece now is to pass the data to our views and render it accordingly. We’ve already set up a standard “index” view in part 1 of building a custom report plugin and we can simply modify the render_page call to pass our data as follows:

..
 
return array(
  'resources' => self::$_resources,
  'html_file' => $this->render_page(
    'index',
    array(
      'report' => $context['report'],
      'project' => $project,
      'runs' => $runs,
      'run_rels' => $run_rels,
      'statuses' => $statuses,
      'types_include' => $types_include,
      'types' => $types,
      'types_results' => $types_results,
      'priorities_include' => $priorities_include,
      'priorities' => $priorities,
      'priorities_results' => $priorities_results
    )
  )
);
 
..

The full source code for the views is not listed here and please refer to TestRail Customizations GitHub repository for this. It should be easy to follow and just generates the HTML based on the report options and passed data.

All put together, rendered reports now look as follows: