DataStore.php
<?php
/**
* API\Reports\Products\DataStore class file.
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore;
use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface;
use Automattic\WooCommerce\Admin\API\Reports\TimeInterval;
use Automattic\WooCommerce\Admin\API\Reports\SqlQuery;
use Automattic\WooCommerce\Utilities\OrderUtil;
use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
use Automattic\WooCommerce\Enums\ProductType;
/**
* API\Reports\Products\DataStore.
*/
class DataStore extends ReportsDataStore implements DataStoreInterface {
/**
* Table used to get the data.
*
* @override ReportsDataStore::$table_name
*
* @var string
*/
protected static $table_name = 'wc_order_product_lookup';
/**
* Cache identifier.
*
* @override ReportsDataStore::$cache_key
*
* @var string
*/
protected $cache_key = 'products';
/**
* Mapping columns to data type to return correct response types.
*
* @override ReportsDataStore::$column_types
*
* @var array
*/
protected $column_types = array(
'date_start' => 'strval',
'date_end' => 'strval',
'product_id' => 'intval',
'items_sold' => 'intval',
'net_revenue' => 'floatval',
'orders_count' => 'intval',
// Extended attributes.
'name' => 'strval',
'price' => 'floatval',
'image' => 'strval',
'permalink' => 'strval',
'stock_status' => 'strval',
'stock_quantity' => 'intval',
'low_stock_amount' => 'intval',
'category_ids' => 'array_values',
'variations' => 'array_values',
'sku' => 'strval',
);
/**
* Extended product attributes to include in the data.
*
* @var array
*/
protected $extended_attributes = array(
'name',
'price',
'image',
'permalink',
'stock_status',
'stock_quantity',
'manage_stock',
'low_stock_amount',
'category_ids',
'variations',
'sku',
);
/**
* Data store context used to pass to filters.
*
* @override ReportsDataStore::$context
*
* @var string
*/
protected $context = 'products';
/**
* Assign report columns once full table name has been assigned.
*
* @override ReportsDataStore::assign_report_columns()
*/
protected function assign_report_columns() {
$table_name = self::get_db_table_name();
$this->report_columns = array(
'product_id' => 'product_id',
'items_sold' => 'SUM(product_qty) as items_sold',
'net_revenue' => 'SUM(product_net_revenue) AS net_revenue',
'orders_count' => "COUNT( DISTINCT ( CASE WHEN product_gross_revenue >= 0 THEN {$table_name}.order_id END ) ) as orders_count",
);
}
/**
* Set up all the hooks for maintaining and populating table data.
*/
public static function init() {
add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 10 );
add_action( 'woocommerce_order_partially_refunded', array( __CLASS__, 'add_partial_refund_type_meta' ), 10, 2 );
add_action( 'woocommerce_order_fully_refunded', array( __CLASS__, 'add_full_refund_type_meta' ), 10, 2 );
}
/**
* Add a partial refund type meta to the order.
*
* @param int $order_id Order ID.
* @param int $refund_id Refund ID.
*/
public static function add_partial_refund_type_meta( $order_id, $refund_id ) {
self::add_refund_type_meta( $refund_id, 'partial' );
}
/**
* Add a full refund type meta to the order.
*
* @param int $order_id Order ID.
* @param int $refund_id Refund ID.
*/
public static function add_full_refund_type_meta( $order_id, $refund_id ) {
self::add_refund_type_meta( $refund_id, 'full' );
}
/**
* Add a refund type meta to the order.
*
* @param int $refund_id Refund ID.
* @param string $type Refund type.
*/
public static function add_refund_type_meta( $refund_id, $type ) {
$order = wc_get_order( $refund_id );
$order->update_meta_data( '_refund_type', $type );
$order->save_meta_data();
}
/**
* Fills FROM clause of SQL request based on user supplied parameters.
*
* @param array $query_args Parameters supplied by the user.
* @param string $arg_name Target of the JOIN sql param.
* @param string $id_cell ID cell identifier, like `table_name.id_column_name`.
*/
protected function add_from_sql_params( $query_args, $arg_name, $id_cell ) {
global $wpdb;
$type = 'join';
// Order by product name requires extra JOIN.
switch ( $query_args['orderby'] ) {
case 'product_name':
$join = " JOIN {$wpdb->posts} AS _products ON {$id_cell} = _products.ID";
break;
case 'sku':
$join = " LEFT JOIN {$wpdb->postmeta} AS postmeta ON {$id_cell} = postmeta.post_id AND postmeta.meta_key = '_sku'";
break;
case 'variations':
$type = 'left_join';
$join = "LEFT JOIN ( SELECT post_parent, COUNT(*) AS variations FROM {$wpdb->posts} WHERE post_type = 'product_variation' GROUP BY post_parent ) AS _variations ON {$id_cell} = _variations.post_parent";
break;
default:
$join = '';
break;
}
if ( $join ) {
if ( 'inner' === $arg_name ) {
$this->subquery->add_sql_clause( $type, $join );
} else {
$this->add_sql_clause( $type, $join );
}
}
}
/**
* Updates the database query with parameters used for Products report: categories and order status.
*
* @param array $query_args Query arguments supplied by the user.
*/
protected function add_sql_query_params( $query_args ) {
global $wpdb;
$order_product_lookup_table = self::get_db_table_name();
$this->add_time_period_sql_params( $query_args, $order_product_lookup_table );
$this->get_limit_sql_params( $query_args );
$this->add_order_by_sql_params( $query_args );
$included_products = $this->get_included_products( $query_args );
if ( $included_products ) {
$this->add_from_sql_params( $query_args, 'outer', 'default_results.product_id' );
$this->subquery->add_sql_clause( 'where', "AND {$order_product_lookup_table}.product_id IN ({$included_products})" );
} else {
$this->add_from_sql_params( $query_args, 'inner', "{$order_product_lookup_table}.product_id" );
}
$included_variations = $this->get_included_variations( $query_args );
if ( $included_variations ) {
$this->subquery->add_sql_clause( 'where', "AND {$order_product_lookup_table}.variation_id IN ({$included_variations})" );
}
$order_status_filter = $this->get_status_subquery( $query_args );
if ( $order_status_filter ) {
$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$order_product_lookup_table}.order_id = {$wpdb->prefix}wc_order_stats.order_id" );
$this->subquery->add_sql_clause( 'where', "AND ( {$order_status_filter} )" );
}
}
/**
* Maps ordering specified by the user to columns in the database/fields in the data.
*
* @override ReportsDataStore::normalize_order_by()
*
* @param string $order_by Sorting criterion.
* @return string
*/
protected function normalize_order_by( $order_by ) {
if ( 'date' === $order_by ) {
return self::get_db_table_name() . '.date_created';
}
if ( 'product_name' === $order_by ) {
return 'post_title';
}
if ( 'sku' === $order_by ) {
return 'meta_value';
}
return $order_by;
}
/**
* Enriches the product data with attributes specified by the extended_attributes.
*
* @param array $products_data Product data.
* @param array $query_args Query parameters.
*/
protected function include_extended_info( &$products_data, $query_args ) {
global $wpdb;
$product_names = array();
foreach ( $products_data as $key => $product_data ) {
$extended_info = new \ArrayObject();
if ( $query_args['extended_info'] ) {
$product_id = $product_data['product_id'];
$product = wc_get_product( $product_id );
// Product was deleted.
if ( ! $product ) {
if ( ! isset( $product_names[ $product_id ] ) ) {
$product_names[ $product_id ] = $wpdb->get_var(
$wpdb->prepare(
"SELECT i.order_item_name
FROM {$wpdb->prefix}wc_order_product_lookup l
JOIN {$wpdb->prefix}woocommerce_order_items i ON i.order_item_id = l.order_item_id
WHERE l.product_id = %d
ORDER BY l.order_item_id DESC
LIMIT 1",
$product_id
)
);
}
/* translators: %s is product name */
$products_data[ $key ]['extended_info']['name'] = $product_names[ $product_id ] ? sprintf( __( '%s (Deleted)', 'woocommerce' ), $product_names[ $product_id ] ) : __( '(Deleted)', 'woocommerce' );
continue;
}
$extended_attributes = apply_filters( 'woocommerce_rest_reports_products_extended_attributes', $this->extended_attributes, $product_data );
foreach ( $extended_attributes as $extended_attribute ) {
if ( 'variations' === $extended_attribute ) {
if ( ! $product->is_type( ProductType::VARIABLE ) ) {
continue;
}
$function = 'get_children';
} else {
$function = 'get_' . $extended_attribute;
}
if ( is_callable( array( $product, $function ) ) ) {
$value = $product->{$function}();
$extended_info[ $extended_attribute ] = $value;
}
}
// If there is no set low_stock_amount, use the one in user settings.
if ( '' === $extended_info['low_stock_amount'] ) {
$extended_info['low_stock_amount'] = absint( max( get_option( 'woocommerce_notify_low_stock_amount' ), 1 ) );
}
$extended_info = $this->cast_numbers( $extended_info );
}
$products_data[ $key ]['extended_info'] = $extended_info;
}
}
/**
* Returns the report data based on parameters supplied by the user.
*
* @override ReportsDataStore::get_data()
*
* @param array $query_args Query parameters.
* @return stdClass|WP_Error Data.
*/
public function get_data( $query_args ) {
$data = parent::get_data( $query_args );
/*
* Do not cache extended info -- this is required to get the latest stock data.
* `include_extended_info` checks only `extended_info` key,
* so we don't need to bother about normalizing timestamps.
*/
$defaults = $this->get_default_query_vars();
$query_args = wp_parse_args( $query_args, $defaults );
$this->include_extended_info( $data->data, $query_args );
return $data;
}
/**
* Get the default query arguments to be used by get_data().
* These defaults are only partially applied when used via REST API, as that has its own defaults.
*
* @override ReportsDataStore::get_default_query_vars()
*
* @return array Query parameters.
*/
public function get_default_query_vars() {
$defaults = parent::get_default_query_vars();
$defaults['category_includes'] = array();
$defaults['product_includes'] = array();
$defaults['extended_info'] = false;
return $defaults;
}
/**
* Returns the report data based on normalized parameters.
* Will be called by `get_data` if there is no data in cache.
*
* @override ReportsDataStore::get_noncached_data()
*
* @see get_data
* @param array $query_args Query parameters.
* @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error.
*/
public function get_noncached_data( $query_args ) {
global $wpdb;
$table_name = self::get_db_table_name();
$this->initialize_queries();
$data = (object) array(
'data' => array(),
'total' => 0,
'pages' => 0,
'page_no' => 0,
);
$selections = $this->selected_columns( $query_args );
$included_products = $this->get_included_products_array( $query_args );
$params = $this->get_limit_params( $query_args );
$this->add_sql_query_params( $query_args );
if ( count( $included_products ) > 0 ) {
$filtered_products = array_diff( $included_products, array( '-1' ) );
$total_results = count( $filtered_products );
$total_pages = (int) ceil( $total_results / $params['per_page'] );
if ( 'date' === $query_args['orderby'] ) {
$selections .= ", {$table_name}.date_created";
}
$fields = $this->get_fields( $query_args );
$join_selections = $this->format_join_selections( $fields, array( 'product_id' ) );
$ids_table = $this->get_ids_table( $included_products, 'product_id' );
$this->subquery->clear_sql_clause( 'select' );
$this->subquery->add_sql_clause( 'select', $selections );
$this->add_sql_clause( 'select', $join_selections );
$this->add_sql_clause( 'from', '(' );
$this->add_sql_clause( 'from', $this->subquery->get_query_statement() );
$this->add_sql_clause( 'from', ") AS {$table_name}" );
$this->add_sql_clause(
'right_join',
"RIGHT JOIN ( {$ids_table} ) AS default_results
ON default_results.product_id = {$table_name}.product_id"
);
$this->add_sql_clause( 'where', 'AND default_results.product_id != -1' );
$products_query = $this->get_query_statement();
} else {
$count_query = "SELECT COUNT(*) FROM (
{$this->subquery->get_query_statement()}
) AS tt";
$db_records_count = (int) $wpdb->get_var(
$count_query // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
);
$total_results = $db_records_count;
$total_pages = (int) ceil( $db_records_count / $params['per_page'] );
if ( ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) ) {
return $data;
}
$this->subquery->clear_sql_clause( 'select' );
$this->subquery->add_sql_clause( 'select', $selections );
if ( in_array( $query_args['orderby'], array( 'items_sold', 'net_revenue', 'orders_count', 'variations' ), true ) ) {
$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', product_id' );
} else {
$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) );
}
$this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) );
$products_query = $this->subquery->get_query_statement();
}
$product_data = $wpdb->get_results(
$products_query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
ARRAY_A
);
if ( null === $product_data ) {
return $data;
}
$product_data = array_map( array( $this, 'cast_numbers' ), $product_data );
$data = (object) array(
'data' => $product_data,
'total' => $total_results,
'pages' => $total_pages,
'page_no' => (int) $query_args['page'],
);
return $data;
}
/**
* Create or update an entry in the wc_admin_order_product_lookup table for an order.
*
* @since 3.5.0
* @param int $order_id Order ID.
* @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success.
*/
public static function sync_order_products( $order_id ) {
global $wpdb;
$order = wc_get_order( $order_id );
if ( ! $order ) {
return -1;
}
$table_name = self::get_db_table_name();
$existing_items = $wpdb->get_col(
$wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT order_item_id FROM {$table_name} WHERE order_id = %d",
$order_id
)
);
$existing_items = array_flip( $existing_items );
$order_items = $order->get_items();
$num_updated = 0;
$decimals = wc_get_price_decimals();
$round_tax = 'no' === get_option( 'woocommerce_tax_round_at_subtotal' );
$is_full_refund_without_line_items = false;
$partial_refund_product_revenue = array();
$refund_type = $order->get_meta( '_refund_type' );
$uses_new_full_refund_data = OrderUtil::uses_new_full_refund_data();
$parent_order = null;
// When changing the order status to "Refunded", the refund order's type will be full refund, and the order items will be empty.
// We need to get the parent order items, and exclude the items that are already being partially refunded.
if (
'shop_order_refund' === $order->get_type() &&
'full' === $refund_type &&
empty( $order_items ) &&
$uses_new_full_refund_data
) {
$is_full_refund_without_line_items = true;
$parent_order_id = $order->get_parent_id();
$parent_order = wc_get_order( $parent_order_id );
$order_items = $parent_order->get_items();
// Get the partially refunded product and variation IDs along with their sum of product_net_revenue from the parent order.
$partial_refund_products = $wpdb->get_results(
$wpdb->prepare(
"
SELECT
product_lookup.product_id,
product_lookup.variation_id,
SUM( product_lookup.product_net_revenue ) AS product_net_revenue
FROM %i AS product_lookup
INNER JOIN {$wpdb->prefix}wc_order_stats AS order_stats
ON order_stats.order_id = product_lookup.order_id
WHERE 1 = 1
AND order_stats.parent_id = %d
AND product_lookup.product_net_revenue < 0
GROUP BY product_lookup.product_id, product_lookup.variation_id
",
$table_name,
$parent_order_id
)
);
/**
* Create a lookup table for partially refunded products.
* E.g. [
* '1' => -20,
* '2' => -40,
* '51' => -10,
* '52' => -30,
* ]
*/
foreach ( $partial_refund_products as $product ) {
$id = $product->variation_id ? $product->variation_id : $product->product_id;
$partial_refund_product_revenue[ $id ] = (float) $product->product_net_revenue;
}
}
foreach ( $order_items as $order_item ) {
$order_item_id = $order_item->get_id();
unset( $existing_items[ $order_item_id ] );
$product_qty = $order_item->get_quantity( 'edit' );
$product_id = $order_item->get_product_id( 'edit' );
$variation_id = $order_item->get_variation_id( 'edit' );
$shipping_amount = $order->get_item_shipping_amount( $order_item );
$shipping_tax_amount = $order->get_item_shipping_tax_amount( $order_item );
$coupon_amount = $order->get_item_coupon_amount( $order_item );
$tax_amount = $order->get_item_cart_tax_amount( $order_item );
$net_revenue = round( $order_item->get_total( 'edit' ), $decimals );
// If the order is a full refund and there is no order items. The order item here is the parent order item.
if ( $is_full_refund_without_line_items ) {
$id = $variation_id ? $variation_id : $product_id;
$partial_refund = $partial_refund_product_revenue[ $id ] ?? 0;
// If a single line item was refunded 60% then fully refunded after, we need store the difference in the product lookup table.
// E.g. A product costs $100, it was previously partially refunded $60, then fully refunded $40.
// So it will be -abs( 100 + (-60) ) = -40.
$net_revenue = -abs( $net_revenue + $partial_refund );
// Skip items that have already been fully refunded (single or multiple partial refunds).
if ( 0.0 === $net_revenue ) {
continue;
}
$product_qty = -abs( $product_qty );
// Set coupon amount to 0 for full refunds without line items.
$coupon_amount = 0;
if ( $parent_order ) {
$remaining_refund_items = $parent_order->get_remaining_refund_items();
// Calculate the shipping amount to refund from the parent order.
$total_shipping_refunded = $parent_order->get_total_shipping_refunded();
$shipping_total = (float) $parent_order->get_shipping_total();
$total_shipping_to_refund = $shipping_total - $total_shipping_refunded;
if ( $total_shipping_to_refund > 0 ) {
$shipping_amount = -abs( $parent_order->get_item_shipping_amount( $order_item, $remaining_refund_items, $total_shipping_to_refund ) );
}
// Calculate the shipping tax amount to refund from the parent order.
$shipping_tax = (float) $parent_order->get_shipping_tax();
$total_shipping_tax_refunded = $parent_order->get_total_shipping_tax_refunded();
$total_shipping_tax_to_refund = $shipping_tax - $total_shipping_tax_refunded;
if ( $total_shipping_tax_to_refund > 0 ) {
$shipping_tax_amount = -abs( $parent_order->get_item_shipping_tax_amount( $order_item, $remaining_refund_items, $total_shipping_tax_to_refund ) );
}
// Calculate cart tax amount of the item from the parent order.
$tax_amount = -abs( $parent_order->get_item_cart_tax_amount( $order_item ) );
}
}
$is_refund = $net_revenue < 0;
// Skip line items without changes to product quantity.
if ( ! $product_qty && ! $is_refund ) {
++$num_updated;
continue;
}
if ( $round_tax ) {
$tax_amount = round( $tax_amount, $decimals );
}
$result = $wpdb->replace(
self::get_db_table_name(),
array(
'order_item_id' => $order_item_id,
'order_id' => $order->get_id(),
'product_id' => $product_id,
'variation_id' => $variation_id,
'customer_id' => $order->get_report_customer_id(),
'product_qty' => $product_qty,
'product_net_revenue' => $net_revenue,
'date_created' => $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ),
'coupon_amount' => $coupon_amount,
'tax_amount' => $tax_amount,
'shipping_amount' => $shipping_amount,
'shipping_tax_amount' => $shipping_tax_amount,
// @todo Can this be incorrect if modified by filters?
'product_gross_revenue' => $net_revenue + $tax_amount + $shipping_amount + $shipping_tax_amount,
),
array(
'%d', // order_item_id.
'%d', // order_id.
'%d', // product_id.
'%d', // variation_id.
'%d', // customer_id.
'%d', // product_qty.
'%f', // product_net_revenue.
'%s', // date_created.
'%f', // coupon_amount.
'%f', // tax_amount.
'%f', // shipping_amount.
'%f', // shipping_tax_amount.
'%f', // product_gross_revenue.
)
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
/**
* Fires when product's reports are updated.
*
* @param int $order_item_id Order Item ID.
* @param int $order_id Order ID.
*/
do_action( 'woocommerce_analytics_update_product', $order_item_id, $order->get_id() );
// Sum the rows affected. Using REPLACE can affect 2 rows if the row already exists.
$num_updated += 2 === intval( $result ) ? 1 : intval( $result );
}
if ( ! empty( $existing_items ) ) {
$existing_items = array_flip( $existing_items );
$format = array_fill( 0, count( $existing_items ), '%d' );
$format = implode( ',', $format );
array_unshift( $existing_items, $order_id );
$wpdb->query(
$wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"DELETE FROM {$table_name} WHERE order_id = %d AND order_item_id in ({$format})",
$existing_items
)
);
}
return ( count( $order_items ) === $num_updated );
}
/**
* Clean products data when an order is deleted.
*
* @param int $order_id Order ID.
*/
public static function sync_on_order_delete( $order_id ) {
global $wpdb;
$wpdb->delete( self::get_db_table_name(), array( 'order_id' => $order_id ) );
/**
* Fires when product's reports are removed from database.
*
* @param int $product_id Product ID.
* @param int $order_id Order ID.
*/
do_action( 'woocommerce_analytics_delete_product', 0, $order_id );
ReportsCache::invalidate();
}
/**
* Initialize query objects.
*/
protected function initialize_queries() {
$this->clear_all_clauses();
$this->subquery = new SqlQuery( $this->context . '_subquery' );
$this->subquery->add_sql_clause( 'select', 'product_id' );
$this->subquery->add_sql_clause( 'from', self::get_db_table_name() );
$this->subquery->add_sql_clause( 'group_by', 'product_id' );
}
}
