Product order price currency mixed up on new order

[lampi87]

Bug

No currency definition for product order price results in a mixup of currencies on orders

Environment

• Version: 6.0.2
• OS: Linux
• Web server: Apache/2.4.18
• PHP: 7.0.22-0ubuntu0.16.04.1
• Database: MariaDB 5.7.20-0ubuntu0.16.04.1

Expected and actual behavior

As currency is not defined at product order price page the prices at the "supplier" -> "generate order" page are mixed up. E.g. from international suppliers I get a USD price. At the moment I have to calculate USD price to EUR and enter this value at product order price page. If USD->EUR rate changes I have to recalculate EUR price and update product order price. Instead of defining USD price and just globally update USD->EUR rate

Steps to reproduce the behavior

• Set own currency to "EUR" and create currency "USD" with rate 1.17
• Define a product "Antenna" for company "antenna factory"
• "antenna factory" has currency "USD"
• Define order price for 1 antenna to be 1 (expected to be 1 USD)
• Create a new order for "cable" at company "cable factory"
• Price per pcs (net) is 1 and net price (currency) is 1.17 As a result the order is 1€ instead of 0.83€

[Attached files]

Order price defined: 0,25 (expected to be USD) Currency for partner/supplier: USD On create order page: 0,25 is used as EUR Expected total result: 327$ (27$ shipping, 300$ goods) Actual result: 379,67$ (27$ shipping as manually defined, 352,67$ goods)

[gregober]

I totally agree with you ! Have the exact same problem.

Product price SHALL depend on the defined SUPPLIER's currency NOT on default CURRENCY.

[lampi87]

There is a temporary workaround for this: Create new order, set currency to your own, add product, at the end change currency to real one.

But it's just a workaround

[caos30]

I think that i solved the problem. I means, supposing that:

1. although in Dolibarr the prices for product/services has not a currency specified, we assume that the currency of each price is the currency of the supplier
2. so in an order, i modified the addLine() method of the order class ("fournisseur commande" class) to invert the 2 prices for the product (system currency and supplier currency) if the currency exchange rate is not equal to 1.

Do this: in this file

/htdocs/fourn/class/fournisseur.commande.class.php #1467

insert the next lines:

// == reverse prices if the supplier currency don't match system currency
// == in other words, it's enough to check the currency exchange ;)
    if ($this->multicurrency_tx != 1){
         $pu_ht_devise = $pu;
         $pu = round($pu_ht_devise / $this->multicurrency_tx , 4);
    }

just before of this other:

$tabprice = calcul_price_total($qty, $pu, $remise_percent, $txtva, $txlocaltax1, $txlocaltax2, 0, $price_base_type, $info_bits, $product_type, $this->thirdparty, $localtaxes_type, 100, $this->multicurrency_tx,$pu_ht_devise);

I'm using Dolibarr 5.0.4.

@eldy i would feel safer if you take a look to this. But i have done several tests and it seems to work fine. The good new is that the change is minimal and it affect to whatever module that use this method to add a line to an order.

Precisely i had to work on it to make run a module called "purchases" that works as an assistant to guide a purchase process, allowing to compare prices between providers even with different currencies (i programmed a fast query to IMF to get "day exchange rates"), and generating orders autmoatically with a simple click. I'm in the phase of implement and test this new module in the Dolibarr of a customer, but i'm working on translate it to share it on Dolistore ;)

Cheers!

[caos30]

I'm sorry, making more testing on this issue just today after post my previous solutions i discovered that it's better this other solution which decide if to invert prices comparing the currency of the supplier and the currency of the system (the currency of our company in Dolibarr, the default currency).

To get this working it requires to query database to get the currency of the supplier. It looks like this:

// == reverse prices if the supplier currency don't match the order currency
    // == get the supplier currency
        $supplier_currency = '';
        $sql = "SELECT * FROM ".MAIN_DB_PREFIX."product_fournisseur_price as pr "
               ." LEFT JOIN ".MAIN_DB_PREFIX."societe as supp "
               ." ON pr.fk_soc = supp.rowid"
               ." WHERE pr.rowid=".$fk_prod_fourn_price;
        $resql = $this->db->query($sql);
        if ($resql) {
            while($row = $resql->fetch_assoc()) $supplier_currency = $row['multicurrency_code'];
        }
    // == decide if to invert prices
        if ($supplier_currency != $conf->currency){
            $pu_ht_devise = $pu;
            $pu = round($pu_ht_devise / $this->multicurrency_tx , 4);
        }

The problem with my previous solution is in the case that order currency choosed is not the supplier currency but neither the default currency. Well, in fact. in this rare case i don't find a coherent solution. But the solution above i think that avoid some confusion in this sense.

@Eldy, i ask to my self if has any sense to let user to make an order for a supplier in a DIFFERENT currency that the currency assigned to the supplier in their profile. Has it any sense? I means, it's supposed that the prices introduced in the system for this supplier are in their currency... so, if we need to take this prices to do an order... Is it not always in this currency?

If we agree with this assumption, then maybe the currency of the new order should be FORCED by the supplier choosed by the user.

Ufff.. this mater really it's a pain :P

[webartis]

nope :( in 6.0.3 it does not change anything doing the command by the replenish function. Still have to use the "workaround" to change currency at the last moment.

Oh other point : on a manual command, the product price amount is in currency, but shown as society default currency.

In fact it seems that the source of the issue is not the suplier as mentioned in the initial post, but just the product order price that is not mentionned in the supplier currency OR in "A" currency : a supplier who sells in euro "by default" can sell THIS product for me in USD or yuan :p

[caos30]

@webartis be careful: when you create an order ("command") it takes by default neither the supplier currency or the society default currency, but it put in that form (for new order) a SELECT control where the selected currency by default is the FIRST in the control, ie. without any SENSE!

@eldy i think this is a point of confusion for the users. The natural way should be that it was the society default currency at least, or in the best scenario quit this currency control from the first form, and once the order is created as DRAFT Doli assign the supplier currency. I think that the currency of an order should match with the supplier currency, without possibility of change.

So, @webartis just like the Doli work now, it's very likely that you create a new order in EUR being the supplier currency USD (for example), so then the product/prices you add to the order will be inserted BAD until you assign USD as currency of the order.

Let me know if i missunderstood to you. In any case, i invite to you to use my patch... i'm quite sure that it will run for your problems.

[webartis]

Hi :)

Thank you for your answer. In fact this is how it work at the moment, but not as it could/should :p

Well the simpliest scenario could be :

• society ME in currency A
• Supplier X in Currency B
• Supplier Y in currency C
• Product 1 supply price in supplier X entered in currency B
• product 1 supply price in supplier Y entered in currency C
• Order (whatever the mean, auto replenish or manual order to suppliers ) created in the correspondant product currency.

At the moment the workaround allow to change the currency directly on the order, just have to be carefull.

The problem is that the AMOUNT of the product price seems to be considered as currency A and shown as this wich is very confusing also on the stock valorization panel mentioned in the society currecy - wich is absolutly normal - because as you an seee, the stock valorization is perfectly correct -translated in MY currency A -last image)

I tested the patch but with no chage at all :)

The "goal" should be to have the oreder directly in the supplier currency, and avoiding the "last time" workaround :p

illustrations :

Price table (MAD is my A currency) :

replenish : price in MY currency

order with last time currency change (the famous woprkaround)

Correct stock valorization in currency A

[caos30]

@webartis Maybe i should put any more instructions about my patch, because i can assure you that it works JUST as you request!! ;)

1. first: include those lines of my patch in your Doli
2. the effects of the patch ONLY affect to NEW LINES added to an order... not the exsisting lines
3. so you should 1) create a new order, 2) set correctly the currency of the supplier on the order, 3) add as many products you have in the database WITH A BUY PRICE for this supplier.
4. this last point is very important: my patch run ONLY FOR products existing in database, with a "buy price" for this supplier. Furthermore, i suppose that you put those "buy prices" for this upplier for that products IN THE CURRENCY OF THE SUPPLIER.

Ufff... i'm sorry, this matter is quite complex to explain. I hope you understood me. In any case, if you followed these instructions and cannot make work it, please contact me by private message and we can talk through chat on Hangouts, for example. I really would like to leave this matter solved ;)

Edited: I think that here (Github) is not possible to see the email of other users or send "private messages", so, let me put here my email address. Afterward you take it i will remove it (you know: to avoid spam robots). Email for hangouts: sergio.cero@-----com ;)

Note: i'm living on Mexico, so i'm on (UTC -7). So 8 hours from Europe time.

[webartis]

taken ;)

[caos30]

Hi @eldy i've downloaded and installed the last version of Doli on branch 6.0 (6.0.4) and this issue problem persist (briefly: confusion with the price in each currency when adding a new product to an order in a currency different to the default system currency, "societe divise"):

In my example, MXN is the "societe divise" (the default currency of the Doli company owner), and the order is for a supplier with USD prices, so must be in USD.

The good new is that i inserted the lines of my patch:

// == reverse prices if the supplier currency don't match the order currency
    // == get the supplier currency
        $supplier_currency = '';
        $sql = "SELECT * FROM ".MAIN_DB_PREFIX."product_fournisseur_price as pr "
               ." LEFT JOIN ".MAIN_DB_PREFIX."societe as supp "
               ." ON pr.fk_soc = supp.rowid"
               ." WHERE pr.rowid=".$fk_prod_fourn_price;
        $resql = $this->db->query($sql);
        if ($resql) {
            while($row = $resql->fetch_assoc()) $supplier_currency = $row['multicurrency_code'];
        }
    // == decide if to invert prices
        if ($supplier_currency != $conf->currency){
            $pu_ht_devise = $pu;
            $pu = round($pu_ht_devise / $this->multicurrency_tx , 4);
        }

in the file:

/htdocs/fourn/class/fournisseur.commande.class.php #1496

and it works fine out-of-the-box! I removed the item added to the order and added again, and this time the new line was correctly added to the order, as you can see on this screenshot:

I hope you find this patch enough interesting and valid. I must mention that this patch only modify the addLine() method, not the updateLine(). It's not necessary! i've modified that line, changing the price (10.00 to 11.00 USD) and it runs perfect. So, the bug only affect the adding of the line to the order.

Cheers, and certainly we must appreciate your not-payable work with Doli. Thanks.

[caos30]

@eldy I must add another interesting comment if you will implement my proposed change, because i detected an odd behaviour that i couldn't explain (sincerely, i didn't search enough deeply). Also is regarding this issue of price in "system currency" vs. "supplier currency".

it seems that "always" (or sometimes...) the calculation is from "system" to "supplier" currency when there are conversions. I say this because although in the addLine() method affected by my patch (at file /htdocs/fourn/class/fournisseur.commande.class.php#1496) now we set a price of ROUND 10.00 USD (supplier currency), i must put a price with AT LEAST 4 decimal digits to "system currency", because if not then Doli render at the product line on the order page something like: 10.0001 USD instead of 10.00 USD.

So, in some way, Doli doesn't respect that the MAIN price is in this case USD and not the system default currency (MXN in my example), and then re-calculate prices "in supplier currency from system currency" instead of re-calculate prices "in system currency from price in supplier currency"... altering the final price!

For this reason, in my patch i applied a round(...,4) to "guarantee as maximum" that the decimals doesn't affect the calculation in the supplier currency, which is the more interesting in these cases in foreign currencies! ;)

But as i said at the beginning, i didn't search how to fix this. So, i preferred to alert to you, because i suspect that you could follow an strategy similar to my patch to manage these questions. Possibly in the construct() method of the object we must get and set on a new property of the object the supplier/order currency, and use it to make the correct conversions on any operation (addLine(), updateLine(), rendering on browser, etc...).

Thanks a lot for your time. I'm here if you need to test something or whatever you need.

[eldy]

Currently, the default supplier prices can only be entered into the main currency of your company. Entering into the currency of the supplier is not yet available. Work for this is in development.

[caos30]

Ok. This explain the current behaviour with prices in orders (commandes). I understand then that my patch is working in a different logic than Doli :(

But if you ask me, i would say that the "coherent" logic would be that we could add prices for a supplier in their currency. If my supplier is for Mexico, i shall pay to him in mexican pesos, so their quotations, orders and even factures will be in mexican pesos!

@eldy i've almost finished a new module "purchases" to work as a wizzard (assistant, guide) to easily (with the minimum clicks) let the user of Doli define a list to products to purchase, and automatically this module render to user a table comparing prices of different suppliers, even with different currencies (using a call to International Monetary Bank to get current exchange rates), and letting to user add new prices for other suppliers for those products (in that same table view!), and letting the user select the preferred price for each product and generate orders (Doli commandes) from there with another click. This module, also, offer a form to let suppliers capture its prices (previous sending a quotation request by email by the user).

The thing is that in all this development i assumed that the "buyprices" stored in the dolibarr table at database are in the supplier currency. As i said, this is the unique logic coherent with real world reality. And with a unique patch (several comments above of this, hehehe) i reversed the addLine() method of the "fournisseur.commande.class" to put "correctly" the prices in each currency. Yes, i understand now that -as you said- Doli doesn't interpret in this way the relation of currencies/prices.

I'm sorry to insist in this question, but i think that it's NUCLEAR of an ERP (i think you too). And just now that i'm working the last 4 weeks intensively in this matter, i've preferred to try to "discuss" this matter with you. Believe me, i only want to help. I can understand that you have your designed plan to solve this question. But you have my offer to talk this by chat if you want (hangouts: sergio.cero@gmail.com).

Cheers!

I share here 2 screenshots of that "purchases" module. It can help to understand better :)

This is an screenshot of the form presented to supplier to capture their prices. The module let you send an automatic email inviting supplier to visit this form (has a unique and encrypted "public" ID).

Before you comment it: i preferred not to use the "quotation requests" native in Dolibarr, just precisely to avoid the question of currencies management, sorry.

[webartis]

huu sorry but no. It does not work like that : If i enter 45 as price in the product, and the i make an order with this product in euros (if my company is not in euros) the "45" will be correctly considered as euros AND change in my currency.

In fact it works perfectly IF we pay attention at the last moment to the currency of the order.

Tne "price in currency" (and for further developement, not automatically in the SUPPLIER currency but in A currency) is the only thing missing to automatize, for ex. the replenish function.

Manual orders works fine since we fix the currency manually, and the the product price entered on product buying details will be taken as the order currency :)

Indeed this is, i think, a crucial developement point for internationalising Dolibarr :)

[gregober]

Is there any way to speed-up the patching of this upstream ?

Entering into the currency of the supplier Getting suppliers orders to handle multi-currency

Thanks for your answer.

[edupulpillo]

Really the implementation of this pacth or the solution ofthe problem is essential for companies that perfomr the sourcing in different countries. Is going to be available in 7.0.2 ?

[webartis]

not yet unfortunatly in 7.0.3. Did not see the 8 changelog though...

[hregis]

this is ok in the 8 branch

[seblu]

Tested with 8.0.2.

Looks ok in the web interface, but the generated PDF (muscadet) has a wrong Total (net of tax).

[seblu]

I submitted a pull request to fix this: #9809

[eldy]

@caos30 With v8, now the prices into supplier prices table should contains the currency so this will avoid error of interpreting what is the currency in supplier table.

[caos30]

Wooowww... perfect! Finally perfect. Thanks to share this new with me. I will check the compatibility of my 2 modules with v8 soon :-)