ablocks / includes / blocks / form-builder / query.php

query.php in aBlocks – Gutenberg Blocks, User Dashboard Builder, Popup Builder, Form Builder & Animation Builder 2.8.0, at includes/blocks/form-builder/query.php

588 lines 18.8 KB

Raw Download Zip

1	<?php
2	namespace ABlocks\Blocks\FormBuilder;
3
4	if ( ! defined( 'ABSPATH' ) ) {
5	exit;
6	}
7
8	use ABlocks\Blocks\FormBuilder\EmailVerification;
9	use SplTempFileObject;
10	/**
11	* @class Query
12	*/
13	class Query {
14	public static function get_entries(
15	$is_in_trash = 'no', // yes \| no
16	$search_by_email = null, // search entries by email
17	$form_type = null, // form type, eg: contact
18	$date = null, // all entries of a month, eg: 2024-12 # dec, 2024
19	$from_date = null,
20	$to_date = null,
21	$order_by = 'created_at',
22	$order = 'desc',
23	$status = 'all', // all \| read \| unread
24	$entry_per_page = 15,
25	$current_page = 1,
26	$do_export_csv = false
27	) {
28	global $wpdb;
29	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
30
31	$conditions = [];
32	$args = [];
33
34	$conditions[] = 'is_email_verified != %s';
35	$args[] = 'no';
36
37	$conditions[] = 'is_in_trash = %s';
38	$args[] = in_array( $is_in_trash, [ 'yes', 'no' ], true ) ? $is_in_trash : 'no';
39
40	if ( $form_type ) {
41	$conditions[] = 'form_type = %s';
42	$args[] = $form_type;
43	}
44	if ( $search_by_email ) {
45	$conditions[] = 'user_email like %s';
46	$args[] = '%' . $wpdb->esc_like( $search_by_email ) . '%';
47	}
48
49	// format: year-month : 2024-12
50	if ( $date ) {
51	$date = explode( '-', $date );
52	$year = intval( $date[0] );
53	$month = intval( $date[1] );
54	if (
55	count( $date ) === 2 &&
56	$year &&
57	$month
58	) {
59	$conditions[] = 'YEAR(created_at) = %s';
60	$args[] = $year;
61	$conditions[] = 'MONTH(created_at) = %s';
62	$args[] = $month;
63	}
64	}
65	if ( ! isset( $year ) && $from_date ) {
66	$from_date = gmdate( 'y-m-d h:i:s', strtotime( $from_date ) );
67	$conditions[] = 'created_at >= %s';
68	$args[] = $from_date;
69	}
70	if ( ! isset( $year ) && $to_date ) {
71	$to_date = gmdate( 'y-m-d h:i:s', strtotime( $to_date ) );
72	$conditions[] = 'created_at <= %s';
73	$args[] = $to_date;
74	}
75	if ( in_array( strtolower( $status ), [ 'read', 'unread' ], true ) ) {
76	$conditions[] = 'status = %s';
77	$args[] = $status;
78	}
79
80	$query = "SELECT * FROM {$table_entries} ";
81	$count_query = "select count(*) from {$table_entries} ";
82
83	if ( ! empty( $conditions ) ) {
84	$query .= ' where ' . implode( ' and ', $conditions );
85	$count_query .= ' where ' . implode( ' and ', $conditions );
86	}
87
88	if ( empty( $args ) && empty( $conditions ) ) {
89	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
90	$num_of_entries = $wpdb->get_var( $count_query );
91	} else {
92	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
93	$num_of_entries = $wpdb->get_var( $wpdb->prepare( $count_query, ...$args ) );
94	}
95
96	// order by
97	if ( ! in_array( $order_by, [ 'id', 'user_email', 'created_at' ], true ) ) {
98	$order_by = 'created_at';
99	}
100	// order
101	if (
102	! in_array( strtoupper( $order ), [ 'ASC', 'DESC' ], true )
103	) {
104	$order = 'ASC';
105	}
106	$query .= " order by {$order_by} {$order}";
107
108	// export entries in csv format
109	if ( $do_export_csv ) {
110	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
111	$entries_to_export = $wpdb->get_results( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? [];
112
113	// make a temporary file object
114	$save_entries_as_csv = new SplTempFileObject();
115
116	// rewind pointer at first line
117	$save_entries_as_csv->rewind();
118
119	// add header to csv
120	$save_entries_as_csv->fputcsv([
121	'email',
122	'form',
123	'page id',
124	'submission date',
125	'ip',
126	'user agent'
127	]);
128
129	// add row to csv
130	foreach ( $entries_to_export as $entry ) {
131	$save_entries_as_csv->fputcsv([
132	sanitize_text_field( $entry['user_email'] ),
133	sanitize_text_field( strtoupper( $entry['form_type'] ) ),
134	sanitize_text_field( '#' . $entry['post_id'] ),
135	sanitize_text_field( gmdate( 'd m, y h:i:s', strtotime( $entry['created_at'] ) ) ),
136	sanitize_text_field( $entry['ip'] ),
137	sanitize_text_field( $entry['user_agent'] ),
138	]);
139	}
140	// rewind pointer at top
141	$save_entries_as_csv->rewind();
142
143	// get csv data as string
144	$csv_data = '';
145	foreach ( $save_entries_as_csv as $line ) {
146	$csv_data .= $line;
147	}
148	return [ 'csv_data' => $csv_data ];
149	}//end if
150	// END
151
152	// pagination
153	$current_page = absint( $current_page );
154	$entry_per_page = absint( $entry_per_page );
155	if ( $current_page < 1 \|\| $current_page > $num_of_entries ) {
156	$current_page = 1;
157	}
158	if ( $entry_per_page < 1 ) {
159	$entry_per_page = 15;
160	}
161	$offset = ( $current_page - 1 ) * $entry_per_page;
162
163	$query .= ' limit %d offset %d';
164	$args[] = $entry_per_page;
165	$args[] = $offset;
166
167	$get_available_date_query = "select
168	count(created_at) as total_entries,
169	year(created_at) as year,
170	month(created_at) as month
171	from {$table_entries}
172	group by year, month
173	order by year, month
174	";
175	$get_available_forms_query = "select
176	form_type
177	from {$table_entries}
178	group by form_type
179	order by form_type asc
180	";
181	$data = [];
182	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
183	foreach ( $wpdb->get_results( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? [] as $item ) {
184	if ( $item['is_in_trash'] === 'yes' ) {
185	$item['status'] = 'trash';
186	}
187	$data[] = $item;
188	}
189
190	return [
191	'total_entries' => $num_of_entries,
192	'current_page' => $current_page,
193	'per_page' => $entry_per_page,
194	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
195	'available_dates' => $wpdb->get_results( $get_available_date_query, ARRAY_A ) ?? [],
196	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
197	'available_forms' => $wpdb->get_results( $get_available_forms_query, ARRAY_A ) ?? [],
198	'data' => $data,
199	];
200	}
201
202	public static function get_block_attributes( $post_id, $block_id ) {
203	$post_content = get_post_field( 'post_content', $post_id );
204	$blocks = parse_blocks( $post_content );
205	$block_data = self::get_block_attributes_recursive( $block_id, $blocks );
206
207	$fields = [];
208	if ( ( $block_data['innerBlocks'][0]['blockName'] ?? '' ) === 'ablocks/form-multi-step' ) {
209
210	foreach ( $block_data['innerBlocks'][0]['innerBlocks'] ?? [] as $block ) {
211	foreach ( $block['innerBlocks'] as [ 'blockName' => $block_name, 'attributes' => $attr ] ) {
212	$name = $attr['name'] ?? false;
213	$input_type = $attr['inputType'] ?? false;
214	$label = $attr['label'] ?? $attr['placeholder'] ?? false;
215	$radio_arr = $attr['radioArr'] ?? false;
216	// check current input name exists in $all_fields
217	if ( $name ) {
218
219	$fields[ $name ] = [
220	'inputType' => sanitize_text_field( $input_type ), // sanitize value
221	'label' => strtolower( sanitize_text_field( $label ) ), // sanitize value
222	];
223
224	if ( is_array( $radio_arr ) ) {
225	$fields[ $name ]['radioArr'] = $radio_arr;
226	}
227	} elseif ( $block_name === 'ablocks/form-hidden' ) {
228	$name = strtolower( sanitize_text_field( $attr['hiddenname'] ?? '' ) );
229	$fields[ $name ] = [
230	'inputType' => 'textarea', // sanitize value
231	'label' => ucfirst( preg_replace( '/[_-]/m', ' ', $name ) ), // sanitize value
232	];
233	}
234	}//end foreach
235	}//end foreach
236	} elseif (
237	isset( $block_data['innerBlocks'] )
238	) {
239	$fields = [];
240	foreach ( $block_data['innerBlocks'] as $input_element_data ) {
241	$name = $input_element_data['attributes']['name'] ?? false;
242	$input_type = $input_element_data['attributes']['inputType'] ?? false;
243	$label = $input_element_data['attributes']['label'] ?? $input_element_data['attributes']['placeholder'] ?? false;
244	$radio_arr = $input_element_data['attributes']['radioArr'] ?? false;
245	// check current input name exists in $all_fields
246	if ( $name ) {
247
248	$fields[ $name ] = [
249	'inputType' => strtolower( sanitize_text_field( $input_type ) ), // sanitize value
250	'label' => sanitize_text_field( $label ), // sanitize value
251	];
252
253	if ( is_array( $radio_arr ) ) {
254	$fields[ $name ]['radioArr'] = $radio_arr;
255	}
256	} elseif ( $input_element_data['blockName'] === 'ablocks/form-hidden' ) {
257	$name = strtolower( sanitize_text_field( $input_element_data['attributes']['hiddenname'] ?? '' ) );
258	$fields[ $name ] = [
259	'inputType' => 'textarea', // sanitize value
260	'label' => ucfirst( preg_replace( '/[_-]/m', ' ', $name ) ), // sanitize value
261	];
262	}
263	}//end foreach
264	}//end if
265	return $fields;
266	}
267
268	public static function get_block_attributes_recursive( $block_id, $blocks ) {
269
270	foreach ( $blocks as $block ) {
271
272	if (
273	( $block['attrs']['block_id'] ?? '' ) === $block_id
274	) {
275	return [
276	'parentAttributes' => $block['attrs'] ?? [],
277	'innerBlocks' => \ABlocks\Helper::extract_inner_blocks( $block['innerBlocks'] ?? [] ),
278	];
279	}
280
281	if (
282	array_key_exists( 'innerBlocks', $block ) &&
283	is_array( $block['innerBlocks'] ) &&
284	count( $block['innerBlocks'] ) > 0
285	) {
286	$data = self::get_block_attributes_recursive(
287	$block_id,
288	$block['innerBlocks']
289	);
290	if (
291	$data
292	) {
293	return $data;
294	}
295	}
296	}//end foreach
297	return [];
298	}
299
300	public static function get_entry( int $id ) {
301	global $wpdb;
302	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
303	$table_meta = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_meta';
304
305	$query = "SELECT * FROM {$table_entries} WHERE id = %d";
306	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
307	$entry = $wpdb->get_row( $wpdb->prepare( $query, $id ), ARRAY_A );
308	if ( $entry ) {
309	// mark this entry as reas
310	if ( count( self::update_status_of_entries( [ $id ] ) ) > 0 ) {
311	$entry['status'] = 'read';
312	}
313	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
314	$meta = $wpdb->get_results(
315	$wpdb->prepare(
316	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
317	"SELECT meta_key, meta_value FROM $table_meta WHERE entry_id = %d ",
318	$id
319	),
320	ARRAY_A
321	);
322
323	$fields = self::get_block_attributes( $entry['post_id'], $entry['block_id'] );
324	$found = [];
325	foreach ( $meta as $field ) {
326	if ( array_key_exists( $field['meta_key'], $fields ) ) {
327	$found[] = $field['meta_key'];
328
329	if (
330	isset( $fields[ $field['meta_key'] ]['radioArr'] )
331	) {
332	$decoded_value = json_decode( $field['meta_value'], true );
333	if ( json_last_error() === JSON_ERROR_NONE && is_array( $decoded_value ) ) {
334	$field['meta_value'] = array_values( $decoded_value );
335	} else {
336	$field['meta_value'] = [];
337	}
338	} elseif (
339	! isset( $fields[ $field['meta_key'] ]['radioArr'] ) &&
340	( $fields[ $field['meta_key'] ]['inputType'] ?? '' ) === 'checkbox'
341	) {
342	$field['meta_value'] = strtolower( $field['meta_value'] ) === 'on' ? true : false;
343	}
344
345	$entry['meta'][] = apply_filters( 'ablocks/form_builder/meta_output', array_merge(
346	$fields[ $field['meta_key'] ],
347	$field
348	) );
349
350	} else {
351	$entry['meta'][] = apply_filters( 'ablocks/form_builder/meta_output', array_merge(
352	$field,
353	[
354	'inputType' => 'text',
355	'label' => ucfirst( preg_replace( '/[_-]/m', ' ', $field['meta_key'] ) ),
356	]
357	) );
358	}//end if
359	}//end foreach
360
361	foreach ( array_diff( array_keys( $fields ), $found ) as $key ) {
362
363	$fields[ $key ]['meta_key'] = $key;
364
365	if (
366	isset( $fields[ $key ]['radioArr'] )
367	) {
368	$fields[ $key ]['meta_value'] = [];
369	} elseif (
370	! isset( $fields[ $key ]['radioArr'] ) &&
371	( $fields[ $key ]['inputType'] ?? '' ) === 'checkbox'
372	) {
373	$fields[ $key ]['meta_value'] = false;
374	} else {
375	$fields[ $key ]['meta_value'] = null;
376	}
377
378	$entry['meta'][] = apply_filters( 'ablocks/form_builder/meta_output', $fields[ $key ] );
379	}//end foreach
380
381	return $entry;
382	}//end if
383	return [];
384	}
385
386	public static function edit_entry( int $id, array $data ) {
387	global $wpdb;
388	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
389	$table_meta = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_meta';
390
391	// Check if the entry exists
392	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
393	$is_entry_exists = (int) $wpdb->get_var(
394	$wpdb->prepare( "SELECT COUNT(*) FROM {$table_entries} WHERE id = %d", $id )// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
395	) > 0;
396
397	if ( ! $is_entry_exists ) {
398	return false;
399	}
400
401	// Get all meta keys for this entry
402	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
403	$get_meta_keys = $wpdb->get_results(
404	$wpdb->prepare(
405	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
406	"SELECT meta_key FROM {$table_meta} WHERE entry_id = %d ",
407	$id
408	),
409	ARRAY_A
410	);
411
412	if ( empty( $get_meta_keys ) ) {
413	return false;
414	}
415
416	$total_updated = 0;
417
418	foreach ( $get_meta_keys as $key ) {
419	$meta_key = $key['meta_key'];
420
421	// Check if the input data has this meta key
422	if ( array_key_exists( $meta_key, $data ) ) {
423	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
424	$is_updated = $wpdb->update(
425	$table_meta,
426	[ 'meta_value' => sanitize_text_field( $data[ $meta_key ] ) ],
427	[
428	'entry_id' => $id,
429	'meta_key' => $meta_key,
430	],
431	[ '%s' ], // Data format
432	[ '%d', '%s' ] // Where format
433	);
434
435	if ( false !== $is_updated && $is_updated > 0 ) {
436	$total_updated++;
437	}
438	}
439	}//end foreach
440
441	return $total_updated > 0;
442	}
443
444	public static function delete_entries( array $entry_id_array ): array {
445	global $wpdb;
446	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
447	$table_meta = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_meta';
448
449	$total_deleted = [];
450	if ( ! empty( $entry_id_array ) ) {
451	foreach ( $entry_id_array as $entry_id ) {
452
453	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
454	if ( $wpdb->delete( $table_entries, [ 'id' => $entry_id ], [ '%d' ] ) ) {
455
456	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
457	$wpdb->delete( $table_meta, [ 'entry_id' => $entry_id ], [ '%d' ] );
458	$total_deleted[] = $entry_id;
459	}
460	}
461	}
462	return $total_deleted;
463	}
464	public static function update_status_of_entries( array $entry_id_array, string $status = 'read' ): array {
465	global $wpdb;
466	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
467
468	$total = [];
469	if ( ! empty( $entry_id_array ) ) {
470	foreach ( $entry_id_array as $entry_id ) {
471
472	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
473	if ( $wpdb->update( $table_entries, [ 'status' => $status ], [ 'id' => $entry_id ] ) ) {
474	self::update_trash_status_of_entries( $entry_id, 'no' );
475	$total[] = $entry_id;
476	}
477	}
478	}
479	return $total;
480	}
481
482	public static function update_trash_status_of_entries( int $id, string $send_to_trash = 'yes' ): array {
483	global $wpdb;
484	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
485
486	// Retrieve the current status
487	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
488	$status = $wpdb->get_var(
489	$wpdb->prepare( "SELECT status FROM {$table_entries} WHERE id = %d", $id )// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
490	);
491
492	// Initialize response array
493	$total = [];
494
495	if ( $id > 0 ) {
496	// Update the trash status
497	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
498	$is_updated = $wpdb->update(
499	$table_entries,
500	[ 'is_in_trash' => sanitize_text_field( $send_to_trash ) ],
501	[ 'id' => $id ],
502	[ '%s' ],
503	[ '%d' ]
504	);
505
506	if ( false !== $is_updated && $is_updated > 0 ) {
507	$total = [
508	'id' => $id,
509	'status' => ( 'yes' === $send_to_trash ) ? 'trash' : $status,
510	];
511	}
512	}
513
514	return $total;
515	}
516
517	public static function add_new_entry(
518	string $form_type,
519	string $post_id,
520	string $user_email,
521	string $ip,
522	string $user_agent,
523	array $meta_data,
524	string $block_id,
525	bool $is_verification_needed = false
526	) {
527	global $wpdb;
528	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
529	$table_meta = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_meta';
530	$token = wp_generate_password( 22, false );
531	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
532	$wpdb->insert($table_entries,
533	[
534	'form_type' => $form_type,
535	'post_id' => $post_id,
536	'user_email' => $user_email,
537	'ip' => $ip,
538	'user_agent' => $user_agent,
539	'block_id' => $block_id,
540	'email_verification_token' => $token,
541	'expire' => time() + EmailVerification::EXPIRE_IN,
542	'is_email_verified' => $is_verification_needed ? 'no' : '-'
543	]
544	);
545	$entry_id = $wpdb->insert_id;
546
547	if ( $entry_id ) {
548	foreach ( $meta_data as $input_id => $attr ) {
549	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
550	$wpdb->insert($table_meta,
551	[
552	'entry_id' => $entry_id,
553	'meta_key' => $input_id,
554	'meta_value' => is_array( $attr['value'] ) ? json_encode( $attr['value'] ) : $attr['value'],
555	]
556	);
557	}
558	}
559	return $entry_id;
560	}
561
562	public static function get_token_by_email( string $email ) {
563	global $wpdb;
564	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
565	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
566	$entry = $wpdb->get_row(
567	$wpdb->prepare(
568	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
569	"SELECT email_verification_token FROM {$table_entries} WHERE user_email = %s",
570	sanitize_email( $email )
571	),
572	ARRAY_A
573	);
574
575	return ! empty( $entry['email_verification_token'] ) ? $entry['email_verification_token'] : false;
576	}
577
578	public static function is_email_exists( string $email, string $form_type ) {
579	global $wpdb;
580	$table_entries = $wpdb->prefix . ABLOCKS_PLUGIN_SLUG . '_form_entries';
581
582	$query = "select count(user_email) from {$table_entries} where form_type = %s and user_email = %s";
583	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
584	return $wpdb->get_var( $wpdb->prepare( $query, $form_type, $email ) ) > 0;
585	}
586
587	}
588