fluent-support / app / Modules / StatModule.php

StatModule.php in Fluent Support – Helpdesk & Customer Support Ticket System 2.4.5, at app/Modules/StatModule.php

362 lines 12.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\App\Modules;
4
5	use FluentSupport\App\Models\Agent;
6	use FluentSupport\App\Models\Conversation;
7	use FluentSupport\App\Models\Ticket;
8	use FluentSupport\App\Models\Product;
9	use FluentSupport\App\Services\Tickets\AgentTicketAccess;
10
11	/**
12	* Dashboard ticket stats, scoped to the current agent's mailbox restrictions.
13	*/
14	class StatModule
15	{
16	/**
17	* Date range defaults to today.
18	*
19	* @param $agentId
20	* @param string\|false $startDate
21	* @param string\|false $endDate
22	* @return array[]
23	*/
24	public static function getAgentStat($agentId, $startDate = false, $endDate = false)
25	{
26	if (!$startDate) {
27	$currentDate = current_time('mysql');
28	$startDate = $currentDate;
29	$endDate = $currentDate;
30	}
31
32	$startDate = gmdate('Y-m-d 00:00.01', strtotime($startDate));
33	$endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate));
34
35	$access = new AgentTicketAccess();
36
37	$newTickets = $access->applyMailboxRestrictionScope(
38	Ticket::where('agent_id', $agentId)->where('status', 'new')
39	)->count();
40
41	$activeTickets = $access->applyMailboxRestrictionScope(
42	Ticket::where('agent_id', $agentId)->where('status', 'active')
43	)->count();
44
45	$closedTickets = $access->applyMailboxRestrictionScope(
46	Ticket::where('agent_id', $agentId)
47	->where('status', 'closed')
48	->whereBetween('resolved_at', [$startDate, $endDate])
49	)->count();
50
51	$responses = $access->applyMailboxRestrictionScopeViaTicket(
52	Conversation::where('conversation_type', 'response')
53	->where('person_id', $agentId)
54	->whereBetween('created_at', [$startDate, $endDate])
55	)->count();
56
57	// Distinct tickets the agent replied to
58	$interactions = $access->applyMailboxRestrictionScopeViaTicket(
59	Conversation::where('person_id', $agentId)
60	->where('conversation_type', 'response')
61	->whereBetween('created_at', [$startDate, $endDate])
62	->groupBy('ticket_id')
63	)->get()->count();
64
65	return [
66	'new_tickets' => [
67	'title' => __('New Tickets', 'fluent-support'),
68	'count' => $newTickets
69	],
70	'active_tickets' => [
71	'title' => __('Active Tickets', 'fluent-support'),
72	'count' => $activeTickets
73	],
74	'closed_tickets' => [
75	'title' => __('Closed Tickets', 'fluent-support'),
76	'count' => $closedTickets
77	],
78	'responses' => [
79	'title' => __('Total Responses', 'fluent-support'),
80	'count' => $responses
81	],
82	'interactions' => [
83	'title' => __('Interactions', 'fluent-support'),
84	'count' => $interactions
85	]
86	];
87	}
88
89	/**
90	* @return array[]
91	*/
92	public static function getOverAllStats()
93	{
94	$access = new AgentTicketAccess();
95
96	// Same mailbox boundary as the ticket list; these totals are agent-facing.
97	$newTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'new'))
98	->count();
99
100	$activeTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'active'))
101	->count();
102
103	$closedTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'closed'))
104	->count();
105
106	$responses = $access->applyMailboxRestrictionScopeViaTicket(
107	Conversation::where('conversation_type', 'response')
108	)->count();
109
110	return [
111	'new_tickets' => [
112	'title' => __('New Tickets', 'fluent-support'),
113	'count' => $newTickets
114	],
115	'active_tickets' => [
116	'title' => __('Active Tickets', 'fluent-support'),
117	'count' => $activeTickets
118	],
119	'closed_tickets' => [
120	'title' => __('Closed Tickets', 'fluent-support'),
121	'count' => $closedTickets
122	],
123	'responses' => [
124	'title' => __('Responses', 'fluent-support'),
125	'count' => $responses
126	]
127	];
128	}
129
130	/**
131	* @param bool\|int $agentId Limit to this agent when given.
132	* @return array
133	*/
134	public static function getTodayStats($agentId = false, ?AgentTicketAccess $access = null)
135	{
136	list($start, $end) = static::todayRange();
137
138	$access = $access ?: new AgentTicketAccess();
139
140	$newTickets = $access->applyMailboxRestrictionScope(
141	Ticket::whereBetween('created_at', [$start, $end])
142	);
143
144	$closedTickets = $access->applyMailboxRestrictionScope(
145	Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])
146	);
147
148	$responses = $access->applyMailboxRestrictionScopeViaTicket(
149	Conversation::where('conversation_type', 'response')
150	->whereHas('person', function ($q) {
151	$q->where('person_type', 'agent');
152	})
153	->whereBetween('created_at', [$start, $end])
154	);
155
156	if ($agentId) {
157	$newTickets->where('agent_id', $agentId);
158	$closedTickets->where('agent_id', $agentId);
159	$responses->where('person_id', $agentId);
160	}
161
162	return [
163	'new_tickets' => [
164	'title' => __('New Tickets', 'fluent-support'),
165	'count' => $newTickets->count()
166	],
167	'closed_tickets' => [
168	'title' => __('Closed Tickets', 'fluent-support'),
169	'count' => $closedTickets->count()
170	],
171	'responses' => [
172	'title' => __('Responses', 'fluent-support'),
173	'count' => $responses->count()
174	]
175	];
176	}
177
178	/**
179	* @param $agentId
180	* @return array[]
181	*/
182	public static function getAgentOverallStats($agentId)
183	{
184	$replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
185	Conversation::where('person_id', $agentId)
186	)->count();
187
188	// Closed tickets assigned to the agent, whoever closed them
189	$total_closed = (new AgentTicketAccess())->applyMailboxRestrictionScope(
190	Ticket::where('agent_id', $agentId)->where('status', 'closed')
191	)->count();
192
193	return [
194	'replies_count' => [
195	'title' => __('Total Replies', 'fluent-support'),
196	'count' => $replies_count
197	],
198	'total_closed' => [
199	'title' => __('Total Closed', 'fluent-support'),
200	'count' => $total_closed
201	]
202	];
203	}
204
205	/**
206	* Open tickets awaiting an agent reply, per product.
207	* @return array $result
208	*/
209	public static function getActiveTicketsByProductStats()
210	{
211	$products = Product::all();
212	$result = [];
213
214	$counts = static::awaitingTicketsQuery(new AgentTicketAccess())
215	->whereIn('product_id', $products->pluck('id')->all())
216	->groupBy('product_id')
217	->selectRaw('product_id, COUNT(*) AS total')
218	->pluck('total', 'product_id')
219	->all();
220
221	foreach ($products as $product) {
222	$result[$product->id] = [
223	'title' => $product->title,
224	'count' => (int) ($counts[$product->id] ?? 0)
225	];
226	}
227
228	return $result;
229	}
230
231
232	/**
233	* Open tickets awaiting an agent reply, optionally filtered by one column.
234	*
235	* @param string $whereClause Column name.
236	* @param string $whereClauseValue
237	* @return int $awatingTicketCount
238	*/
239	public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null, ?AgentTicketAccess $access = null)
240	{
241	$ticket = static::awaitingTicketsQuery($access ?: new AgentTicketAccess());
242
243	if ($whereClause && $whereClauseValue) {
244	$ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue));
245	}
246
247	return $ticket->count();
248	}
249
250	/**
251	* Open tickets awaiting an agent reply, within the viewer's mailboxes.
252	*
253	* @param AgentTicketAccess $access
254	* @return \FluentSupport\Framework\Database\Orm\Builder
255	*/
256	protected static function awaitingTicketsQuery(AgentTicketAccess $access)
257	{
258	// Must be a builder: ->where() on a bare model returns a new one, losing the scope.
259	return $access->applyMailboxRestrictionScope(Ticket::query())
260	->where('status', '!=', 'closed')
261	->where(function ($query) {
262	$query->whereColumn('last_agent_response', '<', 'last_customer_response')
263	->orWhereNull('last_agent_response')
264	->orWhere('status', 'new');
265	});
266	}
267
268	/**
269	* Today in site time, like the stored dates.
270	*
271	* @return string[] [start, end] as "Y-m-d H:i:s".
272	*/
273	protected static function todayRange()
274	{
275	$today = current_time('Y-m-d');
276
277	return [$today . ' 00:00:00', $today . ' 23:59:59'];
278	}
279
280	/**
281	* Today's stats for every agent with replies today or tickets waiting.
282	* @return array
283	*/
284	public static function getAgentTodayStats()
285	{
286	// Share one instance so restrictions are resolved once.
287	$access = new AgentTicketAccess();
288	list($start, $end) = static::todayRange();
289
290	$agents = Agent::get();
291	$agentIds = $agents->pluck('id')->all();
292
293	if (!$agentIds) {
294	return [];
295	}
296
297	// One grouped query per figure for all agents.
298	$countBy = function ($query, $column) {
299	return $query->groupBy($column)
300	->selectRaw($column . ' AS grouped_id, COUNT(*) AS total')
301	->pluck('total', 'grouped_id')
302	->all();
303	};
304
305	$waiting = $countBy(static::awaitingTicketsQuery($access)->whereIn('agent_id', $agentIds), 'agent_id');
306
307	$newTickets = $countBy($access->applyMailboxRestrictionScope(
308	Ticket::whereBetween('created_at', [$start, $end])->whereIn('agent_id', $agentIds)
309	), 'agent_id');
310
311	$closedTickets = $countBy($access->applyMailboxRestrictionScope(
312	Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])->whereIn('agent_id', $agentIds)
313	), 'agent_id');
314
315	// person_id among the agent ids is the "written by an agent" check.
316	$responses = $countBy($access->applyMailboxRestrictionScopeViaTicket(
317	Conversation::where('conversation_type', 'response')
318	->whereIn('person_id', $agentIds)
319	->whereBetween('created_at', [$start, $end])
320	), 'person_id');
321
322	$stats = [];
323
324	foreach ($agents as $agent) {
325	$waitingCount = (int) ($waiting[$agent->id] ?? 0);
326	$responseCount = (int) ($responses[$agent->id] ?? 0);
327
328	if (!$responseCount && !$waitingCount) {
329	continue;
330	}
331
332	$stats[] = [
333	'agent_id' => $agent->id,
334	'agent_name' => $agent->full_name,
335	'agent_email' => $agent->email,
336	'avatar' => $agent->photo,
337	'stats' => [
338	'waiting_today' => [
339	'title' => __('Waiting Today', 'fluent-support'),
340	'count' => $waitingCount
341	],
342	'new_tickets' => [
343	'title' => __('New Tickets', 'fluent-support'),
344	'count' => (int) ($newTickets[$agent->id] ?? 0)
345	],
346	'closed_tickets' => [
347	'title' => __('Closed Tickets', 'fluent-support'),
348	'count' => (int) ($closedTickets[$agent->id] ?? 0)
349	],
350	'responses' => [
351	'title' => __('Responses', 'fluent-support'),
352	'count' => $responseCount
353	]
354	]
355	];
356	}
357
358	return $stats;
359	}
360
361	}
362