DataStore.php
352 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Taxes\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Taxes; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\TimeInterval; |
| 13 | use Automattic\WooCommerce\Admin\API\Reports\SqlQuery; |
| 14 | use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache; |
| 15 | use Automattic\WooCommerce\Enums\OrderItemType; |
| 16 | |
| 17 | /** |
| 18 | * API\Reports\Taxes\DataStore. |
| 19 | */ |
| 20 | class DataStore extends ReportsDataStore implements DataStoreInterface { |
| 21 | |
| 22 | /** |
| 23 | * Table used to get the data. |
| 24 | * |
| 25 | * @override ReportsDataStore::$table_name |
| 26 | * |
| 27 | * @var string |
| 28 | */ |
| 29 | protected static $table_name = 'wc_order_tax_lookup'; |
| 30 | |
| 31 | /** |
| 32 | * Cache identifier. |
| 33 | * |
| 34 | * @override ReportsDataStore::$cache_key |
| 35 | * |
| 36 | * @var string |
| 37 | */ |
| 38 | protected $cache_key = 'taxes'; |
| 39 | |
| 40 | /** |
| 41 | * Mapping columns to data type to return correct response types. |
| 42 | * |
| 43 | * @override ReportsDataStore::$column_types |
| 44 | * |
| 45 | * @var array |
| 46 | */ |
| 47 | protected $column_types = array( |
| 48 | 'tax_rate_id' => 'intval', |
| 49 | 'name' => 'strval', |
| 50 | 'tax_rate' => 'floatval', |
| 51 | 'country' => 'strval', |
| 52 | 'state' => 'strval', |
| 53 | 'priority' => 'intval', |
| 54 | 'total_tax' => 'floatval', |
| 55 | 'order_tax' => 'floatval', |
| 56 | 'shipping_tax' => 'floatval', |
| 57 | 'orders_count' => 'intval', |
| 58 | ); |
| 59 | |
| 60 | /** |
| 61 | * Data store context used to pass to filters. |
| 62 | * |
| 63 | * @override ReportsDataStore::$context |
| 64 | * |
| 65 | * @var string |
| 66 | */ |
| 67 | protected $context = 'taxes'; |
| 68 | |
| 69 | /** |
| 70 | * Assign report columns once full table name has been assigned. |
| 71 | * |
| 72 | * @override ReportsDataStore::assign_report_columns() |
| 73 | */ |
| 74 | protected function assign_report_columns() { |
| 75 | global $wpdb; |
| 76 | $table_name = self::get_db_table_name(); |
| 77 | |
| 78 | // Using wp_woocommerce_tax_rates table limits the result to only the existing tax rates and |
| 79 | // omits the historical records which differs from the purpose of wp_wc_order_tax_lookup table. |
| 80 | // So in order to get the same data present in wp_woocommerce_tax_rates without breaking the |
| 81 | // API contract the values are now retrieved from wp_woocommerce_order_items and wp_woocommerce_order_itemmeta. |
| 82 | // And given that country, state and priority are not separate columns within the woocommerce_order_items, |
| 83 | // a split to order_item_name column value is required to separate those values. This is not ideal, |
| 84 | // but given this query is paginated and cached, then it is not a big deal. There is always room for |
| 85 | // improvements here. |
| 86 | $this->report_columns = array( |
| 87 | 'tax_rate_id' => "{$table_name}.tax_rate_id", |
| 88 | 'name' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-2), '-', 1) as name", |
| 89 | 'tax_rate' => 'CAST(itemmeta_rate_percent.meta_value AS DECIMAL(7,4)) as tax_rate', |
| 90 | 'country' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',1) as country", |
| 91 | 'state' => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-3), '-', 1) as state", |
| 92 | 'priority' => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-1) as priority", |
| 93 | 'total_tax' => 'SUM(total_tax) as total_tax', |
| 94 | 'order_tax' => 'SUM(order_tax) as order_tax', |
| 95 | 'shipping_tax' => 'SUM(shipping_tax) as shipping_tax', |
| 96 | 'orders_count' => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count", |
| 97 | ); |
| 98 | } |
| 99 | |
| 100 | /** |
| 101 | * Set up all the hooks for maintaining and populating table data. |
| 102 | */ |
| 103 | public static function init() { |
| 104 | add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 15 ); |
| 105 | } |
| 106 | |
| 107 | /** |
| 108 | * Fills FROM clause of SQL request based on user supplied parameters. |
| 109 | * |
| 110 | * @param array $query_args Query arguments supplied by the user. |
| 111 | * @param string $order_status_filter Order status subquery. |
| 112 | */ |
| 113 | protected function add_from_sql_params( $query_args, $order_status_filter ) { |
| 114 | global $wpdb; |
| 115 | $table_name = self::get_db_table_name(); |
| 116 | |
| 117 | if ( $order_status_filter ) { |
| 118 | $this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id" ); |
| 119 | } |
| 120 | |
| 121 | $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'" ); |
| 122 | $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'" ); |
| 123 | $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'" ); |
| 124 | } |
| 125 | |
| 126 | /** |
| 127 | * Updates the database query with parameters used for Taxes report: categories and order status. |
| 128 | * |
| 129 | * @see Automattic\WooCommerce\Admin\API\Reports\Taxes\Stats\DataStore::update_sql_query_params() |
| 130 | * @param array $query_args Query arguments supplied by the user. |
| 131 | */ |
| 132 | protected function add_sql_query_params( $query_args ) { |
| 133 | global $wpdb; |
| 134 | |
| 135 | $order_tax_lookup_table = self::get_db_table_name(); |
| 136 | |
| 137 | $this->add_time_period_sql_params( $query_args, $order_tax_lookup_table ); |
| 138 | $this->get_limit_sql_params( $query_args ); |
| 139 | $this->add_order_by_sql_params( $query_args ); |
| 140 | $order_status_filter = $this->get_status_subquery( $query_args ); |
| 141 | $this->add_from_sql_params( $query_args, $order_status_filter ); |
| 142 | |
| 143 | $this->subquery->add_sql_clause( 'where', "AND itemmeta_rate_id.meta_value = {$order_tax_lookup_table}.tax_rate_id" ); |
| 144 | if ( isset( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) { |
| 145 | $allowed_taxes = self::get_filtered_ids( $query_args, 'taxes' ); |
| 146 | $this->subquery->add_sql_clause( 'where', "AND {$order_tax_lookup_table}.tax_rate_id IN ({$allowed_taxes})" ); |
| 147 | } |
| 148 | |
| 149 | if ( $order_status_filter ) { |
| 150 | $this->subquery->add_sql_clause( 'where', "AND ( {$order_status_filter} )" ); |
| 151 | } |
| 152 | } |
| 153 | |
| 154 | /** |
| 155 | * Get the default query arguments to be used by get_data(). |
| 156 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 157 | * |
| 158 | * @override ReportsDataStore::get_default_query_vars() |
| 159 | * |
| 160 | * @return array Query parameters. |
| 161 | */ |
| 162 | public function get_default_query_vars() { |
| 163 | $defaults = parent::get_default_query_vars(); |
| 164 | $defaults['orderby'] = 'tax_rate_id'; |
| 165 | $defaults['taxes'] = array(); |
| 166 | |
| 167 | return $defaults; |
| 168 | } |
| 169 | |
| 170 | /** |
| 171 | * Returns the report data based on normalized parameters. |
| 172 | * Will be called by `get_data` if there is no data in cache. |
| 173 | * |
| 174 | * @override ReportsDataStore::get_noncached_data() |
| 175 | * |
| 176 | * @see get_data |
| 177 | * @param array $query_args Query parameters. |
| 178 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 179 | */ |
| 180 | public function get_noncached_data( $query_args ) { |
| 181 | global $wpdb; |
| 182 | |
| 183 | $this->initialize_queries(); |
| 184 | |
| 185 | $data = (object) array( |
| 186 | 'data' => array(), |
| 187 | 'total' => 0, |
| 188 | 'pages' => 0, |
| 189 | 'page_no' => 0, |
| 190 | ); |
| 191 | |
| 192 | $this->add_sql_query_params( $query_args ); |
| 193 | $params = $this->get_limit_params( $query_args ); |
| 194 | |
| 195 | if ( isset( $query_args['taxes'] ) && is_array( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) { |
| 196 | $total_results = count( $query_args['taxes'] ); |
| 197 | $total_pages = (int) ceil( $total_results / $params['per_page'] ); |
| 198 | } else { |
| 199 | $db_records_count = (int) $wpdb->get_var( |
| 200 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- cache ok, DB call ok, unprepared SQL ok. |
| 201 | "SELECT COUNT(*) FROM ( {$this->subquery->get_query_statement()} ) AS tt" |
| 202 | ); |
| 203 | |
| 204 | $total_results = $db_records_count; |
| 205 | $total_pages = (int) ceil( $db_records_count / $params['per_page'] ); |
| 206 | |
| 207 | if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) { |
| 208 | return $data; |
| 209 | } |
| 210 | } |
| 211 | |
| 212 | $this->subquery->clear_sql_clause( 'select' ); |
| 213 | $this->subquery->add_sql_clause( 'select', $this->selected_columns( $query_args ) ); |
| 214 | if ( in_array( $query_args['orderby'], array( 'total_tax', 'order_tax', 'shipping_tax', 'orders_count' ), true ) ) { |
| 215 | $this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', tax_rate_id' ); |
| 216 | } else { |
| 217 | $this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) ); |
| 218 | } |
| 219 | $this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) ); |
| 220 | |
| 221 | $taxes_query = $this->subquery->get_query_statement(); |
| 222 | |
| 223 | $tax_data = $wpdb->get_results( |
| 224 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok. |
| 225 | $taxes_query, |
| 226 | ARRAY_A |
| 227 | ); |
| 228 | |
| 229 | if ( null === $tax_data ) { |
| 230 | return $data; |
| 231 | } |
| 232 | |
| 233 | $tax_data = array_map( array( $this, 'cast_numbers' ), $tax_data ); |
| 234 | $data = (object) array( |
| 235 | 'data' => $tax_data, |
| 236 | 'total' => $total_results, |
| 237 | 'pages' => $total_pages, |
| 238 | 'page_no' => (int) $query_args['page'], |
| 239 | ); |
| 240 | |
| 241 | return $data; |
| 242 | } |
| 243 | |
| 244 | /** |
| 245 | * Maps ordering specified by the user to columns in the database/fields in the data. |
| 246 | * |
| 247 | * @override ReportsDataStore::normalize_order_by() |
| 248 | * |
| 249 | * @param string $order_by Sorting criterion. |
| 250 | * @return string |
| 251 | */ |
| 252 | protected function normalize_order_by( $order_by ) { |
| 253 | global $wpdb; |
| 254 | |
| 255 | if ( 'tax_code' === $order_by ) { |
| 256 | return "{$wpdb->prefix}woocommerce_order_items.order_item_name"; |
| 257 | } elseif ( 'rate' === $order_by ) { |
| 258 | return 'tax_rate'; |
| 259 | } |
| 260 | |
| 261 | return $order_by; |
| 262 | } |
| 263 | |
| 264 | /** |
| 265 | * Create or update an entry in the wc_order_tax_lookup table for an order. |
| 266 | * |
| 267 | * @param int $order_id Order ID. |
| 268 | * @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success. |
| 269 | */ |
| 270 | public static function sync_order_taxes( $order_id ) { |
| 271 | global $wpdb; |
| 272 | |
| 273 | $order = wc_get_order( $order_id ); |
| 274 | if ( ! $order ) { |
| 275 | return -1; |
| 276 | } |
| 277 | |
| 278 | $tax_items = $order->get_items( OrderItemType::TAX ); |
| 279 | $num_updated = 0; |
| 280 | |
| 281 | foreach ( $tax_items as $tax_item ) { |
| 282 | $result = $wpdb->replace( |
| 283 | self::get_db_table_name(), |
| 284 | array( |
| 285 | 'order_id' => $order->get_id(), |
| 286 | 'date_created' => $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ), |
| 287 | 'tax_rate_id' => $tax_item->get_rate_id(), |
| 288 | 'shipping_tax' => $tax_item->get_shipping_tax_total(), |
| 289 | 'order_tax' => $tax_item->get_tax_total(), |
| 290 | 'total_tax' => (float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total(), |
| 291 | ), |
| 292 | array( |
| 293 | '%d', |
| 294 | '%s', |
| 295 | '%d', |
| 296 | '%f', |
| 297 | '%f', |
| 298 | '%f', |
| 299 | ) |
| 300 | ); |
| 301 | |
| 302 | /** |
| 303 | * Fires when tax's reports are updated. |
| 304 | * |
| 305 | * @param int $tax_rate_id Tax Rate ID. |
| 306 | * @param int $order_id Order ID. |
| 307 | */ |
| 308 | do_action( 'woocommerce_analytics_update_tax', $tax_item->get_rate_id(), $order->get_id() ); |
| 309 | |
| 310 | // Sum the rows affected. Using REPLACE can affect 2 rows if the row already exists. |
| 311 | $num_updated += 2 === intval( $result ) ? 1 : intval( $result ); |
| 312 | } |
| 313 | |
| 314 | return ( count( $tax_items ) === $num_updated ); |
| 315 | } |
| 316 | |
| 317 | /** |
| 318 | * Clean taxes data when an order is deleted. |
| 319 | * |
| 320 | * @param int $order_id Order ID. |
| 321 | */ |
| 322 | public static function sync_on_order_delete( $order_id ) { |
| 323 | global $wpdb; |
| 324 | |
| 325 | $wpdb->delete( self::get_db_table_name(), array( 'order_id' => $order_id ) ); |
| 326 | |
| 327 | /** |
| 328 | * Fires when tax's reports are removed from database. |
| 329 | * |
| 330 | * @param int $tax_rate_id Tax Rate ID. |
| 331 | * @param int $order_id Order ID. |
| 332 | */ |
| 333 | do_action( 'woocommerce_analytics_delete_tax', 0, $order_id ); |
| 334 | |
| 335 | ReportsCache::invalidate(); |
| 336 | } |
| 337 | |
| 338 | /** |
| 339 | * Initialize query objects. |
| 340 | */ |
| 341 | protected function initialize_queries() { |
| 342 | global $wpdb; |
| 343 | |
| 344 | $this->clear_all_clauses(); |
| 345 | $this->subquery = new SqlQuery( $this->context . '_subquery' ); |
| 346 | $this->subquery->add_sql_clause( 'select', self::get_db_table_name() . '.tax_rate_id' ); |
| 347 | $this->subquery->add_sql_clause( 'from', self::get_db_table_name() ); |
| 348 | $this->subquery->add_sql_clause( 'group_by', self::get_db_table_name() . '.tax_rate_id' ); |
| 349 | $this->subquery->add_sql_clause( 'group_by', ", {$wpdb->prefix}woocommerce_order_items.order_item_name, itemmeta_rate_percent.meta_value" ); |
| 350 | } |
| 351 | } |
| 352 |