fluent-support / app / Models / Ticket.php

Ticket.php in Fluent Support – Helpdesk & Customer Support Ticket System 1.5.4, at app/Models/Ticket.php

796 lines 24.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\App\Models;
4
5	use FluentSupport\App\Services\Helper;
6	use FluentSupport\Framework\Support\Arr;
7
8	class Ticket extends Model
9	{
10	protected $table = 'fs_tickets';
11
12	/**
13	* The attributes that are mass assignable.
14	*
15	* @var array
16	*/
17	protected $fillable = [
18	'customer_id',
19	'agent_id',
20	'product_id',
21	'mailbox_id',
22	'product_source',
23	'privacy',
24	'priority',
25	'client_priority',
26	'status',
27	'title',
28	'slug',
29	'hash',
30	'source',
31	'message_id',
32	'content',
33	'last_agent_response',
34	'last_customer_response',
35	'waiting_since',
36	'response_count',
37	'first_response_time',
38	'total_close_time',
39	'resolved_at',
40	'closed_by'
41	];
42
43	public static function boot()
44	{
45	static::creating(function ($model) {
46	$model->slug = static::slugify($model->title);
47	$model->hash = substr(md5(time() . wp_generate_uuid4()), 0, 8) . mt_rand(1, 99);
48	$model->last_customer_response = current_time('mysql');
49	$model->content_hash = md5($model->content);
50	$model->created_at = current_time('mysql');
51	$model->updated_at = current_time('mysql');
52	$model->waiting_since = current_time('mysql');
53	});
54	}
55
56	/**
57	* $searchable Columns in table to search
58	* @var array
59	*/
60	protected $searchable = [
61	'content',
62	'title',
63	'slug',
64	'id'
65	];
66
67	/**
68	* Local scope to filter tickets by search/query string
69	* @param ModelQueryBuilder $query
70	* @param string $search
71	* @return ModelQueryBuilder
72	*/
73	public function scopeSearchBy($query, $search)
74	{
75	if (strpos($search, ':')) {
76	$array = explode(':', $search);
77	$column = $array[0];
78	$value = $array[1];
79	$columns = $this->fillable;
80	$columns[] = 'id';
81
82	if (in_array($column, $columns) && $value) {
83	if (is_numeric($value)) {
84	$query->where($column, $value);
85	} else {
86	$query->where($column, 'LIKE', "%$value%");
87	}
88	return $query;
89	}
90	}
91
92	$fields = $this->searchable;
93	$query->where(function ($query) use ($fields, $search) {
94	$query->where(array_shift($fields), 'LIKE', "%$search%");
95	foreach ($fields as $field) {
96	$query->orWhere($field, 'LIKE', "%$search%");
97	}
98	});
99
100	return $query;
101	}
102
103	/**
104	* Local scope to filter tickets by different filtering condition
105	* @param ModelQueryBuilder $query
106	* @param mixed $search
107	* @return ModelQueryBuilder
108	*/
109
110	public function doSearchForAdvancedFilter($query, $search)
111	{
112	foreach ($search as $s) {
113	$operator = $s['operator'];
114	//If selected item for ticket either title or content
115	if(in_array($s['property'], ['title','content' ])){
116	//If the selected condition is contains, query operator id LIKE
117	if ($operator == 'contains') {
118	$query = $query->where(function ($query) use ($s) {
119	$query->where($s['property'], 'LIKE', "%".$s['value']."%");
120	});
121	} elseif ($operator == 'not_contains') {
122	//If the selected condition is not_contains, query operator id NOT LIKE
123	$query = $query->where(function ($query) use ($s){
124	$query->where($s['property'], 'NOT LIKE', '%'.$s['value'].'%');
125	});
126	}
127	}
128
129	//If selected item is Ticket Conversation Content
130	if($s['property'] == 'conversation_content'){
131	$operator = $s['operator'];
132	if($operator == 'contains') {
133	$query = $query->whereHas('responses', function ($q) use ($s) {
134	$q->where('content', 'LIKE', "%".$s['value']."%");
135	});
136
137	} else if ($operator == 'not_contains') {
138	$query = $query->whereHas('responses', function ($q) use ($s) {
139	$q->where('content', 'NOT LIKE', "%".$s['value']."%");
140	});
141	}
142	}
143
144	//If selected item is Ticket created or Last Response or Customer Waiting For, or Last Agent Response or Last Customer Response
145	if(in_array($s['property'], ['created_at', 'updated_at', 'waiting_since', 'last_agent_response', 'last_customer_response'])){
146	$query = (new \FluentSupport\App\Models\Ticket())->buildDateBaseFilterQuery($query, $s);
147	}
148
149	//If selected item is Ticket Status or Client Priority or Agent Priority or Tags or Product or Waiting For Reply
150	if(in_array($s['property'], ['status', 'client_priority', 'priority', 'tags', 'product', 'waiting_for_reply', 'agent_id', 'mailbox_id'])){
151	$query = (new \FluentSupport\App\Models\Ticket())->buildPropertiesFilterQuery($query, $s);
152	}
153	}
154	return $query;
155	}
156
157	/**
158	* Local scope to filter subscribers by search/query string
159	* @param ModelQueryBuilder $query
160	* @param array $statuses
161	* @return ModelQueryBuilder
162	*/
163	public function scopeFilterByStatues($query, $statuses)
164	{
165	if ($statuses) {
166	$query->whereIn('status', $statuses);
167	}
168
169	return $query;
170	}
171
172	public function scopeWaitingOnly($query)
173	{
174	$query->where(function ($q){
175	$q->whereColumn('last_agent_response', '<' ,'last_customer_response')
176	->orWhereNull('last_agent_response')
177	->orWhere('status', 'new');
178	});
179	return $query;
180	}
181
182	public function scopeApplyFilters($query, $filters)
183	{
184	$supportedColumns = ['product_id', 'client_priority', 'priority', 'mailbox_id'];
185	foreach ($filters as $filterKey => $filterValue) {
186	if (!$filterValue && ($filterValue !== '0' \|\| $filterValue !== 0)) {
187	continue;
188	}
189	if ($filterKey == 'status_type') {
190	$statusArray = Helper::getTkStatusesByGroupName($filterValue);
191	if ($statusArray) {
192	$query->whereIn('status', $statusArray);
193	}
194	} else if (in_array($filterKey, $supportedColumns)) {
195	$query->where($filterKey, $filterValue);
196	} else if ($filterKey == 'waiting_for_reply') {
197	if ($filterValue != 'yes') {
198	continue;
199	}
200	$query = $this->scopeWaitingOnly($query);
201	} else if ($filterKey == 'agent_id') {
202	if ($filterValue == 'unassigned') {
203	$query->whereNull($filterKey);
204	} else {
205	$query->where($filterKey, $filterValue);
206	}
207	} else if ($filterKey == 'ticket_tags') {
208	if (!$filterValue) {
209	continue;
210	}
211	$query->whereHas('tags', function ($q) use ($filterValue) {
212	$q->whereIn('tag_id', $filterValue);
213	});
214	}
215	}
216
217	return $query;
218	}
219
220	/**
221	* Local scope to filter tickets by agent id
222	* @param ModelQueryBuilder $query
223	* @param int $agentId
224	* @return ModelQueryBuilder
225	*/
226	public function scopeFilterByAgentId($query, $agentId)
227	{
228	if ($agentId) {
229	$query->where('agent_id', $agentId);
230	}
231
232	return $query;
233	}
234
235	/**
236	* Local scope to filter subscribers by search/query string
237	* @param ModelQueryBuilder $query
238	* @param int $customerId
239	* @return ModelQueryBuilder
240	*/
241	public function scopeFilterByCustomerId($query, $customerId)
242	{
243	$query->where('customer_id', $customerId);
244
245	return $query;
246	}
247
248	/**
249	* Local scope to filter subscribers by search/query string
250	* @param ModelQueryBuilder $query
251	* @param int $productId
252	* @return ModelQueryBuilder
253	*/
254	public function scopeFilterByProductId($query, $productId)
255	{
256	if ($productId) {
257	$query->where('product_id', $productId);
258	}
259
260	return $query;
261	}
262
263	/**
264	* Local scope to filter subscribers by search/query string
265	* @param ModelQueryBuilder $query
266	* @param array $priorities
267	* @return ModelQueryBuilder
268	*/
269	public function scopeFilterByPriorities($query, $priorities)
270	{
271	if ($priorities) {
272	$query->whereIn('priority', $priorities);
273	}
274
275	return $query;
276	}
277
278	/**
279	* @param $filter
280	* @return string[]
281	*/
282
283	public static function parseRelationalFilterQueryMethods($filter)
284	{
285	// default operator = in
286	$method = 'whereHas';
287	$subMethod = 'whereIn';
288
289	switch ($filter['operator']) {
290	case 'not_in':
291	$method = 'whereDoesntHave';
292	$subMethod = 'whereIn';
293
294	break;
295	case 'in_all':
296	$method = 'whereHas';
297	$subMethod = 'where';
298
299	break;
300	case 'not_in_all':
301	$method = 'whereDoesntHave';
302	$subMethod = 'where';
303
304	break;
305	}
306
307	return [$method, $subMethod];
308	}
309
310	/**
311	* Parse filter to set proper operator and value for the filter query.
312	*
313	* @param array $filter
314	* @return array
315	*/
316	public static function filterParser($filter)
317	{
318	switch ($filter['operator']) {
319	case 'before':
320	$filter['operator'] = '<';
321	$filter['value'] = $filter['value'] . ' 23:59:59';
322	break;
323
324	case 'after':
325	$filter['operator'] = '>';
326	$filter['value'] = $filter['value'] . ' 23:59:59';
327	break;
328
329	case 'date_equal':
330	$filter['operator'] = 'LIKE';
331	$filter['value'] = '%' . $filter['value'] . '%';
332	break;
333
334	case 'days_before':
335	$filter['operator'] = '<';
336	$filter['value'] = date('Y-m-d', current_time('timestamp') - $filter['value'] * 24 * 60 * 60);
337	break;
338
339	case 'days_within':
340	$filter['operator'] = 'BETWEEN';
341	$filter['value'] = [
342	date('Y-m-d', current_time('timestamp') - $filter['value'] * 24 * 60 * 60),
343	date('Y-m-d') . ' 23:59:59'
344	];
345	break;
346	case 'date_range':
347	$filter['operator'] = 'BETWEEN';
348	if(isset($filter['value'][0]))
349	$filter['value'][0] .= ' 00:00:00';
350	if(isset($filter['value'][1]))
351	$filter['value'][1] .= ' 23:59:59';
352	break;
353	}
354
355	return $filter;
356	}
357
358	/**
359	* @param \FluentSupport\Framework\Database\Orm\Builder\|\FluentSupport\Framework\Database\Query\Builder $query
360	* @param array $filters
361	* @return \FluentSupport\App\Models\Ticket
362	*/
363	public function buildDateBaseFilterQuery($query, $filters)
364	{
365	$filter = static::filterParser($filters);
366	$query->where(function ($dateQuery) use ($filter) {
367
368	if ($filter['operator'] == 'BETWEEN') {
369	$dateQuery->whereBetween($filter['property'], $filter['value']);
370	} else {
371	$dateQuery->where($filter['property'], $filter['operator'], $filter['value']);
372	}
373	});
374
375	return $query;
376	}
377
378	/**
379	* Relation builder
380	* @param $relation
381	* @param $query
382	* @param $method
383	* @param $subMethod
384	* @param $subField
385	* @param $filter
386	* @param false $provider
387	* @return mixed
388	*/
389
390	public static function buildRelationFilterQuery($relation, $query, $method, $subMethod, $subField, $filter, $provider = false)
391	{
392	if (in_array($filter['operator'], ['in_all', 'not_in_all']) && $filter['value']) {
393	foreach ($filter['value'] as $item) {
394	$query = static::buildRelationFilterQuery($relation, $query, $method, $subMethod, $subField, ['value' => $item, 'operator' => ''], $provider);
395	}
396	} else {
397	$query = $query->{$method}($relation, function ($relationQuery) use ($subMethod, $subField, $filter, $provider) {
398	$relationQuery = $relationQuery->{$subMethod}($subField, $filter['value']);
399
400	if ($provider) {
401	$relationQuery = $relationQuery->where('provider', $provider);
402	}
403
404	return $relationQuery;
405	});
406	}
407
408	return $query;
409	}
410
411	/**
412	* get tickets by advanced filter segment data
413	* @param $query
414	* @param $filter
415	* @return ModelQueryBuilder
416	*/
417
418	public function buildPropertiesFilterQuery($query, $filter)
419	{
420	if (in_array($filter['property'], ['tags', 'product'])) {
421	$subField = $filter['property'] == 'tags' ? 'tag_id' : 'product_id';
422	list($method, $subMethod) = static::parseRelationalFilterQueryMethods($filter);
423	$query = static::buildRelationFilterQuery($filter['property'], $query, $method, $subMethod, $subField, $filter);
424	}
425
426	elseif ($filter['property'] == 'waiting_for_reply'){
427	if (($filter['value'] == 'yes' && $filter['operator'] == '=') \|\| ($filter['value'] == 'no' && $filter['operator'] == '!=')){
428	$query = $query->where(function ($q){
429	$q->whereColumn('last_agent_response', '<' ,'last_customer_response')
430	->orWhereNull('last_agent_response')
431	->orWhere('status', 'new');
432	});
433	} else {
434	$query = $query->where(function ($q) {
435	$q->whereColumn('last_customer_response', '<' ,'last_agent_response');
436	});
437	}
438	}
439
440	else {
441	$method = $filter['operator'] == 'in' ? 'whereIn' : 'whereNotIn';
442	$query = $query->{$method}($filter['property'], (array) $filter['value']);
443	}
444	return $query;
445	}
446
447	/**
448	* method to search by properties
449	* @param $provider
450	* @param $query
451	* @param $search
452	* @param string $operator
453	* @return mixed
454	*/
455	public function buildSearchableQuery($provider, $query, $search, $operator = 'LIKE')
456	{
457	$fields = $this->searchable;
458
459	$query->whereHas($provider, function ($query) use ($fields, $search, $operator) {
460	$query->where(array_shift($fields), $operator, $search);
461
462	$nameArray = explode(' ', $search);
463
464	if (count($nameArray) >= 2) {
465	$query->orWhere(function ($q) use ($nameArray, $operator) {
466	$firstName = array_shift($nameArray);
467	$lastName = implode(' ', $nameArray);
468
469	$q->where('first_name', $operator, $firstName);
470	$q->where('last_name', $operator, $lastName);
471	});
472	}
473
474	foreach ($fields as $field) {
475	$query->orWhere($field, $operator, $search);
476	}
477	});
478
479	return $query;
480	}
481
482	/**
483	* Filter by ticket general properties like customer name, agent name etc
484	* @param $query
485	* @param $filters
486	*/
487
488	public function filterTicketByUser($provider, $query, $filters)
489	{
490	foreach ($filters as $index=>$filter) {
491	if ($filter['operator']=='in' \|\| $filter['operator']=='not_in') {
492	$method = $filter['operator'] == 'in' ? 'whereIn' : 'whereNotIn';
493	$query = $query->whereHas($provider, function ($q) use ($method, $filter) {
494	$q->{$method}($filter['property'], $filter['value']);
495	});
496	}
497	elseif ($filter['operator'] == 'contains') {
498	$operator = 'LIKE';
499	$searchTerm = '%' . $filter['value'] . '%';
500	$query = $this->buildSearchableQuery($provider, $query, $searchTerm, $operator);
501	} elseif ($filter['operator'] == 'not_contains') {
502	$operator = 'NOT LIKE';
503	$searchTerm = '%' . $filter['value'] . '%';
504	$query = $this->buildSearchableQuery($provider, $query, $searchTerm, $operator);
505	}
506
507	if ($filter['operator'] == '=') {
508	$query->whereHas($provider, function ($q) use ($index, $filter) {
509	if ($index == 0){
510	$q->where($filter['property'], '=', $filter['value']);
511	}else{
512	$q->orWhere($filter['property'], '=', $filter['value']);
513	}
514	});
515	} elseif ($filter['operator'] == '!=') {
516	$query->whereHas($provider, function ($q) use ($index, $filter) {
517	if ($index == 0){
518	$q->where($filter['property'], '!=', $filter['value']);
519	}else{
520	$q->orWhere($filter['property'], '!=', $filter['value']);
521	}
522	});
523
524	}
525	return $query;
526	}
527	}
528	/**
529	* One2Many: Customer has to many Click Tickets
530	* @return Model Collection
531	*/
532	public function responses()
533	{
534	$class = __NAMESPACE__ . '\Conversation';
535
536	return $this->hasMany(
537	$class, 'ticket_id', 'id'
538	);
539	}
540
541	public function preview_response()
542	{
543	$class = __NAMESPACE__ . '\Conversation';
544
545	return $this->hasOne(
546	$class, 'ticket_id', 'id'
547	);
548	}
549
550	public function tags()
551	{
552	$class = __NAMESPACE__ . '\TicketTag';
553
554	return $this->belongsToMany(
555	$class, 'fs_tag_pivot', 'source_id', 'tag_id'
556	)->wherePivot('source_type', 'ticket_tag');
557	}
558
559
560	/**
561	* One2one: Customer has to many Click Tickets
562	* @return Model Collection
563	*/
564	public function customer()
565	{
566	$class = __NAMESPACE__ . '\Customer';
567
568	return $this->belongsTo(
569	$class, 'customer_id', 'id'
570	);
571	}
572
573	/**
574	* One2one: Customer has to many Click Tickets
575	* @return Model Collection
576	*/
577	public function agent()
578	{
579	$class = __NAMESPACE__ . '\Agent';
580
581	return $this->belongsTo(
582	$class, 'agent_id', 'id'
583	);
584	}
585
586	public function closed_by_person()
587	{
588	$class = __NAMESPACE__ . '\Person';
589
590	return $this->belongsTo(
591	$class, 'closed_by', 'id'
592	);
593	}
594
595	public function product()
596	{
597	$class = __NAMESPACE__ . '\Product';
598
599	return $this->belongsTo(
600	$class, 'product_id', 'id'
601	);
602	}
603
604	public function mailbox()
605	{
606	$class = __NAMESPACE__ . '\MailBox';
607
608	return $this->belongsTo(
609	$class, 'mailbox_id', 'id'
610	);
611	}
612
613
614	public function deleteTicket()
615	{
616	do_action('fluent_support/deleting_ticket', $this);
617	// delete the responses first
618	Conversation::where('ticket_id', $this->id)->delete();
619	// Delete the ticket meta
620	Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)->delete();
621
622	Ticket::where('id', $this->id)->delete();
623	}
624
625	public static function slugify($title)
626	{
627	$slug = sanitize_title($title, 'support-ticket-' . time(), 'display');
628	if (Ticket::where('slug', $slug)->first()) {
629	$slug .= '-' . time();
630	}
631	return $slug;
632	}
633
634	public function hasTag($tagId)
635	{
636	$tags = $this->tags;
637	foreach ($tags as $tag) {
638	if ($tag->id == $tagId) {
639	return true;
640	}
641	}
642
643	return false;
644	}
645
646	public function attachments()
647	{
648	$class = __NAMESPACE__ . '\Attachment';
649	return $this->hasMany($class, 'ticket_id', 'id')->where('conversation_id', NULL);
650	}
651
652	public function customData($scope = 'admin', $rendered = false)
653	{
654	if (!defined('FLUENTSUPPORTPRO')) {
655	return [];
656	}
657
658	$fields = \FluentSupportPro\App\Services\CustomFieldsService::getFieldLabels($scope);
659
660	if (!$fields) {
661	return [];
662	}
663
664	$keys = array_keys($fields);
665
666	$customRows = Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)
667	->whereIn('key', $keys)
668	->get();
669
670	if (!$customRows) {
671	return [];
672	}
673
674	$formattedData = [];
675
676	$customRenderers = \FluentSupportPro\App\Services\CustomFieldsService::getCustomerRenderers();
677
678	foreach ($customRows as $row) {
679	$dataKey = $row->key;
680
681	$value = $row->value;
682
683	$fieldType = $fields[$dataKey]['type'];
684
685	if ($value) {
686	if (in_array($fieldType, $customRenderers) && $rendered) {
687	$value = apply_filters('fluent_support/custom_field_render_' . $fieldType, $value, $scope);
688	} else if ($fieldType == 'checkbox') {
689	$value = array_values(array_filter(explode('\|', $value)));
690	}
691	}
692
693	$formattedData[$dataKey] = $value;
694	}
695
696	return $formattedData;
697	}
698
699	public function syncCustomFields($data)
700	{
701	if (!is_array($data)) {
702	return false;
703	}
704
705	$fields = apply_filters('fluent_support/ticket_custom_fields', []);
706
707	if (!$fields) {
708	return false;
709	}
710
711	$keys = array_keys($fields);
712
713	$validData = Arr::only($data, $keys);
714
715	foreach ($validData as $dataKey => $validDatum) {
716	if ($fields[$dataKey]['type'] == 'checkbox' \|\| is_array($validDatum)) {
717	$validDatum = implode('\|', $validDatum);
718	$validDatum = '\|' . $validDatum . '\|';
719	}
720
721	$exist = Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)
722	->where('key', $dataKey)
723	->first();
724
725	if ($exist) {
726	$exist->value = $validDatum;
727	$exist->save();
728	} else {
729	Meta::insert([
730	'object_type' => 'ticket_meta',
731	'object_id' => $this->id,
732	'key' => $dataKey,
733	'value' => $validDatum
734	]);
735	}
736	}
737
738	// maybe delete data
739	$deletedSlugs = array_diff($keys, array_keys($validData));
740
741	if ($deletedSlugs) {
742	Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)
743	->whereIn('key', $deletedSlugs)
744	->delete();
745	}
746
747	return true;
748	}
749
750	public function getLastAgentResponse()
751	{
752	return \FluentSupport\App\App::db()->table('fs_conversations')
753	->select(['fs_conversations.*'])
754	->where('fs_conversations.conversation_type', 'response')
755	->where('fs_conversations.ticket_id', $this->id)
756	->join('fs_persons', 'fs_persons.person_type', '=', 'agent')
757	->orderBy('fs_conversations.id', 'DESC')
758	->first();
759	}
760
761	public function getLastResponse()
762	{
763	return \FluentSupport\App\App::db()->table('fs_conversations')
764	->where('ticket_id', $this->id)
765	->where('conversation_type', 'response')
766	->orderBy('id', 'DESC')
767	->first();
768	}
769
770	public function applyTags($tagIds)
771	{
772	$result = false;
773	foreach ($tagIds as $tagId) {
774	if (!$this->hasTag($tagId)) {
775	$this->tags()->attach($tagId, ['source_type' => 'ticket_tag']);
776	$result = true;
777	do_action('fluent_support/ticket_tag_added', $tagId, $this);
778	}
779	}
780	return $result;
781	}
782
783	public function detachTags($tagIds)
784	{
785	$result = false;
786	foreach ($tagIds as $tagId) {
787	if ($this->hasTag($tagId)) {
788	$this->tags()->detach($tagId);
789	do_action('fluent_support/ticket_tag_removed', $tagId, $this);
790	$result = true;
791	}
792	}
793	return $result;
794	}
795	}
796