sureforms / inc / database / tables / entries.php

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

409 lines 11.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 = 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 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	$this->logs[] = [
186	'title' => Helper::get_string_value( trim( $title ) ),
187	'messages' => Helper::get_array_value( $messages ),
188	'timestamp' => time(),
189	];
190
191	return $this->get_last_log_key();
192	}
193
194	/**
195	* Update an existing log entry.
196	*
197	* @param int $log_key The key of the log entry to update.
198	* @param string\|null $title Optional. The new title for the log entry. If null, the title will not be changed.
199	* @param string[] $messages Optional. An array of new messages to add to the log entry.
200	* @since 0.0.10
201	* @return int\|null The key of the updated log entry, or null if the log entry does not exist.
202	*/
203	public function update_log( $log_key, $title = null, $messages = [] ) {
204	if ( empty( $this->logs[ $log_key ] ) ) {
205	return null;
206	}
207
208	$logs = $this->logs;
209
210	$logs[ $log_key ]['title'] = ! is_null( $title ) ? Helper::get_string_value( trim( $title ) ) : $logs[ $log_key ]['title'];
211	$logs[ $log_key ]['messages'] = array_merge( Helper::get_array_value( $logs[ $log_key ]['messages'] ), Helper::get_array_value( $messages ) );
212
213	$this->logs = $logs;
214	return $log_key;
215	}
216
217	/**
218	* Retrieve all log entries.
219	*
220	* @since 0.0.10
221	* @return array<array<string,mixed>>
222	*/
223	public function get_logs() {
224	return $this->logs;
225	}
226
227	/**
228	* Add a new entry to the database.
229	*
230	* @param array<mixed> $data An associative array of data for the new entry. Must include 'form_id'.
231	* If 'ID' is set, it will be removed before inserting.
232	* @since 0.0.10
233	* @return int\|false The number of rows inserted, or false if the insertion fails.
234	*/
235	public static function add( $data ) {
236	if ( empty( $data['form_id'] ) ) {
237	return false;
238	}
239
240	if ( isset( $data['ID'] ) ) {
241	// Unset ID if exists because we are creating a new entry, not updating.
242	unset( $data['ID'] );
243	}
244
245	$instance = self::get_instance();
246
247	if ( ! isset( $data['logs'] ) ) {
248	// Add default logs if no logs provided.
249	$data['logs'] = $instance->get_logs();
250	}
251
252	return $instance->use_insert( $data );
253	}
254
255	/**
256	* Update an entry by entry id.
257	*
258	* @param int $entry_id Entry ID.
259	* @param array<string,mixed> $data Data to update.
260	* @since 0.0.13
261	* @return int\|false The number of rows updated, or false on error.
262	*/
263	public static function update( $entry_id, $data = [] ) {
264	if ( empty( $entry_id ) ) {
265	return false;
266	}
267	return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
268	}
269
270	/**
271	* Delete an entry by entry id.
272	*
273	* @param int $entry_id Entry ID to delete.
274	* @since 0.0.13
275	* @return int\|false The number of rows deleted, or false on error.
276	*/
277	public static function delete( $entry_id ) {
278	return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
279	}
280
281	/**
282	* Retrieve a specific entry from the database.
283	*
284	* @param int $entry_id The ID of the entry to retrieve.
285	* @since 0.0.10
286	* @return array<mixed> An associative array representing the entry, or an empty array if no entry is found.
287	*/
288	public static function get( $entry_id ) {
289	$results = self::get_instance()->get_results(
290	[
291	'ID' => $entry_id,
292	]
293	);
294
295	return isset( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];
296	}
297
298	/**
299	* Retrieves a list of records based on the provided arguments.
300	*
301	* This method fetches results from the database, allowing for various
302	* customization options such as filtering, pagination, and sorting.
303	*
304	* @param array<string,mixed> $args {
305	* Optional. An array of arguments to customize the query.
306	*
307	* @type array $where An associative array of conditions to filter the results.
308	* @type int $limit The maximum number of results to return. Default is 10.
309	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
310	* @type string $orderby The column by which to order the results. Default is 'created_at'.
311	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
312	* }
313	*
314	* @since 0.0.13
315	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
316	*/
317	public static function get_all( $args = [] ) {
318	$_args = wp_parse_args(
319	$args,
320	[
321	'where' => [],
322	'limit' => 10,
323	'offset' => 0,
324	'orderby' => 'created_at',
325	'order' => 'DESC',
326	]
327	);
328	return self::get_instance()->get_results(
329	$_args['where'],
330	'*',
331	[
332	sprintf( 'ORDER BY `%1$s` %2$s', Helper::get_string_value( esc_sql( $_args['orderby'] ) ), Helper::get_string_value( esc_sql( $_args['order'] ) ) ),
333	sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) ),
334	]
335	);
336	}
337
338	/**
339	* Get the total count of entries by status.
340	*
341	* @param string $status The status of the entries to count.
342	* @param int\|null $form_id The ID of the form to count entries for.
343	* @param array<string,mixed> $where_clause Additional where clause to add to the query.
344	* @since 0.0.13
345	* @return int The total number of entries with the specified status.
346	*/
347	public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
348	switch ( $status ) {
349	case 'all':
350	$where_clause[] =
351	[
352	[
353	'key' => 'status',
354	'compare' => '!=',
355	'value' => 'trash',
356	],
357	];
358	if ( 0 < $form_id ) {
359	$where_clause[] = [
360	[
361	'key' => 'form_id',
362	'compare' => '=',
363	'value' => $form_id,
364	],
365	];
366	}
367	return self::get_instance()->get_total_count( $where_clause );
368	case 'unread':
369	case 'trash':
370	$where_clause[] = [
371	[
372	'key' => 'status',
373	'compare' => '=',
374	'value' => $status,
375	],
376	];
377	return self::get_instance()->get_total_count( $where_clause );
378	default:
379	return self::get_instance()->get_total_count();
380	}
381	}
382
383	/**
384	* Get the available months for entries.
385	*
386	* @param array<string,mixed> $where_clause Additional where clause to add to the query.
387	* @since 0.0.13
388	* @return array<int\|string, mixed>
389	*/
390	public static function get_available_months( $where_clause = [] ) {
391	$results = self::get_instance()->get_results(
392	$where_clause,
393	'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
394	[
395	'ORDER BY month_value ASC',
396	],
397	false
398	);
399
400	$months = [];
401	foreach ( $results as $result ) {
402	if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
403	$months[ $result['month_value'] ] = $result['month_label'];
404	}
405	}
406	return $months;
407	}
408	}
409