Extended by reports to show charts and stats in admin.
/**
* Setup the report object and parse any date filtering.
*
* @param array $filter date filtering
*/
protected function setup_report($filter)
{
include_once WC()->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php';
include_once WC()->plugin_path() . '/includes/admin/reports/class-wc-report-sales-by-date.php';
$this->report = new WC_Report_Sales_By_Date();
if (empty($filter['period'])) {
// Custom date range.
$filter['period'] = 'custom';
if (!empty($filter['date_min']) || !empty($filter['date_max'])) {
// Overwrite _GET to make use of WC_Admin_Report::calculate_current_range() for custom date ranges.
$_GET['start_date'] = $filter['date_min'];
$_GET['end_date'] = isset($filter['date_max']) ? $filter['date_max'] : null;
} else {
// Default custom range to today.
$_GET['start_date'] = $_GET['end_date'] = date('Y-m-d', current_time('timestamp'));
}
} else {
$filter['period'] = empty($filter['period']) ? 'week' : $filter['period'];
// Change "week" period to "7day".
if ('week' === $filter['period']) {
$filter['period'] = '7day';
}
}
$this->report->calculate_current_range($filter['period']);
}
/**
* Show status widget.
*/
public function status_widget()
{
include_once dirname(__FILE__) . '/reports/class-wc-admin-report.php';
$reports = new WC_Admin_Report();
echo '<ul class="wc_status_list">';
if (current_user_can('view_woocommerce_reports') && ($report_data = $this->get_sales_report_data())) {
?>
<li class="sales-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&range=month');
?>
">
<?php
echo $reports->sales_sparkline('', max(7, date('d', current_time('timestamp'))));
?>
<?php
/* translators: %s: net sales */
printf(__('%s net sales this month', 'woocommerce'), '<strong>' . wc_price($report_data->net_sales) . '</strong>');
?>
</a>
</li>
<?php
}
if (current_user_can('view_woocommerce_reports') && ($top_seller = $this->get_top_seller()) && $top_seller->qty) {
?>
<li class="best-seller-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&report=sales_by_product&range=month&product_ids=' . $top_seller->product_id);
?>
">
<?php
echo $reports->sales_sparkline($top_seller->product_id, max(7, date('d', current_time('timestamp'))), 'count');
?>
<?php
/* translators: 1: top seller product title 2: top seller quantity */
printf(__('%1$s top seller this month (sold %2$d)', 'woocommerce'), '<strong>' . get_the_title($top_seller->product_id) . '</strong>', $top_seller->qty);
?>
</a>
</li>
<?php
}
$this->status_widget_order_rows();
$this->status_widget_stock_rows();
do_action('woocommerce_after_dashboard_status_widget', $reports);
echo '</ul>';
}
/**
* Show status widget
*/
public function status_widget()
{
global $wpdb;
include_once 'reports/class-wc-admin-report.php';
$reports = new WC_Admin_Report();
// Sales
$query = array();
$query['fields'] = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
$query['join'] = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id LEFT JOIN {$wpdb->posts} AS parent ON posts.post_parent = parent.ID";
$query['where'] = "WHERE posts.post_type IN ( '" . implode("','", array_merge(wc_get_order_types('sales-reports'), array('shop_order_refund'))) . "' ) ";
$query['where'] .= "AND posts.post_status IN ( 'wc-" . implode("','wc-", apply_filters('woocommerce_reports_order_statuses', array('completed', 'processing', 'on-hold'))) . "' ) ";
$query['where'] .= "AND ( parent.post_status IN ( 'wc-" . implode("','wc-", apply_filters('woocommerce_reports_order_statuses', array('completed', 'processing', 'on-hold'))) . "' ) OR parent.ID IS NULL ) ";
$query['where'] .= "AND postmeta.meta_key = '_order_total' ";
$query['where'] .= "AND posts.post_date >= '" . date('Y-m-01', current_time('timestamp')) . "' ";
$query['where'] .= "AND posts.post_date <= '" . date('Y-m-d H:i:s', current_time('timestamp')) . "' ";
$sales = $wpdb->get_var(implode(' ', apply_filters('woocommerce_dashboard_status_widget_sales_query', $query)));
// Get top seller
$query = array();
$query['fields'] = "SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id\n\t\t\tFROM {$wpdb->posts} as posts";
$query['join'] = "INNER JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_id ";
$query['join'] .= "INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id ";
$query['join'] .= "INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id ";
$query['where'] = "WHERE posts.post_type IN ( '" . implode("','", wc_get_order_types('order-count')) . "' ) ";
$query['where'] .= "AND posts.post_status IN ( 'wc-" . implode("','wc-", apply_filters('woocommerce_reports_order_statuses', array('completed', 'processing', 'on-hold'))) . "' ) ";
$query['where'] .= "AND order_item_meta.meta_key = '_qty' ";
$query['where'] .= "AND order_item_meta_2.meta_key = '_product_id' ";
$query['where'] .= "AND posts.post_date >= '" . date('Y-m-01', current_time('timestamp')) . "' ";
$query['where'] .= "AND posts.post_date <= '" . date('Y-m-d H:i:s', current_time('timestamp')) . "' ";
$query['groupby'] = "GROUP BY product_id";
$query['orderby'] = "ORDER BY qty DESC";
$query['limits'] = "LIMIT 1";
$top_seller = $wpdb->get_row(implode(' ', apply_filters('woocommerce_dashboard_status_widget_top_seller_query', $query)));
// Counts
$on_hold_count = 0;
$processing_count = 0;
foreach (wc_get_order_types('order-count') as $type) {
$counts = (array) wp_count_posts($type);
$on_hold_count += isset($counts['wc-on-hold']) ? $counts['wc-on-hold'] : 0;
$processing_count += isset($counts['wc-processing']) ? $counts['wc-processing'] : 0;
}
// Get products using a query - this is too advanced for get_posts :(
$stock = absint(max(get_option('woocommerce_notify_low_stock_amount'), 1));
$nostock = absint(max(get_option('woocommerce_notify_no_stock_amount'), 0));
$transient_name = 'wc_low_stock_count';
if (false === ($lowinstock_count = get_transient($transient_name))) {
$query_from = apply_filters('woocommerce_report_low_in_stock_query_from', "FROM {$wpdb->posts} as posts\n\t\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id\n\t\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta2 ON posts.ID = postmeta2.post_id\n\t\t\t\tWHERE 1=1\n\t\t\t\tAND posts.post_type IN ( 'product', 'product_variation' )\n\t\t\t\tAND posts.post_status = 'publish'\n\t\t\t\tAND postmeta2.meta_key = '_manage_stock' AND postmeta2.meta_value = 'yes'\n\t\t\t\tAND postmeta.meta_key = '_stock' AND CAST(postmeta.meta_value AS SIGNED) <= '{$stock}'\n\t\t\t\tAND postmeta.meta_key = '_stock' AND CAST(postmeta.meta_value AS SIGNED) > '{$nostock}'\n\t\t\t");
$lowinstock_count = absint($wpdb->get_var("SELECT COUNT( DISTINCT posts.ID ) {$query_from};"));
set_transient($transient_name, $lowinstock_count, DAY_IN_SECONDS * 30);
}
$transient_name = 'wc_outofstock_count';
if (false === ($outofstock_count = get_transient($transient_name))) {
$query_from = apply_filters('woocommerce_report_out_of_stock_query_from', "FROM {$wpdb->posts} as posts\n\t\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id\n\t\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta2 ON posts.ID = postmeta2.post_id\n\t\t\t\tWHERE 1=1\n\t\t\t\tAND posts.post_type IN ( 'product', 'product_variation' )\n\t\t\t\tAND posts.post_status = 'publish'\n\t\t\t\tAND postmeta2.meta_key = '_manage_stock' AND postmeta2.meta_value = 'yes'\n\t\t\t\tAND postmeta.meta_key = '_stock' AND CAST(postmeta.meta_value AS SIGNED) <= '{$nostock}'\n\t\t\t");
$outofstock_count = absint($wpdb->get_var("SELECT COUNT( DISTINCT posts.ID ) {$query_from};"));
set_transient($transient_name, $outofstock_count, DAY_IN_SECONDS * 30);
}
?>
<ul class="wc_status_list">
<li class="sales-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&range=month');
?>
">
<?php
echo $reports->sales_sparkline('', max(7, date('d', current_time('timestamp'))));
?>
<?php
printf(__("<strong>%s</strong> sales this month", 'woocommerce'), wc_price($sales));
?>
</a>
</li>
<?php
if ($top_seller && $top_seller->qty) {
?>
<li class="best-seller-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&report=sales_by_product&range=month&product_ids=' . $top_seller->product_id);
?>
">
<?php
echo $reports->sales_sparkline($top_seller->product_id, max(7, date('d', current_time('timestamp'))), 'count');
?>
<?php
printf(__("%s top seller this month (sold %d)", 'woocommerce'), "<strong>" . get_the_title($top_seller->product_id) . "</strong>", $top_seller->qty);
?>
</a>
</li>
<?php
}
?>
<li class="processing-orders">
<a href="<?php
echo admin_url('edit.php?post_status=wc-processing&post_type=shop_order');
?>
">
<?php
printf(_n("<strong>%s order</strong> awaiting processing", "<strong>%s orders</strong> awaiting processing", $processing_count, 'woocommerce'), $processing_count);
?>
</a>
</li>
<li class="on-hold-orders">
<a href="<?php
echo admin_url('edit.php?post_status=wc-on-hold&post_type=shop_order');
?>
">
<?php
printf(_n("<strong>%s order</strong> on-hold", "<strong>%s orders</strong> on-hold", $on_hold_count, 'woocommerce'), $on_hold_count);
?>
</a>
</li>
<li class="low-in-stock">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=stock&report=low_in_stock');
?>
">
<?php
printf(_n("<strong>%s product</strong> low in stock", "<strong>%s products</strong> low in stock", $lowinstock_count, 'woocommerce'), $lowinstock_count);
?>
</a>
</li>
<li class="out-of-stock">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=stock&report=out_of_stock');
?>
">
<?php
printf(_n("<strong>%s product</strong> out of stock", "<strong>%s products</strong> out of stock", $outofstock_count, 'woocommerce'), $outofstock_count);
?>
</a>
</li>
<?php
do_action('woocommerce_after_dashboard_status_widget', $reports);
?>
</ul>
<?php
}
/**
* Renders the sales widget in the dashboard.
* This method is an almost exact clone of global woocommerce_dashboard_status()
* function, with the main difference being that the correct totals in base
* currency are taken before being aggregated. Due to the lack of filters in
* the original function, the whole code had to be duplicated.
*/
public function woocommerce_dashboard_status_widget()
{
global $wpdb;
$wpdb->show_errors();
include_once $this->woocommerce_admin_path() . '/reports/class-wc-admin-report.php';
$reports = new \WC_Admin_Report();
// Get sales
$sales = $wpdb->get_var("SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts\r\n\t\t\tLEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID\r\n\t\t\tLEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )\r\n\t\t\tLEFT JOIN {$wpdb->terms} AS term USING( term_id )\r\n\t\t\tLEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id\r\n\t\t\tWHERE \tposts.post_type \t= 'shop_order'\r\n\t\t\tAND \tposts.post_status \t= 'publish'\r\n\t\t\tAND \ttax.taxonomy\t\t= 'shop_order_status'\r\n\t\t\tAND\t\tterm.slug\t\t\tIN ( 'completed', 'processing', 'on-hold' )\r\n\t\t\tAND \tpostmeta.meta_key = '_order_total_base_currency'\r\n\t\t\tAND \tposts.post_date >= '" . date('Y-m-01', current_time('timestamp')) . "'\r\n\t\t\tAND \tposts.post_date <= '" . date('Y-m-d H:i:s', current_time('timestamp')) . "'\r\n\t\t");
// Get top seller
$top_seller = $wpdb->get_row("SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id\r\n\t\t\tFROM {$wpdb->posts} as posts\r\n\t\t\tLEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID\r\n\t\t\tLEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )\r\n\t\t\tLEFT JOIN {$wpdb->terms} AS term USING( term_id )\r\n\t\t\tLEFT JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_id\r\n\t\t\tLEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id\r\n\t\t\tLEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id\r\n\t\t\tWHERE \tposts.post_type \t= 'shop_order'\r\n\t\t\tAND \tposts.post_status \t= 'publish'\r\n\t\t\tAND \ttax.taxonomy\t\t= 'shop_order_status'\r\n\t\t\tAND\t\tterm.slug\t\t\tIN ( 'completed', 'processing', 'on-hold' )\r\n\t\t\tAND \torder_item_meta.meta_key = '_qty'\r\n\t\t\tAND \torder_item_meta_2.meta_key = '_product_id'\r\n\t\t\tAND \tposts.post_date >= '" . date('Y-m-01', current_time('timestamp')) . "'\r\n\t\t\tAND \tposts.post_date <= '" . date('Y-m-d H:i:s', current_time('timestamp')) . "'\r\n\t\t\tGROUP BY product_id\r\n\t\t\tORDER BY qty DESC\r\n\t\t\tLIMIT 1\r\n\t\t");
// Counts
$on_hold_count = get_term_by('slug', 'on-hold', 'shop_order_status')->count;
$processing_count = get_term_by('slug', 'processing', 'shop_order_status')->count;
// Get products using a query - this is too advanced for get_posts :(
$stock = absint(max(get_option('woocommerce_notify_low_stock_amount'), 1));
$nostock = absint(max(get_option('woocommerce_notify_no_stock_amount'), 0));
$query_from = "FROM {$wpdb->posts} as posts\r\n\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id\r\n\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta2 ON posts.ID = postmeta2.post_id\r\n\t\t\tWHERE 1=1\r\n\t\t\t\tAND posts.post_type IN ('product', 'product_variation')\r\n\t\t\t\tAND posts.post_status = 'publish'\r\n\t\t\t\tAND (\r\n\t\t\t\t\tpostmeta.meta_key = '_stock' AND CAST(postmeta.meta_value AS SIGNED) <= '{$stock}' AND CAST(postmeta.meta_value AS SIGNED) > '{$nostock}' AND postmeta.meta_value != ''\r\n\t\t\t\t)\r\n\t\t\t\tAND (\r\n\t\t\t\t\t( postmeta2.meta_key = '_manage_stock' AND postmeta2.meta_value = 'yes' ) OR ( posts.post_type = 'product_variation' )\r\n\t\t\t\t)\r\n\t\t\t";
$lowinstock_count = absint($wpdb->get_var("SELECT COUNT( DISTINCT posts.ID ) {$query_from};"));
$query_from = "FROM {$wpdb->posts} as posts\r\n\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id\r\n\t\t\tINNER JOIN {$wpdb->postmeta} AS postmeta2 ON posts.ID = postmeta2.post_id\r\n\t\t\tWHERE 1=1\r\n\t\t\t\tAND posts.post_type IN ('product', 'product_variation')\r\n\t\t\t\tAND posts.post_status = 'publish'\r\n\t\t\t\tAND (\r\n\t\t\t\t\tpostmeta.meta_key = '_stock' AND CAST(postmeta.meta_value AS SIGNED) <= '{$stock}' AND postmeta.meta_value != ''\r\n\t\t\t\t)\r\n\t\t\t\tAND (\r\n\t\t\t\t\t( postmeta2.meta_key = '_manage_stock' AND postmeta2.meta_value = 'yes' ) OR ( posts.post_type = 'product_variation' )\r\n\t\t\t\t)\r\n\t\t\t";
$outofstock_count = absint($wpdb->get_var("SELECT COUNT( DISTINCT posts.ID ) {$query_from};"));
?>
<ul class="wc_status_list">
<li class="sales-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&range=month');
?>
">
<?php
echo $reports->sales_sparkline('', max(7, date('d', current_time('timestamp'))));
?>
<?php
printf(__("<strong>%s</strong> sales this month", 'woocommerce'), wc_price($sales));
?>
</a>
</li>
<?php
if ($top_seller && $top_seller->qty) {
?>
<li class="best-seller-this-month">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=orders&report=sales_by_product&range=month&product_ids=' . $top_seller->product_id);
?>
">
<?php
echo $reports->sales_sparkline($top_seller->product_id, max(7, date('d', current_time('timestamp'))), 'count');
?>
<?php
printf(__("%s top seller this month (sold %d)", 'woocommerce'), "<strong>" . get_the_title($top_seller->product_id) . "</strong>", $top_seller->qty);
?>
</a>
</li>
<?php
}
?>
<li class="processing-orders">
<a href="<?php
echo admin_url('edit.php?s&post_status=all&post_type=shop_order&shop_order_status=processing');
?>
">
<?php
printf(_n("<strong>%s order</strong> awaiting processing", "<strong>%s orders</strong> awaiting processing", $processing_count, 'woocommerce'), $processing_count);
?>
</a>
</li>
<li class="on-hold-orders">
<a href="<?php
echo admin_url('edit.php?s&post_status=all&post_type=shop_order&shop_order_status=on-hold');
?>
">
<?php
printf(_n("<strong>%s order</strong> on-hold", "<strong>%s orders</strong> on-hold", $on_hold_count, 'woocommerce'), $on_hold_count);
?>
</a>
</li>
<li class="low-in-stock">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=stock&report=low_in_stock');
?>
">
<?php
printf(_n("<strong>%s product</strong> low in stock", "<strong>%s products</strong> low in stock", $lowinstock_count, 'woocommerce'), $lowinstock_count);
?>
</a>
</li>
<li class="out-of-stock">
<a href="<?php
echo admin_url('admin.php?page=wc-reports&tab=stock&report=out_of_stock');
?>
">
<?php
printf(_n("<strong>%s product</strong> out of stock", "<strong>%s products</strong> out of stock", $outofstock_count, 'woocommerce'), $outofstock_count);
?>
</a>
</li>
</ul>
<?php
}
function hm_wcst_get_product_sales($productIds, $metaKey, $periodStart, $periodEnd)
{
global $woocommerce;
$salesQuantities = array_combine($productIds, array_fill(0, count($productIds), 0));
include_once $woocommerce->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php';
$wc_report = new WC_Admin_Report();
$wc_report->start_date = $periodStart;
$wc_report->end_date = $periodEnd;
// Based on woocoommerce/includes/admin/reports/class-wc-report-sales-by-product.php
$soldProducts = $wc_report->get_order_report_data(array('data' => array('_product_id' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'product_id'), '_qty' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => 'SUM', 'name' => 'quantity')), 'query_type' => 'get_results', 'group_by' => 'product_id', 'limit' => '', 'filter_range' => true, 'order_types' => wc_get_order_types('order_count')));
foreach ($soldProducts as $product) {
if (isset($salesQuantities[$product->product_id])) {
$salesQuantities[$product->product_id] = $product->quantity;
}
}
/*
foreach ($salesQuantities as $productId => $quantity) {
update_post_meta($productId, $metaKey, $quantity);
}
*/
return $salesQuantities;
}
function hm_sbp_export_body($dest, $return = false)
{
global $woocommerce, $wpdb;
$product_ids = array();
if ($_POST['products'] == 'cats') {
$cats = array();
foreach ($_POST['product_cats'] as $cat) {
if (is_numeric($cat)) {
$cats[] = $cat;
}
}
$product_ids = get_objects_in_term($cats, 'product_cat');
} else {
if ($_POST['products'] == 'ids') {
foreach (explode(',', $_POST['product_ids']) as $productId) {
$productId = trim($productId);
if (is_numeric($productId)) {
$product_ids[] = $productId;
}
}
}
}
// Calculate report start and end dates (timestamps)
switch ($_POST['report_time']) {
case '0d':
$end_date = strtotime('midnight', current_time('timestamp'));
$start_date = $end_date;
break;
case '1d':
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date;
break;
case '7d':
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date - 86400 * 7;
break;
case 'custom':
$end_date = strtotime('midnight', strtotime($_POST['report_end']));
$start_date = strtotime('midnight', strtotime($_POST['report_start']));
break;
default:
// 30 days is the default
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date - 86400 * 30;
}
// Assemble order by string
$orderby = in_array($_POST['orderby'], array('product_id', 'gross', 'gross_after_discount')) ? $_POST['orderby'] : 'quantity';
$orderby .= ' ' . ($_POST['orderdir'] == 'asc' ? 'ASC' : 'DESC');
// Create a new WC_Admin_Report object
include_once $woocommerce->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php';
$wc_report = new WC_Admin_Report();
$wc_report->start_date = $start_date;
$wc_report->end_date = $end_date;
$where_meta = array();
if ($_POST['products'] != 'all') {
$where_meta[] = array('type' => 'order_item_meta', 'meta_key' => '_product_id', 'operator' => 'in', 'meta_value' => $product_ids);
}
if (!empty($_POST['exclude_free'])) {
$where_meta[] = array('meta_key' => '_line_total', 'meta_value' => 0, 'operator' => '!=', 'type' => 'order_item_meta');
}
// Get report data
// Avoid max join size error
$wpdb->query('SET SQL_BIG_SELECTS=1');
// Prevent plugins from overriding the order status filter
add_filter('woocommerce_reports_order_statuses', 'hm_psr_report_order_statuses', 9999);
// Based on woocoommerce/includes/admin/reports/class-wc-report-sales-by-product.php
$sold_products = $wc_report->get_order_report_data(array('data' => array('_product_id' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'product_id'), '_qty' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => 'SUM', 'name' => 'quantity'), '_line_subtotal' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => 'SUM', 'name' => 'gross'), '_line_total' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => 'SUM', 'name' => 'gross_after_discount')), 'query_type' => 'get_results', 'group_by' => 'product_id', 'where_meta' => $where_meta, 'order_by' => $orderby, 'limit' => !empty($_POST['limit_on']) && is_numeric($_POST['limit']) ? $_POST['limit'] : '', 'filter_range' => $_POST['report_time'] != 'all', 'order_types' => wc_get_order_types('order_count'), 'order_status' => hm_psr_report_order_statuses()));
// Remove report order statuses filter
remove_filter('woocommerce_reports_order_statuses', 'hm_psr_report_order_statuses', 9999);
if ($return) {
$rows = array();
}
// Output report rows
foreach ($sold_products as $product) {
$row = array();
foreach ($_POST['fields'] as $field) {
switch ($field) {
case 'product_id':
$row[] = $product->product_id;
break;
case 'variation_id':
$row[] = empty($product->variation_id) ? '' : $product->variation_id;
break;
case 'product_sku':
$row[] = get_post_meta($product->product_id, '_sku', true);
break;
case 'product_name':
$row[] = html_entity_decode(get_the_title($product->product_id));
break;
case 'quantity_sold':
$row[] = $product->quantity;
break;
case 'gross_sales':
$row[] = $product->gross;
break;
case 'gross_after_discount':
$row[] = $product->gross_after_discount;
break;
case 'product_categories':
$terms = get_the_terms($product->product_id, 'product_cat');
if (empty($terms)) {
$row[] = '';
} else {
$categories = array();
foreach ($terms as $term) {
$categories[] = $term->name;
}
$row[] = implode(', ', $categories);
}
break;
}
}
if ($return) {
$rows[] = $row;
} else {
fputcsv($dest, $row);
}
}
if ($return) {
return $rows;
}
}
function hm_xoiwc_export_body($dest, $return = false)
{
global $woocommerce, $wpdb;
// Calculate report start and end dates (timestamps)
switch ($_POST['report_time']) {
case '0d':
$end_date = strtotime('midnight', current_time('timestamp'));
$start_date = $end_date;
break;
case '1d':
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date;
break;
case '7d':
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date - 86400 * 7;
break;
case 'custom':
$end_date = strtotime('midnight', strtotime($_POST['report_end']));
$start_date = strtotime('midnight', strtotime($_POST['report_start']));
break;
default:
// 30 days is the default
$end_date = strtotime('midnight', current_time('timestamp')) - 86400;
$start_date = $end_date - 86400 * 30;
}
// Assemble order by string
$orderby = in_array($_POST['orderby'], array('order_id')) ? $_POST['orderby'] : 'product_id';
$orderby .= ' ' . ($_POST['orderdir'] == 'asc' ? 'ASC' : 'DESC');
// Create a new WC_Admin_Report object
include_once $woocommerce->plugin_path() . '/includes/admin/reports/class-wc-admin-report.php';
$wc_report = new WC_Admin_Report();
$wc_report->start_date = $start_date;
$wc_report->end_date = $end_date;
// Get report data
$reportData = array('_product_id' => array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'product_id'), 'order_id' => array('type' => 'order_item', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'order_id'));
if (in_array('quantity', $_POST['fields'])) {
$reportData['_qty'] = array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'quantity');
}
if (in_array('line_subtotal', $_POST['fields'])) {
$reportData['_line_subtotal'] = array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'line_subtotal');
}
if (in_array('line_total', $_POST['fields'])) {
$reportData['_line_total'] = array('type' => 'order_item_meta', 'order_item_type' => 'line_item', 'function' => '', 'name' => 'line_total');
}
if (in_array('order_status', $_POST['fields'])) {
$reportData['post_status'] = array('type' => 'post_data', 'name' => 'order_status', 'function' => '');
}
if (in_array('order_date', $_POST['fields'])) {
$reportData['post_date'] = array('type' => 'post_data', 'name' => 'order_date', 'function' => '');
}
if (in_array('billing_name', $_POST['fields'])) {
$reportData['_billing_first_name'] = array('type' => 'meta', 'name' => 'billing_first_name', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_last_name'] = array('type' => 'meta', 'name' => 'billing_last_name', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('billing_phone', $_POST['fields'])) {
$reportData['_billing_phone'] = array('type' => 'meta', 'name' => 'billing_phone', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('billing_email', $_POST['fields'])) {
$reportData['_billing_email'] = array('type' => 'meta', 'name' => 'billing_email', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('billing_address', $_POST['fields'])) {
$reportData['_billing_address_1'] = array('type' => 'meta', 'name' => 'billing_address_1', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_address_2'] = array('type' => 'meta', 'name' => 'billing_address_2', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_city'] = array('type' => 'meta', 'name' => 'billing_city', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_state'] = array('type' => 'meta', 'name' => 'billing_state', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_postcode'] = array('type' => 'meta', 'name' => 'billing_postcode', 'join_type' => 'LEFT', 'function' => '');
$reportData['_billing_country'] = array('type' => 'meta', 'name' => 'billing_country', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('shipping_name', $_POST['fields'])) {
$reportData['_shipping_first_name'] = array('type' => 'meta', 'name' => 'shipping_first_name', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_last_name'] = array('type' => 'meta', 'name' => 'shipping_last_name', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('shipping_phone', $_POST['fields'])) {
$reportData['_shipping_phone'] = array('type' => 'meta', 'name' => 'shipping_phone', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('shipping_email', $_POST['fields'])) {
$reportData['_shipping_email'] = array('type' => 'meta', 'name' => 'shipping_email', 'join_type' => 'LEFT', 'function' => '');
}
if (in_array('shipping_address', $_POST['fields'])) {
$reportData['_shipping_address_1'] = array('type' => 'meta', 'name' => 'shipping_address_1', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_address_2'] = array('type' => 'meta', 'name' => 'shipping_address_2', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_city'] = array('type' => 'meta', 'name' => 'shipping_city', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_state'] = array('type' => 'meta', 'name' => 'shipping_state', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_postcode'] = array('type' => 'meta', 'name' => 'shipping_postcode', 'join_type' => 'LEFT', 'function' => '');
$reportData['_shipping_country'] = array('type' => 'meta', 'name' => 'shipping_country', 'join_type' => 'LEFT', 'function' => '');
}
// Avoid max join size error
$wpdb->query('SET SQL_BIG_SELECTS=1');
// Prevent plugins from overriding the order status filter
add_filter('woocommerce_reports_order_statuses', 'hm_xoiwc_report_order_statuses', 9999);
// Based on woocoommerce/includes/admin/reports/class-wc-report-sales-by-product.php
$sold_products = $wc_report->get_order_report_data(array('data' => $reportData, 'query_type' => 'get_results', 'group_by' => '', 'order_by' => $orderby, 'limit' => !empty($_POST['limit_on']) && is_numeric($_POST['limit']) ? $_POST['limit'] : '', 'filter_range' => $_POST['report_time'] != 'all', 'order_types' => wc_get_order_types('order_count'), 'order_status' => hm_xoiwc_report_order_statuses()));
// Remove report order statuses filter
remove_filter('woocommerce_reports_order_statuses', 'hm_xoiwc_report_order_statuses', 9999);
// Output report rows
foreach ($sold_products as $product) {
$row = array();
foreach ($_POST['fields'] as $field) {
switch ($field) {
case 'product_id':
$row[] = $product->product_id;
break;
case 'order_id':
$row[] = $product->order_id;
break;
case 'order_status':
$row[] = wc_get_order_status_name($product->order_status);
break;
case 'order_date':
$row[] = $product->order_date;
break;
case 'product_sku':
$row[] = get_post_meta($product->product_id, '_sku', true);
break;
case 'product_name':
$row[] = html_entity_decode(get_the_title($product->product_id));
break;
case 'product_categories':
$terms = get_the_terms($product->product_id, 'product_cat');
if (empty($terms)) {
$row[] = '';
} else {
$categories = array();
foreach ($terms as $term) {
$categories[] = $term->name;
}
$row[] = implode(', ', $categories);
}
break;
case 'billing_name':
$row[] = $product->billing_first_name . ' ' . $product->billing_last_name;
break;
case 'billing_phone':
$row[] = $product->billing_phone;
break;
case 'billing_email':
$row[] = $product->billing_email;
break;
case 'billing_address':
$addressComponents = array();
if (!empty($product->billing_address_1)) {
$addressComponents[] = $product->billing_address_1;
}
if (!empty($product->billing_address_2)) {
$addressComponents[] = $product->billing_address_2;
}
if (!empty($product->billing_city)) {
$addressComponents[] = $product->billing_city;
}
if (!empty($product->billing_state)) {
$addressComponents[] = $product->billing_state;
}
if (!empty($product->billing_postcode)) {
$addressComponents[] = $product->billing_postcode;
}
if (!empty($product->billing_country)) {
$addressComponents[] = $product->billing_country;
}
$row[] = implode(', ', $addressComponents);
break;
case 'shipping_name':
$row[] = $product->shipping_first_name . ' ' . $product->shipping_last_name;
break;
case 'shipping_phone':
$row[] = $product->shipping_phone;
break;
case 'shipping_email':
$row[] = $product->shipping_email;
break;
case 'shipping_address':
$addressComponents = array();
if (!empty($product->shipping_address_1)) {
$addressComponents[] = $product->shipping_address_1;
}
if (!empty($product->shipping_address_2)) {
$addressComponents[] = $product->shipping_address_2;
}
if (!empty($product->shipping_city)) {
$addressComponents[] = $product->shipping_city;
}
if (!empty($product->shipping_state)) {
$addressComponents[] = $product->shipping_state;
}
if (!empty($product->shipping_postcode)) {
$addressComponents[] = $product->shipping_postcode;
}
if (!empty($product->shipping_country)) {
$addressComponents[] = $product->shipping_country;
}
$row[] = implode(', ', $addressComponents);
break;
case 'quantity':
$row[] = $product->quantity;
break;
case 'line_subtotal':
$row[] = $product->line_subtotal;
break;
case 'line_total':
$row[] = $product->line_total;
break;
}
}
if ($return) {
$rows[] = $row;
} else {
fputcsv($dest, $row);
}
}
if ($return) {
return $rows;
}
}
/**
* Get report totals such as order totals and discount amounts.
* IMPORTANT: to get a correct total amount, totals fields should be replaced
* by their "_base_currency" counterpart, as follows:
* _order_total -> _order_total_base_currency
* _order_discount -> _order_discount_base_currency
* _cart_discount -> _cart_discount_base_currency
* _order_shipping -> _order_shipping_base_currency
*
* Data example:
*
* '_order_total_base_currency' => array(
* 'type' => 'meta',
* 'function' => 'SUM',
* 'name' => 'total_sales'
* )
*
* @param array args The parameters to prepare the report.
* @return array|string depending on query_type
*/
public function get_order_report_data($args = array())
{
// Just call parent method. This method exists mainly to allow documenting it,
// in order to highlight the importance of using the "*_base_currency" fields
return parent::get_order_report_data($args);
}
