Reputation: 855
I need to get each product sell report based on data range. That means I'll input the product id(or ids) as well as start date and end date and the function will return me the number of that product's sell within this (start date and end date) period of time. So I tried with WC_Admin_Report
and WC_Report_Sales_By_Product
. My tried code is-
function the_dramatist_get_report(){
include_once( WP_PLUGIN_DIR . '/woocommerce/includes/admin/reports/class-wc-admin-report.php');
include_once( WP_PLUGIN_DIR . '/woocommerce/includes/admin/reports/class-wc-report-sales-by-product.php');
$reports = new WC_Report_Sales_By_Product();
$reports->start_date = strtotime('2016-11-11');
$reports->end_date = strtotime('2016-11-22');
$reports->product_ids = 15;
$total_items = absint( $reports->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_meta' => array(
'relation' => 'OR',
array(
'type' => 'order_item_meta',
'meta_key' => array( '_product_id', '_variation_id' ),
'meta_value' => $reports->product_ids,
'operator' => 'IN'
)
),
'query_type' => 'get_var',
'filter_range' => true
) ) );
return $total_items;
}
But the above code is returning 0
when I've already tested that it should be 1
. So it would be better if you help me with this problem.
If you have any other idea to get this task done, feel free to share.
Upvotes: 2
Views: 2490
Reputation: 10809
WooCommerce Order in WordPress are treated as Post. So Order Date is stored in
wp_posts.post_date
withwp_posts.post_type
= shop_order.
So first get all the order in a specific date range, then check the product_id from line item in those orders.
Here is the code:
function getproductReportbyDateRange($start_date, $end_date, $product_ids = [])
{
$totalProduct = [];
foreach ($product_ids as $product_id)
{
$totalProduct[$product_id] = 0;
}
unset($product_id);
$args = array(
'post_type' => 'shop_order', //WooCommerce Order Status.
'post_status' => array('wc-completed'), //list of WooCommerce order status.
'posts_per_page' => -1,
'date_query' => array(
array(
'after' => $start_date,
'before' => $end_date,
'inclusive' => true,
),
),
);
$query = new WP_Query($args);
//print_r($query->posts);
//die;
if (isset($query->posts))
{
foreach ($query->posts as $post)
{
$order = new WC_Order($post->ID);
$items = $order->get_items();
foreach ($items as $item)
{
//$product_name = $item['name'];
$product_id = $item['product_id'];
//$product_variation_id = $item['variation_id'];
if (in_array($product_id, $product_ids))
{
$totalProduct[$product_id] = $totalProduct[$product_id] + $item['qty'];
}
}
}
}
return $totalProduct;
}
This code goes in function.php file of your active child theme (or theme) or also in any plugin file.
USAGE
To get the product count for a single product
getproductReportbyDateRange('2016-11-11','2016-11-22',[37]);
To get the product count for a multiple product
getproductReportbyDateRange('2016-11-11','2016-11-22',[37, 53]);
The code is tested and fully functional.
Reference:
Upvotes: 2
Reputation: 855
After trying many times I've not made that class instance to work. So I followed the oldest CRUD technique to solve this (Which actually this class is also doing), I've written my own SQL
query. This WC_Admin_Report
class is also doing this. It's kinda works like WooCommerce's query builder. By the way the below SQL
code solved my problem-
SELECT order_item_meta__product_id.meta_value AS product_id,
Sum(order_item_meta__qty.meta_value) AS order_item_count
FROM wp_posts AS posts
INNER JOIN wp_woocommerce_order_items AS order_items
ON posts.id = order_items.order_id
INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__qty
ON ( order_items.order_item_id =
order_item_meta__qty.order_item_id )
AND ( order_item_meta__qty.meta_key = '_qty' )
INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__product_id
ON ( order_items.order_item_id = order_item_meta__product_id.order_item_id )
AND ( order_item_meta__product_id.meta_key = '_product_id' )
INNER JOIN wp_woocommerce_order_itemmeta AS
order_item_meta__product_id_array
ON order_items.order_item_id = order_item_meta__product_id_array.order_item_id
WHERE posts.post_type IN ( 'shop_order', 'shop_order_refund' )
AND posts.post_status IN ( 'wc-completed', 'wc-processing', 'wc-on-hold' )
AND posts.post_date >= '2016-11-15'
AND posts.post_date < '2016-11-24'
AND (( order_item_meta__product_id_array.meta_key IN ( '_product_id', '_variation_id' )
AND order_item_meta__product_id_array.meta_value IN ( '15' ) ))
GROUP BY product_id
Hope that also help you.
Upvotes: 0