Good morning,
I have a number of currencies, in my Multi-Currency module, of Dollibar ver. 12.0.1, used for purchases and sales alike; the base currency is Euro and would like to have the rates automatically updated or synced with an online Forex rate provider or a bank. I have searched all related topics in this forum and searched dolistore for a solution, but to no avail. Hence, I should be grateful you would help me resolve this challenge.
Many thanks.
I am not sure an auto-update feature is available in Dolibarr, i didnt check very well. I have implemented this totally in mysql so we will not have issues when Dolibarr is updated.
Step 1 create a mysql currency table (preferably new database not in the Dolibarr database)
Step 2 auto update the currency table with a cron job every hour or every day as you like
Step 3 Use a mysql event to get last row of data from the currency table and write that data to llx_multicurrency_rate
I use the following code of php, which is called by the cron job.
<?php
// script to import currency data into a mysql table.
// connect to mysql db
// https://fixer.io/documentation
$dns = 'mysql:host=localhost;dbname=DATABASE-NAME';
$con = new PDO($dns, 'DATABASE-USER', 'DATABASE-PASSWORD', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//read the json file contents
$jsondata = file_get_contents('http://data.fixer.io/api/latest?access_key=PASTE_API_KEY%20base=EUR&symbols=USD,JPY,GBP,CAD,AUD');
//convert json object to php associative array
$data = json_decode($jsondata, true);
$success = $data['success'];
$timestamp = $data['timestamp'];
$base = $data['base'];
$date = $data['date'];
// Eur base rates from JSON
$eur_usd = $data['rates']['USD'];
$eur_jpy = $data['rates']['JPY'];
$eur_gbp = $data['rates']['GBP'];
$eur_cad = $data['rates']['CAD'];
$eur_aud = $data['rates']['AUD'];
// rebase to inverse JPY
$jpy_usd = $eur_jpy / $eur_usd;
$jpy_gbp = $eur_jpy / $eur_gbp;
$jpy_cad = $eur_jpy / $eur_cad;
$jpy_aud = $eur_jpy / $eur_aud;
$jpy_eur = $eur_jpy / 1;
$q = $con -> prepare("INSERT INTO currency (success, timestamp, base, date, jpy_usd, jpy_gbp, jpy_cad, jpy_aud, jpy_eur, eur_usd, eur_jpy,
eur_gbp, eur_cad, eur_aud)
VALUES (:success, :timestamp, :base, :date, :jpy_usd, :jpy_gbp, :jpy_cad, :jpy_aud, :jpy_eur, :eur_usd, :eur_jpy,
:eur_gbp, :eur_cad, :eur_aud)");
$q-> bindValue(":success",$success,PDO::PARAM_STR);
$q-> bindValue(":timestamp", $timestamp,PDO::PARAM_STR);
$q-> bindValue(":base", $base,PDO::PARAM_STR);
$q-> bindValue(":date", $date,PDO::PARAM_STR);
$q-> bindValue(":jpy_usd", $jpy_usd,PDO::PARAM_INT);
$q-> bindValue(":jpy_gbp", $jpy_gbp,PDO::PARAM_INT);
$q-> bindValue(":jpy_cad", $jpy_cad,PDO::PARAM_INT);
$q-> bindValue(":jpy_aud", $jpy_aud,PDO::PARAM_INT);
$q-> bindValue(":jpy_eur", $jpy_eur,PDO::PARAM_INT);
$q-> bindValue(":eur_usd", $eur_usd,PDO::PARAM_INT);
$q-> bindValue(":eur_jpy", $eur_jpy,PDO::PARAM_INT);
$q-> bindValue(":eur_gbp", $eur_gbp,PDO::PARAM_INT);
$q-> bindValue(":eur_cad", $eur_cad,PDO::PARAM_INT);
$q-> bindValue(":eur_aud", $eur_aud,PDO::PARAM_INT);
$q-> execute();
?>
<html>
<head>
<title>Welcome to currency storage</title>
</head>
<body>
<h1>Currency stored into the database</h1>
<p> <?php
echo nl2br ("jpy_usd -> " . $jpy_usd . "\n");
echo nl2br ("jpy_gbp -> " . $jpy_gbp . "\n");
echo nl2br ("jpy_cad -> " . $jpy_cad . "\n");
echo nl2br ("jpy_aud -> " . $jpy_aud . "\n");
echo nl2br ("jpy_eur -> " . $jpy_eur . "\n");
?> </p>
</body>
</html>
The mysql event query should look something like this, actual code depends on how you setup the above:
INSERT INTO llx_multicurrency_rate(date_sync,rate)
SELECT date, eur_usd, FROM currency
ON DUPLICATE KEY UPDATE
eur_usd = rate;
I hope this helps you to get some direction how to implement this relatively simple.
2 Likes
Hi Erik , nice solution and a smart one. Although I think that could be nice if we could join and develop a function in multicurrency module which could be part of dolibarr and have that functionality in dolibarr available with simply enabling the module. Just my 2c.
Please add a feature request here: https://github.com/Dolibarr/dolibarr/issues if you haven’t already.
Good afternoon Erik,
Thank you for taking the time out to write the code and propose a solution. Regrettably, I have little or no knowledge of coding or implementation of the proposed solution. All I know is a Multi Currency module in a comprehensive business solution environment, such as Dolibarr, is incomplete without a currency rate auto update. I wish I could help in the development of such tool, until I can, I will trust the professional to save the day.
1 Like
Does anyone here use any of trading platforms? Like, https://www.ratingfx.com/instaforex this one or other? I’m starting a new project soon and need some specifications for it.
Hi Everybody,
I look for that problem.
My solution was to deploy a small docker image on my stack for automatically updating the currency.
The code (Typescript) is here GitHub - ismogroup/dolirate: Small tool for automatically update currency rates in Dolibarr
The main idea is:
1- query the dolibarr database for retrieving the base currency and the used currencies
2- make a request to api•apilayer•com/exchangerates_data (250 free requests / month) for retrieving the rates
3- INSERT INTO llx_multicurrency_rate
the main logic is here:
Why Typescript, because the logic is integrated in a more complex NodeJS environment .
Hope it can help someone to build a better solution. But yet it is a sufficient solution for me.
1 Like
Hello. How can we add this update module as an add-on to the dolibarr infrastructure on a shared hosting running under a standard subdomain?
You can not, because it is not a Dolibarr module, it is a stand alone tool that serves up it’s own webserver.
IF you ran your Dolibarr inside a container (I use this image Docker) then perhaps you could run this tool inside the same container.
You would probably have a real hard time running it on a remote server because most likely your Dolibarr database would not be reachable over the internet due to security concerns.
| variable | usage | default |
|---|---|---|
| MYSQL_HOST | fqdn of mysql server | mysql |
| MYSQL_PORT | port of the mysql server | 3306 |
| DOLI_DB_USER | the user with access to the Dolibarr database | mysql |
| DOLI_DB_PASSWORD | the password associated with the user | mysql |
| DOLI_DB_PREFIX | the table prefix | llx_ |
| DOLI_DB_NAME | the name of the database | dolismo |
| API_LAYER_KEY | api key for Exchange Rates Data API - APILayer | tobefilled |
Actually thinking more about it, I think that if this ran inside the same container as Dolibarr, then I suppose that a CRON job could reach it - or someone could make a module for Dolibarr that would trigger it.
The server with Plesk panel installed directly belongs to me. There are different domain names within the server. I created a subdomain in one of these domains. Docker and Git plugins are also installed on the Plesk panel server. I can do it if you can briefly describe to me how to add it.
For example.
There is a domain name called erp.example.com and dolibarr is actively running here. If you could briefly describe what and how I can do on the server control panel, I can do it. Thanks so much in advance.
I think it would definitely be great if you developed it as a module.
sorry, I don’t use plesk, but I am sure that you could run this tool on your plesk server
who you?
I am not even a PHP developer, and I have my own stuff in Dolibarr I want to fix or create
