← All changes | app/Modules/StatModule.php +120 -74 2.4.0 → 2.4.5 View file →

		@@ -8,21 +8,18 @@
8	8	use FluentSupport\App\Models\Product;
9	9	use FluentSupport\App\Services\Tickets\AgentTicketAccess;
10	10
11	11	/**
12		- * StatModule class is responsible for getting data related to report
13		- * @package FluentSupport\App\Modules
14		- *
15		- * @version 1.0.0
	12	+ * Dashboard ticket stats, scoped to the current agent's mailbox restrictions.
16	13	*/
17	14	class StatModule
18	15	{
19	16	/**
20		- * getAgentStat method will return ticket statistics by an agent id
21		- * This method will get agent id, start date and end date as parameter and fetch the ticket information and return
	17	+ * Date range defaults to today.
	18	+ *
22	19	* @param $agentId
23		- * @param false $startDate
24		- * @param false $endDate
	20	+ * @param string|false $startDate
	21	+ * @param string|false $endDate
25	22	* @return array[]
26	23	*/
27	24	public static function getAgentStat($agentId, $startDate = false, $endDate = false)
28	25	{
		@@ -36,19 +33,16 @@
36	33	$endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate));
37	34
38	35	$access = new AgentTicketAccess();
39	36
40		- //Get list of new ticket by agent
41	37	$newTickets = $access->applyMailboxRestrictionScope(
42	38	Ticket::where('agent_id', $agentId)->where('status', 'new')
43	39	)->count();
44	40
45		- //Get list of active ticket by agent
46	41	$activeTickets = $access->applyMailboxRestrictionScope(
47	42	Ticket::where('agent_id', $agentId)->where('status', 'active')
48	43	)->count();
49	44
50		- //Get list of closed ticket by agent within a date range(default today)
51	45	$closedTickets = $access->applyMailboxRestrictionScope(
52	46	Ticket::where('agent_id', $agentId)
53	47	->where('status', 'closed')
54	48	->whereBetween('resolved_at', [$startDate, $endDate])
		@@ -53,9 +47,8 @@
53	47	->where('status', 'closed')
54	48	->whereBetween('resolved_at', [$startDate, $endDate])
55	49	)->count();
56	50
57		- //Get list of response by agent id within a date range(default today)
58	51	$responses = $access->applyMailboxRestrictionScopeViaTicket(
59	52	Conversation::where('conversation_type', 'response')
60	53	->where('person_id', $agentId)
61	54	->whereBetween('created_at', [$startDate, $endDate])
		@@ -60,9 +53,9 @@
60	53	->where('person_id', $agentId)
61	54	->whereBetween('created_at', [$startDate, $endDate])
62	55	)->count();
63	56
64		- //Count the response in tickets within a date range(default today) for agent
	57	+ // Distinct tickets the agent replied to
65	58	$interactions = $access->applyMailboxRestrictionScopeViaTicket(
66	59	Conversation::where('person_id', $agentId)
67	60	->where('conversation_type', 'response')
68	61	->whereBetween('created_at', [$startDate, $endDate])
		@@ -82,9 +75,9 @@
82	75	'title' => __('Closed Tickets', 'fluent-support'),
83	76	'count' => $closedTickets
84	77	],
85	78	'responses' => [
86		- 'title' => __('Responses', 'fluent-support'),
	79	+ 'title' => __('Total Responses', 'fluent-support'),
87	80	'count' => $responses
88	81	],
89	82	'interactions' => [
90	83	'title' => __('Interactions', 'fluent-support'),
		@@ -93,10 +86,8 @@
93	86	];
94	87	}
95	88
96	89	/**
97		- * getOverAllStats method will return the overall statistics for all tickets
98		- * This method will count the ticket number by ticket status and return the array
99	90	* @return array[]
100	91	*/
101	92	public static function getOverAllStats()
102	93	{
		@@ -136,18 +127,14 @@
136	127	];
137	128	}
138	129
139	130	/**
140		- * getTodayStats method will return a stats of today's tickets
141		- * This method will count the ticket number by ticket status and return the array
142		- * @param bool|int $agentId By default value set to false however when it gets an agent id it will fetch
143		- * the result by this id
144		- * @return array result in array format
	131	+ * @param bool|int $agentId Limit to this agent when given.
	132	+ * @return array
145	133	*/
146	134	public static function getTodayStats($agentId = false, ?AgentTicketAccess $access = null)
147	135	{
148		- $start = gmdate('Y-m-d 00:00.01');
149		- $end = gmdate('Y-m-d 23:59.59');
	136	+ list($start, $end) = static::todayRange();
150	137
151	138	$access = $access ?: new AgentTicketAccess();
152	139
153	140	$newTickets = $access->applyMailboxRestrictionScope(
		@@ -188,27 +175,18 @@
188	175	];
189	176	}
190	177
191	178	/**
192		- * getAgentOverallStats method will produce overall statistics by agent
193	179	* @param $agentId
194	180	* @return array[]
195	181	*/
196	182	public static function getAgentOverallStats($agentId)
197	183	{
198		- //Get count of response by the agent
199	184	$replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
200	185	Conversation::where('person_id', $agentId)
201	186	)->count();
202	187
203		- //Get the number of interactions/responses by agent with tickets
204		- $interactions_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
205		- Conversation::where('person_id', $agentId)
206		- ->where('conversation_type', 'response')
207		- ->groupBy('ticket_id')
208		- )->get()->count();
209		-
210		- //Get the number of tickets that are closed by this agent
	188	+ // Closed tickets assigned to the agent, whoever closed them
211	189	$total_closed = (new AgentTicketAccess())->applyMailboxRestrictionScope(
212	190	Ticket::where('agent_id', $agentId)->where('status', 'closed')
213	191	)->count();
214	192
		@@ -216,12 +194,8 @@
216	194	'replies_count' => [
217	195	'title' => __('Total Replies', 'fluent-support'),
218	196	'count' => $replies_count
219	197	],
220		- // 'interactions_count' => [
221		- // 'title' => __('Total Interactions', 'fluent-support'),
222		- // 'count' => $interactions_count
223		- // ],
224	198	'total_closed' => [
225	199	'title' => __('Total Closed', 'fluent-support'),
226	200	'count' => $total_closed
227	201	]
		@@ -228,9 +202,9 @@
228	202	];
229	203	}
230	204
231	205	/**
232		- * This `getActiveTicketsByProductStats` method will count today's tickets by product that are waiting for reply
	206	+ * Open tickets awaiting an agent reply, per product.
233	207	* @return array $result
234	208	*/
235	209	public static function getActiveTicketsByProductStats()
236	210	{
		@@ -235,14 +209,20 @@
235	209	public static function getActiveTicketsByProductStats()
236	210	{
237	211	$products = Product::all();
238	212	$result = [];
239		- $access = new AgentTicketAccess();
240	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	+
241	221	foreach ($products as $product) {
242	222	$result[$product->id] = [
243	223	'title' => $product->title,
244		- 'count' => static::countAwaitingTickets('product_id', $product->id, $access)
	224	+ 'count' => (int) ($counts[$product->id] ?? 0)
245	225	];
246	226	}
247	227
248	228	return $result;
		@@ -249,66 +229,132 @@
249	229	}
250	230
251	231
252	232	/**
253		- * This will count the number of tickets that are waiting for response also it can receive parameters
254		- * @param string $whereClause // This is the where clause that will be used in the query
255		- * @param string $whereClauseValue // This is the value of the where clause
	233	+ * Open tickets awaiting an agent reply, optionally filtered by one column.
	234	+ *
	235	+ * @param string $whereClause Column name.
	236	+ * @param string $whereClauseValue
256	237	* @return int $awatingTicketCount
257	238	*/
258	239	public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null, ?AgentTicketAccess $access = null)
259	240	{
260		- // Must be a builder: ->where() on a bare model returns a new one, losing the scope.
261		- $ticket = Ticket::query();
	241	+ $ticket = static::awaitingTicketsQuery($access ?: new AgentTicketAccess());
262	242
263	243	if ($whereClause && $whereClauseValue) {
264		- $ticket = $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue));
	244	+ $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue));
265	245	}
266	246
267		- $ticket = ($access ?: new AgentTicketAccess())->applyMailboxRestrictionScope($ticket);
	247	+ return $ticket->count();
	248	+ }
268	249
269		- $awatingTicketCount = $ticket->where('status', '!=', 'closed')
	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')
270	261	->where(function ($query) {
271	262	$query->whereColumn('last_agent_response', '<', 'last_customer_response')
272	263	->orWhereNull('last_agent_response')
273	264	->orWhere('status', 'new');
274		- })->count();
	265	+ });
	266	+ }
275	267
276		- return $awatingTicketCount;
	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'];
277	278	}
278	279
279	280	/**
280		- * This method will return a summary of today's stats of all agent
	281	+ * Today's stats for every agent with replies today or tickets waiting.
281	282	* @return array
282	283	*/
283	284	public static function getAgentTodayStats()
284	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	+
285	322	$stats = [];
286		- // One instance for the whole loop: resolving restrictions costs an agent and
287		- // meta read, and a fresh instance per agent would repeat it 44 times.
288		- $access = new AgentTicketAccess();
289	323
290		- Agent::get()->each(function ($agent) use (&$stats, $access) {
291		- $agentStat = static::getTodayStats($agent->id, $access);
292		- $waiting = static::countAwaitingTickets('agent_id', $agent->id, $access);
293		- if(!empty($agentStat['responses']['count']) || $waiting) {
294		- $stats[] = [
295		- 'agent_id' => $agent->id,
296		- 'agent_name' => $agent->full_name,
297		- 'agent_email' => $agent->email,
298		- 'avatar' => $agent->photo,
299		- 'stats' => array_merge(
300		- [
301		- 'waiting_today' => [
302		- 'title' => __('Waiting Today', 'fluent-support'),
303		- 'count' => $waiting
304		- ]
305		- ],
306		- $agentStat
307		- )
308		- ];
	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;
309	330	}
310		- });
	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	+ }
311	357
312	358	return $stats;
313	359	}
314	360