Sales breakdown by Category broken

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
5 months 1 day ago #299359

-- HikaShop version -- : 3.5.0
-- Joomla version -- : 3.8.12
-- PHP version -- : 7.1

Situation:

I am trying to show the yearly sales for two master categories. However, some products exist within multiple subcategories. For example, Product A might exist in Master A - > Subcategory A and Master A -> subcategory B. In the reports, i would think that it would filter through this and calculate the total for a given product no matter if it exist in multiple categories. However, this is what displays for the total yearly breakdown (no category filter) and is the correct total for each month:

This message contains confidential information
with the following settings (no category filter):
This message contains confidential information
Now if i choose the first master category (products in this case):
This message contains confidential information
with settings (only thing different is "Products" selected as the category):
This message contains confidential information


Is this just a config error or is something broken? Odd that the category specific case shows MUCH HIGHER totals and is definitely incorrect.

I would like to just break down the monthly sales into "products" and "parts" which are both main categories. I should also note we may have one or two products that exist in both "products" and "parts" but that shouldn't skew the data too much.

Attachments:
Last edit: 5 months 1 day ago by mojooutdoors-holden.

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 3 weeks ago #299404

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 ?

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 3 weeks ago #299441

Here are the query results:




Let me know if you need any further information.

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 3 weeks ago #299477

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';
				}
should be added after the code:
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 ';
in the file administrator/components/com_hikashop/classes/widget.php
That way, it will calculate the sales based on the amounts of the individual products in the order instead of the whole order amount.

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 3 weeks ago #299510

I added the code provided and observed similar behavior.



Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 3 weeks ago #299536

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), ';
in that same file.
That should help better.

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 3 weeks ago #299581

That addition broke the page.



Any other suggestions?

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 3 weeks ago #299586

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.

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 3 weeks ago #299664

Here's more info on the error:



For now I have removed the code. I added it to the requested location in administrator/components/com_hikashop/classes/widget.php which was around line 646.
if($setFilters){
			if(isset($widget->widget_params->filters['cat.category_id'])){
				if(($widget->widget_params->content=='customers' || $widget->widget_params->content=='partners')){
					$leftjoin['order'] = ' LEFT JOIN '.hikashop_table('order').' AS o ON o.order_user_id = a.user_id ';
					$leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = o.order_id ';
				}else{
					$leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id ';
				}
				$leftjoin['product'] = ' LEFT JOIN '.hikashop_table('product').' AS p ON prod.product_id = p.product_id ';
				$select .= 'DISTINCT(prod.order_product_id), ';
				
				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';
				}
				
				$leftjoin['product_category'] = ' LEFT JOIN '.hikashop_table('product_category').' AS cat ON cat.product_id = p.product_id OR cat.product_id=p.product_parent_id';
				if($widget->widget_params->category_childs){
					$leftjoin['category'] = ' LEFT JOIN '.hikashop_table('category').' AS categ ON cat.category_id = categ.category_id ';
					$widget->widget_params->filters['cat.category_id']=array_merge($widget->widget_params->filters['cat.category_id'], $widget->widget_params->childs);
				}
			}
			if(isset($widget->widget_params->filters['prod.product_id'])){
				if(($widget->widget_params->content=='customers' || $widget->widget_params->content=='partners')){
					$leftjoin['order'] = ' LEFT JOIN '.hikashop_table('order').' AS o ON o.order_user_id = a.user_id ';
					$leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = o.order_id ';
				}else{
					$leftjoin['order_product'] = ' LEFT JOIN '.hikashop_table('order_product').' AS prod ON prod.order_id = a.order_id ';
				}
			}
		}

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 3 weeks ago #299700

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.

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 1 week ago #300350

No error now, but the report is still incorrect. Seems to be double counting since they are in multiple categories. The amount of products in both master categories is very small and would not explain the discrepancy.

I should also note that some products inside "Products" and "replacement parts" are also in multiple subcategories. For example, Part 1a might be in 4 subcategories of "replacement parts."

Does this make sense?

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 1 week ago #300408

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.

File Attachment:

File Name: widget_2018-11-12.zip
File Size:14 KB

I've added a fix on our end to avoid that. However, it's a big patch so I can't just give you the lines to change as there are 30 lines at different places.
Here is a zip of the widget.php with the patch:

So you can unzip it and replace the file. Note however that you're using HikaShop 3.5.0 and this file is the one from HikaShop 4.0.0. It should be ok but it might create some unforseen issues.

Attachments:
Last edit: 4 months 1 week ago by nicolas.

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
4 months 1 week ago #300482

Broke the report, but it's probably because of the change from 3.5.0 to 4.0.0. I will have to wait until after our busy season to upgrade.

In the meantime, I think I might just manually select all of the products. Surely that will prevent double counting?

Please Log in or Create an account to join the conversation.

  • Posts: 65116
  • Thank you received: 9456
  • MODERATOR
4 months 1 week ago #300487

Hi,

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

The following user(s) said Thank You: mojooutdoors-holden

Please Log in or Create an account to join the conversation.

  • Posts: 198
  • Thank you received: 11
  • Hikashop Business
3 months 2 weeks ago #301150

Gotcha. I misunderstood. It took some time to manually select each individual product, but the report now displays properly. Thanks for your help!

Hopefully, I will be able to upgrade our hikashop version after the first of the year.

Please Log in or Create an account to join the conversation.

Time to create page: 0.134 seconds
Powered by Kunena Forum