Author Topic: ISSUE Editing Order Calculations are not correct  (Read 1430 times)

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
ISSUE Editing Order Calculations are not correct
« on: October 21, 2019, 18:21:16 pm »
ISSUE Editing Order Calculations are not correct - for example edit one line item and enter a value in 'Total Discount', clear: 'Base price with tax', 'Program Price (Gross)', and 'TAX' - click update. Notice the product_discountedPriceWithoutTax is calculated wrong and the 'Program Price (Cost)' discounted value is wrong.

UPDATED Tested issue exists on VirtueMart 3.6.11 10247
Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10040
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: ISSUE Editing Order Calculations are not correct
« Reply #1 on: January 15, 2020, 18:01:28 pm »
I need your tax settings. Do you use per product or per product? and i need your discount rule
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
Re: ISSUE Editing Order Calculations are not correct
« Reply #2 on: January 15, 2020, 18:20:36 pm »
Using 'Vat tax per product'

FYI - see previous issues with other taxes vat is the only way to go going forward: https://forum.virtuemart.net/index.php?topic=141225
Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
Re: ISSUE Editing Order Calculations are not correct
« Reply #3 on: March 27, 2020, 17:17:26 pm »
Just tested 3.6.11 10289 on PHP 7.3 all looks good... except I still can't use it until we get order editing and calculations working - can I fix this for you?
Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3175
  • VirtueMart Version: 3.8.2
Re: ISSUE Editing Order Calculations are not correct
« Reply #4 on: March 27, 2020, 19:41:09 pm »
Oh I had some fun with this

Try looking at a regular item with discount

Just edit it in the order - touch nothing and hit save
Then check the values on screen and in the database
regards
A

Joomla 3.9.18
php 7.3

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
Re: ISSUE Editing Order Calculations are not correct
« Reply #5 on: April 07, 2020, 16:57:13 pm »
Any hints appreciated, please help me find where the calculations for this are. Notice this still works in 3.2.12 and is the only thing holding me back from upgrading. With COVID-19 shutting down most of my clients businesses I have time to get this fixed now ... I will post what I figure out here.
Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3175
  • VirtueMart Version: 3.8.2
Re: ISSUE Editing Order Calculations are not correct
« Reply #6 on: April 07, 2020, 18:49:02 pm »
Max is happy how the current system is functioning regarding BE order edit and the difference in values it creates from the FE order values - even when there has been no actual change in the order items (i.e open order for edit in admin and save - no changes no nothing else - just save)  Issue with discounted items

Try looking here

Model -> orders.php -  public function updateBill(
current code
Code: [Select]
`order_subtotal`=(SELECT sum(ROUND(product_item_price, '. $rounding .')*product_quantity) FROM #__virtuemart_order_items where `virtuemart_order_id`='.$ordid.'), ';

I dont use this yet - but it did function in my tests back in November 2019 - maybe you can test:
Code: [Select]
`order_subtotal`=(SELECT sum(ROUND(product_item_price, '. $rounding .')*product_quantity)+product_subtotal_discount FROM #__virtuemart_order_items where `virtuemart_order_id`='.$ordid.'), ';
I have just tested and the order_subtotal stored after BE edit is now the same value as the original FE value (note this test was done without changing ANY BE line item values)
regards
A

Joomla 3.9.18
php 7.3

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
Re: ISSUE Editing Order Calculations are not correct
« Reply #7 on: April 11, 2020, 19:04:17 pm »
AH - Thank you for your help and hints!

If I add the tax to the discounted value entered it works!! Is this intended? Would you have thought of adding the tax to your discount? If this is as intended then I guess I can live with it... if not then:

Please point me to where the calculations and DB updates are done when updating the order which then updates these 2 tables: #__virtuemart_order_items, #__virtuemart_order_calc_rules

I might be able to fix this when I can see where the calculations and updates to these tables are done... I can't find these in models/orders.php (I only see update for #__virtuemart_orders)

Hope this makes sense to you: I found the error is that the entered value for the discount is first divided by the tax before using it in the calculations. In my example above with the print screen I found I got the wrong numbers as shown when the discount amount entered was divided by (1 + tax %) >> $138.90 / 1.13

The fix is likely easy logic: IF entered discount is not empty && all other entered values are empty && calculate box is checked THEN:
product_priceWithoutTax = product_item_price - product_subtotal_discount;
product_discountedPriceWithoutTax = product_item_price - product_subtotal_discount;
product_tax = (product_item_price - product_subtotal_discount) * calc_value;

FYI – this took me a while to figure out using a spreadsheet where I listed all the values before and after making the changes - then I added the calculations as they would be expected and then tried different values - see attached print screen.


Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3175
  • VirtueMart Version: 3.8.2
Re: ISSUE Editing Order Calculations are not correct
« Reply #8 on: April 12, 2020, 17:27:54 pm »
Ok I am going out on a limb here because you have not supplied enough information (although you have supplied more than most :-) )

Your discount rule - I hope it is using DBTax and NOT VatTax

So my figures do not adjust the discount based on VAT

calc_rules table does not seem not to get updated

The update table stuff is all in:-

Model -> orders.php

Obviously not a massive help but I tend to search for #__virtuemart_order_items, #__virtuemart_order_calc_rules etc and see if the order edit attempts to use the various functions

I also use an Integrated development Environment which allows me to debug and step through code which provides me with far more information  than only being able to looking at table entries
FYI - PhpStorm from Jetbrains
Costs a small amount but saves me hours and really helps me see what is going on :-)

regards
A

Joomla 3.9.18
php 7.3

Robert_ITMan

  • Jr. Member
  • **
  • Posts: 160
    • websites developed and managed @ ourfingertips.com
  • VirtueMart Version: LIVE 3.2.12 and testing latest
Re: ISSUE Editing Order Calculations are not correct
« Reply #9 on: April 13, 2020, 16:41:38 pm »
Your discount rule - I hope it is using DBTax and NOT VatTax
Good point ... that is likely the issue here - I will test further.

Oh no! I do use VatTax - had to change to that back in Oct 2018 as it was the only thing that worked for me, see: http://forum.virtuemart.net/index.php?topic=141225.0 - looking at this topic and also looking through the VM Docs and searching the forum and other sources I can't find any information on DBTax and what makes it different than VatTax - or what it stands for. I do see in the Docs that VatTax re-calculates taxes after discounts and I'm not sure I understand how that could ever matter. Anyways, as a workaround in Oct 2019 I found only VatTaxit worked on my LIVE 3.2.12 sites ... so with current version maybe this issue is corrected / or now it is the other way around and only DBTax works ... it seems to me VM keeps changing how it performs these calculations ... I wish I knew why the extra options as changing this makes editing past orders even more complicated and might make extra work in getting accurate revenue and tax reports.

PhpStorm from Jetbrains
Costs a small amount but saves me hours and really helps me see what is going on :-)
Thanks for the tip! I've been using DreamWeaver since the beginning... maybe time for something new and PHPstorm looks really good.
Let me know if I can help in anyway!

Hope all is good with you and yours - stay safe! So far all is good with me and mine.

www.ourfingertips.com - websites developed and managed @ ourfingertips

Let me help you make the most of the technology at our fingertips

pinochico

  • 3rd party VirtueMart Developer
  • Jr. Member
  • *
  • Posts: 384
    • MiniJoomla
  • VirtueMart Version: 3
Re: ISSUE Editing Order Calculations are not correct
« Reply #10 on: April 13, 2020, 19:53:02 pm »
Quote
I also use an Integrated development Environment which allows me to debug and step through code which provides me with far more information  than only being able to looking at table entries
FYI - PhpStorm from Jetbrains

I already use 7 years. And gitlab and devel version and production version for eshop. An invaluable tool for application development. I saved a lot of time
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3175
  • VirtueMart Version: 3.8.2
Re: ISSUE Editing Order Calculations are not correct
« Reply #11 on: April 14, 2020, 10:30:56 am »
Robert

I meant DBTax for the discount calculation rule (see diagram below)
Yes - VatTax for products


Quote
PHPstorm looks really good.

I would give it a massive thumbs up, I love having the ability to debug code with simple "wait" points, step through code as it jumps around different files along with the ability to look at all the variables set and their values - it has saved me sooooooo much time and effort - plus I get to learn what is happening as I quickly search all the code for functions and/or jump around functions as the code is executed.
regards
A

Joomla 3.9.18
php 7.3