Commit 1317fd227ec for woocommerce
commit 1317fd227ec470ead4cc1fa7f312c2d3f64a1843
Author: louwie17 <lourensschep@gmail.com>
Date: Mon Jul 27 11:09:11 2026 +0200
Fix legacy reports returning zeros under HPOS-authoritative sites (#65493)
* Add HPOS legacy report query builder
* Simplify HPOS legacy report query builder
Rename OrderReportQueryBuilder to HposLegacyOrderReportQueryBuilder and
strip its CPT branches so the class is honestly HPOS-only — its sole
purpose is keeping legacy admin reports working against wc_orders /
wc_orders_meta / wc_order_operational_data, and the name and docblock
now reflect that.
Collapse the duplicated filter -> debug -> cache -> wpdb execute block
from WC_Admin_Report::get_order_report_data() into a shared
execute_order_report_query() helper used by both the HPOS and CPT
branches. Merge the near-identical prepare_where_meta_value() and
prepare_where_value() into prepare_predicate_value(), and share the
legacy-to-HPOS column mapping between translate_post_column() and
translate_legacy_sql_fragment(). Drop the per-build $report_schema
cache reset (each query already uses a fresh builder instance).
Behaviour unchanged. CPT inline path untouched.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
* Simplify HPOS legacy report query builder cleanups
Apply /simplify cleanups to the HPOS legacy report change:
- Drop the redundant class_exists() guard; the builder is always autoloaded.
- Extract the duplicated enable_big_selects()/result-filter block in
execute_order_report_query() into run_order_report_query().
- Memoize legacy_to_hpos_column_map() so it is built once per query.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01E6KUGCPgvJqoz3JLxrYQFe
* Make HPOS legacy report date handling DST-aware
The HPOS report query builder converted between UTC and site-local time using
a single cached gmt_offset (the offset in effect "now"). On timezones that
observe DST, reusing that offset for a range in a different DST period shifted
the report's date bounds by an hour and bucketed rows near midnight into the
wrong day/month, unlike the CPT path which reads local post_date directly.
- Resolve report date bounds via wp_timezone(), so each boundary uses the
offset that applied on that date.
- Bucket rows with CONVERT_TZ() for named timezones (per-row, DST-correct),
falling back to the fixed-offset shift when MySQL timezone tables are absent.
Manual-offset sites have no DST, so they keep the exact fixed-offset shift.
- Add a query-builder test covering winter/summer bounds and CONVERT_TZ
bucketing for a named timezone.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01E6KUGCPgvJqoz3JLxrYQFe
* Address review feedback on HPOS legacy report query building
- Restore the single-method structure of WC_Admin_Report::get_order_report_data()
and drop the execute_order_report_query()/run_order_report_query() helpers so
no new methods are added to this publicly-subclassed class.
- Resolve HposLegacyOrderReportQueryBuilder through wc_get_container() instead
of direct instantiation, and reset its per-build caches on entry since
container instances are shared.
- Replace strtr() with word-boundary preg matching in the legacy SQL fragment
translator so tokens embedded in longer identifiers (product_ID,
posts.post_date_gmt) are left untouched.
- Add @since 11.0.0 annotations to the query builder.
- Extend query-builder tests: order-item joins and predicates, parent_meta and
parent_order_status handling, empty IN() predicate skipping, and
word-boundary fragment translation.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_012GudfnZqjdhsGCpYBVzYwp
* Make HPOS legacy report date WHERE predicates sargable
Plain post_date where rows (scalar comparison, date/datetime value) now
convert the boundary to UTC in PHP and compare orders.date_created_gmt
directly, instead of wrapping the column in the per-row local-time
expression, so MySQL can use the date index. This matters for the sales
sparklines, whose only date bound is a post_date > comparison.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Build HPOS legacy report joins from where rows for CPT parity
The CPT path builds joins from both the data and where descriptors, so a
where row carrying a type (e.g. order_item) gets its join even without a
matching data entry. Mirror that in the HPOS builder: where rows now
contribute joins (never SELECT columns), with data keys shadowing where
rows exactly like the legacy array union.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Make HPOS legacy report timezone fallback DST-transition aware
When MySQL timezone tables are unavailable, CONVERT_TZ() returns NULL and
the local-date expression fell back to a single shift by the current
gmt_offset, bucketing historical orders near midnight into the wrong day
for named timezones with DST. The fallback now bakes the site timezone's
DST transitions for the report window into a CASE expression so each row
is shifted by the offset in effect when it was created.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Update HPOS legacy report builder @since annotations to 11.1.0
Trunk now targets WooCommerce 11.1.0.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Only use the report range for the timezone fallback when filter_range is on
Queries with filter_range disabled (e.g. the sparklines) can still carry
the report page's selected dates, which may describe a different DST
period than the rows they actually fetch. Ignore the passed range for
such queries so the fallback window defaults to the trailing year.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Enable WC_Tests_Admin_Report under HPOS and map address meta keys
Removing the HPOS skip surfaced that _billing_*/_shipping_* meta keys
returned no rows under HPOS: those fields live in wc_order_addresses, so
the order-meta fallback join matched nothing. Map them to the addresses
table for selects, parent_meta selects and where_meta predicates.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Round HPOS report money columns to the legacy meta format
Mapped money columns are DECIMAL(26,8), so raw selects returned e.g.
50.00000000 where the CPT meta stores 50.00. Round them to the store
price decimals, which matches the legacy format without affecting
aggregates since stored values are already rounded to that precision.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
* Only round HPOS report money columns in non-aggregated selects
Rounding was applied to the column before the report function wrapped it,
producing SUM(ROUND(col, 2)). Several money metas are stored at full
precision, so per-row rounding could drift from the CPT path, which sums
the unrounded meta values. Aggregates now use the raw column and only
bare selects keep the legacy-format ROUND.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Uzh6VKo89NK56K1UFE9bsT
---------
Co-authored-by: Claude Opus 4.7 <noreply@anthropic.com>
diff --git a/plugins/woocommerce/changelog/fix-legacy-reports-hpos-48903 b/plugins/woocommerce/changelog/fix-legacy-reports-hpos-48903
new file mode 100644
index 00000000000..82f5254a8f8
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-legacy-reports-hpos-48903
@@ -0,0 +1,4 @@
+Significance: minor
+Type: fix
+
+Fix legacy `wc/v3/reports/sales` and the other legacy admin reports returning empty results on HPOS-authoritative sites with sync disabled by routing `WC_Admin_Report::get_order_report_data()` through the HPOS schema (`wc_orders` / `wc_orders_meta` / `wc_order_operational_data`).
diff --git a/plugins/woocommerce/includes/admin/reports/class-wc-admin-report.php b/plugins/woocommerce/includes/admin/reports/class-wc-admin-report.php
index 5e4b56d19da..04cde823bb9 100644
--- a/plugins/woocommerce/includes/admin/reports/class-wc-admin-report.php
+++ b/plugins/woocommerce/includes/admin/reports/class-wc-admin-report.php
@@ -6,7 +6,8 @@
*/
use Automattic\WooCommerce\Enums\OrderStatus;
-use Automattic\WooCommerce\Utilities\ArrayUtil;
+use Automattic\WooCommerce\Internal\Admin\Reports\HposLegacyOrderReportQueryBuilder;
+use Automattic\WooCommerce\Utilities\OrderUtil;
if ( ! defined( 'ABSPATH' ) ) {
exit; // Exit if accessed directly.
@@ -122,231 +123,244 @@ class WC_Admin_Report {
$order_status = apply_filters( 'woocommerce_reports_order_statuses', $order_status );
- $query = array();
- $select = array();
+ $args['order_status'] = $order_status;
- foreach ( $data as $raw_key => $value ) {
- $key = sanitize_key( $raw_key );
- $distinct = '';
+ if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
+ $query = wc_get_container()->get( HposLegacyOrderReportQueryBuilder::class )->build_query( $args, (int) $this->start_date, (int) $this->end_date );
+ } else {
+ $query = array();
+ $select = array();
- if ( isset( $value['distinct'] ) ) {
- $distinct = 'DISTINCT';
- }
+ foreach ( $data as $raw_key => $value ) {
+ $key = sanitize_key( $raw_key );
+ $distinct = '';
- switch ( $value['type'] ) {
- case 'meta':
- $get_key = "meta_{$key}.meta_value";
- break;
- case 'parent_meta':
- $get_key = "parent_meta_{$key}.meta_value";
- break;
- case 'post_data':
- $get_key = "posts.{$key}";
- break;
- case 'order_item_meta':
- $get_key = "order_item_meta_{$key}.meta_value";
- break;
- case 'order_item':
- $get_key = "order_items.{$key}";
- break;
- }
+ if ( isset( $value['distinct'] ) ) {
+ $distinct = 'DISTINCT';
+ }
- if ( empty( $get_key ) ) {
- // Skip to the next foreach iteration else the query will be invalid.
- continue;
- }
+ switch ( $value['type'] ) {
+ case 'meta':
+ $get_key = "meta_{$key}.meta_value";
+ break;
+ case 'parent_meta':
+ $get_key = "parent_meta_{$key}.meta_value";
+ break;
+ case 'post_data':
+ $get_key = "posts.{$key}";
+ break;
+ case 'order_item_meta':
+ $get_key = "order_item_meta_{$key}.meta_value";
+ break;
+ case 'order_item':
+ $get_key = "order_items.{$key}";
+ break;
+ }
- if ( $value['function'] ) {
- $get = "{$value['function']}({$distinct} {$get_key})";
- } else {
- $get = "{$distinct} {$get_key}";
- }
+ if ( empty( $get_key ) ) {
+ // Skip to the next foreach iteration else the query will be invalid.
+ continue;
+ }
- $select[] = "{$get} as {$value['name']}";
- }
+ if ( $value['function'] ) {
+ $get = "{$value['function']}({$distinct} {$get_key})";
+ } else {
+ $get = "{$distinct} {$get_key}";
+ }
- $query['select'] = 'SELECT ' . implode( ',', $select );
- $query['from'] = "FROM {$wpdb->posts} AS posts";
+ $select[] = "{$get} as {$value['name']}";
+ }
- // Joins.
- $joins = array();
+ $query['select'] = 'SELECT ' . implode( ',', $select );
+ $query['from'] = "FROM {$wpdb->posts} AS posts";
- foreach ( ( $data + $where ) as $raw_key => $value ) {
- $join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
- $type = isset( $value['type'] ) ? $value['type'] : false;
- $key = sanitize_key( $raw_key );
+ // Joins.
+ $joins = array();
- switch ( $type ) {
- case 'meta':
- $joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON ( posts.ID = meta_{$key}.post_id AND meta_{$key}.meta_key = '{$raw_key}' )";
- break;
- case 'parent_meta':
- $joins[ "parent_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS parent_meta_{$key} ON (posts.post_parent = parent_meta_{$key}.post_id) AND (parent_meta_{$key}.meta_key = '{$raw_key}')";
- break;
- case 'order_item_meta':
- $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id)";
-
- if ( ! empty( $value['order_item_type'] ) ) {
- $joins['order_items'] .= " AND (order_items.order_item_type = '{$value['order_item_type']}')";
- }
+ foreach ( ( $data + $where ) as $raw_key => $value ) {
+ $join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
+ $type = isset( $value['type'] ) ? $value['type'] : false;
+ $key = sanitize_key( $raw_key );
+
+ switch ( $type ) {
+ case 'meta':
+ $joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON ( posts.ID = meta_{$key}.post_id AND meta_{$key}.meta_key = '{$raw_key}' )";
+ break;
+ case 'parent_meta':
+ $joins[ "parent_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS parent_meta_{$key} ON (posts.post_parent = parent_meta_{$key}.post_id) AND (parent_meta_{$key}.meta_key = '{$raw_key}')";
+ break;
+ case 'order_item_meta':
+ $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id)";
+
+ if ( ! empty( $value['order_item_type'] ) ) {
+ $joins['order_items'] .= " AND (order_items.order_item_type = '{$value['order_item_type']}')";
+ }
- $joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON " .
- "(order_items.order_item_id = order_item_meta_{$key}.order_item_id) " .
- " AND (order_item_meta_{$key}.meta_key = '{$raw_key}')";
- break;
- case 'order_item':
- $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
- break;
+ $joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON " .
+ "(order_items.order_item_id = order_item_meta_{$key}.order_item_id) " .
+ " AND (order_item_meta_{$key}.meta_key = '{$raw_key}')";
+ break;
+ case 'order_item':
+ $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
+ break;
+ }
}
- }
- if ( ! empty( $where_meta ) ) {
- foreach ( $where_meta as $value ) {
- if ( ! is_array( $value ) ) {
- continue;
- }
- $join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
- $type = isset( $value['type'] ) ? $value['type'] : false;
- $key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
+ if ( ! empty( $where_meta ) ) {
+ foreach ( $where_meta as $value ) {
+ if ( ! is_array( $value ) ) {
+ continue;
+ }
+ $join_type = isset( $value['join_type'] ) ? $value['join_type'] : 'INNER';
+ $type = isset( $value['type'] ) ? $value['type'] : false;
+ $key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
- if ( 'order_item_meta' === $type ) {
+ if ( 'order_item_meta' === $type ) {
- $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
- $joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";
+ $joins['order_items'] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id";
+ $joins[ "order_item_meta_{$key}" ] = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_{$key} ON order_items.order_item_id = order_item_meta_{$key}.order_item_id";
- } else {
- // If we have a where clause for meta, join the postmeta table.
- $joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
+ } else {
+ // If we have a where clause for meta, join the postmeta table.
+ $joins[ "meta_{$key}" ] = "{$join_type} JOIN {$wpdb->postmeta} AS meta_{$key} ON posts.ID = meta_{$key}.post_id";
+ }
}
}
- }
- if ( ! empty( $parent_order_status ) ) {
- $joins['parent'] = "LEFT JOIN {$wpdb->posts} AS parent ON posts.post_parent = parent.ID";
- }
-
- $query['join'] = implode( ' ', $joins );
+ if ( ! empty( $parent_order_status ) ) {
+ $joins['parent'] = "LEFT JOIN {$wpdb->posts} AS parent ON posts.post_parent = parent.ID";
+ }
- $query['where'] = "
- WHERE posts.post_type IN ( '" . implode( "','", $order_types ) . "' )
- ";
+ $query['join'] = implode( ' ', $joins );
- if ( ! empty( $order_status ) ) {
- $query['where'] .= "
- AND posts.post_status IN ( 'wc-" . implode( "','wc-", $order_status ) . "')
- ";
- }
+ $query['where'] = "
+ WHERE posts.post_type IN ( '" . implode( "','", $order_types ) . "' )
+ ";
- if ( ! empty( $parent_order_status ) ) {
if ( ! empty( $order_status ) ) {
- $query['where'] .= " AND ( parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') OR parent.ID IS NULL ) ";
- } else {
- $query['where'] .= " AND parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') ";
+ $query['where'] .= "
+ AND posts.post_status IN ( 'wc-" . implode( "','wc-", $order_status ) . "')
+ ";
}
- }
- // phpcs:disable WordPress.DateTime.RestrictedFunctions.date_date
- if ( $filter_range ) {
- $query['where'] .= "
- AND posts.post_date >= '" . date( 'Y-m-d H:i:s', $this->start_date ) . "'
- AND posts.post_date < '" . date( 'Y-m-d H:i:s', strtotime( '+1 DAY', $this->end_date ) ) . "'
- ";
- }
- // phpcs:enable WordPress.DateTime.RestrictedFunctions.date_date
+ if ( ! empty( $parent_order_status ) ) {
+ if ( ! empty( $order_status ) ) {
+ $query['where'] .= " AND ( parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') OR parent.ID IS NULL ) ";
+ } else {
+ $query['where'] .= " AND parent.post_status IN ( 'wc-" . implode( "','wc-", $parent_order_status ) . "') ";
+ }
+ }
- if ( ! empty( $where_meta ) ) {
+ // phpcs:disable WordPress.DateTime.RestrictedFunctions.date_date
+ if ( $filter_range ) {
+ $query['where'] .= "
+ AND posts.post_date >= '" . date( 'Y-m-d H:i:s', $this->start_date ) . "'
+ AND posts.post_date < '" . date( 'Y-m-d H:i:s', strtotime( '+1 DAY', $this->end_date ) ) . "'
+ ";
+ }
+ // phpcs:enable WordPress.DateTime.RestrictedFunctions.date_date
- $relation = isset( $where_meta['relation'] ) ? $where_meta['relation'] : 'AND';
+ if ( ! empty( $where_meta ) ) {
- $query['where'] .= ' AND (';
+ $relation = isset( $where_meta['relation'] ) ? $where_meta['relation'] : 'AND';
- foreach ( $where_meta as $index => $value ) {
+ $query['where'] .= ' AND (';
- if ( ! is_array( $value ) ) {
- continue;
- }
+ foreach ( $where_meta as $index => $value ) {
- $key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
+ if ( ! is_array( $value ) ) {
+ continue;
+ }
- if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {
+ $key = sanitize_key( is_array( $value['meta_key'] ) ? $value['meta_key'][0] . '_array' : $value['meta_key'] );
- if ( ! empty( $value['meta_value'] ) && ! is_array( $value['meta_value'] ) ) {
- $value['meta_value'] = (array) $value['meta_value']; // @phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
- }
+ if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {
- if ( ! empty( $value['meta_value'] ) ) {
- $formats = implode( ', ', array_fill( 0, count( $value['meta_value'] ), '%s' ) );
- $where_value = $value['operator'] . ' (' . $wpdb->prepare( $formats, $value['meta_value'] ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
- }
- } else {
- $where_value = $value['operator'] . ' ' . $wpdb->prepare( '%s', $value['meta_value'] );
- }
+ if ( ! empty( $value['meta_value'] ) && ! is_array( $value['meta_value'] ) ) {
+ $value['meta_value'] = (array) $value['meta_value']; // @phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
+ }
- if ( ! empty( $where_value ) ) {
- if ( $index > 0 ) {
- $query['where'] .= ' ' . $relation;
+ if ( ! empty( $value['meta_value'] ) ) {
+ $formats = implode( ', ', array_fill( 0, count( $value['meta_value'] ), '%s' ) );
+ $where_value = $value['operator'] . ' (' . $wpdb->prepare( $formats, $value['meta_value'] ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ }
+ } else {
+ $where_value = $value['operator'] . ' ' . $wpdb->prepare( '%s', $value['meta_value'] );
}
- if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) {
-
- if ( is_array( $value['meta_key'] ) ) {
- $query['where'] .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
- } else {
- $query['where'] .= " ( order_item_meta_{$key}.meta_key = '{$value['meta_key']}'";
+ if ( ! empty( $where_value ) ) {
+ if ( $index > 0 ) {
+ $query['where'] .= ' ' . $relation;
}
- $query['where'] .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
- } else {
+ if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) {
+
+ if ( is_array( $value['meta_key'] ) ) {
+ $query['where'] .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
+ } else {
+ $query['where'] .= " ( order_item_meta_{$key}.meta_key = '{$value['meta_key']}'";
+ }
- if ( is_array( $value['meta_key'] ) ) {
- $query['where'] .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
+ $query['where'] .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
} else {
- $query['where'] .= " ( meta_{$key}.meta_key = '{$value['meta_key']}'";
- }
- $query['where'] .= " AND meta_{$key}.meta_value {$where_value} )";
+ if ( is_array( $value['meta_key'] ) ) {
+ $query['where'] .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $value['meta_key'] ) . "')";
+ } else {
+ $query['where'] .= " ( meta_{$key}.meta_key = '{$value['meta_key']}'";
+ }
+
+ $query['where'] .= " AND meta_{$key}.meta_value {$where_value} )";
+ }
}
}
- }
- $query['where'] .= ')';
- }
+ $query['where'] .= ')';
+ }
- if ( ! empty( $where ) ) {
+ if ( ! empty( $where ) ) {
- foreach ( $where as $value ) {
+ foreach ( $where as $value ) {
- if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {
+ if ( strtolower( $value['operator'] ) === 'in' || strtolower( $value['operator'] ) === 'not in' ) {
- if ( ! empty( $value['value'] ) && ! is_array( $value['value'] ) ) {
- $value['value'] = (array) $value['value'];
- }
- if ( ! empty( $value['value'] ) ) {
- $formats = implode( ', ', array_fill( 0, count( $value['value'] ), '%s' ) );
- $where_value = $value['operator'] . ' (' . $wpdb->prepare( $formats, $value['value'] ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ if ( ! empty( $value['value'] ) && ! is_array( $value['value'] ) ) {
+ $value['value'] = (array) $value['value'];
+ }
+ if ( ! empty( $value['value'] ) ) {
+ $formats = implode( ', ', array_fill( 0, count( $value['value'] ), '%s' ) );
+ $where_value = $value['operator'] . ' (' . $wpdb->prepare( $formats, $value['value'] ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ }
+ } else {
+ $where_value = $value['operator'] . ' ' . $wpdb->prepare( '%s', $value['value'] );
}
- } else {
- $where_value = $value['operator'] . ' ' . $wpdb->prepare( '%s', $value['value'] );
- }
- if ( ! empty( $where_value ) ) {
- $query['where'] .= " AND {$value['key']} {$where_value}";
+ if ( ! empty( $where_value ) ) {
+ $query['where'] .= " AND {$value['key']} {$where_value}";
+ }
}
}
- }
- if ( $group_by ) {
- $query['group_by'] = "GROUP BY {$group_by}";
- }
+ if ( $group_by ) {
+ $query['group_by'] = "GROUP BY {$group_by}";
+ }
- if ( $order_by ) {
- $query['order_by'] = "ORDER BY {$order_by}";
- }
+ if ( $order_by ) {
+ $query['order_by'] = "ORDER BY {$order_by}";
+ }
- if ( $limit ) {
- $query['limit'] = "LIMIT {$limit}";
+ if ( $limit ) {
+ $query['limit'] = "LIMIT {$limit}";
+ }
}
+ /**
+ * Filters the legacy order report SQL clauses before they are combined into a SQL string.
+ *
+ * @param array $query SQL clauses keyed by select/from/join/where/group_by/order_by/limit.
+ *
+ * @since 2.1.0
+ */
$query = apply_filters( 'woocommerce_reports_get_order_report_query', $query );
$query = implode( ' ', $query );
@@ -359,6 +373,14 @@ class WC_Admin_Report {
if ( $debug || $nocache ) {
self::enable_big_selects();
+ /**
+ * Filters the legacy order report query result.
+ *
+ * @param mixed $result Query result returned by wpdb.
+ * @param array $data Report data arguments.
+ *
+ * @since 2.1.0
+ */
$result = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data );
} else {
$query_hash = md5( $query_type . $query );
@@ -366,6 +388,14 @@ class WC_Admin_Report {
if ( null === $result ) {
self::enable_big_selects();
+ /**
+ * Filters the legacy order report query result.
+ *
+ * @param mixed $result Query result returned by wpdb.
+ * @param array $data Report data arguments.
+ *
+ * @since 2.1.0
+ */
$result = apply_filters( 'woocommerce_reports_get_order_report_data', $wpdb->$query_type( $query ), $data );
}
$this->set_cached_query( $query_hash, $result );
diff --git a/plugins/woocommerce/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilder.php b/plugins/woocommerce/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilder.php
new file mode 100644
index 00000000000..a0889fe7668
--- /dev/null
+++ b/plugins/woocommerce/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilder.php
@@ -0,0 +1,936 @@
+<?php
+declare( strict_types=1 );
+
+namespace Automattic\WooCommerce\Internal\Admin\Reports;
+
+use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore;
+use Automattic\WooCommerce\Utilities\OrderUtil;
+
+defined( 'ABSPATH' ) || exit;
+
+/**
+ * Builds HPOS-backed SQL clauses for the legacy admin order reports.
+ *
+ * Used by {@see WC_Admin_Report::get_order_report_data()} when HPOS is the
+ * authoritative order store, so the same `data` / `where` / `where_meta`
+ * descriptors that legacy reports already pass continue to work against
+ * `wc_orders`, `wc_orders_meta` and `wc_order_operational_data` instead of
+ * `{$wpdb->posts}` / `{$wpdb->postmeta}`.
+ *
+ * @internal
+ *
+ * @since 11.1.0
+ */
+class HposLegacyOrderReportQueryBuilder {
+
+ /**
+ * Lazy cache for schema details (column mappings, gmt offset) used during one query build.
+ *
+ * @var array<string,mixed>|null
+ */
+ private $report_schema = null;
+
+ /**
+ * Lazy cache for the legacy `posts` column => HPOS column map used during one query build.
+ *
+ * @var array<string,string>|null
+ */
+ private $column_map = null;
+
+ /**
+ * Report date window for the current build as `[start, end)` Unix timestamps,
+ * or null when the query is not bounded by a caller-supplied range.
+ *
+ * @var array{0:int,1:int}|null
+ */
+ private $report_range = null;
+
+ /**
+ * Build the SQL clauses for an HPOS-backed legacy order report query.
+ *
+ * @since 11.1.0
+ *
+ * @param array $args Parsed report arguments.
+ * @param int $start_date Start date as a Unix timestamp.
+ * @param int $end_date End date as a Unix timestamp.
+ *
+ * @return array<string,string> SQL clauses keyed by select/from/join/where/group_by/order_by/limit.
+ */
+ public function build_query( array $args, int $start_date, int $end_date ): array {
+ // The class is resolved from the DI container as a shared instance: reset the
+ // per-build caches so option-derived state can't leak between queries.
+ $this->report_schema = null;
+ $this->column_map = null;
+ // Only trust the range when the query is actually bounded by it: queries with
+ // filter_range off (e.g. the sparklines) may still carry the report page's
+ // selected dates, which can describe a different DST period than the rows
+ // they fetch.
+ $this->report_range = ( ! empty( $args['filter_range'] ) && $start_date > 0 && $end_date > 0 )
+ ? array( $start_date, (int) strtotime( '+1 DAY', $end_date ) )
+ : null;
+
+ $data = $args['data'] ?? array();
+ $where = $args['where'] ?? array();
+ $where_meta = $args['where_meta'] ?? array();
+ $group_by = $args['group_by'] ?? '';
+ $order_by = $args['order_by'] ?? '';
+ $limit = $args['limit'] ?? '';
+ $filter_range = $args['filter_range'] ?? false;
+ $order_types = $args['order_types'] ?? wc_get_order_types( 'reports' );
+ $order_status = $args['order_status'] ?? array();
+ $parent_order_status = $args['parent_order_status'] ?? false;
+
+ $select = array();
+ $joins = array();
+
+ foreach ( $data as $raw_key => $value ) {
+ $part = $this->build_data_select_and_joins( $raw_key, $value );
+ if ( null === $part ) {
+ continue;
+ }
+ $select[] = $part['select'];
+ $joins = array_merge( $joins, $part['joins'] );
+ }
+
+ // The CPT path builds joins from `( $data + $where )`, so a `where` row carrying a
+ // `type` gets its join even without a matching `data` entry (rows shadowed by a
+ // `data` key are skipped, matching the array-union semantics). Such rows only ever
+ // contribute joins, never SELECT columns.
+ foreach ( $where as $raw_key => $value ) {
+ if ( is_array( $value ) && ! array_key_exists( $raw_key, $data ) ) {
+ $joins = array_merge( $joins, $this->resolve_type_joins( $raw_key, $value ) );
+ }
+ }
+
+ if ( ! empty( $where_meta ) ) {
+ foreach ( $where_meta as $value ) {
+ if ( is_array( $value ) ) {
+ $joins = array_merge( $joins, $this->build_where_meta_joins( $value ) );
+ }
+ }
+ }
+
+ if ( ! empty( $parent_order_status ) ) {
+ $joins = array_merge( $joins, $this->build_parent_orders_join() );
+ }
+
+ $query = array();
+ $query['select'] = 'SELECT ' . implode( ',', $select );
+ $query['from'] = 'FROM ' . OrderUtil::get_table_for_orders() . ' AS orders';
+ $query['join'] = implode( ' ', $joins );
+ $query['where'] = $this->build_where_clause( $order_types, $order_status, $parent_order_status, (bool) $filter_range, $start_date, $end_date );
+
+ if ( ! empty( $where_meta ) ) {
+ $query['where'] .= $this->build_where_meta_predicates( $where_meta );
+ }
+
+ if ( ! empty( $where ) ) {
+ $query['where'] .= $this->build_where_predicates( $where );
+ }
+
+ if ( $group_by ) {
+ $query['group_by'] = 'GROUP BY ' . $this->translate_legacy_sql_fragment( $group_by );
+ }
+
+ if ( $order_by ) {
+ $query['order_by'] = 'ORDER BY ' . $this->translate_legacy_sql_fragment( $order_by );
+ }
+
+ if ( $limit ) {
+ $query['limit'] = "LIMIT {$limit}";
+ }
+
+ return $query;
+ }
+
+ /**
+ * Resolve a single `$data` entry into a SELECT fragment plus the JOINs it requires.
+ *
+ * @param string $raw_key Original key from the `$data` array.
+ * @param array $value The `$data` row.
+ *
+ * @return array{select: string, joins: array<string,string>}|null Null when the row has no resolvable type.
+ */
+ private function build_data_select_and_joins( $raw_key, $value ) {
+ $key = sanitize_key( $raw_key );
+ $distinct = isset( $value['distinct'] ) ? 'DISTINCT' : '';
+ $join_type = $value['join_type'] ?? 'INNER';
+ $type = $value['type'] ?? '';
+ $get_key = '';
+ $joins = array();
+
+ switch ( $type ) {
+ case 'meta':
+ list( $get_key, $joins ) = $this->resolve_meta_select( $raw_key, $key, $join_type );
+ break;
+ case 'parent_meta':
+ list( $get_key, $joins ) = $this->resolve_parent_meta_select( $raw_key, $key, $join_type );
+ break;
+ case 'post_data':
+ $get_key = $this->translate_post_column( $key );
+ break;
+ case 'order_item_meta':
+ $get_key = "order_item_meta_{$key}.meta_value";
+ $joins = $this->build_order_item_meta_joins( $key, $raw_key, $value['order_item_type'] ?? '', $join_type );
+ break;
+ case 'order_item':
+ $get_key = "order_items.{$key}";
+ $joins = $this->build_order_items_join( $join_type );
+ break;
+ }
+
+ if ( '' === $get_key ) {
+ return null;
+ }
+
+ // Only bare selects get the legacy money format; inside an aggregate the raw
+ // column must be used, as per-row rounding would accumulate error where the
+ // CPT path sums the unrounded meta values.
+ if ( ! $value['function'] && $this->is_money_meta_key( $type, $raw_key ) ) {
+ $get_key = $this->format_money_column( $get_key );
+ }
+
+ $expr = $value['function']
+ ? "{$value['function']}({$distinct} {$get_key})"
+ : "{$distinct} {$get_key}";
+
+ return array(
+ 'select' => "{$expr} as {$value['name']}",
+ 'joins' => $joins,
+ );
+ }
+
+ /**
+ * Resolve the JOINs required by a `data` or `where` row's `type`, without any SELECT part.
+ *
+ * @param string|int $raw_key Key of the row in its original array (a numeric index for `where` rows).
+ * @param array $value The row.
+ *
+ * @return array<string,string> JOINs keyed by alias.
+ */
+ private function resolve_type_joins( $raw_key, array $value ): array {
+ $raw_key = (string) $raw_key;
+ $key = sanitize_key( $raw_key );
+ $join_type = $value['join_type'] ?? 'INNER';
+
+ switch ( $value['type'] ?? '' ) {
+ case 'meta':
+ return $this->resolve_meta_select( $raw_key, $key, $join_type )[1];
+ case 'parent_meta':
+ return $this->resolve_parent_meta_select( $raw_key, $key, $join_type )[1];
+ case 'order_item_meta':
+ return $this->build_order_item_meta_joins( $key, $raw_key, $value['order_item_type'] ?? '', $join_type );
+ case 'order_item':
+ return $this->build_order_items_join( $join_type );
+ }
+
+ return array();
+ }
+
+ /**
+ * Resolve a `type=meta` SELECT.
+ *
+ * @param string $raw_key Meta key.
+ * @param string $key Sanitized version of the meta key.
+ * @param string $join_type Join type.
+ *
+ * @return array{0: string, 1: array<string,string>} SELECT fragment and JOINs.
+ */
+ private function resolve_meta_select( $raw_key, $key, $join_type ) {
+ $schema = $this->get_report_schema();
+ if ( isset( $schema['order_column'][ $raw_key ] ) ) {
+ return array( $schema['order_column'][ $raw_key ], array() );
+ }
+ if ( isset( $schema['op_data_column'][ $raw_key ] ) ) {
+ return array( $schema['op_data_column'][ $raw_key ], $this->build_op_data_join() );
+ }
+ if ( isset( $schema['address_column'][ $raw_key ] ) ) {
+ list( $address_type, $column ) = $schema['address_column'][ $raw_key ];
+ return array( "address_{$address_type}.{$column}", $this->build_address_join( $address_type ) );
+ }
+
+ return array(
+ "meta_{$key}.meta_value",
+ $this->build_meta_join( $key, $raw_key, $join_type ),
+ );
+ }
+
+ /**
+ * Resolve a `type=parent_meta` SELECT.
+ *
+ * @param string $raw_key Meta key on the parent order.
+ * @param string $key Sanitized version of the meta key.
+ * @param string $join_type Join type.
+ *
+ * @return array{0: string, 1: array<string,string>} SELECT fragment and JOINs.
+ */
+ private function resolve_parent_meta_select( $raw_key, $key, $join_type ) {
+ $schema = $this->get_report_schema();
+ if ( isset( $schema['order_column'][ $raw_key ] ) ) {
+ $column = substr( $schema['order_column'][ $raw_key ], strlen( 'orders.' ) );
+ return array( "parent_orders.{$column}", $this->build_parent_orders_join() );
+ }
+ if ( isset( $schema['op_data_column'][ $raw_key ] ) ) {
+ $column = substr( $schema['op_data_column'][ $raw_key ], strlen( 'op_data.' ) );
+ $joins = array_merge( $this->build_parent_orders_join(), $this->build_parent_op_data_join() );
+ return array( "parent_op_data.{$column}", $joins );
+ }
+ if ( isset( $schema['address_column'][ $raw_key ] ) ) {
+ list( $address_type, $column ) = $schema['address_column'][ $raw_key ];
+ $joins = array_merge(
+ $this->build_parent_orders_join(),
+ $this->build_address_join( $address_type, 'parent_orders', 'parent_address' )
+ );
+ return array( "parent_address_{$address_type}.{$column}", $joins );
+ }
+
+ return array(
+ "parent_meta_{$key}.meta_value",
+ $this->build_parent_meta_join( $key, $raw_key, $join_type ),
+ );
+ }
+
+ /**
+ * Whether a data row resolves to a mapped HPOS money column.
+ *
+ * @param string $type Row type.
+ * @param string $raw_key Original meta key.
+ *
+ * @return bool
+ */
+ private function is_money_meta_key( $type, $raw_key ): bool {
+ if ( 'meta' !== $type && 'parent_meta' !== $type ) {
+ return false;
+ }
+
+ $schema = $this->get_report_schema();
+ return isset( $schema['order_column'][ $raw_key ] ) || isset( $schema['op_data_column'][ $raw_key ] );
+ }
+
+ /**
+ * Wrap a mapped money column so bare SELECTs match the legacy meta format.
+ *
+ * HPOS money columns are DECIMAL(26,8), so selecting one raw yields e.g.
+ * '50.00000000' where the CPT meta stores '50.00'. Rounding to the store's
+ * price decimals restores the legacy format. Never applied inside aggregate
+ * functions: several money metas are stored at full precision, so per-row
+ * rounding would drift from the CPT sums.
+ *
+ * @param string $column Qualified column reference.
+ *
+ * @return string SQL expression.
+ */
+ private function format_money_column( string $column ): string {
+ return "ROUND({$column}, " . wc_get_price_decimals() . ')';
+ }
+
+ /**
+ * Build the JOIN onto `wc_order_addresses` for one address type.
+ *
+ * @param string $address_type 'billing' or 'shipping'.
+ * @param string $order_alias Orders-table alias to join from.
+ * @param string $alias_prefix Prefix for the address-table alias.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_address_join( string $address_type, string $order_alias = 'orders', string $alias_prefix = 'address' ): array {
+ $alias = "{$alias_prefix}_{$address_type}";
+ return array(
+ $alias => 'LEFT JOIN ' . OrdersTableDataStore::get_addresses_table_name() . " AS {$alias} ON ( {$order_alias}.id = {$alias}.order_id AND {$alias}.address_type = '{$address_type}' )",
+ );
+ }
+
+ /**
+ * Build the JOINs required to satisfy a `where_meta` predicate.
+ *
+ * @param array $value A `where_meta` row.
+ *
+ * @return array<string,string> JOINs keyed by alias.
+ */
+ private function build_where_meta_joins( $value ) {
+ $type = $value['type'] ?? '';
+ $join_type = $value['join_type'] ?? 'INNER';
+ $meta_key = $value['meta_key'];
+ $key = sanitize_key( is_array( $meta_key ) ? $meta_key[0] . '_array' : $meta_key );
+
+ if ( 'order_item_meta' === $type ) {
+ global $wpdb;
+ $alias = "order_item_meta_{$key}";
+ return array(
+ 'order_items' => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON orders.id = order_items.order_id",
+ $alias => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS {$alias} ON order_items.order_item_id = {$alias}.order_item_id",
+ );
+ }
+
+ $schema = $this->get_report_schema();
+ if ( ! is_array( $meta_key ) && isset( $schema['where_meta_column'][ $meta_key ] ) ) {
+ return array();
+ }
+ if ( ! is_array( $meta_key ) && isset( $schema['address_column'][ $meta_key ] ) ) {
+ return $this->build_address_join( $schema['address_column'][ $meta_key ][0] );
+ }
+
+ $alias = "meta_{$key}";
+ return array( $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON orders.id = {$alias}.order_id" );
+ }
+
+ /**
+ * Build the JOIN required to look up parent order data.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_parent_orders_join() {
+ return array(
+ 'parent_orders' => 'LEFT JOIN ' . OrderUtil::get_table_for_orders() . ' AS parent_orders ON orders.parent_order_id = parent_orders.id',
+ );
+ }
+
+ /**
+ * Build the JOIN required to look up the parent order's operational-data row.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_parent_op_data_join() {
+ return array(
+ 'parent_op_data' => 'LEFT JOIN ' . OrdersTableDataStore::get_operational_data_table_name() . ' AS parent_op_data ON parent_orders.id = parent_op_data.order_id',
+ );
+ }
+
+ /**
+ * Build the JOIN onto `wc_order_operational_data`.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_op_data_join() {
+ return array(
+ 'op_data' => 'LEFT JOIN ' . OrdersTableDataStore::get_operational_data_table_name() . ' AS op_data ON orders.id = op_data.order_id',
+ );
+ }
+
+ /**
+ * Build the JOIN onto the orders-meta table for an order meta lookup.
+ *
+ * @param string $key Sanitized meta key for the alias.
+ * @param string $raw_key Original meta key.
+ * @param string $join_type Join type.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_meta_join( $key, $raw_key, $join_type ) {
+ $alias = "meta_{$key}";
+ return array(
+ $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON ( orders.id = {$alias}.order_id AND {$alias}.meta_key = '{$raw_key}' )",
+ );
+ }
+
+ /**
+ * Build the JOIN onto the orders-meta table keyed on the parent order id.
+ *
+ * @param string $key Sanitized meta key for the alias.
+ * @param string $raw_key Original meta key.
+ * @param string $join_type Join type.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_parent_meta_join( $key, $raw_key, $join_type ) {
+ $alias = "parent_meta_{$key}";
+ return array(
+ $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON (orders.parent_order_id = {$alias}.order_id) AND ({$alias}.meta_key = '{$raw_key}')",
+ );
+ }
+
+ /**
+ * Build the JOIN onto `wp_woocommerce_order_items`.
+ *
+ * @param string $join_type Join type.
+ *
+ * @return array<string,string> JOIN keyed by alias.
+ */
+ private function build_order_items_join( $join_type ) {
+ global $wpdb;
+ return array(
+ 'order_items' => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON orders.id = order_items.order_id",
+ );
+ }
+
+ /**
+ * Build the JOIN pair used by `type=order_item_meta` data entries.
+ *
+ * @param string $key Sanitized meta key for the itemmeta alias.
+ * @param string $raw_key Original meta key.
+ * @param string $order_item_type Optional order-item type filter.
+ * @param string $join_type Join type.
+ *
+ * @return array<string,string> JOINs keyed by alias.
+ */
+ private function build_order_item_meta_joins( $key, $raw_key, $order_item_type, $join_type ) {
+ global $wpdb;
+ $items_join = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (orders.id = order_items.order_id)";
+
+ if ( '' !== $order_item_type ) {
+ $items_join .= " AND (order_items.order_item_type = '{$order_item_type}')";
+ }
+
+ $itemmeta_alias = "order_item_meta_{$key}";
+ return array(
+ 'order_items' => $items_join,
+ $itemmeta_alias => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS {$itemmeta_alias} ON " .
+ "(order_items.order_item_id = {$itemmeta_alias}.order_item_id) " .
+ " AND ({$itemmeta_alias}.meta_key = '{$raw_key}')",
+ );
+ }
+
+ /**
+ * Build the top-level WHERE clause.
+ *
+ * @param array $order_types Order types to restrict to.
+ * @param array|false $order_status Order statuses without the `wc-` prefix.
+ * @param array|false $parent_order_status Parent order statuses without the `wc-` prefix.
+ * @param bool $filter_range Whether to bound the query by date.
+ * @param int $start_date Start date as a Unix timestamp.
+ * @param int $end_date End date as a Unix timestamp.
+ *
+ * @return string WHERE clause.
+ */
+ private function build_where_clause( $order_types, $order_status, $parent_order_status, $filter_range, $start_date, $end_date ) {
+ $clause = "
+ WHERE orders.type IN ( '" . implode( "','", $order_types ) . "' )
+ ";
+
+ if ( ! empty( $order_status ) ) {
+ $clause .= "
+ AND orders.status IN ( 'wc-" . implode( "','wc-", $order_status ) . "')
+ ";
+ }
+
+ if ( ! empty( $parent_order_status ) ) {
+ $clause .= $this->build_parent_order_status_where_clause( $parent_order_status, $order_status );
+ }
+
+ if ( $filter_range ) {
+ $clause .= $this->build_filter_range_where_clause( $start_date, $end_date );
+ }
+
+ return $clause;
+ }
+
+ /**
+ * Build the WHERE fragment that filters by the parent order's status.
+ *
+ * @param array $parent_order_status Status slugs without `wc-` prefix.
+ * @param array|false $order_status If non-empty, parent-status NULL is allowed.
+ *
+ * @return string WHERE fragment.
+ */
+ private function build_parent_order_status_where_clause( $parent_order_status, $order_status ) {
+ $statuses_in = "'wc-" . implode( "','wc-", $parent_order_status ) . "'";
+
+ if ( ! empty( $order_status ) ) {
+ return " AND ( parent_orders.status IN ( {$statuses_in} ) OR parent_orders.id IS NULL ) ";
+ }
+
+ return " AND parent_orders.status IN ( {$statuses_in} ) ";
+ }
+
+ /**
+ * Build the WHERE fragment that bounds the query by report date range.
+ *
+ * @param int $start_date Start date as a Unix timestamp.
+ * @param int $end_date End date as a Unix timestamp.
+ *
+ * @return string WHERE fragment.
+ */
+ private function build_filter_range_where_clause( $start_date, $end_date ) {
+ $start_gmt = $this->local_timestamp_to_gmt( (int) $start_date );
+ $end_gmt = $this->local_timestamp_to_gmt( (int) strtotime( '+1 DAY', $end_date ) );
+
+ return "
+ AND orders.date_created_gmt >= '{$start_gmt}'
+ AND orders.date_created_gmt < '{$end_gmt}'
+ ";
+ }
+
+ /**
+ * Convert a WordPress "local" timestamp (one that reads as site-local wall-clock time
+ * when formatted with {@see gmdate()}) into the equivalent GMT datetime string.
+ *
+ * Uses the site timezone via {@see wp_timezone()} so the offset is resolved for that
+ * specific date. On timezones that observe DST this yields the correct UTC instant for
+ * the boundary even when the report range spans a different offset period than "now",
+ * unlike a single cached `gmt_offset`.
+ *
+ * @param int $local_ts Local timestamp to convert.
+ *
+ * @return string GMT datetime in `Y-m-d H:i:s` format.
+ */
+ private function local_timestamp_to_gmt( int $local_ts ): string {
+ return (string) $this->local_datetime_to_gmt( gmdate( 'Y-m-d H:i:s', $local_ts ) );
+ }
+
+ /**
+ * Convert a site-local date or datetime string into the equivalent GMT datetime string.
+ *
+ * Only plain `Y-m-d`, `Y-m-d H:i` and `Y-m-d H:i:s` values are converted; anything else
+ * (including relative formats) returns null so callers can fall back to per-row SQL
+ * conversion instead of guessing at the caller's intent.
+ *
+ * @param string $local_datetime Local date or datetime string.
+ *
+ * @return string|null GMT datetime in `Y-m-d H:i:s` format, or null when not convertible.
+ */
+ private function local_datetime_to_gmt( string $local_datetime ): ?string {
+ if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}(:\d{2})?)?$/', $local_datetime ) ) {
+ return null;
+ }
+
+ try {
+ $local = new \DateTimeImmutable( $local_datetime, wp_timezone() );
+ } catch ( \Exception $e ) {
+ return null;
+ }
+
+ return $local->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
+ }
+
+ /**
+ * Build the WHERE fragment for caller-supplied `where_meta` predicates.
+ *
+ * @param array $where_meta The original `where_meta` argument.
+ *
+ * @return string WHERE fragment.
+ */
+ private function build_where_meta_predicates( $where_meta ) {
+ $schema = $this->get_report_schema();
+ $relation = $where_meta['relation'] ?? 'AND';
+
+ $clause = ' AND (';
+ $first = true;
+
+ foreach ( $where_meta as $value ) {
+ if ( ! is_array( $value ) ) {
+ continue;
+ }
+
+ $where_value = $this->prepare_predicate_value( $value, 'meta_value' );
+ if ( '' === $where_value ) {
+ continue;
+ }
+
+ if ( ! $first ) {
+ $clause .= ' ' . $relation;
+ }
+ $first = false;
+
+ $meta_key = $value['meta_key'];
+ $key = sanitize_key( is_array( $meta_key ) ? $meta_key[0] . '_array' : $meta_key );
+
+ if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) {
+ if ( is_array( $meta_key ) ) {
+ $clause .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $meta_key ) . "')";
+ } else {
+ $clause .= " ( order_item_meta_{$key}.meta_key = '{$meta_key}'";
+ }
+ $clause .= " AND order_item_meta_{$key}.meta_value {$where_value} )";
+ } elseif ( ! is_array( $meta_key ) && isset( $schema['where_meta_column'][ $meta_key ] ) ) {
+ $clause .= ' ( ' . $schema['where_meta_column'][ $meta_key ] . " {$where_value} )";
+ } elseif ( ! is_array( $meta_key ) && isset( $schema['address_column'][ $meta_key ] ) ) {
+ list( $address_type, $column ) = $schema['address_column'][ $meta_key ];
+ $clause .= " ( address_{$address_type}.{$column} {$where_value} )";
+ } else {
+ if ( is_array( $meta_key ) ) {
+ $clause .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $meta_key ) . "')";
+ } else {
+ $clause .= " ( meta_{$key}.meta_key = '{$meta_key}'";
+ }
+ $clause .= " AND meta_{$key}.meta_value {$where_value} )";
+ }
+ }
+
+ return $clause . ')';
+ }
+
+ /**
+ * Build the WHERE fragment for caller-supplied `where` predicates.
+ *
+ * @param array $where Caller-supplied predicates.
+ *
+ * @return string WHERE fragment.
+ */
+ private function build_where_predicates( $where ) {
+ $clause = '';
+
+ foreach ( $where as $value ) {
+ $where_value = $this->prepare_predicate_value( $value, 'value' );
+ if ( '' === $where_value ) {
+ continue;
+ }
+
+ $predicate = $this->build_gmt_date_predicate( $value );
+ if ( null === $predicate ) {
+ $predicate = $this->translate_legacy_sql_fragment( $value['key'] ) . " {$where_value}";
+ }
+
+ $clause .= ' AND ' . $predicate;
+ }
+
+ return $clause;
+ }
+
+ /**
+ * Build a sargable predicate on `orders.date_created_gmt` for a plain `post_date` where row.
+ *
+ * Translating `post_date` wraps the column in the per-row local-time expression, which
+ * stops MySQL from using the date index. For the common shape — a bare `post_date` key,
+ * a scalar comparison operator and a date/datetime value (e.g. the sales sparkline's
+ * only date bound, `post_date > 'Y-m-d'`) — the boundary is converted to UTC in PHP
+ * instead and compared against the raw GMT column, matching what
+ * {@see self::build_filter_range_where_clause()} does for `filter_range` bounds.
+ *
+ * @param array $value A `where` row.
+ *
+ * @return string|null Predicate SQL, or null when the row is not a plain post_date comparison.
+ */
+ private function build_gmt_date_predicate( array $value ): ?string {
+ $key = trim( (string) ( $value['key'] ?? '' ) );
+ if ( 'post_date' !== $key && 'posts.post_date' !== $key ) {
+ return null;
+ }
+
+ $rhs = $value['value'] ?? '';
+ if ( ! in_array( $value['operator'], array( '=', '!=', '<', '<=', '>', '>=' ), true ) || ! is_string( $rhs ) ) {
+ return null;
+ }
+
+ $gmt = $this->local_datetime_to_gmt( $rhs );
+ if ( null === $gmt ) {
+ return null;
+ }
+
+ global $wpdb;
+ return "orders.date_created_gmt {$value['operator']} " . $wpdb->prepare( '%s', $gmt );
+ }
+
+ /**
+ * Prepare the right-hand side of a predicate (e.g. `= '5'` or `IN ('a','b')`).
+ *
+ * Used by both the `where` (`value` / `operator`) and `where_meta`
+ * (`meta_value` / `operator`) builders.
+ *
+ * @param array $value A `where` or `where_meta` row.
+ * @param string $value_key The array key holding the RHS value (`value` or `meta_value`).
+ *
+ * @return string SQL fragment, or empty string when no predicate is emitted.
+ */
+ private function prepare_predicate_value( array $value, string $value_key ): string {
+ global $wpdb;
+ $op_lc = strtolower( $value['operator'] );
+ $rhs = $value[ $value_key ] ?? '';
+
+ if ( 'in' === $op_lc || 'not in' === $op_lc ) {
+ if ( ! empty( $rhs ) && ! is_array( $rhs ) ) {
+ $rhs = (array) $rhs;
+ }
+ if ( empty( $rhs ) ) {
+ return '';
+ }
+ $formats = implode( ', ', array_fill( 0, count( $rhs ), '%s' ) );
+ return $value['operator'] . ' (' . $wpdb->prepare( $formats, $rhs ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ }
+
+ return $value['operator'] . ' ' . $wpdb->prepare( '%s', $rhs );
+ }
+
+ /**
+ * Get HPOS schema details used by the report SQL builder (column mappings and gmt offset).
+ *
+ * @return array<string,mixed>
+ */
+ private function get_report_schema(): array {
+ if ( null !== $this->report_schema ) {
+ return $this->report_schema;
+ }
+
+ $this->report_schema = array(
+ 'gmt_offset' => (int) ( (float) get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ),
+ 'order_column' => array(
+ '_order_total' => 'orders.total_amount',
+ '_order_tax' => 'orders.tax_amount',
+ // On a refund row, wc_orders.total_amount stores the negative refund total.
+ // Legacy callers expect the positive `_refund_amount` meta value, so it
+ // is intentionally not mapped here.
+ ),
+ 'op_data_column' => array(
+ '_order_shipping' => 'op_data.shipping_total_amount',
+ '_order_shipping_tax' => 'op_data.shipping_tax_amount',
+ ),
+ 'where_meta_column' => array(
+ '_customer_user' => 'orders.customer_id',
+ ),
+ 'address_column' => $this->build_address_column_map(),
+ );
+
+ return $this->report_schema;
+ }
+
+ /**
+ * Map legacy `_billing_*` / `_shipping_*` meta keys to `wc_order_addresses` columns.
+ *
+ * Under HPOS these fields live in the addresses table, not in order meta, so a
+ * meta join would return no rows for them.
+ *
+ * @return array<string,array{0:string,1:string}> Meta key => [address_type, column].
+ */
+ private function build_address_column_map(): array {
+ $fields = array( 'first_name', 'last_name', 'company', 'address_1', 'address_2', 'city', 'state', 'postcode', 'country', 'email', 'phone' );
+ $map = array();
+
+ foreach ( array( 'billing', 'shipping' ) as $address_type ) {
+ foreach ( $fields as $field ) {
+ if ( 'shipping' === $address_type && 'email' === $field ) {
+ continue;
+ }
+ $map[ "_{$address_type}_{$field}" ] = array( $address_type, $field );
+ }
+ }
+
+ return $map;
+ }
+
+ /**
+ * Map of bare legacy `posts` column names to their HPOS equivalents.
+ *
+ * Source of truth shared by {@see self::translate_post_column()} and
+ * {@see self::translate_legacy_sql_fragment()}.
+ *
+ * @return array<string,string>
+ */
+ private function legacy_to_hpos_column_map(): array {
+ if ( null !== $this->column_map ) {
+ return $this->column_map;
+ }
+
+ $this->column_map = array(
+ 'ID' => 'orders.id',
+ 'post_date' => $this->hpos_local_date_expr(),
+ 'post_parent' => 'orders.parent_order_id',
+ 'post_status' => 'orders.status',
+ 'post_type' => 'orders.type',
+ );
+
+ return $this->column_map;
+ }
+
+ /**
+ * Translate a `post_data` column key to an HPOS column reference.
+ *
+ * @param string $key Sanitized `post_data` key from the caller.
+ *
+ * @return string SQL fragment referencing the equivalent HPOS column.
+ */
+ private function translate_post_column( string $key ): string {
+ $map = $this->legacy_to_hpos_column_map();
+
+ // `sanitize_key()` lowercases `ID` to `id`; treat both forms as the orders.id column.
+ if ( 'id' === $key ) {
+ return $map['ID'];
+ }
+ return $map[ $key ] ?? "orders.{$key}";
+ }
+
+ /**
+ * Translate legacy `posts.<col>` (and bare `ID` / `post_date`) references in an arbitrary SQL fragment.
+ *
+ * Matches are bounded by `\b` so tokens embedded in longer identifiers
+ * (e.g. `product_ID`, `posts.post_date_gmt`) are left untouched.
+ *
+ * @param string $fragment Caller-supplied SQL fragment.
+ *
+ * @return string Translated fragment safe to drop into an HPOS query.
+ */
+ private function translate_legacy_sql_fragment( string $fragment ): string {
+ $map = $this->legacy_to_hpos_column_map();
+
+ // Qualified `posts.<col>` references first, then the bare tokens legacy
+ // callers use unqualified. Callbacks avoid `$`/`\` replacement-string pitfalls.
+ $fragment = (string) preg_replace_callback(
+ '/\bposts\.(ID|post_date|post_parent|post_status|post_type)\b/',
+ static function ( $matches ) use ( $map ) {
+ return $map[ $matches[1] ];
+ },
+ $fragment
+ );
+
+ return (string) preg_replace_callback(
+ '/\b(ID|post_date)\b/',
+ static function ( $matches ) use ( $map ) {
+ return $map[ $matches[1] ];
+ },
+ $fragment
+ );
+ }
+
+ /**
+ * Build a MySQL expression that converts `orders.date_created_gmt` into site-local time.
+ *
+ * For sites configured with a named timezone (e.g. `Europe/Berlin`) the conversion is done
+ * per row with `CONVERT_TZ()` so DST is honoured and rows near midnight bucket into the
+ * correct day/month. `CONVERT_TZ()` returns NULL when the server's timezone tables are not
+ * loaded, so it falls back to {@see self::build_transition_fallback_expr()}. Sites using a
+ * manual UTC offset have no DST, so a fixed-offset shift is exact and is used directly.
+ *
+ * @return string SQL fragment that produces a local DATETIME.
+ */
+ private function hpos_local_date_expr(): string {
+ $timezone_string = get_option( 'timezone_string' );
+ if ( '' === $timezone_string ) {
+ $schema = $this->get_report_schema();
+ return "DATE_ADD(orders.date_created_gmt, INTERVAL {$schema['gmt_offset']} SECOND)";
+ }
+
+ global $wpdb;
+ $convert = $wpdb->prepare( 'CONVERT_TZ(orders.date_created_gmt, %s, %s)', '+00:00', $timezone_string );
+
+ return "IFNULL({$convert}, {$this->build_transition_fallback_expr()})";
+ }
+
+ /**
+ * Build the fallback used when `CONVERT_TZ()` can't resolve the named timezone
+ * (MySQL timezone tables not loaded).
+ *
+ * A single shift by the current `gmt_offset` would be wrong for rows created in a
+ * different DST period, so the site timezone's DST transitions inside the report
+ * window are baked into a CASE expression and each row is shifted by the offset in
+ * effect when it was created. Without a caller-supplied range (e.g. the sparklines)
+ * the window covers the last year, which spans any window such callers use.
+ *
+ * @return string SQL fragment that produces a local DATETIME.
+ */
+ private function build_transition_fallback_expr(): string {
+ list( $window_start, $window_end ) = $this->report_range ?? array( time() - YEAR_IN_SECONDS, time() + DAY_IN_SECONDS );
+
+ $transitions = wp_timezone()->getTransitions( $window_start, $window_end );
+ if ( ! is_array( $transitions ) || array() === $transitions ) {
+ $schema = $this->get_report_schema();
+ return "DATE_ADD(orders.date_created_gmt, INTERVAL {$schema['gmt_offset']} SECOND)";
+ }
+
+ // The first entry describes the offset already in effect at the window start;
+ // each later entry is a transition inside the window.
+ $case = '';
+ $count = count( $transitions );
+ for ( $i = 0; $i < $count - 1; $i++ ) {
+ $boundary = gmdate( 'Y-m-d H:i:s', $transitions[ $i + 1 ]['ts'] );
+ $offset = (int) $transitions[ $i ]['offset'];
+ $case .= "WHEN orders.date_created_gmt < '{$boundary}' THEN DATE_ADD(orders.date_created_gmt, INTERVAL {$offset} SECOND) ";
+ }
+
+ $last_offset = (int) $transitions[ $count - 1 ]['offset'];
+ $last_shift = "DATE_ADD(orders.date_created_gmt, INTERVAL {$last_offset} SECOND)";
+
+ if ( '' === $case ) {
+ return $last_shift;
+ }
+
+ return "CASE {$case}ELSE {$last_shift} END";
+ }
+}
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/admin/reports/class-wc-tests-admin-report.php b/plugins/woocommerce/tests/legacy/unit-tests/admin/reports/class-wc-tests-admin-report.php
index 5ecc7eb5160..4be81320b2f 100644
--- a/plugins/woocommerce/tests/legacy/unit-tests/admin/reports/class-wc-tests-admin-report.php
+++ b/plugins/woocommerce/tests/legacy/unit-tests/admin/reports/class-wc-tests-admin-report.php
@@ -21,16 +21,6 @@ class WC_Tests_Admin_Report extends WC_Unit_Test_Case {
include_once WC_Unit_Tests_Bootstrap::instance()->plugin_dir . '/includes/admin/reports/class-wc-admin-report.php';
}
- /**
- * Set up the test.
- */
- public function setUp(): void {
- parent::setUp();
- if ( \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) {
- $this->markTestSkipped( 'This test is not compatible with the custom orders table.' );
- }
- }
-
/**
* Clear cached report data.
*
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/rest-api/Tests/Version3/reports-sales.php b/plugins/woocommerce/tests/legacy/unit-tests/rest-api/Tests/Version3/reports-sales.php
new file mode 100644
index 00000000000..1085d5e724b
--- /dev/null
+++ b/plugins/woocommerce/tests/legacy/unit-tests/rest-api/Tests/Version3/reports-sales.php
@@ -0,0 +1,121 @@
+<?php
+/**
+ * Tests for the reports sales REST API.
+ *
+ * @package WooCommerce\Tests\API
+ */
+
+declare( strict_types=1 );
+
+use Automattic\WooCommerce\Enums\OrderStatus;
+use Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer;
+use Automattic\WooCommerce\RestApi\UnitTests\Helpers\OrderHelper;
+use Automattic\WooCommerce\RestApi\UnitTests\HPOSToggleTrait;
+use Automattic\WooCommerce\Utilities\OrderUtil;
+
+/**
+ * WC_Tests_API_Reports_Sales.
+ */
+class WC_Tests_API_Reports_Sales extends WC_REST_Unit_Test_Case {
+
+ use HPOSToggleTrait;
+
+ /**
+ * Original HPOS usage state.
+ *
+ * @var bool
+ */
+ private $original_hpos_usage;
+
+ /**
+ * Setup our test server, endpoints, and user info.
+ */
+ public function setUp(): void {
+ parent::setUp();
+ $this->original_hpos_usage = OrderUtil::custom_orders_table_usage_is_enabled();
+ $this->user = $this->factory->user->create(
+ array(
+ 'role' => 'administrator',
+ )
+ );
+ $this->clear_sales_report_cache();
+ }
+
+ /**
+ * Restore HPOS state and clear report caches.
+ */
+ public function tearDown(): void {
+ global $wpdb;
+
+ // phpcs:disable WordPress.DB.DirectDatabaseQuery
+ $wpdb->query( "TRUNCATE {$wpdb->prefix}wc_orders" );
+ $wpdb->query( "TRUNCATE {$wpdb->prefix}wc_orders_meta" );
+ $wpdb->query( "TRUNCATE {$wpdb->prefix}wc_order_operational_data" );
+ $wpdb->query( "TRUNCATE {$wpdb->prefix}wc_order_addresses" );
+ // phpcs:enable WordPress.DB.DirectDatabaseQuery
+
+ remove_all_actions( 'pre_option_' . DataSynchronizer::ORDERS_DATA_SYNC_ENABLED_OPTION );
+ OrderHelper::toggle_cot_feature_and_usage( $this->original_hpos_usage );
+
+ add_filter( 'query', array( $this, '_create_temporary_tables' ) );
+ add_filter( 'query', array( $this, '_drop_temporary_tables' ) );
+
+ $this->clear_sales_report_cache();
+ parent::tearDown();
+ }
+
+ /**
+ * Clear the sales report transient and in-memory cache.
+ */
+ private function clear_sales_report_cache(): void {
+ delete_transient( 'wc_report_sales_by_date' );
+
+ if ( ! class_exists( 'WC_Admin_Report' ) ) {
+ return;
+ }
+
+ $reflection = new ReflectionClass( WC_Admin_Report::class );
+ $property = $reflection->getProperty( 'cached_results' );
+ $property->setAccessible( true );
+ $property->setValue( array() );
+ }
+
+ /**
+ * @testdox Should return sales totals from HPOS data when sync is disabled.
+ */
+ public function test_get_sales_report_with_hpos_enabled_and_sync_off(): void {
+ $this->setup_cot();
+ $this->disable_cot_sync();
+
+ wp_set_current_user( $this->user );
+
+ $product = WC_Helper_Product::create_simple_product();
+ $product->set_regular_price( '100' );
+ $product->save();
+
+ for ( $i = 0; $i < 2; $i++ ) {
+ $order = wc_create_order();
+ $order->add_product( $product, 1 );
+ $order->calculate_totals();
+ $order->set_status( OrderStatus::COMPLETED );
+ $order->save();
+ }
+
+ $request = new WP_REST_Request( 'GET', '/wc/v3/reports/sales' );
+ $request->set_query_params(
+ array(
+ 'date_min' => gmdate( 'Y-m-d', strtotime( '-1 day' ) ),
+ 'date_max' => gmdate( 'Y-m-d', strtotime( '+1 day' ) ),
+ )
+ );
+
+ $response = $this->server->dispatch( $request );
+ $report = $response->get_data();
+
+ $this->assertEquals( 200, $response->get_status() );
+ $this->assertCount( 1, $report );
+ $this->assertSame( 2, $report[0]['total_orders'], 'Sales report should count HPOS orders when sync is disabled.' );
+ $this->assertSame( 2, $report[0]['total_items'], 'Sales report should count HPOS order items when sync is disabled.' );
+ $this->assertSame( wc_format_decimal( 200, 2 ), $report[0]['total_sales'], 'Sales report total should come from HPOS order totals.' );
+ }
+}
diff --git a/plugins/woocommerce/tests/php/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilderTest.php b/plugins/woocommerce/tests/php/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilderTest.php
new file mode 100644
index 00000000000..f13a68ad4ac
--- /dev/null
+++ b/plugins/woocommerce/tests/php/src/Internal/Admin/Reports/HposLegacyOrderReportQueryBuilderTest.php
@@ -0,0 +1,577 @@
+<?php
+declare( strict_types=1 );
+
+namespace Automattic\WooCommerce\Tests\Internal\Admin\Reports;
+
+use Automattic\WooCommerce\Enums\OrderStatus;
+use Automattic\WooCommerce\Internal\Admin\Reports\HposLegacyOrderReportQueryBuilder;
+use Automattic\WooCommerce\RestApi\UnitTests\Helpers\OrderHelper;
+use Automattic\WooCommerce\Utilities\OrderUtil;
+use WC_Unit_Test_Case;
+
+/**
+ * Tests for the HPOS-backed legacy order report query builder.
+ */
+class HposLegacyOrderReportQueryBuilderTest extends WC_Unit_Test_Case {
+
+ /**
+ * Original HPOS usage state.
+ *
+ * @var bool
+ */
+ private $original_hpos_usage;
+
+ /**
+ * Original GMT offset.
+ *
+ * @var string
+ */
+ private $original_gmt_offset;
+
+ /**
+ * Original timezone string.
+ *
+ * @var string
+ */
+ private $original_timezone_string;
+
+ /**
+ * Set up test fixtures.
+ */
+ public function setUp(): void {
+ parent::setUp();
+ $this->original_hpos_usage = OrderUtil::custom_orders_table_usage_is_enabled();
+ $this->original_gmt_offset = (string) get_option( 'gmt_offset' );
+ $this->original_timezone_string = (string) get_option( 'timezone_string' );
+ }
+
+ /**
+ * Restore test fixtures.
+ */
+ public function tearDown(): void {
+ OrderHelper::toggle_cot_feature_and_usage( $this->original_hpos_usage );
+ update_option( 'gmt_offset', $this->original_gmt_offset );
+ update_option( 'timezone_string', $this->original_timezone_string );
+ parent::tearDown();
+ }
+
+ /**
+ * @testdox Should build HPOS-backed clauses with the expected columns, joins and date bounds.
+ */
+ public function test_build_query_uses_hpos_clauses(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+ update_option( 'timezone_string', '' );
+ update_option( 'gmt_offset', 2 );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ '_order_total' => array(
+ 'type' => 'meta',
+ 'function' => 'SUM',
+ 'name' => 'total_sales',
+ ),
+ '_order_shipping' => array(
+ 'type' => 'meta',
+ 'function' => 'SUM',
+ 'name' => 'total_shipping',
+ ),
+ '_refund_amount' => array(
+ 'type' => 'meta',
+ 'function' => 'SUM',
+ 'name' => 'refund_amount',
+ ),
+ 'post_date' => array(
+ 'type' => 'post_data',
+ 'function' => null,
+ 'name' => 'post_date',
+ ),
+ ),
+ 'where' => array(
+ array(
+ 'key' => 'posts.ID',
+ 'value' => array( '123' ),
+ 'operator' => 'IN',
+ ),
+ ),
+ 'where_meta' => array(
+ // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
+ array(
+ 'meta_key' => '_customer_user',
+ 'meta_value' => '0',
+ 'operator' => '>',
+ ),
+ // phpcs:enable
+ ),
+ 'group_by' => 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date), ID',
+ 'order_by' => 'posts.post_date ASC',
+ 'limit' => '',
+ 'filter_range' => true,
+ 'order_types' => array( 'shop_order' ),
+ 'order_status' => array( OrderStatus::COMPLETED ),
+ 'parent_order_status' => false,
+ ),
+ strtotime( '2026-06-01 00:00:00' ),
+ strtotime( '2026-06-30 00:00:00' )
+ );
+
+ $local_date_expression = 'DATE_ADD(orders.date_created_gmt, INTERVAL 7200 SECOND)';
+
+ $this->assertStringContainsString( 'wc_orders AS orders', $query['from'] );
+ // Aggregated money columns stay unwrapped: rounding inside SUM() would drift
+ // from the CPT path, which sums the unrounded meta values.
+ $this->assertStringContainsString( 'SUM( orders.total_amount)', $query['select'] );
+ $this->assertStringContainsString( 'SUM( op_data.shipping_total_amount)', $query['select'] );
+ $this->assertStringNotContainsString( 'ROUND', $query['select'] );
+ $this->assertStringContainsString( 'meta__refund_amount.meta_value', $query['select'] );
+ $this->assertStringContainsString( 'wc_order_operational_data AS op_data', $query['join'] );
+ $this->assertStringContainsString( 'wc_orders_meta AS meta__refund_amount', $query['join'] );
+ $this->assertStringNotContainsString( 'meta__order_total', $query['join'] );
+ $this->assertStringNotContainsString( 'meta__customer_user', $query['join'] );
+ $this->assertStringContainsString( 'orders.customer_id > \'0\'', $query['where'] );
+ $this->assertStringContainsString( 'orders.id IN (\'123\')', $query['where'] );
+ $this->assertStringContainsString( "orders.date_created_gmt >= '2026-05-31 22:00:00'", $query['where'] );
+ $this->assertStringContainsString( "orders.date_created_gmt < '2026-06-30 22:00:00'", $query['where'] );
+ $this->assertSame(
+ "GROUP BY YEAR({$local_date_expression}), MONTH({$local_date_expression}), DAY({$local_date_expression}), orders.id",
+ $query['group_by']
+ );
+ $this->assertSame( "ORDER BY {$local_date_expression} ASC", $query['order_by'] );
+ }
+
+ /**
+ * @testdox Should resolve date bounds and row buckets with the DST offset for the report dates, not the current offset.
+ */
+ public function test_build_query_is_dst_aware_for_named_timezones(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+ // Europe/Berlin is UTC+1 in winter and UTC+2 in summer, so a single cached offset is wrong
+ // for at least one of the two ranges below.
+ update_option( 'timezone_string', 'Europe/Berlin' );
+
+ $builder = new HposLegacyOrderReportQueryBuilder();
+
+ $base_args = array(
+ 'data' => array(
+ 'post_date' => array(
+ 'type' => 'post_data',
+ 'function' => null,
+ 'name' => 'post_date',
+ ),
+ ),
+ 'group_by' => 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)',
+ 'filter_range' => true,
+ 'order_types' => array( 'shop_order' ),
+ );
+
+ // Winter range: Berlin is UTC+1, so local midnight maps back one hour.
+ $winter = $builder->build_query(
+ $base_args,
+ strtotime( '2026-01-01 00:00:00' ),
+ strtotime( '2026-01-31 00:00:00' )
+ );
+ $this->assertStringContainsString( "orders.date_created_gmt >= '2025-12-31 23:00:00'", $winter['where'] );
+ $this->assertStringContainsString( "orders.date_created_gmt < '2026-01-31 23:00:00'", $winter['where'] );
+
+ // Summer range: Berlin is UTC+2, so the same local midnight maps back two hours.
+ $summer = $builder->build_query(
+ $base_args,
+ strtotime( '2026-07-01 00:00:00' ),
+ strtotime( '2026-07-31 00:00:00' )
+ );
+ $this->assertStringContainsString( "orders.date_created_gmt >= '2026-06-30 22:00:00'", $summer['where'] );
+ $this->assertStringContainsString( "orders.date_created_gmt < '2026-07-31 22:00:00'", $summer['where'] );
+
+ // Row bucketing converts per row with CONVERT_TZ so DST is honoured (with a fixed-offset
+ // fallback for servers without timezone tables), rather than a single fixed shift.
+ $this->assertStringContainsString( "IFNULL(CONVERT_TZ(orders.date_created_gmt, '+00:00', 'Europe/Berlin')", $summer['group_by'] );
+ }
+
+ /**
+ * @testdox Should build order-item joins and predicates for the taxes-report shape (order_item_meta data plus array meta_key where_meta).
+ */
+ public function test_build_query_translates_order_item_meta_joins_and_predicates(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ 'tax_amount' => array(
+ 'type' => 'order_item_meta',
+ 'order_item_type' => 'tax',
+ 'function' => 'SUM',
+ 'name' => 'tax_amount',
+ ),
+ ),
+ 'where_meta' => array(
+ // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
+ array(
+ 'type' => 'order_item_meta',
+ 'meta_key' => array( '_line_tax', '_line_subtotal' ),
+ 'meta_value' => '0',
+ 'operator' => '!=',
+ ),
+ // phpcs:enable
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $this->assertStringContainsString( 'woocommerce_order_items AS order_items', $query['join'] );
+ $this->assertStringContainsString( 'woocommerce_order_itemmeta AS order_item_meta_tax_amount', $query['join'] );
+ $this->assertStringContainsString( 'orders.id = order_items.order_id', $query['join'] );
+ $this->assertStringContainsString( "order_item_meta__line_tax_array.meta_key IN ('_line_tax','_line_subtotal')", $query['where'] );
+ $this->assertStringContainsString( "order_item_meta__line_tax_array.meta_value != '0'", $query['where'] );
+ }
+
+ /**
+ * @testdox Should build the coupon-usage shape: order_item data entry with a bare order_item_type where key passed through untranslated.
+ */
+ public function test_build_query_translates_coupon_usage_shape(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ 'order_item_id' => array(
+ 'type' => 'order_item',
+ 'order_item_type' => 'coupon',
+ 'function' => 'COUNT',
+ 'name' => 'order_coupon_count',
+ ),
+ ),
+ 'where' => array(
+ array(
+ 'key' => 'order_item_type',
+ 'value' => 'coupon',
+ 'operator' => '=',
+ ),
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $this->assertStringContainsString( 'COUNT( order_items.order_item_id) as order_coupon_count', $query['select'] );
+ $this->assertStringContainsString( 'woocommerce_order_items AS order_items', $query['join'] );
+ $this->assertStringContainsString( "AND order_item_type = 'coupon'", $query['where'] );
+ }
+
+ /**
+ * @testdox Should map parent_meta order totals to the parent orders table and honour parent_order_status.
+ */
+ public function test_build_query_translates_refund_lines_shape(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $builder = new HposLegacyOrderReportQueryBuilder();
+
+ $args = array(
+ 'data' => array(
+ '_order_total' => array(
+ 'type' => 'parent_meta',
+ 'function' => '',
+ 'name' => 'parent_total',
+ ),
+ 'ID' => array(
+ 'type' => 'post_data',
+ 'function' => 'COUNT',
+ 'name' => 'total_refunds',
+ 'distinct' => true,
+ ),
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order_refund' ),
+ 'order_status' => false,
+ 'parent_order_status' => array( OrderStatus::COMPLETED, OrderStatus::PROCESSING ),
+ );
+
+ $query = $builder->build_query( $args, 0, 0 );
+
+ $this->assertStringContainsString( 'ROUND(parent_orders.total_amount, 2) as parent_total', $query['select'] );
+ $this->assertStringContainsString( 'COUNT(DISTINCT orders.id) as total_refunds', $query['select'] );
+ $this->assertStringContainsString( 'AS parent_orders ON orders.parent_order_id = parent_orders.id', $query['join'] );
+ $this->assertStringContainsString( "orders.type IN ( 'shop_order_refund' )", $query['where'] );
+ // With no own-status filter the parent status must match strictly (no IS NULL escape).
+ $this->assertStringContainsString( "parent_orders.status IN ( 'wc-completed','wc-processing' )", $query['where'] );
+ $this->assertStringNotContainsString( 'parent_orders.id IS NULL', $query['where'] );
+
+ // With an own-status filter, parentless orders are allowed through.
+ $args['order_status'] = array( OrderStatus::COMPLETED );
+ $query = $builder->build_query( $args, 0, 0 );
+ $this->assertStringContainsString( 'OR parent_orders.id IS NULL', $query['where'] );
+ }
+
+ /**
+ * @testdox Should map billing/shipping meta keys to the HPOS addresses table for selects and where_meta predicates.
+ */
+ public function test_build_query_maps_address_meta_to_hpos_address_table(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ '_billing_first_name' => array(
+ 'type' => 'meta',
+ 'function' => null,
+ 'name' => 'customer_name',
+ ),
+ ),
+ 'where_meta' => array(
+ // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
+ array(
+ 'meta_key' => '_shipping_country',
+ 'meta_value' => 'NL',
+ 'operator' => '=',
+ ),
+ // phpcs:enable
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $this->assertStringContainsString( 'address_billing.first_name as customer_name', $query['select'] );
+ $this->assertStringContainsString( "wc_order_addresses AS address_billing ON ( orders.id = address_billing.order_id AND address_billing.address_type = 'billing' )", $query['join'] );
+ $this->assertStringContainsString( "wc_order_addresses AS address_shipping ON ( orders.id = address_shipping.order_id AND address_shipping.address_type = 'shipping' )", $query['join'] );
+ $this->assertStringContainsString( "address_shipping.country = 'NL'", $query['where'] );
+ // Address fields must not fall back to order-meta joins (the meta rows don't exist under HPOS).
+ $this->assertStringNotContainsString( 'wc_orders_meta', $query['join'] );
+ }
+
+ /**
+ * @testdox Should build a DST-transition-aware CASE fallback for named timezones when CONVERT_TZ is unavailable.
+ */
+ public function test_build_query_timezone_fallback_is_dst_transition_aware(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+ update_option( 'timezone_string', 'Europe/Berlin' );
+ // A stale cached offset must not leak into the fallback.
+ update_option( 'gmt_offset', 0 );
+
+ $builder = new HposLegacyOrderReportQueryBuilder();
+
+ $base_args = array(
+ 'data' => array(
+ 'post_date' => array(
+ 'type' => 'post_data',
+ 'function' => null,
+ 'name' => 'post_date',
+ ),
+ ),
+ 'group_by' => 'YEAR(posts.post_date)',
+ 'filter_range' => true,
+ 'order_types' => array( 'shop_order' ),
+ );
+
+ // Range spanning the 2026-03-29 01:00 UTC spring transition: rows are shifted by
+ // the offset in effect when they were created (UTC+1 before, UTC+2 after).
+ $query = $builder->build_query(
+ $base_args,
+ strtotime( '2026-03-15 00:00:00' ),
+ strtotime( '2026-04-15 00:00:00' )
+ );
+ $this->assertStringContainsString(
+ "CASE WHEN orders.date_created_gmt < '2026-03-29 01:00:00' " .
+ 'THEN DATE_ADD(orders.date_created_gmt, INTERVAL 3600 SECOND) ' .
+ 'ELSE DATE_ADD(orders.date_created_gmt, INTERVAL 7200 SECOND) END',
+ $query['group_by']
+ );
+
+ // No transition inside the range: a single shift with the range's offset (winter,
+ // UTC+1), not the current gmt_offset option (set to 0 above).
+ $query = $builder->build_query(
+ $base_args,
+ strtotime( '2026-01-01 00:00:00' ),
+ strtotime( '2026-01-31 00:00:00' )
+ );
+ $this->assertStringContainsString( 'IFNULL(CONVERT_TZ', $query['group_by'] );
+ $this->assertStringContainsString( 'DATE_ADD(orders.date_created_gmt, INTERVAL 3600 SECOND)', $query['group_by'] );
+ $this->assertStringNotContainsString( 'CASE', $query['group_by'] );
+ $this->assertStringNotContainsString( 'INTERVAL 0 SECOND', $query['group_by'] );
+
+ // With filter_range off the passed dates only describe the report page's selection,
+ // not the rows the query fetches (e.g. sparklines always read recent orders), so the
+ // fallback must use the trailing-year window: for Berlin that always spans both DST
+ // transitions and yields a CASE, never the winter-pinned single shift above.
+ $base_args['filter_range'] = false;
+ $query = $builder->build_query(
+ $base_args,
+ strtotime( '2026-01-01 00:00:00' ),
+ strtotime( '2026-01-31 00:00:00' )
+ );
+ $this->assertStringContainsString( 'CASE WHEN orders.date_created_gmt', $query['group_by'] );
+ }
+
+ /**
+ * @testdox Should build joins required by typed where rows even without a matching data entry, like the CPT path.
+ */
+ public function test_build_query_builds_joins_from_where_rows(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ 'ID' => array(
+ 'type' => 'post_data',
+ 'function' => 'COUNT',
+ 'name' => 'total_orders',
+ ),
+ ),
+ 'where' => array(
+ array(
+ 'type' => 'order_item',
+ 'key' => 'order_items.order_item_type',
+ 'value' => 'coupon',
+ 'operator' => '=',
+ ),
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $this->assertStringContainsString( 'woocommerce_order_items AS order_items ON orders.id = order_items.order_id', $query['join'] );
+ $this->assertStringContainsString( "AND order_items.order_item_type = 'coupon'", $query['where'] );
+ $this->assertStringNotContainsString( 'order_items', $query['select'] );
+ }
+
+ /**
+ * @testdox Should compare plain post_date where predicates against the GMT column so the date index stays usable.
+ */
+ public function test_build_query_where_post_date_predicate_compares_gmt_column(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+ update_option( 'timezone_string', 'Europe/Berlin' );
+
+ $builder = new HposLegacyOrderReportQueryBuilder();
+
+ // Sparkline shape: no filter_range, `post_date >` is the only date bound.
+ $sparkline_args = array(
+ 'data' => array(
+ '_order_total' => array(
+ 'type' => 'meta',
+ 'function' => 'SUM',
+ 'name' => 'sparkline_value',
+ ),
+ 'post_date' => array(
+ 'type' => 'post_data',
+ 'function' => '',
+ 'name' => 'post_date',
+ ),
+ ),
+ 'where' => array(
+ array(
+ 'key' => 'post_date',
+ 'value' => '2026-07-14',
+ 'operator' => '>',
+ ),
+ ),
+ 'group_by' => 'YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)',
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ );
+
+ $query = $builder->build_query( $sparkline_args, 0, 0 );
+
+ // Berlin is UTC+2 on that date, so local midnight maps back two hours.
+ $this->assertStringContainsString( "orders.date_created_gmt > '2026-07-13 22:00:00'", $query['where'] );
+ $this->assertStringNotContainsString( 'CONVERT_TZ', $query['where'] );
+ $this->assertStringNotContainsString( 'DATE_ADD', $query['where'] );
+ // Row bucketing still converts per row.
+ $this->assertStringContainsString( 'CONVERT_TZ', $query['group_by'] );
+
+ // Values that are not plain date/datetime strings keep the translated per-row expression.
+ $sparkline_args['where'][0]['value'] = 'abc';
+
+ $query = $builder->build_query( $sparkline_args, 0, 0 );
+
+ $this->assertStringContainsString( 'CONVERT_TZ', $query['where'] );
+ $this->assertStringContainsString( "> 'abc'", $query['where'] );
+ }
+
+ /**
+ * @testdox Should skip where predicates with an empty IN list instead of emitting broken SQL.
+ */
+ public function test_build_query_skips_empty_in_predicates(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ 'ID' => array(
+ 'type' => 'post_data',
+ 'function' => 'COUNT',
+ 'name' => 'total_orders',
+ ),
+ ),
+ 'where' => array(
+ array(
+ 'key' => 'posts.ID',
+ 'value' => array(),
+ 'operator' => 'IN',
+ ),
+ ),
+ 'where_meta' => array(
+ // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value
+ array(
+ 'meta_key' => '_some_meta',
+ 'meta_value' => array(),
+ 'operator' => 'NOT IN',
+ ),
+ // phpcs:enable
+ ),
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $this->assertStringNotContainsString( 'IN ()', $query['where'] );
+ $this->assertStringNotContainsString( 'orders.id IN', $query['where'] );
+ $this->assertStringNotContainsString( '_some_meta', $query['where'] );
+ }
+
+ /**
+ * @testdox Should translate only word-bounded ID and post_date tokens, leaving longer identifiers untouched.
+ */
+ public function test_translate_legacy_sql_fragment_respects_word_boundaries(): void {
+ OrderHelper::toggle_cot_feature_and_usage( true );
+ update_option( 'timezone_string', '' );
+ update_option( 'gmt_offset', 0 );
+
+ $query = ( new HposLegacyOrderReportQueryBuilder() )->build_query(
+ array(
+ 'data' => array(
+ 'ID' => array(
+ 'type' => 'post_data',
+ 'function' => null,
+ 'name' => 'id',
+ ),
+ ),
+ 'group_by' => 'posts.post_date_gmt, product_ID, ID, YEAR(posts.post_date)',
+ 'order_by' => 'posts.ID DESC',
+ 'filter_range' => false,
+ 'order_types' => array( 'shop_order' ),
+ ),
+ 0,
+ 0
+ );
+
+ $local_date_expression = 'DATE_ADD(orders.date_created_gmt, INTERVAL 0 SECOND)';
+
+ // Tokens embedded in longer identifiers survive untranslated.
+ $this->assertStringContainsString( 'posts.post_date_gmt', $query['group_by'] );
+ $this->assertStringContainsString( 'product_ID', $query['group_by'] );
+ // Word-bounded tokens translate as before.
+ $this->assertStringContainsString( "YEAR({$local_date_expression})", $query['group_by'] );
+ $this->assertStringContainsString( ' orders.id,', $query['group_by'] );
+ $this->assertSame( 'ORDER BY orders.id DESC', $query['order_by'] );
+ }
+}