Report Packs Internals 3 PHP and SQL

Report Packs internals, part 3: PHP and SQL

Series: 1. The designer · 2. The layout engine · 3. PHP and SQL · overview: Report Packs developer guide

This part covers the server side: the two tables, the nine endpoints, who may open, change and share a pack, how every design is checked, the block registry, and how each block's data is fetched, with the reader's rights, the reader's timezone and the reader's companies.

The server does three jobs and nothing else. It stores a design it has checked, it decides who may see the pack, and it answers one block's data at a time. It never lays out a page or builds a PDF; that all happens in the browser (parts 1 and 2). So the server holds no rendering code, and a 40-page export costs it nothing but the block queries.

Files: includes/report_packs/ (access.php, design.php, blocks.php, criteria.php, blocks_tickets.php, blocks_status.php, blocks_software.php, blocks_assets.php, api_common.php), api/reporting/packs/*.php, and includes/services/service_uptime.php.

---

Contents

1. The tables
2. The API
3. Who may open, change and share a pack
4. The design document, and the check on every save
5. Saving: optimistic concurrency
6. The block registry
7. Criteria: the period and the company
8. Data functions
9. Security, in one place
10. Adding a block, end to end
11. Tests
12. Traps

---

1. The tables

CREATE TABLE IF NOT EXISTS `report_packs` (
    `id`               INT NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(200) NOT NULL,
    `description`      VARCHAR(500) NULL,
    `owner_id`         INT NULL,
    `design`           LONGTEXT NOT NULL,            -- the whole design, one JSON document
    `created_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_datetime` DATETIME NULL,                -- the stamp optimistic concurrency compares
    `updated_by`       INT NULL,
    PRIMARY KEY (`id`),
    KEY `ix_rp_owner` (`owner_id`),
    CONSTRAINT `fk_rp_owner`   FOREIGN KEY (`owner_id`)   REFERENCES `analysts` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_rp_updater` FOREIGN KEY (`updated_by`) REFERENCES `analysts` (`id`) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS `report_pack_shares` (
    `id`               INT NOT NULL AUTO_INCREMENT,
    `pack_id`          INT NOT NULL,
    `target_type`      VARCHAR(20) NOT NULL,        -- analyst | team | department
    `target_id`        INT NULL,                    -- analysts.id or teams.id
    `target_value`     VARCHAR(255) NULL,           -- the department's text
    `can_edit`         TINYINT(1) NOT NULL DEFAULT 0,
    `created_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_rps_pack` (`pack_id`),
    KEY `ix_rps_target` (`target_type`, `target_id`),
    CONSTRAINT `fk_rps_pack` FOREIGN KEY (`pack_id`) REFERENCES `report_packs` (`id`) ON DELETE CASCADE
);

They live in all four schema places: database/freeitsm.sql, includes/db_verify_schema.php, the indexes in includes/db_verify_indexes.php, and the foreign keys in api/system/db_verify.php.

Why the design is one JSON column

A design is a tree: a page, a theme, criteria, a header, footer and cover of paragraphs of runs, and a list of blocks with their own options. It is always read and written whole. The designer loads it, edits it in memory and saves it, and nothing queries inside it ("all packs using tickets.list" isn't a question anyone asks).

Normalising it into a dozen tables would buy joins nobody needs and a migration for every new option. As one document, a new block option is a new key, and the check in section 4 brings old documents into today's shape on read.

Why the owner is SET NULL, not CASCADE

Deleting an analyst who built the monthly board pack shouldn't delete the pack. It becomes orphaned (owner_id IS NULL), and administrators are treated as its owner (section 3), so it can still be managed, re-shared or removed.

Shares cascade with their pack. A share pointing at a deleted analyst or team is skipped when listed (rpListShares drops rows whose label joins to nothing) and dropped when the owner next saves the shares.

---

2. The API

Every endpoint is in api/reporting/packs/, starts the session read_and_close (so a slow block query never holds the session lock and blocks the analyst's other tabs), includes api_common.php, and calls requireModuleAccessJson('reporting') itself. The guard is visible in each file, and tests/module-access-coverage.php checks it is there.

Endpoint	Method	Needs	Does
list.php	GET	Reporting	every pack you can open, with your role
get.php?id=	GET	any role	one pack's design, checked on the way out
create.php	POST	Reporting	new pack from the starter design, or a copy (copy_of) of one you can open
save.php	POST	Edit or owner	name, description, design; stale-save conflict
delete.php	POST	owner	the pack, and its shares by cascade
shares.php	GET / POST	owner	the shares, plus the analysts, teams and departments to choose from / replace them
catalogue.php	GET	Reporting	the toolbox, every handler's option schema, your companies, the date presets
context.php	POST	Reporting	the header field values for some criteria
block_data.php	POST	Reporting + the block's module	one block's data, for you

api_common.php holds the four helpers they share:

function rpOut(array $payload, int $code = 200): void   // JSON out, then exit
function rpFail(string $message, int $code = 400): void  // {success:false, error}
function rpInput(): array                                // the JSON body, or []
function rpInitLocale(PDO $conn): void                   // i18n + the viewer's timezone + date format

rpInitLocale matters more than it looks. Labels (Unassigned, Other), formatted dates in table cells and the header fields are produced on the server, in the reader's language and date format. A pack shared between an analyst in Madrid and one in London shows each of them their own.

---

3. Who may open, change and share a pack

All in includes/report_packs/access.php.

The model

A pack is private to its owner until shared. A share names one of three kinds of people, and grants View (open and export) or Edit (also change the design):

target_type	Matches	Stored in
analyst	one analyst	target_id = analysts.id
team	everyone in a team	target_id = teams.id
department	everyone whose profile's Department says it	target_value

Only the owner shares and deletes.

Department here is the free-text field on an analyst's profile (often filled in by directory sync), not the ticket-routing departments table. Groups of People developer guide explains why FreeITSM has both. It is matched case-insensitively and with whitespace collapsed, because a directory writes IT Services and a person types it services.

Who the viewer is

function rpViewer(PDO $conn, int $analystId): array   // cached per request
{
    // team_ids from analyst_teams, department normalised, is_admin
}
function rpNormDept($s): string
{
    return mb_strtolower(trim(preg_replace('/\s+/u', ' ', (string)$s)));
}

Matching shares in SQL

function rpShareMatchSql(array $viewer): array
{
    $parts  = ["(s.target_type = 'analyst' AND s.target_id = ?)"];
    $params = [$viewer['id']];
    if ($viewer['team_ids']) {
        $parts[] = "(s.target_type = 'team' AND s.target_id IN (?, ?, ...))";
        $params  = array_merge($params, $viewer['team_ids']);
    }
    if ($viewer['department'] !== '') {
        $parts[] = "(s.target_type = 'department' AND LOWER(TRIM(s.target_value)) = ?)";
        $params[] = $viewer['department'];
    }
    return ['(' . implode(' OR ', $parts) . ')', $params];
}

One fragment, reused by both the single-pack check and the list. Branches that can't match (no teams, no department) are left out rather than matched against an empty list.

A role on one pack

function rpRole(PDO $conn, int $analystId, int $packId): ?string
{
    // no such pack                              → null
    // owner_id = me                             → 'owner'
    // owner_id IS NULL and I'm an admin         → 'owner'   (orphaned)
    // SELECT MAX(s.can_edit) ... AND $match     → 1 → 'edit', 0 → 'view', NULL → null
}

• MAX(can_edit): if one share gives you View (your team) and another gives you Edit (you personally), you get the better one.
• null for both "no such pack" and "no access". Every endpoint answers not found for both, so pack ids can't be probed to discover what exists.
• rpRoleAtLeast($role, 'edit') compares through RP_ROLE_RANK = ['view' => 1, 'edit' => 2, 'owner' => 3].

The list in one query

SELECT p.id, p.name, ..., o.full_name AS owner_name, u.full_name AS updated_by_name,
       (SELECT MAX(s.can_edit) FROM report_pack_shares s WHERE s.pack_id = p.id AND $match) AS share_edit,
       (SELECT COUNT(*) FROM report_pack_shares s2 WHERE s2.pack_id = p.id) AS share_count
  FROM report_packs p
  LEFT JOIN analysts o ON o.id = p.owner_id
  LEFT JOIN analysts u ON u.id = p.updated_by
 WHERE p.owner_id = ? [OR p.owner_id IS NULL  -- admins]
    OR EXISTS (SELECT 1 FROM report_pack_shares s WHERE s.pack_id = p.id AND $match)
 ORDER BY COALESCE(p.updated_datetime, p.created_datetime) DESC, p.id DESC

The match fragment appears twice, so its parameters are bound twice (array_merge($params, [$analystId], $params)). One round trip gives every pack, the role on each, whether it is shared at all (for the list's Shared badge), and who touched it last.

Saving shares

function rpSaveShares(PDO $conn, int $packId, int $ownerId, array $shares): void

The share dialog sends the whole list, and it replaces the old one in a transaction (delete all, insert all). Every entry is checked first:

• an analyst or team id must exist now. An unknown id is dropped, never stored, so a share can't point at an id that a future analyst or team would inherit.
• you can't share with yourself (the owner)
• a department is trimmed, whitespace-collapsed and at most 255 characters
• duplicates collapse on a key (analyst:12, department:it services), so the same person or department can't appear twice

rpKnownDepartments() offers the departments active analysts actually have, de-duplicated by normalised form, so the picker suggests IT Services once even if profiles spell it three ways.

⚠️ Access to a pack is not access to its data. Sharing a pack shares the design. Every block's numbers are fetched with the reader's own module access and companies (section 8). Sharing never shows anybody a figure they couldn't already see.

---

4. The design document, and the check on every save

includes/report_packs/design.php.

The shape

{
  v: 1,
  page:     { size: 'A4'|'Letter'|'Legal'|'A3', orient: 'portrait'|'landscape', margin: { t, r, b, l } },   // mm, 5-50
  theme:    { font: 'helvetica'|'times'|'courier', size: 7-16, heading, accent, th_bg, th_fg: '#rrggbb',
              palette: 'default'|'ocean'|'sunset'|'forest'|'mono'|'vivid', stripe: bool },
  criteria: { range: { preset, from?, to? }, tenant: 'active'|'all'|<id> },
  header:   { on, rule, first, doc: [paragraphs] },
  footer:   { on, rule, doc: [paragraphs] },
  cover:    { on, doc: [paragraphs] },
  toc:      { on, title },
  blocks: [
    { id, type: 'heading', span: 12, text, level: 1-3, newPage, toc },
    { id, type: 'text', span: 1-12, doc: [paragraphs], box, newRow },
    { id, type: 'data', span, handler, opts: {...}, title, showTitle, height: 30-250, legend, newRow },
    { id, type: 'spacer', span, height: 2-120 } | { id, type: 'divider' } | { id, type: 'pagebreak' },
  ],
}

Rich text is not HTML

paragraph  {t: p|h1|h2|h3|li|img, a: left|center|right|justify, l: ul|ol, lv: 0-4, r: [runs], w: mm (img)}
run        {x: text | fld: page|pages|date_from|date_to|today|title|company,
            b, i, u, s: bool, c: '#rrggbb', hl: '#rrggbb', sz: 6-72}

There are two reasons it isn't HTML:

1. The engine needs exactly this to break lines identically on screen and in the PDF (part 2). HTML would only be parsed into it anyway.
2. There is nothing to sanitise. Every field below is checked for type and range. The renderers write text with textContent / doc.text(). A run can't carry a script, an event handler, a style attribute or a remote image, because there is nowhere in the schema to put one.

The only picture is the install's own logo ({t: 'img', src: 'logo'}). Any other source would need storing and serving, and a remote URL would let a pack beacon whoever opens it.

rpCleanDesign() — whitelist, clamp, default

function rpCleanDesign($d, string $name): array

Every value in the document goes through one of four small functions:

function rpPick($v, array $allowed, string $default): string   // one of a list, or the default
function rpNum($v, float $min, float $max, float $default): float // clamped number
function rpColour($v, ?string $default): ?string               // '#rrggbb' lowercased, or default
function rpStr($v, int $max): string                           // no control or zero-width characters, cut to $max

The rules:

• Unknown keys are dropped, never kept. The output is built from scratch, key by key; nothing from the input is copied wholesale.
• Out-of-range numbers are clamped, not rejected. A margin of 500mm becomes 50mm. A design from a newer or older version still opens.
• Budgets: at most 400 blocks and 800 paragraphs across the whole design, and at most 2MB encoded (The design is too large).
• Block ids must match ^[A-Za-z0-9_-]{1,40}$; anything else gets a fresh random id, and a duplicate gets a suffix. The designer relies on ids being unique.
• A data block whose handler no longer exists is dropped, and its options go through rpCleanOpts() against the handler's schema (section 6).
• rpStr() strips control characters and zero-width characters (U+200B, U+FEFF). They are invisible, have no glyph in the PDF fonts and print as ? (part 1, Traps).

Checked in, checked out

rpCleanDesign() runs on create, copy, save and get. Running it on the way out (get.php) means a design written by an older version, or edited by hand in the database, is brought into today's shape before the designer or the engine ever sees it. A new option with a default needs no data migration.

The starter design

rpDefaultDesign($name) is what a new pack starts from, modelled on Enrique's report: a centred header with the logo, the pack's name and From {date_from} To {date_to}, a right-aligned footer reading Page {page} of {pages} over a rule, a ready-made cover (off), contents (off), and a Summary heading with a text box. Its words come from lang/en/reporting.php, so a pack starts in its creator's language.

---

5. Saving: optimistic concurrency

A shared pack can have two editors open at once. save.php:

$role = rpRole($conn, $aid, $id);
if ($role === null) rpFail(t('reporting.packs.err.not_found'), 404);
if (!rpRoleAtLeast($role, 'edit')) rpFail(t('reporting.packs.err.view_only'), 403);

$design = rpCleanDesign($in['design'] ?? null, $name);

// The stamp the designer loaded vs the stamp now
$cur = /* COALESCE(updated_datetime, created_datetime), and who saved it */;
if (empty($in['force']) && !empty($in['updated']) && $cur['stamp'] !== $in['updated']) {
    rpOut(['success' => false, 'conflict' => true,
           'error' => t('reporting.packs.err.conflict', ['name' => $cur['full_name'] ?: '?'])], 409);
}

UPDATE report_packs SET name = ?, description = ?, design = ?, updated_datetime = UTC_TIMESTAMP(), updated_by = ? WHERE id = ?

rpOut(['success' => true, 'updated' => /* the new stamp */, 'design' => $design]);

• No locks. Two people can open the same pack; only the second save is stopped, with the name of whoever saved in between. The designer offers Reload or Save mine, which resends with force: true (part 1).
• The response carries the cleaned design, and the designer adopts it. What is on screen after a save is exactly what is stored.
• read_and_close means the session is never locked during the write.

---

6. The block registry

includes/report_packs/blocks.php, in two levels on purpose.

Handlers: what a block is

'tickets.breakdown' => [
    'module' => 'tickets',            // whose data: decides who may see it
    'kind'   => 'chart',              // chart | kpi | table | uptime
    'fn'     => 'rpTicketsBreakdown', // fetches it
    'opts'   => [                     // the option schema
        'by'    => rpOptSelect(t('reporting.packs.opt.by'), $ticketBy, 'status'),
        'basis' => rpOptSelect(t('reporting.packs.opt.basis'), $basis, 'created'),
        'chart' => rpChartOpt('doughnut'),
        'limit' => rpOptInt(t('reporting.packs.opt.limit'), 3, 50, 12),
    ],
],

There are 18 handlers across Tickets, Service Status, Software, Assets and Intune. A pack stores the handler key and its options, never a query.

Toolbox items: what the designer offers

'tickets_by_status'   => ['handler' => 'tickets.breakdown', 'area' => 'tickets', 'span' => 6,
                          'opts' => ['by' => 'status',   'chart' => 'doughnut'], 'kw' => 'status pie'],
'tickets_by_priority' => ['handler' => 'tickets.breakdown', 'area' => 'tickets', 'span' => 6,
                          'opts' => ['by' => 'priority', 'chart' => 'doughnut'], 'kw' => 'priority urgent'],

A toolbox item is a handler with options already chosen, a default width, an area and search keywords; its title and description come from lang/en/reporting.php (packs.tool.<key>). Tickets by status and Tickets by priority are two items over one handler. A block dropped in as one can become the other later by changing Group by in Properties, the way a Word chart changes type without being re-inserted.

Option schemas

Helper	Type	Cleaned by rpCleanOpts() to
rpOptSelect(label, values, default)	select	one of the keys of values, or the default
rpOptInt(label, min, max, default)	int	an integer clamped to [min, max]
rpOptBool(label, default)	bool	true/false
rpOptMulti(label, source)	multi	a de-duplicated list of positive integer ids

function rpCleanOpts(string $handler, $opts): array
{
    $schema = rpHandlers()[$handler]['opts'] ?? [];
    foreach ($schema as $k => $def) { /* default, clamp or whitelist each key */ }
    // keys not in the schema are simply never copied
}

A data function never sees a value its schema didn't declare. That is the whole defence for the by option, whose values pick a SQL fragment (section 8).

The schema also is the UI. catalogue.php sends it to the designer, which builds the Properties pane from it (part 1, section 12). Adding an option to a handler gives it a control with no JavaScript change.

rpBlockData() — the one way to fetch a block

function rpBlockData(PDO $conn, int $analystId, string $handler, $opts, array $criteria): array
{
    $h = rpHandlers()[$handler] ?? null;
    if (!$h) throw new RuntimeException(t('reporting.packs.err.unknown_block'));
    if (!analystCanAccessModule($conn, $analystId, $h['module'])) {
        throw new RuntimeException(t('reporting.packs.err.no_module', ['module' => rpModuleName($h['module'])]));
    }
    $range  = rpResolveRange(is_array($criteria['range'] ?? null) ? $criteria['range'] : []);
    $tenant = $criteria['tenant'] ?? 'active';
    $data = call_user_func($h['fn'], $conn, $analystId, rpCleanOpts($handler, $opts), $range, $tenant);
    $data['kind'] = $data['kind'] ?? $h['kind'];
    return $data;
}

block_data.php is a thin wrapper around this, and it is deliberately not tied to a pack id. The designer previews blocks before they are saved, and the answer depends only on who is asking. A RuntimeException carries a message meant for the block (You do not have access to Software), returned as {success: false, error, blocked: true} and drawn in place (part 2). Anything else is logged and answered with a generic error.

The catalogue

rpCatalogue($conn, $analystId) sends the designer every handler (module, kind, option schema with select values as {value, label} pairs, and multi choices from rpOptionSource()) and every toolbox item, each marked available for this viewer.

Items the viewer can't use are still described, not hidden. A shared pack using Software blocks then makes sense to a reader without Software: the toolbox shows them greyed out with needs Software, and the blocks show a placeholder. multi choices (the services list) are only filled in for a viewer who can access that module.

---

7. Criteria: the period and the company

includes/report_packs/criteria.php. Every block in a pack reads the same criteria, set once on the Data tab, so September means the same thirty days on the doughnut, the uptime list and the incident table.

The period, in the reader's timezone

const RP_RANGE_PRESETS = ['today', 'yesterday', 'this_week', 'last_week', 'this_month', 'last_month',
    'this_quarter', 'last_quarter', 'this_year', 'last_year',
    'last_7_days', 'last_30_days', 'last_90_days', 'last_12_months', 'custom'];

function rpResolveRange(array $range, ?string $tz = null): array
// → ['preset', 'from_date', 'to_date' (inclusive, for display), 'from_utc', 'to_utc' (exclusive), 'days', 'tz']

A preset is resolved in the viewer's timezone, then the bounds are converted to UTC, because FreeITSM stores UTC (UTC at rest, #126). Last month for an analyst in Santo Domingo (UTC-4) in October 2026 is:

from_date  2026-09-01            from_utc  2026-09-01 04:00:00
to_date    2026-09-30            to_utc    2026-10-01 04:00:00   ← exclusive

So a ticket logged at 23:30 on 30 September in Santo Domingo (03:30 UTC on 1 October) counts in September, which is where the reader expects it.

• The end is exclusive ([from, to)), so consecutive periods never double-count a boundary second. Every query uses >= ? AND < ?.
• A custom range is inclusive of both days the reader picked: its end is the day after to. Swapped dates are swapped back, and the span is capped at 3,660 days (a report is not a data export).
• Weeks start on Monday, quarters are calendar quarters, and last 12 months runs from the first of the month eleven months ago to the end of today.
• Nothing from the browser reaches SQL as a date string unchecked: presets are whitelisted and custom dates must match ^\d{4}-\d{2}-\d{2}$.

The company

function rpTenantClause(PDO $conn, int $analystId, $tenant, string $qualified): array
// → [" AND t.tenant_id = ?", [6]]  or  [" AND (t.tenant_id = ? OR t.tenant_id IS NULL)", [1]]  or  ['', []]

tenant	Means
'active' (default)	the company the viewer is working in right now, as every dashboard does
'all'	every company the viewer may see (allAccessibleTenantsFilter)
an id	that one company, which the viewer must be able to access

• Single-company installs get an empty clause: no cost, no behaviour.
• NULL company rows belong to the Default company, the same rule as every list in FreeITSM, so the Default company's clause includes IS NULL.
• A pack set to a company the reader can't see doesn't show them that company. It throws This report is set to a company you do not have access to. A shared pack is read with the reader's rights, never the author's.

Context: the header's fields

context.php resolves the criteria for the header and footer: date_from, date_to and today formatted in the reader's date format (rpFmtDate() → DateFmt), and the company's name (rpTenantLabel()). It also returns a company_error instead of failing, so the designer can say so in its status bar while still drawing the pack.

---

8. Data functions

Every handler's fn has the same signature and returns one of four kinds:

function rpSomething(PDO $conn, int $analystId, array $opts, array $range, $tenant): array

['kind' => 'chart', 'chart' => 'bar'|'hbar'|'line'|'doughnut'|'pie', 'labels' => [...],
 'series' => [['name' => '...', 'values' => [...]]], 'colours' => [...] | null]
['kind' => 'kpi',   'tiles' => [['label' => '...', 'value' => '42', 'hint' => '...']]]
['kind' => 'table', 'columns' => [['key' => 'k', 'label' => '...', 'w' => 3, 'align' => 'right']],
 'rows' => [...], 'empty' => '...', 'note' => '...']
     // a cell is a string, ['pill' => text, 'colour' => hex] or ['chips' => [['text', 'colour']...]]
     // a row may carry '_detail': a full-width line under it
['kind' => 'uptime', 'services' => [...], 'from' => '...', 'to' => '...', ...]
// any kind may add 'snapshot' => true: "this shows now, not the period" (the engine captions it)

The engine draws by kind, not by block (part 2). That is why a new block needs no JavaScript.

A worked example: tickets by any dimension

const RP_TICKET_DIMENSIONS = [
    'status'   => ["COALESCE(ts.name, 'Unknown')", '', 'MAX(ts.colour)'],
    'category' => ["COALESCE(croot.name, cmid.name, cleaf.name, 'Not categorised')",
                   'LEFT JOIN ticket_categories cleaf ON cleaf.id = t.category_id
                    LEFT JOIN ticket_categories cmid  ON cmid.id  = cleaf.parent_id
                    LEFT JOIN ticket_categories croot ON croot.id = cmid.parent_id', null],
    'analyst'  => ["COALESCE(a.full_name, 'Unassigned')", 'LEFT JOIN analysts a ON a.id = t.assigned_analyst_id', null],
    // ... priority, department, type, team, origin, resolution, first_time_fix
];

function rpTicketsBreakdown(PDO $conn, int $analystId, array $o, array $range, $tenant): array
{
    [$label, $join, $colour] = RP_TICKET_DIMENSIONS[$o['by']];       // $o['by'] is a whitelisted key
    [$where, $params] = rpTicketWhere($conn, $analystId, $o['basis'], $range, $tenant);
    $colSql = $colour ? ", $colour AS colour" : '';
    $sql = "SELECT $label AS label, COUNT(*) AS value $colSql
              FROM tickets t " . RP_TICKET_LOOKUPS . " $join
              $where
             GROUP BY label ORDER BY value DESC";
    ...
    return rpChartFromRows($rows, $o['limit'], $o['chart'], t('reporting.packs.series.tickets'));
}

• The SQL fragments are constants in the code. The option only chooses which constant. rpCleanOpts() guarantees $o['by'] is a key of the dimension table, so no browser value is ever interpolated into SQL. Every value is bound.
• Categories roll up to their root through three self-joins, matching the ticket dashboard.
• Status and priority bring their own colours (MAX(ts.colour)), so a pack's status doughnut matches the colours used everywhere else in FreeITSM.
• The empty buckets are named as the dashboard names them (Unassigned, Not categorised), so a pack and a dashboard agree.

Three ways to pick tickets for a period

function rpTicketWhere(PDO $conn, int $analystId, string $basis, array $range, $tenant): array
{
    [$tSql, $tParams] = rpTenantClause($conn, $analystId, $tenant, 't.tenant_id');
    $w = 'WHERE t.deleted_datetime IS NULL' . $tSql;        // trashed tickets never count
    if ($basis === 'closed')   // closed inside the range
        $w .= ' AND t.closed_datetime >= ? AND t.closed_datetime < ?';
    elseif ($basis === 'open') // still open at the END of the range: the backlog on that day
        $w .= ' AND t.created_datetime < ? AND (t.closed_datetime IS NULL OR t.closed_datetime >= ?)';
    else                       // created inside the range
        $w .= ' AND t.created_datetime >= ? AND t.created_datetime < ?';
}

open is a snapshot at the period's end. How big was the backlog on 30 September? is answerable even in November, because it asks what was created before the end and not closed by then, rather than what is open now.

Other, not fifty slivers

function rpChartFromRows(array $rows, int $limit, string $chart, string $seriesName): array

Rows past limit are folded into one Other slice, so a pie never has fifty slivers. Colours from the database are passed through only if they look like hex colours.

Time series are bucketed in PHP, not with GROUP BY DATE()

function rpTimeBuckets(array $range, string $grouping): array   // 'auto' → day ≤45 days, week ≤190, else month
function rpBucketCount(array $stamps, array $range, string $grouping, array $keys): array
{
    $zone = new DateTimeZone($range['tz']);
    foreach ($stamps as $utc) {
        $d = (new DateTimeImmutable($utc, new DateTimeZone('UTC')))->setTimezone($zone);   // UTC → the reader's day
        $k = rpBucketKey($d, $grouping);
        if (isset($counts[$k])) $counts[$k]++;
    }
}

GROUP BY DATE(created_datetime) would bucket by the UTC day, so in Santo Domingo every ticket logged after 20:00 would land on the next day's bar. The trend fetches the timestamps and buckets them into local days, weeks (Monday) or months in PHP. Every bucket in the range is present, including empty ones, so a quiet Sunday shows as a zero rather than vanishing from the axis.

Service Status: the board's own arithmetic

$incidents = ServiceUptime::incidentsBetween($conn, $svcId, $range['from_utc'], $range['to_utc']);
$sum       = ServiceUptime::summaryBetween($conn, $svcId, $range['from_utc'], $range['to_utc'], $incidents);
$strip     = ServiceUptime::dailyStripBetween($conn, $svcId, $range['from_date'], $range['to_date'], $range['tz'], $incidents);

The *Between methods were added beside the status board's own *For window methods, not built by changing them. They share the board's rules: downtime segments from the update log, overlaps unioned, and only impact levels that count as downtime. So a pack can't disagree with the board about how uptime is worked out, only about the period. The incidents are fetched once and passed into the other two calls, so each service costs one incident query, not three.

Service Status has no company column (services and incidents belong to the install), so its blocks ignore the company criterion. Intune is the same.

Software: a snapshot, scoped through the machine

The inventory describes now, so software blocks mark themselves snapshot and the engine captions them; the date range doesn't apply. They count machines, not installs (COUNT(DISTINCT d.host_id)), as the Software dashboard does.

They are scoped to the pack's company through the machine each install is on (software_inventory_detail.host_id = assets.id, then rpTenantClause on h.tenant_id). The Software dashboard itself isn't company-scoped, so on a multi-company install a pack and the dashboard can differ. The file's header says so.

---

9. Security, in one place

Concern	How it is handled
Opening a pack you weren't given	rpRole() on every endpoint; not found for both "no such pack" and "no access"
Changing a pack you may only view	save.php requires Edit; the designer's read-only mode is only a courtesy
Sharing or deleting someone else's pack	owner only (shares.php, delete.php)
Seeing data through a shared pack	every block fetched with the reader's module access (rpBlockData) and companies (rpTenantClause)
A company the reader can't see	refused with a message, never served
SQL injection through options	options whitelisted by schema (rpCleanOpts); SQL fragments are constants; values bound; LIMIT cast to int
Script injection through text	no HTML stored; rich text is a checked model; renderers use textContent / doc.text()
A pack that beacons its readers	no remote images: the only picture is the install's logo
A huge or hostile design	400 blocks, 800 paragraphs, 2MB, strings capped, numbers clamped, unknown keys dropped
Share targets inherited by a future id	unknown analyst and team ids are dropped, never stored
Session lock during slow queries	session_start(['read_and_close' => true]) everywhere
Cross-site request forgery	the central check (includes/csrf.php, 3.0.0) covers every POST here; the designer's fetch carries the token automatically (assets/js/csrf.js). See CSRF protection

---

10. Adding a block, end to end

Say, Changes by status.

1. The data function, in a new includes/report_packs/blocks_changes.php, required from blocks.php:

   function rpChangesByStatus(PDO $conn, int $analystId, array $o, array $range, $tenant): array
   {
       [$tSql, $tParams] = rpTenantClause($conn, $analystId, $tenant, 'c.tenant_id');
       $s = $conn->prepare("SELECT COALESCE(cs.name, 'Unknown') AS label, COUNT(*) AS value
                              FROM changes c LEFT JOIN change_statuses cs ON cs.id = c.status_id
                             WHERE c.created_datetime >= ? AND c.created_datetime < ?$tSql
                             GROUP BY label ORDER BY value DESC");
       $s->execute(array_merge([$range['from_utc'], $range['to_utc']], $tParams));
       return rpChartFromRows($s->fetchAll(PDO::FETCH_ASSOC), $o['limit'], $o['chart'], t('reporting.packs.series.changes'));
   }

   (The table and column names are illustrative; check the real schema.)

2. A handler in rpHandlers():

   'changes.by_status' => ['module' => 'changes', 'kind' => 'chart', 'fn' => 'rpChangesByStatus',
       'opts' => ['chart' => rpChartOpt('doughnut'), 'limit' => rpOptInt(t('reporting.packs.opt.limit'), 3, 50, 12)]],

3. A toolbox item in rpToolboxItems(), and a changes area in the designer's AREAS list if it's a new area:

   'changes_by_status' => ['handler' => 'changes.by_status', 'area' => 'changes', 'span' => 6, 'opts' => [], 'kw' => 'change status rfc'],

4. Strings in lang/en/reporting.php: packs.tool.changes_by_status.title / .desc, packs.series.changes, and packs.designer.area.changes.

5. Run php tests/report-packs-blocks.php. It runs every value of every option of every handler on real data, and fails on a missing label.

6. A Feature Bingo card if it is something worth setting up (includes/feature_bingo/cards/reporting.php).

No change to the designer, the engine or either renderer.

---

11. Tests

• php tests/report-packs-blocks.php (32 checks, on real data): every block with every option value, labels present, per-block module refusal as a restricted analyst, timezone range resolution, and the design check.
• The packs API over HTTP as four real analysts (35 checks): private until shared, each share type (department matching case- and space-insensitive), View can't save, share or delete, Edit can't share or delete, the stale-save conflict, and copying.
• tests/report-packs-designer-live.html (30 checks, in a real browser, part 1).
• The actual PDF was looked at, not just its page count: Enrique's report rebuilt on real data, exported, and rasterised with pdf.js in headless Chrome.

---

12. Traps

• Keep the board's figures out of reach. New needs got new methods (ServiceUptime::*Between) rather than new parameters on the methods the status board uses. A report must never be able to change what the board says.
• GROUP BY DATE() buckets in UTC. Any new time series must bucket in PHP in the reader's zone (rpBucketCount), or late-evening activity lands on the wrong day for everyone west of Greenwich.
• The end of a range is exclusive. Use >= from_utc AND < to_utc. BETWEEN includes the boundary second and double-counts it with the next period.
• Don't trust a design because the designer made it. rpCleanDesign() runs on the way in and out; a new field needs adding there, or it will be silently dropped on the next save.
• "Department" is the profile's free text, not departments.id. A share to IT Services matches people whose profile says so, in any case or spacing, and nobody else.
• rpViewer() is cached per request. Changing an analyst's teams mid-request (in a test) needs a fresh process to be seen.
• Software blocks are company-scoped; the Software dashboard isn't. Don't "fix" one to match the other without deciding which is right.

---

Series: 1. The designer · 2. The layout engine · 3. PHP and SQL