Alert: This function’s access is marked private. This means it is not intended for use by plugin or theme developers, only in other core functions. It is listed here for completeness.
WC_Report_Sales_By_Date::query_report_data()
Get all data needed for this report and store in the class.
Description Description
Source Source
File: includes/admin/reports/class-wc-report-sales-by-date.php
private function query_report_data() {
$this->report_data = new stdClass();
$this->report_data->order_counts = (array) $this->get_order_report_data(
array(
'data' => array(
'ID' => array(
'type' => 'post_data',
'function' => 'COUNT',
'name' => 'count',
'distinct' => true,
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
),
'group_by' => $this->group_by_query,
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_types' => wc_get_order_types( 'order-count' ),
'order_status' => array( 'completed', 'processing', 'on-hold', 'refunded' ),
)
);
$this->report_data->coupons = (array) $this->get_order_report_data(
array(
'data' => array(
'order_item_name' => array(
'type' => 'order_item',
'function' => '',
'name' => 'order_item_name',
),
'discount_amount' => array(
'type' => 'order_item_meta',
'order_item_type' => 'coupon',
'function' => 'SUM',
'name' => 'discount_amount',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
),
'where' => array(
array(
'key' => 'order_items.order_item_type',
'value' => 'coupon',
'operator' => '=',
),
),
'group_by' => $this->group_by_query . ', order_item_name',
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_types' => wc_get_order_types( 'order-count' ),
'order_status' => array( 'completed', 'processing', 'on-hold', 'refunded' ),
)
);
// All items from orders - even those refunded.
$this->report_data->order_items = (array) $this->get_order_report_data(
array(
'data' => array(
'_qty' => array(
'type' => 'order_item_meta',
'order_item_type' => 'line_item',
'function' => 'SUM',
'name' => 'order_item_count',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
),
'where' => array(
array(
'key' => 'order_items.order_item_type',
'value' => 'line_item',
'operator' => '=',
),
),
'group_by' => $this->group_by_query,
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_types' => wc_get_order_types( 'order-count' ),
'order_status' => array( 'completed', 'processing', 'on-hold', 'refunded' ),
)
);
/**
* Get total of fully refunded items.
*/
$this->report_data->refunded_order_items = absint(
$this->get_order_report_data(
array(
'data' => array(
'_qty' => array(
'type' => 'order_item_meta',
'order_item_type' => 'line_item',
'function' => 'SUM',
'name' => 'order_item_count',
),
),
'where' => array(
array(
'key' => 'order_items.order_item_type',
'value' => 'line_item',
'operator' => '=',
),
),
'query_type' => 'get_var',
'filter_range' => true,
'order_types' => wc_get_order_types( 'order-count' ),
'order_status' => array( 'refunded' ),
)
)
);
/**
* Order totals by date. Charts should show GROSS amounts to avoid going -ve.
*/
$this->report_data->orders = (array) $this->get_order_report_data(
array(
'data' => array(
'_order_total' => array(
'type' => 'meta',
'function' => 'SUM',
'name' => 'total_sales',
),
'_order_shipping' => array(
'type' => 'meta',
'function' => 'SUM',
'name' => 'total_shipping',
),
'_order_tax' => array(
'type' => 'meta',
'function' => 'SUM',
'name' => 'total_tax',
),
'_order_shipping_tax' => array(
'type' => 'meta',
'function' => 'SUM',
'name' => 'total_shipping_tax',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
),
'group_by' => $this->group_by_query,
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_types' => wc_get_order_types( 'sales-reports' ),
'order_status' => array( 'completed', 'processing', 'on-hold', 'refunded' ),
)
);
/**
* If an order is 100% refunded we should look at the parent's totals, but the refunds dates.
* We also need to ensure each parent order's values are only counted/summed once.
*/
$this->report_data->full_refunds = (array) $this->get_order_report_data(
array(
'data' => array(
'_order_total' => array(
'type' => 'parent_meta',
'function' => '',
'name' => 'total_refund',
),
'_order_shipping' => array(
'type' => 'parent_meta',
'function' => '',
'name' => 'total_shipping',
),
'_order_tax' => array(
'type' => 'parent_meta',
'function' => '',
'name' => 'total_tax',
),
'_order_shipping_tax' => array(
'type' => 'parent_meta',
'function' => '',
'name' => 'total_shipping_tax',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
),
'group_by' => 'posts.post_parent',
'query_type' => 'get_results',
'filter_range' => true,
'order_status' => false,
'parent_order_status' => array( 'refunded' ),
)
);
foreach ( $this->report_data->full_refunds as $key => $order ) {
$this->report_data->full_refunds[ $key ]->net_refund = $order->total_refund - ( $order->total_shipping + $order->total_tax + $order->total_shipping_tax );
}
/**
* Partial refunds. This includes line items, shipping and taxes. Not grouped by date.
*/
$this->report_data->partial_refunds = (array) $this->get_order_report_data(
array(
'data' => array(
'ID' => array(
'type' => 'post_data',
'function' => '',
'name' => 'refund_id',
),
'_refund_amount' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_refund',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
'order_item_type' => array(
'type' => 'order_item',
'function' => '',
'name' => 'item_type',
'join_type' => 'LEFT',
),
'_order_total' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_sales',
),
'_order_shipping' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_shipping',
'join_type' => 'LEFT',
),
'_order_tax' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_tax',
'join_type' => 'LEFT',
),
'_order_shipping_tax' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_shipping_tax',
'join_type' => 'LEFT',
),
'_qty' => array(
'type' => 'order_item_meta',
'function' => 'SUM',
'name' => 'order_item_count',
'join_type' => 'LEFT',
),
),
'group_by' => 'refund_id',
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_status' => false,
'parent_order_status' => array( 'completed', 'processing', 'on-hold' ),
)
);
foreach ( $this->report_data->partial_refunds as $key => $order ) {
$this->report_data->partial_refunds[ $key ]->net_refund = $order->total_refund - ( $order->total_shipping + $order->total_tax + $order->total_shipping_tax );
}
/**
* Refund lines - all partial refunds on all order types so we can plot full AND partial refunds on the chart.
*/
$this->report_data->refund_lines = (array) $this->get_order_report_data(
array(
'data' => array(
'ID' => array(
'type' => 'post_data',
'function' => '',
'name' => 'refund_id',
),
'_refund_amount' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_refund',
),
'post_date' => array(
'type' => 'post_data',
'function' => '',
'name' => 'post_date',
),
'order_item_type' => array(
'type' => 'order_item',
'function' => '',
'name' => 'item_type',
'join_type' => 'LEFT',
),
'_order_total' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_sales',
),
'_order_shipping' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_shipping',
'join_type' => 'LEFT',
),
'_order_tax' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_tax',
'join_type' => 'LEFT',
),
'_order_shipping_tax' => array(
'type' => 'meta',
'function' => '',
'name' => 'total_shipping_tax',
'join_type' => 'LEFT',
),
'_qty' => array(
'type' => 'order_item_meta',
'function' => 'SUM',
'name' => 'order_item_count',
'join_type' => 'LEFT',
),
),
'group_by' => 'refund_id',
'order_by' => 'post_date ASC',
'query_type' => 'get_results',
'filter_range' => true,
'order_status' => false,
'parent_order_status' => array( 'completed', 'processing', 'on-hold', 'refunded' ),
)
);
/**
* Total up refunds. Note: when an order is fully refunded, a refund line will be added.
*/
$this->report_data->total_tax_refunded = 0;
$this->report_data->total_shipping_refunded = 0;
$this->report_data->total_shipping_tax_refunded = 0;
$this->report_data->total_refunds = 0;
$this->report_data->refunded_orders = array_merge( $this->report_data->partial_refunds, $this->report_data->full_refunds );
foreach ( $this->report_data->refunded_orders as $key => $value ) {
$this->report_data->total_tax_refunded += floatval( $value->total_tax < 0 ? $value->total_tax * -1 : $value->total_tax );
$this->report_data->total_refunds += floatval( $value->total_refund );
$this->report_data->total_shipping_tax_refunded += floatval( $value->total_shipping_tax < 0 ? $value->total_shipping_tax * -1 : $value->total_shipping_tax );
$this->report_data->total_shipping_refunded += floatval( $value->total_shipping < 0 ? $value->total_shipping * -1 : $value->total_shipping );
// Only applies to parial.
if ( isset( $value->order_item_count ) ) {
$this->report_data->refunded_order_items += floatval( $value->order_item_count < 0 ? $value->order_item_count * -1 : $value->order_item_count );
}
}
// Totals from all orders - including those refunded. Subtract refunded amounts.
$this->report_data->total_tax = wc_format_decimal( array_sum( wp_list_pluck( $this->report_data->orders, 'total_tax' ) ) - $this->report_data->total_tax_refunded, 2 );
$this->report_data->total_shipping = wc_format_decimal( array_sum( wp_list_pluck( $this->report_data->orders, 'total_shipping' ) ) - $this->report_data->total_shipping_refunded, 2 );
$this->report_data->total_shipping_tax = wc_format_decimal( array_sum( wp_list_pluck( $this->report_data->orders, 'total_shipping_tax' ) ) - $this->report_data->total_shipping_tax_refunded, 2 );
// Total the refunds and sales amounts. Sales subract refunds. Note - total_sales also includes shipping costs.
$this->report_data->total_sales = wc_format_decimal( array_sum( wp_list_pluck( $this->report_data->orders, 'total_sales' ) ) - $this->report_data->total_refunds, 2 );
$this->report_data->net_sales = wc_format_decimal( $this->report_data->total_sales - $this->report_data->total_shipping - max( 0, $this->report_data->total_tax ) - max( 0, $this->report_data->total_shipping_tax ), 2 );
// Calculate average based on net.
$this->report_data->average_sales = wc_format_decimal( $this->report_data->net_sales / ( $this->chart_interval + 1 ), 2 );
$this->report_data->average_total_sales = wc_format_decimal( $this->report_data->total_sales / ( $this->chart_interval + 1 ), 2 );
// Total orders and discounts also includes those which have been refunded at some point.
$this->report_data->total_coupons = number_format( array_sum( wp_list_pluck( $this->report_data->coupons, 'discount_amount' ) ), 2, '.', '' );
$this->report_data->total_refunded_orders = absint( count( $this->report_data->full_refunds ) );
// Total orders in this period, even if refunded.
$this->report_data->total_orders = absint( array_sum( wp_list_pluck( $this->report_data->order_counts, 'count' ) ) );
// Item items ordered in this period, even if refunded.
$this->report_data->total_items = absint( array_sum( wp_list_pluck( $this->report_data->order_items, 'order_item_count' ) ) );
// 3rd party filtering of report data
$this->report_data = apply_filters( 'woocommerce_admin_report_data', $this->report_data );
}