I’m developing a custom WordPress plugin to display WooCommerce order statistics in the admin panel using SQL queries. However, the plugin does not show the correct data.

Issue:

The plugin is supposed to display the total number of completed and processing orders, along with their total amount and total number of canceled orders and their amount. However, the displayed statistics are incorrect or not showing up at all.

Expected Output:

• Total Number of Orders: [Expected number]

• Total Amount for Total Orders: [Expected amount]

• Total Cancelled Orders: [Expected number]

• Total Amount for Cancelled Orders: [Expected amount]

<?php
/*
Plugin Name: WooCommerce Order Stats
Description: Displays order statistics for WooCommerce in the WordPress admin panel.
Version: 1.0
Author: Your Name
*/

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

// Function to display WooCommerce order statistics
function display_woocommerce_order_stats_admin_notice() {
    global $wpdb;
    $table_prefix = $wpdb->prefix;
    $orders_table = "{$table_prefix}wc_orders"; // WooCommerce orders table name

    // Query total number of completed orders
    $total_completed_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-completed'
    ");

    // Query total amount for completed orders
    $total_completed_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-completed'
        AND meta.meta_key = '_order_total'
    ");

    // Query total number of processing orders
    $total_processing_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-processing'
    ");

    // Query total amount for processing orders
    $total_processing_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-processing'
        AND meta.meta_key = '_order_total'
    ");

    // Query total number of cancelled orders
    $total_cancelled_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-cancelled'
    ");

    // Query total amount for cancelled orders
    $total_cancelled_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-cancelled'
        AND meta.meta_key = '_order_total'
    ");

    // Calculate total orders and total amount
    $total_orders = $total_completed_orders + $total_processing_orders;
    $total_order_amount = $total_completed_amount + $total_processing_amount;

    // Output the statistics and table name as an admin notice
    echo '<div class="notice notice-info">';
    echo '<p><strong>WooCommerce Order Statistics (Table: ' . $orders_table . '):</strong></p>';
    echo '<ul>';
    echo '<li>Total Number of Orders: ' . $total_orders . '</li>';
    echo '<li>Total Amount for Total Orders: ' . wc_price($total_order_amount) . '</li>';
    echo '<li>Total Cancelled Orders: ' . $total_cancelled_orders . '</li>';
    echo '<li>Total Amount for Cancelled Orders: ' . wc_price($total_cancelled_amount) . '</li>';
    echo '</ul>';
    echo '</div>';
}

// Hook into the admin menu
add_action('admin_notices', 'display_woocommerce_order_stats_admin_notice');