← All changes | app/Services/TicketHelper.php +450 -19 1.4.1 → 2.4.5 View file →

		@@ -2,14 +2,34 @@
2	2
3	3	namespace FluentSupport\App\Services;
4	4
5	5	use FluentSupport\App\Models\Agent;
	6	+use FluentSupport\App\Models\Conversation;
	7	+use FluentSupport\App\Models\MailBox;
6	8	use FluentSupport\App\Models\Meta;
	9	+use FluentSupport\App\Models\Product;
	10	+use FluentSupport\App\Models\TagPivot;
7	11	use FluentSupport\App\Models\Ticket;
	12	+use FluentSupport\App\Models\TicketTag;
8	13	use FluentSupport\App\Modules\PermissionManager;
	14	+use FluentSupport\App\Services\Helper;
	15	+use FluentSupport\App\Services\Tickets\AgentTicketAccess;
9	16
10	17	class TicketHelper
11	18	{
	19	+ /**
	20	+ * Above this many matches, suggested tickets are sorted in SQL. See firstIdsBy().
	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	+ */
12	32	public static function getActivity($ticketId, $currentAgentId = false)
13	33	{
14	34	$meta = Meta::where('object_type', 'ticket_meta')
15	35	->where('key', '_live_activity')
		@@ -17,9 +37,9 @@
17	37	->first();
18	38
19	39	$activities = [];
20	40	if ($meta) {
21		- $activities = maybe_unserialize($meta->value);
	41	+ $activities = Helper::safeUnserialize($meta->value);
22	42	}
23	43
24	44	foreach ($activities as $index => $activity) {
25	45	if ((time() - $activity) > 60) {
		@@ -47,19 +67,90 @@
47	67
48	68	return self::getAgentsInfoFromActivities($activities);
49	69	}
50	70
51		- public static function getAgentsInfoFromActivities($activities)
	71	+ private static function getAgentsInfoFromActivities($activities)
52	72	{
53	73	if (!$activities) {
54	74	return [];
55	75	}
56	76
57		- return Agent::select(['email', 'first_name', 'last_name'])
	77	+ return Agent::select(['id', 'email', 'first_name', 'last_name'])
58	78	->whereIn('id', array_keys($activities))
59	79	->get();
60	80	}
61	81
	82	+ /**
	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
	151	+ * @return bool
	152	+ */
62	153	public static function removeFromActivities($ticketId, $agentId)
63	154	{
64	155	$meta = Meta::where('object_type', 'ticket_meta')
65	156	->where('key', '_live_activity')
		@@ -67,9 +158,9 @@
67	158	->first();
68	159
69	160	$activities = [];
70	161	if ($meta) {
71		- $activities = maybe_unserialize($meta->value);
	162	+ $activities = Helper::safeUnserialize($meta->value);
72	163	}
73	164
74	165	if (!$activities) {
75	166	return false;
		@@ -87,33 +178,373 @@
87	178
88	179	return true;
89	180	}
90	181
	182	+ /**
	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
	187	+ * @param int $limit
	188	+ * @return mixed
	189	+ */
91	190	public static function getSuggestedTickets($agentId, $limit = 5)
92	191	{
93		- $tickets = Ticket::where('agent_id', $agentId)
	192	+ $restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
	193	+
	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)
94	201	->where('status', '!=', 'closed')
95	202	->applyFilters([
96	203	'waiting_for_reply' => 'yes'
	204	+ ]);
	205	+
	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']);
	211	+ }
	212	+
	213	+ if (!PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
	214	+ return new \FluentSupport\Framework\Support\Collection([]);
	215	+ }
	216	+
	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	+ });
	223	+
	224	+ $unassigned = static::firstIdsBy($unassignedQuery, ['id'], $limit);
	225	+
	226	+ return static::ticketsInOrder($unassigned, ['customer', 'mailbox', 'tags']);
	227	+ }
	228	+
	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	+
	299	+ public static function getWatcherTicketIds($agentId)
	300	+ {
	301	+ $mentioned = TagPivot::where('source_type', 'ticket_watcher')
	302	+ ->where('tag_id', $agentId)
	303	+ ->latest('id')
	304	+ ->get(['source_id']);
	305	+
	306	+ $ticketIds = array_column($mentioned->toArray(), 'source_id');
	307	+ return $ticketIds;
	308	+ }
	309	+
	310	+ // Up to 5 tickets the current agent is watching.
	311	+ public static function getTicketsToWatch()
	312	+ {
	313	+ $agent = Helper::getCurrentAgent();
	314	+ $restrictedBusinessBoxes = PermissionManager::getRestrictedMailboxIds();
	315	+
	316	+ $tickets = Ticket::with(
	317	+ [
	318	+ 'customer',
	319	+ 'mailbox',
	320	+ 'tags',
	321	+ 'agent'
97	322	])
98		- ->orderBy('last_customer_response', 'ASC')
99		- ->limit($limit)
100		- ->with('customer')
	323	+ ->limit(5)
	324	+ ->where(function ($q) use ($restrictedBusinessBoxes) {
	325	+ $q->whereNotIn('mailbox_id', $restrictedBusinessBoxes);
	326	+ $q->orWhereNull('mailbox_id');
	327	+ })
	328	+ ->join('fs_tag_pivot', 'fs_tag_pivot.source_id', '=', 'fs_tickets.id')
	329	+ ->where('fs_tag_pivot.source_type', '=', 'ticket_watcher')
	330	+ ->where('fs_tag_pivot.tag_id', '=', $agent->id)
	331	+ ->select(['fs_tickets.*'])
101	332	->get();
102	333
103		- if($tickets->isEmpty() && PermissionManager::currentUserCan('fst_manage_unassigned_tickets')) {
104		- $tickets = Ticket::where('status', '!=', 'closed')
105		- ->orderBy('id', 'ASC')
106		- ->where(function ($q) {
107		- $q->whereNull('agent_id');
108		- $q->orWhere('agent_id', '0');
109		- })
110		- ->with('customer')
111		- ->limit($limit)
112		- ->get();
	334	+ return $tickets;
	335	+ }
	336	+
	337	+ public static function getWatchers($watchers)
	338	+ {
	339	+ $watcherAgents = [];
	340	+
	341	+ foreach ($watchers as $watcher) {
	342	+ $watcherAgents[] = Agent::where('id', absint($watcher->tag_id))->select(['id', 'first_name', 'last_name'])->first();
113	343	}
114	344
	345	+ return $watcherAgents;
	346	+ }
115	347
116		- return $tickets;
	348	+ /** @internal Not called from core controllers — available for Pro/hook usage. */
	349	+ public static function getCarbonCopyCustomerInfo($ticketId){
	350	+ $existing = Meta::where('object_type', 'beginning_cc_info')->where('object_id', $ticketId)->first();
	351	+ if($existing){
	352	+ return Helper::safeUnserialize($existing->value, []);
	353	+ }
117	354
	355	+ return [];
	356	+ }
	357	+
	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	+
	383	+ public static function countAllTickets()
	384	+ {
	385	+ return static::getStatusCounts()['total'];
	386	+ }
	387	+
	388	+ public static function countUnassignedTickets()
	389	+ {
	390	+ return static::getStatusCounts()['unassigned'];
	391	+ }
	392	+
	393	+ public static function countClosedTickets()
	394	+ {
	395	+ return static::getStatusCounts()['closed'];
	396	+ }
	397	+
	398	+ public static function countNewTickets()
	399	+ {
	400	+ return static::getStatusCounts()['new'];
	401	+ }
	402	+
	403	+ public static function countActiveTickets()
	404	+ {
	405	+ return static::getStatusCounts()['active'];
	406	+ }
	407	+
	408	+ public static function getTicketEssentials($type = '')
	409	+ {
	410	+ $agents = Agent::select(['id', 'first_name', 'last_name'])
	411	+ ->where('person_type', 'agent')
	412	+ ->get()->toArray();
	413	+
	414	+ $data = [
	415	+ 'client_priorities' => Helper::customerTicketPriorities(),
	416	+ 'ticket_statuses_group' => Helper::ticketStatusGroups(),
	417	+ 'agents' => $agents,
	418	+ 'changeable_ticket_statuses' => Helper::changeableTicketStatuses(),
	419	+ 'admin_priorities' => Helper::adminTicketPriorities(),
	420	+ 'enable_draft_mode' => Helper::getBusinessSettings('enable_draft_mode', 'no'),
	421	+ 'max_file_upload' => Helper::getBusinessSettings('max_file_upload', 3),
	422	+ 'support_products' => Product::select(['id', 'title'])->orderedByTitle()->get(),
	423	+ 'ticket_tags' => TicketTag::select(['id', 'title'])->get()->toArray(),
	424	+ 'mailboxes' => MailBox::getAccessibleBoxes(),
	425	+ 'ticket_statuses' => Helper::ticketStatuses(),
	426	+ ];
	427	+
	428	+ if (!$type) {
	429	+ return $data;
	430	+ }
	431	+
	432	+ return $data[$type] ?? [];
	433	+ }
	434	+
	435	+ public static function getLabelSearch($agentId)
	436	+ {
	437	+ $lists = Meta::where('object_id', $agentId)
	438	+ ->where('object_type', 'search_meta')
	439	+ ->where('key', 'label_search')
	440	+ ->first();
	441	+ $unserialize = [];
	442	+ if ($lists) {
	443	+ $unserialize = Helper::safeUnserialize($lists->value);
	444	+ }
	445	+
	446	+ return $unserialize;
	447	+ }
	448	+
	449	+ public static function saveSearchLabel($agent_id, $searchData, $filterType) {
	450	+
	451	+ $agent_id = get_current_user_id();
	452	+ $existingRecord = Meta::where('object_id', $agent_id)
	453	+ ->where('object_type', 'search_meta')
	454	+ ->where('key', 'label_search')
	455	+ ->first();
	456	+
	457	+ $existingData = $existingRecord ? Helper::safeUnserialize($existingRecord->value) ?: [] : [];
	458	+
	459	+ $isUpdate = isset($searchData['id']) && array_filter($existingData, function ($item) use ($searchData) {
	460	+ return isset($item['id']) && $item['id'] == $searchData['id'];
	461	+ });
	462	+
	463	+ $updatedData = self::addOrUpdateSearchData($existingData, $searchData);
	464	+
	465	+ if ($existingRecord) {
	466	+ $existingRecord->update([
	467	+ 'value' => maybe_serialize($updatedData)
	468	+ ]);
	469	+ } else {
	470	+ Meta::insert([
	471	+ 'object_type' => 'search_meta',
	472	+ 'key' => 'label_search',
	473	+ 'object_id' => $agent_id,
	474	+ 'value' => maybe_serialize($updatedData)
	475	+ ]);
	476	+ }
	477	+
	478	+ $message = $isUpdate
	479	+ ? __('Your saved search has been updated successfully!', 'fluent-support')
	480	+ : __('Your search has been saved successfully!', 'fluent-support');
	481	+
	482	+ return [
	483	+ 'message' => $message,
	484	+ ];
	485	+ }
	486	+
	487	+ private static function addOrUpdateSearchData(array $existingData, array $searchData): array
	488	+ {
	489	+ if (isset($searchData['id'])) {
	490	+ $updated = false;
	491	+
	492	+ foreach ($existingData as &$record) {
	493	+ if (isset($record['id']) && $record['id'] == $searchData['id']) {
	494	+ $record = array_merge($record, $searchData);
	495	+ $updated = true;
	496	+ break;
	497	+ }
	498	+ }
	499	+
	500	+ if (!$updated) {
	501	+ $existingData[] = $searchData;
	502	+ }
	503	+ } else {
	504	+ $newId = self::generateNewId($existingData);
	505	+ $searchData['id'] = $newId;
	506	+ $existingData[] = $searchData;
	507	+ }
	508	+
	509	+ return $existingData;
	510	+ }
	511	+
	512	+ public static function deleteSavedSearch($search_id) {
	513	+ $agent_id = get_current_user_id();
	514	+ $existingRecord = Meta::where('object_id', $agent_id)
	515	+ ->where('object_type', 'search_meta')
	516	+ ->where('key', 'label_search')
	517	+ ->first();
	518	+
	519	+ if ($existingRecord) {
	520	+ $existingData = Helper::safeUnserialize($existingRecord->value) ?: [];
	521	+
	522	+ $updatedData = array_filter($existingData, function ($item) use ($search_id) {
	523	+ return isset($item['id']) && $item['id'] != $search_id;
	524	+ });
	525	+
	526	+ if (count($existingData) !== count($updatedData)) {
	527	+ $existingRecord->update([
	528	+ 'value' => maybe_serialize(array_values($updatedData))
	529	+ ]);
	530	+ return [
	531	+ 'message' => __('Search deleted successfully.', 'fluent-support'),
	532	+ ];
	533	+ }
	534	+
	535	+ return [
	536	+ 'message' => __('Search ID not found.', 'fluent-support'),
	537	+ ];
	538	+ }
	539	+ }
	540	+
	541	+ private static function generateNewId(array $existingData): int
	542	+ {
	543	+ if (empty($existingData)) {
	544	+ return 1;
	545	+ }
	546	+
	547	+ $maxId = max(array_column($existingData, 'id'));
	548	+ return $maxId + 1;
118	549	}
119	550	}