Automatic update of exchangerate

V5 Question
1

Ok i’ve been able to create a Jscript that uses the JSON of fixer.io to get the exchangerate for my foreign currency, Euro against Romanian Lei.
This script is a part of the script which i also use for a dynamic tile.

function exchangerate()    {
var nL = '<linebreak/>';
var base = 'EUR'
var symbol = 'RON'
var u = 'http://api.fixer.io/latest?base='+base+'&symbols='+symbol+'';
var getrates = web.Download(u);
var allrates = JSON.parse(getrates);
var currentdate = allrates.date;
var rate = allrates.rates.RON;   }

This script will give me the variable rate with today’s value of 4.435
So far so good.

Now the next step i would like to do is update the exchangerate in Sambapos with this rate value using Actions, Rules and a Trigger to check and update 4 times a day (3, 3.30, 4, 4.30pm CET)

The Action(s) should call the script(s) etc. I don’t think that will be an issue to setup correctly. but for passing the output of the Jscript into Samba i could use some assistance.

My Questions;

  • Is there a report command or another command for setting exchange rate in SambaPos?
    I guess {EXCHANGE RATE:X} is only for displaying the rate. So i would think i should use an simple SQL Script with:
UPDATE ForeignCurrencies ;
SET ForeignCurrencies.ExchangeRate = *Rate variable* ;
WHERE ForeignCurrencies.name = EUR

So should i update the database directly using SQL or is there a better method?

  • Could this be done with a single script, JScript and SQL combined or must they be seperate script?
  • And If so what would be the best method of passing the variable from Jscript to the SQLscript?

Off course i’ll have to add an condition that if the value for the rate is undefined the exchange rate will not be updated and will stay the last value (Probably SQL won’t allow a setting of undefined but better safe than sorry)

So up till now i did some homework but now i’m getting kind of stuck…
Does anybody has any thoughts?

2 Likes
2

I don’t think so. Script is the way to go (SQL).

You have no choice at this time - there is no Action that supports this, so you must use SQL.

You can do it in a single script, using the sql.Query() and sql.ExecSql() helpers to read and set the rate.

simply store the value that comes from JSON in a JScript variable, and use sql.ExecSql() to make the update with a concatenated statement.

You could add the following code (or similar) to your current function.

// get rate from JSON
var rate = 'some JSON value';

var isRateValid = ( rate == 'undefined' ? false : true );
// do more checks ...

// execute the update if rate is good
if (isRateValid) {
  // create SQL statement
  var sql = "UPDATE ForeignCurrencies SET ForeignCurrencies.ExchangeRate = " + rate + " WHERE ForeignCurrencies.name = 'EUR'";
  // execute SQL statement
  sql.ExecSql(sql);
}

Absolutely. Add a condition before the sql.ExecSql() function to ensure you have a valid rate.

I think you should probably reset/clear the cache after this update. There is an Action for this purpose named Refresh Cache, so that SambaPOS will read the updated value.

3 Likes
3

@QMcKay I’ve quickly read your reply and it seems exactly what i wouldn’t have figured out within a week… But now i have some social obligations so i will test it tomorrow.

Thanks :smile:

4

@QMcKay to the rescue again, bow down to the SQL master LOL

1 Like
5

OK, i’ve got the script part of my automatic update for the Exchange rates it working.
If i test the script it will update the Exchange rate.

But i can’t get it to work automatically with an Action, Rule and Trigger. (The part i would have thought to be easy)
I Think i’m making a mistake in my action and the script doesn’t get fired…
I’ve already tried several things for Function and Command but no result.

Action

The Script: I’ve named it currency, so is the handler

    /*
    jscript to Update SambaposV5 exchange rates v.0.1
    Tested with sambapos v5.1.54 
    
    USE AT YOUR OWN RISK!, This is not written by any Sambapos developer!
    It can mess up things, stop working in the future, or maybe even cause a global meltdown of the entire planet...
    
    If correcly excecuted in sambapos by actions, rules and triggers it can automatically update the foreign currency in sambapos.
    The jscript uses the JSON API for foreign exchange rates and currency conversion (http://fixer.io)
    Fixer.io is a free JSON API for current and historical foreign exchange rates published by the European Central Bank.
    The rates are updated daily around 3PM CET.
    
    Important: To get this script working within Sambapos you will have to setup your foreign currency with the proper 3 letter code that's being used by International banks
    
    To setup to your own need (for a single currency) change the "var base" and "ForeignCurrencies.name" value from 'EUR' to your own foreign currency and the "var symbol" & "var rate" from 'RON' 
    to the currency your Sambapos system is using (e.g. your own local currency).
    
    Grtz Proeftuin,
    Special thanks to QMcKay & emree 
    */
    
    function exchangerate()
    {
    // The base currency to retrieve your exchange rate for, your forgein currency, CHANGE if needed
    var base = 'EUR';
    
    // The currency for which the exchange rate should be retrieved, your default currency, CHANGE if needed
    var symbol = 'RON';
    
    // the URL for the fixer.io JSON and download the rates
    var u = 'http://api.fixer.io/latest?base='+base+'&symbols='+symbol+'';
    var getrates = web.Download(u);
    var allrates = JSON.parse(getrates);
    
    // select our actual rate, CHANGE if needed
    var rate = allrates.rates.RON;
    
    // check if a rate is set
    var isRateValid = ( rate == 'undefined' ? false : true );
    
    // execute the update if rate and date is valid
    if ( isRateValid ) 
      {
    //create SQL statement, CHANGE if needed
      var sqlexec = "UPDATE ForeignCurrencies SET ForeignCurrencies.ExchangeRate = " +rate+ " WHERE ForeignCurrencies.name = 'EUR'";
    //execute SQL statement
      sql.ExecSql(sqlexec); 
      }
    }

Anybody any clue?

6

Looks correct.

As a way of testing, put this inside your script, near the top:

dlg.ShowMessage('Hello');

At least you will find out whether the exec is happening or not.

For even more information, put the same helper call at the bottom of the script:

dlg.ShowMessage('Rate:'+rate);
7

Really strange,

I’ve added the dialogs in the script and while at it also an Automation command button and new rule using the same action.
And the script began to run…

Removed the Automation command button and new rule and it stopped working.

Re-added the Automation command button and rule but now it doesn’t work.

8

Ok, now it works on a Trigger with the extra Automation command and Rule.
I Had to logoff/logon

Edit: All working, removed the extra Automation command and Rule and it kept on going.

Thanks

1 Like