← All changes | app/Services/TicketHelper.php +155 -74 2.4.0 → 2.4.5 View file →

		@@ -11,20 +11,27 @@
11	11	use FluentSupport\App\Models\Ticket;
12	12	use FluentSupport\App\Models\TicketTag;
13	13	use FluentSupport\App\Modules\PermissionManager;
14	14	use FluentSupport\App\Services\Helper;
	15	+use FluentSupport\App\Services\Tickets\AgentTicketAccess;
15	16
16	17	class TicketHelper
17	18	{
18	19	/**
19		- * getActivity method will return the activity in a ticket by agent
20		- * @param $ticketId
21		- * @param false $currentAgentId
22		- * @return array
	20	+ * Above this many matches, suggested tickets are sorted in SQL. See firstIdsBy().
23	21	*/
	22	+ const SORT_IN_PHP_MAX = 1000;
	23	+
	24	+ /**
	25	+ * Agents seen on the ticket in the last 60 seconds. Passing $currentAgentId also
	26	+ * records that agent as active now.
	27	+ *
	28	+ * @param int $ticketId
	29	+ * @param int|false $currentAgentId
	30	+ * @return array|\FluentSupport\Framework\Support\Collection
	31	+ */
24	32	public static function getActivity($ticketId, $currentAgentId = false)
25	33	{
26		- //Get the ticket meta information
27	34	$meta = Meta::where('object_type', 'ticket_meta')
28	35	->where('key', '_live_activity')
29	36	->where('object_id', $ticketId)
30	37	->first();
		@@ -72,12 +79,11 @@
72	79	->get();
73	80	}
74	81
75	82	/**
76		- * Batch-load live activity for a collection of tickets.
77		- * Uses 2 queries total: one for activity metas, one for all agents.
	83	+ * Batch version of getActivity() for a list page: one query for metas, one for agents.
78	84	*
79		- * @param \Iterable $tickets Collection of Ticket models
	85	+ * @param \FluentSupport\Framework\Support\Collection $tickets Collection of Ticket models
80	86	* @return void
81	87	*/
82	88	public static function loadBatchLiveActivities($tickets)
83	89	{
		@@ -137,11 +143,12 @@
137	143	}
138	144	}
139	145
140	146	/**
141		- * removeFromActivities method will remove the old live activity by agent and ticket id
142		- * @param $ticketId
143		- * @param $agentId
	147	+ * Drop the agent's live activity on the ticket, plus any expired entries.
	148	+ *
	149	+ * @param int $ticketId
	150	+ * @param int $agentId
144	151	* @return bool
145	152	*/
146	153	public static function removeFromActivities($ticketId, $agentId)
147	154	{
		@@ -172,11 +179,12 @@
172	179	return true;
173	180	}
174	181
175	182	/**
176		- * getSuggestedTickets method will return the list of Suggested tickets
177		- * This method will get the agent id as parameter and fetch ticket information that are waiting to reply or unassigned
178		- * @param $agentId
	183	+ * The agent's tickets waiting for a reply, or unassigned open tickets when there are none
	184	+ * and the user can manage unassigned tickets.
	185	+ *
	186	+ * @param int $agentId
179	187	* @param int $limit
180	188	* @return mixed
181	189	*/
182	190	public static function getSuggestedTickets($agentId, $limit = 5)
		@@ -182,58 +190,113 @@
182	190	public static function getSuggestedTickets($agentId, $limit = 5)
183	191	{
184	192	$restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
185	193
186		- //Get lis of tickets which are waiting for reply
187		- $tickets = Ticket::where('agent_id', $agentId)
188		- ->where(function ($q) use ($restrictedBusinessBoxes) {
189		- $q->whereNotIn('mailbox_id', $restrictedBusinessBoxes);
190		- $q->orWhereNull('mailbox_id');
191		- })
	194	+ $inAllowedMailbox = function ($q) use ($restrictedBusinessBoxes) {
	195	+ $q->whereNotIn('mailbox_id', $restrictedBusinessBoxes);
	196	+ $q->orWhereNull('mailbox_id');
	197	+ };
	198	+
	199	+ $waitingQuery = Ticket::where('agent_id', $agentId)
	200	+ ->where($inAllowedMailbox)
192	201	->where('status', '!=', 'closed')
193	202	->applyFilters([
194	203	'waiting_for_reply' => 'yes'
195		- ])
196		- ->oldest('last_customer_response')
197		- ->limit($limit)
198		- ->with(
199		- [
200		- 'customer',
201		- 'mailbox',
202		- 'tags',
203		- 'agent'
204		- ])
205		- ->get();
	204	+ ]);
206	205
207		- //If no ticket is available for reply and logged-in user has permission to manage unassigned tickets
208		- if($tickets->isEmpty() && PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
209		- //Get the ticket list which status is not closed and agent id is null or 0
210		- $tickets = Ticket::where('status', '!=', 'closed')
211		- ->oldest('id')
212		- ->where(function ($q) use ($restrictedBusinessBoxes) {
213		- $q->whereNotIn('mailbox_id', $restrictedBusinessBoxes);
214		- $q->orWhereNull('mailbox_id');
215		- })
216		- ->where(function ($q) {
217		- $q->whereNull('agent_id');
218		- $q->orWhere('agent_id', '0');
219		- })
220		- ->with(
221		- [
222		- 'customer',
223		- 'mailbox',
224		- 'tags',
225		- ])
226		- ->limit($limit)
227		- ->get();
	206	+ // NULL sorts first, as with ORDER BY ... ASC.
	207	+ $waiting = static::firstIdsBy($waitingQuery, ['last_customer_response', 'id'], $limit);
	208	+
	209	+ if ($waiting) {
	210	+ return static::ticketsInOrder($waiting, ['customer', 'mailbox', 'tags', 'agent']);
228	211	}
229	212
	213	+ if (!PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
	214	+ return new \FluentSupport\Framework\Support\Collection([]);
	215	+ }
230	216
231		- return $tickets;
	217	+ $unassignedQuery = Ticket::where('status', '!=', 'closed')
	218	+ ->where($inAllowedMailbox)
	219	+ ->where(function ($q) {
	220	+ $q->whereNull('agent_id');
	221	+ $q->orWhere('agent_id', '0');
	222	+ });
232	223
	224	+ $unassigned = static::firstIdsBy($unassignedQuery, ['id'], $limit);
	225	+
	226	+ return static::ticketsInOrder($unassigned, ['customer', 'mailbox', 'tags']);
233	227	}
234	228
235		- // This method will return all tagged/mentioned/watcher ticket's ids for filtering
	229	+ /**
	230	+ * Ids of the first $limit rows of $query in ascending $columns order.
	231	+ *
	232	+ * When few rows match, ORDER BY ... LIMIT makes MySQL walk the whole table
	233	+ * in sort order, so up to SORT_IN_PHP_MAX matches are sorted here. When many
	234	+ * match, that walk ends quickly and loading them all would not, so SQL sorts.
	235	+ *
	236	+ * @param \FluentSupport\Framework\Database\Orm\Builder $query
	237	+ * @param string[] $columns
	238	+ * @param int $limit
	239	+ * @return int[]
	240	+ */
	241	+ protected static function firstIdsBy($query, array $columns, $limit)
	242	+ {
	243	+ $matches = (clone $query)->count();
	244	+
	245	+ if (!$matches) {
	246	+ return [];
	247	+ }
	248	+
	249	+ if ($matches > static::SORT_IN_PHP_MAX) {
	250	+ foreach ($columns as $column) {
	251	+ $query->orderBy($column, 'ASC');
	252	+ }
	253	+
	254	+ return array_map('intval', $query->limit($limit)->pluck('id')->all());
	255	+ }
	256	+
	257	+ $rows = $query->get(array_unique(array_merge(['id'], $columns)))->all();
	258	+
	259	+ usort($rows, function ($a, $b) use ($columns) {
	260	+ foreach ($columns as $column) {
	261	+ $left = $column === 'id' ? (int) $a->id : (string) $a->{$column};
	262	+ $right = $column === 'id' ? (int) $b->id : (string) $b->{$column};
	263	+ if ($left != $right) {
	264	+ return $left < $right ? -1 : 1;
	265	+ }
	266	+ }
	267	+ return 0;
	268	+ });
	269	+
	270	+ return array_map(function ($row) {
	271	+ return (int) $row->id;
	272	+ }, array_slice($rows, 0, $limit));
	273	+ }
	274	+
	275	+ /**
	276	+ * Tickets by id with relations, in the order of $ids.
	277	+ *
	278	+ * @param int[] $ids
	279	+ * @param array $with
	280	+ * @return \FluentSupport\Framework\Support\Collection
	281	+ */
	282	+ protected static function ticketsInOrder(array $ids, array $with)
	283	+ {
	284	+ if (!$ids) {
	285	+ return new \FluentSupport\Framework\Support\Collection([]);
	286	+ }
	287	+
	288	+ $position = array_flip(array_map('intval', array_values($ids)));
	289	+
	290	+ return Ticket::whereIn('id', $ids)
	291	+ ->with($with)
	292	+ ->get()
	293	+ ->sortBy(function ($ticket) use ($position) {
	294	+ return $position[(int) $ticket->id];
	295	+ })
	296	+ ->values();
	297	+ }
	298	+
236	299	public static function getWatcherTicketIds($agentId)
237	300	{
238	301	$mentioned = TagPivot::where('source_type', 'ticket_watcher')
239	302	->where('tag_id', $agentId)
		@@ -243,9 +306,9 @@
243	306	$ticketIds = array_column($mentioned->toArray(), 'source_id');
244	307	return $ticketIds;
245	308	}
246	309
247		- // This method will return all tagged/mentioned/watcher tickets of logged in agent
	310	+ // Up to 5 tickets the current agent is watching.
248	311	public static function getTicketsToWatch()
249	312	{
250	313	$agent = Helper::getCurrentAgent();
251	314	$restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
		@@ -270,9 +333,8 @@
270	333
271	334	return $tickets;
272	335	}
273	336
274		- // This method will return all ticket watcher inside a ticket
275	337	public static function getWatchers($watchers)
276	338	{
277	339	$watcherAgents = [];
278	340
		@@ -292,34 +354,56 @@
292	354
293	355	return [];
294	356	}
295	357
296		- // This method will count total tickets
	358	+ /**
	359	+ * Ticket counts by status for the current user, without the mailboxes
	360	+ * they are restricted from.
	361	+ *
	362	+ * @return array{total:int, new:int, active:int, closed:int, unassigned:int}
	363	+ */
	364	+ public static function getStatusCounts()
	365	+ {
	366	+ $access = new AgentTicketAccess();
	367	+
	368	+ $byStatus = $access->applyMailboxRestrictionScope(
	369	+ Ticket::query()->select('status')->selectRaw('COUNT(*) AS total')->groupBy('status')
	370	+ )->get()->pluck('total', 'status')->toArray();
	371	+
	372	+ $unassigned = $access->applyMailboxRestrictionScope(Ticket::query()->whereNull('agent_id'))->count();
	373	+
	374	+ return [
	375	+ 'total' => (int) array_sum($byStatus),
	376	+ 'new' => (int) ($byStatus['new'] ?? 0),
	377	+ 'active' => (int) ($byStatus['active'] ?? 0),
	378	+ 'closed' => (int) ($byStatus['closed'] ?? 0),
	379	+ 'unassigned' => (int) $unassigned,
	380	+ ];
	381	+ }
	382	+
297	383	public static function countAllTickets()
298	384	{
299		- return (new Ticket())->count();
	385	+ return static::getStatusCounts()['total'];
300	386	}
301		- // This method will count all un-assigned tickets
	387	+
302	388	public static function countUnassignedTickets()
303	389	{
304		- return Ticket::whereNull('agent_id')->count();
	390	+ return static::getStatusCounts()['unassigned'];
305	391	}
306		- // This method will count all closed tickets
	392	+
307	393	public static function countClosedTickets()
308	394	{
309		- return Ticket::where('status', 'closed')->count();
	395	+ return static::getStatusCounts()['closed'];
310	396	}
311	397
312		- // This method will count all New tickets
313	398	public static function countNewTickets()
314	399	{
315		- return Ticket::where('status', 'new')->count();
	400	+ return static::getStatusCounts()['new'];
316	401	}
317	402
318		- // This method will count all Active tickets
319	403	public static function countActiveTickets()
320	404	{
321		- return Ticket::where('status', 'active')->count();
	405	+ return static::getStatusCounts()['active'];
322	406	}
323	407
324	408	public static function getTicketEssentials($type = '')
325	409	{
		@@ -340,9 +424,13 @@
340	424	'mailboxes' => MailBox::getAccessibleBoxes(),
341	425	'ticket_statuses' => Helper::ticketStatuses(),
342	426	];
343	427
344		- return $type ? $data[$type] : $data;
	428	+ if (!$type) {
	429	+ return $data;
	430	+ }
	431	+
	432	+ return $data[$type] ?? [];
345	433	}
346	434
347	435	public static function getLabelSearch($agentId)
348	436	{
		@@ -365,20 +453,16 @@
365	453	->where('object_type', 'search_meta')
366	454	->where('key', 'label_search')
367	455	->first();
368	456
369		- // If record exists, unserialize the data or initialize an empty array
370	457	$existingData = $existingRecord ? Helper::safeUnserialize($existingRecord->value) ?: [] : [];
371	458
372		- // Check if it's an update or new entry
373	459	$isUpdate = isset($searchData['id']) && array_filter($existingData, function ($item) use ($searchData) {
374	460	return isset($item['id']) && $item['id'] == $searchData['id'];
375	461	});
376	462
377		- // Handle adding or updating
378	463	$updatedData = self::addOrUpdateSearchData($existingData, $searchData);
379	464
380		- // Save the updated data back to the database
381	465	if ($existingRecord) {
382	466	$existingRecord->update([
383	467	'value' => maybe_serialize($updatedData)
384	468	]);
		@@ -404,9 +488,8 @@
404	488	{
405	489	if (isset($searchData['id'])) {
406	490	$updated = false;
407	491
408		- // Update the existing record with the matching ID
409	492	foreach ($existingData as &$record) {
410	493	if (isset($record['id']) && $record['id'] == $searchData['id']) {
411	494	$record = array_merge($record, $searchData);
412	495	$updated = true;
		@@ -413,14 +496,12 @@
413	496	break;
414	497	}
415	498	}
416	499
417		- // If no matching record found, append the new data
418	500	if (!$updated) {
419	501	$existingData[] = $searchData;
420	502	}
421	503	} else {
422		- // If no ID provided, generate a new one and add the data
423	504	$newId = self::generateNewId($existingData);
424	505	$searchData['id'] = $newId;
425	506	$existingData[] = $searchData;
426	507	}