Fair Case Distribution Not Including Agents with Zero Cases #5338

New issue

Open

opened 2026-02-20 16:59:51 -05:00 by deekerman · 0 comments

deekerman commented

2026-02-20 16:59:51 -05:00

Owner

Originally created by @Aradhyagarg on GitHub (Oct 4, 2025).

Issue

When using the Least Busy distribution method for inbound email case assignment in SuiteCRM 7.14.3, agents with zero active cases were being skipped in workload calculations.

This led to unfair case distribution, where some agents never received new cases, while others continued to get overloaded.

Expected Behavior
Agents with zero open cases should be included in the workload calculation.
Closed cases are still counted in the workload calculation (as per current fix).

Possible Fix

Updated the query in getLeastBusyCounts() to ensure:
Agents with zero active cases are included.
Deleted cases are excluded (c.deleted = 0).
Uses a LEFT JOIN to ensure fair count across all assignable users.

Before (Existing Code):
$res = $db->query("SELECT assigned_user_id, COUNT() AS c
FROM cases
WHERE assigned_user_id IN ({$idIn})
AND deleted = 0
GROUP BY assigned_user_id
ORDER BY COUNT()");

After (Fixed Code):
$res = $db->query("SELECT u.id AS assigned_user_id, COUNT(c.id) AS c
FROM users u
LEFT JOIN cases c
ON c.assigned_user_id = u.id
AND c.deleted = 0
WHERE u.id IN ({$idIn})
GROUP BY u.id
ORDER BY c ASC");

This ensures
Agents with zero cases are still counted (LEFT JOIN handles that).
Deleted cases are ignored.
Distribution becomes fair and balanced among all agents.

Steps to Reproduce the Issue

Configure an inbound email with
Create Case from Email: Yes
Distribution Method: Least Busy
Security Group: Add a Security Group
Ensure one agent has zero open cases and others have some active cases.
Send an inbound email that triggers case creation.
Observe that the agent with 0 open cases now receives the new case correctly.

Context

This bug caused unfair case distribution: some agents never received any tickets while others handled most of the workload.
Fixing it ensures equal opportunity and workload balance among all active agents. The issue was discovered during inbound email automation testing and fixed locally by adjusting the query logic.

Version

SuiteCRM 7.14.3

What browser are you currently using?

Chrome

Browser Version

Chrome Version 129.0.6668.100 (64-bit)

Environment Information

PHP 8.2, MySQL 8.0, (XAMPP local setup)

Operating System and Version

Windows 11

Suggested Labels:
bug inbound-email case-module enhancement fixed ready-for-review

Originally created by @Aradhyagarg on GitHub (Oct 4, 2025). ### Issue When using the Least Busy distribution method for inbound email case assignment in SuiteCRM 7.14.3, agents with zero active cases were being skipped in workload calculations. This led to unfair case distribution, where some agents never received new cases, while others continued to get overloaded. Expected Behavior Agents with zero open cases should be included in the workload calculation. Closed cases are still counted in the workload calculation (as per current fix). ### Possible Fix Updated the query in getLeastBusyCounts() to ensure: Agents with zero active cases are included. Deleted cases are excluded (c.deleted = 0). Uses a LEFT JOIN to ensure fair count across all assignable users. Before (Existing Code): $res = $db->query("SELECT assigned_user_id, COUNT(*) AS c FROM cases WHERE assigned_user_id IN ({$idIn}) AND deleted = 0 GROUP BY assigned_user_id ORDER BY COUNT(*)"); After (Fixed Code): $res = $db->query("SELECT u.id AS assigned_user_id, COUNT(c.id) AS c FROM users u LEFT JOIN cases c ON c.assigned_user_id = u.id AND c.deleted = 0 WHERE u.id IN ({$idIn}) GROUP BY u.id ORDER BY c ASC"); This ensures Agents with zero cases are still counted (LEFT JOIN handles that). Deleted cases are ignored. Distribution becomes fair and balanced among all agents. ### Steps to Reproduce the Issue ```bash Configure an inbound email with Create Case from Email: Yes Distribution Method: Least Busy Security Group: Add a Security Group Ensure one agent has zero open cases and others have some active cases. Send an inbound email that triggers case creation. Observe that the agent with 0 open cases now receives the new case correctly. ``` ### Context This bug caused unfair case distribution: some agents never received any tickets while others handled most of the workload. Fixing it ensures equal opportunity and workload balance among all active agents. The issue was discovered during inbound email automation testing and fixed locally by adjusting the query logic. ### Version SuiteCRM 7.14.3 ### What browser are you currently using? Chrome ### Browser Version Chrome Version 129.0.6668.100 (64-bit) ### Environment Information PHP 8.2, MySQL 8.0, (XAMPP local setup) ### Operating System and Version Windows 11 **Suggested Labels:** `bug` `inbound-email` `case-module` `enhancement` `fixed` `ready-for-review`