← All changes | app/Modules/StatModule.php +282 -53 1.5.1 → 2.4.5 View file →

		@@ -1,13 +1,27 @@
1	1	<?php
2	2
3	3	namespace FluentSupport\App\Modules;
4	4
	5	+use FluentSupport\App\Models\Agent;
5	6	use FluentSupport\App\Models\Conversation;
6	7	use FluentSupport\App\Models\Ticket;
	8	+use FluentSupport\App\Models\Product;
	9	+use FluentSupport\App\Services\Tickets\AgentTicketAccess;
7	10
	11	+/**
	12	+ * Dashboard ticket stats, scoped to the current agent's mailbox restrictions.
	13	+ */
8	14	class StatModule
9	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	+ */
10	24	public static function getAgentStat($agentId, $startDate = false, $endDate = false)
11	25	{
12	26	if (!$startDate) {
13	27	$currentDate = current_time('mysql');
		@@ -14,119 +28,334 @@
14	28	$startDate = $currentDate;
15	29	$endDate = $currentDate;
16	30	}
17	31
18		- $startDate = date('Y-m-d 00:00.01', strtotime($startDate));
19		- $endDate = date('Y-m-d 23:59.59', strtotime($endDate));
	32	+ $startDate = gmdate('Y-m-d 00:00.01', strtotime($startDate));
	33	+ $endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate));
20	34
21		- $newTickets = Ticket::where('agent_id', $agentId)
22		- ->where('status', 'new')
23		- ->count();
	35	+ $access = new AgentTicketAccess();
24	36
25		- $activeTickets = Ticket::where('agent_id', $agentId)
26		- ->where('status', 'active')
27		- ->count();
	37	+ $newTickets = $access->applyMailboxRestrictionScope(
	38	+ Ticket::where('agent_id', $agentId)->where('status', 'new')
	39	+ )->count();
28	40
29		- $closedTickets = Ticket::where('agent_id', $agentId)
30		- ->where('status', 'closed')
31		- ->whereBetween('resolved_at', [$startDate, $endDate])
32		- ->count();
	41	+ $activeTickets = $access->applyMailboxRestrictionScope(
	42	+ Ticket::where('agent_id', $agentId)->where('status', 'active')
	43	+ )->count();
33	44
34		- $responses = Conversation::where('conversation_type', 'response')
35		- ->where('person_id', $agentId)
36		- ->whereBetween('created_at', [$startDate, $endDate])
37		- ->count();
	45	+ $closedTickets = $access->applyMailboxRestrictionScope(
	46	+ Ticket::where('agent_id', $agentId)
	47	+ ->where('status', 'closed')
	48	+ ->whereBetween('resolved_at', [$startDate, $endDate])
	49	+ )->count();
38	50
39		- $interactions = Conversation::where('person_id', $agentId)
40		- ->where('conversation_type', 'response')
41		- ->whereBetween('created_at', [$startDate, $endDate])
42		- ->groupBy('ticket_id')
43		- ->get()
44		- ->count();
	51	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	52	+ Conversation::where('conversation_type', 'response')
	53	+ ->where('person_id', $agentId)
	54	+ ->whereBetween('created_at', [$startDate, $endDate])
	55	+ )->count();
45	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	+
46	65	return [
47	66	'new_tickets' => [
48		- 'title' => 'New Tickets',
	67	+ 'title' => __('New Tickets', 'fluent-support'),
49	68	'count' => $newTickets
50	69	],
51	70	'active_tickets' => [
52		- 'title' => 'Active Tickets',
	71	+ 'title' => __('Active Tickets', 'fluent-support'),
53	72	'count' => $activeTickets
54	73	],
55	74	'closed_tickets' => [
56		- 'title' => 'Closed Tickets',
	75	+ 'title' => __('Closed Tickets', 'fluent-support'),
57	76	'count' => $closedTickets
58	77	],
59	78	'responses' => [
60		- 'title' => 'Responses',
	79	+ 'title' => __('Total Responses', 'fluent-support'),
61	80	'count' => $responses
62	81	],
63		- 'interactions' =>[
64		- 'title' =>'Interactions',
	82	+ 'interactions' => [
	83	+ 'title' => __('Interactions', 'fluent-support'),
65	84	'count' => $interactions
66	85	]
67	86	];
68	87	}
69	88
	89	+ /**
	90	+ * @return array[]
	91	+ */
70	92	public static function getOverAllStats()
71	93	{
72		- $newTickets = Ticket::where('status', 'new')
	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'))
73	98	->count();
74	99
75		- $activeTickets = Ticket::where('status', 'active')
	100	+ $activeTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'active'))
76	101	->count();
77	102
78		- $closedTickets = Ticket::where('status', 'closed')
	103	+ $closedTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'closed'))
79	104	->count();
80	105
81		- $responses = Conversation::where('conversation_type', 'response')
82		- ->count();
	106	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	107	+ Conversation::where('conversation_type', 'response')
	108	+ )->count();
83	109
84	110	return [
85	111	'new_tickets' => [
86		- 'title' => 'New Tickets',
	112	+ 'title' => __('New Tickets', 'fluent-support'),
87	113	'count' => $newTickets
88	114	],
89	115	'active_tickets' => [
90		- 'title' => 'Active Tickets',
	116	+ 'title' => __('Active Tickets', 'fluent-support'),
91	117	'count' => $activeTickets
92	118	],
93	119	'closed_tickets' => [
94		- 'title' => 'Closed Tickets',
	120	+ 'title' => __('Closed Tickets', 'fluent-support'),
95	121	'count' => $closedTickets
96	122	],
97	123	'responses' => [
98		- 'title' => 'Responses',
	124	+ 'title' => __('Responses', 'fluent-support'),
99	125	'count' => $responses
100	126	]
101	127	];
102	128	}
103	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	+ */
104	182	public static function getAgentOverallStats($agentId)
105	183	{
106		- $replies_count = Conversation::where('person_id', $agentId)->count();
	184	+ $replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
	185	+ Conversation::where('person_id', $agentId)
	186	+ )->count();
107	187
108		- $interactions_count = Conversation::where('person_id', $agentId)
109		- ->where('conversation_type', 'response')
110		- ->groupBy('ticket_id')
111		- ->get()
112		- ->count();
	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();
113	192
114		- $total_closed = Ticket::where('agent_id', $agentId)->where('status', 'closed')->count();
115		-
116	193	return [
117		- 'replies_count' => [
118		- 'title' => 'Total Replies',
	194	+ 'replies_count' => [
	195	+ 'title' => __('Total Replies', 'fluent-support'),
119	196	'count' => $replies_count
120	197	],
121		- 'interactions_count' => [
122		- 'title' => 'Total Interactions',
123		- 'count' => $interactions_count
124		- ],
125		- 'total_closed' => [
126		- 'title' => 'Total Closed',
	198	+ 'total_closed' => [
	199	+ 'title' => __('Total Closed', 'fluent-support'),
127	200	'count' => $total_closed
128	201	]
129	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;
130	359	}
131	360
132	361	}