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

1,753 lines 59.4 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 the total donated on each of several donations, grouped by
366	* donation id, in a single query.
367	*
368	* The donation-side counterpart to get_donated_amounts_by_campaign().
369	* Added because no batched per-donation accessor existed: listing a page
370	* of donations and calling get_donation_total_amount() per row issues one
371	* query per donation, which is the same N+1 that had to be fixed out of
372	* the campaign list.
373	*
374	* No status clause: the donation ids are already selected by status by
375	* the caller, and a per-donation total must not be filtered by the
376	* donation's own status — the donation IS the status.
377	*
378	* The donor id comes back alongside the total, and that is the point of
379	* it rather than a convenience. Charitable_Donation::get_donor_id()
380	* resolves it through get_campaign_donations(), which issues its own
381	* `SELECT * FROM campaign_donations WHERE donation_id IN (N)` per
382	* donation — so a page of 20 donations paid 20 extra queries to learn
383	* something this one grouped query already had in hand. Measured on the
384	* development site: dropping that call took charitable/list-donations
385	* from 76 queries to 36.
386	*
387	* MIN( cd.donor_id ) rather than any aggregate cleverness: every row of
388	* a single donation carries the same donor_id, because the donor is a
389	* property of the donation and not of the split across campaigns.
390	*
391	* Ported from Charitable Pro, with one change: Pro's version reads
392	* COALESCE( cd.base_amount, cd.amount ) because Pro's multi-currency
393	* feature adds a base_amount column to this table. Lite has no
394	* base_amount column and no multi-currency feature, so this sums
395	* cd.amount directly — the same substitution every other aggregate
396	* method in this Lite file already carries (see
397	* get_donations_summary_by_day_range() below for the same rationale).
398	*
399	* @since 1.8.13
400	*
401	* @global WPDB $wpdb
402	* @param int[] $donation_ids Donation IDs.
403	* @return array Keyed by donation_id (int), each value an array with
404	* 'amount' (float) and 'donor_id' (int). A donation with no
405	* campaign_donations rows is simply absent — callers should
406	* treat a missing key as zero.
407	*/
408	public function get_donated_amounts_by_donation( $donation_ids ) {
409	global $wpdb;
410
411	list( $donations_in, $parameters ) = $this->get_in_clause_params( $donation_ids );
412
413	if ( empty( $donations_in ) \|\| empty( $parameters ) ) {
414	return array();
415	}
416
417	$sql = "SELECT cd.donation_id,
418	COALESCE( SUM( cd.amount ), 0 ) as total,
419	MIN( cd.donor_id ) as donor_id
420	FROM $this->table_name cd
421	WHERE cd.donation_id IN ( $donations_in )
422	GROUP BY cd.donation_id";
423
424	$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
425
426	$totals = array();
427
428	foreach ( $results as $row ) {
429	$totals[ (int) $row->donation_id ] = array(
430	'amount' => (float) $row->total,
431	'donor_id' => (int) $row->donor_id,
432	);
433	}
434
435	return $totals;
436	}
437
438	/**
439	* Return the per-campaign rows for MANY donations in one query.
440	*
441	* The batched counterpart to get_campaigns_for_donation() below. A
442	* listing that wants to show what each gift was split across needs this
443	* shape or it pays a query per row — the same reason
444	* get_donated_amounts_by_donation() above exists, and that method is the
445	* model for this one.
446	*
447	* Ordered by id so a split donation's campaigns come back in the order
448	* they were recorded, which is the order the donation form collected
449	* them.
450	*
451	* Ported from Charitable Pro, with one change: Pro's version reads
452	* COALESCE( cd.base_amount, cd.amount ) because Pro's multi-currency
453	* feature adds a base_amount column to this table. Lite has no
454	* base_amount column and no multi-currency feature, so this reads
455	* cd.amount directly — the same substitution get_donated_amounts_by_donation()
456	* above carries.
457	*
458	* @since 1.8.13
459	*
460	* @global wpdb $wpdb
461	* @param int[] $donation_ids The donation IDs.
462	* @return array donation_id => array of { campaign_id, campaign_name, amount }
463	*/
464	public function get_campaign_rows_by_donation( $donation_ids ) {
465	global $wpdb;
466
467	list( $donations_in, $parameters ) = $this->get_in_clause_params( $donation_ids );
468
469	if ( empty( $donations_in ) \|\| empty( $parameters ) ) {
470	return array();
471	}
472
473	$sql = "SELECT cd.donation_id,
474	cd.campaign_id,
475	cd.campaign_name,
476	cd.amount
477	FROM $this->table_name cd
478	WHERE cd.donation_id IN ( $donations_in )
479	ORDER BY cd.donation_id ASC, cd.campaign_donation_id ASC";
480
481	$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
482
483	$rows = array();
484
485	foreach ( (array) $results as $row ) {
486	$rows[ (int) $row->donation_id ][] = array(
487	'campaign_id' => (int) $row->campaign_id,
488	'campaign_name' => (string) $row->campaign_name,
489	'amount' => (float) $row->amount,
490	);
491	}
492
493	return $rows;
494	}
495
496	/**
497	* Return an array of campaigns donated to in a single donation.
498	*
499	* @since 1.2.0
500	*
501	* @global wpdb $wpdb WPDB.
502	* @param int $donation_id Donation ID.
503	* @return object
504	*/
505	public function get_campaigns_for_donation( $donation_id ) {
506	global $wpdb;
507
508	$sql = "SELECT DISTINCT campaign_id
509	FROM $this->table_name
510	WHERE donation_id = %d;";
511
512	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
513	}
514
515	/**
516	* Get an object of all donations on a campaign.
517	*
518	* @uses Charitable_Campaign_Donations_DB::get_campaign_donations_by()
519	*
520	* @since 1.0.0
521	*
522	* @param int\|int[] $campaign_id Campaign ID.
523	* @return object
524	*/
525	public function get_donations_on_campaign( $campaign_id ) {
526	return $this->get_campaign_donations_by( 'campaign_id', $campaign_id );
527	}
528
529	/**
530	* Get total amount donated to a campaign.
531	*
532	* @since 1.0.0
533	*
534	* @global wpdb $wpdb WPDB.
535	* @param int\|int[] $campaigns A campaign ID. Optionally, you can pass an array of campaign IDs to get the total of all put together.
536	* @param boolean $include_all Whether donations with non-approved statuses should be included.
537	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
538	* @return string
539	*/
540	public function get_campaign_donated_amount( $campaigns, $include_all = false, $sanitize = true ) {
541	return $this->get_amount_report(
542	array(
543	'status' => $include_all ? array() : charitable_get_approval_statuses(),
544	'campaigns' => $campaigns,
545	),
546	$sanitize
547	);
548	}
549
550	/**
551	* Return the total amount donated to each of several campaigns, grouped
552	* by campaign id, in a single query.
553	*
554	* Exists so a caller iterating N campaigns (e.g. the charitable/list-campaigns
555	* ability) can resolve every row's donated amount in one query instead of
556	* N calls to get_campaign_donated_amount() / Charitable_Campaign::get_donated_amount() —
557	* one query per campaign multiplied by a page size is the exact N+1 shape
558	* that made the long-range Reports Overview timeout ~18,000 queries before
559	* it was batched the same way.
560	*
561	* Ported from Charitable Pro, with one change: Pro's version reads
562	* COALESCE( cd.base_amount, cd.amount ) because Pro's multi-currency
563	* feature adds a base_amount column to this table. Lite has no
564	* base_amount column and no multi-currency feature, so this reads
565	* cd.amount directly — the same column every other aggregate method in
566	* this Lite file already uses (see get_amount_by_campaign_report()).
567	*
568	* Deliberately mirrors get_campaign_donated_amount( $campaigns, false, false )'s
569	* raw, un-locale-converted output (no sanitize_database_amount() pass) so a
570	* batched row and a get_campaign_donated_amount() call for the same campaign
571	* return numerically identical values — callers that need the site's
572	* comma/dot display convention should convert the same way that call site
573	* already does.
574	*
575	* This is a raw aggregate accessor: it does NOT apply the
576	* `charitable_campaign_donated_amount` filter that
577	* Charitable_Campaign::get_donated_amount() applies on every call. A
578	* caller that wants a figure consistent with the rest of the product —
579	* not just a faster one — must apply that filter itself, per
580	* campaign_id, with the same arguments get_donated_amount() uses:
581	* `apply_filters( 'charitable_campaign_donated_amount', $amount,
582	* $campaign, $sanitize )`. See
583	* Charitable_Campaign_Abilities::campaign_summary() for a worked example.
584	*
585	* @since 1.8.13
586	*
587	* @global WPDB $wpdb
588	* @param int[] $campaign_ids Campaign IDs.
589	* @param boolean $include_all Whether to include all donations (true), or only approved (false).
590	* @return array Keyed by campaign_id (int), each value a float total. A
591	* campaign with no matching donations is simply absent from
592	* the array — callers should treat a missing key as 0.
593	*/
594	public function get_donated_amounts_by_campaign( $campaign_ids, $include_all = false ) {
595	global $wpdb;
596
597	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign_ids );
598
599	if ( empty( $campaigns_in ) \|\| empty( $campaigns_parameters ) ) {
600	return array();
601	}
602
603	$statuses = $include_all ? array() : charitable_get_approval_statuses();
604
605	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
606
607	$sql_where_clauses = array( "cd.campaign_id IN ( $campaigns_in )" );
608
609	if ( ! empty( $status_clause ) ) {
610	$sql_where_clauses[] = $status_clause;
611	}
612
613	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
614	$parameters = array_merge( $campaigns_parameters, $status_parameters );
615
616	$sql = "SELECT cd.campaign_id, COALESCE( SUM( cd.amount ), 0 ) as total
617	FROM $this->table_name cd
618	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
619	$sql_where
620	GROUP BY cd.campaign_id";
621
622	$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
623
624	$totals = array();
625
626	foreach ( $results as $row ) {
627	$totals[ (int) $row->campaign_id ] = (float) $row->total;
628	}
629
630	return $totals;
631	}
632
633	/**
634	* Return the number of distinct donors for each of several campaigns,
635	* grouped by campaign id, in a single query.
636	*
637	* Same rationale as get_donated_amounts_by_campaign(): count_campaign_donors()
638	* has no caching layer at all (unlike the donated-amount path, which is at
639	* least transient-cached), so calling it once per row in a list is the
640	* more expensive half of that ability's N+1.
641	*
642	* Ported from Charitable Pro unchanged: this query never touches
643	* base_amount, so Lite's lack of that column does not affect it.
644	*
645	* This is a raw aggregate accessor: it does NOT apply the
646	* `charitable_campaign_donor_count` filter that
647	* Charitable_Campaign::get_donor_count() applies on every call. A caller
648	* that wants a figure consistent with the rest of the product must apply
649	* that filter itself, per campaign_id: `apply_filters(
650	* 'charitable_campaign_donor_count', $count, $campaign )`. See
651	* Charitable_Campaign_Abilities::campaign_summary() for a worked example.
652	*
653	* @since 1.8.13
654	*
655	* @global WPDB $wpdb
656	* @param int[] $campaign_ids Campaign IDs.
657	* @param boolean $include_all Whether to include all donations (true), or only approved (false).
658	* @return array Keyed by campaign_id (int), each value an int count. A
659	* campaign with no matching donors is simply absent from the
660	* array — callers should treat a missing key as 0.
661	*/
662	public function count_campaign_donors_by_campaign( $campaign_ids, $include_all = false ) {
663	global $wpdb;
664
665	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign_ids );
666
667	if ( empty( $campaigns_in ) \|\| empty( $campaigns_parameters ) ) {
668	return array();
669	}
670
671	$statuses = $include_all ? array() : charitable_get_approval_statuses();
672
673	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
674
675	$sql_where_clauses = array( "cd.campaign_id IN ( $campaigns_in )" );
676
677	if ( ! empty( $status_clause ) ) {
678	$sql_where_clauses[] = $status_clause;
679	}
680
681	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
682	$parameters = array_merge( $campaigns_parameters, $status_parameters );
683
684	$sql = "SELECT cd.campaign_id, COUNT( DISTINCT cd.donor_id ) as donor_count
685	FROM $this->table_name cd
686	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
687	$sql_where
688	GROUP BY cd.campaign_id";
689
690	$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
691
692	$counts = array();
693
694	foreach ( $results as $row ) {
695	$counts[ (int) $row->campaign_id ] = (int) $row->donor_count;
696	}
697
698	return $counts;
699	}
700
701	/**
702	* Get an array of all donation ids for a campaign.
703	*
704	* @uses Charitable_Campaign_Donations_DB::get_distinct_ids()
705	*
706	* @since 1.0.0
707	*
708	* @param int $campaign_id Campaign ID.
709	* @return object
710	*/
711	public function get_donation_ids_for_campaign( $campaign_id ) {
712	return $this->get_distinct_ids( 'donation_id', $campaign_id, 'campaign_id' );
713	}
714
715	/**
716	* The donor IDs of all who have donated to the given campaign.
717	*
718	* @uses Charitable_Campaign_Donations_DB::get_distinct_ids()
719	*
720	* @since 1.0.0
721	*
722	* @param int $campaign_id The campaign ID to get donors for.
723	* @return object
724	*/
725	public function get_campaign_donors( $campaign_id ) {
726	return $this->get_distinct_ids( 'donor_id', $campaign_id, 'campaign_id' );
727	}
728
729	/**
730	* Return the number of users who have donated to the given campaign.
731	*
732	* @since 1.0.0
733	*
734	* @global wpdb $wpdb WPDB.
735	* @param int\|int[] $campaign The campaign ID, or list of campaign IDs.
736	* @param boolean $include_all Whether to include all donations (true), or only include approved donations (false).
737	* @return int
738	*/
739	public function count_campaign_donors( $campaign, $include_all = false ) {
740	global $wpdb;
741
742	$statuses = $include_all ? array() : charitable_get_approval_statuses();
743
744	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
745	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign );
746
747	$sql_where_clauses[] = "cd.campaign_id IN ( $campaigns_in )";
748	$sql_where_clauses[] = $status_clause;
749	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
750
751	$parameters = array_merge( $campaigns_parameters, $status_parameters );
752
753	$sql = "SELECT COUNT( DISTINCT cd.donor_id )
754	FROM $this->table_name cd
755	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
756	$sql_where;";
757
758	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
759	}
760
761	/**
762	* Count distinct donations (transactions) for a campaign.
763	*
764	* Ported from Charitable Pro unchanged: a pure COUNT query, it never
765	* touches base_amount, so Lite's lack of that column does not affect it.
766	* Backs Charitable_Campaign::get_donation_count(), which the
767	* charitable/get-campaign-performance ability calls.
768	*
769	* @since 1.8.13
770	*
771	* @global wpdb $wpdb
772	* @param int\|int[] $campaign The campaign ID, or list of campaign IDs.
773	* @param boolean $include_all Whether to include all donations (true), or only approved (false).
774	* @return int
775	*/
776	public function count_campaign_donations( $campaign, $include_all = false ) {
777	global $wpdb;
778
779	$statuses = $include_all ? array() : charitable_get_approval_statuses();
780
781	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
782	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $campaign );
783
784	$sql_where_clauses = array();
785
786	if ( ! empty( $campaigns_in ) && ! empty( $campaigns_parameters ) ) {
787	$sql_where_clauses[] = "cd.campaign_id IN ( $campaigns_in )";
788	}
789
790	if ( ! empty( $status_clause ) ) {
791	$sql_where_clauses[] = $status_clause;
792	}
793
794	if ( empty( $sql_where_clauses ) ) {
795	return 0;
796	}
797
798	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
799	$parameters = array_merge( $campaigns_parameters, $status_parameters );
800
801	$sql = "SELECT COUNT( DISTINCT cd.donation_id )
802	FROM $this->table_name cd
803	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
804	$sql_where;";
805
806	return (int) $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
807	}
808
809	/**
810	* Return all donations made by a donor.
811	*
812	* @since 1.0.0
813	*
814	* @global wpdb $wpdb WPDB.
815	* @param int $donor_id The donor ID.
816	* @param boolean $distinct_donations Whether to get distinct donations.
817	* @return object[]
818	*/
819	public function get_donations_by_donor( $donor_id, $distinct_donations = false ) {
820	global $wpdb;
821
822	if ( $distinct_donations ) {
823	$select_fields = 'DISTINCT( cd.donation_id ), cd.campaign_id, cd.campaign_name, cd.amount';
824	} else {
825	$select_fields = 'cd.campaign_donation_id, cd.donation_id, cd.campaign_id, cd.campaign_name, cd.amount';
826	}
827
828	$sql = "SELECT $select_fields
829	FROM $this->table_name cd
830	WHERE cd.donor_id = %d;";
831
832	$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
833
834	if ( $this->is_comma_decimal() ) {
835	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
836	}
837
838	return $results;
839	}
840
841	/**
842	* Return total amount donated by a donor.
843	*
844	* @since 1.0.0
845	*
846	* @global WPDB $wpdb
847	* @param int $donor_id The donor ID.
848	* @return int
849	*/
850	public function get_total_donated_by_donor( $donor_id ) {
851	global $wpdb;
852
853	$sql = "SELECT COALESCE( SUM(cd.amount), 0 )
854	FROM $this->table_name cd
855	WHERE cd.donor_id = %d;";
856
857	$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
858
859	if ( $this->is_comma_decimal() ) {
860	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
861	}
862
863	return $total;
864	}
865
866	/**
867	* Count the number of donations made by the donor.
868	*
869	* @since 1.0.0
870	*
871	* @global wpdb $wpdb WPDB.
872	* @param int $donor_id The donor ID.
873	* @param boolean $distinct_donations If true, will only count unique donations.
874	* @return int
875	*/
876	public function count_donations_by_donor( $donor_id, $distinct_donations = false ) {
877	global $wpdb;
878
879	$count = $distinct_donations ? 'DISTINCT donation_id' : 'donation_id';
880
881	$sql = "SELECT COUNT( $count )
882	FROM $this->table_name
883	WHERE donor_id = %d;";
884
885	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
886	}
887
888	/**
889	* Count the number of campaigns that the donor has supported.
890	*
891	* @since 1.0.0
892	*
893	* @global wpdb $wpdb WPDB.
894	* @param int $donor_id The donor to retrieve campaigns for.
895	* @return int
896	*/
897	public function count_campaigns_supported_by_donor( $donor_id ) {
898	global $wpdb;
899
900	$sql = "SELECT COUNT( DISTINCT campaign_id )
901	FROM $this->table_name
902	WHERE donor_id = %d;";
903
904	return $wpdb->get_var( $wpdb->prepare( $sql, $donor_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
905	}
906
907	/**
908	* Return a set of donations, filtered by the provided arguments.
909	*
910	* @since 1.0.0
911	* @since 1.8.1.8 Added filter.
912	*
913	* @global WPDB $wpdb
914	* @param array $args Query arguments.
915	* @return array
916	*/
917	public function get_donations_report( $args ) {
918	global $wpdb;
919
920	$sql_order = $this->get_orderby_clause( $args, 'ORDER BY p.post_date ASC' );
921	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
922	$limit = $this->get_limit_clause( $args );
923
924	/* This is our base SQL query */
925	$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
926	FROM $this->table_name cd
927	INNER JOIN {$wpdb->prefix}charitable_donors d
928	ON d.donor_id = cd.donor_id
929	INNER JOIN $wpdb->posts p
930	ON p.ID = cd.donation_id
931	$sql_where
932	$sql_order
933	$limit";
934
935	$sql = apply_filters( 'charitable_campaign_donations_db_sql', $sql, $args, $sql_where, $sql_order, $parameters );
936
937	if ( ! empty( $parameters ) ) {
938	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
939	}
940
941	$results = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
942
943	if ( $this->is_comma_decimal() ) {
944	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
945	}
946
947	return $results;
948	}
949
950	/**
951	* Return a total amount donated
952	*
953	* @since 1.6.57
954	*
955	* @global WPDB $wpdb
956	* @param array $args Query arguments.
957	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
958	* @return int The total amount
959	*/
960	public function get_amount_report( $args, $sanitize = true ) {
961	global $wpdb;
962
963	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
964
965	/* Get the total */
966	$sql = "SELECT COALESCE( SUM(amount), 0 )
967	FROM $this->table_name cd
968	INNER JOIN $wpdb->posts p
969	ON p.ID = cd.donation_id
970	$sql_where";
971
972	if ( ! empty( $parameters ) ) {
973	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
974	}
975
976	$total = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
977
978	if ( $this->is_comma_decimal() && $sanitize ) {
979	$total = Charitable_Currency::get_instance()->sanitize_database_amount( $total );
980	}
981
982	return $total;
983	}
984
985	/**
986	* Return a total amount donated, grouped by campaign.
987	* Created for Charitable-Stats-Shortcode.
988	*
989	* @since 1.6.57
990	*
991	* @global WPDB $wpdb
992	*
993	* @param array $args Query arguments.
994	* @param boolean $sanitize Whether to sanitize the amount if we're using commas for decimals.
995	* @return array An array of amounts and campaign names
996	*/
997	public function get_amount_by_campaign_report( $args, $sanitize = true ) {
998	global $wpdb;
999
1000	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1001
1002	/* Get the total */
1003	$sql = "SELECT cd.campaign_name, COALESCE( SUM(amount), 0 ) as total
1004	FROM $this->table_name cd
1005	INNER JOIN $wpdb->posts p
1006	ON p.ID = cd.donation_id
1007	$sql_where
1008	GROUP BY cd.campaign_id, cd.campaign_name";
1009
1010	if ( ! empty( $parameters ) ) {
1011	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
1012	}
1013
1014	$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
1015
1016	if ( $this->is_comma_decimal() && $sanitize ) {
1017	$results = array_map( array( $this, 'sanitize_grouped_amounts' ), $results );
1018	}
1019
1020	return $results;
1021	}
1022
1023	/**
1024	* Count the distinct donations matching a report query.
1025	*
1026	* Counts DISTINCT donation_id, so a donation split across three
1027	* campaigns counts once rather than three times — unlike
1028	* count_donations_by_status(), which counts campaign_donations ROWS and
1029	* cannot be date-ranged.
1030	*
1031	* Exists because the alternative is loading every matching donation id
1032	* into PHP just to call count() on it. Filtering reuses
1033	* get_report_sql_where_clause(), so campaigns, status and the date range
1034	* behave exactly as they do for get_amount_report() — which means a
1035	* total and a count taken from the same $args are always coherent, and
1036	* average = total / count cannot disagree with itself.
1037	*
1038	* THE INNER JOIN IS DELIBERATE, and it is a behaviour decision worth
1039	* stating. A donation POST with no campaign_donations row is not
1040	* counted, because such a record carries no amount and so contributes
1041	* nothing to any money figure. Counting it would report more donations
1042	* than the money accounts for: on the development database, 18 donation
1043	* posts (abandoned importer and gateway-test records) have no rows at
1044	* all, and counting donation posts against a campaign_donations total
1045	* produced an all-time average gift of 9.49 when the real average of
1046	* the donations that carry money is 14.02 — an average no actual
1047	* donation resembles. Taking the count from the same table as the total
1048	* is what makes the reported average true.
1049	*
1050	* @since 1.8.13
1051	*
1052	* @global WPDB $wpdb
1053	* @param array $args Query arguments: campaigns, status, start_date, end_date, donor_id.
1054	* @return int
1055	*/
1056	public function count_donations_report( $args = array() ) {
1057	global $wpdb;
1058
1059	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1060
1061	$sql = "SELECT COUNT( DISTINCT cd.donation_id )
1062	FROM $this->table_name cd
1063	INNER JOIN $wpdb->posts p
1064	ON p.ID = cd.donation_id
1065	$sql_where";
1066
1067	if ( ! empty( $parameters ) ) {
1068	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore
1069	}
1070
1071	return (int) $wpdb->get_var( $sql ); // phpcs:ignore
1072	}
1073
1074	/**
1075	* Count the distinct donors matching a report query.
1076	*
1077	* A donor_id of 0 is excluded: a row can carry it when the donation was
1078	* recorded without a resolvable donor, and counting those as one donor
1079	* would understate a site's supporter count while counting them
1080	* individually would overstate it. Excluding is the only honest option.
1081	*
1082	* @since 1.8.13
1083	*
1084	* @global WPDB $wpdb
1085	* @param array $args Query arguments: campaigns, status, start_date, end_date.
1086	* @return int
1087	*/
1088	public function count_donors_report( $args = array() ) {
1089	global $wpdb;
1090
1091	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1092
1093	$sql_where = '' !== $sql_where
1094	? $sql_where . ' AND cd.donor_id != 0'
1095	: 'WHERE cd.donor_id != 0';
1096
1097	$sql = "SELECT COUNT( DISTINCT cd.donor_id )
1098	FROM $this->table_name cd
1099	INNER JOIN $wpdb->posts p
1100	ON p.ID = cd.donation_id
1101	$sql_where";
1102
1103	if ( ! empty( $parameters ) ) {
1104	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore
1105	}
1106
1107	return (int) $wpdb->get_var( $sql ); // phpcs:ignore
1108	}
1109
1110	/**
1111	* Count the distinct donations in a report query, grouped by status.
1112	*
1113	* ONE query for every status, rather than one query per status. Pass no
1114	* `status` argument: get_report_sql_where_clause() then constrains to
1115	* all valid donation statuses, which is exactly the set to group over.
1116	*
1117	* Ported from Charitable Pro unchanged: this query never touches
1118	* base_amount, so Lite's lack of that column does not affect it.
1119	*
1120	* @since 1.8.13
1121	*
1122	* @global WPDB $wpdb
1123	* @param array $args Query arguments: campaigns, start_date, end_date, donor_id.
1124	* @return array Keyed by post status, each value an int count. A status with
1125	* no donations is absent — callers should treat a missing key
1126	* as 0.
1127	*/
1128	public function count_donations_by_status_report( $args = array() ) {
1129	global $wpdb;
1130
1131	unset( $args['status'] );
1132
1133	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1134
1135	$sql = "SELECT p.post_status, COUNT( DISTINCT cd.donation_id ) as donation_count
1136	FROM $this->table_name cd
1137	INNER JOIN $wpdb->posts p
1138	ON p.ID = cd.donation_id
1139	$sql_where
1140	GROUP BY p.post_status";
1141
1142	if ( ! empty( $parameters ) ) {
1143	$sql = $wpdb->prepare( $sql, $parameters ); // phpcs:ignore
1144	}
1145
1146	$results = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
1147
1148	$counts = array();
1149
1150	foreach ( (array) $results as $row ) {
1151	$counts[ (string) $row->post_status ] = (int) $row->donation_count;
1152	}
1153
1154	return $counts;
1155	}
1156
1157	/**
1158	* Return campaigns ranked by amount raised over a date range.
1159	*
1160	* The get_amount_by_campaign_report() accessor is the closest existing one
1161	* and is correctly converted, but it selects `campaign_name` WITHOUT
1162	* campaign_id and applies no ordering or limit. A caller that needs to
1163	* link to the campaign, or that wants the top N, therefore cannot use
1164	* it: names are not unique, and they drift when a campaign is renamed
1165	* after the donation was recorded.
1166	*
1167	* Filtering reuses get_report_sql_where_clause(), so campaigns, status,
1168	* start_date, end_date and donor_id behave exactly as they do for every
1169	* other report query, including its default of all valid donation
1170	* statuses when none is given.
1171	*
1172	* Sums cd.amount rather than Pro's COALESCE( cd.base_amount, cd.amount ):
1173	* base_amount belongs to the multi-currency schema and does not exist on
1174	* this table in Lite (see get_donations_summary_by_day_range() below for
1175	* the same substitution and rationale).
1176	*
1177	* @since 1.8.13
1178	*
1179	* @global WPDB $wpdb
1180	* @param array $args Query arguments: campaigns, status, start_date,
1181	* end_date, donor_id, limit.
1182	* @return object[] Rows of campaign_id, campaign_name, total, donation_count.
1183	*/
1184	public function get_top_campaigns_report( $args = array() ) {
1185	global $wpdb;
1186
1187	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1188
1189	$limit = isset( $args['limit'] ) ? absint( $args['limit'] ) : 10;
1190	$limit = $limit > 0 ? $limit : 10;
1191
1192	$sql = "SELECT cd.campaign_id,
1193	cd.campaign_name,
1194	COALESCE( SUM( cd.amount ), 0 ) as total,
1195	COUNT( DISTINCT cd.donation_id ) as donation_count
1196	FROM $this->table_name cd
1197	INNER JOIN $wpdb->posts p
1198	ON p.ID = cd.donation_id
1199	$sql_where
1200	GROUP BY cd.campaign_id, cd.campaign_name
1201	ORDER BY total DESC
1202	LIMIT %d";
1203
1204	$parameters[] = $limit;
1205
1206	$results = $wpdb->get_results( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore
1207
1208	if ( $this->is_comma_decimal() ) {
1209	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
1210	}
1211
1212	return $results;
1213	}
1214
1215	/**
1216	* Return donors ranked by amount given over a date range.
1217	*
1218	* Charitable_Reports::get_top_donors_overview() is the existing
1219	* equivalent, and is not usable here: it requires the whole donation set
1220	* to have been loaded into PHP first via init_with_array() plus
1221	* get_donations(), sorts it in PHP, and calls charitable_get_donation()
1222	* once per row. This is one grouped query.
1223	*
1224	* Ranked on cd.amount rather than Pro's COALESCE( base_amount, amount ):
1225	* base_amount belongs to the multi-currency schema and does not exist on
1226	* this table in Lite (see get_donations_summary_by_day_range() below for
1227	* the same substitution and rationale).
1228	*
1229	* @since 1.8.13
1230	*
1231	* @global WPDB $wpdb
1232	* @param array $args Query arguments: campaigns, status, start_date,
1233	* end_date, limit.
1234	* @return object[] Rows of donor_id, first_name, last_name, email, total,
1235	* donation_count.
1236	*/
1237	public function get_top_donors_report( $args = array() ) {
1238	global $wpdb;
1239
1240	list( $sql_where, $parameters ) = $this->get_report_sql_where_clause( $args );
1241
1242	$limit = isset( $args['limit'] ) ? absint( $args['limit'] ) : 10;
1243	$limit = $limit > 0 ? $limit : 10;
1244
1245	/*
1246	* donor_id 0 must not be grouped: a campaign_donations row can carry
1247	* it when the donation was recorded without a resolvable donor, and
1248	* grouping those would present the sum of several unrelated gifts as
1249	* one large anonymous top donor.
1250	*
1251	* The INNER JOIN on the donors table below is what actually excludes
1252	* them, because no donors row has donor_id 0 — the column is
1253	* AUTO_INCREMENT and starts at 1. This clause is therefore SECONDARY
1254	* and, on any normal install, redundant. It is kept because it states
1255	* the intent at the point the grouping happens, so a future change
1256	* from INNER to LEFT JOIN (to include gifts whose donor record
1257	* was erased, say) does not silently reintroduce the anonymous-group
1258	* bug.
1259	*
1260	* Honest note on coverage: this clause is NOT independently testable.
1261	* Removing it leaves every test green, because the join already
1262	* covers the case, and a donors row with donor_id 0 cannot be created
1263	* to exercise it — MySQL treats an inserted 0 in an AUTO_INCREMENT
1264	* column as a request for the next value unless NO_AUTO_VALUE_ON_ZERO
1265	* is set on the session. Verified by mutation rather than assumed.
1266	*/
1267	$sql_where = '' !== $sql_where
1268	? $sql_where . ' AND cd.donor_id != 0'
1269	: 'WHERE cd.donor_id != 0';
1270
1271	$sql = "SELECT cd.donor_id,
1272	d.first_name,
1273	d.last_name,
1274	d.email,
1275	COALESCE( SUM( cd.amount ), 0 ) as total,
1276	COUNT( DISTINCT cd.donation_id ) as donation_count
1277	FROM $this->table_name cd
1278	INNER JOIN $wpdb->posts p
1279	ON p.ID = cd.donation_id
1280	INNER JOIN {$wpdb->prefix}charitable_donors d
1281	ON d.donor_id = cd.donor_id
1282	$sql_where
1283	GROUP BY cd.donor_id, d.first_name, d.last_name, d.email
1284	ORDER BY total DESC
1285	LIMIT %d";
1286
1287	$parameters[] = $limit;
1288
1289	$results = $wpdb->get_results( $wpdb->prepare( $sql, $parameters ) ); // phpcs:ignore
1290
1291	if ( $this->is_comma_decimal() ) {
1292	$results = array_map( array( $this, 'sanitize_amounts' ), $results );
1293	}
1294
1295	return $results;
1296	}
1297
1298	/**
1299	* Return an SQL limit for the report
1300	*
1301	* @since 1.8.1.10
1302	*
1303	* @param array $args Query arguments.
1304	* @return array
1305	*/
1306	public function get_limit_clause( $args ) {
1307	$limit = '';
1308
1309	if ( isset( $args['limit'] ) ) {
1310	$limit = 'LIMIT ' . $args['limit'];
1311	}
1312
1313	return $limit;
1314	}
1315
1316	/**
1317	* Return an SQL where clause for the report
1318	*
1319	* @since 1.6.57
1320	*
1321	* @param array $args Query arguments.
1322	* @return array
1323	*/
1324	public function get_report_sql_where_clause( $args ) {
1325	$parameters = array();
1326	$sql_where = '';
1327	$sql_where_clauses = array();
1328
1329	/* campaign_id should become 'campaigns'. */
1330	if ( isset( $args['campaign_id'] ) ) {
1331	$args['campaigns'] = $args['campaign_id'];
1332	}
1333
1334	if ( isset( $args['campaigns'] ) ) {
1335	list( $campaigns_in, $campaigns_parameters ) = $this->get_in_clause_params( $args['campaigns'] );
1336
1337	$sql_where_clauses[] = "cd.campaign_id IN ( $campaigns_in )";
1338	$parameters = array_merge( $parameters, $campaigns_parameters );
1339	}
1340
1341	if ( isset( $args['status'] ) ) {
1342	if ( ! is_array( $args['status'] ) ) {
1343	/* Ensure this is an array. */
1344	$args['status'] = array( $args['status'] );
1345	}
1346
1347	list( $status_sql, $status_parameters ) = $this->get_donation_status_clause( $args['status'] );
1348	$sql_where_clauses[] = $status_sql;
1349	$parameters = array_merge( $parameters, $status_parameters );
1350
1351	} else {
1352	/* If ALL: select all valid statuses */
1353	$statuses = array_keys( charitable_get_valid_donation_statuses() );
1354	$in = charitable_get_query_placeholders( count( $statuses ), '%s' );
1355	$sql_where_clauses[] = "p.post_status IN ( $in )";
1356	$parameters = array_merge( $parameters, $statuses );
1357	}
1358
1359	if ( ! empty( $args['start_date'] ) ) {
1360	$sql_where_clauses[] = 'p.post_date >= %s';
1361	$parameters[] = $args['start_date'];
1362	}
1363
1364	if ( ! empty( $args['end_date'] ) ) {
1365	$sql_where_clauses[] = 'p.post_date <= %s';
1366	$parameters[] = $args['end_date'];
1367	}
1368
1369	// Added in 1.8.1.8.
1370	if ( isset( $args['post_parent'] ) ) {
1371	$sql_where_clauses[] = 'p.post_parent = %d';
1372	$parameters[] = $args['post_parent'];
1373	}
1374
1375	/*
1376	* `author` narrows to one donation author, which for a donation is
1377	* the donor's WordPress user id
1378	* (Charitable_Donation_Processor::parse_donation_data() sets
1379	* post_author from `user_id`). Added in 1.8.13 for the ownership
1380	* scoping on charitable/get-donation-summary: a report caller without
1381	* `edit_others_donations` must see only their own donations, which is
1382	* the rule wp-admin's donation list already applies
1383	* (class-charitable-donation-list-table.php:783) via WP_Query's own
1384	* `author` argument. The report accessors do not go through WP_Query,
1385	* so the equivalent has to exist here.
1386	*
1387	* isset(), not ! empty(): author 0 is a real value — guest and
1388	* manually recorded donations carry post_author 0 — so treating it as
1389	* "no filter" would be fail-OPEN, returning every donation to a
1390	* caller who asked to be scoped. No caller passes 0 today, because
1391	* the only one that sets this key is gated behind `edit_donations`
1392	* and so is always a logged-in user; isset() is what keeps that true
1393	* if a future caller is not.
1394	*
1395	* @since 1.8.13
1396	*/
1397	if ( isset( $args['author'] ) ) {
1398	$sql_where_clauses[] = 'p.post_author = %d';
1399	$parameters[] = (int) $args['author'];
1400	}
1401
1402	if ( ! empty( $sql_where_clauses ) ) {
1403	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
1404	}
1405
1406	return array( $sql_where, $parameters );
1407	}
1408
1409	/**
1410	* Return a count and sum of donations for a given period.
1411	*
1412	* @since 1.2.0
1413	*
1414	* @global WPDB $wpdb
1415	* @param string $period The period to get donations for.
1416	* @param string[] $statuses List of statuses.
1417	* @return array
1418	*/
1419	public function get_donations_summary_by_period( $period = '', $statuses = array() ) {
1420	global $wpdb;
1421
1422	if ( empty( $statuses ) ) {
1423	$statuses = charitable_get_approval_statuses();
1424	}
1425
1426	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
1427
1428	array_unshift( $parameters, $period );
1429
1430	$sql_where_clauses[] = "DATE_FORMAT( p.post_date, '%%Y-%%m-%%d' ) LIKE %s";
1431	$sql_where_clauses[] = $status_clause;
1432	$sql_where = 'WHERE ' . implode( ' AND ', $sql_where_clauses );
1433
1434	$sql = "SELECT COALESCE( SUM( cd.amount ), 0 ) as amount, COUNT( cd.donation_id ) as count
1435	FROM {$wpdb->prefix}charitable_campaign_donations cd
1436	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
1437	$sql_where";
1438
1439	$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
1440
1441	if ( empty( $results ) ) {
1442	return (object) array(
1443	'amount' => 0.0000,
1444	'count' => 0,
1445	);
1446	}
1447
1448	$result = $results[0];
1449
1450	if ( $this->is_comma_decimal() ) {
1451	$result = $this->sanitize_amounts( $result );
1452	}
1453
1454	return $result;
1455	}
1456
1457	/**
1458	* Return a count and sum of donations for every day in a date range, keyed by day.
1459	*
1460	* Callers that need a per-day series must use this instead of calling
1461	* get_donations_summary_by_period() once per calendar day. That pattern issued one
1462	* uncached query per day, so the query count scaled with the LENGTH of the range
1463	* rather than the amount of data, and a multi-year range timed out. It also wrapped
1464	* post_date in DATE_FORMAT(), which prevented any index being used, making every
1465	* one of those queries a full scan of the join.
1466	*
1467	* Days with no donations are absent from the return value; callers should treat a
1468	* missing key as zero.
1469	*
1470	* @since 1.8.12.2
1471	* @since 1.8.13 Added $campaign_id parameter.
1472	*
1473	* @global WPDB $wpdb
1474	* @param string $start_date Start of the range, Y-m-d. Inclusive.
1475	* @param string $end_date End of the range, Y-m-d. Inclusive of the whole day.
1476	* @param string[] $statuses List of statuses.
1477	* @param int $campaign_id Optional. Restrict to donations that include this
1478	* campaign. 0, the default, means every campaign —
1479	* which is what existing callers want.
1480	* @return array Keyed by Y-m-d, each value an object with 'amount' and 'count'.
1481	*/
1482	public function get_donations_summary_by_day_range( $start_date, $end_date, $statuses = array(), $campaign_id = 0 ) {
1483	global $wpdb;
1484
1485	if ( empty( $start_date ) \|\| empty( $end_date ) ) {
1486	return array();
1487	}
1488
1489	if ( empty( $statuses ) ) {
1490	$statuses = charitable_get_approval_statuses();
1491	}
1492
1493	list( $status_clause, $status_parameters ) = $this->get_donation_status_clause( $statuses );
1494
1495	// Every status was filtered out as invalid, so nothing could match. Without
1496	// this the IN clause would be emitted empty and the query would error.
1497	if ( empty( $status_parameters ) ) {
1498	return array();
1499	}
1500
1501	$parameters = array_merge(
1502	array( $start_date . ' 00:00:00', $end_date . ' 23:59:59' ),
1503	$status_parameters
1504	);
1505
1506	/*
1507	* Appended AFTER the status clause, so the extra parameter lands last
1508	* in $parameters and the placeholder order still lines up. Filtering
1509	* on cd.campaign_id rather than joining anything: a donation split
1510	* across campaigns has one row per campaign in this table, and the
1511	* per-campaign row is exactly what a per-campaign figure means.
1512	*/
1513	$campaign_clause = '';
1514	$campaign_id = (int) $campaign_id;
1515
1516	if ( $campaign_id > 0 ) {
1517	$campaign_clause = ' AND cd.campaign_id = %d';
1518	$parameters[] = $campaign_id;
1519	}
1520
1521	// Sums cd.amount, matching get_donations_summary_by_period() above. The
1522	// base_amount column belongs to the multi-currency schema and does not exist
1523	// on this table in Lite, so it must not appear here.
1524	$sql = "SELECT DATE( p.post_date ) as day,
1525	COALESCE( SUM( cd.amount ), 0 ) as amount,
1526	COUNT( cd.donation_id ) as count
1527	FROM {$wpdb->prefix}charitable_campaign_donations cd
1528	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
1529	WHERE p.post_date >= %s AND p.post_date <= %s AND {$status_clause}{$campaign_clause}
1530	GROUP BY DATE( p.post_date )";
1531
1532	$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
1533
1534	if ( empty( $results ) ) {
1535	return array();
1536	}
1537
1538	$comma_decimal = $this->is_comma_decimal();
1539	$summary = array();
1540
1541	foreach ( $results as $result ) {
1542	if ( $comma_decimal ) {
1543	$result = $this->sanitize_amounts( $result );
1544	}
1545
1546	$summary[ $result->day ] = $result;
1547	}
1548
1549	return $summary;
1550	}
1551
1552	/**
1553	* Returns the orderby clause.
1554	*
1555	* @since 1.3.4
1556	*
1557	* @param array $args Query arguments.
1558	* @param string $default Default.
1559	* @return string
1560	*/
1561	public function get_orderby_clause( $args, $default = '' ) { // phpcs:ignore
1562	if ( ! isset( $args['orderby'] ) && ! isset( $args['order'] ) ) {
1563	return $default;
1564	}
1565
1566	$orderby = isset( $args['orderby'] ) ? $args['orderby'] : 'ID';
1567	$order = isset( $args['order'] ) && in_array( $args['order'], array( 'ASC', 'DESC' ) ) ? $args['order'] : 'ASC'; // phpcs:ignore
1568
1569	switch ( $orderby ) {
1570	case 'date':
1571	$ret = 'ORDER BY p.post_date ';
1572	break;
1573
1574	default:
1575	$ret = 'ORDER BY cd.campaign_donation_id ';
1576	}
1577
1578	$ret .= $order;
1579
1580	return $ret;
1581	}
1582
1583	/**
1584	* Count donations by status.
1585	*
1586	* @since 1.0.0
1587	*
1588	* @param string\|string[] $statuses List of statuses.
1589	* @return int
1590	*/
1591	public function count_donations_by_status( $statuses ) {
1592	global $wpdb;
1593
1594	if ( ! is_array( $statuses ) ) {
1595	$statuses = array( $statuses );
1596	}
1597
1598	list( $status_clause, $parameters ) = $this->get_donation_status_clause( $statuses );
1599	if ( ! empty( $status_clause ) ) {
1600	$sql_where = 'WHERE ' . $status_clause;
1601	}
1602
1603	$sql = "SELECT COUNT( * )
1604	FROM {$wpdb->prefix}charitable_campaign_donations cd
1605	INNER JOIN $wpdb->posts p ON p.ID = cd.donation_id
1606	$sql_where;";
1607
1608	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
1609	}
1610
1611	/**
1612	* Returns the donation status clause.
1613	*
1614	* @since 1.0.0
1615	*
1616	* @param string[] $statuses List of statuses.
1617	* @return array
1618	*/
1619	private function get_donation_status_clause( $statuses = array() ) {
1620	if ( empty( $statuses ) ) {
1621	return array( '', array() );
1622	}
1623
1624	$statuses = array_filter( $statuses, 'charitable_is_valid_donation_status' );
1625	$in = charitable_get_query_placeholders( count( $statuses ), '%s' );
1626	$sql = "p.post_status IN ( $in )";
1627
1628	return array( $sql, $statuses );
1629	}
1630
1631	/**
1632	* Returns an array containing the placeholders and sanitized parameters for an IN clause.
1633	*
1634	* @since 1.4.0
1635	*
1636	* @param int\|int[] $list List of elements.
1637	* @return array
1638	*/
1639	private function get_in_clause_params( $list ) { // phpcs:ignore
1640	if ( ! is_array( $list ) ) {
1641	$list = array( $list );
1642	}
1643
1644	/* Filter out any non-numeric campaign IDs, then convert to int */
1645	$list = array_filter( $list, 'is_numeric' );
1646	$list = array_map( 'intval', $list );
1647
1648	return array( charitable_get_query_placeholders( count( $list ), '%d' ), $list );
1649	}
1650
1651	/**
1652	* Checks whether we are using commas for decimals.
1653	*
1654	* @since 1.3.0
1655	*
1656	* @return boolean
1657	*/
1658	private function is_comma_decimal() {
1659	if ( ! isset( $this->comma_decimal ) ) {
1660	$this->comma_decimal = Charitable_Currency::get_instance()->is_comma_decimal();
1661	}
1662
1663	return $this->comma_decimal;
1664	}
1665
1666	/**
1667	* Sanitize amounts retrieved from the database.
1668	*
1669	* @since 1.3.0
1670	*
1671	* @param object $campaign_donation Campaign donation record.
1672	* @return object
1673	*/
1674	private function sanitize_amounts( $campaign_donation ) {
1675	$campaign_donation->amount = Charitable_Currency::get_instance()->sanitize_database_amount( $campaign_donation->amount );
1676	return $campaign_donation;
1677	}
1678
1679	/**
1680	* Sanitize amounts for grouped queries
1681	*
1682	* @since 1.6.57
1683	*
1684	* @param array $item in array is a label, item[1] is the amount. We need to sanitize the amount.
1685	* @return object
1686	*/
1687	private function sanitize_grouped_amounts( $item ) {
1688	$item[1] = Charitable_Currency::get_instance()->sanitize_database_amount( $item[1] );
1689	return $item;
1690	}
1691
1692	/**
1693	* Return a sanitized column name.
1694	*
1695	* @since 1.4.0
1696	*
1697	* @param string $field Return the column name.
1698	* @return string
1699	*/
1700	private function get_sanitized_column( $field ) {
1701	switch ( $field ) {
1702	case 'campaign':
1703	case 'campaign_id':
1704	$column = 'campaign_id';
1705	break;
1706
1707	case 'donation':
1708	case 'donation_id':
1709	$column = 'donation_id';
1710	break;
1711
1712	case 'donor':
1713	case 'donor_id':
1714	$column = 'donor_id';
1715	break;
1716
1717	default:
1718	charitable_get_deprecated()->doing_it_wrong(
1719	__METHOD__,
1720	__( 'Field expected to be `campaign`, `campaign_id`, `donation`, `donation_id`, `donor` or `donor_id`.', 'charitable' ),
1721	'1.4.0'
1722	);
1723
1724	$column = false;
1725	}
1726
1727	return $column;
1728	}
1729
1730	/**
1731	* Flush campaign caches when updating one or more rows.
1732	*
1733	* @since 1.5.11
1734	*
1735	* @param int $row_id The primary key ID for the row we are retrieving.
1736	* @param string $where Column used in where argument.
1737	* @return void
1738	*/
1739	private function flush_campaign_caches( $row_id, $where = '' ) {
1740	if ( empty( $where ) ) {
1741	$where = $this->primary_key;
1742	}
1743
1744	$campaign_ids = array_unique( $this->get_column_all_by( 'campaign_id', $where, $row_id ) );
1745
1746	foreach ( $campaign_ids as $campaign_id ) {
1747	Charitable_Campaign::flush_donations_cache( $campaign_id );
1748	}
1749	}
1750	}
1751
1752	endif;
1753