sureforms / inc / database / tables / entries.php

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

623 lines 19.0 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 = 2;
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	// NOT NULL DEFAULT 0 is load-bearing, not tidiness. An anonymous
124	// submission stores 0, and Forms_Data::calculate_form_metrics() excludes
125	// site editors with `user_id NOT IN (…)`. NOT IN never matches NULL, so
126	// making this column nullable would drop every anonymous entry from the
127	// numerator and report a conversion rate near 0% on every form.
128	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
129	'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
130	'logs LONGTEXT',
131	'notes LONGTEXT',
132	'submission_info LONGTEXT',
133	'status VARCHAR(10)',
134	'type VARCHAR(20)', // Note: @since 0.0.13 -- We have added type column, it will have entry's form type eg quiz, standard etc.
135	'extras LONGTEXT',
136	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
137	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
138	'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
139	'INDEX idx_user_id (user_id)',
140	'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
141	// Leads on the two columns Forms_Data::get_editing_submitter_ids()
142	// filters. idx_user_id alone cannot serve it -- created_at is not in it,
143	// and no other index leads on created_at -- so that lookup was a range
144	// scan with a row read per row plus a temp table for DISTINCT, on every
145	// Forms-list render.
146	'INDEX idx_user_id_created_at (user_id, created_at)',
147	];
148	}
149
150	/**
151	* {@inheritDoc}
152	*/
153	public function get_new_columns_definition() {
154	return [
155	// Note: @since 0.0.13 -- We have added new columns `type`, `extras` and `user_id`.
156	'type VARCHAR(20) AFTER status',
157	'extras LONGTEXT AFTER status',
158	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
159	'INDEX idx_user_id (user_id)',
160	// Note: @since 2.12.7 -- Covers the conversion rate's submitter lookup.
161	'INDEX idx_user_id_created_at (user_id, created_at)',
162	];
163	}
164
165	/**
166	* {@inheritDoc}
167	*/
168	public function get_columns_to_rename() {
169	return [
170	// Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
171	[
172	'from' => 'user_data',
173	'to' => 'form_data',
174	],
175	];
176	}
177
178	/**
179	* Retrieve the key of the last log entry.
180	*
181	* @since 0.0.10
182	* @return int\|null The key of the last log entry if logs exist, or null if no logs are present.
183	*/
184	public function get_last_log_key() {
185	$key = array_key_last( $this->logs );
186	return is_int( $key ) ? $key : null;
187	}
188
189	/**
190	* Add a new log entry.
191	*
192	* @param string $title The title of the log entry.
193	* @param array<string> $messages Optional. An array of messages to include in the log entry. Default is an empty array.
194	* @since 0.0.10
195	* @return int\|null The key of the newly added log entry, or null if the log could not be added.
196	*/
197	public function add_log( $title, $messages = [] ) {
198	$log = [
199	'title' => Helper::get_string_value( trim( $title ) ),
200	'messages' => Helper::get_array_value( $messages ),
201	'timestamp' => current_time( 'mysql' ), //phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp -- Using current_time() to match the WordPress timezone.
202	];
203
204	$this->logs = array_merge( [ $log ], $this->logs );
205
206	return $this->get_last_log_key();
207	}
208
209	/**
210	* Update an existing log entry.
211	*
212	* @param int\|null $log_key The key of the log entry to update.
213	* @param string\|null $title Optional. The new title for the log entry. If null, the title will not be changed.
214	* @param array<string> $messages Optional. An array of new messages to add to the log entry.
215	* @since 0.0.10
216	* @return int\|null The key of the updated log entry, or null if the log entry does not exist.
217	*/
218	public function update_log( $log_key, $title = null, $messages = [] ) {
219	if ( empty( $this->logs[ $log_key ] ) ) {
220	return null;
221	}
222
223	$logs = $this->logs;
224
225	$logs[ $log_key ]['title'] = ! is_null( $title ) ? Helper::get_string_value( trim( $title ) ) : $logs[ $log_key ]['title'];
226	$logs[ $log_key ]['messages'] = array_merge( Helper::get_array_value( $logs[ $log_key ]['messages'] ), Helper::get_array_value( $messages ) );
227
228	$this->logs = $logs;
229	return $log_key;
230	}
231
232	/**
233	* Resets logs to zero.
234	*
235	* @since 1.3.0
236	* @return void
237	*/
238	public function reset_logs() {
239	$this->logs = [];
240	}
241
242	/**
243	* Retrieve all log entries.
244	*
245	* @since 0.0.10
246	* @return array<array<string,mixed>>
247	*/
248	public function get_logs() {
249	return $this->logs;
250	}
251
252	/**
253	* Add a new entry to the database.
254	*
255	* @param array<mixed> $data An associative array of data for the new entry. Must include 'form_id'.
256	* If 'ID' is set, it will be removed before inserting.
257	* @since 0.0.10
258	* @return int\|false The number of rows inserted, or false if the insertion fails.
259	*/
260	public static function add( $data ) {
261	if ( empty( $data['form_id'] ) ) {
262	return false;
263	}
264
265	if ( isset( $data['ID'] ) ) {
266	// Unset ID if exists because we are creating a new entry, not updating.
267	unset( $data['ID'] );
268	}
269
270	$instance = self::get_instance();
271
272	if ( ! isset( $data['logs'] ) ) {
273	// Add default logs if no logs provided.
274	$data['logs'] = $instance->get_logs();
275	}
276
277	$result = $instance->use_insert( $data );
278
279	if ( ! $result ) {
280	// A failed entries write is the live-drop signal issue #3084 describes: the
281	// table can have been dropped after being cached as present. Drop that cache
282	// so the next admin load re-checks instead of trusting a stale answer.
283	\SRFM\Inc\Database\Register::flush_entries_table_cache();
284	}
285
286	return $result;
287	}
288
289	/**
290	* Update an entry by entry id.
291	*
292	* @param int $entry_id Entry ID.
293	* @param array<string,mixed> $data Data to update.
294	* @since 0.0.13
295	* @return int\|false The number of rows updated, or false on error.
296	*/
297	public static function update( $entry_id, $data = [] ) {
298	if ( empty( $entry_id ) ) {
299	return false;
300	}
301
302	if ( isset( $data['logs'] ) ) {
303	// Add logs from the current cache at the very last moment so that we don't tax the performance.
304	$data['logs'] = array_merge( Helper::get_array_value( $data['logs'] ), Helper::get_array_value( self::get( $entry_id )['logs'] ) );
305	}
306
307	return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
308	}
309
310	/**
311	* Delete an entry by entry id.
312	*
313	* @param int $entry_id Entry ID to delete.
314	* @since 0.0.13
315	* @return int\|false The number of rows deleted, or false on error.
316	*/
317	public static function delete( $entry_id ) {
318	// Add action before deleting the entry.
319	do_action( 'srfm_before_delete_entry', $entry_id );
320
321	// Delete the entry.
322	return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
323	}
324
325	/**
326	* Retrieve a specific entry from the database.
327	*
328	* @param int $entry_id The ID of the entry to retrieve.
329	* @since 0.0.10
330	* @return array<mixed> An associative array representing the entry, or an empty array if no entry is found.
331	*/
332	public static function get( $entry_id ) {
333	$results = self::get_instance()->get_results(
334	[
335	'ID' => $entry_id,
336	]
337	);
338
339	return isset( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];
340	}
341
342	/**
343	* Retrieves a list of records based on the provided arguments.
344	*
345	* This method fetches results from the database, allowing for various
346	* customization options such as filtering, pagination, and sorting.
347	*
348	* @param array<string,mixed> $args {
349	* Optional. An array of arguments to customize the query.
350	*
351	* @type array $where An associative array of conditions to filter the results.
352	* @type int $limit The maximum number of results to return. Default is 10.
353	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
354	* @type string $orderby The column by which to order the results. Default is 'created_at'.
355	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
356	* }
357	* @param bool $set_limit Whether to set the limit on the query. Default is true.
358	*
359	* @since 0.0.13
360	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
361	*/
362	public static function get_all( $args = [], $set_limit = true ) {
363	/**
364	* Refactored the get_all method to use the get_records_by_args method from the Base class.
365	* Moved the common logic inside the Base class so it can be reused by other tables as well.
366	*
367	* @since 1.13.0
368	*/
369	return self::get_instance()->get_records_by_args( $args, $set_limit );
370	}
371
372	/**
373	* Get the total count of entries by status.
374	*
375	* @param string $status The status of the entries to count.
376	* @param int\|null $form_id The ID of the form to count entries for.
377	* @param array<mixed> $where_clause Additional where clause to add to the query.
378	* @since 0.0.13
379	* @return int The total number of entries with the specified status.
380	*/
381	public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
382	switch ( $status ) {
383	case 'all':
384	$where_clause[] =
385	[
386	[
387	'key' => 'status',
388	'compare' => '!=',
389	'value' => 'trash',
390	],
391	];
392	if ( 0 < $form_id ) {
393	$where_clause[] = [
394	[
395	'key' => 'form_id',
396	'compare' => '=',
397	'value' => $form_id,
398	],
399	];
400	}
401	return self::get_instance()->get_total_count( $where_clause );
402	case 'unread':
403	case 'trash':
404	$where_clause[] = [
405	[
406	'key' => 'status',
407	'compare' => '=',
408	'value' => $status,
409	],
410	];
411	return self::get_instance()->get_total_count( $where_clause );
412	default:
413	return self::get_instance()->get_total_count();
414	}
415	}
416
417	/**
418	* Get the total number of entries created after the given timestamp.
419	*
420	* @param int $timestamp Timestamp in seconds.
421	* @param int $form_id Optional. The ID of the form to count entries for. Default 0 for all forms.
422	* @since 1.7.3
423	* @return int Total number of entries created after the timestamp.
424	*/
425	public static function get_entries_count_after( $timestamp, $form_id = 0 ) {
426	$timestamp = absint( $timestamp );
427
428	if ( ! $timestamp ) {
429	return self::get_total_entries_by_status( 'all', $form_id );
430	}
431
432	global $wpdb;
433
434	// 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.
435	$mysql_time = $wpdb->get_var( 'SELECT NOW()' );
436
437	// Convert to timestamps.
438	$mysql_timestamp = strtotime( $mysql_time );
439	$php_timestamp = time();
440
441	// Offset between MySQL and PHP.
442	$offset_seconds = $mysql_timestamp - $php_timestamp;
443
444	$adjusted_timestamp = $timestamp + $offset_seconds;
445
446	$where_clause = [
447	[
448	[
449	'key' => 'created_at',
450	'compare' => '>',
451	'value' => gmdate( 'Y-m-d H:i:s', $adjusted_timestamp ),
452	],
453	],
454	];
455
456	return self::get_total_entries_by_status( 'all', $form_id, $where_clause );
457	}
458
459	/**
460	* Get the available months for entries.
461	*
462	* @param array<string,mixed> $where_clause Additional where clause to add to the query.
463	* @since 0.0.13
464	* @return array<int\|string, mixed>
465	*/
466	public static function get_available_months( $where_clause = [] ) {
467	$results = self::get_instance()->get_results(
468	$where_clause,
469	'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
470	[
471	'ORDER BY month_value ASC',
472	],
473	false
474	);
475
476	$months = [];
477	foreach ( $results as $result ) {
478	if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
479	$months[ $result['month_value'] ] = $result['month_label'];
480	}
481	}
482	return $months;
483	}
484
485	/**
486	* Get all the entry ID's for a form.
487	* The data is used for checking unique field validation.
488	*
489	* @param int $form_id The ID of the form to fetch entry IDs for.
490	* @since 1.0.0
491	* @return array<mixed> An array of entry IDs.
492	*/
493	public static function get_all_entry_ids_for_form( $form_id ) {
494	return self::get_instance()->get_results(
495	[ 'form_id' => $form_id ],
496	'ID',
497	[
498	'ORDER BY ID DESC',
499	]
500	);
501	}
502
503	/**
504	* Check if any non-trashed entry for a given form contains a specific field value.
505	*
506	* Uses a single SQL query with JSON_EXTRACT on the form_data column
507	* instead of loading all entries into PHP. Stops at the first match.
508	*
509	* SECURITY INVARIANT — do not relax the key matching. The only caller is the
510	* unauthenticated uniqueness check (Form_Submit::field_unique_validation()), which
511	* allows a probe only for fields the form marks unique. That restriction holds
512	* because the lookup is anchored to the EXACT submitted key: a stored form_data key
513	* always embeds its own block ID, so a key that resolves to field X can only carry
514	* X's block ID. Matching on block ID instead, or switching to LIKE / JSON_SEARCH,
515	* would break that anchoring and widen the probe beyond the allowlisted field.
516	*
517	* @param int $form_id The form ID to search within.
518	* @param string $field_key The form_data JSON key to match against.
519	* @param string $field_value The value to check for uniqueness.
520	* @since 2.7.0
521	* @return bool True if a duplicate exists, false otherwise.
522	*/
523	public static function has_duplicate_field_value( $form_id, $field_key, $field_value ) {
524	if ( empty( $form_id ) \|\| empty( $field_key ) \|\| '' === $field_value ) {
525	return false;
526	}
527
528	global $wpdb;
529	$table_name = self::get_instance()->get_tablename();
530
531	// $wpdb->prepare() escapes this for SQL, but the key is interpolated into a JSON
532	// path string, where a quote or backslash would change the path's meaning rather
533	// than break the query. Neutralise both so the path can only ever be a single
534	// quoted member access.
535	$json_path = '$."' . str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $field_key ) . '"';
536
537	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-off existence check; table name from get_tablename() (not user input); caching not beneficial for uniqueness validation.
538	$exists = $wpdb->get_var(
539	$wpdb->prepare(
540	"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.
541	$form_id,
542	$json_path,
543	$field_value
544	)
545	);
546
547	return null !== $exists;
548	}
549
550	/**
551	* Get form IDs associated with a list of entry IDs.
552	* This method retrieves the distinct form IDs that are linked to the provided entry IDs.
553	*
554	* @param array<int> $entry_ids An array of entry IDs to fetch associated form IDs for.
555	* @since 1.1.1
556	* @return array<int> An array of form IDs.
557	*/
558	public static function get_form_ids_by_entries( $entry_ids ) {
559	if ( empty( $entry_ids ) \|\| ! is_array( $entry_ids ) ) {
560	return [];
561	}
562
563	$results = self::get_instance()->get_results(
564	[
565	[
566	[
567	'key' => 'ID',
568	'compare' => 'IN',
569	'value' => $entry_ids,
570	],
571	],
572	],
573	'DISTINCT form_id'
574	);
575
576	return array_map( 'absint', array_column( $results, 'form_id' ) ); // Flatten the array.
577	}
578
579	/**
580	* Get the form data for a specific entry.
581	*
582	* @param int $entry_id The ID of the entry to get the form data for.
583	* @since 1.0.0
584	* @return array<string,mixed> An associative array representing the entry's form data.
585	*/
586	public static function get_form_data( $entry_id ) {
587	$result = self::get_instance()->get_results(
588	[ 'ID' => $entry_id ],
589	'form_data'
590	);
591	return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['form_data'] ) : [];
592	}
593
594	/**
595	* Get the entry data for a specific entry.
596	*
597	* @param int $entry_id The ID of the entry to get the entry data for.
598	* @since 1.8.0
599	* @return array<string,mixed> An associative array representing the entry's data.
600	*/
601	public static function get_entry_data( $entry_id ) {
602	$result = self::get_instance()->get_results(
603	[ 'ID' => $entry_id ],
604	'form_data, extras'
605	);
606	return isset( $result[0] ) && is_array( $result[0] ) ? $result[0] : [];
607	}
608
609	/**
610	* {@inheritDoc}
611	*
612	* Restricts orderable columns to indexed, semantically meaningful fields.
613	* Excludes LONGTEXT blob columns (form_data, submission_info, notes, logs, extras)
614	* to prevent full-table sorts on un-indexed columns.
615	*
616	* @since 2.6.0
617	* @return array<string>
618	*/
619	protected function get_allowed_orderby_columns() {
620	return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'created_at', 'updated_at' ];
621	}
622	}
623