Report Development: Cards

Previous Section: Dashboards


A card is an individual number, chart or table that is part of a report dashboard.

Contents

Creating Cards

Card Basics

Each card is its own PHP class. We recommend storing these under Reports/Cards/[Dashboard Name]/ within the report, but you are free to place these where you like as they are manually registered in the dashboard later.

All cards have three configuration options in common, a name, a width and a height. The dashboard is a grid which spans a width of 12 columns, so cards should be designed to work together to fit this grid. The maximum height a card can have is 12 rows, but usually a value in the region of 2-6 works well.


protected int $width = 4;
protected int $height = 2;

public function name(): string
{
    return 'Backlog Number';
}

Writing Queries

All cards will need at least one query which fetches data from the database and converts this to the relevant format needed for that card type. If you intend to use either the timeframe or filtering options that were available to set in the dashboard, you will need to use our QueryBuilder class.


use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;

...

QueryBuilder::for(Ticket::class)
    ->applyFilters($this->dashboard()->filters('ticket.created_at'))

To break this down, the for method sets the model that the query will run on, in this case the Ticket model. If you intend to use filtering, this should either be the \App\Modules\Ticket\Models\Ticket or \App\Modules\User\Models\User class.

The applyFilters line will apply any filtering conditions set in the operator panel automatically, and the param of $this->dashboard()->filters() is the timeframe column. This should be set to the column that the timeframe applies to, which in our example is the ticket created date.

If you have the timeframe disabled but filtering enabled, you can exclude the timeframe column parameter.


QueryBuilder::for(Ticket::class)
    ->applyFilters($this->dashboard()->filters())

If you have filtering and timeframe disabled, you can exclude the applyFilters call and continue to build your query.


QueryBuilder::for(Ticket::class)
    ->where('ticket.status_id', 1)
    ...

We recommend including the name of the table in the clauses, such as ticket.number and ticket_message.type to avoid potential issues with duplicated column names.

If you need to join with another table, use the joinOnce or leftJoinOnce methods as these will ensure there are no problems with subsequent joins that may come from filtering.


QueryBuilder::for(Ticket::class)
    ->applyFilters($this->dashboard()->filters('ticket.created_at'))
    ->joinOnce('user', 'ticket.user_id', '=', 'user.id')
    ->joinOnce('user_organisation', 'user.organisation_id', '=', 'user_organisation.id')
    ...

Before getting the results of the query, you may want to use the getQuery method which will return the underlying query builder instance without the model. This is useful in cases where you are just getting aggregates and counts, but you will want the model if you intend to use any of its features such as the relations ($ticket->status->name for example).


QueryBuilder::for(Ticket::class)
    ...
    ->getQuery()
    ->get()

Card Types

Now you can build your card, there are four types of cards available:

Number Card

A number card can be used to display just a single number or percentage.

Card Format

The card must extend the \App\Modules\Report\Addon\Dashboards\Cards\NumberCard class. It must also have a number method which returns a float (or integer).


public function number(): ?float
{
    ...

    return $query->count();
}
Number Format

By default, the number card will display a number with up to 2 decimal places if it is not an integer.


use App\Modules\Report\Addon\Dashboards\Cards\Number\NumberFormat;

...

protected NumberFormat $numberFormat = NumberFormat::DECIMAL;
protected int $precision = 2;

The number format can be changed to a percentage by using NumberFormat::PERCENT.


protected NumberFormat $numberFormat = NumberFormat::PERCENT;
Example

Below is an example number card which shows the number of non-resolved tickets.

Reports/Cards/Overview/BacklogNumber.php
<?php declare(strict_types=1);

namespace Addons\Reports\TicketBacklog\Reports\Cards\Overview;

use App\Modules\Report\Addon\Dashboards\Cards\Number\NumberFormat;
use App\Modules\Report\Addon\Dashboards\Cards\NumberCard;
use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Config;

class BacklogNumber extends NumberCard
{
    protected int $width = 4;
    protected int $height = 2;

    protected NumberFormat $numberFormat = NumberFormat::DECIMAL;
    protected int $precision = 2;

    public function name(): string
    {
        return 'Backlog Number';
    }

    public function number(): ?float
    {
        return QueryBuilder::for(Ticket::class)
            ->applyFilters($this->dashboard()->filters('ticket.created_at'))
            ->where('ticket.status_id', '<>', Config::get('settings.default_resolved_status'))
            ->count();
    }
}

Once the card is loaded, it will look like this.

Number Card

HTML Card

A HTML card can be used to display anything, usually useful if you need to include more information or visuals other than just a number.

Card Format

The card must extend the \App\Modules\Report\Addon\Dashboards\Cards\HtmlCard class. It must have a html method which returns a Laravel View.


use Illuminate\Contracts\View\View;

...

public function html(): View
{
    ...
}
Writing Views

Views are stored in the Views folder of the report. SupportPal uses the Twig template system and all templates should have a .twig file extension.

Views/card.twig

<div class="sp-text-center">
    {{ variable }}
</div>

These views can be returned from a controller method using the following, where the name of the file is placed after the ::.


\TemplateView::make('Reports#TicketBacklog::card');

Variables can be fed to the view with the with method.


\TemplateView::make('Reports#TicketBacklog::card')
    ->with('variable', 'value');
Example

Below is an example HTML card which shows the number of non-resolved tickets.

Reports/Cards/Overview/BacklogInactive.php
<?php declare(strict_types=1);

namespace Addons\Reports\TicketBacklog\Reports\Cards\Overview;

use App\Modules\Report\Addon\Dashboards\Cards\HtmlCard;
use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;
use Illuminate\Contracts\View\View;
use Illuminate\Support\Facades\Config;
use TemplateView;

use function now;

class BacklogInactive extends HtmlCard
{
    protected int $width = 4;
    protected int $height = 2;

    public function name(): string
    {
        return 'Backlog Inactive';
    }

    public function html(): View
    {
        $count = QueryBuilder::for(Ticket::class)
            ->applyFilters($this->dashboard()->filters('ticket.created_at'))
            ->where('ticket.status_id', '<>', Config::get('settings.default_resolved_status'))
            ->where('ticket.last_reply_time', '<', now()->subDays(7)->getTimestamp())
            ->count();

        return TemplateView::make('Reports#TicketBacklog::card')
            ->with('count', $count);
    }
}
Views/card.twig

<div class="sp-text-center">
    <span class="sp-text-4xl sp-font-bold">{{ count }}</span>
    <div class="sp-description">Tickets where last reply was more than 7 days ago</div>
</div>

Once the card is loaded, it will look like this.

HTML Card

Chart Card

A chart card can be used to display a bar, line or pie chart. The charts are rendered with the Chart.js library.

Card Format

The card must extend the \App\Modules\Report\Addon\Dashboards\Cards\ChartCard class. It must also have a data method which returns a \App\Modules\Report\Addon\Dashboards\Cards\Chart\DataStructure instance - the data structure class will contain the relevant datasets, labels and options for the chart.


use App\Modules\Report\Addon\Dashboards\Cards\Chart\DataStructure;

...

public function data(): DataStructure
{
    ...
}
Datasets

To build a data structure, we need to start with the datasets. A dataset is essentially an array of data points, and a chart may be made up of one or more datasets. There are two main types of datasets.

Primitive Dataset

A primitive dataset is an array of values in one dimension and has no keys, for example [1, 4, 6]. These are commonly used on pie charts where there is only one thing being measured.


use App\Modules\Report\Addon\Dashboards\Cards\Chart\Datasets\PrimitiveDataset;

...

$dataset = new PrimitiveDataset([5, 2, 18]);
Object Dataset

An object dataset will be an array of data in two dimensions (x and y data points), for example [{x: 10, y: 20}, {x: 15, y: 35}]. This is commonly used on bar and line charts where there are two axes.


use App\Modules\Report\Addon\Dashboards\Cards\Chart\Datasets\ObjectDataset;

...

$dataset = new ObjectDataset([['x' => 0, 'y' => 20], ['x' => 2, 'y' => 35]]);

If your dataset is part of a stacked graph (multiple datasets that overlap), you can set a label for each dataset as the second parameter of the ObjectDataset class.


$goodDataset = new ObjectDataset([['x' => 0, 'y' => 5], ['x' => 1, 'y' => 2]], 'Good');
$badDataset = new ObjectDataset([['x' => 0, 'y' => 2], ['x' => 1, 'y' => 0]], 'Bad');

A normal object dataset must have x and y data points. If your data points have different key names, you can use a CustomObjectDataset class instead. The second parameter is the x-axis key and the third parameter is the y-axis key. It also accepts a label as a fourth parameter should it be required.


use App\Modules\Report\Addon\Dashboards\Cards\Chart\Datasets\CustomObjectDataset;

...

$dataset = new CustomObjectDataset(
    [['name' => 'John Doe', 'count' => 20], ['name' => 'Lisa Monroe', 'count' => 35]],
    'count',
    'name',
);

Should you need to set any dataset level options, you can use the setOptions method for this.


$dataset->setOptions(['backgroundColor' => '#f87171']);
Data Structure

The data structure class takes the chart type as the first argument and a dataset as the second argument.


use App\Modules\Report\Addon\Dashboards\Cards\Chart\ChartType;
use App\Modules\Report\Addon\Dashboards\Cards\Chart\DataStructure;

...

$dataStructure = new DataStructure(ChartType::BAR, $dataset);

There are three types of chart types available:

Data structures can contain more than one dataset, you can add more with the addDataset method. All the datasets must be of the same type.


$dataStructure->addDataset($dataset2);

Use the setAxisNames method to set the displayed names for the axes, and the setLabels method to set the labels for the data. The labels are only usually needed if there is one dataset.


$dataStructure->setAxisNames('Organisation', 'Tickets');
$dataStructure->setLabels($results->pluck('name')->toArray());

Any chart level options can be set using the setOptions method.


$dataStructure->setOptions([
    'plugins' => [
        'legend' => ['display' => false]
    ],
    'scales' => [
        'y' => [
            'ticks' => ['precision' => 0],
        ],
    ]
]);
Example

Below is an example chart card showing a bar chart of non-resolved tickets by priority.

Reports/Cards/Overview/BacklogByPriorityChart.php
<?php declare(strict_types=1);

namespace Addons\Reports\TicketBacklog\Reports\Cards\Overview;

use App\Modules\Report\Addon\Dashboards\Cards\Chart\ChartType;
use App\Modules\Report\Addon\Dashboards\Cards\Chart\Datasets\CustomObjectDataset;
use App\Modules\Report\Addon\Dashboards\Cards\Chart\DataStructure;
use App\Modules\Report\Addon\Dashboards\Cards\ChartCard;
use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;
use Illuminate\Support\Facades\Config;

use function trans_choice;

class BacklogByPriorityChart extends ChartCard
{
    protected int $width = 6;

    public function name(): string
    {
        return 'Backlog By Priority';
    }

    public function data(): DataStructure
    {
        $data = QueryBuilder::for(Ticket::class)
            ->applyFilters($this->dashboard()->filters('ticket.created_at'))
            ->join('ticket_priority', 'ticket_priority.id', '=', 'ticket.priority_id')
            ->where('ticket.status_id', '<>', Config::get('settings.default_resolved_status'))
            ->groupBy('ticket.priority_id')
            ->select('ticket_priority.name')
            ->selectRaw('COUNT(*) as aggregate')
            ->orderBy('ticket_priority.order')
            ->getQuery()
            ->get();

        $dataset = new CustomObjectDataset($data->map(fn($v) => (array) $v)->all(), 'aggregate', 'name');

        return (new DataStructure(ChartType::BAR, $dataset))
            ->setLabels($data->pluck('name')->toArray())
            ->setAxisNames(trans_choice('ticket.ticket', 2), trans_choice('ticket.priority', 1))
            ->setOptions([
                'indexAxis' => 'y',
                'plugins' => [
                    'legend' => ['display' => false]
                ],
                'scales' => [
                    'x' => [
                        'ticks' => ['precision' => 0],
                    ],
                ]
            ]);
    }
}

Once the card is loaded, it will look like this.

Chart Card

Table Card

A table card should be used to show any tabular data, for example a list of tickets or feedback responses.

Card Format

The card must extend the \App\Modules\Report\Addon\Dashboards\Cards\TableCard class. It must have a query method which returns a QueryBuilder instance (see Writing Queries) as well as a columns method which is a list of columns to show in the table.


use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;

...

public function query(): QueryBuilder
{
    return QueryBuilder::for(Ticket::class)
        ->applyFilters($this->dashboard()->filters('ticket.created_at'))
        ...
}

public function columns(): array
{
    return [
        // Columns here
    ];
}
Table Columns

The columns method must return an array of \App\Modules\Report\Addon\Dashboards\Cards\Table\Column instances. The class takes the column title as a constructor argument, below is a simple example for a column that shows the date the model was created.


$column = (new \App\Modules\Report\Addon\Dashboards\Cards\Table\Column('Date'))
    ->setValue(function ($model) {
        return formatDate($model->created_at);
    });

The column will automatically generate an ID based on the title, but if you need to generate a different ID, this can be done with the setId method.


$column->setId('ticket')

If you need to set any Datatables options for the column, you can do this with the setDefinition method.


$column->setDefinition(['width' => '160px'])

The setValue method by default will escape any HTML in the value. The second parameter of the method lets you avoid escaping the value, which is necessary if it includes HTML - ensure to escape any potential user input using the e method to avoid the risk of XSS.


$column->setValue(function ($model) {
    $route = route('ticket.operator.ticket.show', [$model->id]);

    return sprintf('#%s - %s', $route, e($model->number), e($model->subject));
}, false)

The setCsvValue method is used when generating the export data and in this case would show the ticket number without the link. If this method is omitted, it will try to take a plain-text version of the value.


$column->setCsvValue(fn($model) => sprintf('#%s - %s', $model->number, $model->subject))

Finally, you need to ensure all these columns are returned in the order that you wish them to display.


use App\Modules\Report\Addon\Dashboards\Cards\Table\Column;

...

public function columns(): array
{
    $dateColumn = (new Column('Date'))
        ->setValue(function ($model) {
            return formatDate($model->created_at);
        });

    ...

    return [
        $dateColumn,
        ...
    ];
}
Table Options

The table is loaded using Datatables, should you need to change any of the default options you can declare this using the tableOptions method.


public function tableOptions(): array
{
    return [
        'ordering' => true,
        'order'    => [[1, 'desc']]
    ];
}
Example

Below is an example table card which lists all non-resolved tickets.

Reports/Cards/Overview/BacklogTable.php
<?php declare(strict_types=1);

namespace Addons\Reports\TicketBacklog\Reports\Cards\Overview;

use App\Modules\Report\Addon\Dashboards\Cards\Table\Column;
use App\Modules\Report\Addon\Dashboards\Cards\TableCard;
use App\Modules\Report\Addon\Dashboards\Filtering\QueryBuilder;
use App\Modules\Ticket\Models\Ticket;
use Illuminate\Support\Facades\Config;

use function e;
use function formatDate;
use function route;
use function sprintf;
use function trans;
use function trans_choice;

class BacklogTable extends TableCard
{
    public function name(): string
    {
        return 'Backlog List';
    }

    public function query(): QueryBuilder
    {
        return QueryBuilder::for(Ticket::class)
            ->applyFilters($this->dashboard()->filters('ticket.created_at'))
            ->where('ticket.status_id', '<>', Config::get('settings.default_resolved_status'));
    }

    public function columns(): array
    {
        return [
            $this->dateColumn(),
            $this->ticketColumn(),
            $this->subjectColumn(),
            $this->userColumn(),
            $this->lastReplyColumn(),
        ];
    }

    protected function dateColumn(): Column
    {
        return (new Column(trans('general.date')))
            ->setId('created_at')
            ->setDefinition(['width' => '180px'])
            ->setValue(fn($model) => formatDate($model->created_at));
    }

    protected function ticketColumn(): Column
    {
        return (new Column(trans_choice('ticket.ticket', 1)))
            ->setCsvValue(fn($model) => sprintf('#%s', $model->number))
            ->setValue(function ($model) {
                $route = route('ticket.operator.ticket.show', [$model->id]);

                return sprintf('<a href="%s">#%s</a>', e($route), e($model->number));
            }, false);
    }

    protected function subjectColumn(): Column
    {
        return (new Column(trans('ticket.subject')))
            ->setValue(function ($model) {
                return e($model->subject);
            });
    }

    protected function userColumn(): Column
    {
        return (new Column(trans_choice('user.user', 1)))
            ->setCsvValue(fn($model) => $model->user->formatted_name)
            ->setValue(function ($model) {
                return sprintf(
                    '<img src="%s" class="sp-avatar sp-max-w-2xs">&nbsp; <a href="%s">%s</a>',
                    e($model->user->avatar_url),
                    e(route('user.operator.user.edit', [$model->user->id])),
                    e($model->user->formatted_name)
                );
            }, false);
    }

    protected function lastReplyColumn(): Column
    {
        return (new Column(trans('ticket.last_reply')))
            ->setId('last_reply')
            ->setDefinition(['width' => '180px'])
            ->setValue(fn($model) => formatDate($model->last_reply_time));
    }
}

Once the card is loaded, it will look like this.

Table Card

Registering Cards

In the dashboard loadCards method, the cards should be added in the order you wish to show them in the grid.


public function loadCards(CardCollection $collection): void
{
    $collection->add(new Cards\Overview\BacklogNumber);
    $collection->add(new Cards\Overview\BacklogInactive);
    $collection->add(new Cards\Overview\BacklogByPriorityChart);
    $collection->add(new Cards\Overview\BacklogTable);
}

To make this dashboard work based on the grid of 12 columns, we change the first two cards (number and HTML cards) to be 6 columns wide and the chart card to be 12 columns wide. It is best to design your grid once you have all cards ready and loaded.


Next Section: Underlying Data