Sales breakdown by Category broken

Hi,

The fact that the products are in several categories is probably what's messing the amounts as the sales must be counted once for each category of the products in that case.
Could you turn on the "debug mode" setting of the Joomla configuration and look at the report again ?
Then, at the bottom of the page, could you search for the MySQL queries with the text hikashop_order_product in it and provide them ?

Hi,

Thank you for that.
From what I can see, the issue is that the code:

if($widget->widget_params->content=='orders'){
					$selectAdd='prod.order_product_quantity';
				}else if($widget->widget_params->content=='sales'){
					$selectAdd='(prod.order_product_price+prod.order_product_tax)*prod.order_product_quantity';
				}

if(!isset($leftjoin['order_product'])){ $leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id '; }
				if(!isset($leftjoin['product_category'])){ $leftjoin['product_category'] = ' LEFT JOIN '.hikashop_table('product_category').' AS cat ON cat.product_id = prod.product_id '; }
				$leftjoin['category'] = ' LEFT JOIN '.hikashop_table('category').' AS c ON c.category_id = cat.category_id ';

Hi,

Ok, then try to add the same code, but after the code:

$leftjoin['product'] = ' LEFT JOIN '.hikashop_table('product').' AS p ON prod.product_id = p.product_id ';
				$select .= 'DISTINCT(prod.order_product_id), ';

Hi,

This modification will not create that error.
So I don't see why you would get that error.
First, I would recommend to revert the change and see if your still have the error.
Then, turn on the error reporting and debug mode settings of the Joomla configuration and try again. If you get that error again, you should get more information on the issue.

Hi,

So you need to keep that change but also change the line:
$pie= 'SUM('.$selectAdd.') AS total';
to:
$pie= 'SUM('.$selectAdd.') AS total, a.order_currency_id AS currency_id';
in that same file. That will avoid that query error which happens because the way the calculations are done changed with the modification I proposed.

Hi,

Yes, the fix until now what to take the sum of the product amount for all matched orders instead of using the total amount of the order.
Now, it doesn't help if the product is in several categories of the categories selected in the "category" setting of the report as it will sum it once per category matching in that setting.
So if you select there the main category with all it's sub categories, it will sum the amount for each sub categories.
Since you're selected there the main category, I would recommend instead to not select it and leave the field empty. That way, the problem won't happen. Alternatively, you could select only the categories so that no doublon happens.

Hi,

If you don't set a category, it will work properly, yes. That's what I said in my previous message.