← All changes | includes/payments/class-payment-stats.php +210 -3 4.16.3 → 4.18.0 View file →

		@@ -27,8 +27,9 @@
27	27
28	28	/**
29	29	* Retrieve sale stats
30	30	*
	31	+ * @since 4.18.0 Count donations with one SQL query instead of loading every ID
31	32	* @since 1.0
32	33	* @access public
33	34	*
34	35	* @param $form_id int The donation form to retrieve stats for. If false, gets stats for all forms
		@@ -64,8 +65,14 @@
64	65	if ( ! empty( $form_id ) ) {
65	66	$args['give_forms'] = $form_id;
66	67	}
67	68
	69	+ $count = $this->count_donations_in_sql( $args );
	70	+
	71	+ if ( null !== $count ) {
	72	+ return $count;
	73	+ }
	74	+
68	75	/* @var Give_Payments_Query $payments */
69	76	$payments = new Give_Payments_Query( $args );
70	77	$payments = $payments->get_payments();
71	78
		@@ -75,8 +82,9 @@
75	82
76	83	/**
77	84	* Retrieve earning stats
78	85	*
	86	+ * @since 4.18.0 Sum donation totals with one SQL query instead of loading every ID and formatting each amount
79	87	* @since 1.0
80	88	* @access public
81	89	*
82	90	* @param $form_id int The donation form to retrieve stats for. If false, gets stats for all forms.
		@@ -137,12 +145,17 @@
137	145
138	146	if ( false === $earnings ) {
139	147
140	148	$this->timestamp = false;
141		- $payments = new Give_Payments_Query( $args );
142		- $payments = $payments->get_payments();
143		- $earnings = 0;
	149	+ $payments = [];
	150	+ $earnings = $this->sum_earnings_in_sql( $args );
144	151
	152	+ if ( null === $earnings ) {
	153	+ $payments = new Give_Payments_Query( $args );
	154	+ $payments = $payments->get_payments();
	155	+ $earnings = 0;
	156	+ }
	157	+
145	158	if ( ! empty( $payments ) ) {
146	159	$donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
147	160	$query = "SELECT {$donation_id_col} as id, meta_value as total
148	161	FROM {$wpdb->donationmeta}
		@@ -260,8 +273,202 @@
260	273
261	274	// return earnings
262	275	return $key;
263	276
	277	+ }
	278	+
	279	+ /**
	280	+ * Sum donation totals in one query instead of loading every matching donation ID into PHP
	281	+ * and formatting each amount. Uses the Currency Switcher base amount when a donation has one,
	282	+ * which is what its `give_donation_amount` filter returns for stats.
	283	+ *
	284	+ * Returns null when the query args contain something this method does not translate, or when an
	285	+ * add-on filters `give_donation_amount`, so the caller falls back to the per-donation loop.
	286	+ *
	287	+ * @since 4.18.0
	288	+ *
	289	+ * @param array $args Give_Payments_Query arguments.
	290	+ *
	291	+ * @return float|null
	292	+ */
	293	+ private function sum_earnings_in_sql( array $args ) {
	294	+ global $wpdb;
	295	+
	296	+ if ( $this->has_donation_amount_filter() ) {
	297	+ return null;
	298	+ }
	299	+
	300	+ $where = $this->stats_where_sql( $args );
	301	+
	302	+ if ( null === $where ) {
	303	+ return null;
	304	+ }
	305	+
	306	+ $donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
	307	+
	308	+ $sql = "SELECT SUM( COALESCE( NULLIF( base.meta_value, '' ), total.meta_value ) + 0 )
	309	+ FROM {$wpdb->posts} AS p
	310	+ INNER JOIN {$wpdb->donationmeta} AS total ON total.{$donation_id_col} = p.ID AND total.meta_key = '_give_payment_total'
	311	+ LEFT JOIN {$wpdb->donationmeta} AS base ON base.{$donation_id_col} = p.ID AND base.meta_key = '_give_cs_base_amount'
	312	+ WHERE p.post_type = 'give_payment' {$where}";
	313	+
	314	+ return (float) $wpdb->get_var( $sql );
	315	+ }
	316	+
	317	+ /**
	318	+ * Count matching donations in one query instead of loading every ID into PHP.
	319	+ *
	320	+ * @since 4.18.0
	321	+ *
	322	+ * @param array $args Give_Payments_Query arguments.
	323	+ *
	324	+ * @return int|null
	325	+ */
	326	+ private function count_donations_in_sql( array $args ) {
	327	+ global $wpdb;
	328	+
	329	+ $where = $this->stats_where_sql( $args );
	330	+
	331	+ if ( null === $where ) {
	332	+ return null;
	333	+ }
	334	+
	335	+ return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} AS p WHERE p.post_type = 'give_payment' {$where}" );
	336	+ }
	337	+
	338	+ /**
	339	+ * Add-ons that change amounts through `give_donation_amount` (Fee Recovery, Currency Switcher) need
	340	+ * the per-donation loop for earnings so their callbacks run. Counts are unaffected and stay in SQL. Core itself always hooks the deprecated filter
	341	+ * mapping there, so that one callback does not count.
	342	+ *
	343	+ * @since 4.18.0
	344	+ *
	345	+ * @return bool
	346	+ */
	347	+ private function has_donation_amount_filter() {
	348	+ global $wp_filter;
	349	+
	350	+ if ( has_filter( 'give_payment_amount' ) ) {
	351	+ return true;
	352	+ }
	353	+
	354	+ foreach ( $wp_filter['give_donation_amount']->callbacks ?? [] as $callbacks ) {
	355	+ foreach ( $callbacks as $callback ) {
	356	+ if ( 'give_deprecated_filter_mapping' !== $callback['function'] ) {
	357	+ return true;
	358	+ }
	359	+ }
	360	+ }
	361	+
	362	+ return false;
	363	+ }
	364	+
	365	+ /**
	366	+ * Translate the subset of Give_Payments_Query arguments the stats methods build (status, date
	367	+ * range, form, parent, and simple meta equality) into a WHERE fragment. Anything else returns null.
	368	+ *
	369	+ * @since 4.18.0
	370	+ *
	371	+ * @param array $args
	372	+ *
	373	+ * @return string|null
	374	+ */
	375	+ private function stats_where_sql( array $args ) {
	376	+ global $wpdb;
	377	+
	378	+ /**
	379	+ * Allow add-ons that alter stats amounts some other way to keep the per-donation code path.
	380	+ *
	381	+ * @since 4.18.0
	382	+ *
	383	+ * @param bool $aggregate_in_sql
	384	+ * @param array $args
	385	+ */
	386	+ if ( ! apply_filters( 'givewp_payment_stats_aggregate_in_sql', true, $args ) ) {
	387	+ return null;
	388	+ }
	389	+
	390	+ $known = [ 'status', 'post_status', 'start_date', 'end_date', 'fields', 'number', 'output', 'meta_query', 'give_forms' ];
	391	+
	392	+ if ( array_diff( array_keys( $args ), $known ) ) {
	393	+ return null;
	394	+ }
	395	+
	396	+ if ( isset( $args['number'] ) && -1 !== (int) $args['number'] ) {
	397	+ return null;
	398	+ }
	399	+
	400	+ /*
	401	+ * Give_Payments_Query excludes child donations such as renewals by setting post_parent to 0, and
	402	+ * add-ons like Recurring clear that through `give_pre_get_payments`. Give the hook the same query
	403	+ * object it would see on the legacy path so the SQL path keeps the same rows.
	404	+ */
	405	+ $query = new Give_Payments_Query( $args );
	406	+ $query->__set( 'post_parent', 0 );
	407	+ do_action( 'give_pre_get_payments', $query );
	408	+
	409	+ $statuses = (array) ( $args['post_status'] ?? $args['status'] ?? 'publish' );
	410	+ $where = ' AND p.post_status IN (' . implode( ',', array_map( static function ( $status ) use ( $wpdb ) {
	411	+ return $wpdb->prepare( '%s', $status );
	412	+ }, $statuses ) ) . ')';
	413	+
	414	+ if ( isset( $query->args['post_parent'] ) && is_numeric( $query->args['post_parent'] ) ) {
	415	+ $where .= $wpdb->prepare( ' AND p.post_parent = %d', $query->args['post_parent'] );
	416	+ }
	417	+
	418	+ if ( ! empty( $args['start_date'] ) && ! is_wp_error( $args['start_date'] ) ) {
	419	+ $where .= $wpdb->prepare( ' AND p.post_date >= %s', date( 'Y-m-d H:i:s', $args['start_date'] ) );
	420	+ }
	421	+
	422	+ if ( ! empty( $args['end_date'] ) && ! is_wp_error( $args['end_date'] ) ) {
	423	+ $where .= $wpdb->prepare( ' AND p.post_date <= %s', date( 'Y-m-d H:i:s', $args['end_date'] ) );
	424	+ }
	425	+
	426	+ $donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
	427	+ $meta_conditions = [];
	428	+
	429	+ if ( ! empty( $args['give_forms'] ) ) {
	430	+ $meta_conditions[] = [ 'key' => '_give_payment_form_id', 'value' => $args['give_forms'] ];
	431	+ }
	432	+
	433	+ foreach ( (array) ( $args['meta_query'] ?? [] ) as $index => $condition ) {
	434	+ if ( 'relation' === $index ) {
	435	+ if ( 'AND' !== strtoupper( $condition ) ) {
	436	+ return null;
	437	+ }
	438	+ continue;
	439	+ }
	440	+
	441	+ if ( ! is_array( $condition ) || empty( $condition['key'] ) || ! isset( $condition['value'] ) ) {
	442	+ return null;
	443	+ }
	444	+
	445	+ if ( ! in_array( strtoupper( $condition['compare'] ?? '=' ), [ '=', 'IN' ], true ) ) {
	446	+ return null;
	447	+ }
	448	+
	449	+ if ( array_diff( array_keys( $condition ), [ 'key', 'value', 'compare' ] ) ) {
	450	+ return null;
	451	+ }
	452	+
	453	+ $meta_conditions[] = $condition;
	454	+ }
	455	+
	456	+ foreach ( $meta_conditions as $condition ) {
	457	+ $values = array_map( 'strval', (array) $condition['value'] );
	458	+
	459	+ if ( ! $values ) {
	460	+ return null;
	461	+ }
	462	+
	463	+ $placeholders = implode( ',', array_fill( 0, count( $values ), '%s' ) );
	464	+ $where .= $wpdb->prepare(
	465	+ " AND p.ID IN (SELECT {$donation_id_col} FROM {$wpdb->donationmeta} WHERE meta_key = %s AND meta_value IN ({$placeholders}))",
	466	+ array_merge( [ $condition['key'] ], $values )
	467	+ );
	468	+ }
	469	+
	470	+ return $where;
264	471	}
265	472
266	473	/**
267	474	* Get the best selling forms