[BUG] Calculation of total price is wrong with mixed currencies

This issue has been tracked since 2023-01-11.

Please verify that this bug has NOT been raised before.

I checked and didn't find similar issue

Describe the bug*

When adding line items with different currencies, the total price calculated in the line items overview does not take into account different currencies per line.

This happens in Purchase and in Sales orders. 10 yen are only about 8 cents - 20 USD is way off the correct value.

These were both created on the demo server. The total cost, calculated at the top of the order, is correct in both cases (10.08 USD)

Steps to Reproduce

Create sales|purchase order
Add line items with different currencies
Check bottom of table summary - find wrong sum

Expected behavior

One could argue that it makes no sense to use different currencies for each line item. I was trying to use this in a sales order to calculate a total cost of an idea, with parts purchased from different vendors, in different countries.

Regardless of which, it is currently possible to do this, and the sum is wrong. Something must be changed. Options:

Make the calculation take into account the currencies, as with the total cost.
Remove the sum from the table - better no info than wrong info, possibly on if different currencies available.
Make it impossible to use different currencies in line items in an order. This would require setting a currency for an order, and sticking to that.

Deployment Method

Docker
Bare metal

Version Information

Version Information:

InvenTree-Version: 0.10.0 dev
Django Version: 3.2.16
Commit Hash: e730b5c
Commit Date: 2023-01-10
Database: postgresql
Debug-Mode: False
Deployed using Docker: True
Active plugins: False

Relevant log output

No response

matmair wrote this answer on 2023-01-13

@miggland Thanks for the report.
Seems like we will have to disable totals for now as the frontend does not know the rate.

miggland wrote this answer on 2023-01-13

I'm definitely not very good at how the frond-end works. I tried finding where that line is created, but no luck.

Can you point me to the right place, @matmair ?

matmair wrote this answer on 2023-01-13

@miggland I am also not really good with the current thing - although I am currently rebuilding it.

bloemp wrote this answer on 2023-01-15

@miggland you can disable the total price column footer of the purchase/sale order tables by commenting out the footer-formatter code:

InvenTree/InvenTree/templates/js/translated/order.js

Lines 2373 to 2385 in e730b5c

    
           footerFormatter: function(data) { 
        
               var total = data.map(function(row) { 
        
                   return +row['purchase_price']*row['quantity']; 
        
               }).reduce(function(sum, i) { 
        
                   return sum + i; 
        
               }, 0); 
        
               var currency = (data.slice(-1)[0] && data.slice(-1)[0].purchase_price_currency) || 'USD'; 
        
               return formatCurrency(total, { 
        
                   currency: currency 
        
               }); 
        
           }

InvenTree/InvenTree/templates/js/translated/order.js

Lines 2560 to 2572 in e730b5c

    
           footerFormatter: function(data) { 
        
               var total = data.map(function(row) { 
        
                   return +row['price'] * row['quantity']; 
        
               }).reduce(function(sum, i) { 
        
                   return sum + i; 
        
               }, 0); 
        
               var currency = (data.slice(-1)[0] && data.slice(-1)[0].price_currency) || 'USD'; 
        
               return formatCurrency(total, { 
        
                   currency: currency, 
        
               }); 
        
           }

InvenTree/InvenTree/templates/js/translated/order.js

Lines 3885 to 3897 in e730b5c

    
           footerFormatter: function(data) { 
        
               var total = data.map(function(row) { 
        
                   return +row['sale_price'] * row['quantity']; 
        
               }).reduce(function(sum, i) { 
        
                   return sum + i; 
        
               }, 0); 
        
               var currency = (data.slice(-1)[0] && data.slice(-1)[0].sale_price_currency) || 'USD'; 
        
               return formatCurrency(total, { 
        
                   currency: currency, 
        
               }); 
        
           }

InvenTree/InvenTree/templates/js/translated/order.js

Lines 4377 to 4388 in e730b5c

    
               var total = data.map(function(row) { 
        
                   return +row['price'] * row['quantity']; 
        
               }).reduce(function(sum, i) { 
        
                   return sum + i; 
        
               }, 0); 
        
               var currency = (data.slice(-1)[0] && data.slice(-1)[0].price_currency) || 'USD'; 
        
               return formatCurrency(total, { 
        
                   currency: currency, 
        
               }); 
        
           }

At first glance it seems to be doing some currency conversion... but not on individual rows. Will investigate if I can get it working

miggland wrote this answer on 2023-01-23

Thanks @bloemp, this is the code I was looking for. Now to either figure out how to do the conversion. Otherwise, the solution is to remove the total price.

Are you working on this? I would try to fix it, but not right now (other things keeping me busy :))

matmair wrote this answer on 2023-01-23

My recommendation regarding the mixed currency total would be to use the API to get that number.

miggland wrote this answer on 2023-01-23

Good point, better not to recalculate this as it's already done somewhere.

bloemp wrote this answer on 2023-01-23

@miggland still looking to see if I'm able to fix it. Tried a few things, but I was to focused on adding data to the order lines. Having read the recommendation of @matmair, and re-thinking my strategy. I'm now thinking on adding two more fields to the api/order/so/ and api/order/po/ endpoints that represent the sub_total for the order lines and the extra lines.

Total price and sub-total prices will then all be shown in the base currency.

matmair wrote this answer on 2023-01-23

Sounds good @bloemp . Please make a note of the speed of the endpoint before and after the changes, it is pretty simple to introduce slow DB queries unknowingly. The response time should not change much.
HMU if you need any pointers

bloemp wrote this answer on 2023-01-23

I've made a draft PR #4245 which is an initial solution to this problem, but it needs further refinement as for some reason the bootstrap table is firing the onPostFooter twice per table. So that's 4 times on page load.
I'm thinking to have the sub total values present in the normal GET call to the template, so I can pass them to the javascript routines without a callback to the API.

Your Name

Your Comment

More Details About Repo

Owner Name	inventree
Repo Name	InvenTree
Full Name	inventree/InvenTree
Language	Python
Created Date	2017-03-23
Updated Date	2023-03-31
Star Count	2586
Watcher Count	61
Fork Count	411
Issue Count	141

YOU MAY BE INTERESTED

Issue Title	Created Date	Updated Date

	footerFormatter: function(data) {
	var total = data.map(function(row) {
	return +row['purchase_price']*row['quantity'];
	}).reduce(function(sum, i) {
	return sum + i;
	}, 0);

	var currency = (data.slice(-1)[0] && data.slice(-1)[0].purchase_price_currency) \|\| 'USD';

	return formatCurrency(total, {
	currency: currency
	});
	}

	footerFormatter: function(data) {
	var total = data.map(function(row) {
	return +row['price'] * row['quantity'];
	}).reduce(function(sum, i) {
	return sum + i;
	}, 0);

	var currency = (data.slice(-1)[0] && data.slice(-1)[0].price_currency) \|\| 'USD';

	return formatCurrency(total, {
	currency: currency,
	});
	}

	footerFormatter: function(data) {
	var total = data.map(function(row) {
	return +row['sale_price'] * row['quantity'];
	}).reduce(function(sum, i) {
	return sum + i;
	}, 0);

	var currency = (data.slice(-1)[0] && data.slice(-1)[0].sale_price_currency) \|\| 'USD';

	return formatCurrency(total, {
	currency: currency,
	});
	}