← All changes | app/Modules/Reporting/Reporting.php +235 -120 2.3.2 → 2.4.0 View file →

		@@ -10,9 +10,11 @@
10	10	use FluentSupport\App\Models\Person;
11	11	use FluentSupport\App\Models\Product;
12	12	use FluentSupport\App\Models\TagPivot;
13	13	use FluentSupport\App\Models\Ticket;
	14	+use FluentSupport\App\Modules\PermissionManager;
14	15	use FluentSupport\App\Services\Helper;
	16	+use FluentSupport\App\Services\Tickets\AgentTicketAccess;
15	17	use FluentSupport\Framework\Database\Orm\Builder;
16	18	use FluentSupport\Framework\Support\Arr;
17	19	use FluentSupport\Framework\Support\DateTime;
18	20
		@@ -51,8 +53,11 @@
51	53	->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
52	54	->groupBy($groupBy)
53	55	->oldest($orderBy);
54	56
	57	+ // Bound first: a caller-supplied mailbox_id below is not proof of access.
	58	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	59	+
55	60	//If filter by product or agent or status selected
56	61	if ($filters) {
57	62	if (!empty($filters['statuses'])) {
58	63	$query->whereIn('status', $filters['statuses']);
		@@ -107,8 +112,10 @@
107	112	->where($filterColumn, '>', 0)
108	113	->groupBy($groupBy)
109	114	->oldest($orderBy);
110	115
	116	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	117	+
111	118	//If filter by product or agent is selected
112	119	if ($filters) {
113	120	if (!empty($filters['product_id'])) {
114	121	$query->where('product_id', $filters['product_id']);
		@@ -148,17 +155,17 @@
148	155	);
149	156
150	157	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
151	158
152		- $query = Conversation::query()
153		- ->select($this->prepareSelect($frequency))
154		- ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
155		- ->where('conversation_type', 'response')
156		- ->whereHas('person', function ($q) {
157		- $q->where('person_type', 'agent');
158		- })
159		- ->groupBy($groupBy)
160		- ->oldest($orderBy);
	159	+ $query = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
	160	+ Conversation::query()
	161	+ ->select($this->prepareSelect($frequency))
	162	+ ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
	163	+ ->where('conversation_type', 'response')
	164	+ ->whereIn('person_id', Agent::getAgentIds())
	165	+ ->groupBy($groupBy)
	166	+ ->oldest($orderBy)
	167	+ );
161	168
162	169	if ($filters) {
163	170	if (!empty($filters['person_id'])) {
164	171	$query->where('person_id', $filters['person_id']);
		@@ -187,9 +194,9 @@
187	194	);
188	195
189	196	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
190	197
191		- $filterColumn = $type."_id";
	198	+ $filterColumn = (!empty($type)) ? $type.'_id' : 'id';
192	199
193	200	$query = $this->db()->table('fs_tickets')
194	201	->select($this->prepareSelect($frequency,'created_at','response_count'))
195	202	->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
		@@ -197,8 +204,10 @@
197	204	->where($filterColumn, '>', 0)
198	205	->groupBy($groupBy)
199	206	->oldest($orderBy);
200	207
	208	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	209	+
201	210	if ($filters) {
202	211	if (!empty($filters['product_id'])) {
203	212	$query->where('product_id', $filters['product_id']);
204	213	}
		@@ -212,8 +221,23 @@
212	221
213	222	return $this->getResult($period, $items);
214	223	}
215	224
	225	+ // One grouped query instead of one per agent; shared by the two summaries.
	226	+ private function interactionCountsByAgent($from, $to, $access)
	227	+ {
	228	+ return $access->applyMailboxRestrictionScopeViaTicket(
	229	+ Conversation::select([
	230	+ $this->db()->raw('person_id as agent_id'),
	231	+ $this->db()->raw('COUNT(DISTINCT ticket_id) as count')
	232	+ ])
	233	+ ->whereIn('person_id', Agent::getAgentIds())
	234	+ ->whereBetween('created_at', [$from, $to])
	235	+ ->where('conversation_type', 'response')
	236	+ ->groupBy('person_id')
	237	+ )->get();
	238	+ }
	239	+
216	240	/**
217	241	* agentSummary method will prepare ticket summary with responses by agent
218	242	* @param false $from
219	243	* @param false $to
		@@ -233,8 +257,10 @@
233	257	$from .= ' 00:00:00';
234	258	$to .= ' 23:59:59';
235	259	$reports = [];
236	260
	261	+ $access = new AgentTicketAccess();
	262	+
237	263	//Get tickets statistics that are closed
238	264	$resolves = $this->db()->table('fs_tickets')
239	265	->select([
240	266	$this->db()->raw('COUNT(id) AS count'),
		@@ -241,11 +267,12 @@
241	267	'agent_id',
242	268	])
243	269	->groupBy('agent_id')
244	270	->where('status', 'closed')
245		- ->whereBetween('resolved_at', [$from, $to])
246		- ->get();
	271	+ ->whereBetween('resolved_at', [$from, $to]);
247	272
	273	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
	274	+
248	275	$reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id');
249	276
250	277	//get statistics for all except closed ticket
251	278	$openTickets = $this->db()->table('fs_tickets')
		@@ -253,35 +280,32 @@
253	280	$this->db()->raw('COUNT(id) AS count'),
254	281	'agent_id'
255	282	])
256	283	->groupBy('agent_id')
257		- ->where('status', '!=', 'closed')
258		- ->get();
	284	+ ->where('status', '!=', 'closed');
259	285
	286	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
	287	+
260	288	$reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id');
261	289	//Get response by agent
262		- $responses = Conversation::select([
263		- $this->db()->raw('COUNT(id) AS count'),
264		- $this->db()->raw('person_id as agent_id'),
265		- $this->db()->raw('created_at')
266		- ])
267		- ->whereHas('person', function ($q) {
268		- $q->where('person_type', '=', 'agent');
269		- })
270		- ->whereBetween('created_at', [$from, $to])
271		- ->where('conversation_type', 'response')
272		- ->groupBy('agent_id')
273		- ->get();
	290	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	291	+ Conversation::select([
	292	+ $this->db()->raw('COUNT(id) AS count'),
	293	+ $this->db()->raw('person_id as agent_id'),
	294	+ $this->db()->raw('created_at')
	295	+ ])
	296	+ ->whereIn('person_id', Agent::getAgentIds())
	297	+ ->whereBetween('created_at', [$from, $to])
	298	+ ->where('conversation_type', 'response')
	299	+ ->groupBy('agent_id')
	300	+ )->get();
274	301
275	302	$reports = $this->pushReportData('responses', $responses, $reports, 'agent_id');
276	303	//Get interactions/responses by individual agents
277		- foreach ($responses as $response) {
278		- $reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id)
279		- ->where('conversation_type', 'response')
280		- ->whereBetween('created_at', [$from, $to])
281		- ->groupBy('ticket_id')
282		- ->get()
283		- ->count();
	304	+ foreach ($this->interactionCountsByAgent($from, $to, $access) as $row) {
	305	+ if (isset($reports[$row->agent_id])) {
	306	+ $reports[$row->agent_id]['interactions'] = (int) $row->count;
	307	+ }
284	308	}
285	309
286	310	$agentIds = array_keys($reports);
287	311
		@@ -291,19 +315,18 @@
291	315
292	316	// get agent feedback statistics
293	317	$agentFeedbackRatingEnabled = Helper::getBusinessSettings('agent_feedback_rating') === 'yes';
294	318	if (defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled) {
295		- $agentConversations = Conversation::select([
296		- $this->db()->raw('person_id as agent_id'),
297		- $this->db()->raw('GROUP_CONCAT(id) as conversation_ids')
298		- ])
299		- ->whereIn('person_id', $agentIds)
300		- ->whereHas('person', function ($q) {
301		- $q->where('person_type', '=', 'agent');
302		- })
303		- ->where('conversation_type', 'response')
304		- ->groupBy('agent_id')
305		- ->get();
	319	+ $agentConversations = $access->applyMailboxRestrictionScopeViaTicket(
	320	+ Conversation::select([
	321	+ $this->db()->raw('person_id as agent_id'),
	322	+ $this->db()->raw('GROUP_CONCAT(id) as conversation_ids')
	323	+ ])
	324	+ ->whereIn('person_id', $agentIds)
	325	+ ->whereIn('person_id', Agent::getAgentIds())
	326	+ ->where('conversation_type', 'response')
	327	+ ->groupBy('agent_id')
	328	+ )->get();
306	329
307	330	foreach ($agentConversations as $conversation) {
308	331	$conversationIds = array_map('intval', explode(',', $conversation->conversation_ids));
309	332
		@@ -330,30 +353,40 @@
330	353	$reports[$agentId]['dislikes'] = $dislikeCount;
331	354	}
332	355	}
333	356
334		- $agents = Agent::select(['id', 'first_name', 'last_name', 'email'])
335		- ->whereIn('id', $agentIds)
336		- ->get();
	357	+ // Without an explicit agent filter, report on every agent — $reports
	358	+ // only holds the ones with activity in the range, so restricting to its
	359	+ // keys would silently drop agents who simply had a quiet period.
	360	+ $agentsQuery = Agent::select(['id', 'first_name', 'last_name', 'email']);
337	361
338		- foreach ($agents as $agent) {
339		- $report = NULL;
340		- if(isset($reports[$agent->id])) {
341		- $reportFields = [
342		- 'interactions' => 0,
343		- 'responses' => 0,
344		- 'opens' => 0,
345		- 'closed' => 0,
346		- 'waiting_tickets' => 0,
347		- ];
	362	+ if ($agent) {
	363	+ $agentsQuery->whereIn('id', $agentIds);
	364	+ }
348	365
349		- $additionalFields = defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled ? ['likes' => 0, 'dislikes' => 0] : [];
350		- $reportFields = $reportFields + $additionalFields;
	366	+ $agents = $agentsQuery->get();
351	367
352		- $report = wp_parse_args($reports[$agent->id], $reportFields);
353		- }
354		- $agent->stats = $report;
355		- $agent->active_stat = $this->getActiveStatByAgent($agent->id);
	368	+ $reportFields = [
	369	+ 'interactions' => 0,
	370	+ 'responses' => 0,
	371	+ 'opens' => 0,
	372	+ 'closed' => 0,
	373	+ 'waiting_tickets' => 0,
	374	+ ];
	375	+
	376	+ $additionalFields = defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled ? ['likes' => 0, 'dislikes' => 0] : [];
	377	+ $reportFields = $reportFields + $additionalFields;
	378	+
	379	+ // One grouped query instead of one per agent — this summary lists every
	380	+ // agent, so a per-agent call scales with the whole agent table.
	381	+ $waitStats = $this->getWaitStatsByAgents($agents->pluck('id')->toArray());
	382	+ $emptyActiveStat = ['average_waiting' => 0, 'max_waiting' => 0, 'waiting_tickets' => 0];
	383	+
	384	+ foreach ($agents as $agent) {
	385	+ $agent->stats = wp_parse_args(isset($reports[$agent->id]) ? $reports[$agent->id] : [], $reportFields);
	386	+ $agent->active_stat = isset($waitStats[$agent->id])
	387	+ ? $this->formatActiveStat($waitStats[$agent->id])
	388	+ : $emptyActiveStat;
356	389	}
357	390	return $agents;
358	391	}
359	392
		@@ -375,8 +408,10 @@
375	408	$reports = [];
376	409
377	410	$groupByField = $type == 'product' ? 'product_id' : 'mailbox_id';
378	411
	412	+ $access = new AgentTicketAccess();
	413	+
379	414	$resolves = $this->db()->table('fs_tickets')
380	415	->select([
381	416	$this->db()->raw('COUNT(id) AS count'),
382	417	$groupByField,
		@@ -382,11 +417,13 @@
382	417	$groupByField,
383	418	])
384	419	->groupBy($groupByField)
385	420	->where('status', 'closed')
386		- ->whereBetween('resolved_at', [$from, $to])
387		- ->get();
	421	+ ->whereBetween('resolved_at', [$from, $to]);
388	422
	423	+ // Aggregates count tickets too, so the same mailbox boundary applies.
	424	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
	425	+
389	426	$reports = $this->pushReportData('closed', $resolves, $reports, $groupByField);
390	427
391	428	$openTickets = $this->db()->table('fs_tickets')
392	429	->select([
		@@ -394,11 +431,12 @@
394	431	$groupByField
395	432	])
396	433	->groupBy($groupByField)
397	434	->where('status', '!=', 'closed')
398		- ->whereBetween('created_at', [$from, $to])
399		- ->get();
	435	+ ->whereBetween('created_at', [$from, $to]);
400	436
	437	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
	438	+
401	439	$reports = $this->pushReportData('opens', $openTickets, $reports, $groupByField);
402	440
403	441	$responses = $this->db()->table('fs_conversations')
404	442	->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id')
		@@ -406,38 +444,34 @@
406	444	$this->db()->raw('COUNT(' . $tablePrefix . 'fs_conversations.id) AS count'),
407	445	'fs_tickets.' . $groupByField,
408	446	])
409	447	->groupBy('fs_tickets.' . $groupByField)
410		- ->whereBetween('fs_conversations.created_at', [$from, $to])
411		- ->get();
	448	+ ->whereBetween('fs_conversations.created_at', [$from, $to]);
412	449
	450	+ $responses = $access->applyMailboxRestrictionScope($responses)->get();
	451	+
413	452	$reports = $this->pushReportData('responses', $responses, $reports, $groupByField);
414	453
415		- $ticketIds = $this->db()->table('fs_tickets')
416		- ->select([
417		- $groupByField,
418		- $this->db()->raw('GROUP_CONCAT(id) AS ticket_ids'),
419		- ])
420		- ->where($groupByField, '!=', 0)
421		- ->groupBy($groupByField)
422		- ->get();
	454	+ // Interactions = how many distinct tickets were replied to in the range.
	455	+ // This used to GROUP_CONCAT every ticket id per group and feed them back
	456	+ // in a whereIn, one query per group. group_concat_max_len is 1024 bytes
	457	+ // by default, so any group past ~170 tickets had its id list silently
	458	+ // truncated and the count came out far too low. One grouped
	459	+ // COUNT(DISTINCT) has no such limit and drops the per-group queries.
	460	+ $interactions = $this->db()->table('fs_conversations')
	461	+ ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id')
	462	+ ->select([
	463	+ 'fs_tickets.' . $groupByField,
	464	+ $this->db()->raw('COUNT(DISTINCT ' . $tablePrefix . 'fs_conversations.ticket_id) AS count'),
	465	+ ])
	466	+ ->where('fs_conversations.conversation_type', 'response')
	467	+ ->whereBetween('fs_conversations.created_at', [$from, $to])
	468	+ ->groupBy('fs_tickets.' . $groupByField);
423	469
424		- $result = [];
425		- foreach ($ticketIds as $item) {
426		- $result[$item->{$groupByField}] = explode(',', $item->ticket_ids);
427		- }
	470	+ $interactions = $access->applyMailboxRestrictionScope($interactions)->get();
428	471
429		- foreach ($result as $id => $ticketIds) {
430		- $interactions = Conversation::whereIn('ticket_id', $ticketIds)
431		- ->where('conversation_type', 'response')
432		- ->whereBetween('created_at', [$from, $to])
433		- ->groupBy('ticket_id')
434		- ->get()
435		- ->count();
	472	+ $reports = $this->pushReportData('interactions', $interactions, $reports, $groupByField);
436	473
437		- $reports[$id]['interactions'] = $interactions;
438		- }
439		-
440	474	$ids = array_keys($reports);
441	475
442	476	$types = [
443	477	'product' => [
		@@ -494,8 +528,10 @@
494	528
495	529	// Get per-agent stats using the same queries as agentSummary
496	530	$reports = [];
497	531
	532	+ $access = new AgentTicketAccess();
	533	+
498	534	$resolves = $this->db()->table('fs_tickets')
499	535	->select([
500	536	$this->db()->raw('COUNT(id) AS count'),
501	537	'agent_id',
		@@ -501,11 +537,12 @@
501	537	'agent_id',
502	538	])
503	539	->groupBy('agent_id')
504	540	->where('status', 'closed')
505		- ->whereBetween('resolved_at', [$from, $to])
506		- ->get();
	541	+ ->whereBetween('resolved_at', [$from, $to]);
507	542
	543	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
	544	+
508	545	$reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id');
509	546
510	547	$openTickets = $this->db()->table('fs_tickets')
511	548	->select([
		@@ -512,34 +549,31 @@
512	549	$this->db()->raw('COUNT(id) AS count'),
513	550	'agent_id'
514	551	])
515	552	->groupBy('agent_id')
516		- ->where('status', '!=', 'closed')
517		- ->get();
	553	+ ->where('status', '!=', 'closed');
518	554
	555	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
	556	+
519	557	$reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id');
520	558
521		- $responses = Conversation::select([
522		- $this->db()->raw('COUNT(id) AS count'),
523		- $this->db()->raw('person_id as agent_id'),
524		- ])
525		- ->whereHas('person', function ($q) {
526		- $q->where('person_type', '=', 'agent');
527		- })
528		- ->whereBetween('created_at', [$from, $to])
529		- ->where('conversation_type', 'response')
530		- ->groupBy('agent_id')
531		- ->get();
	559	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	560	+ Conversation::select([
	561	+ $this->db()->raw('COUNT(id) AS count'),
	562	+ $this->db()->raw('person_id as agent_id'),
	563	+ ])
	564	+ ->whereIn('person_id', Agent::getAgentIds())
	565	+ ->whereBetween('created_at', [$from, $to])
	566	+ ->where('conversation_type', 'response')
	567	+ ->groupBy('agent_id')
	568	+ )->get();
532	569
533	570	$reports = $this->pushReportData('responses', $responses, $reports, 'agent_id');
534	571
535		- foreach ($responses as $response) {
536		- $reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id)
537		- ->where('conversation_type', 'response')
538		- ->whereBetween('created_at', [$from, $to])
539		- ->groupBy('ticket_id')
540		- ->get()
541		- ->count();
	572	+ foreach ($this->interactionCountsByAgent($from, $to, $access) as $row) {
	573	+ if (isset($reports[$row->agent_id])) {
	574	+ $reports[$row->agent_id]['interactions'] = (int) $row->count;
	575	+ }
542	576	}
543	577
544	578	// Get group -> agent_id mappings
545	579	$groupAgentMap = TagPivot::where('source_type', 'agent_group')
		@@ -611,16 +645,25 @@
611	645
612	646	/**
613	647	* getActiveStats method will return the statistics for active tickets
614	648	* This method will get the list of open tickets calculate the wait times and return results
	649	+ * @param int|null $productId Narrow to one product's tickets
615	650	* @return array|false
616	651	*/
617		- public function getActiveStats()
	652	+ public function getActiveStats($productId = null)
618	653	{
619	654	// We will calculate the wait times for open waiting tickets
620		- $waitStat = Ticket::waitingOnly()
	655	+ $query = (new AgentTicketAccess())->applyMailboxRestrictionScope(
	656	+ Ticket::waitingOnly()
	657	+ )
621	658	->where('status', '!=', 'closed')
622		- ->whereNotNull('waiting_since')
	659	+ ->whereNotNull('waiting_since');
	660	+
	661	+ if ($productId) {
	662	+ $query->where('product_id', $productId);
	663	+ }
	664	+
	665	+ $waitStat = $query
623	666	->select([
624	667	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
625	668	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
626	669	$this->db()->raw('COUNT(*) as total_tickets')
		@@ -657,9 +700,9 @@
657	700	* @return array|false
658	701	*/
659	702	public function getActiveStatByAgent($agentId)
660	703	{
661		- $waitStat = Ticket::waitingOnly()
	704	+ $waitStat = (new AgentTicketAccess())->applyMailboxRestrictionScope(Ticket::waitingOnly())
662	705	->where('status', '!=', 'closed')
663	706	->whereNotNull('waiting_since')
664	707	->where('agent_id', $agentId)
665	708	->select([
		@@ -672,8 +715,45 @@
672	715	if(!$waitStat) {
673	716	return false;
674	717	}
675	718
	719	+ return $this->formatActiveStat($waitStat);
	720	+ }
	721	+
	722	+ /**
	723	+ * getWaitStatsByAgents is the batched form of getActiveStatByAgent's aggregate, keyed by agent id
	724	+ * Agents with no waiting tickets get no row, so callers supply the zeroed default
	725	+ * @param array $agentIds
	726	+ * @return \FluentSupport\Framework\Database\Orm\Collection|array
	727	+ */
	728	+ public function getWaitStatsByAgents($agentIds)
	729	+ {
	730	+ if (empty($agentIds)) {
	731	+ return [];
	732	+ }
	733	+
	734	+ return (new AgentTicketAccess())->applyMailboxRestrictionScope(Ticket::waitingOnly())
	735	+ ->where('status', '!=', 'closed')
	736	+ ->whereNotNull('waiting_since')
	737	+ ->whereIn('agent_id', $agentIds)
	738	+ ->select([
	739	+ 'agent_id',
	740	+ $this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
	741	+ $this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
	742	+ $this->db()->raw('COUNT(*) as total_tickets')
	743	+ ])
	744	+ ->groupBy('agent_id')
	745	+ ->get()
	746	+ ->keyBy('agent_id');
	747	+ }
	748	+
	749	+ /**
	750	+ * formatActiveStat converts a waiting-ticket aggregate row into the display shape
	751	+ * @param $waitStat
	752	+ * @return array
	753	+ */
	754	+ private function formatActiveStat($waitStat)
	755	+ {
676	756	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
677	757	if($waitStat->avg_waiting > 0) {
678	758	$waitSeconds = time() - $waitStat->avg_waiting;
679	759	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
		@@ -705,9 +785,9 @@
705	785	return [];
706	786	}
707	787	}
708	788
709		- public function getTicketStats($from, $to)
	789	+ public function getTicketStats($from, $to, $productId = null)
710	790	{
711	791	global $wpdb;
712	792
713	793	$whereClause = '';
		@@ -720,8 +800,22 @@
720	800	$queryParams[] = $start_date;
721	801	$queryParams[] = $end_date;
722	802	}
723	803
	804	+ if ($productId) {
	805	+ $whereClause .= ($whereClause ? ' AND ' : 'WHERE ') . 'product_id = %d';
	806	+ $queryParams[] = intval($productId);
	807	+ }
	808	+
	809	+ // Raw SQL, so the query-builder helper cannot be used.
	810	+ $restrictedMailboxIds = PermissionManager::getRestrictedMailboxIds();
	811	+
	812	+ if ($restrictedMailboxIds) {
	813	+ $restrictedList = implode(',', array_map('intval', $restrictedMailboxIds));
	814	+ $whereClause .= ($whereClause ? ' AND ' : 'WHERE ')
	815	+ . "(mailbox_id NOT IN ({$restrictedList}) OR mailbox_id IS NULL)";
	816	+ }
	817	+
724	818	// SQL query to count tickets by day of week and hour within the specified date range.
725	819	// $wpdb->prefix is WordPress-internal; $whereClause is built from hardcoded literals
726	820	// only — user-supplied date values are bound via %s placeholders in prepare() below.
727	821	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		@@ -765,9 +859,9 @@
765	859
766	860	return $report;
767	861	}
768	862
769		- public function getResponseStats($from, $to, $reportType, $agentId = null)
	863	+ public function getResponseStats($from, $to, $reportType, $agentId = null, $productId = null)
770	864	{
771	865	global $wpdb;
772	866
773	867	$whereClause = ' AND p.person_type = %s';
		@@ -785,8 +879,27 @@
785	879	$whereClause .= ' AND c.person_id = %d';
786	880	$queryParams[] = intval($agentId);
787	881	}
788	882
	883	+ // As getTicketStats(), but reaching the mailbox and product through the parent ticket.
	884	+ $ticketConditions = [];
	885	+ $restrictedMailboxIds = PermissionManager::getRestrictedMailboxIds();
	886	+
	887	+ if ($restrictedMailboxIds) {
	888	+ $restrictedList = implode(',', array_map('intval', $restrictedMailboxIds));
	889	+ $ticketConditions[] = "(t.mailbox_id NOT IN ({$restrictedList}) OR t.mailbox_id IS NULL)";
	890	+ }
	891	+
	892	+ if ($productId) {
	893	+ $ticketConditions[] = 't.product_id = %d';
	894	+ $queryParams[] = intval($productId);
	895	+ }
	896	+
	897	+ if ($ticketConditions) {
	898	+ $whereClause .= " AND EXISTS (SELECT 1 FROM {$wpdb->prefix}fs_tickets t"
	899	+ . " WHERE t.id = c.ticket_id AND " . implode(' AND ', $ticketConditions) . ')';
	900	+ }
	901	+
789	902	// SQL query to count customer responses by day of week and hour within the specified date range
790	903	$query = $wpdb->prepare(
791	904	"SELECT DAYNAME(c.created_at) AS weekday, HOUR(c.created_at) AS hour, COUNT(*) AS count
792	905	FROM {$wpdb->prefix}fs_conversations AS c
		@@ -831,9 +944,11 @@
831	944	}
832	945
833	946	public function getTicketResponseStats($from, $to, $filter)
834	947	{
835		- $query = Conversation::query()
	948	+ $query = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
	949	+ Conversation::query()
	950	+ )
836	951	->select('id', 'ticket_id', 'person_id', 'created_at', 'content')
837	952	->addSelect([
838	953	'person_type' => Person::select('person_type')
839	954	->whereColumn('id', 'fs_conversations.person_id'),