Commit 91f51c1ac6c for woocommerce
commit 91f51c1ac6c2a49691fcc3532a806fa5aefc62c5
Author: Miroslav Mitev <m1r0@users.noreply.github.com>
Date: Thu Oct 8 15:52:18 2026 +0300
Split the Taxes report taxable amount into order and shipping gross (#68890)
diff --git a/packages/js/data/changelog/43842-analytics-taxes-gross-split b/packages/js/data/changelog/43842-analytics-taxes-gross-split
new file mode 100644
index 00000000000..d814d3f678f
--- /dev/null
+++ b/packages/js/data/changelog/43842-analytics-taxes-gross-split
@@ -0,0 +1,4 @@
+Significance: minor
+Type: add
+
+Add the order_taxable_amount and shipping_taxable_amount fields to the TaxesReport type.
diff --git a/packages/js/data/src/reports/types.ts b/packages/js/data/src/reports/types.ts
index a25051967f4..9903c1942b1 100644
--- a/packages/js/data/src/reports/types.ts
+++ b/packages/js/data/src/reports/types.ts
@@ -222,6 +222,10 @@ export type TaxesReport = {
shipping_tax: number;
/** Taxable amount. */
taxable_amount?: number;
+ /** Taxable amount of line items and fees. */
+ order_taxable_amount?: number;
+ /** Taxable amount of shipping. */
+ shipping_taxable_amount?: number;
/** Number of orders. */
orders_count: number;
};
diff --git a/plugins/woocommerce/changelog/43842-analytics-taxes-gross-split b/plugins/woocommerce/changelog/43842-analytics-taxes-gross-split
new file mode 100644
index 00000000000..edf0394869c
--- /dev/null
+++ b/plugins/woocommerce/changelog/43842-analytics-taxes-gross-split
@@ -0,0 +1,4 @@
+Significance: minor
+Type: add
+
+Split the taxable amount of the Analytics Taxes report into Order gross and Shipping gross columns.
diff --git a/plugins/woocommerce/client/admin/client/analytics/report/taxes/table.js b/plugins/woocommerce/client/admin/client/analytics/report/taxes/table.js
index 6d43abf8ea5..3f37055fd66 100644
--- a/plugins/woocommerce/client/admin/client/analytics/report/taxes/table.js
+++ b/plugins/woocommerce/client/admin/client/analytics/report/taxes/table.js
@@ -14,6 +14,19 @@ import { CurrencyContext } from '@woocommerce/currency';
*/
import { getTaxCode } from './utils';
import ReportTable from '../../components/report-table';
+
+/**
+ * Whether a taxable amount was recorded for a report row. A zero base under a non-zero tax
+ * marks a row recorded before that base existed (or a manual tax line) - unknown, not zero.
+ *
+ * @param {number|undefined} amount Taxable amount.
+ * @param {number} tax Tax charged on that amount.
+ * @return {boolean} True when the amount is known.
+ */
+function hasTaxableAmount( amount, tax ) {
+ return amount !== undefined && ! ( amount === 0 && tax !== 0 );
+}
+
class TaxesReportTable extends Component {
constructor() {
super();
@@ -58,6 +71,16 @@ class TaxesReportTable extends Component {
key: 'taxable_amount',
isSortable: true,
},
+ {
+ label: __( 'Order gross', 'woocommerce' ),
+ key: 'order_taxable_amount',
+ isSortable: true,
+ },
+ {
+ label: __( 'Shipping gross', 'woocommerce' ),
+ key: 'shipping_taxable_amount',
+ isSortable: true,
+ },
{
label: __( 'Orders', 'woocommerce' ),
key: 'orders_count',
@@ -76,6 +99,17 @@ class TaxesReportTable extends Component {
getCurrencyConfig,
} = this.context;
+ const renderTaxableAmount = ( amount, taxCharged ) =>
+ hasTaxableAmount( amount, taxCharged )
+ ? {
+ display: renderCurrency( amount ),
+ value: getCurrencyFormatDecimal( amount ),
+ }
+ : {
+ display: __( 'N/A', 'woocommerce' ),
+ value: '',
+ };
+
return map( taxes, ( tax ) => {
const { query } = this.props;
const {
@@ -86,12 +120,9 @@ class TaxesReportTable extends Component {
total_tax: totalTax,
shipping_tax: shippingTax,
taxable_amount: taxableAmount,
+ order_taxable_amount: orderTaxableAmount,
+ shipping_taxable_amount: shippingTaxableAmount,
} = tax;
- // A zero base under a non-zero tax marks a lookup row recorded before the
- // taxable amount existed (or a manual tax line) - unknown, not zero.
- const hasTaxableAmount =
- taxableAmount !== undefined &&
- ! ( taxableAmount === 0 && totalTax !== 0 );
const taxCode = getTaxCode( tax );
const persistedQuery = getPersistedQuery( query );
@@ -130,14 +161,9 @@ class TaxesReportTable extends Component {
display: renderCurrency( shippingTax ),
value: getCurrencyFormatDecimal( shippingTax ),
},
- {
- display: hasTaxableAmount
- ? renderCurrency( taxableAmount )
- : __( 'N/A', 'woocommerce' ),
- value: hasTaxableAmount
- ? getCurrencyFormatDecimal( taxableAmount )
- : '',
- },
+ renderTaxableAmount( taxableAmount, totalTax ),
+ renderTaxableAmount( orderTaxableAmount, orderTax ),
+ renderTaxableAmount( shippingTaxableAmount, shippingTax ),
{
display: formatValue(
getCurrencyConfig(),
diff --git a/plugins/woocommerce/client/admin/client/analytics/report/taxes/test/table.test.js b/plugins/woocommerce/client/admin/client/analytics/report/taxes/test/table.test.js
new file mode 100644
index 00000000000..82b20bbec0b
--- /dev/null
+++ b/plugins/woocommerce/client/admin/client/analytics/report/taxes/test/table.test.js
@@ -0,0 +1,116 @@
+/**
+ * Internal dependencies
+ */
+import TaxesReportTable from '../table';
+
+// The three taxable amount cells, in the order getRowsContent() returns them.
+const TAXABLE_AMOUNT = 5;
+const ORDER_GROSS = 6;
+const SHIPPING_GROSS = 7;
+
+const currencyContext = {
+ render: ( amount ) => `[${ amount }]`,
+ formatDecimal: ( amount ) => amount,
+ getCurrencyConfig: () => ( {} ),
+};
+
+const taxRow = ( overrides ) => ( {
+ tax_rate_id: 1,
+ name: 'VAT',
+ tax_rate: 19,
+ country: 'DE',
+ state: '',
+ priority: 1,
+ total_tax: 40.85,
+ order_tax: 39.9,
+ shipping_tax: 0.95,
+ orders_count: 1,
+ ...overrides,
+} );
+
+const rowCells = ( tax ) => {
+ const table = new TaxesReportTable();
+ table.context = currencyContext;
+ table.props = { query: {} };
+
+ return table.getRowsContent( [ tax ] )[ 0 ];
+};
+
+describe( 'TaxesReportTable taxable amount cells', () => {
+ it( 'renders a recorded amount and each of its parts', () => {
+ const cells = rowCells(
+ taxRow( {
+ taxable_amount: 215,
+ order_taxable_amount: 210,
+ shipping_taxable_amount: 5,
+ } )
+ );
+
+ expect( cells[ TAXABLE_AMOUNT ] ).toEqual( {
+ display: '[215]',
+ value: 215,
+ } );
+ expect( cells[ ORDER_GROSS ] ).toEqual( {
+ display: '[210]',
+ value: 210,
+ } );
+ expect( cells[ SHIPPING_GROSS ] ).toEqual( {
+ display: '[5]',
+ value: 5,
+ } );
+ } );
+
+ it( 'renders a part the report left out as unknown', () => {
+ // The report leaves the parts out while the rate holds a row the rebuild has not
+ // reached, and on a store still missing the columns.
+ const cells = rowCells( taxRow( { taxable_amount: 215 } ) );
+
+ expect( cells[ TAXABLE_AMOUNT ] ).toEqual( {
+ display: '[215]',
+ value: 215,
+ } );
+ expect( cells[ ORDER_GROSS ] ).toEqual( { display: 'N/A', value: '' } );
+ expect( cells[ SHIPPING_GROSS ] ).toEqual( {
+ display: 'N/A',
+ value: '',
+ } );
+ } );
+
+ it( 'renders a zero under a tax that was charged as unknown, not as a zero', () => {
+ const cells = rowCells(
+ taxRow( {
+ taxable_amount: 0,
+ order_taxable_amount: 0,
+ shipping_taxable_amount: 0,
+ } )
+ );
+
+ expect( cells[ TAXABLE_AMOUNT ] ).toEqual( {
+ display: 'N/A',
+ value: '',
+ } );
+ expect( cells[ ORDER_GROSS ] ).toEqual( { display: 'N/A', value: '' } );
+ expect( cells[ SHIPPING_GROSS ] ).toEqual( {
+ display: 'N/A',
+ value: '',
+ } );
+ } );
+
+ it( 'renders a zero part of a rate that charged no tax on that part as a zero', () => {
+ const cells = rowCells(
+ taxRow( {
+ order_tax: 0,
+ shipping_tax: 0.95,
+ taxable_amount: 5,
+ order_taxable_amount: 0,
+ shipping_taxable_amount: 5,
+ } )
+ );
+
+ expect( cells[ ORDER_GROSS ] ).toEqual( { display: '[0]', value: 0 } );
+ expect( cells[ SHIPPING_GROSS ] ).toEqual( {
+ display: '[5]',
+ value: 5,
+ } );
+ } );
+} );
diff --git a/plugins/woocommerce/includes/class-wc-install.php b/plugins/woocommerce/includes/class-wc-install.php
index ba02c2b8ea7..b1c125c8607 100644
--- a/plugins/woocommerce/includes/class-wc-install.php
+++ b/plugins/woocommerce/includes/class-wc-install.php
@@ -18,6 +18,7 @@ use Automattic\WooCommerce\Internal\Features\FeaturesController;
use Automattic\WooCommerce\Internal\ProductAttributesLookup\DataRegenerator;
use Automattic\WooCommerce\Internal\ProductDownloads\ApprovedDirectories\Synchronize as Download_Directories_Sync;
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\FeaturesUtil;
use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil;
use Automattic\WooCommerce\Internal\WCCom\ConnectionHelper as WCConnectionHelper;
@@ -367,6 +368,7 @@ class WC_Install {
'11.3.0' => array(
'wc_update_1130_set_legacy_variation_price_hash_option',
'wc_update_1130_delete_unpublished_variation_lookup_rows',
+ 'wc_update_1130_split_tax_lookup_taxable_amount',
),
);
@@ -1814,6 +1816,7 @@ class WC_Install {
// Clear table caches.
delete_transient( 'wc_attribute_taxonomies' );
+ TaxesDataStore::flush_lookup_columns_cache();
return $db_delta_result;
}
@@ -2133,6 +2136,8 @@ CREATE TABLE {$wpdb->prefix}wc_order_tax_lookup (
order_tax double DEFAULT 0 NOT NULL,
total_tax double DEFAULT 0 NOT NULL,
taxable_amount double DEFAULT 0 NOT NULL,
+ order_taxable_amount double DEFAULT NULL,
+ shipping_taxable_amount double DEFAULT NULL,
PRIMARY KEY (order_id, tax_rate_id, order_item_id),
KEY tax_rate_id (tax_rate_id),
KEY date_created (date_created)
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 96ee34ffaaf..343b410c71a 100644
--- a/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php
+++ b/plugins/woocommerce/includes/react-admin/wc-admin-update-functions.php
@@ -7,6 +7,7 @@
* @package WooCommerce\Admin
*/
+use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
use Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskLists;
use Automattic\WooCommerce\Admin\Notes\Notes;
use Automattic\WooCommerce\Internal\Admin\Notes\UnsecuredReportFiles;
@@ -326,3 +327,18 @@ function wc_update_1050_add_idx_user_email() {
function wc_update_11201_migrate_tax_lookup_order_items() {
wc_get_container()->get( BatchProcessingController::class )->enqueue_processor( OrderTaxLookupMigrator::class );
}
+
+/**
+ * Queue the rebuild of `wc_order_tax_lookup` rows recorded before the taxable amount was split
+ * into its order and shipping parts.
+ *
+ * @since 11.3.0
+ *
+ * @return void
+ */
+function wc_update_1130_split_tax_lookup_taxable_amount() {
+ wc_get_container()->get( BatchProcessingController::class )->enqueue_processor( OrderTaxLookupMigrator::class );
+
+ // A store with nothing to rebuild would otherwise keep serving cached rows without the split.
+ ReportsCache::invalidate();
+}
diff --git a/plugins/woocommerce/src/Admin/API/Reports/Taxes/Controller.php b/plugins/woocommerce/src/Admin/API/Reports/Taxes/Controller.php
index a2c8e416c70..ff8cb1249bc 100644
--- a/plugins/woocommerce/src/Admin/API/Reports/Taxes/Controller.php
+++ b/plugins/woocommerce/src/Admin/API/Reports/Taxes/Controller.php
@@ -123,67 +123,79 @@ class Controller extends GenericController implements ExportableInterface {
'title' => 'report_taxes',
'type' => 'object',
'properties' => array(
- 'tax_rate_id' => array(
+ 'tax_rate_id' => array(
'description' => __( 'Tax rate ID.', 'woocommerce' ),
'type' => 'integer',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'name' => array(
+ 'name' => array(
'description' => __( 'Tax rate name.', 'woocommerce' ),
'type' => 'string',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'tax_rate' => array(
+ 'tax_rate' => array(
'description' => __( 'Tax rate.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'country' => array(
+ 'country' => array(
'description' => __( 'Country / Region.', 'woocommerce' ),
'type' => 'string',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'state' => array(
+ 'state' => array(
'description' => __( 'State.', 'woocommerce' ),
'type' => 'string',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'priority' => array(
+ 'priority' => array(
'description' => __( 'Priority.', 'woocommerce' ),
'type' => 'integer',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'total_tax' => array(
+ 'total_tax' => array(
'description' => __( 'Total tax.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'order_tax' => array(
+ 'order_tax' => array(
'description' => __( 'Order tax.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'shipping_tax' => array(
+ 'shipping_tax' => array(
'description' => __( 'Shipping tax.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'taxable_amount' => array(
+ 'taxable_amount' => array(
'description' => __( 'Taxable amount.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
- 'orders_count' => array(
+ 'order_taxable_amount' => array(
+ 'description' => __( 'Taxable amount of line items and fees.', 'woocommerce' ),
+ 'type' => 'number',
+ 'context' => array( 'view', 'edit' ),
+ 'readonly' => true,
+ ),
+ 'shipping_taxable_amount' => array(
+ 'description' => __( 'Taxable amount of shipping.', 'woocommerce' ),
+ 'type' => 'number',
+ 'context' => array( 'view', 'edit' ),
+ 'readonly' => true,
+ ),
+ 'orders_count' => array(
'description' => __( 'Number of orders.', 'woocommerce' ),
'type' => 'integer',
'context' => array( 'view', 'edit' ),
@@ -213,6 +225,8 @@ class Controller extends GenericController implements ExportableInterface {
'total_tax',
'shipping_tax',
'taxable_amount',
+ 'order_taxable_amount',
+ 'shipping_taxable_amount',
'orders_count',
)
);
@@ -246,13 +260,15 @@ class Controller extends GenericController implements ExportableInterface {
*/
public function get_export_columns() {
$export_columns = array(
- 'tax_code' => __( 'Tax code', 'woocommerce' ),
- 'rate' => __( 'Rate', 'woocommerce' ),
- 'total_tax' => __( 'Total tax', 'woocommerce' ),
- 'order_tax' => __( 'Order tax', 'woocommerce' ),
- 'shipping_tax' => __( 'Shipping tax', 'woocommerce' ),
- 'taxable_amount' => __( 'Taxable amount', 'woocommerce' ),
- 'orders_count' => __( 'Orders', 'woocommerce' ),
+ 'tax_code' => __( 'Tax code', 'woocommerce' ),
+ 'rate' => __( 'Rate', 'woocommerce' ),
+ 'total_tax' => __( 'Total tax', 'woocommerce' ),
+ 'order_tax' => __( 'Order tax', 'woocommerce' ),
+ 'shipping_tax' => __( 'Shipping tax', 'woocommerce' ),
+ 'taxable_amount' => __( 'Taxable amount', 'woocommerce' ),
+ 'order_taxable_amount' => __( 'Order gross', 'woocommerce' ),
+ 'shipping_taxable_amount' => __( 'Shipping gross', 'woocommerce' ),
+ 'orders_count' => __( 'Orders', 'woocommerce' ),
);
/**
@@ -272,7 +288,7 @@ class Controller extends GenericController implements ExportableInterface {
*/
public function prepare_item_for_export( $item ) {
$export_item = array(
- 'tax_code' => \WC_Tax::get_rate_code(
+ 'tax_code' => \WC_Tax::get_rate_code(
(object) array(
'tax_rate_id' => $item['tax_rate_id'],
'tax_rate_country' => $item['country'],
@@ -281,12 +297,14 @@ class Controller extends GenericController implements ExportableInterface {
'tax_rate_priority' => $item['priority'],
)
),
- 'rate' => $item['tax_rate'],
- 'total_tax' => self::csv_number_format( $item['total_tax'] ),
- 'order_tax' => self::csv_number_format( $item['order_tax'] ),
- 'shipping_tax' => self::csv_number_format( $item['shipping_tax'] ),
- 'taxable_amount' => $this->prepare_taxable_amount_for_export( $item ),
- 'orders_count' => $item['orders_count'],
+ 'rate' => $item['tax_rate'],
+ 'total_tax' => self::csv_number_format( $item['total_tax'] ),
+ 'order_tax' => self::csv_number_format( $item['order_tax'] ),
+ 'shipping_tax' => self::csv_number_format( $item['shipping_tax'] ),
+ 'taxable_amount' => $this->prepare_taxable_amount_for_export( $item, 'taxable_amount', 'total_tax' ),
+ 'order_taxable_amount' => $this->prepare_taxable_amount_for_export( $item, 'order_taxable_amount', 'order_tax' ),
+ 'shipping_taxable_amount' => $this->prepare_taxable_amount_for_export( $item, 'shipping_taxable_amount', 'shipping_tax' ),
+ 'orders_count' => $item['orders_count'],
);
/**
@@ -301,22 +319,24 @@ class Controller extends GenericController implements ExportableInterface {
}
/**
- * Format the taxable amount of a report row for export.
+ * Format a taxable amount of a report row for export.
*
- * A zero base under a non-zero tax marks a lookup row recorded before the taxable
- * amount existed (or a manual tax line) - unknown, so exported as an empty cell
+ * A zero base under a non-zero tax marks a lookup row recorded before that base
+ * existed (or a manual tax line) - unknown, so exported as an empty cell
* rather than a zero a merchant could mistake for a filing figure.
*
- * @param array $item Single report item/row.
+ * @param array $item Single report item/row.
+ * @param string $amount_key Key of the taxable amount to format.
+ * @param string $tax_key Key of the tax charged on that amount.
* @return string
*/
- private function prepare_taxable_amount_for_export( $item ) {
- if ( ! isset( $item['taxable_amount'] ) ) {
+ private function prepare_taxable_amount_for_export( $item, $amount_key, $tax_key ) {
+ if ( ! isset( $item[ $amount_key ] ) ) {
return '';
}
- $unknown = 0.0 === (float) $item['taxable_amount'] && 0.0 !== (float) ( $item['total_tax'] ?? 0 );
+ $unknown = 0.0 === (float) $item[ $amount_key ] && 0.0 !== (float) ( $item[ $tax_key ] ?? 0 );
- return $unknown ? '' : self::csv_number_format( $item['taxable_amount'] );
+ return $unknown ? '' : self::csv_number_format( $item[ $amount_key ] );
}
}
diff --git a/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php b/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
index 543480f06aa..879afb61c7f 100644
--- a/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
+++ b/plugins/woocommerce/src/Admin/API/Reports/Taxes/DataStore.php
@@ -45,17 +45,19 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
* @var array
*/
protected $column_types = array(
- 'tax_rate_id' => 'intval',
- 'name' => 'strval',
- 'tax_rate' => 'floatval',
- 'country' => 'strval',
- 'state' => 'strval',
- 'priority' => 'intval',
- 'total_tax' => 'floatval',
- 'order_tax' => 'floatval',
- 'shipping_tax' => 'floatval',
- 'taxable_amount' => 'floatval',
- 'orders_count' => 'intval',
+ 'tax_rate_id' => 'intval',
+ 'name' => 'strval',
+ 'tax_rate' => 'floatval',
+ 'country' => 'strval',
+ 'state' => 'strval',
+ 'priority' => 'intval',
+ 'total_tax' => 'floatval',
+ 'order_tax' => 'floatval',
+ 'shipping_tax' => 'floatval',
+ 'taxable_amount' => 'floatval',
+ 'order_taxable_amount' => 'floatval',
+ 'shipping_taxable_amount' => 'floatval',
+ 'orders_count' => 'intval',
);
/**
@@ -99,27 +101,46 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
// but given this query is paginated and cached, then it is not a big deal. There is always room for
// improvements here.
$this->report_columns = array(
- 'tax_rate_id' => "{$table_name}.tax_rate_id",
- 'name' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-2), '-', 1) as name",
- 'tax_rate' => 'CAST(itemmeta_rate_percent.meta_value AS DECIMAL(7,4)) as tax_rate',
- 'country' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',1) as country",
- 'state' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-3), '-', 1) as state",
- 'priority' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-1) as priority",
- 'total_tax' => 'SUM(total_tax) as total_tax',
- 'order_tax' => 'SUM(order_tax) as order_tax',
- 'shipping_tax' => 'SUM(shipping_tax) as shipping_tax',
- 'taxable_amount' => 'SUM(taxable_amount) as taxable_amount',
+ 'tax_rate_id' => "{$table_name}.tax_rate_id",
+ 'name' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-2), '-', 1) as name",
+ 'tax_rate' => 'CAST(itemmeta_rate_percent.meta_value AS DECIMAL(7,4)) as tax_rate',
+ 'country' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',1) as country",
+ 'state' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-3), '-', 1) as state",
+ 'priority' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-1) as priority",
+ 'total_tax' => 'SUM(total_tax) as total_tax',
+ 'order_tax' => 'SUM(order_tax) as order_tax',
+ 'shipping_tax' => 'SUM(shipping_tax) as shipping_tax',
+ 'taxable_amount' => 'SUM(taxable_amount) as taxable_amount',
+ 'order_taxable_amount' => self::taxable_amount_part_column( 'order_taxable_amount' ),
+ 'shipping_taxable_amount' => self::taxable_amount_part_column( 'shipping_taxable_amount' ),
// parent_id stays unqualified: wc_order_stats is the only joined table carrying it, and
// this string is carried by the public woocommerce_admin_report_columns filter, so it must
// match the released form for extension callbacks that inspect or rewrite it.
- 'orders_count' => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count",
+ 'orders_count' => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count",
);
- // Guard against the column not existing yet: the report otherwise breaks entirely on a
+ // Guard against a column not existing yet: the report otherwise breaks entirely on a
// site where the upgrade routine has not run (or could not run) the schema update.
if ( ! static::has_taxable_amount_column() ) {
unset( $this->report_columns['taxable_amount'] );
}
+
+ if ( ! static::has_taxable_amount_split_columns() ) {
+ unset( $this->report_columns['order_taxable_amount'], $this->report_columns['shipping_taxable_amount'] );
+ }
+ }
+
+ /**
+ * SQL summing one part of the taxable amount of a tax rate.
+ *
+ * Selects NULL while the rate holds a row recorded before the split (the part is NULL), since
+ * summing it in would report a part that is short with nothing saying so.
+ *
+ * @param string $column Column holding the part, `order_taxable_amount` or `shipping_taxable_amount`.
+ * @return string
+ */
+ private static function taxable_amount_part_column( string $column ): string {
+ return "CASE WHEN MAX( {$column} IS NULL ) = 1 THEN NULL ELSE SUM({$column}) END as {$column}";
}
/**
@@ -276,31 +297,79 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
/**
* Check if the wc_order_tax_lookup table has the taxable_amount column.
*
- * Asks the schema on every request rather than caching the answer in an option, so a
- * column that appears or disappears behind WooCommerce's back (a partial restore, a
- * manual drop) corrects the report on the next request.
- *
* @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
* @since 11.2.0
*
* @return bool
*/
public static function has_taxable_amount_column() {
- global $wpdb;
+ return self::lookup_has_column( 'taxable_amount' );
+ }
- // One schema check per request: imports call this once per synced order. Keyed by
- // blog id since the schema is per site.
- static $has_column = array();
+ /**
+ * Check if the wc_order_tax_lookup table has the taxable_amount column and the columns
+ * holding its order and shipping parts.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ * @since 11.3.0
+ *
+ * @return bool
+ */
+ public static function has_taxable_amount_split_columns() {
+ return static::has_taxable_amount_column()
+ && self::lookup_has_column( 'order_taxable_amount' )
+ && self::lookup_has_column( 'shipping_taxable_amount' );
+ }
+ /**
+ * Columns of the lookup table, read once per request and keyed by blog id.
+ *
+ * @var array<int, string[]>
+ */
+ private static $lookup_columns = array();
+
+ /**
+ * Forget the columns this request has read, so the next check asks the schema again.
+ *
+ * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
+ * @since 11.3.0
+ *
+ * @return void
+ */
+ public static function flush_lookup_columns_cache() {
+ self::$lookup_columns = array();
+ }
+
+ /**
+ * Check if the wc_order_tax_lookup table has a column.
+ *
+ * @param string $column Column name.
+ * @return bool
+ */
+ private static function lookup_has_column( string $column ): bool {
$blog_id = get_current_blog_id();
- if ( isset( $has_column[ $blog_id ] ) ) {
- return $has_column[ $blog_id ];
+ if ( ! isset( self::$lookup_columns[ $blog_id ] ) ) {
+ self::$lookup_columns[ $blog_id ] = self::read_lookup_columns();
}
+ return in_array( $column, self::$lookup_columns[ $blog_id ], true );
+ }
+
+ /**
+ * The columns the wc_order_tax_lookup table holds.
+ *
+ * Asks the schema rather than an option, so a column dropped or restored behind WooCommerce's
+ * back corrects the report by itself.
+ *
+ * @return string[] Column names, empty while the table does not exist.
+ */
+ private static function read_lookup_columns(): array {
+ global $wpdb;
+
$table_name = self::get_db_table_name();
- // If the table itself does not exist yet, checking its columns would be a DB error.
+ // If the table itself does not exist yet, reading its columns would be a DB error.
$table_exists = $wpdb->get_var(
$wpdb->prepare(
'SHOW TABLES LIKE %s',
@@ -309,21 +378,13 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
);
if ( ! $table_exists ) {
- $has_column[ $blog_id ] = false;
- return false;
+ return array();
}
- $column_exists = $wpdb->get_var(
- $wpdb->prepare(
- // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name cannot be prepared.
- "SHOW COLUMNS FROM `{$table_name}` LIKE %s",
- 'taxable_amount'
- )
+ return $wpdb->get_col(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name cannot be prepared.
+ "SHOW COLUMNS FROM `{$table_name}`"
);
-
- $has_column[ $blog_id ] = ! empty( $column_exists );
-
- return $has_column[ $blog_id ];
}
/**
@@ -446,9 +507,10 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
'page_no' => 0,
);
- // While the taxable_amount column is missing its report column is unset, so ordering
+ // While a taxable amount column is missing its report column is unset, so ordering
// by it would be a SQL error. Fall back to the default order.
- if ( 'taxable_amount' === ( $query_args['orderby'] ?? '' ) && ! isset( $this->report_columns['taxable_amount'] ) ) {
+ $orderby = $query_args['orderby'] ?? '';
+ if ( in_array( $orderby, array( 'taxable_amount', 'order_taxable_amount', 'shipping_taxable_amount' ), true ) && ! isset( $this->report_columns[ $orderby ] ) ) {
$query_args['orderby'] = 'tax_rate_id';
}
@@ -481,7 +543,7 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
$this->subquery->clear_sql_clause( 'select' );
$this->subquery->add_sql_clause( 'select', $this->selected_columns( $query_args ) );
- if ( in_array( $query_args['orderby'], array( 'total_tax', 'order_tax', 'shipping_tax', 'taxable_amount', 'orders_count' ), true ) ) {
+ if ( in_array( $query_args['orderby'], array( 'total_tax', 'order_tax', 'shipping_tax', 'taxable_amount', 'order_taxable_amount', 'shipping_taxable_amount', 'orders_count' ), true ) ) {
$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', tax_rate_id' );
} else {
$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) );
@@ -500,6 +562,7 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
return $data;
}
+ $tax_data = array_map( array( $this, 'drop_unknown_taxable_amount_parts' ), $tax_data );
$tax_data = array_map( array( $this, 'cast_numbers' ), $tax_data );
$data = (object) array(
'data' => $tax_data,
@@ -511,6 +574,22 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
return $data;
}
+ /**
+ * Drop the taxable amount parts selected as NULL, before the float cast turns them into zeros.
+ *
+ * @param array $row Single report row.
+ * @return array
+ */
+ private function drop_unknown_taxable_amount_parts( $row ) {
+ foreach ( array( 'order_taxable_amount', 'shipping_taxable_amount' ) as $column ) {
+ if ( array_key_exists( $column, $row ) && null === $row[ $column ] ) {
+ unset( $row[ $column ] );
+ }
+ }
+
+ return $row;
+ }
+
/**
* Maps ordering specified by the user to columns in the database/fields in the data.
*
@@ -679,10 +758,12 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
);
}
- // Guard against the column not existing yet: wpdb::replace() with an unknown column
- // fails whole, which would silently drop the order from the Taxes report.
+ // Guard against a column not existing yet: a write naming an unknown column fails
+ // whole, which would silently drop the order from the Taxes report.
$has_taxable_amount_column = static::has_taxable_amount_column();
+ $has_split_columns = static::has_taxable_amount_split_columns();
$taxable_amounts = array();
+ $taxable_amount_parts = array();
// Also skip orders without tax lines: computing bases would hydrate every
// line item, fee and shipping row for a write that never happens.
@@ -712,7 +793,12 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
$compound_rate_ids[] = $tax_item->get_rate_id();
}
}
+ // Both stay called so a subclass overriding either one keeps working.
$taxable_amounts = static::get_taxable_amounts_by_rate( $order, $compound_rate_ids );
+
+ if ( $has_split_columns ) {
+ $taxable_amount_parts = static::get_taxable_amount_parts_by_rate( $order, $compound_rate_ids );
+ }
}
foreach ( $tax_items as $tax_item ) {
@@ -737,19 +823,29 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
(float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total()
);
+ $placeholders = '(%d, %s, %d, %d, %f, %f, %f';
+
+ // When tax lines share a rate only the first row carries the rate's bases. On an
+ // unkeyed table the lines collapse into one row, so every write carries them.
if ( $has_taxable_amount_column ) {
- // The bases are computed per rate and the report sums the column per rate, so
- // when tax lines share a rate only the first row carries the rate's base. On an
- // unkeyed table the lines collapse into one row and the last write wins, so
- // there every write carries the base.
- $values[] = $taxable_amounts[ $tax_rate_id ] ?? 0;
+ $values[] = $taxable_amounts[ $tax_rate_id ] ?? 0;
+ $placeholders .= ', %f';
if ( $keyed_by_item ) {
unset( $taxable_amounts[ $tax_rate_id ] );
}
- $rows[] = '(%d, %s, %d, %d, %f, %f, %f, %f)';
- } else {
- $rows[] = '(%d, %s, %d, %d, %f, %f, %f)';
}
+
+ if ( $has_split_columns ) {
+ $parts = $taxable_amount_parts[ $tax_rate_id ] ?? array();
+ $values[] = $parts['order'] ?? 0;
+ $values[] = $parts['shipping'] ?? 0;
+ $placeholders .= ', %f, %f';
+ if ( $keyed_by_item ) {
+ unset( $taxable_amount_parts[ $tax_rate_id ] );
+ }
+ }
+
+ $rows[] = $placeholders . ')';
}
// One statement for the whole order. Rebuilding only some of its lines would leave a row
@@ -761,6 +857,9 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
if ( $has_taxable_amount_column ) {
$columns .= ', taxable_amount';
}
+ if ( $has_split_columns ) {
+ $columns .= ', order_taxable_amount, shipping_taxable_amount';
+ }
$written = $wpdb->query(
$wpdb->prepare(
@@ -831,17 +930,36 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
/**
* Sum the net totals of the order parts (line items, fees, shipping) each tax rate applied to.
*
+ * @since 11.2.0
+ *
+ * @param \WC_Order|\WC_Order_Refund $order Order object.
+ * @param int[] $compound_rate_ids Ids of the order's compound tax rates.
+ * @return array Map of tax rate id => taxable amount.
+ */
+ protected static function get_taxable_amounts_by_rate( $order, $compound_rate_ids = array() ) {
+ return array_map(
+ static function ( $parts ) {
+ return $parts['order'] + $parts['shipping'];
+ },
+ static::get_taxable_amount_parts_by_rate( $order, $compound_rate_ids )
+ );
+ }
+
+ /**
+ * Sum the net totals each tax rate applied to, split into the order part (line items and
+ * fees) and the shipping part.
+ *
* Computed from the items rather than derived from the tax amounts, so zero-rated
* sales still record the base amount they were taxed on. A compound rate is applied
* on top of the other taxes of the same item, so its base includes them.
*
- * @since 11.2.0
+ * @since 11.3.0
*
* @param \WC_Order|\WC_Order_Refund $order Order object.
* @param int[] $compound_rate_ids Ids of the order's compound tax rates.
- * @return array Map of tax rate id => taxable amount.
+ * @return array Map of tax rate id => `array( 'order' => float, 'shipping' => float )`.
*/
- protected static function get_taxable_amounts_by_rate( $order, $compound_rate_ids = array() ) {
+ protected static function get_taxable_amount_parts_by_rate( $order, $compound_rate_ids = array() ) {
$amounts = array();
/**
* Taxable line items of the order.
@@ -856,6 +974,8 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
continue;
}
+ $part = OrderItemType::SHIPPING === $item->get_type() ? 'shipping' : 'order';
+
$non_compound_tax = 0.0;
foreach ( $taxes['total'] as $rate_id => $tax ) {
if ( is_numeric( $tax ) && ! in_array( (int) $rate_id, $compound_rate_ids, true ) ) {
@@ -881,7 +1001,15 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
$base += $non_compound_tax + $compound_running_tax;
$compound_running_tax += (float) $tax;
}
- $amounts[ $rate_id ] = ( $amounts[ $rate_id ] ?? 0 ) + $base;
+
+ if ( ! isset( $amounts[ $rate_id ] ) ) {
+ $amounts[ $rate_id ] = array(
+ 'order' => 0.0,
+ 'shipping' => 0.0,
+ );
+ }
+
+ $amounts[ $rate_id ][ $part ] += $base;
}
}
diff --git a/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php b/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php
index ac935f6b761..e3afd6b783a 100644
--- a/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php
+++ b/plugins/woocommerce/src/Internal/Admin/OrderTaxLookupMigrator.php
@@ -19,13 +19,13 @@ 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.
+ * Rebuilds the `wc_order_tax_lookup` rows of orders recorded before the table held the full tax
+ * detail the reports read today, 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.
+ * It makes one pass per shape: rows written before the table held one row per tax order item
+ * (`order_item_id = 0`), and rows written before the taxable amount was split into its order and
+ * shipping parts. 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.
*
@@ -35,19 +35,31 @@ defined( 'ABSPATH' ) || exit;
class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksInterface {
/**
- * Option holding the highest order id the processor has been through.
+ * Option holding the highest order id the tax order item pass 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.
+ * front of the next pass. Delete it by hand to run the pass over the whole table again.
*
* @var string
*/
const CURSOR_OPTION = 'woocommerce_order_tax_lookup_migration_last_order_id';
+ /**
+ * Option holding the highest order id the taxable amount split pass has been through.
+ *
+ * A cursor of its own, since resetting the shared one would race a batch in flight, which
+ * writes back the cursor it read before the reset.
+ *
+ * @since 11.3.0
+ *
+ * @var string
+ */
+ const SPLIT_CURSOR_OPTION = 'woocommerce_order_tax_lookup_split_migration_last_order_id';
+
/**
* How far `get_total_pending_count()` counts before it reports "this many or more".
*
@@ -84,12 +96,56 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
* @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.';
+ return 'Rebuilds wc_order_tax_lookup rows recorded before the table held the full tax detail the reports read today, so that Analytics tax reports account for every tax line an order carries and for the amounts each rate applied to.';
+ }
+
+ /**
+ * The passes the rebuild makes over the lookup table. A pass is only offered once the columns
+ * it reads exist.
+ *
+ * @return array[] List of `array( 'condition' => string, 'cursor' => string )`.
+ */
+ private function get_pending_passes(): array {
+ $passes = array(
+ array(
+ 'condition' => 'order_item_id = 0',
+ 'cursor' => self::CURSOR_OPTION,
+ ),
+ );
+
+ if ( TaxesDataStore::has_taxable_amount_split_columns() ) {
+ $passes[] = array(
+ 'condition' => 'order_taxable_amount IS NULL',
+ 'cursor' => self::SPLIT_CURSOR_OPTION,
+ );
+ }
+
+ return $passes;
+ }
+
+ /**
+ * SQL matching the lookup rows the rebuild would rewrite, each pass from its own cursor.
+ *
+ * @return array `array( 'where' => string, 'values' => int[] )`, the values in placeholder order.
+ */
+ private function get_pending_rows_sql(): array {
+ $clauses = array();
+ $values = array();
+
+ foreach ( $this->get_pending_passes() as $pass ) {
+ $clauses[] = "( order_id > %d AND ( {$pass['condition']} ) )";
+ $values[] = $this->get_cursor( $pass['cursor'] );
+ }
+
+ return array(
+ 'where' => '( ' . implode( ' OR ', $clauses ) . ' )',
+ 'values' => $values,
+ );
}
/**
- * 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.
+ * Get the number of orders left to go through that still hold rows in an outdated shape, 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
@@ -107,13 +163,13 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
}
$table_name = TaxesDataStore::get_db_table_name();
+ $pending = $this->get_pending_rows_sql();
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
+ "SELECT COUNT(*) FROM ( SELECT DISTINCT order_id FROM {$table_name} WHERE {$pending['where']} LIMIT %d ) AS pending",
+ array_merge( $pending['values'], array( self::PENDING_COUNT_LIMIT ) )
)
);
}
@@ -141,13 +197,13 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
}
$table_name = TaxesDataStore::get_db_table_name();
+ $pending = $this->get_pending_rows_sql();
$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
+ "SELECT DISTINCT order_id FROM {$table_name} WHERE {$pending['where']} ORDER BY order_id ASC LIMIT %d",
+ array_merge( $pending['values'], array( $size ) )
)
);
@@ -191,13 +247,15 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
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 ) {
+ // The cursor steps past an order that could not be rebuilt, so record it as a failed
+ // analytics import, which Analytics settings offers to retry.
+ if ( true !== $synced ) {
+ $reason = -1 === $synced
+ ? 'The order could not be read (which is what a deactivated order type plugin looks like) or has no creation date to report it by.'
+ : 'The write did not land.';
+
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.",
+ "Could not rebuild the analytics tax lookup rows of order {$order_id}. {$reason} 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' )
);
@@ -207,11 +265,49 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
// 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 );
+ $this->advance_cursors( array_map( 'absint', $batch ) );
ReportsCache::invalidate();
}
+ /**
+ * Step every pass past the orders the batch covered.
+ *
+ * A pass never moves back, and stops short of any order it still has to rebuild that the batch
+ * left out. That happens when the pass came on offer while the batch was in flight.
+ *
+ * @param non-empty-array<int> $batch Order ids of the batch.
+ */
+ private function advance_cursors( array $batch ): void {
+ global $wpdb;
+
+ $table_name = TaxesDataStore::get_db_table_name();
+ $last = max( $batch );
+ $placeholders = implode( ', ', array_fill( 0, count( $batch ), '%d' ) );
+
+ foreach ( $this->get_pending_passes() as $pass ) {
+ $cursor = $this->get_cursor( $pass['cursor'] );
+
+ if ( $cursor >= $last ) {
+ continue;
+ }
+
+ $skipped = $wpdb->get_var(
+ $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The values come as one array.
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT MIN(order_id) FROM {$table_name} WHERE order_id > %d AND order_id < %d AND order_id NOT IN ( {$placeholders} ) AND ( {$pass['condition']} )",
+ array_merge( array( $cursor, $last ), $batch )
+ )
+ );
+
+ if ( $wpdb->last_error ) {
+ continue;
+ }
+
+ update_option( $pass['cursor'], null === $skipped ? $last : (int) $skipped - 1, false );
+ }
+ }
+
/**
* Default (preferred) batch size to pass to 'get_next_batch_to_process'.
*
@@ -240,7 +336,7 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
'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' ),
+ 'desc' => __( 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept the full tax detail it reports today. 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;
@@ -261,7 +357,7 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
'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' ),
+ 'desc' => __( 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept the full tax detail it reports today. There are currently no orders to rebuild.', 'woocommerce' ),
);
} elseif ( $batch_processor->is_enqueued( self::class ) ) {
$tools['stop_rebuild_analytics_tax_data'] = array(
@@ -271,8 +367,8 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
'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.',
+ 'This will stop the background process that rebuilds the Analytics tax data of orders recorded before WooCommerce kept the full tax detail it reports today. 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 the full tax detail it reports today. There are currently %s orders left to rebuild.',
$pending_count,
'woocommerce'
),
@@ -288,8 +384,8 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
'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.',
+ 'This will rebuild the Analytics tax data of orders recorded before WooCommerce kept the full tax detail it reports today. 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 the full tax detail it reports today. The rebuild happens over time in the background (via Action Scheduler). There are currently %s orders to rebuild.',
$pending_count,
'woocommerce'
),
@@ -362,11 +458,12 @@ class OrderTaxLookupMigrator implements BatchProcessorInterface, RegisterHooksIn
}
/**
- * Highest order id the processor has been through.
+ * Highest order id a pass has been through.
*
+ * @param string $option Cursor option of the pass.
* @return int
*/
- private function get_cursor(): int {
- return (int) get_option( self::CURSOR_OPTION, 0 );
+ private function get_cursor( string $option ): int {
+ return (int) get_option( $option, 0 );
}
}
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 69cc2b79198..ecb6e76d726 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
@@ -385,6 +385,8 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
'order_tax',
'shipping_tax',
'taxable_amount',
+ 'order_taxable_amount',
+ 'shipping_taxable_amount',
'orders_count',
);
@@ -406,7 +408,7 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
$data = $response->get_data();
$properties = $data['schema']['properties'];
- $this->assertEquals( 11, count( $properties ) );
+ $this->assertEquals( 13, count( $properties ) );
$this->assertArrayHasKey( 'tax_rate_id', $properties );
$this->assertArrayHasKey( 'name', $properties );
$this->assertArrayHasKey( 'tax_rate', $properties );
@@ -417,6 +419,8 @@ class WC_Admin_Tests_API_Reports_Taxes extends WC_REST_Unit_Test_Case {
$this->assertArrayHasKey( 'order_tax', $properties );
$this->assertArrayHasKey( 'shipping_tax', $properties );
$this->assertArrayHasKey( 'taxable_amount', $properties );
+ $this->assertArrayHasKey( 'order_taxable_amount', $properties );
+ $this->assertArrayHasKey( 'shipping_taxable_amount', $properties );
$this->assertArrayHasKey( 'orders_count', $properties );
}
diff --git a/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/ControllerTest.php b/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/ControllerTest.php
index 8c7b2449d82..2edf7b8fa0d 100644
--- a/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/ControllerTest.php
+++ b/plugins/woocommerce/tests/php/src/Admin/API/Reports/Taxes/ControllerTest.php
@@ -147,6 +147,48 @@ class ControllerTest extends WC_Unit_Test_Case {
$this->assertSame( Controller::csv_number_format( 1000.5 ), $export_item['taxable_amount'], 'A recorded taxable amount should export as a formatted number.' );
}
+ /**
+ * @testdox prepare_item_for_export formats each part of the taxable amount against the tax charged on it.
+ */
+ public function test_prepare_item_for_export_formats_taxable_amount_parts(): void {
+ $item = array(
+ 'tax_rate_id' => 1,
+ 'country' => 'US',
+ 'state' => 'CA',
+ 'name' => 'State Tax',
+ 'priority' => 1,
+ 'tax_rate' => '8.25',
+ 'total_tax' => 82.50,
+ 'order_tax' => 75.00,
+ 'shipping_tax' => 7.50,
+ 'orders_count' => 10,
+ );
+
+ // Keys absent (columns missing during the upgrade window): empty cells.
+ $export_item = $this->sut->prepare_item_for_export( $item );
+ $this->assertSame( '', $export_item['order_taxable_amount'], 'A missing order part should export as an empty cell.' );
+ $this->assertSame( '', $export_item['shipping_taxable_amount'], 'A missing shipping part should export as an empty cell.' );
+
+ // Zero parts under non-zero taxes (row recorded before the split existed): empty cells.
+ $item['order_taxable_amount'] = 0;
+ $item['shipping_taxable_amount'] = 0;
+ $export_item = $this->sut->prepare_item_for_export( $item );
+ $this->assertSame( '', $export_item['order_taxable_amount'], 'An unknown order part should export as an empty cell, not a zero.' );
+ $this->assertSame( '', $export_item['shipping_taxable_amount'], 'An unknown shipping part should export as an empty cell, not a zero.' );
+
+ // A rate that applied to no shipping charged no shipping tax, so its zero part is known.
+ $item['shipping_tax'] = 0;
+ $export_item = $this->sut->prepare_item_for_export( $item );
+ $this->assertSame( Controller::csv_number_format( 0 ), $export_item['shipping_taxable_amount'], 'A rate that charged no shipping tax should export a zero shipping part.' );
+
+ // Known values: formatted numbers.
+ $item['order_taxable_amount'] = 900.25;
+ $item['shipping_taxable_amount'] = 100.25;
+ $export_item = $this->sut->prepare_item_for_export( $item );
+ $this->assertSame( Controller::csv_number_format( 900.25 ), $export_item['order_taxable_amount'], 'A recorded order part should export as a formatted number.' );
+ $this->assertSame( Controller::csv_number_format( 100.25 ), $export_item['shipping_taxable_amount'], 'A recorded shipping part should export as a formatted number.' );
+ }
+
/**
* @testdox prepare_item_for_export passes the original item to the filter.
*/
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 c0a259c975d..c85e1f478fe 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
@@ -503,6 +503,280 @@ class DataStoreTest extends WC_Unit_Test_Case {
$this->assertSame( 215.0, $taxes_data->data[0]['taxable_amount'], 'The Taxes report row should expose the taxable amount.' );
}
+ /**
+ * @testdox Syncing an order splits the taxable amount into its order and shipping parts, and the reports expose both.
+ */
+ public function test_sync_order_taxes_splits_taxable_amount(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $order = $this->create_taxed_de_order();
+
+ OrdersStatsDataStore::sync_order( $order->get_id() );
+ DataStore::sync_order_taxes( $order->get_id() );
+ ReportsCache::invalidate();
+
+ // 2 x 100 product + 10 fee on the order side, 5 shipping on the shipping side.
+ $lookup_row = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT order_taxable_amount, shipping_taxable_amount FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id = %d AND tax_rate_id = %d",
+ $order->get_id(),
+ $rate_id
+ )
+ );
+ $this->assertSame( 210.0, (float) $lookup_row->order_taxable_amount, 'The order part should hold the net total of the line items and the fee.' );
+ $this->assertSame( 5.0, (float) $lookup_row->shipping_taxable_amount, 'The shipping part should hold the net shipping total.' );
+
+ $after = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
+ $before = gmdate( 'Y-m-d H:i:s', time() + DAY_IN_SECONDS );
+
+ $taxes_data = ( new DataStore() )->get_data( $this->taxes_query( $after, $before, $rate_id ) );
+ $this->assertSame( 210.0, $taxes_data->data[0]['order_taxable_amount'], 'The Taxes report row should expose the order part.' );
+ $this->assertSame( 5.0, $taxes_data->data[0]['shipping_taxable_amount'], 'The Taxes report row should expose the shipping part.' );
+ $this->assertSame(
+ $taxes_data->data[0]['taxable_amount'],
+ $taxes_data->data[0]['order_taxable_amount'] + $taxes_data->data[0]['shipping_taxable_amount'],
+ 'The parts should add up to the taxable amount.'
+ );
+ }
+
+ /**
+ * @testdox A rate holding one row the rebuild has not reached reports no split at all, rather than the sum of the rows it has.
+ */
+ public function test_report_hides_the_split_of_a_rate_holding_an_unrebuilt_row(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $rebuilt = $this->create_taxed_de_order();
+ $unsplit = $this->create_taxed_de_order();
+
+ foreach ( array( $rebuilt, $unsplit ) as $order ) {
+ OrdersStatsDataStore::sync_order( $order->get_id() );
+ DataStore::sync_order_taxes( $order->get_id() );
+ }
+
+ // The shape a row carries until the rebuild reaches it: a base, with neither part of it.
+ $wpdb->query(
+ $wpdb->prepare(
+ "UPDATE {$wpdb->prefix}wc_order_tax_lookup SET order_taxable_amount = NULL, shipping_taxable_amount = NULL WHERE order_id = %d",
+ $unsplit->get_id()
+ )
+ );
+ ReportsCache::invalidate();
+
+ $after = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
+ $before = gmdate( 'Y-m-d H:i:s', time() + DAY_IN_SECONDS );
+ $row = ( new DataStore() )->get_data( $this->taxes_query( $after, $before, $rate_id ) )->data[0];
+
+ $this->assertSame( 430.0, $row['taxable_amount'], 'The taxable amount is recorded on both rows, so it stays whole.' );
+ $this->assertArrayNotHasKey( 'order_taxable_amount', $row, 'The order part should be left out while a row of the rate holds no split.' );
+ $this->assertArrayNotHasKey( 'shipping_taxable_amount', $row, 'The shipping part should be left out while a row of the rate holds no split.' );
+ }
+
+ /**
+ * @testdox A rate whose unrebuilt row has a base netting to zero reports no split, rather than two zero parts.
+ */
+ public function test_report_hides_the_split_of_an_unrebuilt_row_netting_to_zero(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $order = $this->create_taxed_de_order();
+
+ OrdersStatsDataStore::sync_order( $order->get_id() );
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ // A negative fee offsetting shipping at the same rate leaves a zero base before the split.
+ $wpdb->query(
+ $wpdb->prepare(
+ "UPDATE {$wpdb->prefix}wc_order_tax_lookup SET taxable_amount = 0, order_taxable_amount = NULL, shipping_taxable_amount = NULL WHERE order_id = %d",
+ $order->get_id()
+ )
+ );
+ ReportsCache::invalidate();
+
+ $after = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
+ $before = gmdate( 'Y-m-d H:i:s', time() + DAY_IN_SECONDS );
+ $row = ( new DataStore() )->get_data( $this->taxes_query( $after, $before, $rate_id ) )->data[0];
+
+ $this->assertArrayNotHasKey( 'order_taxable_amount', $row, 'The order part should be left out while the row holds no split.' );
+ $this->assertArrayNotHasKey( 'shipping_taxable_amount', $row, 'The shipping part should be left out while the row holds no split.' );
+ }
+
+ /**
+ * @testdox A zero-rated rate whose row the rebuild has not reached reports no split, which its tax cannot say.
+ */
+ public function test_report_hides_the_split_of_an_unrebuilt_zero_rated_rate(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate( '0' );
+ $order = $this->create_taxed_de_order();
+
+ OrdersStatsDataStore::sync_order( $order->get_id() );
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $wpdb->query(
+ $wpdb->prepare(
+ "UPDATE {$wpdb->prefix}wc_order_tax_lookup SET order_taxable_amount = NULL, shipping_taxable_amount = NULL WHERE order_id = %d",
+ $order->get_id()
+ )
+ );
+ ReportsCache::invalidate();
+
+ $after = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
+ $before = gmdate( 'Y-m-d H:i:s', time() + DAY_IN_SECONDS );
+ $row = ( new DataStore() )->get_data( $this->taxes_query( $after, $before, $rate_id ) )->data[0];
+
+ $this->assertSame( 0.0, $row['total_tax'], 'The rate should charge no tax.' );
+ $this->assertSame( 215.0, $row['taxable_amount'], 'A zero-rated sale still records the base it was taxed on.' );
+ $this->assertArrayNotHasKey( 'order_taxable_amount', $row, 'The order part should be left out while the row holds no split.' );
+ $this->assertArrayNotHasKey( 'shipping_taxable_amount', $row, 'The shipping part should be left out while the row holds no split.' );
+ }
+
+ /**
+ * @testdox A rate applied only to shipping records the whole base on the shipping side.
+ */
+ public function test_sync_order_taxes_records_shipping_only_taxable_amount(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $order = $this->create_taxed_de_order();
+
+ // An admin save without recalculating stores '' for rates that never applied to the
+ // item, which is the shape a shipping-only rate leaves on the products and the fee.
+ foreach ( $order->get_items( array( OrderItemType::LINE_ITEM, OrderItemType::FEE ) ) as $item ) {
+ $taxes = $item->get_taxes();
+ $taxes['total'][ $rate_id ] = '';
+ if ( isset( $taxes['subtotal'] ) ) {
+ $taxes['subtotal'][ $rate_id ] = '';
+ }
+ $item->set_taxes( $taxes );
+ $item->save();
+ }
+ $order->update_taxes();
+ $order->save();
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $lookup_row = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT order_taxable_amount, shipping_taxable_amount FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id = %d AND tax_rate_id = %d",
+ $order->get_id(),
+ $rate_id
+ )
+ );
+ $this->assertSame( 0.0, (float) $lookup_row->order_taxable_amount, 'A rate that applied to no line item or fee should record a zero order part.' );
+ $this->assertSame( 5.0, (float) $lookup_row->shipping_taxable_amount, 'The shipping part should still hold the net shipping total.' );
+ }
+
+ /**
+ * @testdox Each part of a compound rate's base includes the taxes compounded over on that same side.
+ */
+ public function test_sync_order_taxes_splits_compound_taxable_amount(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $base_rate_id = $this->insert_tax_rate( '5', 1 );
+ $compound_rate_id = $this->insert_tax_rate( '7', 2, 1 );
+ $order = $this->create_taxed_de_order();
+
+ DataStore::sync_order_taxes( $order->get_id() );
+
+ $amounts = $wpdb->get_results(
+ $wpdb->prepare(
+ "SELECT tax_rate_id, order_taxable_amount, shipping_taxable_amount FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id = %d",
+ $order->get_id()
+ ),
+ OBJECT_K
+ );
+
+ $this->assertSame( 210.0, (float) $amounts[ $base_rate_id ]->order_taxable_amount );
+ $this->assertSame( 5.0, (float) $amounts[ $base_rate_id ]->shipping_taxable_amount );
+
+ // The compound rate is applied on top of the 5% tax of each item, so the order part
+ // carries 210 + 10.50 and the shipping part 5 + 0.25.
+ $this->assertSame( 220.5, (float) $amounts[ $compound_rate_id ]->order_taxable_amount, 'The order part should compound over the order taxes only.' );
+ $this->assertSame( 5.25, (float) $amounts[ $compound_rate_id ]->shipping_taxable_amount, 'The shipping part should compound over the shipping taxes only.' );
+ }
+
+ /**
+ * @testdox A fully refunded order nets both parts of the taxable amount back to zero.
+ */
+ public function test_refunded_order_nets_taxable_amount_parts_to_zero(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $order = $this->create_taxed_de_order();
+ $refund = $this->refund_order_in_full( $order );
+
+ DataStore::sync_order_taxes( $order->get_id() );
+ DataStore::sync_order_taxes( $refund->get_id() );
+
+ $sums = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT SUM(order_taxable_amount) AS order_part, SUM(shipping_taxable_amount) AS shipping_part FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id IN (%d, %d) AND tax_rate_id = %d",
+ $order->get_id(),
+ $refund->get_id(),
+ $rate_id
+ )
+ );
+ $this->assertSame( 0.0, (float) $sums->order_part, 'A full refund should net the order part back to zero.' );
+ $this->assertSame( 0.0, (float) $sums->shipping_part, 'A full refund should net the shipping part back to zero.' );
+ }
+
+ /**
+ * @testdox While the split columns are missing, syncing still writes rows and the report still returns data.
+ */
+ public function test_guards_apply_while_taxable_amount_split_columns_are_missing(): void {
+ global $wpdb;
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $rate_id = $this->insert_tax_rate();
+ $order = $this->create_taxed_de_order();
+
+ // Force the columns-missing code paths through the static:: seam instead of
+ // dropping the real columns, which would break test transaction isolation.
+ $sut = new class() extends DataStore {
+ /**
+ * Report the taxable amount split columns as missing.
+ *
+ * @return bool
+ */
+ public static function has_taxable_amount_split_columns() {
+ return false;
+ }
+ };
+ $sut_class = get_class( $sut );
+
+ $sut_class::sync_order_taxes( $order->get_id() );
+ OrdersStatsDataStore::sync_order( $order->get_id() );
+ ReportsCache::invalidate();
+
+ $lookup_row = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT total_tax, taxable_amount FROM {$wpdb->prefix}wc_order_tax_lookup WHERE order_id = %d AND tax_rate_id = %d",
+ $order->get_id(),
+ $rate_id
+ )
+ );
+ $this->assertNotNull( $lookup_row, 'The sync must still write lookup rows while the columns are missing.' );
+ $this->assertSame( 215.0, (float) $lookup_row->taxable_amount, 'The taxable amount must still be recorded.' );
+
+ $after = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
+ $before = gmdate( 'Y-m-d H:i:s', time() + DAY_IN_SECONDS );
+ $query = $this->taxes_query( $after, $before, $rate_id );
+
+ $data = $sut->get_data( array_merge( $query, array( 'orderby' => 'order_taxable_amount' ) ) );
+ $this->assertCount( 1, $data->data, 'Ordering by a missing column must fall back instead of erroring into an empty report.' );
+ $this->assertArrayNotHasKey( 'order_taxable_amount', $data->data[0], 'The report must omit the columns it cannot select.' );
+ $this->assertArrayNotHasKey( 'shipping_taxable_amount', $data->data[0], 'The report must omit the columns it cannot select.' );
+ }
+
/**
* @testdox Syncing an order records the taxable amount for a zero-rated tax rate.
*/
diff --git a/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php b/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php
index 486ce3aa1de..2ece65e7fa3 100644
--- a/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/Admin/OrderTaxLookupMigratorTest.php
@@ -3,6 +3,7 @@ declare( strict_types = 1 );
namespace Automattic\WooCommerce\Tests\Internal\Admin;
+use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
use Automattic\WooCommerce\Admin\API\Reports\Taxes\DataStore as TaxesDataStore;
use Automattic\WooCommerce\Enums\OrderItemType;
use Automattic\WooCommerce\Enums\OrderStatus;
@@ -49,6 +50,7 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
WC_Helper_Reports::reset_stats_dbs();
delete_option( OrderTaxLookupMigrator::CURSOR_OPTION );
+ delete_option( OrderTaxLookupMigrator::SPLIT_CURSOR_OPTION );
delete_option( OrdersScheduler::FAILED_ORDER_IMPORTS_OPTION );
$this->sut = wc_get_container()->get( OrderTaxLookupMigrator::class );
@@ -60,6 +62,7 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
public function tearDown(): void {
update_option( 'woocommerce_calc_taxes', $this->original_calc_taxes );
delete_option( OrderTaxLookupMigrator::CURSOR_OPTION );
+ delete_option( OrderTaxLookupMigrator::SPLIT_CURSOR_OPTION );
delete_option( OrdersScheduler::FAILED_ORDER_IMPORTS_OPTION );
wc_get_container()->get( BatchProcessingController::class )->remove_processor( OrderTaxLookupMigrator::class );
@@ -106,6 +109,63 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
return $order;
}
+ /**
+ * Create a completed DE order with two product units, a fee and shipping, all taxed at one
+ * registered rate, and let the analytics sync record it.
+ *
+ * @param string $fee_total Fee total, negative for a discount.
+ * @return WC_Order
+ */
+ private function seed_taxed_order( string $fee_total = '10' ): WC_Order {
+ update_option( 'woocommerce_tax_based_on', 'billing' );
+ update_option( 'woocommerce_shipping_tax_class', '' );
+
+ \WC_Tax::_insert_tax_rate(
+ array(
+ 'tax_rate_country' => 'DE',
+ 'tax_rate_state' => '',
+ 'tax_rate' => '19',
+ 'tax_rate_name' => 'VAT',
+ 'tax_rate_priority' => 1,
+ 'tax_rate_compound' => 0,
+ 'tax_rate_shipping' => 1,
+ 'tax_rate_order' => 0,
+ 'tax_rate_class' => '',
+ )
+ );
+
+ $product = new WC_Product_Simple();
+ $product->set_name( 'Taxable Product' );
+ $product->set_regular_price( '100' );
+ $product->save();
+
+ $order = wc_create_order();
+ $order->set_billing_country( 'DE' );
+ $order->set_shipping_country( 'DE' );
+ $order->add_product( $product, 2 );
+
+ $fee = new \WC_Order_Item_Fee();
+ $fee->set_name( 'Handling' );
+ $fee->set_total( $fee_total );
+ $fee->set_tax_status( 'taxable' );
+ $order->add_item( $fee );
+
+ $shipping = new \WC_Order_Item_Shipping();
+ $shipping->set_method_title( 'Flat rate' );
+ $shipping->set_method_id( 'flat_rate' );
+ $shipping->set_total( '5' );
+ $order->add_item( $shipping );
+
+ $order->calculate_totals();
+ $order->set_status( OrderStatus::COMPLETED );
+ $order->set_date_paid( time() );
+ $order->save();
+
+ 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.
*
@@ -154,6 +214,28 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
);
}
+ /**
+ * Clear the taxable amount split of an order's lookup rows and set the base they add up to,
+ * the shape the table held before it recorded the order and shipping parts.
+ *
+ * @param int $order_id Order id.
+ * @param float $taxable_amount Base the rows carry.
+ */
+ private function unsplit_lookup_rows( int $order_id, float $taxable_amount ): void {
+ global $wpdb;
+
+ $table_name = TaxesDataStore::get_db_table_name();
+
+ $wpdb->query(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "UPDATE {$table_name} SET taxable_amount = %f, order_taxable_amount = NULL, shipping_taxable_amount = NULL WHERE order_id = %d",
+ $taxable_amount,
+ $order_id
+ )
+ );
+ }
+
/**
* Read an order's lookup rows.
*
@@ -201,6 +283,45 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
$this->assertNotEmpty( $this->lookup_rows( $migrated->get_id() ), 'The rebuilt order should be left alone.' );
}
+ /**
+ * @testdox An order holding no order and shipping split is pending, one holding a split of zero is not.
+ */
+ public function test_orders_holding_an_unsplit_taxable_amount_are_pending(): void {
+ $unsplit = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+
+ $this->unsplit_lookup_rows( $unsplit->get_id(), 215.0 );
+
+ $this->assertSame( 1, $this->sut->get_total_pending_count(), 'Only the order holding no split should be pending.' );
+ $this->assertSame( array( $unsplit->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'The batch should hold only that order.' );
+ }
+
+ /**
+ * @testdox Processing a batch records the order and shipping parts of the taxable amount.
+ */
+ public function test_process_batch_records_the_taxable_amount_split(): void {
+ global $wpdb;
+
+ $order = $this->seed_taxed_order();
+ $this->unsplit_lookup_rows( $order->get_id(), 215.0 );
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $table_name = TaxesDataStore::get_db_table_name();
+ $sums = $wpdb->get_row(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT SUM(order_taxable_amount) AS order_part, SUM(shipping_taxable_amount) AS shipping_part FROM {$table_name} WHERE order_id = %d",
+ $order->get_id()
+ )
+ );
+
+ // 2 x 100 product + 10 fee on the order side, 5 shipping on the shipping side.
+ $this->assertSame( 210.0, (float) $sums->order_part, 'The rebuild should record the order part.' );
+ $this->assertSame( 5.0, (float) $sums->shipping_part, 'The rebuild should record the shipping part.' );
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'A rebuilt order should stop being pending.' );
+ }
+
/**
* @testdox Processing a batch gives every tax line of the order its own lookup row.
*/
@@ -240,6 +361,7 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
$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.' );
+ $this->assertSame( array(), OrdersScheduler::get_failed_order_imports()['ids'], 'An order whose rows were dropped has nothing left to retry, so it should not be recorded as a failed import.' );
$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.' );
@@ -269,6 +391,10 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
$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.' );
+
+ $failed = OrdersScheduler::get_failed_order_imports();
+
+ $this->assertSame( array( $order->get_id() ), $failed['ids'], 'The cursor steps past an order that could not be read, so it should be left where Analytics settings offers a retry over it. A row left behind reports no taxable amount split for its whole rate.' );
}
/**
@@ -395,6 +521,7 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
'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.' );
+ $this->assertSame( array( $order->get_id() ), OrdersScheduler::get_failed_order_imports()['ids'], 'An order the rebuild kept but could not rewrite should be left where Analytics settings offers a retry over it.' );
}
/**
@@ -435,6 +562,7 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
$this->unmigrate_lookup_rows( $left->get_id(), 0, 0.25 );
update_option( OrderTaxLookupMigrator::CURSOR_OPTION, $stepped_past->get_id() );
+ update_option( OrderTaxLookupMigrator::SPLIT_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.' );
@@ -478,6 +606,91 @@ class OrderTaxLookupMigratorTest extends WC_Unit_Test_Case {
$this->assertTrue( $batch_processor->is_enqueued( OrderTaxLookupMigrator::class ), 'The database update should hand the rebuild to the batch processing controller.' );
}
+ /**
+ * @testdox The taxable amount split update runs the rebuild over the whole table again, on a cursor of its own.
+ */
+ public function test_taxable_amount_split_update_restarts_the_rebuild(): void {
+ $batch_processor = wc_get_container()->get( BatchProcessingController::class );
+ $batch_processor->remove_processor( OrderTaxLookupMigrator::class );
+
+ // An order the earlier pass has already been through, holding a base with no split.
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unsplit_lookup_rows( $order->get_id(), 215.0 );
+ update_option( OrderTaxLookupMigrator::CURSOR_OPTION, $order->get_id() + 1 );
+
+ $cache_version = ReportsCache::get_version();
+
+ wc_update_1130_split_tax_lookup_taxable_amount();
+
+ $this->assertSame( $order->get_id() + 1, (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The update should leave the cursor of the earlier pass alone.' );
+ $this->assertFalse( get_option( OrderTaxLookupMigrator::SPLIT_CURSOR_OPTION ), 'The split pass should start at the top of the table.' );
+ $this->assertSame( array( $order->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'The split pass should reach an order the earlier pass has stepped past.' );
+ $this->assertTrue( $batch_processor->is_enqueued( OrderTaxLookupMigrator::class ), 'The update should hand the rebuild to the batch processing controller.' );
+ $this->assertNotSame( $cache_version, ReportsCache::get_version(), 'The update should invalidate the cached report responses, which last a week.' );
+ }
+
+ /**
+ * @testdox The split pass rebuilds an order whose base nets to zero.
+ */
+ public function test_split_pass_rebuilds_a_base_that_nets_to_zero(): void {
+ global $wpdb;
+
+ // A negative fee takes the order part to -5, which offsets the 5 of shipping.
+ $order = $this->seed_taxed_order( '-205' );
+ $this->unsplit_lookup_rows( $order->get_id(), 0.0 );
+
+ $this->assertSame( array( $order->get_id() ), $this->sut->get_next_batch_to_process( 10 ), 'An order holding no split should be pending, whatever its base.' );
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $table_name = TaxesDataStore::get_db_table_name();
+ $sums = $wpdb->get_row(
+ $wpdb->prepare(
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
+ "SELECT SUM(order_taxable_amount) AS order_part, SUM(shipping_taxable_amount) AS shipping_part FROM {$table_name} WHERE order_id = %d",
+ $order->get_id()
+ )
+ );
+
+ $this->assertSame( -5.0, (float) $sums->order_part, 'The rebuild should record the order part.' );
+ $this->assertSame( 5.0, (float) $sums->shipping_part, 'The rebuild should record the shipping part.' );
+ $this->assertSame( 0, $this->sut->get_total_pending_count(), 'Nothing should be left pending once the pass is through.' );
+ }
+
+ /**
+ * @testdox A batch steps each pass past the orders it covered, and leaves a pass that is already further along.
+ */
+ public function test_each_pass_keeps_its_own_cursor(): void {
+ $order = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unsplit_lookup_rows( $order->get_id(), 215.0 );
+
+ $stepped_past = $order->get_id() + 1000;
+ update_option( OrderTaxLookupMigrator::CURSOR_OPTION, $stepped_past );
+
+ $this->sut->process_batch( array( $order->get_id() ) );
+
+ $this->assertSame( $order->get_id(), (int) get_option( OrderTaxLookupMigrator::SPLIT_CURSOR_OPTION ), 'The split pass should step past the order it went through.' );
+ $this->assertSame( $stepped_past, (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'A pass already further along should not be rewound to the batch.' );
+ }
+
+ /**
+ * @testdox A batch read before the split columns existed does not step the split pass past the orders it left out.
+ */
+ public function test_batch_in_flight_does_not_step_a_new_pass_past_orders_it_left_out(): void {
+ $left_out = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unsplit_lookup_rows( $left_out->get_id(), 215.0 );
+
+ $in_batch = $this->seed_order_with_tax_lines( $this->tax_lines_sharing_a_rate_id() );
+ $this->unmigrate_lookup_rows( $in_batch->get_id(), 0, 0.25 );
+
+ // The batch the order item pass alone would have read, before the split columns existed.
+ $this->sut->process_batch( array( $in_batch->get_id() ) );
+
+ $this->assertSame( $in_batch->get_id(), (int) get_option( OrderTaxLookupMigrator::CURSOR_OPTION ), 'The pass the batch was read for should step past it.' );
+ $this->assertSame( $left_out->get_id() - 1, (int) get_option( OrderTaxLookupMigrator::SPLIT_CURSOR_OPTION ), 'The split pass should stop before the order the batch left out.' );
+ $this->assertContains( $left_out->get_id(), $this->sut->get_next_batch_to_process( 10 ), 'The split pass should still reach the order the batch left out.' );
+ }
+
/**
* @testdox The tool is disabled on a store with nothing to rebuild.
*/