give / includes / donors / class-give-donors-query.php

class-give-donors-query.php in GiveWP – Donation Plugin and Fundraising Platform 4.18.0, at includes/donors/class-give-donors-query.php

694 lines 15.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Donors Query
4	*
5	* @package Give
6	* @subpackage Classes/Stats
7	* @copyright Copyright (c) 2017, GiveWP
8	* @license https://opensource.org/licenses/gpl-license GNU Public License
9	* @since 1.8.14
10	*/
11
12	// Exit if accessed directly.
13	if ( ! defined( 'ABSPATH' ) ) {
14	exit;
15	}
16
17	/**
18	* Give_Donors_Query Class
19	*
20	* This class is for retrieving donors data.
21	*
22	* Donors can be retrieved for date ranges and pre-defined periods.
23	*
24	* @since 1.8.14
25	*/
26	class Give_Donors_Query {
27
28	/**
29	* The args to pass to the give_get_donors() query
30	*
31	* @since 1.8.14
32	* @access public
33	*
34	* @var array
35	*/
36	public $args = [];
37
38	/**
39	* The donors found based on the criteria set
40	*
41	* @since 1.8.14
42	* @access public
43	*
44	* @var array
45	*/
46	public $donors = [];
47
48	/**
49	* The donors found based on the criteria set
50	*
51	* @since 1.8.14
52	* @access public
53	*
54	* @var string
55	*/
56	public $table_name = '';
57
58	/**
59	* The donors found based on the criteria set
60	*
61	* @since 1.8.14
62	* @access public
63	*
64	* @var string
65	*/
66	public $meta_table_name = '';
67
68	/**
69	* The donors found based on the criteria set
70	*
71	* @since 1.8.14
72	* @access public
73	*
74	* @var string
75	*/
76	public $meta_type = '';
77
78	/**
79	* Preserve args
80	*
81	* @since 2.4.0
82	* @access public
83	*
84	* @var array
85	*/
86	public $_args = [];
87
88	/**
89	* Default query arguments.
90	*
91	* Not all of these are valid arguments that can be passed to WP_Query. The ones that are not, are modified before
92	* the query is run to convert them to the proper syntax.
93	*
94	* @since 1.8.14
95	* @access public
96	*
97	* @param $args array The array of arguments that can be passed in and used for setting up this payment query.
98	*/
99	public function __construct( $args = [] ) {
100	$defaults = [
101	'number' => 20,
102	'offset' => 0,
103	'paged' => 1,
104	'orderby' => 'id',
105	'order' => 'DESC',
106	'user' => null,
107	'email' => null,
108	'donor' => null,
109	'meta_query' => [],
110	'date_query' => [],
111	's' => null,
112	'fields' => 'all', // Supports donors (all fields) or valid column as string or array list.
113	'count' => false,
114	'give_forms' => [],
115	'start_date' => false,
116	'end_date' => false,
117
118	/**
119	* donation_amount will contain value like:
120	* array(
121	* 'compare' => compare symbol (by default set to > )
122	* 'amount' => numeric_value
123	* )
124	*
125	* You can also pass number value to this param then compare symbol will auto set to >
126	*/
127	'donation_amount' => [],
128	];
129
130	$this->args = $this->_args = wp_parse_args( $args, $defaults );
131	$this->table_name = Give()->donors->table_name;
132	$this->meta_table_name = Give()->donor_meta->table_name;
133	$this->meta_type = Give()->donor_meta->meta_type;
134
135	$this->date_filter_pre();
136	}
137
138	/**
139	* Modify the query/query arguments before we retrieve donors.
140	*
141	* @since 1.8.14
142	* @access public
143	*
144	* @return void
145	*/
146	public function init() {
147	}
148
149
150	/**
151	* Retrieve donors.
152	*
153	* The query can be modified in two ways; either the action before the
154	* query is run, or the filter on the arguments (existing mainly for backwards
155	* compatibility).
156	*
157	* @since 1.8.14
158	* @access public
159	*
160	* @global wpdb $wpdb
161	*
162	* @return array\|object\|string\|null
163	*/
164	public function get_donors() {
165	global $wpdb;
166
167	/**
168	* Fires before retrieving donors.
169	*
170	* @since 1.8.14
171	*
172	* @param Give_Donors_Query $this Donors query object.
173	*/
174	do_action( 'give_pre_get_donors', $this );
175
176	$cache_key = Give_Cache::get_key( 'give_donor', $this->get_sql(), false );
177
178	// Get donors from cache.
179	$this->donors = Give_Cache::get_db_query( $cache_key );
180
181	if ( null === $this->donors ) {
182	if ( empty( $this->args['count'] ) ) {
183	$this->donors = $wpdb->get_results( $this->get_sql() );
184	self::update_meta_cache( wp_list_pluck( (array) $this->donors, 'id' ) );
185	} else {
186	$this->donors = $wpdb->get_var( $this->get_sql() );
187	}
188
189	Give_Cache::set_db_query( $cache_key, $this->donors );
190	}
191
192	/**
193	* Fires after retrieving donors.
194	*
195	* @since 1.8.14
196	*
197	* @param Give_Donors_Query $this Donors query object.
198	*/
199	do_action( 'give_post_get_donors', $this );
200
201	return $this->donors;
202	}
203
204	/**
205	* Get sql query from queried array.
206	*
207	* @since 2.0
208	* @access public
209	*
210	* @global wpdb $wpdb
211	* @return string
212	*/
213	public function get_sql() {
214	global $wpdb;
215
216	if ( $this->args['number'] < 1 ) {
217	$this->args['number'] = 99999999999;
218	}
219
220	$where = $this->get_where_query();
221
222	// Set offset.
223	if ( empty( $this->args['offset'] ) && ( 0 < $this->args['paged'] ) ) {
224	$this->args['offset'] = $this->args['number'] * ( $this->args['paged'] - 1 );
225	}
226
227	// Set fields.
228	$fields = "{$this->table_name}.*";
229	if ( ! empty( $this->args['fields'] ) && 'all' !== $this->args['fields'] ) {
230	if ( is_string( $this->args['fields'] ) ) {
231	$fields = "{$this->table_name}.{$this->args['fields']}";
232	} elseif ( is_array( $this->args['fields'] ) ) {
233	$fields = "{$this->table_name}." . implode( " , {$this->table_name}.", $this->args['fields'] );
234	}
235	}
236
237	// Set count.
238	if ( ! empty( $this->args['count'] ) ) {
239	$fields = "COUNT({$this->table_name}.id)";
240	}
241
242	$orderby = $this->get_order_query();
243
244	$sql = $wpdb->prepare( "SELECT {$fields} FROM {$this->table_name} LIMIT %d,%d;", absint( $this->args['offset'] ), absint( $this->args['number'] ) );
245
246	// $where, $orderby and order already prepared query they can generate notice if you re prepare them in above.
247	// WordPress consider LIKE condition as placeholder if start with s,f, or d.
248	$sql = str_replace( 'LIMIT', "{$where} {$orderby} LIMIT", $sql );
249
250	return $sql;
251	}
252
253	/**
254	* Set query where clause.
255	*
256	* @since 1.8.14
257	* @access private
258	*
259	* @global wpdb $wpdb
260	* @return string
261	*/
262	private function get_where_query() {
263
264	// Get sql query for meta.
265	if ( ! empty( $this->args['meta_query'] ) ) {
266	$meta_query_object = new WP_Meta_Query( $this->args['meta_query'] );
267	$meta_query = $meta_query_object->get_sql( $this->meta_type, $this->table_name, 'id' );
268
269	$where[] = implode( '', $meta_query );
270	}
271
272	$where[] = 'WHERE 1=1';
273	$where[] = $this->get_where_search();
274	$where[] = $this->get_where_email();
275	$where[] = $this->get_where_donor();
276	$where[] = $this->get_where_user();
277	$where[] = $this->get_where_date();
278	$where[] = $this->get_where_donation_amount();
279	$where[] = $this->get_where_donation_count();
280	$where[] = $this->get_where_give_forms();
281
282	$where = array_filter( $where );
283
284	return trim( implode( ' ', array_map( 'trim', $where ) ) );
285
286	}
287
288	/**
289	* Set email where clause.
290	*
291	* @since 1.8.14
292	* @access private
293	*
294	* @global wpdb $wpdb
295	* @return string
296	*/
297	private function get_where_email() {
298	global $wpdb;
299
300	$where = '';
301
302	if ( ! empty( $this->args['email'] ) ) {
303
304	if ( is_array( $this->args['email'] ) ) {
305
306	$emails_count = count( $this->args['email'] );
307	$emails_placeholder = array_fill( 0, $emails_count, '%s' );
308	$emails = implode( ', ', $emails_placeholder );
309
310	$where .= $wpdb->prepare( "AND {$this->table_name}.email IN( $emails )", $this->args['email'] );
311	} else {
312	$where .= $wpdb->prepare( "AND {$this->table_name}.email = %s", $this->args['email'] );
313	}
314	}
315
316	return $where;
317	}
318
319	/**
320	* Set donor where clause.
321	*
322	* @since 1.8.14
323	* @access private
324	*
325	* @global wpdb $wpdb
326	* @return string
327	*/
328	private function get_where_donor() {
329	$where = '';
330
331	// Specific donors.
332	if ( ! empty( $this->args['donor'] ) ) {
333	if ( ! is_array( $this->args['donor'] ) ) {
334	$this->args['donor'] = explode( ',', $this->args['donor'] );
335	}
336	$donor_ids = implode( ',', array_map( 'intval', $this->args['donor'] ) );
337
338	$where .= "AND {$this->table_name}.id IN( {$donor_ids} )";
339	}
340
341	return $where;
342	}
343
344	/**
345	* Set date where clause.
346	*
347	* @since 1.8.14
348	* @access private
349	*
350	* @global wpdb $wpdb
351	* @return string
352	*/
353	private function get_where_date() {
354	$where = '';
355
356	// Donors created for a specific date or in a date range
357	if ( ! empty( $this->args['date_query'] ) ) {
358	$date_query_object = new WP_Date_Query( is_array( $this->args['date_query'] ) ? $this->args['date_query'] : wp_parse_args( $this->args['date_query'] ), "{$this->table_name}.date_created" );
359
360	$where .= str_replace(
361	[
362	"\n",
363	'( (',
364	'))',
365	],
366	[
367	'',
368	'( (',
369	') )',
370	],
371	$date_query_object->get_sql()
372	);
373	}
374
375	return $where;
376	}
377
378	/**
379	* Set search where clause.
380	*
381	* @since 1.8.14
382	* @access private
383	*
384	* @global wpdb $wpdb
385	* @return string
386	*/
387	private function get_where_search() {
388	$where = '';
389
390	// Bailout.
391	if ( empty( $this->args['s'] ) ) {
392	return $where;
393	}
394
395	// Donors created for a specific date or in a date range
396	if ( false !== strpos( $this->args['s'], ':' ) ) {
397	$search_parts = explode( ':', $this->args['s'] );
398	if ( ! empty( $search_parts[0] ) ) {
399	switch ( $search_parts[0] ) {
400	// Backward compatibility.
401	case 'name':
402	$where = "AND {$this->table_name}.name LIKE '%{$search_parts[1]}%'";
403	break;
404	case 'note':
405	$where = "AND {$this->table_name}.notes LIKE '%{$search_parts[1]}%'";
406	break;
407	}
408	}
409	} elseif ( is_numeric( $this->args['s'] ) ) {
410	$where = "AND {$this->table_name}.id ='{$this->args['s']}'";
411
412	} else {
413	$search_field = is_email( $this->args['s'] ) ? 'email' : 'name';
414	$where = "AND {$this->table_name}.$search_field LIKE '%{$this->args['s']}%'";
415	}
416
417	return $where;
418	}
419
420	/**
421	* Set user where clause.
422	*
423	* @since 1.8.14
424	* @access private
425	*
426	* @global wpdb $wpdb
427	* @return string
428	*/
429	private function get_where_user() {
430	$where = '';
431
432	// Donors create for specific wp user.
433	if ( ! empty( $this->args['user'] ) ) {
434	if ( ! is_array( $this->args['user'] ) ) {
435	$this->args['user'] = explode( ',', $this->args['user'] );
436	}
437	$user_ids = implode( ',', array_map( 'intval', $this->args['user'] ) );
438
439	$where .= "AND {$this->table_name}.user_id IN( {$user_ids} )";
440	}
441
442	return $where;
443	}
444
445	/**
446	* Set orderby query
447	*
448	* @since 1.8.14
449	* @access private
450	*
451	* @return string
452	*/
453	private function get_order_query() {
454	$table_columns = Give()->donors->get_columns();
455
456	$query = [];
457	$ordersby = $this->args['orderby'];
458
459	if ( ! is_array( $ordersby ) ) {
460	$ordersby = [
461	$this->args['orderby'] => $this->args['order'],
462	];
463	}
464
465	// Remove non existing column.
466	// Filter orderby values.
467	foreach ( $ordersby as $orderby => $order ) {
468	if ( ! array_key_exists( $orderby, $table_columns ) ) {
469	unset( $ordersby[ $orderby ] );
470	continue;
471	}
472
473	$ordersby[ esc_sql( $orderby ) ] = esc_sql( $order );
474	}
475
476	if ( empty( $ordersby ) ) {
477	$ordersby = [
478	'id' => $this->args['order'],
479	];
480	}
481
482	// Create query.
483	foreach ( $ordersby as $orderby => $order ) {
484	/**
485	* @since 3.16.2 Prevent SQL Injection by not using the user defined order value directly in the query.
486	*/
487	$sanitizedOrder = $order === 'ASC' ? 'ASC' : 'DESC';
488
489	switch ( $table_columns[ $orderby ] ) {
490	case '%d':
491	case '%f':
492	$query[] = "{$this->table_name}.{$orderby}+0 {$sanitizedOrder}";
493	break;
494
495	default:
496	$query[] = "{$this->table_name}.{$orderby} {$sanitizedOrder}";
497	}
498	}
499
500	return ! empty( $query ) ? 'ORDER BY ' . implode( ', ', $query ) : '';
501	}
502
503	/**
504	* Set donation count value where clause.
505	*
506	* @todo: add phpunit test
507	*
508	* @since 2.2.0
509	* @access private
510	*
511	* @global wpdb $wpdb
512	* @return string
513	*/
514	private function get_where_donation_count() {
515	$where = '';
516
517	if ( ! empty( $this->args['donation_count'] ) ) {
518	$compare = '>';
519	$amount = $this->args['donation_count'];
520	if ( is_array( $this->args['donation_count'] ) ) {
521	$compare = $this->args['donation_count'] ['compare'];
522	$amount = $this->args['donation_count']['amount'];
523	}
524
525	$where .= "AND {$this->table_name}.purchase_count{$compare}{$amount}";
526	}
527
528	return $where;
529	}
530
531	/**
532	* Set purchase value where clause.
533	*
534	* @todo: add phpunit test
535	*
536	* @since 2.1.0
537	* @access private
538	*
539	* @global wpdb $wpdb
540	* @return string
541	*/
542	private function get_where_donation_amount() {
543	$where = '';
544
545	if ( ! empty( $this->args['donation_amount'] ) ) {
546	$compare = '>';
547	$amount = $this->args['donation_amount'];
548	if ( is_array( $this->args['donation_amount'] ) ) {
549	$compare = $this->args['donation_amount'] ['compare'];
550	$amount = $this->args['donation_amount']['amount'];
551	}
552
553	$where .= "AND {$this->table_name}.purchase_value{$compare}{$amount}";
554	}
555
556	return $where;
557	}
558
559	/**
560	* Set give_forms where clause.
561	*
562	* @todo : add phpunit test
563	*
564	* @since 4.14.0 Replace {$wpdb->paymentmeta} with {$wpdb->donationmeta}
565	* @since 2.1.0
566	*
567	* @access private
568	*
569	* @global wpdb $wpdb
570	* @return string
571	*/
572	private function get_where_give_forms() {
573	global $wpdb;
574	$where = '';
575
576	if ( ! empty( $this->args['give_forms'] ) ) {
577	if ( ! is_array( $this->args['give_forms'] ) ) {
578	$this->args['give_forms'] = explode( ',', $this->args['give_forms'] );
579	}
580
581	$form_ids = implode( ',', array_map( 'intval', $this->args['give_forms'] ) );
582	$donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
583
584	$query = $wpdb->prepare(
585	"
586	SELECT DISTINCT meta_value as donor_id
587	FROM {$wpdb->donationmeta}
588	WHERE meta_key=%s
589	AND {$donation_id_col} IN(
590	SELECT {$donation_id_col}
591	FROM {$wpdb->donationmeta}
592	WHERE meta_key=%s
593	AND meta_value IN (%s)
594	)
595	",
596	'_give_payment_donor_id',
597	'_give_payment_form_id',
598	$form_ids
599	);
600
601	$donor_ids = $wpdb->get_results( $query, ARRAY_A );
602
603	if ( ! empty( $donor_ids ) ) {
604	$donor_ids = wp_list_pluck( $donor_ids, 'donor_id' );
605	$donor_ids = implode( ',', array_map( 'intval', $donor_ids ) );
606	$where .= "AND {$this->table_name}.id IN ({$donor_ids})";
607	} else {
608	$where .= "AND {$this->table_name}.id IN ('0')";
609	}
610	}
611
612	return $where;
613	}
614
615	/**
616	* If querying a specific date, add the proper filters.
617	* Note: This function currently only accept dates with admin defined core date format
618	*
619	* @since 2.4.0
620	* @access public
621	*
622	* @return void
623	*/
624	public function date_filter_pre() {
625	if (
626	! empty( $this->args['date_query'] )
627	\|\| empty( $this->args['start_date'] )
628	\|\| empty( $this->args['end_date'] )
629	) {
630	return;
631	}
632
633	$date_query = [];
634
635	if ( ! empty( $this->args['start_date'] ) ) {
636	$date_query['after'] = date(
637	'Y-m-d H:i:s',
638	is_numeric( $this->args['start_date'] )
639	? $this->args['start_date']
640	: strtotime( $this->args['start_date'] )
641	);
642	}
643
644	if ( ! empty( $this->args['end_date'] ) ) {
645	$date_query['before'] = date(
646	'Y-m-d H:i:s',
647	is_numeric( $this->args['end_date'] )
648	? $this->args['end_date']
649	: strtotime( $this->args['end_date'] )
650	);
651	}
652
653	// Include Start Date and End Date while querying.
654	$date_query['inclusive'] = true;
655
656	$this->__set( 'date_query', $date_query );
657	}
658
659	/**
660	* Update donors meta cache
661	*
662	* @since 2.5.0
663	* @access private
664	*
665	* @param array $donor_ids
666	*/
667	public static function update_meta_cache( $donor_ids ) {
668	// Exit.
669	if ( empty( $donor_ids ) ) {
670	return;
671	}
672
673	update_meta_cache( Give()->donor_meta->get_meta_type(), $donor_ids );
674	}
675
676	/**
677	* Set a query variable.
678	*
679	* @since 2.4.0
680	* @access public
681	*
682	* @param $query_var
683	* @param $value
684	*/
685	public function __set( $query_var, $value ) {
686	if ( in_array( $query_var, [ 'meta_query', 'tax_query' ] ) ) {
687	$this->args[ $query_var ][] = $value;
688	} else {
689	$this->args[ $query_var ] = $value;
690	}
691	}
692
693	}
694