michal.vlcek

Hi Pablo, I got a little ahead of myself :-).

It says another error.
You use a different character for the minus sign in the PHP script in the SQL queries.
You are using a long minus character and the MySQL database does not understand it.

—————
[15-Nov-2022 09:51:33 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘– alg_cog_oimc.meta_value * product_qty) AS profit_total
FROM
drs_wc…’ at line 2 for query
SELECT
DATE_FORMAT(drs_wc_order_product_lookup.date_created, ‘%Y-%m-%d’) AS time_interval , MAX(drs_wc_order_product_lookup.date_created) AS datetime_anchor , SUM(product_qty) as items_sold , SUM(alg_cog_oimc.meta_value * product_qty) AS costs_total , SUM(drs_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total
FROM
drs_wc_order_product_lookup
JOIN drs_wc_order_stats ON drs_wc_order_product_lookup.order_id = drs_wc_order_stats.order_id LEFT JOIN drs_woocommerce_order_itemmeta alg_cog_oimc ON drs_wc_order_product_lookup.order_item_id = alg_cog_oimc.order_item_id AND alg_cog_oimc.meta_key = ‘_alg_wc_cog_item_cost’
WHERE
1=1
AND drs_wc_order_product_lookup.product_id IN (770) AND ( drs_wc_order_stats.status NOT IN ( ‘wc-auto-draft’,’wc-trash’,’wc-cancelled’,’wc-failed’,’wc-pending’,’wc-on-hold’,’wc-checkout-draft’ ) ) AND drs_wc_order_product_lookup.date_created <= ‘2022-11-15 23:59:59’ AND drs_wc_order_product_lookup.date_created >= ‘2022-11-01 00:00:00’

GROUP BY
time_interval
LIMIT 0,100 made by wp_dashboard, do_meta_boxes, WC_Admin_Dashboard->status_widget, WC_Admin_Dashboard->sales_sparkline, rest_do_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Controller->get_items, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Query->get_data, WC_Data_Store->__call, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\DataStore->get_data
———————-

You have a typo here:
/wp-content/plugins/cost-of-goods-for-woocommerce-pro/includes/analytics/class-alg-wc-cog-analytics-products.php
… line 73:
$clauses[] = “, SUM({$wpdb->prefix}wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

Best regards
Michal

Hi, yes we have Cost of Goods for WooCommerce Pro 2.7.3 and WooCommerce 7.0.1 and the problem still persists.
The same error above is still written in the PHP log file.

I already wrote you the fix above and it is directly in the source of your plugin
in file: /cost-of-goods-for-woocommerce-pro/includes/analytics/class-alg-wc-cog-analytics-products.php
on line 72:

$clauses[] = “, SUM(wp_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

change to:

$clauses[] = “, SUM({$wpdb->prefix}wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

and it will be good.

Best regards
Michal