← All changes | app/Modules/StatModule.php +235 -52 1.4.6 → 2.4.0 View file →

		@@ -1,13 +1,30 @@
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	+ * StatModule class is responsible for getting data related to report
	13	+ * @package FluentSupport\App\Modules
	14	+ *
	15	+ * @version 1.0.0
	16	+ */
8	17	class StatModule
9	18	{
	19	+ /**
	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
	22	+ * @param $agentId
	23	+ * @param false $startDate
	24	+ * @param false $endDate
	25	+ * @return array[]
	26	+ */
10	27	public static function getAgentStat($agentId, $startDate = false, $endDate = false)
11	28	{
12	29	if (!$startDate) {
13	30	$currentDate = current_time('mysql');
		@@ -14,119 +31,285 @@
14	31	$startDate = $currentDate;
15	32	$endDate = $currentDate;
16	33	}
17	34
18		- $startDate = date('Y-m-d 00:00.01', strtotime($startDate));
19		- $endDate = date('Y-m-d 23:59.59', strtotime($endDate));
	35	+ $startDate = gmdate('Y-m-d 00:00.01', strtotime($startDate));
	36	+ $endDate = gmdate('Y-m-d 23:59.59', strtotime($endDate));
20	37
21		- $newTickets = Ticket::where('agent_id', $agentId)
22		- ->where('status', 'new')
23		- ->count();
	38	+ $access = new AgentTicketAccess();
24	39
25		- $activeTickets = Ticket::where('agent_id', $agentId)
26		- ->where('status', 'active')
27		- ->count();
	40	+ //Get list of new ticket by agent
	41	+ $newTickets = $access->applyMailboxRestrictionScope(
	42	+ Ticket::where('agent_id', $agentId)->where('status', 'new')
	43	+ )->count();
28	44
29		- $closedTickets = Ticket::where('agent_id', $agentId)
30		- ->where('status', 'closed')
31		- ->whereBetween('resolved_at', [$startDate, $endDate])
32		- ->count();
	45	+ //Get list of active ticket by agent
	46	+ $activeTickets = $access->applyMailboxRestrictionScope(
	47	+ Ticket::where('agent_id', $agentId)->where('status', 'active')
	48	+ )->count();
33	49
34		- $responses = Conversation::where('conversation_type', 'response')
35		- ->where('person_id', $agentId)
36		- ->whereBetween('created_at', [$startDate, $endDate])
37		- ->count();
	50	+ //Get list of closed ticket by agent within a date range(default today)
	51	+ $closedTickets = $access->applyMailboxRestrictionScope(
	52	+ Ticket::where('agent_id', $agentId)
	53	+ ->where('status', 'closed')
	54	+ ->whereBetween('resolved_at', [$startDate, $endDate])
	55	+ )->count();
38	56
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();
	57	+ //Get list of response by agent id within a date range(default today)
	58	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	59	+ Conversation::where('conversation_type', 'response')
	60	+ ->where('person_id', $agentId)
	61	+ ->whereBetween('created_at', [$startDate, $endDate])
	62	+ )->count();
45	63
	64	+ //Count the response in tickets within a date range(default today) for agent
	65	+ $interactions = $access->applyMailboxRestrictionScopeViaTicket(
	66	+ Conversation::where('person_id', $agentId)
	67	+ ->where('conversation_type', 'response')
	68	+ ->whereBetween('created_at', [$startDate, $endDate])
	69	+ ->groupBy('ticket_id')
	70	+ )->get()->count();
	71	+
46	72	return [
47	73	'new_tickets' => [
48		- 'title' => 'New Tickets',
	74	+ 'title' => __('New Tickets', 'fluent-support'),
49	75	'count' => $newTickets
50	76	],
51	77	'active_tickets' => [
52		- 'title' => 'Active Tickets',
	78	+ 'title' => __('Active Tickets', 'fluent-support'),
53	79	'count' => $activeTickets
54	80	],
55	81	'closed_tickets' => [
56		- 'title' => 'Closed Tickets',
	82	+ 'title' => __('Closed Tickets', 'fluent-support'),
57	83	'count' => $closedTickets
58	84	],
59	85	'responses' => [
60		- 'title' => 'Responses',
	86	+ 'title' => __('Responses', 'fluent-support'),
61	87	'count' => $responses
62	88	],
63		- 'interactions' =>[
64		- 'title' =>'Interactions',
	89	+ 'interactions' => [
	90	+ 'title' => __('Interactions', 'fluent-support'),
65	91	'count' => $interactions
66	92	]
67	93	];
68	94	}
69	95
	96	+ /**
	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	+ * @return array[]
	100	+ */
70	101	public static function getOverAllStats()
71	102	{
72		- $newTickets = Ticket::where('status', 'new')
	103	+ $access = new AgentTicketAccess();
	104	+
	105	+ // Same mailbox boundary as the ticket list; these totals are agent-facing.
	106	+ $newTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'new'))
73	107	->count();
74	108
75		- $activeTickets = Ticket::where('status', 'active')
	109	+ $activeTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'active'))
76	110	->count();
77	111
78		- $closedTickets = Ticket::where('status', 'closed')
	112	+ $closedTickets = $access->applyMailboxRestrictionScope(Ticket::where('status', 'closed'))
79	113	->count();
80	114
81		- $responses = Conversation::where('conversation_type', 'response')
82		- ->count();
	115	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	116	+ Conversation::where('conversation_type', 'response')
	117	+ )->count();
83	118
84	119	return [
85	120	'new_tickets' => [
86		- 'title' => 'New Tickets',
	121	+ 'title' => __('New Tickets', 'fluent-support'),
87	122	'count' => $newTickets
88	123	],
89	124	'active_tickets' => [
90		- 'title' => 'Active Tickets',
	125	+ 'title' => __('Active Tickets', 'fluent-support'),
91	126	'count' => $activeTickets
92	127	],
93	128	'closed_tickets' => [
94		- 'title' => 'Closed Tickets',
	129	+ 'title' => __('Closed Tickets', 'fluent-support'),
95	130	'count' => $closedTickets
96	131	],
97	132	'responses' => [
98		- 'title' => 'Responses',
	133	+ 'title' => __('Responses', 'fluent-support'),
99	134	'count' => $responses
100	135	]
101	136	];
102	137	}
103	138
	139	+ /**
	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
	145	+ */
	146	+ public static function getTodayStats($agentId = false, ?AgentTicketAccess $access = null)
	147	+ {
	148	+ $start = gmdate('Y-m-d 00:00.01');
	149	+ $end = gmdate('Y-m-d 23:59.59');
	150	+
	151	+ $access = $access ?: new AgentTicketAccess();
	152	+
	153	+ $newTickets = $access->applyMailboxRestrictionScope(
	154	+ Ticket::whereBetween('created_at', [$start, $end])
	155	+ );
	156	+
	157	+ $closedTickets = $access->applyMailboxRestrictionScope(
	158	+ Ticket::where('status', 'closed')->whereBetween('resolved_at', [$start, $end])
	159	+ );
	160	+
	161	+ $responses = $access->applyMailboxRestrictionScopeViaTicket(
	162	+ Conversation::where('conversation_type', 'response')
	163	+ ->whereHas('person', function ($q) {
	164	+ $q->where('person_type', 'agent');
	165	+ })
	166	+ ->whereBetween('created_at', [$start, $end])
	167	+ );
	168	+
	169	+ if ($agentId) {
	170	+ $newTickets->where('agent_id', $agentId);
	171	+ $closedTickets->where('agent_id', $agentId);
	172	+ $responses->where('person_id', $agentId);
	173	+ }
	174	+
	175	+ return [
	176	+ 'new_tickets' => [
	177	+ 'title' => __('New Tickets', 'fluent-support'),
	178	+ 'count' => $newTickets->count()
	179	+ ],
	180	+ 'closed_tickets' => [
	181	+ 'title' => __('Closed Tickets', 'fluent-support'),
	182	+ 'count' => $closedTickets->count()
	183	+ ],
	184	+ 'responses' => [
	185	+ 'title' => __('Responses', 'fluent-support'),
	186	+ 'count' => $responses->count()
	187	+ ]
	188	+ ];
	189	+ }
	190	+
	191	+ /**
	192	+ * getAgentOverallStats method will produce overall statistics by agent
	193	+ * @param $agentId
	194	+ * @return array[]
	195	+ */
104	196	public static function getAgentOverallStats($agentId)
105	197	{
106		- $replies_count = Conversation::where('person_id', $agentId)->count();
	198	+ //Get count of response by the agent
	199	+ $replies_count = (new AgentTicketAccess())->applyMailboxRestrictionScopeViaTicket(
	200	+ Conversation::where('person_id', $agentId)
	201	+ )->count();
107	202
108		- $interactions_count = Conversation::where('person_id', $agentId)
109		- ->where('conversation_type', 'response')
110		- ->groupBy('ticket_id')
111		- ->get()
112		- ->count();
	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();
113	209
114		- $total_closed = Ticket::where('agent_id', $agentId)->where('status', 'closed')->count();
	210	+ //Get the number of tickets that are closed by this agent
	211	+ $total_closed = (new AgentTicketAccess())->applyMailboxRestrictionScope(
	212	+ Ticket::where('agent_id', $agentId)->where('status', 'closed')
	213	+ )->count();
115	214
116	215	return [
117		- 'replies_count' => [
118		- 'title' => 'Total Replies',
	216	+ 'replies_count' => [
	217	+ 'title' => __('Total Replies', 'fluent-support'),
119	218	'count' => $replies_count
120	219	],
121		- 'interactions_count' => [
122		- 'title' => 'Total Interactions',
123		- 'count' => $interactions_count
124		- ],
125		- 'total_closed' => [
126		- 'title' => 'Total Closed',
	220	+ // 'interactions_count' => [
	221	+ // 'title' => __('Total Interactions', 'fluent-support'),
	222	+ // 'count' => $interactions_count
	223	+ // ],
	224	+ 'total_closed' => [
	225	+ 'title' => __('Total Closed', 'fluent-support'),
127	226	'count' => $total_closed
128	227	]
129	228	];
	229	+ }
	230	+
	231	+ /**
	232	+ * This `getActiveTicketsByProductStats` method will count today's tickets by product that are waiting for reply
	233	+ * @return array $result
	234	+ */
	235	+ public static function getActiveTicketsByProductStats()
	236	+ {
	237	+ $products = Product::all();
	238	+ $result = [];
	239	+ $access = new AgentTicketAccess();
	240	+
	241	+ foreach ($products as $product) {
	242	+ $result[$product->id] = [
	243	+ 'title' => $product->title,
	244	+ 'count' => static::countAwaitingTickets('product_id', $product->id, $access)
	245	+ ];
	246	+ }
	247	+
	248	+ return $result;
	249	+ }
	250	+
	251	+
	252	+ /**
	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
	256	+ * @return int $awatingTicketCount
	257	+ */
	258	+ public static function countAwaitingTickets($whereClause = null, $whereClauseValue = null, ?AgentTicketAccess $access = null)
	259	+ {
	260	+ // Must be a builder: ->where() on a bare model returns a new one, losing the scope.
	261	+ $ticket = Ticket::query();
	262	+
	263	+ if ($whereClause && $whereClauseValue) {
	264	+ $ticket = $ticket->where(sanitize_text_field($whereClause), sanitize_text_field($whereClauseValue));
	265	+ }
	266	+
	267	+ $ticket = ($access ?: new AgentTicketAccess())->applyMailboxRestrictionScope($ticket);
	268	+
	269	+ $awatingTicketCount = $ticket->where('status', '!=', 'closed')
	270	+ ->where(function ($query) {
	271	+ $query->whereColumn('last_agent_response', '<', 'last_customer_response')
	272	+ ->orWhereNull('last_agent_response')
	273	+ ->orWhere('status', 'new');
	274	+ })->count();
	275	+
	276	+ return $awatingTicketCount;
	277	+ }
	278	+
	279	+ /**
	280	+ * This method will return a summary of today's stats of all agent
	281	+ * @return array
	282	+ */
	283	+ public static function getAgentTodayStats()
	284	+ {
	285	+ $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	+
	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	+ ];
	309	+ }
	310	+ });
	311	+
	312	+ return $stats;
130	313	}
131	314
132	315	}