disco / app / Analytics / Queries / RevenueQuery.php

RevenueQuery.php in Discount Rules for WooCommerce – Disco | Dynamic Pricing, Conditions, Bulk, Bundle, BOGO 1.4.14, at app/Analytics/Queries/RevenueQuery.php

254 lines 8.3 KB

Raw Download Zip

1	<?php
2
3	/**
4	* RevenueQuery — time-series revenue query for the /analytics/revenue endpoint.
5	*
6	* @package Disco
7	* @subpackage Disco\App\Analytics\Queries
8	* @since 1.3.23
9	*/
10
11	namespace Disco\App\Analytics\Queries;
12
13	/**
14	* Single query that returns net_sales and discount_sales per time bucket.
15	*
16	* Supports three intervals: day, week, month.
17	* All data is scoped to wc-completed orders only.
18	*/
19	class RevenueQuery extends BaseQuery {
20
21	/**
22	* WooCommerce status value for completed orders.
23	*/
24	private const STATUS_COMPLETED = 'wc-completed';
25
26	/**
27	* Returns time-bucketed revenue rows for the given period and interval.
28	*
29	* Every bucket in the range is always present; buckets with no orders get 0 values.
30	*
31	* net_sales = SUM(order_stats.net_total) for ALL completed shop orders.
32	* discount_sales = SUM(total_amount - shipping_total) for completed orders that have a disco_campaign meta.
33	*
34	* Both bases mirror the summary KPI cards (SummaryQuery) so the graph totals
35	* reconcile to the headline net_sales / discount_sales figures.
36	*
37	* The date column always returns a Y-m-d value:
38	* day → the exact date
39	* week → Monday of the week
40	* month → first day of the month (e.g. 2024-04-01)
41	*
42	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
43	* @param string $interval 'day' \| 'week' \| 'month'.
44	* @return array Array of { date, net_sales, discount_sales, total_orders, discount_orders }
45	*/
46	public function get_revenue_series( array $period, string $interval ): array {
47	$totals = $this->fetch_total_rows( $period, $interval );
48	$disco = $this->fetch_disco_rows( $period, $interval );
49	$indexed = $this->index_rows_by_date( $totals, $disco );
50
51	return $this->fill_series( $indexed, $period, $interval );
52	}
53
54	/**
55	* Fetches per-bucket totals for ALL completed shop orders (no campaign filter).
56	*
57	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
58	* @param string $interval 'day' \| 'week' \| 'month'.
59	* @return array Row objects: date, total_orders, net_sales.
60	*/
61	private function fetch_total_rows( array $period, string $interval ): array {
62	global $wpdb;
63
64	$tables = $this->get_tables();
65	$clauses = $this->get_order_clauses( $tables );
66	$date_col = $clauses['date_col'];
67	$date_expr = $this->get_date_group_expr( $interval, $date_col );
68
69	$rows_sql = "SELECT
70	{$date_expr} AS date,
71	COUNT(o.ID) AS total_orders,
72	COALESCE(SUM(order_stats.net_total), 0) AS net_sales
73	FROM {$tables['orders']} o
74	LEFT JOIN {$wpdb->prefix}wc_order_stats order_stats ON order_stats.order_id = o.ID
75	WHERE {$clauses['status_where']}
76	AND o.status = %s
77	AND {$date_col} BETWEEN %s AND %s
78	GROUP BY {$date_expr}
79	ORDER BY date ASC";
80
81	return $this->run_rows(
82	$rows_sql,
83	array( self::STATUS_COMPLETED, $period['from'] . ' 00:00:00', $period['to'] . ' 23:59:59' )
84	);
85	}
86
87	/**
88	* Fetches per-bucket totals for completed disco-campaign orders only.
89	*
90	* The dedup condition guarantees one disco_campaign meta row per order, so
91	* the SUM cannot be inflated by duplicate meta rows — this replaces the
92	* DISTINCT derived-table JOIN the old single query needed.
93	*
94	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
95	* @param string $interval 'day' \| 'week' \| 'month'.
96	* @return array Row objects: date, discount_orders, discount_sales.
97	*/
98	private function fetch_disco_rows( array $period, string $interval ): array {
99	global $wpdb;
100
101	$tables = $this->get_tables();
102	$clauses = $this->get_order_clauses( $tables );
103	$order_id_col = $tables['order_id_col'];
104	$date_col = $clauses['date_col'];
105	$date_expr = $this->get_date_group_expr( $interval, $date_col );
106	$dedup = $this->get_campaign_dedup_condition( $tables );
107
108	$rows_sql = "SELECT
109	{$date_expr} AS date,
110	COUNT(DISTINCT order_meta.{$order_id_col}) AS discount_orders,
111	SUM(o.total_amount - COALESCE(order_stats.shipping_total, 0)) AS discount_sales
112	FROM {$tables['order_meta']} order_meta
113	JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_col} AND {$clauses['status_where']}
114	LEFT JOIN {$wpdb->prefix}wc_order_stats order_stats ON order_stats.order_id = order_meta.{$order_id_col}
115	WHERE order_meta.meta_key = %s
116	AND o.status = %s
117	AND {$date_col} BETWEEN %s AND %s
118	AND {$dedup}
119	GROUP BY {$date_expr}
120	ORDER BY date ASC";
121
122	return $this->run_rows(
123	$rows_sql,
124	array( 'disco_campaign', self::STATUS_COMPLETED, $period['from'] . ' 00:00:00', $period['to'] . ' 23:59:59' )
125	);
126	}
127
128	/**
129	* Merges total and disco rows into one map keyed by bucket date.
130	*
131	* Also normalizes types: sales rounded to 2 decimals, counts cast to int.
132	*
133	* @param array $totals Site-wide rows from fetch_total_rows().
134	* @param array $disco Disco-only rows from fetch_disco_rows().
135	* @return array Map of date => normalized row array.
136	*/
137	private function index_rows_by_date( array $totals, array $disco ): array {
138	$indexed = array();
139
140	foreach ( $totals as $row ) {
141	$indexed[ $row->date ] = array(
142	'date' => $row->date,
143	'net_sales' => round( (float) ( $row->net_sales ?? 0 ), 2 ),
144	'discount_sales' => 0.0,
145	'total_orders' => (int) ( $row->total_orders ?? 0 ),
146	'discount_orders' => 0,
147	);
148	}
149
150	foreach ( $disco as $row ) {
151	if ( ! isset( $indexed[ $row->date ] ) ) {
152	continue;
153	}
154
155	$indexed[ $row->date ]['discount_sales'] = round( (float) ( $row->discount_sales ?? 0 ), 2 );
156	$indexed[ $row->date ]['discount_orders'] = (int) ( $row->discount_orders ?? 0 );
157	}
158
159	return $indexed;
160	}
161
162	/**
163	* Generates every bucket in the period and fills any missing ones with zeros.
164	*
165	* @param array $indexed DB rows indexed by their date string.
166	* @param array $period { from: string Y-m-d, to: string Y-m-d }.
167	* @param string $interval 'day' \| 'week' \| 'month'.
168	*/
169	private function fill_series( array $indexed, array $period, string $interval ): array {
170	$empty = array( 'net_sales' => 0.0, 'discount_sales' => 0.0, 'total_orders' => 0, 'discount_orders' => 0 );
171	$result = array();
172	$current = $this->bucket_start( $period['from'], $interval );
173	$end = strtotime( $period['to'] . ' 23:59:59' );
174
175	while ( $current <= $end ) {
176	$key = gmdate( 'Y-m-d', $current );
177	$result[] = $indexed[ $key ] ?? array_merge( array( 'date' => $key ), $empty );
178	$current = $this->next_bucket( $current, $interval );
179	}
180
181	return $result;
182	}
183
184	/**
185	* Returns the Unix timestamp for the start of the bucket that contains $date.
186	*
187	* @param string $date Y-m-d.
188	* @param string $interval 'day' \| 'week' \| 'month'.
189	*/
190	private function bucket_start( string $date, string $interval ): int {
191	$ts = strtotime( $date . ' 00:00:00' );
192
193	if ( false === $ts ) {
194	return 0;
195	}
196
197	if ( 'month' === $interval ) {
198	return mktime( 0, 0, 0, (int) gmdate( 'n', $ts ), 1, (int) gmdate( 'Y', $ts ) );
199	}
200
201	if ( 'week' === $interval ) {
202	// ISO weekday: 1 = Monday … 7 = Sunday. Rewind to Monday.
203	$dow = (int) gmdate( 'N', $ts );
204
205	return $ts - ( $dow - 1 ) * DAY_IN_SECONDS;
206	}
207
208	return $ts;
209	}
210
211	/**
212	* Advances a bucket timestamp by one interval unit.
213	*
214	* @param int $ts Current bucket start timestamp.
215	* @param string $interval 'day' \| 'week' \| 'month'.
216	*/
217	private function next_bucket( int $ts, string $interval ): int {
218	if ( 'month' === $interval ) {
219	return mktime( 0, 0, 0, (int) gmdate( 'n', $ts ) + 1, 1, (int) gmdate( 'Y', $ts ) );
220	}
221
222	if ( 'week' === $interval ) {
223	return $ts + 7 * DAY_IN_SECONDS;
224	}
225
226	return $ts + DAY_IN_SECONDS;
227	}
228
229	/**
230	* Returns the SQL date-grouping expression for the given interval.
231	*
232	* All expressions return a Y-m-d string so the `date` column is uniform
233	* regardless of the chosen interval.
234	*
235	* @param string $interval 'day' \| 'week' \| 'month'.
236	* @param string $date_col Fully-qualified column reference.
237	*/
238	private function get_date_group_expr( string $interval, string $date_col ): string {
239	switch ( $interval ) {
240	case 'month':
241	// %% so the literal % survives $wpdb->prepare() in run_rows().
242	return "DATE_FORMAT({$date_col}, '%%Y-%%m-01')";
243
244	case 'week':
245	// Monday of the ISO week containing each order.
246	return "DATE(DATE_SUB({$date_col}, INTERVAL WEEKDAY({$date_col}) DAY))";
247
248	default: // day
249	return "DATE({$date_col})";
250	}
251	}
252
253	}
254