← All changes | app/Services/TicketHelper.php +235 -62 1.10.2 → 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();
		@@ -60,23 +67,88 @@
60	67
61	68	return self::getAgentsInfoFromActivities($activities);
62	69	}
63	70
64		- public static function getAgentsInfoFromActivities($activities)
	71	+ private static function getAgentsInfoFromActivities($activities)
65	72	{
66	73	if (!$activities) {
67	74	return [];
68	75	}
69	76
70		- return Agent::select(['email', 'first_name', 'last_name'])
	77	+ return Agent::select(['id', 'email', 'first_name', 'last_name'])
71	78	->whereIn('id', array_keys($activities))
72	79	->get();
73	80	}
74	81
75	82	/**
76		- * removeFromActivities method will remove the old live activity by agent and ticket id
77		- * @param $ticketId
78		- * @param $agentId
	83	+ * Batch version of getActivity() for a list page: one query for metas, one for agents.
	84	+ *
	85	+ * @param \FluentSupport\Framework\Support\Collection $tickets Collection of Ticket models
	86	+ * @return void
	87	+ */
	88	+ public static function loadBatchLiveActivities($tickets)
	89	+ {
	90	+ $activeTicketIds = $tickets->where('status', '!=', 'closed')->pluck('id')->toArray();
	91	+
	92	+ if (!$activeTicketIds) {
	93	+ foreach ($tickets as $ticket) {
	94	+ $ticket->live_activity = [];
	95	+ }
	96	+ return;
	97	+ }
	98	+
	99	+ $activityMetas = Meta::where('object_type', 'ticket_meta')
	100	+ ->where('key', '_live_activity')
	101	+ ->whereIn('object_id', $activeTicketIds)
	102	+ ->get()
	103	+ ->keyBy('object_id');
	104	+
	105	+ $allAgentIds = [];
	106	+ $ticketActivities = [];
	107	+
	108	+ foreach ($activityMetas as $ticketId => $meta) {
	109	+ $activities = Helper::safeUnserialize($meta->value);
	110	+ if (!is_array($activities)) {
	111	+ continue;
	112	+ }
	113	+
	114	+ $activities = array_filter($activities, function ($time) {
	115	+ return (time() - $time) <= 60;
	116	+ });
	117	+
	118	+ $ticketActivities[$ticketId] = $activities;
	119	+ $allAgentIds = array_merge($allAgentIds, array_keys($activities));
	120	+ }
	121	+
	122	+ $allAgentIds = array_unique(array_filter($allAgentIds));
	123	+ $agents = $allAgentIds
	124	+ ? Agent::select(['id', 'email', 'first_name', 'last_name'])
	125	+ ->whereIn('id', $allAgentIds)
	126	+ ->get()
	127	+ ->keyBy('id')
	128	+ : [];
	129	+
	130	+ $activitiesByTicket = [];
	131	+ foreach ($ticketActivities as $ticketId => $activities) {
	132	+ $ticketAgents = [];
	133	+ foreach (array_keys($activities) as $agentId) {
	134	+ if (isset($agents[$agentId])) {
	135	+ $ticketAgents[] = $agents[$agentId];
	136	+ }
	137	+ }
	138	+ $activitiesByTicket[$ticketId] = $ticketAgents;
	139	+ }
	140	+
	141	+ foreach ($tickets as $ticket) {
	142	+ $ticket->live_activity = $activitiesByTicket[$ticket->id] ?? [];
	143	+ }
	144	+ }
	145	+
	146	+ /**
	147	+ * Drop the agent's live activity on the ticket, plus any expired entries.
	148	+ *
	149	+ * @param int $ticketId
	150	+ * @param int $agentId
79	151	* @return bool
80	152	*/
81	153	public static function removeFromActivities($ticketId, $agentId)
82	154	{
		@@ -107,51 +179,124 @@
107	179	return true;
108	180	}
109	181
110	182	/**
111		- * getSuggestedTickets method will return the list of Suggested tickets
112		- * This method will get the agent id as parameter and fetch ticket information that are waiting to reply or unassigned
113		- * @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
114	187	* @param int $limit
115	188	* @return mixed
116	189	*/
117	190	public static function getSuggestedTickets($agentId, $limit = 5)
118	191	{
119		- $restrictedBusinessBoxes = PermissionManager::currentUserRestrictedBusinessBoxes();
	192	+ $restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
120	193
121		- //Get lis of tickets which are waiting for reply
122		- $tickets = Ticket::where('agent_id', $agentId)
123		- ->whereNotIn('mailbox_id', $restrictedBusinessBoxes)
	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)
124	201	->where('status', '!=', 'closed')
125	202	->applyFilters([
126	203	'waiting_for_reply' => 'yes'
127		- ])
128		- ->oldest('last_customer_response')
129		- ->limit($limit)
130		- ->with('customer')
131		- ->get();
	204	+ ]);
132	205
133		- //If no ticket is available for reply and logged-in user has permission to manage unassigned tickets
134		- if($tickets->isEmpty() && PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
135		- //Get the ticket list which status is not closed and agent id is null or 0
136		- $tickets = Ticket::where('status', '!=', 'closed')
137		- ->oldest('id')
138		- ->whereNotIn('mailbox_id', $restrictedBusinessBoxes)
139		- ->where(function ($q) {
140		- $q->whereNull('agent_id');
141		- $q->orWhere('agent_id', '0');
142		- })
143		- ->with('customer')
144		- ->limit($limit)
145		- ->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']);
146	211	}
147	212
	213	+ if (!PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
	214	+ return new \FluentSupport\Framework\Support\Collection([]);
	215	+ }
148	216
149		- 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	+ });
150	223
	224	+ $unassigned = static::firstIdsBy($unassignedQuery, ['id'], $limit);
	225	+
	226	+ return static::ticketsInOrder($unassigned, ['customer', 'mailbox', 'tags']);
151	227	}
152	228
153		- // 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	+
154	299	public static function getWatcherTicketIds($agentId)
155	300	{
156	301	$mentioned = TagPivot::where('source_type', 'ticket_watcher')
157	302	->where('tag_id', $agentId)
		@@ -161,17 +306,26 @@
161	306	$ticketIds = array_column($mentioned->toArray(), 'source_id');
162	307	return $ticketIds;
163	308	}
164	309
165		- // This method will return all tagged/mentioned/watcher tickets of logged in agent
	310	+ // Up to 5 tickets the current agent is watching.
166	311	public static function getTicketsToWatch()
167	312	{
168	313	$agent = Helper::getCurrentAgent();
169		- $restrictedBusinessBoxes = PermissionManager::currentUserRestrictedBusinessBoxes();
	314	+ $restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
170	315
171		- $tickets = Ticket::with('customer')
	316	+ $tickets = Ticket::with(
	317	+ [
	318	+ 'customer',
	319	+ 'mailbox',
	320	+ 'tags',
	321	+ 'agent'
	322	+ ])
172	323	->limit(5)
173		- ->whereNotIn('mailbox_id', $restrictedBusinessBoxes)
	324	+ ->where(function ($q) use ($restrictedBusinessBoxes) {
	325	+ $q->whereNotIn('mailbox_id', $restrictedBusinessBoxes);
	326	+ $q->orWhereNull('mailbox_id');
	327	+ })
174	328	->join('fs_tag_pivot', 'fs_tag_pivot.source_id', '=', 'fs_tickets.id')
175	329	->where('fs_tag_pivot.source_type', '=', 'ticket_watcher')
176	330	->where('fs_tag_pivot.tag_id', '=', $agent->id)
177	331	->select(['fs_tickets.*'])
		@@ -179,9 +333,8 @@
179	333
180	334	return $tickets;
181	335	}
182	336
183		- // This method will return all ticket watcher inside a ticket
184	337	public static function getWatchers($watchers)
185	338	{
186	339	$watcherAgents = [];
187	340
		@@ -191,8 +344,9 @@
191	344
192	345	return $watcherAgents;
193	346	}
194	347
	348	+ /** @internal Not called from core controllers — available for Pro/hook usage. */
195	349	public static function getCarbonCopyCustomerInfo($ticketId){
196	350	$existing = Meta::where('object_type', 'beginning_cc_info')->where('object_id', $ticketId)->first();
197	351	if($existing){
198	352	return Helper::safeUnserialize($existing->value, []);
		@@ -200,34 +354,56 @@
200	354
201	355	return [];
202	356	}
203	357
204		- // 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	+
205	383	public static function countAllTickets()
206	384	{
207		- return (new Ticket())->count();
	385	+ return static::getStatusCounts()['total'];
208	386	}
209		- // This method will count all un-assigned tickets
	387	+
210	388	public static function countUnassignedTickets()
211	389	{
212		- return Ticket::whereNull('agent_id')->count();
	390	+ return static::getStatusCounts()['unassigned'];
213	391	}
214		- // This method will count all closed tickets
	392	+
215	393	public static function countClosedTickets()
216	394	{
217		- return Ticket::where('status', 'closed')->count();
	395	+ return static::getStatusCounts()['closed'];
218	396	}
219	397
220		- // This method will count all New tickets
221	398	public static function countNewTickets()
222	399	{
223		- return Ticket::where('status', 'new')->count();
	400	+ return static::getStatusCounts()['new'];
224	401	}
225	402
226		- // This method will count all Active tickets
227	403	public static function countActiveTickets()
228	404	{
229		- return Ticket::where('status', 'active')->count();
	405	+ return static::getStatusCounts()['active'];
230	406	}
231	407
232	408	public static function getTicketEssentials($type = '')
233	409	{
		@@ -242,15 +418,19 @@
242	418	'changeable_ticket_statuses' => Helper::changeableTicketStatuses(),
243	419	'admin_priorities' => Helper::adminTicketPriorities(),
244	420	'enable_draft_mode' => Helper::getBusinessSettings('enable_draft_mode', 'no'),
245	421	'max_file_upload' => Helper::getBusinessSettings('max_file_upload', 3),
246		- 'support_products' => Product::select(['id', 'title'])->get(),
	422	+ 'support_products' => Product::select(['id', 'title'])->orderedByTitle()->get(),
247	423	'ticket_tags' => TicketTag::select(['id', 'title'])->get()->toArray(),
248		- 'mailboxes' => MailBox::select(['id', 'name', 'settings'])->get(),
	424	+ 'mailboxes' => MailBox::getAccessibleBoxes(),
249	425	'ticket_statuses' => Helper::ticketStatuses(),
250	426	];
251	427
252		- return $type ? $data[$type] : $data;
	428	+ if (!$type) {
	429	+ return $data;
	430	+ }
	431	+
	432	+ return $data[$type] ?? [];
253	433	}
254	434
255	435	public static function getLabelSearch($agentId)
256	436	{
		@@ -273,20 +453,16 @@
273	453	->where('object_type', 'search_meta')
274	454	->where('key', 'label_search')
275	455	->first();
276	456
277		- // If record exists, unserialize the data or initialize an empty array
278	457	$existingData = $existingRecord ? Helper::safeUnserialize($existingRecord->value) ?: [] : [];
279	458
280		- // Check if it's an update or new entry
281	459	$isUpdate = isset($searchData['id']) && array_filter($existingData, function ($item) use ($searchData) {
282	460	return isset($item['id']) && $item['id'] == $searchData['id'];
283	461	});
284	462
285		- // Handle adding or updating
286	463	$updatedData = self::addOrUpdateSearchData($existingData, $searchData);
287	464
288		- // Save the updated data back to the database
289	465	if ($existingRecord) {
290	466	$existingRecord->update([
291	467	'value' => maybe_serialize($updatedData)
292	468	]);
		@@ -312,9 +488,8 @@
312	488	{
313	489	if (isset($searchData['id'])) {
314	490	$updated = false;
315	491
316		- // Update the existing record with the matching ID
317	492	foreach ($existingData as &$record) {
318	493	if (isset($record['id']) && $record['id'] == $searchData['id']) {
319	494	$record = array_merge($record, $searchData);
320	495	$updated = true;
		@@ -321,14 +496,12 @@
321	496	break;
322	497	}
323	498	}
324	499
325		- // If no matching record found, append the new data
326	500	if (!$updated) {
327	501	$existingData[] = $searchData;
328	502	}
329	503	} else {
330		- // If no ID provided, generate a new one and add the data
331	504	$newId = self::generateNewId($existingData);
332	505	$searchData['id'] = $newId;
333	506	$existingData[] = $searchData;
334	507	}