CodeMascot
CodeMascot

Reputation: 855

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 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

Answers (2)

Raunak Gupta
Raunak Gupta

Reputation: 10809

WooCommerce Order in WordPress are treated as Post. So Order Date is stored in wp_posts.post_date with wp_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

CodeMascot
CodeMascot

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

Related Questions