PluginProbe
SureDonation – Donation Forms, Fundraising Campaigns & Donor Management / 1.6.1
SureDonation – Donation Forms, Fundraising Campaigns & Donor Management v1.6.1
1.6.1 1.6.0 1.5.1 1.5.0 1.4.0 1.3.0 trunk 0.0.1 1.0.0 1.1.0 1.1.1 1.1.2 1.2.0
← All changes | inc/database/tables/donations.php +1107 -123 1.1.2 → 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 = 4;
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.
@@ -117,8 +140,12 @@
117 140 'customer_id' => [
118 141 'type' => 'string',
119 142 'default' => '',
120 143 ],
144 + 'stripe_account_id' => [
145 + 'type' => 'string',
146 + 'default' => '',
147 + ],
121 148 'gateway' => [
122 149 'type' => 'string',
123 150 'default' => 'stripe',
124 151 ],
@@ -165,8 +192,12 @@
165 192 'donor_comment' => [
166 193 'type' => 'string',
167 194 'default' => '',
168 195 ],
196 + 'donor_comment_status' => [
197 + 'type' => 'string',
198 + 'default' => 'approved',
199 + ],
169 200 'receipt_sent' => [
170 201 'type' => 'boolean',
171 202 'default' => false,
172 203 ],
@@ -201,8 +232,12 @@
201 232 'import_source' => [
202 233 'type' => 'string',
203 234 'default' => '',
204 235 ],
236 + 'import_provenance' => [
237 + 'type' => 'string',
238 + 'default' => '',
239 + ],
205 240 'created_at' => [
206 241 'type' => 'datetime',
207 242 ],
208 243 'updated_at' => [
@@ -225,8 +260,9 @@
225 260 'refunded_amount DECIMAL(26,8) NOT NULL DEFAULT 0',
226 261 'currency VARCHAR(10) NOT NULL',
227 262 'transaction_id VARCHAR(255) NOT NULL',
228 263 'customer_id VARCHAR(50) NOT NULL',
264 + 'stripe_account_id VARCHAR(50) NOT NULL DEFAULT \'\'',
229 265 'gateway VARCHAR(20) NOT NULL',
230 266 'payment_status VARCHAR(50) NOT NULL',
231 267 'payment_mode VARCHAR(20) NOT NULL',
232 268 'donor_name VARCHAR(255) NOT NULL',
@@ -237,8 +273,9 @@
237 273 'subscription_id VARCHAR(255) NOT NULL',
238 274 'subscription_status VARCHAR(30) NOT NULL',
239 275 'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
240 276 'donor_comment TEXT',
277 + 'donor_comment_status VARCHAR(20) NOT NULL DEFAULT \'approved\'',
241 278 'receipt_sent TINYINT(1) NOT NULL DEFAULT 0',
242 279 'receipt_pdf_url VARCHAR(255) NOT NULL',
243 280 'donation_data LONGTEXT',
244 281 'log LONGTEXT',
@@ -245,9 +282,10 @@
245 282 'ip_address VARCHAR(45) NOT NULL',
246 283 'user_agent TEXT',
247 284 'referer_url TEXT',
248 285 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
249 - 'import_source VARCHAR(20) NOT NULL DEFAULT ""',
286 + 'import_source VARCHAR(20) NOT NULL DEFAULT \'\'',
287 + 'import_provenance VARCHAR(64) NOT NULL DEFAULT \'\'',
250 288 'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
251 289 'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
252 290 'INDEX idx_campaign (campaign_id)',
253 291 'INDEX idx_donor (donor_id)',
@@ -258,8 +296,10 @@
258 296 'INDEX idx_subscription (subscription_id)',
259 297 'INDEX idx_subscription_status (subscription_status)',
260 298 'INDEX idx_parent_subscription (parent_subscription_id)',
261 299 'INDEX idx_import_source (import_source_id, import_source)',
300 + 'INDEX idx_import_provenance (import_source, import_provenance)',
301 + 'INDEX idx_stripe_account (stripe_account_id)',
262 302 ];
263 303 }
264 304
265 305 /**
@@ -266,10 +306,32 @@
266 306 * New columns added across versions.
267 307 *
268 308 * Version 2 added subscription support; version 4 added the
269 309 * source-agnostic pair `import_source_id` + `import_source` used by
270 - * the migration tool for duplicate detection and rollback.
310 + * the migration tool for duplicate detection and rollback; version 5
311 + * added `stripe_account_id` so donations record which connected Stripe
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.
271 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 + *
272 334 * {@inheritDoc}
273 335 *
274 336 * @since 1.0.0
275 337 */
@@ -278,17 +340,199 @@
278 340 'subscription_id VARCHAR(255) NOT NULL AFTER donation_type',
279 341 'subscription_status VARCHAR(30) NOT NULL AFTER subscription_id',
280 342 'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER subscription_status',
281 343 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER referer_url',
282 - 'import_source VARCHAR(20) NOT NULL DEFAULT "" AFTER import_source_id',
344 + 'import_source VARCHAR(20) NOT NULL DEFAULT \'\' AFTER import_source_id',
345 + 'import_provenance VARCHAR(64) NOT NULL DEFAULT \'\' AFTER import_source',
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',
283 348 'INDEX idx_subscription (subscription_id)',
284 349 'INDEX idx_subscription_status (subscription_status)',
285 350 'INDEX idx_parent_subscription (parent_subscription_id)',
286 351 'INDEX idx_import_source (import_source_id, import_source)',
352 + 'INDEX idx_import_provenance (import_source, import_provenance)',
353 + 'INDEX idx_stripe_account (stripe_account_id)',
287 354 ];
288 355 }
289 356
290 357 /**
358 + * One-time data migrations for the donations table.
359 + *
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.
364 + *
365 + * @return void
366 + * @since 1.3.0
367 + */
368 + public function run_data_migrations() {
369 + // A failed CREATE/ALTER earlier in this upgrade already cleared the flag;
370 + // the column may not exist, so don't run an UPDATE against it.
371 + if ( ! $this->db_upgradable ) {
372 + return;
373 + }
374 +
375 + if ( $this->prev_version < 5 ) {
376 + $this->backfill_stripe_account_id();
377 + }
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() {
397 + if ( ! class_exists( '\SureDonation\Inc\Payments\Stripe\Stripe_Helper' ) ) {
398 + return;
399 + }
400 +
401 + // Runs during the v5 DB upgrade — before any second account can be
402 + // connected via the UI — so the default is still the single legacy account.
403 + $account_id = \SureDonation\Inc\Payments\Stripe\Stripe_Helper::get_default_account_id();
404 + if ( ! is_string( $account_id ) || '' === $account_id ) {
405 + return;
406 + }
407 +
408 + global $wpdb;
409 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill of a newly added column; not cacheable.
410 + $result = $wpdb->query(
411 + $wpdb->prepare(
412 + 'UPDATE %i SET stripe_account_id = %s WHERE gateway = %s AND ( stripe_account_id = %s OR stripe_account_id IS NULL )',
413 + $this->get_tablename(),
414 + $account_id,
415 + 'stripe',
416 + ''
417 + )
418 + );
419 +
420 + // A transient failure (e.g. lock wait timeout on a busy table) must not
421 + // persist the new version: `prev_version >= 5` would then skip this
422 + // one-shot backfill forever. Leaving the version unwritten makes the
423 + // idempotent sequence retry on the next request.
424 + if ( false === $result ) {
425 + $this->db_upgradable = false;
426 + }
427 + }
428 +
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 + /**
291 535 * Add a new donation record.
292 536 *
293 537 * @param array<mixed> $data Donation data to insert.
294 538 * @return int|false The donation ID on success, false on error.
@@ -319,16 +563,48 @@
319 563 $donation_id = absint( $result );
320 564 $donation = self::get( $donation_id );
321 565 $donation = is_array( $donation ) ? $donation : [];
322 566
567 + // Curated payload (internal/gateway-only columns omitted; donor
568 + // identity included, see the note in get_integration_payload())
569 + // shared by every hook below.
570 + $payload = self::get_integration_payload( $donation );
571 +
323 572 /**
324 573 * Fires when a new donation record is created.
325 574 *
326 575 * @param int $donation_id Newly created donation ID.
327 - * @param array<mixed> $donation Complete donation record.
576 + * @param array<mixed> $donation Curated donation payload.
328 577 * @since 1.1.0
329 578 */
330 - do_action( 'suredonation_donation_created', $donation_id, $donation );
579 + do_action( 'suredonation_donation_created', $donation_id, $payload );
580 +
581 + /**
582 + * Fires when a new donation record is created.
583 + *
584 + * Mirrors `suredonation_donation_created`; the OttoKit (formerly
585 + * SureTriggers) "New Donation" trigger listens on this hook name.
586 + *
587 + * @param int $donation_id Newly created donation ID.
588 + * @param array<mixed> $donation Curated donation payload.
589 + * @since 1.2.0
590 + */
591 + do_action( 'suredonation_new_donation', $donation_id, $payload );
592 +
593 + // Some donations are created already-completed rather than
594 + // transitioning through update() — recurring renewals and
595 + // admin-recorded paid donations. Fire the completion event here
596 + // too so integration hooks still see them.
597 + if ( 'completed' === ( $data['payment_status'] ?? '' ) ) {
598 + /**
599 + * Fires when a donation payment is completed.
600 + *
601 + * @param int $donation_id Donation ID.
602 + * @param array<mixed> $donation Curated donation payload after insertion.
603 + * @since 1.2.0
604 + */
605 + do_action( 'suredonation_donation_completed', $donation_id, $payload );
606 + }
331 607 }
332 608 }
333 609
334 610 return $result;
@@ -346,15 +622,17 @@
346 622 if ( empty( $donation_id ) ) {
347 623 return false;
348 624 }
349 625
350 - // Capture the current status before the write so integration hooks
351 - // (e.g. OttoKit) can react to the actual status transition, not just
352 - // the resulting value.
353 - $old_status = '';
354 - if ( isset( $data['payment_status'] ) ) {
355 - $existing = self::get( absint( $donation_id ) );
356 - $old_status = is_array( $existing ) ? Helper::get_string_value( $existing['payment_status'] ?? '' ) : '';
626 + // Capture the current status and refunded amount before the write so
627 + // integration hooks (e.g. OttoKit) can react to the transition and to
628 + // refund events, not just the resulting values.
629 + $old_status = '';
630 + $old_refunded = 0.0;
631 + if ( isset( $data['payment_status'] ) || isset( $data['refunded_amount'] ) ) {
632 + $existing = self::get( absint( $donation_id ) );
633 + $old_status = is_array( $existing ) ? Helper::get_string_value( $existing['payment_status'] ?? '' ) : '';
634 + $old_refunded = is_array( $existing ) ? Helper::get_float_value( $existing['refunded_amount'] ?? 0 ) : 0.0;
357 635 }
358 636
359 637 // Set updated_at.
360 638 $data['updated_at'] = current_time( 'mysql' );
@@ -364,22 +642,22 @@
364 642 // Status/amount changes (e.g. a webhook completing a pending donation)
365 643 // affect the cached stats and donor lists.
366 644 if ( $updated ) {
367 645 $donation = self::get( absint( $donation_id ) );
646 + $donation = is_array( $donation ) ? $donation : [];
368 647 if ( ! empty( $donation['campaign_id'] ) ) {
369 648 Campaign_Stats::clear_cache( absint( Helper::get_string_value( $donation['campaign_id'] ) ) );
370 649 }
371 650
372 - // Notify integration hooks about a genuine status transition.
373 - // Fired from update() — the single choke point every status write
374 - // passes through (update_status() delegates here, as do the payment
375 - // frontends and webhooks) — so all transitions are caught.
651 + // Curated payload (internal/gateway-only columns omitted; donor
652 + // identity included, see the note in get_integration_payload())
653 + // shared by every hook below.
654 + $payload = self::get_integration_payload( $donation );
655 +
376 656 if ( isset( $data['payment_status'] ) ) {
377 657 $new_status = Helper::get_string_value( $data['payment_status'] );
378 658
379 659 if ( $new_status !== $old_status ) {
380 - $donation = is_array( $donation ) ? $donation : [];
381 -
382 660 /**
383 661 * Fires when a donation's payment status changes.
384 662 *
385 663 * @param int $donation_id Donation ID.
@@ -384,14 +662,51 @@
384 662 *
385 663 * @param int $donation_id Donation ID.
386 664 * @param string $new_status New payment status.
387 665 * @param string $old_status Previous payment status (empty string if unknown).
388 - * @param array<mixed> $donation Complete donation record after the update.
666 + * @param array<mixed> $donation Curated donation payload after the update.
389 667 * @since 1.1.0
390 668 */
391 - do_action( 'suredonation_donation_status_changed', absint( $donation_id ), $new_status, $old_status, $donation );
669 + do_action( 'suredonation_donation_status_changed', absint( $donation_id ), $new_status, $old_status, $payload );
670 +
671 + // Fire the completion event for any genuine transition into
672 + // 'completed' — including admin review states (suspicious,
673 + // cancelled) — but never for refund reversals that restore
674 + // the 'completed' status (refunded/partially_refunded ->
675 + // completed), which would replay the completion automation.
676 + if ( 'completed' === $new_status && ! in_array( $old_status, [ 'completed', 'refunded', 'partially_refunded' ], true ) ) {
677 + /**
678 + * Fires when a donation payment is completed.
679 + *
680 + * @param int $donation_id Donation ID.
681 + * @param array<mixed> $donation Curated donation payload after the update.
682 + * @since 1.2.0
683 + */
684 + do_action( 'suredonation_donation_completed', absint( $donation_id ), $payload );
685 + }
392 686 }
393 687 }
688 +
689 + // A rise in refunded_amount means a refund was processed. Keying off
690 + // the amount (not the status string) catches repeat partial refunds
691 + // that leave the status as partially_refunded, and excludes refund
692 + // reversals where the amount drops.
693 + if ( isset( $data['refunded_amount'] ) ) {
694 + $new_refunded = Helper::get_float_value( $data['refunded_amount'] );
695 +
696 + if ( $new_refunded - $old_refunded > 0.0001 ) {
697 + /**
698 + * Fires when a donation is refunded, fully or partially.
699 + *
700 + * @param int $donation_id Donation ID.
701 + * @param float $refund_amount Amount refunded in this event.
702 + * @param float $total_refunded Cumulative amount refunded to date.
703 + * @param array<mixed> $donation Curated donation payload after the update.
704 + * @since 1.2.0
705 + */
706 + do_action( 'suredonation_donation_refunded', absint( $donation_id ), $new_refunded - $old_refunded, $new_refunded, $payload );
707 + }
708 + }
394 709 }
395 710
396 711 return $updated;
397 712 }
@@ -396,8 +711,82 @@
396 711 return $updated;
397 712 }
398 713
399 714 /**
715 + * Build a curated donation payload for integration hooks.
716 + *
717 + * Trims the raw database row to the fields advertised in the OttoKit embed
718 + * `sample_response`, omitting internal and gateway-only columns that must not
719 + * leave the site (ip_address, user_agent, referer_url, the admin `log`, the
720 + * gateway `customer_id`, and the full `donation_data` submission). Monetary
721 + * values are cast to float to match the sample the automation builder maps
722 + * against (the raw column is a DECIMAL string). Shared by every `do_action`
723 + * in add()/update() so no listener — OttoKit or otherwise — receives the raw
724 + * row.
725 + *
726 + * Anonymous donations carry their real donor identity here. The anonymous
727 + * checkbox is a display-only flag — the data is stored and processed as
728 + * usual, and only the public donor wall / recent donations / top donors mask
729 + * it. Automations that need to treat anonymous donors differently branch on
730 + * the `is_anonymous` field in this payload; blanking the identity instead
731 + * would silently break receipting and CRM sync for those donations.
732 + *
733 + * @param array<string,mixed> $donation Raw donation record from self::get().
734 + * @return array<string,mixed> Curated, integration-safe payload.
735 + * @since 1.2.0
736 + */
737 + public static function get_integration_payload( $donation ) {
738 + if ( ! is_array( $donation ) ) {
739 + return [];
740 + }
741 +
742 + $is_anonymous = ! empty( $donation['is_anonymous'] );
743 +
744 + $payload = [
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'] ?? '' ),
768 + ];
769 +
770 + /**
771 + * Filter the curated donation payload passed to every integration hook.
772 + *
773 + * The payload carries the donor's real identity even for anonymous
774 + * donations, because the anonymous checkbox only masks public donor
775 + * lists — automations still need a usable record, and they can branch on
776 + * the `is_anonymous` field. A site with a stricter policy (for example an
777 + * automation that posts donor names somewhere public) can use this filter
778 + * to blank or drop fields before they reach OttoKit or any third-party
779 + * listener.
780 + *
781 + * @param array<string,mixed> $payload Curated payload.
782 + * @param array<string,mixed> $donation Raw donation record.
783 + * @since 1.4.0
784 + */
785 + return apply_filters( 'suredonation_integration_payload', $payload, $donation );
786 + }
787 +
788 + /**
400 789 * Get a single donation by ID.
401 790 *
402 791 * @param int $donation_id Donation ID.
403 792 * @return array<mixed>|null Donation data or null if not found.
@@ -517,8 +906,14 @@
517 906 // Build query based on filters.
518 907 // Note: Renewal records (donation_type = 'renewal') are intentionally included in the listing.
519 908 // They are shown alongside parent subscriptions so admins can see all transaction activity.
520 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.
521 916 $has_status = 'all' !== $status;
522 917 $has_campaign = $campaign_id > 0;
523 918 $has_search = ! empty( $search );
524 919 $is_asc = 'ASC' === $order;
@@ -620,9 +1015,9 @@
620 1015 $search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
621 1016 $results = $is_asc
622 1017 ? $wpdb->get_results(
623 1018 $wpdb->prepare(
624 - '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',
625 1020 $table,
626 1021 absint( $campaign_id ),
627 1022 $search_term,
628 1023 $search_term,
@@ -634,9 +1029,9 @@
634 1029 ARRAY_A
635 1030 )
636 1031 : $wpdb->get_results(
637 1032 $wpdb->prepare(
638 - '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',
639 1034 $table,
640 1035 absint( $campaign_id ),
641 1036 $search_term,
642 1037 $search_term,
@@ -674,9 +1069,9 @@
674 1069 } elseif ( $has_campaign ) {
675 1070 $results = $is_asc
676 1071 ? $wpdb->get_results(
677 1072 $wpdb->prepare(
678 - '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',
679 1074 $table,
680 1075 absint( $campaign_id ),
681 1076 $orderby,
682 1077 absint( $offset ),
@@ -685,9 +1080,9 @@
685 1080 ARRAY_A
686 1081 )
687 1082 : $wpdb->get_results(
688 1083 $wpdb->prepare(
689 - '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',
690 1085 $table,
691 1086 absint( $campaign_id ),
692 1087 $orderby,
693 1088 absint( $offset ),
@@ -699,9 +1094,9 @@
699 1094 $search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
700 1095 $results = $is_asc
701 1096 ? $wpdb->get_results(
702 1097 $wpdb->prepare(
703 - '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',
704 1099 $table,
705 1100 $search_term,
706 1101 $search_term,
707 1102 $search_term,
@@ -712,9 +1107,9 @@
712 1107 ARRAY_A
713 1108 )
714 1109 : $wpdb->get_results(
715 1110 $wpdb->prepare(
716 - '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',
717 1112 $table,
718 1113 $search_term,
719 1114 $search_term,
720 1115 $search_term,
@@ -727,9 +1122,9 @@
727 1122 } else {
728 1123 $results = $is_asc
729 1124 ? $wpdb->get_results(
730 1125 $wpdb->prepare(
731 - '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',
732 1127 $table,
733 1128 $orderby,
734 1129 absint( $offset ),
735 1130 absint( $limit )
@@ -737,9 +1132,9 @@
737 1132 ARRAY_A
738 1133 )
739 1134 : $wpdb->get_results(
740 1135 $wpdb->prepare(
741 - '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',
742 1137 $table,
743 1138 $orderby,
744 1139 absint( $offset ),
745 1140 absint( $limit )
@@ -757,8 +1152,147 @@
757 1152 return array_map( [ $instance, 'decode_by_datatype' ], $results );
758 1153 }
759 1154
760 1155 /**
1156 + * Build the WHERE clause + prepare-args for an export query.
1157 + *
1158 + * Always constrains to one-time donations (subscription_id = '' AND
1159 + * parent_subscription_id = 0) so recurring/renewal rows never leak into the
1160 + * free export — recurring export is Pro (see the Import & Export spec, #237).
1161 + * Optional filters: status, campaign_id, payment_mode, gateway, and a
1162 + * created_at date range (after / before).
1163 + *
1164 + * @param array<string, mixed> $filters Filter map.
1165 + * @param array<int, mixed> $args Prepare-args, populated by reference in placeholder order.
1166 + * @return string WHERE clause (without the "WHERE" keyword); placeholders only, no interpolated values.
1167 + * @since 1.3.0
1168 + */
1169 + private static function build_export_where( $filters, &$args ) {
1170 + $conditions = [ '1=1' ];
1171 +
1172 + /**
1173 + * Whether the donations export is restricted to one-time donations.
1174 + *
1175 + * True by default so recurring/renewal rows never leak into the free
1176 + * export; Pro returns false to include subscriptions and renewals.
1177 + *
1178 + * @param bool $one_time_only Whether to restrict to one-time donations.
1179 + */
1180 + if ( apply_filters( 'suredonation_export_one_time_only', true ) ) {
1181 + $conditions[] = 'subscription_id = %s';
1182 + $conditions[] = 'parent_subscription_id = %d';
1183 + $args[] = '';
1184 + $args[] = 0;
1185 + }
1186 +
1187 + $status = sanitize_text_field( Helper::get_string_value( $filters['status'] ?? '' ) );
1188 + if ( '' !== $status && 'all' !== $status ) {
1189 + $conditions[] = 'payment_status = %s';
1190 + $args[] = $status;
1191 + }
1192 +
1193 + $campaign_id = absint( Helper::get_string_value( $filters['campaign_id'] ?? 0 ) );
1194 + if ( $campaign_id > 0 ) {
1195 + $conditions[] = 'campaign_id = %d';
1196 + $args[] = $campaign_id;
1197 + }
1198 +
1199 + $payment_mode = sanitize_text_field( Helper::get_string_value( $filters['payment_mode'] ?? '' ) );
1200 + if ( '' !== $payment_mode ) {
1201 + $conditions[] = 'payment_mode = %s';
1202 + $args[] = $payment_mode;
1203 + }
1204 +
1205 + $gateway = sanitize_text_field( Helper::get_string_value( $filters['gateway'] ?? '' ) );
1206 + if ( '' !== $gateway ) {
1207 + $conditions[] = 'gateway = %s';
1208 + $args[] = $gateway;
1209 + }
1210 +
1211 + $after = sanitize_text_field( Helper::get_string_value( $filters['after'] ?? '' ) );
1212 + if ( '' !== $after ) {
1213 + $conditions[] = 'created_at >= %s';
1214 + $args[] = $after;
1215 + }
1216 +
1217 + $before = sanitize_text_field( Helper::get_string_value( $filters['before'] ?? '' ) );
1218 + if ( '' !== $before ) {
1219 + // A date-only `before` (Y-m-d) coerces to 00:00:00, which would
1220 + // silently drop donations made later that same day. Normalize to
1221 + // end-of-day so the whole end date is inclusive; full datetimes
1222 + // are left untouched.
1223 + if ( 1 === preg_match( '/^\d{4}-\d{2}-\d{2}$/', $before ) ) {
1224 + $before .= ' 23:59:59';
1225 + }
1226 + $conditions[] = 'created_at <= %s';
1227 + $args[] = $before;
1228 + }
1229 +
1230 + return implode( ' AND ', $conditions );
1231 + }
1232 +
1233 + /**
1234 + * Count one-time donations matching the export filters.
1235 + *
1236 + * @param array<string, mixed> $filters Filter map (see build_export_where()).
1237 + * @return int Matching row count.
1238 + * @since 1.3.0
1239 + */
1240 + public static function count_for_export( $filters = [] ) {
1241 + $instance = self::get_instance();
1242 + global $wpdb;
1243 + $table = $instance->get_tablename();
1244 +
1245 + $args = [];
1246 + $where = self::build_export_where( $filters, $args );
1247 +
1248 + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Export count over live data.
1249 + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built only from static placeholder fragments; every value is passed through prepare args.
1250 + $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", array_merge( [ $table ], $args ) ) );
1251 + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1252 +
1253 + return is_numeric( $count ) ? (int) $count : 0;
1254 + }
1255 +
1256 + /**
1257 + * Fetch one-time donations for export, decoded.
1258 + *
1259 + * @param array<string, mixed> $filters Filter map (see build_export_where()).
1260 + * @param int $limit Max rows to return (0 = no limit).
1261 + * @param int $offset Offset for pagination.
1262 + * @return array<int, array<string, mixed>> Decoded donation rows.
1263 + * @since 1.3.0
1264 + */
1265 + public static function get_for_export( $filters = [], $limit = 0, $offset = 0 ) {
1266 + $instance = self::get_instance();
1267 + global $wpdb;
1268 + $table = $instance->get_tablename();
1269 +
1270 + $args = [];
1271 + $where = self::build_export_where( $filters, $args );
1272 +
1273 + $sql = "SELECT * FROM %i WHERE {$where} ORDER BY created_at DESC";
1274 + $prepare_args = array_merge( [ $table ], $args );
1275 +
1276 + if ( $limit > 0 ) {
1277 + $sql .= ' LIMIT %d, %d';
1278 + $prepare_args[] = absint( $offset );
1279 + $prepare_args[] = absint( $limit );
1280 + }
1281 +
1282 + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Export query over live data.
1283 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $sql is assembled only from static placeholder fragments; every value is passed through prepare args.
1284 + $results = $wpdb->get_results( $wpdb->prepare( $sql, $prepare_args ), ARRAY_A );
1285 + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1286 +
1287 + if ( ! $results || ! is_array( $results ) ) {
1288 + return [];
1289 + }
1290 +
1291 + return array_map( [ $instance, 'decode_by_datatype' ], $results );
1292 + }
1293 +
1294 + /**
761 1295 * Get donations by status with pagination.
762 1296 *
763 1297 * @param string $status Payment status.
764 1298 * @param int $limit Number of records to return.
@@ -881,12 +1415,24 @@
881 1415 return array_map( [ $instance, 'decode_by_datatype' ], $results );
882 1416 }
883 1417
884 1418 /**
885 - * Delete a donation record.
1419 + * Delete a donation record and its receipt PDF.
886 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 + *
887 1433 * @param int $donation_id Donation ID.
888 - * @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.
889 1435 * @since 0.0.1
890 1436 */
891 1437 public static function delete( $donation_id ) {
892 1438 if ( empty( $donation_id ) ) {
@@ -892,19 +1438,31 @@
892 1438 if ( empty( $donation_id ) ) {
893 1439 return false;
894 1440 }
895 1441
896 - 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 ] );
897 1453 }
898 1454
899 1455 /**
900 1456 * Get donations by donor email.
901 1457 *
902 - * @param string $email Donor email.
1458 + * @param string $email Donor email.
1459 + * @param int $limit Max rows to return; 0 (default) returns all rows.
1460 + * @param int $offset Row offset, applied only when $limit > 0.
903 1461 * @return array<mixed> Array of donations.
904 1462 * @since 0.0.1
905 1463 */
906 - public static function get_by_donor_email( $email ) {
1464 + public static function get_by_donor_email( $email, $limit = 0, $offset = 0 ) {
907 1465 if ( empty( $email ) ) {
908 1466 return [];
909 1467 }
910 1468
@@ -910,18 +1468,35 @@
910 1468
911 1469 $instance = self::get_instance();
912 1470 global $wpdb;
913 1471
914 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
915 - $results = $wpdb->get_results(
916 - $wpdb->prepare(
917 - 'SELECT * FROM %i WHERE donor_email = %s ORDER BY created_at DESC',
918 - $instance->get_tablename(),
919 - sanitize_email( $email )
920 - ),
921 - ARRAY_A
922 - );
1472 + $limit = max( 0, (int) $limit );
1473 + $offset = max( 0, (int) $offset );
923 1474
1475 + if ( $limit > 0 ) {
1476 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1477 + $results = $wpdb->get_results(
1478 + $wpdb->prepare(
1479 + 'SELECT * FROM %i WHERE donor_email = %s ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
1480 + $instance->get_tablename(),
1481 + sanitize_email( $email ),
1482 + $limit,
1483 + $offset
1484 + ),
1485 + ARRAY_A
1486 + );
1487 + } else {
1488 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1489 + $results = $wpdb->get_results(
1490 + $wpdb->prepare(
1491 + 'SELECT * FROM %i WHERE donor_email = %s ORDER BY created_at DESC, id DESC',
1492 + $instance->get_tablename(),
1493 + sanitize_email( $email )
1494 + ),
1495 + ARRAY_A
1496 + );
1497 + }
1498 +
924 1499 if ( ! $results || ! is_array( $results ) ) {
925 1500 return [];
926 1501 }
927 1502
@@ -960,8 +1535,50 @@
960 1535 return $instance->decode_by_datatype( $result );
961 1536 }
962 1537
963 1538 /**
1539 + * Get donation by gateway subscription ID.
1540 + *
1541 + * Recurring handling lives in Pro, but the table (and its
1542 + * `idx_subscription` index) belongs here, so free-side code that only needs
1543 + * to resolve a row — such as the PayPal webhook listener recording why a
1544 + * delivery was rejected — can look one up without depending on Pro.
1545 + *
1546 + * Renewals carry the same `subscription_id` as the subscription they belong
1547 + * to, so the column is deliberately not unique. The parent row (the one with
1548 + * no `parent_subscription_id`) is preferred and the oldest id breaks any
1549 + * remaining tie, so the result does not depend on the query plan.
1550 + *
1551 + * @param string $subscription_id Gateway subscription ID.
1552 + * @return array<string, mixed>|null Donation data or null if not found.
1553 + * @since 1.4.0
1554 + */
1555 + public static function get_by_subscription_id( $subscription_id ) {
1556 + if ( empty( $subscription_id ) ) {
1557 + return null;
1558 + }
1559 +
1560 + $instance = self::get_instance();
1561 + global $wpdb;
1562 +
1563 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1564 + $result = $wpdb->get_row(
1565 + $wpdb->prepare(
1566 + 'SELECT * FROM %i WHERE subscription_id = %s ORDER BY parent_subscription_id ASC, id ASC LIMIT 1',
1567 + $instance->get_tablename(),
1568 + sanitize_text_field( $subscription_id )
1569 + ),
1570 + ARRAY_A
1571 + );
1572 +
1573 + if ( ! $result ) {
1574 + return null;
1575 + }
1576 +
1577 + return $instance->decode_by_datatype( $result );
1578 + }
1579 +
1580 + /**
964 1581 * Get total donations count (no filters).
965 1582 *
966 1583 * @return int Total count.
967 1584 * @since 0.0.1
@@ -1004,8 +1621,52 @@
1004 1621 return is_numeric( $count ) ? (int) $count : 0;
1005 1622 }
1006 1623
1007 1624 /**
1625 + * Get the count of completed, live-mode donations.
1626 + *
1627 + * Used to gate the review admin notice: a completed live donation is the
1628 + * signal that the site has taken a genuine (non-test) donation.
1629 + *
1630 + * @param string $gateway Optional gateway to scope the count to, e.g. 'paypal'.
1631 + * Empty counts every gateway.
1632 + * @return int Count of completed live donations.
1633 + * @since 1.2.0
1634 + * @since 1.5.1 Optionally scoped to one gateway.
1635 + */
1636 + public static function count_live_completed( $gateway = '' ) {
1637 + $instance = self::get_instance();
1638 + global $wpdb;
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 +
1655 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1656 + $count = $wpdb->get_var(
1657 + $wpdb->prepare(
1658 + 'SELECT COUNT(*) FROM %i WHERE payment_status = %s AND payment_mode = %s',
1659 + $instance->get_tablename(),
1660 + 'completed',
1661 + 'live'
1662 + )
1663 + );
1664 +
1665 + return is_numeric( $count ) ? (int) $count : 0;
1666 + }
1667 +
1668 + /**
1008 1669 * Get total donations count by campaign.
1009 1670 *
1010 1671 * @param int $campaign_id Campaign ID.
1011 1672 * @return int Total count.
@@ -1067,58 +1728,71 @@
1067 1728 return self::count_all();
1068 1729 }
1069 1730
1070 1731 /**
1071 - * Get campaign statistics.
1732 + * Build the currency / payment-mode scope for a reporting query.
1072 1733 *
1073 - * @param int $campaign_id Campaign ID.
1074 - * @return array<string,mixed> Campaign statistics.
1075 - * @since 0.0.1
1734 + * Amounts in different currencies cannot be summed into one figure, and test
1735 + * donations must not be counted alongside live ones. Both filters are opt-in
1736 + * so existing callers keep their behaviour; the abilities always pass them.
1737 + *
1738 + * @param string $currency Currency code ('' for no filter).
1739 + * @param string $payment_mode 'test' or 'live' ('' for no filter).
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.
1743 + * @return string SQL fragment beginning with " AND ", or '' when unscoped.
1744 + * @since 1.5.0
1076 1745 */
1077 - public static function get_campaign_stats( $campaign_id ) {
1078 - $instance = self::get_instance();
1079 - global $wpdb;
1746 + private static function scope_fragment( $currency, $payment_mode, array &$args, $after = '', $before = '' ) {
1747 + $extra = '';
1080 1748
1081 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1082 - $stats = $wpdb->get_row(
1083 - $wpdb->prepare(
1084 - "SELECT
1085 - COUNT(*) as donation_count,
1086 - COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
1087 - COUNT(DISTINCT donor_email) as unique_donors,
1088 - COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
1089 - COALESCE(MAX(amount - refunded_amount), 0) as largest_donation
1090 - FROM %i
1091 - WHERE campaign_id = %d AND payment_status IN ('completed', 'partially_refunded')",
1092 - $instance->get_tablename(),
1093 - absint( $campaign_id )
1094 - ),
1095 - ARRAY_A
1096 - );
1749 + $currency = is_string( $currency ) ? strtoupper( trim( $currency ) ) : '';
1750 + if ( '' !== $currency ) {
1751 + $extra .= ' AND currency = %s';
1752 + $args[] = $currency;
1753 + }
1097 1754
1098 - return $stats ? $stats : [
1099 - 'donation_count' => 0,
1100 - 'total_raised' => 0,
1101 - 'unique_donors' => 0,
1102 - 'average_donation' => 0,
1103 - 'largest_donation' => 0,
1104 - ];
1755 + $payment_mode = is_string( $payment_mode ) ? strtolower( trim( $payment_mode ) ) : '';
1756 + if ( in_array( $payment_mode, [ 'test', 'live' ], true ) ) {
1757 + $extra .= ' AND payment_mode = %s';
1758 + $args[] = $payment_mode;
1759 + }
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 +
1775 + return $extra;
1105 1776 }
1106 -
1107 1777 /**
1108 1778 * Get global dashboard statistics.
1109 1779 *
1780 + * @param string $currency Currency code to scope to ('' for no filter).
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.
1110 1784 * @return array{total_donations: string, total_raised: string, unique_donors: string, average_donation: string, largest_donation: string} Dashboard statistics.
1111 1785 * @since 0.0.1
1112 1786 */
1113 - public static function get_dashboard_stats() {
1787 + public static function get_dashboard_stats( $currency = '', $payment_mode = '', $after = '', $before = '' ) {
1114 1788 $instance = self::get_instance();
1115 1789 global $wpdb;
1116 1790
1117 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1118 - $stats = $wpdb->get_row(
1119 - $wpdb->prepare(
1120 - "SELECT
1791 + $args = [ $instance->get_tablename() ];
1792 + $extra = self::scope_fragment( $currency, $payment_mode, $args, $after, $before );
1793 +
1794 + $sql = "SELECT
1121 1795 COUNT(*) as total_donations,
1122 1796 COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
1123 1797 COUNT(DISTINCT donor_email) as unique_donors,
1124 1798 COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
@@ -1123,14 +1797,14 @@
1123 1797 COUNT(DISTINCT donor_email) as unique_donors,
1124 1798 COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
1125 1799 COALESCE(MAX(amount - refunded_amount), 0) as largest_donation
1126 1800 FROM %i
1127 - WHERE payment_status IN ('completed', 'partially_refunded')",
1128 - $instance->get_tablename()
1129 - ),
1130 - ARRAY_A
1131 - );
1801 + WHERE payment_status IN ('completed', 'partially_refunded')
1802 + {$extra}";
1132 1803
1804 + // 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.
1805 + $stats = $wpdb->get_row( $wpdb->prepare( $sql, $args ), ARRAY_A );
1806 +
1133 1807 return $stats ? $stats : [
1134 1808 'total_donations' => 0,
1135 1809 'total_raised' => 0,
1136 1810 'unique_donors' => 0,
@@ -1141,26 +1815,31 @@
1141 1815
1142 1816 /**
1143 1817 * Get recent donations globally (all campaigns).
1144 1818 *
1145 - * @param int $limit Number of donations to retrieve.
1819 + * @param int $limit Number of donations to retrieve.
1820 + * @param string $currency Currency code to scope to ('' for no filter).
1821 + * @param string $payment_mode 'test' or 'live' ('' for no filter).
1146 1822 * @return array<int, array<string, mixed>> Array of recent donations.
1147 1823 * @since 0.0.1
1148 1824 */
1149 - public static function get_recent_donations_global( $limit = 5 ) {
1825 + public static function get_recent_donations_global( $limit = 5, $currency = '', $payment_mode = '' ) {
1150 1826 $instance = self::get_instance();
1151 1827 global $wpdb;
1152 1828
1153 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1154 - $results = $wpdb->get_results(
1155 - $wpdb->prepare(
1156 - "SELECT * FROM %i WHERE payment_status IN ('completed', 'partially_refunded') ORDER BY created_at DESC LIMIT %d",
1157 - $instance->get_tablename(),
1158 - absint( $limit )
1159 - ),
1160 - ARRAY_A
1161 - );
1829 + $args = [ $instance->get_tablename() ];
1830 + $extra = self::scope_fragment( $currency, $payment_mode, $args );
1831 + $args[] = absint( $limit );
1162 1832
1833 + $sql = "SELECT * FROM %i
1834 + WHERE payment_status IN ('completed', 'partially_refunded')
1835 + {$extra}
1836 + ORDER BY created_at DESC
1837 + LIMIT %d";
1838 +
1839 + // 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.
1840 + $results = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
1841 +
1163 1842 if ( ! $results || ! is_array( $results ) ) {
1164 1843 return [];
1165 1844 }
1166 1845
@@ -1169,48 +1848,118 @@
1169 1848
1170 1849 /**
1171 1850 * Get top campaigns by donations.
1172 1851 *
1173 - * @param int $limit Number of campaigns to retrieve.
1852 + * @param int $limit Number of campaigns to retrieve.
1853 + * @param string $currency Currency code to scope to ('' for no filter).
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.
1174 1856 * @return array<int, array{campaign_id: string, donation_count: string, total_raised: string, unique_donors: string}> Array of top campaigns with stats.
1175 1857 * @since 0.0.1
1176 1858 */
1177 - public static function get_top_campaigns( $limit = 5 ) {
1859 + public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '', $after = '' ) {
1178 1860 $instance = self::get_instance();
1179 1861 global $wpdb;
1180 1862
1181 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1182 - $results = $wpdb->get_results(
1183 - $wpdb->prepare(
1184 - "SELECT
1185 - campaign_id,
1863 + $args = [ $instance->get_tablename(), SUREDONATION_POST_TYPE ];
1864 + $extra = self::scope_fragment( $currency, $payment_mode, $args, $after );
1865 + $args[] = absint( $limit );
1866 +
1867 + // The join is what makes LIMIT meaningful: orphaned campaign_ids (post
1868 + // deleted, donations kept) still carry donations, so filtering them in
1869 + // PHP after a SQL LIMIT returned fewer than the requested top-N while
1870 + // valid campaigns sat below the cut.
1871 + $sql = "SELECT
1872 + d.campaign_id,
1873 + p.post_title AS campaign_title,
1186 1874 COUNT(*) as donation_count,
1187 1875 COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
1188 1876 COUNT(DISTINCT donor_email) as unique_donors
1189 - FROM %i
1877 + FROM %i AS d
1878 + INNER JOIN {$wpdb->posts} AS p
1879 + ON p.ID = d.campaign_id
1880 + AND p.post_type = %s
1190 1881 WHERE payment_status IN ('completed', 'partially_refunded')
1191 - GROUP BY campaign_id
1882 + {$extra}
1883 + GROUP BY d.campaign_id
1192 1884 ORDER BY total_raised DESC
1193 - LIMIT %d",
1194 - $instance->get_tablename(),
1195 - absint( $limit )
1196 - ),
1197 - ARRAY_A
1198 - );
1885 + LIMIT %d";
1199 1886
1887 + // 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.
1888 + $results = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
1889 +
1200 1890 return $results ? $results : [];
1201 1891 }
1202 1892
1203 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 + /**
1204 1950 * Get donation trends over time.
1205 1951 *
1206 - * @param string $after Start date (ISO format).
1207 - * @param string $before End date (ISO format).
1208 - * @param string $group Grouping: 'day', 'week', or 'month'.
1952 + * @param string $after Start date (ISO format).
1953 + * @param string $before End date (ISO format).
1954 + * @param string $group Grouping: 'day', 'week', or 'month'.
1955 + * @param string $currency Currency code to scope to ('' for no currency filter).
1956 + * @param int $campaign_id Campaign to scope to (0 for all campaigns).
1957 + * @param string $payment_mode 'test' or 'live' ('' for no filter).
1209 1958 * @return array<int, array{period: string, donation_count: string, total_amount: string}> Array of donation trends.
1210 1959 * @since 0.0.1
1211 1960 */
1212 - public static function get_donation_trends( $after = '', $before = '', $group = 'day' ) {
1961 + public static function get_donation_trends( $after = '', $before = '', $group = 'day', $currency = '', $campaign_id = 0, $payment_mode = '' ) {
1213 1962 $instance = self::get_instance();
1214 1963 global $wpdb;
1215 1964
1216 1965 // Default to last 30 days if no dates provided.
@@ -1234,12 +1983,33 @@
1234 1983 $date_format = '%Y-%m-%d';
1235 1984 break;
1236 1985 }
1237 1986
1238 - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1239 - $results = $wpdb->get_results(
1240 - $wpdb->prepare(
1241 - "SELECT
1987 + // Amounts of different currencies cannot be summed into one figure, so
1988 + // scope the query to a single currency. Callers that don't care still
1989 + // get coherent numbers because the default is the store currency.
1990 + $currency = is_string( $currency ) ? strtoupper( trim( $currency ) ) : '';
1991 + $extra = '';
1992 + $args = [ $date_format, $instance->get_tablename(), $after, $before ];
1993 +
1994 + if ( '' !== $currency ) {
1995 + $extra .= ' AND currency = %s';
1996 + $args[] = $currency;
1997 + }
1998 +
1999 + if ( $campaign_id > 0 ) {
2000 + $extra .= ' AND campaign_id = %d';
2001 + $args[] = absint( $campaign_id );
2002 + }
2003 +
2004 + // Test and live donations must not be summed together either.
2005 + $payment_mode = is_string( $payment_mode ) ? strtolower( trim( $payment_mode ) ) : '';
2006 + if ( in_array( $payment_mode, [ 'test', 'live' ], true ) ) {
2007 + $extra .= ' AND payment_mode = %s';
2008 + $args[] = $payment_mode;
2009 + }
2010 +
2011 + $sql = "SELECT
1242 2012 DATE_FORMAT(created_at, %s) as period,
1243 2013 COUNT(*) as donation_count,
1244 2014 COALESCE(SUM(amount - refunded_amount), 0) as total_amount
1245 2015 FROM %i
@@ -1245,22 +2015,202 @@
1245 2015 FROM %i
1246 2016 WHERE payment_status IN ('completed', 'partially_refunded')
1247 2017 AND DATE(created_at) >= %s
1248 2018 AND DATE(created_at) <= %s
2019 + {$extra}
1249 2020 GROUP BY period
1250 - ORDER BY period ASC",
1251 - $date_format,
2021 + ORDER BY period ASC";
2022 +
2023 + // 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 is passed through prepare args.
2024 + $results = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
2025 +
2026 + return $results ? $results : [];
2027 + }
2028 +
2029 + /**
2030 + * Count donations recorded through a donation form, in any status.
2031 + *
2032 + * Used to protect a form from permanent deletion while donation rows still
2033 + * reference it, mirroring count_by_campaign()'s role for campaigns.
2034 + *
2035 + * @param int $form_id Donation form post ID.
2036 + * @return int Donation count.
2037 + * @since 1.5.0
2038 + */
2039 + public static function count_by_form( $form_id ) {
2040 + $instance = self::get_instance();
2041 + global $wpdb;
2042 +
2043 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Guard on a destructive action; must read live data.
2044 + $count = $wpdb->get_var(
2045 + $wpdb->prepare(
2046 + 'SELECT COUNT(*) FROM %i WHERE form_id = %d',
1252 2047 $instance->get_tablename(),
1253 - $after,
1254 - $before
2048 + absint( $form_id )
2049 + )
2050 + );
2051 +
2052 + return is_numeric( $count ) ? (int) $count : 0;
2053 + }
2054 +
2055 + /**
2056 + * Get completed entry count and revenue for a single donation form.
2057 + *
2058 + * @param int $form_id Donation form post ID.
2059 + * @return array{entries: int, revenue: float} Form totals.
2060 + * @since 1.5.0
2061 + */
2062 + public static function get_form_stats( $form_id ) {
2063 + $instance = self::get_instance();
2064 + global $wpdb;
2065 +
2066 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live totals; caching would show stale figures.
2067 + $result = $wpdb->get_row(
2068 + $wpdb->prepare(
2069 + 'SELECT COUNT(*) as entries, COALESCE(SUM(amount - refunded_amount), 0) as revenue FROM %i WHERE form_id = %d AND payment_status = %s',
2070 + $instance->get_tablename(),
2071 + absint( $form_id ),
2072 + 'completed'
1255 2073 ),
1256 2074 ARRAY_A
1257 2075 );
1258 2076
1259 - return $results ? $results : [];
2077 + return [
2078 + 'entries' => is_array( $result ) ? (int) ( $result['entries'] ?? 0 ) : 0,
2079 + 'revenue' => is_array( $result ) ? (float) ( $result['revenue'] ?? 0 ) : 0.0,
2080 + ];
1260 2081 }
1261 2082
1262 2083 /**
2084 + * Get entry and revenue totals for several forms in one query.
2085 + *
2086 + * get_form_stats() is a per-form query, so formatting a page of N forms ran
2087 + * N COUNT/SUM queries. This collapses that to one GROUP BY for the page.
2088 + *
2089 + * @param array<int> $form_ids Form IDs to total.
2090 + * @return array<int, array{entries: int, revenue: float}> Totals keyed by form ID; every requested ID is present.
2091 + * @since 1.5.0
2092 + */
2093 + public static function get_form_stats_bulk( array $form_ids ) {
2094 + // intval, not absint: absint( -1 ) is 1, which would silently total a
2095 + // real form the caller never asked about.
2096 + $ids = array_values(
2097 + array_unique(
2098 + array_filter(
2099 + array_map( 'intval', $form_ids ),
2100 + static function ( $id ) {
2101 + return $id > 0;
2102 + }
2103 + )
2104 + )
2105 + );
2106 +
2107 + // Every requested id gets an entry, so callers never have to special-case
2108 + // a form that simply has no donations yet.
2109 + $stats = [];
2110 + foreach ( $ids as $id ) {
2111 + $stats[ $id ] = [
2112 + 'entries' => 0,
2113 + 'revenue' => 0.0,
2114 + ];
2115 + }
2116 +
2117 + if ( empty( $ids ) ) {
2118 + return $stats;
2119 + }
2120 +
2121 + $instance = self::get_instance();
2122 + global $wpdb;
2123 +
2124 + $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
2125 +
2126 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Live totals; placeholders are generated from a count, every value is bound.
2127 + $rows = $wpdb->get_results(
2128 + $wpdb->prepare(
2129 + sprintf(
2130 + 'SELECT form_id, COUNT(*) as entries, COALESCE(SUM(amount - refunded_amount), 0) as revenue FROM %%i WHERE form_id IN ( %s ) AND payment_status = %%s GROUP BY form_id',
2131 + $placeholders
2132 + ),
2133 + array_merge( [ $instance->get_tablename() ], $ids, [ 'completed' ] )
2134 + ),
2135 + ARRAY_A
2136 + );
2137 +
2138 + if ( ! is_array( $rows ) ) {
2139 + return $stats;
2140 + }
2141 +
2142 + foreach ( $rows as $row ) {
2143 + if ( ! is_array( $row ) ) {
2144 + continue;
2145 + }
2146 +
2147 + $form_id = absint( $row['form_id'] ?? 0 );
2148 + if ( ! isset( $stats[ $form_id ] ) ) {
2149 + continue;
2150 + }
2151 +
2152 + $stats[ $form_id ] = [
2153 + 'entries' => (int) ( $row['entries'] ?? 0 ),
2154 + 'revenue' => (float) ( $row['revenue'] ?? 0 ),
2155 + ];
2156 + }
2157 +
2158 + return $stats;
2159 + }
2160 +
2161 + /**
2162 + * Count donations matching the admin-list filters.
2163 + *
2164 + * Mirrors get_admin_list()'s WHERE clause, including the search term. The
2165 + * older get_total_donations_by_status() ignores `$search`, so any searched
2166 + * listing reported the unfiltered total and paginated against it.
2167 + *
2168 + * @param string $status Payment status filter ('all' for no filter).
2169 + * @param int $campaign_id Campaign ID filter (0 for no filter).
2170 + * @param string $search Search term for donor_name, donor_email, or transaction_id.
2171 + * @return int Matching row count.
2172 + * @since 1.5.0
2173 + */
2174 + public static function count_admin_list( $status = 'all', $campaign_id = 0, $search = '' ) {
2175 + $instance = self::get_instance();
2176 + global $wpdb;
2177 +
2178 + $conditions = [ '1=1' ];
2179 + $args = [ $instance->get_tablename() ];
2180 +
2181 + if ( 'all' !== $status ) {
2182 + $conditions[] = 'payment_status = %s';
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'";
2189 + }
2190 +
2191 + if ( $campaign_id > 0 ) {
2192 + $conditions[] = 'campaign_id = %d';
2193 + $args[] = absint( $campaign_id );
2194 + }
2195 +
2196 + if ( ! empty( $search ) ) {
2197 + $conditions[] = '(donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s)';
2198 + $term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
2199 + $args[] = $term;
2200 + $args[] = $term;
2201 + $args[] = $term;
2202 + }
2203 +
2204 + $where = implode( ' AND ', $conditions );
2205 +
2206 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built only from static placeholder fragments; every value is passed through prepare args.
2207 + $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", $args ) );
2208 +
2209 + return is_numeric( $count ) ? (int) $count : 0;
2210 + }
2211 +
2212 + /**
1263 2213 * Get recent donations for a campaign.
1264 2214 *
1265 2215 * @param int $campaign_id Campaign ID.
1266 2216 * @param int $limit Number of donations to retrieve.
@@ -1449,8 +2399,42 @@
1449 2399 * @since 0.0.1
1450 2400 */
1451 2401 public static function get_valid_statuses() {
1452 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';
1453 2437 }
1454 2438
1455 2439 /**
1456 2440 * Add a log entry to a donation.