Overview
I built an internal tool for an IT support team that tracks its tickets in GLPI: a Laravel 12 application that reads the GLPI MySQL database without ever writing to it. It turns that data into a dashboard: ticket count, time spent, resolution time, rankings by category, requester and entity, monthly trend. Below it, a paginated, sortable table lists the matching tickets, with a CSV export and a page for each entity, category or requester. A second module collects customer ratings when a ticket is closed.
The figures and the list come from the same query
Everything that reads tickets goes through TicketQuery::base(), fed by a FilterCriteria object. The metrics in StatsService and the table start from that same base: a figure cannot describe a different set than the list it summarises.
Several GLPI pitfalls are handled there. A global scope on the Ticket model excludes the trash, so that no future query forgets it. The join on categories is a LEFT JOIN, otherwise tickets without a category would vanish from the counters. To filter an entity and its sub-entities, I use completename, which already holds the hierarchical path: a LIKE on the prefix, once % and _ are escaped, fetches the whole branch without a recursive query. The requester filter goes through a WHERE EXISTS, so that a ticket with two requesters is not listed twice. Sorting only accepts the columns in the TRIABLES whitelist, since its value comes from the URL.
In the browser, Alpine.js calls /donnees again 250 ms after the last filter change and writes the filters into the URL, which makes a filtered view shareable.
Filtered on the Client A entity and the Incident type, the table includes tickets from its sub-entities (Siège, branches). Clicking a row expands its description.
Resolution time counted in business hours
A ticket opened on Friday at 5 pm and resolved on Monday at 9 am did not take 64 hours to handle. The BusinessHours class counts the working time between two dates from configured time slots, excluding weekends, French public holidays and exceptional closures. GLPI stores no holidays: I compute them, Easter included, with the Meeus/Jones/Butcher algorithm written by hand so as not to depend on the PHP calendar extension. Full days are counted in whole weeks: even for a ticket left open for a year, at most six days are walked through one by one, then the holidays are subtracted:
$jours = (int) $d1->diffInDays($d2) + 1;
$semaines = intdiv($jours, 7);
$reste = $jours % 7;
$total = $semaines * array_sum($this->secondesParJour);
for ($i = 0; $i < $reste; $i++) {
$jour = $d1->addDays($semaines * 7 + $i);
$total += $this->secondesParJour[$jour->dayOfWeekIso] ?? 0;
}
foreach ($this->feriesEntre($d1, $d2) as $ferie) {
$total -= $this->secondesParJour[$ferie->dayOfWeekIso] ?? 0;
}I kept this calculation in PHP: written in SQL, it would be unreadable and untestable. The trade-off: resolution time cannot be sorted. The unit tests cover the lunch break, the weekend, fixed and moving holidays, and a ticket open for a year. The dashboard shows the median next to the mean, because a single forgotten ticket is enough to skew the mean. A column also gives the resolution time minus the wait for the customer.
The dashboard on a test GLPI database: the median resolution time in business hours (3h33) sits far below the mean (14h14), which a few forgotten tickets pull up.
A production database that can only be read
The MySQL account only has the SELECT privilege, and GlpiModel adds a second layer: save, update, delete and forceDelete throw an explicit exception there. Accounts and ratings live in a separate application database. GLPI stores its labels HTML-encoded, sometimes twice: GlpiText::propre decodes them. If GLPI does not respond, the page says so instead of returning a 500 error.
Collecting a rating from an email
The notification template I wrote for GLPI shows five stars, each one a link that carries the rating, because an email cannot hold a reliable form. The rating is recorded as soon as the rating page is reached, then the page offers to confirm it with a comment. Some email security filters visit links before the recipient does: the avis table therefore keeps a confirme flag, and a different rating only replaces an existing one after confirmation. AvisService merges these ratings with those from GLPI's built-in survey.
The vote link carries a 4/5 rating for a ticket already rated 1/5: the page asks for confirmation before replacing the earlier rating.
Everything else goes through Laravel Fortify. Self-registration is disabled, accounts are created with an Artisan command or an internal page, with a password of at least 12 characters. The ExigerDoubleFacteur middleware redirects to the TOTP setup until the second factor is confirmed, and a disabled account gets the same message as a wrong password. Feature tests check this boundary.
What I would rework
The vote starts from an email generated by GLPI, which limits what the link can carry. I would rather generate these links from the application itself, to better control what reaches the rating page. The entity filter should also be stricter: its prefix can catch a neighbouring entity whose name starts the same way, so it should test the exact name or the prefix followed by the > separator. Finally, the resolution time without waiting remains approximate, since GLPI only stores the total waiting time, in calendar time.