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.12.2, at includes/data/class-charitable-campaign-donations-db.php

1,101 lines 33.3 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	* Return a count and sum of donations for every day in a date range, keyed by day.
826	*
827	* Callers that need a per-day series must use this instead of calling
828	* get_donations_summary_by_period() once per calendar day. That pattern issued one
829	* uncached query per day, so the query count scaled with the LENGTH of the range
830	* rather than the amount of data, and a multi-year range timed out. It also wrapped
831	* post_date in DATE_FORMAT(), which prevented any index being used, making every
832	* one of those queries a full scan of the join.
833	*
834	* Days with no donations are absent from the return value; callers should treat a
835	* missing key as zero.
836	*
837	* @since 1.8.12.2
838	*
839	* @global WPDB $wpdb
840	* @param string $start_date Start of the range, Y-m-d. Inclusive.
841	* @param string $end_date End of the range, Y-m-d. Inclusive of the whole day.
842	* @param string[] $statuses List of statuses.
843	* @return array Keyed by Y-m-d, each value an object with 'amount' and 'count'.
844	*/
845	public function get_donations_summary_by_day_range( $start_date, $end_date, $statuses = array() ) {
846	global $wpdb;
847
848	if ( empty( $start_date ) \|\| empty( $end_date ) ) {
849	return array();
850	}
851
852	if ( empty( $statuses ) ) {
853	$statuses = charitable_get_approval_statuses();
854	}
855
856	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
857
858	// Every status was filtered out as invalid, so nothing could match. Without
859	// this the IN clause would be emitted empty and the query would error.
860	if ( empty( $status_parameters ) ) {
861	return array();
862	}
863
864	$parameters = array_merge(
865	array( $start_date . ' 00:00:00', $end_date . ' 23:59:59' ),
866	$status_parameters
867	);
868
869	// Sums cd.amount, matching get_donations_summary_by_period() above. The
870	// base_amount column belongs to the multi-currency schema and does not exist
871	// on this table in Lite, so it must not appear here.
872	$sql = "SELECT DATE( p.post_date ) as day,
873	COALESCE( SUM( cd.amount ), 0 ) as amount,
874	COUNT( cd.donation_id ) as count
875	FROM {$wpdb->prefix}charitable_campaign_donations cd
876	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
877	WHERE p.post_date >= %s AND p.post_date <= %s AND {$status_clause}
878	GROUP BY DATE( p.post_date )";
879
880	$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
881
882	if ( empty( $results ) ) {
883	return array();
884	}
885
886	$comma_decimal = $this->is_comma_decimal();
887	$summary = array();
888
889	foreach ( $results as $result ) {
890	if ( $comma_decimal ) {
891	$result = $this->sanitize_amounts( $result );
892	}
893
894	$summary[ $result->day ] = $result;
895	}
896
897	return $summary;
898	}
899
900	/**
901	* Returns the orderby clause.
902	*
903	* @since 1.3.4
904	*
905	* @param array $args Query arguments.
906	* @param string $default Default.
907	* @return string
908	*/
909	public function get_orderby_clause( $args, $default = '' ) { // phpcs:ignore
910	if ( ! isset( $args['orderby'] ) && ! isset( $args['order'] ) ) {
911	return $default;
912	}
913
914	$orderby = isset( $args['orderby'] ) ? $args['orderby'] : 'ID';
915	$order = isset( $args['order'] ) && in_array( $args['order'], array( 'ASC', 'DESC' ) ) ? $args['order'] : 'ASC'; // phpcs:ignore
916
917	switch ( $orderby ) {
918	case 'date':
919	$ret = 'ORDER BY p.post_date ';
920	break;
921
922	default:
923	$ret = 'ORDER BY cd.campaign_donation_id ';
924	}
925
926	$ret .= $order;
927
928	return $ret;
929	}
930
931	/**
932	* Count donations by status.
933	*
934	* @since 1.0.0
935	*
936	* @param string\|string[] $statuses List of statuses.
937	* @return int
938	*/
939	public function count_donations_by_status( $statuses ) {
940	global $wpdb;
941
942	if ( ! is_array( $statuses ) ) {
943	$statuses = array( $statuses );
944	}
945
946	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
947	if ( ! empty( $status_clause ) ) {
948	$sql_where = 'WHERE ' . $status_clause;
949	}
950
951	$sql = "SELECT COUNT( * )
952	FROM {$wpdb->prefix}charitable_campaign_donations cd
953	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
954	$sql_where;";
955
956	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
957	}
958
959	/**
960	* Returns the donation status clause.
961	*
962	* @since 1.0.0
963	*
964	* @param string[] $statuses List of statuses.
965	* @return array
966	*/
967	private function get_donation_status_clause( $statuses = array() ) {
968	if ( empty( $statuses ) ) {
969	return array( '', array() );
970	}
971
972	$statuses = array_filter( $statuses, 'charitable_is_valid_donation_status' );
973	$in = charitable_get_query_placeholders( count( $statuses ), '%s' );
974	$sql = "p.post_status IN ( $in )";
975
976	return array( $sql, $statuses );
977	}
978
979	/**
980	* Returns an array containing the placeholders and sanitized parameters for an IN clause.
981	*
982	* @since 1.4.0
983	*
984	* @param int\|int[] $list List of elements.
985	* @return array
986	*/
987	private function get_in_clause_params( $list ) { // phpcs:ignore
988	if ( ! is_array( $list ) ) {
989	$list = array( $list );
990	}
991
992	/* Filter out any non-numeric campaign IDs, then convert to int */
993	$list = array_filter( $list, 'is_numeric' );
994	$list = array_map( 'intval', $list );
995
996	return array( charitable_get_query_placeholders( count( $list ), '%d' ), $list );
997	}
998
999	/**
1000	* Checks whether we are using commas for decimals.
1001	*
1002	* @since 1.3.0
1003	*
1004	* @return boolean
1005	*/
1006	private function is_comma_decimal() {
1007	if ( ! isset( $this->comma_decimal ) ) {
1008	$this->comma_decimal = Charitable_Currency::get_instance()->is_comma_decimal();
1009	}
1010
1011	return $this->comma_decimal;
1012	}
1013
1014	/**
1015	* Sanitize amounts retrieved from the database.
1016	*
1017	* @since 1.3.0
1018	*
1019	* @param object $campaign_donation Campaign donation record.
1020	* @return object
1021	*/
1022	private function sanitize_amounts( $campaign_donation ) {
1023	$campaign_donation->amount = Charitable_Currency::get_instance()->sanitize_database_amount( $campaign_donation->amount );
1024	return $campaign_donation;
1025	}
1026
1027	/**
1028	* Sanitize amounts for grouped queries
1029	*
1030	* @since 1.6.57
1031	*
1032	* @param array $item in array is a label, item[1] is the amount. We need to sanitize the amount.
1033	* @return object
1034	*/
1035	private function sanitize_grouped_amounts( $item ) {
1036	$item[1] = Charitable_Currency::get_instance()->sanitize_database_amount( $item[1] );
1037	return $item;
1038	}
1039
1040	/**
1041	* Return a sanitized column name.
1042	*
1043	* @since 1.4.0
1044	*
1045	* @param string $field Return the column name.
1046	* @return string
1047	*/
1048	private function get_sanitized_column( $field ) {
1049	switch ( $field ) {
1050	case 'campaign':
1051	case 'campaign_id':
1052	$column = 'campaign_id';
1053	break;
1054
1055	case 'donation':
1056	case 'donation_id':
1057	$column = 'donation_id';
1058	break;
1059
1060	case 'donor':
1061	case 'donor_id':
1062	$column = 'donor_id';
1063	break;
1064
1065	default:
1066	charitable_get_deprecated()->doing_it_wrong(
1067	__METHOD__,
1068	__( 'Field expected to be `campaign`, `campaign_id`, `donation`, `donation_id`, `donor` or `donor_id`.', 'charitable' ),
1069	'1.4.0'
1070	);
1071
1072	$column = false;
1073	}
1074
1075	return $column;
1076	}
1077
1078	/**
1079	* Flush campaign caches when updating one or more rows.
1080	*
1081	* @since 1.5.11
1082	*
1083	* @param int $row_id The primary key ID for the row we are retrieving.
1084	* @param string $where Column used in where argument.
1085	* @return void
1086	*/
1087	private function flush_campaign_caches( $row_id, $where = '' ) {
1088	if ( empty( $where ) ) {
1089	$where = $this->primary_key;
1090	}
1091
1092	$campaign_ids = array_unique( $this->get_column_all_by( 'campaign_id', $where, $row_id ) );
1093
1094	foreach ( $campaign_ids as $campaign_id ) {
1095	Charitable_Campaign::flush_donations_cache( $campaign_id );
1096	}
1097	}
1098	}
1099
1100	endif;
1101