fluentform / app / Models / Submission.php

Submission.php in Fluent Forms – Customizable Contact Forms, Survey, Quiz, & Conversational Form Builder 6.2.3, at app/Models/Submission.php

467 lines 15.9 KB

Raw Download Zip

1	<?php
2
3	namespace FluentForm\App\Models;
4
5	use Exception;
6	use FluentForm\App\Modules\Payments\PaymentHelper;
7	use FluentForm\App\Services\Manager\FormManagerService;
8	use FluentForm\Framework\Support\Arr;
9
10	class Submission extends Model
11	{
12	/**
13	* The table associated with the model.
14	*
15	* @var string
16	*/
17	protected $table = 'fluentform_submissions';
18
19	/**
20	* A submission is owned by a User.
21	*
22	* @return \FluentForm\Framework\Database\Orm\Relations\BelongsTo
23	*/
24	public function user()
25	{
26	return $this->belongsTo(User::class, 'user_id', 'ID');
27	}
28
29	/**
30	* A submission is owned by a form.
31	*
32	* @return \FluentForm\Framework\Database\Orm\Relations\BelongsTo
33	*/
34	public function form()
35	{
36	return $this->belongsTo(Form::class, 'form_id', 'id');
37	}
38
39	/**
40	* A submission has many meta.
41	*
42	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
43	*/
44	public function submissionMeta()
45	{
46	return $this->hasMany(SubmissionMeta::class, 'response_id', 'id');
47	}
48
49	/**
50	* A submission has many logs.
51	*
52	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
53	*/
54	public function logs()
55	{
56	return $this->hasMany(Log::class, 'source_id', 'id');
57	}
58
59	/**
60	* A submission has many entry details.
61	*
62	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
63	*/
64	public function entryDetails()
65	{
66	return $this->hasMany(EntryDetails::class, 'submission_id', 'id');
67	}
68
69	/**
70	* A submission has many transactions.
71	*
72	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
73	*/
74	public function transactions()
75	{
76	return $this->hasMany(Transaction::class, 'submission_id', 'id');
77	}
78
79	/**
80	* A submission has many subscriptions.
81	*
82	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
83	*/
84	public function subscriptions()
85	{
86	return $this->hasMany(Subscription::class, 'submission_id', 'id');
87	}
88
89	/**
90	* A submission has many order items.
91	*
92	* @return \FluentForm\Framework\Database\Orm\Relations\HasMany
93	*/
94	public function orderItems()
95	{
96	return $this->hasMany(OrderItem::class, 'submission_id', 'id');
97	}
98
99	/**
100	* Returns the column the entries listing should be sorted by.
101	* Defaults to created_at so imported entries respect their original
102	* submission date. Site owners can switch back to legacy id-based
103	* ordering via the fluentform/entries_default_sort_column filter.
104	*/
105	public static function getSortColumn()
106	{
107	$column = apply_filters('fluentform/entries_default_sort_column', 'created_at');
108	$allowed = ['id', 'created_at'];
109
110	return in_array($column, $allowed, true) ? $column : 'created_at';
111	}
112
113	public function customQuery($attributes = [], $searchExtender = null)
114	{
115	$entryType = Arr::get($attributes, 'entry_type');
116	$dateRange = Arr::get($attributes, 'date_range');
117	$isFavourite = false;
118	$status = $entryType;
119
120	// We have to handle favorites separately because status and favorites are different
121	if ('favorites' === $entryType) {
122	$isFavourite = true;
123	$status = false;
124	}
125
126	$formId = Arr::get($attributes, 'form_id');
127	$startDate = Arr::get($dateRange, 0);
128	$endDate = Arr::get($dateRange, 1);
129	$search = Arr::get($attributes, 'search');
130	$sortBy = \FluentForm\App\Helpers\Helper::sanitizeOrderValue(Arr::get($attributes, 'sort_by', 'DESC'));
131
132	$wheres = [];
133	$paymentStatuses = Arr::get($attributes, 'payment_statuses');
134
135	if ($paymentStatuses && is_array($paymentStatuses)) {
136	$wheres[] = ['payment_status', $paymentStatuses];
137	}
138
139	// Sort by submission date so imported entries (which get new auto-increment ids
140	// but carry their original created_at) interleave correctly with native ones.
141	// Tie-break on id to keep pagination stable for rows sharing a timestamp.
142	// Filter lets site owners revert to legacy id-based ordering if needed.
143	$sortColumn = self::getSortColumn();
144	$query = $this->orderBy('fluentform_submissions.' . $sortColumn, $sortBy);
145	if ('id' !== $sortColumn) {
146	$query = $query->orderBy('fluentform_submissions.id', $sortBy);
147	}
148	$query = $query
149	->when($formId, function ($q) use ($formId) {
150	return $q->where('fluentform_submissions.form_id', $formId);
151	})
152	->when($isFavourite, function ($q) {
153	return $q->where('is_favourite', true);
154	})
155	->where(function ($q) use ($status) {
156	$operator = '=';
157
158	if (!$status) {
159	$operator = '!=';
160	$status = 'trashed';
161	}
162
163	return $q->where('fluentform_submissions.status', $operator, $status);
164	})
165	->when($startDate && $endDate, function ($q) use ($startDate, $endDate) {
166	$endDate .= ' 23:59:59';
167
168	return $q->where('fluentform_submissions.created_at', '>=', $startDate)
169	->where('fluentform_submissions.created_at', '<=', $endDate);
170	})
171	->when($search, function ($q) use ($search, $searchExtender) {
172	global $wpdb;
173	$escaped = $wpdb->esc_like($search);
174	return $q->where(function ($q) use ($escaped, $searchExtender) {
175	$q->where('fluentform_submissions.id', 'LIKE', "%{$escaped}%")
176	->orWhere('response', 'LIKE', "%{$escaped}%")
177	->orWhere('fluentform_submissions.status', 'LIKE', "%{$escaped}%")
178	->orWhere('fluentform_submissions.created_at', 'LIKE', "%{$escaped}%");
179	if ($searchExtender) {
180	$searchExtender($q, $escaped);
181	}
182	});
183	})
184	->when($wheres, function ($q) use ($wheres) {
185	foreach ($wheres as $where) {
186	if (is_array($where) && count($where) > 1) {
187	if (count($where) > 2) {
188	$column = $where[0];
189	$operator = $where[1];
190	$value = $where[2];
191	} else {
192	$column = $where[0];
193	$operator = '=';
194	$value = $where[1];
195	}
196
197	if (is_array($value)) {
198	return $q->whereIn($column, $value);
199	} else {
200	return $q->where($column, $operator, $value);
201	}
202	}
203	}
204	});
205
206	return $query;
207	}
208
209	public function paginateEntries($attributes = [])
210	{
211	$formId = Arr::get($attributes, 'form_id');
212	$allowFormIds = FormManagerService::getUserAllowedFormsScope();
213	$query = $this->customQuery($attributes);
214	$query = $query->when(false !== $allowFormIds, function ($q) use ($allowFormIds) {
215	return $q->whereIn('fluentform_submissions.form_id', $allowFormIds ?: [0]);
216	});
217	if (Arr::get($attributes, 'advanced_filter')) {
218	$query = apply_filters('fluentform/apply_entries_advance_filter', $query, $attributes);
219	}
220	$response = $query->paginate();
221	$response = apply_filters_deprecated(
222	'fluentform_get_raw_responses',
223	[
224	$response,
225	$formId
226	],
227	FLUENTFORM_FRAMEWORK_UPGRADE,
228	'fluentform/get_raw_responses',
229	'Use fluentform/get_raw_responses instead of fluentform_get_raw_responses.'
230	);
231
232	return apply_filters('fluentform/get_raw_responses', $response, $formId);
233	}
234
235	public function findAdjacentSubmission($attributes = [])
236	{
237	$sortBy = Arr::get($attributes, 'sort_by', 'DESC');
238
239	$direction = Arr::get($attributes, 'direction', 'next');
240
241	$operator = 'ASC' === $sortBy && 'previous' === $direction ? '>' : '<';
242
243	if ('previous' === $direction) {
244	$operator = 'ASC' === $sortBy ? '>' : '<';
245	} else {
246	$operator = 'ASC' === $sortBy ? '<' : '>';
247	$attributes['sort_by'] = 'ASC' === $sortBy ? 'DESC' : 'ASC';
248	}
249
250	$entryId = Arr::get($attributes, 'entry_id');
251
252	$columns = Arr::get($attributes, 'columns', 'id');
253
254	$query = $this->customQuery($attributes);
255
256	// Adjacency must match customQuery's sort order. Compare on the same
257	// (sortColumn, id) row-tuple the listing orders by, so Next/Prev walk
258	// in display order regardless of which sort column the filter selects.
259	// When sortColumn is id the tuple degenerates to a simple id compare.
260	$sortColumn = self::getSortColumn();
261	// Re-assert whitelist; $sortColumn is interpolated into whereRaw below.
262	if (!in_array($sortColumn, ['id', 'created_at'], true)) {
263	$sortColumn = 'created_at';
264	}
265	$current = static::select(['id', $sortColumn])->find($entryId);
266	if (!$current) {
267	return apply_filters('fluentform/next_submission', null, $entryId, $attributes);
268	}
269
270	global $wpdb;
271	$table = $wpdb->prefix . 'fluentform_submissions';
272	$submission = $query->select($columns)
273	->whereRaw(
274	"({$table}.{$sortColumn}, {$table}.id) {$operator} (?, ?)",
275	[$current->{$sortColumn}, $entryId]
276	)
277	->first();
278
279	return apply_filters('fluentform/next_submission', $submission, $entryId, $attributes);
280	}
281
282	public function countByGroup($formId)
283	{
284	$statuses = $this->selectRaw('status, COUNT(*) as count')
285	->where('form_id', $formId)
286	->groupBy('status')
287	->get();
288
289	$counts = [];
290
291	foreach ($statuses as $status) {
292	$counts[$status->status] = (int) $status->count;
293	}
294
295	$counts['all'] = array_sum($counts);
296
297	if (isset($counts['trashed'])) {
298	$counts['all'] -= $counts['trashed'];
299	}
300
301	$favorites = $this->where('form_id', $formId)
302	->where('is_favourite', 1)
303	->where('status', '!=', 'trashed')
304	->count();
305
306	$counts['favorites'] = $favorites;
307
308	return array_merge([
309	'unread' => 0,
310	'read' => 0,
311	'spam' => 0,
312	'trashed' => 0,
313	], $counts);
314	}
315
316	public function amend($id, $data = [])
317	{
318	$this->where('id', $id)->update($data);
319	}
320
321	public static function remove($submissionIds)
322	{
323	static::whereIn('id', $submissionIds)->delete();
324
325	SubmissionMeta::whereIn('response_id', $submissionIds)->delete();
326
327	Log::whereIn('source_id', $submissionIds)
328	->where('source_type', 'submission_item')
329	->delete();
330
331	EntryDetails::whereIn('submission_id', $submissionIds)->delete();
332
333	try {
334	if (PaymentHelper::hasPaymentSettings()) {
335	OrderItem::whereIn('submission_id', $submissionIds)->delete();
336	Transaction::whereIn('submission_id', $submissionIds)->delete();
337	Subscription::whereIn('submission_id', $submissionIds)->delete();
338	}
339
340	wpFluent()->table('ff_scheduled_actions')
341	->whereIn('origin_id', $submissionIds)
342	->where('type', 'submission_action')
343	->delete();
344
345	} catch (Exception $exception) {
346	// ...
347	}
348	}
349
350	public function allSubmissions($attributes = []) {
351	$searchExtender = function ($q, $escaped) {
352	$q->orWhereHas('form', function ($q) use ($escaped) {
353	$q->where('title', 'LIKE', "%{$escaped}%");
354	});
355	};
356	$customQuery = $this->customQuery($attributes, $searchExtender);
357	$allowFormIds = FormManagerService::getUserAllowedFormsScope();
358
359	$result = $customQuery
360	->with([
361	'form' => function ($q) {
362	$q->select(['id', 'title']);
363	}
364	])
365	->select(['id', 'form_id', 'status', 'created_at', 'browser', 'currency', 'total_paid'])
366	->when(false !== $allowFormIds, function ($q) use ($allowFormIds){
367	return $q->whereIn('form_id', $allowFormIds ?: [0]);
368	})
369	->paginate()
370	->toArray();
371
372	$useHumanDate = apply_filters('fluentform/entries_human_date', false);
373
374	foreach ($result['data'] as &$entry) {
375	$entry['entry_url'] = admin_url('admin.php?page=fluent_forms&route=entries&form_id=' . $entry['form_id'] . '#/entries/' . $entry['id']);
376
377	if ($useHumanDate) {
378	$entry['human_date'] = human_time_diff(strtotime($entry['created_at']), strtotime(current_time('mysql')));
379	}
380	}
381
382	$result['available_forms'] = $this->availableForms();
383
384	return $result;
385	}
386
387	public function availableForms()
388	{
389	$form = new Form();
390	if (false !== ($allowForms = FormManagerService::getUserAllowedFormsScope())) {
391	return $form->select('id', 'title')->whereIn('id', $allowForms ?: [0])->get();
392	}
393	return $form->select('id', 'title')->get();
394	}
395
396	public static function report($attributes)
397	{
398	$from = date('Y-m-d H:i:s', strtotime('-30 days'));
399	$to = date('Y-m-d H:i:s', strtotime('+1 days'));
400	$formId = Arr::get($attributes, 'form_id');
401	$allowFormIds = FormManagerService::getUserAllowedFormsScope();
402	$status = Arr::get($attributes, 'entry_status');
403	$start = Arr::get($attributes, 'date_range.0', '');
404	$end = Arr::get($attributes, 'date_range.1', '');
405	$dateRange = Arr::get($attributes, 'date_range');
406
407	if ('all' === $dateRange) {
408	$firstItem = self::orderBy('created_at', 'ASC')
409	->when(false !== $allowFormIds, function ($q) use ($allowFormIds) {
410	return $q->whereIn('form_id', $allowFormIds ?: [0]);
411	})
412	->when($formId, function ($q) use ($formId) {
413	return $q->where('form_id', $formId);
414	})
415	->when($status, function ($q2) use ($status) {
416	return $q2->where('status', $status);
417	})
418	->first();
419
420	if ($firstItem && $firstItem->created_at) {
421	$from = date('Y-m-d H:i:s', strtotime($firstItem->created_at));
422	$to = date('Y-m-d H:i:s');
423	}
424	}
425
426
427	if ($start && $startTime = strtotime($start)) {
428	$from = date('Y-m-d 00:00:00', $startTime);
429	}
430
431	if ($end && $endTime = strtotime($end)) {
432	$to = date('Y-m-d 23:59:59', $endTime);
433	}
434
435	$period = new \DatePeriod(new \DateTime($from), new \DateInterval('P1D'), new \DateTime($to));
436
437	$range = [];
438
439	foreach ($period as $date) {
440	$range[$date->format('Y-m-d')] = 0;
441	}
442
443	$items = self::selectRaw('DATE(created_at) AS date')
444	->selectRaw('COUNT(id) AS count')
445	->whereBetween('created_at', [$from, $to])
446	->groupBy('date')
447	->orderBy('date', 'ASC')
448	->when(false !== $allowFormIds, function ($q) use ($allowFormIds) {
449	return $q->whereIn('form_id', $allowFormIds ?: [0]);
450	})
451	->when($formId, function ($q) use ($formId) {
452	return $q->where('form_id', $formId);
453	})
454	->when($status, function ($q2) use ($status) {
455	return $q2->where('status', $status);
456	})
457	->get();
458
459
460	foreach ($items as $item) {
461	$range[$item->date] = $item->count;
462	}
463
464	return $range;
465	}
466	}
467