formidable / classes / models / FrmEntryMeta.php

FrmEntryMeta.php in Formidable Forms – WordPress Form Builder for Contact Forms, Calculators, Quizzes & More 6.34, at classes/models/FrmEntryMeta.php

648 lines 18.2 KB

Raw Download Zip

1	<?php
2	if ( ! defined( 'ABSPATH' ) ) {
3	die( 'You are not allowed to call this page directly.' );
4	}
5
6	class FrmEntryMeta {
7
8	/**
9	* @param int $entry_id
10	* @param int $field_id
11	* @param string $meta_key usually set to '' as this parameter is no longer used.
12	* @param mixed $meta_value
13	*
14	* @return int
15	*/
16	public static function add_entry_meta( $entry_id, $field_id, $meta_key, $meta_value ) {
17	global $wpdb;
18
19	if ( FrmAppHelper::is_empty_value( $meta_value ) ) {
20	// Don't save blank fields
21	return 0;
22	}
23
24	$new_values = array(
25	'meta_value' => is_array( $meta_value ) ? serialize( array_filter( $meta_value, 'FrmAppHelper::is_not_empty_value' ) ) : trim( $meta_value ),
26	'item_id' => $entry_id,
27	'field_id' => $field_id,
28	'created_at' => current_time( 'mysql', 1 ),
29	);
30
31	self::set_value_before_save( $new_values );
32	$new_values = apply_filters( 'frm_add_entry_meta', $new_values );
33	$query_results = $wpdb->insert( $wpdb->prefix . 'frm_item_metas', $new_values );
34
35	if ( $query_results ) {
36	self::clear_cache();
37	wp_cache_delete( $entry_id, 'frm_entry' );
38	return $wpdb->insert_id;
39	}
40
41	return 0;
42	}
43
44	/**
45	* @param int $entry_id
46	* @param int $field_id
47	* @param string $meta_key Deprecated.
48	* @param array\|string $meta_value
49	*
50	* @return bool\|int
51	*/
52	public static function update_entry_meta( $entry_id, $field_id, $meta_key, $meta_value ) {
53	if ( ! $field_id ) {
54	return false;
55	}
56
57	global $wpdb;
58
59	$values = array(
60	'item_id' => $entry_id,
61	'field_id' => $field_id,
62	);
63	$where_values = $values;
64	$values['meta_value'] = $meta_value;
65	self::set_value_before_save( $values );
66	$values = apply_filters( 'frm_update_entry_meta', $values );
67
68	if ( is_array( $values['meta_value'] ) ) {
69	$values['meta_value'] = array_filter( $values['meta_value'], 'FrmAppHelper::is_not_empty_value' );
70	}
71
72	$meta_value = maybe_serialize( $values['meta_value'] );
73
74	wp_cache_delete( $entry_id, 'frm_entry' );
75	self::clear_cache();
76
77	return $wpdb->update( $wpdb->prefix . 'frm_item_metas', array( 'meta_value' => $meta_value ), $where_values );
78	}
79
80	/**
81	* @since 3.0
82	*
83	* @param array $values
84	*
85	* @return void
86	*/
87	private static function set_value_before_save( &$values ) {
88	$field = FrmField::getOne( $values['field_id'] );
89
90	if ( ! $field ) {
91	return;
92	}
93
94	$field_obj = FrmFieldFactory::get_field_object( $field );
95	$values['meta_value'] = $field_obj->set_value_before_save( $values['meta_value'] );
96	}
97
98	/**
99	* @since 3.0
100	*
101	* @param array $atts
102	* @param mixed $value
103	*
104	* @return void
105	*/
106	private static function get_value_to_save( $atts, &$value ) {
107	if ( is_object( $atts['field'] ) ) {
108	$field_obj = FrmFieldFactory::get_field_object( $atts['field'] );
109	$value = $field_obj->get_value_to_save(
110	$value,
111	array(
112	'entry_id' => $atts['entry_id'],
113	'field_id' => $atts['field_id'],
114	)
115	);
116	}
117
118	$value = apply_filters( 'frm_prepare_data_before_db', $value, $atts['field_id'], $atts['entry_id'], array( 'field' => $atts['field'] ) );
119	}
120
121	/**
122	* @param int\|string $entry_id
123	* @param array $values Either indexed by field ID or field key.
124	*
125	* @return void
126	*/
127	public static function update_entry_metas( $entry_id, $values ) {
128	global $wpdb;
129
130	$previous_field_ids = FrmDb::get_col(
131	'frm_item_metas',
132	array(
133	'item_id' => $entry_id,
134	'field_id !' => 0,
135	),
136	'field_id'
137	);
138
139	$values_indexed_by_field_id = array();
140
141	foreach ( $values as $field_id_or_key => $meta_value ) {
142	$field_id = $field_id_or_key;
143	$field = null;
144
145	if ( $field_id_or_key ) {
146	$field = FrmField::getOne( $field_id_or_key );
147
148	if ( is_object( $field ) ) {
149	$field_id = $field->id;
150	}
151	}
152
153	$values_indexed_by_field_id[ $field_id ] = $meta_value;
154
155	self::get_value_to_save( compact( 'field', 'field_id', 'entry_id' ), $meta_value );
156
157	if ( ! $previous_field_ids \|\| ! in_array( $field_id, $previous_field_ids, true ) ) {
158	// If value does not exist, then create it
159	self::add_entry_meta( $entry_id, $field_id, '', $meta_value );
160	continue;
161	}
162
163	if ( $meta_value === array() \|\| ( ! is_array( $meta_value ) && trim( $meta_value ) === '' ) ) {
164	// Remove blank fields.
165	unset( $values_indexed_by_field_id[ $field_id ] );
166	} else {
167	// If value exists, then update it
168	self::update_entry_meta( $entry_id, $field_id, '', $meta_value );
169	}
170	}//end foreach
171
172	if ( ! $previous_field_ids ) {
173	return;
174	}
175
176	$field_ids_to_remove = array_diff( $previous_field_ids, array_keys( $values_indexed_by_field_id ) );
177
178	if ( ! $field_ids_to_remove ) {
179	return;
180	}
181
182	// Prepare the query
183	$where = array(
184	'item_id' => $entry_id,
185	'field_id' => $field_ids_to_remove,
186	);
187	FrmDb::get_where_clause_and_values( $where );
188
189	// Delete any leftovers
190	$wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $wpdb->prefix . 'frm_item_metas ' . $where['where'], $where['values'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, SlevomatCodingStandard.Files.LineLength.LineTooLong
191	self::clear_cache();
192	}
193
194	/**
195	* @param int $old_id
196	* @param int $new_id
197	*
198	* @return void
199	*/
200	public static function duplicate_entry_metas( $old_id, $new_id ) {
201	$metas = self::get_entry_meta_info( $old_id );
202
203	/**
204	* Allows changing entry duplicate values before save.
205	*
206	* @since 5.4.4
207	*
208	* @param array $metas The list of entry meta values.
209	*/
210	$metas = apply_filters( 'frm_before_duplicate_entry_values', $metas );
211
212	foreach ( $metas as $meta ) {
213	self::add_entry_meta( $new_id, $meta->field_id, '', $meta->meta_value );
214	unset( $meta );
215	}
216	self::clear_cache();
217	}
218
219	/**
220	* @param int $entry_id
221	* @param int $field_id
222	*
223	* @return false\|int
224	*/
225	public static function delete_entry_meta( $entry_id, $field_id ) {
226	global $wpdb;
227	self::clear_cache();
228
229	return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}frm_item_metas WHERE field_id=%d AND item_id=%d", $field_id, $entry_id ) );
230	}
231
232	/**
233	* Clear entry meta caching
234	* Called when a meta is added or changed
235	*
236	* @since 2.0.5
237	*
238	* @return void
239	*/
240	public static function clear_cache() {
241	FrmDb::cache_delete_group( 'frm_entry_meta' );
242	FrmDb::cache_delete_group( 'frm_item_meta' );
243	}
244
245	/**
246	* @since 2.0.9
247	*
248	* @param stdClass $entry
249	* @param int\|string $field_id
250	*
251	* @return mixed
252	*/
253	public static function get_meta_value( $entry, $field_id ) {
254	if ( isset( $entry->metas ) ) {
255	return $entry->metas[ $field_id ] ?? false;
256	}
257	return self::get_entry_meta_by_field( $entry->id, $field_id );
258	}
259
260	/**
261	* @param int\|object\|string $entry_id
262	* @param int\|string $field_id This function supports field keys as field id.
263	*
264	* @return mixed
265	*/
266	public static function get_entry_meta_by_field( $entry_id, $field_id ) {
267	global $wpdb;
268
269	if ( is_object( $entry_id ) ) {
270	$entry = $entry_id;
271	$entry_id = $entry->id;
272	$cached = $entry;
273	} else {
274	$entry_id = (int) $entry_id;
275	$cached = FrmDb::check_cache( $entry_id, 'frm_entry' );
276	}
277
278	if ( $cached && isset( $cached->metas ) && isset( $cached->metas[ $field_id ] ) ) {
279	$result = $cached->metas[ $field_id ];
280	return wp_unslash( $result );
281	}
282
283	$get_table = $wpdb->prefix . 'frm_item_metas';
284	$query = array( 'item_id' => $entry_id );
285
286	if ( is_numeric( $field_id ) ) {
287	// Query by field ID.
288	$query['field_id'] = $field_id;
289	} else {
290	// Query by field key.
291	$get_table .= ' it JOIN ' . $wpdb->prefix . 'frm_fields fi ON it.field_id=fi.id';
292	$query['fi.field_key'] = $field_id;
293	}
294
295	$result = FrmDb::get_var( $get_table, $query, 'meta_value' );
296	$field_type = FrmField::get_type( $field_id );
297	FrmFieldsHelper::prepare_field_value( $result, $field_type );
298
299	return wp_unslash( $result );
300	}
301
302	/**
303	* @param int\|string $field_id
304	* @param string $order
305	* @param string $limit
306	* @param array $args
307	*
308	* @return array
309	*/
310	public static function get_entry_metas_for_field( $field_id, $order = '', $limit = '', $args = array() ) {
311	$defaults = array(
312	'value' => false,
313	'unique' => false,
314	'stripslashes' => true,
315	'is_draft' => false,
316	);
317	$args = wp_parse_args( $args, $defaults );
318	$query = array();
319	self::meta_field_query( $field_id, $order, $limit, $args, $query );
320	$query = implode( ' ', $query );
321	$cache_key = 'entry_metas_for_field_' . $field_id . $order . $limit . FrmAppHelper::maybe_json_encode( $args );
322	$values = FrmDb::check_cache( $cache_key, 'frm_entry', $query, 'get_col' );
323
324	if ( ! $args['stripslashes'] ) {
325	return $values;
326	}
327
328	foreach ( $values as $k => $v ) {
329	FrmAppHelper::unserialize_or_decode( $v );
330	$values[ $k ] = $v;
331	unset( $k, $v );
332	}
333
334	return wp_unslash( $values );
335	}
336
337	/**
338	* @param int\|string $field_id
339	* @param string $order
340	* @param string $limit
341	* @param array $args
342	* @param array $query
343	*
344	* @return void
345	*/
346	private static function meta_field_query( $field_id, $order, $limit, $args, array &$query ) {
347	global $wpdb;
348	$query[] = 'SELECT';
349	$query[] = $args['unique'] ? 'DISTINCT(em.meta_value)' : 'em.meta_value';
350	$query[] = 'FROM ' . $wpdb->prefix . 'frm_item_metas em ';
351
352	if ( ! $args['is_draft'] ) {
353	$query[] = 'INNER JOIN ' . $wpdb->prefix . 'frm_items e ON (e.id=em.item_id)';
354	}
355
356	if ( is_numeric( $field_id ) ) {
357	$query[] = $wpdb->prepare( 'WHERE em.field_id=%d', $field_id );
358	} else {
359	$query[] = $wpdb->prepare( 'LEFT JOIN ' . $wpdb->prefix . 'frm_fields fi ON (em.field_id = fi.id) WHERE fi.field_key=%s', $field_id );
360	}
361
362	if ( ! $args['is_draft'] ) {
363	$query[] = 'AND e.is_draft=0';
364	}
365
366	if ( $args['value'] ) {
367	$query[] = $wpdb->prepare( ' AND meta_value=%s', $args['value'] );
368	}
369
370	$query[] = $order . $limit;
371	}
372
373	/**
374	* @param int\|string $entry_id
375	*
376	* @return array
377	*/
378	public static function get_entry_meta_info( $entry_id ) {
379	return FrmDb::get_results( 'frm_item_metas', array( 'item_id' => $entry_id ) );
380	}
381
382	/**
383	* @param array $where
384	* @param string $order_by
385	* @param string $limit
386	* @param bool $stripslashes
387	*
388	* @return mixed
389	*/
390	public static function getAll( $where = array(), $order_by = '', $limit = '', $stripslashes = false ) {
391	global $wpdb;
392	// phpcs:disable Generic.WhiteSpace.ScopeIndent
393	$query = 'SELECT it.*, fi.type as field_type, fi.field_key as field_key,
394	fi.required as required, fi.form_id as field_form_id, fi.name as field_name, fi.options as fi_options
395	FROM ' . $wpdb->prefix . 'frm_item_metas it LEFT OUTER JOIN ' . $wpdb->prefix . 'frm_fields fi ON it.field_id=fi.id' .
396	FrmDb::prepend_and_or_where( ' WHERE ', $where ) . $order_by . $limit;
397	// phpcs:enable Generic.WhiteSpace.ScopeIndent
398
399	$cache_key = 'all_' . FrmAppHelper::maybe_json_encode( $where ) . $order_by . $limit;
400	$results = FrmDb::check_cache( $cache_key, 'frm_entry', $query, $limit === ' LIMIT 1' ? 'get_row' : 'get_results' );
401
402	if ( ! $results \|\| ! $stripslashes ) {
403	return $results;
404	}
405
406	foreach ( $results as $k => $result ) {
407	FrmAppHelper::unserialize_or_decode( $result->meta_value );
408	$results[ $k ]->meta_value = wp_unslash( $result->meta_value );
409	unset( $k, $result );
410	}
411
412	return $results;
413	}
414
415	/**
416	* @param array\|string $where
417	* @param string $order_by
418	* @param string $limit
419	* @param bool $unique
420	* @param array $args
421	*
422	* @return array\|string\|null
423	*/
424	public static function getEntryIds( $where = array(), $order_by = '', $limit = '', $unique = true, $args = array() ) {
425	$defaults = array(
426	'is_draft' => false,
427	'user_id' => '',
428	'group_by' => '',
429	);
430	$args = wp_parse_args( $args, $defaults );
431	$query = array();
432	self::get_ids_query( $where, $order_by, $limit, $unique, $args, $query );
433	$query = implode( ' ', $query );
434	$cache_key = 'ids_' . FrmAppHelper::maybe_json_encode( $where ) . $order_by . 'l' . $limit . 'u' . $unique . FrmAppHelper::maybe_json_encode( $args );
435	$type = 'get_' . ( ' LIMIT 1' === $limit ? 'var' : 'col' );
436	return FrmDb::check_cache( $cache_key, 'frm_entry', $query, $type );
437	}
438
439	/**
440	* Given a query including a form id and its child form ids, output an array of matching entry ids
441	* If a child entry id is matched, its parent will be returned in its place
442	*
443	* @param array $query
444	* @param array $args
445	*
446	* @return array
447	*/
448	public static function get_top_level_entry_ids( $query, $args ) {
449	$args['return_parent_id_if_0_return_id'] = true;
450	return self::getEntryIds( $query, '', '', true, $args );
451	}
452
453	/**
454	* Returns true if the where clause refers to a field table column that is not form_id. It also updates
455	* the where clause to refer to the entry table for form_id if fields table should not be joined.
456	*
457	* @since 6.16.1
458	*
459	* @param array\|string $where
460	*
461	* @return bool
462	*/
463	private static function should_join_fields_table( &$where ) {
464	if ( is_string( $where ) ) {
465	if ( preg_match( '/\bfi\.(?!form_id)\w+/i', $where ) ) {
466	return true;
467	}
468
469	$where = str_ireplace( 'fi.form_id', 'e.form_id', $where );
470	return false;
471	}
472
473	$where_fields = array_keys( $where );
474
475	foreach ( $where_fields as $where_field ) {
476	if ( str_starts_with( $where_field, 'fi.' ) && 'fi.form_id' !== $where_field ) {
477	return true;
478	}
479	}
480
481	if ( isset( $where['fi.form_id'] ) ) {
482	$where['e.form_id'] = $where['fi.form_id'];
483	unset( $where['fi.form_id'] );
484	}
485
486	return false;
487	}
488
489	/**
490	* @param array\|string $where
491	* @param string $order_by
492	* @param string $limit
493	* @param bool $unique
494	* @param array $args
495	* @param array $query
496	*
497	* @return void
498	*/
499	private static function get_ids_query( $where, $order_by, $limit, $unique, $args, array &$query ) { // phpcs:ignore SlevomatCodingStandard.Complexity.Cognitive.ComplexityTooHigh, SlevomatCodingStandard.Files.LineLength.LineTooLong
500	global $wpdb;
501	$query[] = 'SELECT';
502	$defaults = array(
503	'return_parent_id' => false,
504	'return_parent_id_if_0_return_id' => false,
505	);
506	$args = array_merge( $defaults, $args );
507
508	if ( $unique ) {
509	$query[] = 'DISTINCT';
510	}
511
512	if ( $args['return_parent_id_if_0_return_id'] ) {
513	$query[] = 'IF ( e.parent_item_id = 0, it.item_id, e.parent_item_id )';
514	} elseif ( $args['return_parent_id'] ) {
515	$query[] = 'e.parent_item_id';
516	} else {
517	$query[] = 'it.item_id';
518	}
519
520	$from = 'FROM ' . $wpdb->prefix . 'frm_item_metas it';
521
522	if ( self::should_join_fields_table( $where ) \|\| self::should_join_fields_table( $order_by ) ) {
523	$from .= ' LEFT OUTER JOIN ' . $wpdb->prefix . 'frm_fields fi ON it.field_id=fi.id';
524	}
525
526	$query[] = $from;
527	$query[] = 'INNER JOIN ' . $wpdb->prefix . 'frm_items e ON (e.id=it.item_id)';
528
529	if ( is_array( $where ) ) {
530	if ( ! $args['is_draft'] ) {
531	$where['e.is_draft'] = 0;
532	} elseif ( is_numeric( $args['is_draft'] ) ) {
533	$where['e.is_draft'] = class_exists( 'FrmAbandonmentHooksController', false ) ? absint( $args['is_draft'] ) : 1;
534	} elseif ( 'both' === $args['is_draft'] && class_exists( 'FrmAbandonmentHooksController', false ) ) {
535	$where['e.is_draft'] = array( 0, 1 );
536	} elseif ( str_contains( $args['is_draft'], ',' ) ) {
537	$is_draft = array_reduce(
538	explode( ',', $args['is_draft'] ),
539	function ( $total, $current ) {
540	if ( is_numeric( $current ) ) {
541	$total[] = absint( $current );
542	}
543	return $total;
544	},
545	array()
546	);
547
548	if ( $is_draft ) {
549	$where['e.is_draft'] = $is_draft;
550	}
551	}//end if
552
553	if ( ! empty( $args['user_id'] ) ) {
554	$where['e.user_id'] = $args['user_id'];
555	}
556
557	$query[] = FrmDb::prepend_and_or_where( ' WHERE ', $where ) . $order_by . $limit;
558
559	if ( $args['group_by'] ) {
560	$query[] = ' GROUP BY ' . sanitize_text_field( $args['group_by'] );
561	}
562
563	return;
564	}//end if
565
566	$draft_where = '';
567	$user_where = '';
568
569	if ( ! $args['is_draft'] ) {
570	$draft_where = $wpdb->prepare( ' AND e.is_draft=%d', 0 );
571	} elseif ( $args['is_draft'] == 1 ) { // phpcs:ignore Universal.Operators.StrictComparisons
572	$draft_where = $wpdb->prepare( ' AND e.is_draft=%d', 1 );
573	}
574
575	if ( ! empty( $args['user_id'] ) ) {
576	$user_where = $wpdb->prepare( ' AND e.user_id=%d', $args['user_id'] );
577	}
578
579	if ( str_contains( $where, ' GROUP BY ' ) ) {
580	// Don't inject WHERE filtering after GROUP BY
581	$parts = explode( ' GROUP BY ', $where );
582	$where = $parts[0];
583	$where .= $draft_where . $user_where;
584	$where .= ' GROUP BY ' . $parts[1];
585	} else {
586	$where .= $draft_where . $user_where;
587	}
588
589	// The query has already been prepared
590	$query[] = FrmDb::prepend_and_or_where( ' WHERE ', $where ) . $order_by . $limit;
591	}
592
593	/**
594	* @param array\|string $search
595	* @param int\|string $field_id
596	* @param string $operator
597	*
598	* @return array
599	*/
600	public static function search_entry_metas( $search, $field_id, $operator ) {
601	$cache_key = 'search_' . FrmAppHelper::maybe_json_encode( $search ) . $field_id . $operator;
602	$results = wp_cache_get( $cache_key, 'frm_entry' );
603
604	if ( false !== $results ) {
605	return $results;
606	}
607
608	global $wpdb;
609
610	if ( is_array( $search ) ) {
611	$where = '';
612
613	foreach ( $search as $field => $value ) {
614	if ( $value <= 0 \|\| ! in_array( $field, array( 'year', 'month', 'day' ), true ) ) {
615	continue;
616	}
617
618	switch ( $field ) {
619	case 'year':
620	$value = '%' . $value;
621	break;
622	case 'month':
623	$value .= '%';
624	break;
625	case 'day':
626	$value = '%' . $value . '%';
627	}
628
629	$where .= $wpdb->prepare( ' meta_value ' . $operator . ' %s and', $value ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
630	}
631
632	$where .= $wpdb->prepare( ' field_id=%d', $field_id );
633	$query = 'SELECT DISTINCT item_id FROM ' . $wpdb->prefix . 'frm_item_metas' . FrmDb::prepend_and_or_where( ' WHERE ', $where );
634	} else {
635	if ( $operator === 'LIKE' ) {
636	$search = '%' . $search . '%';
637	}
638
639	$query = $wpdb->prepare( "SELECT DISTINCT item_id FROM {$wpdb->prefix}frm_item_metas WHERE meta_value {$operator} %s and field_id = %d", $search, $field_id ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, SlevomatCodingStandard.Files.LineLength.LineTooLong
640	}//end if
641
642	$results = $wpdb->get_col( $query, 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
643	FrmDb::set_cache( $cache_key, $results, 'frm_entry' );
644
645	return $results;
646	}
647	}
648