| @@ -7,8 +7,9 @@ | ||
| 7 | 7 | |
| 8 | 8 | namespace SureDonation\Inc\Database\Tables; |
| 9 | 9 | |
| 10 | 10 | use SureDonation\Inc\Database\Base; |
| 11 | +use SureDonation\Inc\Helper; | |
| 11 | 12 | use SureDonation\Inc\Traits\Get_Instance; |
| 12 | 13 | |
| 13 | 14 | // Exit if accessed directly. |
| 14 | 15 | defined( 'ABSPATH' ) || exit; |
| @@ -34,9 +35,9 @@ | ||
| 34 | 35 | * |
| 35 | 36 | * @var int |
| 36 | 37 | * @since 0.0.1 |
| 37 | 38 | */ |
| 38 | - protected $table_version = 2; | |
| 39 | + protected $table_version = 4; | |
| 39 | 40 | |
| 40 | 41 | /** |
| 41 | 42 | * Valid donor statuses. |
| 42 | 43 | * |
| @@ -84,8 +85,16 @@ | ||
| 84 | 85 | 'phone' => [ |
| 85 | 86 | 'type' => 'string', |
| 86 | 87 | 'default' => '', |
| 87 | 88 | ], |
| 89 | + 'company' => [ | |
| 90 | + 'type' => 'string', | |
| 91 | + 'default' => '', | |
| 92 | + ], | |
| 93 | + 'address' => [ | |
| 94 | + 'type' => 'string', | |
| 95 | + 'default' => '', | |
| 96 | + ], | |
| 88 | 97 | 'user_id' => [ |
| 89 | 98 | 'type' => 'number', |
| 90 | 99 | 'default' => 0, |
| 91 | 100 | ], |
| @@ -122,8 +131,16 @@ | ||
| 122 | 131 | 'stripe_customer_id' => [ |
| 123 | 132 | 'type' => 'string', |
| 124 | 133 | 'default' => '', |
| 125 | 134 | ], |
| 135 | + 'import_source_id' => [ | |
| 136 | + 'type' => 'number', | |
| 137 | + 'default' => 0, | |
| 138 | + ], | |
| 139 | + 'import_source' => [ | |
| 140 | + 'type' => 'string', | |
| 141 | + 'default' => '', | |
| 142 | + ], | |
| 126 | 143 | 'created_at' => [ |
| 127 | 144 | 'type' => 'datetime', |
| 128 | 145 | ], |
| 129 | 146 | 'updated_at' => [ |
| @@ -150,8 +167,10 @@ | ||
| 150 | 167 | 'donor_tags LONGTEXT', |
| 151 | 168 | 'donor_status VARCHAR(20) NOT NULL', |
| 152 | 169 | 'donor_data LONGTEXT', |
| 153 | 170 | 'stripe_customer_id VARCHAR(255) DEFAULT NULL', |
| 171 | + 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0', | |
| 172 | + 'import_source VARCHAR(20) NOT NULL DEFAULT \'\'', | |
| 154 | 173 | 'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP', |
| 155 | 174 | 'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', |
| 156 | 175 | 'INDEX idx_email (email)', |
| 157 | 176 | 'INDEX idx_user (user_id)', |
| @@ -156,12 +175,40 @@ | ||
| 156 | 175 | 'INDEX idx_email (email)', |
| 157 | 176 | 'INDEX idx_user (user_id)', |
| 158 | 177 | 'INDEX idx_total (total_donated)', |
| 159 | 178 | 'INDEX idx_status (donor_status)', |
| 179 | + 'INDEX idx_import_source (import_source_id, import_source)', | |
| 160 | 180 | ]; |
| 161 | 181 | } |
| 162 | 182 | |
| 163 | 183 | /** |
| 184 | + * New columns added across versions. | |
| 185 | + * | |
| 186 | + * Version 3 added company/address; version 4 added the | |
| 187 | + * source-agnostic pair `import_source_id` + `import_source` used by | |
| 188 | + * the migration tool. | |
| 189 | + * | |
| 190 | + * {@inheritDoc} | |
| 191 | + * | |
| 192 | + * @since 1.0.0 | |
| 193 | + */ | |
| 194 | + public function get_new_columns_definition() { | |
| 195 | + // Keep migration defaults consistent with the schema's runtime | |
| 196 | + // defaults (the column definitions above use 'default' => ''). Mixing | |
| 197 | + // NOT NULL DEFAULT '' for one column with DEFAULT NULL for another | |
| 198 | + // produces silent divergence at the data layer — a future | |
| 199 | + // `WHERE address = ''` filter would miss legacy rows that landed as | |
| 200 | + // NULL from the migration. | |
| 201 | + return [ | |
| 202 | + 'company VARCHAR(255) NOT NULL DEFAULT \'\' AFTER phone', | |
| 203 | + 'address TEXT NOT NULL DEFAULT \'\' AFTER company', | |
| 204 | + 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER stripe_customer_id', | |
| 205 | + 'import_source VARCHAR(20) NOT NULL DEFAULT \'\' AFTER import_source_id', | |
| 206 | + 'INDEX idx_import_source (import_source_id, import_source)', | |
| 207 | + ]; | |
| 208 | + } | |
| 209 | + | |
| 210 | + /** | |
| 164 | 211 | * Add a new donor record. |
| 165 | 212 | * |
| 166 | 213 | * @param array<mixed> $data Donor data to insert. |
| 167 | 214 | * @return int|false The donor ID on success, false on error. |
| @@ -399,12 +446,13 @@ | ||
| 399 | 446 | /** |
| 400 | 447 | * Get or create donor by email. |
| 401 | 448 | * |
| 402 | 449 | * @param string $email Donor email. |
| 403 | - * @param string $name Donor name. | |
| 404 | - * @param string $phone Donor phone. | |
| 450 | + * @param string $name Donor name. Stored when creating a new donor, or backfilled onto an existing donor only when its stored name is empty; never overwrites a populated value. | |
| 451 | + * @param string $phone Donor phone. Stored when creating a new donor, or backfilled onto an existing donor only when its stored phone is empty; never overwrites a populated value. | |
| 405 | 452 | * @return int|false Donor ID or false on error. |
| 406 | 453 | * @since 0.0.1 |
| 454 | + * @since 1.4.0 A subsequent donation no longer overwrites an existing donor's name/phone; missing values are backfilled, populated ones are left intact. | |
| 407 | 455 | */ |
| 408 | 456 | public static function get_or_create( $email, $name = '', $phone = '' ) { |
| 409 | 457 | if ( empty( $email ) ) { |
| 410 | 458 | return false; |
| @@ -412,32 +460,36 @@ | ||
| 412 | 460 | |
| 413 | 461 | $existing = self::get_by_email( $email ); |
| 414 | 462 | |
| 415 | 463 | if ( $existing ) { |
| 416 | - // Update name/phone if provided and different. | |
| 464 | + $existing_id = isset( $existing['id'] ) && is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0; | |
| 465 | + | |
| 466 | + // Backfill name/phone only when the stored value is empty — a later | |
| 467 | + // donation never overwrites a populated donor name/phone. This closes | |
| 468 | + // the unauthenticated-tampering vector (an attacker who knows a | |
| 469 | + // donor's email cannot change that donor's existing name/phone on a | |
| 470 | + // bare, unverified match) while still letting genuinely missing | |
| 471 | + // details fill in from a later donation — e.g. an optional-name | |
| 472 | + // gateway, or a phone field mapped after the donor's first donation. | |
| 473 | + // The name/phone entered for each donation are always captured on the | |
| 474 | + // donation row regardless, and admins can edit a donor directly via | |
| 475 | + // the donor management endpoints. | |
| 417 | 476 | $updates = []; |
| 418 | - | |
| 419 | - if ( ! empty( $name ) && $name !== $existing['name'] ) { | |
| 477 | + if ( ! empty( $name ) && '' === (string) ( $existing['name'] ?? '' ) ) { | |
| 420 | 478 | $updates['name'] = $name; |
| 421 | 479 | } |
| 422 | - | |
| 423 | - if ( ! empty( $phone ) && $phone !== $existing['phone'] ) { | |
| 480 | + if ( ! empty( $phone ) && '' === (string) ( $existing['phone'] ?? '' ) ) { | |
| 424 | 481 | $updates['phone'] = $phone; |
| 425 | 482 | } |
| 426 | - | |
| 427 | - if ( ! empty( $updates ) && isset( $existing['id'] ) ) { | |
| 428 | - $existing_id = is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0; | |
| 429 | - if ( $existing_id > 0 ) { | |
| 430 | - self::update( $existing_id, $updates ); | |
| 431 | - } | |
| 483 | + if ( ! empty( $updates ) && $existing_id > 0 ) { | |
| 484 | + self::update( $existing_id, $updates ); | |
| 432 | 485 | } |
| 433 | 486 | |
| 434 | - $existing_id = isset( $existing['id'] ) && is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0; | |
| 435 | 487 | return $existing_id > 0 ? $existing_id : false; |
| 436 | 488 | } |
| 437 | 489 | |
| 438 | 490 | // Create new donor. |
| 439 | - return self::add( | |
| 491 | + $donor_id = self::add( | |
| 440 | 492 | [ |
| 441 | 493 | 'email' => sanitize_email( $email ), |
| 442 | 494 | 'name' => sanitize_text_field( $name ), |
| 443 | 495 | 'phone' => sanitize_text_field( $phone ), |
| @@ -443,13 +495,174 @@ | ||
| 443 | 495 | 'phone' => sanitize_text_field( $phone ), |
| 444 | 496 | 'first_donation_date' => current_time( 'mysql' ), |
| 445 | 497 | ] |
| 446 | 498 | ); |
| 499 | + | |
| 500 | + if ( $donor_id ) { | |
| 501 | + // Auto-create or link WP user for this donor. | |
| 502 | + self::maybe_link_wp_user( $donor_id, sanitize_email( $email ), sanitize_text_field( $name ) ); | |
| 503 | + } | |
| 504 | + | |
| 505 | + return $donor_id; | |
| 447 | 506 | } |
| 448 | 507 | |
| 449 | 508 | /** |
| 509 | + * Link a donor to an existing WP user, or create a new WP user if none exists. | |
| 510 | + * | |
| 511 | + * @param int $donor_id Donor ID. | |
| 512 | + * @param string $email Donor email. | |
| 513 | + * @param string $name Donor name. | |
| 514 | + * @return void | |
| 515 | + * @since 1.0.0 | |
| 516 | + */ | |
| 517 | + public static function maybe_link_wp_user( $donor_id, $email, $name = '' ) { | |
| 518 | + if ( empty( $donor_id ) || empty( $email ) ) { | |
| 519 | + return; | |
| 520 | + } | |
| 521 | + | |
| 522 | + // Check if donor already has a linked user. | |
| 523 | + $donor = self::get( $donor_id ); | |
| 524 | + if ( $donor && ! empty( $donor['user_id'] ) && $donor['user_id'] > 0 ) { | |
| 525 | + return; | |
| 526 | + } | |
| 527 | + | |
| 528 | + // Check if a WP user already exists with this email. | |
| 529 | + $existing_user = get_user_by( 'email', $email ); | |
| 530 | + | |
| 531 | + if ( $existing_user ) { | |
| 532 | + self::update( $donor_id, [ 'user_id' => $existing_user->ID ] ); | |
| 533 | + return; | |
| 534 | + } | |
| 535 | + | |
| 536 | + // Creating a brand-new WordPress account for a donor is gated behind an | |
| 537 | + // explicit, default-off setting. On public (nopriv) donation paths this | |
| 538 | + // prevents unsolicited account creation and new-user notification emails | |
| 539 | + // for attacker-supplied emails. Linking to an already-existing user | |
| 540 | + // (handled above) is always allowed. | |
| 541 | + $donor_settings = Helper::get_suredonation_option( 'donor_settings', [] ); | |
| 542 | + if ( empty( $donor_settings['create_wp_user'] ) ) { | |
| 543 | + return; | |
| 544 | + } | |
| 545 | + | |
| 546 | + // Create a new WP user. | |
| 547 | + $username = sanitize_user( $email, true ); | |
| 548 | + $password = wp_generate_password( 24, true, true ); | |
| 549 | + | |
| 550 | + $user_data = [ | |
| 551 | + 'user_login' => $username, | |
| 552 | + 'user_email' => $email, | |
| 553 | + 'user_pass' => $password, | |
| 554 | + 'role' => 'suredonation_donor', | |
| 555 | + ]; | |
| 556 | + | |
| 557 | + // Split name into first/last if provided. | |
| 558 | + if ( ! empty( $name ) ) { | |
| 559 | + $parts = explode( ' ', $name, 2 ); | |
| 560 | + $user_data['first_name'] = $parts[0]; | |
| 561 | + $user_data['last_name'] = $parts[1] ?? ''; | |
| 562 | + $user_data['display_name'] = $name; | |
| 563 | + } | |
| 564 | + | |
| 565 | + /** | |
| 566 | + * Filter the user data before creating a WP user for a donor. | |
| 567 | + * | |
| 568 | + * Return false to prevent user creation. | |
| 569 | + * | |
| 570 | + * @param array $user_data WP user data array for wp_insert_user(). | |
| 571 | + * @param int $donor_id Donor ID. | |
| 572 | + * @param string $email Donor email. | |
| 573 | + * @since 1.0.0 | |
| 574 | + */ | |
| 575 | + $user_data = apply_filters( 'suredonation_new_donor_user_data', $user_data, $donor_id, $email ); | |
| 576 | + | |
| 577 | + if ( false === $user_data || ! is_array( $user_data ) ) { | |
| 578 | + return; | |
| 579 | + } | |
| 580 | + | |
| 581 | + $user_id = wp_insert_user( $user_data ); | |
| 582 | + | |
| 583 | + if ( is_wp_error( $user_id ) ) { | |
| 584 | + return; | |
| 585 | + } | |
| 586 | + | |
| 587 | + // Link the WP user to the donor. | |
| 588 | + self::update( $donor_id, [ 'user_id' => $user_id ] ); | |
| 589 | + | |
| 590 | + // Send new user notification email. | |
| 591 | + wp_new_user_notification( $user_id, null, 'user' ); | |
| 592 | + | |
| 593 | + /** | |
| 594 | + * Fires after a WP user is created and linked to a donor. | |
| 595 | + * | |
| 596 | + * @param int $user_id WP user ID. | |
| 597 | + * @param int $donor_id Donor ID. | |
| 598 | + * @param string $email Donor email. | |
| 599 | + * @since 1.0.0 | |
| 600 | + */ | |
| 601 | + do_action( 'suredonation_donor_user_created', $user_id, $donor_id, $email ); | |
| 602 | + } | |
| 603 | + | |
| 604 | + /** | |
| 605 | + * Record a donation against a donor's aggregates exactly once. | |
| 606 | + * | |
| 607 | + * Two paths complete the same Stripe donation — the client-side confirm | |
| 608 | + * (`complete_donation()`) and the `payment_intent.succeeded` webhook — and | |
| 609 | + * neither knows whether the other got there first. The webhook applies no | |
| 610 | + * "still pending" guard, so calling record_donation() from both would double | |
| 611 | + * a donor's total; calling it from neither (until now) left the totals stale | |
| 612 | + * on every donation whose webhook never arrived. | |
| 613 | + * | |
| 614 | + * Keyed on the donation, not the donor, so a second genuine gift still | |
| 615 | + * counts. Marked before the write: a duplicated total is harder to notice | |
| 616 | + * and impossible to unpick, whereas a missed one is visible against the | |
| 617 | + * donation list and recomputable. | |
| 618 | + * | |
| 619 | + * @param int $donor_id Donor row ID. | |
| 620 | + * @param float $amount Donation amount. | |
| 621 | + * @param int $donation_id Donation row ID this call is for. | |
| 622 | + * @return bool True when this call recorded it, false when already recorded or invalid. | |
| 623 | + * @since 1.6.0 | |
| 624 | + */ | |
| 625 | + public static function record_donation_once( $donor_id, $amount, $donation_id ) { | |
| 626 | + $donation_id = absint( $donation_id ); | |
| 627 | + | |
| 628 | + if ( $donation_id <= 0 ) { | |
| 629 | + return false; | |
| 630 | + } | |
| 631 | + | |
| 632 | + $key = 'suredonation_donor_recorded_' . $donation_id; | |
| 633 | + | |
| 634 | + if ( get_transient( $key ) ) { | |
| 635 | + return false; | |
| 636 | + } | |
| 637 | + | |
| 638 | + // Marked before the write, so two racers cannot both get through on a | |
| 639 | + // read that saw nothing. | |
| 640 | + set_transient( $key, true, WEEK_IN_SECONDS ); | |
| 641 | + | |
| 642 | + $recorded = (bool) self::record_donation( $donor_id, $amount ); | |
| 643 | + | |
| 644 | + if ( ! $recorded ) { | |
| 645 | + // record_donation() refuses a non-positive amount or a donor row | |
| 646 | + // that no longer exists (the privacy eraser can remove one), and | |
| 647 | + // returns false without writing anything. Leaving the marker up | |
| 648 | + // after that would be worse than not having it: the gateway | |
| 649 | + // webhook retry is this row's safety net, and it would find the | |
| 650 | + // marker and skip, so the donation would never reach the donor's | |
| 651 | + // totals at all. | |
| 652 | + delete_transient( $key ); | |
| 653 | + } | |
| 654 | + | |
| 655 | + return $recorded; | |
| 656 | + } | |
| 657 | + | |
| 658 | + /** | |
| 450 | 659 | * Update donor statistics after a donation. |
| 451 | 660 | * |
| 661 | + * Unconditional: it takes no status and no donation id, so it cannot tell a | |
| 662 | + * repeat call for the same donation from a second gift. Callers that can be | |
| 663 | + * reached twice for one donation should use record_donation_once(). | |
| 664 | + * | |
| 452 | 665 | * @param int $donor_id Donor ID. |
| 453 | 666 | * @param float $amount Donation amount. |
| 454 | 667 | * @return int|false Number of rows updated or false on error. |
| 455 | 668 | * @since 0.0.1 |
| @@ -650,6 +863,345 @@ | ||
| 650 | 863 | |
| 651 | 864 | $result = self::update( $donor_id, [ 'stripe_customer_id' => '' ] ); |
| 652 | 865 | |
| 653 | 866 | return false !== $result; |
| 867 | + } | |
| 868 | + | |
| 869 | + /** | |
| 870 | + * Get the Stripe customer ID for a donor on a specific connected account. | |
| 871 | + * | |
| 872 | + * Reads the per-account map stored in `donor_data['stripe_customers']`. | |
| 873 | + * Falls back to the legacy single `stripe_customer_id` column when the | |
| 874 | + * account is the site default, so pre-multi-account donors keep working. | |
| 875 | + * | |
| 876 | + * @param string $email Donor email. | |
| 877 | + * @param string $account_id Stripe account id (`acct_…`). | |
| 878 | + * @param bool $is_default Whether this is the site default account. | |
| 879 | + * @return string Customer ID, or '' when none is stored. | |
| 880 | + * @since 1.3.0 | |
| 881 | + */ | |
| 882 | + public static function get_stripe_customer_id_for_account( $email, $account_id, $is_default = false ) { | |
| 883 | + if ( empty( $email ) || empty( $account_id ) ) { | |
| 884 | + return ''; | |
| 885 | + } | |
| 886 | + | |
| 887 | + $donor = self::get_by_email( $email ); | |
| 888 | + if ( ! $donor ) { | |
| 889 | + return ''; | |
| 890 | + } | |
| 891 | + | |
| 892 | + $donor_data = isset( $donor['donor_data'] ) && is_array( $donor['donor_data'] ) ? $donor['donor_data'] : []; | |
| 893 | + $map = isset( $donor_data['stripe_customers'] ) && is_array( $donor_data['stripe_customers'] ) ? $donor_data['stripe_customers'] : []; | |
| 894 | + | |
| 895 | + if ( isset( $map[ $account_id ] ) && is_string( $map[ $account_id ] ) && '' !== $map[ $account_id ] ) { | |
| 896 | + return $map[ $account_id ]; | |
| 897 | + } | |
| 898 | + | |
| 899 | + // Legacy fallback: the single column holds the default account's customer. | |
| 900 | + if ( $is_default && ! empty( $donor['stripe_customer_id'] ) && is_string( $donor['stripe_customer_id'] ) ) { | |
| 901 | + return $donor['stripe_customer_id']; | |
| 902 | + } | |
| 903 | + | |
| 904 | + return ''; | |
| 905 | + } | |
| 906 | + | |
| 907 | + /** | |
| 908 | + * Store the Stripe customer ID for a donor on a specific connected account. | |
| 909 | + * | |
| 910 | + * Writes the per-account map in `donor_data['stripe_customers']` and mirrors | |
| 911 | + * the default account's customer into the legacy `stripe_customer_id` column | |
| 912 | + * so back-compat readers keep working. | |
| 913 | + * | |
| 914 | + * @param string $email Donor email. | |
| 915 | + * @param string $account_id Stripe account id (`acct_…`). | |
| 916 | + * @param string $customer_id Stripe customer ID. | |
| 917 | + * @param bool $is_default Whether this is the site default account. | |
| 918 | + * @return bool True on success, false on failure. | |
| 919 | + * @since 1.3.0 | |
| 920 | + */ | |
| 921 | + public static function set_stripe_customer_id_for_account( $email, $account_id, $customer_id, $is_default = false ) { | |
| 922 | + if ( empty( $email ) || empty( $account_id ) || empty( $customer_id ) ) { | |
| 923 | + return false; | |
| 924 | + } | |
| 925 | + | |
| 926 | + $donor = self::get_by_email( $email ); | |
| 927 | + if ( ! $donor || empty( $donor['id'] ) ) { | |
| 928 | + return false; | |
| 929 | + } | |
| 930 | + $donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0; | |
| 931 | + if ( $donor_id <= 0 ) { | |
| 932 | + return false; | |
| 933 | + } | |
| 934 | + | |
| 935 | + $donor_data = isset( $donor['donor_data'] ) && is_array( $donor['donor_data'] ) ? $donor['donor_data'] : []; | |
| 936 | + $map = isset( $donor_data['stripe_customers'] ) && is_array( $donor_data['stripe_customers'] ) ? $donor_data['stripe_customers'] : []; | |
| 937 | + | |
| 938 | + $map[ $account_id ] = sanitize_text_field( $customer_id ); | |
| 939 | + $donor_data['stripe_customers'] = $map; | |
| 940 | + | |
| 941 | + $update = [ 'donor_data' => $donor_data ]; | |
| 942 | + if ( $is_default ) { | |
| 943 | + $update['stripe_customer_id'] = sanitize_text_field( $customer_id ); | |
| 944 | + } | |
| 945 | + | |
| 946 | + $result = self::update( $donor_id, $update ); | |
| 947 | + | |
| 948 | + return false !== $result; | |
| 949 | + } | |
| 950 | + | |
| 951 | + /** | |
| 952 | + * Clear the stored Stripe customer ID for a donor on a specific account. | |
| 953 | + * | |
| 954 | + * Used when a cached customer id is no longer valid on that account | |
| 955 | + * (deleted in Stripe, or a test/live mismatch). | |
| 956 | + * | |
| 957 | + * @param string $email Donor email. | |
| 958 | + * @param string $account_id Stripe account id (`acct_…`). | |
| 959 | + * @param bool $is_default Whether this is the site default account. | |
| 960 | + * @return bool True on success, false on failure. | |
| 961 | + * @since 1.3.0 | |
| 962 | + */ | |
| 963 | + public static function clear_stripe_customer_id_for_account( $email, $account_id, $is_default = false ) { | |
| 964 | + if ( empty( $email ) || empty( $account_id ) ) { | |
| 965 | + return false; | |
| 966 | + } | |
| 967 | + | |
| 968 | + $donor = self::get_by_email( $email ); | |
| 969 | + if ( ! $donor || empty( $donor['id'] ) ) { | |
| 970 | + return false; | |
| 971 | + } | |
| 972 | + $donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0; | |
| 973 | + if ( $donor_id <= 0 ) { | |
| 974 | + return false; | |
| 975 | + } | |
| 976 | + | |
| 977 | + $donor_data = isset( $donor['donor_data'] ) && is_array( $donor['donor_data'] ) ? $donor['donor_data'] : []; | |
| 978 | + $map = isset( $donor_data['stripe_customers'] ) && is_array( $donor_data['stripe_customers'] ) ? $donor_data['stripe_customers'] : []; | |
| 979 | + unset( $map[ $account_id ] ); | |
| 980 | + $donor_data['stripe_customers'] = $map; | |
| 981 | + | |
| 982 | + $update = [ 'donor_data' => $donor_data ]; | |
| 983 | + if ( $is_default ) { | |
| 984 | + $update['stripe_customer_id'] = ''; | |
| 985 | + } | |
| 986 | + | |
| 987 | + $result = self::update( $donor_id, $update ); | |
| 988 | + | |
| 989 | + return false !== $result; | |
| 990 | + } | |
| 991 | + | |
| 992 | + /** | |
| 993 | + * Get donors for admin listing with optional filters. | |
| 994 | + * | |
| 995 | + * @param string $search Search term for name, email, or phone. | |
| 996 | + * @param int $campaign_id Campaign ID filter (0 for no filter). | |
| 997 | + * @param string $status Donor status filter ('all' for no filter). | |
| 998 | + * @param int $limit Number of records to return. | |
| 999 | + * @param int $offset Offset for pagination. | |
| 1000 | + * @param string $orderby Column to order by. | |
| 1001 | + * @param string $order Order direction (ASC or DESC). | |
| 1002 | + * @param string $after Start date filter (Y-m-d). | |
| 1003 | + * @param string $before End date filter (Y-m-d). | |
| 1004 | + * @return array<mixed> Array of donors. | |
| 1005 | + * @since 1.0.0 | |
| 1006 | + */ | |
| 1007 | + public static function get_admin_list( $search = '', $campaign_id = 0, $status = 'all', $limit = 20, $offset = 0, $orderby = 'created_at', $order = 'DESC', $after = '', $before = '' ) { | |
| 1008 | + $instance = self::get_instance(); | |
| 1009 | + global $wpdb; | |
| 1010 | + | |
| 1011 | + $donors_table = $instance->get_tablename(); | |
| 1012 | + $donations_table = $wpdb->prefix . 'suredonation_donations'; | |
| 1013 | + | |
| 1014 | + // Validate orderby column. | |
| 1015 | + if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) { | |
| 1016 | + $orderby = 'created_at'; | |
| 1017 | + } | |
| 1018 | + | |
| 1019 | + // Validate order direction. | |
| 1020 | + $order = strtoupper( $order ); | |
| 1021 | + if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) { | |
| 1022 | + $order = 'DESC'; | |
| 1023 | + } | |
| 1024 | + | |
| 1025 | + $conditions = self::build_admin_list_conditions( $search, $campaign_id, $status, $after, $before ); | |
| 1026 | + $where = $conditions['where']; | |
| 1027 | + $query_args = $conditions['args']; | |
| 1028 | + | |
| 1029 | + if ( $conditions['has_campaign'] ) { | |
| 1030 | + $order_col = 'd.' . $orderby; | |
| 1031 | + | |
| 1032 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic query with validated conditions. | |
| 1033 | + $results = $wpdb->get_results( | |
| 1034 | + $wpdb->prepare( | |
| 1035 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built with prepare-safe conditions, $order_col and $order are validated against whitelists. | |
| 1036 | + "SELECT DISTINCT d.* FROM %i d INNER JOIN %i don ON d.id = don.donor_id {$where} ORDER BY {$order_col} {$order} LIMIT %d, %d", | |
| 1037 | + array_merge( [ $donors_table, $donations_table ], $query_args, [ absint( $offset ), absint( $limit ) ] ) | |
| 1038 | + ), | |
| 1039 | + ARRAY_A | |
| 1040 | + ); | |
| 1041 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber | |
| 1042 | + } else { | |
| 1043 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic query with validated conditions. | |
| 1044 | + $results = $wpdb->get_results( | |
| 1045 | + $wpdb->prepare( | |
| 1046 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built with prepare-safe conditions, $orderby and $order are validated against whitelists. | |
| 1047 | + "SELECT * FROM %i {$where} ORDER BY {$orderby} {$order} LIMIT %d, %d", | |
| 1048 | + array_merge( [ $donors_table ], $query_args, [ absint( $offset ), absint( $limit ) ] ) | |
| 1049 | + ), | |
| 1050 | + ARRAY_A | |
| 1051 | + ); | |
| 1052 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber | |
| 1053 | + } | |
| 1054 | + | |
| 1055 | + if ( ! $results || ! is_array( $results ) ) { | |
| 1056 | + return []; | |
| 1057 | + } | |
| 1058 | + | |
| 1059 | + return array_map( [ $instance, 'decode_by_datatype' ], $results ); | |
| 1060 | + } | |
| 1061 | + | |
| 1062 | + /** | |
| 1063 | + * Get total donors count with filters. | |
| 1064 | + * | |
| 1065 | + * @param string $search Search term for name, email, or phone. | |
| 1066 | + * @param int $campaign_id Campaign ID filter (0 for no filter). | |
| 1067 | + * @param string $status Donor status filter ('all' for no filter). | |
| 1068 | + * @param string $after Start date filter (Y-m-d). | |
| 1069 | + * @param string $before End date filter (Y-m-d). | |
| 1070 | + * @return int Total count. | |
| 1071 | + * @since 1.0.0 | |
| 1072 | + */ | |
| 1073 | + public static function get_total_donors_filtered( $search = '', $campaign_id = 0, $status = 'all', $after = '', $before = '' ) { | |
| 1074 | + $instance = self::get_instance(); | |
| 1075 | + global $wpdb; | |
| 1076 | + | |
| 1077 | + $donors_table = $instance->get_tablename(); | |
| 1078 | + $donations_table = $wpdb->prefix . 'suredonation_donations'; | |
| 1079 | + | |
| 1080 | + $conditions = self::build_admin_list_conditions( $search, $campaign_id, $status, $after, $before ); | |
| 1081 | + $where = $conditions['where']; | |
| 1082 | + $query_args = $conditions['args']; | |
| 1083 | + | |
| 1084 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Data changes frequently, caching would show stale counts. | |
| 1085 | + if ( $conditions['has_campaign'] ) { | |
| 1086 | + $count = $wpdb->get_var( | |
| 1087 | + $wpdb->prepare( | |
| 1088 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built with prepare-safe conditions. | |
| 1089 | + "SELECT COUNT(DISTINCT d.id) FROM %i d INNER JOIN %i don ON d.id = don.donor_id {$where}", | |
| 1090 | + array_merge( [ $donors_table, $donations_table ], $query_args ) | |
| 1091 | + ) | |
| 1092 | + ); | |
| 1093 | + } elseif ( ! empty( $query_args ) ) { | |
| 1094 | + $count = $wpdb->get_var( | |
| 1095 | + $wpdb->prepare( | |
| 1096 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built with prepare-safe conditions. | |
| 1097 | + "SELECT COUNT(*) FROM %i {$where}", | |
| 1098 | + array_merge( [ $donors_table ], $query_args ) | |
| 1099 | + ) | |
| 1100 | + ); | |
| 1101 | + } else { | |
| 1102 | + $count = $wpdb->get_var( | |
| 1103 | + $wpdb->prepare( | |
| 1104 | + 'SELECT COUNT(*) FROM %i', | |
| 1105 | + $donors_table | |
| 1106 | + ) | |
| 1107 | + ); | |
| 1108 | + } | |
| 1109 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber | |
| 1110 | + | |
| 1111 | + return is_numeric( $count ) ? (int) $count : 0; | |
| 1112 | + } | |
| 1113 | + | |
| 1114 | + /** | |
| 1115 | + * Get aggregate donor statistics. | |
| 1116 | + * | |
| 1117 | + * @return array{total_donors: int, total_donated: float, average_donation: float} Aggregate stats. | |
| 1118 | + * @since 1.0.0 | |
| 1119 | + */ | |
| 1120 | + public static function get_aggregate_stats() { | |
| 1121 | + $instance = self::get_instance(); | |
| 1122 | + global $wpdb; | |
| 1123 | + $table = $instance->get_tablename(); | |
| 1124 | + | |
| 1125 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 1126 | + $row = $wpdb->get_row( | |
| 1127 | + $wpdb->prepare( | |
| 1128 | + 'SELECT COUNT(*) AS total_donors, COALESCE(SUM(total_donated), 0) AS total_donated, COALESCE(SUM(donation_count), 0) AS total_donation_count FROM %i', | |
| 1129 | + $table | |
| 1130 | + ), | |
| 1131 | + ARRAY_A | |
| 1132 | + ); | |
| 1133 | + | |
| 1134 | + $total_donors = is_numeric( $row['total_donors'] ?? 0 ) ? (int) $row['total_donors'] : 0; | |
| 1135 | + $total_donated = is_numeric( $row['total_donated'] ?? 0 ) ? (float) $row['total_donated'] : 0.0; | |
| 1136 | + $total_donation_count = is_numeric( $row['total_donation_count'] ?? 0 ) ? (int) $row['total_donation_count'] : 0; | |
| 1137 | + $average_donation = $total_donation_count > 0 ? $total_donated / $total_donation_count : 0.0; | |
| 1138 | + | |
| 1139 | + return [ | |
| 1140 | + 'total_donors' => $total_donors, | |
| 1141 | + 'total_donated' => $total_donated, | |
| 1142 | + 'average_donation' => round( $average_donation, 2 ), | |
| 1143 | + ]; | |
| 1144 | + } | |
| 1145 | + | |
| 1146 | + /** | |
| 1147 | + * Build WHERE conditions and prepare args for admin list queries. | |
| 1148 | + * | |
| 1149 | + * @param string $search Search term for name, email, or phone. | |
| 1150 | + * @param int $campaign_id Campaign ID filter (0 for no filter). | |
| 1151 | + * @param string $status Donor status filter ('all' for no filter). | |
| 1152 | + * @param string $after Start date filter (Y-m-d). | |
| 1153 | + * @param string $before End date filter (Y-m-d). | |
| 1154 | + * @return array{where: string, args: array<mixed>, has_campaign: bool} Query parts. | |
| 1155 | + * @since 1.0.0 | |
| 1156 | + */ | |
| 1157 | + private static function build_admin_list_conditions( $search, $campaign_id, $status, $after = '', $before = '' ) { | |
| 1158 | + global $wpdb; | |
| 1159 | + | |
| 1160 | + $has_search = ! empty( $search ); | |
| 1161 | + $has_campaign = $campaign_id > 0; | |
| 1162 | + $has_status = 'all' !== $status && ! empty( $status ) && in_array( $status, self::$valid_statuses, true ); | |
| 1163 | + | |
| 1164 | + $conditions = []; | |
| 1165 | + $args = []; | |
| 1166 | + | |
| 1167 | + if ( $has_campaign ) { | |
| 1168 | + $conditions[] = 'don.campaign_id = %d'; | |
| 1169 | + $args[] = absint( $campaign_id ); | |
| 1170 | + } | |
| 1171 | + | |
| 1172 | + if ( $has_status ) { | |
| 1173 | + $col_prefix = $has_campaign ? 'd.' : ''; | |
| 1174 | + $conditions[] = $col_prefix . 'donor_status = %s'; | |
| 1175 | + $args[] = sanitize_text_field( $status ); | |
| 1176 | + } | |
| 1177 | + | |
| 1178 | + if ( $has_search ) { | |
| 1179 | + $col_prefix = $has_campaign ? 'd.' : ''; | |
| 1180 | + $search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%'; | |
| 1181 | + $conditions[] = '(' . $col_prefix . 'name LIKE %s OR ' . $col_prefix . 'email LIKE %s OR ' . $col_prefix . 'phone LIKE %s)'; | |
| 1182 | + $args[] = $search_term; | |
| 1183 | + $args[] = $search_term; | |
| 1184 | + $args[] = $search_term; | |
| 1185 | + } | |
| 1186 | + | |
| 1187 | + if ( ! empty( $after ) ) { | |
| 1188 | + $col_prefix = $has_campaign ? 'd.' : ''; | |
| 1189 | + $conditions[] = $col_prefix . 'last_donation_date >= %s'; | |
| 1190 | + $args[] = sanitize_text_field( $after ) . ' 00:00:00'; | |
| 1191 | + } | |
| 1192 | + | |
| 1193 | + if ( ! empty( $before ) ) { | |
| 1194 | + $col_prefix = $has_campaign ? 'd.' : ''; | |
| 1195 | + $conditions[] = $col_prefix . 'last_donation_date <= %s'; | |
| 1196 | + $args[] = sanitize_text_field( $before ) . ' 23:59:59'; | |
| 1197 | + } | |
| 1198 | + | |
| 1199 | + $where = ! empty( $conditions ) ? 'WHERE ' . implode( ' AND ', $conditions ) : ''; | |
| 1200 | + | |
| 1201 | + return [ | |
| 1202 | + 'where' => $where, | |
| 1203 | + 'args' => $args, | |
| 1204 | + 'has_campaign' => $has_campaign, | |
| 1205 | + ]; | |
| 654 | 1206 | } |
| 655 | 1207 | } |
