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 +204 -32 1.5.1 → 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 = 6;
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
@@ -175,8 +192,12 @@
175 192 'donor_comment' => [
176 193 'type' => 'string',
177 194 'default' => '',
178 195 ],
196 + 'donor_comment_status' => [
197 + 'type' => 'string',
198 + 'default' => 'approved',
199 + ],
179 200 'receipt_sent' => [
180 201 'type' => 'boolean',
181 202 'default' => false,
182 203 ],
@@ -252,8 +273,9 @@
252 273 'subscription_id VARCHAR(255) NOT NULL',
253 274 'subscription_status VARCHAR(30) NOT NULL',
254 275 'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
255 276 'donor_comment TEXT',
277 + 'donor_comment_status VARCHAR(20) NOT NULL DEFAULT \'approved\'',
256 278 'receipt_sent TINYINT(1) NOT NULL DEFAULT 0',
257 279 'receipt_pdf_url VARCHAR(255) NOT NULL',
258 280 'donation_data LONGTEXT',
259 281 'log LONGTEXT',
@@ -289,10 +311,27 @@
289 311 * added `stripe_account_id` so donations record which connected Stripe
290 312 * account processed them (multiple Stripe accounts support); version 6
291 313 * added `import_provenance` — an indexed `(donation_post_id, source_campaign_id)`
292 314 * key the Charitable importer dedupes on with a single indexed lookup per
293 - * row, instead of scanning + JSON-decoding every prior imported row per batch.
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.
294 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 + *
295 334 * {@inheritDoc}
296 335 *
297 336 * @since 1.0.0
298 337 */
@@ -304,8 +343,9 @@
304 343 'import_source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER referer_url',
305 344 'import_source VARCHAR(20) NOT NULL DEFAULT \'\' AFTER import_source_id',
306 345 'import_provenance VARCHAR(64) NOT NULL DEFAULT \'\' AFTER import_source',
307 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',
308 348 'INDEX idx_subscription (subscription_id)',
309 349 'INDEX idx_subscription_status (subscription_status)',
310 350 'INDEX idx_parent_subscription (parent_subscription_id)',
311 351 'INDEX idx_import_source (import_source_id, import_source)',
@@ -701,30 +741,31 @@
701 741
702 742 $is_anonymous = ! empty( $donation['is_anonymous'] );
703 743
704 744 $payload = [
705 - 'id' => isset( $donation['id'] ) ? absint( Helper::get_string_value( $donation['id'] ) ) : 0,
706 - 'campaign_id' => isset( $donation['campaign_id'] ) ? absint( Helper::get_string_value( $donation['campaign_id'] ) ) : 0,
707 - 'form_id' => isset( $donation['form_id'] ) ? absint( Helper::get_string_value( $donation['form_id'] ) ) : 0,
708 - 'donor_id' => isset( $donation['donor_id'] ) ? absint( Helper::get_string_value( $donation['donor_id'] ) ) : 0,
709 - 'donor_name' => Helper::get_string_value( $donation['donor_name'] ?? '' ),
710 - 'donor_email' => Helper::get_string_value( $donation['donor_email'] ?? '' ),
711 - 'donor_phone' => Helper::get_string_value( $donation['donor_phone'] ?? '' ),
712 - 'amount' => Helper::get_float_value( $donation['amount'] ?? 0 ),
713 - 'fees_covered' => Helper::get_float_value( $donation['fees_covered'] ?? 0 ),
714 - 'refunded_amount' => Helper::get_float_value( $donation['refunded_amount'] ?? 0 ),
715 - 'currency' => Helper::get_string_value( $donation['currency'] ?? '' ),
716 - 'gateway' => Helper::get_string_value( $donation['gateway'] ?? '' ),
717 - 'payment_status' => Helper::get_string_value( $donation['payment_status'] ?? '' ),
718 - 'payment_mode' => Helper::get_string_value( $donation['payment_mode'] ?? '' ),
719 - 'donation_type' => Helper::get_string_value( $donation['donation_type'] ?? '' ),
720 - 'transaction_id' => Helper::get_string_value( $donation['transaction_id'] ?? '' ),
721 - 'subscription_id' => Helper::get_string_value( $donation['subscription_id'] ?? '' ),
722 - 'subscription_status' => Helper::get_string_value( $donation['subscription_status'] ?? '' ),
723 - 'donor_comment' => Helper::get_string_value( $donation['donor_comment'] ?? '' ),
724 - 'is_anonymous' => $is_anonymous,
725 - 'created_at' => Helper::get_string_value( $donation['created_at'] ?? '' ),
726 - 'updated_at' => Helper::get_string_value( $donation['updated_at'] ?? '' ),
745 + 'id' => isset( $donation['id'] ) ? absint( Helper::get_string_value( $donation['id'] ) ) : 0,
746 + 'campaign_id' => isset( $donation['campaign_id'] ) ? absint( Helper::get_string_value( $donation['campaign_id'] ) ) : 0,
747 + 'form_id' => isset( $donation['form_id'] ) ? absint( Helper::get_string_value( $donation['form_id'] ) ) : 0,
748 + 'donor_id' => isset( $donation['donor_id'] ) ? absint( Helper::get_string_value( $donation['donor_id'] ) ) : 0,
749 + 'donor_name' => Helper::get_string_value( $donation['donor_name'] ?? '' ),
750 + 'donor_email' => Helper::get_string_value( $donation['donor_email'] ?? '' ),
751 + 'donor_phone' => Helper::get_string_value( $donation['donor_phone'] ?? '' ),
752 + 'amount' => Helper::get_float_value( $donation['amount'] ?? 0 ),
753 + 'fees_covered' => Helper::get_float_value( $donation['fees_covered'] ?? 0 ),
754 + 'refunded_amount' => Helper::get_float_value( $donation['refunded_amount'] ?? 0 ),
755 + 'currency' => Helper::get_string_value( $donation['currency'] ?? '' ),
756 + 'gateway' => Helper::get_string_value( $donation['gateway'] ?? '' ),
757 + 'payment_status' => Helper::get_string_value( $donation['payment_status'] ?? '' ),
758 + 'payment_mode' => Helper::get_string_value( $donation['payment_mode'] ?? '' ),
759 + 'donation_type' => Helper::get_string_value( $donation['donation_type'] ?? '' ),
760 + 'transaction_id' => Helper::get_string_value( $donation['transaction_id'] ?? '' ),
761 + 'subscription_id' => Helper::get_string_value( $donation['subscription_id'] ?? '' ),
762 + 'subscription_status' => Helper::get_string_value( $donation['subscription_status'] ?? '' ),
763 + 'donor_comment' => Helper::get_string_value( $donation['donor_comment'] ?? '' ),
764 + 'donor_comment_status' => Helper::get_string_value( $donation['donor_comment_status'] ?? '' ),
765 + 'is_anonymous' => $is_anonymous,
766 + 'created_at' => Helper::get_string_value( $donation['created_at'] ?? '' ),
767 + 'updated_at' => Helper::get_string_value( $donation['updated_at'] ?? '' ),
727 768 ];
728 769
729 770 /**
730 771 * Filter the curated donation payload passed to every integration hook.
@@ -1374,12 +1415,24 @@
1374 1415 return array_map( [ $instance, 'decode_by_datatype' ], $results );
1375 1416 }
1376 1417
1377 1418 /**
1378 - * Delete a donation record.
1419 + * Delete a donation record and its receipt PDF.
1379 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 + *
1380 1433 * @param int $donation_id Donation ID.
1381 - * @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.
1382 1435 * @since 0.0.1
1383 1436 */
1384 1437 public static function delete( $donation_id ) {
1385 1438 if ( empty( $donation_id ) ) {
@@ -1385,9 +1438,19 @@
1385 1438 if ( empty( $donation_id ) ) {
1386 1439 return false;
1387 1440 }
1388 1441
1389 - 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 ] );
1390 1453 }
1391 1454
1392 1455 /**
1393 1456 * Get donations by donor email.
@@ -1674,12 +1737,14 @@
1674 1737 *
1675 1738 * @param string $currency Currency code ('' for no filter).
1676 1739 * @param string $payment_mode 'test' or 'live' ('' for no filter).
1677 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.
1678 1743 * @return string SQL fragment beginning with " AND ", or '' when unscoped.
1679 1744 * @since 1.5.0
1680 1745 */
1681 - private static function scope_fragment( $currency, $payment_mode, array &$args ) {
1746 + private static function scope_fragment( $currency, $payment_mode, array &$args, $after = '', $before = '' ) {
1682 1747 $extra = '';
1683 1748
1684 1749 $currency = is_string( $currency ) ? strtoupper( trim( $currency ) ) : '';
1685 1750 if ( '' !== $currency ) {
@@ -1692,8 +1757,22 @@
1692 1757 $extra .= ' AND payment_mode = %s';
1693 1758 $args[] = $payment_mode;
1694 1759 }
1695 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 +
1696 1775 return $extra;
1697 1776 }
1698 1777 /**
1699 1778 * Get global dashboard statistics.
@@ -1699,17 +1778,19 @@
1699 1778 * Get global dashboard statistics.
1700 1779 *
1701 1780 * @param string $currency Currency code to scope to ('' for no filter).
1702 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.
1703 1784 * @return array{total_donations: string, total_raised: string, unique_donors: string, average_donation: string, largest_donation: string} Dashboard statistics.
1704 1785 * @since 0.0.1
1705 1786 */
1706 - public static function get_dashboard_stats( $currency = '', $payment_mode = '' ) {
1787 + public static function get_dashboard_stats( $currency = '', $payment_mode = '', $after = '', $before = '' ) {
1707 1788 $instance = self::get_instance();
1708 1789 global $wpdb;
1709 1790
1710 1791 $args = [ $instance->get_tablename() ];
1711 - $extra = self::scope_fragment( $currency, $payment_mode, $args );
1792 + $extra = self::scope_fragment( $currency, $payment_mode, $args, $after, $before );
1712 1793
1713 1794 $sql = "SELECT
1714 1795 COUNT(*) as total_donations,
1715 1796 COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
@@ -1770,17 +1851,18 @@
1770 1851 *
1771 1852 * @param int $limit Number of campaigns to retrieve.
1772 1853 * @param string $currency Currency code to scope to ('' for no filter).
1773 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.
1774 1856 * @return array<int, array{campaign_id: string, donation_count: string, total_raised: string, unique_donors: string}> Array of top campaigns with stats.
1775 1857 * @since 0.0.1
1776 1858 */
1777 - public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '' ) {
1859 + public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '', $after = '' ) {
1778 1860 $instance = self::get_instance();
1779 1861 global $wpdb;
1780 1862
1781 1863 $args = [ $instance->get_tablename(), SUREDONATION_POST_TYPE ];
1782 - $extra = self::scope_fragment( $currency, $payment_mode, $args );
1864 + $extra = self::scope_fragment( $currency, $payment_mode, $args, $after );
1783 1865 $args[] = absint( $limit );
1784 1866
1785 1867 // The join is what makes LIMIT meaningful: orphaned campaign_ids (post
1786 1868 // deleted, donations kept) still carry donations, so filtering them in
@@ -1808,8 +1890,64 @@
1808 1890 return $results ? $results : [];
1809 1891 }
1810 1892
1811 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 + /**
1812 1950 * Get donation trends over time.
1813 1951 *
1814 1952 * @param string $after Start date (ISO format).
1815 1953 * @param string $before End date (ISO format).
@@ -2261,8 +2399,42 @@
2261 2399 * @since 0.0.1
2262 2400 */
2263 2401 public static function get_valid_statuses() {
2264 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';
2265 2437 }
2266 2438
2267 2439 /**
2268 2440 * Add a log entry to a donation.