CSAT Company Scope and Filters Developer Guide

CSAT company scope and filters - Developer Guide

Shipped in 2.10.0 · GitHub #157 · Changelog #2056, #2057 · User-facing page: Tickets § CSAT

How the CSAT analytics page (tickets/csat/) works out which survey rows to count, how each filter reaches the SQL, and why the rating filter only touches the list. Two changes shipped together:

• #2056 (fix) - the page now follows the company switcher in the header. Before, all four queries read ticket_csat_responses with no company predicate at all, so an analyst restricted to one company saw every company's averages, comments and ticket subjects.
• #2057 (feature) - From/To dates, a rating band, analyst and customer filters, 25-a-page paging with the customer on each response, and a full-screen Filters sheet on a phone.

There is no API endpoint involved. The page is one server-rendered PHP file that runs its queries before printing the HTML, so nearly everything below lives in tickets/csat/index.php. Read The traps before adding a tile or a widget to it.

---

Files

🗄️ schema · 🔒 company scope · 🖥️ page (queries + markup) · 📱 mobile · 🌐 strings · ❓ help

🎨	File	What it does
🖥️	tickets/csat/index.php	The whole feature. Reads the filters from $_GET, builds one $scope predicate, runs the KPI, distribution, per-analyst, count, list and two drop-down queries, prints the page. Also holds the 6-line csatShowFilters() script for the phone sheet
🔒	includes/tenancy.php	ticketTenantFilter() (the ticket list's own company predicate) and allAccessibleTenantsFilter() (the "All companies" branch). Unchanged - only called from the page now
🗄️	database/freeitsm.sql, includes/db_verify_schema.php	ticket_csat_responses. No schema change - the table has no tenant_id, which is why the scope goes through a join to tickets
🗄️	includes/csat.php	sendCsatSurvey() - writes analyst_id onto the row at send time. Unchanged, but it decides what the Analyst filter means
📱	assets/css/mobile.css	Section 39e under LAYER 39: hides the form on a phone, turns it into a full-screen sheet, adds the sticky Filters button and its badge
🌐	lang/en/tickets.php	21 new keys under tickets.csat.* (filters, filter_from, rating_positive, rating_filter_note, from_customer, showing …) and 2 under tickets.help.csat.*
❓	tickets/help.php	Two new paragraphs in the CSAT section; the company one only prints when isMultiTenant()

The commit for #2057 also touches ~180 other pages - that is only the mobile.css cache-buster going from ?v=152 to ?v=153.

---

1. The company scope

One predicate, taken from the ticket list

// tickets/csat/index.php
[$ttSql, $ttParams] = ticketTenantFilter($conn, (int)$_SESSION['analyst_id'], 't');

ticket_csat_responses has no company column - a survey belongs to whichever company its ticket belongs to. So every query joins the ticket and appends the ticket list's own predicate to the WHERE:

FROM ticket_csat_responses cr
INNER JOIN tickets t ON t.id = cr.ticket_id
WHERE … <$ttSql>

ticketTenantFilter() returns one of four things:

Situation	What $ttSql is
Single-company install	'' - no filter at all, the page behaves exactly as before
One company picked, and it is Default	AND (t.tenant_id = ? OR t.tenant_id IS NULL) - Default owns unrouted tickets
One company picked, not Default	AND t.tenant_id = ?
All companies	AND (t.tenant_id IN (?,?,…) [OR t.tenant_id IS NULL]) - every company this analyst may see, never "no filter"; AND 1 = 0 if they may see none

The active company comes from getActiveTenantId(), which re-checks analystCanAccessTenant() against the session value - so a stale session pointing at a company the analyst has since lost falls back rather than leaking.

Why ticketTenantFilter() and not activeTenantReadFilter()? The rows are scoped through tickets, and ticketTenantFilter() is the function every ticket read already uses, with the All-companies widening built in. Using the same function as the inbox means the CSAT page and the ticket list cannot disagree about which tickets an analyst may see.

Why the join changes nothing on a single-company install

INNER JOIN tickets would drop a CSAT row whose ticket has gone. There are none: fk_ticket_csat_ticket is ON DELETE CASCADE, in the fresh-install dump and added by api/system/db_verify.php on grown installs. With $ttSql empty, the join is a no-op.

Why aggregates are where a company leaks most easily

A list leaks visibly - a ticket subject from the wrong company is something an analyst notices and reports. An average does not. Before #2056, an analyst restricted to one company saw an average rating, a response rate and a per-analyst leaderboard built from every company's surveys, and nothing on screen said so. The numbers simply looked plausible. That is why the fix put the predicate on all four queries at once rather than on the responses list where the leak was obvious, and why the comment block at the top of the filters says:

// 🔴 The company scope above is applied to every query, the drop-down lists
// included - an id typed into the URL for someone in another company simply
// matches nothing.

---

2. Reading the filters

Every filter is a plain GET parameter, so a filtered view is bookmarkable. Each one is validated before it gets anywhere near SQL:

$ratingBands = ['positive' => [4, 5], 'neutral' => [3, 3], 'negative' => [1, 2]];
$ratingF   = isset($ratingBands[$_GET['rating'] ?? '']) ? $_GET['rating'] : '';
$analystF  = max(0, (int)($_GET['analyst'] ?? 0));
$customerF = max(0, (int)($_GET['customer'] ?? 0));
$pageNo    = max(1, (int)($_GET['page'] ?? 1));
$perPage   = 25;

• rating is a whitelist key - anything else becomes "all ratings".
• analyst, customer, page are cast to int; 0 means "not set".
• days (the 7/30/90/365 buttons) is clamped to 1-365, as before.
• from / to must match YYYY-MM-DD and pass checkdate(); otherwise they are ignored. A From later than To is swapped rather than rejected.

Dates: the analyst's calendar days, stored as UTC

The date columns are UTC. An analyst in Sydney picking "1 October" means midnight-to-midnight in Sydney, so the page converts with the analyst's own zone (Tz::current()):

$fromUtc = $fromIn !== ''
    ? (new DateTime($fromIn . ' 00:00:00', $zone))->setTimezone($utc)->format('Y-m-d H:i:s')
    : '1970-01-01 00:00:00';
$toUtc = $toIn !== ''   // up to the END of the To day
    ? (new DateTime($toIn . ' 00:00:00', $zone))->modify('+1 day')->setTimezone($utc)->format('Y-m-d H:i:s')
    : '9999-12-31 00:00:00';

The upper bound is midnight at the start of the day after To, compared with <, so the whole To day is included. Either end may be blank; the sentinels stand in. Without a custom range, the preset becomes $fromUtc = gmdate(…, time() - $days * 86400) with the same open upper bound.

---

3. How the filters reach the SQL

One closure builds the predicate shared by every figure:

// The window on one of the two dates, plus company, analyst and customer.
$scope = function (string $dateCol) use ($ttSql, $ttParams, $fromUtc, $toUtc, $analystF, $customerF): array {
    $sql = " AND cr.$dateCol >= ? AND cr.$dateCol < ?" . $ttSql;
    $params = array_merge([$fromUtc, $toUtc], $ttParams);
    if ($analystF) { $sql .= ' AND cr.analyst_id = ?'; $params[] = $analystF; }
    if ($customerF) { $sql .= ' AND t.user_id = ?'; $params[] = $customerF; }
    return [$sql, $params];
};

It is called twice, on two different date columns:

[$sSql, $sParams] = $scope('sent_datetime');       // the KPI tiles
[$rSql, $rParams] = $scope('responded_datetime');  // distribution, per-analyst, list

The KPI tiles are by sent date so the response rate compares "surveys sent in the period" with "how many of those were answered". Everything else is by response date, as it was before #157. The two can legitimately differ: a survey sent on the 30th and answered on the 2nd is in one window's rate and the next window's distribution.

That difference is also why the distribution percentages changed. They used to divide by the headline count (by sent date); they now divide by the bars' own total, so the five shares add up to 100:

$rated = array_sum($dist);
… round($dist[$i] / $rated * 100) …

Which filter applies to which panel

Filter	KPI tiles	Distribution	Per-analyst	Responses list	Drop-down lists
Company (switcher)	✅	✅	✅	✅	✅
Period (buttons or From/To)	✅ sent date	✅ response date	✅ response date	✅ response date	❌
Analyst	✅	✅	✅	✅	-
Customer	✅	✅	✅	✅	-
Rating band	❌	❌	❌	✅	-

Why the rating band is list-only

Applied to the headline figures, a rating filter destroys them: average only the 4s and 5s and the average is always between 4 and 5; the distribution would show three empty bars. The useful question - "show me the unhappy customers" - is a question about the list. So the band is appended to the list's SQL only:

$listSql    = $rSql;
$listParams = $rParams;
if ($ratingF !== '') {
    $listSql   .= ' AND cr.rating BETWEEN ? AND ?';
    $listParams = array_merge($listParams, $ratingBands[$ratingF]);
}

…and a note above the list says so, so nobody reads a 4.6 average beside a list of 1s as a contradiction (tickets.csat.rating_filter_note: "Showing {rating} ratings only. The figures above include every rating.").

The drop-down lists ignore the period

// The two drop-downs: only people with a CSAT response in THIS company scope
// (not the period, so a choice does not vanish when the dates change).
SELECT DISTINCT a.id, a.full_name AS name
FROM ticket_csat_responses cr
INNER JOIN tickets t ON t.id = cr.ticket_id
INNER JOIN analysts a ON a.id = cr.analyst_id
WHERE 1=1 <$ttSql>

The customer list is the same shape, joining users u ON u.id = t.user_id and showing COALESCE(NULLIF(u.preferred_name, ''), NULLIF(u.display_name, ''), u.email). Both use $ttSql only, so the lists never offer someone from a company the analyst cannot see. Note that there is no rating IS NOT NULL here: anyone with a survey sent appears, answered or not.

---

4. Paging

The list counts first, then clamps the page:

$countStmt = $conn->prepare(
    "SELECT COUNT(*) FROM ticket_csat_responses cr
     INNER JOIN tickets t ON t.id = cr.ticket_id
     WHERE cr.rating IS NOT NULL" . $listSql
);
…
$pages  = max(1, (int)ceil($listTotal / $perPage));
$pageNo = min($pageNo, $pages);
$offset = ($pageNo - 1) * $perPage;

The count uses exactly $listSql / $listParams - the same predicate as the list - so "26-50 of 73" cannot disagree with the rows shown. page=999 lands on the last page. The list orders by cr.responded_datetime DESC, cr.id DESC; the id tiebreaker keeps paging stable when two responses share a second. LIMIT and OFFSET are concatenated into the SQL as (int) casts rather than bound.

Each row now also carries its customer (customer_name, same COALESCE as above, via LEFT JOIN users u ON u.id = t.user_id), printed as "from {name}" under the subject.

Links keep the other filters

One helper builds every link on the page from the current query string:

/** This page's URL with some parameters changed (null removes one). */
$csatUrl = function (array $change): string {
    $keep = array_intersect_key($_GET, array_flip(['days', 'from', 'to', 'rating', 'analyst', 'customer', 'page']));
    $q = array_filter(array_merge($keep, $change), function ($v) {
        return $v !== null && $v !== '' && $v !== 0 && $v !== '0';
    });
    return '?' . http_build_query($q);
};

• Previous / Next change only page.
• 7 / 30 / 90 / 365 pass ['days' => $d, 'from' => null, 'to' => null, 'page' => null]: a preset replaces a custom range and returns to page 1, but keeps rating, analyst and customer.
• Apply submits the form, which has no page field - so applying a filter always starts at page 1.
• Clear is a plain link to ?days=N: it drops every filter but keeps the preset.

---

5. The phone Filters sheet

Five fields in a row under the heading would fill half a phone screen before a single figure. So on a phone the form is hidden and a button fixed to the bottom of the screen opens it full-screen. It is the same form - no second copy of the fields.

The markup has two phone-only pieces, hidden on desktop by the page's own CSS (.csat-filters-head, .csat-filter-fab { display: none; }):

<form class="csat-filters" id="csatFilters" method="get">
    <div class="csat-filters-head">
        <span><?= htmlspecialchars(t('tickets.csat.filters')) ?></span>
        <button type="button" class="ms-close" onclick="csatShowFilters(false)">…</button>
    </div>
    …
</form>
<button type="button" class="csat-filter-fab" onclick="csatShowFilters(true)">
    <svg …funnel…></svg>
    <span><?= htmlspecialchars(t('tickets.csat.filters')) ?></span>
    <?php if ($activeCount): ?><span class="cf-badge"><?= $activeCount ?></span><?php endif; ?>
</button>

function csatShowFilters(open) {
    document.body.classList.toggle('csat-filters-open', open);
}

mobile.css section 39e does the rest, all under body[data-mobile-page="tickets-csat"]:

• .csat-filters { display: none; } - and with .csat-filters-open on body, position: fixed; inset: 0; z-index: 1500 with the fields stacked in a column.
• .csat-filters-head is position: sticky; top: 0, so Close stays reachable while scrolling the sheet. Close reuses LAYER 7's .ms-close.
• Inputs and selects get font-size: 16px and min-height: 44px - iOS zooms the page into any focused input smaller than 16px.
• .csat-filter-fab is fixed to the bottom (z-index: 1400, min-height: 56px, padding includes env(safe-area-inset-bottom)), and .csat-page gets padding-bottom: 84px so the last response is not hidden behind it.

The badge counts the filters in force, so it is obvious the figures are filtered while the form is out of sight:

$activeCount = ($customRange ? 1 : 0) + ($ratingF !== '' ? 1 : 0) + ($analystF ? 1 : 0) + ($customerF ? 1 : 0);

A From/To pair counts as one. The company is not counted - it is shown by the header switcher.

---

The traps

• A new tile, chart or widget that forgets the scope. Any new query on ticket_csat_responses must join tickets t and include $ttSql (or go through $scope()). Forgetting it produces a figure that looks fine and quietly averages every company - exactly the #2056 bug. On a single-company install $ttSql is empty, so a missing predicate is invisible in single-company testing; test with at least two companies.
• "All companies" is never "no filter". If you ever write your own predicate here instead of calling ticketTenantFilter(), an empty string in the All-companies case reads as "multi-tenancy is off" and hands everyone everything. See the 🔴 comment on allAccessibleTenantsFilter().
• Analyst attribution is denormalised at send time. cr.analyst_id is written by sendCsatSurvey(): the analyst who closed the ticket (auto mode, $actorId in includes/services/tickets.php) or who pressed Request feedback (api/tickets/request_csat.php). It is not the ticket's current assignee. The Analyst filter and the per-analyst table both read cr.analyst_id, so a ticket reassigned after the survey still counts for the original analyst - deliberate. Do not "fix" it by joining the ticket's current assignee instead. A deleted analyst becomes NULL (ON DELETE SET NULL) and shows as Unassigned.
• The customer is NOT denormalised. The Customer filter and the "from {name}" line read t.user_id - the ticket's requester now. If the requester is changed after the survey, the response moves to the new requester. And the survey link is a token in an email, so whoever answered it is not recorded at all.
• Two date columns. KPIs use sent_datetime, everything else responded_datetime. A new figure has to pick one on purpose; mixing them is what made the old percentages not add up to 100.
• The rating band must stay off the headline figures. If you add a figure that should respect it, put it beside the list and use $listSql, not $rSql.
• The count and the list share one predicate. Add a condition to $listSql, never to just one of the two statements, or the pager will lie.
• Company scope only. The inbox also narrows by team → department (api/tickets/get_ticket_counts.php); this page did not before #157 and still does not. An analyst limited by team sees all CSAT in their company. That is outside what #157 asked for - if it is ever wanted, it is a new predicate on every query, same as the company one.
• Strings owed. The 23 new English keys are in lang/en/tickets.php only; other locales fall back to English until translated.

---

How it was verified

There is no automated test for this page in the repo (tests/ has nothing for CSAT analytics), and I found no seeding script in the repo for the demo tickets used to check it. What the commits record:

• #2056 (a729c109): checked on the real page with 16 is_demo tickets across 4 companies - each company showed only its own figures, All companies showed all 16, and an analyst restricted to one company saw only that company, including under All companies and with a stale session pointing at another company.
• #2057 (be4269ef): checked on the real page against 40 is_demo tickets across 4 companies, 19 checks covering each filter, paging, swapped dates, company scope and junk input.

To re-check after a change, the cases worth repeating are:

1. Two or more companies, each with rated surveys: switch company and confirm KPIs, bars, leaderboard and list all change together.
2. An analyst restricted to one company: under All companies, the totals equal that one company's.
3. Put another company's analyst or customer id in the URL (?analyst=…): everything should read zero / empty, not that person's figures.
4. ?rating=negative: KPIs and bars unchanged, list only 1-2, the note visible.
5. ?from=2026-10-05&to=2026-10-01 (swapped), ?from=2026-02-30 (invalid), ?rating=x, ?page=999 - swapped, ignored, ignored, last page.
6. A single-company install: figures identical to before the change.
7. On a phone width: Filters button at the bottom, badge count correct, sheet opens and closes, last response not covered.

---

Related

• Tickets § CSAT - what the analyst sees
• Multi-Tenancy Developer Guide
• All-Companies Ticket View - Developer Guide - where "All companies" and allAccessibleTenantsFilter() come from
• Mobile-friendly Tickets - LAYERS 37-39, including the CSAT page
• Date and Time Formats - Developer Guide - UTC at rest, the analyst's zone on screen