the_dramatist the_dramatist - 8 days ago 5
SQL Question

WooCommerce Admin Reports : Get custom product report based on date range

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

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.

Answer

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.

Comments