← All changes | app/Modules/Reporting/Reporting.php +802 -71 1.4.5 → 2.4.0 View file →

		@@ -2,32 +2,63 @@
2	2
3	3	namespace FluentSupport\App\Modules\Reporting;
4	4
5	5	use FluentSupport\App\Models\Agent;
	6	+use FluentSupport\App\Models\AgentGroup;
6	7	use FluentSupport\App\Models\Conversation;
	8	+use FluentSupport\App\Models\MailBox;
	9	+use FluentSupport\App\Models\Meta;
	10	+use FluentSupport\App\Models\Person;
	11	+use FluentSupport\App\Models\Product;
	12	+use FluentSupport\App\Models\TagPivot;
7	13	use FluentSupport\App\Models\Ticket;
	14	+use FluentSupport\App\Modules\PermissionManager;
	15	+use FluentSupport\App\Services\Helper;
	16	+use FluentSupport\App\Services\Tickets\AgentTicketAccess;
	17	+use FluentSupport\Framework\Database\Orm\Builder;
	18	+use FluentSupport\Framework\Support\Arr;
	19	+use FluentSupport\Framework\Support\DateTime;
8	20
	21	+/**
	22	+ * Reporting class is responsible for getting data related to report
	23	+ * @package FluentSupport\App\Modules\Reporting
	24	+ *
	25	+ * @version 1.0.0
	26	+ */
9	27	class Reporting
10	28	{
11	29	use ReportingHelperTrait;
12	30
	31	+ /**
	32	+ * getTicketsGrowth will generate tickets statistics and return
	33	+ * @param false $from
	34	+ * @param false $to
	35	+ * @param array $filters
	36	+ * @return array
	37	+ */
13	38	public function getTicketsGrowth($from = false, $to = false, $filters = [])
14	39	{
	40	+ //Generate report period
15	41	$period = $this->makeDatePeriod(
16		- $from = $this->makeFromDate($from),
17		- $to = $this->makeToDate($to),
18		- $frequency = $this->getFrequency($from, $to)
	42	+ $from = $this->makeFromDate($from),//Date from
	43	+ $to = $this->makeToDate($to),//Date to
	44	+ $frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M
19	45	);
20	46
	47	+ //Get group by and order by i.e date,week, month
21	48	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
22	49
23		-
	50	+ //get all tickets statistics within the date range
24	51	$query = $this->db()->table('fs_tickets')
25	52	->select($this->prepareSelect($frequency))
26		- ->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
	53	+ ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
27	54	->groupBy($groupBy)
28		- ->orderBy($orderBy, 'ASC');
	55	+ ->oldest($orderBy);
29	56
	57	+ // Bound first: a caller-supplied mailbox_id below is not proof of access.
	58	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	59	+
	60	+ //If filter by product or agent or status selected
30	61	if ($filters) {
31	62	if (!empty($filters['statuses'])) {
32	63	$query->whereIn('status', $filters['statuses']);
33	64	}
		@@ -38,8 +69,16 @@
38	69
39	70	if (!empty($filters['agent_id'])) {
40	71	$query->where('agent_id', $filters['agent_id']);
41	72	}
	73	+
	74	+ if (!empty($filters['mailbox_id'])) {
	75	+ $query->where('mailbox_id', $filters['mailbox_id']);
	76	+ }
	77	+
	78	+ if (!empty($filters['agent_ids'])) {
	79	+ $query->whereIn('agent_id', $filters['agent_ids']);
	80	+ }
42	81	}
43	82
44	83	$items = $query->get();
45	84
		@@ -45,25 +84,39 @@
45	84
46	85	return $this->getResult($period, $items);
47	86	}
48	87
49		- public function getTicketResolveGrowth($from = false, $to = false, $filters = [])
	88	+ /**
	89	+ * getTicketResolveGrowth method will get the statistics for resolved/closed tickets
	90	+ * @param false $from
	91	+ * @param false $to
	92	+ * @param array $filters
	93	+ * @return array
	94	+ */
	95	+ public function getTicketResolveGrowth($from = false, $to = false, $filters = [], $type = '')
50	96	{
51	97	$period = $this->makeDatePeriod(
52		- $from = $this->makeFromDate($from),
53		- $to = $this->makeToDate($to),
54		- $frequency = $this->getFrequency($from, $to)
	98	+ $from = $this->makeFromDate($from),//Date from
	99	+ $to = $this->makeToDate($to),//date to
	100	+ $frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M
55	101	);
56	102
57		- list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
	103	+ list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);//Get group by and order by i.e date,week, month
58	104
	105	+ $filterColumn = (!empty($type)) ? $type.'_id' : 'id';
	106	+
	107	+ //get the closed ticket statistics within the date range
59	108	$query = $this->db()->table('fs_tickets')
60	109	->select($this->prepareSelect($frequency, 'resolved_at'))
61		- ->whereBetween('resolved_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
	110	+ ->whereBetween('resolved_at', $this->prepareBetween($frequency, $from, $to))
62	111	->where('status', 'closed')
	112	+ ->where($filterColumn, '>', 0)
63	113	->groupBy($groupBy)
64		- ->orderBy($orderBy, 'ASC');
	114	+ ->oldest($orderBy);
65	115
	116	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	117	+
	118	+ //If filter by product or agent is selected
66	119	if ($filters) {
67	120	if (!empty($filters['product_id'])) {
68	121	$query->where('product_id', $filters['product_id']);
69	122	}
		@@ -70,8 +123,16 @@
70	123
71	124	if (!empty($filters['agent_id'])) {
72	125	$query->where('agent_id', $filters['agent_id']);
73	126	}
	127	+
	128	+ if (!empty($filters['mailbox_id'])) {
	129	+ $query->where('mailbox_id', $filters['mailbox_id']);
	130	+ }
	131	+
	132	+ if (!empty($filters['agent_ids'])) {
	133	+ $query->whereIn('agent_id', $filters['agent_ids']);
	134	+ }
74	135	}
75	136
76	137	$items = $query->get();
77	138
		@@ -77,8 +138,15 @@
77	138
78	139	return $this->getResult($period, $items);
79	140	}
80	141
	142	+ /**
	143	+ * getResponseGrowth method will generate the statistics for response
	144	+ * @param false $from
	145	+ * @param false $to
	146	+ * @param array $filters
	147	+ * @return array
	148	+ */
81	149	public function getResponseGrowth($from = false, $to = false, $filters = [])
82	150	{
83	151	$period = $this->makeDatePeriod(
84	152	$from = $this->makeFromDate($from),
		@@ -87,19 +155,30 @@
87	155	);
88	156
89	157	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
90	158
91		- $query = $this->db()->table('fs_conversations')
92		- ->select($this->prepareSelect($frequency, 'created_at'))
93		- ->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
94		- ->where('conversation_type', 'response')
95		- ->groupBy($groupBy)
96		- ->orderBy($orderBy, 'ASC');
	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	+ );
97	168
98	169	if ($filters) {
99	170	if (!empty($filters['person_id'])) {
100	171	$query->where('person_id', $filters['person_id']);
101	172	}
	173	+
	174	+ if (!empty($filters['person_ids'])) {
	175	+ $query->whereIn('person_id', $filters['person_ids']);
	176	+ }
	177	+
	178	+ if (!empty($filters['product_id'])) {
	179	+ $query->where('product_id', $filters['product_id']);
	180	+ }
102	181	}
103	182
104	183	$items = $query->get();
105	184
		@@ -105,37 +184,98 @@
105	184
106	185	return $this->getResult($period, $items);
107	186	}
108	187
	188	+ public function getResponseGrowthChart($from = false, $to = false, $filters = [], $type = ''): array
	189	+ {
	190	+ $period = $this->makeDatePeriod(
	191	+ $from = $this->makeFromDate($from),
	192	+ $to = $this->makeToDate($to),
	193	+ $frequency = $this->getFrequency($from, $to)
	194	+ );
	195	+
	196	+ list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
	197	+
	198	+ $filterColumn = (!empty($type)) ? $type.'_id' : 'id';
	199	+
	200	+ $query = $this->db()->table('fs_tickets')
	201	+ ->select($this->prepareSelect($frequency,'created_at','response_count'))
	202	+ ->whereBetween('created_at', $this->prepareBetween($frequency, $from, $to))
	203	+ ->havingRaw('COUNT(response_count)> 0')
	204	+ ->where($filterColumn, '>', 0)
	205	+ ->groupBy($groupBy)
	206	+ ->oldest($orderBy);
	207	+
	208	+ (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
	209	+
	210	+ if ($filters) {
	211	+ if (!empty($filters['product_id'])) {
	212	+ $query->where('product_id', $filters['product_id']);
	213	+ }
	214	+
	215	+ if (!empty($filters['mailbox_id'])) {
	216	+ $query->where('mailbox_id', $filters['mailbox_id']);
	217	+ }
	218	+ }
	219	+
	220	+ $items = $query->get();
	221	+
	222	+ return $this->getResult($period, $items);
	223	+ }
	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	+
	240	+ /**
	241	+ * agentSummary method will prepare ticket summary with responses by agent
	242	+ * @param false $from
	243	+ * @param false $to
	244	+ * @param false $agent
	245	+ * @return mixed
	246	+ */
109	247	public function agentSummary($from = false, $to = false, $agent = false)
110	248	{
111	249	if(!$from) {
112		- $from = current_time('mysql');
	250	+ $from = current_time('Y-m-d');
113	251	}
114	252
115	253	if(!$to) {
116		- $to = date('Y-m-d', current_time('timestamp') + 86400);
117		- } else {
118		- $to = date('Y-m-d', strtotime($to) + 86400);
	254	+ $to = current_time('Y-m-d');
119	255	}
120	256
121		- $from = $this->makeFromDate($from);
122		- $to = $this->makeToDate($to);
	257	+ $from .= ' 00:00:00';
	258	+ $to .= ' 23:59:59';
	259	+ $reports = [];
123	260
124		- $reports = [];
	261	+ $access = new AgentTicketAccess();
125	262
	263	+ //Get tickets statistics that are closed
126	264	$resolves = $this->db()->table('fs_tickets')
127	265	->select([
128	266	$this->db()->raw('COUNT(id) AS count'),
129		- 'agent_id'
	267	+ 'agent_id',
130	268	])
131		- ->whereBetween('resolved_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
132	269	->groupBy('agent_id')
133	270	->where('status', 'closed')
134		- ->get();
	271	+ ->whereBetween('resolved_at', [$from, $to]);
135	272
136		- $reports = $this->pushAgentsReport('closed', $resolves, $reports);
	273	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
137	274
	275	+ $reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id');
	276	+
	277	+ //get statistics for all except closed ticket
138	278	$openTickets = $this->db()->table('fs_tickets')
139	279	->select([
140	280	$this->db()->raw('COUNT(id) AS count'),
141	281	'agent_id'
		@@ -140,34 +280,32 @@
140	280	$this->db()->raw('COUNT(id) AS count'),
141	281	'agent_id'
142	282	])
143	283	->groupBy('agent_id')
144		- ->where('status', '!=', 'closed')
145		- ->get();
	284	+ ->where('status', '!=', 'closed');
146	285
147		- $reports = $this->pushAgentsReport('opens', $openTickets, $reports);
	286	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
148	287
149		- $responses = Conversation::select([
150		- $this->db()->raw('COUNT(id) AS count'),
151		- $this->db()->raw('person_id as agent_id'),
152		- ])
153		- ->whereHas('person', function ($q) {
154		- $q->where('person_type', '=', 'agent');
155		- })
156		- ->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
157		- ->where('conversation_type', 'response')
158		- ->groupBy('agent_id')
159		- ->get();
	288	+ $reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id');
	289	+ //Get response by agent
	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();
160	301
161		- $reports = $this->pushAgentsReport('responses', $responses, $reports);
162		-
163		- foreach ($responses as $response) {
164		- $reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id)
165		- ->where('conversation_type', 'response')
166		- ->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
167		- ->groupBy('ticket_id')
168		- ->get()
169		- ->count();
	302	+ $reports = $this->pushReportData('responses', $responses, $reports, 'agent_id');
	303	+ //Get interactions/responses by individual agents
	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	+ }
170	308	}
171	309
172	310	$agentIds = array_keys($reports);
173	311
		@@ -174,50 +312,358 @@
174	312	if ($agent) {
175	313	$agentIds = array_map('intval', explode(',', $agent));
176	314	}
177	315
178		- $agents = Agent::select(['id', 'first_name', 'last_name'])
179		- ->whereIn('id', $agentIds)
180		- ->get();
	316	+ // get agent feedback statistics
	317	+ $agentFeedbackRatingEnabled = Helper::getBusinessSettings('agent_feedback_rating') === 'yes';
	318	+ if (defined('FLUENTSUPPORTPRO_PLUGIN_VERSION') && $agentFeedbackRatingEnabled) {
	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();
181	329
	330	+ foreach ($agentConversations as $conversation) {
	331	+ $conversationIds = array_map('intval', explode(',', $conversation->conversation_ids));
	332	+
	333	+ $feedbackMeta = Meta::whereIn('object_id', $conversationIds)
	334	+ ->where('key', 'agent_feedback_ratings')
	335	+ ->whereBetween('created_at', [$from, $to])
	336	+ ->get();
	337	+
	338	+ $likeCount = 0;
	339	+ $dislikeCount = 0;
	340	+
	341	+ foreach ($feedbackMeta as $feedback) {
	342	+ $feedbackStatus = $feedback->value;
	343	+
	344	+ if ($feedbackStatus === 'like') {
	345	+ $likeCount++;
	346	+ } elseif ($feedbackStatus === 'dislike') {
	347	+ $dislikeCount++;
	348	+ }
	349	+ }
	350	+
	351	+ $agentId = $conversation->agent_id;
	352	+ $reports[$agentId]['likes'] = $likeCount;
	353	+ $reports[$agentId]['dislikes'] = $dislikeCount;
	354	+ }
	355	+ }
	356	+
	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']);
	361	+
	362	+ if ($agent) {
	363	+ $agentsQuery->whereIn('id', $agentIds);
	364	+ }
	365	+
	366	+ $agents = $agentsQuery->get();
	367	+
	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	+
182	384	foreach ($agents as $agent) {
183		- $report = wp_parse_args($reports[$agent->id], [
184		- 'interactions' => 0,
	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;
	389	+ }
	390	+ return $agents;
	391	+ }
	392	+
	393	+ public function getSummary($type, $from = null, $to = null)
	394	+ {
	395	+ global $wpdb;
	396	+ $tablePrefix = $wpdb->prefix;
	397	+
	398	+ if (!$from) {
	399	+ $from = current_time('Y-m-d');
	400	+ }
	401	+
	402	+ if (!$to) {
	403	+ $to = current_time('Y-m-d');
	404	+ }
	405	+
	406	+ $from .= ' 00:00:00';
	407	+ $to .= ' 23:59:59';
	408	+ $reports = [];
	409	+
	410	+ $groupByField = $type == 'product' ? 'product_id' : 'mailbox_id';
	411	+
	412	+ $access = new AgentTicketAccess();
	413	+
	414	+ $resolves = $this->db()->table('fs_tickets')
	415	+ ->select([
	416	+ $this->db()->raw('COUNT(id) AS count'),
	417	+ $groupByField,
	418	+ ])
	419	+ ->groupBy($groupByField)
	420	+ ->where('status', 'closed')
	421	+ ->whereBetween('resolved_at', [$from, $to]);
	422	+
	423	+ // Aggregates count tickets too, so the same mailbox boundary applies.
	424	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
	425	+
	426	+ $reports = $this->pushReportData('closed', $resolves, $reports, $groupByField);
	427	+
	428	+ $openTickets = $this->db()->table('fs_tickets')
	429	+ ->select([
	430	+ $this->db()->raw('COUNT(id) AS count'),
	431	+ $groupByField
	432	+ ])
	433	+ ->groupBy($groupByField)
	434	+ ->where('status', '!=', 'closed')
	435	+ ->whereBetween('created_at', [$from, $to]);
	436	+
	437	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
	438	+
	439	+ $reports = $this->pushReportData('opens', $openTickets, $reports, $groupByField);
	440	+
	441	+ $responses = $this->db()->table('fs_conversations')
	442	+ ->join('fs_tickets', 'fs_tickets.id', '=', 'fs_conversations.ticket_id')
	443	+ ->select([
	444	+ $this->db()->raw('COUNT(' . $tablePrefix . 'fs_conversations.id) AS count'),
	445	+ 'fs_tickets.' . $groupByField,
	446	+ ])
	447	+ ->groupBy('fs_tickets.' . $groupByField)
	448	+ ->whereBetween('fs_conversations.created_at', [$from, $to]);
	449	+
	450	+ $responses = $access->applyMailboxRestrictionScope($responses)->get();
	451	+
	452	+ $reports = $this->pushReportData('responses', $responses, $reports, $groupByField);
	453	+
	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);
	469	+
	470	+ $interactions = $access->applyMailboxRestrictionScope($interactions)->get();
	471	+
	472	+ $reports = $this->pushReportData('interactions', $interactions, $reports, $groupByField);
	473	+
	474	+ $ids = array_keys($reports);
	475	+
	476	+ $types = [
	477	+ 'product' => [
	478	+ 'model' => Product::class,
	479	+ 'fields' => ['id', 'title'],
	480	+ ],
	481	+ 'mailbox' => [
	482	+ 'model' => MailBox::class,
	483	+ 'fields' => ['id', 'name'],
	484	+ ],
	485	+ ];
	486	+
	487	+ $model = $types[$type]['model'];
	488	+ $fields = $types[$type]['fields'];
	489	+
	490	+ $items = $model::select($fields)
	491	+ ->whereIn('id', $ids)
	492	+ ->get();
	493	+
	494	+ foreach ($items as $item) {
	495	+ $report = isset($reports[$item->id]) ? $reports[$item->id] : [];
	496	+
	497	+ $report = wp_parse_args($report, [
185	498	'responses' => 0,
186	499	'opens' => 0,
187	500	'closed' => 0,
188		- 'waiting_tickets' => 0
	501	+ 'interactions' => 0
189	502	]);
190		- $agent->stats = $report;
191		- $agent->active_stat = $this->getActiveStatByAgent($agent->id);
	503	+ $item->stats = $report;
	504	+ $item->active_stat = '';
192	505	}
193	506
194		- return $agents;
	507	+ return $items;
195	508	}
196	509
197		- private function pushAgentsReport($type, $tickets, $reports)
	510	+ /**
	511	+ * agentGroupSummary method will prepare ticket summary aggregated by agent group
	512	+ * @param false $from
	513	+ * @param false $to
	514	+ * @return mixed
	515	+ */
	516	+ public function agentGroupSummary($from = false, $to = false)
198	517	{
	518	+ if (!$from) {
	519	+ $from = current_time('Y-m-d');
	520	+ }
	521	+
	522	+ if (!$to) {
	523	+ $to = current_time('Y-m-d');
	524	+ }
	525	+
	526	+ $from .= ' 00:00:00';
	527	+ $to .= ' 23:59:59';
	528	+
	529	+ // Get per-agent stats using the same queries as agentSummary
	530	+ $reports = [];
	531	+
	532	+ $access = new AgentTicketAccess();
	533	+
	534	+ $resolves = $this->db()->table('fs_tickets')
	535	+ ->select([
	536	+ $this->db()->raw('COUNT(id) AS count'),
	537	+ 'agent_id',
	538	+ ])
	539	+ ->groupBy('agent_id')
	540	+ ->where('status', 'closed')
	541	+ ->whereBetween('resolved_at', [$from, $to]);
	542	+
	543	+ $resolves = $access->applyMailboxRestrictionScope($resolves)->get();
	544	+
	545	+ $reports = $this->pushReportData('closed', $resolves, $reports, 'agent_id');
	546	+
	547	+ $openTickets = $this->db()->table('fs_tickets')
	548	+ ->select([
	549	+ $this->db()->raw('COUNT(id) AS count'),
	550	+ 'agent_id'
	551	+ ])
	552	+ ->groupBy('agent_id')
	553	+ ->where('status', '!=', 'closed');
	554	+
	555	+ $openTickets = $access->applyMailboxRestrictionScope($openTickets)->get();
	556	+
	557	+ $reports = $this->pushReportData('opens', $openTickets, $reports, 'agent_id');
	558	+
	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();
	569	+
	570	+ $reports = $this->pushReportData('responses', $responses, $reports, 'agent_id');
	571	+
	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	+ }
	576	+ }
	577	+
	578	+ // Get group -> agent_id mappings
	579	+ $groupAgentMap = TagPivot::where('source_type', 'agent_group')
	580	+ ->select(['tag_id', 'source_id'])
	581	+ ->get()
	582	+ ->groupBy('tag_id');
	583	+
	584	+ // Get all agent groups
	585	+ $groups = AgentGroup::select(['id', 'title'])->get();
	586	+
	587	+ $defaultStats = [
	588	+ 'responses' => 0,
	589	+ 'interactions' => 0,
	590	+ 'opens' => 0,
	591	+ 'closed' => 0,
	592	+ ];
	593	+
	594	+ foreach ($groups as $group) {
	595	+ $agentIds = isset($groupAgentMap[$group->id])
	596	+ ? array_map('intval', $groupAgentMap[$group->id]->pluck('source_id')->toArray())
	597	+ : [];
	598	+
	599	+ $group->agents_count = count($agentIds);
	600	+
	601	+ $groupStats = $defaultStats;
	602	+
	603	+ foreach ($agentIds as $agentId) {
	604	+ if (isset($reports[$agentId])) {
	605	+ $agentStats = wp_parse_args($reports[$agentId], $defaultStats);
	606	+ $groupStats['responses'] += (int) $agentStats['responses'];
	607	+ $groupStats['interactions'] += (int) $agentStats['interactions'];
	608	+ $groupStats['opens'] += (int) $agentStats['opens'];
	609	+ $groupStats['closed'] += (int) $agentStats['closed'];
	610	+ }
	611	+ }
	612	+
	613	+ $group->stats = $groupStats;
	614	+ }
	615	+
	616	+ return $groups;
	617	+ }
	618	+
	619	+ /**
	620	+ * pushReportData method will format the ticket summary report
	621	+ * @param $type
	622	+ * @param $tickets
	623	+ * @param $reports
	624	+ * @param $groupByField
	625	+ * @return array
	626	+ */
	627	+ private function pushReportData($type, $tickets, $reports, $groupByField): array
	628	+ {
199	629	foreach ($tickets as $ticket) {
200		- if(!$ticket->agent_id) {
	630	+ $groupKey = $ticket->{$groupByField};
	631	+
	632	+ if (!$groupKey) {
201	633	continue;
202	634	}
203	635
204		- if(!isset($reports[$ticket->agent_id])) {
205		- $reports[$ticket->agent_id] = [];
	636	+ if (!isset($reports[$groupKey])) {
	637	+ $reports[$groupKey] = [];
206	638	}
207	639
208		- $reports[$ticket->agent_id][$type] = $ticket->count;
	640	+ $reports[$groupKey][$type] = $ticket->count;
209	641	}
210	642
211	643	return $reports;
212	644	}
213	645
214		- public function getActiveStats()
	646	+ /**
	647	+ * getActiveStats method will return the statistics for active tickets
	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
	650	+ * @return array|false
	651	+ */
	652	+ public function getActiveStats($productId = null)
215	653	{
216	654	// We will calculate the wait times for open waiting tickets
217		- $waitStat = Ticket::waitingOnly()
	655	+ $query = (new AgentTicketAccess())->applyMailboxRestrictionScope(
	656	+ Ticket::waitingOnly()
	657	+ )
218	658	->where('status', '!=', 'closed')
219		- ->whereNotNull('waiting_since')
	659	+ ->whereNotNull('waiting_since');
	660	+
	661	+ if ($productId) {
	662	+ $query->where('product_id', $productId);
	663	+ }
	664	+
	665	+ $waitStat = $query
220	666	->select([
221	667	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
222	668	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
223	669	$this->db()->raw('COUNT(*) as total_tickets')
		@@ -246,11 +692,17 @@
246	692	'waiting_tickets' => $waitStat->total_tickets
247	693	];
248	694	}
249	695
	696	+ /**
	697	+ * getActiveStatByAgent method will return the statistics of active tickets for an agent
	698	+ * This method will get agent id as parameter, fetch the list of open tickets by agent id, calculate the wait times and return results
	699	+ * @param $agentId
	700	+ * @return array|false
	701	+ */
250	702	public function getActiveStatByAgent($agentId)
251	703	{
252		- $waitStat = Ticket::waitingOnly()
	704	+ $waitStat = (new AgentTicketAccess())->applyMailboxRestrictionScope(Ticket::waitingOnly())
253	705	->where('status', '!=', 'closed')
254	706	->whereNotNull('waiting_since')
255	707	->where('agent_id', $agentId)
256	708	->select([
		@@ -263,8 +715,45 @@
263	715	if(!$waitStat) {
264	716	return false;
265	717	}
266	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	+ {
267	756	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
268	757	if($waitStat->avg_waiting > 0) {
269	758	$waitSeconds = time() - $waitStat->avg_waiting;
270	759	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
		@@ -280,6 +769,248 @@
280	769	'average_waiting' => $avgWait,
281	770	'max_waiting' => (intval($waitStat->max_waiting)) ? human_time_diff(intval($waitStat->max_waiting), time()) : 0,
282	771	'waiting_tickets' => $waitStat->total_tickets
283	772	];
	773	+ }
	774	+
	775	+ public function getQueryResults($from, $to, $filter)
	776	+ {
	777	+ switch ($filter['report_type']) {
	778	+ case 'ticket':
	779	+ return $this->getTicketStats($from, $to);
	780	+ case 'agent_response':
	781	+ return $this->getResponseStats($from, $to, 'agent', $filter['agent_id'] ?? null);
	782	+ case 'customer_response':
	783	+ return $this->getResponseStats($from, $to, 'customer');
	784	+ default:
	785	+ return [];
	786	+ }
	787	+ }
	788	+
	789	+ public function getTicketStats($from, $to, $productId = null)
	790	+ {
	791	+ global $wpdb;
	792	+
	793	+ $whereClause = '';
	794	+ $queryParams = [];
	795	+
	796	+ if ($from && $to) {
	797	+ $start_date = gmdate('Y-m-d 00:00:00', strtotime($from));
	798	+ $end_date = gmdate('Y-m-d 23:59:59', strtotime($to));
	799	+ $whereClause = 'WHERE created_at BETWEEN %s AND %s';
	800	+ $queryParams[] = $start_date;
	801	+ $queryParams[] = $end_date;
	802	+ }
	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	+
	818	+ // SQL query to count tickets by day of week and hour within the specified date range.
	819	+ // $wpdb->prefix is WordPress-internal; $whereClause is built from hardcoded literals
	820	+ // only — user-supplied date values are bound via %s placeholders in prepare() below.
	821	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	822	+ $query = "SELECT DAYNAME(created_at) AS weekday, HOUR(created_at) AS hour, COUNT(*) AS count
	823	+ FROM {$wpdb->prefix}fs_tickets
	824	+ {$whereClause}
	825	+ GROUP BY DAYNAME(created_at), HOUR(created_at)
	826	+ ORDER BY FIELD(DAYNAME(created_at), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), HOUR(created_at)";
	827	+
	828	+ // Execute the query — prepared when date params are present, plain otherwise.
	829	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query structure is hardcoded; values are bound via $wpdb->prepare() when $queryParams is non-empty.
	830	+ $results = $queryParams
	831	+ ? $wpdb->get_results($wpdb->prepare($query, $queryParams)) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	832	+ : $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	833	+
	834	+ $fillData = array_fill(0, 24, 0);
	835	+ // add :00 on the suffix
	836	+ $fillData = array_combine(array_map(function ($hour) {
	837	+ return sprintf('%1d:00', $hour);
	838	+ }, array_keys($fillData)), $fillData);
	839	+
	840	+ // Prepare the report
	841	+ // Prepare the report
	842	+ $report = [
	843	+ 'Mon' => $fillData,
	844	+ 'Tue' => $fillData,
	845	+ 'Wed' => $fillData,
	846	+ 'Thu' => $fillData,
	847	+ 'Fri' => $fillData,
	848	+ 'Sat' => $fillData,
	849	+ 'Sun' => $fillData
	850	+ ];
	851	+
	852	+ foreach ($results as $result) {
	853	+ $weekdayName = substr($result->weekday, 0, 3);
	854	+ if (!isset($report[$weekdayName])) {
	855	+ $report[$weekdayName] = $fillData;
	856	+ }
	857	+ $report[$weekdayName][sprintf('%1d:00', $result->hour)] = (int)$result->count;
	858	+ }
	859	+
	860	+ return $report;
	861	+ }
	862	+
	863	+ public function getResponseStats($from, $to, $reportType, $agentId = null, $productId = null)
	864	+ {
	865	+ global $wpdb;
	866	+
	867	+ $whereClause = ' AND p.person_type = %s';
	868	+ $queryParams = [$reportType];
	869	+
	870	+ if ($from && $to) {
	871	+ $start_date = gmdate('Y-m-d 00:00:00', strtotime($from));
	872	+ $end_date = gmdate('Y-m-d 23:59:59', strtotime($to));
	873	+ $whereClause .= ' AND c.created_at BETWEEN %s AND %s';
	874	+ $queryParams[] = $start_date;
	875	+ $queryParams[] = $end_date;
	876	+ }
	877	+
	878	+ if ($agentId) {
	879	+ $whereClause .= ' AND c.person_id = %d';
	880	+ $queryParams[] = intval($agentId);
	881	+ }
	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	+
	902	+ // SQL query to count customer responses by day of week and hour within the specified date range
	903	+ $query = $wpdb->prepare(
	904	+ "SELECT DAYNAME(c.created_at) AS weekday, HOUR(c.created_at) AS hour, COUNT(*) AS count
	905	+ FROM {$wpdb->prefix}fs_conversations AS c
	906	+ JOIN {$wpdb->prefix}fs_persons AS p ON c.person_id = p.id
	907	+ WHERE c.conversation_type = 'response'
	908	+ {$whereClause}
	909	+ GROUP BY DAYNAME(c.created_at), HOUR(c.created_at)
	910	+ ORDER BY FIELD(DAYNAME(c.created_at), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), HOUR(c.created_at)",
	911	+ $queryParams
	912	+ );
	913	+
	914	+ // Execute the query
	915	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $query is the output of $wpdb->prepare() above; checker incorrectly traces back through the prepared parameters.
	916	+ $results = $wpdb->get_results($query);
	917	+
	918	+ $fillData = array_fill(0, 24, 0);
	919	+ // add :00 on the suffix
	920	+ $fillData = array_combine(array_map(function ($hour) {
	921	+ return sprintf('%1d:00', $hour);
	922	+ }, array_keys($fillData)), $fillData);
	923	+
	924	+ // Prepare the report
	925	+ $report = [
	926	+ 'Mon' => $fillData,
	927	+ 'Tue' => $fillData,
	928	+ 'Wed' => $fillData,
	929	+ 'Thu' => $fillData,
	930	+ 'Fri' => $fillData,
	931	+ 'Sat' => $fillData,
	932	+ 'Sun' => $fillData
	933	+ ];
	934	+
	935	+ foreach ($results as $result) {
	936	+ $weekdayName = substr($result->weekday, 0, 3);
	937	+ if (!isset($report[$weekdayName])) {
	938	+ $report[$weekdayName] = $fillData;
	939	+ }
	940	+ $report[$weekdayName][sprintf('%01d:00', $result->hour)] = (int)$result->count;
	941	+ }
	942	+
	943	+ return $report;
	944	+ }
	945	+
	946	+ public function getTicketResponseStats($from, $to, $filter)
	947	+ {
	948	+ $query = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
	949	+ Conversation::query()
	950	+ )
	951	+ ->select('id', 'ticket_id', 'person_id', 'created_at', 'content')
	952	+ ->addSelect([
	953	+ 'person_type' => Person::select('person_type')
	954	+ ->whereColumn('id', 'fs_conversations.person_id'),
	955	+ 'full_name' => Person::selectRaw("CONCAT(first_name, ' ', last_name)")
	956	+ ->whereColumn('id', 'fs_conversations.person_id')
	957	+ ])
	958	+ ->where('conversation_type', 'response')
	959	+ ->when(Arr::get($filter, 'person_type'), function (Builder $q, $personType) {
	960	+ return $q->whereHas('person', function ($q) use ($personType) {
	961	+ return $q->where('person_type', $personType);
	962	+ });
	963	+ })
	964	+ ->when(($from && $to), function ($q) use ($from, $to) {
	965	+ return $q->whereBetween('created_at', [
	966	+ DateTime::parse($from)->startOfDay(),
	967	+ DateTime::parse($to)->endOfDay()
	968	+ ]);
	969	+ })
	970	+ ->when(Arr::get($filter, 'person_id'), function ($q, $personId) {
	971	+ return $q->where('person_id', $personId);
	972	+ });
	973	+
	974	+ return $query->get();
	975	+ }
	976	+
	977	+ public function applyDateFilter($query, $from, $to)
	978	+ {
	979	+ if ($from && $to) {
	980	+ $from .= ' 00:00:00';
	981	+ $to .= ' 23:59:59';
	982	+
	983	+ $query->whereBetween('created_at', [$from, $to]);
	984	+ }
	985	+ }
	986	+
	987	+ public function finalizeQuery($query)
	988	+ {
	989	+ return $query->groupByRaw('DAYNAME(created_at), HOUR(created_at)')
	990	+ ->orderByRaw("FIELD(DAYNAME(created_at), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), HOUR(created_at)")
	991	+ ->get();
	992	+ }
	993	+
	994	+ public function formatResults($results)
	995	+ {
	996	+ $dataItems = [
	997	+ 'Mon' => [], 'Tue' => [], 'Wed' => [], 'Thu' => [], 'Fri' => [], 'Sat' => [], 'Sun' => []
	998	+ ];
	999	+
	1000	+ $hours = array_map(function ($hour) {
	1001	+ return $hour . ":00";
	1002	+ }, range(0, 23));
	1003	+
	1004	+ foreach ($dataItems as $day => $data) {
	1005	+ $dataItems[$day] = array_fill_keys($hours, 0);
	1006	+ }
	1007	+
	1008	+ foreach ($results as $row) {
	1009	+ $day = substr($row['day_of_week'], 0, 3);
	1010	+ $hour = $row['hour_of_day'] . ":00";
	1011	+ $dataItems[$day][$hour] = (int) $row['count'];
	1012	+ }
	1013	+
	1014	+ return $dataItems;
284	1015	}
285	1016	}