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.

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.

• plugin-development
• woocommerce-offtopic

Share Improve this question asked Nov 22, 2016 at 18:33 CodeMascotCodeMascot 4,53722 gold badges1515 silver badges2525 bronze badges 1

• Set false the 'filter_range' => true, it only works on the WC Reports – justjoe Commented Oct 4, 2019 at 1:04

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.