charitable / includes / data / class-charitable-campaign-donations-db.php

class-charitable-campaign-donations-db.php in Charitable – Donation & Fundraising Platform (Donation Forms, Recurring Donations & Fundraising Campaigns) 1.8.11.3, at includes/data/class-charitable-campaign-donations-db.php

1,025 lines 30.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Charitable Campaign Donations DB class.
4	*
5	* @package Charitable/Classes/Charitable_Campaign_Donations_DB
6	* @author David Bisset
7	* @copyright Copyright (c) 2023, WP Charitable LLC
8	* @license http://opensource.org/licenses/gpl-2.0.php GNU Public License
9	* @since 1.0.0
10	* @version 1.6.57
11	*/
12
13	// Exit if accessed directly.
14	if ( ! defined( 'ABSPATH' ) ) {
15	exit;
16	}
17
18	if ( ! class_exists( 'Charitable_Campaign_Donations_DB' ) ) :
19
20	/**
21	* Charitable_Campaign_Donations_DB
22	*
23	* @since 1.0.0
24	*/
25	class Charitable_Campaign_Donations_DB extends Charitable_DB {
26
27	/**
28	* The version of our database table
29	*
30	* @since 1.0.0
31	*
32	* @var string
33	*/
34	public $version = '1.5.4';
35
36	/**
37	* The name of the primary column
38	*
39	* @since 1.0.0
40	*
41	* @var string
42	*/
43	public $primary_key = 'campaign_donation_id';
44
45	/**
46	* Stores whether the site is using commas for decimals in amounts.
47	*
48	* @since 1.3.0
49	*
50	* @var boolean
51	*/
52	private $comma_decimal;
53
54	/**
55	* Set up the database table name.
56	*
57	* @since 1.0.0
58	*
59	* @global WPDB $wpdb
60	*/
61	public function __construct() {
62	global $wpdb;
63
64	$this->table_name = $wpdb->prefix . 'charitable_campaign_donations';
65	}
66
67	/**
68	* Create the table.
69	*
70	* @since 1.0.0
71	*
72	* @global WPDB $wpdb
73	*/
74	public function create_table() {
75	global $wpdb;
76
77	$charset_collate = $wpdb->get_charset_collate();
78
79	$sql = "CREATE TABLE {$this->table_name} (
80	campaign_donation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
81	donation_id bigint(20) unsigned NOT NULL,
82	donor_id bigint(20) unsigned NOT NULL,
83	campaign_id bigint(20) unsigned NOT NULL,
84	campaign_name text NOT NULL,
85	amount decimal(13, 4) NOT NULL,
86	PRIMARY KEY (campaign_donation_id),
87	KEY donation (donation_id),
88	KEY campaign (campaign_id),
89	KEY donor (donor_id)
90	) $charset_collate;";
91
92	$this->_create_table( $sql );
93	}
94
95	/**
96	* Whitelist of columns.
97	*
98	* @since 1.0.0
99	*
100	* @return array
101	*/
102	public function get_columns() {
103	return array(
104	'campaign_donation_id' => '%d',
105	'donation_id' => '%d',
106	'donor_id' => '%d',
107	'campaign_id' => '%d',
108	'campaign_name' => '%s',
109	'amount' => '%f',
110	);
111	}
112
113	/**
114	* Default column values.
115	*
116	* @since 1.0.0
117	*
118	* @return array
119	*/
120	public function get_column_defaults() {
121	return array();
122	}
123
124	/**
125	* Add a new campaign donation.
126	*
127	* @since 1.0.0
128	*
129	* @param array $data Data we are inserting.
130	* @param string $type Type of record we are inserting.
131	* @return int The ID of the inserted campaign donation
132	*/
133	public function insert( $data, $type = 'campaign_donation' ) {
134	if ( ! isset( $data['campaign_name'] ) ) {
135	$data['campaign_name'] = get_the_title( $data['campaign_id'] );
136	}
137
138	Charitable_Campaign::flush_donations_cache( $data['campaign_id'] );
139
140	return parent::insert( $data, $type );
141	}
142
143	/**
144	* Update campaign donation record.
145	*
146	* @since 1.0.0
147	*
148	* @param int $row_id The primary key ID for the row we are retrieving.
149	* @param array $data The updated date.
150	* @param string $where Column used in where argument.
151	* @return boolean
152	*/
153	public function update( $row_id, $data = array(), $where = '' ) {
154	$this->flush_campaign_caches( $row_id, $where );
155
156	return parent::update( $row_id, $data, $where );
157	}
158
159	/**
160	* Delete a row identified by the primary key.
161	*
162	* @since 1.0.0
163	*
164	* @param int $row_id The primary key ID.
165	* @return boolean
166	*/
167	public function delete( $row_id = 0 ) {
168	$this->flush_campaign_caches( $row_id );
169
170	return parent::delete( $row_id );
171	}
172
173	/**
174	* Delete all campaign donation records for a given donation.
175	*
176	* @since 1.2.0
177	* @version 1.8.8.1
178	*
179	* @param int $donation_id The donation ID.
180	* @return boolean
181	*/
182	public static function delete_donation_records( $donation_id ) {
183	// Only act on Charitable donation post type.
184	$post_type = get_post_type( (int) $donation_id );
185	if ( empty( $post_type ) \|\| ( defined( 'Charitable::DONATION_POST_TYPE' ) ? $post_type !== Charitable::DONATION_POST_TYPE : $post_type !== 'donation' ) ) { // Fallback to 'donation' slug if constant is unavailable.
186	return false;
187	}
188
189	$table = charitable_get_table( 'campaign_donations' );
190	if ( empty( $table ) \|\| ! is_object( $table ) \|\| ! method_exists( $table, 'get_campaigns_for_donation' ) ) {
191	return false;
192	}
193
194	foreach ( $table->get_campaigns_for_donation( $donation_id ) as $campaign_id ) {
195	Charitable_Campaign::flush_donations_cache( $campaign_id );
196	}
197
198	if ( ! method_exists( $table, 'delete_by' ) ) {
199	return false;
200	}
201
202	return $table->delete_by( 'donation_id', $donation_id );
203	}
204
205	/**
206	* Get the total amount donated, ever.
207	*
208	* @since 1.0.0
209	*
210	* @global $wpdb WPDB
211	* @param string[] $statuses List of statuses.
212	* @return float
213	*/
214	public function get_total( $statuses = array() ) {
215	global $wpdb;
216
217	if ( empty( $statuses ) ) {
218	$statuses = charitable_get_approval_statuses();
219	}
220
221	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
222
223	if ( ! empty( $status_clause ) ) {
224	$where_sql = 'WHERE ' . $status_clause;
225	}
226
227	$sql = "SELECT COALESCE( SUM(cd.amount), 0 )
228	FROM $this->table_name cd
229	INNER JOIN $wpdb->posts p
230	ON p.ID = cd.donation_id
231	$where_sql";
232
233	$total = $wpdb->get_var( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
234
235	if ( $this->is_comma_decimal() ) {
236	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
237	}
238
239	return $total;
240	}
241
242	/**
243	* Return an object containing all campaign donations associated with a particular
244	* campaign ID or a particular donation ID.
245	*
246	* @since 1.4.0
247	*
248	* @global WPDB $wpdb
249	* @param string $field The field we are retrieving donations by. Either 'campaign' or 'donation'.
250	* @param int\|int[] $donation_id A single donation ID or an array of IDs.
251	* @return object
252	*/
253	public function get_campaign_donations_by( $field, $donation_id ) {
254	global $wpdb;
255
256	$column = $this->get_sanitized_column( $field );
257
258	list( $in, $parameters ) = $this->get_in_clause_params( $donation_id );
259
260	$sql = "SELECT *
261	FROM $this->table_name
262	WHERE $column IN ( $in );";
263
264	$records = $wpdb->get_results( $wpdb->prepare( $sql, $parameters ), OBJECT_K ); // phpcs:ignore
265
266	if ( $this->is_comma_decimal() ) {
267	$records = array_map( array( $this, 'sanitize_amounts' ), $records );
268	}
269
270	return $records;
271	}
272
273	/**
274	* Return a list of all distinct IDs based on a particular field.
275	*
276	* @since 1.4.0
277	*
278	* @global WPDB $wpdb
279	* @param string $field The distinct field we are retrieving.
280	* @param int\|int[] $id An ID or an array of IDs.
281	* @param string $where_field Column used for the where argument.
282	* @return object
283	*/
284	public function get_distinct_ids( $field, $id, $where_field = 'campaign_id' ) {
285	global $wpdb;
286
287	$select_column = $this->get_sanitized_column( $field );
288	$where_column = $this->get_sanitized_column( $where_field );
289
290	list( $in, $parameters ) = $this->get_in_clause_params( $id );
291
292	$sql = "SELECT DISTINCT $select_column
293	FROM $this->table_name
294	WHERE $where_column IN ( $in );";
295
296	return $wpdb->get_col( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
297	}
298
299	/**
300	* Get an object of all campaign donations associated with one or more donations.
301	*
302	* @uses Charitable_Campaign_Donations_DB::get_campaign_donations_by()
303	*
304	* @since 1.0.0
305	*
306	* @param int\|int[] $donation_id A single donation ID or an array of IDs.
307	* @return object
308	*/
309	public function get_donation_records( $donation_id ) {
310	return $this->get_campaign_donations_by( 'donation_id', $donation_id );
311	}
312
313	/**
314	* Get the amount donated in a single donation.
315	*
316	* @since 1.3.0
317	*
318	* @global WPDB $wpdb
319	* @param int\|int[] $donation_id A single donation ID or an array of IDs.
320	* @param int\|int[] $campaign Optional. If set, this will only return the total donated to that campaign, or array of campaigns.
321	* @return decimal
322	*/
323	public function get_donation_amount( $donation_id, $campaign = '' ) {
324	global $wpdb;
325
326	list( $in, $parameters ) = $this->get_in_clause_params( $donation_id );
327
328	$where_clause = "donation_id IN ( $in )";
329
330	if ( ! empty( $campaign ) ) {
331
332	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign );
333
334	$where_clause .= " AND campaign_id IN ( $campaigns_in )";
335	$parameters = array_merge( $parameters, $campaigns_parameters );
336
337	}
338
339	$sql = "SELECT SUM(amount)
340	FROM $this->table_name
341	WHERE $where_clause;";
342
343	$total = $wpdb->get_var( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
344
345	if ( $this->is_comma_decimal() ) {
346	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
347	}
348
349	return $total;
350	}
351
352	/**
353	* Get the total amount donated in a single donation.
354	*
355	* @since 1.0.0
356	*
357	* @param int $donation_id Donation ID.
358	* @return decimal
359	*/
360	public function get_donation_total_amount( $donation_id ) {
361	return $this->get_donation_amount( $donation_id );
362	}
363
364	/**
365	* Return an array of campaigns donated to in a single donation.
366	*
367	* @since 1.2.0
368	*
369	* @global wpdb $wpdb WPDB.
370	* @param int $donation_id Donation ID.
371	* @return object
372	*/
373	public function get_campaigns_for_donation( $donation_id ) {
374	global $wpdb;
375
376	$sql = "SELECT DISTINCT campaign_id
377	FROM $this->table_name
378	WHERE donation_id = %d;";
379
380	return $wpdb->get_col( $wpdb->prepare( $sql, intval( $donation_id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
381	}
382
383	/**
384	* Get an object of all donations on a campaign.
385	*
386	* @uses Charitable_Campaign_Donations_DB::get_campaign_donations_by()
387	*
388	* @since 1.0.0
389	*
390	* @param int\|int[] $campaign_id Campaign ID.
391	* @return object
392	*/
393	public function get_donations_on_campaign( $campaign_id ) {
394	return $this->get_campaign_donations_by( 'campaign_id', $campaign_id );
395	}
396
397	/**
398	* Get total amount donated to a campaign.
399	*
400	* @since 1.0.0
401	*
402	* @global wpdb $wpdb WPDB.
403	* @param int\|int[] $campaigns A campaign ID. Optionally, you can pass an array of campaign IDs to get the total of all put together.
404	* @param boolean $include_all Whether donations with non-approved statuses should be included.
405	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
406	* @return string
407	*/
408	public function get_campaign_donated_amount( $campaigns, $include_all = false, $sanitize = true ) {
409	return $this->get_amount_report(
410	array(
411	'status' => $include_all ? array() : charitable_get_approval_statuses(),
412	'campaigns' => $campaigns,
413	),
414	$sanitize
415	);
416	}
417
418	/**
419	* Get an array of all donation ids for a campaign.
420	*
421	* @uses Charitable_Campaign_Donations_DB::get_distinct_ids()
422	*
423	* @since 1.0.0
424	*
425	* @param int $campaign_id Campaign ID.
426	* @return object
427	*/
428	public function get_donation_ids_for_campaign( $campaign_id ) {
429	return $this->get_distinct_ids( 'donation_id', $campaign_id, 'campaign_id' );
430	}
431
432	/**
433	* The donor IDs of all who have donated to the given campaign.
434	*
435	* @uses Charitable_Campaign_Donations_DB::get_distinct_ids()
436	*
437	* @since 1.0.0
438	*
439	* @param int $campaign_id The campaign ID to get donors for.
440	* @return object
441	*/
442	public function get_campaign_donors( $campaign_id ) {
443	return $this->get_distinct_ids( 'donor_id', $campaign_id, 'campaign_id' );
444	}
445
446	/**
447	* Return the number of users who have donated to the given campaign.
448	*
449	* @since 1.0.0
450	*
451	* @global wpdb $wpdb WPDB.
452	* @param int\|int[] $campaign The campaign ID, or list of campaign IDs.
453	* @param boolean $include_all Whether to include all donations (true), or only include approved donations (false).
454	* @return int
455	*/
456	public function count_campaign_donors( $campaign, $include_all = false ) {
457	global $wpdb;
458
459	$statuses = $include_all ? array() : charitable_get_approval_statuses();
460
461	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
462	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign );
463
464	$sql_where_clauses[] = "cd.campaign_id IN ( $campaigns_in )";
465	$sql_where_clauses[] = $status_clause;
466	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
467
468	$parameters = array_merge( $campaigns_parameters, $status_parameters );
469
470	$sql = "SELECT COUNT( DISTINCT cd.donor_id )
471	FROM $this->table_name cd
472	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
473	$sql_where;";
474
475	return $wpdb->get_var( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
476	}
477
478	/**
479	* Return all donations made by a donor.
480	*
481	* @since 1.0.0
482	*
483	* @global wpdb $wpdb WPDB.
484	* @param int $donor_id The donor ID.
485	* @param boolean $distinct_donations Whether to get distinct donations.
486	* @return object[]
487	*/
488	public function get_donations_by_donor( $donor_id, $distinct_donations = false ) {
489	global $wpdb;
490
491	if ( $distinct_donations ) {
492	$select_fields = 'DISTINCT( cd.donation_id ), cd.campaign_id, cd.campaign_name, cd.amount';
493	} else {
494	$select_fields = 'cd.campaign_donation_id, cd.donation_id, cd.campaign_id, cd.campaign_name, cd.amount';
495	}
496
497	$sql = "SELECT $select_fields
498	FROM $this->table_name cd
499	WHERE cd.donor_id = %d;";
500
501	$results = $wpdb->get_results( $wpdb->prepare( $sql, $donor_id ), OBJECT_K ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
502
503	if ( $this->is_comma_decimal() ) {
504	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
505	}
506
507	return $results;
508	}
509
510	/**
511	* Return total amount donated by a donor.
512	*
513	* @since 1.0.0
514	*
515	* @global WPDB $wpdb
516	* @param int $donor_id The donor ID.
517	* @return int
518	*/
519	public function get_total_donated_by_donor( $donor_id ) {
520	global $wpdb;
521
522	$sql = "SELECT COALESCE( SUM(cd.amount), 0 )
523	FROM $this->table_name cd
524	WHERE cd.donor_id = %d;";
525
526	$total = $wpdb->get_var( $wpdb->prepare( $sql, $donor_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
527
528	if ( $this->is_comma_decimal() ) {
529	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
530	}
531
532	return $total;
533	}
534
535	/**
536	* Count the number of donations made by the donor.
537	*
538	* @since 1.0.0
539	*
540	* @global wpdb $wpdb WPDB.
541	* @param int $donor_id The donor ID.
542	* @param boolean $distinct_donations If true, will only count unique donations.
543	* @return int
544	*/
545	public function count_donations_by_donor( $donor_id, $distinct_donations = false ) {
546	global $wpdb;
547
548	$count = $distinct_donations ? 'DISTINCT donation_id' : 'donation_id';
549
550	$sql = "SELECT COUNT( $count )
551	FROM $this->table_name
552	WHERE donor_id = %d;";
553
554	return $wpdb->get_var( $wpdb->prepare( $sql, $donor_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
555	}
556
557	/**
558	* Count the number of campaigns that the donor has supported.
559	*
560	* @since 1.0.0
561	*
562	* @global wpdb $wpdb WPDB.
563	* @param int $donor_id The donor to retrieve campaigns for.
564	* @return int
565	*/
566	public function count_campaigns_supported_by_donor( $donor_id ) {
567	global $wpdb;
568
569	$sql = "SELECT COUNT( DISTINCT campaign_id )
570	FROM $this->table_name
571	WHERE donor_id = %d;";
572
573	return $wpdb->get_var( $wpdb->prepare( $sql, $donor_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
574	}
575
576	/**
577	* Return a set of donations, filtered by the provided arguments.
578	*
579	* @since 1.0.0
580	* @since 1.8.1.8 Added filter.
581	*
582	* @global WPDB $wpdb
583	* @param array $args Query arguments.
584	* @return array
585	*/
586	public function get_donations_report( $args ) {
587	global $wpdb;
588
589	$sql_order = $this->get_orderby_clause( $args, 'ORDER BY p.post_date ASC' );
590	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
591	$limit = $this->get_limit_clause( $args );
592
593	/* This is our base SQL query */
594	$sql = "SELECT cd.donation_id, cd.campaign_id, cd.campaign_name, cd.amount, d.email, d.first_name, d.last_name, p.post_date, p.post_content, p.post_status
595	FROM $this->table_name cd
596	INNER JOIN {$wpdb->prefix}charitable_donors d
597	ON d.donor_id = cd.donor_id
598	INNER JOIN $wpdb->posts p
599	ON p.ID = cd.donation_id
600	$sql_where
601	$sql_order
602	$limit";
603
604	$sql = apply_filters( 'charitable_campaign_donations_db_sql', $sql, $args, $sql_where, $sql_order, $parameters );
605
606	if ( ! empty( $parameters ) ) {
607	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
608	}
609
610	$results = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
611
612	if ( $this->is_comma_decimal() ) {
613	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
614	}
615
616	return $results;
617	}
618
619	/**
620	* Return a total amount donated
621	*
622	* @since 1.6.57
623	*
624	* @global WPDB $wpdb
625	* @param array $args Query arguments.
626	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
627	* @return int The total amount
628	*/
629	public function get_amount_report( $args, $sanitize = true ) {
630	global $wpdb;
631
632	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
633
634	/* Get the total */
635	$sql = "SELECT COALESCE( SUM(amount), 0 )
636	FROM $this->table_name cd
637	INNER JOIN $wpdb->posts p
638	ON p.ID = cd.donation_id
639	$sql_where";
640
641	if ( ! empty( $parameters ) ) {
642	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
643	}
644
645	$total = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
646
647	if ( $this->is_comma_decimal() && $sanitize ) {
648	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
649	}
650
651	return $total;
652	}
653
654	/**
655	* Return a total amount donated, grouped by campaign.
656	* Created for Charitable-Stats-Shortcode.
657	*
658	* @since 1.6.57
659	*
660	* @global WPDB $wpdb
661	*
662	* @param array $args Query arguments.
663	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
664	* @return array An array of amounts and campaign names
665	*/
666	public function get_amount_by_campaign_report( $args, $sanitize = true ) {
667	global $wpdb;
668
669	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
670
671	/* Get the total */
672	$sql = "SELECT cd.campaign_name, COALESCE( SUM(amount), 0 ) as total
673	FROM $this->table_name cd
674	INNER JOIN $wpdb->posts p
675	ON p.ID = cd.donation_id
676	$sql_where
677	GROUP BY cd.campaign_id, cd.campaign_name";
678
679	if ( ! empty( $parameters ) ) {
680	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
681	}
682
683	$results = $wpdb->get_results( $sql, 'ARRAY_N' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
684
685	if ( $this->is_comma_decimal() && $sanitize ) {
686	$results = array_map( array( $this, 'sanitize_grouped_amounts' ), $results );
687	}
688
689	return $results;
690	}
691
692	/**
693	* Return an SQL limit for the report
694	*
695	* @since 1.8.1.10
696	*
697	* @param array $args Query arguments.
698	* @return array
699	*/
700	public function get_limit_clause( $args ) {
701	$limit = '';
702
703	if ( isset( $args['limit'] ) ) {
704	$limit = 'LIMIT ' . $args['limit'];
705	}
706
707	return $limit;
708	}
709
710	/**
711	* Return an SQL where clause for the report
712	*
713	* @since 1.6.57
714	*
715	* @param array $args Query arguments.
716	* @return array
717	*/
718	public function get_report_sql_where_clause( $args ) {
719	$parameters = array();
720	$sql_where = '';
721	$sql_where_clauses = array();
722
723	/* campaign_id should become 'campaigns'. */
724	if ( isset( $args['campaign_id'] ) ) {
725	$args['campaigns'] = $args['campaign_id'];
726	}
727
728	if ( isset( $args['campaigns'] ) ) {
729	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $args['campaigns'] );
730
731	$sql_where_clauses[] = "cd.campaign_id IN ( $campaigns_in )";
732	$parameters = array_merge( $parameters, $campaigns_parameters );
733	}
734
735	if ( isset( $args['status'] ) ) {
736	if ( ! is_array( $args['status'] ) ) {
737	/* Ensure this is an array. */
738	$args['status'] = array( $args['status'] );
739	}
740
741	list( $status_sql, $status_parameters ) = $this->get_donation_status_clause( $args['status'] );
742	$sql_where_clauses[] = $status_sql;
743	$parameters = array_merge( $parameters, $status_parameters );
744
745	} else {
746	/* If ALL: select all valid statuses */
747	$statuses = array_keys( charitable_get_valid_donation_statuses() );
748	$in = charitable_get_query_placeholders( count( $statuses ), '%s' );
749	$sql_where_clauses[] = "p.post_status IN ( $in )";
750	$parameters = array_merge( $parameters, $statuses );
751	}
752
753	if ( ! empty( $args['start_date'] ) ) {
754	$sql_where_clauses[] = 'p.post_date >= %s';
755	$parameters[] = $args['start_date'];
756	}
757
758	if ( ! empty( $args['end_date'] ) ) {
759	$sql_where_clauses[] = 'p.post_date <= %s';
760	$parameters[] = $args['end_date'];
761	}
762
763	// Added in 1.8.1.8.
764	if ( isset( $args['post_parent'] ) ) {
765	$sql_where_clauses[] = 'p.post_parent = %d';
766	$parameters[] = $args['post_parent'];
767	}
768
769	if ( ! empty( $sql_where_clauses ) ) {
770	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
771	}
772
773	return array( $sql_where, $parameters );
774	}
775
776	/**
777	* Return a count and sum of donations for a given period.
778	*
779	* @since 1.2.0
780	*
781	* @global WPDB $wpdb
782	* @param string $period The period to get donations for.
783	* @param string[] $statuses List of statuses.
784	* @return array
785	*/
786	public function get_donations_summary_by_period( $period = '', $statuses = array() ) {
787	global $wpdb;
788
789	if ( empty( $statuses ) ) {
790	$statuses = charitable_get_approval_statuses();
791	}
792
793	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
794
795	array_unshift( $parameters, $period );
796
797	$sql_where_clauses[] = "DATE_FORMAT( p.post_date, '%%Y-%%m-%%d' ) LIKE %s";
798	$sql_where_clauses[] = $status_clause;
799	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
800
801	$sql = "SELECT COALESCE( SUM( cd.amount ), 0 ) as amount, COUNT( cd.donation_id ) as count
802	FROM {$wpdb->prefix}charitable_campaign_donations cd
803	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
804	$sql_where";
805
806	$results = $wpdb->get_results( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
807
808	if ( empty( $results ) ) {
809	return (object) array(
810	'amount' => 0.0000,
811	'count' => 0,
812	);
813	}
814
815	$result = $results[0];
816
817	if ( $this->is_comma_decimal() ) {
818	$result = $this->sanitize_amounts( $result );
819	}
820
821	return $result;
822	}
823
824	/**
825	* Returns the orderby clause.
826	*
827	* @since 1.3.4
828	*
829	* @param array $args Query arguments.
830	* @param string $default Default.
831	* @return string
832	*/
833	public function get_orderby_clause( $args, $default = '' ) { // phpcs:ignore
834	if ( ! isset( $args['orderby'] ) && ! isset( $args['order'] ) ) {
835	return $default;
836	}
837
838	$orderby = isset( $args['orderby'] ) ? $args['orderby'] : 'ID';
839	$order = isset( $args['order'] ) && in_array( $args['order'], array( 'ASC', 'DESC' ) ) ? $args['order'] : 'ASC'; // phpcs:ignore
840
841	switch ( $orderby ) {
842	case 'date':
843	$ret = 'ORDER BY p.post_date ';
844	break;
845
846	default:
847	$ret = 'ORDER BY cd.campaign_donation_id ';
848	}
849
850	$ret .= $order;
851
852	return $ret;
853	}
854
855	/**
856	* Count donations by status.
857	*
858	* @since 1.0.0
859	*
860	* @param string\|string[] $statuses List of statuses.
861	* @return int
862	*/
863	public function count_donations_by_status( $statuses ) {
864	global $wpdb;
865
866	if ( ! is_array( $statuses ) ) {
867	$statuses = array( $statuses );
868	}
869
870	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
871	if ( ! empty( $status_clause ) ) {
872	$sql_where = 'WHERE ' . $status_clause;
873	}
874
875	$sql = "SELECT COUNT( * )
876	FROM {$wpdb->prefix}charitable_campaign_donations cd
877	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
878	$sql_where;";
879
880	return $wpdb->get_var( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
881	}
882
883	/**
884	* Returns the donation status clause.
885	*
886	* @since 1.0.0
887	*
888	* @param string[] $statuses List of statuses.
889	* @return array
890	*/
891	private function get_donation_status_clause( $statuses = array() ) {
892	if ( empty( $statuses ) ) {
893	return array( '', array() );
894	}
895
896	$statuses = array_filter( $statuses, 'charitable_is_valid_donation_status' );
897	$in = charitable_get_query_placeholders( count( $statuses ), '%s' );
898	$sql = "p.post_status IN ( $in )";
899
900	return array( $sql, $statuses );
901	}
902
903	/**
904	* Returns an array containing the placeholders and sanitized parameters for an IN clause.
905	*
906	* @since 1.4.0
907	*
908	* @param int\|int[] $list List of elements.
909	* @return array
910	*/
911	private function get_in_clause_params( $list ) { // phpcs:ignore
912	if ( ! is_array( $list ) ) {
913	$list = array( $list );
914	}
915
916	/* Filter out any non-numeric campaign IDs, then convert to int */
917	$list = array_filter( $list, 'is_numeric' );
918	$list = array_map( 'intval', $list );
919
920	return array( charitable_get_query_placeholders( count( $list ), '%d' ), $list );
921	}
922
923	/**
924	* Checks whether we are using commas for decimals.
925	*
926	* @since 1.3.0
927	*
928	* @return boolean
929	*/
930	private function is_comma_decimal() {
931	if ( ! isset( $this->comma_decimal ) ) {
932	$this->comma_decimal = Charitable_Currency::get_instance()->is_comma_decimal();
933	}
934
935	return $this->comma_decimal;
936	}
937
938	/**
939	* Sanitize amounts retrieved from the database.
940	*
941	* @since 1.3.0
942	*
943	* @param object $campaign_donation Campaign donation record.
944	* @return object
945	*/
946	private function sanitize_amounts( $campaign_donation ) {
947	$campaign_donation->amount = Charitable_Currency::get_instance()->sanitize_database_amount( $campaign_donation->amount );
948	return $campaign_donation;
949	}
950
951	/**
952	* Sanitize amounts for grouped queries
953	*
954	* @since 1.6.57
955	*
956	* @param array $item in array is a label, item[1] is the amount. We need to sanitize the amount.
957	* @return object
958	*/
959	private function sanitize_grouped_amounts( $item ) {
960	$item[1] = Charitable_Currency::get_instance()->sanitize_database_amount( $item[1] );
961	return $item;
962	}
963
964	/**
965	* Return a sanitized column name.
966	*
967	* @since 1.4.0
968	*
969	* @param string $field Return the column name.
970	* @return string
971	*/
972	private function get_sanitized_column( $field ) {
973	switch ( $field ) {
974	case 'campaign':
975	case 'campaign_id':
976	$column = 'campaign_id';
977	break;
978
979	case 'donation':
980	case 'donation_id':
981	$column = 'donation_id';
982	break;
983
984	case 'donor':
985	case 'donor_id':
986	$column = 'donor_id';
987	break;
988
989	default:
990	charitable_get_deprecated()->doing_it_wrong(
991	__METHOD__,
992	__( 'Field expected to be `campaign`, `campaign_id`, `donation`, `donation_id`, `donor` or `donor_id`.', 'charitable' ),
993	'1.4.0'
994	);
995
996	$column = false;
997	}
998
999	return $column;
1000	}
1001
1002	/**
1003	* Flush campaign caches when updating one or more rows.
1004	*
1005	* @since 1.5.11
1006	*
1007	* @param int $row_id The primary key ID for the row we are retrieving.
1008	* @param string $where Column used in where argument.
1009	* @return void
1010	*/
1011	private function flush_campaign_caches( $row_id, $where = '' ) {
1012	if ( empty( $where ) ) {
1013	$where = $this->primary_key;
1014	}
1015
1016	$campaign_ids = array_unique( $this->get_column_all_by( 'campaign_id', $where, $row_id ) );
1017
1018	foreach ( $campaign_ids as $campaign_id ) {
1019	Charitable_Campaign::flush_donations_cache( $campaign_id );
1020	}
1021	}
1022	}
1023
1024	endif;
1025