← All changes | includes/admin/reports/class-charitable-reports.php +138 -44 1.8.11.1 → 1.8.13 View file →

		@@ -1125,9 +1125,9 @@
1125	1125	foreach ( $donor_data as $index => $data ) :
1126	1126
1127	1127	$donor_id = isset( $data->donor_id ) ? intval( $data->donor_id ) : 0;
1128	1128	$donor_email = isset( $data->email ) ? esc_html( $data->email ) : '';
1129		- $donor_avatar = ( is_email( $donor_email ) ) ? get_avatar_url( $donor_email ) : charitable()->get_path( 'assets', false ) . '/images/misc/placeholder-avatar-small.jpg';
	1129	+ $donor_avatar = ( is_email( $donor_email ) ) ? get_avatar_url( $donor_email ) : charitable()->get_path( 'assets', false ) . 'images/misc/placeholder-avatar-small.jpg';
1130	1130	$donor_name = esc_html( $data->first_name ) . ' ' . esc_html( $data->last_name );
1131	1131	$donor_number_donations = isset( $data->total_count_donations ) ? intval( $data->total_count_donations ) : 0;
1132	1132	$donor_number_campaigns = isset( $data->total_count_campaigns ) ? intval( $data->total_count_campaigns ) : 0;
1133	1133	$donor_average = $donor_number_donations > 0 ? $data->total_amount / $donor_number_donations : 0;
		@@ -1189,9 +1189,9 @@
1189	1189	<td class="donated column-date" data-colname="<?php echo esc_html__( 'Date', 'charitable' ); ?>"><?php echo esc_html( $last_donation_date ); ?></td>
1190	1190	<?php
1191	1191	$display = '<td class="donated column-actions " data-colname="Actions">';
1192	1192	if ( $action_label !== false ) {
1193		- $display .= '<a class="charitable-campaign-action-button" title="' . $action_label . '" href="' . esc_url( $action_link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . '/images/icons/eye.svg" width="14" height="14" alt="' . $action_label . '" /></a>';
	1193	+ $display .= '<a class="charitable-campaign-action-button" title="' . $action_label . '" href="' . esc_url( $action_link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . 'images/icons/eye.svg" width="14" height="14" alt="' . $action_label . '" /></a>';
1194	1194	}
1195	1195	$display .= '</td>';
1196	1196
1197	1197	echo $display; // phpcs:ignore
		@@ -1210,9 +1210,9 @@
1210	1210	<td class="donated column-last-donation" data-colname="Last Donation"><?php echo $last_donation_html; // phpcs:ignore ?></td>
1211	1211	<?php
1212	1212	$display = '<td class="donated column-actions" data-colname="Actions">';
1213	1213	if ( $action_label !== false ) {
1214		- $display .= '<a class="charitable-campaign-action-button" title="' . $action_label . '" href="' . esc_url( $action_link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . '/images/icons/eye.svg" width="14" height="14" alt="' . $action_label . '" /</a>';
	1214	+ $display .= '<a class="charitable-campaign-action-button" title="' . $action_label . '" href="' . esc_url( $action_link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . 'images/icons/eye.svg" width="14" height="14" alt="' . $action_label . '" /</a>';
1215	1215	}
1216	1216	$display .= '</td>';
1217	1217
1218	1218	echo $display; // phpcs:ignore
		@@ -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,
		@@ -3447,9 +3471,9 @@
3447	3471	foreach ( $donors_data as $donor_id => $data ) :
3448	3472
3449	3473	$donor_name = $data->donor_first_name . ' ' . $data->donor_last_name;
3450	3474	$donor_email = $data->donor_email;
3451		- $donor_avatar = ( is_email( $donor_email ) ) ? get_avatar_url( $donor_email ) : charitable()->get_path( 'assets', false ) . '/images/misc/placeholder-avatar-small.jpg';
	3475	+ $donor_avatar = ( is_email( $donor_email ) ) ? get_avatar_url( $donor_email ) : charitable()->get_path( 'assets', false ) . 'images/misc/placeholder-avatar-small.jpg';
3452	3476	$total_donation_count_compare_to = $data->total_donation_count_compare_to;
3453	3477	$total_donation_amount_compare_to = number_format( $data->total_donation_amount_compare_to, 2 );
3454	3478	$total_donation_count_compare_from = $data->total_donation_count_compare_from;
3455	3479	$total_donation_amount_compare_from = number_format( $data->total_donation_amount_compare_from, 2 );
		@@ -3473,13 +3497,13 @@
3473	3497
3474	3498	if ( $show_actions ) :
3475	3499	if ( $link && charitable_is_pro() ) :
3476	3500
3477		- $actions_display = '<td class="donated column-actions " data-colname="Actions"><a class="charitable-campaign-action-button" title="' . esc_html__( 'View User', 'charitable' ) . '" href="' . esc_url( $link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . '/images/icons/eye.svg" width="14" height="14" alt="' . esc_html__( 'View User', 'charitable' ) . '" /></td>';
	3501	+ $actions_display = '<td class="donated column-actions " data-colname="Actions"><a class="charitable-campaign-action-button" title="' . esc_html__( 'View User', 'charitable' ) . '" href="' . esc_url( $link ) . '" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . 'images/icons/eye.svg" width="14" height="14" alt="' . esc_html__( 'View User', 'charitable' ) . '" /></td>';
3478	3502
3479	3503	else :
3480	3504
3481		- $actions_display = '<td class="donated column-actions " data-colname="Actions"><a class="charitable-campaign-action-button" title="' . esc_html__( 'View User', 'charitable' ) . '" href="#" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . '/images/icons/eye.svg" width="14" height="14" alt="' . esc_html__( 'View User', 'charitable' ) . '" /></td>';
	3505	+ $actions_display = '<td class="donated column-actions " data-colname="Actions"><a class="charitable-campaign-action-button" title="' . esc_html__( 'View User', 'charitable' ) . '" href="#" target="_blank"><img src="' . charitable()->get_path( 'assets', false ) . 'images/icons/eye.svg" width="14" height="14" alt="' . esc_html__( 'View User', 'charitable' ) . '" /></td>';
3482	3506
3483	3507	endif;
3484	3508	endif;
3485	3509
		@@ -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