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

In addition to the report helper introduced in the previous part, 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.

Please note: While technically possible, it is not supported to execute write-based queries and change TestRail's database using the database access methods described in this article. It is only allowed to access the database for read queries.

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 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 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: