← All changes | inc/database/tables/donations.php +388 -87 1.5.0 → 1.6.1 View file →

		@@ -9,8 +9,9 @@
9	9
10	10	use SureDonation\Inc\Campaigns\Campaign_Stats;
11	11	use SureDonation\Inc\Database\Base;
12	12	use SureDonation\Inc\Helper;
	13	+use SureDonation\Inc\Pdf\Receipt_Generator;
13	14	use SureDonation\Inc\Traits\Get_Instance;
14	15
15	16	// Exit if accessed directly.
16	17	defined( 'ABSPATH' ) || exit;
		@@ -36,11 +37,27 @@
36	37	*
37	38	* @var int
38	39	* @since 0.0.1
39	40	*/
40		- protected $table_version = 5;
	41	+ protected $table_version = 7;
41	42
42	43	/**
	44	+ * Valid donor-comment moderation statuses.
	45	+ *
	46	+ * `approved` comments are public; `pending` is awaiting review (only reachable
	47	+ * when the "Hold donor comments for review" setting is on); `rejected` is
	48	+ * hidden but kept, so a moderator's decision is not destructive.
	49	+ *
	50	+ * @var array<string>
	51	+ * @since 1.6.0
	52	+ */
	53	+ private static $valid_comment_statuses = [
	54	+ 'approved',
	55	+ 'pending',
	56	+ 'rejected',
	57	+ ];
	58	+
	59	+ /**
43	60	* Valid payment statuses.
44	61	*
45	62	* @var array<string>
46	63	* @since 0.0.1
		@@ -53,8 +70,14 @@
53	70	'refunded',
54	71	'partially_refunded',
55	72	'cancelled',
56	73	'suspicious',
	74	+ // Deliberately not 'failed'. A donor who closed the gateway window never
	75	+ // attempted a payment, and collapsing the two destroys the signal we most
	76	+ // need: our own capture failure rate. If abandonment and genuine failures
	77	+ // share a status, "most donors walk away at the gateway" (a product
	78	+ // problem) is indistinguishable from "our captures are breaking" (a bug).
	79	+ 'abandoned',
57	80	];
58	81
59	82	/**
60	83	* Valid order columns.
		@@ -169,8 +192,12 @@
169	192	'donor_comment' => [
170	193	'type' => 'string',
171	194	'default' => '',
172	195	],
	196	+ 'donor_comment_status' => [
	197	+ 'type' => 'string',
	198	+ 'default' => 'approved',
	199	+ ],
173	200	'receipt_sent' => [
174	201	'type' => 'boolean',
175	202	'default' => false,
176	203	],
		@@ -205,8 +232,12 @@
205	232	'import_source' => [
206	233	'type' => 'string',
207	234	'default' => '',
208	235	],
	236	+ 'import_provenance' => [
	237	+ 'type' => 'string',
	238	+ 'default' => '',
	239	+ ],
209	240	'created_at' => [
210	241	'type' => 'datetime',
211	242	],
212	243	'updated_at' => [
		@@ -242,8 +273,9 @@
242	273	'subscription_id VARCHAR(255) NOT NULL',
243	274	'subscription_status VARCHAR(30) NOT NULL',
244	275	'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
245	276	'donor_comment TEXT',
	277	+ 'donor_comment_status VARCHAR(20) NOT NULL DEFAULT \'approved\'',
246	278	'receipt_sent TINYINT(1) NOT NULL DEFAULT 0',
247	279	'receipt_pdf_url VARCHAR(255) NOT NULL',
248	280	'donation_data LONGTEXT',
249	281	'log LONGTEXT',
		@@ -251,8 +283,9 @@
251	283	'user_agent TEXT',
252	284	'referer_url TEXT',
253	285	'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
254	286	'import_source VARCHAR(20) NOT NULL DEFAULT \'\'',
	287	+ 'import_provenance VARCHAR(64) NOT NULL DEFAULT \'\'',
255	288	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
256	289	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
257	290	'INDEX idx_campaign (campaign_id)',
258	291	'INDEX idx_donor (donor_id)',
		@@ -263,8 +296,9 @@
263	296	'INDEX idx_subscription (subscription_id)',
264	297	'INDEX idx_subscription_status (subscription_status)',
265	298	'INDEX idx_parent_subscription (parent_subscription_id)',
266	299	'INDEX idx_import_source (import_source_id, import_source)',
	300	+ 'INDEX idx_import_provenance (import_source, import_provenance)',
267	301	'INDEX idx_stripe_account (stripe_account_id)',
268	302	];
269	303	}
270	304
		@@ -274,10 +308,30 @@
274	308	* Version 2 added subscription support; version 4 added the
275	309	* source-agnostic pair `import_source_id` + `import_source` used by
276	310	* the migration tool for duplicate detection and rollback; version 5
277	311	* added `stripe_account_id` so donations record which connected Stripe
278		- * account processed them (multiple Stripe accounts support).
	312	+ * account processed them (multiple Stripe accounts support); version 6
	313	+ * added `import_provenance` — an indexed `(donation_post_id, source_campaign_id)`
	314	+ * key the Charitable importer dedupes on with a single indexed lookup per
	315	+ * row, instead of scanning + JSON-decoding every prior imported row per batch;
	316	+ * version 7 added `donor_comment_status`, defaulting to `approved` so
	317	+ * comments that predate moderation stay visible.
279	318	*
	319	+ * Version 7 rather than 6: `import_provenance` had already taken 6 on dev
	320	+ * while this branch was open, and the upgrade only runs when the number
	321	+ * increases (Database\Base::set_db_upgradable()). Leaving both columns on 6
	322	+ * would mean any site already upgraded to 6 never receives
	323	+ * `donor_comment_status`, while get_schema() still declares it and
	324	+ * prepare_data() names every declared column in the INSERT — so every
	325	+ * donation would fail with "Unknown column 'donor_comment_status'".
	326	+ *
	327	+ * No index accompanies `donor_comment_status`: it is `approved` on virtually
	328	+ * every row, so a `(campaign_id, donor_comment_status)` index measured ~3%
	329	+ * better than the existing `idx_campaign` on a 200k-row table and still
	330	+ * filesorted, while adding write cost to the plugin's hottest table. Its one
	331	+ * reader (Campaign_Stats::get_donor_comments()) is also behind a 5-minute
	332	+ * transient. Revisit only if that query shows up in real profiling.
	333	+ *
280	334	* {@inheritDoc}
281	335	*
282	336	* @since 1.0.0
283	337	*/
		@@ -287,13 +341,16 @@
287	341	'subscription_status VARCHAR(30) NOT NULL AFTER subscription_id',
288	342	'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER subscription_status',
289	343	'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER referer_url',
290	344	'import_source VARCHAR(20) NOT NULL DEFAULT \'\' AFTER import_source_id',
	345	+ 'import_provenance VARCHAR(64) NOT NULL DEFAULT \'\' AFTER import_source',
291	346	'stripe_account_id VARCHAR(50) NOT NULL DEFAULT \'\' AFTER customer_id',
	347	+ 'donor_comment_status VARCHAR(20) NOT NULL DEFAULT \'approved\' AFTER donor_comment',
292	348	'INDEX idx_subscription (subscription_id)',
293	349	'INDEX idx_subscription_status (subscription_status)',
294	350	'INDEX idx_parent_subscription (parent_subscription_id)',
295	351	'INDEX idx_import_source (import_source_id, import_source)',
	352	+ 'INDEX idx_import_provenance (import_source, import_provenance)',
296	353	'INDEX idx_stripe_account (stripe_account_id)',
297	354	];
298	355	}
299	356
		@@ -299,14 +356,12 @@
299	356
300	357	/**
301	358	* One-time data migrations for the donations table.
302	359	*
303		- * Version 5 introduced the `stripe_account_id` column. Before multi-account there
304		- * could only be a single connected Stripe account, so every pre-v5 Stripe
305		- * donation belongs to the current (single) default account. Backfill it so
306		- * refunds and subscription lifecycle actions keep routing to the originating
307		- * account after a second account is connected and the default is switched.
308		- * Idempotent (touches only empty rows) and gated to the upgrade into v5.
	360	+ * Each backfill is gated on the version being upgraded *into* (via
	361	+ * $this->prev_version) so it runs exactly once, on the upgrade that adds the
	362	+ * column, and is skipped on fresh installs (which create the column already
	363	+ * populated / empty as appropriate) and on later upgrades.
309	364	*
310	365	* @return void
311	366	* @since 1.3.0
312	367	*/
		@@ -316,13 +371,30 @@
316	371	if ( ! $this->db_upgradable ) {
317	372	return;
318	373	}
319	374
320		- // Already on v5+ (e.g. a later upgrade) — the backfill is done.
321		- if ( $this->prev_version >= 5 ) {
322		- return;
	375	+ if ( $this->prev_version < 5 ) {
	376	+ $this->backfill_stripe_account_id();
323	377	}
324	378
	379	+ if ( $this->prev_version < 6 ) {
	380	+ $this->backfill_import_provenance();
	381	+ }
	382	+ }
	383	+
	384	+ /**
	385	+ * Backfill `stripe_account_id` on the upgrade into v5.
	386	+ *
	387	+ * Before multi-account there could only be a single connected Stripe account,
	388	+ * so every pre-v5 Stripe donation belongs to the current (single) default
	389	+ * account. Backfill it so refunds and subscription lifecycle actions keep
	390	+ * routing to the originating account after a second account is connected and
	391	+ * the default is switched. Idempotent (touches only empty rows).
	392	+ *
	393	+ * @return void
	394	+ * @since 1.3.0
	395	+ */
	396	+ private function backfill_stripe_account_id() {
325	397	if ( ! class_exists( '\SureDonation\Inc\Payments\Stripe\Stripe_Helper' ) ) {
326	398	return;
327	399	}
328	400
		@@ -354,8 +426,113 @@
354	426	}
355	427	}
356	428
357	429	/**
	430	+ * Backfill `import_provenance` on the upgrade into v6.
	431	+ *
	432	+ * The Charitable importer moved its dedupe key out of a per-batch scan of
	433	+ * `donation_data` and onto this indexed column. Rows imported before v6 have
	434	+ * an empty key, so a re-import after upgrade would fail to match them and
	435	+ * insert duplicates. Reconstruct the key from the stored
	436	+ * `donation_data.charitable` block — the same `(donation_post_id,
	437	+ * source_campaign_id | campaign label)` rule the importer keys on — for every
	438	+ * pre-v6 one-time Charitable row. Chunked so a large migrated table does not
	439	+ * exhaust memory during the upgrade; idempotent (touches only empty keys).
	440	+ *
	441	+ * @return void
	442	+ * @since 1.5.1
	443	+ */
	444	+ private function backfill_import_provenance() {
	445	+ global $wpdb;
	446	+ $table = $this->get_tablename();
	447	+
	448	+ do {
	449	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time chunked backfill of a newly added column; not cacheable.
	450	+ $rows = $wpdb->get_results(
	451	+ $wpdb->prepare(
	452	+ 'SELECT id, donation_data FROM %i WHERE import_source = %s AND donation_type != %s AND import_provenance = %s LIMIT 500',
	453	+ $table,
	454	+ 'charitable',
	455	+ 'recurring',
	456	+ ''
	457	+ ),
	458	+ ARRAY_A
	459	+ );
	460	+
	461	+ if ( empty( $rows ) || ! is_array( $rows ) ) {
	462	+ break;
	463	+ }
	464	+
	465	+ $fetched = count( $rows );
	466	+
	467	+ foreach ( $rows as $row ) {
	468	+ $data = json_decode( (string) ( $row['donation_data'] ?? '' ), true );
	469	+ $c = is_array( $data ) && isset( $data['charitable'] ) && is_array( $data['charitable'] ) ? $data['charitable'] : [];
	470	+ $post = isset( $c['donation_post_id'] ) ? absint( $c['donation_post_id'] ) : 0;
	471	+
	472	+ // A row with no resolvable donation post can never be dedupe-matched
	473	+ // or rolled back; leave its key empty (it is already un-reversible)
	474	+ // rather than fabricate a colliding "0:…" key.
	475	+ if ( $post <= 0 ) {
	476	+ $key = '';
	477	+ } else {
	478	+ $campaign = isset( $c['source_campaign_id'] ) ? absint( $c['source_campaign_id'] ) : 0;
	479	+ // DB-path rows carry `campaign_name`; CSV-path rows carry
	480	+ // `campaign_title`. Either serves as the blank-id fallback label.
	481	+ $label = '';
	482	+ if ( isset( $c['campaign_title'] ) && is_scalar( $c['campaign_title'] ) ) {
	483	+ $label = (string) $c['campaign_title'];
	484	+ } elseif ( isset( $c['campaign_name'] ) && is_scalar( $c['campaign_name'] ) ) {
	485	+ $label = (string) $c['campaign_name'];
	486	+ }
	487	+ $key = self::build_provenance_key( $post, $campaign, $label );
	488	+ }
	489	+
	490	+ if ( '' === $key ) {
	491	+ // Nothing to store, but stamp a sentinel so the WHERE clause
	492	+ // stops selecting this row and the loop terminates.
	493	+ $key = '-';
	494	+ }
	495	+
	496	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill update; not cacheable.
	497	+ $wpdb->update( $table, [ 'import_provenance' => $key ], [ 'id' => absint( $row['id'] ) ] );
	498	+ }
	499	+ } while ( 500 === $fetched );
	500	+ }
	501	+
	502	+ /**
	503	+ * Build the indexed dedupe key for a Charitable donation row.
	504	+ *
	505	+ * `"<donation_post_id>:<token>"`, where the token is the numeric campaign id
	506	+ * when present, otherwise a short hash of the campaign label (so the
	507	+ * per-campaign rows of a multi-campaign donation whose export left the
	508	+ * Campaign ID cell blank stay distinct instead of collapsing to "<post>:0"),
	509	+ * otherwise "0". Static so both the importer (Provenance_Dedupe) and the v6
	510	+ * backfill derive identical keys.
	511	+ *
	512	+ * @param int $donation_post_id Charitable donation post ID.
	513	+ * @param int $source_campaign_id Charitable campaign ID (0 when absent).
	514	+ * @param string $campaign_label Campaign title/name fallback (optional).
	515	+ * @return string
	516	+ * @since 1.5.1
	517	+ */
	518	+ public static function build_provenance_key( $donation_post_id, $source_campaign_id, $campaign_label = '' ) {
	519	+ $post = absint( $donation_post_id );
	520	+ $cid = absint( $source_campaign_id );
	521	+ $label = trim( (string) $campaign_label );
	522	+
	523	+ if ( $cid > 0 ) {
	524	+ $token = (string) $cid;
	525	+ } elseif ( '' !== $label ) {
	526	+ $token = 't:' . substr( md5( strtolower( $label ) ), 0, 12 );
	527	+ } else {
	528	+ $token = '0';
	529	+ }
	530	+
	531	+ return $post . ':' . $token;
	532	+ }
	533	+
	534	+ /**
358	535	* Add a new donation record.
359	536	*
360	537	* @param array<mixed> $data Donation data to insert.
361	538	* @return int|false The donation ID on success, false on error.
		@@ -564,30 +741,31 @@
564	741
565	742	$is_anonymous = ! empty( $donation['is_anonymous'] );
566	743
567	744	$payload = [
568		- 'id' => isset( $donation['id'] ) ? absint( Helper::get_string_value( $donation['id'] ) ) : 0,
569		- 'campaign_id' => isset( $donation['campaign_id'] ) ? absint( Helper::get_string_value( $donation['campaign_id'] ) ) : 0,
570		- 'form_id' => isset( $donation['form_id'] ) ? absint( Helper::get_string_value( $donation['form_id'] ) ) : 0,
571		- 'donor_id' => isset( $donation['donor_id'] ) ? absint( Helper::get_string_value( $donation['donor_id'] ) ) : 0,
572		- 'donor_name' => Helper::get_string_value( $donation['donor_name'] ?? '' ),
573		- 'donor_email' => Helper::get_string_value( $donation['donor_email'] ?? '' ),
574		- 'donor_phone' => Helper::get_string_value( $donation['donor_phone'] ?? '' ),
575		- 'amount' => Helper::get_float_value( $donation['amount'] ?? 0 ),
576		- 'fees_covered' => Helper::get_float_value( $donation['fees_covered'] ?? 0 ),
577		- 'refunded_amount' => Helper::get_float_value( $donation['refunded_amount'] ?? 0 ),
578		- 'currency' => Helper::get_string_value( $donation['currency'] ?? '' ),
579		- 'gateway' => Helper::get_string_value( $donation['gateway'] ?? '' ),
580		- 'payment_status' => Helper::get_string_value( $donation['payment_status'] ?? '' ),
581		- 'payment_mode' => Helper::get_string_value( $donation['payment_mode'] ?? '' ),
582		- 'donation_type' => Helper::get_string_value( $donation['donation_type'] ?? '' ),
583		- 'transaction_id' => Helper::get_string_value( $donation['transaction_id'] ?? '' ),
584		- 'subscription_id' => Helper::get_string_value( $donation['subscription_id'] ?? '' ),
585		- 'subscription_status' => Helper::get_string_value( $donation['subscription_status'] ?? '' ),
586		- 'donor_comment' => Helper::get_string_value( $donation['donor_comment'] ?? '' ),
587		- 'is_anonymous' => $is_anonymous,
588		- 'created_at' => Helper::get_string_value( $donation['created_at'] ?? '' ),
589		- 'updated_at' => Helper::get_string_value( $donation['updated_at'] ?? '' ),
	745	+ 'id' => isset( $donation['id'] ) ? absint( Helper::get_string_value( $donation['id'] ) ) : 0,
	746	+ 'campaign_id' => isset( $donation['campaign_id'] ) ? absint( Helper::get_string_value( $donation['campaign_id'] ) ) : 0,
	747	+ 'form_id' => isset( $donation['form_id'] ) ? absint( Helper::get_string_value( $donation['form_id'] ) ) : 0,
	748	+ 'donor_id' => isset( $donation['donor_id'] ) ? absint( Helper::get_string_value( $donation['donor_id'] ) ) : 0,
	749	+ 'donor_name' => Helper::get_string_value( $donation['donor_name'] ?? '' ),
	750	+ 'donor_email' => Helper::get_string_value( $donation['donor_email'] ?? '' ),
	751	+ 'donor_phone' => Helper::get_string_value( $donation['donor_phone'] ?? '' ),
	752	+ 'amount' => Helper::get_float_value( $donation['amount'] ?? 0 ),
	753	+ 'fees_covered' => Helper::get_float_value( $donation['fees_covered'] ?? 0 ),
	754	+ 'refunded_amount' => Helper::get_float_value( $donation['refunded_amount'] ?? 0 ),
	755	+ 'currency' => Helper::get_string_value( $donation['currency'] ?? '' ),
	756	+ 'gateway' => Helper::get_string_value( $donation['gateway'] ?? '' ),
	757	+ 'payment_status' => Helper::get_string_value( $donation['payment_status'] ?? '' ),
	758	+ 'payment_mode' => Helper::get_string_value( $donation['payment_mode'] ?? '' ),
	759	+ 'donation_type' => Helper::get_string_value( $donation['donation_type'] ?? '' ),
	760	+ 'transaction_id' => Helper::get_string_value( $donation['transaction_id'] ?? '' ),
	761	+ 'subscription_id' => Helper::get_string_value( $donation['subscription_id'] ?? '' ),
	762	+ 'subscription_status' => Helper::get_string_value( $donation['subscription_status'] ?? '' ),
	763	+ 'donor_comment' => Helper::get_string_value( $donation['donor_comment'] ?? '' ),
	764	+ 'donor_comment_status' => Helper::get_string_value( $donation['donor_comment_status'] ?? '' ),
	765	+ 'is_anonymous' => $is_anonymous,
	766	+ 'created_at' => Helper::get_string_value( $donation['created_at'] ?? '' ),
	767	+ 'updated_at' => Helper::get_string_value( $donation['updated_at'] ?? '' ),
590	768	];
591	769
592	770	/**
593	771	* Filter the curated donation payload passed to every integration hook.
		@@ -728,8 +906,14 @@
728	906	// Build query based on filters.
729	907	// Note: Renewal records (donation_type = 'renewal') are intentionally included in the listing.
730	908	// They are shown alongside parent subscriptions so admins can see all transaction activity.
731	909	// Renewals are also accessible from the parent donation's subscription detail billing history.
	910	+ // With no status filter, abandoned rows are left out: they are kept as
	911	+ // funnel data (a campaign with 40 starts against 3 completions has
	912	+ // learned something real) but a donor who walked away from the gateway is
	913	+ // not a transaction an admin needs in their default view. Asking for the
	914	+ // status explicitly still returns them, and count_admin_list() mirrors
	915	+ // this or the pagination totals disagree with the rows.
732	916	$has_status = 'all' !== $status;
733	917	$has_campaign = $campaign_id > 0;
734	918	$has_search = ! empty( $search );
735	919	$is_asc = 'ASC' === $order;
		@@ -831,9 +1015,9 @@
831	1015	$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
832	1016	$results = $is_asc
833	1017	? $wpdb->get_results(
834	1018	$wpdb->prepare(
835		- 'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
	1019	+ 'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) AND payment_status != \'abandoned\' ORDER BY %i ASC LIMIT %d, %d',
836	1020	$table,
837	1021	absint( $campaign_id ),
838	1022	$search_term,
839	1023	$search_term,
		@@ -845,9 +1029,9 @@
845	1029	ARRAY_A
846	1030	)
847	1031	: $wpdb->get_results(
848	1032	$wpdb->prepare(
849		- 'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
	1033	+ 'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) AND payment_status != \'abandoned\' ORDER BY %i DESC LIMIT %d, %d',
850	1034	$table,
851	1035	absint( $campaign_id ),
852	1036	$search_term,
853	1037	$search_term,
		@@ -885,9 +1069,9 @@
885	1069	} elseif ( $has_campaign ) {
886	1070	$results = $is_asc
887	1071	? $wpdb->get_results(
888	1072	$wpdb->prepare(
889		- 'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i ASC LIMIT %d, %d',
	1073	+ 'SELECT * FROM %i WHERE campaign_id = %d AND payment_status != \'abandoned\' ORDER BY %i ASC LIMIT %d, %d',
890	1074	$table,
891	1075	absint( $campaign_id ),
892	1076	$orderby,
893	1077	absint( $offset ),
		@@ -896,9 +1080,9 @@
896	1080	ARRAY_A
897	1081	)
898	1082	: $wpdb->get_results(
899	1083	$wpdb->prepare(
900		- 'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i DESC LIMIT %d, %d',
	1084	+ 'SELECT * FROM %i WHERE campaign_id = %d AND payment_status != \'abandoned\' ORDER BY %i DESC LIMIT %d, %d',
901	1085	$table,
902	1086	absint( $campaign_id ),
903	1087	$orderby,
904	1088	absint( $offset ),
		@@ -910,9 +1094,9 @@
910	1094	$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
911	1095	$results = $is_asc
912	1096	? $wpdb->get_results(
913	1097	$wpdb->prepare(
914		- 'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
	1098	+ 'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) AND payment_status != \'abandoned\' ORDER BY %i ASC LIMIT %d, %d',
915	1099	$table,
916	1100	$search_term,
917	1101	$search_term,
918	1102	$search_term,
		@@ -923,9 +1107,9 @@
923	1107	ARRAY_A
924	1108	)
925	1109	: $wpdb->get_results(
926	1110	$wpdb->prepare(
927		- 'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
	1111	+ 'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) AND payment_status != \'abandoned\' ORDER BY %i DESC LIMIT %d, %d',
928	1112	$table,
929	1113	$search_term,
930	1114	$search_term,
931	1115	$search_term,
		@@ -938,9 +1122,9 @@
938	1122	} else {
939	1123	$results = $is_asc
940	1124	? $wpdb->get_results(
941	1125	$wpdb->prepare(
942		- 'SELECT * FROM %i ORDER BY %i ASC LIMIT %d, %d',
	1126	+ 'SELECT * FROM %i WHERE payment_status != \'abandoned\' ORDER BY %i ASC LIMIT %d, %d',
943	1127	$table,
944	1128	$orderby,
945	1129	absint( $offset ),
946	1130	absint( $limit )
		@@ -948,9 +1132,9 @@
948	1132	ARRAY_A
949	1133	)
950	1134	: $wpdb->get_results(
951	1135	$wpdb->prepare(
952		- 'SELECT * FROM %i ORDER BY %i DESC LIMIT %d, %d',
	1136	+ 'SELECT * FROM %i WHERE payment_status != \'abandoned\' ORDER BY %i DESC LIMIT %d, %d',
953	1137	$table,
954	1138	$orderby,
955	1139	absint( $offset ),
956	1140	absint( $limit )
		@@ -1231,12 +1415,24 @@
1231	1415	return array_map( [ $instance, 'decode_by_datatype' ], $results );
1232	1416	}
1233	1417
1234	1418	/**
1235		- * Delete a donation record.
	1419	+ * Delete a donation record and its receipt PDF.
1236	1420	*
	1421	+ * `receipt_pdf_url` is the only pointer to the receipt on disk, so once the
	1422	+ * row is gone nothing can reach the file again and it would sit in the
	1423	+ * uploads directory indefinitely, holding the donor's name and email
	1424	+ * alongside the amount (a Pro template can add more through
	1425	+ * `suredonation_receipt_html`). The file is removed first, and the row and
	1426	+ * its pointer are kept while the file survives so a retry can still reach
	1427	+ * it - the same retry contract the privacy eraser follows.
	1428	+ *
	1429	+ * A pointer that fails containment in `relative_to_path()` is the one
	1430	+ * exception: it reports "nothing to delete" and does not block the row,
	1431	+ * because no caller will ever act on it.
	1432	+ *
1237	1433	* @param int $donation_id Donation ID.
1238		- * @return int|false Number of rows deleted or false on error.
	1434	+ * @return int|false Number of rows deleted, or false on error or when the receipt file could not be removed.
1239	1435	* @since 0.0.1
1240	1436	*/
1241	1437	public static function delete( $donation_id ) {
1242	1438	if ( empty( $donation_id ) ) {
		@@ -1242,9 +1438,19 @@
1242	1438	if ( empty( $donation_id ) ) {
1243	1439	return false;
1244	1440	}
1245	1441
1246		- return self::get_instance()->use_delete( [ 'id' => absint( $donation_id ) ] );
	1442	+ $donation_id = absint( $donation_id );
	1443	+ $donation = self::get( $donation_id );
	1444	+
	1445	+ // delete_receipt() is a no-op that reports success when the column is
	1446	+ // empty or the file is already gone, so donations without a receipt
	1447	+ // fall straight through to the row delete.
	1448	+ if ( is_array( $donation ) && ! Receipt_Generator::delete_receipt( Helper::get_string_value( $donation['receipt_pdf_url'] ?? '' ) ) ) {
	1449	+ return false;
	1450	+ }
	1451	+
	1452	+ return self::get_instance()->use_delete( [ 'id' => $donation_id ] );
1247	1453	}
1248	1454
1249	1455	/**
1250	1456	* Get donations by donor email.
		@@ -1420,15 +1626,33 @@
1420	1626	*
1421	1627	* Used to gate the review admin notice: a completed live donation is the
1422	1628	* signal that the site has taken a genuine (non-test) donation.
1423	1629	*
	1630	+ * @param string $gateway Optional gateway to scope the count to, e.g. 'paypal'.
	1631	+ * Empty counts every gateway.
1424	1632	* @return int Count of completed live donations.
1425	1633	* @since 1.2.0
	1634	+ * @since 1.5.1 Optionally scoped to one gateway.
1426	1635	*/
1427		- public static function count_live_completed() {
	1636	+ public static function count_live_completed( $gateway = '' ) {
1428	1637	$instance = self::get_instance();
1429	1638	global $wpdb;
1430	1639
	1640	+ if ( '' !== $gateway ) {
	1641	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	1642	+ $count = $wpdb->get_var(
	1643	+ $wpdb->prepare(
	1644	+ 'SELECT COUNT(*) FROM %i WHERE payment_status = %s AND payment_mode = %s AND gateway = %s',
	1645	+ $instance->get_tablename(),
	1646	+ 'completed',
	1647	+ 'live',
	1648	+ $gateway
	1649	+ )
	1650	+ );
	1651	+
	1652	+ return is_numeric( $count ) ? (int) $count : 0;
	1653	+ }
	1654	+
1431	1655	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1432	1656	$count = $wpdb->get_var(
1433	1657	$wpdb->prepare(
1434	1658	'SELECT COUNT(*) FROM %i WHERE payment_status = %s AND payment_mode = %s',
		@@ -1504,45 +1728,8 @@
1504	1728	return self::count_all();
1505	1729	}
1506	1730
1507	1731	/**
1508		- * Get campaign statistics.
1509		- *
1510		- * @param int $campaign_id Campaign ID.
1511		- * @return array<string,mixed> Campaign statistics.
1512		- * @since 0.0.1
1513		- */
1514		- public static function get_campaign_stats( $campaign_id ) {
1515		- $instance = self::get_instance();
1516		- global $wpdb;
1517		-
1518		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1519		- $stats = $wpdb->get_row(
1520		- $wpdb->prepare(
1521		- "SELECT
1522		- COUNT(*) as donation_count,
1523		- COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
1524		- COUNT(DISTINCT donor_email) as unique_donors,
1525		- COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
1526		- COALESCE(MAX(amount - refunded_amount), 0) as largest_donation
1527		- FROM %i
1528		- WHERE campaign_id = %d AND payment_status IN ('completed', 'partially_refunded')",
1529		- $instance->get_tablename(),
1530		- absint( $campaign_id )
1531		- ),
1532		- ARRAY_A
1533		- );
1534		-
1535		- return $stats ? $stats : [
1536		- 'donation_count' => 0,
1537		- 'total_raised' => 0,
1538		- 'unique_donors' => 0,
1539		- 'average_donation' => 0,
1540		- 'largest_donation' => 0,
1541		- ];
1542		- }
1543		-
1544		- /**
1545	1732	* Build the currency / payment-mode scope for a reporting query.
1546	1733	*
1547	1734	* Amounts in different currencies cannot be summed into one figure, and test
1548	1735	* donations must not be counted alongside live ones. Both filters are opt-in
		@@ -1550,12 +1737,14 @@
1550	1737	*
1551	1738	* @param string $currency Currency code ('' for no filter).
1552	1739	* @param string $payment_mode 'test' or 'live' ('' for no filter).
1553	1740	* @param array<mixed> $args Prepare args, appended to by reference.
	1741	+ * @param string $after GMT MySQL datetime; only rows created at or after it ('' for no window). Since 1.6.1.
	1742	+ * @param string $before GMT MySQL datetime; only rows created before it ('' for no upper bound). Since 1.6.1.
1554	1743	* @return string SQL fragment beginning with " AND ", or '' when unscoped.
1555	1744	* @since 1.5.0
1556	1745	*/
1557		- private static function scope_fragment( $currency, $payment_mode, array &$args ) {
	1746	+ private static function scope_fragment( $currency, $payment_mode, array &$args, $after = '', $before = '' ) {
1558	1747	$extra = '';
1559	1748
1560	1749	$currency = is_string( $currency ) ? strtoupper( trim( $currency ) ) : '';
1561	1750	if ( '' !== $currency ) {
		@@ -1568,8 +1757,22 @@
1568	1757	$extra .= ' AND payment_mode = %s';
1569	1758	$args[] = $payment_mode;
1570	1759	}
1571	1760
	1761	+ // created_at is stored in GMT (add() uses current_time( 'mysql', true )),
	1762	+ // so callers must pass a GMT datetime or the window drifts by the site offset.
	1763	+ $after = is_string( $after ) ? trim( $after ) : '';
	1764	+ if ( '' !== $after ) {
	1765	+ $extra .= ' AND created_at >= %s';
	1766	+ $args[] = $after;
	1767	+ }
	1768	+
	1769	+ $before = is_string( $before ) ? trim( $before ) : '';
	1770	+ if ( '' !== $before ) {
	1771	+ $extra .= ' AND created_at < %s';
	1772	+ $args[] = $before;
	1773	+ }
	1774	+
1572	1775	return $extra;
1573	1776	}
1574	1777	/**
1575	1778	* Get global dashboard statistics.
		@@ -1575,17 +1778,19 @@
1575	1778	* Get global dashboard statistics.
1576	1779	*
1577	1780	* @param string $currency Currency code to scope to ('' for no filter).
1578	1781	* @param string $payment_mode 'test' or 'live' ('' for no filter).
	1782	+ * @param string $after GMT MySQL datetime; only donations created at or after it ('' for all time). Since 1.6.1.
	1783	+ * @param string $before GMT MySQL datetime; only donations created before it ('' for no upper bound). Since 1.6.1.
1579	1784	* @return array{total_donations: string, total_raised: string, unique_donors: string, average_donation: string, largest_donation: string} Dashboard statistics.
1580	1785	* @since 0.0.1
1581	1786	*/
1582		- public static function get_dashboard_stats( $currency = '', $payment_mode = '' ) {
	1787	+ public static function get_dashboard_stats( $currency = '', $payment_mode = '', $after = '', $before = '' ) {
1583	1788	$instance = self::get_instance();
1584	1789	global $wpdb;
1585	1790
1586	1791	$args = [ $instance->get_tablename() ];
1587		- $extra = self::scope_fragment( $currency, $payment_mode, $args );
	1792	+ $extra = self::scope_fragment( $currency, $payment_mode, $args, $after, $before );
1588	1793
1589	1794	$sql = "SELECT
1590	1795	COUNT(*) as total_donations,
1591	1796	COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
		@@ -1646,17 +1851,18 @@
1646	1851	*
1647	1852	* @param int $limit Number of campaigns to retrieve.
1648	1853	* @param string $currency Currency code to scope to ('' for no filter).
1649	1854	* @param string $payment_mode 'test' or 'live' ('' for no filter).
	1855	+ * @param string $after GMT MySQL datetime; only donations created at or after it ('' for all time). Since 1.6.1.
1650	1856	* @return array<int, array{campaign_id: string, donation_count: string, total_raised: string, unique_donors: string}> Array of top campaigns with stats.
1651	1857	* @since 0.0.1
1652	1858	*/
1653		- public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '' ) {
	1859	+ public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '', $after = '' ) {
1654	1860	$instance = self::get_instance();
1655	1861	global $wpdb;
1656	1862
1657	1863	$args = [ $instance->get_tablename(), SUREDONATION_POST_TYPE ];
1658		- $extra = self::scope_fragment( $currency, $payment_mode, $args );
	1864	+ $extra = self::scope_fragment( $currency, $payment_mode, $args, $after );
1659	1865	$args[] = absint( $limit );
1660	1866
1661	1867	// The join is what makes LIMIT meaningful: orphaned campaign_ids (post
1662	1868	// deleted, donations kept) still carry donations, so filtering them in
		@@ -1684,8 +1890,64 @@
1684	1890	return $results ? $results : [];
1685	1891	}
1686	1892
1687	1893	/**
	1894	+ * Published campaigns whose most recent completed donation is older than
	1895	+ * $before, or that have never received one.
	1896	+ *
	1897	+ * The scope (currency / payment mode) applies to the donations side of
	1898	+ * the join, so a campaign whose only gifts fall outside the scope is
	1899	+ * reported as never-donated rather than dropped. Campaigns that used to
	1900	+ * receive donations sort first, most recently active first — they are
	1901	+ * the ones an admin acts on — and never-donated campaigns fill whatever
	1902	+ * is left of the limit, so a site with many that never converted does not
	1903	+ * show the same five forever.
	1904	+ *
	1905	+ * @param string $before GMT MySQL datetime; a campaign is quiet when its last completed donation is earlier than this.
	1906	+ * @param int $limit Number of campaigns to retrieve.
	1907	+ * @param string $currency Currency code to scope donations to ('' for no filter).
	1908	+ * @param string $payment_mode 'test' or 'live' ('' for no filter).
	1909	+ * @return array<int, array{campaign_id: string, campaign_title: string, last_donation_at: string|null}>
	1910	+ * @since 1.6.1
	1911	+ */
	1912	+ public static function get_stale_campaigns( $before, $limit = 5, $currency = '', $payment_mode = '' ) {
	1913	+ $before = is_string( $before ) ? trim( $before ) : '';
	1914	+ if ( '' === $before ) {
	1915	+ return [];
	1916	+ }
	1917	+
	1918	+ $instance = self::get_instance();
	1919	+ global $wpdb;
	1920	+
	1921	+ $args = [ $instance->get_tablename() ];
	1922	+ $extra = self::scope_fragment( $currency, $payment_mode, $args );
	1923	+ $args[] = SUREDONATION_POST_TYPE;
	1924	+ $args[] = $before;
	1925	+ $args[] = absint( $limit );
	1926	+
	1927	+ $sql = "SELECT
	1928	+ p.ID AS campaign_id,
	1929	+ p.post_title AS campaign_title,
	1930	+ MAX(d.created_at) AS last_donation_at
	1931	+ FROM {$wpdb->posts} AS p
	1932	+ LEFT JOIN %i AS d
	1933	+ ON d.campaign_id = p.ID
	1934	+ AND d.payment_status IN ('completed', 'partially_refunded')
	1935	+ {$extra}
	1936	+ WHERE p.post_type = %s
	1937	+ AND p.post_status = 'publish'
	1938	+ GROUP BY p.ID, p.post_title
	1939	+ HAVING MAX(d.created_at) IS NULL OR MAX(d.created_at) < %s
	1940	+ ORDER BY (MAX(d.created_at) IS NULL) ASC, MAX(d.created_at) DESC, p.ID ASC
	1941	+ LIMIT %d";
	1942	+
	1943	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $extra is built only from static placeholder fragments; every value travels in $args.
	1944	+ $results = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
	1945	+
	1946	+ return $results ? $results : [];
	1947	+ }
	1948	+
	1949	+ /**
1688	1950	* Get donation trends over time.
1689	1951	*
1690	1952	* @param string $after Start date (ISO format).
1691	1953	* @param string $before End date (ISO format).
		@@ -1918,8 +2180,13 @@
1918	2180
1919	2181	if ( 'all' !== $status ) {
1920	2182	$conditions[] = 'payment_status = %s';
1921	2183	$args[] = sanitize_text_field( $status );
	2184	+ } else {
	2185	+ // Mirrors get_admin_list(): abandoned rows are out of the unfiltered
	2186	+ // listing, so the total has to leave them out too or the last page
	2187	+ // comes back short.
	2188	+ $conditions[] = "payment_status != 'abandoned'";
1922	2189	}
1923	2190
1924	2191	if ( $campaign_id > 0 ) {
1925	2192	$conditions[] = 'campaign_id = %d';
		@@ -2132,8 +2399,42 @@
2132	2399	* @since 0.0.1
2133	2400	*/
2134	2401	public static function get_valid_statuses() {
2135	2402	return self::$valid_statuses;
	2403	+ }
	2404	+
	2405	+ /**
	2406	+ * Get valid donor-comment moderation statuses.
	2407	+ *
	2408	+ * @return array<string> Valid donor-comment statuses.
	2409	+ * @since 1.6.0
	2410	+ */
	2411	+ public static function get_valid_comment_statuses() {
	2412	+ return self::$valid_comment_statuses;
	2413	+ }
	2414	+
	2415	+ /**
	2416	+ * Resolve the moderation status a newly captured donor comment should get.
	2417	+ *
	2418	+ * Held for review only when the site owner has opted in; otherwise comments
	2419	+ * publish straight away, matching how GiveWP and Charitable behave out of the
	2420	+ * box. An empty comment gets `approved` so a donation with nothing to moderate
	2421	+ * never shows up in a review queue.
	2422	+ *
	2423	+ * @param string $comment The captured comment.
	2424	+ * @return string One of self::$valid_comment_statuses.
	2425	+ * @since 1.6.0
	2426	+ */
	2427	+ public static function initial_comment_status( $comment ) {
	2428	+ if ( '' === trim( Helper::get_string_value( $comment ) ) ) {
	2429	+ return 'approved';
	2430	+ }
	2431	+
	2432	+ $donor_settings = Helper::get_array_value(
	2433	+ Helper::get_suredonation_option( \SureDonation\Inc\API\Settings_API::DONOR_OPTION_KEY, [] )
	2434	+ );
	2435	+
	2436	+ return ! empty( $donor_settings['hold_donor_comments'] ) ? 'pending' : 'approved';
2136	2437	}
2137	2438
2138	2439	/**
2139	2440	* Add a log entry to a donation.