Question SQL Reports WooCommerce WordPress
- SOLVED
I need 3 functions that contain SQL calls to make a detailed report of a specific product using ID.
I need to know total sales with specific payment
Total sales for each variation in the product
Total sales with specific custom field (aff_tkg_order)
Thanks in advance
Answers (3)
John Cotton answers:
This will give you the total sales for each payment method.
SELECT SUM(pm1.meta_value), pm2.meta_value
FROM wp_postmeta pm1
INNER JOIN wp_postmeta pm2 ON pm1.post_id = pm2.post_id AND pm2.meta_key = '_payment_method'
WHERE pm1.meta_key = '_order_total'
GROUP BY pm2.meta_value
If you want it for a specific payment method, add "AND pm2.meta_key == '{THE METHOD}'" to the WHERE clause.
If you want that for each product, then try this
SELECT SUM(oim2.meta_value), pm1.meta_value
FROM wp_postmeta pm1
INNER JOIN wp_woocommerce_order_items oi ON oi.order_id = pm1.post_id
INNER JOIN wp_woocommerce_order_itemmeta oim1 ON oim1.order_item_id = oi.order_item_id AND oim1.meta_key = '_product_id'
INNER JOIN wp_woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_line_total'
WHERE oim1.meta_value = {$product_id} AND pm1.meta_key = '_payment_method'
GROUP BY pm1.meta_value
Again, you can add a specific method to the WHERE clause if required.
If I've understood correctly, you can sum by some other meta value by just changing '_payment_method' in those queries to the one you want.
John Cotton comments:
In PHP, that might look something like this:
function total_by_payment( $method = null ) {
global $wpdb;
$sql = $wpdb->prepare( "SELECT SUM(pm1.meta_value) AS total, pm2.meta_value AS method
FROM $wpdb->postmeta pm1
INNER JOIN $wpdb->postmeta pm2 ON pm1.post_id = pm2.post_id AND pm2.meta_key = '_payment_method'
WHERE pm1.meta_key = '_order_total' %s
GROUP BY pm2.meta_value", is_null($method) ? '' : sprintf( " AND pm2.meta_key = '%s'", $method ) );
$r = $wpdb->get_results( $sql );
if( is_null($method) ) {
// This will be a set of rows with total by method
return $r;
} else {
// Just one method? ... return the total value only
if( count($r) > 0 ) {
return $r[0]->total;
}
}
}
Davide Pantè comments:
Thanks John Cotton.
Please could you wrap your queries within the function so that you understand better? I'm not very expert.
Also I have to query only on the processing not completed orders.
Thank you very much
John Cotton comments:
Here's a function with both of them in:
function total_by_payment( $product_id = null, $method = null ) {
global $wpdb;
if( is_null( $product_id ) ) {
$sql = $wpdb->prepare( "SELECT SUM(pm1.meta_value) AS total, pm2.meta_value AS method
FROM $wpdb->postmeta pm1
INNER JOIN $wpdb->postmeta pm2 ON pm1.post_id = pm2.post_id AND pm2.meta_key = '_payment_method'
WHERE pm1.meta_key = '_order_total' %s
GROUP BY pm2.meta_value", is_null($method) ? '' : sprintf( " AND pm2.meta_key = '%s'", $method ) );
} else {
$sql = $wpdb->prepare( "SELECT SUM(oim2.meta_value), pm1.meta_value
FROM wp_postmeta pm1
INNER JOIN wp_woocommerce_order_items oi ON oi.order_id = pm1.post_id
INNER JOIN wp_woocommerce_order_itemmeta oim1 ON oim1.order_item_id = oi.order_item_id AND oim1.meta_key = '_product_id'
INNER JOIN wp_woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_line_total'
WHERE oim1.meta_value = %s AND pm1.meta_key = '_payment_method' %s
GROUP BY pm1.meta_value", $product_id, is_null($method) ? '' : sprintf( " AND pm1.meta_key = '%s'", $method ) );
}
$r = $wpdb->get_results( $sql );
if( is_null($method) ) { // This will be a set of rows with total by method
return $r;
} else { // Just one method? ... return the total value only
if( count($r) > 0 ) {
return $r[0]->total;
}
}
}
Davide Pantè answers:
Example perfect functions
total_sales_by_payment(115,'paypal');
total_sales_by_payment(115);
total_sales_by_payment(115,'custom_meta_key_order');
Arnav Joy answers:
Do you have your site live I can see url?
Davide Pantè comments:
YES, this a example of event on my sites
https://www.ticketgold.it/prodotto/14-15-16-settembre-2018-torino-tattoo-convention-palavela-torino/
In this case, I have necessity a detail of total sales of each variation
Total sales with specific payment
Total sales by specific custom meta key (is a CODE for Affiliate Scope)