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

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

579 lines 13.0 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 = array();
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 = array();
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	* Default query arguments.
80	*
81	* Not all of these are valid arguments that can be passed to WP_Query. The ones that are not, are modified before
82	* the query is run to convert them to the proper syntax.
83	*
84	* @since 1.8.14
85	* @access public
86	*
87	* @param $args array The array of arguments that can be passed in and used for setting up this payment query.
88	*/
89	public function __construct( $args = array() ) {
90	$defaults = array(
91	'number' => 20,
92	'offset' => 0,
93	'paged' => 1,
94	'orderby' => 'id',
95	'order' => 'DESC',
96	'user' => null,
97	'email' => null,
98	'donor' => null,
99	'meta_query' => array(),
100	'date_query' => array(),
101	's' => null,
102	'fields' => 'all', // Supports donors (all fields) or valid column as string or array list.
103	'count' => false,
104	'give_forms' => array(),
105
106	/**
107	* donation_amount will contain value like:
108	* array(
109	* 'compare' => compare symbol (by default set to > )
110	* 'amount' => numeric_value
111	* )
112	*
113	* You can also pass number value to this param then compare symbol will auto set to >
114	*/
115	'donation_amount' => array()
116	// 'form' => array(),
117	);
118
119	$this->args = wp_parse_args( $args, $defaults );
120	$this->table_name = Give()->donors->table_name;
121	$this->meta_table_name = Give()->donor_meta->table_name;
122	$this->meta_type = Give()->donor_meta->meta_type;
123	}
124
125	/**
126	* Modify the query/query arguments before we retrieve donors.
127	*
128	* @since 1.8.14
129	* @access public
130	*
131	* @return void
132	*/
133	public function init() {
134	}
135
136
137	/**
138	* Retrieve donors.
139	*
140	* The query can be modified in two ways; either the action before the
141	* query is run, or the filter on the arguments (existing mainly for backwards
142	* compatibility).
143	*
144	* @since 1.8.14
145	* @access public
146	*
147	* @global wpdb $wpdb
148	*
149	* @return array
150	*/
151	public function get_donors() {
152	global $wpdb;
153
154	/**
155	* Fires before retrieving donors.
156	*
157	* @since 1.8.14
158	*
159	* @param Give_Donors_Query $this Donors query object.
160	*/
161	do_action( 'give_pre_get_donors', $this );
162
163	$cache_key = Give_Cache::get_key( 'give_donor', $this->get_sql(), false );
164
165	// Get donors from cache.
166	$this->donors = Give_Cache::get_db_query( $cache_key );
167
168	if ( is_null( $this->donors ) ) {
169	if ( empty( $this->args['count'] ) ) {
170	$this->donors = $wpdb->get_results( $this->get_sql() );
171	} else {
172	$this->donors = $wpdb->get_var( $this->get_sql() );
173	}
174
175	Give_Cache::set_db_query( $cache_key, $this->donors );
176	}
177
178
179	/**
180	* Fires after retrieving donors.
181	*
182	* @since 1.8.14
183	*
184	* @param Give_Donors_Query $this Donors query object.
185	*/
186	do_action( 'give_post_get_donors', $this );
187
188	return $this->donors;
189	}
190
191	/**
192	* Get sql query from queried array.
193	*
194	* @since 2.0
195	* @access public
196	*
197	* @global wpdb $wpdb
198	* @return string
199	*/
200	public function get_sql() {
201	global $wpdb;
202
203	if ( $this->args['number'] < 1 ) {
204	$this->args['number'] = 99999999999;
205	}
206
207	$where = $this->get_where_query();
208
209
210	// Set offset.
211	if ( empty( $this->args['offset'] ) && ( 0 < $this->args['paged'] ) ) {
212	$this->args['offset'] = $this->args['number'] * ( $this->args['paged'] - 1 );
213	}
214
215	// Set fields.
216	$fields = "{$this->table_name}.*";
217	if ( ! empty( $this->args['fields'] ) && 'all' !== $this->args['fields'] ) {
218	if ( is_string( $this->args['fields'] ) ) {
219	$fields = "{$this->table_name}.{$this->args['fields']}";
220	} elseif ( is_array( $this->args['fields'] ) ) {
221	$fields = "{$this->table_name}." . implode( " , {$this->table_name}.", $this->args['fields'] );
222	}
223	}
224
225	// Set count.
226	if ( ! empty( $this->args['count'] ) ) {
227	$fields = "COUNT({$this->table_name}.id)";
228	}
229
230	$orderby = $this->get_order_query();
231
232	$sql = $wpdb->prepare( "SELECT {$fields} FROM {$this->table_name} LIMIT %d,%d;", absint( $this->args['offset'] ), absint( $this->args['number'] ) );
233
234	// $where, $orderby and order already prepared query they can generate notice if you re prepare them in above.
235	// WordPress consider LIKE condition as placeholder if start with s,f, or d.
236	$sql = str_replace( 'LIMIT', "{$where} {$orderby} LIMIT", $sql );
237
238	return $sql;
239	}
240
241	/**
242	* Set query where clause.
243	*
244	* @since 1.8.14
245	* @access private
246	*
247	* @global wpdb $wpdb
248	* @return string
249	*/
250	private function get_where_query() {
251
252	// Get sql query for meta.
253	if ( ! empty( $this->args['meta_query'] ) ) {
254	$meta_query_object = new WP_Meta_Query( $this->args['meta_query'] );
255	$meta_query = $meta_query_object->get_sql( $this->meta_type, $this->table_name, 'id' );
256
257	$where[] = implode( '', $meta_query );
258	}
259
260	$where[] = 'WHERE 1=1';
261	$where[] = $this->get_where_search();
262	$where[] = $this->get_where_email();
263	$where[] = $this->get_where_donor();
264	$where[] = $this->get_where_user();
265	$where[] = $this->get_where_date();
266	$where[] = $this->get_where_donation_amount();
267	$where[] = $this->get_where_donation_count();
268	$where[] = $this->get_where_give_forms();
269
270	$where = array_filter( $where );
271
272	return trim( implode( ' ', array_map( 'trim', $where ) ) );
273
274	}
275
276	/**
277	* Set email where clause.
278	*
279	* @since 1.8.14
280	* @access private
281	*
282	* @global wpdb $wpdb
283	* @return string
284	*/
285	private function get_where_email() {
286	global $wpdb;
287
288	$where = '';
289
290	if ( ! empty( $this->args['email'] ) ) {
291
292	if ( is_array( $this->args['email'] ) ) {
293
294	$emails_count = count( $this->args['email'] );
295	$emails_placeholder = array_fill( 0, $emails_count, '%s' );
296	$emails = implode( ', ', $emails_placeholder );
297
298	$where .= $wpdb->prepare( "AND {$this->table_name}.email IN( $emails )", $this->args['email'] );
299	} else {
300	$where .= $wpdb->prepare( "AND {$this->table_name}.email = %s", $this->args['email'] );
301	}
302	}
303
304	return $where;
305	}
306
307	/**
308	* Set donor where clause.
309	*
310	* @since 1.8.14
311	* @access private
312	*
313	* @global wpdb $wpdb
314	* @return string
315	*/
316	private function get_where_donor() {
317	$where = '';
318
319	// Specific donors.
320	if ( ! empty( $this->args['donor'] ) ) {
321	if ( ! is_array( $this->args['donor'] ) ) {
322	$this->args['donor'] = explode( ',', $this->args['donor'] );
323	}
324	$donor_ids = implode( ',', array_map( 'intval', $this->args['donor'] ) );
325
326	$where .= "AND {$this->table_name}.id IN( {$donor_ids} )";
327	}
328
329	return $where;
330	}
331
332	/**
333	* Set date where clause.
334	*
335	* @since 1.8.14
336	* @access private
337	*
338	* @global wpdb $wpdb
339	* @return string
340	*/
341	private function get_where_date() {
342	$where = '';
343
344	// Donors created for a specific date or in a date range
345	if ( ! empty( $this->args['date_query'] ) ) {
346	$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" );
347
348	$where .= str_replace( array(
349	"\n",
350	'( (',
351	'))',
352	), array(
353	'',
354	'( (',
355	') )',
356	), $date_query_object->get_sql() );
357	}
358
359	return $where;
360	}
361
362	/**
363	* Set search where clause.
364	*
365	* @since 1.8.14
366	* @access private
367	*
368	* @global wpdb $wpdb
369	* @return string
370	*/
371	private function get_where_search() {
372	$where = '';
373
374	// Donors created for a specific date or in a date range
375	if ( ! empty( $this->args['s'] ) && false !== strpos( $this->args['s'], ':' ) ) {
376	$search_parts = explode( ':', $this->args['s'] );
377
378	if ( ! empty( $search_parts[0] ) ) {
379	switch ( $search_parts[0] ) {
380	case 'name':
381	$where = "AND {$this->table_name}.name LIKE '%{$search_parts[1]}%'";
382	break;
383
384	case 'note':
385	$where = "AND {$this->table_name}.notes LIKE '%{$search_parts[1]}%'";
386	break;
387	}
388	}
389	}
390
391	return $where;
392	}
393
394	/**
395	* Set user where clause.
396	*
397	* @since 1.8.14
398	* @access private
399	*
400	* @global wpdb $wpdb
401	* @return string
402	*/
403	private function get_where_user() {
404	$where = '';
405
406	// Donors create for specific wp user.
407	if ( ! empty( $this->args['user'] ) ) {
408	if ( ! is_array( $this->args['user'] ) ) {
409	$this->args['user'] = explode( ',', $this->args['user'] );
410	}
411	$user_ids = implode( ',', array_map( 'intval', $this->args['user'] ) );
412
413	$where .= "AND {$this->table_name}.user_id IN( {$user_ids} )";
414	}
415
416	return $where;
417	}
418
419	/**
420	* Set orderby query
421	*
422	* @since 1.8.14
423	* @access private
424	*
425	* @return string
426	*/
427	private function get_order_query() {
428	$table_columns = Give()->donors->get_columns();
429
430	$query = array();
431	$ordersby = $this->args['orderby'];
432
433	if( ! is_array( $ordersby ) ) {
434	$ordersby = array(
435	$this->args['orderby'] => $this->args['order']
436	);
437	}
438
439	// Remove non existing column.
440	// Filter orderby values.
441	foreach ( $ordersby as $orderby => $order ) {
442	if( ! array_key_exists( $orderby, $table_columns ) ) {
443	unset( $ordersby[$orderby] );
444	}
445
446	$ordersby[ esc_sql( $orderby ) ] = esc_sql( $order );
447	}
448
449	if( empty( $ordersby ) ) {
450	$ordersby = array(
451	'id' => $this->args['order']
452	);
453	}
454
455	// Create query.
456	foreach ( $ordersby as $orderby => $order ) {
457	switch ( $table_columns[ $orderby ] ) {
458	case '%d':
459	case '%f':
460	$query[] = "{$this->table_name}.{$orderby}+0 {$order}";
461	break;
462
463	default:
464	$query[] = "{$this->table_name}.{$orderby} {$order}";
465	}
466	}
467
468	return ! empty( $query ) ? 'ORDER BY ' . implode( ', ', $query ) : '';
469	}
470
471	/**
472	* Set donation count value where clause.
473	* @todo: add phpunit test
474	*
475	* @since 2.2.0
476	* @access private
477	*
478	* @global wpdb $wpdb
479	* @return string
480	*/
481	private function get_where_donation_count() {
482	$where = '';
483
484	if ( ! empty( $this->args['donation_count'] ) ) {
485	$compare = '>';
486	$amount = $this->args['donation_count'];
487	if ( is_array( $this->args['donation_count'] ) ) {
488	$compare = $this->args['donation_count'] ['compare'];
489	$amount = $this->args['donation_count']['amount'];
490	}
491
492	$where .= "AND {$this->table_name}.purchase_count{$compare}{$amount}";
493	}
494
495	return $where;
496	}
497
498	/**
499	* Set purchase value where clause.
500	* @todo: add phpunit test
501	*
502	* @since 2.1.0
503	* @access private
504	*
505	* @global wpdb $wpdb
506	* @return string
507	*/
508	private function get_where_donation_amount() {
509	$where = '';
510
511	if ( ! empty( $this->args['donation_amount'] ) ) {
512	$compare = '>';
513	$amount = $this->args['donation_amount'];
514	if ( is_array( $this->args['donation_amount'] ) ) {
515	$compare = $this->args['donation_amount'] ['compare'];
516	$amount = $this->args['donation_amount']['amount'];
517	}
518
519	$where .= "AND {$this->table_name}.purchase_value{$compare}{$amount}";
520	}
521
522	return $where;
523	}
524
525	/**
526	* Set give_forms where clause.
527	*
528	* @todo : add phpunit test
529	*
530	* @since 2.1.0
531	* @access private
532	*
533	* @global wpdb $wpdb
534	* @return string
535	*/
536	private function get_where_give_forms() {
537	global $wpdb;
538	$where = '';
539
540	if ( ! empty( $this->args['give_forms'] ) ) {
541	if ( ! is_array( $this->args['give_forms'] ) ) {
542	$this->args['give_forms'] = explode( ',', $this->args['give_forms'] );
543	}
544
545	$form_ids = implode( ',', array_map( 'intval', $this->args['give_forms'] ) );
546	$donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
547
548	$query = $wpdb->prepare(
549	"
550	SELECT DISTINCT meta_value as donor_id
551	FROM {$wpdb->donationmeta}
552	WHERE meta_key=%s
553	AND {$donation_id_col} IN(
554	SELECT {$donation_id_col}
555	FROM {$wpdb->paymentmeta}
556	WHERE meta_key=%s
557	AND meta_value IN (%s)
558	)
559	",
560	'_give_payment_donor_id',
561	'_give_payment_form_id',
562	$form_ids
563	);
564
565	$donor_ids = $wpdb->get_results( $query, ARRAY_A );
566
567	if ( ! empty( $donor_ids ) ) {
568	$donor_ids = wp_list_pluck( $donor_ids, 'donor_id' );
569	$donor_ids = implode( ',', array_map( 'intval', $donor_ids ) );
570	$where .= "AND {$this->table_name}.id IN ({$donor_ids})";
571	} else {
572	$where .= "AND {$this->table_name}.id IN ('0')";
573	}
574	}
575
576	return $where;
577	}
578	}
579