sureforms / inc / database / tables / entries.php

entries.php in SureForms – Contact Form Builder, AI Forms, Payment Form, Survey & Quiz 2.10.0, at inc/database/tables/entries.php

588 lines 16.8 KB

Raw Download Zip

1	<?php
2	/**
3	* SureForms Database Entires Table Class.
4	*
5	* @link https://sureforms.com
6	* @since 0.0.10
7	* @package SureForms
8	* @author SureForms <https://sureforms.com/>
9	*/
10
11	namespace SRFM\Inc\Database\Tables;
12
13	use SRFM\Inc\Database\Base;
14	use SRFM\Inc\Helper;
15	use SRFM\Inc\Traits\Get_Instance;
16
17	// Exit if accessed directly.
18	defined( 'ABSPATH' ) \|\| exit;
19
20	/**
21	* SureForms Database Entires Table Class.
22	*
23	* @since 0.0.10
24	*/
25	class Entries extends Base {
26	use Get_Instance;
27
28	/**
29	* {@inheritDoc}
30	*
31	* @var string
32	*/
33	protected $table_suffix = 'entries';
34
35	/**
36	* {@inheritDoc}
37	*
38	* @var int
39	*/
40	protected $table_version = 1;
41
42	/**
43	* Current logs.
44	*
45	* @var array<array<string,mixed>> $logs
46	* The structure of each log entry is:
47	* [
48	* 'title' => string,
49	* 'messages' => array<string>,
50	* 'timestamp' => int
51	* ]
52	*
53	* @since 0.0.10
54	*/
55	private $logs = [];
56
57	/**
58	* {@inheritDoc}
59	*/
60	public function get_schema() {
61	return [
62	// Entry ID.
63	'ID' => [
64	'type' => 'number',
65	],
66	// Submitted form ID.
67	'form_id' => [
68	'type' => 'number',
69	],
70	// User ID.
71	'user_id' => [
72	'type' => 'number',
73	'default' => 0,
74	],
75	// Current entry status: 'read', 'unread' and 'trash'.
76	'status' => [
77	'type' => 'string',
78	'default' => 'unread',
79	],
80	// Entry's form type eg quiz, standard etc. Default empty or null means standard.
81	'type' => [
82	'type' => 'string',
83	],
84	// Submitted form data by user.
85	'form_data' => [
86	'type' => 'array',
87	'default' => [],
88	],
89	// Additional information about the current submitted data: i.e: Browser type, device etc.
90	'submission_info' => [
91	'type' => 'array',
92	'default' => [],
93	],
94	// Any additional notes that is added by the admin.
95	'notes' => [
96	'type' => 'array',
97	'default' => [],
98	],
99	// Entry activities logs.
100	'logs' => [
101	'type' => 'array',
102	'default' => [],
103	],
104	// Entry submitted date and time.
105	'created_at' => [
106	'type' => 'datetime',
107	],
108	// Any misc extra data that needs to be saved.
109	'extras' => [
110	'type' => 'array',
111	'default' => [],
112	],
113	];
114	}
115
116	/**
117	* {@inheritDoc}
118	*/
119	public function get_columns_definition() {
120	return [
121	'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
122	'form_id BIGINT(20) UNSIGNED',
123	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
124	'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
125	'logs LONGTEXT',
126	'notes LONGTEXT',
127	'submission_info LONGTEXT',
128	'status VARCHAR(10)',
129	'type VARCHAR(20)', // Note: @since 0.0.13 -- We have added type column, it will have entry's form type eg quiz, standard etc.
130	'extras LONGTEXT',
131	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
132	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
133	'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
134	'INDEX idx_user_id (user_id)',
135	'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
136	];
137	}
138
139	/**
140	* {@inheritDoc}
141	*/
142	public function get_new_columns_definition() {
143	return [
144	// Note: @since 0.0.13 -- We have added new columns `type`, `extras` and `user_id`.
145	'type VARCHAR(20) AFTER status',
146	'extras LONGTEXT AFTER status',
147	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
148	'INDEX idx_user_id (user_id)',
149	];
150	}
151
152	/**
153	* {@inheritDoc}
154	*/
155	public function get_columns_to_rename() {
156	return [
157	// Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
158	[
159	'from' => 'user_data',
160	'to' => 'form_data',
161	],
162	];
163	}
164
165	/**
166	* Retrieve the key of the last log entry.
167	*
168	* @since 0.0.10
169	* @return int\|null The key of the last log entry if logs exist, or null if no logs are present.
170	*/
171	public function get_last_log_key() {
172	$key = array_key_last( $this->logs );
173	return is_int( $key ) ? $key : null;
174	}
175
176	/**
177	* Add a new log entry.
178	*
179	* @param string $title The title of the log entry.
180	* @param array<string> $messages Optional. An array of messages to include in the log entry. Default is an empty array.
181	* @since 0.0.10
182	* @return int\|null The key of the newly added log entry, or null if the log could not be added.
183	*/
184	public function add_log( $title, $messages = [] ) {
185	$log = [
186	'title' => Helper::get_string_value( trim( $title ) ),
187	'messages' => Helper::get_array_value( $messages ),
188	'timestamp' => current_time( 'mysql' ), //phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp -- Using current_time() to match the WordPress timezone.
189	];
190
191	$this->logs = array_merge( [ $log ], $this->logs );
192
193	return $this->get_last_log_key();
194	}
195
196	/**
197	* Update an existing log entry.
198	*
199	* @param int\|null $log_key The key of the log entry to update.
200	* @param string\|null $title Optional. The new title for the log entry. If null, the title will not be changed.
201	* @param array<string> $messages Optional. An array of new messages to add to the log entry.
202	* @since 0.0.10
203	* @return int\|null The key of the updated log entry, or null if the log entry does not exist.
204	*/
205	public function update_log( $log_key, $title = null, $messages = [] ) {
206	if ( empty( $this->logs[ $log_key ] ) ) {
207	return null;
208	}
209
210	$logs = $this->logs;
211
212	$logs[ $log_key ]['title'] = ! is_null( $title ) ? Helper::get_string_value( trim( $title ) ) : $logs[ $log_key ]['title'];
213	$logs[ $log_key ]['messages'] = array_merge( Helper::get_array_value( $logs[ $log_key ]['messages'] ), Helper::get_array_value( $messages ) );
214
215	$this->logs = $logs;
216	return $log_key;
217	}
218
219	/**
220	* Resets logs to zero.
221	*
222	* @since 1.3.0
223	* @return void
224	*/
225	public function reset_logs() {
226	$this->logs = [];
227	}
228
229	/**
230	* Retrieve all log entries.
231	*
232	* @since 0.0.10
233	* @return array<array<string,mixed>>
234	*/
235	public function get_logs() {
236	return $this->logs;
237	}
238
239	/**
240	* Add a new entry to the database.
241	*
242	* @param array<mixed> $data An associative array of data for the new entry. Must include 'form_id'.
243	* If 'ID' is set, it will be removed before inserting.
244	* @since 0.0.10
245	* @return int\|false The number of rows inserted, or false if the insertion fails.
246	*/
247	public static function add( $data ) {
248	if ( empty( $data['form_id'] ) ) {
249	return false;
250	}
251
252	if ( isset( $data['ID'] ) ) {
253	// Unset ID if exists because we are creating a new entry, not updating.
254	unset( $data['ID'] );
255	}
256
257	$instance = self::get_instance();
258
259	if ( ! isset( $data['logs'] ) ) {
260	// Add default logs if no logs provided.
261	$data['logs'] = $instance->get_logs();
262	}
263
264	return $instance->use_insert( $data );
265	}
266
267	/**
268	* Update an entry by entry id.
269	*
270	* @param int $entry_id Entry ID.
271	* @param array<string,mixed> $data Data to update.
272	* @since 0.0.13
273	* @return int\|false The number of rows updated, or false on error.
274	*/
275	public static function update( $entry_id, $data = [] ) {
276	if ( empty( $entry_id ) ) {
277	return false;
278	}
279
280	if ( isset( $data['logs'] ) ) {
281	// Add logs from the current cache at the very last moment so that we don't tax the performance.
282	$data['logs'] = array_merge( Helper::get_array_value( $data['logs'] ), Helper::get_array_value( self::get( $entry_id )['logs'] ) );
283	}
284
285	return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
286	}
287
288	/**
289	* Delete an entry by entry id.
290	*
291	* @param int $entry_id Entry ID to delete.
292	* @since 0.0.13
293	* @return int\|false The number of rows deleted, or false on error.
294	*/
295	public static function delete( $entry_id ) {
296	// Add action before deleting the entry.
297	do_action( 'srfm_before_delete_entry', $entry_id );
298
299	// Delete the entry.
300	return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
301	}
302
303	/**
304	* Retrieve a specific entry from the database.
305	*
306	* @param int $entry_id The ID of the entry to retrieve.
307	* @since 0.0.10
308	* @return array<mixed> An associative array representing the entry, or an empty array if no entry is found.
309	*/
310	public static function get( $entry_id ) {
311	$results = self::get_instance()->get_results(
312	[
313	'ID' => $entry_id,
314	]
315	);
316
317	return isset( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];
318	}
319
320	/**
321	* Retrieves a list of records based on the provided arguments.
322	*
323	* This method fetches results from the database, allowing for various
324	* customization options such as filtering, pagination, and sorting.
325	*
326	* @param array<string,mixed> $args {
327	* Optional. An array of arguments to customize the query.
328	*
329	* @type array $where An associative array of conditions to filter the results.
330	* @type int $limit The maximum number of results to return. Default is 10.
331	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
332	* @type string $orderby The column by which to order the results. Default is 'created_at'.
333	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
334	* }
335	* @param bool $set_limit Whether to set the limit on the query. Default is true.
336	*
337	* @since 0.0.13
338	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
339	*/
340	public static function get_all( $args = [], $set_limit = true ) {
341	/**
342	* Refactored the get_all method to use the get_records_by_args method from the Base class.
343	* Moved the common logic inside the Base class so it can be reused by other tables as well.
344	*
345	* @since 1.13.0
346	*/
347	return self::get_instance()->get_records_by_args( $args, $set_limit );
348	}
349
350	/**
351	* Get the total count of entries by status.
352	*
353	* @param string $status The status of the entries to count.
354	* @param int\|null $form_id The ID of the form to count entries for.
355	* @param array<mixed> $where_clause Additional where clause to add to the query.
356	* @since 0.0.13
357	* @return int The total number of entries with the specified status.
358	*/
359	public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
360	switch ( $status ) {
361	case 'all':
362	$where_clause[] =
363	[
364	[
365	'key' => 'status',
366	'compare' => '!=',
367	'value' => 'trash',
368	],
369	];
370	if ( 0 < $form_id ) {
371	$where_clause[] = [
372	[
373	'key' => 'form_id',
374	'compare' => '=',
375	'value' => $form_id,
376	],
377	];
378	}
379	return self::get_instance()->get_total_count( $where_clause );
380	case 'unread':
381	case 'trash':
382	$where_clause[] = [
383	[
384	'key' => 'status',
385	'compare' => '=',
386	'value' => $status,
387	],
388	];
389	return self::get_instance()->get_total_count( $where_clause );
390	default:
391	return self::get_instance()->get_total_count();
392	}
393	}
394
395	/**
396	* Get the total number of entries created after the given timestamp.
397	*
398	* @param int $timestamp Timestamp in seconds.
399	* @param int $form_id Optional. The ID of the form to count entries for. Default 0 for all forms.
400	* @since 1.7.3
401	* @return int Total number of entries created after the timestamp.
402	*/
403	public static function get_entries_count_after( $timestamp, $form_id = 0 ) {
404	$timestamp = absint( $timestamp );
405
406	if ( ! $timestamp ) {
407	return self::get_total_entries_by_status( 'all', $form_id );
408	}
409
410	global $wpdb;
411
412	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct DB access is required here to get the most accurate server time for menu badge logic. Caching is not suitable as this is used for real-time admin notifications.
413	$mysql_time = $wpdb->get_var( 'SELECT NOW()' );
414
415	// Convert to timestamps.
416	$mysql_timestamp = strtotime( $mysql_time );
417	$php_timestamp = time();
418
419	// Offset between MySQL and PHP.
420	$offset_seconds = $mysql_timestamp - $php_timestamp;
421
422	$adjusted_timestamp = $timestamp + $offset_seconds;
423
424	$where_clause = [
425	[
426	[
427	'key' => 'created_at',
428	'compare' => '>',
429	'value' => gmdate( 'Y-m-d H:i:s', $adjusted_timestamp ),
430	],
431	],
432	];
433
434	return self::get_total_entries_by_status( 'all', $form_id, $where_clause );
435	}
436
437	/**
438	* Get the available months for entries.
439	*
440	* @param array<string,mixed> $where_clause Additional where clause to add to the query.
441	* @since 0.0.13
442	* @return array<int\|string, mixed>
443	*/
444	public static function get_available_months( $where_clause = [] ) {
445	$results = self::get_instance()->get_results(
446	$where_clause,
447	'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
448	[
449	'ORDER BY month_value ASC',
450	],
451	false
452	);
453
454	$months = [];
455	foreach ( $results as $result ) {
456	if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
457	$months[ $result['month_value'] ] = $result['month_label'];
458	}
459	}
460	return $months;
461	}
462
463	/**
464	* Get all the entry ID's for a form.
465	* The data is used for checking unique field validation.
466	*
467	* @param int $form_id The ID of the form to fetch entry IDs for.
468	* @since 1.0.0
469	* @return array<mixed> An array of entry IDs.
470	*/
471	public static function get_all_entry_ids_for_form( $form_id ) {
472	return self::get_instance()->get_results(
473	[ 'form_id' => $form_id ],
474	'ID',
475	[
476	'ORDER BY ID DESC',
477	]
478	);
479	}
480
481	/**
482	* Check if any non-trashed entry for a given form contains a specific field value.
483	*
484	* Uses a single SQL query with JSON_EXTRACT on the form_data column
485	* instead of loading all entries into PHP. Stops at the first match.
486	*
487	* @param int $form_id The form ID to search within.
488	* @param string $field_key The form_data JSON key to match against.
489	* @param string $field_value The value to check for uniqueness.
490	* @since 2.7.0
491	* @return bool True if a duplicate exists, false otherwise.
492	*/
493	public static function has_duplicate_field_value( $form_id, $field_key, $field_value ) {
494	if ( empty( $form_id ) \|\| empty( $field_key ) \|\| '' === $field_value ) {
495	return false;
496	}
497
498	global $wpdb;
499	$table_name = self::get_instance()->get_tablename();
500	$json_path = '$."' . $field_key . '"';
501
502	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-off existence check; caching not beneficial for uniqueness validation.
503	$exists = $wpdb->get_var(
504	$wpdb->prepare(
505	"SELECT 1 FROM {$table_name} WHERE form_id = %d AND status != 'trash' AND JSON_UNQUOTE(JSON_EXTRACT(form_data, %s)) = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally generated.
506	$form_id,
507	$json_path,
508	$field_value
509	)
510	);
511
512	return null !== $exists;
513	}
514
515	/**
516	* Get form IDs associated with a list of entry IDs.
517	* This method retrieves the distinct form IDs that are linked to the provided entry IDs.
518	*
519	* @param array<int> $entry_ids An array of entry IDs to fetch associated form IDs for.
520	* @since 1.1.1
521	* @return array<int> An array of form IDs.
522	*/
523	public static function get_form_ids_by_entries( $entry_ids ) {
524	if ( empty( $entry_ids ) && ! is_array( $entry_ids ) ) {
525	return [];
526	}
527
528	$results = self::get_instance()->get_results(
529	[
530	[
531	[
532	'key' => 'ID',
533	'compare' => 'IN',
534	'value' => $entry_ids,
535	],
536	],
537	],
538	'DISTINCT form_id'
539	);
540
541	return array_map( 'absint', array_column( $results, 'form_id' ) ); // Flatten the array.
542	}
543
544	/**
545	* Get the form data for a specific entry.
546	*
547	* @param int $entry_id The ID of the entry to get the form data for.
548	* @since 1.0.0
549	* @return array<string,mixed> An associative array representing the entry's form data.
550	*/
551	public static function get_form_data( $entry_id ) {
552	$result = self::get_instance()->get_results(
553	[ 'ID' => $entry_id ],
554	'form_data'
555	);
556	return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['form_data'] ) : [];
557	}
558
559	/**
560	* Get the entry data for a specific entry.
561	*
562	* @param int $entry_id The ID of the entry to get the entry data for.
563	* @since 1.8.0
564	* @return array<string,mixed> An associative array representing the entry's data.
565	*/
566	public static function get_entry_data( $entry_id ) {
567	$result = self::get_instance()->get_results(
568	[ 'ID' => $entry_id ],
569	'form_data, extras'
570	);
571	return isset( $result[0] ) && is_array( $result[0] ) ? $result[0] : [];
572	}
573
574	/**
575	* {@inheritDoc}
576	*
577	* Restricts orderable columns to indexed, semantically meaningful fields.
578	* Excludes LONGTEXT blob columns (form_data, submission_info, notes, logs, extras)
579	* to prevent full-table sorts on un-indexed columns.
580	*
581	* @since 2.6.0
582	* @return array<string>
583	*/
584	protected function get_allowed_orderby_columns() {
585	return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'created_at', 'updated_at' ];
586	}
587	}
588