← All changes | includes/payments/class-payment-stats.php +249 -37 2.3.1 → 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
		@@ -51,21 +52,27 @@
51	52	if ( is_wp_error( $this->end_date ) ) {
52	53	return $this->end_date;
53	54	}
54	55
55		- $args = array(
	56	+ $args = [
56	57	'status' => 'publish',
57	58	'start_date' => $this->start_date,
58	59	'end_date' => $this->end_date,
59	60	'fields' => 'ids',
60	61	'number' => - 1,
61		- 'output' => ''
62		- );
	62	+ 'output' => '',
	63	+ ];
63	64
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.
		@@ -99,33 +107,31 @@
99	107	if ( is_wp_error( $this->end_date ) ) {
100	108	return $this->end_date;
101	109	}
102	110
103		- $args = array(
	111	+ $args = [
104	112	'status' => 'publish',
105		- 'give_forms' => $form_id,
106	113	'start_date' => $this->start_date,
107	114	'end_date' => $this->end_date,
108	115	'fields' => 'ids',
109	116	'number' => - 1,
110	117	'output' => '',
111		- );
	118	+ ];
112	119
113		-
114	120	// Filter by Gateway ID meta_key
115	121	if ( $gateway_id ) {
116		- $args['meta_query'][] = array(
	122	+ $args['meta_query'][] = [
117	123	'key' => '_give_payment_gateway',
118	124	'value' => $gateway_id,
119		- );
	125	+ ];
120	126	}
121	127
122	128	// Filter by Gateway ID meta_key
123	129	if ( $form_id ) {
124		- $args['meta_query'][] = array(
	130	+ $args['meta_query'][] = [
125	131	'key' => '_give_payment_form_id',
126	132	'value' => $form_id,
127		- );
	133	+ ];
128	134	}
129	135
130	136	if ( ! empty( $args['meta_query'] ) && 1 < count( $args['meta_query'] ) ) {
131	137	$args['meta_query']['relation'] = 'AND';
		@@ -139,22 +145,27 @@
139	145
140	146	if ( false === $earnings ) {
141	147
142	148	$this->timestamp = false;
143		- $payments = new Give_Payments_Query( $args );
144		- $payments = $payments->get_payments();
145		- $earnings = 0;
	149	+ $payments = [];
	150	+ $earnings = $this->sum_earnings_in_sql( $args );
146	151
	152	+ if ( null === $earnings ) {
	153	+ $payments = new Give_Payments_Query( $args );
	154	+ $payments = $payments->get_payments();
	155	+ $earnings = 0;
	156	+ }
	157	+
147	158	if ( ! empty( $payments ) ) {
148	159	$donation_id_col = Give()->payment_meta->get_meta_type() . '_id';
149		- $query = "SELECT {$donation_id_col} as id, meta_value as total
	160	+ $query = "SELECT {$donation_id_col} as id, meta_value as total
150	161	FROM {$wpdb->donationmeta}
151	162	WHERE meta_key='_give_payment_total'
152		- AND {$donation_id_col} IN ('". implode( '\',\'', $payments ) ."')";
	163	+ AND {$donation_id_col} IN ('" . implode( '\',\'', $payments ) . "')";
153	164
154		- $payments = $wpdb->get_results($query, ARRAY_A);
	165	+ $payments = $wpdb->get_results( $query, ARRAY_A );
155	166
156		- if( ! empty( $payments ) ) {
	167	+ if ( ! empty( $payments ) ) {
157	168	foreach ( $payments as $payment ) {
158	169	$currency_code = give_get_payment_currency_code( $payment['id'] );
159	170
160	171	/**
		@@ -164,18 +175,21 @@
164	175	* @since 2.1
165	176	*/
166	177	$formatted_amount = apply_filters(
167	178	'give_donation_amount',
168		- give_format_amount( $payment['total'], array( 'donation_id' => $payment['id'] ) ),
	179	+ give_format_amount( $payment['total'], [ 'donation_id' => $payment['id'] ] ),
169	180	$payment['total'],
170	181	$payment['id'],
171		- array( 'type' => 'stats', 'currency'=> false, 'amount' => false )
	182	+ [
	183	+ 'type' => 'stats',
	184	+ 'currency' => false,
	185	+ 'amount' => false,
	186	+ ]
172	187	);
173	188
174		- $earnings += (float) give_maybe_sanitize_amount( $formatted_amount, array( 'currency' => $currency_code ) );
	189	+ $earnings += (float) give_maybe_sanitize_amount( $formatted_amount, [ 'currency' => $currency_code ] );
175	190	}
176	191	}
177		-
178	192	}
179	193
180	194	// Cache the results for one hour.
181	195	Give_Cache::set( $key, give_sanitize_amount_for_db( $earnings ), 60 * 60 );
		@@ -193,9 +207,9 @@
193	207	* @param string|bool $gateway_id Payment gateway id.
194	208	*/
195	209	$earnings = apply_filters( 'give_get_earnings', $earnings, $form_id, $start_date, $end_date, $gateway_id );
196	210
197		- //return earnings
	211	+ // return earnings
198	212	return round( $earnings, give_get_price_decimals( $form_id ) );
199	213
200	214	}
201	215
		@@ -225,32 +239,30 @@
225	239	if ( is_wp_error( $this->end_date ) ) {
226	240	return $this->end_date;
227	241	}
228	242
229		- $args = array(
	243	+ $args = [
230	244	'status' => 'publish',
231		- 'give_forms' => $form_id,
232	245	'start_date' => $this->start_date,
233	246	'end_date' => $this->end_date,
234	247	'fields' => 'ids',
235	248	'number' => - 1,
236		- );
	249	+ ];
237	250
238		-
239	251	// Filter by Gateway ID meta_key
240	252	if ( $gateway_id ) {
241		- $args['meta_query'][] = array(
	253	+ $args['meta_query'][] = [
242	254	'key' => '_give_payment_gateway',
243	255	'value' => $gateway_id,
244		- );
	256	+ ];
245	257	}
246	258
247	259	// Filter by Gateway ID meta_key
248	260	if ( $form_id ) {
249		- $args['meta_query'][] = array(
	261	+ $args['meta_query'][] = [
250	262	'key' => '_give_payment_form_id',
251	263	'value' => $form_id,
252		- );
	264	+ ];
253	265	}
254	266
255	267	if ( ! empty( $args['meta_query'] ) && 1 < count( $args['meta_query'] ) ) {
256	268	$args['meta_query']['relation'] = 'AND';
		@@ -258,17 +270,213 @@
258	270
259	271	$args = apply_filters( 'give_stats_earnings_args', $args );
260	272	$key = Give_Cache::get_key( 'give_stats', $args );
261	273
262		- //return earnings
	274	+ // return earnings
263	275	return $key;
264	276
265	277	}
266	278
267	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;
	471	+ }
	472	+
	473	+ /**
268	474	* Get the best selling forms
269	475	*
270	476	* @since 1.0
	477	+ * @since 2.9.6 Added an explicit ORDER BY on which to apply a DESC order.
	478	+ * @since 2.18.0 Updated sales calculation so that the results are ordered as integers.
271	479	* @access public
272	480	* @global wpdb $wpdb
273	481	*
274	482	* @param $number int The number of results to retrieve with the default set to 10.
		@@ -277,16 +485,20 @@
277	485	*/
278	486	public function get_best_selling( $number = 10 ) {
279	487	global $wpdb;
280	488
281		- $meta_table = __give_v20_bc_table_details( 'form' );
	489	+ $meta_table = give_v20_bc_table_details( 'form' );
282	490
283		- $give_forms = $wpdb->get_results( $wpdb->prepare(
284		- "SELECT {$meta_table['column']['id']} as form_id, max(meta_value) as sales
	491	+ $give_forms = $wpdb->get_results(
	492	+ $wpdb->prepare(
	493	+ "SELECT {$meta_table['column']['id']} as form_id, max(ABS(meta_value)) as sales
285	494	FROM {$meta_table['name']} WHERE meta_key='_give_form_sales' AND meta_value > 0
286	495	GROUP BY meta_value+0
287		- DESC LIMIT %d;", $number
288		- ) );
	496	+ ORDER BY sales DESC
	497	+ LIMIT %d;",
	498	+ $number
	499	+ )
	500	+ );
289	501
290	502	return $give_forms;
291	503	}
292	504