formidable / classes / models / FrmEntryMeta.php

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

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