I am trying to create a currency update button on the navigation screen in order that my staff input the local bank issued Exchange Rate for Sri Lanka Rupees to US$. There is no API available to do this automatically and I need a simple method to input the Rate.
I have set up a simple button which displays the current rate and execute an automation command to set Global Program setting named “Dollarrate” with a prompt [?Todays Dollar Rate;;;ONC].
This works fine
I then execute a script designed to update the US$ currency value set up in the database as detailed below.
function exchangerate()
{
dlg.ShowMessage('Hello');
function getValue(settingName) {
var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
var rate = sql.Query(qry).First;
return rate;
}
// 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 = 'US$'";
//execute SQL statement
sql.ExecSql(sqlexec);
}
dlg.ShowMessage('Rate:'+rate);
}
The script executes fine but I cannot set var rate with the value of Dollarrate. If I manually set the var rate to a value using var rate = 160, the script executes perfectly .
The problem appears to be in the following section of script
function getValue(settingName) {
var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
var rate = sql.Query(qry).First;
return rate;
}
Maybe I have some syntax error in here but this is copied from working examples shown by @QMcKay in numerous posts. Anyone know what I am doing wrong?
Variable Dollarrate has no value according to your script. You are not passing in a value as a parameter, nor are you hard-coding a value for that variable. So it is like executing the following query:
SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='undefined'
Dollarate is a Global Program Setting I have defined in a Program setting Action as part of this automation. It is a value assigned by a prompt as detailed below
added that the beginning of the script but no effect.
Maybe I am overcomplicationg this. I need to set the script variable rate with the value set in the Program Setting Dollarrate. I do not need to set Dollarrate as a variable in the script, I just need to apply the value input by the prompt in my automation to the script var rate
No, it is not complicated; you just don’t understand how to code using JS LOL.
Also, you could use GraphQL instead of SQL, but you still need to use JS, and if you don’t understand what I am trying to tell you about your JS Variables, then it won’t help at all … see how I initialize the variables here first, to give them a value that gets concatenated into the GQL query …
var settingName = 'Dollarate';
var settingValue = 1.234;
gqlCommand = 'mutation m {updateLocalSetting(name:"'+settingName+'",value:"'+settingValue+'"){name,value}}';
gql.Exec(gqlCommand);
That is very true. I need to do some more reading and experimenting. Is there a good section that will give me some better idea of the JS syntax and structure as cribbing other peoples scripts and trying to adapt them is clearly not ideal.
Is it just me or are you not declaring a function within a function here?
function exchangerate()
{
dlg.ShowMessage('Hello');
function getValue(settingName) {
var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
var rate = sql.Query(qry).First;
return rate;
}
// check if a rate is set
var isRateValid = ( rate == 'undefined' ? false : true );
// execute the update if rate and date is valid
if ( isRateValid )
Yes I see, this is due to cutting and pasting various code to read a Program Setting Value with JScript and not checking properly.
I thought this would be a simple bit of code to start learning JS with but clearly no such thing exists (for me anyway). I have been reading through your early days in generating your Newbook link. I now have a PMS that I will be linking with some simple(!) JSON commands and the first thing I wanted to do was synchronise my exchange rates on the 2 systems. Set up a simple button to input the days exchange rate from the Navigation screen, saved that to a Program setting called Dollarrate.
Now I cannot read this value with the script, which I need to do to then apply this to currency settings thus
var sqlexec = “UPDATE ForeignCurrencies SET ForeignCurrencies.ExchangeRate = " +rate+ " WHERE ForeignCurrencies.name = ‘US$’”;
This works in changing the exchange rate but I just cant read the Program setting to apply to the rate variable in my script.
You will only need the “GQL” functions or the “SQL” functions; not both. This is just to illustrate different ways to get the same result. The “SQL” functions were the only way to do this via JScript before GraphQL came into existence. Now my preference is the “GQL” methods.
Name:Update Setting Handler:setting
function readGlobal(settingName,method) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
method = typeof method === 'undefined' ? 'GQL' : method;
var returnVal = "UNKNOWN";
if (method=='SQL') {
returnVal = readGlobalSQL(settingName);
} else {
returnVal = readGlobalGQL(settingName);
}
return returnVal;
}
function readGlobalSQL(settingName) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
var qry = "";
var res = "";
var currentValue = "UNKNOWN";
var newValue = "";
if (settingName != '') {
qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name] = '" + settingName + "'";
res = sql.Exec(qry);
currentValue = res[0];
}
return currentValue;
}
function readGlobalGQL(settingName) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
var qry = "";
var res = "";
var currentValue = "UNKNOWN";
var newValue = "";
if (settingName != '') {
qry = '{setting:getGlobalSetting(name:"'+ settingName + '"){name,value}}';
res = gql.Exec(qry);
res = JSON.parse(res);
currentValue = res.data.setting.value;
}
return currentValue;
}
function updateGlobal(settingName,settingValue,method) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
method = typeof method === 'undefined' ? 'GQL' : method;
var returnVal = "UNKNOWN";
if (method=='SQL') {
returnVal = updateGlobalSQL(settingName,settingValue);
} else {
returnVal = updateGlobalGQL(settingName,settingValue);
}
return renturnVal;
}
function updateGlobalSQL(settingName,settingValue) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
var qry = "";
var res = "";
var currentValue = "";
var newValue = "";
currentValue = readGlobalSQL(settingName);
if (settingValue == 'increase') {
newValue = Helper.ToNumber(currentValue) + 1;
} else if (settingValue == 'decrease') {
newValue = Helper.ToNumber(currentValue) - 1;
} else {
newValue = settingValue;
}
qry = "UPDATE [ProgramSettingValues] SET [Value]='" + newValue + "' WHERE [Name] = '" + settingName + "'";
res = sql.Exec(qry);
return newValue;
}
function updateGlobalGQL(settingName,settingValue) {
settingName = typeof settingName === 'undefined' ? '' : settingName;
settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
var qry = "";
var res = "";
var currentValue = "";
var newValue = "";
qry = '{setting:getGlobalSetting(name:"'+ settingName + '"){name,value}}';
res = gql.Exec(qry);
res = JSON.parse(res);
currentValue = readGlobalGQL(settingName);
if (settingValue == 'increase') {
newValue = Helper.ToNumber(currentValue) + 1;
} else if (settingValue == 'decrease') {
newValue = Helper.ToNumber(currentValue) - 1;
} else {
newValue = settingValue;
}
qry = 'mutation m {setting:updateGlobalSetting(name:"'+ settingName + '",value:"' + newValue + '"){name,value}}';
res = gql.Exec(qry);
res = JSON.parse(res);
newValue = res.data.setting.value;
return newValue;
}
So guys have learnt a bit more about scripts but having some problem with Posting JSON.
The script below works fine until trying to Post data to Beds24 api.
The script collects the exchange rate, applies it to the currency setting in Samba and applies it using the var rate to the JSON script I am trying to Post to the Beds24 API.
the variable jsondata is a formatted JSON string which I have posted to the Beds24 page both manually and with Postmaster. Both methods work fine and update the Beds24 site accordingly.
Nothing happens with Sambapos and something is wrong with the script as my final Test message does not display.
Personally I would return the postdata and drop in json validator site to first check the string is correctly formatted.
Do you get nothing in your response?
One this you might want to check is the TLS version required by the API
Newbook upped their minimum for PCI reasons which requires a registry edit for .net to force higher security level.
which is what I expected to see (setting template4 with the value defined by currency rate). Also direct input of the variable into the text box of the API webpage also works perfectly.
If I paste the jsondata into different Json validation sites it gives me different responses and I cannot understand what I need to do.
Is there an alternative URL I can use from SambaPos which will show me exactly what web.PostJson is posting (if it is actually posting anything)
JSONlint.com tells me it is valid JSON. My worry is web.PostJson in Samba is somehow changing the output.
So it validated the structure of the variable and then tarted it up for me also. if sambaPOS is posting the variable unchanged, there is no reason this should not work.
Incidentally I copied the tarted up version of the Json string into my samba script and it completely broke the script execution.