<?php

[0] Fix | Delete

/**

[1] Fix | Delete

* API\Reports\Taxes\DataStore class file.

[2] Fix | Delete

*/

[3] Fix | Delete

[4] Fix | Delete

namespace Automattic\WooCommerce\Admin\API\Reports\Taxes;

[5] Fix | Delete

[6] Fix | Delete

defined( 'ABSPATH' ) || exit;

[7] Fix | Delete

[8] Fix | Delete

use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore;

[9] Fix | Delete

use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface;

[10] Fix | Delete

use Automattic\WooCommerce\Admin\API\Reports\TimeInterval;

[11] Fix | Delete

use Automattic\WooCommerce\Admin\API\Reports\SqlQuery;

[12] Fix | Delete

use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;

[13] Fix | Delete

[14] Fix | Delete

/**

[15] Fix | Delete

* API\Reports\Taxes\DataStore.

[16] Fix | Delete

*/

[17] Fix | Delete

class DataStore extends ReportsDataStore implements DataStoreInterface {

[18] Fix | Delete

[19] Fix | Delete

/**

[20] Fix | Delete

* Table used to get the data.

[21] Fix | Delete

*

[22] Fix | Delete

* @override ReportsDataStore::$table_name

[23] Fix | Delete

*

[24] Fix | Delete

* @var string

[25] Fix | Delete

*/

[26] Fix | Delete

protected static $table_name = 'wc_order_tax_lookup';

[27] Fix | Delete

[28] Fix | Delete

/**

[29] Fix | Delete

* Cache identifier.

[30] Fix | Delete

*

[31] Fix | Delete

* @override ReportsDataStore::$cache_key

[32] Fix | Delete

*

[33] Fix | Delete

* @var string

[34] Fix | Delete

*/

[35] Fix | Delete

protected $cache_key = 'taxes';

[36] Fix | Delete

[37] Fix | Delete

/**

[38] Fix | Delete

* Mapping columns to data type to return correct response types.

[39] Fix | Delete

*

[40] Fix | Delete

* @override ReportsDataStore::$column_types

[41] Fix | Delete

*

[42] Fix | Delete

* @var array

[43] Fix | Delete

*/

[44] Fix | Delete

protected $column_types = array(

[45] Fix | Delete

'tax_rate_id' => 'intval',

[46] Fix | Delete

'name' => 'strval',

[47] Fix | Delete

'tax_rate' => 'floatval',

[48] Fix | Delete

'country' => 'strval',

[49] Fix | Delete

'state' => 'strval',

[50] Fix | Delete

'priority' => 'intval',

[51] Fix | Delete

'total_tax' => 'floatval',

[52] Fix | Delete

'order_tax' => 'floatval',

[53] Fix | Delete

'shipping_tax' => 'floatval',

[54] Fix | Delete

'orders_count' => 'intval',

[55] Fix | Delete

);

[56] Fix | Delete

[57] Fix | Delete

/**

[58] Fix | Delete

* Data store context used to pass to filters.

[59] Fix | Delete

*

[60] Fix | Delete

* @override ReportsDataStore::$context

[61] Fix | Delete

*

[62] Fix | Delete

* @var string

[63] Fix | Delete

*/

[64] Fix | Delete

protected $context = 'taxes';

[65] Fix | Delete

[66] Fix | Delete

/**

[67] Fix | Delete

* Assign report columns once full table name has been assigned.

[68] Fix | Delete

*

[69] Fix | Delete

* @override ReportsDataStore::assign_report_columns()

[70] Fix | Delete

*/

[71] Fix | Delete

protected function assign_report_columns() {

[72] Fix | Delete

global $wpdb;

[73] Fix | Delete

$table_name = self::get_db_table_name();

[74] Fix | Delete

[75] Fix | Delete

// Using wp_woocommerce_tax_rates table limits the result to only the existing tax rates and

[76] Fix | Delete

// omits the historical records which differs from the purpose of wp_wc_order_tax_lookup table.

[77] Fix | Delete

// So in order to get the same data present in wp_woocommerce_tax_rates without breaking the

[78] Fix | Delete

// API contract the values are now retrieved from wp_woocommerce_order_items and wp_woocommerce_order_itemmeta.

[79] Fix | Delete

// And given that country, state and priority are not separate columns within the woocommerce_order_items,

[80] Fix | Delete

// a split to order_item_name column value is required to separate those values. This is not ideal,

[81] Fix | Delete

// but given this query is paginated and cached, then it is not a big deal. There is always room for

[82] Fix | Delete

// improvements here.

[83] Fix | Delete

$this->report_columns = array(

[84] Fix | Delete

'tax_rate_id' => "{$table_name}.tax_rate_id",

[85] Fix | Delete

'name' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-2), '-', 1) as name",

[86] Fix | Delete

'tax_rate' => 'CAST(itemmeta_rate_percent.meta_value AS DECIMAL(7,4)) as tax_rate',

[87] Fix | Delete

'country' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',1) as country",

[88] Fix | Delete

'state' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-3), '-', 1) as state",

[89] Fix | Delete

'priority' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-1) as priority",

[90] Fix | Delete

'total_tax' => 'SUM(total_tax) as total_tax',

[91] Fix | Delete

'order_tax' => 'SUM(order_tax) as order_tax',

[92] Fix | Delete

'shipping_tax' => 'SUM(shipping_tax) as shipping_tax',

[93] Fix | Delete

'orders_count' => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count",

[94] Fix | Delete

);

[95] Fix | Delete

}

[96] Fix | Delete

[97] Fix | Delete

/**

[98] Fix | Delete

* Set up all the hooks for maintaining and populating table data.

[99] Fix | Delete

*/

[100] Fix | Delete

public static function init() {

[101] Fix | Delete

add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 15 );

[102] Fix | Delete

}

[103] Fix | Delete

[104] Fix | Delete

/**

[105] Fix | Delete

* Fills FROM clause of SQL request based on user supplied parameters.

[106] Fix | Delete

*

[107] Fix | Delete

* @param array $query_args Query arguments supplied by the user.

[108] Fix | Delete

* @param string $order_status_filter Order status subquery.

[109] Fix | Delete

*/

[110] Fix | Delete

protected function add_from_sql_params( $query_args, $order_status_filter ) {

[111] Fix | Delete

global $wpdb;

[112] Fix | Delete

$table_name = self::get_db_table_name();

[113] Fix | Delete

[114] Fix | Delete

if ( $order_status_filter ) {

[115] Fix | Delete

$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id" );

[116] Fix | Delete

}

[117] Fix | Delete

[118] Fix | Delete

$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_items ON {$table_name}.order_id = {$wpdb->prefix}woocommerce_order_items.order_id AND {$wpdb->prefix}woocommerce_order_items.order_item_type = 'tax'" );

[119] Fix | Delete

$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_rate_id ON itemmeta_rate_id.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id AND itemmeta_rate_id.meta_key = 'rate_id'" );

[120] Fix | Delete

$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_rate_percent ON itemmeta_rate_percent.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id AND itemmeta_rate_percent.meta_key = 'rate_percent'" );

[121] Fix | Delete

}

[122] Fix | Delete

[123] Fix | Delete

/**

[124] Fix | Delete

* Updates the database query with parameters used for Taxes report: categories and order status.

[125] Fix | Delete

*

[126] Fix | Delete

* @see Automattic\WooCommerce\Admin\API\Reports\Taxes\Stats\DataStore::update_sql_query_params()

[127] Fix | Delete

* @param array $query_args Query arguments supplied by the user.

[128] Fix | Delete

*/

[129] Fix | Delete

protected function add_sql_query_params( $query_args ) {

[130] Fix | Delete

global $wpdb;

[131] Fix | Delete

[132] Fix | Delete

$order_tax_lookup_table = self::get_db_table_name();

[133] Fix | Delete

[134] Fix | Delete

$this->add_time_period_sql_params( $query_args, $order_tax_lookup_table );

[135] Fix | Delete

$this->get_limit_sql_params( $query_args );

[136] Fix | Delete

$this->add_order_by_sql_params( $query_args );

[137] Fix | Delete

$order_status_filter = $this->get_status_subquery( $query_args );

[138] Fix | Delete

$this->add_from_sql_params( $query_args, $order_status_filter );

[139] Fix | Delete

[140] Fix | Delete

$this->subquery->add_sql_clause( 'where', "AND itemmeta_rate_id.meta_value = {$order_tax_lookup_table}.tax_rate_id" );

[141] Fix | Delete

if ( isset( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) {

[142] Fix | Delete

$allowed_taxes = self::get_filtered_ids( $query_args, 'taxes' );

[143] Fix | Delete

$this->subquery->add_sql_clause( 'where', "AND {$order_tax_lookup_table}.tax_rate_id IN ({$allowed_taxes})" );

[144] Fix | Delete

}

[145] Fix | Delete

[146] Fix | Delete

if ( $order_status_filter ) {

[147] Fix | Delete

$this->subquery->add_sql_clause( 'where', "AND ( {$order_status_filter} )" );

[148] Fix | Delete

}

[149] Fix | Delete

}

[150] Fix | Delete

[151] Fix | Delete

/**

[152] Fix | Delete

* Get the default query arguments to be used by get_data().

[153] Fix | Delete

* These defaults are only partially applied when used via REST API, as that has its own defaults.

[154] Fix | Delete

*

[155] Fix | Delete

* @override ReportsDataStore::get_default_query_vars()

[156] Fix | Delete

*

[157] Fix | Delete

* @return array Query parameters.

[158] Fix | Delete

*/

[159] Fix | Delete

public function get_default_query_vars() {

[160] Fix | Delete

$defaults = parent::get_default_query_vars();

[161] Fix | Delete

$defaults['orderby'] = 'tax_rate_id';

[162] Fix | Delete

$defaults['taxes'] = array();

[163] Fix | Delete

[164] Fix | Delete

return $defaults;

[165] Fix | Delete

}

[166] Fix | Delete

[167] Fix | Delete

/**

[168] Fix | Delete

* Returns the report data based on normalized parameters.

[169] Fix | Delete

* Will be called by `get_data` if there is no data in cache.

[170] Fix | Delete

*

[171] Fix | Delete

* @override ReportsDataStore::get_noncached_data()

[172] Fix | Delete

*

[173] Fix | Delete

* @see get_data

[174] Fix | Delete

* @param array $query_args Query parameters.

[175] Fix | Delete

* @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error.

[176] Fix | Delete

*/

[177] Fix | Delete

public function get_noncached_data( $query_args ) {

[178] Fix | Delete

global $wpdb;

[179] Fix | Delete

[180] Fix | Delete

$this->initialize_queries();

[181] Fix | Delete

[182] Fix | Delete

$data = (object) array(

[183] Fix | Delete

'data' => array(),

[184] Fix | Delete

'total' => 0,

[185] Fix | Delete

'pages' => 0,

[186] Fix | Delete

'page_no' => 0,

[187] Fix | Delete

);

[188] Fix | Delete

[189] Fix | Delete

$this->add_sql_query_params( $query_args );

[190] Fix | Delete

$params = $this->get_limit_params( $query_args );

[191] Fix | Delete

[192] Fix | Delete

if ( isset( $query_args['taxes'] ) && is_array( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) {

[193] Fix | Delete

$total_results = count( $query_args['taxes'] );

[194] Fix | Delete

$total_pages = (int) ceil( $total_results / $params['per_page'] );

[195] Fix | Delete

} else {

[196] Fix | Delete

$db_records_count = (int) $wpdb->get_var(

[197] Fix | Delete

// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- cache ok, DB call ok, unprepared SQL ok.

[198] Fix | Delete

"SELECT COUNT(*) FROM ( {$this->subquery->get_query_statement()} ) AS tt"

[199] Fix | Delete

);

[200] Fix | Delete

[201] Fix | Delete

$total_results = $db_records_count;

[202] Fix | Delete

$total_pages = (int) ceil( $db_records_count / $params['per_page'] );

[203] Fix | Delete

[204] Fix | Delete

if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) {

[205] Fix | Delete

return $data;

[206] Fix | Delete

}

[207] Fix | Delete

}

[208] Fix | Delete

[209] Fix | Delete

$this->subquery->clear_sql_clause( 'select' );

[210] Fix | Delete

$this->subquery->add_sql_clause( 'select', $this->selected_columns( $query_args ) );

[211] Fix | Delete

if ( in_array( $query_args['orderby'], array( 'total_tax', 'order_tax', 'shipping_tax', 'orders_count' ), true ) ) {

[212] Fix | Delete

$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', tax_rate_id' );

[213] Fix | Delete

} else {

[214] Fix | Delete

$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) );

[215] Fix | Delete

}

[216] Fix | Delete

$this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) );

[217] Fix | Delete

[218] Fix | Delete

$taxes_query = $this->subquery->get_query_statement();

[219] Fix | Delete

[220] Fix | Delete

$tax_data = $wpdb->get_results(

[221] Fix | Delete

// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok.

[222] Fix | Delete

$taxes_query,

[223] Fix | Delete

ARRAY_A

[224] Fix | Delete

);

[225] Fix | Delete

[226] Fix | Delete

if ( null === $tax_data ) {

[227] Fix | Delete

return $data;

[228] Fix | Delete

}

[229] Fix | Delete

[230] Fix | Delete

$tax_data = array_map( array( $this, 'cast_numbers' ), $tax_data );

[231] Fix | Delete

$data = (object) array(

[232] Fix | Delete

'data' => $tax_data,

[233] Fix | Delete

'total' => $total_results,

[234] Fix | Delete

'pages' => $total_pages,

[235] Fix | Delete

'page_no' => (int) $query_args['page'],

[236] Fix | Delete

);

[237] Fix | Delete

[238] Fix | Delete

return $data;

[239] Fix | Delete

}

[240] Fix | Delete

[241] Fix | Delete

/**

[242] Fix | Delete

* Maps ordering specified by the user to columns in the database/fields in the data.

[243] Fix | Delete

*

[244] Fix | Delete

* @override ReportsDataStore::normalize_order_by()

[245] Fix | Delete

*

[246] Fix | Delete

* @param string $order_by Sorting criterion.

[247] Fix | Delete

* @return string

[248] Fix | Delete

*/

[249] Fix | Delete

protected function normalize_order_by( $order_by ) {

[250] Fix | Delete

global $wpdb;

[251] Fix | Delete

[252] Fix | Delete

if ( 'tax_code' === $order_by ) {

[253] Fix | Delete

return "{$wpdb->prefix}woocommerce_order_items.order_item_name";

[254] Fix | Delete

} elseif ( 'rate' === $order_by ) {

[255] Fix | Delete

return 'tax_rate';

[256] Fix | Delete

}

[257] Fix | Delete

[258] Fix | Delete

return $order_by;

[259] Fix | Delete

}

[260] Fix | Delete

[261] Fix | Delete

/**

[262] Fix | Delete

* Create or update an entry in the wc_order_tax_lookup table for an order.

[263] Fix | Delete

*

[264] Fix | Delete

* @param int $order_id Order ID.

[265] Fix | Delete

* @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success.

[266] Fix | Delete

*/

[267] Fix | Delete

public static function sync_order_taxes( $order_id ) {

[268] Fix | Delete

global $wpdb;

[269] Fix | Delete

[270] Fix | Delete

$order = wc_get_order( $order_id );

[271] Fix | Delete

if ( ! $order ) {

[272] Fix | Delete

return -1;

[273] Fix | Delete

}

[274] Fix | Delete

[275] Fix | Delete

$tax_items = $order->get_items( 'tax' );

[276] Fix | Delete

$num_updated = 0;

[277] Fix | Delete

[278] Fix | Delete

foreach ( $tax_items as $tax_item ) {

[279] Fix | Delete

$result = $wpdb->replace(

[280] Fix | Delete

self::get_db_table_name(),

[281] Fix | Delete

array(

[282] Fix | Delete

'order_id' => $order->get_id(),

[283] Fix | Delete

'date_created' => $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ),

[284] Fix | Delete

'tax_rate_id' => $tax_item->get_rate_id(),

[285] Fix | Delete

'shipping_tax' => $tax_item->get_shipping_tax_total(),

[286] Fix | Delete

'order_tax' => $tax_item->get_tax_total(),

[287] Fix | Delete

'total_tax' => (float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total(),

[288] Fix | Delete

),

[289] Fix | Delete

array(

[290] Fix | Delete

'%d',

[291] Fix | Delete

'%s',

[292] Fix | Delete

'%d',

[293] Fix | Delete

'%f',

[294] Fix | Delete

'%f',

[295] Fix | Delete

'%f',

[296] Fix | Delete

)

[297] Fix | Delete

);

[298] Fix | Delete

[299] Fix | Delete

/**

[300] Fix | Delete

* Fires when tax's reports are updated.

[301] Fix | Delete

*

[302] Fix | Delete

* @param int $tax_rate_id Tax Rate ID.

[303] Fix | Delete

* @param int $order_id Order ID.

[304] Fix | Delete

*/

[305] Fix | Delete

do_action( 'woocommerce_analytics_update_tax', $tax_item->get_rate_id(), $order->get_id() );

[306] Fix | Delete

[307] Fix | Delete

// Sum the rows affected. Using REPLACE can affect 2 rows if the row already exists.

[308] Fix | Delete

$num_updated += 2 === intval( $result ) ? 1 : intval( $result );

[309] Fix | Delete

}

[310] Fix | Delete

[311] Fix | Delete

return ( count( $tax_items ) === $num_updated );

[312] Fix | Delete

}

[313] Fix | Delete

[314] Fix | Delete

/**

[315] Fix | Delete

* Clean taxes data when an order is deleted.

[316] Fix | Delete

*

[317] Fix | Delete

* @param int $order_id Order ID.

[318] Fix | Delete

*/

[319] Fix | Delete

public static function sync_on_order_delete( $order_id ) {

[320] Fix | Delete

global $wpdb;

[321] Fix | Delete

[322] Fix | Delete

$wpdb->delete( self::get_db_table_name(), array( 'order_id' => $order_id ) );

[323] Fix | Delete

[324] Fix | Delete

/**

[325] Fix | Delete

* Fires when tax's reports are removed from database.

[326] Fix | Delete

*

[327] Fix | Delete

* @param int $tax_rate_id Tax Rate ID.

[328] Fix | Delete

* @param int $order_id Order ID.

[329] Fix | Delete

*/

[330] Fix | Delete

do_action( 'woocommerce_analytics_delete_tax', 0, $order_id );

[331] Fix | Delete

[332] Fix | Delete

ReportsCache::invalidate();

[333] Fix | Delete

}

[334] Fix | Delete

[335] Fix | Delete

/**

[336] Fix | Delete

* Initialize query objects.

[337] Fix | Delete

*/

[338] Fix | Delete

protected function initialize_queries() {

[339] Fix | Delete

global $wpdb;

[340] Fix | Delete

[341] Fix | Delete

$this->clear_all_clauses();

[342] Fix | Delete

$this->subquery = new SqlQuery( $this->context . '_subquery' );

[343] Fix | Delete

$this->subquery->add_sql_clause( 'select', self::get_db_table_name() . '.tax_rate_id' );

[344] Fix | Delete

$this->subquery->add_sql_clause( 'from', self::get_db_table_name() );

[345] Fix | Delete

$this->subquery->add_sql_clause( 'group_by', self::get_db_table_name() . '.tax_rate_id' );

[346] Fix | Delete

$this->subquery->add_sql_clause( 'group_by', ", {$wpdb->prefix}woocommerce_order_items.order_item_name, itemmeta_rate_percent.meta_value" );

[347] Fix | Delete

}

[348] Fix | Delete

}

[349] Fix | Delete

[350] Fix | Delete