Viewed   68 times

For Woocommerce, I wrote code below, trying to get orders total purchases amount for today:

function order_total_woo_fahad(){

    // Get orders from people named John that were paid in the year 2016.
$orders = wc_get_orders( array(
    'date_paid' => '2018-07-03'
) );
$total_of_all=0;
for($i=0;$orders[i];$i++)
    $total_of_all= $orders[i]->get_total();
    return $total_of_all;
}

But it returns null.

What I am doing wrong? How can I get orders total purchases amount for the day?

 Answers

2

The best and effective way to get that is to use the following very light SQL query, that will get the sum of all order totals in the last 24 hours for "processing" and "completed" orders statuses:

function get_daily_purchases_total(){
    global $wpdb;

    return $wpdb->get_var( "
        SELECT SUM(pm.meta_value)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_order'
        AND p.post_status IN ('wc-processing','wc-completed')
        AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
        AND pm.meta_key = '_order_total'
    " );
}

Code goes in function.php file of your active child theme (or active theme). Tested and works.


USAGE Example - Display the daily total purchased formatted amount:

<?php echo '<p>Total purchased of the day: ' . strip_tags( wc_price(get_daily_purchases_total() ) ) . '</p>'; ?>

If you want to get instead the total based on the "today" date, you will replace in the code this line:

AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))

by this line:

AND DATE(p.post_date) >= CURDATE()
Friday, August 19, 2022
4

Updated - The following code will avoid checkout under a defined minimal cart amount except for a define product ID:

add_action( 'woocommerce_check_cart_items', 'min_cart_amount' );
function min_cart_amount() {
    ## ----- Your Settings below ----- ##

    $min_amount = 15; // Minimum cart amount
    $except_ids = array(37, 53); // Except for this product(s) ID(s)

    // Loop though cart items searching for the defined product
    foreach( WC()->cart->get_cart() as $cart_item ){
        if( in_array( $cart_item['variation_id'], $except_ids ) 
        ||  in_array( $cart_item['product_id'], $except_ids )
            return; // Exit if the defined product is in cart
    }

    if( WC()->cart->subtotal < $min_amount ) {
        wc_add_notice( sprintf(
            __( "<strong>A Minimum of %s is required before checking out.</strong><br>The current cart's total is %s" ),
            wc_price( $min_amount ),
            wc_price( WC()->cart->subtotal )
        ), 'error' );
    }
}

Code goes in functions.php file of your active child theme (or active theme). Tested and work.

Monday, September 5, 2022
2

There is many ways to accomplish that. Here I use in a custom function with one SQL query and a $variation_id (the variation ID to set in) as parameter in it:

function get_all_orders_items_from_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order Items with that variation ID
    $item_ids_arr = $wpdb->get_col( $wpdb->prepare( "
        SELECT `order_item_id` 
        FROM {$wpdb->prefix}woocommerce_order_itemmeta 
        WHERE meta_key LIKE '_variation_id' 
        AND meta_value = %s
    ", $variation_id ) );

    return $item_ids_arr; // return the array of orders items ids

}

Code goes in function.php file of your active child theme (or theme) or also in any plugin file.

USAGE (here with the variation ID 41 for example):

This will display a list of orders items IDs for this variation ID with some data (for example).

$items_ids = get_all_orders_items_from_a_product_variation( 41 );

// Iterating through each order item
foreach( $items_ids as $item_id ){

    // Getting some data (the color value here)
    $item_color = wc_get_order_item_meta( $item_id, 'pa_color', true );

    // Displaying some data related to the current order item
    echo 'Item ID: '. $item_id . ' with color "' . $item_color .'"<br>';
}

This code is tested and works.


GETTING THE ORDER ID (UPDATED)

Now if you need to get all the Orders IDs instead, you can use another query inside that function this way:

function get_all_orders_that_have_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order IDs with that variation ID
    $order_ids_arr = $wpdb->get_col( $wpdb->prepare( "
        SELECT DISTINCT items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items AS items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id
        WHERE meta_key LIKE '_variation_id'
        AND meta_value = %s
    ", $variation_id ) );

    return $order_ids_arr; // return the array of orders ids

}

Code goes in function.php file of your active child theme (or theme) or also in any plugin file.

USAGE (here always with the variation ID 41 for example):

This will display a list of orders IDs for this variation ID with their status (for example).

$orders_ids = get_all_orders_that_have_a_product_variation( 41 );

// Iterating through each order item
foreach( $orders_ids as $order_id ){

    // Getting an instance of the order object
    $order = wc_get_order($order_id);

    // Displaying some data related to the current order
    echo 'Order #'. $order_id . ' has status "' . $order->get_status() .'"<br>';
}

This code is tested and works.

You can also combine in a more complex array, the Order ID with it's related Items IDs in a multi-dimensional array this way:

function get_all_orders_and_item_ids_that_have_a_product_variation( $variation_id ){

    global $wpdb;

    // Getting all Order IDs and item ids with that variation ID
    $results = $wpdb->get_results( $wpdb->prepare( "
        SELECT items.order_id, items.order_item_id AS item_id
        FROM {$wpdb->prefix}woocommerce_order_items AS items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id
        WHERE meta_key LIKE '_variation_id'
        AND meta_value = %s
    ", $variation_id ), ARRAY_A );

    return $results; // return a multi-dimensional array of orders Ids / items Ids

}
Tuesday, August 16, 2022
 
kashif
 
4

In a WP_Query, you need to use the post_status slugs like in the database table wp_posts… All orders status start with "wc-":

So in your case: 'post_status' => 'wc-processing'.

This should work now.

Sunday, September 11, 2022
 
5

This can be done with the following very light SQL query and a foreach loop.

That will give you the list of products (and product variations, but not parent variable products) of orders count by product for the past 24 hours:

global $wpdb;

$results = $wpdb->get_results( "
    SELECT DISTINCT woim.meta_value as id, COUNT(woi.order_id) as count, woi.order_item_name as name
    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim
    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id
    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id
    WHERE p.post_status IN ('wc-processing','wc-on-hold')
    AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
    AND ((woim.meta_key LIKE '_variation_id' AND woim.meta_value > 0)
    OR (woim.meta_key LIKE '_product_id'
    AND woim.meta_value NOT IN (SELECT DISTINCT post_parent FROM {$wpdb->prefix}posts WHERE post_type LIKE 'product_variation')))
    GROUP BY woim.meta_value
" );

// Loop though each product
foreach( $results as $result ){
    $product_id   = $result->id;
    $product_name = $result->name;
    $orders_count = $result->count;
    
    // Formatted Output
    echo 'Product: ' . $product_name .' (' . $product_id . ') = ' . $orders_count . '<br>';
}

Tested and works.


If you want to get instead the total based on the "today" date, you will replace in the code this line:

AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))

by this line:

AND DATE(p.post_date) >= CURDATE()

Time zone ajustement using CONVERT_TZ() SQL function
(Where you will adjust '+10:00' the last argument as an offset to match the timezone)

AND DATE(p.post_date) >= DATE(CONVERT_TZ( NOW(),'+00:00','+10:00'))

Related similar answers:

  • Get orders total purchases amount for the day in Woocommerce
  • Total count for each order item in a loop on Woocommerce
Tuesday, September 20, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :