fluent-support / app / Modules / Reporting / Reporting.php

Reporting.php in Fluent Support – Helpdesk & Customer Support Ticket System 1.5.3, at app/Modules/Reporting/Reporting.php

286 lines 9.1 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\App\Modules\Reporting;
4
5	use FluentSupport\App\Models\Agent;
6	use FluentSupport\App\Models\Conversation;
7	use FluentSupport\App\Models\Ticket;
8
9	class Reporting
10	{
11	use ReportingHelperTrait;
12
13	public function getTicketsGrowth($from = false, $to = false, $filters = [])
14	{
15	$period = $this->makeDatePeriod(
16	$from = $this->makeFromDate($from),
17	$to = $this->makeToDate($to),
18	$frequency = $this->getFrequency($from, $to)
19	);
20
21	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
22
23
24	$query = $this->db()->table('fs_tickets')
25	->select($this->prepareSelect($frequency))
26	->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
27	->groupBy($groupBy)
28	->orderBy($orderBy, 'ASC');
29
30	if ($filters) {
31	if (!empty($filters['statuses'])) {
32	$query->whereIn('status', $filters['statuses']);
33	}
34
35	if (!empty($filters['product_id'])) {
36	$query->where('product_id', $filters['product_id']);
37	}
38
39	if (!empty($filters['agent_id'])) {
40	$query->where('agent_id', $filters['agent_id']);
41	}
42	}
43
44	$items = $query->get();
45
46	return $this->getResult($period, $items);
47	}
48
49	public function getTicketResolveGrowth($from = false, $to = false, $filters = [])
50	{
51	$period = $this->makeDatePeriod(
52	$from = $this->makeFromDate($from),
53	$to = $this->makeToDate($to),
54	$frequency = $this->getFrequency($from, $to)
55	);
56
57	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
58
59	$query = $this->db()->table('fs_tickets')
60	->select($this->prepareSelect($frequency, 'resolved_at'))
61	->whereBetween('resolved_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
62	->where('status', 'closed')
63	->groupBy($groupBy)
64	->orderBy($orderBy, 'ASC');
65
66	if ($filters) {
67	if (!empty($filters['product_id'])) {
68	$query->where('product_id', $filters['product_id']);
69	}
70
71	if (!empty($filters['agent_id'])) {
72	$query->where('agent_id', $filters['agent_id']);
73	}
74	}
75
76	$items = $query->get();
77
78	return $this->getResult($period, $items);
79	}
80
81	public function getResponseGrowth($from = false, $to = false, $filters = [])
82	{
83	$period = $this->makeDatePeriod(
84	$from = $this->makeFromDate($from),
85	$to = $this->makeToDate($to),
86	$frequency = $this->getFrequency($from, $to)
87	);
88
89	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
90
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');
97
98	if ($filters) {
99	if (!empty($filters['person_id'])) {
100	$query->where('person_id', $filters['person_id']);
101	}
102	}
103
104	$items = $query->get();
105
106	return $this->getResult($period, $items);
107	}
108
109	public function agentSummary($from = false, $to = false, $agent = false)
110	{
111	if(!$from) {
112	$from = current_time('mysql');
113	}
114
115	if(!$to) {
116	$to = date('Y-m-d', current_time('timestamp') + 86400);
117	} else {
118	$to = date('Y-m-d', strtotime($to) + 86400);
119	}
120
121	$from = $this->makeFromDate($from);
122	$to = $this->makeToDate($to);
123
124	$reports = [];
125
126	$resolves = $this->db()->table('fs_tickets')
127	->select([
128	$this->db()->raw('COUNT(id) AS count'),
129	'agent_id'
130	])
131	->whereBetween('resolved_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
132	->groupBy('agent_id')
133	->where('status', 'closed')
134	->get();
135
136	$reports = $this->pushAgentsReport('closed', $resolves, $reports);
137
138	$openTickets = $this->db()->table('fs_tickets')
139	->select([
140	$this->db()->raw('COUNT(id) AS count'),
141	'agent_id'
142	])
143	->groupBy('agent_id')
144	->where('status', '!=', 'closed')
145	->get();
146
147	$reports = $this->pushAgentsReport('opens', $openTickets, $reports);
148
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();
160
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();
170	}
171
172	$agentIds = array_keys($reports);
173
174	if ($agent) {
175	$agentIds = array_map('intval', explode(',', $agent));
176	}
177
178	$agents = Agent::select(['id', 'first_name', 'last_name'])
179	->whereIn('id', $agentIds)
180	->get();
181
182	foreach ($agents as $agent) {
183	$report = wp_parse_args($reports[$agent->id], [
184	'interactions' => 0,
185	'responses' => 0,
186	'opens' => 0,
187	'closed' => 0,
188	'waiting_tickets' => 0
189	]);
190	$agent->stats = $report;
191	$agent->active_stat = $this->getActiveStatByAgent($agent->id);
192	}
193
194	return $agents;
195	}
196
197	private function pushAgentsReport($type, $tickets, $reports)
198	{
199	foreach ($tickets as $ticket) {
200	if(!$ticket->agent_id) {
201	continue;
202	}
203
204	if(!isset($reports[$ticket->agent_id])) {
205	$reports[$ticket->agent_id] = [];
206	}
207
208	$reports[$ticket->agent_id][$type] = $ticket->count;
209	}
210
211	return $reports;
212	}
213
214	public function getActiveStats()
215	{
216	// We will calculate the wait times for open waiting tickets
217	$waitStat = Ticket::waitingOnly()
218	->where('status', '!=', 'closed')
219	->whereNotNull('waiting_since')
220	->select([
221	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
222	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
223	$this->db()->raw('COUNT(*) as total_tickets')
224	])
225	->first();
226
227	if(!$waitStat) {
228	return false;
229	}
230
231	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
232	if($waitStat->avg_waiting > 0) {
233	$waitSeconds = time() - $waitStat->avg_waiting;
234	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
235	$avgWait = ceil($waitSeconds / 3600) . ' hours';
236	} else {
237	$avgWait = human_time_diff($waitStat->avg_waiting, time());
238	}
239	} else {
240	$avgWait = 0;
241	}
242
243	return [
244	'average_waiting' => $avgWait,
245	'max_waiting' => (intval($waitStat->max_waiting)) ? human_time_diff(intval($waitStat->max_waiting), time()) : 0,
246	'waiting_tickets' => $waitStat->total_tickets
247	];
248	}
249
250	public function getActiveStatByAgent($agentId)
251	{
252	$waitStat = Ticket::waitingOnly()
253	->where('status', '!=', 'closed')
254	->whereNotNull('waiting_since')
255	->where('agent_id', $agentId)
256	->select([
257	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
258	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
259	$this->db()->raw('COUNT(*) as total_tickets')
260	])
261	->first();
262
263	if(!$waitStat) {
264	return false;
265	}
266
267	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
268	if($waitStat->avg_waiting > 0) {
269	$waitSeconds = time() - $waitStat->avg_waiting;
270	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
271	$avgWait = ceil($waitSeconds / 3600) . ' hours';
272	} else {
273	$avgWait = human_time_diff($waitStat->avg_waiting, time());
274	}
275	} else {
276	$avgWait = 0;
277	}
278
279	return [
280	'average_waiting' => $avgWait,
281	'max_waiting' => (intval($waitStat->max_waiting)) ? human_time_diff(intval($waitStat->max_waiting), time()) : 0,
282	'waiting_tickets' => $waitStat->total_tickets
283	];
284	}
285	}
286