Get Orders Total Purchases Amount for the Day in Woocommerce

Get orders total purchases amount for the day in Woocommerce

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

Get today's total orders count for each product in Woocommerce

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

WooCommerce: How to calculate and list total weight of each order with status 'on hold' and calculate total amount and total weight for all orders

function kg_total(){
    
    ?>
    <div class="wrap">
        <h2>Total weight (kg):</h2>
    </div>
    <?php   
    
    $order_weight = 0;
    $orders_weight = 0;
    
    $on_hold_orders = wc_get_orders( array(
    'limit' => -1,
    // 'customer_id' => $user->ID,
    'post_type' => 'shop_order',
    'status'    => 'on-hold',
    ) );
    
    ?>
    <table class="table">
    <thead>
        <tr>
            <th data-sort="int">order_ID</th>
            <th data-sort="string">order_weight</th>
            <th data-sort="string">last name</th>
            <th data-sort="string">first name</th>
        </tr>
    </thead>
    
    <tbody> 
    <?php
    
    foreach( $on_hold_orders as $order) {
        // Gesamt €
        $total_amount += $order->get_total();
        
        foreach( $order->get_items() as $item ) {
        // Gesamt kg per order
        if ( $item['product_id'] > 0 ) {
            $_product = $item->get_product();
                if ( ! $_product->is_virtual() ) {
                    $order_weight += $_product->get_weight() * $item['qty'];

                }
            }
        }
        $orders_weight = $orders_weight + $order_weight;
        
        echo '<td style="color: #0000aa;"><b>' . $order->get_id() . '</b></td>';
        echo '<td style="text-align:right;"><b>' . $order_weight . ' kg - </b></td>';
        echo '<td>' . '<b>' . strtoupper( $order->get_billing_last_name() ) . '</b>' . '</td>';
        echo '<td>' . $order->get_billing_first_name() . '</td>';
        echo '</tr>';
                
        $order_weight = 0;
        
    }
    echo '</tbody>';
    
    echo '<br>';
    echo '<p style="color: #0000aa; font-size: 20px;"><b>Total amount (on hold): ' . $total_amount . ' €</b></p>';
    echo '<p style="color: #aa0000; font-size: 20px;"><b>Total weight (on hold): ' . $orders_weight . ' kg</b></p>';
        
}

How to set a maximum limit in WooCommerce orders per item & per day

The code below uses the product ID to get the total quantity for the current day. If this is higher than or equal to the $dailyOrderLimit that you set (in my example, it's 10), the product cannot be purchased.

add_filter('woocommerce_is_purchasable', 'preventPurchaseIfDailyOrderLimitReached', 10, 2);

function preventPurchaseIfDailyOrderLimitReached($is_purchasable, $product)
{
    $dailyOrderLimit = 10;
    $productId = $product->get_id();
    $quantityOrderedToday = getDailyOrderAmount($productId);

    if ($quantityOrderedToday >= $dailyOrderLimit) {
        $is_purchasable = false;
    }

    return $is_purchasable;
}

function getDailyOrderAmount($productId)
{
    global $wpdb;
    $today = date('Y-m-d');
    $result = $wpdb->get_results("SELECT sum(product_qty) as quantity_ordered_today FROM {$wpdb->prefix}wc_order_product_lookup where product_id= {$productId} and date_created > '{$today} 00:00:00';");

    return $result[0]->quantity_ordered_today;
}

Code is tested and works. Add it to the functions.php file of your child theme.

If you feel that my answer helped you, you could accept my answer.

Show custom text based on total spent by user in WooCommerce

You can add the if condition. try the below code.

function get_user_total_spent( $atts ) {

    extract( shortcode_atts( array(
        'user_id' => get_current_user_id(),
    ), $atts, 'user_total_spent' ) );

    if( $user_id > 0 ) {
        $customer = new WC_Customer( $user_id ); // Get WC_Customer Object

        $total_spent = $customer->get_total_spent(); // Get total spent amount

        if( $total_spent > 600 && $total_spent < 1200 ){
            $text = __( 'congrats you are now tier 1', 'woocommerce' );
        }elseif( $total_spent > 1200 ){
            $text = __( 'congrats you are now tier 2', 'woocommerce' );
        }else{
            $text = __( 'congrats you are now tier 3', 'woocommerce' );
        }

        $total_spent = wc_price( $total_spent );

        return "Total Amount Spent: ".$total_spent." ".$text;
    }
}
add_shortcode('user_total_spent', 'get_user_total_spent');

WC Get Orders with Sort by Highest Total Number of Orders

I understand that you already get totals of orders for each customer. This will require some changes in your code but I think you can easily modify it.

Create an array,

$customer_totals = array();

Then insert each customer id as key and total of orders for this customer as value (*),

$temp_arr = array($customer_id => $total);
$customer_totals =array_merge($customer_totals , $temp_arr);

After insertion of all customer ids and totals, sort the final array in descending order (**),

arsort($customer_totals);

Now you can display the totals as you wish. I hope this helps. Please inform me if you have any problems by updating your code.

*https://stackoverflow.com/a/9735696

**https://www.w3schools.com/php/func_array_arsort.asp

Get products of the customer's last order in WooCommerce

You can use wc_get_customer_last_order( $user_id ) to get info about customer’s last order.

So you get:

// For logged in users only
if ( is_user_logged_in() ) {

    // The current user ID
    $user_id = get_current_user_id();

    // Get the last WC_Order Object instance from current customer
    $last_order = wc_get_customer_last_order( $user_id );

    // NOT empty
    if ( ! empty( $last_order ) ) {
        // Initalize
        $product_ids = array();

        // Loop
        foreach ( $last_order->get_items() as $item ) {
            // Get product ID
            $product_id = $item->get_product_id();
            $product_ids[] = $product_id;
        }

        echo '<pre>';
        var_dump( $product_ids );
        echo '</pre>';
    }
}

Related: How to display the last ordered product in WooCommerce via a shortcode

Get Orders Total Purchases Amount for the Day in Woocommerce