Commit c725aac2a42 for woocommerce
commit c725aac2a426bae735943d13e810ff8376104d3f
Author: Miroslav Mitev <m1r0@users.noreply.github.com>
Date: Thu Sep 3 14:39:32 2026 +0300
Fix Analytics tax reports losing tax lines that share a rate id (#67896)
* Fix Analytics tax reports losing tax lines that share a rate id
diff --git a/plugins/woocommerce/changelog/38347-fix-tax-lookup-grain b/plugins/woocommerce/changelog/38347-fix-tax-lookup-grain
new file mode 100644
index 00000000000..e1975211095
--- /dev/null
+++ b/plugins/woocommerce/changelog/38347-fix-tax-lookup-grain
@@ -0,0 +1,4 @@
+Significance: minor
+Type: fix
+
+Store one wc_order_tax_lookup row per tax order item, so Analytics tax reports keep every tax line of an order, including lines that share a tax rate id, and drop rows for tax lines that have been removed.
diff --git a/plugins/woocommerce/changelog/38347-tax-lookup-failed-import-retry b/plugins/woocommerce/changelog/38347-tax-lookup-failed-import-retry
new file mode 100644
index 00000000000..d846c285327
--- /dev/null
+++ b/plugins/woocommerce/changelog/38347-tax-lookup-failed-import-retry
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Keep an order on the Analytics failed imports list when its tax lookup rows could not be written, so a retry that fails again stays visible instead of reporting success.
diff --git a/plugins/woocommerce/changelog/38347-tax-lookup-grain-schema b/plugins/woocommerce/changelog/38347-tax-lookup-grain-schema
new file mode 100644
index 00000000000..bfc93658e8a
--- /dev/null
+++ b/plugins/woocommerce/changelog/38347-tax-lookup-grain-schema
@@ -0,0 +1,4 @@
+Significance: minor
+Type: dev
+
+wc_order_tax_lookup gains an order_item_id column and is re-keyed on (order_id, tax_rate_id, order_item_id), so it now holds one row per tax line instead of one row per tax rate. Code summing the table is unaffected and code counting rows will see more of them. Code writing to the table with $wpdb->replace() on (order_id, tax_rate_id) lands on the new column's zero default, which no longer replaces the rows WooCommerce writes for that order, so both are counted and the order's tax is reported twice; such code needs to write order_item_id too. Existing rows are rebuilt in the background, and can be rebuilt by hand from WooCommerce > Status > Tools.
diff --git a/plugins/woocommerce/includes/class-wc-install.php b/plugins/woocommerce/includes/class-wc-install.php
index fed5ac272b1..1741b076035 100644
--- a/plugins/woocommerce/includes/class-wc-install.php
+++ b/plugins/woocommerce/includes/class-wc-install.php
@@ -354,6 +354,9 @@ class WC_Install {
'wc_update_1120_remove_abandoned_cart_recovery',
'wc_update_1120_migrate_stock_notifications_alpha_constant',
),
+ '11.2.0-1' => array(
+ 'wc_update_11201_migrate_tax_lookup_order_items',
+ ),
);
/**
@@ -1711,6 +1714,42 @@ class WC_Install {
}
}
+ /**
+ * Re-key wc_order_tax_lookup by tax order item. The new column has to join the primary key,
+ * which dbDelta cannot do, so both changes run here as one table rebuild. Rows already in
+ * the table land on the column's zero default and keep reporting on their tax rate id alone
+ * until OrderTaxLookupMigrator has been through them.
+ */
+ if ( $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_order_tax_lookup';" ) ) {
+ $tax_lookup_alterations = array();
+
+ if ( ! $wpdb->get_var( "SHOW COLUMNS FROM `{$wpdb->prefix}wc_order_tax_lookup` LIKE 'order_item_id';" ) ) {
+ $tax_lookup_alterations[] = 'ADD COLUMN order_item_id bigint(20) unsigned NOT NULL DEFAULT 0 AFTER tax_rate_id';
+ }
+
+ if ( 3 > $wpdb->get_var( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '{$wpdb->prefix}wc_order_tax_lookup' AND INDEX_NAME = 'PRIMARY'" ) ) {
+ $tax_lookup_alterations[] = 'DROP PRIMARY KEY, ADD PRIMARY KEY (order_id, tax_rate_id, order_item_id)';
+ }
+
+ if ( $tax_lookup_alterations ) {
+ $tax_lookup_altered = $wpdb->query( "ALTER TABLE {$wpdb->prefix}wc_order_tax_lookup " . implode( ', ', $tax_lookup_alterations ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input, the fragments are hardcoded above.
+
+ // A re-key that failed is otherwise invisible: the sync keeps writing rows in the
+ // released shape and the reports keep reading them, so the store quietly misses the
+ // fix. Leave a trace, and name the tool that retries the change.
+ if ( false === $tax_lookup_altered ) {
+ wc_get_logger()->error(
+ sprintf(
+ 'Could not re-key %1$s by tax order item: %2$s. Analytics tax reports keep reading the way they did, and running "Verify base database tables" under WooCommerce > Status > Tools retries the change.',
+ $wpdb->prefix . 'wc_order_tax_lookup',
+ '' !== $wpdb->last_error ? $wpdb->last_error : 'unknown error'
+ ),
+ array( 'source' => 'wc-order-tax-lookup-migration' )
+ );
+ }
+ }
+ }
+
/**
* Change wp_woocommerce_sessions schema to use a bigint auto increment field instead of char(32) field as
* the primary key as it is not a good practice to use a char(32) field as the primary key of a table and as
@@ -2064,11 +2103,12 @@ CREATE TABLE {$wpdb->prefix}wc_order_product_lookup (
CREATE TABLE {$wpdb->prefix}wc_order_tax_lookup (
order_id bigint(20) unsigned NOT NULL,
tax_rate_id bigint(20) unsigned NOT NULL,
+ order_item_id bigint(20) unsigned NOT NULL DEFAULT 0,
date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
shipping_tax double DEFAULT 0 NOT NULL,
order_tax double DEFAULT 0 NOT NULL,
total_tax double DEFAULT 0 NOT NULL,
- PRIMARY KEY (order_id, tax_rate_id),
+ PRIMARY KEY (order_id, tax_rate_id, order_item_id),
KEY tax_rate_id (tax_rate_id),
KEY date_created (date_created)
) $collate;
diff --git a/plugins/woocommerce/includes/class-woocommerce.php b/plugins/woocommerce/includes/class-woocommerce.php
index 9791b115d7c..4f00fbe5dd0 100644
--- a/plugins/woocommerce/includes/class-woocommerce.php
+++ b/plugins/woocommerce/includes/class-woocommerce.php
@@ -435,6 +435,7 @@ final class WooCommerce {
$container->get( Automattic\WooCommerce\Internal\StockNotifications\StockNotifications::class )->register();
$container->get( Automattic\WooCommerce\Internal\ScheduledSalePriceReconciler::class )->register();
$container->get( Automattic\WooCommerce\Internal\OrderWithdrawal\OrderWithdrawalController::class )->register();
+ $container->get( Automattic\WooCommerce\Internal\Admin\OrderTaxLookupMigrator::class )->register();
// Classes inheriting from RestApiControllerBase.
$container->get( Automattic\WooCommerce\Internal\ReceiptRendering\ReceiptRenderingRestController::class )->register();
diff --git a/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php b/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php
index 390bfcff325..96ee34ffaaf 100644
--- a/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php
+++ b/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php
@@ -11,6 +11,8 @@ use Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskLists;
use Automattic\WooCommerce\Admin\Notes\Notes;
use Automattic\WooCommerce\Internal\Admin\Notes\UnsecuredReportFiles;
use Automattic\WooCommerce\Admin\ReportExporter;
+use Automattic\WooCommerce\Internal\Admin\OrderTaxLookupMigrator;
+use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessingController;
/**
* Update order stats `status` index length.
@@ -307,3 +309,20 @@ function wc_update_1050_add_idx_user_email() {
$wpdb->query( "ALTER TABLE {$wpdb->prefix}woocommerce_downloadable_product_permissions ADD INDEX idx_user_email (user_email(100))" );
}
}
+
+/**
+ * Queue the rebuild of `wc_order_tax_lookup` rows recorded before the table held one row per tax
+ * order item.
+ *
+ * The rebuild runs through BatchProcessingController, which owns the batching, the retries and the
+ * "Rebuild analytics tax data" tool on WooCommerce > Status > Tools. Until it has been through an
+ * order, that order's rows keep reporting the way they did before the column existed, so nothing
+ * waits on this finishing.
+ *
+ * @since 11.2.0
+ *
+ * @return void
+ */
+function wc_update_11201_migrate_tax_lookup_order_items() {
+ wc_get_container()->get( BatchProcessingController::class )->enqueue_processor( OrderTaxLookupMigrator::class );
+}
diff --git a/plugins/woocommerce/includes/rest-api/Controllers/Version2/class-wc-rest-system-status-tools-v2-controller.php b/plugins/woocommerce/includes/rest-api/Controllers/Version2/class-wc-rest-system-status-tools-v2-controller.php
index f7652640ee8..8dd6cbc9993 100644
--- a/plugins/woocommerce/includes/rest-api/Controllers/Version2/class-wc-rest-system-status-tools-v2-controller.php
+++ b/plugins/woocommerce/includes/rest-api/Controllers/Version2/class-wc-rest-system-status-tools-v2-controller.php
@@ -236,11 +236,14 @@ class WC_REST_System_Status_Tools_V2_Controller extends WC_REST_Controller {
);
if ( method_exists( 'WC_Install', 'verify_base_tables' ) ) {
$tools['verify_db_tables'] = array(
- 'name' => __( 'Verify base database tables', 'woocommerce' ),
- 'button' => __( 'Verify database', 'woocommerce' ),
- 'desc' => sprintf(
+ 'name' => __( 'Verify base database tables', 'woocommerce' ),
+ 'button' => __( 'Verify database', 'woocommerce' ),
+ 'desc' => sprintf(
__( 'Verify if all base database tables are present.', 'woocommerce' )
),
+ // Re-creating tables changes what schema-dependent tools can offer, so re-render
+ // the list this tool is part of.
+ 'requires_refresh' => true,
);
}
diff --git a/plugins/woocommerce/phpstan-baseline.neon b/plugins/woocommerce/phpstan-baseline.neon
index c7c63c7af9b..231f6560797 100644
--- a/plugins/woocommerce/phpstan-baseline.neon
+++ b/plugins/woocommerce/phpstan-baseline.neon
@@ -43695,12 +43695,6 @@ parameters:
count: 2
path: src/Admin/API/Reports/Taxes/DataStore.php
- -
- message: '#^Cannot call method date\(\) on WC_DateTime\|null\.$#'
- identifier: method.nonObject
- count: 1
- path: src/Admin/API/Reports/Taxes/DataStore.php
-
-
message: '#^Method Automattic\\WooCommerce\\Admin\\API\\Reports\\Taxes\\DataStore\:\:add_from_sql_params\(\) has no return type specified\.$#'
identifier: missingType.return
diff --git a/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php b/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
index 34c78c974ac..4bf24e42e53 100644
--- a/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
+++ b/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
@@ -162,6 +162,19 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
$this->add_from_sql_params( $query_args, $order_status_filter );
$this->subquery->add_sql_clause( 'where', "AND itemmeta_rate_id.meta_value = {$order_tax_lookup_table}.tax_rate_id" );
+
+ /*
+ * Narrow the rate match to the single tax line the row was written for. The rate id on its
+ * own fans one lookup row out across every line of the order that shares it, which is how
+ * an order carrying several lines on one rate id came to report the wrong tax.
+ *
+ * Rows recorded before the lookup held one row per tax order item sit at the column's zero
+ * default and have no line to narrow to, so they go on matching the rate id alone, which is
+ * how the report read them all along. OrderTaxLookupMigrator rebuilds them in the
+ * background.
+ */
+ $this->subquery->add_sql_clause( 'where', "AND ( {$order_tax_lookup_table}.order_item_id = 0 OR {$order_tax_lookup_table}.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id )" );
+
if ( isset( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) {
$allowed_taxes = self::get_filtered_ids( $query_args, 'taxes' );
$this->subquery->add_sql_clause( 'where', "AND {$order_tax_lookup_table}.tax_rate_id IN ({$allowed_taxes})" );
@@ -282,9 +295,50 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
return $order_by;
}
+ /**
+ * Cache of lookup_is_keyed_by_order_item(). Only `true` sticks: the re-key can land while a
+ * request runs (the "Verify base database tables" tool re-keys right before the tools list
+ * re-renders), and a cached `false` would outlive it.
+ *
+ * @var bool|null
+ */
+ private static $lookup_keyed_by_order_item = null;
+
+ /**
+ * Whether the lookup's primary key includes the tax order item.
+ *
+ * The re-key in `WC_Install::create_tables()` can fail on a large store, and dbDelta adds the
+ * `order_item_id` column either way. Writing a real tax order item id into a table still keyed
+ * on (order_id, tax_rate_id) collapses the lines sharing a rate into one row that the report
+ * then matches to a single line, which reads worse than it did before the column existed.
+ *
+ * `OrderTaxLookupMigrator` reads this too: a rebuild over such a table would write every row
+ * back at zero while stepping the cursor past it, so it waits instead.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ * @since 11.2.0
+ *
+ * @return bool
+ */
+ public static function lookup_is_keyed_by_order_item(): bool {
+ global $wpdb;
+
+ if ( true !== self::$lookup_keyed_by_order_item ) {
+ $table_name = self::get_db_table_name();
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ self::$lookup_keyed_by_order_item = (bool) $wpdb->get_var( "SHOW KEYS FROM `{$table_name}` WHERE Key_name = 'PRIMARY' AND Column_name = 'order_item_id'" );
+ }
+
+ return self::$lookup_keyed_by_order_item;
+ }
+
/**
* Create or update an entry in the wc_order_tax_lookup table for an order.
*
+ * Writes one row per tax order item, in a single statement so that a write that does not land
+ * rebuilds none of the order's tax lines rather than some of them, then drops the rows the
+ * order held before and no longer carries.
+ *
* @param int $order_id Order ID.
* @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success.
*/
@@ -292,47 +346,150 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
global $wpdb;
$order = wc_get_order( $order_id );
- if ( ! $order ) {
+
+ // An order with no creation date has nothing to date its lookup rows by, and
+ // `WC_Data::set_date_prop()` leaves the date null for a zero datetime, not only for a
+ // missing one. `OrdersScheduler::import()` leaves such an order out of the reports for the
+ // same reason.
+ if ( ! $order || ! $order->get_date_created( 'edit' ) ) {
return -1;
}
- $tax_items = $order->get_items( OrderItemType::TAX );
- $num_updated = 0;
+ $table_name = self::get_db_table_name();
+ $date_created = $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format );
+ $tax_items = $order->get_items( OrderItemType::TAX );
+ $keyed_by_item = self::lookup_is_keyed_by_order_item();
+
+ // Read the rows the order already holds, so that the prune below names the rows this sync
+ // found, the way the Products and Coupons stores do. Deleting everything outside the
+ // snapshot instead would let two syncs that read different tax lines delete each other's
+ // rows and leave the order with none.
+ $existing_rows = $wpdb->get_results(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT tax_rate_id, order_item_id FROM {$table_name} WHERE order_id = %d",
+ $order->get_id()
+ ),
+ ARRAY_A
+ );
+
+ // Nothing has been written yet, so the order keeps the rows it came in with. Carrying on
+ // would prune against an empty snapshot, which leaves every row the order no longer
+ // carries in place for the reports to go on counting.
+ if ( $wpdb->last_error ) {
+ wc_get_logger()->error(
+ "Could not read the analytics tax lookup rows of order {$order->get_id()}. The order keeps the rows it had and reports the way it did before.",
+ array( 'source' => 'wc-order-tax-lookup' )
+ );
+
+ return false;
+ }
+
+ $stale = array();
+ $rows = array();
+ $values = array();
+
+ foreach ( $existing_rows as $existing_row ) {
+ $stale[ $existing_row['tax_rate_id'] . '-' . $existing_row['order_item_id'] ] = array(
+ (int) $existing_row['tax_rate_id'],
+ (int) $existing_row['order_item_id'],
+ );
+ }
foreach ( $tax_items as $tax_item ) {
- $result = $wpdb->replace(
- self::get_db_table_name(),
- array(
- 'order_id' => $order->get_id(),
- 'date_created' => $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ),
- 'tax_rate_id' => $tax_item->get_rate_id(),
- 'shipping_tax' => $tax_item->get_shipping_tax_total(),
- 'order_tax' => $tax_item->get_tax_total(),
- 'total_tax' => (float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total(),
- ),
- array(
- '%d',
- '%s',
- '%d',
- '%f',
- '%f',
- '%f',
+ // Leaving the column at zero on a table the re-key never reached keeps the row in the
+ // shape the released report reads, rather than collapsing the order's tax lines into
+ // one row the report matches to a single line.
+ $order_item_id = $keyed_by_item ? $tax_item->get_id() : 0;
+ $tax_rate_id = (int) $tax_item->get_rate_id();
+
+ // A row this sync is about to write is not stale. The key is the rate and the item
+ // together, so a line whose rate id has changed leaves behind the row it held before.
+ unset( $stale[ $tax_rate_id . '-' . $order_item_id ] );
+
+ $rows[] = '(%d, %s, %d, %d, %f, %f, %f)';
+
+ array_push(
+ $values,
+ $order->get_id(),
+ $date_created,
+ $tax_rate_id,
+ $order_item_id,
+ $tax_item->get_shipping_tax_total(),
+ $tax_item->get_tax_total(),
+ (float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total()
+ );
+ }
+
+ // One statement for the whole order. Rebuilding only some of its lines would leave a row
+ // still on the order item column's zero default beside the rows written next to it, and
+ // that row stands in for every line of the order sharing its rate, so the reports would
+ // count those lines twice.
+ if ( $rows ) {
+ $written = $wpdb->query(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table name is not user input, and the value placeholders are built above, one set per tax line.
+ "REPLACE INTO {$table_name} (order_id, date_created, tax_rate_id, order_item_id, shipping_tax, order_tax, total_tax) VALUES " . implode( ', ', $rows ),
+ $values
)
);
+ if ( false === $written ) {
+ wc_get_logger()->error(
+ "Could not write the analytics tax lookup rows of order {$order->get_id()}. The order keeps the rows it had and reports the way it did before.",
+ array( 'source' => 'wc-order-tax-lookup' )
+ );
+
+ return false;
+ }
+ }
+
+ // Drop the rows the order came in with that it no longer carries, which includes the rows
+ // written before the order item column existed, since those sit at zero. Prune only once
+ // the writes have landed, so a write that did not land leaves the order with the rows it
+ // came in with.
+ if ( $stale ) {
+ $keys = array();
+ $key_values = array( $order->get_id() );
+
+ foreach ( $stale as $stale_key ) {
+ $keys[] = '(%d, %d)';
+ array_push( $key_values, $stale_key[0], $stale_key[1] );
+ }
+
+ $deleted = $wpdb->query(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table name is not user input, and the key placeholders are built above, one pair per stale row.
+ "DELETE FROM {$table_name} WHERE order_id = %d AND (tax_rate_id, order_item_id) IN (" . implode( ', ', $keys ) . ')',
+ $key_values
+ )
+ );
+
+ // A row the order no longer carries goes on being counted by the reports, so a prune
+ // that failed is not a sync that succeeded.
+ if ( false === $deleted ) {
+ wc_get_logger()->error(
+ "Could not drop the analytics tax lookup rows order {$order->get_id()} no longer carries. Its old rows stand beside the rows just written, so the reports count those tax lines twice until the order is synced again.",
+ array( 'source' => 'wc-order-tax-lookup' )
+ );
+
+ return false;
+ }
+ }
+
+ foreach ( $tax_items as $tax_item ) {
/**
* Fires when tax's reports are updated.
*
* @param int $tax_rate_id Tax Rate ID.
* @param int $order_id Order ID.
+ *
+ * @since 4.0.0
*/
do_action( 'woocommerce_analytics_update_tax', $tax_item->get_rate_id(), $order->get_id() );
-
- // Sum the rows affected. Using REPLACE can affect 2 rows if the row already exists.
- $num_updated += 2 === intval( $result ) ? 1 : intval( $result );
}
- return ( count( $tax_items ) === $num_updated );
+ return true;
}
/**
diff --git a/plugins/woocommerce/src/Admin/API/Reports/Taxes/Stats/DataStore.php b/plugins/woocommerce/src/Admin/API/Reports/Taxes/Stats/DataStore.php
index 73a872753a3..c7b20689caa 100644
--- a/plugins/woocommerce/src/Admin/API/Reports/Taxes/Stats/DataStore.php
+++ b/plugins/woocommerce/src/Admin/API/Reports/Taxes/Stats/DataStore.php
@@ -234,6 +234,10 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
'from_clause' => $this->interval_query->get_sql_clause( 'join' ),
'where_time_clause' => $this->interval_query->get_sql_clause( 'where_time' ),
'where_clause' => $this->interval_query->get_sql_clause( 'where' ),
+ // The segmenter reads this key on every store. This one never calls
+ // get_limit_sql_params(), unlike the sibling stats stores, so the clause is empty and
+ // the segment queries go unlimited, as they always have.
+ 'limit' => $this->get_sql_clause( 'limit' ),
);
$segmenter = new Segmenter( $query_args, $this->report_columns );
$totals[0]['segments'] = $segmenter->get_totals_segments( $totals_query, $table_name );
diff --git a/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php b/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php
new file mode 100644
index 00000000000..ac935f6b761
--- /dev/null
+++ b/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php
@@ -0,0 +1,372 @@
+<?php
+/**
+ * OrderTaxLookupMigrator class file.
+ */
+
+declare( strict_types=1 );
+
+namespace Automattic\WooCommerce\Internal\Admin;
+
+use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
+use Automattic\WooCommerce\Admin\API\Reports\Orders\Stats\DataStore as OrderStatsDataStore;
+use Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore as TaxesDataStore;
+use Automattic\WooCommerce\Internal\Admin\Schedulers\OrdersScheduler;
+use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessingController;
+use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessorInterface;
+use Automattic\WooCommerce\Internal\RegisterHooksInterface;
+use Exception;
+
+defined( 'ABSPATH' ) || exit;
+
+/**
+ * Rebuilds the `wc_order_tax_lookup` rows of orders recorded before the table held one row per tax
+ * order item, by re-syncing each order through the Taxes data store.
+ *
+ * Rows written before then carry the zero default of the `order_item_id` column, and the Taxes
+ * report keeps matching those on their tax rate id alone, the way it did before the column
+ * existed. So reporting stays as it was while this runs, and an order the processor cannot rebuild
+ * keeps reporting the way it did.
+ *
+ * Additionally, this class manages the "Rebuild analytics tax data" tool.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ * @since 11.2.0
+ */
+class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksInterface {
+
+ /**
+ * Option holding the highest order id the processor has been through.
+ *
+ * The cursor is what bounds progress, so it outlives the run. An order the processor could not
+ * rebuild keeps its rows at zero; without the cursor every later batch would pick that order up
+ * again and the processor would never reach the end of the table. Such an order is recorded as
+ * a failed analytics import instead, which is retried from Analytics settings. That is also why
+ * the option is left behind once the pass is done: clearing it would put those orders back in
+ * front of the next pass. Delete it by hand to run the rebuild over the whole table again.
+ *
+ * @var string
+ */
+ const CURSOR_OPTION = 'woocommerce_order_tax_lookup_migration_last_order_id';
+
+ /**
+ * How far `get_total_pending_count()` counts before it reports "this many or more".
+ *
+ * Nothing indexes the tax order item column, so counting every order left to rebuild reads the
+ * lookup table end to end, and Status > Tools runs that count on every render. The tool only
+ * has to say whether there is work left and roughly how much of it, so stop counting once
+ * there is enough to report.
+ *
+ * @var int
+ */
+ const PENDING_COUNT_LIMIT = 1000;
+
+ /**
+ * Register this class instance to the appropriate hooks.
+ *
+ * @return void
+ */
+ public function register() {
+ add_filter( 'woocommerce_debug_tools', array( $this, 'handle_woocommerce_debug_tools' ), 999, 1 );
+ }
+
+ /**
+ * Get a user-friendly name for this processor.
+ *
+ * @return string Name of the processor.
+ */
+ public function get_name(): string {
+ return 'Order tax lookup tax order item migrator';
+ }
+
+ /**
+ * Get a user-friendly description for this processor.
+ *
+ * @return string Description of what this processor does.
+ */
+ public function get_description(): string {
+ return 'Rebuilds wc_order_tax_lookup rows recorded before the table held one row per tax order item, so that Analytics tax reports account for every tax line an order carries.';
+ }
+
+ /**
+ * Get the number of orders left to go through that still hold rows in the shape that predates
+ * the tax order item column, up to PENDING_COUNT_LIMIT.
+ *
+ * Counts from the cursor, the same place `get_next_batch_to_process()` reads from, so the
+ * number the tool shows is the number the rebuild will actually get through. Counting the whole
+ * table instead would leave the tool offering a run over orders every pass steps past.
+ *
+ * @return int Number of orders pending processing, at most PENDING_COUNT_LIMIT.
+ */
+ public function get_total_pending_count(): int {
+ global $wpdb;
+
+ // While the lookup is not keyed by tax order item there is nothing the rebuild can change,
+ // so no order counts as pending. See get_next_batch_to_process().
+ if ( ! TaxesDataStore::lookup_is_keyed_by_order_item() ) {
+ return 0;
+ }
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ return (int) $wpdb->get_var(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT COUNT(*) FROM ( SELECT DISTINCT order_id FROM {$table_name} WHERE order_id > %d AND order_item_id = 0 LIMIT %d ) AS pending",
+ $this->get_cursor(),
+ self::PENDING_COUNT_LIMIT
+ )
+ );
+ }
+
+ /**
+ * Returns the ids of the next orders to rebuild.
+ *
+ * @param int $size Maximum size of the batch to be returned.
+ *
+ * @throws Exception On a database error, so that an empty batch is never mistaken for the end
+ * of the table.
+ *
+ * @return array Batch of order ids, containing $size or less items.
+ */
+ public function get_next_batch_to_process( int $size ): array {
+ global $wpdb;
+
+ // On a table the re-key in `WC_Install::create_tables()` never reached, the sync would
+ // write every row back at zero and the pass would park the cursor at the end of the table
+ // with nothing rebuilt. Hand out nothing instead: an empty batch retires the processor
+ // with the cursor where it stands, so the rebuild is still on offer once the re-key has
+ // landed.
+ if ( ! TaxesDataStore::lookup_is_keyed_by_order_item() ) {
+ return array();
+ }
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ $order_ids = $wpdb->get_col(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT DISTINCT order_id FROM {$table_name} WHERE order_id > %d AND order_item_id = 0 ORDER BY order_id ASC LIMIT %d",
+ $this->get_cursor(),
+ $size
+ )
+ );
+
+ if ( $wpdb->last_error ) {
+ // An empty batch reads as "nothing left to do" and retires the processor, which would
+ // leave the rest of the table behind. Report the failure instead, which fails the
+ // scheduled action and leaves the controller's watchdog to schedule another attempt.
+ // The controller only counts failures its process_batch() call throws, so a database
+ // that stays broken is retried rather than retired.
+ throw new Exception( $wpdb->last_error ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
+ }
+
+ return array_map( 'absint', $order_ids );
+ }
+
+ /**
+ * Re-sync the orders in the batch, which writes one row per tax order item and, once they are
+ * all written, drops whatever the order no longer carries.
+ *
+ * @param array $batch Batch of order ids, as returned by 'get_next_batch_to_process'.
+ *
+ * @return void
+ */
+ public function process_batch( array $batch ): void {
+ global $wpdb;
+
+ if ( empty( $batch ) ) {
+ return;
+ }
+
+ foreach ( $batch as $order_id ) {
+ $order_id = (int) $order_id;
+ $synced = TaxesDataStore::sync_order_taxes( $order_id );
+
+ // The reports only see lookup rows they can join to a `wc_order_stats` row, so an order
+ // without one is gone as far as Analytics is concerned and its rows can go. A failed
+ // `wc_get_order()` is not the same thing: it also fails while the plugin that registers
+ // the order's type is deactivated, and that order still has its stats row.
+ if ( -1 === $synced && ! $this->order_has_stats_row( $order_id ) ) {
+ $wpdb->delete( TaxesDataStore::get_db_table_name(), array( 'order_id' => $order_id ), array( '%d' ) );
+ continue;
+ }
+
+ // A write that did not land leaves the order holding the rows it came in with, which
+ // report the way they did before. The cursor steps past it either way, so record it as
+ // a failed analytics import: that is the list Analytics settings offers a retry over,
+ // and the retry re-imports the order, which is the same work this pass could not do.
+ if ( false === $synced ) {
+ wc_get_logger()->error(
+ "Could not rebuild the analytics tax lookup rows of order {$order_id}. The order keeps the rows it had and reports the way it did before. It is recorded as a failed analytics import, so it can be retried from Analytics settings.",
+ array( 'source' => 'wc-order-tax-lookup-migration' )
+ );
+
+ OrdersScheduler::record_failed_order_import( $order_id );
+ }
+ }
+
+ // Step past every order in the batch, including any that could not be rebuilt, which are
+ // left to the failed import retry. See CURSOR_OPTION.
+ update_option( self::CURSOR_OPTION, max( array_map( 'absint', $batch ) ), false );
+
+ ReportsCache::invalidate();
+ }
+
+ /**
+ * Default (preferred) batch size to pass to 'get_next_batch_to_process'.
+ *
+ * A batch is a `wc_get_order()` and a handful of writes per order, so it is sized like the
+ * analytics order importer rather than like a single-query migration.
+ *
+ * @return int Default batch size.
+ */
+ public function get_default_batch_size(): int {
+ return 100;
+ }
+
+ /**
+ * Add the tool to start or stop the background rebuild.
+ *
+ * @param array $tools Old tools array.
+ * @return array Updated tools array.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ */
+ public function handle_woocommerce_debug_tools( array $tools ): array {
+ // A failed re-key would otherwise go unseen here: with no order counting as pending, the
+ // tool would say there is nothing to rebuild. Say what is actually missing instead.
+ if ( ! TaxesDataStore::lookup_is_keyed_by_order_item() ) {
+ $tools['rebuild_analytics_tax_data'] = array(
+ 'name' => __( 'Rebuild analytics tax data', 'woocommerce' ),
+ 'button' => __( 'Rebuild', 'woocommerce' ),
+ 'disabled' => true,
+ 'desc' => __( 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. The database change the rebuild needs is missing on this store. Run "Verify base database tables" to apply it, then come back here.', 'woocommerce' ),
+ );
+
+ return $tools;
+ }
+
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+ $pending_count = $this->get_total_pending_count();
+
+ // The count stops at PENDING_COUNT_LIMIT, so say "or more" rather than a number the store
+ // has already gone past.
+ $pending_label = $pending_count < self::PENDING_COUNT_LIMIT
+ ? number_format_i18n( $pending_count )
+ /* translators: %s: number of orders, where there are at least that many. */
+ : sprintf( __( '%s+', 'woocommerce' ), number_format_i18n( self::PENDING_COUNT_LIMIT ) );
+
+ if ( 0 === $pending_count ) {
+ $tools['rebuild_analytics_tax_data'] = array(
+ 'name' => __( 'Rebuild analytics tax data', 'woocommerce' ),
+ 'button' => __( 'Rebuild', 'woocommerce' ),
+ 'disabled' => true,
+ 'desc' => __( 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. There are currently no orders to rebuild.', 'woocommerce' ),
+ );
+ } elseif ( $batch_processor->is_enqueued( self::class ) ) {
+ $tools['stop_rebuild_analytics_tax_data'] = array(
+ 'name' => __( 'Stop rebuilding analytics tax data', 'woocommerce' ),
+ 'button' => __( 'Stop rebuilding', 'woocommerce' ),
+ 'requires_refresh' => true,
+ 'desc' => sprintf(
+ /* translators: %s: number of orders still to rebuild. */
+ _n(
+ 'This will stop the background process that rebuilds the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. There is currently %s order left to rebuild.',
+ 'This will stop the background process that rebuilds the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. There are currently %s orders left to rebuild.',
+ $pending_count,
+ 'woocommerce'
+ ),
+ $pending_label
+ ),
+ 'callback' => array( $this, 'dequeue' ),
+ );
+ } else {
+ $tools['rebuild_analytics_tax_data'] = array(
+ 'name' => __( 'Rebuild analytics tax data', 'woocommerce' ),
+ 'button' => __( 'Rebuild', 'woocommerce' ),
+ 'requires_refresh' => true,
+ 'desc' => sprintf(
+ /* translators: %s: number of orders to rebuild. */
+ _n(
+ 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. The rebuild happens over time in the background (via Action Scheduler). There is currently %s order to rebuild.',
+ 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept a record of every tax line. The rebuild happens over time in the background (via Action Scheduler). There are currently %s orders to rebuild.',
+ $pending_count,
+ 'woocommerce'
+ ),
+ $pending_label
+ ),
+ 'callback' => array( $this, 'enqueue' ),
+ );
+ }
+
+ return $tools;
+ }
+
+ /**
+ * Start the background rebuild.
+ *
+ * @return string Informative string to show after the tool is triggered in UI.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ */
+ public function enqueue(): string {
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+
+ if ( $batch_processor->is_enqueued( self::class ) ) {
+ return __( 'Background process for rebuilding analytics tax data already started, nothing done.', 'woocommerce' );
+ }
+
+ $batch_processor->enqueue_processor( self::class );
+
+ return __( 'Background process for rebuilding analytics tax data started.', 'woocommerce' );
+ }
+
+ /**
+ * Stop the background rebuild.
+ *
+ * @return string Informative string to show after the tool is triggered in UI.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ */
+ public function dequeue(): string {
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+
+ if ( ! $batch_processor->is_enqueued( self::class ) ) {
+ return __( 'Background process for rebuilding analytics tax data not started, nothing done.', 'woocommerce' );
+ }
+
+ $batch_processor->remove_processor( self::class );
+
+ return __( 'Background process for rebuilding analytics tax data stopped.', 'woocommerce' );
+ }
+
+ /**
+ * Whether the order has a row in the order stats table, which is the table every analytics
+ * report reads orders through.
+ *
+ * @param int $order_id Order id.
+ * @return bool
+ */
+ private function order_has_stats_row( int $order_id ): bool {
+ global $wpdb;
+
+ $table_name = OrderStatsDataStore::get_db_table_name();
+
+ return (bool) $wpdb->get_var(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT 1 FROM {$table_name} WHERE order_id = %d",
+ $order_id
+ )
+ );
+ }
+
+ /**
+ * Highest order id the processor has been through.
+ *
+ * @return int
+ */
+ private function get_cursor(): int {
+ return (int) get_option( self::CURSOR_OPTION, 0 );
+ }
+}
diff --git a/plugins/woocommerce/src/Internal/Admin/Schedulers/OrdersScheduler.php b/plugins/woocommerce/src/Internal/Admin/Schedulers/OrdersScheduler.php
index 001007b5ed8..634ebaf6603 100644
--- a/plugins/woocommerce/src/Internal/Admin/Schedulers/OrdersScheduler.php
+++ b/plugins/woocommerce/src/Internal/Admin/Schedulers/OrdersScheduler.php
@@ -364,7 +364,7 @@ AND status NOT IN ( 'wc-auto-draft', 'trash', 'auto-draft' )
/**
* Imports a single order or refund to update lookup tables for.
- * If an error is encountered in one of the updates, a retry action is scheduled.
+ * An order whose tax lookup rows could not be written stays on the failed imports list.
*
* @internal
* @param int $order_id Order or refund ID.
@@ -407,13 +407,11 @@ AND status NOT IN ( 'wc-auto-draft', 'trash', 'auto-draft' )
return;
}
- $results = array(
- OrdersStatsDataStore::sync_order( $order_id ),
- ProductsDataStore::sync_order_products( $order_id ),
- CouponsDataStore::sync_order_coupons( $order_id ),
- TaxesDataStore::sync_order_taxes( $order_id ),
- CustomersDataStore::sync_order_customer( $order_id ),
- );
+ OrdersStatsDataStore::sync_order( $order_id );
+ ProductsDataStore::sync_order_products( $order_id );
+ CouponsDataStore::sync_order_coupons( $order_id );
+ $taxes_synced = TaxesDataStore::sync_order_taxes( $order_id );
+ CustomersDataStore::sync_order_customer( $order_id );
if ( 'shop_order' === $type ) {
$order_refunds = $order->get_refunds();
@@ -425,8 +423,16 @@ AND status NOT IN ( 'wc-auto-draft', 'trash', 'auto-draft' )
ReportsCache::invalidate();
- // A successful import means the order is no longer missing from analytics.
- self::clear_failed_order_import( $order_id );
+ // A tax sync that did not land leaves the order holding the lookup rows it came in with,
+ // so the import did not finish and the order stays on the list Analytics settings retries
+ // over. Only this sync is read: the other stores return false for writes that changed no
+ // rows as well, so their return value does not tell a failure from an order that was
+ // already up to date.
+ if ( false === $taxes_synced ) {
+ self::record_failed_order_import( $order_id );
+ } else {
+ self::clear_failed_order_import( $order_id );
+ }
/**
* Fires after an order or refund has been imported into Analytics lookup tables
diff --git a/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-reports.php b/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-reports.php
index 6d2fcee85cc..fb6dd888434 100644
--- a/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-reports.php
+++ b/plugins/woocommerce/tests/legacy/framework/helpers/class-wc-helper-reports.php
@@ -21,6 +21,7 @@ class WC_Helper_Reports {
$wpdb->query( 'DELETE FROM ' . \Automattic\WooCommerce\Admin\API\Reports\Products\DataStore::get_db_table_name() ); // @codingStandardsIgnoreLine.
$wpdb->query( 'DELETE FROM ' . \Automattic\WooCommerce\Admin\API\Reports\Coupons\DataStore::get_db_table_name() ); // @codingStandardsIgnoreLine.
$wpdb->query( 'DELETE FROM ' . \Automattic\WooCommerce\Admin\API\Reports\Customers\DataStore::get_db_table_name() ); // @codingStandardsIgnoreLine.
+ $wpdb->query( 'DELETE FROM ' . \Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore::get_db_table_name() ); // @codingStandardsIgnoreLine.
$wpdb->query( "DELETE FROM {$wpdb->wc_category_lookup}" ); // @codingStandardsIgnoreLine.
$category_lookup = \Automattic\WooCommerce\Internal\Admin\CategoryLookup::instance();
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/api/reports-taxes.php b/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/api/reports-taxes.php
index 82d9d6b404f..f2623dfa731 100644
--- a/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/api/reports-taxes.php
+++ b/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/api/reports-taxes.php
@@ -106,19 +106,6 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
$order->set_total( 100 ); // $25 x 4.
$order->save();
- // @todo Remove this once order data is synced to wc_order_tax_lookup
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order->get_id(),
- 'tax_rate_id' => 1,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 2,
- 'order_tax' => 5,
- 'total_tax' => 7,
- )
- );
-
WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
$response = $this->server->dispatch( new WP_REST_Request( 'GET', $this->endpoint ) );
@@ -315,41 +302,6 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
$order->set_total( 109.75 ); // Product + all taxes.
$order->save();
- // @todo Remove this once order data is synced to wc_order_tax_lookup
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order->get_id(),
- 'tax_rate_id' => 1,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 1,
- 'order_tax' => 5,
- 'total_tax' => 6,
- )
- );
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order->get_id(),
- 'tax_rate_id' => 2,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 0.5,
- 'order_tax' => 2.5,
- 'total_tax' => 3,
- )
- );
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order->get_id(),
- 'tax_rate_id' => 3,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 0.25,
- 'order_tax' => 1,
- 'total_tax' => 1.25,
- )
- );
-
WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
// Make the API request.
@@ -549,54 +501,6 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
$order_es_2->save();
$order_es_2->calculate_totals( true );
- // @todo Remove this once order data is synced to wc_order_tax_lookup
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order->get_id(),
- 'tax_rate_id' => 1,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 2,
- 'order_tax' => 5,
- 'total_tax' => 7,
- )
- );
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order_ca->get_id(),
- 'tax_rate_id' => 2,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 2,
- 'order_tax' => 5,
- 'total_tax' => 7,
- )
- );
-
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order_es->get_id(),
- 'tax_rate_id' => 3,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 2,
- 'order_tax' => 5,
- 'total_tax' => 7,
- )
- );
-
- $wpdb->insert(
- $wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order_es_2->get_id(),
- 'tax_rate_id' => 3,
- 'date_created' => gmdate( 'Y-m-d H:i:s' ),
- 'shipping_tax' => 2,
- 'order_tax' => 5,
- 'total_tax' => 7,
- )
- );
-
WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
}
}
diff --git a/plugins/woocommerce/tests/php/includes/class-wc-install-test.php b/plugins/woocommerce/tests/php/includes/class-wc-install-test.php
index f0fb7c791ed..23f00ebc6fc 100644
--- a/plugins/woocommerce/tests/php/includes/class-wc-install-test.php
+++ b/plugins/woocommerce/tests/php/includes/class-wc-install-test.php
@@ -4,11 +4,13 @@ declare( strict_types = 1 );
use Automattic\WooCommerce\Admin\Notes\Note;
use Automattic\WooCommerce\Caches\ProductCountCache;
use Automattic\WooCommerce\Enums\ProductStatus;
+use Automattic\WooCommerce\RestApi\UnitTests\LoggerSpyTrait;
/**
* Class WC_Install_Test.
*/
class WC_Install_Test extends \WC_Unit_Test_Case {
+ use LoggerSpyTrait;
/**
* Test if verify base table can detect missing tables and clear the stored missing table list.
@@ -110,6 +112,106 @@ class WC_Install_Test extends \WC_Unit_Test_Case {
$this->assertEmpty( $db_delta_result );
}
+ /**
+ * dbDelta cannot change a primary key, so wc_order_tax_lookup is re-keyed by a guarded ALTER in
+ * create_tables(). The rows a store carries into it have to survive, and since create_tables()
+ * runs again on every update, the second pass has to leave everything alone.
+ *
+ * @testdox create_tables() re-keys the tax lookup by tax order item, keeps its rows, and runs once.
+ */
+ public function test_create_tables_rekeys_the_order_tax_lookup_by_tax_order_item(): void {
+ global $wpdb;
+
+ // The lookup tables are real rather than temporary, so let this test alter them.
+ remove_filter( 'query', array( $this, '_create_temporary_tables' ) );
+ remove_filter( 'query', array( $this, '_drop_temporary_tables' ) );
+
+ $table = "{$wpdb->prefix}wc_order_tax_lookup";
+ $key = function () use ( $wpdb, $table ) {
+ return $wpdb->get_var( "SHOW KEYS FROM `{$table}` WHERE Key_name = 'PRIMARY' AND Column_name = 'order_item_id'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ };
+ $rows = function () use ( $wpdb, $table ) {
+ return $wpdb->get_results( "SELECT * FROM `{$table}` WHERE order_id = 4242", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ };
+
+ // Put the table back in the shape it held before it was keyed by tax order item.
+ $wpdb->query( "ALTER TABLE `{$table}` DROP PRIMARY KEY, DROP COLUMN order_item_id, ADD PRIMARY KEY (order_id, tax_rate_id)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $wpdb->insert(
+ $table,
+ array(
+ 'order_id' => 4242,
+ 'tax_rate_id' => 7,
+ 'date_created' => '2023-02-10 10:00:00',
+ 'total_tax' => 6.0,
+ )
+ );
+
+ $this->assertEmpty( $key(), 'The table should start out on the released key.' );
+
+ WC_Install::create_tables();
+
+ $this->assertNotEmpty( $key(), 'The primary key should gain the tax order item column.' );
+ $this->assertCount( 1, $rows(), 'The rows a store carried into the re-key should survive it.' );
+ $this->assertSame( 0, (int) $rows()[0]['order_item_id'], 'Rows that predate the column should land on its default and keep reporting on their rate id alone.' );
+
+ $before = $rows();
+ WC_Install::create_tables();
+
+ $this->assertNotEmpty( $key(), 'The second pass should leave the key alone.' );
+ $this->assertSame( $before, $rows(), 'The second pass should leave the rows alone.' );
+
+ $wpdb->delete( $table, array( 'order_id' => 4242 ), array( '%d' ) );
+ add_filter( 'query', array( $this, '_create_temporary_tables' ) );
+ add_filter( 'query', array( $this, '_drop_temporary_tables' ) );
+ }
+
+ /**
+ * The reports read a table the re-key never reached the way they always did, so nothing else
+ * says the store missed the fix.
+ *
+ * @testdox create_tables() logs a tax lookup re-key that did not land.
+ */
+ public function test_create_tables_logs_a_failed_order_tax_lookup_rekey(): void {
+ global $wpdb;
+
+ remove_filter( 'query', array( $this, '_create_temporary_tables' ) );
+ remove_filter( 'query', array( $this, '_drop_temporary_tables' ) );
+
+ $table = "{$wpdb->prefix}wc_order_tax_lookup";
+
+ // Put the table back in the shape it held before it was keyed by tax order item.
+ $wpdb->query( "ALTER TABLE `{$table}` DROP PRIMARY KEY, DROP COLUMN order_item_id, ADD PRIMARY KEY (order_id, tax_rate_id)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+
+ // Fail every ALTER against the table, the way a server that refuses the statement would.
+ $break_alter = function ( $query ) use ( $table ) {
+ if ( 0 === strpos( $query, "ALTER TABLE {$table} " ) ) {
+ return "ALTER TABLE `{$table}_missing` ADD COLUMN broken bigint";
+ }
+
+ return $query;
+ };
+ add_filter( 'query', $break_alter );
+
+ $suppress = $wpdb->suppress_errors( true );
+ WC_Install::create_tables();
+ $wpdb->suppress_errors( $suppress );
+
+ remove_filter( 'query', $break_alter );
+
+ $this->assertLogged( 'error', 'wc_order_tax_lookup', array( 'source' => 'wc-order-tax-lookup-migration' ) );
+
+ // Put the key right again for the tests that follow.
+ WC_Install::create_tables();
+
+ $this->assertNotEmpty(
+ $wpdb->get_var( "SHOW KEYS FROM `{$table}` WHERE Key_name = 'PRIMARY' AND Column_name = 'order_item_id'" ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ 'The re-key should land again once the server accepts the ALTER.'
+ );
+
+ add_filter( 'query', array( $this, '_create_temporary_tables' ) );
+ add_filter( 'query', array( $this, '_drop_temporary_tables' ) );
+ }
+
/**
* Test that delete_obsolete_notes deletes notes.
*/
diff --git a/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php b/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
index f70b26bc70f..c7e0bb0c4cf 100644
--- a/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
+++ b/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
@@ -8,6 +8,8 @@
use Automattic\Jetpack\Constants;
use Automattic\WooCommerce\Blocks\Options as BlockOptions;
use Automattic\WooCommerce\Blocks\Utils\BlockTemplateUtils;
+use Automattic\WooCommerce\Internal\Admin\OrderTaxLookupMigrator;
+use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessingController;
use Automattic\WooCommerce\Internal\Features\FeaturesController;
use Automattic\WooCommerce\Internal\VariationGallery\Package as VariationGalleryPackage;
@@ -468,4 +470,30 @@ class WC_Update_Functions_Test extends \WC_Unit_Test_Case {
$this->assertArrayHasKey( 'customer_stock_notifications', $changes );
$this->assertTrue( $changes['customer_stock_notifications'] );
}
+
+ /**
+ * @testdox Migration registers and queues the rebuild of the tax lookup table.
+ */
+ public function test_wc_update_11201_migrate_tax_lookup_order_items(): void {
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $db_updates = WC_Install::get_db_update_callbacks();
+
+ // Under its own key, so that a store already on 11.2.0 from the batch that shipped beside
+ // it still runs the rebuild.
+ $this->assertArrayHasKey( '11.2.0-1', $db_updates );
+ $this->assertContains( 'wc_update_11201_migrate_tax_lookup_order_items', $db_updates['11.2.0-1'] );
+
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+ $batch_processor->remove_processor( OrderTaxLookupMigrator::class );
+
+ wc_update_11201_migrate_tax_lookup_order_items();
+
+ $this->assertTrue(
+ $batch_processor->is_enqueued( OrderTaxLookupMigrator::class ),
+ 'The migration should hand the rebuild to the batch processing controller.'
+ );
+
+ $batch_processor->remove_processor( OrderTaxLookupMigrator::class );
+ }
}
diff --git a/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/DataStoreTest.php b/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/DataStoreTest.php
index 22f2a6cea9f..a0afcce696f 100644
--- a/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/DataStoreTest.php
+++ b/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/DataStoreTest.php
@@ -8,10 +8,14 @@ use Automattic\WooCommerce\Admin\API\Reports\Orders\DataStore as OrdersDataStore
use Automattic\WooCommerce\Admin\ReportsSync;
use Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore;
use Automattic\WooCommerce\Admin\API\Reports\Taxes\Stats\DataStore as StatsDataStore;
+use Automattic\WooCommerce\Enums\OrderItemType;
use Automattic\WooCommerce\Enums\OrderStatus;
+use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore;
+use Automattic\WooCommerce\Utilities\OrderUtil;
use WC_Helper_Order;
use WC_Helper_Queue;
use WC_Helper_Reports;
+use WC_Order;
use WC_Order_Item_Tax;
use WC_Product_Simple;
use WC_Unit_Test_Case;
@@ -116,17 +120,11 @@ class DataStoreTest extends WC_Unit_Test_Case {
$order_id = $order->get_id();
- // Ensure a tax lookup row exists for the rate, dated by order creation.
- $wpdb->replace(
+ // The sync writes the lookup rows; only the date needs forcing.
+ $wpdb->update(
$wpdb->prefix . 'wc_order_tax_lookup',
- array(
- 'order_id' => $order_id,
- 'tax_rate_id' => $rate_id,
- 'date_created' => $created_gmt,
- 'shipping_tax' => 0,
- 'order_tax' => 19,
- 'total_tax' => 19,
- )
+ array( 'date_created' => $created_gmt ),
+ array( 'order_id' => $order_id )
);
// Force the created/paid/completed dates on the stats row.
@@ -328,4 +326,720 @@ class DataStoreTest extends WC_Unit_Test_Case {
$this->assertSame( 2, $taxes_count, 'Only the two paid orders should be counted in the Taxes report.' );
$this->assertSame( $taxes_count, (int) $orders_data->total, 'Taxes and Orders reports should agree on the order count for the same tax rate and period.' );
}
+
+ /**
+ * Create a completed, paid order whose tax lines carry an arbitrary `rate_id`, including one
+ * that has no `woocommerce_tax_rates` row and one shared by several lines.
+ *
+ * WC_Order_Item_Tax::set_rate() cannot be used for that shape because it reads the rate row.
+ *
+ * @param array $lines Tax lines. Each is `code`, `label`, `rate_id`, `tax_total`, and an optional `rate_percent`.
+ * @param string $created_gmt Order creation datetime (GMT).
+ * @param string $paid_gmt Order payment datetime (GMT).
+ * @return WC_Order
+ */
+ private function seed_order_with_tax_lines( array $lines, string $created_gmt, string $paid_gmt ): WC_Order {
+ global $wpdb;
+
+ $product = new WC_Product_Simple();
+ $product->set_name( 'Repro Product' );
+ $product->set_regular_price( '100' );
+ $product->save();
+
+ $order = WC_Helper_Order::create_order( 1, $product );
+
+ foreach ( $lines as $line ) {
+ $tax_item = new WC_Order_Item_Tax();
+ $tax_item->set_name( $line['code'] );
+ $tax_item->set_label( $line['label'] );
+ $tax_item->set_rate_id( $line['rate_id'] );
+ $tax_item->set_tax_total( $line['tax_total'] );
+ $tax_item->set_shipping_tax_total( 0 );
+
+ if ( isset( $line['rate_percent'] ) ) {
+ $tax_item->set_rate_percent( $line['rate_percent'] );
+ }
+
+ $order->add_item( $tax_item );
+ }
+
+ $order->set_status( OrderStatus::COMPLETED );
+ $order->save();
+
+ // A rate id of 0 matches the default, so the data store never writes the meta.
+ $rate_ids_by_code = wp_list_pluck( $lines, 'rate_id', 'code' );
+ foreach ( $order->get_items( OrderItemType::TAX ) as $item_id => $tax_item ) {
+ wc_update_order_item_meta( $item_id, 'rate_id', $rate_ids_by_code[ $tax_item->get_name() ] );
+ }
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ $order_id = $order->get_id();
+
+ $wpdb->update(
+ $wpdb->prefix . 'wc_order_tax_lookup',
+ array( 'date_created' => $created_gmt ),
+ array( 'order_id' => $order_id )
+ );
+
+ $wpdb->update(
+ $wpdb->prefix . 'wc_order_stats',
+ array(
+ 'date_created' => $created_gmt,
+ 'date_created_gmt' => $created_gmt,
+ 'date_paid' => $paid_gmt,
+ 'date_completed' => $paid_gmt,
+ ),
+ array( 'order_id' => $order_id )
+ );
+
+ ReportsCache::invalidate();
+
+ return $order;
+ }
+
+ /**
+ * Four jurisdiction tax lines that all carry `rate_id = 0`, the shape produced by an
+ * integration that calculates tax without registering its rates with WooCommerce.
+ *
+ * @return array
+ */
+ private function tax_lines_sharing_a_rate_id(): array {
+ return array(
+ array(
+ 'code' => 'US-CA-STATE-TAX',
+ 'label' => 'State Tax',
+ 'rate_id' => 0,
+ 'rate_percent' => 6.0,
+ 'tax_total' => 6.0,
+ ),
+ array(
+ 'code' => 'US-CA-COUNTY-TAX',
+ 'label' => 'County Tax',
+ 'rate_id' => 0,
+ 'rate_percent' => 0.25,
+ 'tax_total' => 0.25,
+ ),
+ array(
+ 'code' => 'US-CA-CITY-TAX',
+ 'label' => 'City Tax',
+ 'rate_id' => 0,
+ 'rate_percent' => 1.25,
+ 'tax_total' => 1.25,
+ ),
+ array(
+ 'code' => 'US-CA-DISTRICT-TAX',
+ 'label' => 'District Tax',
+ 'rate_id' => 0,
+ 'rate_percent' => 2.25,
+ 'tax_total' => 2.25,
+ ),
+ );
+ }
+
+ /**
+ * Read the lookup rows for an order.
+ *
+ * @param int $order_id Order id.
+ * @return array
+ */
+ private function lookup_rows( int $order_id ): array {
+ global $wpdb;
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT tax_rate_id, order_item_id, total_tax FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id = %d ORDER BY order_item_id ASC",
+ $order_id
+ ),
+ ARRAY_A
+ );
+ }
+
+ /**
+ * Build query args for a whole-month report request covering every tax rate.
+ *
+ * @param string $after Period start (GMT).
+ * @param string $before Period end (GMT).
+ * @return array
+ */
+ private function all_taxes_query( string $after, string $before ): array {
+ return array(
+ 'after' => $after,
+ 'before' => $before,
+ 'per_page' => 100,
+ 'page' => 1,
+ );
+ }
+
+ /**
+ * @testdox Sync writes one lookup row per tax line, so lines sharing a rate id no longer overwrite each other.
+ */
+ public function test_sync_writes_one_lookup_row_per_tax_line(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id(), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $rows = $this->lookup_rows( $order->get_id() );
+
+ $this->assertCount( 4, $rows, 'Each tax line should get its own lookup row even though they share a rate id.' );
+ $this->assertSame( 9.75, array_sum( array_column( $rows, 'total_tax' ) ), 'The lookup rows should add up to the tax the order actually carries.' );
+ $this->assertCount( 4, array_unique( array_column( $rows, 'order_item_id' ) ), 'Every lookup row should point at a distinct tax order item.' );
+ }
+
+ /**
+ * @testdox Taxes stats totals count every tax line of an order whose lines share a rate id.
+ */
+ public function test_taxes_stats_totals_include_every_tax_line(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id(), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $sut = new StatsDataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) + array( 'interval' => 'day' ) );
+
+ $this->assertSame( 9.75, $data->totals->total_tax, 'The Taxes stats total should match the tax the order carries.' );
+ $this->assertSame( 1, $data->totals->orders_count, 'The order should be counted once however many tax lines it carries.' );
+ }
+
+ /**
+ * @testdox Taxes table report gives each tax line its own row and amount instead of repeating one collapsed amount.
+ */
+ public function test_taxes_report_rows_do_not_fan_out_across_lines_sharing_a_rate_id(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id(), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertCount( 4, $data->data, 'Each tax line should render as its own report row.' );
+
+ $amounts = array_column( $data->data, 'total_tax' );
+ sort( $amounts );
+ $this->assertSame( array( 0.25, 1.25, 2.25, 6.0 ), $amounts, 'Each row should carry its own amount, not the same collapsed amount repeated.' );
+ $this->assertSame( 9.75, array_sum( $amounts ), 'The report rows should add up to the tax the order carries.' );
+ }
+
+ /**
+ * @testdox Taxes stats segmented by tax rate id sum every tax line sharing that rate id.
+ */
+ public function test_taxes_stats_segments_by_tax_rate_id_sum_every_line(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $lines = array(
+ array(
+ 'code' => 'DE-VAT-1',
+ 'label' => 'VAT',
+ 'rate_id' => $rate_id,
+ 'rate_percent' => 19.0,
+ 'tax_total' => 19.0,
+ ),
+ array(
+ 'code' => 'DE-VAT-REDUCED-1',
+ 'label' => 'VAT reduced',
+ 'rate_id' => $rate_id,
+ 'rate_percent' => 7.0,
+ 'tax_total' => 7.0,
+ ),
+ );
+
+ $this->seed_order_with_tax_lines( $lines, '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $sut = new StatsDataStore();
+ $data = $sut->get_data(
+ $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) + array(
+ 'interval' => 'day',
+ 'segmentby' => 'tax_rate_id',
+ )
+ );
+
+ $segments = wp_list_pluck( $data->totals->segments, 'subtotals', 'segment_id' );
+
+ $this->assertArrayHasKey( $rate_id, $segments, 'The rate should appear as a segment of the Taxes stats totals.' );
+
+ $subtotals = (array) $segments[ $rate_id ];
+ $this->assertSame( 26.0, (float) $subtotals['total_tax'], 'The segment should sum both tax lines carrying that rate id.' );
+ }
+
+ /**
+ * @testdox Sync writes a lookup row for a tax line whose rate id has no woocommerce_tax_rates row.
+ */
+ public function test_sync_handles_a_rate_id_with_no_tax_rates_row(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $lines = array(
+ array(
+ 'code' => 'US-CA-STATE-TAX',
+ 'label' => 'State Tax',
+ 'rate_id' => 4242,
+ 'rate_percent' => 6.0,
+ 'tax_total' => 6.0,
+ ),
+ );
+
+ $order = $this->seed_order_with_tax_lines( $lines, '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $rows = $this->lookup_rows( $order->get_id() );
+
+ $this->assertCount( 1, $rows, 'A rate id with no tax rate row should still be synced.' );
+ $this->assertSame( 4242, (int) $rows[0]['tax_rate_id'] );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertCount( 1, $data->data, 'The line should still be reported even though its rate is not registered with WooCommerce.' );
+ $this->assertSame( 6.0, $data->data[0]['total_tax'] );
+ }
+
+ /**
+ * @testdox Sync writes a lookup row for a tax line that carries no rate_percent meta.
+ */
+ public function test_sync_handles_a_tax_line_with_no_rate_percent_meta(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $lines = array(
+ array(
+ 'code' => 'US-CA-STATE-TAX',
+ 'label' => 'State Tax',
+ 'rate_id' => 4242,
+ 'tax_total' => 6.0,
+ ),
+ );
+
+ $order = $this->seed_order_with_tax_lines( $lines, '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $this->assertCount( 1, $this->lookup_rows( $order->get_id() ), 'A tax line with no rate_percent meta should still be synced.' );
+
+ $sut = new StatsDataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) + array( 'interval' => 'day' ) );
+
+ $this->assertSame( 6.0, $data->totals->total_tax, 'The Taxes stats total should include a line that carries no rate_percent meta.' );
+ }
+
+ /**
+ * @testdox Sync removes the lookup row of a tax line that has been removed from an order.
+ */
+ public function test_sync_removes_lookup_rows_for_removed_tax_lines(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id(), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $this->assertCount( 4, $this->lookup_rows( $order->get_id() ) );
+
+ $tax_items = $order->get_items( OrderItemType::TAX );
+ $removed = array_shift( $tax_items );
+ $order->remove_item( $removed->get_id() );
+ $order->save();
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $rows = $this->lookup_rows( $order->get_id() );
+
+ $this->assertCount( 3, $rows, 'Removing a tax line from an order should remove its lookup row.' );
+ $this->assertNotContains( (string) $removed->get_id(), array_column( $rows, 'order_item_id' ), 'The removed line should leave no lookup row behind.' );
+ }
+
+ /**
+ * @testdox Sync removes the lookup row a tax line left behind when its rate id changed.
+ */
+ public function test_sync_removes_the_lookup_row_of_a_tax_line_whose_rate_id_changed(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $this->assertCount( 2, $this->lookup_rows( $order->get_id() ) );
+
+ $tax_items = $order->get_items( OrderItemType::TAX );
+ $moved = array_shift( $tax_items );
+ $moved->set_rate_id( 999 );
+ $order->save();
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $rows = $this->lookup_rows( $order->get_id() );
+
+ $this->assertCount( 2, $rows, 'A tax line that changed rate id should hold one row, not one on each rate id.' );
+ $this->assertNotContains( '101', array_column( $rows, 'tax_rate_id' ), 'The row on the rate id the line carried before should be gone.' );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertSame( 6.25, array_sum( array_column( $data->data, 'total_tax' ) ), 'The order should not be counted twice because one of its lines changed rate id.' );
+ }
+
+ /**
+ * @testdox Sync passes over an order with no creation date instead of failing on it.
+ */
+ public function test_sync_passes_over_an_order_with_no_creation_date(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+ $rows_before = $this->lookup_rows( $order->get_id() );
+
+ $this->clear_order_date_created( $order->get_id() );
+
+ $this->assertSame( -1, DataStore::sync_order_taxes( $order->get_id() ), 'An order with no creation date has nothing to date its rows by, so it should be passed over.' );
+ $this->assertSame( $rows_before, $this->lookup_rows( $order->get_id() ), 'An order that was passed over should keep its rows.' );
+ }
+
+ /**
+ * Take an order's creation date away, the way a store holding a zero datetime has.
+ *
+ * `WC_Data::set_date_prop()` reads '0000-00-00 00:00:00' as no date at all.
+ *
+ * @param int $order_id Order id.
+ */
+ private function clear_order_date_created( int $order_id ): void {
+ global $wpdb;
+
+ if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
+ $wpdb->update(
+ OrdersTableDataStore::get_orders_table_name(),
+ array( 'date_created_gmt' => null ),
+ array( 'id' => $order_id )
+ );
+ } else {
+ $wpdb->update(
+ $wpdb->posts,
+ array(
+ 'post_date' => '0000-00-00 00:00:00',
+ 'post_date_gmt' => '0000-00-00 00:00:00',
+ ),
+ array( 'ID' => $order_id )
+ );
+ }
+
+ wp_cache_flush();
+ }
+
+ /**
+ * The Orders list joins the tax lookup for its rate filter, so one row per tax line is one
+ * joined row per tax line. What keeps the order out of the list twice is the DISTINCT the
+ * report selects with, and the count above the list being over distinct order ids.
+ *
+ * @testdox Orders report lists an order once when several of its tax lines share the filtered rate.
+ */
+ public function test_orders_report_lists_an_order_once_when_its_tax_lines_share_the_filtered_rate(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $lines = array_map(
+ function ( $line ) use ( $rate_id ) {
+ $line['rate_id'] = $rate_id;
+ return $line;
+ },
+ $this->tax_lines_sharing_a_rate_id()
+ );
+
+ $order = $this->seed_order_with_tax_lines( $lines, '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $this->assertCount( 4, $this->lookup_rows( $order->get_id() ), 'The order should hold a row per tax line to begin with.' );
+
+ $orders = new OrdersDataStore();
+ $included = $orders->get_data(
+ array(
+ 'after' => '2023-02-01 00:00:00',
+ 'before' => '2023-02-28 23:59:59',
+ 'tax_rate_includes' => array( $rate_id ),
+ 'per_page' => 100,
+ 'page' => 1,
+ )
+ );
+
+ $this->assertCount( 1, $included->data, 'An order carrying several tax lines on one rate should be listed once, not once per line.' );
+ $this->assertSame( 1, (int) $included->total, 'The list and the count above it should agree.' );
+
+ $excluded = $orders->get_data(
+ array(
+ 'after' => '2023-02-01 00:00:00',
+ 'before' => '2023-02-28 23:59:59',
+ 'tax_rate_excludes' => array( $rate_id ),
+ 'per_page' => 100,
+ 'page' => 1,
+ )
+ );
+
+ $this->assertCount( 0, $excluded->data, 'Excluding the rate the order carries should leave it out of the list.' );
+ }
+
+ /**
+ * @testdox Sync leaves the rows an order already had alone when one of its writes fails.
+ */
+ public function test_sync_keeps_existing_rows_when_a_write_fails(): void {
+ global $wpdb;
+
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ // The shape the rebuild finds an order in: every row on the column default.
+ $this->unmigrate_lookup_rows( $order->get_id() );
+ $rows_before = $this->lookup_rows( $order->get_id() );
+
+ $suppress = $wpdb->suppress_errors( true );
+ $restore = $this->break_next_lookup_write();
+ $result = DataStore::sync_order_taxes( $order->get_id() );
+ $restore();
+ $wpdb->suppress_errors( $suppress );
+
+ $this->assertFalse( $result, 'A write that did not land should be reported as a failed sync.' );
+ $this->assertSame( $rows_before, $this->lookup_rows( $order->get_id() ), 'The rows the order came in with should survive a failed sync.' );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertCount( 2, $data->data, 'A failed sync should leave both tax lines reportable.' );
+
+ $amounts = array_column( $data->data, 'total_tax' );
+ sort( $amounts );
+ $this->assertSame( array( 0.25, 6.0 ), $amounts, 'No line should be lost or counted twice because a sync failed.' );
+ }
+
+ /**
+ * @testdox Sync writes every tax line of an order in one statement.
+ */
+ public function test_sync_writes_every_tax_line_of_an_order_in_one_statement(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id(), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $writes = 0;
+ $counter = $this->count_lookup_writes( $writes );
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $counter();
+
+ $this->assertCount( 4, $this->lookup_rows( $order->get_id() ), 'Every tax line of the order should hold a row.' );
+ $this->assertSame( 1, $writes, 'An order that is rebuilt line by line can be left with only some of its lines rebuilt, so the whole order should go in one write.' );
+ }
+
+ /**
+ * @testdox Sync leaves alone a row a sync running beside it wrote.
+ */
+ public function test_sync_keeps_the_rows_a_sync_running_beside_it_wrote(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ $restore = $this->write_a_row_beside_the_next_sync( $order->get_id(), 777, 888 );
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $restore();
+
+ $rows = $this->lookup_rows( $order->get_id() );
+
+ $this->assertContains( '888', array_column( $rows, 'order_item_id' ), 'A sync should prune the rows it read and no others, so a row written beside it survives.' );
+ $this->assertCount( 3, $rows, 'The order should hold its own two rows and the one written beside them.' );
+ }
+
+ /**
+ * Write a row for the order while the next sync is between reading the order's rows and
+ * pruning them, the way a sync of the same order running beside it does.
+ *
+ * @param int $order_id Order id.
+ * @param int $tax_rate_id Tax rate id to write the row on.
+ * @param int $order_item_id Tax order item id to write the row on.
+ * @return callable Removes the filter again.
+ */
+ private function write_a_row_beside_the_next_sync( int $order_id, int $tax_rate_id, int $order_item_id ): callable {
+ global $wpdb;
+
+ $table_name = $wpdb->prefix . 'wc_order_tax_lookup';
+ $written = false;
+
+ $filter = function ( $query ) use ( &$written, $table_name, $order_id, $tax_rate_id, $order_item_id ) {
+ global $wpdb;
+
+ if ( $written || 0 !== strpos( $query, "REPLACE INTO {$table_name}" ) ) {
+ return $query;
+ }
+
+ $written = true;
+
+ $wpdb->insert(
+ $table_name,
+ array(
+ 'order_id' => $order_id,
+ 'date_created' => '2023-02-10 10:00:00',
+ 'tax_rate_id' => $tax_rate_id,
+ 'order_item_id' => $order_item_id,
+ 'shipping_tax' => 0,
+ 'order_tax' => 1.0,
+ 'total_tax' => 1.0,
+ )
+ );
+
+ return $query;
+ };
+
+ add_filter( 'query', $filter );
+
+ return function () use ( $filter ) {
+ remove_filter( 'query', $filter );
+ };
+ }
+
+ /**
+ * Make the next write to the lookup table fail, the way a database error mid-sync would.
+ *
+ * @return callable Removes the filter again.
+ */
+ private function break_next_lookup_write(): callable {
+ global $wpdb;
+
+ $table_name = $wpdb->prefix . 'wc_order_tax_lookup';
+ $broken = false;
+
+ $filter = function ( $query ) use ( &$broken, $table_name ) {
+ if ( $broken || 0 !== strpos( $query, "REPLACE INTO {$table_name}" ) ) {
+ return $query;
+ }
+
+ $broken = true;
+
+ // A table that does not exist, so the write fails the way a database error would.
+ return "REPLACE INTO `{$table_name}_missing` (order_id) VALUES (1)";
+ };
+
+ add_filter( 'query', $filter );
+
+ return function () use ( $filter ) {
+ remove_filter( 'query', $filter );
+ };
+ }
+
+ /**
+ * Count the writes a sync makes to the lookup table.
+ *
+ * @param int $writes Counter to increment, by reference.
+ * @return callable Removes the filter again.
+ */
+ private function count_lookup_writes( int &$writes ): callable {
+ global $wpdb;
+
+ $table_name = $wpdb->prefix . 'wc_order_tax_lookup';
+
+ $filter = function ( $query ) use ( &$writes, $table_name ) {
+ if ( 0 === strpos( $query, "REPLACE INTO {$table_name}" ) ) {
+ ++$writes;
+ }
+
+ return $query;
+ };
+
+ add_filter( 'query', $filter );
+
+ return function () use ( $filter ) {
+ remove_filter( 'query', $filter );
+ };
+ }
+
+ /**
+ * Put an order's lookup rows back into the shape the table held before it held one row per tax
+ * order item.
+ *
+ * @param int $order_id Order id.
+ */
+ private function unmigrate_lookup_rows( int $order_id ): void {
+ global $wpdb;
+
+ $wpdb->update(
+ $wpdb->prefix . 'wc_order_tax_lookup',
+ array( 'order_item_id' => 0 ),
+ array( 'order_id' => $order_id )
+ );
+
+ ReportsCache::invalidate();
+ }
+
+ /**
+ * Two tax lines on distinct rate ids, the shape almost every store's history is in.
+ *
+ * @param string $prefix Tax code prefix, so lines from different orders group separately.
+ * @param int $rate_id_base First rate id; the second line takes the next one.
+ * @return array
+ */
+ private function tax_lines_on_distinct_rate_ids( string $prefix, int $rate_id_base ): array {
+ return array(
+ array(
+ 'code' => "{$prefix}-STATE-TAX",
+ 'label' => 'State Tax',
+ 'rate_id' => $rate_id_base,
+ 'rate_percent' => 6.0,
+ 'tax_total' => 6.0,
+ ),
+ array(
+ 'code' => "{$prefix}-COUNTY-TAX",
+ 'label' => 'County Tax',
+ 'rate_id' => $rate_id_base + 1,
+ 'rate_percent' => 0.25,
+ 'tax_total' => 0.25,
+ ),
+ );
+ }
+
+ /**
+ * @testdox Taxes report still reports rows written before the lookup was keyed by tax order item.
+ */
+ public function test_taxes_report_reads_rows_written_before_the_grain_change(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+
+ // The schema change lands with the plugin files; the rebuild that fills the new column runs
+ // later off a queue. Between the two, every existing row sits at the column default.
+ $this->unmigrate_lookup_rows( $order->get_id() );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertCount( 2, $data->data, 'Rows waiting on the migration should still be reported, not dropped.' );
+
+ $amounts = array_column( $data->data, 'total_tax' );
+ sort( $amounts );
+ $this->assertSame( array( 0.25, 6.0 ), $amounts, 'Each line should still carry its own amount.' );
+ }
+
+ /**
+ * @testdox Taxes report counts each tax line once while the lookup table is half migrated.
+ */
+ public function test_taxes_report_counts_each_line_once_while_half_migrated(): void {
+ update_option( 'woocommerce_date_type', 'date_paid' );
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $old_order = $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-CA', 101 ), '2023-02-10 10:00:00', '2023-02-10 10:00:00' );
+ $this->seed_order_with_tax_lines( $this->tax_lines_on_distinct_rate_ids( 'US-NY', 201 ), '2023-02-11 10:00:00', '2023-02-11 10:00:00' );
+
+ // Only the first order is left in the old shape, which is what a store looks like part way
+ // through the rebuild. Matching on the rate id must not spill onto the rebuilt rows.
+ $this->unmigrate_lookup_rows( $old_order->get_id() );
+
+ $sut = new DataStore();
+ $data = $sut->get_data( $this->all_taxes_query( '2023-02-01 00:00:00', '2023-02-28 23:59:59' ) );
+
+ $this->assertCount( 4, $data->data, 'Both orders should report both of their tax lines.' );
+
+ $amounts = array_column( $data->data, 'total_tax' );
+ sort( $amounts );
+ $this->assertSame( array( 0.25, 0.25, 6.0, 6.0 ), $amounts, 'No line should be counted twice or lost.' );
+ $this->assertSame( 12.5, array_sum( $amounts ), 'The report should add up to the tax both orders carry.' );
+ }
}
diff --git a/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php b/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php
new file mode 100644
index 00000000000..be4390990e6
--- /dev/null
+++ b/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php
@@ -0,0 +1,479 @@
+<?php
+declare( strict_types = 1 );
+
+namespace Automattic\WooCommerce\Tests\Internal\Admin;
+
+use Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore as TaxesDataStore;
+use Automattic\WooCommerce\Enums\OrderItemType;
+use Automattic\WooCommerce\Enums\OrderStatus;
+use Automattic\WooCommerce\Internal\Admin\OrderTaxLookupMigrator;
+use Automattic\WooCommerce\Internal\Admin\Schedulers\OrdersScheduler;
+use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessingController;
+use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore;
+use Automattic\WooCommerce\Utilities\OrderUtil;
+use WC_Helper_Order;
+use WC_Helper_Queue;
+use WC_Helper_Reports;
+use WC_Order;
+use WC_Order_Item_Tax;
+use WC_Product_Simple;
+use WC_Unit_Test_Case;
+
+/**
+ * Tests for the OrderTaxLookupMigrator class.
+ */
+class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
+
+ /**
+ * The System Under Test.
+ *
+ * @var OrderTaxLookupMigrator
+ */
+ private $sut;
+
+ /**
+ * Original woocommerce_calc_taxes option value.
+ *
+ * @var string|false
+ */
+ private $original_calc_taxes;
+
+ /**
+ * Set up test fixtures.
+ */
+ public function setUp(): void {
+ parent::setUp();
+
+ $this->original_calc_taxes = get_option( 'woocommerce_calc_taxes' );
+ update_option( 'woocommerce_calc_taxes', 'yes' );
+
+ WC_Helper_Reports::reset_stats_dbs();
+ delete_option( OrderTaxLookupMigrator::CURSOR_OPTION );
+ delete_option( OrdersScheduler::FAILED_ORDER_IMPORTS_OPTION );
+
+ $this->sut = wc_get_container()->get( OrderTaxLookupMigrator::class );
+ }
+
+ /**
+ * Tear down test fixtures.
+ */
+ public function tearDown(): void {
+ update_option( 'woocommerce_calc_taxes', $this->original_calc_taxes );
+ delete_option( OrderTaxLookupMigrator::CURSOR_OPTION );
+ delete_option( OrdersScheduler::FAILED_ORDER_IMPORTS_OPTION );
+ wc_get_container()->get( BatchProcessingController::class )->remove_processor( OrderTaxLookupMigrator::class );
+
+ parent::tearDown();
+ }
+
+ /**
+ * Create an order carrying the given tax lines and let the analytics sync record it.
+ *
+ * @param array $lines Tax lines, each with a `code`, a `rate_id` and a `tax_total`.
+ * @return WC_Order
+ */
+ private function seed_order_with_tax_lines( array $lines ): WC_Order {
+ $product = new WC_Product_Simple();
+ $product->set_name( 'Repro Product' );
+ $product->set_regular_price( '100' );
+ $product->save();
+
+ $order = WC_Helper_Order::create_order( 1, $product );
+
+ foreach ( $lines as $line ) {
+ $tax_item = new WC_Order_Item_Tax();
+ $tax_item->set_name( $line['code'] );
+ $tax_item->set_label( $line['code'] );
+ $tax_item->set_rate_id( $line['rate_id'] );
+ $tax_item->set_rate_percent( 0 );
+ $tax_item->set_tax_total( $line['tax_total'] );
+ $tax_item->set_shipping_tax_total( 0 );
+
+ $order->add_item( $tax_item );
+ }
+
+ $order->set_status( OrderStatus::COMPLETED );
+ $order->save();
+
+ // A rate id of 0 matches the property default, so the data store never writes the meta.
+ $rate_ids_by_code = wp_list_pluck( $lines, 'rate_id', 'code' );
+ foreach ( $order->get_items( OrderItemType::TAX ) as $item_id => $tax_item ) {
+ wc_update_order_item_meta( $item_id, 'rate_id', $rate_ids_by_code[ $tax_item->get_name() ] );
+ }
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ return $order;
+ }
+
+ /**
+ * Two tax lines that share a rate id, the shape an automated tax plugin produces.
+ *
+ * @return array
+ */
+ private function tax_lines_sharing_a_rate_id(): array {
+ return array(
+ array(
+ 'code' => 'US-CA-STATE-TAX-1',
+ 'rate_id' => 0,
+ 'tax_total' => 6.0,
+ ),
+ array(
+ 'code' => 'US-CA-COUNTY-TAX-1',
+ 'rate_id' => 0,
+ 'tax_total' => 0.25,
+ ),
+ );
+ }
+
+ /**
+ * Replace an order's lookup rows with the single row the table held before it kept one row per
+ * tax order item.
+ *
+ * @param int $order_id Order id.
+ * @param int $tax_rate_id Tax rate id the row carries.
+ * @param float $total_tax Tax amount the row carries.
+ */
+ private function unmigrate_lookup_rows( int $order_id, int $tax_rate_id, float $total_tax ): void {
+ global $wpdb;
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ $wpdb->delete( $table_name, array( 'order_id' => $order_id ) );
+ $wpdb->insert(
+ $table_name,
+ array(
+ 'order_id' => $order_id,
+ 'tax_rate_id' => $tax_rate_id,
+ 'order_item_id' => 0,
+ 'date_created' => '2023-02-10 10:00:00',
+ 'shipping_tax' => 0,
+ 'order_tax' => $total_tax,
+ 'total_tax' => $total_tax,
+ )
+ );
+ }
+
+ /**
+ * Read an order's lookup rows.
+ *
+ * @param int $order_id Order id.
+ * @return array
+ */
+ private function lookup_rows( int $order_id ): array {
+ global $wpdb;
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT order_item_id, total_tax FROM {$table_name} WHERE order_id = %d ORDER BY order_item_id ASC",
+ $order_id
+ ),
+ ARRAY_A
+ );
+ }
+
+ /**
+ * Clear the data store's per-request cache of the lookup table's key shape, so a key change
+ * made by this test is seen.
+ */
+ private function reset_lookup_key_cache(): void {
+ $property = new \ReflectionProperty( TaxesDataStore::class, 'lookup_keyed_by_order_item' );
+ $property->setAccessible( true );
+ $property->setValue( null, null );
+ }
+
+ /**
+ * @testdox Only orders still holding rows without a tax order item are pending.
+ */
+ public function test_only_orders_in_the_old_shape_are_pending(): void {
+ $migrated = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $old = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'A store on the current shape has nothing to rebuild.' );
+
+ $this->unmigrate_lookup_rows( $old->get_id(), 0, 6.25 );
+
+ $this->assertSame( 1, $this->sut->get_total_pending_count(), 'Only the order left in the old shape should be pending.' );
+ $this->assertSame( array( $old->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'The batch should hold only that order.' );
+ $this->assertNotEmpty( $this->lookup_rows( $migrated->get_id() ), 'The rebuilt order should be left alone.' );
+ }
+
+ /**
+ * @testdox Processing a batch gives every tax line of the order its own lookup row.
+ */
+ public function test_process_batch_rebuilds_one_row_per_tax_line(): void {
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+
+ // The single row a store carried out of the old schema: both lines share a rate id, so
+ // only the last one written survived.
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 0.25 );
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $rows = $this->lookup_rows( $order->get_id() );
+ $this->assertCount( 2, $rows, 'Each tax line of the order should end up with its own row.' );
+
+ $item_ids = array_map( 'absint', array_column( $rows, 'order_item_id' ) );
+ $this->assertSame( array_keys( $order->get_items( OrderItemType::TAX ) ), $item_ids, 'Every row should point at its tax order item.' );
+ $this->assertEqualsWithDelta( 6.25, array_sum( array_map( 'floatval', array_column( $rows, 'total_tax' ) ) ), 0.001, 'The rows should add up to the tax the order carries.' );
+
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'A rebuilt order should stop being pending.' );
+ }
+
+ /**
+ * @testdox Processing a batch drops the rows of an order no report can read.
+ */
+ public function test_process_batch_drops_the_rows_of_an_order_no_report_can_read(): void {
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 0.25 );
+
+ $missing_order_id = $order->get_id() + 1000;
+ $this->unmigrate_lookup_rows( $missing_order_id, 0, 3.0 );
+
+ $this->sut->process_batch( $this->sut->get_next_batch_to_process( 10 ) );
+
+ $this->assertSame( $missing_order_id, (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The cursor should step past the whole batch.' );
+ $this->assertSame( array(), $this->lookup_rows( $missing_order_id ), 'No report can read the rows of an order that is gone, so they should be dropped.' );
+ $this->assertCount( 2, $this->lookup_rows( $order->get_id() ), 'The rest of the batch should still be rebuilt.' );
+ $this->assertSame( array(), $this->sut->get_next_batch_to_process( 10 ), 'An order that cannot be loaded should not hold the pass up.' );
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'Nothing should be left pending once the pass is through.' );
+
+ $tools = $this->sut->handle_woocommerce_debug_tools( array() );
+ $this->assertTrue( $tools['rebuild_analytics_tax_data']['disabled'], 'The tool should not go on offering a run that cannot change anything.' );
+ }
+
+ /**
+ * @testdox Processing a batch keeps the rows of an order the reports still read but `wc_get_order()` cannot load.
+ */
+ public function test_process_batch_keeps_the_rows_of_an_order_whose_type_is_not_registered(): void {
+ global $wpdb;
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 6.25 );
+
+ // A deactivated plugin leaves its order type unregistered, so `wc_get_order()` fails while
+ // the order's stats row stays behind and the reports go on reading its lookup rows.
+ if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
+ $wpdb->update( OrdersTableDataStore::get_orders_table_name(), array( 'type' => 'shop_unknown' ), array( 'id' => $order->get_id() ) );
+ } else {
+ $wpdb->update( $wpdb->posts, array( 'post_type' => 'shop_unknown' ), array( 'ID' => $order->get_id() ) );
+ }
+ wp_cache_flush();
+
+ $this->assertFalse( wc_get_order( $order->get_id() ), 'The order should not be loadable once its type is not registered.' );
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $this->assertCount( 1, $this->lookup_rows( $order->get_id() ), 'An order the reports still read should keep its rows.' );
+ $this->assertSame( $order->get_id(), (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The cursor should step past the order.' );
+ }
+
+ /**
+ * @testdox Processing a batch carries on past an order whose rebuild fails, and leaves its rows alone.
+ */
+ public function test_process_batch_carries_on_past_an_order_it_could_not_rebuild(): void {
+ global $wpdb;
+
+ $failing = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $failing->get_id(), 0, 6.25 );
+
+ $rebuilt = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $rebuilt->get_id(), 0, 6.25 );
+
+ $rows_before = $this->lookup_rows( $failing->get_id() );
+
+ $suppress = $wpdb->suppress_errors( true );
+ $restore = $this->break_next_lookup_write();
+ $this->sut->process_batch( $this->sut->get_next_batch_to_process( 10 ) );
+ $restore();
+ $wpdb->suppress_errors( $suppress );
+
+ $this->assertSame( $rows_before, $this->lookup_rows( $failing->get_id() ), 'An order whose rebuild failed should keep the rows it had.' );
+ $this->assertCount( 2, $this->lookup_rows( $rebuilt->get_id() ), 'The rest of the batch should still be rebuilt.' );
+ $this->assertSame( $rebuilt->get_id(), (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The cursor should step past the whole batch.' );
+ $this->assertSame( array(), $this->sut->get_next_batch_to_process( 10 ), 'An order that could not be rebuilt should not hold the pass up.' );
+
+ $failed = OrdersScheduler::get_failed_order_imports();
+
+ $this->assertSame( array( $failing->get_id() ), $failed['ids'], 'The cursor steps past an order the rebuild could not finish, so it should be left where Analytics settings offers a retry over it.' );
+ $this->assertNotContains( $rebuilt->get_id(), $failed['ids'], 'An order that was rebuilt should not be recorded as a failed import.' );
+ }
+
+ /**
+ * @testdox The rebuild waits for the re-key instead of parking the cursor at the end of the table.
+ */
+ public function test_rebuild_waits_until_the_lookup_is_keyed_by_order_item(): void {
+ global $wpdb;
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 6.25 );
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ // The shape a failed re-key leaves behind: dbDelta added the column, the key change never
+ // landed. Rebuilding here would write every row back at zero and step past it for good.
+ $wpdb->query( "ALTER TABLE `{$table_name}` DROP PRIMARY KEY, ADD PRIMARY KEY (order_id, tax_rate_id)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ $this->reset_lookup_key_cache();
+
+ try {
+ $this->assertSame( array(), $this->sut->get_next_batch_to_process( 10 ), 'No orders should be handed out while the re-key has not landed.' );
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'No order should count as pending while the rebuild cannot change it.' );
+ $this->assertFalse( get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The cursor should stay where it is.' );
+
+ $tools = $this->sut->handle_woocommerce_debug_tools( array() );
+ $this->assertTrue( $tools['rebuild_analytics_tax_data']['disabled'], 'The tool should not offer a run that cannot change anything.' );
+ $this->assertStringContainsString( 'Verify base database tables', $tools['rebuild_analytics_tax_data']['desc'], 'The tool should say how to put the re-key right.' );
+ } finally {
+ $wpdb->query( "ALTER TABLE `{$table_name}` DROP PRIMARY KEY, ADD PRIMARY KEY (order_id, tax_rate_id, order_item_id)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ $this->reset_lookup_key_cache();
+ }
+
+ $this->assertSame( array( $order->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'The rebuild should pick the order up once the re-key has landed.' );
+ $this->assertSame( 1, $this->sut->get_total_pending_count(), 'The order should be back to pending once the re-key has landed.' );
+ }
+
+ /**
+ * @testdox The pending count stops at its limit rather than counting the whole table.
+ */
+ public function test_pending_count_stops_at_its_limit(): void {
+ global $wpdb;
+
+ $table_name = TaxesDataStore::get_db_table_name();
+ $rows = array();
+
+ for ( $order_id = 1; $order_id <= OrderTaxLookupMigrator::PENDING_COUNT_LIMIT + 10; $order_id++ ) {
+ $rows[] = "({$order_id}, 0, 0, '2023-02-10 10:00:00', 0, 1, 1)";
+ }
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name is not user input, and the values are order ids counted out above.
+ $wpdb->query( "INSERT INTO {$table_name} (order_id, tax_rate_id, order_item_id, date_created, shipping_tax, order_tax, total_tax) VALUES " . implode( ', ', $rows ) );
+
+ $this->assertSame(
+ OrderTaxLookupMigrator::PENDING_COUNT_LIMIT,
+ $this->sut->get_total_pending_count(),
+ 'Nothing indexes the column the count reads, so it should stop counting once it has enough to report.'
+ );
+ }
+
+ /**
+ * @testdox Processing a batch passes over an order with no creation date.
+ */
+ public function test_process_batch_passes_over_an_order_with_no_creation_date(): void {
+ global $wpdb;
+
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 0.25 );
+
+ // '0000-00-00 00:00:00' reads as no date at all, which is what `WC_Data::set_date_prop()`
+ // makes of it.
+ if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
+ $wpdb->update(
+ OrdersTableDataStore::get_orders_table_name(),
+ array( 'date_created_gmt' => null ),
+ array( 'id' => $order->get_id() )
+ );
+ } else {
+ $wpdb->update(
+ $wpdb->posts,
+ array(
+ 'post_date' => '0000-00-00 00:00:00',
+ 'post_date_gmt' => '0000-00-00 00:00:00',
+ ),
+ array( 'ID' => $order->get_id() )
+ );
+ }
+ wp_cache_flush();
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $this->assertSame(
+ $order->get_id(),
+ (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ),
+ 'An order with no creation date should be stepped past. A batch that raises never reaches the cursor write, and is handed out again forever.'
+ );
+ $this->assertNotEmpty( $this->lookup_rows( $order->get_id() ), 'An order that was stepped past should keep its rows.' );
+ }
+
+ /**
+ * Make the next write to the lookup table fail, the way a database error mid-rebuild would.
+ *
+ * @return callable Removes the filter again.
+ */
+ private function break_next_lookup_write(): callable {
+ $table_name = TaxesDataStore::get_db_table_name();
+ $broken = false;
+
+ $filter = function ( $query ) use ( &$broken, $table_name ) {
+ if ( $broken || 0 !== strpos( $query, "REPLACE INTO {$table_name}" ) ) {
+ return $query;
+ }
+
+ $broken = true;
+
+ // A table that does not exist, so the write fails the way a database error would.
+ return "REPLACE INTO `{$table_name}_missing` (order_id) VALUES (1)";
+ };
+
+ add_filter( 'query', $filter );
+
+ return function () use ( $filter ) {
+ remove_filter( 'query', $filter );
+ };
+ }
+
+ /**
+ * @testdox The tool reports and rebuilds what is left of the pass, not what an earlier one went through.
+ */
+ public function test_tool_resumes_from_where_the_last_pass_stopped(): void {
+ $stepped_past = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $stepped_past->get_id(), 0, 0.25 );
+
+ $left = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $left->get_id(), 0, 0.25 );
+
+ update_option( OrderTaxLookupMigrator::CURSOR_OPTION, $stepped_past->get_id() );
+
+ $this->assertSame( 1, $this->sut->get_total_pending_count(), 'The count should hold what is left of the pass.' );
+
+ $this->sut->enqueue();
+
+ $this->assertSame( array( $left->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'The rebuild should pick up where it stopped.' );
+ $this->assertSame( $stepped_past->get_id(), (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'Starting the tool should not rewind the pass.' );
+ }
+
+ /**
+ * @testdox The tool on the Status page starts and stops the rebuild.
+ */
+ public function test_tool_starts_and_stops_the_rebuild(): void {
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $order->get_id(), 0, 0.25 );
+
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+
+ $tools = $this->sut->handle_woocommerce_debug_tools( array() );
+ $this->assertArrayHasKey( 'rebuild_analytics_tax_data', $tools, 'A store with orders to rebuild should be offered the tool.' );
+
+ $this->sut->enqueue();
+ $this->assertTrue( $batch_processor->is_enqueued( OrderTaxLookupMigrator::class ), 'The tool should hand the rebuild to the batch processing controller.' );
+
+ $tools = $this->sut->handle_woocommerce_debug_tools( array() );
+ $this->assertArrayHasKey( 'stop_rebuild_analytics_tax_data', $tools, 'A rebuild in progress should be offered a stop button.' );
+
+ $this->sut->dequeue();
+ $this->assertFalse( $batch_processor->is_enqueued( OrderTaxLookupMigrator::class ), 'The tool should be able to stop the rebuild.' );
+ }
+
+ /**
+ * @testdox The tool is disabled on a store with nothing to rebuild.
+ */
+ public function test_tool_is_disabled_with_nothing_to_rebuild(): void {
+ $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+
+ $tools = $this->sut->handle_woocommerce_debug_tools( array() );
+
+ $this->assertTrue( $tools['rebuild_analytics_tax_data']['disabled'], 'A store on the current shape should not be offered a rebuild.' );
+ }
+}
diff --git a/plugins/woocommerce/tests/php/src/Internal/Admin/Schedulers/OrdersSchedulerTest.php b/plugins/woocommerce/tests/php/src/Internal/Admin/Schedulers/OrdersSchedulerTest.php
index 9974d22116d..3e6fb605888 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Admin/Schedulers/OrdersSchedulerTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Admin/Schedulers/OrdersSchedulerTest.php
@@ -5,6 +5,7 @@ namespace Automattic\WooCommerce\Tests\Internal\Admin\Schedulers;
use Automattic\WooCommerce\Internal\Admin\Schedulers\OrdersScheduler;
use Automattic\WooCommerce\Admin\API\Reports\Orders\Stats\DataStore as OrdersStatsDataStore;
+use Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore as TaxesDataStore;
use Automattic\WooCommerce\Utilities\OrderUtil;
use WC_Unit_Test_Case;
@@ -1180,6 +1181,63 @@ class OrdersSchedulerTest extends WC_Unit_Test_Case {
$this->assertNotContains( $order->get_id(), $failed['ids'] );
}
+ /**
+ * @testdox import keeps the failed record of an order whose tax lookup rows could not be written.
+ */
+ public function test_import_keeps_failed_order_when_the_tax_sync_does_not_land(): void {
+ global $wpdb;
+
+ $tax_item = new \WC_Order_Item_Tax();
+ $tax_item->set_name( 'US-CA-STATE-TAX-1' );
+ $tax_item->set_rate_id( 1 );
+ $tax_item->set_rate_percent( 0 );
+ $tax_item->set_tax_total( 6.0 );
+ $tax_item->set_shipping_tax_total( 0 );
+
+ $order = \WC_Helper_Order::create_order();
+ $order->add_item( $tax_item );
+ $order->set_status( 'completed' );
+ $order->save();
+
+ OrdersScheduler::record_failed_order_import( $order->get_id() );
+
+ $suppress = $wpdb->suppress_errors( true );
+ $restore = $this->break_next_lookup_write();
+ OrdersScheduler::import( $order->get_id() );
+ $restore();
+ $wpdb->suppress_errors( $suppress );
+
+ $failed = OrdersScheduler::get_failed_order_imports();
+ $this->assertContains( $order->get_id(), $failed['ids'], 'A retry whose tax sync failed again should stay on the failed imports list.' );
+ }
+
+ /**
+ * Make the next write to the tax lookup table fail, the way a database error would.
+ *
+ * @return callable Restores normal writes.
+ */
+ private function break_next_lookup_write(): callable {
+ $table_name = TaxesDataStore::get_db_table_name();
+ $broken = false;
+
+ $filter = function ( $query ) use ( &$broken, $table_name ) {
+ if ( $broken || 0 !== strpos( $query, "REPLACE INTO {$table_name}" ) ) {
+ return $query;
+ }
+
+ $broken = true;
+
+ // A table that does not exist, so the write fails the way a database error would.
+ return "REPLACE INTO `{$table_name}_missing` (order_id) VALUES (1)";
+ };
+
+ add_filter( 'query', $filter );
+
+ return function () use ( $filter ) {
+ remove_filter( 'query', $filter );
+ };
+ }
+
/**
* Clear any scheduled batch processor actions.
*