File: /var/www/vhosts/uyarreklam.com.tr/httpdocs/Products.tar
Controller.php 0000644 00000026673 15155362210 0007416 0 ustar 00 <?php
/**
* REST API Reports products controller
*
* Handles requests to the /reports/products endpoint.
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\GenericController;
use Automattic\WooCommerce\Admin\API\Reports\ExportableInterface;
use WP_REST_Request;
use WP_REST_Response;
/**
* REST API Reports products controller class.
*
* @internal
* @extends GenericController
*/
class Controller extends GenericController implements ExportableInterface {
/**
* Route base.
*
* @var string
*/
protected $rest_base = 'reports/products';
/**
* Mapping between external parameter name and name used in query class.
*
* @var array
*/
protected $param_mapping = array(
'categories' => 'category_includes',
'products' => 'product_includes',
'variations' => 'variation_includes',
);
/**
* Get items.
*
* @param WP_REST_Request $request Request data.
*
* @return array|WP_Error
*/
public function get_items( $request ) {
$args = array();
$registered = array_keys( $this->get_collection_params() );
foreach ( $registered as $param_name ) {
if ( isset( $request[ $param_name ] ) ) {
if ( isset( $this->param_mapping[ $param_name ] ) ) {
$args[ $this->param_mapping[ $param_name ] ] = $request[ $param_name ];
} else {
$args[ $param_name ] = $request[ $param_name ];
}
}
}
$reports = new Query( $args );
$products_data = $reports->get_data();
$data = array();
foreach ( $products_data->data as $product_data ) {
$item = $this->prepare_item_for_response( $product_data, $request );
if ( isset( $item->data['extended_info']['name'] ) ) {
$item->data['extended_info']['name'] = wp_strip_all_tags( $item->data['extended_info']['name'] );
}
$data[] = $this->prepare_response_for_collection( $item );
}
return $this->add_pagination_headers(
$request,
$data,
(int) $products_data->total,
(int) $products_data->page_no,
(int) $products_data->pages
);
}
/**
* Prepare a report object for serialization.
*
* @param Array $report Report data.
* @param WP_REST_Request $request Request object.
* @return WP_REST_Response
*/
public function prepare_item_for_response( $report, $request ) {
$response = parent::prepare_item_for_response( $report, $request );
$response->add_links( $this->prepare_links( $report ) );
/**
* Filter a report returned from the API.
*
* Allows modification of the report data right before it is returned.
*
* @param WP_REST_Response $response The response object.
* @param object $report The original report object.
* @param WP_REST_Request $request Request used to generate the response.
*/
return apply_filters( 'woocommerce_rest_prepare_report_products', $response, $report, $request );
}
/**
* Prepare links for the request.
*
* @param Array $object Object data.
* @return array Links for the given post.
*/
protected function prepare_links( $object ) {
$links = array(
'product' => array(
'href' => rest_url( sprintf( '/%s/%s/%d', $this->namespace, 'products', $object['product_id'] ) ),
),
);
return $links;
}
/**
* Get the Report's schema, conforming to JSON Schema.
*
* @return array
*/
public function get_item_schema() {
$schema = array(
'$schema' => 'http://json-schema.org/draft-04/schema#',
'title' => 'report_products',
'type' => 'object',
'properties' => array(
'product_id' => array(
'type' => 'integer',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product ID.', 'woocommerce' ),
),
'items_sold' => array(
'type' => 'integer',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Number of items sold.', 'woocommerce' ),
),
'net_revenue' => array(
'type' => 'number',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Total Net sales of all items sold.', 'woocommerce' ),
),
'orders_count' => array(
'type' => 'integer',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Number of orders product appeared in.', 'woocommerce' ),
),
'extended_info' => array(
'name' => array(
'type' => 'string',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product name.', 'woocommerce' ),
),
'price' => array(
'type' => 'number',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product price.', 'woocommerce' ),
),
'image' => array(
'type' => 'string',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product image.', 'woocommerce' ),
),
'permalink' => array(
'type' => 'string',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product link.', 'woocommerce' ),
),
'category_ids' => array(
'type' => 'array',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product category IDs.', 'woocommerce' ),
),
'stock_status' => array(
'type' => 'string',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product inventory status.', 'woocommerce' ),
),
'stock_quantity' => array(
'type' => 'integer',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product inventory quantity.', 'woocommerce' ),
),
'low_stock_amount' => array(
'type' => 'integer',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product inventory threshold for low stock.', 'woocommerce' ),
),
'variations' => array(
'type' => 'array',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product variations IDs.', 'woocommerce' ),
),
'sku' => array(
'type' => 'string',
'readonly' => true,
'context' => array( 'view', 'edit' ),
'description' => __( 'Product SKU.', 'woocommerce' ),
),
),
),
);
return $this->add_additional_fields_schema( $schema );
}
/**
* Get the query params for collections.
*
* @return array
*/
public function get_collection_params() {
$params = parent::get_collection_params();
$params['orderby']['enum'] = array(
'date',
'net_revenue',
'orders_count',
'items_sold',
'product_name',
'variations',
'sku',
);
$params['categories'] = array(
'description' => __( 'Limit result to items from the specified categories.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_id_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'integer',
),
);
$params['match'] = array(
'description' => __( 'Indicates whether all the conditions should be true for the resulting set, or if any one of them is sufficient. Match affects the following parameters: status_is, status_is_not, product_includes, product_excludes, coupon_includes, coupon_excludes, customer, categories', 'woocommerce' ),
'type' => 'string',
'default' => 'all',
'enum' => array(
'all',
'any',
),
'validate_callback' => 'rest_validate_request_arg',
);
$params['products'] = array(
'description' => __( 'Limit result to items with specified product ids.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_id_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'integer',
),
);
$params['extended_info'] = array(
'description' => __( 'Add additional piece of info about each product to the report.', 'woocommerce' ),
'type' => 'boolean',
'default' => false,
'sanitize_callback' => 'wc_string_to_bool',
'validate_callback' => 'rest_validate_request_arg',
);
return $params;
}
/**
* Get stock status column export value.
*
* @param array $status Stock status from report row.
* @return string
*/
protected function get_stock_status( $status ) {
$statuses = wc_get_product_stock_status_options();
return isset( $statuses[ $status ] ) ? $statuses[ $status ] : '';
}
/**
* Get categories column export value.
*
* @param array $category_ids Category IDs from report row.
* @return string
*/
protected function get_categories( $category_ids ) {
$category_names = get_terms(
array(
'taxonomy' => 'product_cat',
'include' => $category_ids,
'fields' => 'names',
)
);
return implode( ', ', $category_names );
}
/**
* Get the column names for export.
*
* @return array Key value pair of Column ID => Label.
*/
public function get_export_columns() {
$export_columns = array(
'product_name' => __( 'Product title', 'woocommerce' ),
'sku' => __( 'SKU', 'woocommerce' ),
'items_sold' => __( 'Items sold', 'woocommerce' ),
'net_revenue' => __( 'N. Revenue', 'woocommerce' ),
'orders_count' => __( 'Orders', 'woocommerce' ),
'product_cat' => __( 'Category', 'woocommerce' ),
'variations' => __( 'Variations', 'woocommerce' ),
);
if ( 'yes' === get_option( 'woocommerce_manage_stock' ) ) {
$export_columns['stock_status'] = __( 'Status', 'woocommerce' );
$export_columns['stock'] = __( 'Stock', 'woocommerce' );
}
/**
* Filter to add or remove column names from the products report for
* export.
*
* @since 1.6.0
*/
return apply_filters(
'woocommerce_report_products_export_columns',
$export_columns
);
}
/**
* Get the column values for export.
*
* @param array $item Single report item/row.
* @return array Key value pair of Column ID => Row Value.
*/
public function prepare_item_for_export( $item ) {
$export_item = array(
'product_name' => $item['extended_info']['name'],
'sku' => $item['extended_info']['sku'],
'items_sold' => $item['items_sold'],
'net_revenue' => $item['net_revenue'],
'orders_count' => $item['orders_count'],
'product_cat' => $this->get_categories( $item['extended_info']['category_ids'] ),
'variations' => isset( $item['extended_info']['variations'] ) ? count( $item['extended_info']['variations'] ) : 0,
);
if ( 'yes' === get_option( 'woocommerce_manage_stock' ) ) {
if ( $item['extended_info']['manage_stock'] ) {
$export_item['stock_status'] = $this->get_stock_status( $item['extended_info']['stock_status'] );
$export_item['stock'] = $item['extended_info']['stock_quantity'];
} else {
$export_item['stock_status'] = __( 'N/A', 'woocommerce' );
$export_item['stock'] = __( 'N/A', 'woocommerce' );
}
}
/**
* Filter to prepare extra columns in the export item for the products
* report.
*
* @since 1.6.0
*/
return apply_filters(
'woocommerce_report_products_prepare_export_item',
$export_item,
$item
);
}
}
DataStore.php 0000644 00000042526 15155362210 0007154 0 ustar 00 <?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\Admin\API\Reports\Cache as ReportsCache;
/**
* API\Reports\Products\DataStore.
*/
class DataStore extends ReportsDataStore implements DataStoreInterface {
/**
* Table used to get the data.
*
* @var string
*/
protected static $table_name = 'wc_order_product_lookup';
/**
* Cache identifier.
*
* @var string
*/
protected $cache_key = 'products';
/**
* Mapping columns to data type to return correct response 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.
*
* @var string
*/
protected $context = 'products';
/**
* Assign report columns once full table name has been assigned.
*/
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 );
}
/**
* 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.
*
* @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}woocommerce_order_items i, {$wpdb->prefix}woocommerce_order_itemmeta m
WHERE i.order_item_id = m.order_item_id
AND m.meta_key = '_product_id'
AND m.meta_value = %s
ORDER BY i.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( '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.
*
* @param array $query_args Query parameters.
* @return stdClass|WP_Error Data.
*/
public function get_data( $query_args ) {
global $wpdb;
$table_name = self::get_db_table_name();
// These defaults are only partially applied when used via REST API, as that has its own defaults.
$defaults = array(
'per_page' => get_option( 'posts_per_page' ),
'page' => 1,
'order' => 'DESC',
'orderby' => 'date',
'before' => TimeInterval::default_before(),
'after' => TimeInterval::default_after(),
'fields' => '*',
'category_includes' => array(),
'product_includes' => array(),
'extended_info' => false,
);
$query_args = wp_parse_args( $query_args, $defaults );
$this->normalize_timezones( $query_args, $defaults );
/*
* We need to get the cache key here because
* parent::update_intervals_sql_params() modifies $query_args.
*/
$cache_key = $this->get_cache_key( $query_args );
$data = $this->get_cached_data( $cache_key );
if ( false === $data ) {
$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 );
$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'],
);
$this->set_cached_data( $cache_key, $data );
}
$this->include_extended_info( $data->data, $query_args );
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' );
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' );
$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 );
// Skip line items without changes to product quantity.
if ( ! $product_qty ) {
$num_updated++;
continue;
}
// Tax amount.
$tax_amount = 0;
$order_taxes = $order->get_taxes();
$tax_data = $order_item->get_taxes();
foreach ( $order_taxes as $tax_item ) {
$tax_item_id = $tax_item->get_rate_id();
$tax_amount += isset( $tax_data['total'][ $tax_item_id ] ) ? (float) $tax_data['total'][ $tax_item_id ] : 0;
}
$net_revenue = round( $order_item->get_total( 'edit' ), $decimals );
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' => wc_get_order_item_meta( $order_item_id, '_product_id' ),
'variation_id' => wc_get_order_item_meta( $order_item_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' );
}
}
Query.php 0000644 00000002352 15155362210 0006364 0 ustar 00 <?php
/**
* Class for parameter-based Products Report querying
*
* Example usage:
* $args = array(
* 'before' => '2018-07-19 00:00:00',
* 'after' => '2018-07-05 00:00:00',
* 'page' => 2,
* 'categories' => array(15, 18),
* 'products' => array(1,2,3)
* );
* $report = new \Automattic\WooCommerce\Admin\API\Reports\Products\Query( $args );
* $mydata = $report->get_data();
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\Query as ReportsQuery;
/**
* API\Reports\Products\Query
*/
class Query extends ReportsQuery {
/**
* Valid fields for Products report.
*
* @return array
*/
protected function get_default_query_vars() {
return array();
}
/**
* Get product data based on the current query vars.
*
* @return array
*/
public function get_data() {
$args = apply_filters( 'woocommerce_analytics_products_query_args', $this->get_query_vars() );
$data_store = \WC_Data_Store::load( 'report-products' );
$results = $data_store->get_data( $args );
return apply_filters( 'woocommerce_analytics_products_select_query', $results, $args );
}
}
Stats/Controller.php 0000644 00000017310 15155362210 0010500 0 ustar 00 <?php
/**
* REST API Reports products stats controller
*
* Handles requests to the /reports/products/stats endpoint.
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products\Stats;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\GenericStatsController;
use Automattic\WooCommerce\Admin\API\Reports\ParameterException;
use WP_REST_Request;
use WP_REST_Response;
/**
* REST API Reports products stats controller class.
*
* @internal
* @extends GenericStatsController
*/
class Controller extends GenericStatsController {
/**
* Route base.
*
* @var string
*/
protected $rest_base = 'reports/products/stats';
/**
* Mapping between external parameter name and name used in query class.
*
* @var array
*/
protected $param_mapping = array(
'categories' => 'category_includes',
'products' => 'product_includes',
'variations' => 'variation_includes',
);
/**
* Constructor.
*/
public function __construct() {
add_filter( 'woocommerce_analytics_products_stats_select_query', array( $this, 'set_default_report_data' ) );
}
/**
* Get all reports.
*
* @param WP_REST_Request $request Request data.
* @return array|WP_Error
*/
public function get_items( $request ) {
$query_args = array(
'fields' => array(
'items_sold',
'net_revenue',
'orders_count',
'products_count',
'variations_count',
),
);
$registered = array_keys( $this->get_collection_params() );
foreach ( $registered as $param_name ) {
if ( isset( $request[ $param_name ] ) ) {
if ( isset( $this->param_mapping[ $param_name ] ) ) {
$query_args[ $this->param_mapping[ $param_name ] ] = $request[ $param_name ];
} else {
$query_args[ $param_name ] = $request[ $param_name ];
}
}
}
$query = new Query( $query_args );
try {
$report_data = $query->get_data();
} catch ( ParameterException $e ) {
return new \WP_Error( $e->getErrorCode(), $e->getMessage(), array( 'status' => $e->getCode() ) );
}
$out_data = array(
'totals' => get_object_vars( $report_data->totals ),
'intervals' => array(),
);
foreach ( $report_data->intervals as $interval_data ) {
$item = $this->prepare_item_for_response( $interval_data, $request );
$out_data['intervals'][] = $this->prepare_response_for_collection( $item );
}
return $this->add_pagination_headers(
$request,
$out_data,
(int) $report_data->total,
(int) $report_data->page_no,
(int) $report_data->pages
);
}
/**
* Prepare a report object for serialization.
*
* @param array $report Report data.
* @param WP_REST_Request $request Request object.
* @return WP_REST_Response
*/
public function prepare_item_for_response( $report, $request ) {
$response = parent::prepare_item_for_response( $report, $request );
/**
* Filter a report returned from the API.
*
* Allows modification of the report data right before it is returned.
*
* @param WP_REST_Response $response The response object.
* @param object $report The original report object.
* @param WP_REST_Request $request Request used to generate the response.
*/
return apply_filters( 'woocommerce_rest_prepare_report_products_stats', $response, $report, $request );
}
/**
* Get the Report's item properties schema.
* Will be used by `get_item_schema` as `totals` and `subtotals`.
*
* @return array
*/
protected function get_item_properties_schema() {
return array(
'items_sold' => array(
'title' => __( 'Products sold', 'woocommerce' ),
'description' => __( 'Number of product items sold.', 'woocommerce' ),
'type' => 'integer',
'context' => array( 'view', 'edit' ),
'readonly' => true,
'indicator' => true,
),
'net_revenue' => array(
'description' => __( 'Net sales.', 'woocommerce' ),
'type' => 'number',
'context' => array( 'view', 'edit' ),
'readonly' => true,
'format' => 'currency',
),
'orders_count' => array(
'description' => __( 'Number of orders.', 'woocommerce' ),
'type' => 'integer',
'context' => array( 'view', 'edit' ),
'readonly' => true,
),
);
}
/**
* Get the Report's schema, conforming to JSON Schema.
*
* @return array
*/
public function get_item_schema() {
$schema = parent::get_item_schema();
$schema['title'] = 'report_products_stats';
$segment_label = array(
'description' => __( 'Human readable segment label, either product or variation name.', 'woocommerce' ),
'type' => 'string',
'context' => array( 'view', 'edit' ),
'readonly' => true,
'enum' => array( 'day', 'week', 'month', 'year' ),
);
$schema['properties']['totals']['properties']['segments']['items']['properties']['segment_label'] = $segment_label;
$schema['properties']['intervals']['items']['properties']['subtotals']['properties']['segments']['items']['properties']['segment_label'] = $segment_label;
return $this->add_additional_fields_schema( $schema );
}
/**
* Set the default results to 0 if API returns an empty array
*
* @internal
* @param Mixed $results Report data.
* @return object
*/
public function set_default_report_data( $results ) {
if ( empty( $results ) ) {
$results = new \stdClass();
$results->total = 0;
$results->totals = new \stdClass();
$results->totals->items_sold = 0;
$results->totals->net_revenue = 0;
$results->totals->orders_count = 0;
$results->intervals = array();
$results->pages = 1;
$results->page_no = 1;
}
return $results;
}
/**
* Get the query params for collections.
*
* @return array
*/
public function get_collection_params() {
$params = parent::get_collection_params();
$params['orderby']['enum'] = array(
'date',
'net_revenue',
'coupons',
'refunds',
'shipping',
'taxes',
'net_revenue',
'orders_count',
'items_sold',
);
$params['categories'] = array(
'description' => __( 'Limit result to items from the specified categories.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_id_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'integer',
),
);
$params['products'] = array(
'description' => __( 'Limit result to items with specified product ids.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_id_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'integer',
),
);
$params['variations'] = array(
'description' => __( 'Limit result to items with specified variation ids.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_id_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'integer',
),
);
$params['segmentby'] = array(
'description' => __( 'Segment the response by additional constraint.', 'woocommerce' ),
'type' => 'string',
'enum' => array(
'product',
'category',
'variation',
),
'validate_callback' => 'rest_validate_request_arg',
);
$params['fields'] = array(
'description' => __( 'Limit stats fields to the specified items.', 'woocommerce' ),
'type' => 'array',
'sanitize_callback' => 'wp_parse_slug_list',
'validate_callback' => 'rest_validate_request_arg',
'items' => array(
'type' => 'string',
),
);
return $params;
}
}
Stats/DataStore.php 0000644 00000023373 15155362210 0010251 0 ustar 00 <?php
/**
* API\Reports\Products\Stats\DataStore class file.
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products\Stats;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\Products\DataStore as ProductsDataStore;
use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface;
use Automattic\WooCommerce\Admin\API\Reports\TimeInterval;
use Automattic\WooCommerce\Admin\API\Reports\SqlQuery;
/**
* API\Reports\Products\Stats\DataStore.
*/
class DataStore extends ProductsDataStore implements DataStoreInterface {
/**
* Mapping columns to data type to return correct response 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',
'products_count' => 'intval',
'variations_count' => 'intval',
);
/**
* Cache identifier.
*
* @var string
*/
protected $cache_key = 'products_stats';
/**
* Data store context used to pass to filters.
*
* @var string
*/
protected $context = 'products_stats';
/**
* Assign report columns once full table name has been assigned.
*/
protected function assign_report_columns() {
$table_name = self::get_db_table_name();
$this->report_columns = array(
'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",
'products_count' => 'COUNT(DISTINCT product_id) as products_count',
'variations_count' => 'COUNT(DISTINCT variation_id) as variations_count',
);
}
/**
* Updates the database query with parameters used for Products Stats report: categories and order status.
*
* @param array $query_args Query arguments supplied by the user.
*/
protected function update_sql_query_params( $query_args ) {
global $wpdb;
$products_where_clause = '';
$products_from_clause = '';
$order_product_lookup_table = self::get_db_table_name();
$included_products = $this->get_included_products( $query_args );
if ( $included_products ) {
$products_where_clause .= " AND {$order_product_lookup_table}.product_id IN ({$included_products})";
}
$included_variations = $this->get_included_variations( $query_args );
if ( $included_variations ) {
$products_where_clause .= " AND {$order_product_lookup_table}.variation_id IN ({$included_variations})";
}
$order_status_filter = $this->get_status_subquery( $query_args );
if ( $order_status_filter ) {
$products_from_clause .= " JOIN {$wpdb->prefix}wc_order_stats ON {$order_product_lookup_table}.order_id = {$wpdb->prefix}wc_order_stats.order_id";
$products_where_clause .= " AND ( {$order_status_filter} )";
}
$this->add_time_period_sql_params( $query_args, $order_product_lookup_table );
$this->total_query->add_sql_clause( 'where', $products_where_clause );
$this->total_query->add_sql_clause( 'join', $products_from_clause );
$this->add_intervals_sql_params( $query_args, $order_product_lookup_table );
$this->interval_query->add_sql_clause( 'where', $products_where_clause );
$this->interval_query->add_sql_clause( 'join', $products_from_clause );
$this->interval_query->add_sql_clause( 'select', $this->get_sql_clause( 'select' ) . ' AS time_interval' );
}
/**
* Returns the report data based on parameters supplied by the user.
*
* @since 3.5.0
* @param array $query_args Query parameters.
* @return stdClass|WP_Error Data.
*/
public function get_data( $query_args ) {
global $wpdb;
$table_name = self::get_db_table_name();
// These defaults are only partially applied when used via REST API, as that has its own defaults.
$defaults = array(
'per_page' => get_option( 'posts_per_page' ),
'page' => 1,
'order' => 'DESC',
'orderby' => 'date',
'before' => TimeInterval::default_before(),
'after' => TimeInterval::default_after(),
'fields' => '*',
'category_includes' => array(),
'interval' => 'week',
'product_includes' => array(),
);
$query_args = wp_parse_args( $query_args, $defaults );
$this->normalize_timezones( $query_args, $defaults );
/*
* We need to get the cache key here because
* parent::update_intervals_sql_params() modifies $query_args.
*/
$cache_key = $this->get_cache_key( $query_args );
$data = $this->get_cached_data( $cache_key );
if ( false === $data ) {
$this->initialize_queries();
$selections = $this->selected_columns( $query_args );
$params = $this->get_limit_params( $query_args );
$this->update_sql_query_params( $query_args );
$this->get_limit_sql_params( $query_args );
$this->interval_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) );
$db_intervals = $wpdb->get_col(
$this->interval_query->get_query_statement()
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
$db_interval_count = count( $db_intervals );
$expected_interval_count = TimeInterval::intervals_between( $query_args['after'], $query_args['before'], $query_args['interval'] );
$total_pages = (int) ceil( $expected_interval_count / $params['per_page'] );
if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) {
return array();
}
$intervals = array();
$this->update_intervals_sql_params( $query_args, $db_interval_count, $expected_interval_count, $table_name );
$this->total_query->add_sql_clause( 'select', $selections );
$this->total_query->add_sql_clause( 'where_time', $this->get_sql_clause( 'where_time' ) );
$totals = $wpdb->get_results(
$this->total_query->get_query_statement(),
ARRAY_A
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
// @todo remove these assignements when refactoring segmenter classes to use query objects.
$totals_query = array(
'from_clause' => $this->total_query->get_sql_clause( 'join' ),
'where_time_clause' => $this->total_query->get_sql_clause( 'where_time' ),
'where_clause' => $this->total_query->get_sql_clause( 'where' ),
);
$intervals_query = array(
'select_clause' => $this->get_sql_clause( 'select' ),
'from_clause' => $this->interval_query->get_sql_clause( 'join' ),
'where_time_clause' => $this->interval_query->get_sql_clause( 'where_time' ),
'where_clause' => $this->interval_query->get_sql_clause( 'where' ),
'order_by' => $this->get_sql_clause( 'order_by' ),
'limit' => $this->get_sql_clause( 'limit' ),
);
$segmenter = new Segmenter( $query_args, $this->report_columns );
$totals[0]['segments'] = $segmenter->get_totals_segments( $totals_query, $table_name );
if ( null === $totals ) {
return new \WP_Error( 'woocommerce_analytics_products_stats_result_failed', __( 'Sorry, fetching revenue data failed.', 'woocommerce' ) );
}
$this->interval_query->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) );
$this->interval_query->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) );
$this->interval_query->add_sql_clause( 'select', ", MAX({$table_name}.date_created) AS datetime_anchor" );
if ( '' !== $selections ) {
$this->interval_query->add_sql_clause( 'select', ', ' . $selections );
}
$intervals = $wpdb->get_results(
$this->interval_query->get_query_statement(),
ARRAY_A
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
if ( null === $intervals ) {
return new \WP_Error( 'woocommerce_analytics_products_stats_result_failed', __( 'Sorry, fetching revenue data failed.', 'woocommerce' ) );
}
$totals = (object) $this->cast_numbers( $totals[0] );
$data = (object) array(
'totals' => $totals,
'intervals' => $intervals,
'total' => $expected_interval_count,
'pages' => $total_pages,
'page_no' => (int) $query_args['page'],
);
if ( TimeInterval::intervals_missing( $expected_interval_count, $db_interval_count, $params['per_page'], $query_args['page'], $query_args['order'], $query_args['orderby'], count( $intervals ) ) ) {
$this->fill_in_missing_intervals( $db_intervals, $query_args['adj_after'], $query_args['adj_before'], $query_args['interval'], $data );
$this->sort_intervals( $data, $query_args['orderby'], $query_args['order'] );
$this->remove_extra_records( $data, $query_args['page'], $params['per_page'], $db_interval_count, $expected_interval_count, $query_args['orderby'], $query_args['order'] );
} else {
$this->update_interval_boundary_dates( $query_args['after'], $query_args['before'], $query_args['interval'], $data->intervals );
}
$segmenter->add_intervals_segments( $data, $intervals_query, $table_name );
$this->create_interval_subtotals( $data->intervals );
$this->set_cached_data( $cache_key, $data );
}
return $data;
}
/**
* Normalizes order_by clause to match to SQL query.
*
* @param string $order_by Order by option requeste by user.
* @return string
*/
protected function normalize_order_by( $order_by ) {
if ( 'date' === $order_by ) {
return 'time_interval';
}
return $order_by;
}
/**
* Initialize query objects.
*/
protected function initialize_queries() {
$this->clear_all_clauses();
unset( $this->subquery );
$this->total_query = new SqlQuery( $this->context . '_total' );
$this->total_query->add_sql_clause( 'from', self::get_db_table_name() );
$this->interval_query = new SqlQuery( $this->context . '_interval' );
$this->interval_query->add_sql_clause( 'from', self::get_db_table_name() );
$this->interval_query->add_sql_clause( 'group_by', 'time_interval' );
}
}
Stats/Query.php 0000644 00000002425 15155362210 0007463 0 ustar 00 <?php
/**
* Class for parameter-based Products Stats Report querying
*
* Example usage:
* $args = array(
* 'before' => '2018-07-19 00:00:00',
* 'after' => '2018-07-05 00:00:00',
* 'page' => 2,
* 'categories' => array(15, 18),
* 'product_ids' => array(1,2,3)
* );
* $report = new \Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Query( $args );
* $mydata = $report->get_data();
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products\Stats;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\Query as ReportsQuery;
/**
* API\Reports\Products\Stats\Query
*/
class Query extends ReportsQuery {
/**
* Valid fields for Products report.
*
* @return array
*/
protected function get_default_query_vars() {
return array();
}
/**
* Get product data based on the current query vars.
*
* @return array
*/
public function get_data() {
$args = apply_filters( 'woocommerce_analytics_products_stats_query_args', $this->get_query_vars() );
$data_store = \WC_Data_Store::load( 'report-products-stats' );
$results = $data_store->get_data( $args );
return apply_filters( 'woocommerce_analytics_products_stats_select_query', $results, $args );
}
}
Stats/Segmenter.php 0000644 00000024312 15155362210 0010306 0 ustar 00 <?php
/**
* Class for adding segmenting support without cluttering the data stores.
*/
namespace Automattic\WooCommerce\Admin\API\Reports\Products\Stats;
defined( 'ABSPATH' ) || exit;
use Automattic\WooCommerce\Admin\API\Reports\Segmenter as ReportsSegmenter;
use Automattic\WooCommerce\Admin\API\Reports\ParameterException;
/**
* Date & time interval and numeric range handling class for Reporting API.
*/
class Segmenter extends ReportsSegmenter {
/**
* Returns column => query mapping to be used for product-related product-level segmenting query
* (e.g. products sold, revenue from product X when segmenting by category).
*
* @param string $products_table Name of SQL table containing the product-level segmenting info.
*
* @return array Column => SELECT query mapping.
*/
protected function get_segment_selections_product_level( $products_table ) {
$columns_mapping = array(
'items_sold' => "SUM($products_table.product_qty) as items_sold",
'net_revenue' => "SUM($products_table.product_net_revenue ) AS net_revenue",
'orders_count' => "COUNT( DISTINCT $products_table.order_id ) AS orders_count",
'products_count' => "COUNT( DISTINCT $products_table.product_id ) AS products_count",
'variations_count' => "COUNT( DISTINCT $products_table.variation_id ) AS variations_count",
);
return $columns_mapping;
}
/**
* Calculate segments for totals where the segmenting property is bound to product (e.g. category, product_id, variation_id).
*
* @param array $segmenting_selections SELECT part of segmenting SQL query--one for 'product_level' and one for 'order_level'.
* @param string $segmenting_from FROM part of segmenting SQL query.
* @param string $segmenting_where WHERE part of segmenting SQL query.
* @param string $segmenting_groupby GROUP BY part of segmenting SQL query.
* @param string $segmenting_dimension_name Name of the segmenting dimension.
* @param string $table_name Name of SQL table which is the stats table for orders.
* @param array $totals_query Array of SQL clauses for totals query.
* @param string $unique_orders_table Name of temporary SQL table that holds unique orders.
*
* @return array
*/
protected function get_product_related_totals_segments( $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $totals_query, $unique_orders_table ) {
global $wpdb;
$product_segmenting_table = $wpdb->prefix . 'wc_order_product_lookup';
// Can't get all the numbers from one query, so split it into one query for product-level numbers and one for order-level numbers (which first need to have orders uniqued).
// Product-level numbers.
$segments_products = $wpdb->get_results(
"SELECT
$segmenting_groupby AS $segmenting_dimension_name
{$segmenting_selections['product_level']}
FROM
$table_name
$segmenting_from
{$totals_query['from_clause']}
WHERE
1=1
{$totals_query['where_time_clause']}
{$totals_query['where_clause']}
$segmenting_where
GROUP BY
$segmenting_groupby",
ARRAY_A
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
$totals_segments = $this->merge_segment_totals_results( $segmenting_dimension_name, $segments_products, array() );
return $totals_segments;
}
/**
* Calculate segments for intervals where the segmenting property is bound to product (e.g. category, product_id, variation_id).
*
* @param array $segmenting_selections SELECT part of segmenting SQL query--one for 'product_level' and one for 'order_level'.
* @param string $segmenting_from FROM part of segmenting SQL query.
* @param string $segmenting_where WHERE part of segmenting SQL query.
* @param string $segmenting_groupby GROUP BY part of segmenting SQL query.
* @param string $segmenting_dimension_name Name of the segmenting dimension.
* @param string $table_name Name of SQL table which is the stats table for orders.
* @param array $intervals_query Array of SQL clauses for intervals query.
* @param string $unique_orders_table Name of temporary SQL table that holds unique orders.
*
* @return array
*/
protected function get_product_related_intervals_segments( $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $intervals_query, $unique_orders_table ) {
global $wpdb;
$product_segmenting_table = $wpdb->prefix . 'wc_order_product_lookup';
// LIMIT offset, rowcount needs to be updated to a multiple of the number of segments.
preg_match( '/LIMIT (\d+)\s?,\s?(\d+)/', $intervals_query['limit'], $limit_parts );
$segment_count = count( $this->get_all_segments() );
$orig_offset = intval( $limit_parts[1] );
$orig_rowcount = intval( $limit_parts[2] );
$segmenting_limit = $wpdb->prepare( 'LIMIT %d, %d', $orig_offset * $segment_count, $orig_rowcount * $segment_count );
// Can't get all the numbers from one query, so split it into one query for product-level numbers and one for order-level numbers (which first need to have orders uniqued).
// Product-level numbers.
$segments_products = $wpdb->get_results(
"SELECT
{$intervals_query['select_clause']} AS time_interval,
$segmenting_groupby AS $segmenting_dimension_name
{$segmenting_selections['product_level']}
FROM
$table_name
$segmenting_from
{$intervals_query['from_clause']}
WHERE
1=1
{$intervals_query['where_time_clause']}
{$intervals_query['where_clause']}
$segmenting_where
GROUP BY
time_interval, $segmenting_groupby
$segmenting_limit",
ARRAY_A
); // WPCS: cache ok, DB call ok, unprepared SQL ok.
$intervals_segments = $this->merge_segment_intervals_results( $segmenting_dimension_name, $segments_products, array() );
return $intervals_segments;
}
/**
* Return array of segments formatted for REST response.
*
* @param string $type Type of segments to return--'totals' or 'intervals'.
* @param array $query_params SQL query parameter array.
* @param string $table_name Name of main SQL table for the data store (used as basis for JOINS).
*
* @return array
* @throws \Automattic\WooCommerce\Admin\API\Reports\ParameterException In case of segmenting by variations, when no parent product is specified.
*/
protected function get_segments( $type, $query_params, $table_name ) {
global $wpdb;
if ( ! isset( $this->query_args['segmentby'] ) || '' === $this->query_args['segmentby'] ) {
return array();
}
$product_segmenting_table = $wpdb->prefix . 'wc_order_product_lookup';
$unique_orders_table = 'uniq_orders';
$segmenting_where = '';
// Product, variation, and category are bound to product, so here product segmenting table is required,
// while coupon and customer are bound to order, so we don't need the extra JOIN for those.
// This also means that segment selections need to be calculated differently.
if ( 'product' === $this->query_args['segmentby'] ) {
$product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table );
$segmenting_selections = array(
'product_level' => $this->prepare_selections( $product_level_columns ),
);
$this->report_columns = $product_level_columns;
$segmenting_from = '';
$segmenting_groupby = $product_segmenting_table . '.product_id';
$segmenting_dimension_name = 'product_id';
$segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table );
} elseif ( 'variation' === $this->query_args['segmentby'] ) {
if ( ! isset( $this->query_args['product_includes'] ) || count( $this->query_args['product_includes'] ) !== 1 ) {
throw new ParameterException( 'wc_admin_reports_invalid_segmenting_variation', __( 'product_includes parameter need to specify exactly one product when segmenting by variation.', 'woocommerce' ) );
}
$product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table );
$segmenting_selections = array(
'product_level' => $this->prepare_selections( $product_level_columns ),
);
$this->report_columns = $product_level_columns;
$segmenting_from = '';
$segmenting_where = "AND $product_segmenting_table.product_id = {$this->query_args['product_includes'][0]}";
$segmenting_groupby = $product_segmenting_table . '.variation_id';
$segmenting_dimension_name = 'variation_id';
$segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table );
} elseif ( 'category' === $this->query_args['segmentby'] ) {
$product_level_columns = $this->get_segment_selections_product_level( $product_segmenting_table );
$segmenting_selections = array(
'product_level' => $this->prepare_selections( $product_level_columns ),
);
$this->report_columns = $product_level_columns;
$segmenting_from = "
LEFT JOIN {$wpdb->term_relationships} ON {$product_segmenting_table}.product_id = {$wpdb->term_relationships}.object_id
JOIN {$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
LEFT JOIN {$wpdb->wc_category_lookup} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->wc_category_lookup}.category_id
";
$segmenting_where = " AND {$wpdb->wc_category_lookup}.category_tree_id IS NOT NULL";
$segmenting_groupby = "{$wpdb->wc_category_lookup}.category_tree_id";
$segmenting_dimension_name = 'category_id';
// Restrict our search space for category comparisons.
if ( isset( $this->query_args['category_includes'] ) ) {
$category_ids = implode( ',', $this->get_all_segments() );
$segmenting_where .= " AND {$wpdb->wc_category_lookup}.category_id IN ( $category_ids )";
}
$segments = $this->get_product_related_segments( $type, $segmenting_selections, $segmenting_from, $segmenting_where, $segmenting_groupby, $segmenting_dimension_name, $table_name, $query_params, $unique_orders_table );
}
return $segments;
}
}