WooCommerce - Calculate Total Sales By Product Category

woocommerce-calculate-total-sales-by-product-category.php

/**
 * @snippet       Calculate Total Sales By Product Category
 * @author        Rasool Vahdati
 * @compatible    WooCommerce 7
 */

/**
 * Retrieve orders containing products from a specific category - Written by Rodolfo Melogli
 *
 * @param string $cat_slug       The slug of the product category.
 * @param array  $order_status   Optional. Array of order statuses to include. Default is an empty array, meaning all statuses will be included.
 * @return array                 An array of order IDs containing products from the specified category.
 */
function get_orders_by_product_cat( $cat_slug, $order_status = array() ) {
    global $wpdb;
    
    // Set default status to include all if empty array passed
    if ( empty( $order_status ) ) {
        $order_status = array( 'wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending', 'wc-cancelled', 'wc-refunded', 'wc-failed' );
    }
    
    $args = array(
        'limit' => -1,
        'status' => 'publish',
        'return' => 'ids',
        'category' => array( $cat_slug ),
    );
    $product_ids = wc_get_products( $args );
    $results = $wpdb->get_col("
        SELECT order_items.order_id
        FROM {$wpdb->prefix}woocommerce_order_items as order_items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order'
        AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND order_item_meta.meta_value IN ( '" . implode( "','", $product_ids ) . "' )
    ");
    return $results;
}

/**
 * Calculate the total sales amount of products from a specific category across orders
 *
 * @param string $cat_slug   The slug of the product category.
 * @return string            The formatted total sales amount of products from the specified category.
 */
function cat_total_sales( $cat_slug ) {
    $orders = get_orders_by_product_cat( $cat_slug );
    $total = 0;
    foreach ( $orders as $order_id ) {
        foreach ( wc_get_order( $order_id )->get_items() as $key => $item ) {
            $product_id = $item->get_product_id();
            if ( ! $product_id ) continue;
            if ( has_term( $cat_slug, 'product_cat', $product_id ) ) $total += $item->get_total();
        }
    }
    return wc_price( $total );
}

/**
 * Add a custom column to the product category admin page to display the total sales amount
 *
 * @param array $columns   An array of column headers.
 * @return array           An updated array of column headers including the custom column.
 */
function cat_total_sales_column_header( $columns ) {
    $columns['total_sales'] = __('Total Sales');
    return $columns;
}

/**
 * Display the content of the custom column on the product category admin page
 *
 * @param string $content      The current content of the column.
 * @param string $column_name  The name of the current column.
 * @param int    $term_id      The ID of the current term.
 * @return string              The updated content of the column including the total sales amount.
 */
function cat_total_sales_column_content( $content, $column_name, $term_id ) {
    if ( 'total_sales' === $column_name ) {
        $total_sales = cat_total_sales( get_term( $term_id )->slug );
        $content .= $total_sales;
    }
    return $content;
}

add_filter( 'manage_edit-product_cat_columns', 'cat_total_sales_column_header' );
add_filter( 'manage_product_cat_custom_column', 'cat_total_sales_column_content', 10, 3 );