← All changes | inc/database/tables/donations.php +106 -8 1.6.0 → 1.6.1 View file →

		@@ -9,8 +9,9 @@
9	9
10	10	use SureDonation\Inc\Campaigns\Campaign_Stats;
11	11	use SureDonation\Inc\Database\Base;
12	12	use SureDonation\Inc\Helper;
	13	+use SureDonation\Inc\Pdf\Receipt_Generator;
13	14	use SureDonation\Inc\Traits\Get_Instance;
14	15
15	16	// Exit if accessed directly.
16	17	defined( 'ABSPATH' ) || exit;
		@@ -1414,12 +1415,24 @@
1414	1415	return array_map( [ $instance, 'decode_by_datatype' ], $results );
1415	1416	}
1416	1417
1417	1418	/**
1418		- * Delete a donation record.
	1419	+ * Delete a donation record and its receipt PDF.
1419	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	+ *
1420	1433	* @param int $donation_id Donation ID.
1421		- * @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.
1422	1435	* @since 0.0.1
1423	1436	*/
1424	1437	public static function delete( $donation_id ) {
1425	1438	if ( empty( $donation_id ) ) {
		@@ -1425,9 +1438,19 @@
1425	1438	if ( empty( $donation_id ) ) {
1426	1439	return false;
1427	1440	}
1428	1441
1429		- 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 ] );
1430	1453	}
1431	1454
1432	1455	/**
1433	1456	* Get donations by donor email.
		@@ -1714,12 +1737,14 @@
1714	1737	*
1715	1738	* @param string $currency Currency code ('' for no filter).
1716	1739	* @param string $payment_mode 'test' or 'live' ('' for no filter).
1717	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.
1718	1743	* @return string SQL fragment beginning with " AND ", or '' when unscoped.
1719	1744	* @since 1.5.0
1720	1745	*/
1721		- private static function scope_fragment( $currency, $payment_mode, array &$args ) {
	1746	+ private static function scope_fragment( $currency, $payment_mode, array &$args, $after = '', $before = '' ) {
1722	1747	$extra = '';
1723	1748
1724	1749	$currency = is_string( $currency ) ? strtoupper( trim( $currency ) ) : '';
1725	1750	if ( '' !== $currency ) {
		@@ -1732,8 +1757,22 @@
1732	1757	$extra .= ' AND payment_mode = %s';
1733	1758	$args[] = $payment_mode;
1734	1759	}
1735	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	+
1736	1775	return $extra;
1737	1776	}
1738	1777	/**
1739	1778	* Get global dashboard statistics.
		@@ -1739,17 +1778,19 @@
1739	1778	* Get global dashboard statistics.
1740	1779	*
1741	1780	* @param string $currency Currency code to scope to ('' for no filter).
1742	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.
1743	1784	* @return array{total_donations: string, total_raised: string, unique_donors: string, average_donation: string, largest_donation: string} Dashboard statistics.
1744	1785	* @since 0.0.1
1745	1786	*/
1746		- public static function get_dashboard_stats( $currency = '', $payment_mode = '' ) {
	1787	+ public static function get_dashboard_stats( $currency = '', $payment_mode = '', $after = '', $before = '' ) {
1747	1788	$instance = self::get_instance();
1748	1789	global $wpdb;
1749	1790
1750	1791	$args = [ $instance->get_tablename() ];
1751		- $extra = self::scope_fragment( $currency, $payment_mode, $args );
	1792	+ $extra = self::scope_fragment( $currency, $payment_mode, $args, $after, $before );
1752	1793
1753	1794	$sql = "SELECT
1754	1795	COUNT(*) as total_donations,
1755	1796	COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
		@@ -1810,17 +1851,18 @@
1810	1851	*
1811	1852	* @param int $limit Number of campaigns to retrieve.
1812	1853	* @param string $currency Currency code to scope to ('' for no filter).
1813	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.
1814	1856	* @return array<int, array{campaign_id: string, donation_count: string, total_raised: string, unique_donors: string}> Array of top campaigns with stats.
1815	1857	* @since 0.0.1
1816	1858	*/
1817		- public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '' ) {
	1859	+ public static function get_top_campaigns( $limit = 5, $currency = '', $payment_mode = '', $after = '' ) {
1818	1860	$instance = self::get_instance();
1819	1861	global $wpdb;
1820	1862
1821	1863	$args = [ $instance->get_tablename(), SUREDONATION_POST_TYPE ];
1822		- $extra = self::scope_fragment( $currency, $payment_mode, $args );
	1864	+ $extra = self::scope_fragment( $currency, $payment_mode, $args, $after );
1823	1865	$args[] = absint( $limit );
1824	1866
1825	1867	// The join is what makes LIMIT meaningful: orphaned campaign_ids (post
1826	1868	// deleted, donations kept) still carry donations, so filtering them in
		@@ -1839,8 +1881,64 @@
1839	1881	WHERE payment_status IN ('completed', 'partially_refunded')
1840	1882	{$extra}
1841	1883	GROUP BY d.campaign_id
1842	1884	ORDER BY total_raised DESC
	1885	+ LIMIT %d";
	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	+
	1890	+ return $results ? $results : [];
	1891	+ }
	1892	+
	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
1843	1941	LIMIT %d";
1844	1942
1845	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.
1846	1944	$results = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );