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

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

346 lines 11.2 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	/**
10	* Reporting class is responsible for getting data related to report
11	* @package FluentSupport\App\Modules\Reporting
12	*
13	* @version 1.0.0
14	*/
15	class Reporting
16	{
17	use ReportingHelperTrait;
18
19	/**
20	* getTicketsGrowth will generate tickets statistics and return
21	* @param false $from
22	* @param false $to
23	* @param array $filters
24	* @return array
25	*/
26	public function getTicketsGrowth($from = false, $to = false, $filters = [])
27	{
28	//Generate report period
29	$period = $this->makeDatePeriod(
30	$from = $this->makeFromDate($from),//Date from
31	$to = $this->makeToDate($to),//Date to
32	$frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M
33	);
34
35	//Get group by and order by i.e date,week, month
36	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
37
38	//get all tickets statistics within the date range
39	$query = $this->db()->table('fs_tickets')
40	->select($this->prepareSelect($frequency))
41	->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
42	->groupBy($groupBy)
43	->orderBy($orderBy, 'ASC');
44
45	//If filter by product or agent or status selected
46	if ($filters) {
47	if (!empty($filters['statuses'])) {
48	$query->whereIn('status', $filters['statuses']);
49	}
50
51	if (!empty($filters['product_id'])) {
52	$query->where('product_id', $filters['product_id']);
53	}
54
55	if (!empty($filters['agent_id'])) {
56	$query->where('agent_id', $filters['agent_id']);
57	}
58	}
59
60	$items = $query->get();
61
62	return $this->getResult($period, $items);
63	}
64
65	/**
66	* getTicketResolveGrowth method will get the statistics for resolved/closed tickets
67	* @param false $from
68	* @param false $to
69	* @param array $filters
70	* @return array
71	*/
72	public function getTicketResolveGrowth($from = false, $to = false, $filters = [])
73	{
74	$period = $this->makeDatePeriod(
75	$from = $this->makeFromDate($from),//Date from
76	$to = $this->makeToDate($to),//date to
77	$frequency = $this->getFrequency($from, $to)// frequency P1D, P1W, P1M
78	);
79
80	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);//Get group by and order by i.e date,week, month
81
82	//get the closed ticket statistics within the date range
83	$query = $this->db()->table('fs_tickets')
84	->select($this->prepareSelect($frequency, 'resolved_at'))
85	->whereBetween('resolved_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
86	->where('status', 'closed')
87	->groupBy($groupBy)
88	->orderBy($orderBy, 'ASC');
89
90	//If filter by product or agent is selected
91	if ($filters) {
92	if (!empty($filters['product_id'])) {
93	$query->where('product_id', $filters['product_id']);
94	}
95
96	if (!empty($filters['agent_id'])) {
97	$query->where('agent_id', $filters['agent_id']);
98	}
99	}
100
101	$items = $query->get();
102
103	return $this->getResult($period, $items);
104	}
105
106	/**
107	* getResponseGrowth method will generate the statistics for response
108	* @param false $from
109	* @param false $to
110	* @param array $filters
111	* @return array
112	*/
113	public function getResponseGrowth($from = false, $to = false, $filters = [])
114	{
115	$period = $this->makeDatePeriod(
116	$from = $this->makeFromDate($from),
117	$to = $this->makeToDate($to),
118	$frequency = $this->getFrequency($from, $to)
119	);
120
121	list($groupBy, $orderBy) = $this->getGroupAndOrder($frequency);
122
123	$query = $this->db()->table('fs_conversations')
124	->select($this->prepareSelect($frequency, 'created_at'))
125	->whereBetween('created_at', [$from->format('Y-m-d'), $to->format('Y-m-d')])
126	->where('conversation_type', 'response')
127	->groupBy($groupBy)
128	->orderBy($orderBy, 'ASC');
129
130	if ($filters) {
131	if (!empty($filters['person_id'])) {
132	$query->where('person_id', $filters['person_id']);
133	}
134	}
135
136	$items = $query->get();
137
138	return $this->getResult($period, $items);
139	}
140
141	/**
142	* agentSummary method will prepare ticket summary with responses by agent
143	* @param false $from
144	* @param false $to
145	* @param false $agent
146	* @return mixed
147	*/
148	public function agentSummary($from = false, $to = false, $agent = false)
149	{
150	if(!$from) {
151	$from = current_time('Y-m-d');
152	}
153
154	if(!$to) {
155	$to = current_time('Y-m-d');
156	}
157
158	$from .= ' 00:00:00';
159	$to .= ' 23:59:59';
160	$reports = [];
161
162	//Get tickets statistics that are closed
163	$resolves = $this->db()->table('fs_tickets')
164	->select([
165	$this->db()->raw('COUNT(id) AS count'),
166	'agent_id',
167	])
168	->groupBy('agent_id')
169	->where('status', 'closed')
170	->whereBetween('resolved_at', [$from, $to])
171	->get();
172
173	$reports = $this->pushAgentsReport('closed', $resolves, $reports);
174
175	//get statistics for all except closed ticket
176	$openTickets = $this->db()->table('fs_tickets')
177	->select([
178	$this->db()->raw('COUNT(id) AS count'),
179	'agent_id'
180	])
181	->groupBy('agent_id')
182	->where('status', '!=', 'closed')
183	->get();
184
185	$reports = $this->pushAgentsReport('opens', $openTickets, $reports);
186
187	//Get response by agent
188	$responses = Conversation::select([
189	$this->db()->raw('COUNT(id) AS count'),
190	$this->db()->raw('person_id as agent_id'),
191	$this->db()->raw('created_at')
192	])
193	->whereHas('person', function ($q) {
194	$q->where('person_type', '=', 'agent');
195	})
196	->whereBetween('created_at', [$from, $to])
197	->where('conversation_type', 'response')
198	->groupBy('agent_id')
199	->get();
200
201	$reports = $this->pushAgentsReport('responses', $responses, $reports);
202
203	//Get interactions/responses by individual agents
204	foreach ($responses as $response) {
205	$reports[$response->agent_id]['interactions'] = Conversation::where('person_id', $response->agent_id)
206	->where('conversation_type', 'response')
207	->whereBetween('created_at', [$from, $to])
208	->groupBy('ticket_id')
209	->get()
210	->count();
211	}
212
213	$agentIds = array_keys($reports);
214
215	if ($agent) {
216	$agentIds = array_map('intval', explode(',', $agent));
217	}
218
219	$agents = Agent::select(['id', 'first_name', 'last_name'])
220	->whereIn('id', $agentIds)
221	->get();
222
223	foreach ($agents as $agent) {
224	$report = wp_parse_args($reports[$agent->id], [
225	'interactions' => 0,
226	'responses' => 0,
227	'opens' => 0,
228	'closed' => 0,
229	'waiting_tickets' => 0
230	]);
231	$agent->stats = $report;
232	$agent->active_stat = $this->getActiveStatByAgent($agent->id);
233	}
234
235	return $agents;
236	}
237
238
239	/**
240	* pushAgentsReport method will format the ticket summary report by agent
241	* @param $type
242	* @param $tickets
243	* @param $reports
244	* @return array
245	*/
246	private function pushAgentsReport($type, $tickets, $reports)
247	{
248	foreach ($tickets as $ticket) {
249	if(!$ticket->agent_id) {
250	continue;
251	}
252
253	if(!isset($reports[$ticket->agent_id])) {
254	$reports[$ticket->agent_id] = [];
255	}
256
257	$reports[$ticket->agent_id][$type] = $ticket->count;
258	}
259
260	return $reports;
261	}
262
263	/**
264	* getActiveStats method will return the statistics for active tickets
265	* This method will get the list of open tickets calculate the wait times and return results
266	* @return array\|false
267	*/
268	public function getActiveStats()
269	{
270	// We will calculate the wait times for open waiting tickets
271	$waitStat = Ticket::waitingOnly()
272	->where('status', '!=', 'closed')
273	->whereNotNull('waiting_since')
274	->select([
275	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
276	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
277	$this->db()->raw('COUNT(*) as total_tickets')
278	])
279	->first();
280
281	if(!$waitStat) {
282	return false;
283	}
284
285	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
286	if($waitStat->avg_waiting > 0) {
287	$waitSeconds = time() - $waitStat->avg_waiting;
288	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
289	$avgWait = ceil($waitSeconds / 3600) . ' hours';
290	} else {
291	$avgWait = human_time_diff($waitStat->avg_waiting, time());
292	}
293	} else {
294	$avgWait = 0;
295	}
296
297	return [
298	'average_waiting' => $avgWait,
299	'max_waiting' => (intval($waitStat->max_waiting)) ? human_time_diff(intval($waitStat->max_waiting), time()) : 0,
300	'waiting_tickets' => $waitStat->total_tickets
301	];
302	}
303
304	/**
305	* getActiveStatByAgent method will return the statistics of active tickets for an agent
306	* This method will get agent id as parameter, fetch the list of open tickets by agent id, calculate the wait times and return results
307	* @param $agentId
308	* @return array\|false
309	*/
310	public function getActiveStatByAgent($agentId)
311	{
312	$waitStat = Ticket::waitingOnly()
313	->where('status', '!=', 'closed')
314	->whereNotNull('waiting_since')
315	->where('agent_id', $agentId)
316	->select([
317	$this->db()->raw('avg(UNIX_TIMESTAMP(waiting_since)) as avg_waiting'),
318	$this->db()->raw('MIN(UNIX_TIMESTAMP(waiting_since)) as max_waiting'),
319	$this->db()->raw('COUNT(*) as total_tickets')
320	])
321	->first();
322
323	if(!$waitStat) {
324	return false;
325	}
326
327	$waitStat->avg_waiting = intval($waitStat->avg_waiting);
328	if($waitStat->avg_waiting > 0) {
329	$waitSeconds = time() - $waitStat->avg_waiting;
330	if( $waitSeconds < 172800 && $waitSeconds > 7200) {
331	$avgWait = ceil($waitSeconds / 3600) . ' hours';
332	} else {
333	$avgWait = human_time_diff($waitStat->avg_waiting, time());
334	}
335	} else {
336	$avgWait = 0;
337	}
338
339	return [
340	'average_waiting' => $avgWait,
341	'max_waiting' => (intval($waitStat->max_waiting)) ? human_time_diff(intval($waitStat->max_waiting), time()) : 0,
342	'waiting_tickets' => $waitStat->total_tickets
343	];
344	}
345	}
346