← All changes | app/Models/Ticket.php +861 -69 1.5.3 → 2.4.5 View file →

		@@ -1,9 +1,14 @@
1	1	<?php
2	2
3	3	namespace FluentSupport\App\Models;
4	4
	5	+use Exception;
	6	+use FluentSupport\App\Modules\PermissionManager;
5	7	use FluentSupport\App\Services\Helper;
	8	+use FluentSupport\App\Services\TicketHelper;
	9	+use FluentSupport\App\Services\TicketQueryService;
	10	+use FluentSupport\App\Services\Tickets\TicketService;
6	11	use FluentSupport\Framework\Support\Arr;
7	12
8	13	class Ticket extends Model
9	14	{
		@@ -8,9 +13,21 @@
8	13	class Ticket extends Model
9	14	{
10	15	protected $table = 'fs_tickets';
11	16
	17	+ protected $dates = ['waiting_since'];
	18	+
12	19	/**
	20	+ * The hash is a bearer credential for the public ticket view, so keep it
	21	+ * out of API responses. Property access still works for signed URLs.
	22	+ *
	23	+ * @var array
	24	+ */
	25	+ protected $hidden = ['hash', 'content_hash'];
	26	+
	27	+ protected $appends = ['display_ticket_number'];
	28	+
	29	+ /**
13	30	* The attributes that are mass assignable.
14	31	*
15	32	* @var array
16	33	*/
		@@ -36,37 +53,79 @@
36	53	'response_count',
37	54	'first_response_time',
38	55	'total_close_time',
39	56	'resolved_at',
40		- 'closed_by'
	57	+ 'closed_by',
	58	+ 'created_by',
	59	+ 'serial_number',
	60	+ 'ticket_number'
41	61	];
42	62
43	63	public static function boot()
44	64	{
	65	+ parent::boot();
	66	+
45	67	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);
	68	+ if (empty($model->slug)) {
	69	+ $model->slug = static::slugify($model->title);
	70	+ }
	71	+
	72	+ $model->hash = bin2hex(random_bytes(16));
	73	+ $model->content_hash = md5($model->content);
	74	+
48	75	$model->last_customer_response = current_time('mysql');
49		- $model->content_hash = md5($model->content);
50	76	$model->created_at = current_time('mysql');
51	77	$model->updated_at = current_time('mysql');
52	78	$model->waiting_since = current_time('mysql');
	79	+
53	80	});
	81	+
	82	+ static::updating(function ($model) {
	83	+ // Rotate the hash on customer change so links already emailed to
	84	+ // the previous customer stop working.
	85	+ if ($model->isDirty('customer_id')) {
	86	+ $model->hash = bin2hex(random_bytes(16));
	87	+ }
	88	+ });
	89	+
	90	+ static::created(function ($model) {
	91	+ if (empty($model->serial_number) || empty($model->ticket_number)) {
	92	+ $model->assignTicketNumber();
	93	+ }
	94	+ });
	95	+
	96	+ static::deleting(function ($model) {
	97	+ Meta::where('object_type', 'ticket_meta')->where('object_id', $model->id)->delete();
	98	+ // CC info
	99	+ Meta::where('object_type', 'ticket')->where('object_id', $model->id)->delete();
	100	+ // Drafts
	101	+ Meta::where('object_type', '_fs_auto_draft')->where('object_id', $model->id)->delete();
	102	+ Notification::deleteByTicketId($model->id);
	103	+ // Response attachments are cleaned up by Conversation::deleting
	104	+ Conversation::deleteAll($model->id);
	105	+ // Ticket-level attachments, plus the ticket upload directory
	106	+ $class = __NAMESPACE__ . '\Attachment';
	107	+ $ticketAttachments = $class::where('ticket_id', $model->id)->whereNull('conversation_id')->get();
	108	+ $class::purgeAttachments($ticketAttachments, $model->id);
	109	+ $class::where('ticket_id', $model->id)->whereNull('conversation_id')->delete();
	110	+ });
54	111	}
55	112
56	113	/**
57		- * $searchable Columns in table to search
58	114	* @var array
59	115	*/
60	116	protected $searchable = [
	117	+ 'content',
61	118	'title',
62	119	'slug',
63		- 'content',
64		- 'id'
	120	+ 'id',
	121	+ 'serial_number',
	122	+ 'ticket_number'
65	123	];
66	124
67	125	/**
68		- * Local scope to filter subscribers by search/query string
	126	+ * Supports `column:value` to match a single fillable column.
	127	+ *
69	128	* @param ModelQueryBuilder $query
70	129	* @param string $search
71	130	* @return ModelQueryBuilder
72	131	*/
		@@ -71,42 +130,162 @@
71	130	* @return ModelQueryBuilder
72	131	*/
73	132	public function scopeSearchBy($query, $search)
74	133	{
75		- if ($search) {
76	134
77		- if (strpos($search, ':')) {
78		- $array = explode(':', $search);
79		- $column = $array[0];
80		- $value = $array[1];
81		- $columns = $this->fillable;
82		- $columns[] = 'id';
	135	+ if(!$search) {
	136	+ return $query;
	137	+ }
83	138
84		- if (in_array($column, $columns) && $value) {
85		- if (is_numeric($value)) {
86		- $query->where($column, $value);
87		- } else {
88		- $query->where($column, 'LIKE', "%$value%");
89		- }
90		- return $query;
	139	+ if (strpos($search, ':')) {
	140	+ $array = explode(':', (string) $search);
	141	+ $column = $array[0];
	142	+ $value = $array[1];
	143	+ $columns = $this->fillable;
	144	+ $columns[] = 'id';
	145	+
	146	+ if (in_array($column, $columns) && $value) {
	147	+ if (is_numeric($value)) {
	148	+ $query->where($column, $value);
	149	+ } else {
	150	+ $query->where($column, 'LIKE', "%$value%");
91	151	}
	152	+ return $query;
92	153	}
	154	+ }
93	155
94		- $fields = $this->searchable;
95		- $query->where(function ($query) use ($fields, $search) {
96		- $query->where(array_shift($fields), 'LIKE', "%$search%");
	156	+ $fields = $this->searchable;
	157	+ $query->where(function ($query) use ($fields, $search) {
	158	+ $query->where(array_shift($fields), 'LIKE', "%$search%");
	159	+ foreach ($fields as $field) {
	160	+ $query->orWhere($field, 'LIKE', "%$search%");
	161	+ }
	162	+ });
97	163
98		- foreach ($fields as $field) {
99		- $query->orWhere($field, 'LIKE', "$search%");
	164	+ return $query;
	165	+ }
	166	+
	167	+ /**
	168	+ * @param ModelQueryBuilder $query
	169	+ * @param array $search
	170	+ * @return ModelQueryBuilder
	171	+ */
	172	+
	173	+ public function doSearchForAdvancedFilter($query, $search)
	174	+ {
	175	+ foreach ($search as $s) {
	176	+ $operator = $s['operator'];
	177	+ if (in_array($s['property'], ['title', 'content'])) {
	178	+ if ($operator == 'contains') {
	179	+ $query = $query->where(function ($query) use ($s) {
	180	+ $query->where($s['property'], 'LIKE', "%" . $s['value'] . "%");
	181	+ });
	182	+ } elseif ($operator == 'not_contains') {
	183	+ $query = $query->where(function ($query) use ($s) {
	184	+ $query->where($s['property'], 'NOT LIKE', '%' . $s['value'] . '%');
	185	+ });
100	186	}
	187	+ }
	188	+
	189	+ if ($s['property'] == 'conversation_content') {
	190	+ $operator = $s['operator'];
	191	+ if ($operator == 'contains') {
	192	+ $query = $query->whereHas('responses', function ($q) use ($s) {
	193	+ $q->where('content', 'LIKE', "%" . $s['value'] . "%");
	194	+ });
	195	+
	196	+ } else if ($operator == 'not_contains') {
	197	+ $query = $query->whereHas('responses', function ($q) use ($s) {
	198	+ $q->where('content', 'NOT LIKE', "%" . $s['value'] . "%");
	199	+ });
	200	+ }
	201	+ }
	202	+
	203	+ if ($s['property'] == 'source') {
	204	+ $query = static::buildSourceFilterQuery($query, $s);
	205	+ }
	206	+
	207	+ if (in_array($s['property'], ['created_at', 'updated_at', 'waiting_since', 'last_agent_response', 'last_customer_response'])) {
	208	+ $query = (new \FluentSupport\App\Models\Ticket())->buildDateBaseFilterQuery($query, $s);
	209	+ }
	210	+
	211	+ if (in_array($s['property'], ['status', 'client_priority', 'priority', 'tags', 'product', 'waiting_for_reply', 'agent_id', 'mailbox_id'])) {
	212	+ $query = (new \FluentSupport\App\Models\Ticket())->buildPropertiesFilterQuery($query, $s);
	213	+ }
	214	+ }
	215	+ return $query;
	216	+ }
	217	+
	218	+ /** Advanced-filter value for tickets with no recorded source (older tickets). */
	219	+ const SOURCE_NONE = '__none__';
	220	+
	221	+ /**
	222	+ * Where tickets come from, for the advanced filter's Source field.
	223	+ *
	224	+ * @return array<string,string> source => label
	225	+ */
	226	+ public static function sourceFilterOptions()
	227	+ {
	228	+ return apply_filters('fluent_support/ticket_source_filter_options', [
	229	+ 'web' => __('Web', 'fluent-support'),
	230	+ 'email' => __('Email', 'fluent-support'),
	231	+ self::SOURCE_AGENT_INITIATED => __('Agent outreach', 'fluent-support'),
	232	+ 'mcp' => __('MCP (AI agents)', 'fluent-support'),
	233	+ 'telegram' => __('Telegram', 'fluent-support'),
	234	+ 'slack' => __('Slack', 'fluent-support'),
	235	+ 'whatsapp' => __('WhatsApp', 'fluent-support'),
	236	+ 'awesome-support' => __('Imported: Awesome Support', 'fluent-support'),
	237	+ 'freshdesk' => __('Imported: Freshdesk', 'fluent-support'),
	238	+ 'helpscout' => __('Imported: Help Scout', 'fluent-support'),
	239	+ 'js-helpdesk' => __('Imported: JS Help Desk', 'fluent-support'),
	240	+ 'support-candy' => __('Imported: SupportCandy', 'fluent-support'),
	241	+ 'zendesk' => __('Imported: Zendesk', 'fluent-support'),
	242	+ self::SOURCE_NONE => __('No source', 'fluent-support'),
	243	+ ]);
	244	+ }
	245	+
	246	+ /**
	247	+ * Source "in" / "not in". SOURCE_NONE stands for NULL or empty, which SQL's
	248	+ * IN and NOT IN would otherwise skip.
	249	+ *
	250	+ * @param \FluentSupport\Framework\Database\Orm\Builder $query
	251	+ * @param array $filter
	252	+ * @return \FluentSupport\Framework\Database\Orm\Builder
	253	+ */
	254	+ public static function buildSourceFilterQuery($query, $filter)
	255	+ {
	256	+ $values = array_values(array_filter(array_map('sanitize_text_field', (array) Arr::get($filter, 'value', [])), 'strlen'));
	257	+ if (!$values) {
	258	+ return $query;
	259	+ }
	260	+
	261	+ $includeNone = in_array(self::SOURCE_NONE, $values, true);
	262	+ $sources = array_values(array_diff($values, [self::SOURCE_NONE]));
	263	+
	264	+ if (Arr::get($filter, 'operator') === 'not_in') {
	265	+ return $query->where(function ($q) use ($sources, $includeNone) {
	266	+ if ($includeNone) {
	267	+ $q->whereNotNull('source')->where('source', '!=', '');
	268	+ if ($sources) {
	269	+ $q->whereNotIn('source', $sources);
	270	+ }
	271	+ } else {
	272	+ $q->whereNotIn('source', $sources)->orWhereNull('source');
	273	+ }
101	274	});
102	275	}
103	276
104		- return $query;
	277	+ return $query->where(function ($q) use ($sources, $includeNone) {
	278	+ if ($sources) {
	279	+ $q->whereIn('source', $sources);
	280	+ }
	281	+ if ($includeNone) {
	282	+ $q->orWhereNull('source')->orWhere('source', '');
	283	+ }
	284	+ });
105	285	}
106	286
107	287	/**
108		- * Local scope to filter subscribers by search/query string
109	288	* @param ModelQueryBuilder $query
110	289	* @param array $statuses
111	290	* @return ModelQueryBuilder
112	291	*/
		@@ -118,13 +297,43 @@
118	297
119	298	return $query;
120	299	}
121	300
	301	+ /**
	302	+ * Who replied last: 'agent', 'customer', or null when neither timestamp is
	303	+ * set. PHP counterpart of the timestamp check in scopeWaitingOnly(). A new
	304	+ * ticket resolves to 'customer' because boot() seeds last_customer_response.
	305	+ *
	306	+ * @return string|null
	307	+ */
	308	+ public function getLastReplyByAttribute()
	309	+ {
	310	+ $agentAt = $this->last_agent_response;
	311	+ $customerAt = $this->last_customer_response;
	312	+
	313	+ if (!$agentAt && !$customerAt) {
	314	+ return null;
	315	+ }
	316	+ if (!$agentAt) {
	317	+ return 'customer';
	318	+ }
	319	+ if (!$customerAt) {
	320	+ return 'agent';
	321	+ }
	322	+
	323	+ // A tie resolves to 'customer'. scopeWaitingOnly() uses a strict <, so SQL treats a tie as answered.
	324	+ return strtotime($customerAt) >= strtotime($agentAt) ? 'customer' : 'agent';
	325	+ }
	326	+
	327	+ /**
	328	+ * Tickets awaiting an agent reply.
	329	+ * @param $query
	330	+ * @return mixed
	331	+ */
122	332	public function scopeWaitingOnly($query)
123	333	{
124		- global $wpdb;
125		- $query->where(function ($q) use ($wpdb) {
126		- $q->whereRaw($wpdb->prefix . 'fs_tickets.last_agent_response < ' . $wpdb->prefix . 'fs_tickets.last_customer_response')
	334	+ $query->where(function ($q) {
	335	+ $q->whereColumn('last_agent_response', '<', 'last_customer_response')
127	336	->orWhereNull('last_agent_response')
128	337	->orWhere('status', 'new');
129	338	});
130	339	return $query;
		@@ -129,13 +338,18 @@
129	338	});
130	339	return $query;
131	340	}
132	341
	342	+ /**
	343	+ * @param $query
	344	+ * @param $filters
	345	+ * @return ModelQueryBuilder
	346	+ */
133	347	public function scopeApplyFilters($query, $filters)
134	348	{
135	349	$supportedColumns = ['product_id', 'client_priority', 'priority', 'mailbox_id'];
136	350	foreach ($filters as $filterKey => $filterValue) {
137		- if (!$filterValue && ($filterValue !== '0' || $filterValue !== 0)) {
	351	+ if (!$filterValue && ($filterValue !== '0' && $filterValue !== 0)) {
138	352	continue;
139	353	}
140	354	if ($filterKey == 'status_type') {
141	355	$statusArray = Helper::getTkStatusesByGroupName($filterValue);
		@@ -142,9 +356,13 @@
142	356	if ($statusArray) {
143	357	$query->whereIn('status', $statusArray);
144	358	}
145	359	} else if (in_array($filterKey, $supportedColumns)) {
146		- $query->where($filterKey, $filterValue);
	360	+ if (is_array($filterValue)) {
	361	+ $query->whereIn($filterKey, $filterValue);
	362	+ } else {
	363	+ $query->where($filterKey, $filterValue);
	364	+ }
147	365	} else if ($filterKey == 'waiting_for_reply') {
148	366	if ($filterValue != 'yes') {
149	367	continue;
150	368	}
		@@ -149,13 +367,67 @@
149	367	continue;
150	368	}
151	369	$query = $this->scopeWaitingOnly($query);
152	370	} else if ($filterKey == 'agent_id') {
153		- if ($filterValue == 'unassigned') {
154		- $query->whereNull($filterKey);
	371	+ if (is_array($filterValue)) {
	372	+ $hasUnassigned = in_array('unassigned', $filterValue);
	373	+ $agentIds = array_filter($filterValue, function($v) {
	374	+ return $v !== 'unassigned';
	375	+ });
	376	+
	377	+ if ($hasUnassigned && !empty($agentIds)) {
	378	+ $query->where(function($q) use ($agentIds) {
	379	+ $q->whereNull('agent_id')
	380	+ ->orWhereIn('agent_id', $agentIds);
	381	+ });
	382	+ } elseif ($hasUnassigned) {
	383	+ $query->whereNull('agent_id');
	384	+ } elseif (!empty($agentIds)) {
	385	+ if (defined('FLUENTSUPPORTPRO')) {
	386	+ if (isset($filters['watcher']) && $filters['watcher'] == 'watcher') {
	387	+ $watcherTickets = [];
	388	+ foreach ($agentIds as $agentId) {
	389	+ $watcherTickets = array_merge($watcherTickets, TicketHelper::getWatcherTicketIds($agentId));
	390	+ }
	391	+ $query->whereIn('id', array_unique($watcherTickets));
	392	+ } else {
	393	+ $query->whereIn('agent_id', $agentIds);
	394	+ }
	395	+ } else {
	396	+ $query->whereIn('agent_id', $agentIds);
	397	+ }
	398	+ }
155	399	} else {
156		- $query->where($filterKey, $filterValue);
	400	+ // Single value (legacy)
	401	+ if ($filterValue == 'unassigned') {
	402	+ $query->whereNull($filterKey);
	403	+ } else {
	404	+ if (defined('FLUENTSUPPORTPRO')) {
	405	+ if (isset($filters['watcher']) && $filters['watcher'] == 'watcher') {
	406	+ $watcherTickets = TicketHelper::getWatcherTicketIds($filterValue);
	407	+ $query->whereIn('id', $watcherTickets);
	408	+ } else {
	409	+ $query->where($filterKey, $filterValue);
	410	+ }
	411	+ } else {
	412	+ $query->where($filterKey, $filterValue);
	413	+ }
	414	+ }
157	415	}
	416	+ } else if ($filterKey == 'agent_group') {
	417	+ $groupIds = is_array($filterValue) ? $filterValue : [$filterValue];
	418	+ $groupIds = array_filter(array_map('intval', $groupIds));
	419	+ if (!empty($groupIds)) {
	420	+ $agentIds = TagPivot::where('source_type', 'agent_group')
	421	+ ->whereIn('tag_id', $groupIds)
	422	+ ->pluck('source_id')
	423	+ ->toArray();
	424	+ if ($agentIds) {
	425	+ $query->whereIn('agent_id', $agentIds);
	426	+ } else {
	427	+ $query->whereRaw('1 = 0');
	428	+ }
	429	+ }
158	430	} else if ($filterKey == 'ticket_tags') {
159	431	if (!$filterValue) {
160	432	continue;
161	433	}
		@@ -168,11 +440,10 @@
168	440	return $query;
169	441	}
170	442
171	443	/**
172		- * Local scope to filter subscribers by search/query string
173	444	* @param ModelQueryBuilder $query
174		- * @param array $statuses
	445	+ * @param int $agentId
175	446	* @return ModelQueryBuilder
176	447	*/
177	448	public function scopeFilterByAgentId($query, $agentId)
178	449	{
		@@ -183,9 +454,8 @@
183	454	return $query;
184	455	}
185	456
186	457	/**
187		- * Local scope to filter subscribers by search/query string
188	458	* @param ModelQueryBuilder $query
189	459	* @param int $customerId
190	460	* @return ModelQueryBuilder
191	461	*/
		@@ -196,9 +466,8 @@
196	466	return $query;
197	467	}
198	468
199	469	/**
200		- * Local scope to filter subscribers by search/query string
201	470	* @param ModelQueryBuilder $query
202	471	* @param int $productId
203	472	* @return ModelQueryBuilder
204	473	*/
		@@ -211,9 +480,8 @@
211	480	return $query;
212	481	}
213	482
214	483	/**
215		- * Local scope to filter subscribers by search/query string
216	484	* @param ModelQueryBuilder $query
217	485	* @param array $priorities
218	486	* @return ModelQueryBuilder
219	487	*/
		@@ -225,11 +493,252 @@
225	493
226	494	return $query;
227	495	}
228	496
	497	+ /**
	498	+ * @param $filter
	499	+ * @return string[]
	500	+ */
229	501
	502	+ public static function parseRelationalFilterQueryMethods($filter)
	503	+ {
	504	+ // default operator = in
	505	+ $method = 'whereHas';
	506	+ $subMethod = 'whereIn';
	507	+
	508	+ switch ($filter['operator']) {
	509	+ case 'not_in':
	510	+ $method = 'whereDoesntHave';
	511	+ $subMethod = 'whereIn';
	512	+
	513	+ break;
	514	+ case 'in_all':
	515	+ $method = 'whereHas';
	516	+ $subMethod = 'where';
	517	+
	518	+ break;
	519	+ case 'not_in_all':
	520	+ $method = 'whereDoesntHave';
	521	+ $subMethod = 'where';
	522	+
	523	+ break;
	524	+ }
	525	+
	526	+ return [$method, $subMethod];
	527	+ }
	528	+
230	529	/**
231		- * One2Many: Customer has to many Click Tickets
	530	+ * Parse filter to set proper operator and value for the filter query.
	531	+ *
	532	+ * @param array $filter
	533	+ * @return array
	534	+ */
	535	+ public static function filterParser($filter)
	536	+ {
	537	+ switch ($filter['operator']) {
	538	+ case 'before':
	539	+ $filter['operator'] = '<';
	540	+ $filter['value'] = $filter['value'] . ' 23:59:59';
	541	+ break;
	542	+
	543	+ case 'after':
	544	+ $filter['operator'] = '>';
	545	+ $filter['value'] = $filter['value'] . ' 23:59:59';
	546	+ break;
	547	+
	548	+ case 'date_equal':
	549	+ $filter['operator'] = 'LIKE';
	550	+ $filter['value'] = '%' . $filter['value'] . '%';
	551	+ break;
	552	+
	553	+ case 'days_before':
	554	+ $filter['operator'] = '<';
	555	+ $filter['value'] = gmdate('Y-m-d', time() - $filter['value'] * 24 * 60 * 60);
	556	+ break;
	557	+
	558	+ case 'days_within':
	559	+ $filter['operator'] = 'BETWEEN';
	560	+ $filter['value'] = [
	561	+ gmdate('Y-m-d', time() - $filter['value'] * 24 * 60 * 60),
	562	+ gmdate('Y-m-d') . ' 23:59:59'
	563	+ ];
	564	+ break;
	565	+ case 'date_range':
	566	+ $filter['operator'] = 'BETWEEN';
	567	+ if (isset($filter['value'][0]))
	568	+ $filter['value'][0] .= ' 00:00:00';
	569	+ if (isset($filter['value'][1]))
	570	+ $filter['value'][1] .= ' 23:59:59';
	571	+ break;
	572	+ }
	573	+
	574	+ return $filter;
	575	+ }
	576	+
	577	+ /**
	578	+ * @param \FluentSupport\Framework\Database\Orm\Builder|\FluentSupport\Framework\Database\Query\Builder $query
	579	+ * @param array $filters
	580	+ * @return ModelQueryBuilder
	581	+ */
	582	+ public function buildDateBaseFilterQuery($query, $filters)
	583	+ {
	584	+ $filter = static::filterParser($filters);
	585	+ $query->where(function ($dateQuery) use ($filter) {
	586	+
	587	+ if ($filter['operator'] == 'BETWEEN') {
	588	+ $dateQuery->whereBetween($filter['property'], $filter['value']);
	589	+ } else {
	590	+ $dateQuery->where($filter['property'], $filter['operator'], $filter['value']);
	591	+ }
	592	+ });
	593	+
	594	+ return $query;
	595	+ }
	596	+
	597	+ /**
	598	+ * @param $relation
	599	+ * @param $query
	600	+ * @param $method
	601	+ * @param $subMethod
	602	+ * @param $subField
	603	+ * @param $filter
	604	+ * @param false $provider
	605	+ * @return ModelQueryBuilder
	606	+ */
	607	+
	608	+ public static function buildRelationFilterQuery($relation, $query, $method, $subMethod, $subField, $filter, $provider = false)
	609	+ {
	610	+ if (in_array($filter['operator'], ['in_all', 'not_in_all']) && $filter['value']) {
	611	+ foreach ($filter['value'] as $item) {
	612	+ $query = static::buildRelationFilterQuery($relation, $query, $method, $subMethod, $subField, ['value' => $item, 'operator' => ''], $provider);
	613	+ }
	614	+ } else {
	615	+ $query = $query->{$method}($relation, function ($relationQuery) use ($subMethod, $subField, $filter, $provider) {
	616	+ $relationQuery = $relationQuery->{$subMethod}($subField, $filter['value']);
	617	+
	618	+ if ($provider) {
	619	+ $relationQuery = $relationQuery->where('provider', $provider);
	620	+ }
	621	+
	622	+ return $relationQuery;
	623	+ });
	624	+ }
	625	+
	626	+ return $query;
	627	+ }
	628	+
	629	+ /**
	630	+ * @param $query
	631	+ * @param $filter
	632	+ * @return ModelQueryBuilder
	633	+ */
	634	+
	635	+ public function buildPropertiesFilterQuery($query, $filter)
	636	+ {
	637	+ if (in_array($filter['property'], ['tags', 'product'])) {
	638	+ $subField = $filter['property'] == 'tags' ? 'tag_id' : 'product_id';
	639	+ list($method, $subMethod) = static::parseRelationalFilterQueryMethods($filter);
	640	+ $query = static::buildRelationFilterQuery($filter['property'], $query, $method, $subMethod, $subField, $filter);
	641	+ } elseif ($filter['property'] == 'waiting_for_reply') {
	642	+ if (($filter['value'] == 'yes' && $filter['operator'] == 'in') || ($filter['value'] == 'no' && $filter['operator'] == 'not_in')) {
	643	+ $query = $query->where(function ($q) {
	644	+ $q->whereColumn('last_agent_response', '<', 'last_customer_response')
	645	+ ->orWhereNull('last_agent_response')
	646	+ ->orWhere('status', 'new');
	647	+ });
	648	+ } else {
	649	+ $query = $query->where(function ($q) {
	650	+ $q->whereColumn('last_customer_response', '<', 'last_agent_response');
	651	+ });
	652	+ }
	653	+ } else {
	654	+ $method = $filter['operator'] == 'in' ? 'whereIn' : 'whereNotIn';
	655	+ $query = $query->{$method}($filter['property'], (array)$filter['value']);
	656	+ }
	657	+ return $query;
	658	+ }
	659	+
	660	+ /**
	661	+ * @param $provider
	662	+ * @param $query
	663	+ * @param $search
	664	+ * @param string $operator
	665	+ * @return ModelQueryBuilder
	666	+ */
	667	+ public function buildSearchableQuery($provider, $query, $search, $operator = 'LIKE')
	668	+ {
	669	+ switch ($provider) {
	670	+ case 'customer':
	671	+ $fields = (new Customer())->getSearchableFields();
	672	+ break;
	673	+ case 'agent':
	674	+ $fields = (new Agent())->getSearchableFields();
	675	+ break;
	676	+ default:
	677	+ $fields = $this->searchable;
	678	+ break;
	679	+ }
	680	+
	681	+ $query->whereHas($provider, function ($query) use ($fields, $search, $operator) {
	682	+ $query->where(array_shift($fields), $operator, $search);
	683	+
	684	+ $nameArray = explode(' ', (string) $search);
	685	+
	686	+ if (count($nameArray) >= 2) {
	687	+ $query->orWhere(function ($q) use ($nameArray, $operator) {
	688	+ $firstName = array_shift($nameArray);
	689	+ $lastName = implode(' ', $nameArray);
	690	+
	691	+ $q->where('first_name', $operator, $firstName);
	692	+ $q->where('last_name', $operator, $lastName);
	693	+ });
	694	+ }
	695	+
	696	+ foreach ($fields as $field) {
	697	+ $query->orWhere($field, $operator, $search);
	698	+ }
	699	+ });
	700	+
	701	+ return $query;
	702	+ }
	703	+
	704	+ /**
	705	+ * Filter by fields on the related customer or agent.
	706	+ * @param $provider
	707	+ * @param $query
	708	+ * @param $filters
	709	+ * @return ModelQueryBuilder
	710	+ */
	711	+ public function filterTicketByUser($provider, $query, $filters)
	712	+ {
	713	+ foreach ($filters as $filter) {
	714	+ if ($filter['operator'] == 'in' || $filter['operator'] == 'not_in') {
	715	+ $method = $filter['operator'] == 'in' ? 'whereIn' : 'whereNotIn';
	716	+ $query = $query->whereHas($provider, function ($q) use ($method, $filter) {
	717	+ $q->{$method}($filter['property'], $filter['value']);
	718	+ });
	719	+ }
	720	+
	721	+ if ($filter['operator'] == 'contains' || $filter['operator'] == 'not_contains') {
	722	+ $operator = $filter['operator'] == 'contains' ? 'LIKE' : 'NOT LIKE';
	723	+ $query->whereHas($provider, function ($q) use ($operator, $filter) {
	724	+ $q->where($filter['property'], $operator, '%' . $filter['value'] . '%');
	725	+ });
	726	+ }
	727	+
	728	+ if ($filter['operator'] == '=' || $filter['operator'] == '!=') {
	729	+ $operator = $filter['operator'];
	730	+ $query->whereHas($provider, function ($q) use ($operator, $filter) {
	731	+ $q->where($filter['property'], $operator, $filter['value']);
	732	+ });
	733	+ }
	734	+ }
	735	+ return $query;
	736	+ }
	737	+
	738	+ /**
	739	+ * Newest first.
	740	+ *
232	741	* @return Model Collection
233	742	*/
234	743	public function responses()
235	744	{
		@@ -236,11 +745,43 @@
236	745	$class = __NAMESPACE__ . '\Conversation';
237	746
238	747	return $this->hasMany(
239	748	$class, 'ticket_id', 'id'
240		- );
	749	+ )->orderBy('created_at', 'desc')
	750	+ ->orderBy('id', 'desc');
241	751	}
242	752
	753	+ /** source value for a ticket an agent opened to reach out to the customer. */
	754	+ const SOURCE_AGENT_INITIATED = 'agent_initiated';
	755	+
	756	+ /**
	757	+ * Whether an agent opened this ticket to reach out. The message is then the
	758	+ * agent's first response, and the ticket body is only a placeholder.
	759	+ *
	760	+ * @return bool
	761	+ */
	762	+ public function isAgentInitiated()
	763	+ {
	764	+ if ($this->source === self::SOURCE_AGENT_INITIATED) {
	765	+ return true;
	766	+ }
	767	+
	768	+ // Before 2.4.1 only the "initialized this ticket" placeholder body marked it.
	769	+ return $this->created_by && self::hasLegacyAgentInitiatedMarker($this->content);
	770	+ }
	771	+
	772	+ /**
	773	+ * @param string $content
	774	+ * @return bool
	775	+ */
	776	+ public static function hasLegacyAgentInitiatedMarker($content)
	777	+ {
	778	+ $content = (string) $content;
	779	+
	780	+ return strpos($content, ' initialized this ticket') !== false
	781	+ || strpos($content, __(' initialized this ticket', 'fluent-support')) !== false;
	782	+ }
	783	+
243	784	public function preview_response()
244	785	{
245	786	$class = __NAMESPACE__ . '\Conversation';
246	787
		@@ -257,11 +798,18 @@
257	798	$class, 'fs_tag_pivot', 'source_id', 'tag_id'
258	799	)->wherePivot('source_type', 'ticket_tag');
259	800	}
260	801
	802	+ public function watchers()
	803	+ {
	804	+ $class = __NAMESPACE__ . '\TagPivot';
261	805
	806	+ return $this->hasMany($class, 'source_id', 'id')
	807	+ ->where('source_type', 'ticket_watcher')
	808	+ ->select(['tag_id']);
	809	+ }
	810	+
262	811	/**
263		- * One2one: Customer has to many Click Tickets
264	812	* @return Model Collection
265	813	*/
266	814	public function customer()
267	815	{
		@@ -272,9 +820,8 @@
272	820	);
273	821	}
274	822
275	823	/**
276		- * One2one: Customer has to many Click Tickets
277	824	* @return Model Collection
278	825	*/
279	826	public function agent()
280	827	{
		@@ -293,8 +840,17 @@
293	840	$class, 'closed_by', 'id'
294	841	);
295	842	}
296	843
	844	+ public function created_by_person()
	845	+ {
	846	+ $class = __NAMESPACE__ . '\Agent';
	847	+
	848	+ return $this->belongsTo(
	849	+ $class, 'created_by', 'id'
	850	+ );
	851	+ }
	852	+
297	853	public function product()
298	854	{
299	855	$class = __NAMESPACE__ . '\Product';
300	856
		@@ -314,17 +870,86 @@
314	870
315	871
316	872	public function deleteTicket()
317	873	{
	874	+ /*
	875	+ * Action on ticket deleting
	876	+ *
	877	+ * @since v1.0.0
	878	+ * @param object $ticket
	879	+ */
318	880	do_action('fluent_support/deleting_ticket', $this);
319		- // delete the responses first
320		- Conversation::where('ticket_id', $this->id)->delete();
321		- // Delete the ticket meta
322		- Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)->delete();
	881	+ $this->delete();
	882	+ }
323	883
324		- Ticket::where('id', $this->id)->delete();
	884	+ public static function getNextSerialNumber()
	885	+ {
	886	+ $businessSettings = Helper::getOption('global_business_settings', []);
	887	+ $minNumber = (int) ($businessSettings['min_serial_number'] ?? 1);
	888	+ $minNumber = (int) apply_filters('fluent_support/min_serial_number', $minNumber);
	889	+
	890	+ try {
	891	+ $lastTicketNumber = self::query()->max('serial_number');
	892	+ } catch (\Exception $e) {
	893	+ $lastTicketNumber = null;
	894	+ }
	895	+
	896	+ $nextNumber = ((int) $lastTicketNumber) + 1;
	897	+
	898	+ return max($nextNumber, $minNumber);
325	899	}
326	900
	901	+ public static function isMinimumSerialNumberEnabled()
	902	+ {
	903	+ $businessSettings = Helper::getOption('global_business_settings', []);
	904	+ return ($businessSettings['enable_min_serial_number'] ?? 'no') === 'yes';
	905	+ }
	906	+
	907	+ public static function getTicketPrefix($ticket = null)
	908	+ {
	909	+ $businessSettings = Helper::getOption('global_business_settings', []);
	910	+ $prefix = self::isMinimumSerialNumberEnabled() ? trim((string) ($businessSettings['ticket_prefix'] ?? '')) : '';
	911	+
	912	+ $productId = $ticket ? $ticket->product_id : null;
	913	+
	914	+ return apply_filters('fluent_support/ticket_prefix', $prefix, $ticket, $productId);
	915	+ }
	916	+
	917	+ public function getDisplayTicketNumberAttribute()
	918	+ {
	919	+ return $this->ticket_number ?: ($this->serial_number ?: $this->id);
	920	+ }
	921	+
	922	+ public function scopeWherePublicIdentifier($query, $identifier)
	923	+ {
	924	+ return $query->where('serial_number', $identifier);
	925	+ }
	926	+
	927	+ protected function assignTicketNumber()
	928	+ {
	929	+ for ($attempt = 0; $attempt < 5; $attempt++) {
	930	+ $nextNumber = $this->serial_number ?: (self::isMinimumSerialNumberEnabled() ? self::getNextSerialNumber() : $this->id);
	931	+ $ticketNumber = $this->ticket_number ?: (self::getTicketPrefix($this) . $nextNumber);
	932	+
	933	+ try {
	934	+ self::where('id', $this->id)->update([
	935	+ 'serial_number' => $nextNumber,
	936	+ 'ticket_number' => $ticketNumber
	937	+ ]);
	938	+ $this->serial_number = $nextNumber;
	939	+ $this->ticket_number = $ticketNumber;
	940	+
	941	+ return $nextNumber;
	942	+ } catch (\Exception $e) {
	943	+ if (stripos($e->getMessage(), 'duplicate') === false) {
	944	+ throw $e;
	945	+ }
	946	+ }
	947	+ }
	948	+
	949	+ throw new \RuntimeException('Could not allocate a unique ticket number.');
	950	+ }
	951	+
327	952	public static function slugify($title)
328	953	{
329	954	$slug = sanitize_title($title, 'support-ticket-' . time(), 'display');
330	955	if (Ticket::where('slug', $slug)->first()) {
		@@ -386,19 +1011,27 @@
386	1011
387	1012	if ($value) {
388	1013	if (in_array($fieldType, $customRenderers) && $rendered) {
389	1014	$value = apply_filters('fluent_support/custom_field_render_' . $fieldType, $value, $scope);
390		- } else if ($fieldType == 'checkbox') {
	1015	+ } else if (in_array($fieldType, ['checkbox', 'date-range'])) {
391	1016	$value = array_values(array_filter(explode('|', $value)));
392	1017	}
	1018	+
	1019	+ if (!is_array($value) && !is_object($value)) {
	1020	+ $formattedData[$dataKey] = links_add_target(make_clickable($value));
	1021	+ } else {
	1022	+ $formattedData[$dataKey] = $value;
	1023	+ }
393	1024	}
394		-
395		- $formattedData[$dataKey] = $value;
396	1025	}
397	1026
398	1027	return $formattedData;
399	1028	}
400	1029
	1030	+ /**
	1031	+ * @param array $data Custom field values keyed by field key.
	1032	+ * @return bool
	1033	+ */
401	1034	public function syncCustomFields($data)
402	1035	{
403	1036	if (!is_array($data)) {
404	1037	return false;
		@@ -414,14 +1047,23 @@
414	1047
415	1048	$validData = Arr::only($data, $keys);
416	1049
417	1050	foreach ($validData as $dataKey => $validDatum) {
	1051	+ if (empty($validDatum)) {
	1052	+ Meta::where('object_type', 'ticket_meta')
	1053	+ ->where('object_id', $this->id)
	1054	+ ->where('key', $dataKey)
	1055	+ ->delete();
	1056	+ continue;
	1057	+ }
	1058	+
418	1059	if ($fields[$dataKey]['type'] == 'checkbox' || is_array($validDatum)) {
419	1060	$validDatum = implode('|', $validDatum);
420	1061	$validDatum = '|' . $validDatum . '|';
421	1062	}
422	1063
423		- $exist = Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)
	1064	+ $exist = Meta::where('object_type', 'ticket_meta')
	1065	+ ->where('object_id', $this->id)
424	1066	->where('key', $dataKey)
425	1067	->first();
426	1068
427	1069	if ($exist) {
		@@ -436,29 +1078,22 @@
436	1078	]);
437	1079	}
438	1080	}
439	1081
440		- // maybe delete data
441		- $deletedSlugs = array_diff($keys, array_keys($validData));
442		-
443		- if ($deletedSlugs) {
444		- Meta::where('object_type', 'ticket_meta')->where('object_id', $this->id)
445		- ->whereIn('key', $deletedSlugs)
446		- ->delete();
447		- }
448		-
449	1082	return true;
450	1083	}
451	1084
452	1085	public function getLastAgentResponse()
453	1086	{
454		- return \FluentSupport\App\App::db()->table('fs_conversations')
	1087	+ $query = \FluentSupport\App\App::db()->table('fs_conversations')
455	1088	->select(['fs_conversations.*'])
456	1089	->where('fs_conversations.conversation_type', 'response')
457	1090	->where('fs_conversations.ticket_id', $this->id)
458		- ->join('fs_persons', 'fs_persons.person_type', '=', 'agent')
459		- ->orderBy('fs_conversations.id', 'DESC')
460		- ->first();
	1091	+ ->where('fs_persons.person_type', '=', 'agent')
	1092	+ ->join('fs_persons', 'fs_persons.id', '=', 'fs_conversations.person_id')
	1093	+ ->orderBy('fs_conversations.id', 'DESC');
	1094	+
	1095	+ return $query->first();
461	1096	}
462	1097
463	1098	public function getLastResponse()
464	1099	{
		@@ -464,19 +1099,36 @@
464	1099	{
465	1100	return \FluentSupport\App\App::db()->table('fs_conversations')
466	1101	->where('ticket_id', $this->id)
467	1102	->where('conversation_type', 'response')
468		- ->orderBy('id', 'DESC')
	1103	+ ->latest('id')
469	1104	->first();
470	1105	}
471	1106
	1107	+ /**
	1108	+ * @param int|array $tagIds
	1109	+ * @return bool True if any tag was added.
	1110	+ */
472	1111	public function applyTags($tagIds)
473	1112	{
474	1113	$result = false;
	1114	+
	1115	+ if (!is_array($tagIds)) {
	1116	+ $tagIds = array($tagIds);
	1117	+ }
	1118	+
475	1119	foreach ($tagIds as $tagId) {
476	1120	if (!$this->hasTag($tagId)) {
477	1121	$this->tags()->attach($tagId, ['source_type' => 'ticket_tag']);
478	1122	$result = true;
	1123	+
	1124	+ /*
	1125	+ * Action while tag added to ticket
	1126	+ *
	1127	+ * @since v1.0.0
	1128	+ * @param integer $tagId
	1129	+ * @param object $ticket
	1130	+ */
479	1131	do_action('fluent_support/ticket_tag_added', $tagId, $this);
480	1132	}
481	1133	}
482	1134	return $result;
		@@ -481,14 +1133,31 @@
481	1133	}
482	1134	return $result;
483	1135	}
484	1136
	1137	+ /**
	1138	+ * @param int|array $tagIds
	1139	+ * @return bool True if any tag was removed.
	1140	+ */
485	1141	public function detachTags($tagIds)
486	1142	{
487	1143	$result = false;
	1144	+
	1145	+ if (!is_array($tagIds)) {
	1146	+ $tagIds = array($tagIds);
	1147	+ }
	1148	+
488	1149	foreach ($tagIds as $tagId) {
489	1150	if ($this->hasTag($tagId)) {
490	1151	$this->tags()->detach($tagId);
	1152	+
	1153	+ /*
	1154	+ * Action while tag removed from ticket
	1155	+ *
	1156	+ * @since v1.0.0
	1157	+ * @param integer $tagId
	1158	+ * @param object $ticket
	1159	+ */
491	1160	do_action('fluent_support/ticket_tag_removed', $tagId, $this);
492	1161	$result = true;
493	1162	}
494	1163	}
		@@ -493,5 +1162,128 @@
493	1162	}
494	1163	}
495	1164	return $result;
496	1165	}
	1166	+
	1167	+ /**
	1168	+ * @deprecated Use TicketService::storeTicket() instead.
	1169	+ */
	1170	+ public function createTicket($ticketData, $maybeNewCustomer = false)
	1171	+ {
	1172	+ _deprecated_function(__METHOD__, '2.0.5', 'TicketService::storeTicket()');
	1173	+
	1174	+ if (empty($ticketData['customer_id']) && $maybeNewCustomer) {
	1175	+ $email = Arr::get($maybeNewCustomer, 'email');
	1176	+ if (!$email || !is_email($email)) {
	1177	+ return new \WP_Error('error', 'A valid email is required to create a ticket');
	1178	+ }
	1179	+
	1180	+ $existingCustomer = Customer::where('email', $email)->first();
	1181	+ if ($existingCustomer) {
	1182	+ $ticketData['customer_id'] = $existingCustomer->id;
	1183	+ } else {
	1184	+ $customerData = Arr::only($maybeNewCustomer, (new Customer())->getFillable());
	1185	+ $customerData = array_filter($customerData);
	1186	+ $createCustomer = Customer::create($customerData);
	1187	+ if (!$createCustomer) {
	1188	+ return new \WP_Error('error', 'Customer could not be created');
	1189	+ }
	1190	+ $ticketData['customer_id'] = $createCustomer->id;
	1191	+ }
	1192	+ }
	1193	+
	1194	+ if (empty($ticketData['customer_id'])) {
	1195	+ return new \WP_Error('error', 'Ticket could not be created');
	1196	+ }
	1197	+
	1198	+ $customer = Customer::findOrFail($ticketData['customer_id']);
	1199	+
	1200	+ return (new TicketService())->storeTicket($ticketData, $customer);
	1201	+ }
	1202	+
	1203	+ public static function countTicketByMailBoxId($mailbox_id)
	1204	+ {
	1205	+ return self::where('mailbox_id', $mailbox_id)->count();
	1206	+ }
	1207	+
	1208	+ public static function syncMailBoxId($mailbox_id, $fallback_id)
	1209	+ {
	1210	+ return self::where('mailbox_id', $mailbox_id)
	1211	+ ->update([
	1212	+ 'mailbox_id' => $fallback_id
	1213	+ ]);
	1214	+ }
	1215	+
	1216	+ public static function getTicketsQuery()
	1217	+ {
	1218	+ return self::with([
	1219	+ 'customer' => function ($query) {
	1220	+ $query->select(['first_name', 'last_name', 'email', 'id', 'avatar']);
	1221	+ }, 'agent' => function ($query) {
	1222	+ $query->select(['first_name', 'last_name', 'id']);
	1223	+ },
	1224	+ 'product',
	1225	+ 'tags',
	1226	+ 'preview_response' => function ($query) {
	1227	+ $query->latest('id');
	1228	+ }
	1229	+ ]);
	1230	+ }
	1231	+
	1232	+ public function getSettingsValue($valueKey = false, $default = false)
	1233	+ {
	1234	+ $exist = Meta::where('object_type', 'ticket')
	1235	+ ->where('key', 'settings')
	1236	+ ->where('object_id', $this->id)
	1237	+ ->first();
	1238	+
	1239	+ if ($exist) {
	1240	+ $value = Helper::safeUnserialize($exist->value);
	1241	+ if ($valueKey) {
	1242	+ if (!is_array($value)) {
	1243	+ return $default;
	1244	+ }
	1245	+ return Arr::get($value, $valueKey, $default);
	1246	+ }
	1247	+ return $value;
	1248	+ }
	1249	+
	1250	+ return $default;
	1251	+ }
	1252	+
	1253	+ public function updateSettingsValue($valueKey, $value)
	1254	+ {
	1255	+ $exist = Meta::where('object_type', 'ticket')
	1256	+ ->where('key', 'settings')
	1257	+ ->where('object_id', $this->id)
	1258	+ ->first();
	1259	+
	1260	+ if ($exist) {
	1261	+ $existingValue = Helper::safeUnserialize($exist->value);
	1262	+
	1263	+ if (!is_array($existingValue)) {
	1264	+ $existingValue = [];
	1265	+ }
	1266	+
	1267	+ $existingValue[$valueKey] = $value;
	1268	+
	1269	+ $exist->value = maybe_serialize($existingValue);
	1270	+ $exist->save();
	1271	+ return $this;
	1272	+ }
	1273	+
	1274	+ $settings = [
	1275	+ 'object_type' => 'ticket',
	1276	+ 'key' => 'settings',
	1277	+ 'object_id' => $this->id,
	1278	+ 'value' => maybe_serialize([
	1279	+ $valueKey => $value
	1280	+ ])
	1281	+ ];
	1282	+
	1283	+ Meta::create($settings);
	1284	+
	1285	+ return $this;
	1286	+
	1287	+ }
	1288	+
497	1289	}