User Activity report: "Tickets Assigned" column filters by ticket creation date, not assignment date #49

New Issue

2026-08-31T21:36:17-04:00

jared commented

2026-08-31 21:36:17 -04:00

Severity: Medium

index.php (~lines 385-390), the ta subquery for the User Activity report: SELECT assigned_to, COUNT(*) ... FROM tickets WHERE DATE(created_at) BETWEEN ? AND ? GROUP BY assigned_to.

Impact: A ticket created outside the selected date range but assigned to a user within it won't count toward "Tickets Assigned"; conversely a ticket created inside the range but assigned/reassigned later still counts as if the assignment happened in-range. The column is filtered by the wrong date field for what it claims to measure, producing a misleading metric on /admin/user-activity.

Fix: If assignment date isn't tracked separately, either add an assigned_at timestamp column, derive it from audit_log assignment events, or relabel the column to clarify it's actually measuring tickets created-and-assigned-to in the range.

**Severity:** Medium `index.php` (~lines 385-390), the `ta` subquery for the User Activity report: `SELECT assigned_to, COUNT(*) ... FROM tickets WHERE DATE(created_at) BETWEEN ? AND ? GROUP BY assigned_to`. **Impact:** A ticket created outside the selected date range but assigned to a user within it won't count toward "Tickets Assigned"; conversely a ticket created inside the range but assigned/reassigned later still counts as if the assignment happened in-range. The column is filtered by the wrong date field for what it claims to measure, producing a misleading metric on `/admin/user-activity`. **Fix:** If assignment date isn't tracked separately, either add an `assigned_at` timestamp column, derive it from `audit_log` assignment events, or relabel the column to clarify it's actually measuring tickets created-and-assigned-to in the range.

jared added the priority/medium reliability labels 2026-09-08 10:15:45 -04:00

jared referenced this issue from a commit

2026-09-11 22:17:40 -04:00

Fix User Activity report's Tickets Assigned column to use assignment date (#49)

jared referenced this issue from a commit

2026-09-11 22:19:59 -04:00

Merge development into main: user-activity fix + attachment thumbnails (#49, #98)

jared commented

2026-09-11 22:20:13 -04:00

Fixed in commit 80169de (merged to main in 786674a).

The 'Tickets Assigned' column filtered by tickets.created_at, so a ticket created outside the selected date range but assigned to a user within it never counted, while one created in-range but assigned/reassigned later counted as if the assignment happened in-range.

tickets has no assigned_at column, so the count is now derived from audit_log's 'assign' events (already logged by both the single-ticket and bulk-assign paths), filtered by the event's own created_at. COUNT(DISTINCT entity_id) so a ticket reassigned more than once to the same user within the range still counts once.

Verified against real MariaDB: seeded one ticket created outside the test range but assigned inside it, and one created inside the range but assigned outside it. The old query counted the wrong one; the new query correctly flips to count the one actually assigned within the range.

Fixed in commit 80169de (merged to main in 786674a). The 'Tickets Assigned' column filtered by tickets.created_at, so a ticket created outside the selected date range but assigned to a user within it never counted, while one created in-range but assigned/reassigned later counted as if the assignment happened in-range. tickets has no assigned_at column, so the count is now derived from audit_log's 'assign' events (already logged by both the single-ticket and bulk-assign paths), filtered by the event's own created_at. COUNT(DISTINCT entity_id) so a ticket reassigned more than once to the same user within the range still counts once. Verified against real MariaDB: seeded one ticket created outside the test range but assigned inside it, and one created inside the range but assigned outside it. The old query counted the wrong one; the new query correctly flips to count the one actually assigned within the range.

jared closed this issue

2026-09-11 22:20:13 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: LotusGuild/tinker_tickets#49