← All changes | includes/admin/reports/class-charitable-reports.php +132 -38 1.8.12.1 → 1.8.13 View file →

		@@ -1443,18 +1443,34 @@
1443	1443	$donations_by_day = array();
1444	1444	$days = $this->get_days_between_dates( $this->start_date, $this->end_date );
1445	1445	$refunded_statuses = array( 'charitable-refunded' );
1446	1446
	1447	+ /**
	1448	+ * Two grouped queries for the whole range, rather than two queries per calendar
	1449	+ * day. The old per-day loop made the cost a function of how long the organisation
	1450	+ * had existed rather than how much data it had, so a 24-year range issued ~18,000
	1451	+ * queries and hit max_execution_time before returning anything.
	1452	+ *
	1453	+ * The range is taken from $days rather than from $this->start_date /
	1454	+ * $this->end_date, because get_days_between_dates() normalises a falsy date to
	1455	+ * "now". Reading the bounds back off $days guarantees the query covers exactly
	1456	+ * the days this loop is about to render, however those dates were normalised.
	1457	+ */
	1458	+ $day_keys = array_keys( $days );
	1459	+ $range_start = reset( $day_keys );
	1460	+ $range_end = end( $day_keys );
	1461	+
	1462	+ $completed_by_day = $campaign_donations_table->get_donations_summary_by_day_range( $range_start, $range_end, array( 'charitable-completed', 'charitable-refunded' ) );
	1463	+ $refunded_by_day = $campaign_donations_table->get_donations_summary_by_day_range( $range_start, $range_end, $refunded_statuses );
	1464	+
1447	1465	foreach ( $days as $day_unformatted => $day_label ) {
1448	1466
1449		- $donation_today_completed = $campaign_donations_table->get_donations_summary_by_period( gmdate( 'Y-m-d', strtotime( $day_unformatted ) ), array( 'charitable-completed', 'charitable-refunded' ) );
1450		- $donation_today_refunded = $campaign_donations_table->get_donations_summary_by_period( gmdate( 'Y-m-d', strtotime( $day_unformatted ) ), $refunded_statuses );
	1467	+ $donation_day = gmdate( 'Y-m-d', strtotime( $day_unformatted ) );
1451	1468
1452		- $donation_day = gmdate( 'Y-m-d', strtotime( $day_unformatted ) );
1453	1469	$donations_by_day[ $donation_day ]['label'] = gmdate( 'F d, Y', strtotime( $day_unformatted ) );
1454		- $donations_by_day[ $donation_day ]['amount'] = $donation_today_completed->amount;
	1470	+ $donations_by_day[ $donation_day ]['amount'] = isset( $completed_by_day[ $donation_day ] ) ? $completed_by_day[ $donation_day ]->amount : 0;
1455	1471	$donations_by_day[ $donation_day ]['donors'] = 0;
1456		- $donations_by_day[ $donation_day ]['refunds'] = $donation_today_refunded->amount;
	1472	+ $donations_by_day[ $donation_day ]['refunds'] = isset( $refunded_by_day[ $donation_day ] ) ? $refunded_by_day[ $donation_day ]->amount : 0;
1457	1473	$donations_by_day[ $donation_day ]['net'] = 0;
1458	1474	}
1459	1475
1460	1476	// Go through the array and group the amounts and number of donors by day.
		@@ -2831,50 +2847,58 @@
2831	2847
2832	2848	$refunds_by_day = array();
2833	2849	$refunded_donations_total_amount = 0;
2834	2850	$refunded_donations_total_count = 0;
2835		- $processed_donation_ids = array(); // Track unique donation IDs to avoid double-counting
2836	2851
2837	2852	$days = $this->get_days_between_dates( $this->start_date, $this->end_date );
2838	2853
2839		- // First, calculate totals from all refunded donations (regardless of day matching)
2840		- // This ensures count and amount are consistent
	2854	+ /**
	2855	+ * Bucket the refunds by day once, up front, then read from that.
	2856	+ *
	2857	+ * The day loop below used to iterate the day LABEL ('M d') rather than the
	2858	+ * 'Y-m-d' key, so strtotime() stamped every bucket with the CURRENT year. The
	2859	+ * Refunds column and Net in the breakdown table therefore read 0 for any range
	2860	+ * outside the current year, and for a multi-year range refunds from different
	2861	+ * years merged into a single month-day bucket. (The totals were already
	2862	+ * computed up front, so the Refunds card itself was correct.)
	2863	+ *
	2864	+ * Buckets on post_date, matching the WHERE clause above and the donation series
	2865	+ * in get_donations_by_day(). Bucketing on post_date_gmt while filtering on
	2866	+ * post_date could put a late-evening refund in a day outside the range on a
	2867	+ * non-UTC site.
	2868	+ *
	2869	+ * The GROUP BY p.ID in the query already guarantees one row per donation, so no
	2870	+ * per-day dedup is needed. The previous "$donation_id > 0" guard also silently
	2871	+ * dropped a refunded donation that has no campaign_donations row from the totals;
	2872	+ * such a refund is now counted, with a zero amount, so it stays visible.
	2873	+ */
	2874	+ $refunds_by_date = array();
	2875	+
2841	2876	if ( ! empty( $refunded_donations ) ) {
2842	2877	foreach ( $refunded_donations as $refunded_donation ) {
2843		- // Only count each unique donation ID once
2844		- $donation_id = isset( $refunded_donation->donation_id ) ? intval( $refunded_donation->donation_id ) : 0;
2845		- if ( $donation_id > 0 && ! in_array( $donation_id, $processed_donation_ids, true ) ) {
2846		- $refunded_donations_total_amount += floatval( $refunded_donation->amount );
2847		- $refunded_donations_total_count++;
2848		- $processed_donation_ids[] = $donation_id;
	2878	+ $refund_day = gmdate( 'Y-m-d', strtotime( $refunded_donation->post_date ) );
	2879	+
	2880	+ if ( ! isset( $refunds_by_date[ $refund_day ] ) ) {
	2881	+ $refunds_by_date[ $refund_day ] = array(
	2882	+ 'amount' => 0,
	2883	+ 'count' => 0,
	2884	+ );
2849	2885	}
	2886	+
	2887	+ $refunds_by_date[ $refund_day ]['amount'] += floatval( $refunded_donation->amount );
	2888	+ ++$refunds_by_date[ $refund_day ]['count'];
	2889	+
	2890	+ $refunded_donations_total_amount += floatval( $refunded_donation->amount );
	2891	+ ++$refunded_donations_total_count;
2850	2892	}
2851	2893	}
2852	2894
2853		- // Then, organize by day for the breakdown
2854		- foreach ( $days as $day ) {
2855		- $donation_day = gmdate( 'Y-m-d', strtotime( $day ) );
2856		- $refunded_donation_amount = 0;
2857		- $refunded_donation_count = 0;
2858		- $processed_day_donation_ids = array(); // Track unique donation IDs per day
2859		- if ( ! empty( $refunded_donations ) ) :
2860		- foreach ( $refunded_donations as $refunded_donation ) {
2861		- // if the refunded donation post date is the same day as the donation_day, add it to the refunded total.
2862		- $refund_date = gmdate( 'Y-m-d', strtotime( $refunded_donation->post_date_gmt ) );
2863		- if ( $refund_date === $donation_day ) {
2864		- $donation_id = isset( $refunded_donation->donation_id ) ? intval( $refunded_donation->donation_id ) : 0;
2865		- // Only count each unique donation ID once per day
2866		- if ( $donation_id > 0 && ! in_array( $donation_id, $processed_day_donation_ids, true ) ) {
2867		- $refunded_donation_amount += floatval( $refunded_donation->amount );
2868		- ++$refunded_donation_count;
2869		- $processed_day_donation_ids[] = $donation_id;
2870		- }
2871		- }
2872		- }
2873		- endif;
2874		- $refunds_by_day[ $donation_day ]['label'] = gmdate( 'F d, Y', strtotime( $day ) );
2875		- $refunds_by_day[ $donation_day ]['amount'] = $refunded_donation_amount;
2876		- $refunds_by_day[ $donation_day ]['donors'] = $refunded_donation_count;
	2895	+ foreach ( $days as $day_unformatted => $day_label ) {
	2896	+ $donation_day = gmdate( 'Y-m-d', strtotime( $day_unformatted ) );
	2897	+
	2898	+ $refunds_by_day[ $donation_day ]['label'] = gmdate( 'F d, Y', strtotime( $day_unformatted ) );
	2899	+ $refunds_by_day[ $donation_day ]['amount'] = isset( $refunds_by_date[ $donation_day ] ) ? $refunds_by_date[ $donation_day ]['amount'] : 0;
	2900	+ $refunds_by_day[ $donation_day ]['donors'] = isset( $refunds_by_date[ $donation_day ] ) ? $refunds_by_date[ $donation_day ]['count'] : 0;
2877	2901	}
2878	2902
2879	2903	return array(
2880	2904	'donations' => $refunded_donations,
		@@ -3605,8 +3629,69 @@
3605	3629	return false;
3606	3630	}
3607	3631
3608	3632	/**
	3633	+ * Get the date of the earliest donation on the site.
	3634	+ *
	3635	+ * Used to build the "All Time" preset in the reporting date picker, so that
	3636	+ * lifetime figures are one click away rather than a hand-typed range.
	3637	+ *
	3638	+ * Every valid donation status is considered, not just completed ones, because
	3639	+ * the reports can be filtered by status and a narrower floor would hide the
	3640	+ * earliest refunded or cancelled donation.
	3641	+ *
	3642	+ * @since 1.8.12.4
	3643	+ *
	3644	+ * @return string Date in Y/m/d format. Falls back to one month ago when the
	3645	+ * site has no donations yet.
	3646	+ */
	3647	+ public function get_earliest_donation_date() {
	3648	+
	3649	+ $cached = get_transient( 'charitable-reports-earliest-donation-date' );
	3650	+
	3651	+ if ( false !== $cached ) {
	3652	+ return $cached;
	3653	+ }
	3654	+
	3655	+ global $wpdb;
	3656	+
	3657	+ $statuses = array_keys( charitable_get_valid_donation_statuses() );
	3658	+
	3659	+ // `charitable_donation_statuses` is a public filter, and an empty list would
	3660	+ // build `IN ( )`. Nothing to measure in that case, so use the fallback.
	3661	+ if ( empty( $statuses ) ) {
	3662	+ return gmdate( 'Y/m/d', strtotime( '-1 month' ) );
	3663	+ }
	3664	+
	3665	+ $placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
	3666	+
	3667	+ $earliest = $wpdb->get_var( // phpcs:ignore
	3668	+ $wpdb->prepare(
	3669	+ "SELECT MIN( post_date ) FROM {$wpdb->posts}
	3670	+ WHERE post_type = 'donation'
	3671	+ AND post_status IN ( {$placeholders} )", // phpcs:ignore
	3672	+ $statuses
	3673	+ )
	3674	+ );
	3675	+
	3676	+ if ( empty( $earliest ) || '0000-00-00 00:00:00' === $earliest ) {
	3677	+ /*
	3678	+ * Deliberately not cached. A site with no donations today may be one
	3679	+ * import away from four years of history, and a cached fallback would
	3680	+ * leave "All Time" starting a month ago for a full day afterwards -
	3681	+ * which is the exact wrong-figure trap this preset exists to close.
	3682	+ */
	3683	+ return gmdate( 'Y/m/d', strtotime( '-1 month' ) );
	3684	+ }
	3685	+
	3686	+ $date = gmdate( 'Y/m/d', strtotime( $earliest ) );
	3687	+
	3688	+ set_transient( 'charitable-reports-earliest-donation-date', $date, DAY_IN_SECONDS );
	3689	+
	3690	+ return $date;
	3691	+ }
	3692	+
	3693	+ /**
3609	3694	* Get localized strings.
3610	3695	*
3611	3696	* @since 1.8.1
3612	3697	*
		@@ -3665,9 +3750,18 @@
3665	3750	'payment_keys' => (array) array_reverse( $this->payment_keys ),
3666	3751	),
3667	3752	'default_start_date' => current_time( gmdate( 'Y-m-d', strtotime( '-1 month' ) ) ),
3668	3753	'default_end_date' => current_time( gmdate( 'Y-m-d' ) ),
	3754	+ 'earliest_donation_date' => $this->get_earliest_donation_date(),
	3755	+ // The date picker uses each preset's label as its object key, so the
	3756	+ // label has to travel to JS rather than being written there. It rides
	3757	+ // this same array as `earliest_donation_date` on purpose: the
	3758	+ // reporting JS contains no wp.i18n calls, so a second localization
	3759	+ // mechanism for one string would be the inconsistent choice.
	3760	+ 'all_time_label' => __( 'All Time', 'charitable' ),
3669	3761	'advanced_reports' => $charitable_reports_advanced_report_strings,
	3762	+ 'i18n_report_timeout' => __( 'This report took too long to load and was stopped by the server. The figures have been cleared so they are not mistaken for the range you asked for. Try a shorter date range.', 'charitable' ),
	3763	+ 'i18n_report_error' => __( 'This report could not be loaded. The figures have been cleared so they are not mistaken for the range you asked for. Please try again.', 'charitable' ),
3670	3764	);
3671	3765
3672	3766	$strings = apply_filters( 'charitable_reporting_strings', $strings );
3673	3767